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?
TownTalkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel Van Der WerkenIndependent ConsultantCommented:
Stick with what you know and trust.

As a web developer for multiple companies and multiple applications, I've seen data access done in a variety of ways. If it works for you and you're comfortable with using it, then there is no reason to change.

You can use a SQLConnecton/SQLCommand/SQLDataReader for multiple data sets and any simple search engine search should give you results for this topic.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
it_saigeDeveloperCommented:
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 -Capture.JPGCapture.JPGCapture.JPGWhich produces the following output (results will vary) -Capture.JPG
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-
0
TownTalkAuthor Commented:
@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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.