// string column = "refid,LineID,DeviceID,IsDown,Shift,Model,Start_time,End_time,Duration,Category,Lev1,Lev2,Brand,CBrand,Ctype,stand_time,Reason,Memo,status,create_by,create_time,update_by,update_time"; //@refid,@LineID,@DeviceID,@IsDown,@Shift,@Model,@Start_time,@End_time,@Duration,@Category,@Lev1,@Lev2,@Brand,@CBrand,@Ctype,@stand_time,@Reason,@Memo,@status,@create_by,@create_time,@update_by,@update_time public static string CreateNew(DTRecord data) { string sql = " insert into DTRecord(TagID,Value,PDate,LineID,DeviceID,IsDown,Shift,Model,Start_time,End_time,Duration,Category,Lev1,Lev2,Brand,CBrand,Ctype,stand_time,Reason,Memo,status,create_by,create_time)values(@TagID,@Value,@PDate,@LineID,@DeviceID,@IsDown,@Shift,@Model,@Start_time,@End_time,@Duration,@Category,@Lev1,@Lev2,@Brand,@CBrand,@Ctype,@stand_time,@Reason,@Memo,@status,@create_by,@create_time)"; SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TagID", data.TagID), new SqlParameter("@Value", data.Value), new SqlParameter("@PDate", data.PDate), new SqlParameter("@LineID", data.LineID), new SqlParameter("@DeviceID", data.DeviceID), new SqlParameter("@IsDown", data.IsDown), new SqlParameter("@Shift", data.Shift), new SqlParameter("@Model", data.Model), new SqlParameter("@Start_time", data.Start_time), new SqlParameter("@End_time", data.End_time), new SqlParameter("@Duration", data.Duration), new SqlParameter("@Category", data.Category), new SqlParameter("@Lev1", data.Lev1), new SqlParameter("@Lev2", data.Lev2), new SqlParameter("@Brand", data.Brand), new SqlParameter("@CBrand", data.CBrand), new SqlParameter("@Ctype", data.Ctype), new SqlParameter("@stand_time", data.stand_time), new SqlParameter("@Reason", data.Reason), new SqlParameter("@Memo", data.Memo), new SqlParameter("@status", data.status), new SqlParameter("@create_by", data.create_by), new SqlParameter("@create_time", DateTime.Now) }; foreach (SqlParameter parm in paras) { if (parm.Value == null) { parm.Value = DBNull.Value; } } return(MSSql.ExecInsertParaTransStr(sql, paras)); }
public static DataTable QueryByCondition(DTRecord data) { StringBuilder sb = new StringBuilder(); string sqlstr = "select refid,PDate,LineID,DeviceID,IsDown,Shift,Model,Start_time,End_time,Duration,Category,Lev1,Lev2,Brand,CBrand,Ctype,stand_time,Reason,Memo,status,create_by,create_time,update_by,update_time from DTRecord where 1=1 "; sb.Append(sqlstr); if (!string.IsNullOrEmpty(data.LineID)) { sb.AppendFormat(" and LineID like '%{0}%' ", data.LineID); } if (!string.IsNullOrEmpty(data.DeviceID)) { sb.AppendFormat(" and DeviceID like '%{0}%' ", data.DeviceID); } if (!string.IsNullOrEmpty(data.IsDown)) { sb.AppendFormat(" and IsDown like '%{0}%' ", data.IsDown); } if (!string.IsNullOrEmpty(data.Shift)) { sb.AppendFormat(" and Shift like '%{0}%' ", data.Shift); } if (!string.IsNullOrEmpty(data.Model)) { sb.AppendFormat(" and Model like '%{0}%' ", data.Model); } if (!string.IsNullOrEmpty(data.Category)) { sb.AppendFormat(" and Category like '%{0}%' ", data.Category); } if (!string.IsNullOrEmpty(data.Lev1)) { sb.AppendFormat(" and Lev1 like '%{0}%' ", data.Lev1); } if (!string.IsNullOrEmpty(data.Lev2)) { sb.AppendFormat(" and Lev2 like '%{0}%' ", data.Lev2); } if (!string.IsNullOrEmpty(data.Brand)) { sb.AppendFormat(" and Brand like '%{0}%' ", data.Brand); } if (!string.IsNullOrEmpty(data.CBrand)) { sb.AppendFormat(" and CBrand like '%{0}%' ", data.CBrand); } if (!string.IsNullOrEmpty(data.Ctype)) { sb.AppendFormat(" and Ctype like '%{0}%' ", data.Ctype); } if (!string.IsNullOrEmpty(data.stand_time)) { sb.AppendFormat(" and stand_time like '%{0}%' ", data.stand_time); } if (!string.IsNullOrEmpty(data.Reason)) { sb.AppendFormat(" and Reason like '%{0}%' ", data.Reason); } if (!string.IsNullOrEmpty(data.Memo)) { sb.AppendFormat(" and Memo like '%{0}%' ", data.Memo); } if (string.IsNullOrEmpty(data.bgntime) || string.IsNullOrEmpty(data.endtime)) { if (string.IsNullOrEmpty(data.bgntime) && string.IsNullOrEmpty(data.endtime)) { } else { if (string.IsNullOrEmpty(data.bgntime)) { sb.AppendFormat(" and create_time between '2000-01-11 00:00:00' and '{0}'", data.endtime); } else { sb.AppendFormat(" and create_time between '{0}' and GETDATE()+1 ", data.bgntime); } } } else { sb.AppendFormat(" and create_time between '{0}' and '{1}'", data.bgntime, data.endtime); } return(MSSql.ExecuteQueryDataTable(sb.ToString())); }
public static DTRecord GetObject(UInt64 refid) { DTRecord data = new DTRecord(); DataTable dt = DTRecord.QueryDataByRefID(refid); if (dt == null || dt.Rows.Count == 0) { return(null); } else { data.refid = Convert.ToUInt64(dt.Rows[0]["refid"]); data.PDate = Convert.ToDateTime(dt.Rows[0]["PDate"].ToString()); data.LineID = dt.Rows[0]["LineID"].ToString(); data.DeviceID = dt.Rows[0]["DeviceID"].ToString(); data.IsDown = dt.Rows[0]["IsDown"].ToString(); data.Shift = dt.Rows[0]["Shift"].ToString(); data.Model = dt.Rows[0]["Model"].ToString(); if (dt.Rows[0]["Start_time"] != System.DBNull.Value) { data.Start_time = Convert.ToDateTime(dt.Rows[0]["Start_time"].ToString()); } if (dt.Rows[0]["End_time"] != System.DBNull.Value) { data.End_time = Convert.ToDateTime(dt.Rows[0]["End_time"].ToString()); } data.Duration = Convert.ToInt32(dt.Rows[0]["Duration"].ToString()); data.Category = dt.Rows[0]["Category"].ToString(); data.Lev1 = dt.Rows[0]["Lev1"].ToString(); data.Lev2 = dt.Rows[0]["Lev2"].ToString(); data.Brand = dt.Rows[0]["Brand"].ToString(); data.CBrand = dt.Rows[0]["CBrand"].ToString(); data.Ctype = dt.Rows[0]["Ctype"].ToString(); data.stand_time = dt.Rows[0]["stand_time"].ToString(); data.Reason = dt.Rows[0]["Reason"].ToString(); data.Memo = dt.Rows[0]["Memo"].ToString(); data.status = dt.Rows[0]["status"].ToString(); data.create_by = dt.Rows[0]["create_by"].ToString(); data.update_by = dt.Rows[0]["update_by"].ToString(); DateTime time; if (dt.Rows[0]["create_time"] != System.DBNull.Value) { if (DateTime.TryParse(dt.Rows[0]["create_time"].ToString(), out time)) { data.create_time = time; } } else { data.create_time = null; } return(data); } }
public static string UpdateDataByRefID(DTRecord data) { const string sql = "update DTRecord set "; StringBuilder sb = new StringBuilder(); sb.Append(sql); if (!string.IsNullOrEmpty(data.DeviceID)) { sb.AppendFormat(" DeviceID='{0}' ", data.DeviceID); } if (!string.IsNullOrEmpty(data.Shift)) { sb.AppendFormat(" , Shift='{0}' ", data.Shift); } if (!string.IsNullOrEmpty(data.Model)) { sb.AppendFormat(" , Model='{0}' ", data.Model); } if (!string.IsNullOrEmpty(data.Category)) { sb.AppendFormat(" , Category='{0}' ", data.Category); } if (!string.IsNullOrEmpty(data.Lev1)) { sb.AppendFormat(" , Lev1='{0}' ", data.Lev1); } if (!string.IsNullOrEmpty(data.Lev2)) { sb.AppendFormat(" , Lev2='{0}' ", data.Lev2); } if (!string.IsNullOrEmpty(data.Brand)) { sb.AppendFormat(" , Brand='{0}' ", data.Brand); } if (!string.IsNullOrEmpty(data.CBrand)) { sb.AppendFormat(" , CBrand='{0}' ", data.CBrand); } if (!string.IsNullOrEmpty(data.Ctype)) { sb.AppendFormat(" , Ctype='{0}' ", data.Ctype); } if (!string.IsNullOrEmpty(data.stand_time)) { sb.AppendFormat(" , stand_time='{0}' ", data.stand_time); } if (!string.IsNullOrEmpty(data.Reason)) { sb.AppendFormat(" , Reason='{0}' ", data.Reason); } if (!string.IsNullOrEmpty(data.Memo)) { sb.AppendFormat(" , Memo='{0}' ", data.Memo); } if (!string.IsNullOrEmpty(data.status)) { sb.AppendFormat(" , status='{0}' ", data.status); } if (!string.IsNullOrEmpty(data.update_by)) { sb.AppendFormat(" , update_by='{0}' ", data.update_by); } sb.AppendFormat(" , update_time='{0}' ", DateTime.Now); sb.AppendFormat(" where refid={0} ", data.refid); return(MSSql.ExcuteNOQueryStrEx(sb.ToString())); }