was successfully added to your cart.

C# Data Access: SQL Database

By February 28, 2017 C#, SQL 14 Comments

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.

Sample Code

Reading the Connection String



Getting Data from SQL

using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("SampleDB")))
    var output = connection.Query("dbo.People_GetByLastName @LastName", new { LastName = lastName }).ToList();
    return output;


Putting Data into SQL

using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("SampleDB")))
    List people = new List();
    people.Add(new Person { FirstName = firstName, LastName = lastName, EmailAddress = emailAddress, PhoneNumber = phoneNumber });
    connection.Execute("dbo.People_Insert @FirstName, @LastName, @EmailAddress, @PhoneNumber", people);


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.

Other Resources

Join the discussion 14 Comments

  • Brandon says:

    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?

    • Tim Corey says:

      (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 hope that helps.

  • Abs K says:

    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.

    • Tim Corey says:

      There isn’t a lot to the sample database, but I’m happy to give you the script necessary to create the database and the few sample objects inside it. Here it is:

      CREATE TABLE [dbo].[People](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [FirstName] [nvarchar](50) NOT NULL,
      [LastName] [nvarchar](50) NOT NULL,
      [EmailAddress] [nvarchar](100) NOT NULL,
      [PhoneNumber] [varchar](20) NOT NULL,
      [id] ASC
      ) ON [PRIMARY]


      CREATE PROCEDURE [dbo].[People_GetByLastName]
      @LastName nvarchar(50)

      select *
      from dbo.People
      where LastName = @LastName;


      CREATE PROCEDURE [dbo].[People_Insert]
      @FirstName nvarchar(50),
      @LastName nvarchar(50),
      @EmailAddress nvarchar(100),
      @PhoneNumber varchar(20)

      insert into dbo.People (FirstName, LastName, EmailAddress, PhoneNumber)
      values (@FirstName, @LastName, @EmailAddress, @PhoneNumber);


      USE [master]

      • H says:

        Hi Tim,
        The code you have provided…
        Is it just a matter of putting it all in a ‘New Query’ window and hitting ‘Execute’
        or does it require anything else after launching SSMS?

        • Tim Corey says:

          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.

  • lilnate says:

    Can you post the code for the store procedures. I would love to look at how they should be formatted. Thanks

    Your website is helping me so much on my senior project for my under grad at Purdue. Tim you’re a life saver!!!

  • Suz says:

    I tried to do this but in web application… and there you can not use DisplayMember. Is there any other option or is making this in web app impossible?

  • Softfamily says:

    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

    • Tim Corey says:

      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.

      • Softfamily says:

        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.

        • Softfamily says:

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

Leave a Reply