/// <summary> /// 開啟資料庫連線的動作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnOpen_Click(object sender, EventArgs e) { DatabaseConn objDb = new DatabaseConn(txtConnection.Text); DataTable objDt = objDb.GetTableList(out strErrMsg); for (int i = 0; i < objDt.Rows.Count; i++) { cbxlTable.Items.Add(objDt.Rows[i]["Table_Name"].ToString(), true); } }
/// <summary> /// 取得資料表的欄位 /// </summary> /// <param name="strTableName">資料表的名稱</param> /// <returns></returns> public DataTable GetTableColumn(string strTableName) { string strSql = @"Select c.COLUMN_NAME, c.Character_maximum_length, c.ORDINAL_POSITION, c.IS_NULLABLE, c.DATA_TYPE, cu.CONSTRAINT_NAME from INFORMATION_SCHEMA.COLUMNS c LEft Outer Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu On c.COLUMN_NAME = cu.COLUMN_NAME And c.TABLE_NAME = cu.TABLE_NAME And c.TABLE_CATALOG = cu.TABLE_CATALOG and c.TABLE_SCHEMA = cu.TABLE_SCHEMA Where c.TABLE_NAME = @TableName"; DataTable objDt = new DataTable(); SqlConnection SqlConn = new SqlConnection(this.ConnectionString); SqlCommand SqlCmd = new SqlCommand(strSql, SqlConn); SqlCmd.Parameters.AddWithValue("@TableName", strTableName); SqlDataAdapter SqlAdp = new SqlDataAdapter(SqlCmd); try { SqlAdp.Fill(objDt); } catch (Exception e) { } finally { SqlCmd = null; SqlConn = null; SqlAdp = null; } // 放入說明描述 objDt.Columns.Add("Description"); DatabaseConn objDb = new DatabaseConn(this.ConnectionString); for (int i = 0; i < objDt.Rows.Count; i++) { string strDescription = objDb.GetColumnDescription(strTableName, objDt.Rows[i]["COLUMN_NAME"].ToString()); objDt.Rows[i]["Description"] = (strDescription == "") ? objDt.Rows[i]["COLUMN_NAME"].ToString() : strDescription; } objDb = null; return(objDt); }
/// <summary> /// 匯出Word /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExport_Click(object sender, EventArgs e) { DatabaseConn objDb = new DatabaseConn(txtConnection.Text); strContent = new StringBuilder(); CheckedListBox.CheckedItemCollection checked_items = cbxlTable.CheckedItems; for (int i = 0; i < checked_items.Count; i++) { string strTableName = checked_items[i].ToString(); DataTable objColumn = objDb.GetTableColumn(strTableName); this.AppendTable(strContent, strTableName, objColumn, objDb); } // 寫入檔案 diaSave.ShowDialog(); }
/// <summary> /// 放入欄位內容 /// </summary> /// <param name="strContent"></param> /// <param name="strTableName"></param> /// <param name="objColumn"></param> private void AppendTable(StringBuilder strContent, string strTableName, DataTable objColumn, DatabaseConn objDb) { strContent.Append("<table border=\"1\">"); strContent.Append(" <tr>"); strContent.Append(" <td colspan=\"7\">資料表名稱:" + strTableName + "</td>"); strContent.Append(" </tr>"); strContent.Append(" <tr>"); strContent.Append(" <td>欄位名稱</td>"); strContent.Append(" <td>資料型態</td>"); strContent.Append(" <td>長度</td>"); strContent.Append(" <td>空值?</td>"); strContent.Append(" <td>主鍵</td>"); strContent.Append(" <td>外部鍵</td>"); strContent.Append(" <td>欄位說明</td>"); strContent.Append(" </tr>"); for (int i = 0; i < objColumn.Rows.Count; i++) { string strConstraintName = (objColumn.Rows[i]["CONSTRAINT_NAME"].ToString() != "") ? "YES" : ""; string strColumnName = objColumn.Rows[i]["COLUMN_NAME"].ToString(); strContent.Append(" <tr>"); strContent.Append(" <td>" + strColumnName + "</td>"); strContent.Append(" <td>" + objColumn.Rows[i]["DATA_TYPE"].ToString() + "</td>"); strContent.Append(" <td>" + objColumn.Rows[i]["Character_maximum_length"].ToString() + "</td>"); strContent.Append(" <td>" + objColumn.Rows[i]["IS_NULLABLE"].ToString() + "?</td>"); strContent.Append(" <td>" + strConstraintName + "</td>"); strContent.Append(" <td></td>"); strContent.Append(" <td>" + objDb.GetColumnDescription(strTableName, strColumnName) + "</td>"); strContent.Append(" </tr>"); } strContent.Append("</table><br><br>"); }