spring.net +dapper 打造简易的DataAccess 工具类. 

  1 public class DBUtil
  2     {
  3         /// <summary>
  4         /// 数据库连接字符串
  5         /// </summary>
  6         private static string DataBase_Connection = System.Configuration.ConfigurationManager.ConnectionStrings["DNT"].ToString();
  7 
  8         /// <summary>
  9         /// 脚本文件存放路径
 10         /// </summary>
 11         private static string Data_File_Path = System.Configuration.ConfigurationManager.AppSettings["Data_File_Path"].ToString();
 12 
 13         /// <summary>
 14         /// 全局配置文件列表
 15         /// </summary>
 16         public static Dictionary<string, DataConfig> DataConfigList;
 17 
 18         /// <summary>
 19         /// 加载配置文件
 20         /// </summary>
 21         public static void InitConfig()
 22         {
 23             var filePath =  Path.Combine(AppDomain.CurrentDomain.BaseDirectory,Data_File_Path);
 24 
 25             DirectoryInfo TheFolder = new DirectoryInfo(filePath);
 26             foreach (FileInfo NextFile in TheFolder.GetFiles().Where(f=>f.Extension == ".config"))
 27             {
 28                 Spring.Context.Support.XmlApplicationContext context = new Spring.Context.Support.XmlApplicationContext(NextFile.FullName);
 29                 string[] fileNames = context.ObjectFactory.GetObjectDefinitionNames();
 30 
 31                 foreach (var fileName in fileNames)
 32                 {
 33                     var mObj = context.GetObject(fileName);
 34 
 35                     if (DataConfigList == null)
 36                     {
 37                         DataConfigList = new Dictionary<string, DataConfig>();
 38                     }
 39 
 40                     string keyName = NextFile.Name.Split('.')[0].ToString() + "." + fileName;
 41 
 42                     if (!DataConfigList.ContainsKey(keyName))
 43                     {
 44                         DataConfigList.Add(keyName, mObj as DataConfig);
 45                     }
 46                     else
 47                     {
 48                         throw new ApplicationException("The existence of multiple identical configuration node,plseae check!");
 49                     }
 50                 }
 51             }
 52             
 53         }
 54 
 55         /// <summary>
 56         /// 获取指定名称SQL
 57         /// </summary>
 58         /// <param name="commandName"></param>
 59         /// <returns></returns>
 60         public static string GetSql(string commandName)
 61         {
 62             InitConfig();
 63 
 64             if (!DataConfigList.ContainsKey(commandName))
 65             {
 66                 throw new ApplicationException("Data file can't be found!");
 67             }
 68             else
 69             {
 70                 return DataConfigList[commandName].commandText;
 71             }
 72 
 73         }
 74 
 75         /// <summary>
 76         /// 获取数据库连接
 77         /// </summary>
 78         /// <returns></returns>
 79         public static SqlConnection GetConnection()
 80         {
 81             SqlConnection connection = new SqlConnection(DataBase_Connection);
 82             connection.Open();
 83 
 84             return connection;
 85         }
 86 
 87         /// <summary>
 88         /// 获取Command
 89         /// </summary>
 90         /// <param name="commandName"></param>
 91         /// <returns></returns>
 92         public static IDbCommand CreateCommand(string commandName)
 93         {
 94             var connection = GetConnection();
 95 
 96             using (IDbCommand command = connection.CreateCommand())
 97             {
 98                 command.CommandText = GetSql(commandName);
 99                 return command;
100             }
101         }
102 
103         /// <summary>
104         /// 执行查询动作
105         /// </summary>
106         /// <typeparam name="T"></typeparam>
107         /// <param name="command"></param>
108         /// <param name="pars"></param>
109         /// <returns></returns>
110         public static List<T> Query<T>(string commandName, dynamic pars)
111         {
112             using (IDbCommand command = DBUtil.CreateCommand(commandName))
113             {
114                 return SqlMapper.Query<T>(command.Connection, command.CommandText, pars);
115             }
116         }
117     }

SQL配置文件管理

  • 支持多文件 (Order.config,  Item.config....), DBUtil 会在调用时加配置文件加入内存中缓存，待下次时候，直接读取内存数据.
 1 <?xml version="1.0" encoding="utf-8" ?>
 2 <objects xmlns="http://www.springframework.net"
 3     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 4     xsi:schemaLocation="http://www.springframework.net
 5         http://www.springframework.net/xsd/spring-objects.xsd">
 6   <object id="GetOrderList" type="SpringDemo.DataConfig,SpringDemo">
 7     <property name="commandText">
 8       <value>
 9         <![CDATA[
10            SELECT TOP(@Size) * FROM dbo.Order
11          ]]>
12       </value>
13     </property>
14   </object>
15 
16 <object id="GetOrderDetail" type="SpringDemo.DataConfig,SpringDemo">
17     <property name="commandText">
18       <value>
19         <![CDATA[
20            SELECT TOP(@Size) * FROM dbo.OrderDetail WHERE OrderID = @OrderID
21          ]]>
22       </value>
23     </property>
24   </object>
25 </objects>

 

Demo代码

class Program
    {
        static void Main(string[] args)
        {
            List<Order> OrderList = GetOrderList(1000);

            OrderList.ForEach(f =>
            {
                Console.WriteLine(f.OrderNumber);
            });

            Console.ReadLine();
        }

        public static List<Order> GetOrderLIST(int size)
        {
            return DBUtil.Query<Order>("Order.GetOrderList", new { Size = size }).ToList();
        }

       public static List<OrderDetail> GetOrderDetail(int size,int orderId)
        {
            return DBUtil.Query<OrderDetail>("Order.GetOrderDetail", new { Size = size,OrderID = orderId  }).ToList();
        }
}

 

posted @ 2014-06-17 11:13  darjuan  阅读(890)  评论(0)  编辑  收藏  举报