SqlHelper和OracleHelper

最新推荐文章于 2024-08-04 02:36:47 发布

阅读量6.7k

点赞数

CC 4.0 BY-SA版权

分类专栏： .Net 文章标签： SqlHelper OracleHelper

本文链接：https://blog.youkuaiyun.com/jingjingwin5/article/details/10115409

.Net 专栏收录该内容

35 篇文章

订阅专栏

本文介绍了一套针对SQL Server和Oracle数据库的操作辅助类，包括连接管理、执行SQL语句、存储过程调用及结果集处理等功能。这些帮助类简化了数据库交互流程，并提供了异常处理机制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前几天结合了自己的想法和别人的帮助类，重写了一份自己觉得还可以的帮助类，相比宠物商店的而言，我觉得要好一些，但是自己入行年数太少，经验不足也有很大可能是我很多东西没有考虑到。无论如何，这是我第二份记录的“帮助类”代码。

SqlHelper:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;

namespace DAL
{
    public class SqlHelper
    {
        //Database connection strings
        public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["CR_sqlStr"].ConnectionString;


        //封装连接字符串属性
        private static SqlConnection _con=null;

        public static SqlConnection Con
        {
            get {
                if (SqlHelper._con==null)
                {
                    SqlHelper._con = new SqlConnection();
                }
                if (SqlHelper._con.ConnectionString=="")
                {
                    SqlHelper._con.ConnectionString = SqlHelper.ConnectionString;
                }
                return SqlHelper._con; }
            set { SqlHelper._con = value; }
        }
        #region ExecuteScalar
        /// <summary>
        /// 执行Sql 语句
        /// </summary>
        /// <param name="commandText">Sql/或存储过程</param>
        /// <param name="commandType">类型</param>
        /// <param name="param">参数</param>
        /// <returns>执行结果对象</returns>
        public static int ExecuteScalar(string commandText, CommandType commandType, params SqlParameter[] param)
        {
            int count = 0;
            using (SqlHelper.Con)
            {
                using (SqlCommand cmd = new SqlCommand(commandText, SqlHelper.Con))
                {
                    try
                    {
                        cmd.CommandType = commandType;
                        if (param != null)
                        {
                            cmd.Parameters.AddRange(param);
                        }
                        SqlHelper.Con.Open();
                        count = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                    catch (Exception)
                    {
                        count = 0;
                        throw;
                    }
                }
            }
            return count;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 执行sql命令
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText">sql语句/参数化sql语句/存储过程名</param>
        /// <param name="commandParameters"></param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string commandText, CommandType commandType, params SqlParameter[] param)
        {
            int result = 0;
            using (SqlHelper.Con)
            {
                using (SqlCommand cmd = new SqlCommand(commandText, SqlHelper.Con))
                {
                    try
                    {
                        cmd.CommandType = commandType;
                        if (param != null)
                        {
                            cmd.Parameters.AddRange(param);
                        }
                        SqlHelper.Con.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        result = 0;
                    }
                }
            }
            return result;
        }
        #endregion

        #region ExecuteEntity

        /// <summary>
        /// 执行返回一条记录的泛型对象
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="commandText">语句或存储过程名</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="param">参数数组</param>
        /// <returns>实体对象</returns>
        public static T ExecuteEntity<T>(string commandText, CommandType commandType, params SqlParameter[] param)
        {
            T obj = default(T);
            using (SqlHelper.Con)
            {
                using (SqlCommand cmd = new SqlCommand(commandText, SqlHelper.Con))
                {
                    cmd.CommandType = commandType;
                    cmd.Parameters.AddRange(param);
                    SqlHelper.Con.Open();
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        obj = SqlHelper.ExecuteDataReader<T>(reader);
                    }
                }
            }
            return obj;
        }
        #endregion

        #region ExecuteDataReader
        /// <summary>
        /// 执行返回一条记录的泛型对象
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="reader">只进只读对象</param>
        /// <returns>泛型对象</returns>
        private static T ExecuteDataReader<T>(IDataReader reader)
        {
            T obj = default(T);
            try
            {
                Type type = typeof(T);
                obj = (T)Activator.CreateInstance(type);//从当前程序集里面通过反射的方式创建指定类型的对象   
                //obj = (T)Assembly.Load(OracleHelper._assemblyName).CreateInstance(OracleHelper._assemblyName + "." + type.Name);//从另一个程序集里面通过反射的方式创建指定类型的对象 
                PropertyInfo[] propertyInfos = type.GetProperties();//获取指定类型里面的所有属性
                foreach (PropertyInfo propertyInfo in propertyInfos)
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string fieldName = reader.GetName(i);
                        if (fieldName.ToLower() == propertyInfo.Name.ToLower())
                        {
                            object val = reader[propertyInfo.Name];//读取表中某一条记录里面的某一列
                            if (val != null && val != DBNull.Value)
                            {
                                if (val.GetType() == typeof(decimal) || val.GetType() == typeof(int))
                                {
                                    propertyInfo.SetValue(obj, Convert.ToInt32(val), null);
                                }
                                else if (val.GetType() == typeof(DateTime))
                                {
                                    propertyInfo.SetValue(obj, Convert.ToDateTime(val), null);
                                }
                                else if (val.GetType() == typeof(string))
                                {
                                    propertyInfo.SetValue(obj, Convert.ToString(val), null);
                                }
                            }
                            break;
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return obj;
        }
        #endregion

        #region ExecuteList
        /// <summary>
        /// 执行返回多条记录的泛型集合对象
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="commandText">语句或存储过程名</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="param">命令参数数组</param>
        /// <returns>泛型集合对象</returns>
        public static List<T> ExecuteList<T>(string commandText, CommandType commandType, params SqlParameter[] param)
        {
            List<T> list = new List<T>();
            using (SqlHelper.Con)
            {
                using (SqlCommand cmd = new SqlCommand(commandText, SqlHelper.Con))
                {
                    try
                    {
                        cmd.CommandType = commandType;
                        if (param != null)
                        {
                            cmd.Parameters.AddRange(param);
                        }
                        SqlHelper.Con.Open();
                        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                        {
                            T obj = SqlHelper.ExecuteDataReader<T>(reader);
                            list.Add(obj);
                        }
                    }
                    catch (Exception)
                    {
                        list = null;
                    }
                }
            }
            return list;
        }
        #endregion

        #region ExecuteDataSet
        /// <summary>
        /// 执行返回多条记录的dataset
        /// </summary>
        /// <param name="commandText">sql或存储过程</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="param">参数数组</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataSet(string commandText,CommandType commandType, params SqlParameter[] param)
        {
            using (SqlHelper.Con)
            {
                using (SqlCommand cmd = new SqlCommand(commandText, SqlHelper.Con))
                {
                    cmd.CommandType = commandType;
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        return ds;
                    }
                }
            }
        }
        #endregion
    }
}

OracleHelper:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Configuration;
using System.Data;
using System.Reflection;
using System.Web.Script.Serialization;



namespace DAL
{
    public class OracleHelper
    {
        #region 变量

        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private static OracleConnection _con = null;
        public static string constr = ConfigurationManager.ConnectionStrings["CR_oracleStr"].ToString();
        #endregion


