使用 Linq 和 WPF 进行 N 层开发

Nigel-Findlater

2.55/5 (8投票s)

2008 年 11 月 11 日

CPOL

6分钟阅读

34837

501

使用 Linq 和 WPF 进行 N 层开发

下载 LinqDataAccess - 311.08 KB

引言

在本文中，我将通过一个事务性数据库应用程序，使用 WPF 作为 UI，Linq 作为 DAL，来展示一小部分功能。我想遵循数据从数据访问层 DAL 到业务逻辑层，再到 WPF UI 的路径。

N 层架构

我喜欢将功能封装到应用程序的各个层中。这样，我可以将应用程序模块彼此隔离，从而最大限度地减少更改的影响。它还有助于以一种易于维护和进一步开发的方式构建应用程序，从而能够在各种抽象级别上进行测试驱动的开发。结果是一个可扩展的架构，可以通过简单地修改适当层中的适当子项目来适应新技术。它也使团队开发更容易，有助于避免冲突更新。我在子项目前面加上了

DAL = 数据访问层
DOM = 领域层
UI = 用户界面
UTILS = 可在所有层中访问的实用工具

Linq 数据访问层

我避免使用第三方 DAL 的主要原因是，除了购买下一个版本之外，没有自动升级路径，而且开发者也没有熟悉 Microsoft 提供的内置函数，而是学习了可能可用也可能不可用的第三方接口。尽管我旧的 DAL 在 .Net 1.0 到 3.5 中都能工作，而 Linq 只在 3.5 及更高版本中可用，但我认为我将来不会编写任何 .Net 2.0 代码，因此我可以享受这项新技术带来的简化……

正如我在之前的 VB DAL https://codeproject.org.cn/KB/database/NigelDBTable.aspx 中所做的那样，我在一个可以包含在整个解决方案中的程序集中定义了值对象。这些对象与数据库表中的内容是一对一对应的。将它们放在一个单独的程序集中，就可以通过业务层将 DAL 与 UI 分开。这里有两个例子，对应数据库中的表

USR_USERInfo.cs
===============
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

namespace UTILS.TestDB
{
    public class USR_USERInfo
    {
        public class TableStructure
        {
            public int USER_ID;
            public String USER_NAME;
            public String EMAIL;
        }
    }
}

USR_USER_GROUPInfo
==================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

namespace UTILS.TestDB
{
    public class USR_USER_GROUPInfo
    {
        public class TableStructure
        {
            public int USER_GROUP_ID;
            public String USER_GROUP;
        }
        public enum USER_GROUPEnum
        {
            Super = 1,
            Normal = 2
        }

    }
}

注意：此方法不兼容以下方式定义的 VB 值对象。原因是，将 Linq 数据对象转换为此数据对象的函数能够分配所有字段，但在返回时，某些字段未返回。奇怪的是，有些字段有效，而有些则无效。VB 结构与公共类不同，因此对这些对象的内存管理进行了一些奇怪的处理，导致 ConvertLinqToTableStructure 在将值作为对象返回时遇到问题。

Imports System.Reflection
Public Class USR_USERInfo
    Public Data As TableStructure
    Public ReadOnlyFields As ReadOnlyFieldsStructure

#Region "Developer defines the table in this seaction"

    REM Definition of fields in the database
    REM NB We need to use a structure otherwize this does not appear in intellisence when 
    REM programming function parameter data types
    Public Structure TableStructure
        Dim USER_ID As Integer
        Dim DOMAIN_USER_NAME As String
        Dim DISPLAY_NAME As String
        Dim MAX_SIM_SERVER_USAGE As Integer
        Dim EMAIL As String
    End Structure
    Public IndexField As String = "USER_ID"          ' Primary index
    Public Structure ReadOnlyFieldsStructure
        Dim USER_ID As Integer
    End Structure

#End Region
End Class

我创建了一个包含所有 Linq 代码的项目。我创建了一些 Linq to SQL 图，并将数据库中的所有表拖放进来。为此，请向 DAL 子项目添加一个新的 Linq to SQL 类，然后从服务器导航器浏览到要导入的表，并将它们拖放到新创建的 Linq to SQL 类上。

我发现将表分组在一起是个好主意，否则屏幕会显得有点拥挤。我还发现不可能在同一项目内的两个图表中拖放同一个表。当数据库发生更改时，需要删除受影响的相应表，刷新服务器资源管理器，然后将表重新拖放到相应的 Linq to SQL 图中。我使用反射来自动化数据从整个解决方案中可访问的值对象到仅在 DAL 中可访问的 Linq 表对象的转换。这在 DBTable 中完成。由于并非所有内容都可以轻松地用 Linq 实现，因此我在其中包含了一些老式的 OLEDB 内容……

DBTable.cs
==========
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;


