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;";
//创建连接字符串对象
SqlConnection con = new SqlConnection(str);
//创建命令对象
SqlCommand cmd = null;
//创建连接对象
SqlDataAdapter da = null;
DataSet ds = null;
//创建一个表对象
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();// 执行命令，返回受影响行数
}//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
}
}
