非常不錯的數(shù)據(jù)庫操作通用類，原創(chuàng)GMM。轉(zhuǎn)過來備份，經(jīng)常會用到
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
/// <summary>
///針對sql server數(shù)據(jù)庫操作的通用類
/// </summary>
public class SqlHelper
{
public SqlHelper()
{
//
//TODO: 在此處添加構(gòu)造函數(shù)邏輯
//
connectionstring = ConfigurationManager.ConnectionStrings["MyDataBaseConStr"].ConnectionString;
}
private string connectionstring;
/// <summary>
/// 設(shè)置連接字符串
/// </summary>
public string Connectionstring
{
set { connectionstring = value; }
}
/// <summary>
/// 構(gòu)造函數(shù)
/// </summary>
/// <param name="connectionstring">數(shù)據(jù)庫連接字符串</param>
/// <returns></returns>
public SqlHelper(string connectionstring)
{
this.connectionstring = connectionstring;
}
/// <summary>
/// 執(zhí)行一個查詢，并返回結(jié)果集
/// </summary>
/// <param name="sql">要執(zhí)行的查詢sql文本命令</param>
/// <returns>返回查詢結(jié)果集</returns>
public DataTable ExecuteDataTable(string sql)
{
return ExecuteDataTable(sql, CommandType.Text, null);
}
/// <summary>
/// 執(zhí)行一個查詢，并返回查詢結(jié)果
/// </summary>
/// <param name="sql">要執(zhí)行的sql語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或sql文本命令</param>
/// <returns>返回查詢結(jié)果集</returns>
public DataTable ExecuteDataTable(string sql, CommandType commandType)
{
return ExecuteDataTable(sql, commandType, null);
}
/// <summary>
/// 執(zhí)行一個查詢，并且返回查詢結(jié)果
/// </summary>
/// <param name="sql">要執(zhí)行的sql語句</param>
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或sql文本命令</param>
/// <param name="parameters">Transact-SQL語句或存儲過程的參數(shù)數(shù)組</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)
{
DataTable data = new DataTable();//實例化datatable，用于裝載查詢結(jié)果集
using (SqlConnection connection = new SqlConnection(connectionstring))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;//設(shè)置command的commandtype為制定的commandtype
//如果同時傳入的參數(shù)，則添加這些參數(shù)
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameters);
}
}
//通過包含查詢SQL的sqlcommand實例來實例化sqldataadapter
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(data);//填充DataTable
}
}
return data;
}
/// <summary>
/// 返回一個sqldatareader對象的實例
/// </summary>
/// <param name="sql">要執(zhí)行的查詢sql文本命令</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql)
{
return ExecuteReader(sql, CommandType.Text, null);
}
/// <summary>
/// 返回一個sqldatareader對象的實例
/// </summary>
/// <param name="sql">要執(zhí)行的查詢sql文本命令</param>
/// <param name="commandtype">要執(zhí)行的查詢語句的類型，如存儲過程或sql文本命令</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql, CommandType commandtype)
{
return ExecuteReader(sql, commandtype, null);
}
public SqlDataReader ExecuteReader(string sql, CommandType commandtype, SqlParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionstring);
SqlCommand command = new SqlCommand(sql, connection);
//如果傳入了參數(shù)，則添加這些參數(shù)
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameters);
}
}
connection.Open();
//CommandBehavior .CloseConnection參數(shù)指示關(guān)閉Reader對象時關(guān)閉與其關(guān)聯(lián)的connection對象
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 執(zhí)行一個查詢，返回查詢結(jié)果集的第一行，忽略其他的行和列
/// </summary>
/// <param name="sql">要執(zhí)行的查詢sql文本命令</param>
/// <returns></returns>
public Object ExecuteScalar(string sql)
{
return ExecuteScalar(sql, CommandType.Text, null);
}
/// <summary>
/// 執(zhí)行一個查詢，返回查詢結(jié)果集的第一行，忽略其他的行和列
/// </summary>
/// <param name="sql">要執(zhí)行的SQL語句</param>
/// <param name="commandtype">要執(zhí)行的查詢語句的類型，如存儲過程或sql文本命令</param>
/// <returns></returns>
public Object ExecuteScalar(string sql, CommandType commandtype)
{
return ExecuteScalar(sql, CommandType.Text, null);
}
public Object ExecuteScalar(string sql, CommandType commandtype, SqlParameter[] parameter)
{
object result = null;
using (SqlConnection connection = new SqlConnection(connectionstring))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandtype;//設(shè)置command的commandtype為指定的commandtype
//如果同時傳入了參數(shù)，則添加這些參數(shù)
if (parameter != null)
{
foreach (SqlParameter parameters in parameter)
{
command.Parameters.Add(parameter);
}
}
connection.Open();//打開數(shù)據(jù)庫
result = command.ExecuteScalar();
}
}
return result;//返回查詢結(jié)果的第一行和第一列，忽略其他行和列
}
/// <summary>
/// 對數(shù)據(jù)庫實行增刪改操作
/// </summary>
/// <param name="sql">要執(zhí)行的查詢Sql文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql, CommandType.Text, null);
}
/// <summary>
/// 對數(shù)據(jù)庫實行增刪改操作
/// </summary>
/// <param name="sql">要執(zhí)行的查詢Sql文本命令</param>
/// <param name="commandtype">要執(zhí)行的查詢語句的類型，如存儲過程或sql文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType commandtype)
{
return ExecuteNonQuery(sql, CommandType.Text, null);
}
/// <summary>
/// 對數(shù)據(jù)庫實行增刪改操作
/// </summary>
/// <param name="sql">要執(zhí)行的查詢Sql文本命令</param>
/// <param name="commandtype">要執(zhí)行的查詢語句的類型，如存儲過程或sql文本命令</param>
/// <param name="parameters">要執(zhí)行的查詢語句的類型，如存儲過程或sql文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType commandtype, SqlParameter[] parameters)
{
int count = 0;
using (SqlConnection connection = new SqlConnection(connectionstring))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandtype;//設(shè)置command的commandtype為制定commandtype
//如果同時傳入了參數(shù)，則添加這些參數(shù)
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();//打開數(shù)據(jù)庫連接
count = command.ExecuteNonQuery();
}
}
return count;//執(zhí)行增刪改查操作后，返回數(shù)據(jù)庫中受影響的行數(shù)
}
/// <summary>
/// 返回當(dāng)前連接的數(shù)據(jù)庫中所有由用戶創(chuàng)建的數(shù)據(jù)庫
/// </summary>
/// <returns></returns>
public DataTable GetTables()
{
DataTable data = null;
using (SqlConnection connection = new SqlConnection(connectionstring))
{
connection.Open();//打開數(shù)據(jù)庫連接
data = connection.GetSchema("Tables");
}
return data;
}
}
