sqlhelper.cs

这是一个很好的图书管理系统 数据库SQL2005

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
//５１aspx
namespace DAL
{
    public class SqlHelper
    {
        public static DataTable Select(SqlParameter[] _SqlParameter, string _CommandText)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = SqlConnStr.ReturnConnStr();
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = new SqlCommand();
            sda.SelectCommand.Connection = conn;
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            sda.SelectCommand.CommandText = _CommandText;
            sda.SelectCommand.Parameters.AddRange(_SqlParameter);
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                sda.Fill(dt);
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception("检索数据发生错误", ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return dt;
        }

        public static DataTable Select(string _CommandText)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = SqlConnStr.ReturnConnStr();
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = new SqlCommand();
            sda.SelectCommand.Connection = conn;
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            sda.SelectCommand.CommandText = _CommandText;
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                sda.Fill(dt);
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception("检索数据发生错误", ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return dt;
        }

        public static int Insert(SqlParameter[] _SqlParameter, string _CommandText)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = SqlConnStr.ReturnConnStr();
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.InsertCommand = new SqlCommand();
            sda.InsertCommand.Connection = conn;
            sda.InsertCommand.CommandType = CommandType.StoredProcedure;
            sda.InsertCommand.CommandText = _CommandText;
            sda.InsertCommand.Parameters.AddRange(_SqlParameter);
            int row = 0;
            try
            {
                conn.Open();
                row = sda.InsertCommand.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception("插入数据失败", ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return row;
        }

        public static int Delete(SqlParameter[] _SqlParameter, string _CommandText)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = SqlConnStr.ReturnConnStr();
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.DeleteCommand = new SqlCommand();
            sda.DeleteCommand.Connection = conn;
            sda.DeleteCommand.CommandType = CommandType.StoredProcedure;
            sda.DeleteCommand.CommandText = _CommandText;
            sda.DeleteCommand.Parameters.AddRange(_SqlParameter);
            int row = 0;
            try
            {
                conn.Open();
                row = sda.DeleteCommand.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception("删除数据失败", ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return row;
        }

        public static int Update(SqlParameter[] _SqlParameter, string _CommandText)
        {
            SqlConnection conn = new SqlConnection(DAL.SqlConnStr.ReturnConnStr());
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.UpdateCommand = new SqlCommand();
            sda.UpdateCommand.Connection = conn;
            sda.UpdateCommand.CommandText = _CommandText;
            sda.UpdateCommand.CommandType = CommandType.StoredProcedure;
            sda.UpdateCommand.Parameters.AddRange(_SqlParameter);
            int row = 0;
            try
            {
                conn.Open();
                row = sda.UpdateCommand.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception("更新数据出错", ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return row;
        }
    }
}