Access Database Table Generation SQL statement and Table Structure Analysis

Source: Internet
Author: User

First Paste

Main form

Form for generating SQL statements

Paste source code

Main form form1Code:

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 = "Select Access Database";
ofd. filter = "MDB File (*. MDB) | *. mdb ";
If (dialogresult. OK = ofd. showdialog ()
{< br> If (OFD. filename! = "")
{< br> Reset ();
strcurdb = ofd. filename;
txtaccessdb. TEXT = strcurdb;
datagriddbinfo. captiontext = "database [" + path. getfilenamewithoutextension (strcurdb) + "] Information";
lbldbtables. TEXT = "database [" + path. getfilenamewithoutextension (strcurdb) + "] Table";
getdbinfo ();
}< BR >}

Private void getdbinfo ()
{

Try
{
String strmdb = strcurdb; // MDB path
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, "table "});

// Set the current database information
This. datagriddbinfo. datasource = schematable. defaultview;

foreach (datarow DR in schematable. Rows)
{< br>
hashtable httb = new hashtable ();

// field name
datatable columntable = connection. getoledbschematable (oledbschemaguid. columns, new object [] {null, null, Dr ["table_name"]. tostring (), null});
foreach (datarow DR2 in columntable. rows)
{< br> 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 () // bind table
{
If (htdb. Count> 0)
{
Listbox1.items. Clear ();

Listbox1.datasource = new arraylist (htdb. Keys );
Listbox1.selectedindex = 0;
Bindtbinfo ();
}


}

Private void bindtbinfo () // bind table information
{
Listview1.items. Clear ();
Listview1.columns. Clear ();

Strcurtb = listbox1.selecteditem. tostring ();

hashtable H = (hashtable) htdb [strcurtb];
listview1.columns. add ("field name", 100, system. windows. forms. horizontalalignment. left);
listview1.columns. add ("field type", 100, system. windows. forms. horizontalalignment. left);
listview1.columns. add ("type description", 100, system. windows. forms. horizontalalignment. left);
listview1.columns. add ("whether it is the primary key", 100, system. windows. forms. horizontalalignment. left);
listview1.columns. add ("whether it can be blank", 100, system. windows. forms. horizontalalignment. left);
listview1.columns. add ("Default Value", 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 ("yes ");
Else lviarr [Index]. subitems. Add ("no ");
}

If (Fi. isnull = 1) lviarr [Index]. subitems. Add ("yes ");
Else lviarr [Index]. subitems. Add ("no ");

If (Fi. ishasdefault = 1) lviarr [Index]. subitems. Add (Fi. defaultval );
Index ++;
}
Listview1.beginupdate ();
Listview1.items. addrange (lviarr );
Listview1.endupdate ();
}

Private void bindtbcontent () // bind table content
{
If (connection = NULL) return;
If (strcurtb = "") return;
Datagridtbinfo. captiontext = "table [" + strcurtb + "] content ";
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 ()
{
// Field name
Datatable columntable = connection. getoledbschematable (oledbschemaguid. columns, null );
Datagridtbinfo. datasource = columntable. defaultview;
}

Private void getprimarykey ()
{
// Field name
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 ()
{
// Field name
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 = "table [" + strcurtb + "] structure ";

Try
{
Bindtbinfo ();
Bindtbcontent ();
// Bindtbstruct (strcurtb );
}
Catch (system. Exception ex)
{
MessageBox. Show (ex. tostring ());
}
}

Private string getdatatypenote (string Arg)
{
Switch (ARG)
{
Case "3 ":
Return "automatic number/number ";
Case "6 ":
Return "currency ";
Case "7 ":
Return "date/time ";
Case "11 ":
Return "yes/no ";
Case "130 ":
Return "text ";
Case "203 ":
Return "remarks/HYPERLINK ";
Case "205 ":
Return "OLE object ";
}
Return "unknown ";
}

Private string getdatatype (string Arg)
{
Switch (ARG)
{
Case "2 ":
Return "smallint"; // integer
Case "3 ":
Return "int"; // a long integer
Case "4 ":
Return "real"; // Single-precision
Case "5 ":
Return "float"; // Double Precision type
Case "6 ":
Return "money"; // currency
Case "7": Case "133 ":
Return "datetime"; // Date and Time
Case "11 ":
Return "bit"; // whether
Case "13 ":
Return "timestamp ";
Case "17 ":
Return "tinyint"; // byte
Case "72 ":
Return "uniqueidentifier"; // synchronous replication ID
Example "128": Case "204 ":
Return "binary"; // binary
Case "129 ":
Return "char ";
Case "130 ":
Return "nchar ";
Case "131 ":
Return "decimal ";
Case "135 ":
Return "smalldatetime ";
Case "200 ":
Return "varchar ";
Example "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 ("You have not selected a database! Select Database ", this. Text );
Return;
}
Frmtosql frmsql = new frmtosql (this );
Frmsql. showdialog ();

}

 

// External call
Public arraylist gettbs () // obtain the table
{
If (htdb. Count> 0)
Return new arraylist (htdb. Keys );
Return NULL;
}

 

Public String tosql (string strtbname) // generate an SQL statement for creating a table
{
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")
{< br> text + = "(" + fi. length + ")";
}< br> 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 ";
}

 

Field Information:

Public class fieldinfo
{

Public int isnull = 0;
Public int ishasdefault = 0;
Public String datatype = "";
Public String defaultval = "";
Public String Length = "";
Public fieldinfo (){}

}

 

Generate the SQL statement form code:

private void frmtosql_load (Object sender, system. eventargs e)
{< br> rbtnselectall. enabled = false;
rbtncancel. enabled = false;
arraylist list = FRM. gettbs ();
If (list! = NULL)
{< br> checkedlistbox1.items. addrange (list. toarray ();
rbtnselectall. enabled = true;
rbtncancel. enabled = true;
}< BR >}

private void btndo_click (Object sender, system. eventargs e)
{< br> If (checkedlistbox1.checkeditems. count = 0)
{< br> MessageBox. show ("select a table first! ", This. text);
return;
}< br> string strsql = "";
for (INT I = 0; I {< br> strsql + = FRM. tosql (checkedlistbox1.checkeditems [I]. tostring ();
}

Savefiledialog SFD = new savefiledialog ();
SFD. Title = "Saving SQL Files ";
SFD. Filter = "text file (*. 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 );
}

}

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.