設計一個用戶管理系統，對系統中的用戶進行管理。假定，用戶表中有下列字段：用戶名，密碼，電話和 email 等信息。要求，1）利用 SQL server 首先創建用戶數據表；2）實現對用戶的增加、刪除、修改和查詢等功能。
（0）根據學生在使用CMS系統和Discuz系統時，發現其數據庫的后臺操作界面很簡單，因此根據sql語句的基本組成，設置where條件選擇輸入區和更新插入的更新區。
（1）只有在勾選該字段時，才允許輸入該字段的值
（2）使用listview列出數據查詢結果
（3）支持數據瀏覽，無須手動輸入查詢語句
以下只是簡單的幾組測試，其他的sql組合同理能夠運行
（1） 瀏覽數據
注：選擇瀏覽操作的示意圖
（2）選擇查看符合相應條件的數據
注：設置選擇的條件示意圖
注：執行選擇后的結果
（3）插入數據
注：插入數據示意
注：數據插入后，瀏覽數據結果
（4）更新數據
注：設置更新條件示意圖
注：執行更新操作后
（5）刪除數據
注：設置刪除條件示意圖
注：刪除操作執行后
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.Data;using System.Data.Sql;using System.Data.SqlClient;namespace 數據庫操作_WNFORM{ public partial class Form1 : Form { PRivate SqlConnection sqlcon; private SqlCommand sqlcom; private SqlDataReader sda; private string conn_str = @”Data Source=./SQLEXPRESS;AttachDbFilename=|DataDirectory|/info.mdf;Integrated Security=True;User Instance=True”; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { listView_res.Columns.Add(“用戶名”); listView_res.Columns.Add(“密 碼”); listView_res.Columns.Add(“手機號”); listView_res.Columns.Add(“郵箱號”); listView_res.Columns[0].Width = 250; listView_res.Columns[1].Width = 340; listView_res.Columns[2].Width = 245; listView_res.Columns[3].Width = 325; } private void checkBox1_CheckedChanged(object sender, EventArgs e) { textBox_name.Enabled = true; } private void checkBox3_CheckedChanged(object sender, EventArgs e) { textBox_pass.Enabled = true; } private void checkBox2_CheckedChanged(object sender, EventArgs e) { textBox_name2.Enabled = true; } private void checkBox6_CheckedChanged(object sender, EventArgs e) { textBox_pass2.Enabled = true; } private void checkBox4_CheckedChanged(object sender, EventArgs e) { textBox_tel.Enabled = true; } private void checkBox5_CheckedChanged(object sender, EventArgs e) { textBox_mail.Enabled = true; } private void checkBox7_CheckedChanged(object sender, EventArgs e) { textBox_tel2.Enabled = true; } private void checkBox8_CheckedChanged(object sender, EventArgs e) { textBox_mail2.Enabled = true; } private void init_controler() { checkBox1.Checked = false; checkBox2.Checked = false; checkBox3.Checked = false; checkBox4.Checked = false; checkBox5.Checked = false; checkBox6.Checked = false; checkBox7.Checked = false; checkBox8.Checked = false; textBox_mail.Text = “”; textBox_name.Text = “”; textBox_pass.Text = “”; textBox_tel.Text = “”; textBox_mail2.Text = “”; textBox_name2.Text = “”; textBox_pass2.Text = “”; textBox_tel2.Text = “”; textBox_mail.Enabled = false; textBox_name.Enabled = false; textBox_pass.Enabled = false; textBox_tel.Enabled = false; textBox_mail2.Enabled = false; textBox_name2.Enabled = false; textBox_pass2.Enabled = false; textBox_tel2.Enabled = false; } private string [] get_where() { string [] res=new string[4]; if (textBox_name.Text != null) { res[0]= textBox_name.Text.ToString(); } if (textBox_pass.Text != null) { res[1]= textBox_pass.Text.ToString(); } if (textBox_tel.Text != null) { res[2]= textBox_tel.Text.ToString(); } if (textBox_mail.Text != null) { res[3]= textBox_mail.Text.ToString(); } return res; } private string[] get_value() { string[] res = new string[4]; if (textBox_name2.Text != null) { res[0] = textBox_name2.Text.ToString(); } if (textBox_pass2.Text != null) { res[1] = textBox_pass2.Text.ToString(); } if (textBox_tel2.Text != null) { res[2] = textBox_tel2.Text.ToString(); } if (textBox_mail2.Text != null) { res[3] = textBox_mail2.Text.ToString(); } return res; } //執行數據庫查詢 private string execu_sql() { string res = “”; sqlcon = new SqlConnection(conn_str); sqlcom = new SqlCommand(); sqlcon.Open(); sqlcom.Connection = sqlcon; string sw = “”; sw = comboBox1.SelectedItem.ToString(); MessageBox.Show(sw); / 瀏覽數據 查詢數據 插入數據 更新數據 刪除數據 / switch (sw) { case “瀏覽數據”: res = “select * from [用戶表]”; sqlcom.CommandText = res; int count = 0; try { sda = sqlcom.ExecuteReader(); listView_res.Items.Clear(); while (sda.Read()) { ListViewItem lvi = new ListViewItem(); lvi.Text = sda.GetString(0); lvi.SubItems.Add(sda.GetString(1)); lvi.SubItems.Add(sda.GetString(2)); lvi.SubItems.Add(sda.GetString(3)); lvi.UseItemStyleForSubItems = false; lvi.SubItems[0].BackColor = Color.HotPink; lvi.SubItems[1].BackColor = Color.LightSkyBlue; lvi.SubItems[2].BackColor = Color.SpringGreen; lvi.SubItems[3].BackColor = Color.Orange; listView_res.Items.Add(lvi); count++; } if(count==0) { MessageBox.Show(“沒有查詢到匹配的數據”); } } catch (Exception ex) { MessageBox.Show(“出現數據查詢錯誤，請重試” + ex.Message); } finally { sqlcon.Close(); } break; case “查詢數據”: res = “select * from [用戶表]”; string [] wh=get_where(); bool tags=false; if(wh!=null) { res+=” where “; if (wh[0]!= “”) { res += ” 用戶名=@user “; //res+=wh[0]; tags=true; } if (wh[1] != “”) { if(tags) res+=”and “; res+=” 密碼=@pass “; //res+=wh[1]; tags=true; } if (wh[2] != “”) { if(tags) res += ” and “; res+=” 電話=@tel “; //res+=wh[2]; tags=true; } if(wh[3]!=”“) { if(tags) res += ” and “; res+=” 郵箱=@mail “; // res+=wh[1]; } sqlcom.CommandText = res; if (wh != null) { if (wh[0] != “”) { sqlcom.Parameters.AddWithValue(“@user”, wh[0]); } if (wh[1] != “”) { sqlcom.Parameters.AddWithValue(“@pass”, wh[1]); } if (wh[2] != “”) { sqlcom.Parameters.AddWithValue(“@tel”, wh[2]); } if (wh[3] != “”) { sqlcom.Parameters.AddWithValue(“@mail”, wh[3]); } } try { sda = sqlcom.ExecuteReader(); listView_res.Items.Clear(); if (sda.Read()) { ListViewItem lvi = new ListViewItem(); lvi.Text = sda.GetString(0); lvi.SubItems.Add(sda.GetString(1)); lvi.SubItems.Add(sda.GetString(2)); lvi.SubItems.Add(sda.GetString(3)); lvi.UseItemStyleForSubItems = false; lvi.SubItems[0].BackColor = Color.HotPink; lvi.SubItems[1].BackColor = Color.LightSkyBlue; lvi.SubItems[2].BackColor = Color.SpringGreen; lvi.SubItems[3].BackColor = Color.Orange; listView_res.Items.Add(lvi); } else { MessageBox.Show(“沒有查詢到匹配的數據，請檢查原因”); } } catch (Exception ex) { MessageBox.Show(“出現數據查詢錯誤，請重試” + ex.Message); } finally { sqlcon.Close(); } } break; case “插入數據”: res = “insert into [用戶表](“; string [] vl=get_value(); bool tagi=false; if (vl != null) { if (vl[0] != “”) { res += “用戶名”; tagi = true; } if (vl[1] != “”) { if (tagi) res+=” , “; res+=”密碼 “; tagi = true; } if (vl[2] != “”) { if (tagi) res+=” , “; res+=”電話”; tagi = true; } if (vl[3] != “”) { if (tagi) res+=” , “; res+=”郵箱”; } } res += ” ) values(“; /*** 構造values *******/ tagi=false; if (vl != null) { if (vl[0] != “”) { res += “@user”; //res += vl[0]; tagi = true; } if (vl[1] != “”) { if (tagi) res+=” , “; res+=”@pass”; //res += vl[1]; tagi = true; } if (vl[2] != “”) { if (tagi) res+=” , “; res+=”@tel”; // res += vl[2]; tagi = true; } if (vl[3] != “”) { if (tagi) res+=” , “; res+=”@mail”; //res += vl[1]; } } res += ” )”; sqlcom.CommandText = res; if (vl != null) { if (vl[0] != “”) { sqlcom.Parameters.AddWithValue(“@user”, vl[0]); } if (vl[1] != “”) { sqlcom.Parameters.AddWithValue(“@pass”, vl[1]); } if (vl[2] != “”) { sqlcom.Parameters.AddWithValue(“@tel”, vl[2]); } if (vl[3] != “”) { sqlcom.Parameters.AddWithValue(“@mail”, vl[3]); } } int i = sqlcom.ExecuteNonQuery(); if (i > 0) { MessageBox.Show(“插入成功,請瀏覽數據”); } else { MessageBox.Show(“插入失敗，請檢查原因”); } sqlcon.Close(); break; case “更新數據”: res = “update [用戶表] set”; string [] vlu=get_value(); string[] whu = get_where(); bool tagu=false; if (vlu != null) { if (vlu[0] != “”) { res += ” 用戶名=”; res += ” @user2”; tagu = true; } if (vlu[1] != “”) { if (tagu) res+=” , “; res+=” 密碼=”; res += ” @pass2”; tagu = true; } if (vlu[2] != “”) { if (tagu) res+=” , “; res+=” 電話=”; res += ” @tel2”; tagu = true; } if (vlu[3] != “”) { if (tagu) res+=” , “; res+=” 郵箱=”; res+=” @mail2”; } } tagu = false; if (whu != null) { res+=” where “; if (whu[0] != “”) { res += ” 用戶名=@user “; //res+=wh[0]; tagu = true; } if (whu[1] != “”) { if (tagu) res+=”and “; res+=” 密碼=@pass “; //res+=wh[1]; tagu = true; } if (whu[2] != “”) { if (tagu) res += ” and “; res+=” 電話=@tel “; //res+=wh[2]; tagu = true; } if (whu[3] != “”) { if (tagu) res += ” and “; res+=” 郵箱=@mail “; // res+=wh[1]; } } / * * */ sqlcom.CommandText = res; if (vlu != null) { if (vlu[0] != “”) { sqlcom.Parameters.AddWithValue(“@user2”, vlu[0]); } if (vlu[1] != “”) { sqlcom.Parameters.AddWithValue(“@pass2”, vlu[1]); } if (vlu[2] != “”) { sqlcom.Parameters.AddWithValue(“@tel2”, vlu[2]); } if (vlu[3] != “”) { sqlcom.Parameters.AddWithValue(“@mail2”, vlu[3]); } } if (whu != null) { if (whu[0] != “”) { sqlcom.Parameters.AddWithValue(“@user”, whu[0]); } if (whu[1] != “”) { sqlcom.Parameters.AddWithValue(“@pass”, whu[1]); } if (whu[2] != “”) { sqlcom.Parameters.AddWithValue(“@tel”, whu[2]); } if (whu[3] != “”) { sqlcom.Parameters.AddWithValue(“@mail”, whu[3]); } } int pcount = sqlcom.ExecuteNonQuery(); if (pcount > 0) { MessageBox.Show(“更新成功,請瀏覽數據”); } else { MessageBox.Show(“更新失敗，請檢查原因”); } sqlcon.Close(); break; case “刪除數據”: res = “delete from [用戶表]”; string [] whd=get_where(); bool tagd=false; if (whd != null) { res+=” where “; if (whd[0] != “”) { res += ” 用戶名=@user “; //res+=wh[0]; tagd = true; } if (whd[1] != “”) { if (tagd) res+=”and “; res+=” 密碼=@pass “; //res+=wh[1]; tagd = true; } if (whd[2] != “”) { if (tagd) res += ” and “; res+=” 電話=@tel “; //res+=wh[2]; tagd = true; } if (whd[3] != “”) { if (tagd) res += ” and “; res+=” 郵箱=@mail “; // res+=wh[1]; } sqlcom.CommandText = res; if (whd != null) { if (whd[0] != “”) { sqlcom.Parameters.AddWithValue(“@user”, whd[0]); } if (whd[1] != null) { sqlcom.Parameters.AddWithValue(“@pass”, whd[1]); } if (whd[2] != null) { sqlcom.Parameters.AddWithValue(“@tel”, whd[2]); } if (whd[3] != null) { sqlcom.Parameters.AddWithValue(“@mail”, whd[3]); } } try { int rc=sqlcom.ExecuteNonQuery(); if (rc > 0) { MessageBox.Show(“數據刪除成功，請瀏覽數據”); } else { MessageBox.Show(“數據刪除失敗，請檢查原因”); } } catch (Exception ex) { MessageBox.Show(“出現數據查詢錯誤，請重試” + ex.Message); } finally { sqlcon.Close(); } } break; } return res; } private void button1_Click(object sender, EventArgs e) { init_controler(); } private void button2_Click(object sender, EventArgs e) { if (comboBox1.SelectedItem == null) { MessageBox.Show(“請選擇右上方的操作后再執行確認”); return; } execu_sql(); init_controler(); } }}
新聞熱點
疑難解答
