C#通过三层链接数据库编写QQ（一）

淩亂°似流年 2022-07-12 15:25 155阅读 0赞

数据库的三层（表示层也成为会话层；逻辑业务层；数据访问层）在数据库的学习中起着至关重要的作用，今天通过编写一个小程序（QQ）来了解数据库三层的实现过程。  
开发工具是VS2013和SQLServer2014  
首先建立数据库保存个人信息：  
IF DB\_ID(‘MYQQ’) IS NOT NULL  
DROP DATABASE MYQQ

CREATE DATABASE MYQQ  
ON  
(  
NAME=MYQQ\_DBT,  
FILENAME=’J:\\SQL\\MYQQ\_DBT.MDF’,  
SIZE=10,  
FILEGROWTH=10  
)  
LOG ON  
(  
NAME=MYQQ\_LOG,  
FILENAME=’J:\\SQL\\MYQQ\_LOG.LDF’,  
SIZE=5,  
FILEGROWTH=5  
)  
USE MYQQ

CREATE TABLE Users  
(  
id INT NOT NULL IDENTITY(1000,1),  
LoginPwd VARCHAR(50) NOT NULL,  
FriendshipPolicyid INT NOT NULL DEFAULT (0),  
NickName VARCHAR(50) NOT NULL,  
Faceid INT NOT NULL ,  
Sex VARCHAR(50) NOT NULL,  
Age INT NOT NULL ,  
Name VARCHAR(50) NULL,  
Starid INT NULL,  
BloodTypeid INT NULL  
)  
CREATE TABLE BloodType  
(  
id INT NOT NULL,  
BloodType VARCHAR(50) NOT NULL  
)  
CREATE TABLE Friends  
(  
id INT NOT NULL,  
Hostid INT NOT NULL,  
Friendid INT NOT NULL  
)  
CREATE TABLE FriendshipPolicy  
(  
id INT NOT NULL,  
FriendshipPolicy VARCHAR(50) NOT NULL  
)  
CREATE TABLE \[Messages\]  
(  
id INT NOT NULL IDENTITY ,  
FromUserid INT NOT NULL,  
ToUserid INT NOT NULL,  
\[Message\] VARCHAR(1000) NULL,  
MessageTypeid INT NOT NULL,  
MessageState INT NOT NULL,  
MessageTime DATETIME NOT NULL  
)  
CREATE TABLE MessageType  
(  
id INT NOT NULL,  
MessageType VARCHAR(50) NOT NULL  
)  
CREATE TABLE Star  
(  
id INT NOT NULL,  
Star VARCHAR(50) NOT NULL  
)

ALTER TABLE BloodType  
ADD CONSTRAINT PK1\_ID PRIMARY KEY (id)

ALTER TABLE FriendshipPolicy  
ADD CONSTRAINT PK2\_ID PRIMARY KEY (id)

ALTER TABLE MessageType  
ADD CONSTRAINT PK3\_ID PRIMARY KEY (id)

ALTER TABLE \[Messages\]  
ADD CONSTRAINT FK7\_ID FOREIGN KEY (MessageTypeid) REFERENCES MessageType (id)

ALTER TABLE Star  
ADD CONSTRAINT PK4\_ID PRIMARY KEY (id)

ALTER TABLE Users  
ADD CONSTRAINT PK\_ID PRIMARY KEY (id)

ALTER TABLE Users  
ADD CONSTRAINT FK1\_ID FOREIGN KEY (Starid) REFERENCES Star(id)  
ALTER TABLE Users  
ADD CONSTRAINT FK2\_ID FOREIGN KEY (BloodTypeid) REFERENCES BloodType(id)  
ALTER TABLE Users  
ADD CONSTRAINT FK8\_ID FOREIGN KEY(FriendshipPolicyid) REFERENCES FriendshipPolicy(id)

ALTER TABLE \[Messages\]  
ADD CONSTRAINT FK5\_ID FOREIGN KEY (FromUserid) REFERENCES Users (id)  
ALTER TABLE \[Messages\]  
ADD CONSTRAINT FK6\_ID FOREIGN KEY (ToUserid) REFERENCES Users (id)

SELECT \* FROM Users  
SELECT \* FROM Friends  
SELECT \* FROM FriendshipPolicy  
SELECT \* FROM \[Messages\]  
SELECT \* FROM MessageType  
SELECT \* FROM Star  
SELECT \* FROM BloodType

DROP TABLE Friends  
DROP TABLE \[Messages\]  
DROP TABLE Users  
DROP TABLE BloodType  
DROP TABLE Star  
DROP TABLE MessageType  
DROP TABLE FriendshipPolicy

