机遇屋-C#Sql Server的sqlHelper

C#Sql Server的sqlHelper

作者：ych

代码：
```
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace getUrl
{
   public class SqlHelper
    {
        public static readonly string connString = "Data Source=DESKTOP-BCJ2EPI\\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=123456";
        #region 事务
        private static SqlConnection connTr;
        private static SqlCommand cmdTr;
        private static SqlTransaction sqlTr;
        /// <summary>
        /// 事务开始
        /// </summary>
        public static void BeginTransaction()
        {
            connTr = new SqlConnection(connString);
            connTr.Open();
            sqlTr = connTr.BeginTransaction();
        }
        /// <summary>
        /// 事务方法
        /// </summary>
        /// <param name="sql">数据库语句</param>
        /// <param name="ps">参数</param>
        /// <returns></returns>
        public static int ExecuteTranNonQuery(string sql, CommandType commandType, params SqlParameter[] ps)
        {
            cmdTr = new SqlCommand(sql, connTr);
            if (connTr.State == ConnectionState.Closed)
            {
                connTr.Open();//打开数据库
            }
            cmdTr.CommandType = commandType;
            if (ps != null)
            {
                cmdTr.Parameters.AddRange(ps);
            }
            cmdTr.Transaction = sqlTr;//事物
            return cmdTr.ExecuteNonQuery();
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public static void Commit()
        {
            sqlTr.Commit();
        }
        /// <summary>
        /// 事务回滚
        /// </summary>
        public static void Rollback()
        {
            sqlTr.Rollback();
        }
        /// <summary>
        /// 事务关闭连接
        /// </summary>
        public static void DisTranConnect()
        {
            sqlTr.Dispose();
            cmdTr.Dispose();
            connTr.Close();
            connTr.Dispose();
        }
        #endregion
        #region 执行 增 删 改
        /// <summary>
        /// 执行 增 删 改
        /// </summary>
        /// <param name="Sql">要执行的Sql</param>
        /// <param name="param">参数</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(string sql, CommandType commandType, params SqlParameter[] param)
        {
            //实例化连接对象，并指定连接字符串，自动释放资源，不用关闭
            using (SqlConnection conn = new SqlConnection(connString))
            {
                //实例化命令对象，指定Sql，与连接对象
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = commandType;
                    //如果有参数
                    if (param != null)
                    {
                        //批量添加参数
                        cmd.Parameters.AddRange(param);
                    }
                    //打开连接
                    conn.Open();
                    //执行Sql并返回影响行数
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        #endregion
        #region 执行 查询
        /// <summary>
        /// 执行 查询
        /// </summary>
        /// <param name="Sql">要执行的Sql</param>
        /// <param name="param">参数</param>
        /// <returns>数据集</returns>
        public static SqlDataReader ExecuteReader(string Sql, CommandType commandType, params SqlParameter[] param)
        {
            //实例化连接对象，并指定连接字符串
            SqlConnection conn = new SqlConnection(connString);
            //实例化命令对象，指定Sql，与连接对象
            using (SqlCommand cmd = new SqlCommand(Sql, conn))
            {
                cmd.CommandType = commandType;
                //如果有参数
                if (param != null)
                {
                    //批量添加参数
                    cmd.Parameters.AddRange(param);
                }
                //打开连接
                conn.Open();
                //执行Sql并返回影响行数,如果将返回的SqlDataReader关闭时也将关闭连接
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        /// <summary>
        /// 执行查询存储过程，并且返回一个SqlDataReader对象(使用SqlDataReader对象执行)
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <param name="ps">存储过程中需要的参数</param>
        /// <returns>读取器对象</returns>
        public static SqlDataReader ExecuteReaderProc(string Sql, CommandType commandType, params SqlParameter[] param)
        {
            //实例化连接对象，并指定连接字符串
            SqlConnection conn = new SqlConnection(connString);
            //实例化命令对象，指定Sql，与连接对象
            using (SqlCommand cmd = new SqlCommand(Sql, conn))
            {
                cmd.CommandType = commandType;
                //如果有参数
                if (param != null)
                {
                    //批量添加参数
                    cmd.Parameters.AddRange(param);
                }
                //打开连接
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                //执行Sql并返回影响行数,如果将返回的SqlDataReader关闭时也将关闭连接
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        #endregion
        #region 完成数据的查询，返回DataTable
        /// <summary>
        /// 完成数据的查询，返回DataTable
        /// </summary>
        /// <param name="Sql">要执行的Sql</param>
        /// <param name="param">参数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetTable(string sql, CommandType commandType, params SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            //实例化连接对象，并指定连接字符串，自动释放资源，不用关闭
            using (SqlConnection conn = new SqlConnection(connString))
            using (SqlDataAdapter adp = new SqlDataAdapter(sql, conn))
            {
                adp.SelectCommand.CommandType = commandType;
                if (param != null)
                {
                    adp.SelectCommand.Parameters.AddRange(param);
                }
                adp.Fill(dt);
            }
            return dt;
        }
        #endregion
        #region 返回首行首列
        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="SqlSql">要执行的SqlSql</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public static object ExecuteScaler(string Sql, CommandType commandType,params SqlParameter[] param)
        {
            //实例化连接对象，并指定连接字符串
            using (SqlConnection conn = new SqlConnection(connString))
            //实例化命令对象，指定Sql，与连接对象
            using (SqlCommand cmd = new SqlCommand(Sql, conn))
            {
                cmd.CommandType = commandType;
                //如果有参数
                if (param != null)
                {
                    //批量添加参数
                    cmd.Parameters.AddRange(param);
                }
                //打开连接
                conn.Open();
                //执行Sql
                return cmd.ExecuteScalar();
            }
        }
        #endregion

#region 批量插入
        public static bool BulkInsert(DataTable dataTable, string tableName)
        {
            bool b = true;
            try
            {
                using (SqlConnection connection = new SqlConnection(connString))
                {
                    connection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        bulkCopy.DestinationTableName = tableName;
                        bulkCopy.WriteToServer(dataTable);
                    }
                }
            }
            catch (Exception ex)
            {
                b = false;
            }
            return b;
        }
        #endregion
    }
}

```
调用：
```
           DataTable dt=SqlHelper.GetTable("h5_test", CommandType.StoredProcedure,
                        new SqlParameter("@ids", "1,2,3,4"));
            Console.WriteLine(dt.Rows[0][0]);
            Console.ReadKey();
```
函数：
```
USE [test]
GO
/****** Object:  UserDefinedFunction [dbo].[tableListSplit]    Script Date: 2020/4/7 10:34:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:	<Description,-根据useridx,useridx组合的字符串分割为表,>
-- =============================================
ALTER  function [dbo].[tableListSplit](@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date    :2005-4-20
--Author :Domino
as 
begin
    declare @i int
    set @SourceSql=rtrim(ltrim(@SourceSql))
    set @i=charindex(@StrSeprate,@SourceSql)
    while @i>=1
    begin
        insert @temp values(left(@SourceSql,@i-1))
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
        set @i=charindex(@StrSeprate,@SourceSql)
    end
    if @SourceSql<>'\'
       insert @temp values(@SourceSql)
    return 
end
```
存储过程：
```
USE [test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:	测试
-- =============================================
Create PROCEDURE [dbo].[h5_test]
	@ids varchar(2000)=''
AS

BEGIN

DECLARE  @fillid TABLE(
		id INT 
	)
	INSERT INTO @fillid( id )SELECT a  AS id  FROM dbo.tableListSplit(@ids,',')
	select * from @fillid
	
END

```

评论区

先去登录

C#Sql Server的sqlHelper

相关推荐

评论区