C# SQLhelper

最新推荐文章于 2022-11-14 13:43:59 发布
最新推荐文章于 2022-11-14 13:43:59 发布
阅读量76 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： 数据库
原文链接：http://www.cnblogs.com/xiexiaokui/archive/2008/11/29/1343941.html
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace DBUtility
{

    /// <summary>
    /// The SqlHelper class is intended to encapsulate high performance,
    /// scalable best practices for common uses of SqlClient.
    /// </summary>
    public abstract class SqlHelper
    {

        //Database connection strings
      public static readonly string ConnectionString = @"server=数据库实例名;database=数据库名;uid=登录名";pwd=密码(没有可以不写);
     //   public static readonly string ConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=DfrvDB;Integrated Security=True";
        //public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["SQLConnString1"].ConnectionString;
        //public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString2"].ConnectionString;
        //public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString3"].ConnectionString;
        //public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ConnectionString;

        // Hashtable to store cached parameters
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) against an existing database connection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing sql transaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a SqlCommand that returns a resultset against the database specified in the connection string
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>A SqlDataReader containing the results</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);

            // we use a try/catch here because if the method throws an exception we want to
            // close the connection throw code, because no datareader will exist, hence the
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        public static DataTable GetDataTable(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter(cmd);

            SqlConnection conn = new SqlConnection(connectionString);

            // we use a try/catch here because if the method throws an exception we want to
            // close the connection throw code, because no datareader will exist, hence the
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                // SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                da.Fill(dt);
                return dt;
                // return rdr;
            }
            catch
            {
                // conn.Close();
                throw;
            }
        }

        /// <summary>
        /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a SqlCommand that returns the first column of the first record against an existing database connection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// add parameter array to the cache
        /// </summary>
        /// <param name="cacheKey">Key to the parameter cache</param>
        /// <param name="cmdParms">an array of SqlParamters to be cached</param>
        public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// Retrieve cached parameters
        /// </summary>
        /// <param name="cacheKey">key used to lookup parameters</param>
        /// <returns>Cached SqlParamters array</returns>
        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">SqlCommand object</param>
        /// <param name="conn">SqlConnection object</param>
        /// <param name="trans">SqlTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}

转载于:https://www.cnblogs.com/xiexiaokui/archive/2008/11/29/1343941.html

