Simple C# Data Access with Dapper and SQL - Minimal API Project Part 1 

Switched over to Dapper thanks to you Tim! You have been a great help to me. The way you explain things is incredible. Soo much easier than so many others and also your voice helps a lot. No funny accents and very human. Even the tiny errors and correcting them helps.

Another great video sir, please keep them coming. For anyone watching wondering wether his courses are worth it, they totally are! I've been a developer for quite some time and I wish I'd found you and your work a long time ago sir. Can't recommend you to other developers enough! Thank you!

Right now, I understand dapper, it's really cool to use to any database, SQL server + stored procedure is magic. Thanks Tim.

I really like the Contrib extension of Dapper. It allows us to write generic CRUD methods in the case of this video to the SqlDataAccess class and then we don't have to write CRUD stored procedures for every table unless there are some specific side effects that are needed in the sprocs. I think it saves a lot of coding time but of course there might be some reasons such as access control and maybe as you mentioned, performance which would require stored procedures. In my experience though, the Contrib methods are just fine.

Hey Tim... I was watching your videos since sometime but never commented before. I can't resist myself this time. This is a really good video, easy to go along the flow. Nicely explained (as always). I am pretty sure second part will be as interesting to learn as this one.

That was such a great help for me, thanks tim! I am really new in c# and worked on a project for my company with ef core but now I am thinking to use dapper instead of ef core because of the advantages you mentioned. I just love this simplicity and the separation of concerns with dapper, also it seems to me much easier to understand than ef core.

When I want to learn something new, Corey is my go to trainer. To show my appreciation I will purchase one of his classes. I learned working with SQL DB's with EF. I like EF, but it made my SQL programming weak. Learning Dapper my alleviate that handicap.

Thanks Tim. Amazing video. It's changing my workflow and now do a mix of EF and Dapper. EF to build my database tables, Visual Studio database project to build my Stored Procedures and Dapper to do all the CRUD. The best of both worlds, or maybe good and evil being nice to each other.

I have been searching .net core web api from the last 2 months and my search end here It help me a lot, Thanku you very much for this greate informational videos.

That was a great video. Thanks for the good content, organized presentation, clear voice and accent. Also the last but not least, thanks for NOT using Entity Framework.

Your videos always seem to pop up just at the right time for me, I’m in two minds about using EF for my latest project, the cons are outweighing the pros at the minute on a technical level, I tend to lean towards stored procedures as I find all but the simplest db schema just bloats the code/queries when using EF, but, the team all use It exclusively, so this will help give another option.

OH MY GOD , thank u so much , this is very handy , i think every beginner dev should watch this video , thanks bro

Wonderful first video, Tim! Thank you! For the insert operations, isn't in best practice to return (at a minimum) the ID of the object that was created, if not the entire new UserModel itself? I'm just wondering if practices have changed or if I have misinterpreted things along the way. Maybe I'm conflating the behavior of this w/ a POST operation for a RESTful API, which would typically return the object being created, but I'm assuming that's what this DataAccessLayer would be used for in part 2 (a RESTful API implementation?). Thanks for all you do for the community!

Hello Corey your videos ready amazing.. since I've been watching these videos I have broadly understanding in programming

Excellent video Tim!! The only weakness that I could think of, of this approach, is the lack of a proper way to get back the Id of the newly created object, by hitting the DB once with the Insert stored proc!! I really value your opinion about this!! Thanx again Tim, for enlightening us with your in depth knowledge of what we all love!! I look forward to hearing from you...

Hi Tim, can't find the words to describe my thoughts now. Your contents are awesome, that's it, thank you very much!

This is amazing so far. Thank you Tim. Glad I found your videos.

FINALLY. My most waiting Topic this month. Thanks Tim

Thank you very much, it was really helpful! Could you please add dynamic sorting, paging and filtering on part 2? Or at least point me to some example of how this could be achieved.

