多條sql帶參數怎麼寫？

[takako_mu]

遍歷N個datatable.把各欄的值插入DB.

以前習慣了拼sql:
begin
insert into db1 values('"+dt1.rows[i][0].tostring()+"','"+dt1.rows[i][1].tostring()+"');
insert into db2 values('"+dt2.rows[i][0].tostring()+"','"+dt2.rows[i][1].tostring()+"','"+dt2.rows[i][1].tostring()+"');
……
end

現在不想拼sql了，想把這些都用參數表示，不知道如何寫？
參數數量不定啊.

[tzs2304]

SqlParameter

[zhouing00]

顶

[takako_mu]

        public void Insert(BodyInfo myBodyInfo)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" INSERT INTO [GPSS].[dbo].[QUE_BODY]([QID],[BCODE],[BNAME],[BCATEGORY],[BTYPE],[BRULE1],[CREATEBY],[CREATEDATE],[UPDATEBY],[UPDATEDATE])\n");
            sb.Append(" VALUES(@qid,@bcode,@bname,@bcategory,@btype,@brule1,@createby,@createdate,@updateby,@updatedate\n");

            SqlParameter[] param = new SqlParameter[] { 
                        new SqlParameter("@qid", SqlDbType.NVarChar,8), 
                        new SqlParameter("@bcode",SqlDbType.NVarChar,2),
                        new SqlParameter("@bname",SqlDbType.NVarChar,255),
                        new SqlParameter("@bcategory",SqlDbType.NVarChar,1),
                        new SqlParameter("@btype",SqlDbType.NVarChar,1),
                        new SqlParameter("@brule1",SqlDbType.NVarChar,20),
                        new SqlParameter("@createby",SqlDbType.NVarChar,10),
                        new SqlParameter("@createdate",SqlDbType.DateTime),
                        new SqlParameter("@updateby",SqlDbType.NVarChar,10),
                        new SqlParameter("@updatedate",SqlDbType.DateTime),
                        };
            param[0].Value = myBodyInfo.QID == null ? DBNull.Value : (object)myBodyInfo.QID;
            param[1].Value = myBodyInfo.BCode == null ? DBNull.Value : (object)myBodyInfo.BCode;
            param[2].Value = myBodyInfo.BName == null ? DBNull.Value : (object)myBodyInfo.BName;
            param[3].Value = myBodyInfo.BCategory == null ? DBNull.Value : (object)myBodyInfo.BCategory;
            param[4].Value = myBodyInfo.BType == null ? DBNull.Value : (object)myBodyInfo.BType;
            param[5].Value = myBodyInfo.BRule1 == null ? DBNull.Value : (object)myBodyInfo.BRule1;
            param[6].Value = myBodyInfo.CreateBy == null ? DBNull.Value : (object)myBodyInfo.CreateBy;
            param[7].Value = myBodyInfo.CreateDate == null ? DBNull.Value : (object)myBodyInfo.CreateDate;
            param[8].Value = myBodyInfo.UpdateBy == null ? DBNull.Value : (object)myBodyInfo.UpdateBy;
            param[9].Value = myBodyInfo.UpdateDate == null ? DBNull.Value : (object)myBodyInfo.UpdateDate;
            string strSQL = sb.ToString();
            int i = SQLHelper.ExecuteNonQuery(strSQL, param);
        }
固定了參數的我會寫，可是那麼多參數怎麼寫啊？

[wiki14]

想了想，
存储过程？Parameters.AddWithValue？
还是拼接吧。

[路人乙e]

string sql = "insert into tb values('{0}','{1}')";
sql=string.Format(sql, "aa", "bb");

[wiki14]

楼主，该写的还是要写的，逃不到的。

[Dobzhansky]

匿名 sql 语句块中的参数也是可以跨越不同子句的
作为一个command 也是可以绑定 parameter 的.

[Dobzhansky]

参数名不同就可以啊

[takako_mu]

[Quote=引用 5 楼 sq_zhuyi 的回复:]
string sql = "insert into tb values('{0}','{1}')";
sql=string.Format(sql, "aa", "bb");
[/Quote]
這還是在拼啊，感覺是一樣的，還是不安全.

[aellonxie]

出来混迟早是要还的，还是小心点为上

[骑猪看海]

[Quote=引用 3 楼 takako_mu 的回复:]
C# codepublicvoid Insert(BodyInfo myBodyInfo)
{
StringBuilder sb=new StringBuilder();
sb.Append(" INSERT INTO [GPSS].[dbo].[QUE_BODY]([QID],[BCODE],[BNAME],[BCATEGORY],?-
[/Quote]

[qawszx]

//添加参数
if (paraName != null)
{
for (int i = 0; i < paraName.Length; i++)
{
System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter();
param.Direction = System.Data.ParameterDirection.Input;
param.ParameterName = paraName[i];
param.Value = paraValue[i];

cmd.Parameters.Add(param);
}
}

ps：

string[] paraName = new string[1];
string[] paraValue = new string[1];

paraName[0] = "@Para";
paraValue[0] = 值;

[mail_ricklee]

declare @XX nvarchar(300)
declare @YY nvarchar(300)
declare @ZZ nvarchar(300)
set @XX='xx'
set @yy='yy'
set @zz='zz'
begin
insert into db1 values(@XX,@YY);
insert into db2 values(@XX,@YY,@ZZ);
end

[wuyq11]

通过数组传递参数到操作类操作存储过程，可看看sqlhepler

[zhouzangood]

不会，帮你顶下

[z415353144]

public int SetDataBySqlWithTrans(string[] sqlList,SqlParameter[][] paList)
{
SqlConnection cn=new SqlConnection(strCn);
SqlCommand cmd = cn.CreateCommand();
cn.Open();
SqlTransaction ta = cn.BeginTransaction();
cmd.Transaction = ta;
int executeCount = 0;
try
{
for (int i = 0; i < sqlList.Length; i++)
{
for (int j = 0; j < paList[i].Length; j++)
{
cmd.Parameters.Add(paList[i][j]);
}
cmd.CommandText = sqlList[i];
executeCount+= cmd.ExecuteNonQuery();
}
ta.Commit();
}
catch
{
ta.Rollback();
executeCount = 0;
}
finally
{
cn.Close();
}
return executeCount;
}

[自由_]

学习

[qq18527575]

找到一个规律，还得用拼字符串连接到一起！

[alan0128]

还是习惯用存储过程了