private void btnLogin_Click(object sender, EventArgs e)
{
string txtUserName = this.txtUserName.Text.Trim();
string txtPwd = this.txtPwd.Text.Trim();
if (txtUserName==null||txtPwd==null||txtUserName.Length==||txtPwd.Length==)
{
MessageBox.Show("您输入的内容为空，请重新输入！");
}
string connString = "server=.;database=StudentMISDB;uid=sa;pwd=123456";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
string sql = @"select l.*,DATEDIFF(MI,LoginErrorLastTime,GETDATE()) as 间隔 from login as l where loginname='{0}'";
sql = string.Format(sql, txtUserName);
SqlCommand cmd = new SqlCommand(sql,conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
adapter.Fill(dSet);
conn.Close();
if (dSet.Tables[].Rows.Count > )
{
int errorCount = Convert.ToInt32(dSet.Tables[].Rows[][]);
int times = Convert.ToInt32(dSet.Tables[].Rows[][]);
if (errorCount >= && times <= )
{
if (dSet.Tables[].Rows[][].ToString() == txtUserName && dSet.Tables[].Rows[][].ToString()==txtPwd)
{
MessageBox.Show("登陆成功");
conn.Open();
string uptateSql = @"update login set loginerrorcount=0 where id='{0}'";
uptateSql = string.Format(uptateSql,dSet.Tables[].Rows[][].ToString());
cmd = new SqlCommand(uptateSql,conn);
cmd.ExecuteNonQuery();
conn.Close();
}
else
{
MessageBox.Show("登录名或者密码错误！");
conn.Open();
string updateSql = @"update login set loginerrorcount=loginerrorcount+1 ,loginerrorlasttime=getdate() where id='{0}'";
updateSql = string.Format(updateSql,dSet.Tables[].Rows[][].ToString());
cmd = new SqlCommand(updateSql,conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
else
{
MessageBox.Show("请在"+(-times)+"分钟后登录！");
}
}
else
{
MessageBox.Show("用户不存在！");
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient; namespace ADO.NET8._30
{
public partial class DataGridView : Form
{
public DataGridView()
{
InitializeComponent();
} private void DataGridView_Load(object sender, EventArgs e)
{
//加载学生选课信息
loadStudntData();
//加载课程信息（没有选的课程）
LoadCourse();
}
/// <summary>
/// 自定义一个方法
/// </summary>
private void LoadCourse()
{
//1.创建数据库连接符
string connString = "server=.;database=StudentMISDB;uid=sa;pwd=123456";
//2.链接数据库
SqlConnection conn = new SqlConnection(connString);
//3.添加数据库要执行的语句，通过ID查找没有选择到的课程
string sql = @"select * from Course where CourseId not in(select distinct sc.CourseId from [dbo].[Students] as s
join Score as sc on s.StudentId=sc.StudentId
join Course as c on sc.CourseId = c.CourseId
where s.StudentId='2')";
//4.创建命令
SqlCommand cmd = new SqlCommand(sql, conn);
//5.断开式连接查询
SqlDataAdapter da = new SqlDataAdapter(cmd);
//6.创建数据缓冲区（数据集）
DataSet ds = new DataSet();
conn.Open();
//7.填充数据集
da.Fill(ds);
conn.Close();
//8.绑定数据源
this.cmbCourseName.DataSource = ds.Tables[]; //9.设置combobox控件中要显示的列
//this.cmboxCourse.DisplayMember = "列名";
this.cmbCourseName.DisplayMember = "Name";
//10.DisplayMember绑定需要显示的数据表字段，而ValueMember绑定需要获取选择的项的值；直接可见的是此item的 DisplayMember 对应内容，而此 item的值是ValueMember 的对应内容。
this.cmbCourseName.ValueMember = "CourseId";
}
/// <summary>
/// 获取选中的值
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmbCourseName_SelectedIndexChanged(object sender, EventArgs e)
{
string courseID = this.cmbCourseName.SelectedValue.ToString();
///string courseName = this.cmboxCourse.SelectedText;
}
/// <summary>
/// 保存 选课后，
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSave_Click(object sender, EventArgs e)
{
//获取到在combobox控件中已经选择的值
string courseID = this.cmbCourseName.SelectedValue.ToString();
//string courseName = this.cmboxCourse.SelectedText;
int studentId = ;
//1.
string connString = "server=.;database=StudentMISDB;uid=sa;pwd=123456";
SqlConnection conn = new SqlConnection(connString); string sql = "insert into Score values('{0}','{1}','{2}')";
sql = string.Format(sql, studentId, courseID, ); SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
if (result > )
{
MessageBox.Show("保存成功"); loadStudntData();
LoadCourse();
}
else
{
MessageBox.Show("保存失败");
}
}
/// <summary>
/// 加载学生选课信息
/// </summary>
private void loadStudntData()
{
string connString = "server=.;database=StudentMISDB;uid=sa;pwd=123456";
SqlConnection conn = new SqlConnection(connString); string sql = @"select s.Studentid,c.courseId, s.Name as 姓名,c.Name as 课程名, sc.Score as 成绩 from [dbo].[Students] as s
join Score as sc on s.StudentId=sc.StudentId
join Course as c on sc.CourseId = c.CourseId
where s.StudentId='{0}'";
sql = string.Format(sql, ); SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
conn.Open();
da.Fill(ds);
conn.Close(); this.dataGridView1.DataSource = ds;
this.dataGridView1.DataMember = ds.Tables[].TableName;
}
}
}
二、（1）封装的类：
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data; namespace Demo2
{
public static class SqlCommon
{ /// <summary>
/// 连接字符串
/// </summary>
public static string connString = "server=.;database=StudentMISDB;uid=sa;pwd=123456"; /// <summary>
/// 执行增删改操作
/// </summary>
/// <param name="sql">sql语句 参数传入</param>
/// <returns></returns>
public static int ExecuteSql(string sql)
{
int result = ;
//创建连接对象new SqlConnection( 连接字符串)
SqlConnection conn = new SqlConnection(connString);
//创建命令对象 new SqlCommand(sql语句, conn)
SqlCommand cmd = new SqlCommand(sql, conn);
// 打开数据连接
conn.Open();
// 执行 sql 命令，返回受影响的行数
result = cmd.ExecuteNonQuery();
// 关闭数据连接
conn.Close();
// 把执行结果【受影响的行数】，返回给调用者
return result;
} public static DataSet ExecuteQuery(string sql) { SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
conn.Open();
da.Fill(ds);
conn.Close(); return ds;
}
}
}
（二）、调用类
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms; using System.Data.SqlClient; namespace Demo2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} private void btnAdd_Click(object sender, EventArgs e)
{
/// 定义变量接收 用户输入的值
string loginName = this.txtLoginName.Text.Trim();
string pwd = this.txtPWD.Text.Trim(); ///判断用户是否有输入值
if (loginName == "" || loginName == null)
{
MessageBox.Show("请输入用户名");
return;
}
//string.IsNullOrEmpty(字符串) ==>判断字符串是否为“空字符”或为 null
if (string.IsNullOrEmpty(pwd))
{
MessageBox.Show("请输入密码");
return;
} if (isExistsLoginName(loginName))
{
MessageBox.Show("该用户名已经存在，请重新输入");
this.txtLoginName.Text = string.Empty;
this.txtPWD.Text = string.Empty;
return;
} string sql = @"insert into LoginInfo (loginName,pwd,LoginErrorLastTime)
values('{0}','{1}','{2}')";
sql = string.Format(sql, loginName, pwd, DateTime.Now.ToString()); int row = SqlCommon.ExecuteSql(sql);
if (row > )
{
MessageBox.Show("添加成功");
}
else
{
MessageBox.Show("添加失败");
} } #region 判断 用户名是否存在 private bool isExistsLoginName(string loginName)
{
string sql = @"select * from LoginInfo where LoginName='{0}'";
sql = string.Format(sql, loginName);
DataSet ds = SqlCommon.ExecuteQuery(sql);
return ds.Tables[].Rows.Count > ;
}
#endregion
}
}
（三）、datagrideview插件
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms; namespace Demo2
{
public partial class FrmScore : Form
{
public FrmScore()
{
InitializeComponent();
} private void btnSelect_Click(object sender, EventArgs e)
{
string name = this.txtCourse.Text.Trim(); string sql = @"select ScoreId,Name,Score from Course as c
join Score as sc on c.CourseId = sc.CourseId
where sc.StudentId='1' and c.Name like '%{0}%'";
sql = string.Format(sql,name);
DataSet ds = SqlCommon.ExecuteQuery(sql); this.dataGridView1.DataSource = ds.Tables[]; }
}
}