(untagged)

N Tier development with Linq and WPF

Nigel-Findlater

0.00/5 (No votes)

11 Nov 2008

N Tier development with Linq and WPF

Download LinqDataAccess - 311.08 KB

Introduction

In this article I take a thin slice of functionality through a transactional database application using WPF as the UI and Linq as the DAL. I want to follow the path of data starting from the data access layer DAL through the business logic layer and then into the WPF UI.

N Tier Architecture

I like to encapsulate functionality into layers in my application. In this way I can isolate application modules from each other thus minimising the impact of changes. It also helps structure the application in a way that is easier to maintain and further develop enabling test driven development at various levels of abstraction. The result is a scalable architecture which can be adapted to new technologies by simply modifying the appropriate subproject in the appropriate tier. It also makes life easier when working in a team of developers helping to avoid conflicting updates. I have prefixed the sub projects with:

DAL = Data Access Layer
DOM = Domain Layer
UI = User Interface
UTILS = Utilities accessible across all layers

Linq Data Access Layer

The main reason why I avoid third party DALs is that there is no automatic upgrade path other than buying the next release and instead of becoming familiar with the built in Microsoft functions that are available every the developer learns a third party interface which may or may not be available. Although my old DAL works in .Net 1.0 to 3.5 and that Linq only works in 3.5 and higher I don't think I will be coding any .Net 2.0 code going forward and that therefore I can enjoy the simplifications that this new technology brings....

As in my previous VB DAL http://www.codeproject.com/KB/database/NigelDBTable.aspx I define value objects in an assembly that can be included throughout the solution. These reflect one to one what is in the database tables. Having them in a separate assembly makes it possible to separate the DAL from the UI with a business layer. Here are 2 examples that correspond to tables in a database

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
        }

    }
}

NB This approach is not compatible with the VB value objects defined in the following way. The reason is that the function that converts the Linq data object to this data object is able to assign all fields but on returning some fields are not returned. The strange thing is that some fields work but others do not. The VB structure is not the same as a public class, consequently something odd is done with the memory management of these objects that gives the ConvertLinqToTableStructure a problem when returning the values as an object.

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

I made a project that contains all the Linq code. I created some Linq to SQL diagrams and dragged and dropped all the tables from the database. To do this Add a new Linq to SQL class to the DAL subproject Then from the server navigator browse to the tables you want to import and drag them onto newly created Linq to SQL class.

I found it's a good idea to group tables together otherwise the screen becomes a bit full. I also found it's not possible to drag the same table into 2 diagrams within the same project. When the database is changed it is necessary to delete the corresponding tables effected, refresh the server explorer and re drag and drop the tables into the corresponding Linq to SQL diagram. I used reflection to automate the conversion of data from value objects that are accessible across the whole solution to Linq table objects that are only accessible in the DAL. This is done in DBTable. Since not everything is easily made in Linq I have include some good old OLEDB stuff here to...

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


    }
}

This is the Facade that is used to expose database functions to the business layer (not the UI). This inherits from DBTable and will have the tendency of becoming quite fat.

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

    }
}

Here are some unit tests that demonstrate how this all works... NB The test USR_USERInfoTableStructure checks whether value object definitions are in synch with the Linq table definitions... I think this is quite a cool way to build a DAL because the cost = 0 and is easily expandable...

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);

        }

    }
}

Business Logic Layer

This is where I would put the business logic of the application. In this case I have no business logic instead I am just exposing the DAL using value objects defined in UTILS.TestDB.

WPF UI Layer

I have based my WPF layer on http://www.galasoft-lb.ch/mydotnet/articles/article-2007041201.aspx I really recommend that you read this article, its very well written and describes how to include design time and test data. In the example described here I have extended Laurent Bugnion’s example to include a parameterized data provider and a bound list of objects.

Rather than repeating Laurent’s article lets look at what I changed to make this work for lists of objects. Each WPF window has a dataprovider to which wpf controls are bound. Very often we need to display data based on something we only know at run time such as a foreign key. The problem is that the constructor of WPF windows does not allow parameters. To get over this I use a Singleton. This is an object that uses static variables that can only be instantiated 1 time. This means it can be used to pass parameters such as foreign keys to the dataprovider.

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; }
        }

    }
}

I initialize this singleton in the constructor of my wpf page

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

            InitializeComponent();

        }

The parameters I read in the MakeData procedure in the WinMyAppDataProvider class. These parameters are the consumed in the constructor of the business tier.

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

The objects within the list are defined in the Items class that inherits from DependancyObject. This contains Dependancy properties as described in Laurent’s article. In addition I store the original value of each property in order to determine if an item has changed. My first attempt at doing this was to set an Edited flag in the setter of the dependency property, unfortunately this is not executed when the property is changed from the 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;

        }
    }

Within the dataprovider we make an observable list of items

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

Within the xaml we define a data template that includes data binding to the data provider

When the user makes an update to a control in the list box the corresponding change is made in the dataprovider object. Once the user is ready to persist the data back to the database he clicks save button which invokes the PeristChangesToDB function within the data provider:

            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");
            };

Within the data provider we instanciate our Business Tier and iterate through each item checking if an update is needed.

      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;
        }

Conclusion

I hope this is a useful starting point to build scalable applications. I am interested in any ideas how to improve this. Please send me your comments.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here