110,533
社区成员
发帖
与我相关
我的任务
分享
public static int ExecuteNonQuery(string sql, SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(conn))
{
con.Open();
SqlCommand cmd = new SqlCommand();//sql, con
//创建一个事务
SqlTransaction myTran = con.BeginTransaction();
try
{
cmd.Connection = con;
cmd.CommandText = sql;
cmd.Transaction = myTran;
cmd.Parameters.AddRange(parameters);
int Count = cmd.ExecuteNonQuery();
myTran.Commit();//提交事务
SendLocal(cmd.ToString());//发送SQL语句给本地文件生成记录.此为特定方法
return Count;
}
catch (System.Exception ex)
{
myTran.Rollback();//回滚事务
return 0;
}
}
}
ublic static string ToSQL(SqlCommand cmd, string sqlQuery)
{
StringBuilder sbRetVal=new StringBuilder();
foreach (SqlParameter item in cmd.Parameters)
{
switch (item.DbType)
{
case DbType.String:
sbRetVal.AppendFormat("DECLARE {0} AS VARCHAR(255)", item.ParameterName);
sbRetVal.AppendLine();
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value);
sbRetVal.AppendLine();
break;
case DbType.DateTime:
sbRetVal.AppendFormat("DECLARE {0} AS DATETIME", item.ParameterName);
sbRetVal.AppendLine();
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value);
sbRetVal.AppendLine();
break;
case DbType.Guid:
sbRetVal.AppendFormat("DECLARE {0} AS UNIQUEIDENTIFIER", item.ParameterName);
sbRetVal.AppendLine();
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value);
sbRetVal.AppendLine();
break;
case DbType.Int32:
sbRetVal.AppendFormat("DECLARE {0} AS int", item.ParameterName);
sbRetVal.AppendLine();
sbRetVal.AppendFormat("SET {0} = {1}", item.ParameterName, item.Value);
sbRetVal.AppendLine();
break;
default:
sbRetVal.AppendFormat("DECLARE {0} AS int", item.ParameterName);
sbRetVal.AppendLine();
sbRetVal.AppendFormat("SET {0} = {1}", item.ParameterName, item.Value);
sbRetVal.AppendLine();
break;
}
}
sbRetVal.AppendLine("");
sbRetVal.AppendLine(cmd.CommandText);
string resultSQL = sbRetVal.ToString();
resultSQL = resultSQL.Replace("\r\n", " ");//去掉格式
return resultSQL;
}