Generic class which helps in dynamic sql script executing

This post is a sample class, which lets the user to execute their query at run time.

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Web;
using Microsoft.VisualBasic;

///

/// Summary description for Class1
///

public class DBProcess
{

#region "Private Members"

private string connectionString = string.Empty;
private string objectQualifier = string.Empty;
private string databaseOwner = string.Empty;

#endregion

public DBProcess()
{
}

public string ConnectionString
{
get { return connectionString; }
set { connectionString = value; }
}

public string ObjectQualifier
{
get { return objectQualifier; }
set { objectQualifier = value; }
}

public string DatabaseOwner
{
get { return databaseOwner; }
set { databaseOwner = value; }
}


#region ExecuteADOScript Method (first overload).
private DataSet ExecuteADOScript(string SQL) //void
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataAdapter objsqlda = null;
DataSet objds = null;
try
{
//Get the connection
connection = new SqlConnection(ConnectionString);
command = new SqlCommand(SQL, connection);
command.CommandTimeout = 0;
command.CommandType = CommandType.Text;
command.CommandText = SQL;
connection.Open();
objsqlda = new SqlDataAdapter();
objds = new DataSet();
objsqlda.SelectCommand = command;
objsqlda.Fill(objds);

}
finally
{
connection.Close();
command = null;
objsqlda = null;
}

return objds;
}
#endregion

#region ExecuteADOScript Method (second overload).
private DataSet ExecuteADOScript(SqlTransaction trans, string SQL)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataAdapter objsqlda = null;
DataSet objds = null;
try
{
//Get the connection
connection = trans.Connection;
command = new SqlCommand(SQL, trans.Connection);
command.Transaction = trans;
command.CommandType = CommandType.Text;
command.CommandText = SQL;
connection.Open();
objsqlda = new SqlDataAdapter();
objds = new DataSet();
objsqlda.SelectCommand = command;
objsqlda.Fill(objds);
}
finally
{
connection.Close();
command = null;
objsqlda = null;
}

return objds;
}
#endregion

#region ExecuteScript Method(first overload).
public DataSet ExecuteScript(string Script, bool UseTransactions)//string
{
string SQL = "";
string Exceptions = "";
string Delimiter = "G" + ControlChars.CrLf;
char[] char2 = Delimiter.ToCharArray();
string[] arrSQL = Script.Split(char2);//, , CompareMethod.Text);

DataSet ds = null;

if (objectQualifier != "" && (!objectQualifier.EndsWith("_")))
{
objectQualifier += "_";
}

if (databaseOwner != "" && (!databaseOwner.EndsWith(".")))
{
databaseOwner += ".";
}

if (UseTransactions)
{
SqlConnection Conn = new SqlConnection(ConnectionString);
Conn.Open();
try
{
SqlTransaction Trans = Conn.BeginTransaction();
bool IgnoreErrors;

foreach (string SQL1 in arrSQL)
{
if (SQL.Trim() != "")
{
// script dynamic substitution
SQL = SQL1.Replace("{databaseOwner}", DatabaseOwner);
SQL = SQL1.Replace("{objectQualifier}", ObjectQualifier);

IgnoreErrors = false;
if (SQL.Trim().StartsWith("{IgnoreError}"))
{
IgnoreErrors = true;
SQL = SQL1.Replace("{IgnoreError}", "");
}

try
{
ds = ExecuteADOScript(Trans, SQL);
}
catch (SqlException objException)
{
if (!IgnoreErrors)
{
Exceptions += objException.ToString() + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf + SQL + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf;
}
}

}
}



if (Exceptions.Length == 0)
{
//No exceptions so go ahead and commit
Trans.Commit();
}
else
{
//Found exceptions, so rollback db
Trans.Rollback();
Exceptions += "SQL Execution failed. Database rolled back" + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf + SQL + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf;
}
}
finally
{
Conn.Close();
}
}
else
{
foreach (string SQL1 in arrSQL)
{
if (SQL1.Trim() != "")
{
//script dynamic substitution
SQL = SQL1.Replace("{databaseOwner}", DatabaseOwner);
SQL = SQL.Replace("{objectQualifier}", ObjectQualifier);
try
{
ds = ExecuteADOScript(SQL);
}
catch (SqlException objException)
{
Exceptions += objException.ToString() + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf + SQL + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf;
throw;
}
}
}
}


return ds;
}
#endregion




}// END CLASS DEFINITION

//////////////////////////////
Calling Client
/////////////////////////////

protected void btnGenerate_Click(object sender, EventArgs e)
{
DBProcess objDbProcess = new DBProcess();

objDbProcess.ConnectionString = txtconn.Text;
objDbProcess.DatabaseOwner = txtowner.Text;
objDbProcess.ObjectQualifier = txtoq.Text;
//txtScript.Text = objDbProcess.ExecuteScript(txtScript.Text, false);
try
{

grdresult.DataSource = objDbProcess.ExecuteScript(txtScript.Text, false);
grdresult.DataBind();
}
catch(Exception ex)
{
txtScript.Text = ex.Message;
}
}


Sometime it is require that you want execute some query on client's server but you cannot access live server every now and then. So having this type of class in your web application with a proper UI would a great help.