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
{
//獲取數據庫連接字符串
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)建連接字符串對象
SqlConnection con = new SqlConnection(str);
//創(chuàng)建命令對象
SqlCommand cmd = null;
//創(chuàng)建連接對象
SqlDataAdapter da = null;
DataSet ds = null;
//創(chuàng)建一個表對象
DataTable dt = null;
//SqlParameter[] values = null;
//string sql = null;
#region 具體數據查詢的方法
/// <summary>
/// 調用方法查詢一個或多個表的信息，并返回DataSet類型值的方法
/// </summary>
/// <param name="sql">sql語句或存儲過程名</param>
/// <param name="re">是否是存儲過程</param>
/// <param name="values">參數</param>
/// <returns>返回DataSet</returns>
public DataSet GetDataSet(string sql, bool re, params SqlParameter[] values)
{
ds = new DataSet();
da = new SqlDataAdapter(sql, con);
if (re)
{
//定義命令類型
da.SelectCommand.CommandType = CommandType.StoredProcedure;
}//if (re)//判斷命令類型
if (values != null)
{
//添加參數
da.SelectCommand.Parameters.AddRange(values);
}//if (values != null)//判斷參數是否存在
try
{
da.Fill(ds);
}//try
catch (SqlException ex)
{
//寫錯誤日志
}//try catch(SqlException ex)捕獲數據庫異常
finally
{
if (con.State == ConnectionState.Open)
{
//關閉連接
con.Close();
}//if (con.State == ConnectionState.Open)關閉連接
}//try catch(SqlException ex) finally
return ds;
}//public DataSet GetDataSet(string sql, bool re, params SqlParameter[] values)
/// <summary>
/// 調用方法查詢單個表的信息,，并返回DataTable類型值的方法
/// </summary>
/// <param name="sql">sql語句或存儲過程名</param>
/// <param name="re">是否是存儲過程</param>
/// <param name="values">參數</param>
/// <returns>返回DataTable</returns>
public DataTable GetDataTable(string sql, bool re, params SqlParameter[] values)
{
dt = new DataTable();
da = new SqlDataAdapter(sql, con);
if (re)
{
//定義命令類型
da.SelectCommand.CommandType = CommandType.StoredProcedure;
}//if (re)
if (values != null)
{
//添加參數
da.SelectCommand.Parameters.AddRange(values);
}//if (values != null)
try
{
da.Fill(dt);
}//try
catch (SqlException ex)
{
//寫錯誤日志
}//try catch(SqlException ex)
finally
{
if (con.State == ConnectionState.Open)
{
//關閉連接
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>
/// 調用方法查詢單個值,，返回object類型值的方法
/// </summary>
/// <param name="sql">sql語句或存儲過程名</param>
/// <param name="re">是否是存儲過程</param>
/// <param name="values">參數</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)
{
//定義命令類型
cmd.CommandType = CommandType.StoredProcedure;
}//if (re)
if (values != null)
{
//添加參數
cmd.Parameters.AddRange(values);
}//if (values != null)
if (con.State == ConnectionState.Closed)
{
//打開連接
con.Open();
}//if(con.State == ConnectionState.Closed)
//讀取一行一列的數據
strReturn = cmd.ExecuteScalar();
//如果返回值是數據庫空值類型
if (strReturn == DBNull.Value)
{
strReturn = null;
}
}//try
catch (SqlException ex)
{
//寫錯誤日志
}//try catch(SqlException ex)
finally
{
if (con.State == ConnectionState.Open)
{
//關閉連接
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>
/// 調用方法更新數據庫,，并返回受影響行數
/// </summary>
/// <param name="sql">sql語句或存儲過程名</param>
/// <param name="re">是否是存儲過程</param>
/// <param name="values">參數</param>
/// <returns>返回受影響行數</returns>
public int ProExecuteCommand(string sql, bool re, params SqlParameter[] values)
{
int count = 0;
cmd = new SqlCommand(sql, con);
if (re)
{
//定義命令類型
cmd.CommandType = CommandType.StoredProcedure;
}//if(re)
if (values != null)
{
//向命令對象的參數集合里添加參數
cmd.Parameters.AddRange(values);
}//if(values != null)
try
{
if (con.State == ConnectionState.Closed)
{
//打開連接
con.Open();
}//if(con.State == ConnectionState.Closed)
count = cmd.ExecuteNonQuery();// 執(zhí)行命令，返回受影響行數
}//try
catch (SqlException ex)
{
//寫錯誤日志
}//try catch(SqlException ex)
finally
{
if (con.State == ConnectionState.Open)
{
//關閉連接
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
}
}
