sql DBHelder类-优快云博客

本文介绍了一个用于简化SQL操作的实用工具类SQLHelper。该类提供了执行SQL命令、获取数据表及读取数据等功能的方法实现，并详细展示了如何通过连接字符串、命令类型等参数配置来执行SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
/// <summary>
/// SQLHelper 的摘要说明
/// </summary>
namespace YHTDBUnititly
{
    public abstract class SQLHelper
    {
        public static readonly string sqlconstring = ConfigurationManager.ConnectionStrings["sqlconstring"].ConnectionString;

public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, SqlParameter [] cmdParas)
{

            SqlCommand cmd = new SqlCommand();
            using (SqlConnection con = new SqlConnection(connectionString))
            {

                PrepareCommand(cmd, con, null, cmdType, cmdText, cmdParas);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;

}

        }
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand();
            da.SelectCommand = cmd;
            using (SqlConnection con = new SqlConnection(sqlconstring))
            {
                cmd.Connection = con;
                cmd.CommandText = sql;

                if (param!=null)
                {
                    foreach (SqlParameter p in param)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                try
                {
                    da.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
                finally
                { }
            }
            return dt;
        }
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, SqlParameter[] cmdParas)
        {

            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {

                PrepareCommand(cmd, con, null, cmdType, cmdText, cmdParas);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;

            }
            catch (OleDbException ex)
            {

con.Close();
throw new Exception(ex.Message, ex);

            }
        }
        public static object ExecuteSclare(string connectionString, CommandType cmdType, string cmdText, SqlParameter[] cmdParas)
        {

            SqlCommand cmd = new SqlCommand();
            using (SqlConnection con = new SqlConnection(connectionString))
            {

PrepareCommand(cmd, con, null, cmdType, cmdText, cmdParas);

                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;

}

        }
        public static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParas)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;

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

//当定义cmdParas.Length< 0的时候,在调用该方法时，如果参数为空的话就会报错，错误为“调用的对象可能为空”,所以使用cmdParas!=null

if (cmdParas != null)
{

foreach (SqlParameter para in cmdParas)
{

cmd.Parameters.Add(para);

}

        public static bool ExecuteNonquery(string sql)
        {
            bool flag = false;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection con = new SqlConnection(sqlconstring))
            {
                cmd.Connection = con;
                cmd.CommandText = sql;
                try
                {
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                    int result = cmd.ExecuteNonQuery();
                    if (result > 0)
                        flag = true;
                    return flag;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
                finally
                {
                    con.Close();
                }
            }

        }
    }
}