INSERT INTO Star  
SELECT 1,’白羊座’ UNION  
SELECT 2,’水瓶座’ UNION  
SELECT 3,’巨蟹座’ UNION  
SELECT 4,’射手座’ UNION  
SELECT 5,’天枰座’ UNION  
SELECT 6,’处女座’ UNION  
SELECT 7,’金牛座’ UNION  
SELECT 8,’双子座’ UNION  
SELECT 9,’狮子座’ UNION  
SELECT 10,’天蝎座’ UNION  
SELECT 11,’摩羯座’ UNION  
SELECT 12,’双鱼座’

INSERT INTO BloodType  
SELECT 1,’A’ UNION  
SELECT 2,’B’ UNION  
SELECT 3,’AB’ UNION  
SELECT 4,’O’

INSERT INTO FriendshipPolicy  
SELECT 1,’任何人都可加我为好友’ UNION  
SELECT 2,’需要好与验证’ UNION  
SELECT 3,’任何人都不能加我为好友’

INSERT INTO MessageType  
SELECT 1,’信息’ UNION  
SELECT 2,’好友验证’ UNION  
SELECT 3,’震动’

INSERT INTO Friends  
SELECT 1,1002,1001 UNION  
SELECT 1,1002,1003 UNION  
SELECT 2,1002,1005 UNION  
SELECT 2,1003,1002 UNION  
SELECT 1,1003,1005 UNION  
SELECT 1,1003,1006 UNION  
SELECT 2,1004,1005 UNION  
SELECT 2,1004,1006 UNION  
SELECT 1,1005,1003

INSERT INTO Users  
SELECT ‘111’,1,’钢琴师’,56,’男’,18,’冯增辉’,1,1 UNION  
SELECT ‘111’,1,’艾光的猫’,55,’女’,19,’孙统’,2,4 UNION  
SELECT ‘111’,1,’陈瘦瘦’,12,’女’,20,’陈鑫鑫’,12,1 UNION  
SELECT ‘111’,1,’丑八怪’,33,’男’,33,’田旭旭’,6,3 UNION  
SELECT ‘111’,1,’XD’,12,’女’,2,’王翔宇’,9,2 UNION  
SELECT ‘111’,1,’追梦’,6,’男’,69,’王哲’,8,1  
这里用的是WPF，首先添加解决方案：模型层（Model），表示层（用户界面，这里名字是MyQQTest5），逻辑业务层（BLL），数据访问层（DAL）  
![这里写图片描述][SouthEast]  
![这里写图片描述][SouthEast 1]  
模型层相对固定，一旦编写好基本不会更改，其中包含两个类  
DBHelper.cs:(用来连接数据库并对数据库进行操作，注意导入命名空间Syste.Data和System.Data.SqlClient,因为方法要返回的类型为DataTable)  
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
using System.Data;  
using System.Data.SqlClient;

namespace Model  
\{  
public class DBHelper  
\{  
string strsql = “data source =.;initial catalog=MyQQ;trusted\_connection=true;”;  
SqlConnection sqlcon;  
///  
/// 返回整张表  
///  
///  
///  
public DataTable Table(string sql)  
\{  
sqlcon = new SqlConnection(strsql);  
SqlCommand cmd = new SqlCommand(sql, sqlcon);  
SqlDataAdapter sda = new SqlDataAdapter(cmd);  
DataTable dt = new DataTable();  
sda.Fill(dt);  
return dt;  
\}  
///  
/// 返回受影响行数  
///  
///  
///  
public int ExecuteNonQuery(string sql)  
\{  
try  
\{  
sqlcon = new SqlConnection(strsql);  
SqlCommand cmd = new SqlCommand(sql, sqlcon);  
sqlcon.Open();  
return cmd.ExecuteNonQuery();  
\}  
catch (Exception)  
\{

throw;
            }
            finally
            {
                sqlcon.Close();
            }
        }
        /// <summary>
        /// 返回第一行的第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
            try
            {
                sqlcon = new SqlConnection(strsql);
                SqlCommand cmd = new SqlCommand(sql, sqlcon);
                sqlcon.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception)
            {
    
                throw;
            }
            finally
            {
                sqlcon.Close();
            }
        }
    }

User.cs:（用来将用户的个人信息封装成属性进行访问）  
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
using System.Data;  
using System.Data.SqlClient;

