public table_Info(string table_name, MyConnection myConnection) { InitializeComponent(); SqlConnection cn = myConnection.getConnection(); string student = table_name; string sql = "select * from " + student; SqlCommand cmd; try { cn.Open(); cmd = new SqlCommand(sql, cn); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo); DataTable dt = dr.GetSchemaTable(); foreach (DataRow dt_row in dt.Rows) { foreach (DataColumn dt_col in dt.Columns) { this.rtbView.Text += string.Format("{0}={1}", dt_col.ColumnName, dt_row[dt_col]).ToString(); this.rtbView.Text += "\n"; } this.rtbView.Text += "=======================\n"; } } catch (SqlException ex) { MessageBox.Show(ex.Message); } finally { if (cn.State == ConnectionState.Open) { cn.Close(); } } }
/** * Clear current database object and list of DataGridView * Generate new Database **/ #endregion private void btnGenerate_Click(object sender, EventArgs e) { try { #region Create sql connection myConnection = new MyConnection(this.cmbListDatabase.SelectedItem.ToString()); this.cn = myConnection.getConnection(); if (IsConnection(this.cn)) { if (listDgv.Count > 0) { foreach (DataGridView dgv in listDgv) { dgv.Dispose(); } } listDgv.Clear(); this.panel1.Controls.Clear(); #endregion #region Load information to Database class DataTable databasesSchemaTable = cn.GetSchema("Databases"); db = new Database(databasesSchemaTable, myConnection.database_name); #endregion #region Load information to Table class DataTable allTablesSchemaTable = cn.GetSchema("Tables", new string[] { null, null, null, "BASE TABLE" }); filterUnnessaryRow(allTablesSchemaTable); List <Table> listTables = new List <Table>(); foreach (DataRow dr in allTablesSchemaTable.Rows) { Table t = new Table(dr[2], db); listTables.Add(t); } db.GetSetTable = listTables; #endregion #region Load information to Columns class DataTable allColumnsSchemaTable = cn.GetSchema("Columns"); filterUnnessaryRow(allColumnsSchemaTable); var selectedRows = from info in allColumnsSchemaTable.AsEnumerable() select new { TableCatalog = info["TABLE_CATALOG"], TableSchema = info["TABLE_SCHEMA"], TableName = info["TABLE_NAME"], ColumnName = info["COLUMN_NAME"], DataType = info["DATA_TYPE"] }; foreach (Table t in db.GetSetTable) { List <Columns> listColumns = new List <Columns>(); foreach (var row in selectedRows) { if (t.table_name.Equals(row.TableName)) { Columns col = new Columns(row.DataType, row.ColumnName, t); listColumns.Add(col); } } t.GetSetColumns = listColumns; } #endregion #region Set primary key for Columns class DataTable allIndexColumnsSchemaTable = cn.GetSchema("IndexColumns"); filterUnnessaryRow(allIndexColumnsSchemaTable); var selectedIndexRows = from info in allIndexColumnsSchemaTable.AsEnumerable() select new { TableSchema = info["table_schema"], TableName = info["table_name"], ColumnName = info["column_name"], ConstraintSchema = info["constraint_schema"], ConstraintName = info["constraint_name"], KeyType = info["KeyType"] }; foreach (Table t in db.GetSetTable) { foreach (var row in selectedIndexRows) { if (t.table_name.Equals(row.TableName)) { foreach (Columns col in t.GetSetColumns) { if (col.column_name.Equals(row.ColumnName)) { col.isKey = true; } } } } } #endregion #region Set foreign key for Foreign class string selectQuery = "select "; selectQuery += "rc.CONSTRAINT_NAME,"; selectQuery += "rcu.TABLE_NAME 'Referencing Table',"; selectQuery += "rcu.COLUMN_NAME 'Referencing Column',"; selectQuery += "rcu1.TABLE_NAME 'Referenced Table',"; selectQuery += "rcu1.COLUMN_NAME 'Referenced Column'\n"; selectQuery += "from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc\n"; selectQuery += "inner join\n"; selectQuery += "INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu\n"; selectQuery += "on rc.CONSTRAINT_CATALOG = rcu.CONSTRAINT_CATALOG\n"; selectQuery += "and rc.CONSTRAINT_NAME = rcu.CONSTRAINT_NAME\n"; selectQuery += "inner join\n"; selectQuery += "INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu1\n"; selectQuery += "on rc.UNIQUE_CONSTRAINT_CATALOG = rcu1.CONSTRAINT_CATALOG\n"; selectQuery += "and rc.UNIQUE_CONSTRAINT_NAME = rcu1.CONSTRAINT_NAME"; SqlCommand cmd = new SqlCommand(selectQuery, cn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); foreach (Table t in db.GetSetTable) { List <ForeignKey> listForeignKeys = new List <ForeignKey>(); foreach (DataRow row in dt.Rows) { ForeignKey fk = new ForeignKey(); foreach (DataColumn col in dt.Columns) { if (col.ColumnName.Equals("CONSTRAINT_NAME")) { fk.constraint_name = row[col]; } else if (col.ColumnName.Equals("Referencing Table")) { fk.referencingTable = db.getTableByName(row[col]); } else if (col.ColumnName.Equals("Referencing Column")) { fk.referencingCol = row[col]; } else if (col.ColumnName.Equals("Referenced Table")) { fk.referencedTable = db.getTableByName(row[col]); } else { fk.referencedCol = row[col]; } } if (t.table_name.Equals(fk.referencingTable.table_name)) { listForeignKeys.Add(fk); } } t.GetSetForeignKeys = listForeignKeys; } #endregion #region add table info to datagridview for (int i = 0; i < db.GetSetTable.Count; i++) { DataGridView dgv = new DataGridView(); dgv.Height = dgv.RowTemplate.Height; DataGridViewTextBoxColumn IdCol = new DataGridViewTextBoxColumn(); IdCol.HeaderText = db.GetSetTable[i].table_name.ToString(); dgv.Columns.Add(IdCol); DataGridViewTextBoxColumn dataTypeColumn = new DataGridViewTextBoxColumn(); dataTypeColumn.Name = "Datatype"; dataTypeColumn.HeaderText = "Data Type"; dgv.Columns.Add(dataTypeColumn); int indexRow = 0; foreach (Columns col in db.GetSetTable[i].GetSetColumns) { dgv.Rows.Add(string.Format("{0}", col.column_name), string.Format("{0}", col.data_type)); if (col.isKey) { dgv.Rows[indexRow].Cells[0].Value += " (PK)"; } dgv.Height += dgv.RowTemplate.Height; indexRow++; } dgv.ColumnHeadersDefaultCellStyle.BackColor = Color.FromArgb(184, 239, 252); dgv.ColumnHeadersDefaultCellStyle.Font = new Font("Verdana", 12, FontStyle.Bold); dgv.EnableHeadersVisualStyles = false; dgv.DefaultCellStyle.Font = new Font("Verdana", 10); dgv.DefaultCellStyle.BackColor = Color.White; dgv.BorderStyle = BorderStyle.Fixed3D; dgv.MultiSelect = false; dgv.GridColor = Color.MediumTurquoise; dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; dgv.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCells; dgv.RowHeadersVisible = false; dgv.AllowUserToAddRows = false; listDgv.Add(dgv); } #endregion this.cn.Close(); this.txtDBName.Text = db.database_name.ToString(); this.txtDBId.Text = db.dbid.ToString(); this.txtCreatedDate.Text = db.create_date.ToString(); postioning(listDgv); InitEvent(listDgv); } } catch (Exception) { MessageBox.Show("Your Account Has No Permission To Access This Database!"); cn.Close(); } this.cmbListDatabase.SelectedIndex = -1; }