private void Init()
{
try
{
if (rbt_sqlserver.Checked)
{
//1.查询数据库
string sql = @"SELECT name,dbid FROM sysdatabases";
var dataBases = DapperHelper <DataBase> .Query(sql, null);
foreach (var itemData in dataBases)
{
this.cboDataBases.Items.Add(itemData.Name);
}
}
else if (rbt_mysql.Checked)
{
//1.查询数据库
string sql = @"SELECT SCHEMA_NAME AS Name
FROM INFORMATION_SCHEMA.SCHEMATA";
var dataBases = DapperMysqlHelper <string> .Query(sql, null);
foreach (var itemData in dataBases)
{
this.cboDataBases.Items.Add(itemData);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//选择数据库，显示表名
private void cboDataBases_SelectedIndexChanged(object sender, EventArgs e)
{
this.tvTables.Nodes.Clear();
if (rbt_sqlserver.Checked)
{
string databaseName = this.cboDataBases.Text.Trim();
string sql = $"SELECT name FROM {databaseName}.dbo.sysobjects WHERE xtype = 'U' ORDER BY name";
List <string> dataBases = DapperHelper <string> .Query(sql, null);
foreach (var dataBase in dataBases)
{
TreeNode tn = new TreeNode();
tn.Text = dataBase;
this.tvTables.Nodes.Add(tn);
}
}
else if (rbt_mysql.Checked)
{
string databaseName = this.cboDataBases.Text.Trim();
string sql = $"SELECT table_name FROM information_schema.tables where table_schema = '{databaseName}'";
List <string> dataBases = DapperMysqlHelper <string> .Query(sql, null);
foreach (var dataBase in dataBases)
{
TreeNode tn = new TreeNode();
tn.Text = dataBase;
this.tvTables.Nodes.Add(tn);
}
}
}
//选择treenode,获取列名
private void tvTables_AfterSelect(object sender, TreeViewEventArgs e)
{
if (rbt_sqlserver.Checked)
{
string databaseName = this.cboDataBases.Text.Trim();
string tableName = this.tvTables.SelectedNode.Text;
//string sql = $"SELECT * FROM {databaseName}.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{tableName}'";
string sql = $@"SELECT
A.name AS table_name,
B.name AS column_name,
C.value AS column_description,
d.COLUMN_NAME,d.DATA_TYPE,d.IS_NULLABLE,d.CHARACTER_MAXIMUM_LENGTH
FROM {databaseName}.sys.tables A
INNER JOIN {databaseName}.sys.columns B ON B.object_id = A.object_id
LEFT JOIN {databaseName}.sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
INNER JOIN {databaseName}.INFORMATION_SCHEMA.COLUMNS d ON b.name = d.COLUMN_NAME
WHERE A.name = '{tableName}'";
var dataBases = DapperHelper <DataBaseColumn> .Query(sql, null);
this.dataGridView1.DataSource = dataBases;
Type t = typeof(DataBaseColumn);
PropertyInfo[] propertyInfos = t.GetProperties();//属性
this.dataGridView1.DataSource = dataBases;
this.dataGridView1.Columns[propertyInfos.Count() - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
}
else if (rbt_mysql.Checked)
{
string databaseName = this.cboDataBases.Text.Trim();
string tableName = this.tvTables.SelectedNode.Text;
string sql = $"SELECT * FROM information_schema.COLUMNS where table_schema = '{databaseName}' AND table_Name = '{tableName}'";
var dataBases = DapperMysqlHelper <DataBaseColumnMysql> .Query(sql, null);
Type t = typeof(DataBaseColumnMysql);
PropertyInfo[] propertyInfos = t.GetProperties();//属性
this.dataGridView1.DataSource = dataBases;
this.dataGridView1.Columns[propertyInfos.Count() - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
}
}
