传智播客数据绑定和数据库开发基础(第四季)-杨中科
时间：2022-03-14 13:10
(一)数据绑定、ListBox、DataGrid SQLServer基础、SQLServer使用主键策略
(二)DataReader、DataSet、参数化查询、防注入漏洞攻击、SQLHelper
用户界面中进行登录判断。输错三次禁止登陆（半小时），用数据库记录ErrorTimes。 数据导入：从文本文件导入用户信息。易错点：Parameter的重复添加。File.ReadAllLines() 数据导出：将用户信息导出到文本文件。File.WriteAllLines()
省市联动选择 手机号码归属地查询
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.Data.SqlClient;
10
11 namespace adonet
12 {
13 public partial class Form1 : Form
14 {
15 public Form1()
16 {
17 InitializeComponent();
18 }
19
20 private void button1_Click(object sender, EventArgs e)
21 {
22 SqlHelper.ExecuteNonQuery("insert into T_Student(Name,Age) values(‘a‘,33)");
23 DataTable table = SqlHelper.ExecuteDataTable("select * from T_Student where Age=@Age or Name=@Name",
24 new SqlParameter("@Age", 26), new SqlParameter("name", "刘洋"));
25 foreach (DataRow row in table.Rows)
26 {
27 string name = (string)row["name"];
28 MessageBox.Show(name);
29 }
30 }
31 }
32 }
View Code
1 namespace adonet
2 {
3 partial class Form1
4 {
5 /// <summary>
6 /// 必需的设计器变量。
7 /// </summary>
8 private System.ComponentModel.IContainer components = null;
9
10 /// <summary>
11 /// 清理所有正在使用的资源。
12 /// </summary>
13 /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
14 protected override void Dispose(bool disposing)
15 {
16 if (disposing && (components != null))
17 {
18 components.Dispose();
19 }
20 base.Dispose(disposing);
21 }
22
23 #region Windows 窗体设计器生成的代码
24
25 /// <summary>
26 /// 设计器支持所需的方法 - 不要
27 /// 使用代码编辑器修改此方法的内容。
28 /// </summary>
29 private void InitializeComponent()
30 {
31 this.button1 = new System.Windows.Forms.Button();
32 this.SuspendLayout();
33 //
34 // button1
35 //
36 this.button1.Location = new System.Drawing.Point(88, 78);
37 this.button1.Name = "button1";
38 this.button1.Size = new System.Drawing.Size(75, 23);
39 this.button1.TabIndex = 0;
40 this.button1.Text = "button1";
41 this.button1.UseVisualStyleBackColor = true;
42 this.button1.Click += new System.EventHandler(this.button1_Click);
43 //
44 // Form1
45 //
46 this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
47 this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
48 this.ClientSize = new System.Drawing.Size(284, 261);
49 this.Controls.Add(this.button1);
50 this.Name = "Form1";
51 this.Text = "Form1";
52 this.ResumeLayout(false);
53
54 }
55
56 #endregion
57
58 private System.Windows.Forms.Button button1;
59 }
60 }
View Code
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Windows.Forms;
5
6 namespace adonet
7 {
8 static class Program
9 {
10 /// <summary>
11 /// 应用程序的主入口点。
12 /// </summary>
13 [STAThread]
14 static void Main()
15 {
16 Application.EnableVisualStyles();
17 Application.SetCompatibleTextRenderingDefault(false);
18 Application.Run(new Form1());
19 }
20 }
21 }
View Code
sqlhelper
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Data.SqlClient;
6 using System.Configuration;
7 using System.Data;
8
9 namespace adonet
10 {
11 class SqlHelper
12 {
13 private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
14
15 public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
16 {
17 using (SqlConnection conn = new SqlConnection(connStr))
18 {
19 conn.Open();
20 using (SqlCommand cmd = conn.CreateCommand())
21 {
22 cmd.CommandText = sql;
23 cmd.Parameters.AddRange(parameters);
24 return cmd.ExecuteNonQuery();
25 }
26 }
27 }
28
29 public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
30 {
31 using (SqlConnection conn = new SqlConnection(connStr))
32 {
33 conn.Open();
34 using (SqlCommand cmd = conn.CreateCommand())
35 {
36 cmd.CommandText = sql;
37 cmd.Parameters.AddRange(parameters);
38 return cmd.ExecuteScalar();
39 }
40 }
41 }
42
43 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
44 {
45 using (SqlConnection conn = new SqlConnection(connStr))
46 {
47 conn.Open();
48 using (SqlCommand cmd = conn.CreateCommand())
49 {
50 cmd.CommandText = sql;
51 cmd.Parameters.AddRange(parameters);
52
53 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
54 DataSet dataset=new DataSet();
55 adapter.Fill(dataset);
56 return dataset.Tables[0];
57 }
58 }
59 }
60 }
61 }
View Code
(三)ADO.Net案例：登陆、数据导入导出、省市联动选择
1 <?xml version="1.0" encoding="utf-8" ?> 2 <configuration> 3 <connectionStrings> 4 <add name="dbConnStr" connectionString="data source=.;initial catalog=Scott;user id=sa;password=222222"/> 5 </connectionStrings> 6 </configuration>View Code
1 <?xml version="1.0" encoding="utf-8" ?> 2 <configuration> 3 <connectionStrings> 4 <add name="myconnStr" connectionString ="data source=.;initial catalog=Scott;user id=sa;password=222222"/> 5 </connectionStrings> 6 </configuration>View Code
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5
6 namespace Adonet
7 {
8 class Area
9 {
10 public int AreaId
11 { get; set; }
12 public string AreaName
13 { get; set; }
14 }
15 }
View Code
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.Data.SqlClient;
10
11 namespace Adonet
12 {
13 public partial class LoginWindow : Form
14 {
15 public LoginWindow()
16 {
17 InitializeComponent();
18 }
19
20 private void btnLogin_Click(object sender, EventArgs e)
21 {
22 if (userName.Text.Length <= 0)
23 {
24 MessageBox.Show("请输入用户名");
25 return;
26 }
27 if (passWord.Text.Length <= 0)
28 {
29 MessageBox.Show("请输入密码");
30 return;
31 }
32
33 DataTable table = SqlHelper.ExecuteDataTable("select * from T_User where username=@username;",
34 new SqlParameter("@username", userName.Text));
35 if (table.Rows.Count <= 0)
36 {
37 MessageBox.Show("用户名不存在");
38 return;
39 }
40 if (table.Rows.Count > 1)
41 {
42 MessageBox.Show("不好啦，用户名重复");
43 return;
44 }
45 DataRow row = table.Rows[0];
46 string dbPassword = (string)row["Password"];
47 long id = (long)row["Id"];
48 int errorTimes = (int)row["ErrorTimes"];
49 if (errorTimes >= 3)
50 {
51 MessageBox.Show("输入次数过多，用户已经锁定");
52 return;
53 }
54 if (passWord.Text != dbPassword)
55 {
56 SqlHelper.ExecuteNonQuery("update T_User set ErrorTimes=ErrorTimes+1 where Id=@Id",
57 new SqlParameter("@Id", id));
58 MessageBox.Show("密码错误");
59 }
60 else
61 {
62 MessageBox.Show("登陆成功");
63 }
64 }
65 }
66 }
View Code
1 namespace Adonet
2 {
3 partial class LoginWindow
4 {
5 /// <summary>
6 /// 必需的设计器变量。
7 /// </summary>
8 private System.ComponentModel.IContainer components = null;
9
10 /// <summary>
11 /// 清理所有正在使用的资源。
12 /// </summary>
13 /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
14 protected override void Dispose(bool disposing)
15 {
16 if (disposing && (components != null))
17 {
18 components.Dispose();
19 }
20 base.Dispose(disposing);
21 }
22
23 #region Windows 窗体设计器生成的代码
24
25 /// <summary>
26 /// 设计器支持所需的方法 - 不要
27 /// 使用代码编辑器修改此方法的内容。
28 /// </summary>
29 private void InitializeComponent()
30 {
31 this.userName = new System.Windows.Forms.TextBox();
32 this.label1 = new System.Windows.Forms.Label();
33 this.label2 = new System.Windows.Forms.Label();
34 this.passWord = new System.Windows.Forms.TextBox();
35 this.btnLogin = new System.Windows.Forms.Button();
36 this.SuspendLayout();
37 //
38 // userName
39 //
40 this.userName.Location = new System.Drawing.Point(135, 34);
41 this.userName.Name = "userName";
42 this.userName.Size = new System.Drawing.Size(100, 21);
43 this.userName.TabIndex = 0;
44 //
45 // label1
46 //
47 this.label1.AutoSize = true;
48 this.label1.Location = new System.Drawing.Point(46, 37);
49 this.label1.Name = "label1";
50 this.label1.Size = new System.Drawing.Size(41, 12);
51 this.label1.TabIndex = 1;
52 this.label1.Text = "用户名";
53 //
54 // label2
55 //
56 this.label2.AutoSize = true;
57 this.label2.Location = new System.Drawing.Point(58, 104);
58 this.label2.Name = "label2";
59 this.label2.Size = new System.Drawing.Size(29, 12);
60 this.label2.TabIndex = 2;
61 this.label2.Text = "密码";
62 //
63 // passWord
64 //
65 this.passWord.Location = new System.Drawing.Point(135, 101);
66 this.passWord.Name = "passWord";
67 this.passWord.Size = new System.Drawing.Size(100, 21);
68 this.passWord.TabIndex = 3;
69 //
70 // btnLogin
71 //
72 this.btnLogin.Location = new System.Drawing.Point(88, 161);
73 this.btnLogin.Name = "btnLogin";
74 this.btnLogin.Size = new System.Drawing.Size(75, 23);
75 this.btnLogin.TabIndex = 4;
76 this.btnLogin.Text = "登录";
77 this.btnLogin.UseVisualStyleBackColor = true;
78 this.btnLogin.Click += new System.EventHandler(this.btnLogin_Click);
79 //
80 // LoginWindow
81 //
82 this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
83 this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
84 this.ClientSize = new System.Drawing.Size(284, 261);
85 this.Controls.Add(this.btnLogin);
86 this.Controls.Add(this.passWord);
87 this.Controls.Add(this.label2);
88 this.Controls.Add(this.label1);
89 this.Controls.Add(this.userName);
90 this.Name = "LoginWindow";
91 this.Text = "LoginWindow";
92 this.ResumeLayout(false);
93 this.PerformLayout();
94
95 }
96
97 #endregion
98
99 private System.Windows.Forms.TextBox userName;
100 private System.Windows.Forms.Label label1;
101 private System.Windows.Forms.Label label2;
102 private System.Windows.Forms.TextBox passWord;
103 private System.Windows.Forms.Button btnLogin;
104 }
105 }
View Code
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.Data.SqlClient;
10
11 namespace Adonet
12 {
13 public partial class CitySelectWindow : Form
14 {
15 public CitySelectWindow()
16 {
17 InitializeComponent();
18 }
19
20 Dictionary<string, int> dictProv = new Dictionary<string, int>();
21 private void CitySelectWindow_Load(object sender, EventArgs e)
22 {
23 DataTable dtProv = SqlHelper.ExecuteDataTable("select * from AreaFull where AreaPid=0");
24 //List<Area> listProv = new List<Area>();
25 //Area area = new Area();
26 foreach (DataRow row in dtProv.Rows)
27 {
28 //area.AreaId = (int)row["AreaId"];
29 //area.AreaName = (string)row["AreaName"];
30 //listProv.Add(area);
31 dictProv.Add((string)row["AreaName"], (int)row["AreaId"]);
32 lbProv.Items.Add((string)row["AreaName"]);
33 }
34 }
35
36 private void lbProv_SelectedIndexChanged(object sender, EventArgs e)
37 {
38 lbCity.Items.Clear();
39 string areaName = (string)lbProv.SelectedItem;
40 int areaId = dictProv[areaName];
41 DataTable dtCity = SqlHelper.ExecuteDataTable("select * from AreaFull where AreaPid=@AreaPid",
42 new SqlParameter("@AreaPid", areaId));
43 foreach (DataRow row in dtCity.Rows)
44 {
45 lbCity.Items.Add((string)row["AreaName"]);
46 }
47 //MessageBox.Show(id.ToString());
48 }
49 }
50 }
View Code
1 namespace Adonet
2 {
3 partial class CitySelectWindow
4 {
5 /// <summary>
6 /// Required designer variable.
7 /// </summary>
8 private System.ComponentModel.IContainer components = null;
9
10 /// <summary>
11 /// Clean up any resources being used.
12 /// </summary>
13 /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
14 protected override void Dispose(bool disposing)
15 {
16 if (disposing && (components != null))
17 {
18 components.Dispose();
19 }
20 base.Dispose(disposing);
21 }
22
23 #region Windows Form Designer generated code
24
25 /// <summary>
26 /// Required method for Designer support - do not modify
27 /// the contents of this method with the code editor.
28 /// </summary>
29 private void InitializeComponent()
30 {
31 this.lbProv = new System.Windows.Forms.ListBox();
32 this.lbCity = new System.Windows.Forms.ListBox();
33 this.lbXian = new System.Windows.Forms.ListBox();
34 this.SuspendLayout();
35 //
36 // lbProv
37 //
38 this.lbProv.FormattingEnabled = true;
39 this.lbProv.ItemHeight = 12;
40 this.lbProv.Location = new System.Drawing.Point(12, 12);
41 this.lbProv.Name = "lbProv";
42 this.lbProv.Size = new System.Drawing.Size(120, 340);
43 this.lbProv.TabIndex = 0;
44 this.lbProv.SelectedIndexChanged += new System.EventHandler(this.lbProv_SelectedIndexChanged);
45 //
46 // lbCity
47 //
48 this.lbCity.FormattingEnabled = true;
49 this.lbCity.ItemHeight = 12;
50 this.lbCity.Location = new System.Drawing.Point(138, 12);
51 this.lbCity.Name = "lbCity";
52 this.lbCity.Size = new System.Drawing.Size(120, 340);
53 this.lbCity.TabIndex = 1;
54 //
55 // lbXian
56 //
57 this.lbXian.FormattingEnabled = true;
58 this.lbXian.ItemHeight = 12;
59 this.lbXian.Location = new System.Drawing.Point(264, 12);
60 this.lbXian.Name = "lbXian";
61 this.lbXian.Size = new System.Drawing.Size(120, 340);
62 this.lbXian.TabIndex = 2;
63 //
64 // CitySelectWindow
65 //
66 this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
67 this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
68 this.ClientSize = new System.Drawing.Size(795, 367);
69 this.Controls.Add(this.lbXian);
70 this.Controls.Add(this.lbCity);
71 this.Controls.Add(this.lbProv);
72 this.Name = "CitySelectWindow";
73 this.Text = "CitySelectWindow";
74 this.Load += new System.EventHandler(this.CitySelectWindow_Load);
75 this.ResumeLayout(false);
76
77 }
78
79 #endregion
80
81 private System.Windows.Forms.ListBox lbProv;
82 private System.Windows.Forms.ListBox lbCity;
83 private System.Windows.Forms.ListBox lbXian;
84 }
85 }
View Code
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.IO;
10 using System.Data.SqlClient;
11
12 namespace Adonet
13 {
14 public partial class CustomerImportExportWindow : Form
15 {
16 public CustomerImportExportWindow()
17 {
18 InitializeComponent();
19 }
20
21 private void btnImport_Click(object sender, EventArgs e)
22 {
23 List<string> list=new List<string>();
24 OpenFileDialog ofd = new OpenFileDialog();
25 ofd.Filter = "所有文件|*.txt";
26 ofd.ShowDialog();
27 string filename = ofd.FileName;
28 using (StreamReader sr=new StreamReader(filename,Encoding.Default))
29 {
30 while (!sr.EndOfStream)
31 {
32 list.Add(sr.ReadLine());
33 }
34 //string[] strs = File.ReadAllLines(filename, Encoding.Default);
35 string[] strs = File.ReadLines(filename, Encoding.Default).ToArray();
36 foreach (string str in strs)
37 {
38 string[] segs = str.Split(new char[] { ‘\t‘ }, StringSplitOptions.RemoveEmptyEntries);
39 string name = segs[0];
40 string age = segs[1];
41 SqlHelper.ExecuteNonQuery("insert into T_Customer(Name,Age) values(@Name,@Age)",
42 new SqlParameter("@Name", name),
43 new SqlParameter("Age", Convert.ToInt32(age)));
44 }
45 MessageBox.Show("导入成功！成功导入" + strs.Length + "条数据！");
46 //foreach (string line in list)
47 //{
48 // string[] segs = line.Split(new char[] { ‘\t‘ }, StringSplitOptions.RemoveEmptyEntries);
49 // string name = segs[0];
50 // string age = segs[1];
51 // SqlHelper.ExecuteNonQuery("Insert into T_Customer(Name,Age) values(@Name,@Age)",
52 // new SqlParameter("@Name", name),
53 // new SqlParameter("@Age", Convert.ToInt32(age)));
54 //}
55 //MessageBox.Show("导入成功！成功导入" + list.Count + "条数据！");
56 }
57 }
58 }
59 }
View Code
1 namespace Adonet
2 {
3 partial class CustomerImportExportWindow
4 {
5 /// <summary>
6 /// Required designer variable.
7 /// </summary>
8 private System.ComponentModel.IContainer components = null;
9
10 /// <summary>
11 /// Clean up any resources being used.
12 /// </summary>
13 /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
14 protected override void Dispose(bool disposing)
15 {
16 if (disposing && (components != null))
17 {
18 components.Dispose();
19 }
20 base.Dispose(disposing);
21 }
22
23 #region Windows Form Designer generated code
24
25 /// <summary>
26 /// Required method for Designer support - do not modify
27 /// the contents of this method with the code editor.
28 /// </summary>
29 private void InitializeComponent()
30 {
31 this.btnImport = new System.Windows.Forms.Button();
32 this.SuspendLayout();
33 //
34 // btnImport
35 //
36 this.btnImport.Location = new System.Drawing.Point(98, 95);
37 this.btnImport.Name = "btnImport";
38 this.btnImport.Size = new System.Drawing.Size(75, 23);
39 this.btnImport.TabIndex = 0;
40 this.btnImport.Text = "导入";
41 this.btnImport.UseVisualStyleBackColor = true;
42 this.btnImport.Click += new System.EventHandler(this.btnImport_Click);
43 //
44 // CustomerImportExportWindow
45 //
46 this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
47 this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
48 this.ClientSize = new System.Drawing.Size(284, 261);
49 this.Controls.Add(this.btnImport);
50 this.Name = "CustomerImportExportWindow";
51 this.Text = "CustomerImportExportWindow";
52 this.ResumeLayout(false);
53
54 }
55
56 #endregion
57
58 private System.Windows.Forms.Button btnImport;
59 }
60 }
View Code
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Windows.Forms;
5
6 namespace Adonet
7 {
8 static class Program
9 {
10 /// <summary>
11 /// 应用程序的主入口点。
12 /// </summary>
13 [STAThread]
14 static void Main()
15 {
16 Application.EnableVisualStyles();
17 Application.SetCompatibleTextRenderingDefault(false);
18 Application.Run(new CitySelectWindow());
19 }
20 }
21 }
View Code
sqlhelper
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Configuration;
6 using System.Data.SqlClient;
7 using System.Data;
8
9 namespace Adonet
10 {
11 static class SqlHelper
12 {
13 private static string connStr = ConfigurationManager.ConnectionStrings["myconnStr"].ConnectionString;
14
15 public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
16 {
17 using (SqlConnection conn = new SqlConnection(connStr))
18 {
19 conn.Open();
20 using (SqlCommand cmd = conn.CreateCommand())
21 {
22 cmd.CommandText = sql;
23 cmd.Parameters.AddRange(parameters);
24 return cmd.ExecuteNonQuery();
25 }
26 }
27 }
28
29 public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
30 {
31 using (SqlConnection conn = new SqlConnection(connStr))
32 {
33 conn.Open();
34 using (SqlCommand cmd = conn.CreateCommand())
35 {
36 cmd.CommandText = sql;
37 cmd.Parameters.AddRange(parameters);
38 return cmd.ExecuteScalar();
39 }
40 }
41 }
42
43 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
44 {
45 using (SqlConnection conn = new SqlConnection(connStr))
46 {
47 conn.Open();
48 using (SqlCommand cmd = conn.CreateCommand())
49 {
50 cmd.CommandText = sql;
51 cmd.Parameters.AddRange(parameters);
52 DataSet dataSet = new DataSet();
53 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
54 adapter.Fill(dataSet);
55 return dataSet.Tables[0];
56 }
57 }
58 }
59 }
60 }
View Code
(四)ADO.Net案例：手机号码归属地查询、DbValue.Null的处理、三层架构
(五)典型的ListUI+EditUI、人事管理系统需求分析、架构设计、MD5算法、程序登录
DAL：
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using Customer.Model;
6 using System.Data;
7 using System.Data.SqlClient;
8
9 namespace Customer.DAL
10 {
11 public static class CustomerDAL
12 {
13 public static CustomerModel ToCustomer(DataRow row)
14 {
15 CustomerModel cust = new CustomerModel();
16 cust.Id = (long)row["Id"];
17 cust.Name = (string)row["Name"];
18 cust.Birthday = (DateTime?)SqlHelper.FromDBValue(row["Birthday"]);
19 cust.Address = (string)row["Address"];
20 cust.TelNum = (string)row["TelNum"];
21 cust.CustLevel = (int)row["CustLevel"];
22 return cust;
23 }
24
25 public static CustomerModel GetById(long id)
26 {
27 DataTable table = SqlHelper.ExecuteDataTable("select * from T_Customer where Id=@Id",
28 new SqlParameter("@Id", id));
29 if (table.Rows.Count <= 0)
30 {
31 return null;
32 }
33 else if (table.Rows.Count > 1)
34 {
35 throw new Exception("严重错误，查出多条数据！");
36 }
37 else
38 {
39 return ToCustomer(table.Rows[0]);
40 }
41 }
42
43 public static CustomerModel[] GetAll()
44 {
45
46 DataTable table = SqlHelper.ExecuteDataTable("select * from T_Customer");
47 CustomerModel[] customers=new CustomerModel[table.Rows.Count];
48 for (int i = 0; i < table.Rows.Count; i++)
49 {
50 customers[i] = ToCustomer(table.Rows[i]);
51 }
52 return customers;
53 }
54
55 public static void DeleteById(long id)
56 {
57 SqlHelper.ExecuteNonQuery("delete from T_Customer where Id=@Id",
58 new SqlParameter("@Id", id));
59 }
60
61 public static void Insert(CustomerModel cust)
62 {
63 SqlHelper.ExecuteNonQuery(@"insert into T_Customer (Name,Birthday,Address,TelNum,CustLevel)
64 values(@Name,@Birthday,@Address,@TelNum,@CustLevel)",
65 new SqlParameter("@Name", cust.Name),
66 new SqlParameter("@Birthday", cust.Birthday),
67 new SqlParameter("@Address", cust.Address),
68 new SqlParameter("@TelNum", cust.TelNum),
69 new SqlParameter("@CustLevel", cust.CustLevel));
70 }
71
72 public static void Update(CustomerModel cust)
73 {
74 SqlHelper.ExecuteNonQuery(@"update T_Customer
75 set Name=@Name,Birthday=@Birthday,Address=@Address,TelNum=@TelNum,CustLevel=@CustLevel
76 where Id=@Id",
77 new SqlParameter("@Name", cust.Name),
78 new SqlParameter("@Birthday", cust.Birthday),
79 new SqlParameter("@Address", cust.Address),
80 new SqlParameter("@TelNum", cust.TelNum),
81 new SqlParameter("@CustLevel", cust.CustLevel),
82 new SqlParameter("@Id", cust.Id));
83 }
84 }
85 }
View Code
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Configuration;
6 using System.Data.SqlClient;
7 using System.Data;
8
9 namespace Customer.DAL
10 {
11 class SqlHelper
12 {
13 public static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
14
15 public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
16 {
17 using (SqlConnection conn = new SqlConnection(connStr))
18 {
19 conn.Open();
20 using (SqlCommand cmd = conn.CreateCommand())
21 {
22 cmd.CommandText = sql;
23 cmd.Parameters.AddRange(parameters);
24 return cmd.ExecuteNonQuery();
25 }
26 }
27 }
28
29 public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
30 {
31 using (SqlConnection conn = new SqlConnection(connStr))
32 {
33 conn.Open();
34 using (SqlCommand cmd = conn.CreateCommand())
35 {
36 cmd.CommandText = sql;
37 cmd.Parameters.AddRange(parameters);
38 return cmd.ExecuteScalar();
39 }
40 }
41 }
42
43 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
44 {
45 using (SqlConnection conn = new SqlConnection(connStr))
46 {
47 conn.Open();
48 using (SqlCommand cmd = conn.CreateCommand())
49 {
50 cmd.CommandText = sql;
51 cmd.Parameters.AddRange(parameters);
52 DataSet dataset = new DataSet();
53 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
54 adapter.Fill(dataset);
55 return dataset.Tables[0];
56 }
57 }
58 }
59
60 public static object FromDBValue(object value)
61 {
62 if (DBNull.Value == value)
63 {
64 return null;
65 }
66 else
67 {
68 return value;
69 }
70 }
71
72 public static object ToDBValue(object value)
73 {
74 if (null == value)
75 {
76 return DBNull.Value;
77 }
78 else
79 {
80 return value;
81 }
82 }
83 }
84 }
View Code
images:
Model:
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5
6 namespace Customer.Model
7 {
8 public class CustomerModel
9 {
10 public long Id { get; set; }
11 public string Name { get; set; }
12 public DateTime? Birthday { get; set; }
13 public string Address { get; set; }
14 public string TelNum { get; set; }
15 public int CustLevel { get; set; }
16 }
17 }
View Code
1 <?xml version="1.0" encoding="utf-8" ?> 2 <configuration> 3 <connectionStrings> 4 <add name="connStr" connectionString="server=.;database=Scott;uid=sa;pwd=222222"/> 5 </connectionStrings> 6 </configuration>View Code
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 Customer.Model;
10 using Customer.DAL;
11
12 namespace Customer
13 {
14 public partial class CustomerEditUI : Form
15 {
16 public CustomerEditUI()
17 {
18 InitializeComponent();
19 }
20
21 public bool isInsert { get; set; }
22 public long EditingId { get; set; }
23
24 private void btnSave_Click(object sender, EventArgs e)
25 {
26 CustomerModel cust = new CustomerModel();
27 cust.Name = txtName.Text;
28 cust.Birthday = dtBirthday.Value;
29 cust.TelNum = txtTelNum.Text;
30 cust.CustLevel = int.Parse(txtCustLevel.Text);
31 cust.Address = txtAddress.Text;
32 if (isInsert)
33 {
34 CustomerDAL.Insert(cust);
35 }
36 else
37 {
38 cust.Id=EditingId;
39 CustomerDAL.Update(cust);
40 }
41 DialogResult = DialogResult.Yes;
42 }
43
44 private void btnCancel_Click(object sender, EventArgs e)
45 {
46 DialogResult = DialogResult.No;
47 }
48
49 private void CustomerEditUI_Load(object sender, EventArgs e)
50 {
51 if (isInsert)
52 {
53 txtName.Focus();
54 txtCustLevel.Text = "2";
55 }
56 else
57 {
58 CustomerModel cust = new CustomerModel();
59 cust = CustomerDAL.GetById(EditingId);
60 txtName.Text = cust.Name;
61 dtBirthday.Value = (DateTime)cust.Birthday;
62 txtTelNum.Text = cust.TelNum;
63 txtCustLevel.Text = cust.CustLevel.ToString();
64 txtAddress.Text = cust.Address;
65 }
66 }
67 }
68 }
View Code
1 namespace Customer
2 {
3 partial class CustomerEditUI
4 {
5 /// <summary>
6 /// Required designer variable.
7 /// </summary>
8 private System.ComponentModel.IContainer components = null;
9
10 /// <summary>
11 /// Clean up any resources being used.
12 /// </summary>
13 /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
14 protected override void Dispose(bool disposing)
15 {
16 if (disposing && (components != null))
17 {
18 components.Dispose();
19 }
20 base.Dispose(disposing);
21 }
22
23 #region Windows Form Designer generated code
24
25 /// <summary>
26 /// Required method for Designer support - do not modify
27 /// the contents of this method with the code editor.
28 /// </summary>
29 private void InitializeComponent()
30 {
31 this.txtName = new System.Windows.Forms.TextBox();
32 this.txtTelNum = new System.Windows.Forms.TextBox();
33 this.txtCustLevel = new System.Windows.Forms.TextBox();
34 this.txtAddress = new System.Windows.Forms.TextBox();
35 this.label1 = new System.Windows.Forms.Label();
36 this.label2 = new System.Windows.Forms.Label();
37 this.label3 = new System.Windows.Forms.Label();
38 this.label4 = new System.Windows.Forms.Label();
39 this.label5 = new System.Windows.Forms.Label();
40 this.btnSave = new System.Windows.Forms.Button();
41 this.btnCancel = new System.Windows.Forms.Button();
42 this.dtBirthday = new System.Windows.Forms.DateTimePicker();
43 this.SuspendLayout();
44 //
45 // txtName
46 //
47 this.txtName.Location = new System.Drawing.Point(146, 54);
48 this.txtName.Name = "txtName";
49 this.txtName.Size = new System.Drawing.Size(100, 21);
50 this.txtName.TabIndex = 0;
51 //
52 // txtTelNum
53 //
54 this.txtTelNum.Location = new System.Drawing.Point(503, 57);
55 this.txtTelNum.Name = "txtTelNum";
56 this.txtTelNum.Size = new System.Drawing.Size(100, 21);
57 this.txtTelNum.TabIndex = 1;
58 //
59 // txtCustLevel
60 //
61 this.txtCustLevel.Location = new System.Drawing.Point(503, 117);
62 this.txtCustLevel.Name = "txtCustLevel";
63 this.txtCustLevel.Size = new System.Drawing.Size(100, 21);
64 this.txtCustLevel.TabIndex = 3;
65 //
66 // txtAddress
67 //
68 this.txtAddress.Location = new System.Drawing.Point(146, 179);
69 this.txtAddress.Name = "txtAddress";
70 this.txtAddress.Size = new System.Drawing.Size(457, 21);
71 this.txtAddress.TabIndex = 4;
72 //
73 // label1
74 //
75 this.label1.AutoSize = true;
76 this.label1.Location = new System.Drawing.Point(77, 60);
77 this.label1.Name = "label1";
78 this.label1.Size = new System.Drawing.Size(29, 12);
79 this.label1.TabIndex = 5;
80 this.label1.Text = "姓名";
81 //
82 // label2
83 //
84 this.label2.AutoSize = true;
85 this.label2.Location = new System.Drawing.Point(426, 60);
86 this.label2.Name = "label2";
87 this.label2.Size = new System.Drawing.Size(53, 12);
88 this.label2.TabIndex = 6;
89 this.label2.Text = "电话号码";
90 //
91 // label3
92 //
93 this.label3.AutoSize = true;
94 this.label3.Location = new System.Drawing.Point(77, 120);
95 this.label3.Name = "label3";
96 this.label3.Size = new System.Drawing.Size(29, 12);
97 this.label3.TabIndex = 7;
98 this.label3.Text = "生日";
99 //
100 // label4
101 //
102 this.label4.AutoSize = true;
103 this.label4.Location = new System.Drawing.Point(426, 120);
104 this.label4.Name = "label4";
105 this.label4.Size = new System.Drawing.Size(29, 12);
106 this.label4.TabIndex = 8;
107 this.label4.Text = "级别";
108 //
109 // label5
110 //
111 this.label5.AutoSize = true;
112 this.label5.Location = new System.Drawing.Point(77, 182);
113 this.label5.Name = "label5";
114 this.label5.Size = new System.Drawing.Size(53, 12);
115 this.label5.TabIndex = 9;
116 this.label5.Text = "通信地址";
117 //
118 // btnSave
119 //
120 this.btnSave.DialogResult = System.Windows.Forms.DialogResult.Yes;
121 this.btnSave.Location = new System.Drawing.Point(392, 257);
122 this.btnSave.Name = "btnSave";
123 this.btnSave.Size = new System.Drawing.Size(75, 23);
124 this.btnSave.TabIndex = 10;
125 this.btnSave.Text = "保存";
126 this.btnSave.UseVisualStyleBackColor = true;
127 this.btnSave.Click += new System.EventHandler(this.btnSave_Click);
128 //
129 // btnCancel
130 //
131 this.btnCancel.Location = new System.Drawing.Point(528, 257);
132 this.btnCancel.Name = "btnCancel";
133 this.btnCancel.Size = new System.Drawing.Size(75, 23);
134 this.btnCancel.TabIndex = 11;
135 this.btnCancel.Text = "取消";
136 this.btnCancel.UseVisualStyleBackColor = true;
137 this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
138 //
139 // dtBirthday
140 //
141 this.dtBirthday.Location = new System.Drawing.Point(146, 114);
142 this.dtBirthday.Name = "dtBirthday";
143 this.dtBirthday.Size = new System.Drawing.Size(200, 21);
144 this.dtBirthday.TabIndex = 12;
145 //
146 // CustomerEditUI
147 //
148 this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
149 this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
150 this.ClientSize = new System.Drawing.Size(732, 348);
151 this.Controls.Add(this.dtBirthday);
152 this.Controls.Add(this.btnCancel);
153 this.Controls.Add(this.btnSave);
154 this.Controls.Add(this.label5);
155 this.Controls.Add(this.label4);
156 this.Controls.Add(this.label3);
157 this.Controls.Add(this.label2);
158 this.Controls.Add(this.label1);
159 this.Controls.Add(this.txtAddress);
160 this.Controls.Add(this.txtCustLevel);
161 this.Controls.Add(this.txtTelNum);
162 this.Controls.Add(this.txtName);
163 this.Name = "CustomerEditUI";
164 this.Text = "CustomerEditUI";
165 this.Load += new System.EventHandler(this.CustomerEditUI_Load);
166 this.ResumeLayout(false);
167 this.PerformLayout();
168
169 }
170
171 #endregion
172
173 private System.Windows.Forms.TextBox txtName;
174 private System.Windows.Forms.TextBox txtTelNum;
175 private System.Windows.Forms.TextBox txtCustLevel;
176 private System.Windows.Forms.TextBox txtAddress;
177 private System.Windows.Forms.Label label1;
178 private System.Windows.Forms.Label label2;
179 private System.Windows.Forms.Label label3;
180 private System.Windows.Forms.Label label4;
181 private System.Windows.Forms.Label label5;
182 private System.Windows.Forms.Button btnSave;
183 private System.Windows.Forms.Button btnCancel;
184 private System.Windows.Forms.DateTimePicker dtBirthday;
185 }
186 }
View Code
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 Customer.DAL;
10 using Customer.Model;
11
12 namespace Customer
13 {
14 public partial class CustomerListUI : Form
15 {
16 public CustomerListUI()
17 {
18 InitializeComponent();
19 }
20
21 void LoadData()
22 {
23 dataGridView1.DataSource = CustomerDAL.GetAll();
24 }
25
26 private void CustomerListUI_Load(object sender, EventArgs e)
27 {
28 LoadData();
29 }
30
31 private void toolStripButton1_Click(object sender, EventArgs e)
32 {
33 CustomerEditUI editUI = new CustomerEditUI();
34 editUI.isInsert = true;
35 editUI.ShowDialog();
36 if (editUI.DialogResult == DialogResult.Yes)
37 {
38 LoadData();
39 }
40
41 }
42
43 private void toolStripButton2_Click(object sender, EventArgs e)
44 {
45 if (dataGridView1.SelectedRows.Count <= 0)
46 {
47 MessageBox.Show("请选择要编辑的行！");
48 return;
49 }
50 else if(dataGridView1.SelectedRows.Count >=2)
51 {
52 MessageBox.Show("只能选择一行！");
53 return;
54 }
55 else
56 {
57 CustomerModel cust = (CustomerModel)dataGridView1.SelectedRows[0].DataBoundItem;
58 CustomerEditUI editUI = new CustomerEditUI();
59 editUI.isInsert = false;
60 editUI.EditingId = cust.Id;
61 editUI.ShowDialog();
62 if (editUI.DialogResult == DialogResult.Yes)
63 {
64 LoadData();
65 }
66 }
67
68 }
69
70 private void toolStripButton3_Click(object sender, EventArgs e)
71 {
72 if (dataGridView1.SelectedRows.Count <= 0)
73 {
74 MessageBox.Show("请选择要编辑的行！");
75 return;
76 }
77
78 DialogResult dr = MessageBox.Show("是否确定", "询问", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
79 if (dr == DialogResult.Yes)
80 {
81 for (int i = 0; i < dataGridView1.SelectedRows.Count; i++)
82 {
83 CustomerModel cust = (CustomerModel)dataGridView1.SelectedRows[i].DataBoundItem;
84 CustomerDAL.DeleteById(cust.Id);
85 }
86 }
87 LoadData();
88 }
89 }
90 }
View Code
1 namespace Customer
2 {
3 partial class CustomerListUI
4 {
5 /// <summary>
6 /// 必需的设计器变量。
7 /// </summary>
8 private System.ComponentModel.IContainer components = null;
9
10 /// <summary>
11 /// 清理所有正在使用的资源。
12 /// </summary>
13 /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
14 protected override void Dispose(bool disposing)
15 {
16 if (disposing && (components != null))
17 {
18 components.Dispose();
19 }
20 base.Dispose(disposing);
21 }
22
23 #region Windows 窗体设计器生成的代码
24
25 /// <summary>
26 /// 设计器支持所需的方法 - 不要
27 /// 使用代码编辑器修改此方法的内容。
28 /// </summary>
29 private void InitializeComponent()
30 {
31 System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(CustomerListUI));
32 this.toolStrip1 = new System.Windows.Forms.ToolStrip();
33 this.toolStripButton1 = new System.Windows.Forms.ToolStripButton();
34 this.toolStripButton2 = new System.Windows.Forms.ToolStripButton();
35 this.toolStripButton3 = new System.Windows.Forms.ToolStripButton();
36 this.dataGridView1 = new System.Windows.Forms.DataGridView();
37 this.toolStrip1.SuspendLayout();
38 ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
39 this.SuspendLayout();
40 //
41 // toolStrip1
42 //
43 this.toolStrip1.Items.AddRange(new System.Windows.Forms.ToolStripItem[] {
44 this.toolStripButton1,
45 this.toolStripButton2,
46 this.toolStripButton3});
47 this.toolStrip1.Location = new System.Drawing.Point(0, 0);
48 this.toolStrip1.Name = "toolStrip1";
49 this.toolStrip1.Size = new System.Drawing.Size(1112, 25);
50 this.toolStrip1.TabIndex = 0;
51 this.toolStrip1.Text = "toolStrip1";
52 //
53 // toolStripButton1
54 //
55 this.toolStripButton1.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;
56 this.toolStripButton1.Image = ((System.Drawing.Image)(resources.GetObject("toolStripButton1.Image")));
57 this.toolStripButton1.ImageTransparentColor = System.Drawing.Color.Magenta;
58 this.toolStripButton1.Name = "toolStripButton1";
59 this.toolStripButton1.Size = new System.Drawing.Size(23, 22);
60 this.toolStripButton1.Text = "toolStripButton1";
61 this.toolStripButton1.Click += new System.EventHandler(this.toolStripButton1_Click);
62 //
63 // toolStripButton2
64 //
65 this.toolStripButton2.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;
66 this.toolStripButton2.Image = ((System.Drawing.Image)(resources.GetObject("toolStripButton2.Image")));
67 this.toolStripButton2.ImageTransparentColor = System.Drawing.Color.Magenta;
68 this.toolStripButton2.Name = "toolStripButton2";
69 this.toolStripButton2.Size = new System.Drawing.Size(23, 22);
70 this.toolStripButton2.Text = "toolStripButton2";
71 this.toolStripButton2.Click += new System.EventHandler(this.toolStripButton2_Click);
72 //
73 // toolStripButton3
74 //
75 this.toolStripButton3.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;
76 this.toolStripButton3.Image = ((System.Drawing.Image)(resources.GetObject("toolStripButton3.Image")));
77 this.toolStripButton3.ImageTransparentColor = System.Drawing.Color.Magenta;
78 this.toolStripButton3.Name = "toolStripButton3";
79 this.toolStripButton3.Size = new System.Drawing.Size(23, 22);
80 this.toolStripButton3.Text = "toolStripButton3";
81 this.toolStripButton3.Click += new System.EventHandler(this.toolStripButton3_Click);
82 //
83 // dataGridView1
84 //
85 this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
86 this.dataGridView1.Location = new System.Drawing.Point(12, 28);
87 this.dataGridView1.Name = "dataGridView1";
88 this.dataGridView1.RowTemplate.Height = 23;
89 this.dataGridView1.Size = new System.Drawing.Size(1088, 361);
90 this.dataGridView1.TabIndex = 1;
91 //
92 // CustomerListUI
93 //
94 this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
95 this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
96 this.ClientSize = new System.Drawing.Size(1112, 401);
97 this.Controls.Add(this.dataGridView1);
98 this.Controls.Add(this.toolStrip1);
99 this.Name = "CustomerListUI";
100 this.Text = "CustomerListUI";
101 this.Load += new System.EventHandler(this.CustomerListUI_Load);
102 this.toolStrip1.ResumeLayout(false);
103 this.toolStrip1.PerformLayout();
104 ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
105 this.ResumeLayout(false);
106 this.PerformLayout();
107
108 }
109
110 #endregion
111
112 private System.Windows.Forms.ToolStrip toolStrip1;
113 private System.Windows.Forms.ToolStripButton toolStripButton1;
114 private System.Windows.Forms.ToolStripButton toolStripButton2;
115 private System.Windows.Forms.ToolStripButton toolStripButton3;
116 private System.Windows.Forms.DataGridView dataGridView1;
117
118 }
119 }
View Code
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Windows.Forms;
5
6 namespace Customer
7 {
8 static class Program
9 {
10 /// <summary>
11 /// 应用程序的主入口点。
12 /// </summary>
13 [STAThread]
14 static void Main()
15 {
16 Application.EnableVisualStyles();
17 Application.SetCompatibleTextRenderingDefault(false);
18 Application.Run(new CustomerListUI());
19 }
20 }
21 }
View Code
