Access Database Table Generation SQL statement and Table Structure Analysis

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

}