C# Data Access: SQL Database

Have a C# application that needs data but lost as to how to connect to a SQL database? Unsure how best to get data into SQL and back out safely and easily? Maybe you tried ADO.NET or Entity Framework and now you are more confused. Well, C# data access doesn’t have to be hard. You just need the right tool and a little help. Check out this video for an in-depth look at how to talk to a SQL database.

Further SQL Training

I mentioned it a couple times in the video, but if you really want to dig deeper into Microsoft SQL Server to learn how to install SQL, create a database, and perform advanced queries against that database, I highly recommend my SQL Databases: From Start to Finish course. I spend almost seven hours teaching you everything you need to know to build a great database. This, essentially, is a college-level course on SQL without all of the filler.

Join the discussion 17 Comments

When you added the second button for insert you had to add the code for the DataAccess class again ‘DataAccess db = new DataAccess();’ If I had multiple buttons for insert, save, edit etc. would it be better to add this code after the public partial class declaration to make it available globally?﻿

(using my answer from YouTube so people here can see it as well) Good thought. The answer is a bit of a toss-up. In the case of what I did, the instance of the class was created just in time and destroyed when it was no longer needed. That means that the memory used could be returned for other tasks. On the other hand, creating the instance once as a class-wide variable means that the memory for the instantiated class can never be reclaimed. It is used for the life of the form. So from that perspective, it would seem my way is better. But wait, there is more to consider.

Each time you instantiate a class, it takes processing power to do so (allocate the memory, initialize the class, etc.) So, if you are instantiating on every button click, that is more expensive than doing it once. From just this perspective, your way is better.

I could go on (garbage collection practices need to be taken into account) but the end result is this: it is a toss-up. It all depends on how your class is architected, how many times you call the class, and other factors. If you wanted, you could do memory tracking and test both methods in a production-like environment and see which one wins in your particular case. However, for 90% of the development you will do, that is probably overkill. So, the simple answer is this: do what seems best. If you have multiple buttons that are clicked often (clicked often is the more important factor), go your route and put the instantiated class at the class level. If you have a ton of buttons doing work but they aren’t clicked rapidly, go my route. It is more efficient. However, if you decide for simplicity sake to limit the code by going your route, your application won’t explode.

I was trying to restore sampleDB.bak file to my SQL Management studio but the version I am using is 2014 so I am getting incompatibility error.
Looked at solution online only one I found promising requires to create script from the source database. It will be nice to have something available if that’s not too much to ask for those who don’t have 2016 version or I appreciate if you could tell me other way to do that.

I provided two types of SQL statements. The one is all of the statements you need in one file. If you were to open up that file and hit run, it will do everything else. It will create the database, tables, and stored procedures that you need. The other is a zip file full of scripts. You would need to run each script in there one at a time if you wanted to fully recreate the database. However, you could also just run the one script you need if you deleted a table or made a mistake with a stored procedure.

var output = connection.Query($”Select * from People where LastName = ‘ { lastName } ‘”).ToList();
It is not recognizing the connection.Query even when the reference to dapper was there, it gives error of the type IEnumerable in an assemble that is not referrence please help

It sounds like you have a problem in C#, not necessarily with Dapper. It does not know what to do with an IEnumerable, which seems odd. My suggestion is to create a model for the data coming from People and then do an explicit call like so: List output = connection.Query($”select * from People where LastName = ‘{ lastName }'”).ToList();

That should tell you if the issue is with C# or Dapper at the very least.

Thanks Tim for your prompt reply, I am most grateful. Little more explanation is that after the ‘Using IDbConnection ….’ statement which is good, when I try to use the new connection and type connection. (I mean connection and a dot), Query option is not included in the list of options from the drop down only few options like (BeginTransactions, ChangeDatabase, Close, CreateCommand, Database, Dispose, Equals) that were available, I am using Visual Studio 2015 Enterprise Edition.
Thanks.

Hi Tim, I restarted the whole tutorial from the beginning with using a new project, downgraded the Dapper to 1.5.0.4 and everything is working fine now. Quite strange. Thanks a lot. I really enjoy the method. Thanks.

Keeping a connection open is dangerous because if the application crashes, the connection will stay open. This will bog down your server eventually. It also keeps using the resources (the connection) the entire time instead of just when you need it.