用C#实现对MSSqlServer数据库的增删改查DAL层

说明：本人完成的工作是对传感器--超声波物位计进行硬件集成，上位机通过串口接收传感器数据并将其存到数据库中；在DAL层实现对数据库的增删改查，其中包含两个数据表分别是WaterLevelSet表和WaterLevelRecord表，以下代码即是完成对两张表进行数据操作。

  1 /*----------------------------------------------------------------
  2           // Copyright (C) 2013  *************
  3           // 版权所有。 
  4           //
  5           // 文件名：WaterLevelDao.cs
  6           // 文件功能描述：定义水位计设置信息相关的数据访问对象
  7           // 
  8           // 创建标识：2013-9-22
  9           //
 10           // 修改标识：2013-9-23
 11           // 修改描述：添加GetAllWaterLevelSetInfo,AddWaterLevelSetInfo,
 12           //ModifyWaterLevelSetInfo,DelWaterLevelSetInfo等方法
 13           //
 14           // 修改标识：2013-9-24
 15           // 修改描述：修改可空字段的数据绑定
 16           //修改标识：2013-9-26
 17           // 修改描述：修改AddWaterLevelSet,去掉SELECT @@IDENTITY
 18           //
 19 //----------------------------------------------------------------*/
 20 using System;
 21 using System.Collections.Generic;
 22 using System.Linq;
 23 using System.Text;
 24 using SMOS.Model.Device;
 25 using System.Data.SqlClient;
 26 using System.Data;
 27 
 28 namespace SMOS.DAL.Device
 29 {
 30     public class WaterLevelSetDao
 31     {
 32         /// <summary>
 33         /// 根据设备ID获取水位计设置信息
 34         /// </summary>
 35         /// <param name="deviceID"></param>
 36         /// <returns>水位计设置信息</returns>
 37       public WaterLevelSetInfo GetWaterLevelSetInfo(int deviceID)
 38       {
 39           string sql =
 40                 @"select t.DeviceID,t.BaseLevel,t.Remark,t.Port,
 41                     t.BaudRate,t.DataBites,t.ParityBit,t.StopBits,t.AcquisitionInterval,
 42                     t.ConnectType,t.CreateBy,t.CreateTime,t.UpdateBy,t.UpdateTime
 43                 from dbo.WaterLevelSet t(nolock)
 44                 where t.DeviceID=@DeviceID";
 45 
 46           IList<SqlParameter> paras = new List<SqlParameter>()
 47             {
 48                 new SqlParameter("@DeviceID",SqlDbType.Int){Value = deviceID}
 49             };
 50 
 51           WaterLevelSetInfo entity = null;
 52           DataSet ds = DBHelper.ExecuteDataset(sql,paras.ToArray());
 53 
 54           if (ds == null || ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0)
 55               return entity;
 56           return DataRowBinding(ds.Tables[0].Rows[0]);
 57       }
 58         /// <summary>
 59         /// 获取所有水位计设置信息
 60         /// </summary>
 61         /// <returns>水位计设置列表</returns>
 62       public IList<WaterLevelSetInfo> GetAllWaterLevelSetInfo()
 63       {
 64           string sql =
 65                 @"select t.DeviceID,t.BaseLevel,t.Remark,t.Port,
 66                     t.BaudRate,t.DataBites,t.ParityBit,t.StopBits,t.AcquisitionInterval,
 67                     t.ConnectType,t.CreateBy,t.CreateTime,t.UpdateBy,t.UpdateTime
 68                 from dbo.WaterLevelSet t(nolock)
 69                 where 1=1";
 70             IList<WaterLevelSetInfo> lstWaterLevelSetInfo = new List<WaterLevelSetInfo>();
 71             DataSet ds = DBHelper.ExecuteDataset(sql);
 72             if (ds == null || ds.Tables.Count <= 0)
 73                 return lstWaterLevelSetInfo;
 74             foreach (DataRow dr in ds.Tables[0].Rows)
 75             {
 76                 WaterLevelSetInfo entity = DataRowBinding(dr);
 77                 if (entity != null)
 78                     lstWaterLevelSetInfo.Add(entity);
 79             }
 80             return lstWaterLevelSetInfo;
 81       }
 82         /// <summary>
 83         /// 增加水位计设置信息
 84         /// </summary>
 85       /// <param name="waterLevelSetInfo">水位计设置信息</param>
 86       /// <returns>操作记录数</returns>
 87       public int AddWaterLevelSetInfo(WaterLevelSetInfo waterLevelSetInfo)
 88       {
 89           if (waterLevelSetInfo == null)
 90           {
 91               return 0;
 92           }
 93           string sql=
 94                @"INSERT INTO dbo.WaterLevelSet
 95                  (DeviceID,BaseLevel,Remark,Port,BaudRate,
 96                   DataBites,ParityBit,StopBits,AcquisitionInterval,
 97                   ConnectType,CreateBy,CreateTime
 98                   )
 99                 VALUES
100                  (@DeviceID,@BaseLevel,@Remark,@Port,@BaudRate,
101                   @DataBites,@ParityBit,@StopBits,@AcquisitionInterval,
102                   @ConnectType,@CreateBy,@CreateTime
103                  )";
104           IList<SqlParameter> paras = new List<SqlParameter>()
105             {
106                 new SqlParameter("@DeviceID",SqlDbType.Int){Value = waterLevelSetInfo.DeviceID},
107                 new SqlParameter("@BaseLevel",SqlDbType.Decimal){Value = waterLevelSetInfo.BaseLevel},
108                 new SqlParameter("@Remark",SqlDbType.NVarChar,255){Value = waterLevelSetInfo.Remark},
109                 new SqlParameter("@Port",SqlDbType.Int){Value = waterLevelSetInfo.Port},
110                 new SqlParameter("@BaudRate",SqlDbType.Int){Value = waterLevelSetInfo.BaudRate},
111                 new SqlParameter("@DataBites",SqlDbType.Int){Value = waterLevelSetInfo.DataBits},
112                 new SqlParameter("@ParityBit",SqlDbType.Int){Value = waterLevelSetInfo.ParityBit},
113                 new SqlParameter("@StopBits",SqlDbType.Int){Value = waterLevelSetInfo.StopBits},
114                 new SqlParameter("@AcquisitionInterval",SqlDbType.Int){Value = waterLevelSetInfo.AcquisitionInterval},
115                 new SqlParameter("@ConnectType",SqlDbType.TinyInt){Value = waterLevelSetInfo.ConnectType.GetHashCode()},
116                 new SqlParameter("@CreateBy",SqlDbType.NVarChar,25){Value = Global.GlobalInfo.loginInfo.LoginAccount},
117                 new SqlParameter("@CreateTime",SqlDbType.DateTime){Value = DateTime.Now},
118             };
119 
120           return DBHelper.ExecuteNonQuery(sql, paras.ToArray());
121       }
122         /// <summary>
123         /// 修改水位计设置信息
124         /// </summary>
125       /// <param name="waterLevelSetInfo">水位计设置信息</param>
126         /// <returns>操作记录</returns>
127       public int ModifyWaterLevelSetInfo(WaterLevelSetInfo waterLevelSetInfo)
128       {
129           if (waterLevelSetInfo == null)
130           {
131               return 0;
132           }
133           string sql =
134              @"UPDATE dbo.WaterLevelSet
135              SET  DeviceID=@DeviceID
136                  ,BaseLevel=@BaseLevel
137                  ,Remark=@Remark
138                  ,Port=@Port
139                  ,BaudRate=@BaudRate
140                  ,DataBites=@DataBites
141                  ,ParityBit=@ParityBit
142                  ,StopBits=@StopBits
143                  ,AcquisitionInterval=@AcquisitionInterval
144                  ,ConnectType=@ConnectType
145                  ,UpdateBy=@UpdateBy
146                  ,UpdateTime=@UpdateTime
147               WHERE DeviceID=@DeviceID";
148           IList<SqlParameter> paras = new List<SqlParameter>()
149             {
150                 new SqlParameter("@DeviceID",SqlDbType.Int){Value = waterLevelSetInfo.DeviceID},
151                 new SqlParameter("@BaseLevel",SqlDbType.Decimal){Value = waterLevelSetInfo.BaseLevel},
152                 new SqlParameter("@Remark",SqlDbType.NVarChar,255){Value = waterLevelSetInfo.Remark},
153                 new SqlParameter("@Port",SqlDbType.Int){Value = waterLevelSetInfo.Port},
154                 new SqlParameter("@BaudRate",SqlDbType.Int){Value = waterLevelSetInfo.BaudRate},
155                 new SqlParameter("@DataBites",SqlDbType.Int){Value = waterLevelSetInfo.DataBits},
156                 new SqlParameter("@ParityBit",SqlDbType.Int){Value = waterLevelSetInfo.ParityBit},
157                 new SqlParameter("@StopBits",SqlDbType.Int){Value = waterLevelSetInfo.StopBits},
158                 new SqlParameter("@AcquisitionInterval",SqlDbType.Int){Value = waterLevelSetInfo.AcquisitionInterval},
159                 new SqlParameter("@ConnectType",SqlDbType.TinyInt){Value = waterLevelSetInfo.ConnectType.GetHashCode()},
160                 new SqlParameter("@UpdateBy",SqlDbType.NVarChar,25){Value = Global.GlobalInfo.loginInfo.LoginAccount},
161                 new SqlParameter("@UpdateTime",SqlDbType.DateTime){Value =  DateTime.Now},
162             };
163           return DBHelper.ExecuteNonQuery(sql, paras.ToArray());
164       }
165         /// <summary>
166         /// 删除水位计设置信息
167         /// </summary>
168         /// <param name="waterLevelSetInfo">水位计设置信息</param>
169         /// <returns>操作记录</returns>
170       public int DelWaterLevelSetInfo(WaterLevelSetInfo waterLevelSetInfo)
171       {
172           if (waterLevelSetInfo == null)
173           {
174               return 0;
175           }
176           string sql =
177               @"DELETE FROM dbo.WaterLevelSet
178                 WHERE DeviceID = @DeviceID";
179           IList<SqlParameter> paras = new List<SqlParameter>()
180           {
181                 new SqlParameter("@DeviceID",SqlDbType.Int){Value = waterLevelSetInfo.DeviceID },
182           };
183 
184             return DBHelper.ExecuteNonQuery(sql, paras.ToArray());          
185       }
186       #region 数据绑定
187       private WaterLevelSetInfo DataRowBinding(DataRow dr)
188       {
189           if (dr == null)
190           {
191               return null; 
192           }
193 
194           WaterLevelSetInfo entity = new WaterLevelSetInfo();
195           entity.DeviceID = Convert.ToInt32(dr["DeviceID"]);
196           entity.BaseLevel = Convert.ToDecimal(dr["BaseLevel"]);
197 
198           if (dr["Remark"] != DBNull.Value)
199           {
200               entity.Remark = dr["Remark"].ToString();
201           }
202           else
203           {
204              entity.Remark = string.Empty; 
205           }
206           entity.Port = Convert.ToInt32(dr["Port"]);
207           entity.BaudRate = Convert.ToInt32(dr["BaudRate"]);
208           if (dr["DataBites"] != DBNull.Value)
209           {
210               entity.DataBits = Convert.ToInt32(dr["DataBites"]);
211           }
212           if (dr["ParityBit"] != DBNull.Value)
213           {
214               entity.ParityBit = Convert.ToInt32(dr["ParityBit"]);
215           }
216           if (dr["StopBits"] != DBNull.Value)
217           {
218               entity.StopBits = Convert.ToInt32(dr["StopBits"]); 
219           }
220           if (dr["AcquisitionInterval"] != DBNull.Value)
221           {
222               entity.AcquisitionInterval = Convert.ToInt32(dr["AcquisitionInterval"]);
223           }
224           if (dr["ConnectType"] != DBNull.Value)
225           {
226               entity.ConnectType = SMOS.Model.Eunm.ConvertToEnum<SMOS.Model.Eunm.DeviceConnectType>(dr["ConnectType"]);
227           }
228           if (dr["CreateBy"] != DBNull.Value)
229           {
230               entity.CreateBy = dr["CreateBy"].ToString();
231           }
232           else
233           {
234               entity.CreateBy = string.Empty;
235           }
236           entity.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
237           if (dr["UpdateBy"] != DBNull.Value)
238           {
239               entity.UpdateBy = dr["UpdateBy"].ToString();
240           }
241           else
242           {
243               entity.UpdateBy = string.Empty;
244           }
245           if (dr["UpdateTime"] != DBNull.Value)
246           {
247               entity.UpdateTime = Convert.ToDateTime(dr["UpdateTime"]);
248           }
249           return entity;
250       }
251       #endregion
252     }
253 }

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using SMOS.Model.Device;
  6 using System.Data.SqlClient;
  7 using System.Data;
  8 
  9 
 10 namespace SMOS.DAL.Device
 11 {
 12     public class WaterLevelRecordDao
 13     {
 14         /// <summary>
 15         /// 查出水位计记录信息的最后一条记录
 16         /// </summary>
 17         /// <param name="deviceID">设备ID</param>
 18         /// <returns>操作记录</returns>
 19         public WaterLevelRecordInfo GetLastWaterLevelRecordInfo(int deviceID)
 20         {
 21             string sql =
 22                  @"select top 1
 23                       t.RecordID,t.DeviceID,t.MeasuredLevel,t.RecordTime,
 24                       t.CreateBy,t.CreateTime,t.Remark
 25                  from dbo.WaterLevelRecord t(nolock)
 26                  where t.DeviceID=@DeviceID
 27                  order by t.CreateTime desc";
 28 
 29             IList<SqlParameter> paras = new List<SqlParameter>()
 30             {
 31                 new SqlParameter("@DeviceID",SqlDbType.Int){Value = deviceID}
 32             };
 33 
 34             WaterLevelRecordInfo entity = null;
 35             DataSet ds = DBHelper.ExecuteDataset(sql, paras.ToArray());
 36 
 37             if (ds == null || ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0)
 38             {
 39                 return entity;
 40             }
 41             return DataRowBinding(ds.Tables[0].Rows[0]);
 42 
 43         }
 44         /// <summary>
 45         /// 查询水位计的记录信息
 46         /// </summary>
 47         /// <param name="deviceID">设备ID</param>
 48         /// <returns>记录信息</returns>
 49         public IList<WaterLevelRecordInfo> GetWaterLevelRecordInfos(int deviceID)
 50         {
 51             string sql =
 52              @"select t.RecordID,t.DeviceID,t.MeasuredLevel,t.RecordTime,
 53                       t.CreateBy,t.CreateTime,t.UpdateBy,t.UpdateTime,t.Remark
 54                 from dbo.WaterLevelRecord t(nolock)
 55                 where t.DeviceID=@DeviceID";
 56             IList<SqlParameter> paras = new List<SqlParameter>()
 57             {
 58                 new SqlParameter("@DeviceID",SqlDbType.Int){Value = deviceID}
 59             };
 60             IList<WaterLevelRecordInfo> lstWaterLevelRecordInfo = new List<WaterLevelRecordInfo>();
 61             DataSet ds = DBHelper.ExecuteDataset(sql, paras.ToArray());
 62 
 63             if (ds == null || ds.Tables.Count <= 0)
 64                 return lstWaterLevelRecordInfo;
 65             foreach (DataRow dr in ds.Tables[0].Rows)
 66             {
 67                 WaterLevelRecordInfo entity = DataRowBinding(dr);
 68                 if (entity != null)
 69                 {
 70                     lstWaterLevelRecordInfo.Add(entity);
 71                 }
 72             }
 73             return lstWaterLevelRecordInfo;     
 74         }
 75         /// <summary>
 76         /// 分时间查出水位计相应记录信息
 77         /// </summary>
 78         /// <param name="deviceID">设备ID</param>
 79         /// <param name="startTime">开始时间</param>
 80         /// <param name="endTime">结束时间</param>
 81         /// <returns>记录信息</returns>
 82         public IList<WaterLevelRecordInfo> GetWaterLevelRecordInfos(int deviceID, DateTime startTime, DateTime endTime)
 83         {
 84             string sql =
 85              @"select t.RecordID,t.DeviceID,t.MeasuredLevel,t.RecordTime,
 86                       t.CreateBy,t.CreateTime,t.Remark
 87                 from dbo.WaterLevelRecord t(nolock)
 88                 where t.DeviceID=@DeviceID and RecordTime between @startTime and @endTime";
 89             IList<SqlParameter> paras = new List<SqlParameter>() 
 90             {                  
 91                 new SqlParameter("@DeviceID",SqlDbType.Int){Value = deviceID},
 92                 new SqlParameter("@startTime",SqlDbType.DateTime){Value = startTime },
 93                 new SqlParameter("@endTime",SqlDbType.DateTime){Value = endTime }
 94             };
 95             IList<WaterLevelRecordInfo> lstWaterLevelRecordInfos = new List<WaterLevelRecordInfo>();
 96             DataSet ds = DBHelper.ExecuteDataset(sql, paras.ToArray());
 97             if (ds == null || ds.Tables.Count <= 0)
 98             {
 99                 return lstWaterLevelRecordInfos; 
100             }
101             foreach (DataRow dr in ds.Tables[0].Rows)
102             {
103                 WaterLevelRecordInfo entity = DataRowBinding(dr);
104                 if (entity != null)
105                 {
106                     lstWaterLevelRecordInfos.Add(entity);
107                 }
108             }
109             return lstWaterLevelRecordInfos;
110         }
111         /// <summary>
112         /// 增加水位计记录信息
113         /// </summary>
114         /// <param name="recordInfo">水位计记录信息</param>
115         /// <returns>操作记录</returns>
116         public int AddWaterLevelRecordInfo(WaterLevelRecordInfo recordInfo)
117         {
118             if (recordInfo == null)
119             {
120                 return 0;
121             }
122             string sql =
123                  @"INSERT INTO dbo.WaterLevelRecord
124                  (DeviceID,MeasuredLevel,RecordTime,
125                   CreateBy,CreateTime,Remark
126                   )
127                 VALUES
128                  (@DeviceID,@MeasuredLevel,@RecordTime,
129                   @CreateBy,@CreateTime,@Remark
130                  )
131                  SELECT @@IDENTITY";
132             IList<SqlParameter> paras = new List<SqlParameter>()
133             {
134                 new SqlParameter("@DeviceID",SqlDbType.Int){Value = recordInfo.DeviceID},
135                 new SqlParameter("@MeasuredLevel",SqlDbType.Decimal){Value = recordInfo.MeasuredLevel},
136                 new SqlParameter("@RecordTime",SqlDbType.DateTime){Value = recordInfo.RecordTime},
137                 new SqlParameter("@CreateTime",SqlDbType.DateTime){Value = DateTime.Now},
138                 new SqlParameter("@CreateBy",SqlDbType.NVarChar,25){Value = Global.GlobalInfo.loginInfo.LoginAccount},
139                 new SqlParameter("@Remark",SqlDbType.NChar,255){Value = recordInfo.Remark},
140             };
141             object ret = DBHelper.ExecuteScalar(sql, paras.ToArray());//返回非表类查询结果，自增的ID
142             if (ret != null && int.Parse(ret.ToString()) >= 0)
143             {
144                return int.Parse(ret.ToString());
145             }
146             return 0;
147         }
148         /// <summary>
149         /// 更新水位计记录
150         /// </summary>
151         /// <param name="recordInfo">水位计记录信息</param>
152         /// <returns>操作记录</returns>
153         public int  ModifyWaterLevelRecordInfo(WaterLevelRecordInfo recordInfo)
154         {
155             if (recordInfo == null)
156             {
157                 return 0;
158             }
159             string sql =
160                @"UPDATE dbo.WaterLevelRecord
161              SET  DeviceID=@DeviceID
162                  ,MeasuredLevel=@MeasuredLevel
163                  ,RecordTime=@RecordTime
164                  ,UpdateBy=@UpdateBy
165                  ,UpdateTime=@UpdateTime
166                  ,Remark=@Remark
167               WHERE RecordID=@RecordID";
168             IList<SqlParameter> paras = new List<SqlParameter>()
169             {
170                 new SqlParameter("@RecordID",SqlDbType.Int){Value = recordInfo.RecordID},
171                 new SqlParameter("@DeviceID",SqlDbType.Int){Value = recordInfo.DeviceID},
172                 new SqlParameter("@MeasuredLevel",SqlDbType.Decimal){Value = recordInfo.MeasuredLevel},
173                 new SqlParameter("@RecordTime",SqlDbType.DateTime){Value = recordInfo.RecordTime},
174                 new SqlParameter("@UpdateTime",SqlDbType.DateTime){Value = DateTime.Now},
175                 new SqlParameter("@UpdateBy",SqlDbType.NVarChar,25){Value = Global.GlobalInfo.loginInfo.LoginAccount},
176                 new SqlParameter("@Remark",SqlDbType.NVarChar,255){Value = recordInfo.Remark},
177             };
178             return DBHelper.ExecuteNonQuery(sql, paras.ToArray());
179         }
180         /// <summary>
181         /// 删除水位计记录信息
182         /// </summary>
183         /// <param name="recordInfo">水位计记录信息</param>
184         /// <returns>操作记录</returns>
185         public int  DelWaterLevelRecordInfo(WaterLevelRecordInfo recordInfo)
186         {
187             if (recordInfo == null)
188             {
189                 return 0;
190             }
191             string sql =
192                 @"DELETE FROM dbo.WaterLevelRecord
193                 WHERE RecordID=@RecordID";
194             IList<SqlParameter> paras = new List<SqlParameter>()
195             {
196                 new SqlParameter("@RecordID",SqlDbType.Int){Value = recordInfo.RecordID },
197             };
198 
199             return DBHelper.ExecuteNonQuery(sql, paras.ToArray());      
200         }
201         #region 数据绑定
202         private WaterLevelRecordInfo DataRowBinding(DataRow dr)
203         {
204             if (dr == null)
205             {
206                 return null;
207             }
208             WaterLevelRecordInfo entity = new WaterLevelRecordInfo();            
209             entity.DeviceID = Convert.ToInt32(dr["DeviceID"]);
210             entity.MeasuredLevel=Convert.ToDecimal(dr["MeasuredLevel"]);
211             if (dr["Remark"] != DBNull.Value)
212             {
213                 entity.Remark = dr["Remark"].ToString();
214             }
215             else
216             {
217                 entity.Remark = string.Empty;
218             }
219             entity.RecordTime = Convert.ToDateTime(dr["RecordTime"]);
220             if (dr["CreateBy"] != DBNull.Value)
221             {
222                 entity.CreateBy= dr["CreateBy"].ToString();
223             }
224             else
225             {
226                 entity.CreateBy = string.Empty;
227             }
228             entity.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
229             if (dr["UpdateBy"] != DBNull.Value)
230             {
231                 entity.UpdateBy = dr["UpdateBy"].ToString();
232             }
233             else
234             {
235                 entity.UpdateBy = string.Empty;
236             }
237             if (dr["UpdateTime"] != DBNull.Value)
238             {
239                 entity.UpdateTime = Convert.ToDateTime(dr["UpdateTime"]);
240             }
241 
242             return entity;
243         }
244         #endregion
245     }
246 }

　　若需转载请标明出处或链接http://www.cnblogs.com/EaIE099/，本人初入职场，对编程也是一个菜鸟，只想把自己所学点滴记录下来，如有不合理的地方希望各位高手多多指点，非常感谢！