ASP.NET DBHelper类

最新推荐文章于 2019-03-08 21:49:00 发布

原创最新推荐文章于 2019-03-08 21:49:00 发布 · 2.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#asp.net #command #parameters #string #object #exception

本文介绍了一个用于数据库操作的工具类，包括连接数据库、执行SQL语句、存储过程调用等功能，并支持参数化查询，适用于多种数据库操作场景。

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

using System.Configuration;
using System.Data.Common;
using System.Data;

namespace DownData.dal
{
    public static class DBHelper
    {
        private static readonly string connectionString =
            "Data Source=.//newdb;Initial Catalog=downloaddata; uid=sa;pwd=sa";
        private static readonly string providerName ="System.Data.SqlClient";

        //GetConnection 用于获取连接数据库的 connection 对象...
        private static DbConnection GetConnection()
        {
            DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
            DbConnection connection = _factory.CreateConnection();
            connection.ConnectionString = connectionString;
            return connection;
        }

        //GetCommand 获取命令参数 command 对象...
        private static DbCommand GetCommand(string commandText, CommandType commandType, DbConnection connection)
        {
            DbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            return command;
        }

        //GetCommand 方法重载...
        private static DbCommand GetCommand(string commandText, DbConnection connection)
        {
            DbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = CommandType.Text;
            return command;
        }

        //GetParameter 用于为命令设置参数...
        private static DbParameter GetParameter(string paramName, object paramValue, DbCommand command)
        {
            DbParameter parameter = command.CreateParameter();
            parameter.ParameterName = paramName;
            parameter.Value = paramValue;
            return parameter;
        }
        public static int ExecuteNonQueryProc(string sqlCommand)
        {
            int result = 0;
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
                connection.Open();
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
            }
            return result;
        }
  public static int ExecuteNonQueryProc(string sqlCommand, Dictionary<string, object> parameters)
        {
            int result = 0;
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
                foreach (var p in parameters)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));
                }
                connection.Open();
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
            }
            return result;
        }
        /// <summary>
        /// ExecuteNonQuery方法重载.............
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <param name="parameters"></param>
        public static int ExecuteNonQuery(string sqlCommand)
        {
            int result = 0;
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
                connection.Open();
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return result;
            }
        }
        public static int ExecuteNonQuery(string sqlCommand,Dictionary<string,object> para)
        {
            int result = 0;
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
                foreach (var p in para)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));
                }
                connection.Open();
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return result;
            }
        }
        //ExecuteScalar-用存储过程对数据库进行查询操作
        public static object ExecuteScalarProc(string cmdText)
        {
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);
                connection.Open();
                object val = command.ExecuteScalar();
                command.Parameters.Clear();
                return val;
            }
        }
   public static object ExecuteScalarProc(string cmdText, Dictionary<string, object> para)
        {
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);
                foreach (var p in para)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));
                }
                connection.Open();
                object val = command.ExecuteScalar();
                command.Parameters.Clear();
                return val;
            }
        }
        public static object ExecuteScalar(string cmdText)
        {
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(cmdText, CommandType.Text, connection);
                connection.Open();
                object val = command.ExecuteScalar();
                command.Parameters.Clear();
                return val;
            }
        }

        public static object ExecuteScalar(string cmdText,Dictionary<string,object> para)
        {
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(cmdText, CommandType.Text, connection);
                foreach (var p in para)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));
                }
                connection.Open();
                object val = command.ExecuteScalar();
                command.Parameters.Clear();
                return val;
            }
        }

        //ExecuteReader-用存储过程对数据库进行查询操作----无查询条件
        public static DbDataReader GetReaderProc(string sqlCommand)
        {
            try
            {
                DbConnection connection = GetConnection();
                DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
                connection.Open();
                DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (Exception ex)
            {
                Console.Write("" + ex.Message);
                return null;
            }
        }

//重载ExecuteReader - 用存储过程对数库进行查询操作----有查询条件...

        public static DbDataReader GetReaderProc(string sqlCommand, Dictionary<string, object> parameters)
        {
            try
            {
                DbConnection connection = GetConnection();
                DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
                foreach (var p in parameters)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));
                }
                connection.Open();
                DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
                return reader;
            }
            catch
            {
                return null;
            }
        }
        //重载ExecuteReader - 用sql语句对数库进行查询操作----无查询条件...
        #region
        public static DbDataReader GetReader(string sqlCommand)
        {
            try
            {
                DbConnection connection = GetConnection();
                DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
                connection.Open();
                DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                Console.Write("" + ex.Message);
                return null;
            }
        }
        #endregion
        //重载ExecuteReader -用sql语句对数库进行查询操作----有查询条件...
        public static DbDataReader GetReader(string sqlCommand, Dictionary<string, object> parameters)
        {
            try
            {
                DbConnection connection = GetConnection();
                DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
                foreach (var p in parameters)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));
                }
                connection.Open();
                DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                Console.Write("" + ex.Message);
                return null;
            }
        }

        public static DataTable GetDataSet(string safeSql)
        {
            DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
            DbConnection connection = GetConnection();
            DbDataAdapter da = _factory.CreateDataAdapter();
            da.SelectCommand = connection.CreateCommand();
            da.SelectCommand.CommandText = safeSql;
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

}
}