connecting oracle database to C#.NET

anurag_45
anurag_45 used Ask the Experts™
on
Hi All,

I am trying to connect oracle database to c#...want to establish connection to database and fetch the records then want to close the connection. i am new to .net and need help.

Can you please guide me step by step?

Thank you,

Anurag_45
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Developer
Commented:
This function will return a datatable . . . either using the passed in connection (trans connection) or a fresh one using a predefined  DBConnectionString

public static DataTable GetTable(string strSQL, OracleConnection TransConnection = null)
{

	int recordCount = 0;
	DataTable Dtable = new DataTable();

	try {
		strSQL = strSQL.Replace(Constants.vbCr, " ");

		if (TransConnection != null) {
			OracleCommand command = TransConnection.CreateCommand();
			command.CommandText = strSQL;
			command.CommandType = CommandType.Text;
			if (TransConnection.Connection.State != ConnectionState.Open) {
				TransConnection.Connection.Open();
			}
			Dtable.Load(command.ExecuteReader());
			if (!TransConnection.IsInTransaction) {
				TransConnection.Connection.Close();
			}
			return Dtable;

		} else {
			if (string.IsNullOrEmpty(DBConnectionString) == false) {
				SetUserRole();
				using (OracleConnection connection = new OracleConnection(DBConnectionString)) {
					OracleCommand command = connection.CreateCommand();
					command.CommandText = strSQL;
					command.CommandType = CommandType.Text;

					connection.Open();
					Dtable.Load(command.ExecuteReader());
					connection.Close();

					return Dtable;
				}
			} else {
				return null;
			}
		}


	} catch (Exception ex) {
		//handle exception
	}

}

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
What do you want to do with the data once it is brought back?
How much data will you be brining back?

I also suggest Oracle's Data Provider, ODP.Net:
http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html

Author

Commented:
Thanks a lot guys...it was a big help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial