C# 数据库访问通用类 (ADO.NET)
SqlDbHelper.cs
view source print?
001using System; 002using System.Collections.Generic; 003using System.Text; 004using System.Data; 005using System.Data.SqlClient; 006using System.Configuration; 007??008namespace ADODoNETDemo 009{ 010????/// <summary> 011????/// 针对SQL Server数据库操作的通用类 012????/// 作者：周公 013????/// 日期：2009-01-08 014????/// Version:1.0 015????/// </summary> 016????public class SqlDbHelper 017????{ 018????????private string connectionString; 019????????/// <summary> 020????????/// 设置数据库连接字符串 021????????/// </summary> 022????????public string ConnectionString 023????????{ 024????????????set { connectionString = value; } 025????????} 026????????/// <summary> 027????????/// 构造函数 028????????/// </summary> 029????????public SqlDbHelper() 030????????????: this(ConfigurationManager.ConnectionStrings["Conn"].ConnectionString) 031????????{ 032??033????????} 034????????/// <summary> 035????????/// 构造函数 036????????/// </summary> 037????????/// <param name="connectionString">数据库连接字符串</param> 038????????public SqlDbHelper(string connectionString) 039????????{ 040????????????this.connectionString = connectionString; 041????????} 042????????/// <summary> 043????????/// 执行一个查询，并返回结果集 044????????/// </summary> 045????????/// <param name="sql">要执行的查询SQL文本命令</param> 046????????/// <returns>返回查询结果集</returns> 047????????public DataTable ExecuteDataTable(string sql) 048????????{ 049????????????return ExecuteDataTable(sql, CommandType.Text, null); 050????????} 051????????/// <summary> 052????????/// 执行一个查询,并返回查询结果 053????????/// </summary> 054????????/// <param name="sql">要执行的SQL语句</param> 055????????/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 056????????/// <returns>返回查询结果集</returns> 057????????public DataTable ExecuteDataTable(string sql, CommandType commandType) 058????????{ 059????????????return ExecuteDataTable(sql, commandType, null); 060????????} 061????????/// <summary> 062????????/// 执行一个查询,并返回查询结果 063????????/// </summary> 064????????/// <param name="sql">要执行的SQL语句</param> 065????????/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 066????????/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param> 067????????/// <returns></returns> 068????????public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters) 069????????{ 070????????????DataTable data = new DataTable();//实例化DataTable，用于装载查询结果集 071????????????using (SqlConnection connection = new SqlConnection(connectionString)) 072????????????{ 073????????????????using (SqlCommand command = new SqlCommand(sql, connection)) 074????????????????{ 075????????????????????command.CommandType = commandType;//设置command的CommandType为指定的CommandType 076????????????????????//如果同时传入了参数，则添加这些参数 077????????????????????if (parameters != null) 078????????????????????{ 079????????????????????????foreach (SqlParameter parameter in parameters) 080????????????????????????{ 081????????????????????????????command.Parameters.Add(parameter); 082????????????????????????} 083????????????????????} 084????????????????????//通过包含查询SQL的SqlCommand实例来实例化SqlDataAdapter 085????????????????????SqlDataAdapter adapter = new SqlDataAdapter(command); 086??087????????????????????adapter.Fill(data);//填充DataTable 088????????????????} 089????????????} 090????????????return data; 091????????} 092????????/// <summary> 093????????///? 094????????/// </summary> 095????????/// <param name="sql">要执行的查询SQL文本命令</param> 096????????/// <returns></returns> 097????????public SqlDataReader ExecuteReader(string sql) 098????????{ 099????????????return ExecuteReader(sql, CommandType.Text, null); 100????????} 101????????/// <summary> 102????????///? 103????????/// </summary> 104????????/// <param name="sql">要执行的SQL语句</param> 105????????/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 106????????/// <returns></returns> 107????????public SqlDataReader ExecuteReader(string sql, CommandType commandType) 108????????{ 109????????????return ExecuteReader(sql, commandType, null); 110????????} 111????????/// <summary> 112????????///? 113????????/// </summary> 114????????/// <param name="sql">要执行的SQL语句</param> 115????????/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 116????????/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param> 117????????/// <returns></returns> 118????????public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters) 119????????{ 120????????????SqlConnection connection = new SqlConnection(connectionString); 121????????????SqlCommand command = new SqlCommand(sql, connection); 122????????????//如果同时传入了参数，则添加这些参数 123????????????if (parameters != null) 124????????????{ 125????????????????foreach (SqlParameter parameter in parameters) 126????????????????{ 127????????????????????command.Parameters.Add(parameter); 128????????????????} 129????????????} 130????????????connection.Open(); 131????????????//CommandBehavior.CloseConnection参数指示关闭Reader对象时关闭与其关联的Connection对象 132????????????return command.ExecuteReader(CommandBehavior.CloseConnection); 133????????} 134????????/// <summary> 135????????///? 136????????/// </summary> 137????????/// <param name="sql">要执行的查询SQL文本命令</param> 138????????/// <returns></returns> 139????????public Object ExecuteScalar(string sql) 140????????{ 141????????????return ExecuteScalar(sql, CommandType.Text, null); 142????????} 143????????/// <summary> 144????????///? 145????????/// </summary> 146????????/// <param name="sql">要执行的SQL语句</param> 147????????/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 148????????/// <returns></returns> 149????????public Object ExecuteScalar(string sql, CommandType commandType) 150????????{ 151????????????return ExecuteScalar(sql, commandType, null); 152????????} 153????????/// <summary> 154????????///? 155????????/// </summary> 156????????/// <param name="sql">要执行的SQL语句</param> 157????????/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 158????????/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param> 159????????/// <returns></returns> 160????????public Object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters) 161????????{ 162????????????object result = null; 163????????????using (SqlConnection connection = new SqlConnection(connectionString)) 164????????????{ 165????????????????using (SqlCommand command = new SqlCommand(sql, connection)) 166????????????????{ 167????????????????????command.CommandType = commandType;//设置command的CommandType为指定的CommandType 168????????????????????//如果同时传入了参数，则添加这些参数 169????????????????????if (parameters != null) 170????????????????????{ 171????????????????????????foreach (SqlParameter parameter in parameters) 172????????????????????????{ 173????????????????????????????command.Parameters.Add(parameter); 174????????????????????????} 175????????????????????} 176????????????????????connection.Open();//打开数据库连接 177????????????????????result = command.ExecuteScalar(); 178????????????????} 179????????????} 180????????????return result;//返回查询结果的第一行第一列，忽略其它行和列 181????????} 182????????/// <summary> 183????????/// 对数据库执行增删改操作 184????????/// </summary> 185????????/// <param name="sql">要执行的查询SQL文本命令</param> 186????????/// <returns></returns> 187????????public int ExecuteNonQuery(string sql) 188????????{ 189????????????return ExecuteNonQuery(sql, CommandType.Text, null); 190????????} 191????????/// <summary> 192????????/// 对数据库执行增删改操作 193????????/// </summary> 194????????/// <param name="sql">要执行的SQL语句</param> 195????????/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 196????????/// <returns></returns> 197????????public int ExecuteNonQuery(string sql, CommandType commandType) 198????????{ 199????????????return ExecuteNonQuery(sql, commandType, null); 200????????} 201????????/// <summary> 202????????/// 对数据库执行增删改操作 203????????/// </summary> 204????????/// <param name="sql">要执行的SQL语句</param> 205????????/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 206????????/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param> 207????????/// <returns></returns> 208????????public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters) 209????????{ 210????????????int count = 0; 211????????????using (SqlConnection connection = new SqlConnection(connectionString)) 212????????????{ 213????????????????using (SqlCommand command = new SqlCommand(sql, connection)) 214????????????????{ 215????????????????????command.CommandType = commandType;//设置command的CommandType为指定的CommandType 216????????????????????//如果同时传入了参数，则添加这些参数 217????????????????????if (parameters != null) 218????????????????????{ 219????????????????????????foreach (SqlParameter parameter in parameters) 220????????????????????????{ 221????????????????????????????command.Parameters.Add(parameter); 222????????????????????????} 223????????????????????} 224????????????????????</