#region 通用存储过程查询,返囬DataSet
/// <summary>
/// 查询数据
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="parameters">存储过程的参数</param>
/// <param name="tableName">表名</param>
/// <returns>返回DataSet</returns>
public DataSet SelectDS(string procName, SqlParameter[] parameters, string tableName)
{
DataSet ds = null;
try
{
con = new SqlConnection(conStr);
cmd = new SqlCommand();
cmd.Connection = con;
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
cmd.Parameters.Clear();
if (parameters != null)
{
foreach (SqlParameter par in parameters)
{
cmd.Parameters.Add(par);
}
}
ds = new DataSet();
da.Fill(ds, tableName);
con.Close();
}
catch (Exception ex)
{
throw new ApplicationException("取数据错误!", ex);
}
finally
{
}
return ds;
}
#endregion
#region 通用存储过程,进行增删改操作,返回int型
/// <summary>
/// 传入存储过程和参数,执行增删改
/// </summary>
/// <param name="procName">procName:存储过程名</param>
/// <param name="paras">paras:参数数组</param>
/// <returns>1:成功;-1:失败</returns>
public int RunSql(string procName, params SqlParameter[] paras)
{
int dt = -1;
try
{
con = new SqlConnection(conStr);
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
cmd.Connection = con;
if (paras != null)
{
foreach (SqlParameter sp in paras)
{
cmd.Parameters.Add(sp);
}
}
con.Open();
cmd.ExecuteNonQuery();
dt = 1;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
con.Close();
}
return dt;
}
#endregion
#region 通用存儲過程查詢,返囬DataReader類型
public SqlDataReader SelectDR(string procName, SqlParameter[] parameters)
{
SqlDataReader dr = null;
try
{
con = new SqlConnection(conStr);
cmd = new SqlCommand();
cmd.Connection = this.con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
cmd.Parameters.Clear();
if (parameters != null)
{
foreach (SqlParameter par in parameters)
{
cmd.Parameters.Add(par);
}
}
con.Open();
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
throw new ApplicationException("取数据错误!", ex);
}
return dr;
}
#endregion
#region 通用存儲過程查詢,返囬DataTable類型
/// <summary>
///函数输入存储过程,输出一个结果集(DATATABLE)
/// </summary>
/// <param name="procName">sql:传入的sql语句</param>
/// <param name="paras">paras:存储过程的参数数组</param>
/// <returns>返回DataTable</returns>
public DataTable GetTable(string procName, params SqlParameter[] paras)
{
DataTable dt = null;
try
{
con = new SqlConnection(conStr);
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
cmd.Connection = con;
ds = new DataSet();
if (paras != null)
{
foreach (SqlParameter sp in paras)
{
cmd.Parameters.Add(sp);
}
}
da = new SqlDataAdapter(cmd);
con.Open();
da.Fill(ds, procName);
dt = ds.Tables[procName];
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
con.Close();
}
return dt;
}
#endregion