多层多数据库模式开发的实验（六）数据层-SQLServer数据库

    经过上篇文章的介绍，我们实现了Access数据库的设计。本节，我们来实现SQLServer数据库的设计。

    同样的，对于SQLServer数据库我们也在这里将一些可复用的代码抽取出来，编写成助手类，以此减少代码量，提高代码复用性。这个助手类也取名SQLHelper。代码如下：

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Configuration;
using System.Web;
using System.Web.Caching;
using System.Data;
using MWC.Utility;
using System.Data.SqlClient;

namespace MWC.DAL.SQLServer
{
    public sealed class SQLHelper
    {
        private static string GetConnectionString()
        {
            if (CacheUtility.GetFromCache("SQLServerConnectionString") != null)
            {
                return CacheUtility.GetFromCache("SQLServerConnectionString").ToString();
            }
            else
            {
                string connectionString = ConfigurationSettings.AppSettings["SQLServerConnectionString"];

                CacheDependency fileDependency = new CacheDependency(HttpContext.Current.Server.MapPath("Web.Config"));
                CacheUtility.SaveToCache("SQLServerConnectionString", connectionString, fileDependency);

                return connectionString;
            }
        }

        public static void ExecuteSQLNonQuery(string SQLCommand, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(GetConnectionString());
            SqlCommand comm = new SqlCommand(SQLCommand, conn);
            
            try
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    comm.Parameters.Add(parameters[i]);
                }

                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                comm.Dispose();
                conn.Close();
            }
        }

        public static DataSet ExecuteSQLDataSet(string SQLCommand)
        {
            SqlConnection conn = new SqlConnection(GetConnectionString());            
            SqlDataAdapter da = new SqlDataAdapter(SQLCommand, conn);

            try
            {
                conn.Open();
                DataSet ds = new DataSet();
                da.Fill(ds);

                return ds;
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                da.Dispose();
                conn.Close();
            }
        }

        public static DataSet ExecuteSQLDataSet(string SQLCommand, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(GetConnectionString());
            SqlCommand comm = new SqlCommand(SQLCommand, conn);
            SqlDataAdapter da = new SqlDataAdapter();

            try
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    comm.Parameters.Add(parameters[i]);
                }

                conn.Open();
                da.SelectCommand = comm;
                DataSet ds = new DataSet();
                da.Fill(ds);

                return ds;
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                da.Dispose();
                comm.Dispose();
                conn.Close();
            }
        }
    }
}

 

用户User：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using MWC.Entity;
using MWC.Interface;
using System.Data;
using System.Data.SqlClient;

namespace MWC.DAL.SQLServer
{
    public class User : IUser
    {
        public void Insert(Entity.User user)
        {
            SqlParameter[] parameters ={
                new SqlParameter("UserID",SqlDbType.UniqueIdentifier),
                new SqlParameter("UserName",SqlDbType.VarChar,50),
                new SqlParameter("LoginUID",SqlDbType.VarChar,50),
                new SqlParameter("LoginPWD",SqlDbType.VarChar,50)
            };

            parameters[0].Value = user.UserID;
            parameters[1].Value = user.UserName;
            parameters[2].Value = user.LoginUID;
            parameters[3].Value = user.LoginPWD;

            string SQLCommand = "INSERT INTO [User] (UserID,UserName,LoginUID,LoginPWD) VALUES (@UserID,@UserName,@LoginUID,@LoginPWD)";

            SQLHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
        }

        public DataSet GetList()
        {
            string SQLCommand = "Select * FROM [User]";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand);
        }

        public DataSet GetUserFromID(Guid userID)
        {
            SqlParameter[] parameters ={
                new SqlParameter("UserID",SqlDbType.UniqueIdentifier)
            };

            parameters[0].Value = userID;

            string SQLCommand = "Select * FROM [User] WHERE UserID=@UserID";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand, parameters);
        }

        public DataSet GetUserFromName(string userName)
        {
            SqlParameter[] parameters ={
                new SqlParameter("UserName",SqlDbType.VarChar,50)
            };

            parameters[0].Value = userName;

            string SQLCommand = "Select * FROM [User] WHERE UserName=@UserName";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand, parameters);
        }
    }  
}

 

部门、群组Group：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using MWC.Entity;
using MWC.Interface;
using System.Data;
using System.Data.SqlClient;

namespace MWC.DAL.SQLServer
{
    public class Group : IGroup
    {
        public void Insert(Entity.Group group)
        {
            SqlParameter[] parameters ={
                new SqlParameter("GroupID",SqlDbType.UniqueIdentifier),
                new SqlParameter("GroupName",SqlDbType.VarChar,50)
            };

            parameters[0].Value = group.GroupID;
            parameters[1].Value = group.GroupName;

            string SQLCommand = "INSERT INTO [Group] (GroupID,GroupName) VALUES (@GroupID,@GroupName)";

            SQLHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
        }

        public DataSet GetList()
        {
            string SQLCommand = "Select * FROM [Group]";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand);
        }

        public DataSet GetGroupFromID(Guid groupID)
        {
            SqlParameter[] parameters ={
                new SqlParameter("GroupID",SqlDbType.UniqueIdentifier)
            };

            parameters[0].Value = groupID;

            string SQLCommand = "Select * FROM [Group] WHERE GroupID=@GroupID";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand, parameters);
        }

        public DataSet GetGroupFromName(string groupName)
        {
            SqlParameter[] parameters ={
                new SqlParameter("GroupName",SqlDbType.VarChar,50)
            };

            parameters[0].Value = groupName;

            string SQLCommand = "Select * FROM [Group] WHERE GroupName=@GroupName";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand, parameters);
        }

        public void InsertUserToGroup(Guid groupID, Guid userID)
        {
            SqlParameter[] parameters ={
                new SqlParameter("GroupID",SqlDbType.UniqueIdentifier),
                new SqlParameter("UserID",SqlDbType.UniqueIdentifier)
            };

            parameters[0].Value = groupID;
            parameters[1].Value = userID;

            string SQLCommand = "INSERT INTO [GroupOfUser] (GroupID,UserID) VALUES (@GroupID,@UserID)";

            SQLHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
        }

        public DataSet GetGroupOfUser(Guid groupID)
        {
            SqlParameter[] parameters ={
                new SqlParameter("GroupID",SqlDbType.UniqueIdentifier)
            };

            parameters[0].Value = groupID;

            string SQLCommand = "Select gu.GroupID,g.GroupName,gu.UserID,u.UserName FROM ([GroupOfUser] gu ";
            SQLCommand += "left outer join [Group] g on gu.GroupID=g.GroupID) ";
            SQLCommand += "left outer join [User] u on gu.UserID=u.UserID ";
            SQLCommand += "WHERE gu.GroupID=@GroupID";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand, parameters);
        }
    }
}

 

    从这2篇文章我们可以看出，不同的数据库因为设计的不同，导致各自的执行代码存在着一些误差（虽然很多地方很相似）。但是经过访问工厂提供接口，用户完全不用关心使用的是那个数据库。