DBProc

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;

namespace WebApplication1
{
public class DbHelperProc
{
public static string connectionString = “Data Source=.;Initial Catalog=wsyh;Integrated Security=True”; //ConfigurationManager.ConnectionStrings[“ConnectionString”].ConnectionString;
///
/// 调用insert 、update 、 delete 存储过程
///
///
///
///
public static int ExecProc(SqlParameter[] parameter, string procName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = procName;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameter);
connection.Open();
return command.ExecuteNonQuery();
}
}

    /// <summary>
    /// 调用select 存储过程
    /// </summary>
    /// <param name="procName"></param>
    /// <returns></returns>
    public static DataTable ExecQueryProc(string procName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            DataTable ds = new DataTable("dt");
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = procName;

            SqlDataAdapter sda = new SqlDataAdapter(command);
            sda.Fill(ds);
            return ds;
        }
    }
    /// <summary>
    /// 有参数查询
    /// </summary>
    /// <param name="parameter"></param>
    /// <param name="procName"></param>
    /// <returns></returns>
    public static DataTable ExecQueryParmsProc(SqlParameter[] parameter, string procName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            DataTable ds = new DataTable("dt");
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = procName;
            command.Parameters.AddRange(parameter);
            SqlDataAdapter sda = new SqlDataAdapter(command);
            sda.Fill(ds);
            return ds;
        }
    }
    /// <summary>
    /// 分页显示数据
    /// </summary>
    /// <param name="PageIndex"></param>
    /// <param name="PageSize"></param>
    /// <param name="talCount"></param>
    /// <param name="name"></param>
    /// <param name="ProName"></param>
    /// <returns></returns>
    public static DataTable ExecuteDataTable(CommandType type, string safeSql, params SqlParameter[] values)
    {
        using (SqlConnection Connection = new SqlConnection(connectionString))
        {

            if (Connection.State != ConnectionState.Open)
                Connection.Open();
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            cmd.CommandType = type;
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            cmd.Parameters.Clear();
            return ds.Tables[0];
        }
    }

    /// <summary>
    /// 执行select 语句
    /// </summary>
    /// <param name="SQLString"></param>
    /// <returns></returns>
    public static DataTable Query(string SQLString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            DataTable ds = new DataTable("com");
            try
            {
                connection.Open();
                SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                command.Fill(ds);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
    }

    /// <summary>
    /// 执行SQL语句返回影响记录的条数
    /// </summary>
    /// <param name="SQLString"></param>
    /// <returns></returns>
    public static int ExecuteSql(string SQLString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(SQLString, connection))
            {
                try
                {
                    connection.Open();
                    int rows = command.ExecuteNonQuery();
                    return rows;
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
            }
        }
    }


}

}