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);
}
}
