//创建 历史数据 表格 public static int CreateDataTable(string TableName, List <AnalogModel> SignalList) { string tabName = TableName; //声明要创建的表名,你也可以改为从textbox中获取; string sqlStr = "create table "; sqlStr += tabName + "( ";//"EA_Device_" + sqlStr += "KeyId AUTOINCREMENT PRIMARY KEY, "; //col0为列名,同样可以改为通过从textbox中获取 //identity(1,1)是标记递增种子 //primary key定义主键 sqlStr += "dtime datetime,"; foreach (AnalogModel row in SignalList) { if (!row.ISRecord) { continue; } sqlStr += "sid" + row.SignalId.ToString() + " " + row.SignalType + ","; } sqlStr = sqlStr.Substring(0, sqlStr.Length - 1); sqlStr += " )"; return(AccessDbHelper.ExecuteSql(sqlStr)); }
//插入事件 public static bool InsertAlarm(DateTime time, string Msg) { string savetime = time.ToString("yyyy-MM-dd HH:mm:ss"); string sql = string.Format("insert into Alarm_History_Table (SaveTime,MessageStr) values (#{0}#,'{1}')", savetime, Msg);; return(AccessDbHelper.ExecuteSql(sql) > 0); }
//历史数据 查询 public static DataTable GetHistoricData(DateTime TheBegin, DateTime TheEnd, string tableName) { string begin = TheBegin.ToString("yyyy-MM-dd HH:mm:ss"); string end = TheEnd.ToString("yyyy-MM-dd HH:mm:ss"); string sql = string.Format("select * from {0} where ( Dtime>#{1}# and Dtime<#{2}# ) order by Dtime", tableName, begin, end); return(AccessDbHelper.ExecuteSql_GetTable(sql)); }
//插入 数据 public static bool InsertData(string tableName, List <int> ids, List <string> values) { List <string> colnames = new List <string>(); foreach (int item in ids) { colnames.Add("sid" + item.ToString()); } string time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); string InsertSql = "insert into {0} (dtime,{1}) values(#{2}#,{3})"; InsertSql = string.Format(InsertSql, tableName, string.Join(",", colnames.ToArray()), time, string.Join(",", values.ToArray())); return(AccessDbHelper.ExecuteSql(InsertSql) > 0); }
//历史事件 查询 public static DataTable GetAlermHistoricEvent(DateTime BeginTime, DateTime EndTime) { string begin = BeginTime.ToString("yyyy-MM-dd HH:mm:ss"); string end = EndTime.ToString("yyyy-MM-dd HH:mm:ss"); string sql = string.Format("select * from Alarm_History_Table where ( SaveTime>#{0}# and SaveTime<#{1}# ) order by SaveTime desc", begin, end); DataTable AlarmTable = AccessDbHelper.ExecuteSql_GetTable(sql); if (AlarmTable != null && AlarmTable.Columns.Count > 0) { //移除不相关的列 AlarmTable.Columns.Remove("KeyId"); //修改表格 列名 AlarmTable.Columns["MessageStr"].ColumnName = Model_Data.Language.Desc.EventInfo; AlarmTable.Columns["SaveTime"].ColumnName = Model_Data.Language.Desc.EventTime; } return(AlarmTable); }
//创建 历史事件 表格 public static int CreateEventTable() { string sql = "create table Alarm_History_Table ( KeyId AUTOINCREMENT PRIMARY KEY,SaveTime datetime,MessageStr text(100) )"; return(AccessDbHelper.ExecuteSql(sql)); }
//删除 存储过程 public static Boolean DropProcedure(string ProcedureName) { string sqlStr = string.Format("drop procedure {0}", ProcedureName); return(AccessDbHelper.ExecuteSql(sqlStr) > 0); }
//判断 存储过程 是否存在 public static Boolean IsProcessExist(string ProcessName) { string sql = string.Format("select MSysObjects.name from MSysObjects where (((MSysObjects.name)='{0}') AND ((MSysObjects.type)=5 ));", ProcessName); return(AccessDbHelper.GetSingle(sql) != null); }
//删除 表格 public static Boolean DropTable(string TableName) { string sqlStr = string.Format("drop table {0}", TableName); return(AccessDbHelper.ExecuteSql(sqlStr) > 0); }