C# 一个SqlHelper例子

最新推荐文章于 2021-03-12 00:45:16 发布

原创最新推荐文章于 2021-03-12 00:45:16 发布 · 1.7k 阅读

1 ·

CC 4.0 BY-SA版权

C#(1) 专栏收录该内容

158 篇文章

订阅专栏

本文详细介绍了SQLHelper类的基本属性和常用方法，包括执行SQL、获取DataReader、查询DataTable和DataSet、获取单个值等操作，并提供了错误处理机制。通过实例演示了如何在实际项目中灵活运用该类进行数据库操作。

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


using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;


/// <summary>
/// Summary description for SqlHelper
/// </summary>
public class SqlHelper : IDisposable
{
    #region 基本属性
    private string _lastError;


    public string LastError
    {
        get
        {
            return _lastError;
        }
        set
        {
            _lastError = value;
        }
    }




    public static string ConnectionString = ConfigurationSettings.AppSettings["DBConnectionString"];


    protected SqlConnection cnn;


    #endregion




    public SqlHelper()
    {
        this._lastError = string.Empty;
        try
        {
            cnn = new SqlConnection(ConnectionString);
            cnn.Open();
        }
        catch (Exception ex)
        {
            this._lastError = ex.Message;
            throw ex;
        }
    }






    ///执行sql
    public bool Execute(string sql)
    {
        try
        {
            this._lastError = string.Empty;
            using (SqlCommand cmd = new SqlCommand(sql.Trim(), cnn))
            {
                cmd.ExecuteNonQuery();
                return true;
            }
        }
        catch (Exception ex)
        {
            this._lastError = ex.Message;
            return false;
        }
    }




    ///执行,返回DataReader
    public SqlDataReader Reader(string sql)
    {
        try
        {
            this._lastError = string.Empty;
            using (SqlCommand cmd = new SqlCommand(sql.Trim(), cnn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                return dr;
            }
        }
        catch (Exception ex)
        {
            this._lastError = ex.Message;
            return null;
        }


    }


    ///执行sql,返回DataTable
    public DataTable Query(string sql)
    {
        this._lastError = string.Empty;


        DataTable dt = new DataTable();


        try
        {
            using (SqlDataAdapter da = new SqlDataAdapter(sql.Trim(), cnn))
            {
                da.Fill(dt);
                return dt;
            }
        }
        catch (Exception ex)
        {
            this._lastError = ex.Message;
            return null;
        }


    }




    ///执行sql,返回DataSet
    public DataSet Query(string sql, object inputNull)
    {
        this._lastError = string.Empty;


        DataSet ds = new DataSet();


        try
        {
            using (SqlDataAdapter da = new SqlDataAdapter(sql.Trim(), cnn))
            {
                da.Fill(ds);
                return ds;
            }
        }
        catch (Exception ex)
        {
            this._lastError = ex.Message;
            return null;
        }


    }




    ///执行sql,返回一串字符
    public string GetValue(string sql, int ColumnIndex)
    {
        try
        {
            this._lastError = string.Empty;


            using (SqlDataReader dr = this.Reader(sql))
            {
                if (dr.Read())
                {
                    return dr[ColumnIndex].ToString().Trim();
                }
                return string.Empty;
            }
        }
        catch (Exception ex)
        {
            this._lastError = ex.Message;
            return string.Empty;
        }
    }


    public string GetValue(string sql, string ColumnName)
    {
        try
        {
            this._lastError = string.Empty;


            using (SqlDataReader dr = this.Reader(sql))
            {
                if (dr.Read())
                {
                    return dr[ColumnName].ToString().Trim();
                }
                return string.Empty;
            }
        }
        catch (Exception ex)
        {
            this._lastError = ex.Message;
            return string.Empty;
        }
    }


    ~SqlHelper()
    {
        this.Dispose(false);
    }


    private bool disposed = false;


    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }


    private void Dispose(bool disposing)
    {
        if (!this.disposed && disposing)
        {
            try
            {
                cnn.Close();
                cnn.Dispose();
            }
            catch
            {
            }
        }
        disposed = true;
    }




}