C# ADO.NET 数据库访问的基础类

  1 using System;
  2 using System.Data;
  3 using System.Data.SqlClient;
  4 
  5 namespace SQLServerAccess
  6 {
  7     /// <summary>
  8     /// 数据库访问的基础类
  9     /// </summary>
 10     public class BaseAccess
 11     {
 12         /// <summary>
 13         /// 数据库连接对象
 14         /// </summary>
 15         protected SqlConnection innerDBConnection;
 16 
 17         /// <summary>
 18         /// 数据库命令对象
 19         /// </summary>
 20         protected SqlCommand innerDBCommand;
 21 
 22         /// <summary>
 23         /// 构造函数
 24         /// </summary>
 25         public BaseAccess(DbConnection connection)
 26         {
 27             innerDBConnection = connection.Connection;
 28             innerDBCommand = connection.Command;
 29         }
 30 
 31         /// <summary>
 32         /// 构造函数
 33         /// </summary>
 34         public BaseAccess(IDbConnection connection)
 35         {
 36             innerDBConnection = connection as SqlConnection;
 37             innerDBCommand = connection.CreateCommand() as SqlCommand;
 38         }
 39 
 40         /// <summary>
 41         /// 执行查询处理
 42         /// </summary>
 43         /// <param name="strSql">SQL语句</param>
 44         /// <returns>获得的数据</returns>
 45         protected DataSet Select(string strSql)
 46         {
 47             // 声明
 48             DataSet ds = null;
 49 
 50             try
 51             {
 52                 // 初始化
 53                 ds = new DataSet();
 54 
 55                 // 创建SQL命令对象
 56                 innerDBCommand.CommandText = strSql;
 57 
 58                 // SQL语句日志输出
 59                 //Log.WriteInfo("SQL:{0}", strSql);
 60 
 61                 // SQL执行
 62                 SqlDataAdapter objDAdp = new SqlDataAdapter(innerDBCommand);
 63                 objDAdp.Fill(ds);
 64 
 65                 // 返回采集的数据集
 66                 return ds;
 67             }
 68             catch (Exception ex)
 69             {
 70                 throw ex;
 71             }
 72         }
 73 
 74         /// <summary>
 75         /// 执行计数处理
 76         /// </summary>
 77         /// <param name="strSql">执行SQL</param>
 78         /// <returns>处理结果</returns>
 79         protected long Count(string strSql)
 80         {
 81             // 声明
 82             int lngCount = 0;
 83 
 84             try
 85             {
 86                 // 创建SQL命令对象
 87                 innerDBCommand.CommandText = strSql;
 88 
 89                 // SQL语句日志输出
 90                 //Log.WriteInfo("SQL:{0}", strSql);
 91 
 92                 // SQL执行
 93                 lngCount = int.Parse(innerDBCommand.ExecuteScalar().ToString());
 94 
 95                 return lngCount;
 96             }
 97             catch (Exception ex)
 98             {
 99                 throw ex;
100             }
101         }
102 
103         /// <summary>
104         /// 执行SQL，添加、更新、删除
105         /// </summary>
106         /// <param name="strSql">SQL语句</param>
107         protected long Execute(string strSql)
108         {
109             try
110             {
111                 // 创建SQL命令对象
112                 innerDBCommand.CommandText = strSql;
113 
114                 // SQL语句日志输出
115                 //Log.WriteInfo("SQL:{0}", strSql);
116 
117                 // SQL执行
118                 long lonCount = innerDBCommand.ExecuteNonQuery();
119 
120                 // 返回受影响的行数
121                 return lonCount;
122 
123             }
124             catch (Exception ex)
125             {
126                 throw ex;
127             }
128         }
129 
130         /// <summary>
131         /// 执行SQL，返回新增记录的自增ID
132         /// </summary>
133         /// <param name="strSql">SQL语句</param>
134         /// <returns></returns>
135         protected long ExecuteSqlInsert(string strSql)
136         {
137             try
138             {
139                 // 创建SQL命令对象
140                 innerDBCommand.CommandText = strSql + ";SELECT SCOPE_IDENTITY();";
141 
142                 // SQL语句日志输出
143                 //Log.WriteInfo("SQL:{0}", strSql);
144 
145                 // SQL执行
146                 long id = Convert.ToInt64(innerDBCommand.ExecuteScalar());
147 
148                 // 返回受影响的ID
149                 return id;
150             }
151             catch (Exception ex)
152             {
153                 throw ex;
154             }
155         }
156     }
157 }