说明：本人完成的工作是对传感器--超声波物位计进行硬件集成，上位机通过串口接收传感器数据并将其存到数据库中；在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/，本人初入职场，对编程也是一个菜鸟，只想把自己所学点滴记录下来，如有不合理的地方希望各位高手多多指点，非常感谢！