Hey Tim, I am a big fan of you - and even suggest your videos to newbies. I am applying Dapper to my main project, I did a basic dbcontext myself with MySQLConnector (that I could type Data.Table(procedureQuery, parsObjectArray). It worked well by now but everytime I need to add something is still painful. With your videos I have learned coding patterns like nowhere. Keep it up! ♥

Outstanding work here. This has help me design a clearer roadmap to migrate framework project to core replace linqtosql Orm. Thanks again!

Tim, this was very informative and will make me go back and refactor one of my sample projects to get a firm grip on this subject to fully understand it

Just wanted to say thank you for helping me learn about web development in general. I will soon contribute to this awesome cause!

Wow! Powerful way to setup data access. I am sold.

Thanks Tim, looking forward to part two!

This is my 10th hour of watching your explanation. Mind-blowing ❣

Exactly what I've done after seeing your older tutorial on dapper as my data access layer. I'm curious to see if my approach is close than yours then!

Ok, probably I'm just weird for asking, but could you not somehow default the parameters to null if you don't need one, or use a single parameter (e.g. 'ID') directly? I assume this is a non-issue, but the new-ing up of dynamic objects makes me a bit uneasy. For multiple params it makes perfect sense to me.

Wow... this tool is soooo awesome!!! How did I survive for so long without it!

Thanks a lot sensei Tim Corey, regards from some place in Perú.

I love working with SQL and this Video was Awsome, thank you.

Tim, Great video! I love the idea of using generic data access with dapper. But, I don't understand how I can use generic data access with complex models (i.e. models that have some number of sub-models within them). For example, a customer model where the prefix isn't just a string... It is a prefix model. ...and a suffix where the suffix is a suffix model. Using Dapper, is it possible to use just one generic data retrieval function regardless of how many sub-models there are within my model? Thank you so much for your videos. Looking forward to your reply. :)

Its like you are reading my mind on what I want/need

Thank you so much sir...I never really liked the idea of using ORM's in my .NET apps, but from what I've seen in this tutorial, I thing I'm gonna start implementing Dapper in any new application I'll develop😌.

Hello Tim, thanks again for a wonderful tutorial. I generally avoid to use stored procedures as I do not want to encapsulate business logic in the database. Also it is also recommended not to encapsulate business logic at database level. I try to use SPs for creating reports etc. My simple question is - what is your recommendation I mean when to use stored procedures?

Great Tutorial, Thanks. Looking to speed up my web that utilize EF6, Wow, all in an hour. Tim you are awesome!

I really need part 2. Waiting for it. Good job.

Awesome video, this has really helped me with my new project. Thanks so much Tim.

Beautifully simple solution. Thanks Tim!

Firstly - first video I've seen of yours Tim - awesome - I'm now a subscriber / fan! Q - LoadData is only implemented with IEnumerable. Why not one also for returning one row? Is this out of simplicity for the demo/video or do you *always* do it this way? Thanks.

Hi there, Thanks for all the resources. I know alot more about MSSQL than I do about .NET so thanks for getting me learning new skills. Few comments about stored procedures; stored procedures are not inherently faster than ad-hoc statements, all plans are compiled and cached ad-hoc or otherwise. MSSQL will consider dynamic SQL to be a different query and so will create a new plan for every execution - but if you use parameters (please god use parameters) then the query plan is reused for each different parameter. If you wrote this sql as a dapper query 'SELECT FIRSTNAME, LASTNAME FROM USERS WHERE FIRSTNAME = @0' and substituted in the parameter at run time then the query plan would be reused regardless of which parameter is used. You get parameter sniffing issues with both adhoc and SPs, where the same plan is grossly wrong for different parameters but you are definitely using the same query plan. If you write dynamic sql eg "SELECT FIRSTNAME, LASTNAME FROM USERS WHERE FIRSTNAME = '" + firstName + "'" then you would produce a different query plan for every query. Parameter sniffing is where the plan is optimised for the first parameter used but maybe grossly inefficient for other parameters..you get weird effects where you run the query in SSMS and it runs quick but your app runs slow. Its a whole different topic. Stored procedures are not faster than parameterized queries...its the same to the engine. Replicating simple SQL statements (even update ones) in a SP is no faster and if you know SQL is probably slower to create. What stored procedures definitely do is act as a gatekeeper to your database and prevent unstructured data access. Thanks again for producing so many quality learning resources

