Click here to Skip to main content
15,884,237 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;

public class DatabaseHelper : IDisposable, IError
{
	private SqlConnection objConnection;
	private SqlCommand objCommand;
	private bool boolHandleErrors;
	private string strLastError;
	private bool boolLogError;
	private string strLogFile;
    public event System.EventHandler IError;
   // public event OnExecuteErrorEventHandler IError.OnExecuteError;
	public delegate void OnExecuteErrorEventHandler(string Reason, string Message, string Location);
	protected void ErrorOccured(string Reason, string Message, string Location)
	{
		if (OnExecuteError != null) {
			OnExecuteError(Reason, Message, Location);
		}
	}

	public DatabaseHelper()
	{
		objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings("conn").ConnectionString);
		objCommand = new SqlCommand();
		objConnection.ConnectionString = ConfigurationManager.ConnectionStrings("conn").ConnectionString;
		objCommand.Connection = objConnection;
	}
	public DatabaseHelper(string DatabaseName)
	{
		objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings("conn").ConnectionString);
		objCommand = new SqlCommand();
		objConnection.ConnectionString = ConfigurationManager.ConnectionStrings("conn").ConnectionString;
		objCommand.Connection = objConnection;
	}
	public bool HandleErrors {
		get { return boolHandleErrors; }
		set { boolHandleErrors = value; }
	}
	public string LastError {
		get { return strLastError; }
	}
	public bool LogErrors {
		get { return boolLogError; }
		set { boolLogError = value; }
	}
	public string LogFile {
		get { return strLogFile; }
		set { strLogFile = value; }
	}
	public object AddParameter(SqlParameter parameter)
	{
		return objCommand.Parameters.Add(parameter);
	}
	public object AddParameter(string name)
	{
		SqlParameter p = new SqlParameter();
		p.ParameterName = name;
		p.SqlDbType = SqlDbType.Int;
		p.Direction = ParameterDirection.Output;
		return objCommand.Parameters.Add(p);
	}
	public object AddParameter(string name, object value, SqlDbType StrSqlDbType, int IntSize , ParameterDirection StrDirection )
	{
		SqlParameter p = new SqlParameter();
		p.ParameterName = name;
		p.Value = value;
		p.SqlDbType = StrSqlDbType;
		p.Direction = StrDirection;
		p.Size = IntSize;
		return objCommand.Parameters.Add(p);
	}

	public object AddOptionalParameter(string name, ParameterDirection StrDirection = ParameterDirection.Output, SqlDbType StrSqlDbType = SqlDbType.Int, int IntSize )
	{
		SqlParameter p = new SqlParameter();
		p.ParameterName = name;
		p.SqlDbType = StrSqlDbType;
		p.Direction = StrDirection;
		p.Size = IntSize;
		return objCommand.Parameters.Add(p);
	}
	public SqlCommand Command {
		get { return objCommand; }
	}
	public SqlConnection Connection {
		get { return objConnection; }
	}
	public SqlParameter GetObjParameter {
		get {
			SqlParameter p = new SqlParameter();
			return p;
		}
	}
	public void BeginTransaction()
	{
		if (objConnection.State == System.Data.ConnectionState.Closed) {
			objConnection.Open();
		}
		objCommand.Transaction = objConnection.BeginTransaction();
	}
	public void CommitTransaction()
	{
		objCommand.Transaction.Commit();
		objConnection.Close();
	}
	public void RollbackTransaction()
	{
		objCommand.Transaction.Rollback();
		objConnection.Close();
	}
	public int ExecuteNonQuery(string query)
	{
		return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit);
	}
	public int ExecuteNonQuery(string query, CommandType commandtype)
	{
		return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
	}
	public int ExecuteNonQuery(string query, ConnectionState connectionstate)
	{
		return ExecuteNonQuery(query, CommandType.Text, connectionstate);
	}
	public int ExecuteNonQuery(SqlCommand objCommand, ConnectionState connectionstate)
	{
		int i = -1;
		try {
			if (objConnection.State == System.Data.ConnectionState.Closed) {
				objConnection.Open();
			}
			i = objCommand.ExecuteNonQuery();
		} catch (Exception ex) {
			HandleExceptions(ex);
		} finally {
			objCommand.Parameters.Clear();
			if (connectionstate == connectionstate.CloseOnExit) {
				objConnection.Close();
			}
		}
		return i;
	}

	public int ExecuteNonQuery(string query, CommandType commandtype, ConnectionState connectionstate)
	{
		objCommand.CommandText = query;
		objCommand.CommandType = commandtype;
		int i = -1;
		try {
			if (objConnection.State == System.Data.ConnectionState.Closed) {
				objConnection.Open();
			}
			i = objCommand.ExecuteNonQuery();
		} catch (Exception ex) {
			HandleExceptions(ex);
		} finally {
			objCommand.Parameters.Clear();
			if (connectionstate == connectionstate.CloseOnExit) {
				objConnection.Close();
			}
		}
		return i;
	}
	public object ExecuteScalar(string query)
	{
		return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
	}
	public object ExecuteScalar(string query, CommandType commandtype)
	{
		return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
	}
	public object ExecuteScalar(string query, ConnectionState connectionstate)
	{
		return ExecuteScalar(query, CommandType.Text, connectionstate);
	}
	public object ExecuteScalar(string query, CommandType commandtype, ConnectionState connectionstate)
	{
		objCommand.CommandText = query;
		objCommand.CommandType = commandtype;
		object o = null;
		try {
			if (objConnection.State == System.Data.ConnectionState.Closed) {
				objConnection.Open();
			}
			o = objCommand.ExecuteScalar();
		} catch (Exception ex) {
			HandleExceptions(ex);
		} finally {
			objCommand.Parameters.Clear();
			if (connectionstate == connectionstate.CloseOnExit) {
				objConnection.Close();
			}
		}
		return o;
	}
	public SqlDataReader ExecuteReader(string query)
	{
		return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
	}
	public SqlDataReader ExecuteReader(string query, CommandType commandtype)
	{
		return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
	}
	public SqlDataReader ExecuteReader(string query, ConnectionState connectionstate)
	{
		return ExecuteReader(query, CommandType.Text, connectionstate);
	}
	public SqlDataReader ExecuteReader(string query, CommandType commandtype, ConnectionState connectionstate)
	{
		objCommand.CommandText = query;
		objCommand.CommandType = commandtype;
		SqlDataReader reader = null;
		try {
			if (objConnection.State == System.Data.ConnectionState.Closed) {
				objConnection.Open();
			}
			if (connectionstate == connectionstate.CloseOnExit) {
				reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
			} else {
				reader = objCommand.ExecuteReader();
			}
		} catch (Exception ex) {
			HandleExceptions(ex);
		} finally {
			objCommand.Parameters.Clear();
		}
		return reader;
	}
	public SqlDataReader ExecuteReader(SqlCommand cmd, ConnectionState connectionstate)
	{
		SqlDataReader reader = null;
		try {
			if (objConnection.State == System.Data.ConnectionState.Closed) {
				objConnection.Open();
			}
			if (connectionstate == connectionstate.CloseOnExit) {
				reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			} else {
				reader = cmd.ExecuteReader();
			}
		} catch (Exception ex) {
			//HandleExceptions(ex)
			throw ex;
		} finally {
			// cmd.Parameters.Clear()
		}
		return reader;
	}
	public DataSet ExecuteDataSet(string query)
	{
		return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
	}
	public DataSet ExecuteDataSet(string query, CommandType commandtype)
	{
		return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
	}
	public DataSet ExecuteDataSet(string query, ConnectionState connectionstate)
	{
		return ExecuteDataSet(query, CommandType.Text, connectionstate);
	}
	public DataSet ExecuteDataSet(string query, CommandType commandtype, ConnectionState connectionstate)
	{
		SqlDataAdapter adapter = new SqlDataAdapter();
		objCommand.CommandText = query;
		objCommand.CommandType = commandtype;
		adapter.SelectCommand = objCommand;
		DataSet ds = new DataSet();
		try {
			adapter.Fill(ds);
		} catch (Exception ex) {
			HandleExceptions(ex);
		} finally {
			objCommand.Parameters.Clear();
			if (connectionstate == connectionstate.CloseOnExit) {
				if (objConnection.State == System.Data.ConnectionState.Open) {
					objConnection.Close();
				}
			}
		}
		return ds;
	}
	private void HandleExceptions(Exception ex)
	{
		if (LogErrors) {
			WriteToLog(ex.Message);
		}
		if (HandleErrors) {
			strLastError = ex.Message;
		} else {
			throw ex;
		}
	}
	private void WriteToLog(string msg)
	{
		StreamWriter writer = File.AppendText(LogFile);
		writer.WriteLine(DateTime.Now.ToString() + " - " + msg);
		writer.Close();
	}
	public void Dispose1()
	{
		objConnection.Close();
		objConnection.Dispose();
		objCommand.Dispose();
	}
	void System.IDisposable.Dispose()
	{
		Dispose1();
	}
}
public enum Providers
{
	SqlServer,
	OleDb,
	Oracle,
	ODBC,
	ConfigDefined
}
public enum ConnectionState
{
	KeepOpen,
	CloseOnExit
}
public interface IError
{
	event OnExecuteErrorEventHandler OnExecuteError;
	delegate void OnExecuteErrorEventHandler(string Reason, string Message, string Location);
}


