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

时间：2022-02-24 17:16:42

说明：本人完成的工作是对传感器--超声波物位计的数据进行采集，并将其存到数据库中，针对此传感器数据在数据库中的增删改查

/*----------------------------------------------------------------
//Copyright(C)2013*******
//版权所有。
//
//文件名：WaterLevelDao.cs
//文件功能描述：定义水位计设置信息相关的数据访问对象
//
//创建标识：2013-9-22
//
//修改标识：2013-9-23
//修改描述：添加GetAllWaterLevelSetInfo,AddWaterLevelSetInfo,
//ModifyWaterLevelSetInfo,DelWaterLevelSetInfo等方法
//
//修改标识：2013-9-24
//修改描述：修改可空字段的数据绑定
//修改标识：2013-9-26
//修改描述：修改AddWaterLevelSet,去掉SELECT@@IDENTITY
//
//----------------------------------------------------------------*/
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSMOS.Model.Device;
usingSystem.Data.SqlClient;
usingSystem.Data;

namespaceSMOS.DAL.Device
{
publicclassWaterLevelSetDao
{
///<summary>
///根据设备ID获取水位计设置信息
///</summary>
///<paramname="deviceID"></param>
///<returns>水位计设置信息</returns>
publicWaterLevelSetInfoGetWaterLevelSetInfo(intdeviceID)
{
stringsql=
@"selectt.DeviceID,t.BaseLevel,t.Remark,t.Port,
t.BaudRate,t.DataBites,t.ParityBit,t.StopBits,t.AcquisitionInterval,
t.ConnectType,t.CreateBy,t.CreateTime,t.UpdateBy,t.UpdateTime
fromdbo.WaterLevelSett(nolock)
wheret.DeviceID=@DeviceID";

IList<SqlParameter>paras=newList<SqlParameter>()
{
newSqlParameter("@DeviceID",SqlDbType.Int){Value=deviceID}
};

WaterLevelSetInfoentity=null;
DataSetds=DBHelper.ExecuteDataset(sql,paras.ToArray());

if(ds==null||ds.Tables.Count<=0||ds.Tables[0].Rows.Count<=0)
returnentity;
returnDataRowBinding(ds.Tables[0].Rows[0]);
}
///<summary>
///获取所有水位计设置信息
///</summary>
///<returns>水位计设置列表</returns>
publicIList<WaterLevelSetInfo>GetAllWaterLevelSetInfo()
{
stringsql=
@"selectt.DeviceID,t.BaseLevel,t.Remark,t.Port,
t.BaudRate,t.DataBites,t.ParityBit,t.StopBits,t.AcquisitionInterval,
t.ConnectType,t.CreateBy,t.CreateTime,t.UpdateBy,t.UpdateTime
fromdbo.WaterLevelSett(nolock)
where1=1";
IList<WaterLevelSetInfo>lstWaterLevelSetInfo=newList<WaterLevelSetInfo>();
DataSetds=DBHelper.ExecuteDataset(sql);
if(ds==null||ds.Tables.Count<=0)
returnlstWaterLevelSetInfo;
foreach(DataRowdrinds.Tables[0].Rows)
{
WaterLevelSetInfoentity=DataRowBinding(dr);
if(entity!=null)
lstWaterLevelSetInfo.Add(entity);
}
returnlstWaterLevelSetInfo;
}
///<summary>
///增加水位计设置信息
///</summary>
///<paramname="waterLevelSetInfo">水位计设置信息</param>
///<returns>操作记录数</returns>
publicintAddWaterLevelSetInfo(WaterLevelSetInfowaterLevelSetInfo)
{
if(waterLevelSetInfo==null)
{
return0;
}
stringsql=
@"INSERTINTOdbo.WaterLevelSet
(DeviceID,BaseLevel,Remark,Port,BaudRate,
DataBites,ParityBit,StopBits,AcquisitionInterval,
ConnectType,CreateBy,CreateTime
)
VALUES
(@DeviceID,@BaseLevel,@Remark,@Port,@BaudRate,
@DataBites,@ParityBit,@StopBits,@AcquisitionInterval,
@ConnectType,@CreateBy,@CreateTime
)";
IList<SqlParameter>paras=newList<SqlParameter>()
{
newSqlParameter("@DeviceID",SqlDbType.Int){Value=waterLevelSetInfo.DeviceID},
newSqlParameter("@BaseLevel",SqlDbType.Decimal){Value=waterLevelSetInfo.BaseLevel},
newSqlParameter("@Remark",SqlDbType.NVarChar,255){Value=waterLevelSetInfo.Remark},
newSqlParameter("@Port",SqlDbType.Int){Value=waterLevelSetInfo.Port},
newSqlParameter("@BaudRate",SqlDbType.Int){Value=waterLevelSetInfo.BaudRate},
newSqlParameter("@DataBites",SqlDbType.Int){Value=waterLevelSetInfo.DataBits},
newSqlParameter("@ParityBit",SqlDbType.Int){Value=waterLevelSetInfo.ParityBit},
newSqlParameter("@StopBits",SqlDbType.Int){Value=waterLevelSetInfo.StopBits},
newSqlParameter("@AcquisitionInterval",SqlDbType.Int){Value=waterLevelSetInfo.AcquisitionInterval},
newSqlParameter("@ConnectType",SqlDbType.TinyInt){Value=waterLevelSetInfo.ConnectType.GetHashCode()},
newSqlParameter("@CreateBy",SqlDbType.NVarChar,25){Value=Global.GlobalInfo.loginInfo.LoginAccount},
newSqlParameter("@CreateTime",SqlDbType.DateTime){Value=DateTime.Now},
};

returnDBHelper.ExecuteNonQuery(sql,paras.ToArray());
}
///<summary>
///修改水位计设置信息
///</summary>
///<paramname="waterLevelSetInfo">水位计设置信息</param>
///<returns>操作记录</returns>
publicintModifyWaterLevelSetInfo(WaterLevelSetInfowaterLevelSetInfo)
{
if(waterLevelSetInfo==null)
{
return0;
}
stringsql=
@"UPDATEdbo.WaterLevelSet
SETDeviceID=@DeviceID
,BaseLevel=@BaseLevel
,Remark=@Remark
,Port=@Port
,BaudRate=@BaudRate
,DataBites=@DataBites
,ParityBit=@ParityBit
,StopBits=@StopBits
,AcquisitionInterval=@AcquisitionInterval
,ConnectType=@ConnectType
,UpdateBy=@UpdateBy
,UpdateTime=@UpdateTime
WHEREDeviceID=@DeviceID";
IList<SqlParameter>paras=newList<SqlParameter>()
{
newSqlParameter("@DeviceID",SqlDbType.Int){Value=waterLevelSetInfo.DeviceID},
newSqlParameter("@BaseLevel",SqlDbType.Decimal){Value=waterLevelSetInfo.BaseLevel},
newSqlParameter("@Remark",SqlDbType.NVarChar,255){Value=waterLevelSetInfo.Remark},
newSqlParameter("@Port",SqlDbType.Int){Value=waterLevelSetInfo.Port},
newSqlParameter("@BaudRate",SqlDbType.Int){Value=waterLevelSetInfo.BaudRate},
newSqlParameter("@DataBites",SqlDbType.Int){Value=waterLevelSetInfo.DataBits},
newSqlParameter("@ParityBit",SqlDbType.Int){Value=waterLevelSetInfo.ParityBit},
newSqlParameter("@StopBits",SqlDbType.Int){Value=waterLevelSetInfo.StopBits},
newSqlParameter("@AcquisitionInterval",SqlDbType.Int){Value=waterLevelSetInfo.AcquisitionInterval},
newSqlParameter("@ConnectType",SqlDbType.TinyInt){Value=waterLevelSetInfo.ConnectType.GetHashCode()},
newSqlParameter("@UpdateBy",SqlDbType.NVarChar,25){Value=Global.GlobalInfo.loginInfo.LoginAccount},
newSqlParameter("@UpdateTime",SqlDbType.DateTime){Value=DateTime.Now},
};
returnDBHelper.ExecuteNonQuery(sql,paras.ToArray());
}
///<summary>
///删除水位计设置信息
///</summary>
///<paramname="waterLevelSetInfo">水位计设置信息</param>
///<returns>操作记录</returns>
publicintDelWaterLevelSetInfo(WaterLevelSetInfowaterLevelSetInfo)
{
if(waterLevelSetInfo==null)
{
return0;
}
stringsql=
@"DELETEFROMdbo.WaterLevelSet
WHEREDeviceID=@DeviceID";
IList<SqlParameter>paras=newList<SqlParameter>()
{
newSqlParameter("@DeviceID",SqlDbType.Int){Value=waterLevelSetInfo.DeviceID},
};

returnDBHelper.ExecuteNonQuery(sql,paras.ToArray());
}
#region数据绑定
privateWaterLevelSetInfoDataRowBinding(DataRowdr)
{
if(dr==null)
{
returnnull;
}

WaterLevelSetInfoentity=newWaterLevelSetInfo();
entity.DeviceID=Convert.ToInt32(dr["DeviceID"]);
entity.BaseLevel=Convert.ToDecimal(dr["BaseLevel"]);

if(dr["Remark"]!=DBNull.Value)
{
entity.Remark=dr["Remark"].ToString();
}
else
{
entity.Remark=string.Empty;
}
entity.Port=Convert.ToInt32(dr["Port"]);
entity.BaudRate=Convert.ToInt32(dr["BaudRate"]);
if(dr["DataBites"]!=DBNull.Value)
{
entity.DataBits=Convert.ToInt32(dr["DataBites"]);
}
if(dr["ParityBit"]!=DBNull.Value)
{
entity.ParityBit=Convert.ToInt32(dr["ParityBit"]);
}
if(dr["StopBits"]!=DBNull.Value)
{
entity.StopBits=Convert.ToInt32(dr["StopBits"]);
}
if(dr["AcquisitionInterval"]!=DBNull.Value)
{
entity.AcquisitionInterval=Convert.ToInt32(dr["AcquisitionInterval"]);
}
if(dr["ConnectType"]!=DBNull.Value)
{
entity.ConnectType=SMOS.Model.Eunm.ConvertToEnum<SMOS.Model.Eunm.DeviceConnectType>(dr["ConnectType"]);
}
if(dr["CreateBy"]!=DBNull.Value)
{
entity.CreateBy=dr["CreateBy"].ToString();
}
else
{
entity.CreateBy=string.Empty;
}
entity.CreateTime=Convert.ToDateTime(dr["CreateTime"]);
if(dr["UpdateBy"]!=DBNull.Value)
{
entity.UpdateBy=dr["UpdateBy"].ToString();
}
else
{
entity.UpdateBy=string.Empty;
}
if(dr["UpdateTime"]!=DBNull.Value)
{
entity.UpdateTime=Convert.ToDateTime(dr["UpdateTime"]);
}
returnentity;
}
#endregion
}
}

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSMOS.Model.Device;
usingSystem.Data.SqlClient;
usingSystem.Data;


