using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace MyCommunityDAL
{
public static class GetConnection
{
private static SqlConnection _connection;
/// <summary>
/// 获取数据库联接桥
/// </summary>
public static SqlConnection connection {
get {
string connectionString = "Data Source=.;Initial Catalog=mycommunity;Integrated Security=True";
if (_connection == null) {
_connection = new SqlConnection(connectionString);
_connection.Open();
} if (_connection.State == ConnectionState.Closed) {
_connection.Open();
} if (_connection.State == ConnectionState.Broken) {
_connection.Close();
_connection.Open();
}
return _connection;
}
}
/// <summary>
/// 参数化sql语句,执行非查询
/// </summary>
/// <param name="strSql"></param>
/// <param name="pars"></param>
/// <returns></returns>
public static int NotExecute(string strSql, SqlParameter[] pars) {
SqlCommand comm = new SqlCommand(strSql,connection);
comm.Parameters.AddRange(pars);
int row = comm.ExecuteNonQuery();
return row;
}
/// <summary>
/// 参数化sql语句,执行查询,返回一个DataTable
/// </summary>
/// <param name="sql"></param>
/// <param name="pars"></param>
/// <returns></returns>
public static DataTable GetDataTable(string strSql, SqlParameter[] pars) {
SqlCommand comm = new SqlCommand(strSql, connection);
DataTable dt = new DataTable();
comm.Parameters.AddRange(pars);
SqlDataAdapter sda = new SqlDataAdapter(comm);
sda.Fill(dt);
return dt;
}
/// <summary>
/// 参数化sql语句,执行查询,返回一个DataReader
/// </summary>
/// <param name="strSql"></param>
/// <param name="prams"></param>
/// <returns></returns>
public static SqlDataReader GetDataReader(string strSql, SqlParameter[] prams)
{
SqlCommand comm = new SqlCommand(strSql, connection);
comm.Parameters.AddRange(prams);
return comm.ExecuteReader();
}
/// <summary>
/// 执行有参数的存储过程的方法,返回一个DataTable
/// </summary>
/// <param name="sql"></param>
/// <param name="pars"></param>
/// <returns></returns>
public static DataTable GetDataTableByProc(string storeName, SqlParameter[] pars)
{
SqlCommand comm = new SqlCommand(storeName, Connection);
DataTable dt = new DataTable();
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddRange(pars);
SqlDataAdapter sda = new SqlDataAdapter(comm);
sda.Fill(dt);
return dt;
}
// 执行无参数的存储过程的方法,返回类型为DataTable
public static DataTable GetDataTableByProc(string storeName)
{
SqlCommand comm = new SqlCommand(storeName, Connection);
DataTable dt = new DataTable();
comm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(comm);
sda.Fill(dt);
return dt;
}
}
}