C#在窗体程序中操作数据库数据

浅浅的花香味﹌ 2022-05-18 04:42 185阅读 0赞

# 一、界面布局 #

![这里写图片描述][70]

界面中有一个dataGridview、两个Button、两个Label和两个TextBox。

# 二、定义数据库操作的公共类 #

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    using System.Windows.Forms;
    using System.Data;
    using MySql.Data.MySqlClient;
    
    namespace TemSys
    {
       public  class DBCtrl
       {
            private MySqlConnection m_ClientsqlConn;
    
            public DBCtrl()                                                                          //    连接类型
            {
                m_ClientsqlConn = new MySqlConnection();
    
                try
                {
                    m_ClientsqlConn.Dispose();
                    m_ClientsqlConn.Close();              
                    m_ClientsqlConn.ConnectionString = "Database=dbName;Data Source=localhost;User Id=root;Password=123;charset=utf8";
                    m_ClientsqlConn.Open();
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }
            }
            public DBCtrl(string IP, string DBname, string Uname, string Pword)                     //    创建连接
            {
                m_ClientsqlConn = new MySqlConnection();
                try
                {
                    m_ClientsqlConn.Dispose();
                    m_ClientsqlConn.Close();
                    m_ClientsqlConn.ConnectionString = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8", DBname, IP, Uname, Pword);
                    m_ClientsqlConn.Open();
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }
            }
    
            public void DBConn(string connStr)                                                         //    重载  创建连接
            {
                try
                {
                    m_ClientsqlConn.Close();
                    m_ClientsqlConn.ConnectionString = connStr;
                    m_ClientsqlConn.Open();
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }
            }
    
    
            public DataTable GetDataTable(string SQLstr)                                     //    获取DataTable 一个表
            {
                Console.Write("zcn==获取数据库连接,打开数据库");
                try
                {
                    if (m_ClientsqlConn.State == ConnectionState.Open)
                        m_ClientsqlConn.Close();
                    m_ClientsqlConn.Open();
                    MySqlDataAdapter da = new MySqlDataAdapter(SQLstr, m_ClientsqlConn);
                    DataTable resultDS = new DataTable();
                    da.Fill(resultDS);
    
                    return resultDS;
                }
                catch (Exception ee)
                {
                    Console.Write("zcn==获取数据库连接,打开数据库异常异常");
                    //MessageBox.Show( ee.Message);
                    m_logclass.WriteLogFilein(ee.Message, "GetDataTable.txt");
                    return null;
                }
                finally
                {
                    m_ClientsqlConn.Close();
    
                }
    
            }
    
            public DataTable GetDataTableUsing(string SQLstr)                                     //    获取DataTable 一个表
            {
                using (MySqlConnection m_ClientsqlConn = new MySqlConnection())
                {
    
                }
                try
                {
                    if (m_ClientsqlConn.State == ConnectionState.Open)
                        m_ClientsqlConn.Close();
                    m_ClientsqlConn.Open();
                    MySqlDataAdapter da = new MySqlDataAdapter(SQLstr, m_ClientsqlConn);
                    DataTable resultDS = new DataTable();
                    da.Fill(resultDS);
    
                    return resultDS;
                }
                catch (Exception ee)
                {
                    //MessageBox.Show( ee.Message);
                    return null;
                }
    
    
            }
    
            public List<string> GetStringListfor(string lineName,DataTable dt)    //根据某一列的名字  获取某个集合中该列的所有值
            {
                List<string> list = new List<string>();
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add((string)dr[lineName]);
                }
                return list;
            }
    
            public List<DataRow> GetDataRowfor(DataTable dt)   //根据 datatable  获取每一行的数据的datarow
            {
                List<DataRow> list = new List<DataRow>();
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(dr);  
                }         
                return list;
            }
    /* public DataRow GetDataRowfor(string tablename,string ID) //根据ID号 返回对应行的 DataRow { string ss = "select * from " + tablename + " where ID = \'"+ID +"\'"; DataRow dr = new DataRow(); DataTable dt = GetDataTable(ss); dr = dt.Rows[0]; return dr; } */
             public DataTable GetDataTableOneLine(string SQLstr)                                     //    获取DataTable 一个表中一行
            {
                MySqlDataAdapter da = new MySqlDataAdapter(SQLstr, m_ClientsqlConn);
                DataTable resultDS = new DataTable();
                da.Fill(resultDS);
                return resultDS;
            }
    
    
    
            public DataTable GetDataSet_to_Table(string SQLstr)                               //    获取 dataset   多个表中  table
            {
                try
                {
                    MySqlDataAdapter da = new MySqlDataAdapter(SQLstr, m_ClientsqlConn);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds.Tables[0];
                }
                catch
                {
                    return null;
                }
            }
    
            public Boolean InsertDBase(string insString)
            {
                try
                {
                    if (m_ClientsqlConn.State == ConnectionState.Open)
                        m_ClientsqlConn.Close();
                    m_ClientsqlConn.Open();
                    MySqlCommand sqlcomd = new MySqlCommand(insString, m_ClientsqlConn);
                    sqlcomd.ExecuteNonQuery();
    
                    return true;
                }
                catch (Exception ee)
                {
                    return false;
                }
                finally
                {
                    m_ClientsqlConn.Close();
    
                }
            }
    
            public Boolean deleteRowfor(string tablename,int deleteID)  //根据 ID 删除指定行
            {
                try
                {
                    string ss ="delete from "+ tablename +" where ID = "+ deleteID;
                    if (m_ClientsqlConn.State == ConnectionState.Open)
                        m_ClientsqlConn.Close();
                    m_ClientsqlConn.Open();
                    MySqlCommand sqlcmd = new MySqlCommand(ss, m_ClientsqlConn);
                    sqlcmd.ExecuteNonQuery();
                    return true;
                }
                catch(Exception ee)
                {
                    return false;
                } 
            }
    
            public Boolean ModifyRowfor(string modifystr,string modifyID)         // 根据
            {
                try
                {
                    string ss = modifystr + " where id = " + modifyID;
                    if (m_ClientsqlConn.State == ConnectionState.Open)
                        m_ClientsqlConn.Close();
                    m_ClientsqlConn.Open();
                    MySqlCommand sqlcmd = new MySqlCommand(ss, m_ClientsqlConn);
                    sqlcmd.ExecuteNonQuery();
    
                    return true;
                }
                catch (Exception ee)
                {
                    return false;
                }
                finally
                {
                    m_ClientsqlConn.Close();
                }
    
            }
    
            public void CloseDBase()               //   参数类型  不同数据库连接
            {
                m_ClientsqlConn.Close();
            }
    
        }
    }

