操作数据库表，并作日志记录。

实验室和公司中存在两个同质的数据库，两个数据库都有不同的人使用，需要保持两个数据库的结构同步，一下代码是通过翻译数据字典语言，实现简单的操作数据库，增加列等。

 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记录的是操作数据库名称。