using System;
using System.Data;
using System.Data.SqlClient;

namespace com.hua..li
{
?/// <summary>
?/// 數據庫操作
?/// </summary>
?public class pathDB:pathPage
?{

??override protected void OnInit(EventArgs e)
??{
???pathInit();
???base.OnInit(e);
??}

??protected void pathInit()
??{
???this.ConnectDb();
??}

??protected void ConnectDb()
??{
???if(doh == null)
???{
????System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connString"]);
????doh = new com.path.SqlDbOperHandler(conn);
???}
??}
?}
}
using System;

namespace com.hua.li
?{
??/// <summary>
??/// 表示數據庫連接類型。
??/// </summary>
??public enum DatabaseType:byte{SqlServer,OleDb};
??/// <summary>
??/// DbOperHandler 的摘要說明。
??/// </summary>
??public abstract class DbOperHandler
??{
???/// <summary>
???/// 析構函數，釋放申請的資源。
???/// </summary>
???~DbOperHandler()
???{
????conn.Close();
???}
???/// <summary>
???/// 表示數據庫連接的類型，目前支持SqlServer和OLEDB
???/// </summary>
???protected DatabaseType dbType=DatabaseType.OleDb;
???/// <summary>
???/// 返回當前使用的數據庫連接對象。
???/// </summary>
???/// <returns></returns>
???public System.Data.IDbConnection GetConnection()
???{
????return conn;
???}
???/// <summary>
???/// 條件表達式，用于在數據庫操作時篩選記錄，通常用于僅需指定表名稱和某列名稱的操作，如GetValue()，Delete()等，支持查詢參數，由AddConditionParameters指定。。
???/// </summary>
???public string ConditionExpress=string.Empty;
???/// <summary>
???/// 當前的SQL語句。
???/// </summary>
???public string SqlCmd=string.Empty;
???/// <summary>
???/// 當前操作所涉及的數據表名稱。
???/// </summary>
???protected string tableName=string.Empty;
???/// <summary>
???/// 當前操作所設計的字段名稱。
???/// </summary>
???protected string fieldName=string.Empty;
???/// <summary>
???/// 當前所使用的數據庫連接。
???/// </summary>
???protected System.Data.IDbConnection conn;
???/// <summary>
???/// 當前所使用的命令對象。
???/// </summary>
???protected System.Data.IDbCommand cmd;
???/// <summary>
???/// 當前所使用的數據庫適配器。
???/// </summary>
???protected System.Data.IDbDataAdapter da;

??
???/// <summary>
???/// 用于存儲字段/值配對。
???/// </summary>
???protected System.Collections.ArrayList alFieldItems=new System.Collections.ArrayList(10);
???/// <summary>
???/// 用于存儲SQL語句中的查詢參數。
???/// </summary>
???protected System.Collections.ArrayList alSqlCmdParameters=new System.Collections.ArrayList(5);
???/// <summary>
???/// 用于存儲條件表達式中的查詢參數。
???/// </summary>
???protected System.Collections.ArrayList alConditionParameters=new System.Collections.ArrayList(5);
???/// <summary>
???/// 重值該對象，使之恢復到構造時的狀態。
???/// </summary>
???public void Reset()
???{
????this.alFieldItems.Clear();
????this.alSqlCmdParameters.Clear();
????this.alConditionParameters.Clear();
????this.ConditionExpress=string.Empty;
????this.SqlCmd=string.Empty;
????this.cmd.Parameters.Clear();
????this.cmd.CommandText=string.Empty;
???}
???/// <summary>
???/// 添加一個字段/值對到數組中。
???/// </summary>
???/// <param name="_fieldName">字段名稱。</param>
???/// <param name="_fieldValue">字段值。</param>
???public void AddFieldItem(string _fieldName,object _fieldValue)
???{

????for(int i=0;i<this.alFieldItems.Count;i++)
????{
?????if(((DbKeyItem)this.alFieldItems[i]).fieldName==_fieldName)
?????{
??????throw new ArgumentException("The field name has existed!");
?????}
????}
????this.alFieldItems.Add(new DbKeyItem(_fieldName,_fieldValue));
???}
???/// <summary>
???/// 添加條件表達式中的查詢參數到數組中。注意：當數據庫連接為SqlServer時，參數名稱必須和SQL語句匹配。其它則只需保持添加順序一致，名稱無需匹配。
???/// </summary>
???/// <param name="_conditionName">條件名稱。</param>
???/// <param name="_conditionValue">條件值。</param>
???public void AddConditionParameter(string _conditionName,object _conditionValue)
???{
????for(int i=0;i<this.alConditionParameters.Count;i++)
????{
?????if(((DbKeyItem)this.alConditionParameters[i]).fieldName==_conditionName)
?????{
??????throw new ArgumentException("The condition name has existed!");
?????}
????}
????this.alConditionParameters.Add(new DbKeyItem(_conditionName,_conditionValue));
???}

???/// <summary>
???/// 添加SQL語句中的查詢參數到數組中。注意：當數據庫連接為SqlServer時，參數名稱必須和SQL語句匹配。其它則只需保持添加順序一致，名稱無需匹配。
???/// </summary>
???/// <param name="_paraName">參數名稱。</param>
???/// <param name="_paraValue">參數值。</param>
???public void AddSqlCmdParameters(string _paraName,object _paraValue)
???{
????for(int i=0;i<this.alSqlCmdParameters.Count;i++)
????{
?????if(((DbKeyItem)this.alSqlCmdParameters[i]).fieldName==_paraName)
?????{
??????throw new ArgumentException("The sqlcmd parameter name has existed!");
?????}
????}
????this.alSqlCmdParameters.Add(new DbKeyItem(_paraName,_paraValue));
???}

???public bool Exist(string tableName)
???{
????return this.GetValue(tableName,"count(*)").ToString()!="0";
???}
???/// <summary>
???/// 抽象函數。用于產生Command對象所需的參數。
???/// </summary>
???protected abstract void GenParameters();
???/// <summary>
???/// 根據當前alFieldItem數組中存儲的字段/值向指定表中添加一條數據。在該表無觸發器的情況下返回添加數據所獲得的自動增長id值。
???/// </summary>
???/// <param name="_tableName">要插入數據的表名稱。</param>
???/// <returns>返回本數據連接上產生的最后一個自動增長id值。</returns>
???public int Insert(string _tableName)
???{
???
????this.tableName=_tableName;
????this.fieldName=string.Empty;
????this.SqlCmd="insert into "+this.tableName+"(";
????string tempValues=" values(";
????for(int i=0;i<this.alFieldItems.Count-1;i++)
????{
?????this.SqlCmd+=((DbKeyItem)alFieldItems[i]).fieldName;
?????this.SqlCmd+=",";

?????tempValues+="@para";
?????tempValues+=i.ToString();

?????tempValues+=",";
????}
????this.SqlCmd+=((DbKeyItem)alFieldItems[alFieldItems.Count-1]).fieldName;
????this.SqlCmd+=") ";

????tempValues+="@para";
????tempValues+=(alFieldItems.Count-1).ToString();

????tempValues+=")";
????this.SqlCmd+=tempValues;
????this.cmd.CommandText=this.SqlCmd;
????this.GenParameters();
????cmd.ExecuteNonQuery();
????cmd.CommandText="select @@identity as id";
????int autoId=Convert.ToInt32(cmd.ExecuteScalar());
????return autoId;
???}

???/// <summary>
???/// 根據當前alFieldItem數組中存儲的字段/值和條件表達式所指定的條件來更新數據庫中的記錄，返回所影響的行數。
???/// </summary>
???/// <param name="_tableName">要更新的數據表名稱。</param>
???/// <returns>返回此次操作所影響的數據行數。</returns>
???public int Update(string _tableName)
???{
????this.tableName=_tableName;
????this.fieldName=string.Empty;
????this.SqlCmd="update "+this.tableName+" set ";
????for(int i=0;i<this.alFieldItems.Count-1;i++)
????{
?????this.SqlCmd+=((DbKeyItem)alFieldItems[i]).fieldName;
?????this.SqlCmd+="=";

?????this.SqlCmd+="@para";
?????this.SqlCmd+=i.ToString();

?????this.SqlCmd+=",";
????}
????this.SqlCmd+=((DbKeyItem)alFieldItems[alFieldItems.Count-1]).fieldName;
????this.SqlCmd+="=";

????this.SqlCmd+="@para";
????this.SqlCmd+=(alFieldItems.Count-1).ToString();

????if(this.ConditionExpress!=string.Empty)
????{
?????this.SqlCmd=this.SqlCmd+" where "+this.ConditionExpress;
????}
????this.cmd.CommandText=this.SqlCmd;
????this.GenParameters();
????int effectedLines=this.cmd.ExecuteNonQuery();
????return effectedLines;
???}

???/// <summary>
???/// 執行SqlCmd中的SQL語句，參數由AddSqlCmdParameters指定，與ConditionExpress無關。
???/// </summary>
???/// <returns>返回此次操作所影響的數據行數。</returns>
???public int ExecuteSqlNonQuery()
???{
????this.cmd.CommandText=this.SqlCmd;
????this.GenParameters();
????return cmd.ExecuteNonQuery();
???}
???/// <summary>
???/// 獲取指定表，指定列，指定條件的第一個符合條件的值。
???/// </summary>
???/// <param name="_tableName">表名稱。</param>
???/// <param name="_fieldName">字段名稱。</param>
???/// <returns>獲取的值。如果為空則返回null。</returns>
???public object GetValue(string _tableName,string _fieldName)
???{
????this.tableName=_tableName;
????this.fieldName=_fieldName;
????this.SqlCmd="select "+this.fieldName+" from "+this.tableName;
????if(this.ConditionExpress!=string.Empty)
????{
?????this.SqlCmd=this.SqlCmd+" where "+this.ConditionExpress;
????}
????this.cmd.CommandText=this.SqlCmd;
????this.GenParameters();
????return cmd.ExecuteScalar();
???}
???/// <summary>
???/// 根據當前指定的SqlCmd獲取DataTable。如果ConditionExpress不為空則會將其清空，所以條件表達式需要包含在SqlCmd中。
???/// </summary>
???/// <returns>返回查詢結果DataTable。</returns>
???public System.Data.DataTable GetDataTable()
???{
????System.Data.DataSet ds=this.GetDataSet();
????return ds.Tables[0];
???}
???/// <summary>
???/// 根據當前指定的SqlCmd獲取DataSet。如果ConditionExpress不為空則會將其清空，所以條件表達式需要包含在SqlCmd中。
???/// </summary>
???/// <returns>返回查詢結果DataSet。</returns>
???public System.Data.DataSet GetDataSet()
???{
????this.alConditionParameters.Clear();
????this.ConditionExpress=string.Empty;
????this.cmd.CommandText=this.SqlCmd;
????this.GenParameters();
????System.Data.DataSet ds=new System.Data.DataSet();
????this.da.SelectCommand=this.cmd;
????this.da.Fill(ds);
????return ds;
???}
???/// <summary>
???/// 對指定表，指定字段執行加一計數，返回計數后的值。條件由ConditionExpress指定。
???/// </summary>
???/// <param name="_tableName">表名稱。</param>
???/// <param name="_fieldName">字段名稱。</param>
???/// <returns>返回計數后的值。</returns>
???public int Count(string _tableName,string _fieldName)
???{
????this.tableName=_tableName;
????this.fieldName=_fieldName;
????int count=Convert.ToInt32(this.GetValue(this.tableName,this.fieldName));
????count++;
????this.cmd.Parameters.Clear();
????this.cmd.CommandText=string.Empty;
????this.AddFieldItem(_fieldName,count);
????this.Update(this.tableName);
????return count;
???}

???/// <summary>
???/// 對指定表，指定字段執行減一計數，返回計數后的值。條件由ConditionExpress指定。
???/// </summary>
???/// <param name="_tableName">表名稱。</param>
???/// <param name="_fieldName">字段名稱。</param>
???/// <returns>返回計數后的值。</returns>
???public int Substract(string _tableName,string _fieldName)
???{
????this.tableName=_tableName;
????this.fieldName=_fieldName;
????int count=Convert.ToInt32(this.GetValue(this.tableName,this.fieldName));
????if(count>0)count--;
????this.cmd.Parameters.Clear();
????this.cmd.CommandText=string.Empty;
????this.AddFieldItem(_fieldName,count);
????this.Update(this.tableName);
????return count;
???}

???/// <summary>
???/// 根據ConditionExpress指定的條件在指定表中刪除記錄。返回刪除的記錄數。
???/// </summary>
???/// <param name="_tableName">指定的表名稱。</param>
???/// <returns>返回刪除的記錄數。</returns>
???public int Delete(string _tableName)
???{
????this.tableName=_tableName;
????this.SqlCmd="delete from "+this.tableName;
????if(this.ConditionExpress!=string.Empty)
????{
?????this.SqlCmd=this.SqlCmd+" where "+this.ConditionExpress;
????}
????this.cmd.CommandText=this.SqlCmd;
????this.GenParameters();
????return cmd.ExecuteNonQuery();
???}
??????????? /// <summary>
??????????? /// 函數sendMsg需要 __Receive接受者 如果是系統則為 admin 否則為手機號碼
??????????? /// </summary>
??????????? /// <param name="_PHONE">手機號碼</param>
??????????? /// <param name="_KeyWorld">關鍵字</param>
??????????? /// <param name="_INFO">信息的基本內容</param>
??????????? /// <param name="_Receive">接受者</param>
??????????? /// <returns></returns>
??????????? public bool SendMsg(string _PHONE, string _KeyWorld, string _INFO, string _Receive)
??????????? {
??????????????? bool SendOk;
??????????????? if (_PHONE != null || _KeyWorld != null)
??????????????? {
??????????????????? this.Reset();
??????????????????? this.AddFieldItem("PHONE", _PHONE);
??????????????????? this.AddFieldItem("KeyWorld", _KeyWorld);
??????????????????? this.AddFieldItem("INFO", _INFO);
??????????????????? this.AddFieldItem("Receive", _Receive);
??????????????????? this.Insert("smsRawRecv").ToString();
??????????????????? SendOk = true;
??????????????? }
??????????????? else
??????????????? {
??????????????????? SendOk = false;
???????????????????
??????????????? }
??????????????? return SendOk;
??????????? }
??????????? /// <summary>
??????????? ///
??????????? /// </summary>
??????????? /// <param name="_PHONE"></param>
??????????? /// <param name="_KeyWorld"></param>
??????????? /// <param name="_INFO"></param>
??????????? /// <param name="_Receive"></param>
??????????? /// <returns></returns>
???????????? //public bool Received(string _PHONE, string _KeyWorld, string _INFO, string _Receive)
???????????? //{
???????????? //??? bool Received, SendOk;
???????????? //??? if (SendOk)
???????????? //??? {
???????????? //??????? if (_PHONE != null || _KeyWorld != null)
???????????? //??????? {
???????????? //??????????? this.Reset();
???????????? //??????????? this.AddFieldItem("PHONE", _PHONE);
???????????? //??????????? this.AddFieldItem("KeyWorld", _KeyWorld);
???????????? //??????????? this.AddFieldItem("INFO", _INFO);
???????????? //??????????? this.AddFieldItem("Receive", _Receive);
???????????? //??????????? this.Insert("smsSended").ToString();
???????????? //??????????? Received = true;
???????????? //??????? }
???????????? //??????? else
???????????? //??????? {
???????????? //??????????? Received = false;
???????????? //??????? }
???????????? //??? }
???????????? //??? else
???????????? //??? {
???????????? //??????? Received = false;
???????????? //??? }
???????????? //}
???/// <summary>
???/// 審核函數。將指定表，指定字段的值進行翻轉，如：1->0或0->1。條件由ConditionExpress指定。
???/// </summary>
???/// <param name="_tableName">表名稱。</param>
???/// <param name="_fieldName">字段名稱。</param>
???/// <returns>返回影響的行數。</returns>
???public int Audit(string _tableName,string _fieldName)
???{
????this.tableName=_tableName;
????this.fieldName=_fieldName;
????this.SqlCmd="update "+this.tableName+" set "+this.fieldName+"=1-"+this.fieldName;
????if(this.ConditionExpress!=string.Empty)
????{
?????this.SqlCmd=this.SqlCmd+" where "+this.ConditionExpress;
????}
????this.cmd.CommandText=this.SqlCmd;
????this.GenParameters();
????return cmd.ExecuteNonQuery();
???}

???/// <summary>
???/// 釋放資源
???/// </summary>
???public void Dispose()
???{
????conn.Close();
???}
??
??}

??/// <summary>
??/// 數據表中的字段屬性，包括字段名，字段值。
??/// 常用于保存要提交的數據。
??/// </summary>
??public class DbKeyItem
??{
???/// <summary>
???/// 構造函數。
???/// </summary>
???/// <param name="_fieldName">字段名稱。</param>
???/// <param name="_fieldValue">字段值。</param>
???public DbKeyItem(string _fieldName,object _fieldValue)
???{
????this.fieldName=_fieldName;
????this.fieldValue=_fieldValue.ToString();
???}
???/// <summary>
???/// 字段名稱。
???/// </summary>
???public string fieldName;
???/// <summary>
???/// 字段值。
???/// </summary>
???public string fieldValue;
??}
?}