基于C#开发数据库应用程序
一、思路
1.窗体上使用DataGridView控件来实现对数据库表格内容的显示
2.绑定数据源到DataGridView控件（应用程序连接到数据库）
3.在应用程序上实现对数据库文件的增、删、改、查
二、界面
三、实现过程
1.绑定数据源到DataGridView控件（应用程序连接到数据库）
方法引用：
1 private void Form1_Load(object sender, EventArgs e) 2 { 3 //Binding Mode Get DataSource to DataGridView 4 dataGridView1.DataSource = BindingMode_BindingSource("Hyson_Staff", "Table_Staff_Info").Tables[0]; 5 6 //dataGridView1.DataSource = NonBindingMode_BindingSource(); 7 }
方法实现：
1 /// <summary> 2 /// 绑定模式下获取数据源 3 /// </summary> 4 /// <param name="DB_Name">数据库名称</param> 5 /// <param name="Table_Name">表格名称</param> 6 /// <returns>返回类型为DataSet</returns> 7 private DataSet BindingMode_BindingSource(string DB_Name, string Table_Name) 8 { 9 string constr = @"Server=DESKTOP-K1D8VOKHYSON_STOCK_V01;user=sa;pwd=sa;database=" DB_Name; 10 SqlConnection mycon = new SqlConnection(constr); 11 DataSet myds = new DataSet(); 12 try 13 { 14 mycon.Open(); 15 string sql = "select * from " Table_Name; 16 SqlDataAdapter myda = new SqlDataAdapter(sql, mycon); 17 myda.Fill(myds, "Table_Staff_Info"); 18 } 19 catch (Exception ex) 20 { 21 MessageBox.Show(ex.Message); 22 } 23 finally 24 { 25 mycon.Close(); 26 } 27 return myds; 28 }
2.实现数据库的新增记录
方法引用：
InsertDB( "Hyson_Staff","Table_Staff_Info", new string[]{"Name","Gender","Department","BrithDay","JoinTime", "LeaveTime","IsWork"}, new string[]{ textBox_Name.Text, textBox_Gender.Text, textBox_Department.Text, textBox_BrithDay.Text, textBox_JoinTime.Text, textBox_LeaveTime.Text, checkBox_IsWork.Checked.ToString()}); dataGridView1.DataSource = BindingMode_BindingSource("Hyson_Staff", "Table_Staff_Info").Tables[0];
方法实现：
1 /// <summary> 2 /// 向数据库中增加数据 3 /// </summary> 4 /// <param name="DB_Name">数据库名称</param> 5 /// <param name="Table_Name">表名</param> 6 /// <param name="Array_Keys">Key数组</param> 7 /// <param name="Array_Vaules">Value数组</param> 8 private void InsertDB(string DB_Name, string Table_Name, string[] Array_Keys, string[] Array_Vaules) 9 { 10 string constr = @"Server=DESKTOP-K1D8VOKHYSON_STOCK_V01;user=sa;pwd=sa;database=" DB_Name; 11 SqlConnection mycon = new SqlConnection(constr); 12 13 try 14 { 15 mycon.Open(); 16 string keys = " ("; 17 for (int i = 0; i < Array_Keys.Length; i ) 18 { 19 if (i<=Array_Keys.Length-2) 20 { 21 keys = keys "[" Array_Keys[i].Trim() "],"; 22 } 23 else 24 { 25 keys = keys "[" Array_Keys[i].Trim() "])"; 26 } 27 } 28 29 string values=""; 30 for (int i = 0; i < Array_Vaules.Length; i ) 31 { 32 if (i <= Array_Vaules.Length - 2) 33 { 34 values = values "‘" Array_Vaules[i].Trim() "‘,"; 35 } 36 else 37 { 38 values = values "‘" Array_Vaules[i].Trim() "‘"; 39 } 40 } 41 42 string update_sql = "insert into [" Table_Name "] " keys "values(" values ")"; 43 SqlCommand mycom = new SqlCommand(update_sql, mycon); 44 mycom.ExecuteNonQuery(); 45 } 46 catch (Exception ex) 47 { 48 MessageBox.Show(ex.Message); 49 } 50 finally 51 { 52 mycon.Close(); 53 } 54 }
3.实现数据库的删除记录
方法引用：
DeleteDB("Hyson_Staff", "Table_Staff_Info", label_ID.Text);
方法实现：
1 /// <summary> 2 /// 实现数据库的删除记录 3 /// </summary> 4 /// <param name="DB_Name">数据库名称</param> 5 /// <param name="Table_Name">表名</param> 6 /// <param name="id">主键（数字主键ID）</param> 7 private void DeleteDB(string DB_Name, string Table_Name,string id) 8 { 9 string constr = @"Server=DESKTOP-K1D8VOKHYSON_STOCK_V01;user=sa;pwd=sa;database=" DB_Name; 10 SqlConnection mycon = new SqlConnection(constr); 11 12 try 13 { 14 mycon.Open(); 15 string delete_sql = "delete top(1) from " Table_Name " where id=" id; 16 SqlCommand mycom = new SqlCommand(delete_sql, mycon); 17 mycom.ExecuteNonQuery(); 18 } 19 catch (Exception ex) 20 { 21 MessageBox.Show(ex.Message); 22 } 23 finally 24 { 25 mycon.Close(); 26 } 27 }
4.实现数据库的修改记录
方法引用：
1 string ID = (label_ID.Text); 2 UpdateDB("Hyson_Staff", "Table_Staff_Info", "Name", textBox_Name.Text, ID); 3 UpdateDB("Hyson_Staff", "Table_Staff_Info", "Gender", textBox_Gender.Text, ID); 4 UpdateDB("Hyson_Staff", "Table_Staff_Info", "Department", textBox_Department.Text, ID); 5 UpdateDB("Hyson_Staff", "Table_Staff_Info", "BrithDay", textBox_BrithDay.Text, ID); 6 UpdateDB("Hyson_Staff", "Table_Staff_Info", "JoinTime", textBox_JoinTime.Text, ID); 7 UpdateDB("Hyson_Staff", "Table_Staff_Info", "LeaveTime", textBox_LeaveTime.Text, ID); 8 UpdateDB("Hyson_Staff", "Table_Staff_Info", "IsWork", checkBox_IsWork.Checked.ToString(), ID);
方法实现：
1 /// <summary> 2 /// 修改数据库数据 3 /// </summary> 4 /// <param name="DB_Name">数据库名称</param> 5 /// <param name="Table_Name">表名</param> 6 /// <param name="Key">待修改键</param> 7 /// <param name="NewValue">数值</param> 8 /// <param name="Update_ID">当前记录的主键</param> 9 private void UpdateDB(string DB_Name, string Table_Name, string Key, string NewValue, string Update_ID) 10 { 11 string constr = @"Server=DESKTOP-K1D8VOKHYSON_STOCK_V01;user=sa;pwd=sa;database=" DB_Name; 12 SqlConnection mycon = new SqlConnection(constr); 13 14 try 15 { 16 mycon.Open(); 17 string update_sql = "update " Table_Name " set " Key "=‘" NewValue "‘ where id=" Update_ID; 18 SqlCommand mycom = new SqlCommand(update_sql, mycon); 19 mycom.ExecuteNonQuery(); 20 } 21 catch (Exception ex) 22 { 23 MessageBox.Show(ex.Message); 24 } 25 finally 26 { 27 mycon.Close(); 28 } 29 }
5.实现数据库的修改记录
方法引用：（将查询到的内容，填充到dataGridView1中）
dataGridView1.DataSource = SelectDB("Hyson_Staff", "Table_Staff_Info", comboBox_SelectKey.Text, comboBox_Relationship.Text,comboBox_SelectValue.Text).Tables[0];
方法实现：
1 /// <summary> 2 /// 基于Select..Where..的数据库查询 3 /// </summary> 4 /// <param name="DB_Name">数据库名</param> 5 /// <param name="Table_Name">表名</param> 6 /// <param name="SelectKey">待查询的键</param> 7 /// <param name="Relationship">关系（大于，小于，等于）</param> 8 /// <param name="SelectValue">待查询的值</param> 9 /// <returns>返回类型DataSet</returns> 10 private DataSet SelectDB(string DB_Name, string Table_Name,string SelectKey,string Relationship,string SelectValue) 11 { 12 string constr = @"Server=DESKTOP-K1D8VOKHYSON_STOCK_V01;user=sa;pwd=sa;database=" DB_Name; 13 //string constr = @"Server=192.168.10.123;user=sa;pwd=sa;database=" DB_Name; 14 SqlConnection mycon = new SqlConnection(constr); 15 DataSet myds = new DataSet(); 16 17 try 18 { 19 mycon.Open(); 20 string sql = "select * from " Table_Name " where (" SelectKey Relationship "‘" SelectValue "‘)"; 21 SqlDataAdapter myda = new SqlDataAdapter(sql, mycon); 22 myda.Fill(myds, "Table_Staff_Info"); 23 } 24 catch (Exception ex) 25 { 26 MessageBox.Show(ex.Message); 27 } 28 finally 29 { 30 mycon.Close(); 31 } 32 return myds; 33 }