用户名:  密码:
兄弟在线   

标题:sqlHelper.cs 类函数

作者:agui005 来源:http://www.xdlmn.com 时间:2011-09-08

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
/// <summary>
/// SQL 的摘要描述
/// </summary>
public class SQL
{
 public SQL()
 {
  //
  // TODO: 在此加入建構函式的程式碼
  //
 }
    /// <summary>
    /// 數據庫連接字符串
    /// C@\WINNT\Microsoft.NET\Framework\v2.0.50727\CONFIG
    /// 在 machine.config的 最下面configuration之上加上
    /// appSettings
    /// add key="p_link" value="Provider=msdaora;Data Source=MTKSFCS_10.87.100.42;User Id= MAGELLAN_WAREHOUSE;Password=MAGELLAN_WAREHOUSE"
    /// appSettings
    /// configuration
    /// </summary>
  
    //设定DB Connection 字串
    public SqlConnection OracleProductConn(string strConfig)
    {
       
        SqlConnection DBConn = new SqlConnection();
        try
        {
            DBConn.ConnectionString = ConfigurationManager.AppSettings[strConfig].ToString();          
        }
        catch(Exception ex)
        {
            MessageLog log = new MessageLog();
            log.ErrorLog(ex.Message,"");
            throw new Exception(ex.Message, ex);
        }
        return DBConn;
    }

    //執行DML的SQL(INSERT INTO、DELETE、UPDATE),回傳成功筆數,如果失敗就回傳-1
    //strSql 传入要执行的sql
    public int DMLRecord(string strSql,string strConfig)
    {
        int intResult = -1;
        SqlConnection DBConn = OracleProductConn(strConfig);
        try
        {
            if (DBConn.State != ConnectionState.Open) DBConn.Open();
            SqlCommand cmd = new SqlCommand(strSql, DBConn);
            intResult = cmd.ExecuteNonQuery();
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            DBConn = null;
        }
        catch(Exception ex)
        {
            return intResult;
            MessageLog log = new MessageLog();
            log.ErrorLog(ex.Message + "\r\n" + "SQL=" + strSql, "");
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            DBConn = null;
            throw new Exception(ex.Message, ex);
        }
        return intResult;
    }

    //执行传入的sql,回传查询结果(datatable)
    public DataTable GetInfoDT(string strSql,string strConfig)
    {
        DataTable dt = new DataTable();
        SqlConnection DBConn = OracleProductConn(strConfig);
        try
        {
            if (DBConn.State != ConnectionState.Open) DBConn.Open();
            SqlDataAdapter dar = new SqlDataAdapter(strSql, DBConn);
            dar.Fill(dt);
            dar.Dispose();
            dar = null;
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            DBConn = null;
        }
        catch(Exception ex)
        {
            MessageLog log = new MessageLog();
            log.ErrorLog(ex.Message + "\r\n" + "SQL=" + strSql, "");
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            DBConn = null;
            throw new Exception(ex.Message, ex);
        }
        return dt;
    }

    //执行传入sql , 回传查询结果(DataReader)
    public SqlDataReader GetInfoDR(string strSql,string strConfig)
    {
        SqlDataReader dr;
        SqlConnection DBConn = OracleProductConn(strConfig);
        SqlCommand cmd = new SqlCommand();
        try
        {
            if (DBConn.State != ConnectionState.Open) DBConn.Open();
            cmd = new SqlCommand(strSql, DBConn);
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            // 使用CommandBehavior.CloseConnection這個方式,當datareader消失時,會自動closeconnection。
            // 所以本function不用作conn.close及conn=nothing
        }
        catch(Exception ex)
        {
            MessageLog log = new MessageLog();
            log.ErrorLog(ex.Message + "\r\n" + "SQL=" + strSql, "");
            cmd.Parameters.Clear();
            throw new Exception(ex.Message, ex);
        }
        return dr;
    }

    //執行傳入的sql(該sql只要求回傳一筆資料 and 一個欄位,如DB中的function或store procedures),回傳查詢結果(該欄位資料string)
    public string GetStr(string strSql,string strConfig)
    {
        string strResult = "";
        SqlConnection DBConn = OracleProductConn(strConfig);
        try
        {
            if (DBConn.State != ConnectionState.Open) DBConn.Open();
            SqlCommand cmd = new SqlCommand(strSql, DBConn);
            strResult = cmd.ExecuteScalar().ToString();
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            DBConn = null;           
        }
        catch(Exception ex)
        {
            MessageLog log = new MessageLog();
            log.ErrorLog(ex.Message + "\r\n" + "SQL=" + strSql, "");
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            DBConn = null;           
            throw new Exception(ex.Message,ex);
        }
        return strResult;
    }

