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;
}
}
}
}
}
}