using System.Data;
using System.Data.SqlCient;
#region ###jonse
//共通函数 把sql语句执行的查询结果放入datatable中
// 传入sql语句，datatable名称，数据连接字符可选
public static DataTable GetDataTable(out string sEx, string sSql, string sDataTableName, string sConnStr = "", SqlParameter[] commandParameters = null)
{
sEx = string.Empty;
if (string.IsNullOrEmpty(sDataTableName))
{
sEx = "datatable的名字不能为空";
return null;
}
if (string.IsNullOrEmpty(sConnStr)) sConnStr = DbHelperSQL.connectionString;
DataTable dt = new DataTable();
try
{
if (commandParameters == null)
{
dt = ExecuteDataTable(sConnStr, CommandType.Text, sSql);
}
else
{
dt = ExecuteDataTable(sConnStr, CommandType.StoredProcedure, sSql, commandParameters);
}
}
catch (Exception ex)
{
sEx = ex.Message.ToString();
}
finally
{
dt.TableName = sDataTableName;
}
return dt;
}
// 取dataset
public static DataSet GetDataSet(out string sError, string sSQL, string sConnStr="")
{
DataSet ds = null;
sError = string.Empty;
try
{
if (string.IsNullOrEmpty(sConnStr)) sConnStr = DbHelperSQL.connectionString;
SqlConnection conn = new SqlConnection(sConnStr);
SqlCommand comm = new SqlCommand();
comm.CommandText = sSQL;
SqlDataAdapter dapter = new SqlDataAdapter(comm);
ds = new DataSet();
dapter.Fill(ds);
}
catch (Exception ex)
{
sError = ex.Message;
}
return ds;
}
// 取某个单一的元素
public static object GetSingle(out string sError, string sSQL)
{
DataTable dt = GetDataTable(out sError, sSQL, "singleTbl");
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0][0];
}
return null;
}
// 取最大的ID
public static Int32 GetMaxID(out string sError, string sKeyField, string sTableName)
{
DataTable dt = GetDataTable(out sError, "select isnull(max([" + sKeyField + "]),0) as MaxID from [" + sTableName + "]","maxID");
if (dt != null && dt.Rows.Count > 0)
{
return Convert.ToInt32(dt.Rows[0][0].ToString());
}
return 0;
}
// 执行 insert,update,delete 动作，也可以使用事务
public static bool UpdateData(out string sError, string sSQL, SqlParameter[] sqlParams=null,bool bUseTransaction = false, string sConnStr = "")
{
int iResult = 0;
sError = string.Empty;
if (string.IsNullOrEmpty(sConnStr)) sConnStr = DbHelperSQL.connectionString;
if (!bUseTransaction)
{
try