Three-tier architecture-database access layer full

Using system;
Using system. Data;
Using system. Data. sqlclient;
Using system. configuration;

Namespace dbbase
{

Public abstract class base
{

# Region "fields of base calss"

Protected static string strconn = configurationsettings. deleettings ["strconnection"];

Protected static string strsql;

# Endregion

# Region "properties of base class"
}

# Endregion

# Region "functions of base class"
Public base ()
{
//
// Todo: Add constructor logic here
//
}

/// <Summary>
/// Executing SQL commands
/// </Summary>
/// <Param name = "strsql"> string </param>
/// <Returns> return int </returns>
Protected static int executesql (string strsql)
{
Sqlconnection mycn = new sqlconnection (strconn );
Sqlcommand mycmd = new sqlcommand (strsql, mycn );
Try
{
Mycn. open ();
Mycmd. executenonquery ();
Return 0;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Mycmd. Dispose ();
Mycn. Close ();
}
}

/// <Summary>
/// Executing SQL commands
/// </Summary>
/// <Param name = "strsql"> SQL statement to be executed, string type </param>
/// <Returns> returns the execution status and integer value. </returns>
Protected static int executesqlex (string strsql)
{
Sqlconnection mycn = new sqlconnection (strconn );
Sqlcommand mycmd = new sqlcommand (strsql, mycn );

Try
{
Mycn. open ();
Sqldatareader myreader = mycmd. executereader ();
If (myreader. Read ())
{
Return 0;
}
Else
{
Throw new exception ("value unavailable! ");
}
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Mycmd. Dispose ();
Mycn. Close ();
}
}

/// <Summary>
/// Get Dataset
/// </Summary>
/// <Param name = "strsql"> (string) </param>
/// <Returns> (Dataset) </returns>
Protected static dataset executesql4ds (string strsql)
{
Sqlconnection mycn = new sqlconnection (strconn );
Try
{
Mycn. open ();
Sqldataadapter SDA = new sqldataadapter (strsql, mycn );
Dataset DS = new dataset ("ds ");
SDA. Fill (DS );
Return Ds;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Mycn. Close ();
}
}

/// <Summary>
/// Get single value [from: 51item.net]
/// </Summary>
/// <Param name = "strsql"> (string) </param>
/// <Returns> (INT) </returns>
Protected static int executesql4value (string strsql)
{
Sqlconnection mycn = new sqlconnection (strconn );
Sqlcommand mycmd = new sqlcommand (strsql, mycn );
Try
{
Mycn. open ();
Object r = mycmd. executescalar ();
If (object. Equals (R, null ))
{
Throw new exception ("value unavailable! ");
}
Else
{
Return (INT) R;
}
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Mycmd. Dispose ();
Mycn. Close ();
}
}

/// <Summary>
/// Get object
/// </Summary>
/// <Param name = "strsql"> (string) </param>
/// <Returns> (object) </returns>
Protected static object executesql4valueex (string strsql)
{
Sqlconnection mycn = new sqlconnection (strconn );
Sqlcommand mycmd = new sqlcommand (strsql, mycn );
Try
{
Mycn. open ();
Object r = mycmd. executescalar ();
If (object. Equals (R, null ))
{
Throw new exception ("Object unavailable! ");
}
Else
{
Return R;
}
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Mycmd. Dispose ();
Mycn. Close ();
}
}

/// <Summary>
/// Execute multipul SQL commands
/// </Summary>
/// <Param name = "strsqls"> string </param>
/// <Returns> int </returns>
Protected static int executesqls (string [] strsqls)
{
Sqlconnection mycn = new sqlconnection (strconn );
Sqlcommand mycmd = new sqlcommand ();
Int J = strsqls. length;

Try
{
Mycn. open ();
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Sqltransaction mytrans = mycn. begintransaction ();

try
{< br> mycmd. Connection = mycn;
mycmd. Transaction = mytrans;

Foreach (string STR in strsqls)
{
Mycmd. commandtext = STR;
Mycmd. executenonquery ();
}
Mytrans. Commit ();
Return 0;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Mytrans. rollback ();
Throw new exception (E. Message );
}
Finally
{
Mycmd. Dispose ();
Mycn. Close ();
}
}

# Endregion
}
}