C# datagrideview插件的使用 

 1  private void btnLogin_Click(object sender, EventArgs e)
 2         {
 3             string txtUserName = this.txtUserName.Text.Trim();
 4             string txtPwd = this.txtPwd.Text.Trim();
 5             if (txtUserName==null||txtPwd==null||txtUserName.Length==0||txtPwd.Length==0)
 6             {
 7                 MessageBox.Show("您输入的内容为空，请重新输入！");
 8             }
 9             string connString = "server=.;database=StudentMISDB;uid=sa;pwd=123456";
10             SqlConnection conn = new SqlConnection(connString);
11             conn.Open();
12             string sql = @"select l.*,DATEDIFF(MI,LoginErrorLastTime,GETDATE()) as 间隔 from login as l where loginname='{0}'";
13             sql = string.Format(sql, txtUserName);
14             SqlCommand cmd = new SqlCommand(sql,conn);
15             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
16             DataSet dSet = new DataSet();
17             adapter.Fill(dSet);
18             conn.Close();
19             if (dSet.Tables[0].Rows.Count > 0)
20             {
21                 int errorCount = Convert.ToInt32(dSet.Tables[0].Rows[0][3]);
22                 int times = Convert.ToInt32(dSet.Tables[0].Rows[0][5]);
23                 if (errorCount >= 3 && times <= 15)
24                 {
25                     if (dSet.Tables[0].Rows[0][1].ToString() == txtUserName && dSet.Tables[0].Rows[0][2].ToString()==txtPwd)
26                     {
27                         MessageBox.Show("登陆成功");
28                         conn.Open();
29                         string uptateSql = @"update login set loginerrorcount=0 where id='{0}'";
30                         uptateSql = string.Format(uptateSql,dSet.Tables[0].Rows[0][0].ToString());
31                         cmd = new SqlCommand(uptateSql,conn);
32                         cmd.ExecuteNonQuery();
33                         conn.Close();
34                     }
35                     else
36                     {
37                         MessageBox.Show("登录名或者密码错误！");
38                         conn.Open();
39                         string updateSql = @"update login set loginerrorcount=loginerrorcount+1 ,loginerrorlasttime=getdate() where id='{0}'";
40                         updateSql = string.Format(updateSql,dSet.Tables[0].Rows[0][0].ToString());
41                         cmd = new SqlCommand(updateSql,conn);
42                         cmd.ExecuteNonQuery();
43                         conn.Close();
44                     }
45                 }
46                 else
47                 {
48                     MessageBox.Show("请在"+(15-times)+"分钟后登录！");
49                 }
50             }
51             else
52             {
53                 MessageBox.Show("用户不存在！");
54             }
55         }
56     }
  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.Threading.Tasks;
  9 using System.Windows.Forms;
 10 using System.Data.SqlClient;
 11 
 12 namespace ADO.NET8._30
 13 {
 14     public partial class DataGridView : Form
 15     {
 16         public DataGridView()
 17         {
 18             InitializeComponent();
 19         }
 20 
 21         private void DataGridView_Load(object sender, EventArgs e)
 22         {
 23             //加载学生选课信息
 24             loadStudntData();
 25             //加载课程信息（没有选的课程）
 26             LoadCourse();
 27         }
 28         /// <summary>
 29         /// 自定义一个方法
 30         /// </summary>
 31         private void LoadCourse()
 32         {
 33             //1.创建数据库连接符
 34             string connString = "server=.;database=StudentMISDB;uid=sa;pwd=123456";
 35             //2.链接数据库
 36             SqlConnection conn = new SqlConnection(connString);
 37             //3.添加数据库要执行的语句，通过ID查找没有选择到的课程
 38             string sql = @"select * from Course where CourseId not in(select distinct sc.CourseId from  [dbo].[Students] as s
 39                 join  Score as sc on s.StudentId=sc.StudentId
 40                 join  Course as c on sc.CourseId = c.CourseId
 41                 where s.StudentId='2')";
 42             //4.创建命令
 43             SqlCommand cmd = new SqlCommand(sql, conn);
 44             //5.断开式连接查询
 45             SqlDataAdapter da = new SqlDataAdapter(cmd);
 46             //6.创建数据缓冲区（数据集）
 47             DataSet ds = new DataSet();
 48             conn.Open();
 49             //7.填充数据集
 50             da.Fill(ds);
 51             conn.Close();
 52             //8.绑定数据源
 53             this.cmbCourseName.DataSource = ds.Tables[0];
 54             
 55             //9.设置combobox控件中要显示的列
 56             //this.cmboxCourse.DisplayMember = "列名";
 57             this.cmbCourseName.DisplayMember = "Name";
 58             //10.DisplayMember绑定需要显示的数据表字段，而ValueMember绑定需要获取选择的项的值；直接可见的是此item的 DisplayMember 对应内容，而此 item的值是ValueMember 的对应内容。
 59             this.cmbCourseName.ValueMember = "CourseId";
 60         }
 61         /// <summary>
 62         /// 获取选中的值
 63         /// </summary>
 64         /// <param name="sender"></param>
 65         /// <param name="e"></param>
 66         private void cmbCourseName_SelectedIndexChanged(object sender, EventArgs e)
 67         {
 68             string courseID = this.cmbCourseName.SelectedValue.ToString();
 69             ///string courseName = this.cmboxCourse.SelectedText;
 70         }
 71         /// <summary>
 72         /// 保存 选课后，
 73         /// </summary>
 74         /// <param name="sender"></param>
 75         /// <param name="e"></param>
 76         private void btnSave_Click(object sender, EventArgs e)
 77         {
 78             //获取到在combobox控件中已经选择的值
 79             string courseID = this.cmbCourseName.SelectedValue.ToString();
 80             //string courseName = this.cmboxCourse.SelectedText;
 81             int studentId = 2;
 82             //1.
 83             string connString = "server=.;database=StudentMISDB;uid=sa;pwd=123456";
 84             SqlConnection conn = new SqlConnection(connString);
 85 
 86             string sql = "insert into Score values('{0}','{1}','{2}')";
 87             sql = string.Format(sql, studentId, courseID, 0);
 88 
 89             SqlCommand cmd = new SqlCommand(sql, conn);
 90             conn.Open();
 91             int result = cmd.ExecuteNonQuery();
 92             conn.Close();
 93             if (result > 0)
 94             {
 95                 MessageBox.Show("保存成功");
 96 
 97                 loadStudntData();
 98                 LoadCourse();
 99             }
100             else
101             {
102                 MessageBox.Show("保存失败");
103             }
104         }
105         /// <summary>
106         /// 加载学生选课信息
107         /// </summary>
108         private void loadStudntData()
109         {
110             string connString = "server=.;database=StudentMISDB;uid=sa;pwd=123456";
111             SqlConnection conn = new SqlConnection(connString);
112 
113             string sql = @"select s.Studentid,c.courseId, s.Name as 姓名,c.Name as 课程名, sc.Score as 成绩 from  [dbo].[Students] as s
114                 join  Score as sc on s.StudentId=sc.StudentId
115                 join  Course as c on sc.CourseId = c.CourseId
116                 where s.StudentId='{0}'";
117             sql = string.Format(sql, 2);
118 
119             SqlCommand cmd = new SqlCommand(sql, conn);
120             SqlDataAdapter da = new SqlDataAdapter(cmd);
121             DataSet ds = new DataSet();
122             conn.Open();
123             da.Fill(ds);
124             conn.Close();
125 
126             this.dataGridView1.DataSource = ds;
127             this.dataGridView1.DataMember = ds.Tables[0].TableName;
128         }
129     }
130 }

 二、（1）封装的类：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Data.SqlClient;
 4 using System.Linq;
 5 using System.Text;
 6 using System.Threading.Tasks;
 7 using System.Data;
 8 
 9 namespace Demo2
