淘先锋技术网

首页 1 2 3 4 5 6 7

每次新项目的时候,都要从头去找一遍数据库工具类。这里分享一个简单实用的C#的通用DbHelper工具类,支持数据连接池。

连接池配置

DbHelper类

public classDBHelper

{

private static string connectionString = ConfigurationManager.ConnectionStrings["dh_web"].ConnectionString;

//不带参数的执行命令

public static int ExecuteCommand(stringsafeSql)

{

using (SqlConnection connection = newSqlConnection(connectionString))

{

connection.Open();

SqlCommand cmd = newSqlCommand(safeSql, connection);

returncmd.ExecuteNonQuery();

}

}

//带参数的执行命令

public static int ExecuteCommand(string sql, paramsSqlParameter[] values)

{

using (SqlConnection connection = newSqlConnection(connectionString))

{

connection.Open();

SqlCommand cmd = newSqlCommand(sql, connection);

cmd.Parameters.AddRange(values);

returncmd.ExecuteNonQuery();

}

}

public static int GetScalar(stringsafeSql)

{

using (SqlConnection connection = newSqlConnection(connectionString))

{

connection.Open();

SqlCommand cmd = newSqlCommand(safeSql, connection);

returnConvert.ToInt32(cmd.ExecuteScalar());

}

}

public static int GetScalar(string sql, paramsSqlParameter[] values)

{

using (SqlConnection connection = newSqlConnection(connectionString))

{

connection.Open();

SqlCommand cmd = newSqlCommand(sql, connection);

cmd.Parameters.AddRange(values);

returnConvert.ToInt32(cmd.ExecuteScalar());

}

}

public static SqlDataReader GetReader(stringsafeSql)

{

SqlConnection connection = newSqlConnection(connectionString);

connection.Open();

SqlCommand cmd = newSqlCommand(safeSql, connection);

returncmd.ExecuteReader(CommandBehavior.CloseConnection);

}

public static SqlDataReader GetReader(string sql, paramsSqlParameter[] values)

{

SqlConnection connection = newSqlConnection(connectionString);

connection.Open();

SqlCommand cmd = newSqlCommand(sql, connection);

cmd.Parameters.AddRange(values);

returncmd.ExecuteReader(CommandBehavior.CloseConnection);

}

public static DataTable GetDataSet(stringsafeSql)

{

using (SqlConnection connection = newSqlConnection(connectionString))

{

DataSet ds = newDataSet();

SqlCommand cmd = newSqlCommand(safeSql, connection);

SqlDataAdapter da = newSqlDataAdapter(cmd);

da.Fill(ds);

return ds.Tables[0];

}

}

public static DataTable GetDataSet(string sql, paramsSqlParameter[] values)

{

using (SqlConnection connection = newSqlConnection(connectionString))

{

DataSet ds = newDataSet();

SqlCommand cmd = newSqlCommand(sql, connection);

cmd.Parameters.AddRange(values);

SqlDataAdapter da = newSqlDataAdapter(cmd);

da.Fill(ds);

return ds.Tables[0];

}

}

}

注意:

CommandBehavior.CloseConnection解决了流读取数据模式下,数据库连接不能有效关闭的情况.

当某个XXXDataReader对象在生成时使用了CommandBehavior.CloseConnection,那数据库连接将在XXXDataReader对象关闭时自动关闭.