传智播客数据绑定和数据库开发基础(第四季)-杨中科-白红宇

传智播客数据绑定和数据库开发基础(第四季)-杨中科

阅读量：6479 次

发布时间：2019-06-23

本文共 50413 字，大约阅读时间需要 168 分钟。

(一)数据绑定、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 adonet12 {13     public partial class Form1 : Form14     {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         ///  6         /// 必需的设计器变量。 7         /// 
 8         private System.ComponentModel.IContainer components = null; 9 10         /// 11         /// 清理所有正在使用的资源。12         /// 
13         /// 如果应释放托管资源，为 true；否则为 false。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         /// 26         /// 设计器支持所需的方法 - 不要27         /// 使用代码编辑器修改此方法的内容。28         /// 
29         private void InitializeComponent()30         {31             this.button1 = new System.Windows.Forms.Button();32             this.SuspendLayout();33             // 34             // button135             // 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             // Form145             // 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         #endregion57 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         /// 11         /// 应用程序的主入口点。12         /// 
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 adonet10 {11     class SqlHelper12     {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案例：登陆、数据导入导出、省市联动选择

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 AreaId11         { get; set; }12         public string AreaName13         { 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 Adonet12 {13     public partial class LoginWindow : Form14     {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             else61             {62                 MessageBox.Show("登陆成功");63             }64         }65     }66 }

View Code

1 namespace Adonet  2 {  3     partial class LoginWindow  4     {  5         ///   6         /// 必需的设计器变量。  7         /// 
  8         private System.ComponentModel.IContainer components = null;  9  10         ///  11         /// 清理所有正在使用的资源。 12         /// 
 13         /// 如果应释放托管资源，为 true；否则为 false。 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         ///  26         /// 设计器支持所需的方法 - 不要 27         /// 使用代码编辑器修改此方法的内容。 28         /// 
 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 Adonet12 {13     public partial class CitySelectWindow : Form14     {15         public CitySelectWindow()16         {17             InitializeComponent();18         }19 20         Dictionary
      
        dictProv = new Dictionary
       
        ();21         private void CitySelectWindow_Load(object sender, EventArgs e)22         {23             DataTable dtProv = SqlHelper.ExecuteDataTable("select * from AreaFull where AreaPid=0");24             //List
         listProv = new List
        ();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         ///  6         /// Required designer variable. 7         /// 
 8         private System.ComponentModel.IContainer components = null; 9 10         /// 11         /// Clean up any resources being used.12         /// 
13         /// true if managed resources should be disposed; otherwise, false.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 code24 25         /// 26         /// Required method for Designer support - do not modify27         /// the contents of this method with the code editor.28         /// 
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             // lbProv37             // 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             // lbCity47             // 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             // lbXian56             // 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             // CitySelectWindow65             // 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         #endregion80 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 Adonet13 {14     public partial class CustomerImportExportWindow : Form15     {16         public CustomerImportExportWindow()17         {18             InitializeComponent();19         }20 21         private void btnImport_Click(object sender, EventArgs e)22         {23             List
      
        list=new List
       
        ();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         ///  6         /// Required designer variable. 7         /// 
 8         private System.ComponentModel.IContainer components = null; 9 10         /// 11         /// Clean up any resources being used.12         /// 
13         /// true if managed resources should be disposed; otherwise, false.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 code24 25         /// 26         /// Required method for Designer support - do not modify27         /// the contents of this method with the code editor.28         /// 
29         private void InitializeComponent()30         {31             this.btnImport = new System.Windows.Forms.Button();32             this.SuspendLayout();33             // 34             // btnImport35             // 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             // CustomerImportExportWindow45             // 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         #endregion57 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         /// 11         /// 应用程序的主入口点。12         /// 
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 Adonet10 {11     static class SqlHelper12     {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.DAL10 {11     public static class CustomerDAL12     {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             else38             {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_Customer75                                         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.DAL10 {11     class SqlHelper12     {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             else67             {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             else79             {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 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 Customer13 {14     public partial class CustomerEditUI : Form15     {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             else37             {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             else57             {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         ///   6         /// Required designer variable.  7         /// 
  8         private System.ComponentModel.IContainer components = null;  9  10         ///  11         /// Clean up any resources being used. 12         /// 
 13         /// true if managed resources should be disposed; otherwise, false. 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         ///  26         /// Required method for Designer support - do not modify 27         /// the contents of this method with the code editor. 28         /// 
 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             // label4101             // 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             // label5110             // 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             // btnSave119             // 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             // btnCancel130             // 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             // dtBirthday140             // 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             // CustomerEditUI147             // 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         #endregion172 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 Customer13 {14     public partial class CustomerListUI : Form15     {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             else56             {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         ///   6         /// 必需的设计器变量。  7         /// 
  8         private System.ComponentModel.IContainer components = null;  9  10         ///  11         /// 清理所有正在使用的资源。 12         /// 
 13         /// 如果应释放托管资源，为 true；否则为 false。 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         ///  26         /// 设计器支持所需的方法 - 不要 27         /// 使用代码编辑器修改此方法的内容。 28         /// 
 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         #endregion111 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         /// 11         /// 应用程序的主入口点。12         /// 
13         [STAThread]14         static void Main()15         {16             Application.EnableVisualStyles();17             Application.SetCompatibleTextRenderingDefault(false);18             Application.Run(new CustomerListUI());19         }20     }21 }

View Code

转载于:https://www.cnblogs.com/liuslayer/p/4713787.html

你可能感兴趣的文章

awk相关用法

查看>>

oracle中创建java包.执行JAVA代码的方法