谢谢LS的各位的回答,我想要得效果是,根据用户选择的行,获取id(主键),然后再用sql语句根据ID来删除对应的数据,如果一个ID一个ID的删,数据库不是要多次执行删除操作吗,所以我想放在一个数组中用类似这样的语句delete from xxtable where id in (@IDS);来删除,一次搞定,有办法没?明早结贴给分,再次谢谢各位~
如果为了防止sql注入,分成一条一条的删除语句,ID通过参数传入 将删除语句放到事务中
in 的效率不高 用 /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static bool ExecuteSqlTran(string SQLString, Dictionary<string, SqlParameter[]> mydic) { bool returnValue = false; using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { //循环 foreach (KeyValuePair<string, SqlParameter[]> myDE in mydic) { string cmdText = SQLString; SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } returnValue = true; trans.Commit(); } catch { returnValue = false; trans.Rollback(); throw; } } } return returnValue; } 例如 delete from xxtable where id =1 delete from xxtable where id =2 delete from xxtable where id =3
可以这样啊, string ids="12,14,15,16"; string sql=string.Format("delete from xxtable where id in ({0});",ids); 然后再执行sql就行了.
/// <summary> /// 方法名称:DeleteSelect /// 作者: /// 用途:批量删除 /// </summary> /// <param name="SelecedCataId"></param> /// <returns></returns> public static int DeleteSelect(string[] ArrayGRID) { string sqlStr = "delete from dbo.GR_JBXX where GRID in ("; for (int i = 0; i < ArrayGRID.Length; i++) { if (i == ArrayGRID.Length - 1) { sqlStr += ("'" + ArrayGRID[i] + "')"); break; } else { sqlStr += ("'" + ArrayGRID[i] + "',"); } } return DbHelperSQL.ExecuteSql(sqlStr); }
根据delete from xxtable where id in (@IDS); 可以判断LZ的@IDS是1001,1002,1003这样的一种格式 传参的时候: var para = new SqlParameter("@IDS", SqlDbType.String,20) para.Value = //你要传的值然后在SQL里面将@IDS转换成字符串,去掉两边的引号。 这样试试看
var para = new SqlParameter("@IDS", SqlDbType.Int,4)
para.Value = //你要传的值
谢谢,这样试可以传数组了,但在执行SQL的时候会报错说,无法从Int32[]转Int32
有办法解决没
(@IDS)明明是字符串类型,为什么写int类型,LZ你搞错了吧?
2.
ID是你程序控制的,不是用户输入的,不存在sql注入,你为什么要参数,自找麻烦?听我的,木有错滴!
你在用 spilt 分割一下 就可以了
SQL Server数据库不支持数组的。
在程序中做处理,变换一下格式将@IDS分成 'a','b','c'---之类的格式。如果一定要参数化的话则用动态SQL语句来执行。直接这样是不可以的。
将删除语句放到事务中
用 /// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static bool ExecuteSqlTran(string SQLString, Dictionary<string, SqlParameter[]> mydic)
{
bool returnValue = false;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
//循环
foreach (KeyValuePair<string, SqlParameter[]> myDE in mydic)
{
string cmdText = SQLString;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
returnValue = true;
trans.Commit();
}
catch
{
returnValue = false;
trans.Rollback();
throw;
}
}
}
return returnValue;
}
例如
delete from xxtable where id =1
delete from xxtable where id =2
delete from xxtable where id =3
string ids="12,14,15,16";
string sql=string.Format("delete from xxtable where id in ({0});",ids);
然后再执行sql就行了.
/// 方法名称:DeleteSelect
/// 作者:
/// 用途:批量删除
/// </summary>
/// <param name="SelecedCataId"></param>
/// <returns></returns>
public static int DeleteSelect(string[] ArrayGRID)
{
string sqlStr = "delete from dbo.GR_JBXX where GRID in (";
for (int i = 0; i < ArrayGRID.Length; i++)
{
if (i == ArrayGRID.Length - 1)
{
sqlStr += ("'" + ArrayGRID[i] + "')");
break;
}
else
{
sqlStr += ("'" + ArrayGRID[i] + "',");
}
}
return DbHelperSQL.ExecuteSql(sqlStr);
}
可以判断LZ的@IDS是1001,1002,1003这样的一种格式
传参的时候:
var para = new SqlParameter("@IDS", SqlDbType.String,20)
para.Value = //你要传的值然后在SQL里面将@IDS转换成字符串,去掉两边的引号。
这样试试看