C# SqlHelper类 （微软官方）
03-07
微软官方C# SqlHelper类 ，内带有注释说明，供大家参考
c# SqlHelper.cs
09-27
c# SqlHelper类提供对 SQL语句的增删改查 下载
C# SqlHelper类
08-07
自己写C# SqlHelper类，简易封装
C# SqlHelper
weixin_43942765的博客
09-28 999
每当小编在做一个新项目的时候都不可避免的会接触到与数据库的交互，小编准备一份，分享给大家，小编不觉得自己的这份SqlHelper多好，因为与部门里的老师傅们相比，我简直是逊毙了，不过平常自己练习项目的时候这个还是可以减少大家很多时间的 using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; name
c# SQLHelper(for winForm)实现代码
09-06
C# SQLHelper 实现代码详解 C# SQLHelper 是一个常用的数据库操作类库，主要用于实现数据库的连接、执行 SQL 语句、返回DataReader 等功能。在 WinForm 应用程序中，SQLHelper 通常用于实现数据的CRUD（Create, ...
一个C# SQLHelper 数据库操作类
03-17
内容索引:C#源码,数据库应用,SQLHelper类　一个C#操作数据库的类，SQLHelper中包含了所有数据库的操作，www.okbase.net 测试通过，拿来就能用，在微软网站发现的，觉得很实用。
C# SqlHelper应用开发学习
01-20
本文实例为大家分享了C# SqlHelper应用技巧，供大家参考，具体内容如下 使用App.config配置文件封装连接字符串，方便重复使用 —>添加App.conifg配置文件 —>Add : ConnectionString: —>添加引用 <?xml ...
C# SqlHelper类
01-23
总的来说，SqlHelper类是C#中一个非常实用的工具，它可以帮助开发者更高效、更安全地处理与数据库的交互。通过封装常见的数据库操作，它使得代码更加简洁、易于理解和维护。在实际项目中，可以根据具体需求扩展和...
C#：SqlHelper
weixin_34351321的博客
02-07 136
虽然日常工作中都是调用别人写好的底层，但是要真正学到技术，还是要懂些底层原理，最好是能自己写底层 一、底层 注：引用命名空间 using System.Data;using System.Data.SqlClient; 1 public class SqlHelper 2 { 3 /// <summary> 4 ...
C# SQLHelper
10-12
微软的C#连接SQL的SQLHelper类.
C# Sqlhelper
11-19
C# Sqlhelper 简单的sqlhelper 类
c# SqlHelper
潇洒哥的运维之道
11-21 1818
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namesp...
SqlHelper 详解
热门推荐
非闪--BLOG
03-05 1万+
SqlHelper 类实现详细信息SqlHelper 类用于通过一组静态方法来封装数据访问功能。该类不能被继承或实例化，因此将其声明为包含专用构造函数的不可继承类。 在 SqlHelper 类中实现的每种方法都提供了一组一致的重载。这提供了一种很好的使用 SqlHelper 类来执行命令的模式，同时为开发人员选择访问数据的方式提供了必要的
C#中的SqlHelper
GEEK-BANANA
04-01 1867
一直都在使用的SqlHelper究竟是什么呢，其实就是将需要连接数据库的部分封装成为了一个SqlHelper但是只会用还不行，还要将SqlHelper手敲出来，没有源码也要会敲，所以我就多敲了几遍来加深记忆，当然最重要的是你要理解之后才行！注释回头再加，见谅代码如下：public class SqlHelper { public static readonly strin...
C#_SqlHelper
weixin_43986048的博客
11-14 231
C#_sql使用助手
C#之SqlHelper
weixin_34075551的博客
02-16 134
<add name="ConnStr" connectionString="Data Source=.;Initial Catalog=test;User ID=sa;Password=123456"/> using System; using System.Configuration; using System.Data; using System.Data...
C# sqlhelper
最新发布
02-11
### C# 中 `SqlHelper` 类的使用方法 #### 创建连接字符串 为了方便管理数据库连接，在应用程序中通常会创建一个静态的方法来获取连接字符串。这可以通过配置文件读取或硬编码实现。 ```csharp public static class SqlHelper { public static string GetConnectionStringSqlServer() { return "your_connection_string_here"; } } ``` 此部分展示了如何定义一个名为 `GetConnectionStringSqlServer` 的公共静态函数用于返回SQL Server 数据库的连接串[^2]。 #### 执行简单查询 当需要执行简单的 SELECT 查询时，可以利用 ADO.NET 提供的基础 API 或者像 Dapper 这样的微 ORM 来简化操作流程。下面的例子展示了一个基本的查询场景： ```csharp using System.Data.SqlClient; using Dapper; // 假设 Customer 是实体类 List<Customer> customers; using (var connection = new SqlConnection(SqlHelper.GetConnectionStringSqlServer())) { customers = connection.Query<Customer>("SELECT * FROM Customers").ToList(); } ``` 上述代码片段通过调用 `SqlHelper.GetConnectionStringSqlServer()` 方法获得连接对象并执行了一次无条件的选择语句，最终将结果集映射到列表类型的变量上[^1]。 #### 参数化查询防止注入攻击 对于带有输入参数的情况，则应该采用参数化的形式构建 SQL 语句以增强安全性。这里继续沿用了之前提到过的 Dapper 库作为例子： ```csharp string userName = "张三"; // 用户名来自安全验证后的来源 using (var connection = new SqlConnection(SqlHelper.GetConnectionStringSqlServer())) { var customer = connection.QueryFirstOrDefault<Customer>( @"SELECT TOP(1) * FROM Users WHERE Name = @name", new { name = userName }); } ``` 这段代码不仅实现了基于用户名查找用户的逻辑，还有效防范了潜在的 SQL 注入风险。 #### 更新数据记录 除了查询之外，有时也需要修改已有的表项内容。此时可借助命令模式完成更新动作： ```csharp int rowsAffected; using (var connection = new SqlConnection(SqlHelper.GetConnectionStringSqlServer())) { rowsAffected = connection.Execute( "UPDATE Products SET Price = @price WHERE Id = @id", new { price = 9.9m, id = productId }); } if(rowsAffected > 0){ Console.WriteLine("Update successful."); } else{ Console.WriteLine("No record was updated."); } ``` 该段程序尝试更改指定 ID 商品的价格，并反馈影响行数给用户知道操作是否成功。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值