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

SqlDbHelper.cs

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

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

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
}