    public int GetUserInPage(string UserName, string addr)
    {
        //string strSql = "select count(1) from page_addr where page_id in(select page_id from role_to_page where role_id in(select a.role_id from user_to_role a,sys_user b where a.user_id=b.user_id and b.user_name='"+ UserName +"' and b.user_flag='Y')) and page_flag='Y' and page_addr='"+ addr +"'";
        string strSql = "select dbo.GetUserInPage('"+ UserName +"','"+ addr.ToLower() +"') as getuser";
        int iresult = 0;
        iresult = GetNum(strSql,"sqllink");
        return iresult;
    }

    // 执行传入的sql(該sql只要求回傳一筆資料 and 一個欄位,如DB中的function或store procedures),回傳查詢結果(該欄位資料integer)
    // 例如:select count(*) as icount from table
    public int GetNum(string strSql,string strConfig)
    {
        int IntResult = -1;
        SqlConnection DBConn = OracleProductConn(strConfig);
        try
        {
            if (DBConn.State != ConnectionState.Open) DBConn.Open();
            SqlCommand cmd = new SqlCommand(strSql, DBConn);
            string strResult = cmd.ExecuteScalar().ToString();
            if (strResult != "") IntResult = Convert.ToInt32(strResult);
            else IntResult = 0;
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            DBConn = null;
        }
        catch(Exception ex)
        {
            MessageLog log = new MessageLog();
            log.ErrorLog(ex.Message + "\r\n" + "SQL=" + strSql, "");
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            DBConn = null;
            throw new Exception(ex.Message, ex);
        }
        return IntResult;
    }

    public string SqlCommandWithTransaction(string[] AryStrSQL,string strConfig)
    {
        string strRV = "";
        int iCnt = 0;
        SqlTransaction sqlTran;
        SqlCommand cmd = new SqlCommand();
        SqlConnection DBConn = OracleProductConn(strConfig);
        if (DBConn.State != ConnectionState.Open) DBConn.Open();
        sqlTran = DBConn.BeginTransaction(IsolationLevel.ReadCommitted);
        try
        {
            cmd.Connection = DBConn;
            cmd.Transaction = sqlTran;
            for (iCnt = 0; iCnt <= AryStrSQL.Length - 1; iCnt++)
            {
                if (AryStrSQL[iCnt].Trim().Length != 0)
                {
                    cmd.CommandText = AryStrSQL[iCnt];
                    cmd.ExecuteNonQuery();
                }
            }
            sqlTran.Commit();
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            return strRV;

        }
        catch(Exception ex)
        {
            MessageLog log = new MessageLog();
            log.ErrorLog(ex.Message + "\r\n" + "Transaction SQL=" + AryStrSQL[iCnt].Trim(), "");
            strRV = ex.Message;
            sqlTran.Rollback();
            log.ErrorLog("TransactionSQL RollBack OK!!!", "");
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            DBConn = null;
            return strRV;
           
        }

    }
    /// <summary>
    /// 綁定下拉框
    /// </summary>
    /// <param name="strSql">sql 語句</param>
    /// <param name="Ddl">下拉框ID</param>
    /// <param name="strConfig">DB 連接字串</param>
    /// <param name="strFlag">標識碼,"1" : 添加"請選擇", '0" 不添加,'2' ALL</param>
    public void BindDropDownList(string strSql, string strConfig, DropDownList Ddl, string strFlag)
    {
        SqlConnection DBConn = OracleProductConn(strConfig);
        DataTable Dt = new DataTable();
        try
        {
            if (DBConn.State != ConnectionState.Open) DBConn.Open();
            SqlDataAdapter Adp = new SqlDataAdapter(strSql, DBConn);
            Adp.Fill(Dt);
            Ddl.Items.Clear();
            Ddl.DataSource = Dt.DefaultView;
            Ddl.DataTextField = "text";
            Ddl.DataValueField = "value";
            Ddl.DataBind();
            if (strFlag == "1")
            {
                Ddl.Items.Insert(0, new ListItem("请选择", "请选择"));
            }
            if (strFlag == "2")
            {
                Ddl.Items.Insert(0, new ListItem("ALL", "ALL"));
            }
            Adp.Dispose();
            Dt.Dispose();
            Dt = null;
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
        }
        catch (Exception ex)
        {
            MessageLog log = new MessageLog();
            log.ErrorLog(ex.Message + "\r\n" + " SQL=" + strSql, "");
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            DBConn = null;
        }
    }
    //绑定 RadiobuttonList
    public void BindRadiobuttonList(string strSql,string strConfig,RadioButtonList rbl)
    {      
            SqlConnection DBConn = OracleProductConn(strConfig);    
            DataTable Dt = new DataTable();
            try
            {
                if (DBConn.State != ConnectionState.Open) DBConn.Open();
                SqlDataAdapter Adp = new SqlDataAdapter(strSql, DBConn);
                Adp.Fill(Dt);
                rbl.Items.Clear();
                rbl.DataSource = Dt.DefaultView;
                rbl.DataTextField = "text";
                rbl.DataValueField = "value";
                rbl.DataBind();              
                Adp.Dispose();
                Dt.Dispose();
                Dt = null;
                if (DBConn.State == ConnectionState.Open) DBConn.Close();
            }
            catch (Exception ex)
            {
                MessageLog log = new MessageLog();
                log.ErrorLog(ex.Message + "\r\n" + " SQL=" + strSql, "");
                if (DBConn.State == ConnectionState.Open) DBConn.Close();
                DBConn = null;
            }
       
    }
    //绑定 CheckBoxList
    public void BindCheckBoxList(string strSql, string strConfig, CheckBoxList rbl)
    {
        SqlConnection DBConn = OracleProductConn(strConfig);
        DataTable Dt = new DataTable();
        try
        {
            if (DBConn.State != ConnectionState.Open) DBConn.Open();
            SqlDataAdapter Adp = new SqlDataAdapter(strSql, DBConn);
            Adp.Fill(Dt);
            rbl.Items.Clear();
            rbl.DataSource = Dt.DefaultView;
            rbl.DataTextField = "text";
            rbl.DataValueField = "value";
            rbl.DataBind();
            Adp.Dispose();
            Dt.Dispose();
            Dt = null;
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
        }
        catch (Exception ex)
        {
            MessageLog log = new MessageLog();
            log.ErrorLog(ex.Message + "\r\n" + " SQL=" + strSql, "");
            if (DBConn.State == ConnectionState.Open) DBConn.Close();
            DBConn = null;
        }

    }