An absolutely great tutorial, thank you, Tim!

Would it make sense to move the stuff that's in the 'DBAccess' folder, namely the SqlDataAccess class, to a separate project, so that it could be reused with other projects that use other SQL databases? It knows nothing about specific tables, columns, etc., and it encapsulates all the Dapper stuff, so as you point out it's the only part of the application that deals with either Dapper or Sql.

I wonder if it is possible to unit test db layer with this approach same as you can with Entity Framework by using memory implementation of EF? That is one benefit I think EF has over every other approach (as far as I know) is that you can easily test all the operations that manipulate the database.

Hey Tim once again another beautiful video. Do you have a video explaining Lambdas? The process seems similar to Java however your tutorials are so great I would love to see one on Lambdas expressions

Brilliant as always Tim.

Thank you very much for this awesome video 😇

Tim Love using your videos to learn proper coding patterns and new technique, after this I wonder what you would recommend for data security, already have an Encryption Library, so any help with a proper pattern to secure the api other than that would be awesome.

Real thanks to Tim and community for helping out in comments!

Thanks Tim for this! Exactly what i need at the moment

Excellent "starting from scratch" video.

Hey Tim, thanks for making this video. I was looking for a up-to-date tutorial on dapper. Could you tell us when you will release part 2?

Hey Tim, Another great tutorial thank you. But I have one hesitation, if I recall correctly, PostgreSQL does not use dbo prefix? So I will have to re-write a separate UserData for it? Or is there a shorter way? I can probably do something in DataAccess class like if connectionID == "Postgres" then replace "dbo." with "" but I don't think this is the right way to do it. Maybe you mentioned this in Part-2 , moving to that one now. Thanks again for this great tutorial.

Hey Tim. Maybe you could do a video on how to do a scalable app? Now you have covered most aspect of real-world-applications, and lots of us do have real world app's out there, customers use. Now scalability becomes a problem, since one server instance is not enough. Should we do Azure Functions and let Azure scale our app? Should we do CosmosDB instead of MS SQL, because it's easily scalable? What about the serverless options? Serverless Functions? Serverless CosmosDB? There is even a serverless SignalR service. Like to hear your thoughts on this

Question with the single line methods on the UserData class, why don't you need 'await'? For example getUsers doesn't have await? On a side note thanks for the videos, they have been very helpful and educational.

Very nice presentation. Thanks for sharing your Knowledge. Keep going........

If I'm not mistaken, the first time you new up a SqlConnection, it creates an internal connection pool so later new ups just return a free connection from the pool.

After watching the whole video, and following along of course, it is very similar to your older video with a ton of wonderful little tidbits thrown in for the updated versions, and looks like a bit cleaner code as well. I still need to take your dapper course to get a better understanding of what is available. The quick intro to the stored procedures covered more than I thought it would in such a short time. I would like to see a 10 min video on how to change this DataAccess layer over to a different db like you mention. My current work process is using docker with a restored db from a backup from your video ( ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Yj69cWEG_Yo.html ), setting up in the appsettings.json and appsettings.development.json so when developing/updating the application ( also from your videos, I believe the paid course - worth every penny ). I just have to make sure I start the container, then when I publish it out it just moves to the live db. This gives a great way for me to just spin up a backup and replicate any issues the users are seeing in a safe environment for me to break.

