delete tb where id=2
while @@rowcount<>0
delete tb where parentid not in(select id from tb ) and parentid<>0上面这些语句是存储过程的代码,请问分别代表什么意思。
现在我不用存储过程而是直接在VS2008环境下怎么进行转换,请指教。
while @@rowcount<>0
delete tb where parentid not in(select id from tb ) and parentid<>0上面这些语句是存储过程的代码,请问分别代表什么意思。
现在我不用存储过程而是直接在VS2008环境下怎么进行转换,请指教。
strSql=@"delete tb where id=2
while @@rowcount<>0
delete tb where parentid not in(select id from tb ) and parentid<>0";
SqlConnection conn=new SqlConnection("连接字符串");
///然后执行 获取返回值
over
string strSql="delete tb where id=2 while @@rowcount<>0 "
+" delete tb where parentid not in(select id from tb ) and parentid<>0"
然后把字符串传到数据库操作方法中就可以了
using System.Collections.Generic;
using System.Text;using System.Configuration;
using System.Data;
using System.Data.SqlClient;namespace Wicresoft.Common.Utility
{
public static class SqlHelper
{
public static readonly string connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString(); public static DataSet GetDataFormDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(cmdText, conn);
command.CommandType = cmdType;
if (commandParameters != null)
{
foreach (SqlParameter parm in commandParameters)
command.Parameters.Add(parm);
}
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
} public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
} public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
} public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString); try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
} public static SqlDataReader ExecuteReader(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand(); try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(); cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
} public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand(); using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
} public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
} private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{ if (conn.State != ConnectionState.Open)
conn.Open(); cmd.Connection = conn;
cmd.CommandText = cmdText; if (trans != null)
cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
}
SQL2005以后 DELETE 可以不写FROM了
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string strSql)
{
using (SqlConnection connection = new SqlConnection(SERVER_CONNECTION))
{
using (SqlCommand cmd = new SqlCommand(strSql, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}int rowcount=ExecuteSql("delete tb where id=2");
if(int !=0)
{
ExecuteSql("delete tb where parentid not in(select id from tb ) and parentid<>0")
}
以上为手写可能有误。
{
string delsql = "delete from tb_info where ID="+ParentId.ToString()+"";
string delsql2 = "delete from tb_info where ParentId not in(select ID from tb_info ) and ParentID<>0"; int rowcount = DataAccess.ExeCmd(delsql);
while(rowcount != 0)
{
DataAccess.ExeCmd(delsql2);
}
}