asked on

ADODB vs SQLConnection

I have a large MSAccess Production/Sales application which has been evolving over the last 15 years. I've decided to bite the bullet and rewrite it in C#. There are a number of factors to consider, but the most important is to decided how to access the SqlServer data. I've been very happy using ADODB, and I was pleased to find that I can continue to use it in C#. However the modern way is to use SQLConnection. I don't mind the learning curve so long as it does what I need. I was reading last night that as a default, only one recordset can be opened on a connection, and that this can be overridden to allow more. There are numerous places in my application where there are multiple recordsets open. So I certainly need this facility.

So I was wondering what you guys in here think? SQLConnection is supposedly more efficient, but actually I have no issues with the efficiency of ADODB. Should I stick with what I know and trust? Or should I move with the times move over to SQLConnection?

ASKER CERTIFIED SOLUTION

Daniel Van Der Werken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

it_saige

I would actually recommend that you take it one step further and use Entity Framework.

Consider the following:

using System;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using ADODB;

namespace EE_Q28689555
{
	class Program
	{
		static Stopwatch watch = new Stopwatch();

		static void Main(string[] args)
		{
			LookupAndDisplayADO();
			LookupAndDisplaySQL();
			LookupAndDisplayEntities();
			Console.ReadLine();
		}

		private static void LookupAndDisplayADO()
		{
			watch.Reset();
			Console.WriteLine("Performing an ADODB lookup:");
			watch.Start();
			Connection conn = null;
			Recordset rs = null;
			try
			{
				conn = new Connection();
				rs = new Recordset();
				string query = "SELECT P.NAME, G.NAME FROM PEOPLE P INNER JOIN GENDERS G ON P.GENDERID = G.ID WHERE GENDERID = 1";
				conn.Open("Provider=SQLOLEDB;Data Source=.;Initial Catalog=EE_Q28689555;Integrated Security=SSPI");
				rs.Open(query, conn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockBatchOptimistic, 0);
				while (!rs.EOF)
				{
					Console.WriteLine("{0}'s gender is: {1}", rs.Fields[0].Value.ToString(), rs.Fields[1].Value.ToString());
					rs.MoveNext();
				}
			}
			finally
			{
				if (rs != null)
				{
					rs.Close();
					rs = null;
				}

				if (conn != null)
				{
					conn.Close();
					conn = null;
				}
			}
			watch.Stop();
			Console.WriteLine("ADO Lookup took {0} ms", watch.ElapsedMilliseconds);
			Console.WriteLine();
		}

		private static void LookupAndDisplaySQL()
		{
			watch.Reset();
			Console.WriteLine("Performing a SQL lookup:");
			watch.Start();
			using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=EE_Q28689555;Integrated Security=True;Pooling=False"))
			{
				conn.Open();
				using (SqlCommand command = new SqlCommand("SELECT P.NAME, G.NAME FROM PEOPLE P INNER JOIN GENDERS G ON P.GENDERID = G.ID WHERE GENDERID = 1", conn))
				{
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							Console.WriteLine("{0}'s gender is: {1}", reader.GetString(0), reader.GetString(1));
						}
					}
				}
			}
			watch.Stop();
			Console.WriteLine("SQL Lookup took {0} ms", watch.ElapsedMilliseconds);
			Console.WriteLine();
		}

		private static void LookupAndDisplayEntities()
		{
			watch.Reset();
			Console.WriteLine("Performing an Entities Framework lookup:");
			watch.Start();
			using (DataEntities context = new DataEntities())
			{
				foreach (var person in (from p in context.People.Include("Gender") where p.Gender.ID.Equals(1) select p))
				{
					Console.WriteLine("{0}'s gender is: {1}", person.Name, person.Gender.Name);
				}
			}
			watch.Stop();
			Console.WriteLine("Entity Framework Lookup took {0} ms", watch.ElapsedMilliseconds);
			Console.WriteLine();
		}
	}
}

Open in new window

For the following database objects -

Which produces the following output (results will vary) -

While the Entities Framework lookup is slower than that of the SQL lookup, they both are much faster than the ADO lookup. IMHO, the ability to have direct access to your field values without having to perform type casting is significant advantage of EF over SQL.

However, if you decide to only use either SQL or ADO, I would still use SQL because of the performance increase.

Edit: I should also note that the DataEntities have to be built by the Entity Framework, but this happens in development.

-saige-

TownTalk

ASKER

@saige: Thanks for your input, but i'm only just starting out learning C#. So I don't want to start learning another concept like Entity Framework also. My MS Access Application has all the SQl Statements ready for me to copy and paste into C#. So I just need the mechanism to Open/Update Recordsets and Execute SQL Statements.

@Dan7el: You seem to be saying that ADODB is still acceptable. I've not made my mind up yet, but i'll accept your answer on the basis that you gave the best answer to the question that I asked.

Thanks guys.