SQL操作助手类库-优快云博客

本文介绍了一个用于简化SQL操作的C#类库，包括执行SQL命令、读取数据、获取数据表及执行标量查询等功能，并实现了事务处理，确保数据的一致性和安全性。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;

namespace SqlHelper
{
    public class DataHelper
    {
        public static string ConnectionStrings = ConfigurationManager.ConnectionStrings["ConnectionStrings"].ConnectionString;

        public static int ExecuteNonQuery(string sql, params SqlParameter[] Params)
        {
            int Result = 0;
            using (SqlConnection conn = new SqlConnection(ConnectionStrings))
            {
                if (conn.State != ConnectionState .Open)
                {
                    conn.Open();
                    SqlTransaction Tran = conn.BeginTransaction(); //开始事务
                    SqlCommand cmd = new SqlCommand(sql, conn, Tran);
                    if (Params != null)
                    {
                        cmd.Parameters.Add(Params);

                        try
                        {
                             Result = cmd.ExecuteNonQuery();
                            Tran.Commit();
                            cmd.Parameters.Clear();
                            return Result;
                        }
                        catch
                        {
                            Result = 0;
                            Tran.Rollback();
                            conn.Close();
                        }
                    }
                }
            }

return Result;
}

        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] Param)
        {
            SqlDataReader dr = null;
            SqlConnection conn = new SqlConnection(ConnectionStrings);
            SqlCommand cmd = new SqlCommand(sql, conn);

            if (Param != null)
            {
                cmd.Parameters.Add(Param);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                try
                {
                     dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    cmd.Parameters.Clear();
                    return dr;
                }
                catch
                {
                    conn.Close();
                    dr = null;
                }
            }
            return dr;
        }

        public static DataTable GetDataTable(string sql, params SqlParameter[] Param)
        {
            SqlConnection conn = new SqlConnection(ConnectionStrings);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            if (Param != null)
            {
                da.SelectCommand.Parameters.AddRange(Param);

                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                da.Fill(dt);
                conn.Close();
            }
            return dt;
        }

public static Object ExecuteScalar(string sql, params SqlParameter[] Param)
{

            using (SqlConnection conn = new SqlConnection(ConnectionStrings))
            {
                object Result = null;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlTransaction Tran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand(sql, conn, Tran);
                if (Param != null)
                {
                    cmd.Parameters.AddRange(Param);
                    try
                    {
                        Result = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        Tran.Commit();

                    }
                    catch
                    {
                        Tran.Rollback();
                        conn.Close();
                    }
                }

                return Result;
            }
        }

        public static SqlParameter GetParameter(string ParaName, SqlDbType paramType, object ParamValue)
        {
            SqlParameter param = new SqlParameter(ParaName, paramType);
            param.Value = ParamValue;
            return param;
        }
    }
}

转载于:https://www.cnblogs.com/daiweixm/archive/2009/08/18/1549141.html