        #region 属性

        /// <summary>

        /// 获取或设置数据库连接对象

        /// </summary>

        public static OracleConnection Con
        {

            get
            {

                if (OracleHelper._con == null)
                {

                    OracleHelper._con = new OracleConnection();

                }

                if (OracleHelper._con.ConnectionString == "")
                {

                    OracleHelper._con.ConnectionString = OracleHelper.constr;

                }

                return OracleHelper._con;

            }
            set
            {
                OracleHelper._con = value;
            }
        }
        #endregion

        #region 方法

        #region 执行返回一行一列的数据库操作

        /// <summary>

        /// 执行返回一行一列的数据库操作

        /// </summary>

        /// <param name="commandText">Oracle语句或存储过程名</param>

        /// <param name="commandType">Oracle命令类型</param>

        /// <param name="param">Oracle命令参数数组</param>

        /// <returns>第一行第一列的记录</returns>

        public static int ExecuteScalar(string commandText, CommandType commandType, params OracleParameter[] param)
        {

            int count = 0;

            using (OracleHelper.Con)
            {

                using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
                {

                    try
                    {

                        cmd.CommandType = commandType;
                        if (param!=null)
                        {
                            cmd.Parameters.AddRange(param);
                        }
                        

                        OracleHelper.Con.Open();

                        count = Convert.ToInt32(cmd.ExecuteScalar());

                    }

                    catch (Exception ex)
                    {

                        count = 0;

                    }

                }


            }

            return count;

        }

        #endregion


        #region 执行不查询的数据库操作

        /// <summary>

        /// 执行不查询的数据库操作

        /// </summary>

        /// <param name="commandText">Oracle语句或存储过程名</param>

        /// <param name="commandType">Oracle命令类型</param>