namespaceSMOS.DAL.Device
{
publicclassWaterLevelRecordDao
{
///<summary>
///查出水位计记录信息的最后一条记录
///</summary>
///<paramname="deviceID">设备ID</param>
///<returns>操作记录</returns>
publicWaterLevelRecordInfoGetLastWaterLevelRecordInfo(intdeviceID)
{
stringsql=
@"selecttop1
t.RecordID,t.DeviceID,t.MeasuredLevel,t.RecordTime,
t.CreateBy,t.CreateTime,t.Remark
fromdbo.WaterLevelRecordt(nolock)
wheret.DeviceID=@DeviceID
orderbyt.CreateTimedesc";

IList<SqlParameter>paras=newList<SqlParameter>()
{
newSqlParameter("@DeviceID",SqlDbType.Int){Value=deviceID}
};

WaterLevelRecordInfoentity=null;
DataSetds=DBHelper.ExecuteDataset(sql,paras.ToArray());

if(ds==null||ds.Tables.Count<=0||ds.Tables[0].Rows.Count<=0)
{
returnentity;
}
returnDataRowBinding(ds.Tables[0].Rows[0]);

}
///<summary>
///查询水位计的记录信息
///</summary>
///<paramname="deviceID">设备ID</param>
///<returns>记录信息</returns>
publicIList<WaterLevelRecordInfo>GetWaterLevelRecordInfos(intdeviceID)
{
stringsql=
@"selectt.RecordID,t.DeviceID,t.MeasuredLevel,t.RecordTime,
t.CreateBy,t.CreateTime,t.UpdateBy,t.UpdateTime,t.Remark
fromdbo.WaterLevelRecordt(nolock)
wheret.DeviceID=@DeviceID";
IList<SqlParameter>paras=newList<SqlParameter>()
{
newSqlParameter("@DeviceID",SqlDbType.Int){Value=deviceID}
};
IList<WaterLevelRecordInfo>lstWaterLevelRecordInfo=newList<WaterLevelRecordInfo>();
DataSetds=DBHelper.ExecuteDataset(sql,paras.ToArray());

if(ds==null||ds.Tables.Count<=0)
returnlstWaterLevelRecordInfo;
foreach(DataRowdrinds.Tables[0].Rows)
{
WaterLevelRecordInfoentity=DataRowBinding(dr);
if(entity!=null)
{
lstWaterLevelRecordInfo.Add(entity);
}
}
returnlstWaterLevelRecordInfo;
}
///<summary>
///分时间查出水位计相应记录信息
///</summary>
///<paramname="deviceID">设备ID</param>
///<paramname="startTime">开始时间</param>
///<paramname="endTime">结束时间</param>
///<returns>记录信息</returns>
publicIList<WaterLevelRecordInfo>GetWaterLevelRecordInfos(intdeviceID,DateTimestartTime,DateTimeendTime)
{
stringsql=
@"selectt.RecordID,t.DeviceID,t.MeasuredLevel,t.RecordTime,
t.CreateBy,t.CreateTime,t.Remark
fromdbo.WaterLevelRecordt(nolock)
wheret.DeviceID=@DeviceIDandRecordTimebetween@startTimeand@endTime";
IList<SqlParameter>paras=newList<SqlParameter>()
{
newSqlParameter("@DeviceID",SqlDbType.Int){Value=deviceID},
newSqlParameter("@startTime",SqlDbType.DateTime){Value=startTime},
newSqlParameter("@endTime",SqlDbType.DateTime){Value=endTime}
};
IList<WaterLevelRecordInfo>lstWaterLevelRecordInfos=newList<WaterLevelRecordInfo>();
DataSetds=DBHelper.ExecuteDataset(sql,paras.ToArray());
if(ds==null||ds.Tables.Count<=0)
{
returnlstWaterLevelRecordInfos;
}
foreach(DataRowdrinds.Tables[0].Rows)
{
WaterLevelRecordInfoentity=DataRowBinding(dr);
if(entity!=null)
{
lstWaterLevelRecordInfos.Add(entity);
}
}
returnlstWaterLevelRecordInfos;
}
///<summary>
///增加水位计记录信息
///</summary>
///<paramname="recordInfo">水位计记录信息</param>
///<returns>操作记录</returns>
publicintAddWaterLevelRecordInfo(WaterLevelRecordInforecordInfo)
{
if(recordInfo==null)
{
return0;
}
stringsql=
@"INSERTINTOdbo.WaterLevelRecord
(DeviceID,MeasuredLevel,RecordTime,
CreateBy,CreateTime,Remark
)
VALUES
(@DeviceID,@MeasuredLevel,@RecordTime,
@CreateBy,@CreateTime,@Remark
)
SELECT@@IDENTITY";
IList<SqlParameter>paras=newList<SqlParameter>()
{
newSqlParameter("@DeviceID",SqlDbType.Int){Value=recordInfo.DeviceID},
newSqlParameter("@MeasuredLevel",SqlDbType.Decimal){Value=recordInfo.MeasuredLevel},
newSqlParameter("@RecordTime",SqlDbType.DateTime){Value=recordInfo.RecordTime},
newSqlParameter("@CreateTime",SqlDbType.DateTime){Value=DateTime.Now},
newSqlParameter("@CreateBy",SqlDbType.NVarChar,25){Value=Global.GlobalInfo.loginInfo.LoginAccount},
newSqlParameter("@Remark",SqlDbType.NChar,255){Value=recordInfo.Remark},
};
objectret=DBHelper.ExecuteScalar(sql,paras.ToArray());//返回非表类查询结果，自增的ID
if(ret!=null&&int.Parse(ret.ToString())>=0)
{
returnint.Parse(ret.ToString());
}
return0;
}
///<summary>
///更新水位计记录
///</summary>
///<paramname="recordInfo">水位计记录信息</param>
///<returns>操作记录</returns>
publicintModifyWaterLevelRecordInfo(WaterLevelRecordInforecordInfo)
{
if(recordInfo==null)
{
return0;
}
stringsql=
@"UPDATEdbo.WaterLevelRecord
SETDeviceID=@DeviceID
,MeasuredLevel=@MeasuredLevel
,RecordTime=@RecordTime
,UpdateBy=@UpdateBy
,UpdateTime=@UpdateTime
,Remark=@Remark
WHERERecordID=@RecordID";
IList<SqlParameter>paras=newList<SqlParameter>()
{
newSqlParameter("@RecordID",SqlDbType.Int){Value=recordInfo.RecordID},
newSqlParameter("@DeviceID",SqlDbType.Int){Value=recordInfo.DeviceID},
newSqlParameter("@MeasuredLevel",SqlDbType.Decimal){Value=recordInfo.MeasuredLevel},
newSqlParameter("@RecordTime",SqlDbType.DateTime){Value=recordInfo.RecordTime},
newSqlParameter("@UpdateTime",SqlDbType.DateTime){Value=DateTime.Now},
newSqlParameter("@UpdateBy",SqlDbType.NVarChar,25){Value=Global.GlobalInfo.loginInfo.LoginAccount},
newSqlParameter("@Remark",SqlDbType.NVarChar,255){Value=recordInfo.Remark},
};
returnDBHelper.ExecuteNonQuery(sql,paras.ToArray());
}
///<summary>
///删除水位计记录信息
///</summary>
///<paramname="recordInfo">水位计记录信息</param>
///<returns>操作记录</returns>
publicintDelWaterLevelRecordInfo(WaterLevelRecordInforecordInfo)
{
if(recordInfo==null)
{
return0;
}
stringsql=
@"DELETEFROMdbo.WaterLevelRecord
WHERERecordID=@RecordID";
IList<SqlParameter>paras=newList<SqlParameter>()
{
newSqlParameter("@RecordID",SqlDbType.Int){Value=recordInfo.RecordID},
};

returnDBHelper.ExecuteNonQuery(sql,paras.ToArray());
}
#region数据绑定
privateWaterLevelRecordInfoDataRowBinding(DataRowdr)
{
if(dr==null)
{
returnnull;
}
WaterLevelRecordInfoentity=newWaterLevelRecordInfo();
entity.DeviceID=Convert.ToInt32(dr["DeviceID"]);
entity.MeasuredLevel=Convert.ToDecimal(dr["MeasuredLevel"]);
if(dr["Remark"]!=DBNull.Value)
{
entity.Remark=dr["Remark"].ToString();
}
else
{
entity.Remark=string.Empty;
}
entity.RecordTime=Convert.ToDateTime(dr["RecordTime"]);
if(dr["CreateBy"]!=DBNull.Value)
{
entity.CreateBy=dr["CreateBy"].ToString();
}
else
{
entity.CreateBy=string.Empty;
}
entity.CreateTime=Convert.ToDateTime(dr["CreateTime"]);
if(dr["UpdateBy"]!=DBNull.Value)
{
entity.UpdateBy=dr["UpdateBy"].ToString();
}
else
{
entity.UpdateBy=string.Empty;
}
if(dr["UpdateTime"]!=DBNull.Value)
{
entity.UpdateTime=Convert.ToDateTime(dr["UpdateTime"]);
}

returnentity;
}
#endregion
}
}

本文出自 “EaIE099” 博客，请务必保留此出处http://tustring.blog.51cto.com/7573154/1305853

标签：

相关文章