using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using System.Text;
using System.Data.SqlClient;
using System.Xml;
/// <summary>
/// SQLHelper 的摘要描述
/// </summary>
public class SQLHelper
{
private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
#region ExecuteReader():執行并返回一個數據集
/// <summary>
/// 執行一段SqlCommand并返回一個結果集
/// </summary>
/// <param name="strSql">sql語句</param>
/// <param name="Parms">參數</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string strSql, SqlParameter[] Parms)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = strSql;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(Parms);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
conn.Close();
}
}
}
catch (Exception ex)
{
//fn_share fn_share = new fn_share();
systemErrorLog(strSql + ex.Message);
return (SqlDataReader)null;
}
}
#endregion
#region ExecuteNonQuery():執行并返回一個結果，1代表成功，-1代表失敗
/// <summary>
/// 執行并返回一個結果，1代表成功，-1代表失敗
/// </summary>
/// <param name="strSql"></param>
/// <param name="Parms"></param>
/// <returns></returns>
public int ExecuteNonQuery(string strSql, SqlParameter[] Parms)
{
try
{
int retcount = -1;
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = strSql;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(Parms);
conn.Open();
retcount = cmd.ExecuteNonQuery();
conn.Close();
cmd.Parameters.Clear();
}
}
return retcount;
}
catch (Exception ex)
{
systemErrorLog(strSql + ex.Message);
return -1;
}
}
#endregion
#region ExecuteScalar():返回數據集第一行第一列的數據
/// <summary>
/// 返回數據集第一行第一列的數據
/// </summary>
/// <param name="strSql"></param>
/// <param name="Parms">參數</param>
/// <returns></returns>
public object ExecuteScalar(string strSql, SqlParameter[] Parms)
{
object retobject = null;
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = strSql;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(Parms);
conn.Open();
retobject = cmd.ExecuteScalar();
conn.Close();
cmd.Parameters.Clear();
}
}
}
catch (Exception ex)
{
systemErrorLog(strSql + ex.Message);
}
return retobject;
}
#endregion
#region GetDataTable():根據sql返回一個數據表
/// <summary>
/// 根據sql返回一個數據表
/// </summary>
/// <param name="strSql"></param>
/// <param name="Parms"></param>
/// <returns></returns>
public DataTable GetDataTable(string strSql, SqlParameter[] Parms)
{
try
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = strSql;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(Parms);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
conn.Close();
cmd.Parameters.Clear();
}
}
return dt;
}
catch (Exception ex)
{
systemErrorLog(strSql + ex.Message);
return (DataTable)null;
}
}
#endregion
#region GetDataSet():根據sql返回一個數據集
/// <summary>
/// 根據sql返回一個數據集
/// </summary>
/// <param name="strSql"></param>
/// <param name="Parms"></param>
/// <returns></returns>
public DataSet GetDataSet(string strSql, SqlParameter[] Parms)
{
try
{
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = strSql;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(Parms);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
conn.Close();
cmd.Parameters.Clear();
}
}
return ds;
}
catch(Exception ex)
{
systemErrorLog(strSql + ex.Message);
return (DataSet)null;
}
}
#endregion
public void systemErrorLog(string msg)
{
//sqlconn_Close();
string filename = ConfigurationManager.AppSettings["UploadFilePath"].ToString() + @"\file\log\weblog_" + DateTime.Today.ToString("yyyyMMdd") + ".txt";
if (File.Exists(filename.Trim()))
{
StreamWriter sw = File.AppendText(filename.Trim());
sw.WriteLine("\n");
sw.WriteLine(DateTime.Now.ToString() + msg);
sw.Close();
}
else
{
StreamWriter sw = new StreamWriter(filename, false, Encoding.UTF8);
sw.Write(msg);
sw.Close();
}
}
}
public DataSet UserCart_List(string flag, string Guid, string UserCode, string DirectorCode, string SendNum, string paravalue)
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@flag",flag),
new SqlParameter("@Guid",Guid),
new SqlParameter("@UserCode",UserCode),
new SqlParameter("@DirectorCode",DirectorCode),
new SqlParameter("@SendNum",SendNum),
new SqlParameter("@paravalue",paravalue)
};
DataTable dt = new DataTable();
return SQLHelper.GetDataSet("usp_BCC_Send_UserCart_List", param);
//try
//{
// sqlcomm("usp_BCC_Send_UserCart_List");
// mycomm.Parameters.AddWithValue("@flag", flag.Trim());
// mycomm.Parameters.AddWithValue("@Guid", Guid.Trim());
// mycomm.Parameters.AddWithValue("@UserCode", UserCode.Trim());
// mycomm.Parameters.AddWithValue("@DirectorCode", DirectorCode.Trim());
// mycomm.Parameters.AddWithValue("@SendNum", SendNum.Trim());
// mycomm.Parameters.AddWithValue("@paravalue", paravalue.Trim());
// SqlDataAdapter da = new SqlDataAdapter(mycomm);
// DataSet ds = new DataSet();
// da.Fill(ds, "DeptAssistant_List");
// sqlconn_Close();
// return ds;
//}
//catch (Exception ee)
//{
// systemErrorLog("UserCart_List" + ee.Message);
// sqlconn_Close();
// return (DataSet)null;
//}
}