传智播客数据绑定和数据库开发基础(第四季)-杨中科
生活随笔
收集整理的這篇文章主要介紹了
传智播客数据绑定和数据库开发基础(第四季)-杨中科
小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.
(一)數(shù)據(jù)綁定、ListBox、DataGrid SQLServer基礎(chǔ)、SQLServer使用主鍵策略
?
(二)DataReader、DataSet、參數(shù)化查詢、防注入漏洞攻擊、SQLHelper
用戶界面中進(jìn)行登錄判斷。輸錯(cuò)三次禁止登陸（半小時(shí)），用數(shù)據(jù)庫(kù)記錄ErrorTimes。 數(shù)據(jù)導(dǎo)入：從文本文件導(dǎo)入用戶信息。易錯(cuò)點(diǎn)：Parameter的重復(fù)添加。File.ReadAllLines() 數(shù)據(jù)導(dǎo)出：將用戶信息導(dǎo)出到文本文件。File.WriteAllLines()
省市聯(lián)動(dòng)選擇 手機(jī)號(hào)碼歸屬地查詢
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 /// 必需的設(shè)計(jì)器變量。 7 /// </summary> 8 private System.ComponentModel.IContainer components = null; 9 10 /// <summary> 11 /// 清理所有正在使用的資源。 12 /// </summary> 13 /// <param name="disposing">如果應(yīng)釋放托管資源，為 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 窗體設(shè)計(jì)器生成的代碼 24 25 /// <summary> 26 /// 設(shè)計(jì)器支持所需的方法 - 不要 27 /// 使用代碼編輯器修改此方法的內(nèi)容。 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 /// 應(yīng)用程序的主入口點(diǎn)。 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 Codesqlhelper
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案例：登陸、數(shù)據(jù)導(dǎo)入導(dǎo)出、省市聯(lián)動(dòng)選擇
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("請(qǐng)輸入用戶名"); 25 return; 26 } 27 if (passWord.Text.Length <= 0) 28 { 29 MessageBox.Show("請(qǐng)輸入密碼"); 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("不好啦，用戶名重復(fù)"); 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("輸入次數(shù)過(guò)多，用戶已經(jīng)鎖定"); 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("密碼錯(cuò)誤"); 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 /// 必需的設(shè)計(jì)器變量。 7 /// </summary> 8 private System.ComponentModel.IContainer components = null; 9 10 /// <summary> 11 /// 清理所有正在使用的資源。 12 /// </summary> 13 /// <param name="disposing">如果應(yīng)釋放托管資源，為 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 窗體設(shè)計(jì)器生成的代碼 24 25 /// <summary> 26 /// 設(shè)計(jì)器支持所需的方法 - 不要 27 /// 使用代碼編輯器修改此方法的內(nèi)容。 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("導(dǎo)入成功！成功導(dǎo)入" + strs.Length + "條數(shù)據(jù)！"); 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("導(dǎo)入成功！成功導(dǎo)入" + list.Count + "條數(shù)據(jù)！"); 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 = "導(dǎo)入"; 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 /// 應(yīng)用程序的主入口點(diǎn)。 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 Codesqlhelper
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案例：手機(jī)號(hào)碼歸屬地查詢、DbValue.Null的處理、三層架構(gòu)
?
(五)典型的ListUI+EditUI、人事管理系統(tǒng)需求分析、架構(gòu)設(shè)計(jì)、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("嚴(yán)重錯(cuò)誤，查出多條數(shù)據(jù)！"); 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 Codeimages:
Model:
?
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 = "電話號(hào)碼"; 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 = "級(jí)別"; 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("請(qǐng)選擇要編輯的行！"); 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("請(qǐng)選擇要編輯的行！"); 75 return; 76 } 77 78 DialogResult dr = MessageBox.Show("是否確定", "詢問(wèn)", 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 /// 必需的設(shè)計(jì)器變量。 7 /// </summary> 8 private System.ComponentModel.IContainer components = null; 9 10 /// <summary> 11 /// 清理所有正在使用的資源。 12 /// </summary> 13 /// <param name="disposing">如果應(yīng)釋放托管資源，為 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 窗體設(shè)計(jì)器生成的代碼 24 25 /// <summary> 26 /// 設(shè)計(jì)器支持所需的方法 - 不要 27 /// 使用代碼編輯器修改此方法的內(nèi)容。 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 /// 應(yīng)用程序的主入口點(diǎn)。 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?
轉(zhuǎn)載于:https://www.cnblogs.com/liuslayer/p/4713787.html
總結(jié)
以上是生活随笔為你收集整理的传智播客数据绑定和数据库开发基础(第四季)-杨中科的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 杨中科说给弟弟的话
- 下一篇： 笔记本键盘扣安装注意事项（小技巧）