Access数据库表生成SQL语句以及表结构的分析

先贴上效果图

主窗体

生成SQL语句的窗体

贴上源码

主窗体Form1代码：

private string strCurDb = "";
  private string strCurTb = "";
  private OleDbConnection Connection = null;
  private Hashtable htDb = new Hashtable();
  private Hashtable htPrimaryKey = new Hashtable();
  private Hashtable htForeignKey = new Hashtable();
  
  private void Reset()
  {
   strCurDb = "";
   strCurTb = "";
   Connection = null;
   htPrimaryKey.Clear();
   htForeignKey.Clear();
   htDb.Clear();
  }

  private void btnSelect_Click(object sender, System.EventArgs e)
  {

   OpenFileDialog ofd = new OpenFileDialog();
   ofd.Multiselect = false;
   ofd.Title = "请选择Access数据库";
   ofd.Filter = "MDB文件(*.mdb)|*.mdb";
   if(DialogResult.OK == ofd.ShowDialog())
   {
    if(ofd.FileName != "")
    {
     Reset();
     strCurDb = ofd.FileName;
     txtAccessDb.Text = strCurDb;
     dataGridDbInfo.CaptionText = "数据库【" + Path.GetFileNameWithoutExtension(strCurDb) + "】信息";
     lblDbTables.Text = "数据库【" + Path.GetFileNameWithoutExtension(strCurDb) + "】中的表";
     GetDbInfo();
    }
   }
  }

  private void GetDbInfo()
  {
   
   try
   {
    string strMdb = strCurDb ;//mdb路径
    Connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMdb + "");
    Connection.Open();
   
    GetPrimaryKey();
    GetForeignKey();

    DataTable schemaTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    
    // 设置当前数据库信息
    this.dataGridDbInfo.DataSource = schemaTable.DefaultView;

    foreach (DataRow dr in schemaTable.Rows)
    {
   
     
     Hashtable htTb = new Hashtable(); 

     // 字段名  
     DataTable columnTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, dr["TABLE_NAME"].ToString(), null });
 
 
     foreach (DataRow dr2 in columnTable.Rows)
     {
      FieldInfo fi = new FieldInfo();
      fi.DataType = dr2["DATA_TYPE"].ToString();
      fi.IsNull = Convert.ToInt16(dr2["IS_NULLABLE"]);
      fi.IsHasDefault = Convert.ToInt16(dr2["COLUMN_HASDEFAULT"]) ;
      fi.DefaultVal = dr2["COLUMN_DEFAULT"].ToString() ;
      fi.Length = dr2["CHARACTER_MAXIMUM_LENGTH"].ToString();

         htTb.Add(dr2["COLUMN_NAME"],fi);
     }

     htDb.Add(dr["TABLE_NAME"].ToString(),htTb);

     
    }
    bindTbs();
    bindTbContent();
   }
   catch(System.Exception ex)
   {
    MessageBox.Show(ex.ToString());
   }
  }

  
  private void bindTbs() // 绑定表
  {
   if(htDb.Count > 0)
   {
    listBox1.Items.Clear();
    
    listBox1.DataSource = new ArrayList(htDb.Keys);
    listBox1.SelectedIndex = 0;
    bindTbInfo();
   }
    
   
  }

  private void bindTbInfo() // 绑定表信息
  {
   listView1.Items.Clear();
   listView1.Columns.Clear();

   strCurTb = listBox1.SelectedItem.ToString();

   Hashtable h = (Hashtable)htDb[strCurTb];
   listView1.Columns.Add("字段名称",100,System.Windows.Forms.HorizontalAlignment.Left );
   listView1.Columns.Add("字段类型",100,System.Windows.Forms.HorizontalAlignment.Left);
   listView1.Columns.Add("类型描述",100,System.Windows.Forms.HorizontalAlignment.Left);
   listView1.Columns.Add("是否是主键",100,System.Windows.Forms.HorizontalAlignment.Left);
   listView1.Columns.Add("是否可以为空",100,System.Windows.Forms.HorizontalAlignment.Left);
   listView1.Columns.Add("缺省值",100,System.Windows.Forms.HorizontalAlignment.Left);
    
   ListViewItem [] lviArr;
   lviArr = new ListViewItem [h.Keys.Count];

   int index = 0;
   foreach(string field in h.Keys)
   {
    FieldInfo fi = (FieldInfo)h[field];
    lviArr[index] = new ListViewItem();
    lviArr[index].SubItems[0].Text = field;
    lviArr[index].SubItems.Add(GetDataType(fi.DataType));
    lviArr[index].SubItems.Add(GetDataTypeNote(fi.DataType));

    if(htPrimaryKey.Contains(strCurTb))
    {
     if(field == htPrimaryKey[strCurTb].ToString())lviArr[index].SubItems.Add("是");
     else lviArr[index].SubItems.Add("否");
    }
    
    if(fi.IsNull == 1)lviArr[index].SubItems.Add("是");
    else lviArr[index].SubItems.Add("否");

    if(fi.IsHasDefault == 1)lviArr[index].SubItems.Add(fi.DefaultVal);
    index ++;
   }
   listView1.BeginUpdate();
   listView1.Items.AddRange(lviArr);
   listView1.EndUpdate();
  }

  private void bindTbContent() // 绑定表内容
  {
   if(Connection == null) return;
   if(strCurTb == "")return;
   dataGridTbInfo.CaptionText = "表【" + strCurTb + "】内容";
   string strCmd = "select * from " + strCurTb;
   OleDbDataAdapter oda = new OleDbDataAdapter(strCmd,Connection);
   DataSet ds = new DataSet();
   oda.Fill(ds);
   dataGridTbInfo.DataSource = ds.Tables[0].DefaultView;
  }

  private void bindTbStruct()
  {
   // 字段名  
   DataTable columnTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);
   dataGridTbInfo.DataSource = columnTable.DefaultView;
  }

  private void GetPrimaryKey()
  {
   // 字段名  
   DataTable columnTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
   foreach(DataRow dr in columnTable.Rows)
   {
    htPrimaryKey.Add(dr["TABLE_NAME"],dr["COLUMN_NAME"]);
   }
  }

  private void GetForeignKey()
  {
   // 字段名  
   DataTable columnTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
   foreach(DataRow dr in columnTable.Rows)
   {
    htForeignKey.Add(dr["TABLE_NAME"],dr["COLUMN_NAME"]);
   }
  }

  private void Form1_Load(object sender, System.EventArgs e)
  {
   listView1.View = View.Details;
   lblDbTables.Text = "";
   lblTbDataType.Text = "";
  }

  private void listBox1_SelectedIndexChanged(object sender, System.EventArgs e)
  {
   strCurTb = listBox1.SelectedItem.ToString();
   
   lblTbDataType.Text = "表【" + strCurTb + "】结构";

   try
   {
    bindTbInfo();
    bindTbContent();
    //bindTbStruct(strCurTb);
   }
   catch(System.Exception ex)
   {
    MessageBox.Show(ex.ToString());
   }
  }
  
  private string GetDataTypeNote(string arg)
  {
   switch(arg)
   {
    case "3":
     return "自动编号/数字";
    case "6":
     return "货币";
    case "7":
     return "日期/时间";
    case "11":
     return "是/否";
    case "130":
     return "文本";
    case "203":
     return  "备注/超链接";
    case "205":
     return "OLE对象";
   }
   return  "未知";
  }

  private string GetDataType(string arg)
  {
   switch(arg)
   {
    case "2":
     return "SmallInt"; // 整型
    case "3":
     return "Int";    // 长整型
    case "4":
     return "Real";     // 单精度型
    case "5":
     return "Float";    // 双精度型
    case "6":
     return "Money";    // 货币
    case "7": case "133":
     return "DateTime"; // 日期时间
    case "11":
     return "Bit";      // 是否
    case "13":
     return "TimeStamp";
    case "17":
     return "TinyInt";   // 字节
    case "72":
     return "UniqueIdentifier";     // 同步复制 ID
    case "128": case "204":
     return "Binary";  // 二进制
    case "129":
     return "Char";   
    case "130":
     return "NChar";
    case "131":
     return "Decimal";
    case "135":
     return "SmallDateTime";
    case "200":
     return "VarChar";
    case "201": case "203":
     return "Text";
    case "202":
     return "VarChar";
    case "205":
     return "Image";
   }
   return "VarChar";
  }

  
  private string GetDefaultValue(string arg)
  {
   return "";
  }

  private void btn_SQL_Create_Click(object sender, System.EventArgs e)
  {

   if(strCurDb == "")
   {
    MessageBox.Show("您还未选择数据库！请先选择数据库",this.Text);
    return;
   }
   FrmToSQL frmSql = new FrmToSQL(this);
   frmSql.ShowDialog();

  }

  // 外部调用
  public ArrayList GetTbs() // 获取表
  {
   if(htDb.Count > 0)
    return new ArrayList(htDb.Keys);
   return null;
  }

  public string ToSql(string strTbName) // 生成创建表的SQL语句
  {
   if(htDb.Count < 0) return "";

   Hashtable h = (Hashtable)htDb[strTbName];

   StringBuilder sql = new StringBuilder();
   sql.Append("create table " + strTbName + "\r\n");
   sql.Append("("+"\r\n");

   foreach(string field in h.Keys)
   {

    FieldInfo fi = (FieldInfo)h[field];
    string dataType =  GetDataType(fi.DataType) ;
    string text = "[" + field + "] \t" + dataType;
    if(dataType.ToUpper() == "CHAR" || dataType.ToUpper() == "NCHAR" || dataType.ToUpper() == "VARCHAR")
    {
     text +=  "(" + fi.Length + ")";
    }
    if(fi.IsNull == 0)
     text += "  NOT NULL";
    if(htPrimaryKey.Contains(strTbName))
     if(field == htPrimaryKey[strTbName].ToString())text += "  Primary Key";
    if(fi.IsHasDefault == 1)
     text += "  default " + fi.DefaultVal.Replace("\"", "'");

    text += ", \r\n";
    sql.Append(text);
   }

   sql.Append(")");
   int index = sql.ToString().LastIndexOf(",");
   return sql.ToString().Remove(index,1).ToUpper() + "\r\n\r\n";
  }

