登录的几种写法

 

const string CONNSTRING = @"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|Database1.mdf;Integrated Security=True;User Instance=True";

 

 

表Table1

 

表T_user

//2012.3.1登录程序
              Console.WriteLine("----------------登录开始----------------");
            Console.WriteLine("请输入用户名");
            string username = Console.ReadLine();
            Console.WriteLine("请输入密码");
            string password = Console.ReadLine();

            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from Table1 where Name=@userName";
                    cmd.Parameters.AddWithValue("userName", username);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            //用户存在，判断密码是否和数据库中密码一致
                            string db_password = reader.GetString(reader.GetOrdinal("Password"));
                            if (db_password == password)
                            {
                                Console.WriteLine("登录成功");
                            }
                            else
                            {
                                Console.WriteLine("密码错误");
                            }
                        }
                        else
                        {
                            //没有这个用户名
                            Console.WriteLine("没有这个用户！");
                        }
                    }
                }
            }

 

 

//登录程序段二
            /*
            Console.WriteLine("----------------登录开始----------------");
            Console.WriteLine("请输入用户名");
            string username = Console.ReadLine();
            Console.WriteLine("请输入密码");
            string password = Console.ReadLine();

            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from Table1 where name=@userName and password=@password";
                    cmd.Parameters.AddWithValue("userName", username);
                    cmd.Parameters.AddWithValue("password", password);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            //用户名和密码完全正确
                            Console.WriteLine("登录成功！");
                        }
                        else
                        {
                            //用户名或密码错误
                            Console.WriteLine("用户名或密码错误");
                        }
                    }
                }
            }
            */

 

//另一种登录方式,有sql注入漏洞风险的程序段
            /*
            Console.WriteLine("----------------登录开始----------------");
            Console.WriteLine("请输入用户名");
            string username = Console.ReadLine();
            Console.WriteLine("请输入密码");//输入1' or '1'='1
            string password = Console.ReadLine();

            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select count(*) from Table1 where name='"+username+"' and password='"+password+"'";
                    int count = Convert.ToInt32(cmd.ExecuteScalar());
                    if (count > 0)
                    {
                        Console.WriteLine("登录成功");
                    }
                    else
                    {
                        Console.WriteLine("用户名或密码错误");
                    }
                }
            }
             */

 

 

 

//错误次数>3禁止登录的程序
            Console.WriteLine("----------------登录开始----------------");
            Console.WriteLine("请输入用户名");
            string username = Console.ReadLine();
            Console.WriteLine("请输入密码");
            string password = Console.ReadLine();
            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from T_users where userName=@userName";
                    cmd.Parameters.AddWithValue("userName", username);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            string dbPassword = reader.GetString(reader.GetOrdinal("fpassword"));//数据库的密码
                            int dbErrortimes = reader.GetInt32(reader.GetOrdinal("errortimes"));//错误次数
                            DateTime dberrorDate = reader.GetDateTime(reader.GetOrdinal("errorDate"));//登录错误时间
                            //方法一
                            //int hour = SpanTime(username);//上次登录错误时间与当前时间比较返回的小时数

                            //方法二
                            //TimeSpan ts = DateTime.Now.Subtract(dberrorDate);
                            //double hour = ts.TotalHours;
                            
                            //方法三
                            double hour = (DateTime.Now - dberrorDate).TotalHours;
                            if (dbErrortimes > 3&&hour<3)
                            {
                                Console.WriteLine("错误次数过多，禁止登录！");
                                Console.ReadKey();
                                return;//以后的代码不再执行
                            }

                            if (dbPassword == password)
                            {
                                Console.WriteLine("登录成功！");
                                //错误次数归0
                                ZeroError(username);

                                /*
                                using (SqlCommand zeroCmd = conn.CreateCommand())
                                {
                                    zeroCmd.CommandText = "update T_users set errortimes=0 where userName=@userName";
                                    zeroCmd.Parameters.Add(new SqlParameter("userName", username));
                                    zeroCmd.ExecuteNonQuery();
                                }
                                 */
                            }
                            else
                            {
                                Console.WriteLine("密码错误！");
                                //错误时间设定为当前时间
                                SetErrorDate(username);

                                //错误次数+1
                                IncError(username);

                                //reader已占用了一个sqlcommand，不能在reader里再次创建sqlcommand
                                /*
                                using (SqlCommand incCmd = conn.CreateCommand())
                                {
                                    incCmd.CommandText = "update T_users set errortimes=errortimes+1 where userName=@userName";
                                    incCmd.Parameters.Add(new SqlParameter("userName", username));
                                    incCmd.ExecuteNonQuery();
                                }
                                 */
                            }
                        }
                        else
                        {
                            Console.WriteLine("用户名不存在");
                        }
                    }
                }
            }