C# 数据库访问通用类 (ADO.NET)

SqlDbHelper.cs

view source

print?

001	using System;

002	using System.Collections.Generic;

003	using System.Text;

004	using System.Data;

005	using System.Data.SqlClient;

006	using System.Configuration;

007

008	namespace 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	connection.Open();//打开数据库连接

225	count = command.ExecuteNonQuery();

226

}

227

}

228	return count;//返回执行增删改操作之后，数据库中受影响的行数

229

}

230	/// <summary>

231	/// 返回当前连接的数据库中所有由用户创建的数据库

232	/// </summary>

233	/// <returns></returns>

234	public DataTable GetTables()

235

{

236	DataTable data = null;

237	using (SqlConnection connection = new SqlConnection(connectionString))

238

{

239	connection.Open();//打开数据库连接

240	data = connection.GetSchema("Tables");

241

}

242	return data;

243

}

244

245

}

246

}

[代码] ADODotNetCRUD.cs

view source

print?

001	using System;

002	using System.Collections.Generic;

003	using System.Linq;

004	using System.Text;

005	using System.Data.SqlClient;

006	using System.Data;

007

008	namespace ADODoNETDemo

009

{

010	/// <summary>

011	/// 用ADO.NET实现CRUD功能

012	/// </summary>

013	public class ADODotNetCRUD

014

{

015	/// <summary>

016	/// 统计用户总数

017	/// </summary>

018	/// <returns></returns>

019	public int Count()

020

{

021	string sql = "select count(1) from UserInfo";

022	SqlDbHelper db = new SqlDbHelper();

023	return int.Parse(db.ExecuteScalar(sql).ToString());

024

}

025	/// <summary>

026

/// 创建用户

027	/// </summary>

028	/// <param name="info">用户实体</param>

029	/// <returns></returns>

030	public bool Create(UserInfo info)

031

{

032	string sql = "insert UserInfo(UserName,RealName,Age,Sex,Mobile,Email,Phone)values(@UserName,@RealName,@Age,@Sex,@Mobile,@Email,@Phone)";

033	SqlParameter[] paramters = new SqlParameter[]{

034	new SqlParameter("@UserName",info.UserName),

035	new SqlParameter("@RealName",info.RealName),

036	new SqlParameter("@Age",info.Age),

037	new SqlParameter("@Sex",info.Sex),

038	new SqlParameter("@Mobile",info.Mobile),

039	new SqlParameter("@Email",info.Email),

040	new SqlParameter("@Phone",info.Phone),

041

};

042	SqlDbHelper db = new SqlDbHelper();

043	return db.ExecuteNonQuery(sql, CommandType.Text, paramters) > 0;

044

}

045	/// <summary>

046	/// 读取用户信息

047	/// </summary>

048	/// <param name="userId">用户编号</param>

049	/// <returns></returns>

050	public UserInfo Read(int userId)

051

{

052	string sql = "select * from UserInfo Where UserId="+userId;

053	SqlDbHelper db = new SqlDbHelper();

054	DataTable data = db.ExecuteDataTable(sql);

055	if (data.Rows.Count > 0)

056

{

057	DataRow row = data.Rows[0];

058	UserInfo info = new UserInfo()

059

{

060	UserId=int.Parse(row["UserId"].ToString()),

061	UserName=row["UserName"].ToString(),

062	Age=byte.Parse(row["Age"].ToString()),

063	Email=row["Email"].ToString(),

064	Mobile=row["Mobile"].ToString(),

065	Phone=row["Phone"].ToString(),

066	RealName=row["RealName"].ToString(),

067	Sex=bool.Parse(row["Sex"].ToString())

068

};

069	return info;

070

}

071

else

072

{

073	return null;

074

}

075

}

076	/// <summary>

077	/// 更新用户信息

078	/// </summary>

079	/// <param name="info">用户实体</param>

080	/// <returns></returns>

081	public bool Update(UserInfo info)

082

{

083	string sql = "update UserInfo set UserName=@UserName,RealName=@RealName,Age=@Age,Sex=@Sex,Mobile=@Mobile,Email=@Email,Phone=@Phone where UserID=@UserID";

084	SqlParameter[] paramters = new SqlParameter[]{

085	new SqlParameter("@UserName",info.UserName),

086	new SqlParameter("@RealName",info.RealName),

087	new SqlParameter("@Age",info.Age),

088	new SqlParameter("@Sex",info.Sex),

089	new SqlParameter("@Mobile",info.Mobile),

090	new SqlParameter("@Email",info.Email),

091	new SqlParameter("@Phone",info.Phone),

092	new SqlParameter("@UserID",info.UserId),

093

};

094	SqlDbHelper db = new SqlDbHelper();

095	return db.ExecuteNonQuery(sql, CommandType.Text, paramters) > 0;

096

}

097	/// <summary>

098

/// 删除用户

099	/// </summary>

100	/// <param name="userId">用户编号</param>

101	/// <returns></returns>

102	public bool Delete(int userId)

103

{

104	string sql = "delete from UserInfo where UserId=" + userId;

105	SqlDbHelper db = new SqlDbHelper();

106	return db.ExecuteNonQuery(sql) > 0;

107

}

108	/// <summary>

109	/// 获取用户表中编号最大的用户

110	/// </summary>

111	/// <returns></returns>

112	public int GetMaxUserId()

113

{

114	string sql = "select max(userId) from UserInfo";

115	SqlDbHelper db = new SqlDbHelper();

116	return int.Parse(db.ExecuteScalar(sql).ToString());

117

}

118

}

119

}

