private void btnLogin_Click(object sender, EventArgs e)
{
//从app.config文件中获取节点的name属性为strcon的connectionString的值,也就是获取对应的连接字符串
string strcon=ConfigurationManager.ConnectionStrings["strcon"].ConnectionString;
//建立了程序到数据库的连接,好比铺设了一条水管
SqlConnection conn = new SqlConnection(strcon);
//存储要像数据库管理系统发送的一条sql语句
SqlCommand cmd = new SqlCommand();
//指明要像哪个数据库发送sql语句
cmd.Connection = conn;
cmd.CommandText = "select * from T_User where UserName=@username and Password=@password" ;
//使用用户输入的内容替换sql语句中@username占位符
//cmd.Parameters.Add(new SqlParameter("@username", txtUserName.Text));
//cmd.Parameters.Add(new SqlParameter("@password", txtPassword.Text));
/*最新的方式,内部还是调用了cmd,parameter,add方法*/
cmd.Parameters.AddWithValue("@username", txtUserName.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
//数据适配器,向数据库发送命令
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//内存中的表格,用来存储从数据库中返回的数据
DataTable dt = new DataTable();
adapter.Fill(dt);
//根据datatable对象中的数据的行数来判断用户输入的是否正确
if (dt.Rows.Count <= 0)
{
MessageBox.Show("输入的用户名或者密码不正确,请重新输入");
}
else if (dt.Rows.Count > 1)
{
MessageBox.Show("系统中存在重复的用户,请联系管理员");
}
else
{
MessageBox.Show("登录成功");
}
cmd.Dispose();
//关闭连接
//conn.Close();
//释放资源
conn.Dispose();
}
//时间的限制
if (dr.Read())
{
int i = Convert.ToInt32(dr["Error"]);
if (i==3)//错误不能超过三次
{
TimeSpan span = DateTime.Now.Subtract(Convert.ToDateTime(dr["ErrorTime"]));//获得本地时间对数据库中已存时间的差值
if (span.TotalSeconds>120)//将其差值转换成秒 <120秒
{
cnn.Close();
cmm = new SqlCommand("update T_User set Error=0 where UserName='" + this.tbuser.Text + "'", cnn);// 将错误次数归零
cnn.Open();
cmm.ExecuteNonQuery();
cnn.Close();
}
}
else
{
MessageBox.Show("OK!");
cmm.Dispose();
}
}
数据库基本语句:
***SELECT***
select *(列名) from table_name(表名) where column_name operator value ex:(宿主) select * from stock_information where stockid = str(nid) stockname = 'str_name' stockname like '% find this %' stockname like '[a-zA-Z]%' --------- ([]指定值的范围) stockname like '[^F-M]%' --------- (^排除指定范围) --------- 只能在使用like关键字的where子句中使用通配符) or stockpath = 'stock_path' or stocknumber < 1000 and stockindex = 24 not stock*** = 'man' stocknumber between 20 and 100 stocknumber in(10,20,30)
order by stockid desc(asc) --------- 排序,desc-降序,asc-升序 order by 1,2 --------- by列号 stockname = (select stockname from stock_information where stockid = 4) --------- 子查询 --------- 除非能确保内层select只返回一个行的值, --------- 否则应在外层where子句中用一个in限定符 select distinct column_name form table_name --------- distinct指定检索独有的列值,不重复 select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name select stockname , "stocknumber" = count(*) from table_name group by stockname --------- group by 将表按行分组,指定列中有相同的值 having count(*) = 2 --------- having选定指定的组
select * from table1, table2 where table1.id *= table2.id -------- 左外部连接,table1中有的而table2中没有得以null表示 table1.id =* table2.id -------- 右外部连接 select stockname from table1 union [all] ----- union合并查询结果集,all-保留重复行 select stockname from table2
***insert***
insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx") value (select Stockname , Stocknumber from Stock_table2)---value为select语句 ***update*** update table_name set Stockname = "xxx" [where Stockid = 3] Stockname = default
Stockname = null Stocknumber = Stockname + 4
***delete***
delete from table_name where Stockid = 3 truncate table_name ----------- 删除表中所有行,仍保持表的完整性 drop table table_name --------------- 完全删除表