2012,c#复习（一），登陆界面的判定和时间限定，SQL

//登录的判定；

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 --------------- 完全删除表