当前位置: 澳门新濠3559 > 数据库 > 正文

(2)在系统中,用户编号(UserID)

时间:2019-11-08 05:44来源:数据库
一 前言 我相信几乎每一个用到用户登录的系统中都要有这样一个系统用户表吧,当然表的名字可能有所不同,但其代表的意义应该算是都一样的吧。 1.需求分析:做一个登录的小程序

一 前言

我相信几乎每一个用到用户登录的系统中都要有这样一个系统用户表吧,当然表的名字可能有所不同,但其代表的意义应该算是都一样的吧。

1.需求分析:做一个登录的小程序,基于Winform的窗体小程序。基本要求:登录成功:弹框显示登录成功,登录失败就弹框显示失败。

看到技术交流群中,有人分享了这个面试题,试的做了下,现在把题目和sql写下来。

在我看来这个表是比较特殊的,我说它特殊有如下这么几项原因:
(1)一般情况下:SysUser表的主键对于用户而言是可见的,并且是系统用户自己任意添加的(在这里排除数据表对与该数据列的约束)。
而其他类型的表呢?主键一般是没有必要让用户看到的,而且其主键值一般是不受 人为操控的。 还有一点主键列通常使用的有: 启用递增的标识列,DateTime.Now.Ticks,Guid等等。

扩展功能:登录次数超过3次,就”锁定“用户,提示登录错误次数过多,不能登录。用户点击之后,就退出程序;登录成功,就把错误字段清零。

 

(2)在系统中,用户名就相当于代表了现实生活中一个真实的用户的身份,尤其是在一些管理系统、商务系统等等中,系统用户的每一个操作都是承担着相应的责任的,所以SysUser表中的记录是不可以随意删除的,否则当系统出现问题时,追查数据、及相关责任人时 都会遇到困难的。

这里我们在数据库表中,加一个错误字段,类型为int,初始值设置为0.

二 题目

基于以上的所述原因,让我们在看一个小例子,现比如有如下一个SysUser表,该表要被多个机构所共用:

相信;这个小程序对于大家来说,是超级简单的。我这里也是来熟悉一下,把基础知识再巩固一下。

    1.  日志表SysLog,字段:用户编号(UserID),时间(Time)。

UserID:                主键 char(20)用户名
UserName:                   char(20)用户真实姓名
OrganizationID:             char(30)所属机构ID号,该ID号为DateTime.Now.Ticks.ToString();
UserPhone:                  char(20)用户电话
PassWord:                  char(20)用户密码
IsUsed:                    bit      是否可用,删除的时候其实是将该字段设为 False

2.用到的技术:ADO.NET,存储过程。

UserID

Time

1

2011-04-11 13:10:36.663

1

2011-08-12 13:10:36.663

1

2011-03-11 15:10:36.663

1

2011-02-11 15:10:36.663

1

2011-01-11 15:10:36.663

1

2011-05-11 15:10:36.663

1

2011-06-11 15:10:36.663

1

2011-07-11 15:10:36.663

1

2011-03-11 15:10:36.663

2

2011-08-12 15:10:36.663

1

2011-08-12 12:10:36.663

1

2011-08-12 02:10:36.663

由于这里的主键只有UserID这一列,删除记录并不是使用的DELETE 语句,而只是将IsUsed字段改成了False,所以在这样的表结构下就回产生一个问题就是:删除一个用户名之后、再添加一个相同的用户名就会报主键约束。而且两个不同的机构之间也不能有相同的用户名。

3.项目实现:

                       ......

面对这个很现实的问题,这样的软件交不了工,还得改。唉,也只能怪当时没能把这些问题考虑到。

  • 首先我们新建一个窗体应用程序,然后把该有的控件,放上去,如图所示:

    2.  用户表SysUser,字段:用户编号(UserID),姓名(Name)

由于这个软件的相当多的一部分编码工作已经做完了,所以要是在这种情况下去更改数据库结构,哇塞,那简直就是将一颗大树连根把起,这样做很不现实,所以要想办法降低软件的改动量。

   澳门新濠3559 1

UserID

Name

1

张三

2

李四

