自己写了一个简单的数据库访问类

 

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

namespace DBUtility
{
    public class SQLHelper
    {
        private SqlConnection con;
        public SQLHelper()
        {
            con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ToString());
        }
        public SQLHelper(String ConnectionString)
        {
            con = new SqlConnection(ConnectionString);
        }

        /// <summary>
        /// Execute SqlCommand
        /// </summary>
        /// <param name="proc_name"></param>
        /// <param name="param"></param>
        public void ExecuteSqlCommand(string proc_name, SqlParameter[] param)
        {
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand(proc_name, con);
            cmd.Transaction = tran;
            cmd.CommandType = CommandType.StoredProcedure;

            if (param != null)
            {
                foreach (SqlParameter parameter in param)
                {
                    ReplaceParameters(parameter);
                    cmd.Parameters.Add(parameter);
                }
            }
            try
            {
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw (ex);
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// Execute SqlCommand
        /// </summary>
        /// <param name="proc_name"></param>
        public void ExecuteSqlCommand(string proc_name)
        {
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand(proc_name, con);
            cmd.Transaction = tran;
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw (ex);
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// Return DataTable
        /// </summary>
        /// <param name="proc_name"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string proc_name, SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlDataAdapter sda = new SqlDataAdapter(proc_name, con);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            sda.SelectCommand.Transaction = tran;
            if (param != null)
            {
                foreach (SqlParameter parameter in param)
                {
                    ReplaceParameters(parameter);
                    sda.SelectCommand.Parameters.Add(parameter);
                }
            }
            try
            {
                sda.Fill(dt);
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw (ex);
            }
            finally
            {
                con.Close();
            }
            return dt;
        }

        /// <summary>
        /// Return DataTable
        /// </summary>
        /// <param name="proc_name"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string proc_name)
        {
            DataTable dt = new DataTable();
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlDataAdapter sda = new SqlDataAdapter(proc_name, con);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            sda.SelectCommand.Transaction = tran;
            try
            {
                sda.Fill(dt);
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw (ex);
            }
            finally
            {
                con.Close();
            }
            return dt;
        }

        /// <summary>
        /// Return DataSet
        /// </summary>
        /// <param name="proc_name"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string proc_name)
        {
            DataSet ds = new DataSet();
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlDataAdapter sda = new SqlDataAdapter(proc_name, con);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            sda.SelectCommand.Transaction = tran;
            try
            {
                sda.Fill(ds);
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw (ex);
            }
            finally
            {
                con.Close();
            }
            return ds;
        }

        /// <summary>
        /// Return DataSet
        /// </summary>
        /// <param name="proc_name"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string proc_name, SqlParameter[] param)
        {
            DataSet ds = new DataSet();
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlDataAdapter sda = new SqlDataAdapter(proc_name, con);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            sda.SelectCommand.Transaction = tran;
            if (param != null)
            {
                foreach (SqlParameter parameter in param)
                {
                    ReplaceParameters(parameter);
                    sda.SelectCommand.Parameters.Add(parameter);
                }
            }
            try
            {
                sda.Fill(ds);
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw (ex);
            }
            finally
            {
                con.Close();
            }
            return ds;
        }

        /// <summary>
        /// Return DataReader
        /// </summary>
        /// <param name="proc_name"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string proc_name, SqlParameter[] param)
        {
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand(proc_name, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Transaction = tran;
            if (param != null)
            {
                foreach (SqlParameter parameter in param)
                {
                    ReplaceParameters(parameter);
                    cmd.Parameters.Add(parameter);
                }
            }
            SqlDataReader sdr;
            try
            {
                sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
            }
            return sdr;
        }

 

        /// <summary>
        /// Return DataTable
        /// </summary>
        /// <param name="SqlStr"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTableSql(string SqlStr, SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlDataAdapter sda = new SqlDataAdapter(SqlStr, con);
            sda.SelectCommand.CommandType = CommandType.Text;
            sda.SelectCommand.Transaction = tran;
            if (param != null)
            {
                foreach (SqlParameter parameter in param)
                {
                    ReplaceParameters(parameter);
                    sda.SelectCommand.Parameters.Add(parameter);
                }
            }
            try
            {
                sda.Fill(dt);
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw (ex);
            }
            finally
            {
                con.Close();
            }
            return dt;
        }
        /// <summary>
        /// Execute SqlCommand
        /// </summary>
        /// <param name="SqlStr"></param>
        /// <param name="param"></param>
        public void ExecuteSqlCommandSql(string SqlStr, SqlParameter[] param)
        {
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand(SqlStr, con);
            cmd.Transaction = tran;
            cmd.CommandType = CommandType.Text;

            if (param != null)
            {
                foreach (SqlParameter parameter in param)
                {
                    ReplaceParameters(parameter);
                    cmd.Parameters.Add(parameter);
                }
            }
            try
            {
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw (ex);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// Return DataReader
        /// </summary>
        /// <param name="SqlStr"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReaderSql(string SqlStr, SqlParameter[] param)
        {
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand(SqlStr, con);
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = tran;
            if (param != null)
            {
                foreach (SqlParameter parameter in param)
                {
                    ReplaceParameters(parameter);
                    cmd.Parameters.Add(parameter);
                }
            }
            SqlDataReader sdr;
            try
            {
                sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
            }
            return sdr;
        }

        /// <summary>
        /// Return DataSet
        /// </summary>
        /// <param name="proc_name"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSetSql(string SqlStr, SqlParameter[] param)
        {
            DataSet ds = new DataSet();
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlDataAdapter sda = new SqlDataAdapter(SqlStr, con);
            sda.SelectCommand.CommandType = CommandType.Text;
            sda.SelectCommand.Transaction = tran;
            if (param != null)
            {
                foreach (SqlParameter parameter in param)
                {
                    ReplaceParameters(parameter);
                    sda.SelectCommand.Parameters.Add(parameter);
                }
            }
            try
            {
                sda.Fill(ds);
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw (ex);
            }
            finally
            {
                con.Close();
            }
            return ds;
        }

        /// <summary>
        /// Replace Parameters
        /// </summary>
        /// <param name="SingleParameter"></param>
        /// <returns></returns>
        private SqlParameter ReplaceParameters(SqlParameter SingleParameter)
        {
            if (SingleParameter.Value is String)
            {
                SingleParameter.Value.ToString().Replace(@"\", @"\\");
                SingleParameter.Value.ToString().Replace("'", "\"");
            }
            return SingleParameter;
        }
    }

 

调用方法 引入命名空间
            SQLHelper sh= new SQLHelper();
            DataTable dt = new DataTable();
            SqlParameter[] param ={ new SqlParameter("@ID", SqlDbType.Int), new SqlParameter("@Title", SqlDbType.NVarChar) };
            param[0].Value = 1;
            param[1].Value = "档案管理";
            dt = sh.ExecuteDataTable("sp_UDS_DabyGetAll", param);