ZLPlugin_LisPacs_MR_NEW/ZLPlugin_LisPacs_MR/Domain/Units/DbHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using ADODB;
using System.Reflection;
namespace ZLPlugin_LisPacs_MR.Domain.Units
{
    class DbHelper
    {
        /// <summary>
        /// 执行SQL，返回DataTable对象
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="isThrowNullQuery">数据为空是否抛异常</param>
        /// <returns></returns>
        public static DataTable ExcuteSql(string sql, Connection con, bool isThrowNullQuery = true)
        {
            Recordset recordset = new Recordset();
            recordset.Open(sql, con);

            if (recordset.BOF || recordset.EOF)
                if (isThrowNullQuery)
                    throw new ArgumentException("查询无数据");
                else return null;

            recordset.MoveFirst();
            DataTable dt = new DataTable();
            for (int i = 0; i < recordset.Fields.Count; i++)
                dt.Columns.Add(recordset.Fields[i].Name, typeof(string));

            while (!recordset.EOF)
            {
                var row = dt.NewRow();
                for (int i = 0; i < recordset.Fields.Count; i++)
                    row[i] = recordset.Fields[i].Value.ToString();
                recordset.MoveNext();
                dt.Rows.Add(row);
            }

            return dt;
        }

        /// <summary>
        /// 执行SQL，返回实体对象
        /// </summary>
        /// <typeparam name="T">对象实体</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="con">数据库连接对象</param>
        /// <returns></returns>
        public static List<T> ExcuteSql<T>(string sql, Connection con, params string[] pars) where T : new()
        {
            Recordset recordset = new Recordset();
            for (int i = 0; i < pars.Length; i++)
                sql = sql.Replace(":" + i, pars[i]);
            recordset.Open(sql, con);

            if (recordset.BOF || recordset.EOF)
                return null;

            recordset.MoveFirst();
            var revals = new List<T>();

            while (!recordset.EOF)
            {
                var row = new T();
                foreach (PropertyInfo prop in row.GetType().GetProperties())
                {
                    try
                    {
                        if (recordset.Fields[prop.Name] != null)
                            prop.SetValue(row, recordset.Fields[prop.Name].Value, null);
                    }
                    catch { continue; }
                }
                revals.Add(row);
                recordset.MoveNext();
            }
            recordset.Close();
            return revals;
        }

        /// <summary>
        /// 获取单个结果
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="con">数据库连接对象</param>
        /// <param name="pars">sql参数，sql语句中以 :0 开始进行参数替换</param>
        /// <returns></returns>
        public static object GetSingleValue(string sql, ADODB.Connection con, params string[] pars)
        {
            Recordset recordset = new Recordset();
            for (int i = 0; i < pars.Length; i++)
                sql = sql.Replace(":" + i, pars[i]);
            recordset.Open(sql, con);

            if (recordset.BOF || recordset.EOF)
                return null;

            recordset.MoveFirst();
            return recordset.Fields[0].Value;
        }
    }
}