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


    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();
    
          
    
        
        }
    }

    这样显示sql语句的时候条件的值 就不是1

    而是变量@qkid

  • 相关阅读:
    Web服务器安全设置
    java大文件读写操作,java nio 之MappedByteBuffer,高效文件/内存映射
    Java IO和Java NIO在文件拷贝上的性能差异分析
    Java高效读取大文件
    NIO入门之轻松读取大文件
    我来说说java的NIO
    java读取大文件 超大文件的几种方法
    @RequestBody 的正确使用办法
    友鱼项目知识点
    怎样查看Tomcat动态控制台信息
  • 原文地址:https://www.cnblogs.com/haihang/p/2794974.html
Copyright © 2020-2023  润新知