Asp.net C# DBHelper类

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();

}

}

}