场景

Winform中连接Mysql8并查询表中数据进行显示：

Winform中连接Mysql8并查询表中数据进行显示_BADAO_LIUMANG_QIZHI的博客-CSDN博客

与上面实现的流程类似，怎么在连接mysql的基础上实现执行查询、编辑、删除、插入操作的sql。

但是不能执行"drop", "drop database" , "drop table" , "truncate", "alter","rename" , "create"等这些dll语句。

同上面一样，项目中引入Mysql.Data.dll依赖并设计窗体布局如下

注：

博客：

BADAO_LIUMANG_QIZHI的博客_霸道流氓气质_CSDN博客-C#,SpringBoot,架构之路领域博主

关注公众号

霸道的程序猿

获取编程相关电子书、教程推送与免费下载。

实现

1、声明变量获取数据库连接的相关参数

string connetStr = String.Empty;MySqlConnection mySqlConnection = null;String hostaddress = String.Empty;String port = String.Empty;String databaseName = String.Empty;String name = String.Empty;String pass = String.Empty;private MySqlCommand dbCmd = null;private MySqlDataReader dbDataReader = null;

2、连接按钮的点击事件

private void button_connect_Click(object sender, EventArgs e){hostaddress = this.textBox_host.Text.Trim();databaseName = this.textBox_database.Text.Trim();name = this.textBox_username.Text.Trim();pass = this.textBox_password.Text.Trim();port = this.textBox_port.Text.Trim();connetStr = "server=" + hostaddress + ";port="+ port+";User Id=" + name + ";password=" + pass + ";database=" + databaseName; //localhost不支持ssl连接时，最后一句一定要加！！！mySqlConnection = new MySqlConnection(connetStr);try{mySqlConnection.Open(); //连接数据库MessageBox.Show("数据库连接成功", "提示", MessageBoxButtons.OK);}catch (MySqlException ex){MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK); //显示错误信息}}

连接数据库效果

2、执行新增、编辑、删除的sql的按钮的点击事件

private void button_executeSql_Click(object sender, EventArgs e){string searchStr = this.textBox_sql.Text.Trim();if (String.IsNullOrEmpty(this.textBox_sql.Text)){MessageBox.Show("执行sql为空");}else if (!badaoHelper.checkSql(searchStr)){MessageBox.Show("执行sql不被允许");}else if (mySqlConnection.State == ConnectionState.Closed) {MessageBox.Show("请先建立数据库连接");}else {try {dbCmd = new MySqlCommand();mandText = searchStr;dbCmd.Connection = mySqlConnection;int result = dbCmd.ExecuteNonQuery();if (result > 0){MessageBox.Show("sql执行成功，数据库连接关闭，受影响的行数：" + result);mySqlConnection.Close();}else {MessageBox.Show("sql执行失败，数据库连接关闭，受影响的行数：" + result);mySqlConnection.Close();}}catch (Exception ex) {mySqlConnection.Close();MessageBox.Show("sql执行失败，数据库连接关闭，报错信息:" + ex.Message);}}}

执行sql的效果

这其中用到了校验是否包含指定sql的工具类方法checkSql

public static bool checkSql(string sql){bool isRight = true;string[] notAllowKeyWords = { "drop", "drop database" , "drop table" , "truncate", "alter","rename" , "create" };for (int i = 0; i < notAllowKeyWords.Length; i++){string arr = notAllowKeyWords[i];if (sql.ToLower().Contains(arr.ToLower())) {isRight = false;}}return isRight;}

3、执行查询sql的执行按钮的点击事件

private void button_sql_query_Click(object sender, EventArgs e){string searchStr = this.textBox_sql_query.Text.Trim();if (String.IsNullOrEmpty(searchStr)){MessageBox.Show("执行sql为空");} else if (!badaoHelper.checkSql(searchStr)) {MessageBox.Show("执行sql不被允许");}else if (mySqlConnection.State == ConnectionState.Closed){MessageBox.Show("请先建立数据库连接");}else{try{MySqlDataAdapter adapter = new MySqlDataAdapter(searchStr, mySqlConnection);DataSet dataSet = new DataSet();adapter.Fill(dataSet, "table1");this.dataGridView_select.DataSource = dataSet.Tables["table1"];}catch (Exception ex){MessageBox.Show("报错信息:" + ex.Message);}}}

执行查询sql的效果

4、完整示例代码

using MySql.Data.MySqlClient;using System;using System.Collections.Generic;using ponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace BdtdDataUpload{public partial class Main : Form{string connetStr = String.Empty;MySqlConnection mySqlConnection = null;String hostaddress = String.Empty;String port = String.Empty;String databaseName = String.Empty;String name = String.Empty;String pass = String.Empty;private MySqlCommand dbCmd = null;private MySqlDataReader dbDataReader = null;public Main(){InitializeComponent();}private void button_connect_Click(object sender, EventArgs e){hostaddress = this.textBox_host.Text.Trim();databaseName = this.textBox_database.Text.Trim();name = this.textBox_username.Text.Trim();pass = this.textBox_password.Text.Trim();port = this.textBox_port.Text.Trim();connetStr = "server=" + hostaddress + ";port="+ port+";User Id=" + name + ";password=" + pass + ";database=" + databaseName; //localhost不支持ssl连接时，最后一句一定要加！！！mySqlConnection = new MySqlConnection(connetStr);try{mySqlConnection.Open(); //连接数据库MessageBox.Show("数据库连接成功", "提示", MessageBoxButtons.OK);}catch (MySqlException ex){MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK); //显示错误信息}}private void button_disconnect_Click(object sender, EventArgs e){if (mySqlConnection.State == ConnectionState.Open) {mySqlConnection.Close();}}private void button_executeSql_Click(object sender, EventArgs e){string searchStr = this.textBox_sql.Text.Trim();if (String.IsNullOrEmpty(this.textBox_sql.Text)){MessageBox.Show("执行sql为空");}else if (!badaoHelper.checkSql(searchStr)){MessageBox.Show("执行sql不被允许");}else if (mySqlConnection.State == ConnectionState.Closed) {MessageBox.Show("请先建立数据库连接");}else {try {dbCmd = new MySqlCommand();mandText = searchStr;dbCmd.Connection = mySqlConnection;int result = dbCmd.ExecuteNonQuery();if (result > 0){MessageBox.Show("sql执行成功，数据库连接关闭，受影响的行数：" + result);mySqlConnection.Close();}else {MessageBox.Show("sql执行失败，数据库连接关闭，受影响的行数：" + result);mySqlConnection.Close();}}catch (Exception ex) {mySqlConnection.Close();MessageBox.Show("sql执行失败，数据库连接关闭，报错信息:" + ex.Message);}}}private void button_sql_query_Click(object sender, EventArgs e){string searchStr = this.textBox_sql_query.Text.Trim();if (String.IsNullOrEmpty(searchStr)){MessageBox.Show("执行sql为空");} else if (!badaoHelper.checkSql(searchStr)) {MessageBox.Show("执行sql不被允许");}else if (mySqlConnection.State == ConnectionState.Closed){MessageBox.Show("请先建立数据库连接");}else{try{MySqlDataAdapter adapter = new MySqlDataAdapter(searchStr, mySqlConnection);DataSet dataSet = new DataSet();adapter.Fill(dataSet, "table1");this.dataGridView_select.DataSource = dataSet.Tables["table1"];}catch (Exception ex){MessageBox.Show("报错信息:" + ex.Message);}}}}}