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