C# 构筑访问数据库的处理(语句 & 存储过程)

1.简单拼接方式

string strSQL = "exec sp_StepTwo_UpdateLaborCostAndOutlay "
+this.CenterCD+","
+this.CalculationDate+","
+"'"+this.dsExcelData.Tables[0].Rows[i][0]+"'"+","
+this.dsExcelData.Tables[0].Rows[i][1]+","
+this.dsExcelData.Tables[0].Rows[i][2]+","
+this.dsExcelData.Tables[0].Rows[i][3]+","
+this.dsExcelData.Tables[0].Rows[i][4]+","
+this.dsExcelData.Tables[0].Rows[i][5]+","
+this.LoginCD;

2.利用StringBuilder拼接字符串

StringBuilder sql = new StringBuilder();
sql.Append("update Books  set [Title]=@title");
sql.Append(",[Author]=@author,[PublisherId]=@publisherId");
sql.Append(",[PublishDate]=@publishDate,[ISBN]=@iSBN,[WordsCount]=@wordsCount");
sql.Append(",[UnitPrice]=@unitPrice,[ContentDescription]=@ContentDes,[AurhorDescription]=@autorDes");
sql.Append(",[EditorComment]=@editComm,[TOC]=@tOC,[CategoryId]=@cateId,[Clicks]=@clicks ");
sql.Append (" where  Id=@bookId");

3. 占位符替换方式

string strSql=string.Format(@"sp_StepSix_GetMaterialGroupInformation {0},'{1}','{2}',{3},'{4}','{5}',{6}"
,this.lblCenterCD.Text
,this.cboLineName.Text
,this.txtMaterialGroupCD.Text
,this.lblProductID.Text
,this.lblProductCD.Text
,this.lblStatus.Text
,this.LoginCD);

4.StringBuilder 拼接sql 使用 ArrayList传入参数

StringBuilder sb = New StringBuilder();
ArrayList   arry = New ArryList();
String str ="";
sb.Append("insert into table(termNo,teacherName) values ( '{0} ', '{1} ' )") ;
arry.Add(dpterm.SelectedItem.Text.ToString); 
arry.Add(txtName.Text.Trim());
strsql = String.Format(sb.ToString(), arry.ToArray());

5.SqlParameter

SqlParameter[] sp=
{
new SqlParameter("@in_CenterCD",SqlDbType.Int),
new SqlParameter("@in_MaterialGroupCD",SqlDbType.VarChar,13),
new SqlParameter("@in_LoginCD",SqlDbType.Int),
new SqlParameter("@out_TableName",SqlDbType.VarChar,200)
};

sp[0].Value=this.extCboCenterCD_New.Text.Trim().ToString(); 
sp[1].Value=this.lblMaterialGroupCD_New.Text.Trim().ToString(); 
sp[2].Value=ucFormLabel.sUserCD.ToString();
sp[3].Direction=ParameterDirection.Output;

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(sp);