using System.Reflection;
namespace DAL.TestDB
{
    public class DBTable
    {
        private String _DatabaseServerName;
        private String _DatabaseName;
        private String ConnectionStr;
        public String LinqConnectionStr;
        private String DefaultTableName = "DAL";

        public DBTable(string DatabaseServerName, string DatabaseName)
        {
            _DatabaseServerName = DatabaseServerName;
            _DatabaseName = DatabaseName;
            // String ComputerName = "ThisPC"; (My.Computer.Name
            String ComputerName = System.Net.Dns.GetHostName();

            DefaultTableName = this.GetType().Name;

            ConnectionStr = "Provider=sqloledb;workstation id='" 
                + ComputerName + 
                   "';packet size=4096;integrated security=SSPI;data source='" 
                + DatabaseServerName + 
                   "';persist security info=False;initial catalog=" 
                + DatabaseName + "";
            LinqConnectionStr = "Data Source=" + DatabaseServerName 
                + ";Initial Catalog=" + DatabaseName + ";Integrated Security=True";
        }

        #region Conversion of objects
        public bool ConvertInfoToInfo(object Input, object Output, ref String Message)
        {
            try
            {
                FieldInfo[] InputFields;
                InputFields = Input.GetType().GetFields();

                FieldInfo[] OutputFields;
                OutputFields = Output.GetType().GetFields();

                for (int i = 0; i <= InputFields.Length - 1; i++)
                {

                    for (int j = 0; j <= OutputFields.Length - 1; j++)
                    {
                        if ((String)InputFields[i].Name == (String)OutputFields[j].Name)
                        {
                            OutputFields[j].SetValue(Output,
                                InputFields[i].GetValue(Input));
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Message = ex.ToString();
                return false;
            }
        }
        public bool ConvertTableStructureToLinq(object Input, object Output,
            ref String Message)
        {
            try
            {
                FieldInfo[] InputFields;
                InputFields = Input.GetType().GetFields();

                PropertyInfo[] OutputProperties;
                OutputProperties = Output.GetType().GetProperties();

                for (int i = 0; i <= InputFields.Length - 1; i++)
                {
                    for (int j = 0; j <= OutputProperties.Length - 1; j++)
                    {
                        if ((String)InputFields[i].Name == 
                            (String)OutputProperties[j].Name)
                        {
                            OutputProperties[j].SetValue(Output,
                                InputFields[i].GetValue(Input), null);
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Message = ex.ToString();
                return false;
            }
        }
        public bool ConvertLinqToTableStructure(object Input, object Output,
            ref String Message)
        {
            try
            {
                PropertyInfo[] InputProperties;
                InputProperties = Input.GetType().GetProperties();

                FieldInfo[] OutputFields;
                OutputFields = Output.GetType().GetFields();

                for (int i = 0; i <= InputProperties.Length - 1; i++)
                {
                    for (int j = 0; j <= OutputFields.Length - 1; j++)
                    {
                        if ((String)InputProperties[i].Name == 
                            (String)OutputFields[j].Name)
                        {
                            OutputFields[j].SetValue(Output,
                                InputProperties[i].GetValue(Input,null));
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Message = ex.ToString();
                return false;
            }
        }
        public bool CompareTableStructureToLinq(object TableStructure,
            object LinqTable, ref String Message)
        {
            try
            {
                FieldInfo[] TableStructureFields;
                TableStructureFields = TableStructure.GetType().GetFields();

                PropertyInfo[] LinqTableProperties;
                LinqTableProperties = LinqTable.GetType().GetProperties();

                for (int i = 0; i <= TableStructureFields.Length - 1; i++)
                {
                    Boolean Found = false;
                    for (int j = 0; j <= LinqTableProperties.Length - 1; j++)
                    {
                        if ((String)TableStructureFields[i].Name == 
                            (String)LinqTableProperties[j].Name)
                        {
                            Found = true;
                        }
                    }
                    if (Found == false) return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                Message = ex.ToString();
                return false;
            }
        }
        public bool CompareLinqToTableStructure(object LinqTable, object TableStructure,
            ref String Message)
        {
            try
            {
                FieldInfo[] TableStructureFields;
                TableStructureFields = TableStructure.GetType().GetFields();

                PropertyInfo[] LinqTableProperties;
                LinqTableProperties = LinqTable.GetType().GetProperties();

                for (int j = 0; j <= LinqTableProperties.Length - 1; j++) 
                {
                    
                    Boolean Found = false;
                    switch (LinqTableProperties[j].PropertyType.FullName )
                    {
                        case "System.Int32":
                        case "System.Boolean":
                        case "System.Double":
                        case "System.String":
                            for (int i = 0; i <= TableStructureFields.Length - 1; i++)
                            {
                                if ((String)TableStructureFields[i].Name ==
                                    (String)LinqTableProperties[j].Name)
                                {
                                    Found = true;
                                }
                            }
                            if (Found == false) return false;
                        break;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Message = ex.ToString();
                return false;
            }
        }
        #endregion

        #region OLEDB
        public OleDbConnection objConn = new OleDbConnection();

        #region Basic database operations
            public OleDbConnection OpenConnection()
        {


            OleDbConnection objConnection = new OleDbConnection();
            if (objConn == null)
            {
                objConnection.ConnectionString = ConnectionStr;
                objConnection.Open();
                return objConnection;
            };

            if (objConn.State != ConnectionState.Open)
            {
                objConnection.ConnectionString = ConnectionStr;
                objConnection.Open();
                return objConnection;
            }
            else
            {
                return objConn;
            }
        }
            public DataSet SQLDS(string SQL)
        {
            System.Data.DataSet ds = new DataSet();
            objConn = OpenConnection();
            OleDbDataAdapter objCmd = new OleDbDataAdapter(SQL, objConn);
            objCmd.Fill(ds, DefaultTableName);
            objCmd.Dispose();
            return ds;
        }
            public bool ExecuteSQL(string SQL)
        {
            objConn = OpenConnection();

            OleDbCommand objCmd = new OleDbCommand(SQL, objConn);
            objCmd.CommandType = CommandType.Text;
            objCmd.ExecuteNonQuery();
            objCmd.Dispose();

            return true;
        }
        #endregion

        #region Higher database operations
            public DataSet GetDS(String TableName)
        {
            string SQL = "SELECT * FROM " + TableName + " ";
            DataSet ds = new DataSet();
            ds = SQLDS(SQL);
            return ds;
        }
        #endregion

        #region Database string manipulation
            public string DBSTR(string sStr ) 
		{
			// Function to convert strings into a format that the
                           // database can take
			sStr = sStr.Replace("'", "''");
			return sStr;
		}
	    	public string SQLBooleanValue(bool bBoolean ) 
		{
			return " '" + BOOLEAN_To_DBSTR(bBoolean) + "' ";
		}
		    public string SQLStringValue(string Str )
		{
			return " '" + DBSTR(Str) + "' ";
		}
    		public string BOOLEAN_To_DBSTR(bool bValue )
		{
			if (bValue)
			{
				return "True";
			}
			else 
			{
				return "False";
			};
        }
        #endregion

        #endregion


    }
}

这是用于将数据库函数暴露给业务层（而非 UI）的 Facade。它继承自 DBTable，并且有变得相当庞大的趋势。

TestDBFacade.cs
===============
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using UTILS.TestDB;

using System.Data;

namespace DAL.TestDB

{
    public class TestDBFacade: DBTable
    {
        public TestDBFacade(String DatabaseServerName, string DatabaseName)
            : base(DatabaseServerName, DatabaseName)
        {

        }

        #region USR_USER
        public Boolean USR_USERInsertRecord(USR_USERInfo.TableStructure USR_USERRow,
            ref String Message)
        {
            try
            {
                USR_USER USR_USERLinq = new USR_USER();
                if (ConvertTableStructureToLinq(USR_USERRow, USR_USERLinq,
                    ref Message) == false) return false;

                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext = 
                    new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString =
                    LinqConnectionStr;
                iTestDB_UsrTablesDataContext.USR_USERs.InsertOnSubmit(USR_USERLinq);
                iTestDB_UsrTablesDataContext.SubmitChanges();

            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return false;
            }
            return true;
        }
        public int USR_USERInsertAndReadRecord(USR_USERInfo.TableStructure USR_USERRow,
            ref String Message)
        {
            try
            {
                USR_USER USR_USERLinq = new USR_USER();
                if (ConvertTableStructureToLinq(USR_USERRow, USR_USERLinq,
                    ref Message) == false) return -1;

                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext =
                    new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString =
                    LinqConnectionStr;
                iTestDB_UsrTablesDataContext.USR_USERs.InsertOnSubmit(USR_USERLinq);
                iTestDB_UsrTablesDataContext.SubmitChanges();
                return USR_USERLinq.USER_ID;
            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return -1;
            }
        }
        public Boolean USR_USERGetRecord(ref USR_USERInfo.TableStructure USR_USERRow,
            ref String Message)
        {
            try
            {
                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext =
                    new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString =
                    LinqConnectionStr;
                USR_USER USR_USERLinq;
                int USER_ID = USR_USERRow.USER_ID;
                USR_USERLinq = (from Something
                                  in iTestDB_UsrTablesDataContext.USR_USERs
                                where Something.USER_ID == USER_ID
                                select Something).Single();

                if (ConvertLinqToTableStructure(USR_USERLinq, USR_USERRow,
                    ref Message) == false) return false;

                return true;
            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return false;
            }
        }
        public Boolean USR_USERUpdateRecord(USR_USERInfo.TableStructure USR_USERRow,
            ref String Message)
        {
            try
            {
                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext =
                    new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString =
                    LinqConnectionStr;
                USR_USER USR_USERLinq;
                int USER_ID = USR_USERRow.USER_ID;
                USR_USERLinq = (from Something
                                  in iTestDB_UsrTablesDataContext.USR_USERs
                                where Something.USER_ID == USER_ID
                                select Something).Single();

                if (ConvertTableStructureToLinq(USR_USERRow, USR_USERLinq,
                    ref Message) == false) return false;

                iTestDB_UsrTablesDataContext.Refresh(
                    System.Data.Linq.RefreshMode.KeepCurrentValues, USR_USERLinq);
                iTestDB_UsrTablesDataContext.SubmitChanges(
                    System.Data.Linq.ConflictMode.ContinueOnConflict);

                return true;
            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return false;
            }
        }
        public Boolean USR_USERDeleteRecord(int USER_ID, ref String Message)
        {
            try
            {
                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext =
                    new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString =
                    LinqConnectionStr;
                USR_USER USR_USERLinq;
                USR_USERLinq = (from Something
                                  in iTestDB_UsrTablesDataContext.USR_USERs
                               where Something.USER_ID == USER_ID
                              select Something).Single();


                iTestDB_UsrTablesDataContext.USR_USERs.DeleteOnSubmit((
                    from Something
                      in iTestDB_UsrTablesDataContext.USR_USERs
                   where Something.USER_ID == USER_ID
                  select Something).Single());

                iTestDB_UsrTablesDataContext.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return false;
            }
        }
        public List USR_USERList()
        {
                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext =
                    new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString =
                    LinqConnectionStr;
                List USR_USERList;

                USR_USERList = (from Something
                                in iTestDB_UsrTablesDataContext.USR_USERs
                                where Something.USER_ID > 0
                                select Something).ToList();

                return USR_USERList;
        }
        public List USR_USERInfoList()
        {
            String Message = "";
            List USR_USERInfoLst = new List();
            List USR_USERLst;
            USR_USERLst = USR_USERList();
            foreach( USR_USER el in USR_USERLst)
            {
                USR_USERInfo.TableStructure newElement =
                    new USR_USERInfo.TableStructure();
                if (ConvertLinqToTableStructure(el, newElement,
                    ref Message) == false) return null;
                USR_USERInfoLst.Add(newElement);
            }
            return USR_USERInfoLst;
        }
        public DataSet USR_USERGetDS()
        {
            String TableName = "USR_USER";
            DataSet DS = new DataSet();

            DS = this.GetDS(TableName);
            return DS;
        }
        #endregion
        #region USR_USER_GROUP
        public Boolean USR_USER_GROUPInsertRecord(
            USR_USER_GROUPInfo.TableStructure USR_USER_GROUPRow, ref String Message)
        {
            try
            {
                USR_USER_GROUP USR_USER_GROUPLinq = new USR_USER_GROUP();
                if (ConvertTableStructureToLinq(USR_USER_GROUPRow, USR_USER_GROUPLinq,
                    ref Message) == false) return false;

                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext = 
                    new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString = 
                    LinqConnectionStr;
                iTestDB_UsrTablesDataContext.USR_USER_GROUPs.InsertOnSubmit(
                    USR_USER_GROUPLinq);
                iTestDB_UsrTablesDataContext.SubmitChanges();

            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return false;
            }
            return true;
        }
        public int USR_USER_GROUPInsertAndReadRecord(
            USR_USER_GROUPInfo.TableStructure USR_USER_GROUPRow, ref String Message)
        {
            try
            {
                USR_USER_GROUP USR_USER_GROUPLinq = new USR_USER_GROUP();
                if (ConvertTableStructureToLinq(USR_USER_GROUPRow, USR_USER_GROUPLinq,
                    ref Message) == false) return -1;

                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext =
                    new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString =
                    LinqConnectionStr;
                iTestDB_UsrTablesDataContext.USR_USER_GROUPs.InsertOnSubmit(
                    USR_USER_GROUPLinq);
                iTestDB_UsrTablesDataContext.SubmitChanges();
                return USR_USER_GROUPLinq.USER_GROUP_ID;
            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return -1;
            }
        }
        public Boolean USR_USER_GROUPGetRecord(
            ref USR_USER_GROUPInfo.TableStructure USR_USER_GROUPRow, ref String Message)
        {
            try
            {
                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext =
                    new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString =
                    LinqConnectionStr;
                USR_USER_GROUP USR_USER_GROUPLinq;
                int USER_GROUP_ID = USR_USER_GROUPRow.USER_GROUP_ID;
                USR_USER_GROUPLinq = (from Something
                                  in iTestDB_UsrTablesDataContext.USR_USER_GROUPs
                                      where Something.USER_GROUP_ID == USER_GROUP_ID
                                      select Something).Single();

                if (ConvertLinqToTableStructure(USR_USER_GROUPLinq, USR_USER_GROUPRow,
                    ref Message) == false) return false;

                return true;
            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return false;
            }
        }
        public Boolean USR_USER_GROUPUpdateRecord(
            USR_USER_GROUPInfo.TableStructure USR_USER_GROUPRow, ref String Message)
        {
            try
            {
                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext =
                    new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString =
                    LinqConnectionStr;
                USR_USER_GROUP USR_USER_GROUPLinq;
                int USER_GROUP_ID = USR_USER_GROUPRow.USER_GROUP_ID;
                USR_USER_GROUPLinq = (from Something
                                  in iTestDB_UsrTablesDataContext.USR_USER_GROUPs
                                      where Something.USER_GROUP_ID == USER_GROUP_ID
                                      select Something).Single();

                if (ConvertTableStructureToLinq(USR_USER_GROUPRow, USR_USER_GROUPLinq,
                    ref Message) == false) return false;

                iTestDB_UsrTablesDataContext.Refresh(
                    System.Data.Linq.RefreshMode.KeepCurrentValues, USR_USER_GROUPLinq);
                iTestDB_UsrTablesDataContext.SubmitChanges(
                    System.Data.Linq.ConflictMode.ContinueOnConflict);

                return true;
            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return false;
            }
        }
        public Boolean USR_USER_GROUPDeleteRecord(int USER_GROUP_ID, ref String Message)
        {
            try
            {
                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext =
                   new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString =
                   LinqConnectionStr;
                USR_USER_GROUP USR_USER_GROUPLinq;
                USR_USER_GROUPLinq = (from Something
                                  in iTestDB_UsrTablesDataContext.USR_USER_GROUPs
                                      where Something.USER_GROUP_ID == USER_GROUP_ID
                                      select Something).Single();


                iTestDB_UsrTablesDataContext.USR_USER_GROUPs.DeleteOnSubmit((
                    from Something
                      in iTestDB_UsrTablesDataContext.USR_USER_GROUPs
                   where Something.USER_GROUP_ID == USER_GROUP_ID
                  select Something).Single());

                iTestDB_UsrTablesDataContext.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return false;
            }
        }
        public List USR_USER_GROUPList()
        {
            TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext =
                new TestDB_UsrTablesDataContext();
            iTestDB_UsrTablesDataContext.Connection.ConnectionString = LinqConnectionStr;
            List USR_USER_GROUPList;

            USR_USER_GROUPList = (from Something
                            in iTestDB_UsrTablesDataContext.USR_USER_GROUPs
                                  where Something.USER_GROUP_ID > 0
                                  select Something).ToList();

            return USR_USER_GROUPList;
        }
        public List USR_USER_GROUPInfoList()
        {
            String Message = "";
            List USR_USER_GROUPInfoLst = new List();
            List USR_USER_GROUPLst;
            USR_USER_GROUPLst = USR_USER_GROUPList();
            foreach (USR_USER_GROUP el in USR_USER_GROUPLst)
            {
                USR_USER_GROUPInfo.TableStructure newElement = 
                    new USR_USER_GROUPInfo.TableStructure();
                if (ConvertLinqToTableStructure(el, newElement,
                    ref Message) == false) return null;
                USR_USER_GROUPInfoLst.Add(newElement);
            }
            return USR_USER_GROUPInfoLst;
        }
        public DataSet USR_USER_GROUPGetDS()
        {
            String TableName = "USR_USER_GROUP";
            DataSet DS = new DataSet();

            DS = this.GetDS(TableName);
            return DS;
        }
        #endregion

    }
}

这里有一些单元测试，演示了这一切如何工作…… 注意：测试 USR_USERInfoTableStructure 检查值对象定义是否与 Linq 表定义同步……我认为这是一种构建 DAL 的很棒的方法，因为成本为 0 且易于扩展……

TestDBFacadeTest.cs
===================
using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using DAL.TestDB;
using UTILS.TestDB;

using System.Data;
using System.Xml;

namespace DAL.TestDBTest
{
    /// 
    /// Summary description for TestDBFacadeTest
    /// 
    [TestClass]
    public class TestDBFacadeTest
    {
        private String _DatabaseServerName = "CHZUPREL227";
        private String _DatabaseName = "TestDB";
        public TestDBFacadeTest()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// 
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }


        [TestMethod]
        public void USR_USERCRUD()
        {
            String Message = "";
            DAL.TestDB.TestDBFacade iTestDBFacade = new DAL.TestDB.TestDBFacade(
                _DatabaseServerName, _DatabaseName);

            //Add a row
            USR_USERInfo.TableStructure USR_USERRow = new USR_USERInfo.TableStructure();
            //USR_USERRow.USER_ID = 1;//Index
            USR_USERRow.USER_NAME = "USER_NAME";
            USR_USERRow.EMAIL = "EMAIL";
            Assert.IsTrue(iTestDBFacade.USR_USERInsertRecord(USR_USERRow, ref Message));

            //Insert and read index
            int USER_ID = 0;
            USER_ID = iTestDBFacade.USR_USERInsertAndReadRecord(USR_USERRow, ref Message);
            Assert.IsTrue(USER_ID > 0);

            //Read a row
            USR_USERRow.USER_ID = USER_ID;
            Assert.IsTrue(iTestDBFacade.USR_USERGetRecord(ref USR_USERRow, ref Message));
            Assert.IsTrue(USR_USERRow.USER_NAME == "USER_NAME");

            //Edit a row
            USR_USERRow.USER_NAME = "USER_NAME2";
            Assert.IsTrue(iTestDBFacade.USR_USERUpdateRecord(USR_USERRow, ref Message));
            USR_USERRow.USER_NAME = "";
            Assert.IsTrue(iTestDBFacade.USR_USERGetRecord(ref USR_USERRow, ref Message));
            Assert.IsTrue(USR_USERRow.USER_NAME == "USER_NAME2");

            //List rows
            List USR_USERList;
            USR_USERList = iTestDBFacade.USR_USERList();
            Assert.IsTrue(USR_USERList.Count > 0);

            List USR_USERInfoList;
            USR_USERInfoList = iTestDBFacade.USR_USERInfoList();
            Assert.IsTrue(USR_USERInfoList.Count > 0);


            //Delete a row
            Assert.IsTrue(iTestDBFacade.USR_USERDeleteRecord(USR_USERRow.USER_ID,
                ref Message));


            DataSet DS = new DataSet();
            DS = iTestDBFacade.USR_USERGetDS();
            Assert.IsTrue(DS.Tables[0].Rows.Count > 0);
        }
        [TestMethod]
        public void USR_USERInfoTableStructure()
        {
            String Message = "";
            DAL.TestDB.TestDBFacade iTestDBFacade = new DAL.TestDB.TestDBFacade(
                _DatabaseServerName, _DatabaseName);

            //Check that all records in the TableStructure definition are present
            //in the Linq table definition
            USR_USERInfo.TableStructure USR_USERRow = new USR_USERInfo.TableStructure();
            USR_USER USR_USERLinq = new USR_USER();
            Assert.IsTrue(iTestDBFacade.CompareTableStructureToLinq(USR_USERRow,
                USR_USERLinq, ref Message));
            Assert.IsTrue(iTestDBFacade.CompareLinqToTableStructure(USR_USERLinq,
                USR_USERRow, ref Message));


        }
  
        [TestMethod]
        public void USR_USER_GROUPCRUD()
        {
            String Message = "";
            DAL.TestDB.TestDBFacade iTestDBFacade = new DAL.TestDB.TestDBFacade(
                _DatabaseServerName, _DatabaseName);

            //Add a row
            USR_USER_GROUPInfo.TableStructure USR_USER_GROUPRow =
                new USR_USER_GROUPInfo.TableStructure();
            USR_USER_GROUPRow.USER_GROUP = "USER_GROUP";
            Assert.IsTrue(iTestDBFacade.USR_USER_GROUPInsertRecord(USR_USER_GROUPRow,
                ref Message));

            //Insert and read index
            int USER_GROUP_ID = 0;
            USER_GROUP_ID = iTestDBFacade.USR_USER_GROUPInsertAndReadRecord(
                USR_USER_GROUPRow, ref Message);
            Assert.IsTrue(USER_GROUP_ID > 0);

            //Read a row
            USR_USER_GROUPRow.USER_GROUP_ID = USER_GROUP_ID;
            Assert.IsTrue(iTestDBFacade.USR_USER_GROUPGetRecord(ref USR_USER_GROUPRow,
                ref Message));
            Assert.IsTrue(USR_USER_GROUPRow.USER_GROUP == "USER_GROUP");

            //Edit a row
            USR_USER_GROUPRow.USER_GROUP = "USER_GROUP2";
            Assert.IsTrue(iTestDBFacade.USR_USER_GROUPUpdateRecord(USR_USER_GROUPRow,
                ref Message));
            USR_USER_GROUPRow.USER_GROUP = "";
            Assert.IsTrue(iTestDBFacade.USR_USER_GROUPGetRecord(ref USR_USER_GROUPRow,
                ref Message));
            Assert.IsTrue(USR_USER_GROUPRow.USER_GROUP == "USER_GROUP2");

            //List rows
            List USR_USER_GROUPList;
            USR_USER_GROUPList = iTestDBFacade.USR_USER_GROUPList();
            Assert.IsTrue(USR_USER_GROUPList.Count > 0);

            List USR_USER_GROUPInfoList;
            USR_USER_GROUPInfoList = iTestDBFacade.USR_USER_GROUPInfoList();
            Assert.IsTrue(USR_USER_GROUPInfoList.Count > 0);


            //Delete a row
            Assert.IsTrue(iTestDBFacade.USR_USER_GROUPDeleteRecord(
                USR_USER_GROUPRow.USER_GROUP_ID, ref Message));


            DataSet DS = new DataSet();
            DS = iTestDBFacade.USR_USER_GROUPGetDS();
            Assert.IsTrue(DS.Tables[0].Rows.Count > 0);
        }
        [TestMethod]
        public void USR_USER_GROUPInfoTableStructure()
        {
            String Message = "";
            DAL.TestDB.TestDBFacade iTestDBFacade = new DAL.TestDB.TestDBFacade(
                _DatabaseServerName, _DatabaseName);

            //Check that all records in the TableStructure definition are present
            //in the Linq table definition
            USR_USER_GROUPInfo.TableStructure USR_USER_GROUPRow =
                new USR_USER_GROUPInfo.TableStructure();
            USR_USER_GROUP USR_USER_GROUPLinq = new USR_USER_GROUP();
            Assert.IsTrue(iTestDBFacade.CompareTableStructureToLinq(USR_USER_GROUPRow,
                USR_USER_GROUPLinq, ref Message));
            Assert.IsTrue(iTestDBFacade.CompareLinqToTableStructure(USR_USER_GROUPLinq,
                USR_USER_GROUPRow, ref Message));


        }



        [TestMethod]
        public void Reflection()
        {
            String Message = "";
            TestDBFacade iTestDBFacade = new TestDBFacade(_DatabaseServerName,
                _DatabaseName);

            USR_USERInfo.TableStructure Input = new USR_USERInfo.TableStructure();
            Input.USER_NAME = "USER_NAME";
            Input.EMAIL = "EMAIL";

            USR_USERInfo.TableStructure Output = new USR_USERInfo.TableStructure();
            Output.USER_NAME = "USER_NAME2";
            Output.EMAIL = "EMAIL";


            Assert.IsTrue(iTestDBFacade.ConvertInfoToInfo((Object)Input, (Object)Output,
                ref Message));

            Assert.IsTrue(Input.USER_NAME == Output.USER_NAME);

        }

    }
}

业务逻辑层

我在这里放置应用程序的业务逻辑。在这种情况下，我没有业务逻辑，而是仅仅使用 UTILS.TestDB 中定义的值对象来暴露 DAL。

WPF UI 层

我的 WPF 层基于 http://www.galasoft-lb.ch/mydotnet/articles/article-2007041201.aspx 我强烈建议您阅读这篇文章，它写得非常好，并描述了如何包含设计时和测试数据。在本文中描述的示例中，我扩展了 Laurent Bugnion 的示例，以包含参数化数据提供程序和绑定对象列表。

与其重复 Laurent 的文章，不如看看我为处理对象列表所做的更改。每个 WPF 窗口都有一个数据提供程序，WPF 控件绑定到该数据提供程序。我们经常需要显示基于运行时才能知道的内容（例如外键）的数据。问题是 WPF 窗口的构造函数不允许参数。为了解决这个问题，我使用了一个 Singleton。这是一个使用静态变量的对象，只能实例化一次。这意味着它可以用来将参数（例如外键）传递给数据提供程序。

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

namespace UI.MyApplication
{
    public class Singleton
    {
        private static Singleton instance;
        public Singleton() { }

        public static Singleton Instance()
        {
            if (instance == null)
                instance = new Singleton();
            return instance;
        }

        private static String _DatabaseServerName = "Defualt";
        public String DatabaseServerName
        {
            get { return _DatabaseServerName; }
            set { _DatabaseServerName = value; }
        }

        private static String _DatabaseName = "Defualt";
        public String DatabaseName
        {
            get { return _DatabaseName; }
            set { _DatabaseName = value; }
        }

    }
}

我在我的 WPF 页面的构造函数中初始化了这个 Singleton。

      public WinMyApp()
        {
            Singleton t = Singleton.Instance();
            t.DatabaseName = "TestDB";
            t.DatabaseServerName = "CHZUPREL227";

            InitializeComponent();

        }

我在 WinMyAppDataProvider 类的 MakeData 过程中读取这些参数。这些参数在业务层的构造函数中使用。

                Singleton t = Singleton.Instance();
                String DatabaseName = t.DatabaseName;
                String DatabaseServerName = t.DatabaseServerName;
                BusinessLogicFacade iBusinessLogicFacade =
                    new BusinessLogicFacade(DatabaseServerName, DatabaseName);

列表中的对象定义在 Items 类中，该类继承自 DependancyObject。它包含 Laurent 文章中描述的 Dependancy 属性。此外，我存储了每个属性的原始值，以确定项目是否已更改。我第一次尝试这样做是在依赖属性的 setter 中设置一个 Edited 标志，但当属性从 UI 更改时，它不会被执行。

    public class Item : DependencyObject
    {

        #region USER_NAME
        private string _USER_NAMEOriginal;
        public string USER_NAME
        {
            get { return (string)GetValue(USER_NAMEProperty); }
            set
            {
                SetValue(USER_NAMEProperty, value);
            }
        }
        public static readonly DependencyProperty USER_NAMEProperty =
            DependencyProperty.Register("USER_NAME",
            typeof(string),
            typeof(Item),
            new UIPropertyMetadata(""));
        #endregion

…
 
        public Boolean EDITED()
        {
            if (_USER_NAMEOriginal == USER_NAME
                && _EMAILOriginal == EMAIL
                && _USER_IDOriginal == USER_ID
                && _USER_GROUP_IDOriginal == USER_GROUP_ID
                && _USER_TO_USER_GROUP_IDOriginal == USER_TO_USER_GROUP_ID) 
            {
                return false;
            }
            else
            {
                return true;
            }
        }


        public Item(String uSER_NAME, int uSER_ID, String eMAIL,
            int uSER_GROUP_ID, int uSER_TO_USER_GROUP_ID)
        {
            USER_NAME = uSER_NAME;
            USER_ID = uSER_ID;
            EMAIL = eMAIL;
            USER_GROUP_ID = uSER_GROUP_ID;
            USER_TO_USER_GROUP_ID = uSER_TO_USER_GROUP_ID;

            _USER_NAMEOriginal = USER_NAME;
            _USER_IDOriginal = USER_ID;
            _EMAILOriginal = EMAIL;
            _USER_GROUP_IDOriginal = USER_GROUP_ID;
            _USER_TO_USER_GROUP_IDOriginal = USER_TO_USER_GROUP_ID;

        }
    }

在数据提供程序中，我们创建一个可观察的对象列表。

        private ObservableCollection _myCollection  = new ObservableCollection();
        public ObservableCollection MyCollection
        {
            get
            {
                return _myCollection;
            }
        }

在 XAML 中，我们定义一个数据模板，其中包括绑定到数据提供程序的数据绑定。

当用户在列表框中的控件上进行更新时，会相应地在数据提供程序对象中进行更改。一旦用户准备好将数据持久化回数据库，他就会点击保存按钮，该按钮会调用数据提供程序内的 PeristChangesToDB 函数。

            String Message = "";
            WinMyAppDataProvider dataProvider =
                 TryFindResource("WinMyAppProvider") as WinMyAppDataProvider;
            if (  dataProvider.PersistChangesToDB(ref Message) == false)
            {
                MessageBox.Show("Error: Data not persisted successfully :" +Message);
            }
            else
            {
                MessageBox.Show("Data persisted successfully");
            };

在数据提供程序中，我们实例化我们的业务层，并遍历每个项目，检查是否需要更新。

      public Boolean PersistChangesToDB(ref string Message)
        {
            Singleton t = Singleton.Instance();
            String DatabaseName = t.DatabaseName;
            String DatabaseServerName = t.DatabaseServerName;
            BusinessLogicFacade iBusinessLogicFacade = new BusinessLogicFacade(
                DatabaseServerName, DatabaseName);
            
            foreach (Item e in _myCollection)
            {
                if (e.EDITED())
                {
                    // Update USR_USER
                    USR_USERInfo.TableStructure USR_USERRow =
                        new USR_USERInfo.TableStructure();
                    USR_USERRow.USER_ID = e.USER_ID;
                    if (iBusinessLogicFacade.USR_USERGetRecord(ref USR_USERRow,
                        ref Message) == true)
                    {
                        USR_USERRow.USER_NAME = e.USER_NAME;
                        if (iBusinessLogicFacade.USR_USERUpdateRecord(USR_USERRow,
                            ref Message) == false)
                        {
                            Message = "Unable to update USER_ID =" +
                                e.USER_ID.ToString() + " " + Message;
                            return false;
                        }
                    }
                    else
                    {
                        Message = "USER_ID =" + e.USER_ID.ToString() + " no longer exists";
                        return false;
                    }

…

                    
                }
            }
            Message = "";
            return true;
        }

结论

我希望这是一个构建可扩展应用程序的有用起点。我对任何改进的想法都很感兴趣。请给我您的评论。