# 三、在界面中操作数据库方法 #

ps:数据库的配置信息保存在Config.ini文件中，如果仅是测试用的话，可以直接在`m_DataBase = new DBCtrl(ipstr, namestr, usernamestr, passwordstr);`处输入ip地址、数据库名、数据库用户名和密码即可

using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Runtime.InteropServices;
    
    namespace TemSys
    {
        public partial class ModifyDevice : Form
        {
            [DllImport("kernel32")]                                        //读写ini文件函数
            private static extern long WritePrivateProfileString(string section, string key, string val, string filePath);
            [DllImport("kernel32")]
            private static extern long GetPrivateProfileString(string section, string key, string def, StringBuilder retVal, int size, string filePath);
    
            DataTable dt = new DataTable();
    
            private DBCtrl m_DataBase;
    
            public ModifyDevice()
            {
                InitializeComponent();
            }
    
            //将所有的textBox值设为空
            private void TextBoxNull()
            {
                textBox1.Text = "";
                textBox2.Text = "";
            }
    
            //设置Lab值
            private void labelshow()
            {
                label1.Text = dataGridView1.Columns[0].HeaderText;
                label2.Text = dataGridView1.Columns[12].HeaderText;
            }          
    
            //初始化界面
            private void ModifyDevice_Load(object sender, EventArgs e)
            {
                StringBuilder retval = new StringBuilder();
                GetPrivateProfileString("DBConfig", "dbip", "", retval, 20, AppDomain.CurrentDomain.BaseDirectory + "Config.ini");
                string ipstr = retval.ToString();
                GetPrivateProfileString("DBConfig", "dbname", "", retval, 20, AppDomain.CurrentDomain.BaseDirectory + "Config.ini");
                string namestr = retval.ToString();
                GetPrivateProfileString("DBConfig", "dbusername", "", retval, 20, AppDomain.CurrentDomain.BaseDirectory + "Config.ini");
                string usernamestr = retval.ToString();
                GetPrivateProfileString("DBConfig", "dbpassword", "", retval, 20, AppDomain.CurrentDomain.BaseDirectory + "Config.ini");
                string passwordstr = retval.ToString();
    
                m_DataBase = new DBCtrl(ipstr, namestr, usernamestr, passwordstr);
    
                initDataTable();
            }
    
            private void initDataTable()
            {
                string ssp = string.Format("select * from device_info1");
                dt = m_DataBase.GetDataTable(ssp);
                dataGridView1.DataSource = dt;
    
                labelshow();
            }
    
            //双击dataGridView响应事件
            private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
            {
                string index = dataGridView1.CurrentRow.Cells[0].Value.ToString();
                if (label1.Text == "id")
                {
                    string ssp = string.Format("select * from device_info1 where id='" + index + "'");
                    dt = m_DataBase.GetDataTable(ssp); 
                    //DataRow row = dt.Rows[0];
                    textBox1.Text = dt.Rows[0]["id"].ToString();
                    textBox2.Text = dt.Rows[0]["number"].ToString();
                }
            }
    
            //点击修改按钮响应事件
            private void btnModify_Click(object sender, EventArgs e)
            {
                bool flag = false;
                string ssp = string.Format("update device_info1 set number='" + textBox2.Text + "'");
    
                flag = m_DataBase.ModifyRowfor(ssp, textBox1.Text);
    
                if (flag)
                {
                    MessageBox.Show("修改成功！");
                    initDataTable();
                }
                else
                {
                    MessageBox.Show("修改失败！");
                }
            }
    
            private void btnDelete_Click(object sender, EventArgs e)
            {
                 bool flag = false;
                int currentIndex = (int)dataGridView1.CurrentRow.Cells[0].Value;
                Console.WriteLine("输出当前选中数据行：" + currentIndex);
                flag = m_DataBase.deleteRowfor("device_info1", currentIndex);
                if (flag)
                {
                    MessageBox.Show("删除成功！");
                    initDataTable();
                }
                else
                {
                    MessageBox.Show("删除失败！");
                }
            }
    
        }
    }

**注：刚刚开始学习，有不足之处还请多多指教**

[70]: /images/20220518/b627fe398fc94835a1b848eb1fa87ac7.png