namespace Model  
\{  
public class User  
\{  
int id;

public int Id
        {
            get { return id; }
            set { id = value; }
        }
        string LoginPwd;
    
        public string LoginPwd1
        {
            get { return LoginPwd; }
            set { LoginPwd = value; }
        }
        int FriendshipPolicyid;
    
        public int FriendshipPolicyid1
        {
            get { return FriendshipPolicyid; }
            set { FriendshipPolicyid = value; }
        }
        string NickName;
    
        public string NickName1
        {
            get { return NickName; }
            set { NickName = value; }
        }
        int Faceid;
    
        public int Faceid1
        {
            get { return Faceid; }
            set { Faceid = value; }
        }
        string Sex;
    
        public string Sex1
        {
            get { return Sex; }
            set { Sex = value; }
        }
        int Age;
    
        public int Age1
        {
            get { return Age; }
            set { Age = value; }
        }
        string Name;
    
        public string Name1
        {
            get { return Name; }
            set { Name = value; }
        }
        int Starid;
    
        public int Starid1
        {
            get { return Starid; }
            set { Starid = value; }
        }
        int BloodTypeid;
    
        public int BloodTypeid1
        {
            get { return BloodTypeid; }
            set { BloodTypeid = value; }
        }
    }

\}  
之后是数据访问层包含一个类  
UserService.cs:(访问用户数据并进行增删改查等操作，注意添加对模型层的引用)  
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
using System.Data;  
using System.Data.SqlClient;  
using Model;  
namespace DAL  
\{  
public class UserService  
\{  
DBHelper db = new DBHelper();  
///  
/// 登录验证  
///  
///  
///  
///  
public int CheckLogin(int id,string pwd)  
\{  
string sql = “select count(\*) from users where id=”+id+” and loginpwd=’”+pwd+”’”;  
return (int)db.ExecuteScalar(sql);  
\}  
///  
/// 星座表  
///  
///  
public DataTable StarTable()  
\{  
string sql = “select \* from star”;  
return db.Table(sql);  
\}  
///  
/// 血型表  
///  
///  
public DataTable BloodTypeTable()  
\{  
string sql = “select \* from bloodtype”;  
return db.Table(sql);  
\}  
///  
/// 好友验证表  
///  
///  
public DataTable FriednshpPolicyTable()  
\{  
string sql = “select \* from friendshippolicy”;  
return db.Table(sql);  
\}  
///  
/// 注册  
///  
///  
///  
public int RegisterQQ(User user)  
\{  
string sql = “insert into users values (‘”+user.LoginPwd1+”’,”+user.NickName1+”,”)”;  
return db.ExecuteNonQuery(sql);  
\}  
///  
/// 查找注册Id  
///  
///  
public int FindId()  
\{  
string sql = “select id from users order by id desc”;  
return (int)db.ExecuteScalar(sql);  
\}  
///  
/// 查找ID对应的资料  
///  
///  
///  
public DataTable FindData(int id)  
\{  
string sql = “select \* from users where id=”+id;  
return db.Table(sql);  
\}  
///  
/// 寻找我的家人  
///  
///  
///  
public DataTable FindFamily(int id)  
\{  
string sql = “select \* from users where id in (select friendid from friends where hostid=”+id+” and id=1)”;  
return db.Table(sql);  
\}  
///  
/// 寻找我的朋友  
///  
///  
///  
public DataTable FindFriends(int id)  
\{  
string sql = “select \* from users where id in (select friendid from friends where hostid=” + id + ” and id=2)”;  
return db.Table(sql);  
\}  
///  
/// 寻找陌生人  
///  
///  
///  
public DataTable FindPeople(int id)  
\{  
string sql = “select \* from users where id not in(select friendid from friends where hostid=”+id+”) and id !=”+id;  
return db.Table(sql);  
\}  
///  
/// 修改资料  
///  
///  
///  
///  
public int IsChanged(User user, int id)  
\{  
string sql = “update users set loginpwd=’” + user.LoginPwd1 + “’,friendshippolicyid=” + 1 + “,nickname=’” + user.NickName1 + “’,faceid=” + user.Faceid1 + “,sex=’” + user.Sex1 + “’,age=” + user.Age1 + “,name=’” + user.Name1 + “’,starid=” + user.Starid1 + “,bloodtypeid=” + user.BloodTypeid1 + ” where id=” + id;  
return db.ExecuteNonQuery(sql);  
\}  
///  
/// 添加聊天信息  
///  
///  
///  
///  
///  
///  
public int AddMessage(string messages,int fromuserid,int touserid,int messagetypeid)  
\{  
string sql = “insert into \[messages\] values (” + fromuserid + “,” + touserid + “,’” + messages + “’,” + messagetypeid + “,” + 1 + “,1997-09-23)”;  
return db.ExecuteNonQuery(sql);  
\}  
///  
/// 添加朋友  
///  
///  
///  
///  
public int AddFriends(int hostid, int friendid)  
\{  
string sql = “insert into friends select ” + 1 + “,” + hostid + “,” + friendid;  
return db.ExecuteNonQuery(sql);  
\}  
///  
/// 查找好友的信息  
///  
///  
public DataTable FindMessage(int fromuserid, int touserid)  
\{  
string sql = “select \* from \[messages\] where fromuserid=”+fromuserid+” and touserid=”+touserid;  
return db.Table(sql);  
\}  
///  
/// 删除信息  
///  
///  
///  
///  
public int DeleteMessages(int fromuserid,int touserid)  
\{  
string sql = “delete from \[messages\] where fromuserid=” + fromuserid + ” and touserid=” + touserid;  
return db.ExecuteNonQuery(sql);  
\}  
///  
/// 判断是否为好友  
///  
///  
///  
///  
public int IsFriend(int hostid,int friendid)  
\{  
string sql = “select count(\*) from friends where hostid=”+hostid+” and friendid=”+friendid;  
return (int)db.ExecuteScalar(sql);  
\}  
///  
/// 模糊查询  
///  
///  
///  
public DataTable FindIt(int id)  
\{  
string sql = “select \* from users where id like ‘”+id+”%’”;  
return db.Table(sql);  
\}  
public int IsNews(int fromuserid,int touserid)  
\{  
string sql = “select count(\*) from \[messages\] where fromuserid=” + fromuserid + ” and touserid=” + touserid;  
return (int)db.ExecuteScalar(sql);  
\}  
\}  
\}  
逻辑业务层包含一个类：  
UserManager.cs:(用来获取表示层的请求并从数据访问层获取和传递数据，注意添加对数据访问层和模型层的引用)  
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
using System.Data;  
using System.Data.SqlClient;  
using DAL;  
using Model;

