c# 調用sql

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;        //}    }