using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace RPMO.DAL
{
public class DataBases
{
//獲取數(shù)據(jù)庫(kù)連接字符串
static string str = System.Configuration.ConfigurationManager.ConnectionStrings["SharePoint_RPMO_SiteConnectionString"].ToString();
//static string str = "Data Source=10.12.17.32;Initial Catalog=issuetracker;uid=sa;pwd=hisoft;";
//創(chuàng)建連接字符串對(duì)象
SqlConnection con = new SqlConnection(str);
//創(chuàng)建命令對(duì)象
SqlCommand cmd = null;
//創(chuàng)建連接對(duì)象
SqlDataAdapter da = null;
DataSet ds = null;
//創(chuàng)建一個(gè)表對(duì)象
DataTable dt = null;
//SqlParameter[] values = null;
//string sql = null;
#region 具體數(shù)據(jù)查詢的方法
/// <summary>
/// 調(diào)用方法查詢一個(gè)或多個(gè)表的信息，并返回DataSet類(lèi)型值的方法
/// </summary>
/// <param name="sql">sql語(yǔ)句或存儲(chǔ)過(guò)程名</param>
/// <param name="re">是否是存儲(chǔ)過(guò)程</param>
/// <param name="values">參數(shù)</param>
/// <returns>返回DataSet</returns>
public DataSet GetDataSet(string sql, bool re, params SqlParameter[] values)
{
ds = new DataSet();
da = new SqlDataAdapter(sql, con);
if (re)
{
//定義命令類(lèi)型
da.SelectCommand.CommandType = CommandType.StoredProcedure;
}//if (re)//判斷命令類(lèi)型
if (values != null)
{
//添加參數(shù)
da.SelectCommand.Parameters.AddRange(values);
}//if (values != null)//判斷參數(shù)是否存在
try
{
da.Fill(ds);
}//try
catch (SqlException ex)
{
//寫(xiě)錯(cuò)誤日志
}//try catch(SqlException ex)捕獲數(shù)據(jù)庫(kù)異常
finally
{
if (con.State == ConnectionState.Open)
{
//關(guān)閉連接
con.Close();
}//if (con.State == ConnectionState.Open)關(guān)閉連接
}//try catch(SqlException ex) finally
return ds;
}//public DataSet GetDataSet(string sql, bool re, params SqlParameter[] values)
/// <summary>
/// 調(diào)用方法查詢單個(gè)表的信息，并返回DataTable類(lèi)型值的方法
/// </summary>
/// <param name="sql">sql語(yǔ)句或存儲(chǔ)過(guò)程名</param>
/// <param name="re">是否是存儲(chǔ)過(guò)程</param>
/// <param name="values">參數(shù)</param>
/// <returns>返回DataTable</returns>
public DataTable GetDataTable(string sql, bool re, params SqlParameter[] values)
{
dt = new DataTable();
da = new SqlDataAdapter(sql, con);
if (re)
{
//定義命令類(lèi)型
da.SelectCommand.CommandType = CommandType.StoredProcedure;
}//if (re)
if (values != null)
{
//添加參數(shù)
da.SelectCommand.Parameters.AddRange(values);
}//if (values != null)
try
{
da.Fill(dt);
}//try
catch (SqlException ex)
{
//寫(xiě)錯(cuò)誤日志
}//try catch(SqlException ex)
finally
{
if (con.State == ConnectionState.Open)
{
//關(guān)閉連接
con.Close();
}//if (con.State == ConnectionState.Open)
}//try catch(SqlException ex) finally
return dt;
}//public DataTable GetDataTable(string sql, bool re, params SqlParameter[] values)
/// <summary>
/// 調(diào)用方法查詢單個(gè)值，返回object類(lèi)型值的方法
/// </summary>
/// <param name="sql">sql語(yǔ)句或存儲(chǔ)過(guò)程名</param>
/// <param name="re">是否是存儲(chǔ)過(guò)程</param>
/// <param name="values">參數(shù)</param>
/// <returns>返回object</returns>
public object GetValue(string sql, bool re, params SqlParameter[] values)
{
object strReturn = null;
try
{
cmd = new SqlCommand(sql, con);
if (re)
{
//定義命令類(lèi)型
cmd.CommandType = CommandType.StoredProcedure;
}//if (re)
if (values != null)
{
//添加參數(shù)
cmd.Parameters.AddRange(values);
}//if (values != null)
if (con.State == ConnectionState.Closed)
{
//打開(kāi)連接
con.Open();
}//if(con.State == ConnectionState.Closed)
//讀取一行一列的數(shù)據(jù)
strReturn = cmd.ExecuteScalar();
//如果返回值是數(shù)據(jù)庫(kù)空值類(lèi)型
if (strReturn == DBNull.Value)
{
strReturn = null;
}
}//try
catch (SqlException ex)
{
//寫(xiě)錯(cuò)誤日志
}//try catch(SqlException ex)
finally
{
if (con.State == ConnectionState.Open)
{
//關(guān)閉連接
con.Close();
}//if (con.State == ConnectionState.Open)
}//try catch(SqlException ex) finally
return strReturn;
}//public object GetValue(string sql, bool re, params SqlParameter[] values)
/// <summary>
/// 調(diào)用方法更新數(shù)據(jù)庫(kù)，并返回受影響行數(shù)
/// </summary>
/// <param name="sql">sql語(yǔ)句或存儲(chǔ)過(guò)程名</param>
/// <param name="re">是否是存儲(chǔ)過(guò)程</param>
/// <param name="values">參數(shù)</param>
/// <returns>返回受影響行數(shù)</returns>
public int ProExecuteCommand(string sql, bool re, params SqlParameter[] values)
{
int count = 0;
cmd = new SqlCommand(sql, con);
if (re)
{
//定義命令類(lèi)型
cmd.CommandType = CommandType.StoredProcedure;
}//if(re)
if (values != null)
{
//向命令對(duì)象的參數(shù)集合里添加參數(shù)
cmd.Parameters.AddRange(values);
}//if(values != null)
try
{
if (con.State == ConnectionState.Closed)
{
//打開(kāi)連接
con.Open();
}//if(con.State == ConnectionState.Closed)
count = cmd.ExecuteNonQuery();// 執(zhí)行命令，返回受影響行數(shù)
}//try
catch (SqlException ex)
{
//寫(xiě)錯(cuò)誤日志
}//try catch(SqlException ex)
finally
{
if (con.State == ConnectionState.Open)
{
//關(guān)閉連接
con.Close();
}//if (con.State == ConnectionState.Open)
}//try catch(SqlException ex) finally
return count;
}//public int ProExecuteCommand(string sql, bool re, params SqlParameter[] values)
#endregion
}
}
