public static void MSAccessExecuteBySql()
{
const string strDBFile = @"E:\receive\李宏明\Lastest\佰丽酒店备份.mdb";
DataTable objDT = new DataTable();
StringBuilder sbCreateTableSql = new StringBuilder();
//文件配置
//"update 文件配置 set 文件版本=4"
//sbCreateTableSql.Append("alter table 文件配置 drop column 器件长度");
List <string> addList = new List <string>();
//addList.Add(" alter table 0001 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0002 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0003 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0004 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0005 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0006 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0007 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0008 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0009 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0010 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0011 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0012 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0013 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0014 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0015 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0016 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0017 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0018 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0019 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0020 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0021 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
addList.Add(" alter table 0022 add panhao varchar(3),jianhao varchar(3), louhao varchar(3), quhao varchar(3), cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5); ");
_msDBConn = new SCA.DatabaseAccess.MSAccessDatabaseAccess(strDBFile, _logHelper, _fileService);
//foreach(string type in addList)
//{
// sbCreateTableSql.Clear();
// sbCreateTableSql.Append(type);
// _msDBConn.ExecuteBySql(sbCreateTableSql);
//}
sbCreateTableSql.Clear();
///sbCreateTableSql.Append("select 回路 from 系统设置;");
//sbCreateTableSql.Append("Create table 网络手控盘(编号 VarChar(4),板卡号 VarChar(4), 盘号 VarChar(4),键号 VarChar(4),地编号 VarChar(7),sdpkey VarChar(5))");
//sbCreateTableSql.Append("Create table 混合组态(编号 VarChar(4),动作常数 VarChar(4),动作类型 VarChar(4),分类A VarChar(4),楼号A VarChar(4),区号A VarChar(4),层号A VarChar(4),类型A VarChar(20),分类B VarChar(4),楼号B VarChar(4),区号B VarChar(4),层号B VarChar(4),类型B VarChar(20),分类C VarChar(4),楼号C VarChar(4),区号C VarChar(4),层号C VarChar(4),类型C VarChar(20))");
// sbCreateTableSql.Append("Create table 通用组态(编号 VarChar(4),动作常数 VarChar(4),楼号A VarChar(4),区号A VarChar(4),层号A1 VarChar(4),层号A2 VarChar(4),类型A VarChar(20),分类C VarChar(4),楼号C VarChar(4),区号C VarChar(4),层号C VarChar(4),类型C VarChar(20))");
//sbCreateTableSql.Clear();
//sbCreateTableSql.Append("alter table 文件配置 drop 器件长度;");
//_msDBConn.ExecuteBySql(sbCreateTableSql);
// sbCreateTableSql.Clear();
sbCreateTableSql.Append("update 文件配置 set 文件版本=6;");
//_msDBConn.ExecuteBySql(sbCreateTableSql);
//sbCreateTableSql.Clear();
//sbCreateTableSql.Append("alter table 0101 drop texing;");
_msDBConn.ExecuteBySql(sbCreateTableSql);
}
/// <summary>
/// 更新器件信息
/// </summary>
public static void UpdateDeviceInfo()
{
const string strDBFile = @"E:\receive\李宏明\图形显示装置和传设置软件异常情况汇总\湖南异常反馈\2016年11月22邵东凤凰城为启动声光回传.mdb";
const string strDB2File = @"E:\receive\李宏明\图形显示装置和传设置软件异常情况汇总\湖南异常反馈\2016年11月22邵东凤凰城为启动声光回传_new.mdb";
DataTable objDT = new DataTable();
StringBuilder sbCreateTableSql = new StringBuilder();
_msDBConn = new SCA.DatabaseAccess.MSAccessDatabaseAccess(strDBFile, _logHelper, _fileService);
//foreach(string type in addList)
//{
// sbCreateTableSql.Clear();
// sbCreateTableSql.Append(type);
// _msDBConn.ExecuteBySql(sbCreateTableSql);
//}
sbCreateTableSql.Clear();
sbCreateTableSql.Append("select 回路 from 系统设置;");
//sbCreateTableSql.Append("Create table 网络手控盘(编号 VarChar(4),板卡号 VarChar(4), 盘号 VarChar(4),键号 VarChar(4),地编号 VarChar(7),sdpkey VarChar(5))");
//sbCreateTableSql.Append("Create table 混合组态(编号 VarChar(4),动作常数 VarChar(4),动作类型 VarChar(4),分类A VarChar(4),楼号A VarChar(4),区号A VarChar(4),层号A VarChar(4),类型A VarChar(20),分类B VarChar(4),楼号B VarChar(4),区号B VarChar(4),层号B VarChar(4),类型B VarChar(20),分类C VarChar(4),楼号C VarChar(4),区号C VarChar(4),层号C VarChar(4),类型C VarChar(20))");
// sbCreateTableSql.Append("Create table 通用组态(编号 VarChar(4),动作常数 VarChar(4),楼号A VarChar(4),区号A VarChar(4),层号A1 VarChar(4),层号A2 VarChar(4),类型A VarChar(20),分类C VarChar(4),楼号C VarChar(4),区号C VarChar(4),层号C VarChar(4),类型C VarChar(20))");
System.Text.StringBuilder updateSQL = new StringBuilder();
DataTable data = _msDBConn.GetDataTableBySQL(sbCreateTableSql);
List <string> lstLoopName = new List <string>();
for (int i = 0; i < data.Rows.Count; i++)
{
lstLoopName.Add(data.Rows[i][0].ToString());
}
foreach (string name in lstLoopName)
{
sbCreateTableSql.Clear();
sbCreateTableSql.Append("select * from " + name);
data = _msDBConn.GetDataTableBySQL(sbCreateTableSql);
_msDBConn.GetDataTableBySQL(sbCreateTableSql);
for (int i = 0; i < data.Rows.Count; i++)
{
updateSQL.Append("Insert into " + name + "(bianhao,leixing,geli,lingmd,shuchu1,shuchu2,shuchu3,yanshi,xianggh,panhao,jianhao,gbzone,louhao,quhao,cenghao,fangjianhao,didian,cleixing,sdpkey)");
updateSQL.Append(" values('" + data.Rows[i]["bianhao"].ToString() + "','" + data.Rows[i]["leixing"].ToString() + "','" + data.Rows[i]["geli"].ToString() + "','" + data.Rows[i]["lingmd"].ToString() + "','");
updateSQL.Append(data.Rows[i]["shuchu1"].ToString() + "','" + data.Rows[i]["shuchu2"].ToString() + "','" + data.Rows[i]["shuchu3"].ToString() + "','" + data.Rows[i]["yanshi"].ToString() + "','");
updateSQL.Append(data.Rows[i]["xianggh"].ToString() + "','" + data.Rows[i]["panhao"].ToString() + "','" + data.Rows[i]["jianhao"].ToString() + "','" + data.Rows[i]["gbzone"].ToString() + "','");
updateSQL.Append(data.Rows[i]["louhao"].ToString() + "','" + data.Rows[i]["quhao"].ToString() + "','" + data.Rows[i]["cenghao"].ToString() + "','" + data.Rows[i]["fangjianhao"].ToString() + "','");
updateSQL.Append(data.Rows[i]["didian"].ToString() + "','" + data.Rows[i]["cleixing"].ToString() + "','" + data.Rows[i]["sdpkey"].ToString() + "');");
}
}
_msDBConn = new SCA.DatabaseAccess.MSAccessDatabaseAccess(strDB2File, _logHelper, _fileService);
//for (int i = 0; i < lstLoopName.Count; i++)
//{
// updateSQL.Append("Insert into 系统设置(回路,总数) values("+lstLoopName[i]+","+lstNodeNum[i]+");");
//}
updateSQL.Remove(updateSQL.Length - 1, 1);
_msDBConn.ExecuteBySql(updateSQL);
}
public void ExecuteBySqlTest()
{
StringBuilder sbCreateTableSql = new StringBuilder();
//附属信息
sbCreateTableSql.Append("select count(*) from 文件配置");
_dbConn = new DatabaseAccess.MSAccessDatabaseAccess(strDBFile, _logHelper, _fileService);
int returnValue = _dbConn.ExecuteBySql(sbCreateTableSql);
Assert.AreEqual(0, returnValue);
}
/// <summary>
/// 系统设置表
/// </summary>
public static void UpdateSystemSetting()
{
StringBuilder sbCreateTableSql = new StringBuilder();
const string strDBFile = @"E:\receive\李宏明\图形显示装置和传设置软件异常情况汇总\湖南异常反馈\2016年11月22邵东凤凰城为启动声光回传.mdb";
const string strDB2File = @"E:\receive\李宏明\图形显示装置和传设置软件异常情况汇总\湖南异常反馈\2016年11月22邵东凤凰城为启动声光回传_new.mdb";
_msDBConn = new SCA.DatabaseAccess.MSAccessDatabaseAccess(strDBFile, _logHelper, _fileService);
//foreach(string type in addList)
//{
// sbCreateTableSql.Clear();
// sbCreateTableSql.Append(type);
// _msDBConn.ExecuteBySql(sbCreateTableSql);
//}
sbCreateTableSql.Clear();
sbCreateTableSql.Append("select * from 系统设置;");
//sbCreateTableSql.Append("Create table 网络手控盘(编号 VarChar(4),板卡号 VarChar(4), 盘号 VarChar(4),键号 VarChar(4),地编号 VarChar(7),sdpkey VarChar(5))");
//sbCreateTableSql.Append("Create table 混合组态(编号 VarChar(4),动作常数 VarChar(4),动作类型 VarChar(4),分类A VarChar(4),楼号A VarChar(4),区号A VarChar(4),层号A VarChar(4),类型A VarChar(20),分类B VarChar(4),楼号B VarChar(4),区号B VarChar(4),层号B VarChar(4),类型B VarChar(20),分类C VarChar(4),楼号C VarChar(4),区号C VarChar(4),层号C VarChar(4),类型C VarChar(20))");
// sbCreateTableSql.Append("Create table 通用组态(编号 VarChar(4),动作常数 VarChar(4),楼号A VarChar(4),区号A VarChar(4),层号A1 VarChar(4),层号A2 VarChar(4),类型A VarChar(20),分类C VarChar(4),楼号C VarChar(4),区号C VarChar(4),层号C VarChar(4),类型C VarChar(20))");
DataTable data = _msDBConn.GetDataTableBySQL(sbCreateTableSql);
System.Text.StringBuilder updateSQL = new StringBuilder();
int rows = data.Rows.Count;
List <String> lstLoopName = new List <string>();
List <String> lstNodeNum = new List <string>();
for (int i = 0; i < data.Rows.Count; i++)
{
string strName = data.Rows[i]["回路"].ToString();
lstLoopName.Add(strName);
lstNodeNum.Add(data.Rows[i]["总数"].ToString());
}
_msDBConn = new SCA.DatabaseAccess.MSAccessDatabaseAccess(strDB2File, _logHelper, _fileService);
for (int i = 0; i < lstLoopName.Count; i++)
{
updateSQL.Append("Insert into 系统设置(回路,总数) values(" + lstLoopName[i] + "," + lstNodeNum[i] + ");");
}
updateSQL.Remove(updateSQL.Length - 1, 1);
_msDBConn.ExecuteBySql(updateSQL);
}
/// <summary>
/// 器件组态更新
/// </summary>
public static void UpdateGeneralLinkage()
{
StringBuilder sbCreateTableSql = new StringBuilder();
const string strDBFile = @"E:\receive\李宏明\图形显示装置和传设置软件异常情况汇总\湖南异常反馈\2016年11月22邵东凤凰城为启动声光回传.mdb";
const string strDB2File = @"E:\receive\李宏明\图形显示装置和传设置软件异常情况汇总\湖南异常反馈\2016年11月22邵东凤凰城为启动声光回传_new.mdb";
_msDBConn = new SCA.DatabaseAccess.MSAccessDatabaseAccess(strDBFile, _logHelper, _fileService);
//foreach(string type in addList)
//{
// sbCreateTableSql.Clear();
// sbCreateTableSql.Append(type);
// _msDBConn.ExecuteBySql(sbCreateTableSql);
//}
sbCreateTableSql.Clear();
sbCreateTableSql.Append("select * from 器件组态;");
//sbCreateTableSql.Append("Create table 网络手控盘(编号 VarChar(4),板卡号 VarChar(4), 盘号 VarChar(4),键号 VarChar(4),地编号 VarChar(7),sdpkey VarChar(5))");
//sbCreateTableSql.Append("Create table 混合组态(编号 VarChar(4),动作常数 VarChar(4),动作类型 VarChar(4),分类A VarChar(4),楼号A VarChar(4),区号A VarChar(4),层号A VarChar(4),类型A VarChar(20),分类B VarChar(4),楼号B VarChar(4),区号B VarChar(4),层号B VarChar(4),类型B VarChar(20),分类C VarChar(4),楼号C VarChar(4),区号C VarChar(4),层号C VarChar(4),类型C VarChar(20))");
// sbCreateTableSql.Append("Create table 通用组态(编号 VarChar(4),动作常数 VarChar(4),楼号A VarChar(4),区号A VarChar(4),层号A1 VarChar(4),层号A2 VarChar(4),类型A VarChar(20),分类C VarChar(4),楼号C VarChar(4),区号C VarChar(4),层号C VarChar(4),类型C VarChar(20))");
DataTable data = _msDBConn.GetDataTableBySQL(sbCreateTableSql);
System.Text.StringBuilder updateSQL = new StringBuilder();
for (int i = 0; i < data.Rows.Count; i++)
{
updateSQL.Append("Insert into 器件组态(输出组号,编号1,编号2,编号3,编号4,编号5,编号6,编号7,编号8,编号9,编号10,动作常数,联动组1,联动组2,联动组3) ");
updateSQL.Append("values('" + data.Rows[i]["输出组号"].ToString() + "','" + data.Rows[i]["编号1"].ToString() + "','" + data.Rows[i]["编号2"].ToString() + "','");
updateSQL.Append(data.Rows[i]["编号3"].ToString() + "','" + data.Rows[i]["编号4"].ToString() + "','" + data.Rows[i]["编号5"].ToString() + "','" + data.Rows[i]["编号6"].ToString() + "','");
updateSQL.Append(data.Rows[i]["编号7"].ToString() + "','" + data.Rows[i]["编号8"].ToString() + "','" + data.Rows[i]["编号9"].ToString() + "','" + data.Rows[i]["编号10"].ToString() + "','");
updateSQL.Append(data.Rows[i]["动作常数"].ToString() + "','" + data.Rows[i]["联动组1"].ToString() + "','" + data.Rows[i]["联动组2"].ToString() + "','" + data.Rows[i]["联动组3"].ToString() + "');");
}
_msDBConn = new SCA.DatabaseAccess.MSAccessDatabaseAccess(strDB2File, _logHelper, _fileService);
//for (int i = 0; i < lstLoopName.Count; i++)
//{
// updateSQL.Append("Insert into 系统设置(回路,总数) values("+lstLoopName[i]+","+lstNodeNum[i]+");");
//}
updateSQL.Remove(updateSQL.Length - 1, 1);
_msDBConn.ExecuteBySql(updateSQL);
}
/// <summary>
/// 根据系统配置信息表，创建回路表
/// </summary>
public static void CreateLoopName()
{
StringBuilder sbCreateTableSql = new StringBuilder();
const string strDBFile = @"E:\receive\李宏明\图形显示装置和传设置软件异常情况汇总\湖南异常反馈\2016年11月22邵东凤凰城为启动声光回传.mdb";
const string strDB2File = @"E:\receive\李宏明\图形显示装置和传设置软件异常情况汇总\湖南异常反馈\2016年11月22邵东凤凰城为启动声光回传_new.mdb";
_msDBConn = new SCA.DatabaseAccess.MSAccessDatabaseAccess(strDBFile, _logHelper, _fileService);
//foreach(string type in addList)
//{
// sbCreateTableSql.Clear();
// sbCreateTableSql.Append(type);
// _msDBConn.ExecuteBySql(sbCreateTableSql);
//}
sbCreateTableSql.Clear();
sbCreateTableSql.Append("select 回路 from 系统设置;");
//sbCreateTableSql.Append("Create table 网络手控盘(编号 VarChar(4),板卡号 VarChar(4), 盘号 VarChar(4),键号 VarChar(4),地编号 VarChar(7),sdpkey VarChar(5))");
//sbCreateTableSql.Append("Create table 混合组态(编号 VarChar(4),动作常数 VarChar(4),动作类型 VarChar(4),分类A VarChar(4),楼号A VarChar(4),区号A VarChar(4),层号A VarChar(4),类型A VarChar(20),分类B VarChar(4),楼号B VarChar(4),区号B VarChar(4),层号B VarChar(4),类型B VarChar(20),分类C VarChar(4),楼号C VarChar(4),区号C VarChar(4),层号C VarChar(4),类型C VarChar(20))");
// sbCreateTableSql.Append("Create table 通用组态(编号 VarChar(4),动作常数 VarChar(4),楼号A VarChar(4),区号A VarChar(4),层号A1 VarChar(4),层号A2 VarChar(4),类型A VarChar(20),分类C VarChar(4),楼号C VarChar(4),区号C VarChar(4),层号C VarChar(4),类型C VarChar(20))");
DataTable data = _msDBConn.GetDataTableBySQL(sbCreateTableSql);
System.Text.StringBuilder updateSQL = new StringBuilder();
int rows = data.Rows.Count;
List <String> lstLoopName = new List <string>();
for (int i = 0; i < data.Rows.Count; i++)
{
string strName = data.Rows[i]["回路"].ToString();
lstLoopName.Add(strName);
}
_msDBConn = new SCA.DatabaseAccess.MSAccessDatabaseAccess(strDB2File, _logHelper, _fileService);
foreach (string strName in lstLoopName)
{
updateSQL.Append("Create table " + strName + "(bianhao varchar(7),leixing varchar(3),geli varchar(1),lingmd varchar(1),shuchu1 varchar(4),shuchu2 varchar(4), shuchu3 varchar(4),yanshi varchar(3),xianggh varchar(5), panhao varchar(3), jianhao varchar(3),cleixing varchar(15), GbZone varchar(3),didian varchar(25),louhao varchar(3),quhao varchar(3),cenghao varchar(3),fangjianhao varchar(3),sdpkey varchar(5));");
}
_msDBConn.ExecuteBySql(updateSQL);
}