using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
public class SQLHelper
{
//连接字符串
static string strConn = ConfigurationManager.ConnectionStrings["CnnhoRechargePlatformConnectionString"].ToString();
#region 执行查询,返回DataTable对象-----------------------
public static DataTable GetTable(string strSQL)
{
return GetTable(strSQL, null);
}
public static DataTable GetTable(string strSQL, SqlParameter[] pas)
{
return GetTable(strSQL, pas, CommandType.Text);
}
/// <summary>
/// 执行查询,返回DataTable对象
/// </summary>
/// <param name="strSQL">sql语句</param>
/// <param name="pas">参数数组</param>
/// <param name="cmdtype">Command类型</param>
/// <returns>DataTable对象</returns>
public static DataTable GetTable(string strSQL, SqlParameter[] pas, CommandType cmdtype)
{
DataTable dt = new DataTable(); ;
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
da.SelectCommand.CommandType = cmdtype;
if (pas != null)
{
da.SelectCommand.Parameters.AddRange(pas);
}
da.Fill(dt);
}
return dt;
}
#endregion
#region 执行查询,返回DataSet对象-------------------------
public static DataSet GetDataSet(string strSQL)
{
return GetDataSet(strSQL,null);
}
public static DataSet GetDataSet(string strSQL, SqlParameter[] pas)
{
return GetDataSet(strSQL,pas,CommandType.Text);
}
/// <summary>
/// 执行查询,返回DataSet对象
/// </summary>
/// <param name="strSQL">sql语句</param>
/// <param name="pas">参数数组</param>
/// <param name="cmdtype">Command类型</param>
/// <returns>DataSet对象</returns>
public static DataSet GetDataSet(string strSQL, SqlParameter[] pas, CommandType cmdtype)
{
DataSet dt = new DataSet(); ;
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
da.SelectCommand.CommandType = cmdtype;
if (pas != null)
{
da.SelectCommand.Parameters.AddRange(pas);
}
da.Fill(dt);
}
return dt;
}
#endregion
#region 执行非查询存储过程和SQL语句-----------------------------
public static int ExcuteProc(string ProcName)
{
return ExcuteSQL(ProcName, null, CommandType.StoredProcedure);
}
public static int ExcuteProc(string ProcName, SqlParameter[] pars)
{
return ExcuteSQL(ProcName, pars, CommandType.StoredProcedure);
}
public static int ExcuteSQL(string strSQL)
{
return ExcuteSQL(strSQL, null);
}
public static int ExcuteSQL(string strSQL, SqlParameter[] paras)
{
return ExcuteSQL(strSQL, paras, CommandType.Text);
}
/// 执行非查询存储过程和SQL语句
/// 增、删、改
/// </summary>
/// <param name="strSQL">要执行的SQL语句</param>
/// <param name="paras">参数列表,没有参数填入null</param>
/// <param name="cmdType">Command类型</param>
/// <returns>返回影响行数</returns>
public static int ExcuteSQL(string strSQL, SqlParameter[] paras, CommandType cmdType)
{
int i = 0;
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlCommand cmd = new SqlCommand(strSQL, conn);
cmd.CommandType = cmdType;
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
i = cmd.ExecuteNonQuery();
conn.Close();
}
r