Posts Tagged ‘Table Design’

SQL Database Schema To Excel

March 3, 2010
   I searched lot of web site for document preparation for Database design
in excel format, at last I developed one web application for generating
SQL DB Schema to excel format , use the following code In page load
event of aspx page
protected void Page_Load(object sender, EventArgs e)
 {
 System.Data.SqlClient.SqlConnection con =
 new System.Data.SqlClient.SqlConnection
 (ConfigurationManager.ConnectionStrings["connectionStrings"].ConnectionString);
 con.Open();
 //System.Data.DataTable tbl = con.GetSchema("Databases");
 //ExportExcel(tbl);

 System.Text.StringBuilder sb = new System.Text.StringBuilder();
 sb.Append(" <TABLE BORDER=1>");
 sb.Append("<tr><td colspan='6' align='center'>");
 sb.Append("<H3>Table Design for: -" + con.Database.ToString() + "</H3>");

 sb.Append("</td></tr>");

 sb.Append("<tr><td colspan='6' align='center'> </td></tr>");
 con.Close();
 System.Data.DataTable tblTables = null;

 con.Open();
 tblTables = con.GetSchema(System.Data.SqlClient.
 SqlClientMetaDataCollectionNames.Tables,
 new string[] { null, null, null, "BASE TABLE" });
 DataTable columnsTable = null;
 foreach (System.Data.DataRow rowDatabase in tblTables.Rows)
 {
 columnsTable = con.GetSchema(SqlClientMetaDataCollectionNames.Columns, new string[] { null, null, rowDatabase["TABLE_NAME"].ToString() });
 sb.Append("<tr><td colspan='5' align='center'>");
 sb.Append("<b>Table Description</b></td>");
 sb.Append("<td align='center'>");
 sb.Append("<b>Table Name</b></td></tr>");
 sb.Append("<tr><td colspan='5' align='center' >" + rowDatabase["TABLE_NAME"].ToString() + "</td><td colspan='1' align='center'>" + rowDatabase["TABLE_NAME"].ToString() + "</td></tr>");
 sb.Append("<tr><td><b> SL No</b></td> <td><b> Field Name</b></td> <td><b> Data Type</b></td> <td><b> Size</b></td> <td><b> Constraint </b></td><td><b> Explanation </b></td></tr>");
 int i = 1;
 foreach (System.Data.DataRow columnsRows in columnsTable.Rows)
 {

 sb.Append("<tr><td>" + i + "</td> <td>" + columnsRows["COLUMN_NAME"].ToString() + "</td> <td>" + columnsRows["DATA_TYPE"].ToString() + "</td> <td>" + columnsRows["CHARACTER_MAXIMUM_LENGTH"].ToString() + "</td> <td></td><td></td></tr>");
 i++;
 }
 sb.Append("<tr><td colspan=6></td></tr>");
 }

 con.Close();
 sb.Append(" </TABLE>");

 Response.Clear();
 Response.ClearContent();
 Response.ClearHeaders();
 Response.ContentType = "application/vnd.ms-excel";
 Response.AddHeader("Content-Disposition", "attachment; filename=Reports.xls");
 Response.Write(sb);
 Response.End();
 }

The out put will be displayed like as follows :

Advertisements