usage:
List<KeyValuePair<string, object>> sqlArg = new List<KeyValuePair<string, object>>();
sqlArg.Add(new KeyValuePair<string, object>("@XBIG5CODE", textBox2.Text));
sqlArg.Add(new KeyValuePair<string, object>("@XBYTES", s1));
sqlArg.Add(new KeyValuePair<string, object>("@XUNICODE", textBox3.Text));
sqlArg.Add(new KeyValuePair<string, object>("@XWORD", textBox1.Text));
sqlArg.Add(new KeyValuePair<string, object>("@XIMAGE", imageData));
_dbHelper.ExecuteNonQuery(_dbHelper.Connstr, CommandType.Text, DEVICEFONT_SQL_UPD, "", sqlArg);
base code:
/// <summary>
/// 利用cmdParms轉成SqlParameter來做新增,可以用於新增Image
/// </summary>
/// <param name="connString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cacheKey"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, string cacheKey, List<KeyValuePair<string, object>> cmdParms)
{
lock (_ThisLock)
{
SqlParameter[] cmdSParms = null;
if (cmdParms != null)
{
string pname = "";
object pvalue = "";
int inx = 0;
cmdSParms = new SqlParameter[cmdParms.Count];
for (int i = 0; i < cmdParms.Count; i++)
{
//inx = strdParms[i].IndexOf('=');
pname = cmdParms[i].Key;
pvalue = cmdParms[i].Value;
cmdSParms[i] = new SqlParameter(pname, pvalue);
}
}
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdSParms);
int val = 0;
try
{
val = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
CommLibrary.cWriteLog cw = new CommLibrary.cWriteLog();
cw.WriteLog("CMSSQL", ex.Message);
}
cmd.Parameters.Clear();
cmd.Dispose();
return val;
}
}
}
/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">SqlCommand object</param>
/// <param name="conn">SqlConnection object</param>
/// <param name="trans">SqlTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">SqlParameters to use in the command</param>
private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
lock (_ThisLock)
{
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);
}
}
}