增加事务与防注入参数的数据库连接类

access:

using System;
using System.Data.OleDb;
using System.Data;
using System.Diagnostics;
using System.Collections.Generic;

public class AccessDBUtil
{
    private static String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
            System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["ConnectString"]);
    private AccessDBUtil()
    {
        
    }
    
    //执行单条插入语句，并返回id，不需要返回id的用ExceuteNonQuery执行。
    public static int ExecuteInsert(string sql,OleDbParameter[] parameters)
    {
        //Debug.WriteLine(sql);
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            try
            {
                connection.Open();
                if(parameters!=null) cmd.Parameters.AddRange(parameters);
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"select @@identity";
                int value = Int32.Parse(cmd.ExecuteScalar().ToString());
                return value;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
    public static int ExecuteInsert(string sql)
    {
        return ExecuteInsert(sql,null);
    }
    
    //执行带参数的sql语句,返回影响的记录数（insert,update,delete)
    public static int ExecuteNonQuery(string sql,OleDbParameter[] parameters)
    {
        //Debug.WriteLine(sql);
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            try
            {
                connection.Open();
                if(parameters!=null) cmd.Parameters.AddRange(parameters);
                int rows = cmd.ExecuteNonQuery();
                return rows;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
    //执行不带参数的sql语句，返回影响的记录数
    //不建议使用拼出来SQL
    public static int ExecuteNonQuery(string sql)
    {
        return ExecuteNonQuery(sql,null);
    }
    
    //执行单条语句返回第一行第一列,可以用来返回count(*)
    public static int ExecuteScalar(string sql,OleDbParameter[] parameters)
    {
        //Debug.WriteLine(sql);
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            try
            {
                connection.Open();
                if(parameters!=null) cmd.Parameters.AddRange(parameters);
                int value = Int32.Parse(cmd.ExecuteScalar().ToString());
                return value;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
    public static int ExecuteScalar(string sql)
    {
        return ExecuteScalar(sql,null);
    }
    
    //执行事务
    public static void ExecuteTrans(List<string> sqlList,List<OleDbParameter[]> paraList)
    {
        //Debug.WriteLine(sql);
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            OleDbCommand cmd = new OleDbCommand();
            OleDbTransaction transaction = null;
            cmd.Connection = connection;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                cmd.Transaction = transaction;
                
                for(int i=0;i<sqlList.Count;i++)
                {
                    cmd.CommandText=sqlList[i];
                    if(paraList!=null&&paraList[i]!=null) 
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(paraList[i]);
                    }
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();

            }
            catch (Exception e)
            {
                try
                {
                    transaction.Rollback();
                }
                catch
                {
                   
                }
                throw e;
            }
            
        }
    }
    public static void ExecuteTrans(List<string> sqlList)
    {
        ExecuteTrans(sqlList,null);
    }

    //执行查询语句，返回dataset
    public static DataSet ExecuteQuery(string sql,OleDbParameter[] parameters)
    {
        //Debug.WriteLine(sql);
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                
                OleDbDataAdapter da = new OleDbDataAdapter(sql, connection);
                if(parameters!=null) da.SelectCommand.Parameters.AddRange(parameters);
                da.Fill(ds,"ds");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
    }
    public static DataSet ExecuteQuery(string sql)
    {
        return ExecuteQuery(sql,null);
    }
    //执行查询语句返回datareader，使用后要注意close
    //这个函数在AccessPageUtils中使用，执行其它查询时最好不要用
    public static OleDbDataReader ExecuteReader(string sql)
    {
        //Debug.WriteLine(sql);
        OleDbConnection connection = new OleDbConnection(connectionString);
        OleDbCommand cmd = new OleDbCommand(sql, connection);
        try
        {
            connection.Open();
            return  cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception e)
        {
            connection.Close();
            throw e;
        }
    }
    
}

 

sql server:

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


class SqlServerUtil
{
    private static String connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectString"];
    private SqlServerUtil() { }

    #region 查询操作
    /// <summary>
    /// 执行查询语句，返回dataset
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="parameters">参数</param>
    /// <returns>所查询的数据集</returns>
    public static DataSet ExecuteQuery(string sql, SqlParameter[] parameters)
    {
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            DataSet ds = new DataSet();
            try
            {
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
                if (parameters != null)
                    adapter.SelectCommand.Parameters.AddRange(parameters);

                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
    }

    public static DataSet ExecuteQuery(string sql)
    {
        return ExecuteQuery(sql, null);
    }

    /// <summary>
    /// 此查询操作只返回所查询结果的第一行第一列，速度比使用Adapter快很多
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parameters"></param>
    /// <returns>返回第一行第一列，类型为object</returns>
    public static object ExecuteScalar(string sql, SqlParameter[] parameters)
    {
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand(sql,con);
            try
            {
                con.Open();
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

    public static object ExecuteScalar(string sql)
    {
        return ExecuteScalar(sql, null);
    }

    /// <summary>
    /// 执行查询语句返回datareader，使用后要注意close
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static SqlDataReader ExecuteReader(string sql)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(sql, con);
        try
        {
            con.Open();
            //传递CommandBehavior.CloseConnection 枚举变量，这样在调用SqlDataReader的Close方法时会自动关闭数据库连接
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            con.Close();
            throw;
        }
    }
    #endregion

    #region 插入、删除、更新操作
    /// <summary>
    /// 执行带参数的sql语句,返回影响的记录数
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    public static int ExecuteNonQuery(string sql, SqlParameter[] parameters)
    {
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

    public static int ExecuteNonQuery(string sql)
    {
        return ExecuteNonQuery(sql, null);
    }

    /// <summary>
    /// 执行单条插入语句，并返回id，不需要返回id的用ExceuteNonQuery执行。
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    public static int ExecuteInsert(string sql, SqlParameter[] parameters)
    {
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"select @@identity";
                return int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

    public static int ExecuteInsert(string sql)
    {
        return ExecuteInsert(sql, null);
    }
    #endregion

    #region 事务处理
    public static void ExecuteTrans(List<string> sqlList, List<SqlParameter[]> paraList)
    {
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand();
            SqlTransaction transaction = null;
            cmd.Connection = con;
            try
            {
                con.Open();
                transaction = con.BeginTransaction();
                cmd.Transaction = transaction;

                for (int i = 0; i < sqlList.Count; i++)
                {
                    cmd.CommandText = sqlList[i];
                    if (paraList != null && paraList[i] != null)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(paraList[i]);
                    }
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    transaction.Rollback();
                }
                catch 
                {
                }
                throw ex;
            }
        }
    }
    #endregion
}