N Tier development with Linq and WPF

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_USER> USR_USERList()
        {
                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext = new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString = LinqConnectionStr;
                List<USR_USER> 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_USERInfo.TableStructure> USR_USERInfoList()
        {
            String Message = "";
            List<USR_USERInfo.TableStructure> USR_USERInfoLst = new List<USR_USERInfo.TableStructure>();
            List<USR_USER> 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 List<USR_USERInfo.JoinedToUSR_USER_GROUP> JoinedToUSR_USER_GROUPList()
        {
            List<USR_USERInfo.JoinedToUSR_USER_GROUP> USR_USERInfoLst = new List<USR_USERInfo.JoinedToUSR_USER_GROUP>();

            TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext = new TestDB_UsrTablesDataContext();
            iTestDB_UsrTablesDataContext.Connection.ConnectionString = LinqConnectionStr;

            var test = (from iUSR_USER in iTestDB_UsrTablesDataContext.USR_USERs
                        from iUSR_USER_TO_USER_GROUP in iTestDB_UsrTablesDataContext.USR_USER_TO_USER_GROUPs
                        from iUSR_USER_GROUP in iTestDB_UsrTablesDataContext.USR_USER_GROUPs
                        where iUSR_USER.USER_ID == iUSR_USER_TO_USER_GROUP.USER_ID
                        where iUSR_USER_TO_USER_GROUP.USER_GROUP_ID == iUSR_USER_GROUP.USER_GROUP_ID
                        select new { iUSR_USER.USER_ID, iUSR_USER.USER_NAME, iUSR_USER.EMAIL, iUSR_USER_GROUP.USER_GROUP_ID, iUSR_USER_GROUP.USER_GROUP, iUSR_USER_TO_USER_GROUP.USER_TO_USER_GROUP_ID }).ToList();

            String Message = "";
            for (int i = 0; i < test.Count; i++)
            {
                USR_USERInfo.JoinedToUSR_USER_GROUP newElement = new USR_USERInfo.JoinedToUSR_USER_GROUP();
                if (ConvertLinqToTableStructure(test[i], 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_GROUP> USR_USER_GROUPList()
        {
            TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext = new TestDB_UsrTablesDataContext();
            iTestDB_UsrTablesDataContext.Connection.ConnectionString = LinqConnectionStr;
            List<USR_USER_GROUP> 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_GROUPInfo.TableStructure> USR_USER_GROUPInfoList()
        {
            String Message = "";
            List<USR_USER_GROUPInfo.TableStructure> USR_USER_GROUPInfoLst = new List<USR_USER_GROUPInfo.TableStructure>();
            List<USR_USER_GROUP> 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
        #region USR_USER_TO_USER_GROUP
        public Boolean USR_USER_TO_USER_GROUPInsertRecord(USR_USER_TO_USER_GROUPInfo.TableStructure USR_USER_TO_USER_GROUPRow, ref String Message)
        {
            try
            {
                USR_USER_TO_USER_GROUP USR_USER_TO_USER_GROUPLinq = new USR_USER_TO_USER_GROUP();
                if (ConvertTableStructureToLinq(USR_USER_TO_USER_GROUPRow, USR_USER_TO_USER_GROUPLinq, ref Message) == false) return false;

                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext = new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString = LinqConnectionStr;
                iTestDB_UsrTablesDataContext.USR_USER_TO_USER_GROUPs.InsertOnSubmit(USR_USER_TO_USER_GROUPLinq);
                iTestDB_UsrTablesDataContext.SubmitChanges();

            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return false;
            }
            return true;
        }
        public int USR_USER_TO_USER_GROUPInsertAndReadRecord(USR_USER_TO_USER_GROUPInfo.TableStructure USR_USER_TO_USER_GROUPRow, ref String Message)
        {
            try
            {
                USR_USER_TO_USER_GROUP USR_USER_TO_USER_GROUPLinq = new USR_USER_TO_USER_GROUP();
                if (ConvertTableStructureToLinq(USR_USER_TO_USER_GROUPRow, USR_USER_TO_USER_GROUPLinq, ref Message) == false) return -1;

                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext = new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString = LinqConnectionStr;
                iTestDB_UsrTablesDataContext.USR_USER_TO_USER_GROUPs.InsertOnSubmit(USR_USER_TO_USER_GROUPLinq);
                iTestDB_UsrTablesDataContext.SubmitChanges();
                return USR_USER_TO_USER_GROUPLinq.USER_TO_USER_GROUP_ID;
            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return -1;
            }
        }
        public Boolean USR_USER_TO_USER_GROUPGetRecord(ref USR_USER_TO_USER_GROUPInfo.TableStructure USR_USER_TO_USER_GROUPRow, ref String Message)
        {
            try
            {
                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext = new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString = LinqConnectionStr;
                USR_USER_TO_USER_GROUP USR_USER_TO_USER_GROUPLinq;
                int USER_TO_USER_GROUP_ID = USR_USER_TO_USER_GROUPRow.USER_TO_USER_GROUP_ID;
                USR_USER_TO_USER_GROUPLinq = (from Something
                                  in iTestDB_UsrTablesDataContext.USR_USER_TO_USER_GROUPs
                                              where Something.USER_TO_USER_GROUP_ID == USER_TO_USER_GROUP_ID
                                              select Something).Single();

                if (ConvertLinqToTableStructure(USR_USER_TO_USER_GROUPLinq, USR_USER_TO_USER_GROUPRow, ref Message) == false) return false;

                return true;
            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return false;
            }
        }
        public Boolean USR_USER_TO_USER_GROUPUpdateRecord(USR_USER_TO_USER_GROUPInfo.TableStructure USR_USER_TO_USER_GROUPRow, ref String Message)
        {
            try
            {
                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext = new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString = LinqConnectionStr;
                USR_USER_TO_USER_GROUP USR_USER_TO_USER_GROUPLinq;
                int USER_TO_USER_GROUP_ID = USR_USER_TO_USER_GROUPRow.USER_TO_USER_GROUP_ID;
                USR_USER_TO_USER_GROUPLinq = (from Something
                                  in iTestDB_UsrTablesDataContext.USR_USER_TO_USER_GROUPs
                                              where Something.USER_TO_USER_GROUP_ID == USER_TO_USER_GROUP_ID
                                              select Something).Single();

                if (ConvertTableStructureToLinq(USR_USER_TO_USER_GROUPRow, USR_USER_TO_USER_GROUPLinq, ref Message) == false) return false;

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

                return true;
            }
            catch (Exception ex)
            {
                Message = ex.Message;
                return false;
            }
        }
        public Boolean USR_USER_TO_USER_GROUPDeleteRecord(int USER_TO_USER_GROUP_ID, ref String Message)
        {
            try
            {
                TestDB_UsrTablesDataContext iTestDB_UsrTablesDataContext = new TestDB_UsrTablesDataContext();
                iTestDB_UsrTablesDataContext.Connection.ConnectionString = LinqConnectionStr;
                USR_USER_TO_USER_GROUP USR_USER_TO_USER_GROUPLinq;
                USR_USER_TO_USER_GROUPLinq = (from Something
                                  in iTestDB_UsrTablesDataContext.USR_USER_TO_USER_GROUPs
                                              where Something.USER_TO_USER_GROUP_ID == USER_TO_USER_GROUP_ID
                                              select Something).Single();


                iTestDB_UsrTablesDataContext.USR_USER_TO_USER_GROUPs.DeleteOnSubmit((from Something
                                                                         in iTestDB_UsrTablesDataContext.USR_USER_TO_USER_GROUPs
                                                                                     where Something.USER_TO_USER_GROUP_ID == USER_TO_USER_GROUP_ID
                                                                                     select Something).Single());

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

            USR_USER_TO_USER_GROUPList = (from Something
                            in iTestDB_UsrTablesDataContext.USR_USER_TO_USER_GROUPs
                                          where Something.USER_TO_USER_GROUP_ID > 0
                                          select Something).ToList();

            return USR_USER_TO_USER_GROUPList;
        }
        public List<USR_USER_TO_USER_GROUPInfo.TableStructure> USR_USER_TO_USER_GROUPInfoList()
        {
            String Message = "";
            List<USR_USER_TO_USER_GROUPInfo.TableStructure> USR_USER_TO_USER_GROUPInfoLst = new List<USR_USER_TO_USER_GROUPInfo.TableStructure>();
            List<USR_USER_TO_USER_GROUP> USR_USER_TO_USER_GROUPLst;
            USR_USER_TO_USER_GROUPLst = USR_USER_TO_USER_GROUPList();
            foreach (USR_USER_TO_USER_GROUP el in USR_USER_TO_USER_GROUPLst)
            {
                USR_USER_TO_USER_GROUPInfo.TableStructure newElement = new USR_USER_TO_USER_GROUPInfo.TableStructure();
                if (ConvertLinqToTableStructure(el, newElement, ref Message) == false) return null;
                USR_USER_TO_USER_GROUPInfoLst.Add(newElement);
            }
            return USR_USER_TO_USER_GROUPInfoLst;
        }
        public DataSet USR_USER_TO_USER_GROUPGetDS()
        {
            String TableName = "USR_USER_TO_USER_GROUP";
            DataSet DS = new DataSet();

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