C # Some Common Access Operations

 

Using System;
Using System. configuration;
Using System. collections;
Using System. Data;
Using System. Data. oledb;
Namespace Mydata
{
Public Static Class Class_oledb_conn
{
// Public static string connstr = @ "provider = Microsoft. Jet. oledb.4.0; Data Source = database/*. mdb ;";
Public Static String Connstr = mydata. properties. settings. Default. my_soft_oledbconn;
// Open Database Link
Public Static Oledbconnection open_conn ( String Connstr)
{
Oledbconnection conn = New Oledbconnection (connstr );
Conn. open ();
Return Conn;
}
// Close Database Link
Public Static Void Close_conn (oledbconnection conn)
{
If (Conn! = Null )
{
Conn. Close ();
Conn. Dispose ();
}
GC. Collect ();
}
// Run the oledb statement
Public Static Int Run_ SQL ( String SQL, String Connstr)
{
Oledbconnection conn = open_conn (connstr );
Oledbcommand cmd = create_cmd (SQL, Conn );
Try
{
Int Result_count = cmd. executenonquery ();
Close_conn (conn );
Return Result_count;
}
Catch
{
Close_conn (conn );
Return 0 ;
}
}
// Generate command object
Public Static Oledbcommand create_cmd ( String SQL, oledbconnection conn)
{
Oledbcommand cmd = New Oledbcommand (SQL, Conn );
Return CMD;
}
// Run the oledb statement to return the datatable
Public Static Datatable get_datatable ( String SQL, String Connstr, String Table_name)
{
Oledbdataadapter da = get_adapter (SQL, connstr );
Datatable dt = New Datatable (table_name );
Da. Fill (DT );
Return DT;
}
// Run the oledb statement to return the oledbdatareader object.
Public Static Oledbdatareader get_reader ( String SQL, String Connstr)
{
Oledbconnection conn = open_conn (connstr );
Oledbcommand cmd = create_cmd (SQL, Conn );
Oledbdatareader Dr;
Try
{
Dr = cmd. executereader (commandbehavior. Default );
}
Catch
{
Throw New Exception (SQL );
}
Close_conn (conn );
Return Dr;
}
// Run the oledb statement to return the oledbdataadapter object.
Public Static Oledbdataadapter get_adapter ( String SQL, String Connstr)
{
Oledbconnection conn = open_conn (connstr );
Oledbdataadapter da = New Oledbdataadapter (SQL, Conn );
Return Da;
}
// Run the oledb statement and return the DataSet object.
Public Static Dataset get_dataset ( String SQL, String Connstr, dataset DS)
{
Oledbdataadapter da = get_adapter (SQL, connstr );
Try
{
Da. Fill (DS );
}
Catch (Exception ERR)
{
Throw Err;
}
Return DS;
}
// Run the oledb statement and return the DataSet object.
Public Static Dataset get_dataset ( String SQL, String Connstr, dataset ds, String Tablename)
{
Oledbdataadapter da = get_adapter (SQL, connstr );
Try
{
Da. Fill (DS, tablename );
}
Catch (Exception ex)
{
Throw Ex;
}
Return DS;
}
// Run the oledb statement, return the DataSet object, and paging the data.
Public Static Dataset get_dataset ( String SQL, String Connstr, dataset ds, Int Startindex, Int Pagesize, String Tablename)
{
Oledbconnection conn = open_conn (connstr );
Oledbdataadapter da = get_adapter (SQL, connstr );
Try
{
Da. Fill (DS, startindex, pagesize, tablename );
}
Catch (Exception ex)
{
Throw Ex;
}
Close_conn (conn );
Return DS;
}
// Returns the first column of the first row of the oledb statement execution result.
Public Static String Get_row1_col1_value ( String SQL, String Connstr)
{
Oledbconnection conn = open_conn (connstr );
String Result;
Oledbdatareader Dr;
Try
{
Dr = create_cmd (SQL, Conn). executereader ();
If (Dr. Read ())
{
Result = Dr [ 0 ]. Tostring ();
Dr. Close ();
}
Else
{
Result = "" ;
Dr. Close ();
}
}
Catch
{
Throw New Exception (SQL );
}
Close_conn (conn );
Return Result;
}
}
}

 

------------------------------------------------------------------
Database link string in APP. config
------------------------------------------------------------------

<? XML version = " 1.0 " Encoding = " UTF-8 " ?>
<Configuration>
<Configsections>
</Configsections>
<Connectionstrings>
<Add name = " Mydata. properties. settings. my_soft_oledbconn " Connectionstring = " Provider = Microsoft. Jet. oledb.4.0; Data Source = database path \ *. MDB "
Providername = " System. Data. oledb " />
</Connectionstrings>
</Configuration>