Hi Tim, do you have any resources for understanding how to work with Dapper with Identity? Seems to me like a complete overhaul of their interfaces may be required but I'm afraid I might leave vulnerabilities. I really like the idea of using dapper and SQL Data Tools but can't seem to commit to it without some understanding of how to work without EF and identity. Small Edit: Just to add some context. For me this would include working in MVC with EF and Identity.

Great video! What are your thoughts on the scalability of this approach? Is a small query string here and there considered a bad practice? My only concern with stored procedures is that when looking at the code, you can't actually tell how you got that result, you'll have to go and find the stored procedure.

pretty cool video, really like that you used a sql database project! One question, for your get user method, is there a reason you used FirstOrDefault over SingleOrDefault? In the database since the id is the primary key if it returns more than one record wouldn't you want it to throw an exception?

This is awesome! Great video! I never take enough advantage of generics to be honest.

Hi Tim, In your advanced Dapper tutorial, you showed how to combine multiple datasets with Dapper. Do you know of a good way to write a generic implementation as done in this video, but capable of joining multiple datasets? If not, what would be your preferred way to handle such an issue (IE: Loading a user model which may have a phone model as a property)

really good video explaining how to build the api, made a different project with various stored procedures and it all worked fine testing it in swagger. It says in the video that it can be used with blazor server and loads of other stuff, but then there is nothing explaining how to link it all up, and its not much use having the api if you don't know how to link it to the project

Wow! This is simple and clean. can you please recommend an optimal way to implement retry logic along with Dapper? Let's say one of the DB operations fails due to a deadlock/intermediate connectivity issue, is there a direct way to retry like Polly for HTTP? As of now, I use a recursive function to retry one more time and before failing, but I am sure there should be a better way to handle this.

Is this the repository pattern or some version of it? I would love a video on that at some point! Thank you Tim

Fantastic... I'm going on to the next one... I'll have to pay for another class of some sort. Cash is almost as flattering as imitation...

Hi Tim, what are the benefits of creating the SqlDataAccess and UserData classes as non-static classes as opposed to static classes?

Hi Tim, Thanks for your great tutorials, I am an enthusiast rather than active developer, you have advised the seperation of identity context from data contextand I understand why, do you have anything on how to implement that in an .net core app, i.e. do you have 2 active contexts at the same time. Thanks again Ron Coy

Hi Tim, I brushed up my knowledge of SQL and SQL server in your “SQL Databases From Start to Finish” course and I learned how to do most of the SQL Server development in the “System.data.SqlClient”. This helped me a lot! The SqlClient draws the database into the application development environment and offers a single source development here. I am fully adapting the Only Stored Procedure approach to protect my data. I consider the relation ships between tables as an important part in that as it guarantees the relational characteristics of the database against programmer mistakes. But I am missing the relationship definitions in the SqlClient. Is there a way to overcome this? I Appreciate your videos, keep them coming.

You always have timely videos.

Thanks for a great video. At 1:08:00, I guess it might be better in the long run if you return inserted record after inserting it in DB and the same is true for the update and delete method.

Can't wait for part 2

10:45 Possible reasons for intellisense issue, 1) User is a reserved keyword, so brackets required for intellisense to work, i.e. use [User] instead of User. 2) Save the file User.Sql before using it in SPs

Awesome content for beginner to be a LEO ;). Thank you.

Amazing as always!

Why not add "Async" to the name of the async methods? (LoadData & SaveData)

Excellent video

One thing that wasn't apparent was how Dapper functioned because I saw the using Dapper statement but I didn't see a single instantiation of a Dapper object or helper. I sort of figured out that it extends the DBConnection object adding those methods but I don't think everyone will infer that. Still, I'm not sure that I prefer that over an explicit Dapper (ala SQLHelper) object that encapsulates that functionality.

Wow... love this workflow. I honestly have been looking for something that is easy to work with. I've tried FluentMigrator and the DB initializer technique and I think this is a best approach for me now, since I don't want to use EF Core. Every other course just use EF core but I want my codes to be optimized, thus Dapper. However, I find it clumsy to work with it when I need to make changes to the models or just simply seed more data. Thanks for the great content. I've canceled my every other subscriptions. I will sign up to your course soon.

