General background execution Method

Using system;
Using system. Data;
Using system. Data. sqlclient;
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;

/// <Summary>
/// Summary of sqldb
/// </Summary>
Public class sqldb
{

// Connection object
Sqlconnection conn;

// Constructor to obtain the connection string in Web. config

Public sqldb ()
: This (configurationmanager. connectionstrings ["sqldb"]. connectionstring)
{

}

// Rewrite the constructor, instance
Public sqldb (string connectionstring)
{
Conn = new sqlconnection (connectionstring );
}

/// <Summary>
/// Used to execute SQL statements
/// </Summary>
/// <Param name = "SQL"> SQL statement </param>
/// <Returns> datatable </returns>
Public datatable search (string SQL)
{
If (conn. State! = Connectionstate. open)
Conn. open ();
Datatable dt = new datatable ();
Sqldataadapter SDA = new sqldataadapter (SQL, Conn );
Try
{
SDA. Fill (DT );
}
Catch (sqlexception SE)
{
System. Diagnostics. Debug. writeline (SE. Message );
}
Finally
{
Conn. Dispose ();
SDA. Dispose ();
Conn. Close ();
}

Return DT;
}

/// <Summary>
/// Used to execute SQL statements
/// </Summary>
/// <Param name = "SQL"> SQL statement </param>
/// <Returns> bool </returns>
Public bool execute (string SQL)
{
If (conn. State! = Connectionstate. open)
Conn. open ();
Sqlcommand cmd = new sqlcommand (SQL, Conn );

Try
{
If (CMD. executenonquery ()> 0)
Return true;
}
Catch (sqlexception SE)
{
System. Diagnostics. Debug. writeline (SE. Message );
}
Finally
{
Conn. Dispose ();
Cmd. Dispose ();
Conn. Close ();
}
Return false;
}

 

/// <Summary>
/// Method for executing a stored procedure without Parameters
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Returns> sqldatareader data </returns>
Public datatable searchproc (string procname)
{
If (conn. State! = Connectionstate. open)
Conn. open ();
Datatable dt = new datatable ();

Sqldataadapter SDA = new sqldataadapter (procname, Conn );

Try
{
SDA. Fill (DT );
}
Catch (exception ex)
{
System. Diagnostics. Debug. writeline (ex. Message );
}
Finally
{
Conn. Dispose ();
SDA. Dispose ();
Conn. Close ();
}
Return DT;
}

/// <Summary>
/// Rewrite the stored procedure with Parameters
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "procparms"> stored procedure parameters </param>
/// <Returns> sqldatareader data </returns>
Public datatable searchproc (string procname, sqlparameter [] procparams)
{

If (conn. State! = Connectionstate. open)
Conn. open ();
Datatable dt = new datatable ();
Sqldataadapter SDA = new sqldataadapter ();
SDA. selectcommand = new sqlcommand (procname, Conn );
SDA. selectcommand. commandtype = commandtype. storedprocedure;

If (procparams! = NULL)
{
Foreach (sqlparameter procparam in procparams)
SDA. selectcommand. Parameters. Add (procparam );

}

Try
{
SDA. Fill (DT );
}
Catch (exception ex)
{
System. Diagnostics. Debug. writeline (ex. Message );
}
Finally
{
Conn. Dispose ();
SDA. Dispose ();
Conn. Close ();
}
Return DT;
}

/// <Summary>
/// Execution method of stored procedure without return values

/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "procparms"> Stored Procedure Parameter array </param>
/// <Returns> bool </returns>
Public bool executeproc (string procname, sqlparameter [] procparams)
{
If (conn. State! = Connectionstate. open)
Conn. open ();
Sqlcommand cmd = new sqlcommand (procname, Conn );
Cmd. commandtype = commandtype. storedprocedure;

If (procparams! = NULL)
{
Foreach (sqlparameter procparm in procparams)
Cmd. Parameters. Add (procparm );
}
// Try
//{
Int I;
I = cmd. executenonquery ();
If (I> 0)
Return true;
Else
Return false;
//}
// Catch (exception ex)
//{
// System. Diagnostics. Debug. writeline (ex. Message );
//}
// Finally
//{
// Conn. Dispose ();
// Cmd. Dispose ();
// Conn. Close ();

//}
Return false;
}

}