注册 登录
Small Basic中文站-快乐编程 返回首页

akyao的个人空间 http://www.smallbasic.cn/?4 [收藏] [复制] [分享] [RSS]

日志

Linq操作数据库的通用类

已有 2753 次阅读2010-7-5 15:48 |个人分类:技术交流|

Linq操作数据库的通用类

1. 建一个project 命名为DLinq ,添加一个Linq To SQL 的数据源,这里以经典的Northwind数据库为例,命名为NWDB.dbml 。

2. 建另一个Project 为DAL层 ,添加一个Table工厂, 这样我们就可以通过实体来获得Table

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

namespace DAL
{
    public static  class TableFactory
    { 
        public static System.Data.Linq.Table<T> CreateTable<T>() where T : class
        {
            return Database.NWDB.GetTable<T>();
        }
    }
}
 

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

namespace DAL
{
    public   static class Database
    {
        private static DLinq.NWDBDataContext _NWDB = null;

        public static DLinq.NWDBDataContext NWDB
        {
            get
            {
                if (_NWDB == null)
                    _NWDB = new DLinq.NWDBDataContext();
                return _NWDB;
            }
        }
       
    }
}
    3. 借助Linq的特性,现在就可以写通用的数据库操作类了

 

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

namespace DAL
{
    public class Utility
    {
        public static void Insert<T>(T TEntity) where T : class
        {
            var table = TableFactory.CreateTable<T>();
            table.InsertOnSubmit(TEntity);
        }

        public static IEnumerable<T> Where<T>(Func<T, bool> predicate) where T : class
        {
            var table = TableFactory.CreateTable<T>();
            return table.Where(predicate).AsEnumerable();
        }

        public static void SubmitChanges()
        {
            Database.NWDB.SubmitChanges();
        }
    }
}

    4. 现在让我们来写个测试方法来测试一下是否成功

 

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

namespace DALTest
{
    class Program
    {
        static void Main(string[] args)
        {
            InsertTest();
            WhereTest();
            Console.WriteLine("All testings are success!");
            Console.Read();
        }

        private static void InsertTest()
        {
            Customer _customer=new Customer{ 
                CustomerID="Bruce",
                 ContactName="Lee",
                 CompanyName ="CodingSky",
                 City ="Shenzhen"};
            Utility.Insert(_customer);
            Utility.SubmitChanges();
        }

        private static void WhereTest()
        {
            var _result= Utility.Where<Customer>(c => c.CustomerID == "Bruce");
            var _list = _result.ToList();
            if (_list.Count == 0)
            {
                Console.WriteLine("No result!");
                return;
            }
            Console.WriteLine("Query result is:");
            _list.ForEach(c => Console.WriteLine(Toolkits.StringExtension.ToString(c)));
        }
        
    }
}
    5. 其中WhereTest调用了另一个Project的StringExtension类,这个类主要扩展了ToString方法,通过Reflection 来读取实例的所有属性以及它们的值。

 

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

namespace Toolkits
{
    public class StringExtension
    {
        public static string ToString<T>(T t) where T:class 
        {
            var typeInfo = BLReflection.GetProperties(typeof(T));

            var rType = (from q in typeInfo select new 
            {
                           TypeName=  q.PropertyType.Name,
                           PropName= q.Name ,
                           Value= q.GetValue(t, null)
            }).ToList();
            
            StringBuilder sb = new StringBuilder();
            string header="Class Name: {0}\n";
            sb.AppendFormat(header , typeof(T).Name);
            rType.ForEach(c => sb.Append(String.Format ("\t{0}: {1} ({2}),\n", c.PropName, c.Value,c.TypeName) ));
            string result=sb.ToString ();
            return (result.Length > header.Length ? result.Substring(0, result.Length - 2)+"\n" : header);           
        }
    }
}
    6. 最后,输出的结果应该是这样:

 

Query result is:
Class Name: Customer
        CustomerID: Bruce (String),
        CompanyName: CodingSky (String),
        ContactName: Lee (String),
        ContactTitle:  (String),
        Address:  (String),
        City: Shenzhen (String),
        Region:  (String),
        PostalCode:  (String),
        Country:  (String),
        Phone:  (String),
        Fax:  (String),
        Orders: System.Data.Linq.EntitySet`1[DLinq.Order] (EntitySet`1)

 

All testings are success!

================================================================

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

namespace DAL
{
    public class Utility
    {
        public static void Insert<T>(T TEntity) where T : class
        {
            var table = TableFactory.CreateTable<T>();
            table.InsertOnSubmit(TEntity);
        }

        public static void Delete<T>(T TEntity) where T : class
        {
            var table = TableFactory.CreateTable<T>();
            table.DeleteOnSubmit(TEntity);
        }

        public static void Delete<T>(Func<T, bool> predicate) where T : class
        {
            var table = TableFactory.CreateTable<T>();
            var dResult = Where<T>(predicate);
            table.DeleteAllOnSubmit(dResult );
        }

        public static void Update<T>(T TEntity, Action<T> action)
        {
            action(TEntity);
            SubmitChanges();
        }