[代码] 单元测试 ADODotNetTest.cs

view source

print?

01	using System;

02	using System.Collections.Generic;

03	using System.Linq;

04	using System.Text;

05	using ADODoNETDemo;

06	using NUnit.Framework;

07

08	namespace NUnitTest

09

{

10	[TestFixture]

11	class ADODotNetTest

12

{

13	private ADODotNetCRUD instance = null;

14

[SetUp]

15	public void Initialize()

16

{

17	instance = new ADODotNetCRUD();

18

}

19

[Test]

20	/// <summary>

21	/// 统计用户总数

22	/// </summary>

23	/// <returns></returns>

24	public void Count()

25

{

26	Assert.Greater(instance.Count(), 0);

27

}

28

[Test]

29	/// <summary>

30

/// 创建用户

31	/// </summary>

32	/// <param name="info">用户实体</param>

33	/// <returns></returns>

34	public void Create()

35

{

36	UserInfo info = new UserInfo()

37

{

38

Age = 12,

39	Email = "zzz@ccav.com",

40	Mobile = "13812345678",

41	Phone = "01012345678",

42	RealName = "测试" + DateTime.Now.Millisecond.ToString(),

43	Sex = true,

44	UserName = "zhoufoxcn" + DateTime.Now.Millisecond.ToString()

45

};

46	instance.Create(info);

47

}

48

[Test]

49	/// <summary>

50	/// 读取用户信息

51	/// </summary>

52	/// <param name="userId">用户编号</param>

53	/// <returns></returns>

54	public void Read()

55

{

56	UserInfo info = instance.Read(1);

57	Assert.NotNull(info);

58

}

59

[Test]

60	/// <summary>

61	/// 更新用户信息

62	/// </summary>

63	/// <param name="info">用户实体</param>

64	/// <returns></returns>

65	public void Update()

66

{

67	UserInfo info = instance.Read(1);

68	info.RealName = "测试" + DateTime.Now.Millisecond.ToString();

69	instance.Update(info);

70

}

71

[Test]

72	/// <summary>

73

/// 删除用户

74	/// </summary>

75	/// <param name="userId">用户编号</param>

76	/// <returns></returns>

77	public void DeleteByID()

78

{

79	int userId = instance.GetMaxUserId();

80	instance.Delete(userId);

81

}

82

}

83

}