刚遇到这个问题时、我的思考过程是如下的:
(1)将UserID,OrganizationID,IsUsed三列弄成一个主键列,但是这样做也是有问题的,比如这种情况:已经有一个UserID 为 “AAA”OrganizationID 为 “18个8”的用户被删除,现在有添加了一个UserID为“AAA”OrganizationID 为 “18个8”的用户,但是这个用户一旦被删除就会违反主键约束。

 

                       ......

(2)增加一个ID列作为主键,但是这样还是有问题的,因为这样做UserID就不能作为其他表的外键使用了。

 

     3.  按登录次数排序得到2011年登录次数大于20次的用户登陆情况,如下表:

(3)将UserID和OrganizationID设成主键列。删除时 使用DELETE 删除数据,但是同时要用另外的一张表来保存UserID 的历史记录,即某一个UserID对应的真实姓名、创建时间、每一次的修改时间、删除时间,注意这张表中数据只允许添加不允许修改和删除。但是这样做还是于上述的(3)无任何差别,同样改变了主键列,导致外键失效。

  • 创建数据库和存储过程:
  • 澳门新濠3559 2澳门新濠3559 3

     1 USE [DB_USERS]
     2 GO
     3 
     4 /****** Object:  Table [dbo].[T_USERS]    Script Date: 06/07/2015 17:48:33 ******/
     5 SET ANSI_NULLS ON
     6 GO
     7 
     8 SET QUOTED_IDENTIFIER ON
     9 GO
    10 
    11 CREATE TABLE [dbo].[T_USERS](
    12     [T_ID] [INT] IDENTITY(1,1) NOT NULL,
    13     [T_NAME] [NVARCHAR](10) NOT NULL,
    14     [T_PWD] [NVARCHAR](10) NOT NULL,
    15     [T_AGE] [INT] NOT NULL,
    16     [T_ErrorTimes] [INT] NOT NULL,
    17 PRIMARY KEY CLUSTERED 
    18 (
    19     [T_ID] ASC
    20 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    21 ) ON [PRIMARY]
    22 
    23 GO
    

    数据库脚本

用户编号

姓名

登录次数

登陆天数

1月登录次数

......

12月登录次数

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(4)经过上面的思考已经能够明确,SysUser这个表的主键列不能改动,但是还要解决上述的两个问题:【删除一个用户名之后、再添加一个相同的用户名就会报主键约束。而且两个不同的机构之间也不能有相同的用户名。】
慢慢想来,其实这些问题可以转化为如何实现“用户所见【用户名】和数据库实际保存【用户名】相分离”,再具体一点就是【在此以UserID 为 AAA  机构编号为 “18个8”为例】:
    如何将AAA经过一个特定规则转化成其他数据然后保存到数据库中,
再深度思考一下我们可以这样做:将UserID长度改成60,保存数据时UserID列实际保存的数据是
“UserID【长度不固定、但是可以计算出来】”+“OrganizationID【18位、长度固定】”+“一个顺序的四位编号【4位、长度固定】”,读取数据时UserID还是读取用户所见的真是UserID。
澳门新濠3559,其中的“四位顺序编号”是用回来解决相同机构中相同UserID的问题,即同一个机构中第一次出现的“AAA”,默认给他分配“0001”,同一个机构第二次的“AAA”给他分配“0002”,依次类推。【四位的顺序编号、分配最好还要借助一个表去完成,这个表的机构是UserID【用户所见用户名、主键】,OrganizationID【机构编号、主键】,Index【四位顺序编号】】

*数据库表是我之前建好的,现在就直接导成脚本算了。现在来写存储过程

 

这样以来就做到了“用户所见和数据库保存相分离”,这样做只需要改动“相关的UserID列的读写即可”,改动算是比较小的,可以解决上述问题。

*根据用户名来查询信息(因为之前,我写了根据用户名来查询用户信息,但是现在我给之前写的存储过程改个名字)

三 sql实现

嘿嘿…… 大侠们见效喽,如果您还有更好的想法和建议,还望多多指教哦…

我们可以使用:EXEC sp_rename '旧名', '新名',

实现过程:

 

这里我就不贴根据用户名,来查询用户信息的存储过程了,可以看我前面几个系列的文章。

select u.UserID 用戶编号, u.Name 姓名,
count(case when l.Time is not null then 1 else null end) 登录次数,
count(distinct convert(char(10),l.Time,120)) 登录天数,
count(distinct case when convert(char(7),l.Time,120)='2011-01' then 1 else null end) 一月份登录次数,
count(distinct case when convert(char(7),l.Time,120)='2011-02' then 1 else null end) 二月份登录次数,
count(distinct case when convert(char(7),l.Time,120)='2011-03' then 1 else null end) 三月份登录次数,
count(distinct case when convert(char(7),l.Time,120)='2011-04' then 1 else null end) 四月份登录次数,
count(distinct case when convert(char(7),l.Time,120)='2011-05' then 1 else null end) 五月份登录次数,
count(distinct case when convert(char(7),l.Time,120)='2011-06' then 1 else null end) 六月份登录次数,
count(distinct case when convert(char(7),l.Time,120)='2011-07' then 1 else null end) 七月份登录次数,
count(distinct case when convert(char(7),l.Time,120)='2011-08' then 1 else null end) 八月份登录次数,
count(distinct case when convert(char(7),l.Time,120)='2011-09' then 1 else null end) 九月份登录次数,
count(distinct case when convert(char(7),l.Time,120)='2011-10' then 1 else null end) 十月份登录次数,
count(distinct case when convert(char(7),l.Time,120)='2011-11' then 1 else null end) 十一月份登录次数,
count(distinct case when convert(char(7),l.Time,120)='2011-12' then 1 else null end) 十二月份登录次数
from [User] u left join [Log] l on u.UserID = l.UserID 
where convert(char(4),l.time,120) = 2011 
group by u.UserID,u.Name  
having count(case when l.Time is not null then 1 else null end) >= 0 
order by  COUNT(case when l.time is not null then 1 else null end) desc

*用户输入错误,给错误字段自增1的存储过程:

查询效果:

1  IF OBJECT_ID('Incre_errorTimes','P') IS NOT NULL
2  DROP PROCEDURE Incre_errorTimes
3  GO 
4  CREATE PROCEDURE Incre_errorTimes
5  @userName nvarchar(10)
6  AS 
7  UPDATE dbo.T_USERS SET T_ErrorTimes=T_ErrorTimes+1 WHERE T_NAME=@userName
8  GO 
9  

澳门新濠3559 4

*用户输入正确,给错误字段的值设置为0

 

1  IF OBJECT_ID('Reset_errorTimes','P') IS NOT NULL
2  DROP PROCEDURE Reset_errorTimes
3  GO 
4  CREATE PROCEDURE Reset_errorTimes
5  @userName nvarchar(10)
6  AS 
7  UPDATE dbo.T_USERS SET T_ErrorTimes=0 WHERE T_NAME=@userName
8  GO 
9  

 

数据库表,和存储过程弄好之后,我们可以来写程序了

这里的连接字符串,我放在配置文件中,具体怎么操作,就不再赘述了。

编码实现:

 

澳门新濠3559 5澳门新濠3559 6

  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;
 11 using System.Data.SqlClient;
 12 
 13 namespace 窗体的登录程序
 14 {
 15     public partial class Login : Form
 16     {
 17         public Login()
 18         {
 19             InitializeComponent();
 20         }
 21         /// <summary>
 22         /// 增加错误次数
 23         /// </summary>
 24         public void IncerErrTimes(string sql, params SqlParameter[] parmeters)
 25         {
 26             //1创建连接对象
 27             SqlConnection scon = new SqlConnection(DBHelper.GetConnectString());
 28             //2创建命令对象
 29             SqlCommand scmd = new SqlCommand();
 30             scmd.CommandText = sql;
 31             scmd.CommandType = CommandType.StoredProcedure;
 32             scmd.Connection = scon;
 33             //3打开连接
 34             scon.Open();
 35 
 36             //配置参数
 37             foreach (SqlParameter item in parmeters)
 38             {
 39                 scmd.Parameters.Add(item);
 40 
 41             }
 42 
 43             //4执行命令
 44             //5.处理数据
 45            scmd.ExecuteNonQuery();
 46            
 47             //6关闭连接
 48             scon.Close();
 49 
 50            
 51         }
 52 
 53         /// <summary>
 54         /// 充置错误次数
 55         /// </summary>
 56         public void ResetErrTimes(string sql, params SqlParameter[] parmeters)
 57         {
 58             //1创建连接对象
 59             SqlConnection scon = new SqlConnection(DBHelper.GetConnectString());
 60             //2创建命令对象
 61             SqlCommand scmd = new SqlCommand();
 62             scmd.CommandText = sql;
 63             scmd.CommandType = CommandType.StoredProcedure;
 64             scmd.Connection = scon;
 65             //3打开连接
 66             scon.Open();
 67 
 68             //配置参数
 69             foreach (SqlParameter item in parmeters)
 70             {
 71                 scmd.Parameters.Add(item);
 72 
 73             }
 74             //4执行命令
 75             //5.处理数据
 76             scmd.ExecuteNonQuery();
 77 
 78             //6关闭连接
 79             scon.Close();
 80 
 81         }
 82 
 83         private void btnLogin_Click(object sender, EventArgs e)
 84         {
 85             //获取用户输入的用户名和密码
 86             string usernName = txtLoginName.Text.Trim();
 87             string password = txtPassword.Text.Trim();
 88 
 89             //连接方式访问数据库
 90 
 91             //1.创建连接对象(连接字符串)
 92             SqlConnection scon = new SqlConnection(DBHelper.GetConnectString());
 93 
 94             //2创建命令对象
 95             SqlCommand scmd = new SqlCommand();
 96             scmd.CommandText = "GetUserInfoByName";
 97             scmd.CommandType = CommandType.StoredProcedure;
 98             scmd.Connection = scon;
 99 
100             //3打开连接
101             scon.Open();
102 
103             //配置参数
104             SqlParameter parameter = new SqlParameter();
105             parameter.ParameterName = "@name";
106             parameter.DbType = DbType.String;
107             parameter.Value = usernName;
108             parameter.Direction = ParameterDirection.Input;
109             scmd.Parameters.Add(parameter);
110 
111             //4执行命令
112             SqlDataReader reader = scmd.ExecuteReader(CommandBehavior.CloseConnection);
113 
114            
115 
116             //5.处理数据。
117             if (reader.Read())
118             {
119                 int errorTimes =(int)reader["T_ErrorTimes"];
120                 if (errorTimes > 3)
121                 {
122                     MessageBox.Show("登录错误次数过多", "友情提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
123                     return;
124                 }
125 
126                 if (password == reader.GetString(reader.GetOrdinal("T_PWD")))
127                 {
128                     MessageBox.Show("登录成功", "友情提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
129                     ResetErrTimes("Reset_errorTimes", new SqlParameter("@userName", usernName));
130 
131                 }
132                 else
133                 {
134                     MessageBox.Show("密码错误", "友情提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
135                     IncerErrTimes("Incre_errorTimes", new SqlParameter("@userName", usernName));
136 
137                 }
138             }
139             else
140             {
141                 MessageBox.Show("用户名不存在", "友情提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
142             }
143 
144             //6.关闭连接
145             reader.Close();
146 
147 
148         }
149     }
150 }

编码实现

实现这个功能的过程中,遇到了几个问题:

1.创建存储过程的时候,使用的参数是@userName。而根据用户名查询用户的信息的存储过程只用的参数是@name,这两个搞混了,应该分开来。。

2.创建一个封装查询的方法  public void ResetErrTimes(string sql, params SqlParameter[] parmeters)和  public void IncerErrTimes(string sql, params SqlParameter[] parmeters)的时候,写成了 public void ResetErrTimes(string sql, params string[] parmeters)导致后面调用这个方法的时候,报错。

3.循环添加参数的时候,写错了,我之前写成了:

foreach (var item in parmeters)

{

scmd.Parameters.Add(parmeters);    //这是错误的。

}

4.存储过程的改名:EXEC sp_rename '旧名', '新名'

正确的应该是:

foreach (SqlParameter item in parmeters)

{

scmd.Parameters.Add(item);

}

 程序执行的效果图:

澳门新濠3559 7

澳门新濠3559 8

澳门新濠3559 9

澳门新濠3559 10

总的来说,通过做这个例子,我收获很大。大家呢?

编辑:数据库 本文来源:(2)在系统中,用户编号(UserID)

关键词: