ADO.NET工具类（三）

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

namespace AppUtility
{
    public class ADOTools
    {
        //操作数据库的API
        public static readonly string CONNECTION_STR = ConfigurationManager.ConnectionStrings["TestStr"].ToString();

        /// <summary>
        /// 执行SQL操作,ExcuteNoQuery
        /// </summary>
        /// <param name="connectionStr">数据库链接字符串</param>
        /// <param name="strSql">执行的SQL语句</param>
        /// <returns>影响行数</returns>
        public static int ExcuteNoQuery(string connectionStr, string strSql)
        {
            int flag = 0;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(connectionStr);
                conn.Open();
                cmd = new SqlCommand(strSql, conn);
                flag = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return flag;
        }

        /// <summary>
        /// 执行SQL操作,ExecuteScalar
        /// </summary>
        /// <param name="connectionStr">数据库链接字符串</param>
        /// <param name="strSql">执行的SQL语句</param>
        /// <returns>返回唯一值</returns>
        public static object ExecuteScalar(string connectionStr, string strSql)
        {
            object flag = 0;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(connectionStr);
                conn.Open();
                cmd = new SqlCommand(strSql, conn);
                flag = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return flag;
        }

        /// <summary>
        /// Reader查询
        /// </summary>
        /// <param name="connectionStr">数据库链接字符串</param>
        /// <param name="strSql">执行的SQL语句</param>
        /// <returns>影响行数</returns>
        public static SqlDataReader ExcuteReader(string connectionStr, string strSql)
        {
            SqlConnection conn = new SqlConnection(connectionStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(strSql, conn);
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }

        /// <summary>
        /// DataTable查询
        /// </summary>
        /// <param name="connectionStr">数据库链接字符串</param>
        /// <param name="strSql">执行的SQL语句</param>
        /// <returns>返回DataTable数据源</returns>
        public static DataTable ExcuteDataTable(string connectionStr, string strSql)
        {
            DataTable dt = null;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataAdapter adapter = null;
            try
            {
                conn = new SqlConnection(connectionStr);
                cmd = new SqlCommand(strSql, conn);
                adapter = new SqlDataAdapter(cmd);
                dt = new DataTable();
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                dt = null;
                throw ex;
            }
            finally
            {
                adapter.Dispose();
                cmd.Dispose();
                conn.Dispose();
            }
            return dt;
        }

        /// <summary>
        /// DataTable参数化查询
        /// </summary>
        /// <param name="connectionStr">数据库链接字符串</param>
        /// <param name="strSql">执行的SQL语句</param>
        /// <param name="CommandType">存储过程或者SQL</param>
        /// <returns>返回DataTable数据源</returns>
        public static DataTable ExcuteDataTable(string connectionStr, string strSql, CommandType cd, SqlParameter[] sp)
        {
            DataTable dt = null;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataAdapter adapter = null;
            try
            {
                conn = new SqlConnection(connectionStr);
                cmd = new SqlCommand(strSql, conn);
                cmd.CommandType = cd;
                cmd.Parameters.AddRange(sp);
                adapter = new SqlDataAdapter(cmd);
                dt = new DataTable();
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                dt = null;
                throw ex;
            }
            finally
            {
                adapter.Dispose();
                cmd.Dispose();
                conn.Dispose();
            }
            return dt;
        }

        /// <summary>
        /// 参数化执行SQL
        /// </summary>
        /// <param name="connectionStr">连接字符串</param>
        /// <param name="strSql">执行的SQL语句</param>
        /// <param name="cd">存储过程或者SQL</param>
        /// <param name="sp">参数数组</param>
        /// <returns>影响的行数</returns>
        public static int ExcuteNoQuery(string connectionStr, string strSql, CommandType cd, SqlParameter[] sp)
        {
            int flag = 0;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(connectionStr);
                conn.Open();
                cmd = new SqlCommand(strSql, conn);
                cmd.CommandType = cd;
                cmd.Parameters.AddRange(sp);
                flag = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return flag;
        }

        /// <summary>
        /// 参数化执行SQL,返回插入后自增ID
        /// </summary>
        /// <param name="connectionStr">连接字符串</param>
        /// <param name="strSql">执行的SQL语句</param>
        /// <param name="cd">存储过程或者SQL</param>
        /// <param name="sp">参数数组</param>
        /// <returns>影响的行数</returns>
        public static int ExcuteNoQueryByReturnID(string connectionStr, string strSql, CommandType cd, SqlParameter[] sp)
        {
            int flag = 0;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(connectionStr);
                conn.Open();
                cmd = new SqlCommand(strSql, conn);
                cmd.CommandType = cd;
                cmd.Parameters.AddRange(sp);
                cmd.ExecuteNonQuery();
                flag = Convert.ToInt32(sp[1].Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return flag;
        }

        /// <summary>
        /// 执行SQL事务,ExcuteNoQuery
        /// </summary>
        /// <param name="transaction">事务对象</param>
        /// <param name="strSql">执行脚本</param>
        /// <returns></returns>
        public static int ExcuteNoQuery(SqlTransaction transaction,string strSql)
        {
            int flag = 0;
            SqlCommand cmd = null;
            try
            {
                cmd = new SqlCommand(strSql, transaction.Connection, transaction);
                flag = cmd.ExecuteNonQuery(); 
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
            }
            return flag;
        }

        /// <summary>
        /// 执行SQL事务,ExecuteScalar
        /// </summary>
        /// <param name="transaction">事务对象</param>
        /// <param name="strSql">执行脚本</param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlTransaction transaction, string strSql)
        {
            object flag = 0;
            SqlCommand cmd = null;
            try
            {
                cmd = new SqlCommand(strSql, transaction.Connection, transaction);
                flag = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
            }
            return flag;
        }
    }
}