电话本管理程序（实现增删改查功能）
由于刚开始学习ADO.NET在Winform上的应用，所以程序编写过程中出现了好多的问题。但是现在问题基本上都解决了，所以今天把代码放上来，给想我这样的初学者们一个思路的提示。用到的控件有：TextBox，ComboBox，button，DataGridView
首先，先放上程序运行后的截图：
上代码：
首先还是APP.config文件,这个文件的作用是连接字符串的时候使用，方便了以后的修改 PS：记得添加引用和命名空间，不会的看我上一篇文章
1 <?xml version="1.0" encoding="utf-8" ?> 2 <configuration> 3 <connectionStrings> 4 <add name="connStr" connectionString="Data Source=ZIP-CW\MSSQLSERVER0;Initial Catalog=PhoneNumManager;Integrated Security=True"/> 5 </connectionStrings> 6 </configuration>
然后是Group类，这个类的作用是用在ComboBox选择某项的时候可以获得这项的Id号
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 namespace PhoneBook 7 { 8 class Group 9 { 10 private string pId; 11 12 public string PId 13 { 14 get { return pId; } 15 set { pId = value; } 16 } 17 private string ptName; 18 19 public string PtName 20 { 21 get { return ptName; } 22 set { ptName = value; } 23 } 24 25 public override string ToString() 26 { 27 return ptName; 28 } 29 } 30 }
最后就是整个程序主要的代码了
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.Configuration; 10 using System.Data.SqlClient; 11 12 namespace PhoneBook 13 { 14 public partial class Form1 : Form 15 { 16 public Form1() 17 { 18 InitializeComponent(); 19 } 20 21 //连接字符串 22 string conStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; 23 24 private void Form1_Load(object sender, EventArgs e) 25 { 26 string sql = "select * from [PhoneNum]"; 27 GetPhoneNum(sql); 28 LoadCbo(); 29 30 } 31 32 /// <summary> 33 /// 执行查询数据库功能的方法 34 /// </summary> 35 /// <param name="sql">查询数据库的语句</param> 36 private void GetPhoneNum(string sql) 37 { 38 using (SqlConnection conn = new SqlConnection(conStr)) 39 { 40 DataSet ds = new DataSet(); 41 SqlDataAdapter sda = new SqlDataAdapter(sql, conn); //用于查询数据库，并填充DataSet集;创建DataAdapter数据适配器实例 42 sda.Fill(ds); //填充DataSet集 43 dgvPhoneBook.DataSource = ds.Tables[0]; 44 } 45 } 46 47 /// <summary> 48 /// 获得执行的SQL语句的方法 49 /// </summary> 50 /// <returns>返回拼接好的SQL语句</returns> 51 private string GetString() 52 { 53 //为了简单和易读性的考虑，使用where 1=1 54 StringBuilder sql = new StringBuilder("select * from [PhoneNum] where 1=1 "); 55 if (txtName.Text != "") 56 { 57 sql.AppendLine(" and pName like '%" + txtName.Text.Trim() + "%'"); 58 } 59 60 if (txtPhoneNumber.Text != "") 61 { 62 sql.AppendLine(" and pCellPhone like '%" + txtPhoneNumber.Text.Trim() + "%'"); 63 } 64 //类型转换 65 Group group = cboGroup.SelectedItem as Group; 66 if (cboGroup.SelectedItem != null) 67 { 68 if (group.PId == "1") 69 { 70 sql.AppendLine(" and pTypeId=1"); 71 } 72 else if (group.PId == "2") 73 { 74 sql.AppendLine(" and pTypeId=2"); 75 } 76 else if (group.PId == "3") 77 { 78 sql.AppendLine(" and pTypeId=3"); 79 } 80 else if (group.PId == "4") 81 { 82 sql.AppendLine(" and pTypeId=4"); 83 } 84 85 } 86 return sql.ToString(); 87 } 88 //查询按钮的响应 89 private void btnSearch_Click(object sender, EventArgs e) 90 { 91 GetPhoneNum(GetString()); 92 } 93 //combox下拉列表选择的事件响应 94 private void cboGroup_SelectedIndexChanged(object sender, EventArgs e) 95 { 96 GetPhoneNum(GetString()); 97 } 98 //加载Combox 99 private void LoadCbo() 100 { 101 Group group = new Group(); 102 group.PtName = "全部"; 103 group.PId = "0"; 104 cboGroup.Items.Add(group); 105 cboAddGroup.Items.Add(group); 106 107 Group group1 = new Group(); 108 group1.PtName = "朋友"; 109 group1.PId = "1"; 110 cboGroup.Items.Add(group1); 111 cboAddGroup.Items.Add(group1); 112 113 Group group2 = new Group(); 114 group2.PtName = "同事"; 115 group2.PId = "2"; 116 cboGroup.Items.Add(group2); 117 cboAddGroup.Items.Add(group2); 118 119 Group group3 = new Group(); 120 group3.PtName = "同学"; 121 group3.PId = "3"; 122 cboGroup.Items.Add(group3); 123 cboAddGroup.Items.Add(group3); 124 125 Group group4 = new Group(); 126 group4.PtName = "家人"; 127 group4.PId = "4"; 128 cboGroup.Items.Add(group4); 129 cboAddGroup.Items.Add(group4); 130 } 131 132 /// <summary> 133 /// 执行增删改的操作 134 /// </summary> 135 private void BindDgv() 136 { 137 string sql = "select * from [PhoneNum] order by pId desc"; 138 using (SqlConnection conn = new SqlConnection(conStr)) 139 { 140 DataTable dt = new DataTable(); 141 SqlDataAdapter sda = new SqlDataAdapter(sql,conn); 142 sda.Fill(dt); 143 dgvPhoneBook.DataSource = dt; 144 } 145 } 146 147 //加载增删改部分的combox 148 private void LoadAddCbo() 149 { 150 Group group = new Group(); 151 group.PtName = "全部"; 152 group.PId = "0"; 153 cboAddGroup.Items.Add(group); 154 155 Group group1 = new Group(); 156 group1.PtName = "朋友"; 157 group1.PId = "1"; 158 cboAddGroup.Items.Add(group1); 159 160 Group group2 = new Group(); 161 group2.PtName = "同事"; 162 group2.PId = "2"; 163 cboAddGroup.Items.Add(group2); 164 165 Group group3 = new Group(); 166 group3.PtName = "同学"; 167 group3.PId = "3"; 168 cboAddGroup.Items.Add(group3); 169 170 Group group4 = new Group(); 171 group4.PtName = "家人"; 172 group4.PId = "4"; 173 cboAddGroup.Items.Add(group4); 174 } 175 176 /// <summary> 177 /// 增加新的数据行 178 /// </summary> 179 /// <param name="sender"></param> 180 /// <param name="e"></param> 181 private void btnAdd_Click(object sender, EventArgs e) 182 { 183 Group group = cboAddGroup.SelectedItem as Group; 184 string sql = string.Format("insert into [PhoneNum](pTypeId, pName, pCellPhone, pHomePhone) values({0},'{1}','{2}','{3}')", group.PId, txtAddName.Text, txtAddPhoneNumber.Text, txtAddHomeNum.Text); 185 GetMethod(sql); 186 } 187 188 /// <summary> 189 /// 执行增删改数据库的方法 190 /// </summary> 191 /// <param name="sql">要执行的SQL语句</param> 192 private void GetMethod(string sql) 193 { 194 using (SqlConnection conn = new SqlConnection(conStr)) 195 { 196 using (SqlCommand cmd = new SqlCommand(sql, conn)) 197 { 198 conn.Open(); 199 cmd.ExecuteNonQuery(); 200 BindDgv(); 201 } 202 } 203 } 204 205 /// <summary> 206 /// 删除选中的数据行 207 /// </summary> 208 /// <param name="sender"></param> 209 /// <param name="e"></param> 210 private void btnDel_Click(object sender, EventArgs e) 211 { 212 int index = 0; 213 string sql = string.Format("delete from [PhoneNum] where pId={0}",dgvPhoneBook[index,dgvPhoneBook.SelectedCells[0].RowIndex].Value); 214 GetMethod(sql); 215 } 216 217 /// <summary> 218 /// 修改选中的数据行 219 /// </summary> 220 /// <param name="sender"></param> 221 /// <param name="e"></param> 222 private void btnChange_Click(object sender, EventArgs e) 223 { 224 int index = 0; 225 Group group = cboAddGroup.SelectedItem as Group; 226 string sql = string.Format("update [PhoneNum] set pTypeId={0},pName='{1}',pCellPhone='{2}',pHomePhone='{3}' where pId={4}", group.PId, txtAddName.Text, txtAddPhoneNumber.Text, txtAddHomeNum.Text, dgvPhoneBook[index, dgvPhoneBook.SelectedCells[0].RowIndex].Value); 227 GetMethod(sql); 228 } 229 230 231 232 233 } 234 }
代码写的比较乱，希望大家能看懂
