C#如何在窗體程序中操作數(shù)據(jù)庫數(shù)據(jù)
一、界面布局
界面中有一個dataGridview、兩個Button、兩個Label和兩個TextBox。
二、定義數(shù)據(jù)庫操作的公共類
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) // 創(chuàng)建連接 { 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) // 重載 創(chuàng)建連接 { 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==獲取數(shù)據(jù)庫連接,打開數(shù)據(jù)庫"); 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==獲取數(shù)據(jù)庫連接,打開數(shù)據(jù)庫異常異常"); //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) //根據(jù)某一列的名字 獲取某個集合中該列的所有值 { 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) //根據(jù) datatable 獲取每一行的數(shù)據(jù)的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) //根據(jù)ID號 返回對應(yīng)行的 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) //根據(jù) 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) // 根據(jù) { 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() // 參數(shù)類型 不同數(shù)據(jù)庫連接 { m_ClientsqlConn.Close(); } } }
三、在界面中操作數(shù)據(jù)庫方法
ps:數(shù)據(jù)庫的配置信息保存在Config.ini文件中,如果僅是測試用的話,可以直接在
m_DataBase = new DBCtrl(ipstr, namestr, usernamestr, passwordstr);
處輸入ip地址、數(shù)據(jù)庫名、數(shù)據(jù)庫用戶名和密碼即可
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文件函數(shù) 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值設(shè)為空 private void TextBoxNull() { textBox1.Text = ""; textBox2.Text = ""; } //設(shè)置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響應(yīng)事件 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(); } } //點(diǎn)擊修改按鈕響應(yīng)事件 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("輸出當(dāng)前選中數(shù)據(jù)行:" + currentIndex); flag = m_DataBase.deleteRowfor("device_info1", currentIndex); if (flag) { MessageBox.Show("刪除成功!"); initDataTable(); } else { MessageBox.Show("刪除失??!"); } } } }
以上為個人經(jīng)驗(yàn),希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
C# Winform 實(shí)現(xiàn)屏蔽鍵盤的win和alt+F4的實(shí)現(xiàn)代碼
最近在做一個惡搞程序,就是打開后,程序獲得桌面的截圖然后,然后全屏顯示在屏幕上,用戶此時(shí)則不能進(jìn)行任何操作。2009-02-02解決C#中取消方向鍵對控件焦點(diǎn)控制的實(shí)現(xiàn)方法
本篇文章是對C#中取消方向鍵對控件焦點(diǎn)控制的解決方法進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-05-05C#利用WebClient實(shí)現(xiàn)兩種方式下載文件
本篇文章主要介紹了C#利用WebClient 兩種方式下載文件,詳細(xì)的介紹了兩種方式,非常具有實(shí)用價(jià)值,需要的朋友可以參考下。2017-02-02C# Fiddler插件實(shí)現(xiàn)網(wǎng)站離線瀏覽功能
本文主要介紹了C# Fiddler插件實(shí)現(xiàn)網(wǎng)站離線瀏覽功能的原理與方法。具有很好的參考價(jià)值,下面跟著小編一起來看下吧2017-02-02利用AOP實(shí)現(xiàn)SqlSugar自動事務(wù)
這篇文章主要為大家詳細(xì)介紹了利用AOP實(shí)現(xiàn)SqlSugar自動事務(wù),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2017-10-10