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();
}
}