Amazing vidoes Tim!! I wonder if is good practise to use sqlconnection as DI? And if yes could you please create a seperate video for this?

At 36:25 If you don't see Microsoft.Extensions.Configuration.Abstraction you can download .FileExtension and it will install that package as well.

Thanks' Tim for another awesome video.

Excelent. Tim. You change my way of thinking about database and layer implementation. Just one question. How can i return one or more output parameters?

Great video, helped me a lot in my work! One thing I didn't understand: the "Save data" method in the "ISqlDataAccess" interface has the following signature: SaveData(string ..., T ..., string ... = "Default"). When we use this method in the UserData class, we don't always "adhere" to this signature. For example: _db.SaveData("dbo.spUser_Update", user). It appears we don't pass in / specify the argument. I would have expected something like: _db.SaveData("dbo.spUser_Update", user). Why is the generic parameter not required to call this method? And am I right to assume that the string argument can be dropped because a default value has been defined in the SaveData method?

hey tim, very very nice course. So my question is, is there a way to log what dapper is doing while executing those procs? i.e what if in the update proc I had a method that checked whether a user with Id 2 exists and I found out he/she doesn't, in such a case I might be returning 31 from the proc. Is there a way to check for such a value being returned from the .net application?

Thank you for this Tim 🚀🚀 really helpful

You discuss the possibility of "abstracting out" the 'using' statements that are in both LoadData and SaveData. How about one way to make it a little simpler would be to put the connection string lookup in the class constructor right after _config is assigned. You could also instantiate the SqlConnection object itself in the constructor, but I guess the problem with that is you don't guarantee its properly disposed like it is in the 'using' blocks.

Hi Tim. Great video as always. I just finished part 2 and decided to try to implement another class that implements the DataAccess interface and use MySQL. I've entered a second connection string in the appsettings.json file, but it always tries to grab the "Default" one instead of the new one I've added for MySQL. I put in a breakpoint and inspected the value for the connectionId and found it to be "Default" even though my new MySqlDataAccess class is setting it to something else. I think it's doing this because it's being assigned in the interface itself. If I remove it from the interface then I don't have the correct number of arguments when the UserData class calls the new implementation. If I add it in the UserData class when it calls the DataAccess then every method needs to know the connection string name and changing it in the future would need it to be changed everywhere. Is there a way to get this to work the way it was intended, or am I just doing something wrong?

Thanks Tim! You rock man!

very useful video for me thanks tim

Hi,Tim. Why InsertUser,UpdateUser and DeleteUser are not async methods is you use Task as return type ?

Thanks Tim

Hello Tim!! I read in a article that is good practise to use .ConfigureAwait(false). Example await conn.ExecuteAsync(sql, param) .ConfigureAwait(false); Is it good practise ?

Thank you so much for enlightening us Mr. Corey! Can you tell me if SqlDataAccess class methods can be used with MySql instead of Microsoft SQL without significant modification? I am not sure if StoredProcedures will function the same way. And as far as I understand Identity is an Auto_Increment in MySql.

Great video! Thanks!

And another question: in the UserData class, one method has the "async" keyword, but the others have not. Why is that? - Method with "async" keyword: public async Task GetUser(int id) - Methods without "async" keyword: - public Task GetUsers() - public Task InsertUser(UserModel user) - public Task UpdateUser(UserModel user) - public Task DeleteUser(int id)

Hi Tim, is this really best practice? In my opinion, an ORM would be cleaner. For example, if you change FirstName varchar 50 --> 60, you have to change it on several places. In an ORM, you would just change the model an run a migration. What is your opinion about a clean best practice approach? (not talking about speed, since C# is already much faster than python).

Great video, Thank you! Q: Why dynamic? and Why are we obligated to pass an empty object in GetUsers 1:01 ?

Thank you