/// <summary>
/// 执行sql语句并返回受影响的行数
/// </summary>
/// <param name="sql">要查询的语句</param>
/// <param name="paraList">变量列表Sqlparameters(可选)</param>
/// <param name="commType">要查询语句的类型</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string sql, List <SqlParameter> paraList = null, CommandType commType = CommandType.Text)
{
int value = -1;
try
{
SqlConnection conn = new SqlConnection(GuGuGuHelper.GetConnectionString());
conn.Open();
SqlCommand comm = new SqlCommand()
{
Connection = conn,
CommandType = commType,
CommandText = sql,
CommandTimeout = 600,
};
if (paraList != null)
{
foreach (var para in paraList)
{
comm.Parameters.Add(para);
}
}
return(comm.ExecuteNonQuery());
}
catch (Exception)
{
throw;
value = -1;
}
return(value);
}
/// <summary>
/// 离线查询数据DataTable(返回表)
/// </summary>
/// <param name="sql">要查询的语句</param>
/// <param name="paraList">变量列表Sqlparameters(可选)</param>
/// <param name="commType">要查询语句的类型</param>
/// <returns>返回查询得到的DataTable</returns>
public static DataTable ExecuteReaderDataTable(string sql, List <SqlParameter> paraList = null, CommandType commType = CommandType.Text)
{
try
{
SqlConnection conn = new SqlConnection(GuGuGuHelper.GetConnectionString());
SqlCommand comm = new SqlCommand
{
CommandText = sql,
CommandType = commType,
Connection = conn,
CommandTimeout = 600,
};
if (paraList != null)
{
foreach (var para in paraList)
{
comm.Parameters.Add(para);
}
}
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(comm);
DataTable table = new DataTable();
sqlDataAdapter.Fill(table);
return(table);
}
catch (Exception)
{
//MessageBox.Show(ex.ToString());
throw;
}
}
/// <summary>
/// 事务方法
/// </summary>
/// <param name="sqlList">命令集合</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQueryTransaction(List <string> sqlList)
{
SqlConnection conn = new SqlConnection();
SqlTransaction trans = null; // 定义事务
int cnt = 0;
try
{
conn.ConnectionString = GuGuGuHelper.GetConnectionString();
conn.Open();
trans = conn.BeginTransaction(); // 创建事务
foreach (string sql in sqlList)
{
SqlCommand comm = conn.CreateCommand();
comm.CommandText = sql;
comm.Transaction = trans; //命令事务对象
cnt += comm.ExecuteNonQuery();
}
trans.Commit(); // 提交事务
return(cnt);
}
catch (Exception)
{
trans.Rollback();// 回滚事务
return(-1);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
/// <summary>
/// 测试数据库连接
/// </summary>
/// <returns>返回状态码</returns>
public static int ConnectionTest()
{
int value = 100;
try
{
SqlConnection conn = new SqlConnection(GuGuGuHelper.GetConnectionString());
conn.Open();
if (conn.State == ConnectionState.Open)
{
conn.Close();
value = 0;
}
}
catch (Exception)
{
value = -1;
}
return(value);
}
/// <summary>
/// 汇总查询
/// </summary>
/// <param name="sql">要查询的语句</param>
/// <param name="paraList">变量列表Sqlparameters(可选)</param>
/// <param name="commandType">要查询语句的类型</param>
/// <returns>返回第一行的第一个字段</returns>
public static object ExecuteScalar(string sql, List <SqlParameter> paraList = null, CommandType commandType = CommandType.Text)
{
object cnt;
SqlConnection conn = new SqlConnection();
try
{
conn.ConnectionString = GuGuGuHelper.GetConnectionString();
conn.Open();
SqlCommand comm = new SqlCommand()
{
Connection = conn,
CommandText = sql,
CommandTimeout = 600,
CommandType = commandType,
};
if (paraList != null)
{
foreach (SqlParameter para in paraList)
{
comm.Parameters.Add(para);
}
}
cnt = comm.ExecuteScalar();
}
catch (Exception)
{
return(-1);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(cnt);
}