/// <summary>
///执行一个不需要返回值的SqlCommand命令，通过指定专用的连接字符串。
/// 使用参数数组形式提供参数列表
/// </summary>
/// <remarks>
/// 使用示例：
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">一个有效的数据库连接字符串</param>
/// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
/// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
iRedoTimes++;
Mylog.Info(cmdText);
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
//通过PrePareCommand方法将参数逐个加入到SqlCommand的参数集合中
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
//清空SqlCommand中的参数列表
cmd.Parameters.Clear();
return(val);
}
}
catch (Exception ex)
{
Mylog.Error(ex.Message);
if (ex.ToString().Contains("死锁") || ex.ToString().Contains("无法访问") || ex.ToString().Contains("超时") || ex.ToString().Contains("传输级"))
{
if (iRedoTimes % 10 != 0)
{
return(ExecuteNonQuery(connectionString, cmdType, cmdText, commandParameters));
}
// MessageBox.Show("网络异常，点击确定后系统自动重试。如果此异常长时间没有解除，请检查设备或者联系13164846310。\r\b" + ex.ToString().Substring(0, 200));
}
else
{
Mylog.Error(ex.ToString());
}
return(0);
}
}
/// <summary>
/// 执行事务
/// </summary>
/// <param name="connectionString">一个有效的数据库连接字符串</param>
/// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
/// <returns>return a bool</returns>
public static bool ExecuteSqlTran(string connectionString, string strSQL)
{
char[] de = { ';' };
string strChildSQL;
int i;
string[] strSQLArr = strSQL.Split(de);
bool IsControll = true;
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand();
SqlTransaction myTrans = null;
Mylog.Info(strSQL);
try
{
conn.Open();
myTrans = conn.BeginTransaction();
myCommand.Connection = conn;
myCommand.Transaction = myTrans;
for (i = 0; i < strSQLArr.Length; i++)
{
strChildSQL = strSQLArr[i];
myCommand.CommandText = strChildSQL;
myCommand.ExecuteNonQuery();
}
myTrans.Commit();
IsControll = true;
}
catch (Exception ex)
{
Mylog.Error(ex.Message);
try
{
IsControll = false;
if (myTrans != null)
{
myTrans.Rollback();
}
//ExecuteSqlTran(connectionString, strSQL);
}
catch (SqlException sex)
{
Mylog.Error(sex.Message);
}
}
finally
{
conn.Close();
}
return(IsControll);
}
/// <summary>
/// return a dataset
/// </summary>
/// <param name="connectionString">一个有效的数据库连接字符串</param>
/// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
/// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>return a dataset</returns>
public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
Mylog.Info(cmdText);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;
da.Fill(ds);
return(ds);
}
catch (Exception ex)
{
Mylog.Error(ex.Message);
if (ex.ToString().Contains("死锁") || ex.ToString().Contains("无法访问") || ex.ToString().Contains("超时") || ex.ToString().Contains("传输级"))
{
if (iRedoTimes % 10 != 0)
{
return(ExecuteDataSet(connectionString, cmdType, cmdText, commandParameters));
}
// MessageBox.Show("网络异常，点击确定后系统自动重试。如果此异常长时间没有解除，请检查设备或者联系13164846310。\r\b" + ex.ToString().Substring(0, 200));
}
else
{
Mylog.Error(ex.ToString());
}
return(null);
}
finally
{
conn.Close();
}
}
