//创建 历史数据 表格 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 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 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 DropTable(string TableName) { string sqlStr = string.Format("drop table {0}", TableName); return(AccessDbHelper.ExecuteSql(sqlStr) > 0); }