namespace BLL  
\{  
public class UserManager  
\{  
UserService us=new UserService();  
public int CheckLogin(int id, string pwd)  
\{  
return us.CheckLogin(id,pwd);  
\}  
public DataTable StarTable()  
\{  
return us.StarTable();  
\}  
public DataTable BloodTypeTable()  
\{  
return us.BloodTypeTable();  
\}  
public DataTable FriednshpPolicyTable()  
\{  
return us.FriednshpPolicyTable();  
\}  
public int RegisterQQ(User user)  
\{  
return us.RegisterQQ(user);  
\}  
public int FindId()  
\{  
return us.FindId();  
\}  
public DataTable FindData(int id)  
\{  
return us.FindData(id);  
\}  
public DataTable FindFamily(int id)  
\{  
return us.FindFamily(id);  
\}  
public DataTable FindFriends(int id)  
\{  
return us.FindFriends(id);  
\}  
public DataTable FindPeople(int id)  
\{  
return us.FindPeople(id);  
\}  
public int IsChanged(User user, int id)  
\{  
return us.IsChanged(user, id);  
\}  
public int AddMessage(string messages, int fromuseid, int touserid, int messagetypeid)  
\{  
return us.AddMessage(messages, fromuseid, touserid, messagetypeid);  
\}  
public int AddFriends(int hostid, int friendid)  
\{  
return us.AddFriends(hostid, friendid);  
\}  
public DataTable FindMessage(int fromuserid, int touserid)  
\{  
return us.FindMessage(fromuserid,touserid);  
\}  
public int DeleteMessages(int fromuserid, int touserid)  
\{  
return us.DeleteMessages(fromuserid,touserid);  
\}  
public int IsFriend(int hostid, int friendid)  
\{  
return us.IsFriend(hostid,friendid);  
\}  
public DataTable FindIt(int id)  
\{  
return us.FindIt(id);  
\}  
public int IsNews(int fromuserid, int touserid)  
\{  
return us.IsNews(fromuserid,touserid);  
\}  
\}  
\}  
以上讲的都是C\#实现数据库的三层，而表示层通过需求向逻辑业务层传递请求，在通过曾与层之间的数据中传递最终实现数据的访问。表示层的图形用户界面则根据用户去求进行编写，下一节将会进行讲解。

[SouthEast]: /images/20220709/f74746b2a8954027b4cf7243e82b8dc8.png
[SouthEast 1]: /images/20220709/49633e21153a472ab91d8509012c74dc.png