在我之前學習到java資料庫架構mybatis逆向工程時驚歎了一會兒，想了想這個思路理應早該想到。我們使用navicat工具建立了資料表還要寫SQL語句跟表字段一一對應，多麼費勁的一件事。SQL語句的增删查改萬變不離其宗，變的隻是庫、表、字段等，也就是說我們動态擷取到庫、表、字段這些名字後動态插入SQL語句中不就完事了？
于是今天呈有空就用C#實作了一個可以擷取所有庫、表、字段的demo，可以像navicat一樣通過點選事件檢視任意庫任意表任意資料，還能像任意表進行增删查改功能。
以下隻展示其中一個小子產品：查詢任意庫中的任意表中的資料
導入Dll：MySql.Data.dll
視窗設計：
Form1:
Form2:
代碼：
Form1：
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
//重置
private void button2_Click(object sender, EventArgs e)
{
this.textBox1.Text = "";
this.textBox2.Text = "";
this.textBox3.Text = "";
}
//連接配接
private void button1_Click(object sender, EventArgs e)
{
string server = this.textBox1.Text;
string userid = this.textBox2.Text;
string password = this.textBox3.Text;
Form2 f = new Form2(server, userid, password , out bool status);
if (status)
{
f.Show();
return;
}
MessageBox.Show("連接配接失敗！");
}
}
Form2：
public partial class Form2 : Form
{
string server, userid, password;
MySqlConnection conn;
public Form2(string server, string userid, string password , out bool status)
{
InitializeComponent();
this.server = server;
this.userid = userid;
this.password = password;
if (OpenConnection())
{
status = true;
}
else
{
status = false;
}
}
private void Form2_Load(object sender, EventArgs e)
{
//加載庫名
this.comboBox1.Items.AddRange(GetDatabases());
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
this.comboBox2.Items.Clear();
string item = (sender as ComboBox).Text;
this.comboBox2.Items.AddRange(GetTables(item));
}
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
string database = this.comboBox1.Text;
string table = this.comboBox2.Text;
this.dataGridView1.Columns.Clear();
this.dataGridView1.Rows.Clear();
string[] datas = GetField(database, table);
//加載字段
dataGridView1.ColumnCount = datas.Length;
for (int i = 0; i < datas.Length; i++)
{
this.dataGridView1.Columns[i].Name = datas[i];
}
//加載資料
List<string[]> dataList = GetDatas(database,datas, table);
int listCount = dataList.Count;
for (int i = 0; i < dataList[0].Length; i++)
{
for (int j = 0; j < listCount; j++)
{
this.dataGridView1.Rows.Add();
try
{
dataGridView1.Rows[i].Cells[j].Value = dataList[j][i];
}
catch (Exception)
{
}
}
}
}
#region 操作資料庫
//打開mysql連接配接
private bool OpenConnection()
{
try
{
string strConn = "server=" + server +
";userid=" + userid +
";password=" + password;
conn = new MySqlConnection(strConn);
conn.Open();
}
catch (Exception)
{
return false;
}
return true;
}
private bool OpenConnection(string database)
{
try
{
string strConn = "server=" + server +
";userid=" + userid +
";password=" + password +
";Database=" +database;
conn = new MySqlConnection(strConn);
conn.Open();
}
catch (Exception)
{
return false;
}
return true;
}
//關閉mysql連接配接
private bool CloseConnection()
{
try
{
conn.Close();
return true;
}
catch (Exception)
{
return false;
}
}
//增删改
private bool AddUpdateDelet(string sql)
{
if (CloseConnection())
{
MySqlCommand msc = new MySqlCommand(sql, conn);
int status = msc.ExecuteNonQuery();
CloseConnection();
if (status > 0)
{
return true;
}
return false;
}
return false;
}
//查詢
private MySqlDataReader Select(string sql)
{
if (OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dataReader = cmd.ExecuteReader();
dataReader.Read();
return dataReader;
}
return null;
}
private MySqlDataReader Select(string database,string sql)
{
if (OpenConnection(database))
{
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dataReader = cmd.ExecuteReader();
dataReader.Read();
return dataReader;
}
return null;
}
//擷取資料庫名
private string[] GetDatabases() {
try
{
string getDatabaseSql = "SELECT GROUP_CONCAT(DISTINCT Table_schema) FROM information_schema.`TABLES`";
MySqlDataReader data = Select(getDatabaseSql);
MessageBox.Show("" + data[0]);
return data[0].ToString().Split(',');
}
finally
{
CloseConnection();
}
}
//擷取資料表名
private string[] GetTables(string database) {
try
{
string getTabesSql = "SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema='"+ database + "'";
MySqlDataReader data = Select(getTabesSql);
return data[0].ToString().Split(',');
}
finally
{
CloseConnection();
}
}
//擷取表字段名
private string[] GetField(string database , string table) {
try
{
string getFieldSql = "SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.Columns WHERE table_name='"+ table + "' AND table_schema='"+ database + "'";
MySqlDataReader data = Select(getFieldSql);
return data[0].ToString().Split(',');
}
finally
{
CloseConnection();
}
}
//擷取表資料
private List<string[]> GetDatas(string database, string[] fields ,string table) {
List<string[]> dataList = new List<string[]>();
foreach (string field in fields)
{
try
{
string getDataSql = "SELECT GROUP_CONCAT(" + field + ") FROM " + table + "";
MySqlDataReader data = Select(database,getDataSql);
dataList.Add(data[0].ToString().Split(','));
}
finally
{
CloseConnection();
}
}
return dataList;
}
#endregion
}
效果：
（資料庫工具中的mysql資料庫内容）
（demo效果）