10 {
11 public static    class SqlCommon
12     {
13 
14         /// <summary>
15         /// 连接字符串
16         /// </summary>
17         public static string connString = "server=.;database=StudentMISDB;uid=sa;pwd=123456";
18 
19         /// <summary>
20         /// 执行增删改操作
21         /// </summary>
22         /// <param name="sql">sql语句 参数传入</param>
23         /// <returns></returns>
24         public static int ExecuteSql(string sql)
25         {
26             int result = 0;
27             //创建连接对象new SqlConnection( 连接字符串)
28             SqlConnection conn = new SqlConnection(connString);
29             //创建命令对象  new SqlCommand(sql语句, conn)
30             SqlCommand cmd = new SqlCommand(sql, conn);
31             // 打开数据连接
32             conn.Open();
33             // 执行 sql 命令，返回受影响的行数
34             result = cmd.ExecuteNonQuery();
35             // 关闭数据连接
36             conn.Close();
37             // 把执行结果【受影响的行数】，返回给调用者
38             return result;
39         }
40 
41 
42         public static DataSet ExecuteQuery(string sql) {
43 
44             SqlConnection conn = new SqlConnection(connString);
45             SqlCommand cmd = new SqlCommand(sql, conn);
46             SqlDataAdapter da = new SqlDataAdapter(cmd);
47             DataSet ds = new DataSet();
48             conn.Open();
49             da.Fill(ds);
50             conn.Close();
51 
52             return ds;
53         }
54     }
55 }

 （二）、调用类

 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.Threading.Tasks;
 9 using System.Windows.Forms;
