using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace JHDAL
{
public static class DBHelper
{
public static SqlConnection connection;
public static SqlConnection Connection
{
get
{
if (connection == null)
{
//string connectionString = ConfigurationManager.ConnectionStrings["MyBookShop"].ConnectionString;
string strConn = @"Data Source=.;Initial Catalog=MYBOOKSHOP;Integrated Security=True; pwd=AbcXyz123";
// string strConn = @"Data Source=MICROSOF-E36F55;integrated security=sspi;database=MyBookShop";
connection = new SqlConnection(strConn); connection.Open();
}
else if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == ConnectionState.Broken)
{
connection.Close(); connection.Open();
}
return connection;
}
}
/// <summary>
/// 执行增,删,改,的方法
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="para"></param>
/// <returns></returns>
public static int ExecuteCommand(string commandText, CommandType commandType, SqlParameter[] para)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType;
try
{
if (para != null)
{
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
/// <summary>
/// 执行查询的方法
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="para"></param>
/// <returns></returns>
public static DataTable GetDataTable(string commandText, CommandType commandType, SqlParameter[] para)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType;
try
{
if (para != null)
{
cmd.Parameters.AddRange(para);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable temp = new DataTable();
da.Fill(temp); return temp;
}
finally
{
connection.Close();
}
}
/// <summary>
/// 执行少量查询的方法
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="para"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string commandText, CommandType commandType, SqlParameter[] para)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType;
if (para != null)
{
cmd.Parameters.AddRange(para);
} return cmd.ExecuteReader();
}
/// <summary>
/// 执行聚合函数的方法
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="para"></param>
/// <returns></returns>
public static object GetScalar(string commandText, CommandType commandType, SqlParameter[] para)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType;
try
{
if (para != null)
{
cmd.Parameters.AddRange(para);
} return cmd.ExecuteScalar();
}
finally
{
connection.Close();
}
}
}
}