实验室和公司中存在两个同质的数据库,两个数据库都有不同的人使用,需要保持两个数据库的结构同步,一下代码是通过翻译数据字典语言,实现简单的操作数据库,增加列等。
public void CreateColumn(string sheetName)
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + strcims + ";Extended Properties=Excel 8.0";
string strConComac = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + strShangfei + ";Extended Properties=Excel 8.0";
DataTable SheetTable = new DataTable();
DataTable SheetTableComac = new DataTable();
SheetTable = GetSheetTable(strCon, sheetName);
SheetTableComac = GetSheetTable(strConComac, sheetName);
for (int m = 0; m < SheetTableComac.Rows.Count; m++)
{
bool columnExist = false;//标志该表在实验中是否存在相同的列名
int NO = 0;//找出该实验室表中相同列的号码
for (int n = 0; n < SheetTable.Rows.Count; n++)
{
string str = SheetTableComac.Rows[m]["字段名"].ToString().Trim();
string str1 = SheetTable.Rows[n]["字段名"].ToString().Trim();
if (SheetTableComac.Rows[m]["字段名"].ToString() == SheetTable.Rows[n]["字段名"].ToString())//该列名存在
{
columnExist = true;
NO = n;
break;
}
}
/如果存在,比较是不有改变
if (columnExist)
{
//该列在公司表中的信息
string ColumnName = SheetTableComac.Rows[m]["字段名"].ToString().Trim();
string ColumnDescption = SheetTableComac.Rows[m]["字段说明"].ToString().Trim();
string ColumnAdentity = SheetTableComac.Rows[m]["标识"].ToString().Trim();
string ColumnKey = SheetTableComac.Rows[m]["主键"].ToString().Trim();
string ColumnType = SheetTableComac.Rows[m]["类型"].ToString().Trim();
string ColumnLength = SheetTableComac.Rows[m]["字段长度"].ToString().Trim();
string ColumnPrecision = SheetTableComac.Rows[m]["精度"].ToString().Trim();
string ColumnDecimal = SheetTableComac.Rows[m]["小数位数"].ToString().Trim();
string ColumnNull = SheetTableComac.Rows[m]["允许空"].ToString().Trim();
//该列在实验室表中的信息
string liColumnName = SheetTable.Rows[NO]["字段名"].ToString().Trim();
string liColumnDescption = SheetTable.Rows[NO]["字段说明"].ToString().Trim();
string liColumnAdentity = SheetTable.Rows[NO]["标识"].ToString().Trim();
string liColumnKey = SheetTable.Rows[NO]["主键"].ToString().Trim();
string liColumnType = SheetTable.Rows[NO]["类型"].ToString().Trim();
string liColumnLength = SheetTable.Rows[NO]["字段长度"].ToString().Trim();
string liColumnPrecision = SheetTable.Rows[NO]["精度"].ToString().Trim();
string liColumnDecimal = SheetTable.Rows[NO]["小数位数"].ToString().Trim();
string liColumnNull = SheetTable.Rows[NO]["允许空"].ToString().Trim();
string ModifyColumn = string.Empty;
//修改字符的长度;
if (ColumnType.Trim() == "nvarchar" || ColumnType.Trim() == "varchar" || ColumnType.Trim() == "char" || ColumnType.Trim() == "nchar" || ColumnType.Trim() == "binary" || ColumnType.Trim() == "varbinary" || ColumnType.Trim() == "float")
{
if (ColumnType.Trim() == liColumnType.Trim() && ColumnPrecision.Trim() != liColumnPrecision.Trim())
{
if (ColumnPrecision.Trim() == "-1")
{
ColumnPrecision = "max";
}
ModifyColumn += "[" + ColumnType + "] " + "(" + ColumnPrecision + ") ";
}
}
else if (ColumnType == "decimal") //decimal时修改精度
{
if (ColumnType == liColumnType && (ColumnDecimal != liColumnDecimal || ColumnLength != liColumnLength))
ModifyColumn += "[" + ColumnType + "] " + "(" + ColumnPrecision + "," + ColumnDecimal + ") ";
}
///为空的时候是否相同
if(ColumnNull!=liColumnNull)
{
if (ColumnNull.Trim() != "")
{
ColumnNull = " NULL";
}
else
{
ColumnNull = " NOT NULL";
}
ModifyColumn+=ColumnNull;
}
if(ModifyColumn.Trim() !="")
{
string temp = SheetTableComac.Rows[m]["字段名"].ToString();// 获取表名;
string modifyHead = "ALTER TABLE " + sheetName + " ALTER COLUMN "+temp+" ";
ModifyColumn=modifyHead+ModifyColumn;
ExecuteNonQueryFunc(ModifyColumn);
}
//修改列的说明;
if (ColumnDescption.Trim() != "" && liColumnDescption.Trim() == "")
{
string columnName= SheetTableComac.Rows[m]["字段名"].ToString();
string ExecuteSql=ADDColumnDes(sheetName, columnName, ColumnDescption);
ExecuteNonQueryFunc(ExecuteSql);
}
}
else如果不存在添加新新的列
{
string ColumnName = SheetTableComac.Rows[m]["字段名"].ToString();
string ColumnDescption = SheetTableComac.Rows[m]["字段说明"].ToString();
string ColumnAdentity = SheetTableComac.Rows[m]["标识"].ToString();
string ColumnKey = SheetTableComac.Rows[m]["主键"].ToString();
string ColumnType = SheetTableComac.Rows[m]["类型"].ToString();
string ColumnLength = SheetTableComac.Rows[m]["字段长度"].ToString();
string ColumnPrecision = SheetTableComac.Rows[m]["精度"].ToString();
string ColumnDecimal = SheetTableComac.Rows[m]["小数位数"].ToString();
string ColumnNull = SheetTableComac.Rows[m]["允许空"].ToString();
string AddColumn = "alter table" + " [" + sheetName + "] Add " + SheetTableComac.Rows[m]["字段名"].ToString() + " ";
if (ColumnType == "int")
{
AddColumn += "[" + ColumnType + "] ";
}
else if (ColumnType == "nvarchar" || ColumnType == "varchar" || ColumnType == "char" || ColumnType == "nchar" || ColumnType == "binary" || ColumnType == "varbinary" || ColumnType == "float")
{
if (ColumnPrecision.Trim() == "-1")
{
ColumnPrecision = "max";
}
AddColumn += "[" + ColumnType + "] " + "(" + ColumnPrecision + ") ";
}
else if (ColumnType == "decimal")
{
AddColumn += "[" + ColumnType + "] " + "(" + ColumnPrecision + "," + ColumnDecimal + ") ";
}
else
{
AddColumn += "[" + ColumnType + "] ";
}
//此处待完善,一个表只有一个自增的的ID列
是否是标识符子增加
if (ColumnAdentity == "√")
{
AddColumn += " IDENTITY(1,1) ";
}
//判断是否可以为空
if (ColumnNull.Trim() == "")
{
AddColumn += " NOT NULL ";
}
else
{
AddColumn += " NULL ";
}
string temp = AddColumn;
ExecuteNonQueryFunc(temp);
}
}
}
修改数据表中更改的列,考虑不是很周到,MARK 一下。
public void ExecuteNonQueryFunc(string QueryStr) ///执行SQL语句
{
SqlConnection con = new SqlConnection(connectionString);
try
{
con.Open();
string str = string.Empty;
SqlCommand cmd = new SqlCommand();
cmd.CommandText = QueryStr;
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();
DateTime date = new DateTime();
date = System.DateTime.Now;
using (StreamWriter sw = File.AppendText(@"d:\同步日志文件.txt"))
{
sw.WriteLine(date + " " + "操作的数据库:" + textBox2.Text.Trim() + " 操作内容: " + QueryStr + "\r\n");
}
}
catch
{
con.Close();
MessageBox.Show("以下的信息同步失败,详情请看日志信息"+QueryStr);
DateTime date = new DateTime();
date = System.DateTime.Now;
using (StreamWriter sw = File.AppendText(@"d:\同步更新出错的日志文件.txt"))
{
// sw.WriteLine(date + " " + QueryStr + "\r\n");
sw.WriteLine(date + " " + "操作的数据库:" + textBox2.Text.Trim() +" 操作内容: "+ QueryStr + "\r\n");
}
}
}
执行函数。实现对数据库的操作
DateTime date = new DateTime();
date = System.DateTime.Now;
using (StreamWriter sw = File.AppendText(@"d:\同步日志文件.txt"))
{
sw.WriteLine(date + " " + "操作的数据库:" + textBox2.Text.Trim() + " 操作内容: " + QueryStr + "\r\n");
}
记录操作数据可的操作,queryStr是操作语句,记录在数据库文件中。主要记录的是操作语句和操作时间。textbox2.text记录的是操作数据库名称。