找一个适合自己的数据库操作类刚开始不理解sqlparameter[] 这个参数是干什么的怎么传...-CSDN博客

using System.Data.SqlClient;
using System.Data;
using System;
using System.Configuration;
using System.Collections.Generic;
namespace DBUtility
{
    public class sqlheper
    {
        public SqlConnection con;
        public sqlheper()
        {
            con = new SqlConnection(ConfigurationManager.AppSettings["SQLConnString"].ToString());
        }
        public sqlheper (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;
        }
        /// <summary>
        /// 事物处理
        /// </summary>
        /// <param name="listsql">sql数组</param>
        /// <returns></returns>
        public  int ExuceteSqlTransaction(List<string> listsql)
        {
            int cnt = 0;
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection();
           string ConnectionStringLocalTransaction = ConfigurationManager.AppSettings["SQLConnString"].ToString();
           conn.ConnectionString = ConnectionStringLocalTransaction;
            try
            {
                for (int i = 0; i < listsql.Count; i++)
                {

                    cmd.CommandText += listsql[i].ToString();
                }
                cmd.Connection = conn;
                conn.Open();

                //开始事务
                cmd.Transaction = conn.BeginTransaction();
                cnt = cmd.ExecuteNonQuery();

                cmd.Transaction.Commit();
            }
            catch
            {
                //回滚事务
                cmd.Transaction.Rollback();
                throw;
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
            }
            return cnt;

        }
    }
}

sqlparameter[] 这东西是防止sql注入的也就是说 sql后面加的条件语句中所满足的条件在显示的时候是个隐藏的变量而不会出现值下面对这个方法进行引用注意上述方法是调用存储过程如果想直接sql语句可以注释掉方法中的sda.SelectCommand.CommandType = CommandType.Text;部分下面写一下调用过程的例子

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        bind();
    }
    public void bind()
    { 
    sqlheper sql =new sqlheper();
        string str ="select * from thesis where qkid=@qkid";
        SqlParameter[] sqlpa=new SqlParameter[]{new SqlParameter("@qkid", SqlDbType.Int)};
        sqlpa[0].Value= "1";

         DataSet myds=sql.ExecuteDataSet(str, sqlpa);
         Gridview1.DataSource = myds;
         Gridview1.DataBind();

      

    
    }
}