using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
namespace Solog.SQLTask
...{
public class GetData
...{
private SqlConnection _myConnection;
public SqlConnection myConnection
...{
get ...{ return _myConnection; }
}
public GetData()
...{
_myConnection = new SqlConnection(ConfigurationManager.AppSettings["SQLConnectionString"]);
_myConnection.Open();
}
//获取DataSet
public DataSet GetDataSet(string sql)
...{
try
...{
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sql, _myConnection);
sda.Fill(ds);
return ds;
}
catch (Exception ex)
...{
throw new Exception(ex.Message);
}
finally
...{
Close();
}
}
//重载GetDataSet
public DataSet GetDataSet(string sql, Parameters para)
...{
DataSet ds = new DataSet();
if (para != null)
...{
try
...{
SqlCommand sc = new SqlCommand(sql, _myConnection);
foreach (ParameterString pstring in para.List)
...{
sc.Parameters.AddWithValue(pstring.Parameter, pstring.Value);
}
SqlDataAdapter sda = new SqlDataAdapter(sc);
sda.Fill(ds);
return ds;
}
catch (Exception ex)
...{
throw new Exception(ex.Message);
}
finally
...{
Close();
}
}
else
...{
ds = GetDataSet(sql);
}
return ds;
}
/**//// <summary>
/// 选择使用StoreProcedure返回DataSet操作类,如果无参数请把para=null
/// </summary>
/// <param name="query">SQL语句或存储过程名称</param>
/// <param name="para">SQL参数,无参查询为null</param>
/// <param name="IsStoreProcedure">是否使用存储过程</param>
/// <returns></returns>
public DataSet GetDataSet(string query, Parameters para, bool IsStoreProcedure)
...{
DataSet ds = new DataSet();
if (IsStoreProcedure == false)
...{
ds = GetDataSet(query, para);
}
else
...{
SqlDataAdapter sda = new SqlDataAdapter(query, _myConnection);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
if (para != null)
...{
foreach (ParameterString pstring in para.List)
...{
sda.SelectCommand.Parameters.AddWithValue(pstring.Parameter, pstring.Value);
}
}
else
...{
}
sda.Fill(ds);
Close();
}
return ds;
}
//获取DataReader
public SqlDataReader GetReader(string sqlstring)
...{
//获取IDataReader
SqlDataReader datareader = null;
SqlCommand dbcmd = new SqlCommand(sqlstring,_myConnection);
try
...{
datareader = dbcmd.ExecuteReader(CommandBehavior.CloseConnection);
return datareader;
}
catch (Exception e)
...{
throw new Exception(e.Message);
}
}
//重载GetReader
public SqlDataReader GetReader(string sqlstring, Parameters para)
...{
SqlDataReader datareader = null;
SqlCommand dbcmd = new SqlCommand(sqlstring, _myConnection);
if (para != null)
...{
try
...{
foreach (ParameterString pstring in para.List)
...{
dbcmd.Parameters.AddWithValue(pstring.Parameter, pstring.Value);
}
datareader = dbcmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
...{
throw new Exception(e.Message);
}
}
else
...{
datareader= GetReader(sqlstring);
}
return datareader;
}
//重载GetReader
/**//// <summary>
/// 选择使用StoreProcedure返回DataReader操作类,如果无参数请把para=null
/// </summary>
/// <param name="query">SQL语句或存储过程名称</param>
/// <param name="para">SQL参数,无参查询为null</param>
/// <param name="IsStoreProcedure">是否使用存储过程</param>
/// <returns></returns>
public SqlDataReader GetReader(string query, Parameters para, bool IsStoreProcedure)
...{
SqlDataReader reader = null;
if (IsStoreProcedure == false)
...{
reader=GetReader(query, para);
}
else
...{
SqlCommand cmd = new SqlCommand(query, _myConnection);
cmd.CommandType = CommandType.StoredProcedure;
if (para != null)
...{
foreach (ParameterString pstring in para.List)
...{
cmd.Parameters.AddWithValue(pstring.Parameter, pstring.Value);
}
}
else
...{
}
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
return reader;
}
public void excNonQuery(string sqlstring)
...{
//执行一个无返回值的COMMADND
SqlCommand exc = new SqlCommand(sqlstring, _myConnection);
exc.ExecuteNonQuery();
exc.Connection.Close();
Close();
}
public void excNonQuery(string sqlstring, Parameters para)
...{
SqlCommand exc = new SqlCommand(sqlstring,_myConnection);
if (para != null)
...{
foreach (ParameterString pstring in para.List)
...{
exc.Parameters.AddWithValue(pstring.Parameter, pstring.Value);
}
exc.ExecuteNonQuery();
Close();
}
else
...{
exc.ExecuteNonQuery();
Close();
}
}
//重载excNonQuery
/**//// <summary>
/// 执行无返回值的SQL存储过程或SQL语句
/// </summary>
/// <param name="query">SQL语句或存储过程名称</param>
/// <param name="IsStoreProdure"></param>
public void excNonQuery(string query,Parameters para,bool IsStoreProdure)
...{
if (IsStoreProdure == false)
...{
excNonQuery(query,para);
}
else
...{
SqlCommand cmd = new SqlCommand(query, _myConnection);
cmd.CommandType = CommandType.StoredProcedure;
if (para != null)
...{
foreach (ParameterString pstring in para.List)
...{
cmd.Parameters.AddWithValue(pstring.Parameter, pstring.Value);
}
cmd.ExecuteNonQuery();
Close();
}
else
...{
cmd.ExecuteNonQuery();
Close();
}
}
}
public int excScalar(string sqlstring)
...{
//执行返回单一INT值
SqlCommand exc = new SqlCommand(sqlstring, _myConnection);
int result = Convert.ToInt32(exc.ExecuteScalar());
exc.Connection.Close();
return result;
}
public int excScalar(string sqlstring, Parameters para)
...{
SqlCommand exc = new SqlCommand(sqlstring, _myConnection);
int result;
if (para != null)
...{
foreach (ParameterString pstring in para.List)
...{
exc.Parameters.AddWithValue(pstring.Parameter, pstring.Value);
}
result = Convert.ToInt32(exc.ExecuteScalar());
}
else
...{
result = Convert.ToInt32(exc.ExecuteScalar());
}
return result;
}
/**//// <summary>
/// 选择使用StoreProcedure返回Int值操作类,如果无参数请把para=null
/// </summary>
/// <param name="query">SQL语句或存储过程名称</param>
/// <param name="para">SQL参数,无参查询为null</param>
/// <param name="IsStoreProcedure">是否使用存储过程</param>
/// <returns></returns>
public int excScalar(string query, Parameters para, bool IsStoreProdure)
...{
int result;
if (IsStoreProdure == false)
...{
result = excScalar(query,para);
}
else
...{
SqlCommand cmd = new SqlCommand(query, _myConnection);
cmd.CommandType = CommandType.StoredProcedure;
if (para != null)
...{
foreach (ParameterString pstring in para.List)
...{
cmd.Parameters.AddWithValue(pstring.Parameter, pstring.Value);
}
result = Convert.ToInt32(cmd.ExecuteScalar());
}
else
...{
result = Convert.ToInt32(cmd.ExecuteScalar());
}
}
Close();
return result;
}
public void Close()
...{
//用完记得关掉
_myConnection.Close();
}
}
}
本文转载:
CSDN博客