Store database tables in memory and call them at any time

Note: You can use SQL statements and only tables to load tables in the database to the memory. Note that the memory here will not be released. Therefore, this component can be used on the asp.net server. The table retrieved from the memory is customized by you. Data is stored in the memory and HashTable objects are used as storage objects. The extracted data is a able object. You can also get only one value in the DataTable table. If you want DataSet, you can also convert each other. For more information, see annotations.

/// <Summary>
/// Process basic data. Use the. net3.5 framework
// Author: If not light cold Email: ooofcu@hotmail.com
/// Principle: add the tables in the database to the memory object HashTable.
/// Retrieve the table or part of the content from the HashTable object during the call.
/// </Summary>
Public class DataDisposal
{
/* // ----------------- Example -------------------
// Author: If not light cold Email: ooofcu@hotmail.com
DataDisposal g_DataDisposal = new DataDisposal ("link string ");
// Load basic business data
G_DataDisposal.AddData ("JJ_SFLBB ");
G_DataDisposal.AddData ("JJ_SFWCYYB ");
G_DataDisposal.AddData ("JJ_DWXXB ");
// Determine whether the specified name's inner table exists
If (g_DataDisposal.Contains ("TableName "))
Return true;
// Obtain basic business data and obtain the entire table
DataTable dt = g_DataDisposal.GetData ("JJ_DWXXB ");

// Obtain the data through the operation and obtain a value by field in the specified table
String str = g_DataDisposal.GetTableValue ("ID", "0", "DWMC", "JJ_DWXXB ");

// Return a row of data and obtain the value of a row based on the primary key in the specified table
DataRow dr = g_DataDisposal.GetDataRow ("ID", "00", "JJ_DWXXB ");

// There are some other methods, please read them yourself
*/

// Private ZIT. I _ZIT_Public g_DBOperation; // data access component

Private unsafe Hashtable hashTable = new Hashtable (); // used to store a memory table set
Private String sConnString = "";

/// <Summary>
/// Memory data table set
/// </Summary>
Public Hashtable HashTableObj
{
Get {return hashTable ;}
Set {hashTable = value ;}
}
/// <Summary>
/// Constructor
/// </Summary>
/// <Param name = "sConnString"> database link string </param>
Public DataDisposal (string sConnString)
{
This. sConnString = sConnString;
GC. SuppressFinalize (hashTable); // The request system does not call the terminator of this object (important, ensure that this object is not garbage collected)
}

/// <Summary>
/// Determine whether the specified memory table exists in this object
/// </Summary>
/// <Param name = "sTableName"> table name </param>
Public bool Contains (string sTableName)
{
Return hashTable. ContainsKey (sTableName );
}

/// <Summary>
/// Obtain the number of memory tables in the object
/// </Summary>
/// <Returns> </returns>
Public int Count ()
{
Return hashTable. Count;
}

/// <Summary>
/// Load the data table to the memory table and load all the data in the table to the memory. Parameter: Database Table Name
/// </Summary>
/// <Param name = "sTableName"> data table name </param>
Public void AddData (string sTableName)
{
Try
{
If (hashTable. Contains (sTableName ))
{
// MessageBox. Show ("this table has been loaded! Program aborted! ");
Return;
}
String sSql = "select * from" + sTableName;
DataTable dtTemp = GetDataTable (sSql );
DtTemp. TableName = sTableName;
HashTable. Add (sTableName, dtTemp );
DtTemp = null;
}
Catch (Exception ex)
{
Throw new Exception (ex. Message );
}
}

/// <Summary>
/// Load the data table to the memory table according to the SQL statement. A memory table name must be customized. Parameter: SQL query statement, custom memory table name
/// </Summary>
/// <Param name = "sSql"> SQL statement </param>
/// <Param name = "sTableName"> customizes a memory table name, which is called by this table name. </param>
Public void AddDataSql (string sSql, string sTableName)
{
Try
{
DataTable dtTemp = GetDataTable (sSql );
DtTemp. TableName = sTableName;
HashTable. Add (sTableName, dtTemp );
DtTemp = null;
}
Catch (Exception ex)
{
Throw new Exception (ex. Message );
}
}

/// <Summary>
/// Retrieve a memory table with the specified table name from the memory table set
/// </Summary>
/// <Param name = "sTableName"> memory table name </param>
/// <Returns> </returns>
Public DataTable GetData (string sTableName)
{
Try
{
If (hashTable. Contains (sTableName ))
Return (DataTable) hashTable [sTableName];
Else
{
// MessageBox. Show ("the specified memory table does not exist! ");
}
}
Catch (Exception ex)
{
Throw new Exception (ex. Message );
}
Return null;
}

/// <Summary>
/// Return a row of data based on conditions
/// Select * from sTableName where sID = sValue
/// </Summary>
/// <Param name = "sTableName"> the memory table name is the same as the table name in the database </param>
/// <Param name = "sID"> table field name </param>
/// <Param name = "sValue"> table field value </param>
/// <Returns> one row of Data </returns>
Public DataRow GetDataRow (string sTableName, string sID, string sValue)
{
Try
{
// Operation to obtain data
DataTable dtTemp = GetData (sTableName );
LinqList <DataRow> rowsCollection = new LinqList <DataRow> (dtTemp. Rows );
// Implicit type
IEnumerable <DataRow> selectedRows = from r in rowsCollection
Where r [sID]. ToString () = sValue
Select r;
DtTemp = null;
Foreach (DataRow dr in selectedRows)
{
// Operation
Return dr;
}
}
Catch (Exception ex)
{
Throw new Exception (ex. Message );
}
Return null;
}

/// <Summary>
/// Return a row of data based on conditions
/// Select * from sTableName where sID1 = sValue1 and sID2 = sValue2
/// </Summary>
/// <Param name = "sTableName"> the memory table name is the same as the table name in the database </param>
/// <Param name = "sID1"> table field name 1 </param>
/// <Param name = "sValue1"> table field value 1 </param>
/// <Param name = "sID2"> table field name 2 </param>
/// <Param name = "sValue2"> table field value 2 </param>
/// <Returns> one row of Data </returns>
Public DataRow GetDataRow (string sTableName, string sID1, string sValue1, string sID2, string sValue2)
{
Try
{
// Operation to obtain data
DataTable dtTemp = GetData (sTableName );
LinqList <DataRow> rowsCollection = new LinqList <DataRow> (dtTemp. Rows );
IEnumerable <DataRow> selectedRows = from r in rowsCollection
Where r [sID1]. ToString () = sValue1 & r [sID2]. ToString () = sValue2
Select r; // implicit type
DtTemp = null;
Foreach (DataRow dr in selectedRows)
{
// Operation
Return dr;
}
}
Catch (Exception ex)
{
Throw new Exception (ex. Message );
}

Return null;
}

/// <Summary>
/// Query data in the memory table by conditions
/// Select retValue from sTableName where sID = sValue
/// </Summary>
/// <Param name = "retValue"> name of the table field to be returned </param>
/// <Param name = "sTableName"> the memory table name is the same as the table name in the database </param>
/// <Param name = "sID"> table field name </param>
/// <Param name = "sValue"> table field value </param>
/// <Returns> the returned value is the field defined by the retValue parameter. If an exception occurs, an error is returned and "" </returns>
Public string GetTableValue (string retValue, string sTableName, string sID, string sValue)
{
Try
{
// Operation to obtain data
DataTable dtTemp = GetData (sTableName );
LinqList <DataRow> rowsCollection = new LinqList <DataRow> (dtTemp. Rows );
IEnumerable <DataRow> selectedRows = from r in rowsCollection
Where r [sID]. ToString () = sValue
Select r; // implicit type
DtTemp = null;
Foreach (DataRow dr in selectedRows)
{
// Operation
Return dr [retValue]. ToString ();
}
}
Catch (Exception ex)
{
Throw new Exception (ex. Message );
}
Return "";
}

/// <Summary>
/// Query data in the memory table by conditions
/// Select retValue from sTableName where sID1 = sValue1 and sID2 = sValue2
/// </Summary>
/// <Param name = "retValue"> name of the table field to be returned </param>
/// <Param name = "sTableName"> the memory table name is the same as the table name in the database </param>
/// <Param name = "sID1"> table field name 1 </param>
/// <Param name = "sValue1"> table field value 1 </param>
/// <Param name = "sID2"> table field name 2 </param>
/// <Param name = "sValue2"> table field value 2 </param>
/// <Returns> the returned value is the field defined by the retValue parameter. If an exception occurs, an error is returned and "" </returns>
Public string GetTableValue (string retValue, string sTableName, string sID1, string sValue1, string sID2, string sValue2)
{
Try
{
// Operation to obtain data
DataTable dtTemp = GetData (sTableName );
LinqList <DataRow> rowsCollection = new LinqList <DataRow> (dtTemp. Rows );
IEnumerable <DataRow> selectedRows = from r in rowsCollection
Where r [sID1]. ToString () = sValue1 & r [sID2]. ToString () = sValue2
Select r; // implicit type
DtTemp = null;
Foreach (DataRow dr in selectedRows)
{
// Operation
Return dr [retValue]. ToString ();
}
}
Catch (Exception ex)
{
Throw new Exception (ex. Message );
}

Return "";
}

/// <Summary>
/// Delete a specific table in the memory
/// </Summary>
/// <Param name = "sTableName"> </param>
Public void RemoveTable (string sTableName)
{
If (hashTable. Contains (sTableName ))
HashTable. Remove (sTableName );
}

/// <Summary>
/// Delete all tables in the memory
/// </Summary>
Public void RemoveAllTable ()
{
HashTable = null;
}

/// <Summary>
/// Convert DataTable to DataSet
/// </Summary>
/// <Param name = "dt"> DataTable to be converted </param>
/// <Returns> new DataSet </returns>
Public DataSet DataTableToDataSet (DataTable dt)
{
DataSet ds = new DataSet ();
Ds. Tables. Add (dt );
Return ds;
}

/// <Summary>
/// Internal method. Determine the unique method for obtaining data inventory from this module,
/// Process the data access component to access the database to obtain the data
/// </Summary>
/// <Param name = "sSql"> </param>
/// <Returns> </returns>
Private DataTable GetDataTable (string sSql)
{
Return Query (sSql). Tables [0];
}

/// <Summary>
/// Internal method: Execute the query statement and return DataSet
/// </Summary>
/// <Param name = "SQLString"> query statement </param>
/// <Returns> DataSet </returns>
Private DataSet Query (string SQLString)
{
Using (OleDbConnection connection = new OleDbConnection (sConnString ))
{
DataSet ds = new DataSet ();
Try
{
Connection. Open ();
OleDbDataAdapter command = new OleDbDataAdapter (SQLString, connection );
Command. Fill (ds, "ds ");
}
Catch (System. Data. OleDb. OleDbException ex)
{
Throw new Exception (ex. Message );
}
Return ds;
}
}
}

Public class LinqList <T>: IEnumerable <T>, IEnumerable
{
IEnumerable items;
Public LinqList (IEnumerable items)
{
This. items = items;
}
# Region
IEnumerable <DataRow> Members;
IEnumerator <T> IEnumerable <T>. GetEnumerator ()
{
Foreach (T item in items)
Yield return item;
}
IEnumerator IEnumerable. GetEnumerator ()
{
IEnumerable <T> ie = this;
Return ie. GetEnumerator ();
}
# Endregion
}