执行多条带参数SQL语句的问题（HASHTABLE）

[编程有钱人了]

调用的类
SqlServerHelper.cs

 /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            trans.Commit();
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
  public static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, 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 = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {


                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

使用

    protected void Button1_Click(object sender, EventArgs e)
    {
        Hashtable hs = new Hashtable();
        string sslq = "delete  from testtab where id=@id";
        SqlParameter[] para={new SqlParameter("@id",SqlDbType.Int,4)};
        for (int i = 1; i < 10;i++ )
        { 
            para[0].Value=i;
            hs.Add(sslq,para);
        }
        RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);
    }

如果FOR里有一条数据那这个方法成功，多余一就出现错误
说参数不对 我以前都用ILIST<string>
现在想用SqlParameter[]了却不知道怎么用 了，以前还没用过

高手过招！

[ojekleen]

乱七八糟的了。。

for (int i = 1; i < 10;i++ )
{
para[i]=new SqlPararmenter();//这个好像要加上，
para[i].Value=i;
//hs.Add(sslq,para);//hashcode不能key相同
hs.add(i,para);
}


像你这样的没只要写成"

protected void Button1_Click(object sender, EventArgs e)
{
Hashtable hs = new Hashtable();
string sslq = "delete from testtab where id=@id";
SqlParameter[] para=new SqlParameter[10];//{new SqlParameter("@id",SqlDbType.Int,4)};
hs.add(sslq,para);
RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);
}

[Garnett_KG]

假设sql语句含有多个参数的话,要先定义SqlParameter[]的个数，然後再给值.
EX:

            Hashtable hs = new Hashtable();
            string sslq = "delete  from testtab where id=@p1 and name=@p2 ";
            SqlParameter[] para = new SqlParameter[2];
            for (int i = 1; i < 2; i++)
            {
               para[i]= new SqlParameter("@p"+i.ToString(), SqlDbType.Int, 4);
               para[i].Value = i;
            }
            hs.Add(sslq, para);

            RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);

另外

                   SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            trans.Commit();  //放在这里提交事务有问题.要放在循环体外面.
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }

[ojekleen]

//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();//你这当做KeyValuePair用了
//SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;//这段代码好像有问题
SqlParameter[] cmdParms =(SqlParameter[])myDE[0].Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();

trans.Commit();
}

你可以不用hash,用keyValuePair<T,T>就可以了。

[编程有钱人了]

[Quote=引用 3 楼 ojekleen 的回复:]
C# codeprotectedvoidButton1_Click(objectsender, EventArgs e)
{
Hashtable hs=newHashtable();stringsslq="delete from testtab where id=@id";
SqlParameter[] para=newSqlParameter[10];//{new SqlParameter("@id",SqlDbType.Int,4)};for(inti=1; i<10;i++)
{
para[i].Value=i;//hs.Add(sslq,para);//hashcode不能key相同hs.add(i,para);
}
RedGlove.DBUtility.…
[/Quote]
汗 我要执行的SQL语句呢
你这样它怎么知道我要的动作是“删除”？

HASHTBALE集合应为
key:delete from testtab where id=@id value:1 //1赋给@id
key:delete from testtab where id=@id value:2 //1赋给@id
key:delete from testtab where id=@id value:3 //1赋给@id
key:delete from testtab where id=@id value:4 //1赋给@id

[ojekleen]

  protected void Button1_Click(object sender, EventArgs e)
    {
        Hashtable hs = new Hashtable();
               
        for (int i = 1; i < 10;i++ )
        {  
string sslq = "delete  from testtab where id=@id"+i;

SqlParameter[] para={new SqlParameter("@id",SqlDbType.Int,4)};
            para[0].Value=i;
            hs.Add(sslq,para);
        }
        RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);
    }

[ojekleen]

[Quote=引用 7 楼 wangjun8868 的回复:]
引用 3 楼 ojekleen 的回复:
C# codeprotectedvoidButton1_Click(objectsender, EventArgs e)
{
Hashtable hs=newHashtable();stringsslq="delete from testtab where id=@id";
SqlParameter[] para=newSqlParameter[10];//{new SqlParameter("@id",SqlDbType.Int,4)};for(inti=1; i <10;i++)
{
para[i].Value=i;//hs.Add(sslq,para);//hashcode不能key相同hs.add(i,para);…
[/Quote]

你封装的方法是对的，思维比较混乱。。。你只是在添加hashcode的时候key相同，你只要改下key值就可以了。

[ojekleen]

你调用的方法确保KEY值不同，

protected void Button1_Click(object sender, EventArgs e)
    {
        Hashtable hs = new Hashtable();
               
        for (int i = 1; i < 10;i++ )
        {  
string sslq = "delete  from testtab where id=@id"+i;

SqlParameter[] para={new SqlParameter("@id"+i,SqlDbType.Int,4)};
            para[0].Value=i;
            hs.Add(sslq,para);
        }
        RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);
    }

[shenlong0125]

up

[阿非]

[Quote=引用楼主 wangjun8868 的帖子:]
调用的类
SqlServerHelper.cs

C# code
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
…
[/Quote]
Hashtable key 是唯一的，不能重复

[阿非]

hs.Add(sslq+"|"+i.ToString(),para);

string cmdText = myDE.Key.ToString().Split('|')[0];

[wwwiii520]

 protected void Button1_Click(object sender, EventArgs e)
    {
        Hashtable hs = new Hashtable();
        string sslq = "delete  from testtab where id=@id";
        SqlParameter[] para={new SqlParameter("@id",SqlDbType.Int,4)};
        for (int i = 1; i < 10;i++ )
        { 
            para[0].Value=i;
            hs.Add(sslq,para);
            RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);

        }
        ///////////RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);
    }

