datalayerhelper.cs

来自「This is a datalayer helper class」· CS 代码 · 共 82 行

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

namespace dpant
{

    /// <summary>
    /// Summary description for DataLayerHelper
    /// DataLayerHelper is a general purpose implementation of an SQL data layer class.
    /// Written by [e-mail], 13/10/2007
    /// </summary>
    public static class DataLayerHelper
    {
        public static DataTable Get(string sqlSelect)
        {
            // Query the database and return the rowset.
            using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand(sqlSelect, cnn);
                DataTable tbl = new DataTable();
                tbl.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
        }

        public static DataTable GetById(string sqlSelect, string IDName, string IDValue)
        {
            // Query the database and return only 1 row with the matching ID value.
            using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand(sqlSelect, cnn);
                cmd.Parameters.Add(IDName, SqlDbType.Int).Value = IDValue;
                DataTable tbl = new DataTable();
                tbl.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
        }

        public static object ExecuteScalar(string sqlSelect)
        {
            // Query the database and return a single col value.
            using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand(sqlSelect, cnn);
                return cmd.ExecuteScalar();
            }
        }

        public static int ExecuteNonQuery(string cmdText, SqlParameter param)
        {
            // Execute the sql command against the database and return the number of rows affected.
            // It accepts only one paramater.
            using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand(cmdText, cnn);
                cmd.Parameters.Add(param);
                return cmd.ExecuteNonQuery();
            }
        }

        public static int ExecuteNonQuery(string cmdText, ref SqlParameter[] sqlParams)
        {
            // Execute the sql command against the database and return the number of rows affected.
            // It accepts multiple parameters. 
            // Parameters may be INPUT or OUTPUT.
            using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand(cmdText, cnn);
                cmd.Parameters.AddRange(sqlParams);
                return cmd.ExecuteNonQuery();
            }
        }
    }

}