------------------
This is my C# Global Class Declaration.But Whene I Run my Pages ..It Gives The Error Like


----------
Error 1'OnExecuteErrorEventHandler': interfaces cannot declare types D:\Working Project\SOUBHAGYA\App_Code\DAL\DatabaseHelper.cs 370 16 D:\Working Project\SOUBHAGYA\
Error 2
Default parameter specifiers are not permitted<br />
D:\Working Project\SOUBHAGYA\App_Code\DAL\DatabaseHelper.cs 97  82  D:\Working Project\SOUBHAGYA\

Error 3
	Default parameter specifiers are not permitted	<br />
D:\Working Project\SOUBHAGYA\App_Code\DAL\DatabaseHelper.cs	97	134	D:\Working Project\SOUBHAGYA\

-----------------------


Please help!
Posted
Updated 11-Jul-11 0:53am
v4

Hi Pravati,

1. First error is coming due to you declare deligate
"OnExecuteErrorEventHandler" in Interface
VB
IError
.

2.Default parameters are not permitted
C#
public object AddOptionalParameter(string name, ParameterDirection StrDirection = ParameterDirection.Output, SqlDbType StrSqlDbType = SqlDbType.Int, int IntSize )
    {
C# version below than 4.0
For Optional parameter please ref this article.
http://msdn.microsoft.com/en-us/library/dd264739.aspx[^]
 
Share this answer
 
v2
Comments
Sergey Alexandrovich Kryukov 11-Jul-11 21:24pm    
Good catch, a 5.
--SA
vivekse 12-Jul-11 1:21am    
thanks SA
Arindam Tewary 12-Jul-11 4:33am    
Dear vivekse, I appreciate your post. Good points but interface can have event and having an event can not raise an error like "interfaces cannot declare types ". This error appears when you declare an delegate inside interface.
vivekse 12-Jul-11 8:26am    
thanks Arindam, i improved my answer.
Hi,

Just reading the error message and quick observation gives me following points which might help you to correct these errors,

1. You can not declare a delegate inside a interface.

C#
public interface IError
{
    event OnExecuteErrorEventHandler OnExecuteError;
    delegate void OnExecuteErrorEventHandler(string Reason, string Message, string Location);
}



2. Till .NET 3.5 you can not provide a default parameter value in a method signature that you have done.


C#
public object AddOptionalParameter(string name, ParameterDirection StrDirection = ParameterDirection.Output, SqlDbType StrSqlDbType = SqlDbType.Int, int IntSize )
    {
        SqlParameter p = new SqlParameter();
        p.ParameterName = name;
        p.SqlDbType = StrSqlDbType;
        p.Direction = StrDirection;
        p.Size = IntSize;
        return objCommand.Parameters.Add(p);
    }


Let me know if that helps.
 
Share this answer
 
Comments
Sandesh M Patil 11-Jul-11 7:34am    
Correct Answer
Arindam Tewary 11-Jul-11 9:25am    
Thanks :)
Sergey Alexandrovich Kryukov 11-Jul-11 21:22pm    
Not 3.5. Default parameter introduced in C# 4.0. Will you fix it?
--SA
Arindam Tewary 12-Jul-11 4:27am    
Dear SAKryukov, not sure whether you misread my post.

(1) I have mentioned that till .NET 3.5 function parameter with default value is not allowed. Is that wrong? I never mentioned that .NET 3.5 allows default parameter. And the question posted was even marked with tag ".NET 3.0", not ".NET 4.0". So do you feel my post worth downvoted?

(2) Surprisingly, though an interface can have an event and having an event can never raise a error like "interfaces cannot declare types " , but the answer which wrongly says having an event causes the problem have been "upvoted". Didn’t you bother to check the line "First error is coming due to you declare "event OnExecuteErrorEventHandler OnExecuteError" in Interface"?
I wish you had provided a comment there too.

I am least bothered about points, but surprising to me is when a person of your ("SAKryukov") caliber gives "comments" I do believe that better I brush up my knowledge, and cross check every time to make sure my own knowledge is correct.
Uday P.Singh 12-Jul-11 8:38am    
correct answer my 5! and SAKryukov please read the answer again!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900