[wwwiii520]

protected void Button1_Click(object sender, EventArgs e)
    {
        Hashtable hs = new Hashtable();
        string sslq = "delete  from testtab where id=@id";
        SqlParameter[] para={new SqlParameter("@id",SqlDbType.Int,4)};
        for (int i = 1; i < 10;i++ )
        { 
            para[0].Value=i;
            hs.Add(sslq,para);
            RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);
        }
        ///////////RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);
    }

這樣就對了撒.參數刪除一次只能操作一條的

[编程有钱人了]

[Quote=引用 13 楼 Sandy945 的回复:]
C# codehs.Add(sslq+"|"+i.ToString(),para);

C# codestringcmdText=myDE.Key.ToString().Split('|')[0];
[/Quote]
很好
就是系统提示
此 SqlTransaction 已完成；它再也无法使用。

[编程有钱人了]

[Quote=引用 15 楼 wwwiii520 的回复:]
C# codeprotectedvoidButton1_Click(objectsender, EventArgs e)
{
Hashtable hs=newHashtable();stringsslq="delete from testtab where id=@id";
SqlParameter[] para={newSqlParameter("@id",SqlDbType.Int,4)};for(inti=1; i<10;i++)
{
para[0].Value=i;
hs.Add(sslq,para);
RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);
}/////////…
[/Quote]
你这样还要HASHTANLE有什么用
这好似用for删的

不是调用的那个方法

[codingtaoist]

或者 增加点冗余数据：

 protected void Button1_Click2(object sender, EventArgs e)
    {
        Hashtable hs = new Hashtable();
        string sslq = "delete  from testtab where id=@id --";
        for (int i = 1; i < 10; i++)
        {
            SqlParameter[] para ={ new SqlParameter("@id", SqlDbType.Int, 4) };
            para[0].Value = i;
            hs.Add(sslq+Guid.NewGuid().ToString(), para);
        }
        RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);
    }

或者id为非输入，无sql注入,不用SqlParameter[]

 protected void Button1_Click(object sender, EventArgs e)
    {
        Hashtable hs = new Hashtable();
        string sslq = "delete  from testtab where id={0}";
        for (int i = 1; i < 10; i++)
        {
            string curSql = string.Format(sslq, i);
            hs.Add(curSql,null);
        }
        RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);
    }

或者一起删 ：delete from testtab where id in (.....)

[wwwiii520]

[Quote=引用 17 楼 wangjun8868 的回复:]
引用 15 楼 wwwiii520 的回复:
C# codeprotectedvoidButton1_Click(objectsender, EventArgs e)
{
Hashtable hs=newHashtable();stringsslq="delete from testtab where id=@id";
SqlParameter[] para={newSqlParameter("@id",SqlDbType.Int,4)};for(inti=1; i <10;i++)
{
para[0].Value=i;
hs.Add(sslq,para);
RedGlove.DBUtility.SqlServerH…
[/Quote]

嘿嘿...也是的.
HASHTANLE我沒用過. 那這次學習了.
看上面的高手回的貼子. 意思就是HASHTANLE的list 的key要是唯一. 想辦法讓他唯一

[wwwiii520]

string sslq = "delete from testtab where id={0}";
string curSql = string.Format(sslq, i);

這樣使很好也.

[编程有钱人了]

哈哈搞定了
谢谢诶大家
我在这贴出正确代码

 protected void Button1_Click(object sender, EventArgs e)
    {
        Hashtable hs = new Hashtable();
        string sslq = "delete  from testtab where id=@id";
        SqlParameter[] para =new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
        for (int i = 5; i < 10;i++ )
        {   
            para[0].Value = i;
            hs.Add(sslq+"|"+i.ToString(), para);

        }
        RedGlove.DBUtility.SqlServerHelper.ExecuteSqlTran(hs);
    }




  /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString().Split('|')[0];//拆解前台加工那个的SQL语句
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();//这个要放在FOR外面
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

==揭帖 哈哈我很高兴

[阿非]

[Quote=引用 16 楼 wangjun8868 的回复:]
引用 13 楼 Sandy945 的回复:
C# codehs.Add(sslq+"|"+i.ToString(),para);

C# codestringcmdText=myDE.Key.ToString().Split('|')[0];

很好
就是系统提示
此 SqlTransaction 已完成；它再也无法使用。
[/Quote]

foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
  trans.Commit();

[阿非]

[Quote=引用 21 楼 wangjun8868 的回复:]
哈哈搞定了
谢谢诶大家
我在这贴出正确代码

C# code
protected void Button1_Click(object sender, EventArgs e)
{
Hashtable hs = new Hashtable();
string sslq = "delete from testtab where id=@id";
SqlParameter[] para =new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
for (int i = 5; i < 10;i++ )
{
para[0].Value…
[/Quote]

trans.Commit();//这个要放在FOR外面

恩，你不能每次都用一个事务去Commit，一个事务只能Commit 一次