    //绑定ListBox
    public  void BindListBox(string strSql,string strConfig,ListBox lb)
    {       
            SqlConnection DBConn = OracleProductConn(strConfig);    
            DataTable Dt = new DataTable();
            try
            {
                if (DBConn.State != ConnectionState.Open) DBConn.Open();
                SqlDataAdapter Adp = new SqlDataAdapter(strSql, DBConn);
                Adp.Fill(Dt);
                lb.Items.Clear();
                lb.DataSource = Dt.DefaultView;
                lb.DataTextField = "text";
                lb.DataValueField = "value";
                lb.DataBind();
                Adp.Dispose();
                Dt.Dispose();
                Dt = null;              
                if (DBConn.State == ConnectionState.Open) DBConn.Close();
            }
            catch (Exception ex)
            {
                MessageLog log = new MessageLog();
                log.ErrorLog(ex.Message + "\r\n" + " SQL=" + strSql, "");
                if (DBConn.State == ConnectionState.Open) DBConn.Close();
                DBConn = null;
            }
       
    }
    public void selectsql(string strSql, string strConfig, GridView DataGrid)
    {

        SqlConnection conn = OracleProductConn(strConfig);

        DataSet ds = new DataSet();

        SqlDataAdapter comm = new SqlDataAdapter();

        comm = new SqlDataAdapter(strSql, conn);

        conn.Open();

        ds.Clear();

        comm.Fill(ds);

        DataGrid.DataSource = ds;

        DataGrid.DataBind();

        conn.Close();

    }


}
 



总点击 [3131]   评论  0 查看评论
上一篇:绑定序号gridview datagrid c# vb 2003 2008
下一篇:asp.net一些验证函数类
【关闭窗口】
您可能感兴趣的文章
我要评论
          
评论标题:   可以输入250
 
验证数字: 2 + 1 =
兄弟友情提示
· 请自觉遵守国家有关法律、法规,尊重网上道德。
· 兄弟在线坚决抵制不良言行,违者文责自负。
· 如果文章有版权或其他问题等,请联系我们,我们会尽快处理。
· 文章注名来自网络的旨在传播共享信息,不做其它用途;注名原创的本站支持原创,但不代表同意其观点。
· 兄弟在线拥有管理用户与其文章和评论的一切权利,并有权在网站内转载或引用。
兄弟在线
兄弟热门文章
兄弟推荐文章
兄弟站内搜索

兄弟感兴趣的文章
兄弟最新影视