超简DbHelper

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Dynamic;

namespace ConsoleApplication1 {
    public class QuickDB {
        static void Main() {
            var sql = "select * from Categories";
            var connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Northwind;";
            var list = new QuickDB(connectionString).ExecuteReader(sql);
            for (int i = 0; i < list.Length; i++) {
                var item = list[i];
                Console.WriteLine(item.CategoryName);
            }
            Console.ReadLine();
        }

        private string connectionString;
        public QuickDB(string connectionString) {
            this.connectionString = connectionString;
        }

        private void command(string sql, Action<SqlCommand> act) {
            var c = new SqlConnection(connectionString);
            c.Open();
            var comm = c.CreateCommand();
            comm.CommandText = sql;
            act(comm);
            c.Close();
        }

        private T command<T>(string sql, Func<SqlCommand, T> func) {
            T t = default(T);
            command(sql, c => { t = func(c); });
            return t;
        }

        public int ExecuteNonQuery(string sql) {
            return command(sql, c => {
                return c.ExecuteNonQuery();
            });
        }

        public object ExecuteScalar(string sql) {
            var r = ExecuteReader(sql);
            return r.Length == 0 ? null : r[0][0];
        }

        public dynamic[] ExecuteReader(string sql) {
            return command(sql, c => {
                var q = c.ExecuteReader();
                var r = new List<dynamic>();//will return this.
                var ns = new List<string>();//column names
                while (q.Read()) {
                    if (ns.Count == 0) {
                        for (int i = 0; i < q.FieldCount; i++) {
                            ns.Add(q.GetName(i));
                        }
                        ns.Sort();
                    }
                    IDictionary<string, object> obj = new ExpandoObject();//same to javascript object
                    foreach (var n in ns) {
                        obj.Add(n, q[n]);
                    }
                    r.Add(obj);
                }
                q.Close();
                return r.ToArray();
            });
        }
    }
}

 

posted @ 2017-01-12 17:41  大胡子毛绒老头  阅读(193)  评论(0编辑  收藏  举报