        public static void InsertAll<T>(IEnumerable <T> TEntities) where T : class
        {
            var table = TableFactory.CreateTable<T>();
            table.InsertAllOnSubmit( TEntities);
        }

        public static void DeleteAll<T>(IEnumerable<T> TEntities) where T : class
        {
            var table = TableFactory.CreateTable<T>();
            table.DeleteAllOnSubmit(TEntities);
        }

        public static IEnumerable<T> SelectAll<T>() where T : class
        {
            var table = TableFactory.CreateTable<T>();
            return table.Select(c => c).AsEnumerable();
        }

        public static IEnumerable<T> Where<T>(Func<T, bool> predicate) where T : class
        {
            var table = TableFactory.CreateTable<T>();
            return table.Where(predicate).AsEnumerable();
        }

        public static void SubmitChanges()
        {
            Database.NWDB.SubmitChanges();
        }
    }
}

 

    同样的, 我们也是写一些Test 方法来验证一下是否正确

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using DLinq;
using Toolkits;

namespace DALTest
{
    class Program
    {
        static void Main(string[] args)
        {
            //SelectAllTest(); 
            InsertTest();
            WhereTest();
            UpdateTest();
            WhereTest();
            DeleteTest1();
            WhereTest();
            Console.WriteLine("All testings are success!");
            Console.Read();
        }

        private static void InsertTest()
        {
            Customer _customer=new Customer{ 
                CustomerID="Bruce",
                 ContactName="Lee",
                 CompanyName ="CodingSky",
                 City ="Shenzhen"};
            Utility.Insert(_customer);
            Utility.SubmitChanges();
        }

        private static void DeleteTest1()
        {
            Utility.Delete<Customer>(c => c.CustomerID == "Bruce");
            Utility.SubmitChanges();
        }

        private static void DeleteTest2()
        {
            var _result= Utility.Where<Customer>(c => c.CustomerID == "Bruce");
            if (_result.Count() > 0)
            {
                Utility.Delete(_result.First());
                Utility.SubmitChanges();
            }
        }

        private static void UpdateTest()
        {
            var _result= Utility.Where<Customer>(c => c.CustomerID == "Bruce");
            if (_result.Count() > 0)
            {
                var _customer = _result.First();
                if (_customer != null)
                {
                    Utility.Update(_customer, c =>
                    {
                        c.ContactName = "Jack";
                        c.CompanyName = "Microsoft";
                        c.City = "Beijing";
                    });
                    Utility.SubmitChanges();
                }
            }
        }

        private static void SelectAllTest()
        {
            var _result = Utility.SelectAll<Customer>();
            var _list = _result.ToList();
            if (_list.Count == 0)
            {
                Console.WriteLine("No result!");
                return;
            }
            _list.ForEach(c => Console.WriteLine(StringExtension.ToString(c)));
        }

        private static void WhereTest()
        {
            var _result= Utility.Where<Customer>(c => c.CustomerID == "Bruce");
            var _list = _result.ToList();
            if (_list.Count == 0)
            {
                Console.WriteLine("No result!");
                return;
            }
            Console.WriteLine("Query result is:");
            _list.ForEach(c => Console.WriteLine(StringExtension.ToString(c)));
        }
        
    }
}

    其他代码(例如TableFactory,StringExtension)请参考上一篇 :http://blog.csdn.net/fengart/archive/2008/08/19/2798534.aspx

    以上的Test是先在Customers表中Insert一个叫Bruce的家伙,接着把他的ContactName修改为Jack ,最后Delete他。

    (其中Customers表的数据太多,所以我把SelectAllTest方法注释掉了)

    最后打印的结果应该是这样:

Query result is:
Class Name: Customer
        CustomerID: Bruce (String),
        CompanyName: CodingSky (String),
        ContactName: Lee (String),
        ContactTitle:  (String),
        Address:  (String),
        City: Shenzhen (String),
        Region:  (String),
        PostalCode:  (String),
        Country:  (String),
        Phone:  (String),
        Fax:  (String),
        Orders: System.Data.Linq.EntitySet`1[DLinq.Order] (EntitySet`1)

Query result is:
Class Name: Customer
        CustomerID: Bruce (String),
        CompanyName: Microsoft (String),
        ContactName: Jack (String),
        ContactTitle:  (String),
        Address:  (String),
        City: Beijing (String),
        Region:  (String),
        PostalCode:  (String),
        Country:  (String),
        Phone:  (String),
        Fax:  (String),
        Orders: System.Data.Linq.EntitySet`1[DLinq.Order] (EntitySet`1)

No result!
All testings are success!


路过

鸡蛋

鲜花

握手

雷人

评论 (0 个评论)

facelist

您需要登录后才可以评论 登录 | 注册

QQ|手机版|Small Basic中文站-快乐编程 ( 闽ICP备09051788号 |

GMT+8, 2019-8-22 12:25 , Processed in 0.122512 second(s), 7 queries , File On.

Powered by Discuz! X3.3

返回顶部