C#处理access数据库结构.docx

C#处理access数据库结构using System;using System.Collections.Generic;using System.Text;using System.Data.OleDb;using System.Data;using System.Windows.Forms;namespace NewUserControl public class AccessTableCopy / / 获取两个数据库的路径 / / 老数据库的路径 / 新数据库的路径 public void GetTwoAccessConnection(string oldpath,string newpath) OleDbConnection newconnection; string connStr = Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + newpath + ;Persist Security Info=False; newconnection = new OleDbConnection(connStr); string oldconnStr = Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + oldpath + ;Persist Security Info=False; OleDbConnection oldconnection; oldconnection = new OleDbConnection(oldconnStr); this.BatchUpdate(newconnection, oldconnection, oldpath); / / 批量执行更新数据 / / 新数据库的连接 / 旧数据库的连接 / 老数据库的路径 public void BatchUpdate(OleDbConnection newconnection, OleDbConnection oldconnection,string oldpath) /获取旧数据库中的表列表 List oldtablelist =this.GetTableName(oldconnection); /获取新的数据库中的表 List newtablelist = this.GetTableName(newconnection); for(int i=0;ioldtablelist.Count;i+) string oldtablename = oldtablelisti; for(int j=0;j newtablelist.Count;j+) string newtablename = newtablelistj; /如果存在该表则直接插入数据,忽略大小写 if (oldtablename.ToLower() = newtablename.ToLower() /获取老数据库表中的表字段，并处理成符合sql标准 List listold = this.GetTableColumn(oldtablename, oldconnection); string oldcolumns = ; string columnname = new stringlistold.Count; foreach (string lst in listold) oldcolumns += lst + ,; oldcolumns = oldcolumns.Remove(oldcolumns.LastIndexOf(,), 1); oldcolumns.Trim(); /生成sql语句 string sql = ; sql = insert into + oldtablename + ( + oldcolumns + ) select + oldcolumns + from ;database= + oldpath + ;. + oldtablename + ; using (OleDbCommand command = new OleDbCommand(sql, newconnection) newconnection.Open(); command.ExecuteNonQuery(); newconnection.Close(); /获取指定表的主关键字段名称 string spiltname=this.PrimaryKeyName(oldtablename); /执行完批量更新以后删除重复的记录 this.DelMoreData(newtablename, spiltname, newconnection); break; / / 获取指定连接的数据库中的表列表 / / 指定数据库连接 / 数据库表中的列表 public List GetTableName(OleDbConnection conn) try conn.Open(); DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object null, null, null, TABLE ); conn.Close(); int n = dt.Rows.Count; string tableName = new stringn; int m = dt.Columns.IndexOf(TABLE_NAME); List list = new List(); for (int i = 0; i n; i+) DataRow dr = dt.Rowsi; list.Add(dr.ItemArray.GetValue(m).ToString(); return list; catch (Exception ex) throw ex; finally conn.Close(); / / 返回某一表的所有字段名 / / 待获取表的名称 / 数据库的连接 / 表字段列表 public List GetTableColumn(string tableName, OleDbConnection conn) List list = new List(); DataTable dt = new DataTable(); try conn.Open(); dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object null, null, tableName, null ); conn.Close(); int n = dt.Rows.Count; /获取字段数据的排列顺序 int j = dt.Columns.IndexOf(ORDINAL_POSITION); /string strTable = new stringn; int m = dt.Columns.IndexOf(COLUMN_NAME); for (int i = 0; i n; i+) DataRow dr = dt.Rowsi; list.Add(dr.ItemArray.GetValue(m).ToString(); if (dr.ItemArray.GetValue(j).ToString() = 1) list.Remove(dr.ItemArray.GetValue(m).ToString(); return list; catch (Exception ex) throw ex; finally conn.Close(); #region 删除重复记录集 / / 删除表中多余的字段 / / 表名称 / 关键字段 / 新表的连接 public void DelMoreData(string tablename, string splitname, OleDbConnection newconnection) string idname = this.GetTableColumnIndex(tablename, newconnection); string delsql = delete from +tablename + a where exists(select * from +tablename+ b where; for (int i = 0; i b. + idname + ); using (OleDbCommand command = new OleDbCommand(delsql, newconnection) newconnection.Open(); command.ExecuteNonQuery(); newconnection.Close(); / / 获取相应表的关键字段 / / 表名 / 返回该字段名称 public string PrimaryKeyName(string tablename) string prykeyname = ; if (tablename.ToLower() = DList.ToLower() prykeyname = WorkerId; if (prykeyname != ) string split = prykeyname.Split(new Char , ); return split; else return null; / / 获取一个表的自动编号字段 / / 表名称 / 新表的连接 / 自动编号字段的name public string GetTableColumnIndex(string tableName, OleDbConnection conn) DataTable dt = new DataTable(); string indexidname = ; try conn.Open(); dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object null, null, tableName, null ); conn.Close();