10 
11 using System.Data.SqlClient;
12 
13 namespace Demo2
14 {
15     public partial class Form1 : Form
16     {
17         public Form1()
18         {
19             InitializeComponent();
20         }
21 
22         private void btnAdd_Click(object sender, EventArgs e)
23         {
24             /// 定义变量接收  用户输入的值
25             string loginName = this.txtLoginName.Text.Trim();
26             string pwd = this.txtPWD.Text.Trim();
27 
28             ///判断用户是否有输入值
29             if (loginName == "" || loginName == null)
30             {
31                 MessageBox.Show("请输入用户名");
32                 return;
33             }
34             //string.IsNullOrEmpty(字符串) ==>判断字符串是否为“空字符”或为 null
35             if (string.IsNullOrEmpty(pwd))
36             {
37                 MessageBox.Show("请输入密码");
38                 return;
39             }
40 
41 
42             if (isExistsLoginName(loginName))
43             {
44                 MessageBox.Show("该用户名已经存在，请重新输入");
45                 this.txtLoginName.Text = string.Empty;
46                 this.txtPWD.Text = string.Empty;
47                 return;
48             }
49 
50             string sql = @"insert  into LoginInfo (loginName,pwd,LoginErrorLastTime)  
51             values('{0}','{1}','{2}')";
52             sql = string.Format(sql, loginName, pwd, DateTime.Now.ToString());
53 
54             int row = SqlCommon.ExecuteSql(sql);
55             if (row > 0)
56             {
57                 MessageBox.Show("添加成功");
58             }
59             else
60             {
61                 MessageBox.Show("添加失败");
62             }
63 
64         }
65 
66 
67         #region 判断 用户名是否存在
68 
69         private bool isExistsLoginName(string loginName)
70         { 
71             string sql = @"select * from LoginInfo where LoginName='{0}'";
72             sql = string.Format(sql, loginName);
73             DataSet ds = SqlCommon.ExecuteQuery(sql);
74             return ds.Tables[0].Rows.Count > 0;
75         }
76         #endregion
77     }
78 }

 （三）、datagrideview插件

 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.Threading.Tasks;
 9 using System.Windows.Forms;
10 
11 namespace Demo2
12 {
13     public partial class FrmScore : Form
14     {
15         public FrmScore()
16         {
17             InitializeComponent();
18         }
19 
20         private void btnSelect_Click(object sender, EventArgs e)
21         {
22             string name = this.txtCourse.Text.Trim();
23 
24             string sql = @"select ScoreId,Name,Score from Course as c
25             join Score as sc on c.CourseId = sc.CourseId
26             where sc.StudentId='1' and c.Name like '%{0}%'";
27             sql = string.Format(sql,name);
28             DataSet ds = SqlCommon.ExecuteQuery(sql);
29 
30             this.dataGridView1.DataSource = ds.Tables[0];
31 
32         }
33     }
34 }

 

posted @ 2017-09-03 18:52  青红造了个白  阅读(1252)  评论(0)  编辑  收藏  举报