        /// <param name="param">Oracle命令参数数组</param>

        /// <returns>受影响的行数</returns>

        public static int ExecuteNonQuery(string commandText, CommandType commandType, params OracleParameter[] param)
        {

            int result = 0;

            using (OracleHelper.Con)
            {

                using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
                {

                    try
                    {

                        cmd.CommandType = commandType;
                        if (param!=null)
                        {
                            cmd.Parameters.AddRange(param);
                        }

                        

                        OracleHelper.Con.Open();

                        result = cmd.ExecuteNonQuery();

                    }

                    catch (Exception ex)
                    {

                        result = 0;

                    }

                }


            }

            return result;

        }

        #endregion


        #region 执行返回一条记录的泛型对象

        /// <summary>

        /// 执行返回一条记录的泛型对象

        /// </summary>

        /// <typeparam name="T">泛型类型</typeparam>

        /// <param name="reader">只进只读对象</param>

        /// <returns>泛型对象</returns>

        private static T ExecuteDataReader<T>(IDataReader reader)
        {

            T obj = default(T);
            try
            {
                Type type = typeof(T);

                obj = (T)Activator.CreateInstance(type);//从当前程序集里面通过反射的方式创建指定类型的对象   

                //obj = (T)Assembly.Load(OracleHelper._assemblyName).CreateInstance(OracleHelper._assemblyName + "." + type.Name);//从另一个程序集里面通过反射的方式创建指定类型的对象 

                PropertyInfo[] propertyInfos = type.GetProperties();//获取指定类型里面的所有属性

                foreach (PropertyInfo propertyInfo in propertyInfos)
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string fieldName = reader.GetName(i);
                        if (fieldName.ToLower() == propertyInfo.Name.ToLower())
                        {
                            object val = reader[propertyInfo.Name];//读取表中某一条记录里面的某一列
                            if (val != null && val != DBNull.Value)
                            {
                                if (val.GetType() == typeof(decimal) || val.GetType() == typeof(int))
                                {
                                    propertyInfo.SetValue(obj, Convert.ToInt32(val), null);
                                }
                                else if (val.GetType() == typeof(DateTime))
                                {
                                    propertyInfo.SetValue(obj, Convert.ToDateTime(val), null);
                                }
                                else if (val.GetType() == typeof(string))
                                {
                                    propertyInfo.SetValue(obj, Convert.ToString(val), null);
                                }
                            }
                            break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return obj;
        }
        #endregion


        #region 执行返回一条记录的泛型对象

        /// <summary>

        /// 执行返回一条记录的泛型对象

        /// </summary>

        /// <typeparam name="T">泛型类型</typeparam>

        /// <param name="commandText">Oracle语句或存储过程名</param>

        /// <param name="commandType">Oracle命令类型</param>

        /// <param name="param">Oracle命令参数数组</param>

        /// <returns>实体对象</returns>

        public static T ExecuteEntity<T>(string commandText, CommandType commandType, params OracleParameter[] param)
        {

            T obj = default(T);

            using (OracleHelper.Con)
            {

                using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
                {

                    cmd.CommandType = commandType;

                    cmd.Parameters.AddRange(param);

                    OracleHelper.Con.Open();

                    OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    while (reader.Read())
                    {

                        obj = OracleHelper.ExecuteDataReader<T>(reader);

                    }

                }

            }

            return obj;

        }

        #endregion


        #region 执行返回多条记录的泛型集合对象

        /// <summary>

        /// 执行返回多条记录的泛型集合对象

        /// </summary>

        /// <typeparam name="T">泛型类型</typeparam>

        /// <param name="commandText">Oracle语句或存储过程名</param>

        /// <param name="commandType">Oracle命令类型</param>

        /// <param name="param">Oracle命令参数数组</param>

        /// <returns>泛型集合对象</returns>

        public static List<T> ExecuteList<T>(string commandText, CommandType commandType, params OracleParameter[] param)
        {

            List<T> list = new List<T>();

            using (OracleHelper.Con)
            {

                using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
                {

                    try
                    {

                        cmd.CommandType = commandType;
                        if (param != null)
                        {
                            cmd.Parameters.AddRange(param);
                        }
                        OracleHelper.Con.Open();

                        OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                        while (reader.Read())
                        {


                            T obj = OracleHelper.ExecuteDataReader<T>(reader);

                            list.Add(obj);

                        }

                    }

                    catch (Exception ex)
                    {

                        list = null;
                    }
                }
            }
            return list;
        }
        #endregion

        #endregion

    }
}