字段信息类：

public class FieldInfo
 {

  public int IsNull = 0;
  public int IsHasDefault = 0;
  public string DataType = "";
  public string DefaultVal = "";
  public string Length = "";
  public FieldInfo(){  }

 }

生成SQL语句窗体代码：

private void FrmToSQL_Load(object sender, System.EventArgs e)
  {
   rbtnSelectAll.Enabled = false;
   rbtnCancel.Enabled = false;
   ArrayList list = frm.GetTbs();
   if(list != null)
   {
    checkedListBox1.Items.AddRange(list.ToArray());
    rbtnSelectAll.Enabled = true;
    rbtnCancel.Enabled = true;  
   }
  }

  private void btnDo_Click(object sender, System.EventArgs e)
  {
   if(checkedListBox1.CheckedItems.Count == 0)
   {
    MessageBox.Show("请先选取表！",this.Text);
    return;
   }
   string strSql = "";
   for(int i=0; i<checkedListBox1.CheckedItems.Count; i++)
   {
    strSql += frm.ToSql(checkedListBox1.CheckedItems[i].ToString());
   }

   SaveFileDialog sfd = new SaveFileDialog();
   sfd.Title = "保存SQL文件";
   sfd.Filter = "TEXT文件(*.txt)|*.txt";
   sfd.FileName = "SQL_TABLES";
   if(DialogResult.OK == sfd.ShowDialog())
   {
    if(sfd.FileName != "")
    {
     
     StreamWriter sw =new StreamWriter(sfd.FileName,false,Encoding.Unicode);
     sw.Write(strSql);
     sw.Close();

    }
   }
  }

  private void btnClose_Click(object sender, System.EventArgs e)
  {
   this.Close();
  }

  private void rbtnSelectAll_CheckedChanged(object sender, System.EventArgs e)
  {
   for(int i=0; i<checkedListBox1.Items.Count; i++)
   {
    checkedListBox1.SetItemChecked(i,true);
   }
  }

  private void rbtnCancel_CheckedChanged(object sender, System.EventArgs e)
  {
   for(int i=0; i<checkedListBox1.Items.Count; i++)
   {
    checkedListBox1.SetItemChecked(i,false);
   }

  }