public static class DBHelper
{
//数据库连接属性
private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
/// <summary>
/// 执行带参存储过程s
/// </summary>
public static void ExecProc(string strProName, SqlParameter[] para)
{
connection.Open();
try
{
SqlCommand sqlcmd = connection.CreateCommand();
sqlcmd.CommandText = strProName;
sqlcmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter paras in para)
{
sqlcmd.Parameters.Add(paras);
}
sqlcmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
/// <summary>
/// 根据sql语句执行非查询操作
/// </summary>
public static bool ExecNoQuery(string sql)
{
try
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 根据sql语句获得一个DataTable
/// </summary>
public static DataTable ExecQuery(string Sql)
{
connection.Open();
SqlDataAdapter da = new SqlDataAdapter(Sql, connection);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
connection.Close();
}
/// <summary>
/// 执行无参SQL语句
/// </summary>
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
/// <summary>
/// 执行带参SQL语句
/// </summary>
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 执行无参SQL语句，并返回执行记录数
/// </summary>
public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
/// <summary>
/// 执行有参SQL语句，并返回执行记录数
/// </summary>
public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
/// <summary>
/// 执行无参SQL语句，并返回SqlDataReader
/// </summary>
public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
/// <summary>
/// 执行有参SQL语句，并返回SqlDataReader
/// </summary>
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// 根据sql语句获得一个单值字符串
/// </summary>
public static string ReturnStringScalar(string sql)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
try
{
string result = cmd.ExecuteScalar().ToString();
return result;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return "0";
}
connection.Close();
}
/// <summary>
/// 执行事务处理
/// </summary>
public static bool ExecTSQL(string[] sqls)
{
connection.Open();
SqlTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
for (int i = 0; i < sqls.Length; i++)
{
if (sqls[i] == "" || sqls[i] == null)
{
continue;
}
SqlCommand cmd = connection.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = sqls[i];
cmd.ExecuteNonQuery();
}
trans.Commit();
return true;
}
catch
{
trans.Rollback();
return false;
}
finally
{
trans = null;
connection.Close();
}
}
}