It's not often I look at code and say to myself, "That is a beautiful solution." I usually think, "There has got to be a better way" (e.g. whenever I look at Entity Framework or nHibernate code). This is a beautiful solution.
This two part series is awesome. I especially appreciate that you showed us everything using Dapper. You have a gift of making complex topics understandable. Thank you so much for sharing your talent with all of us.
I appreciate your presentation style immensely. The asides to talk about why you use Dapper for instance and your reasoning for why you put things in separate files as just two examples of many, adds a significant amount of value.
Thanks Tim .. i am such a huge fan of your work and efforts for making such great videos. I cannot thank you enough. I learnt a lot from your tutorials.. To be very honest with you .. Your tutorials give me two things 1. A whole lot of learning knowledge 2. It also does the ASMR thing when watching your tutorials really late.. So thank you much more.. Stay happy and blessed
First of all, love your videos! Few people are able to explain these things as succinctly as you. I would probably have stuck with the Controller way with this example but this is great way of covering all of the bases with minimal APIs.
Thank you for all of your great work, Tim! You're helping out alot and I'm so happy everytime I see your name among the suggestions when I'm searching for a tutorial. Love from Sweden!
Originally, I ignored Dapper since I found ORMs to be more of a pain than their worth (e.g. Entity Framework.) However, I just completed your Blazor course that used Dapper and it seemed very good. I am now using Dapper in one of my own (quick and dirty console app) projects and I have to admit, I am very impressed. It was VERY easy to implement and it is very intuitive. They did a really good job putting it together. So yeah, I will be using it going forward. Oh as an aside, your courses are EXCELLENT! Definitely worth the money.
Yet again amazing stuff man. It would be nice to see authorization and roles or claims working with the mapping methods. Or any newer security features that net 6 comes with
@@IAmTimCorey I second this! Amazing stuff here. Seeing an implementation of Identity or custom authentication using Dapper instead of EF would be great.
I too would like see security features AND how to publish everything to something like Azure. This is not meant to be a criticism because I obviously appreciate your help very much, but unless both of these are included, your videos are nothing more than academic exercises.
Hey Tim, I hope you see this comment! I think it would be awesome to see a part 3 or part 4 that goes over writing tests for this minimal setup! Thanks!
This is brilliant Tim, thanks. Got me from .NET6 zero to running in no time. A session on consuming the micro service would be very useful, especially deserialization. Also struggling to work out the best way to get a return value from a stored procedure in Dapper.
I'm sorry to bug you but I now have a problem which is driving me nuts and I can't find an answer anywhere. When you wrap the results of the db calls in Response.OK it creates an object which holds the model in a value property with the response status code. The problem is that when the JSON comes back on some calls it passes that wrapper object out e.g. {"value": {"obj_property1": "string","obj_property2": int},"statusCode": 200} and on some others it passes the raw object model e.g.{"obj_property1": "string","obj_property2": int}. It also shows in the response body in Swagger. Is there something that controls this and can be used to force the response body to be just the object model, or is it a bug?
Now I wrote that, I think I have the answer, something to do with using var results = data.getWhatever in the mapped function. If I use a model type instead of var e.g. Users results = data.GetUsers then it never passes out the Response.OK wrapper in the JSON
I'm using .net 5 I don't suppose I can continue the video with that. So far I've been using it to follow up but I guess I'd have to update it this night. Great video so far though. You practically answered a lot of questions I've been asking. All your videos are really great
Hello Tim! Extraordinary tutorials. I'd love to see even more parts on this. Authorization, Tests and more of something that I don't know of ;D Maybe some do's and don'ts as well.
GREAT content! Love how you made it practical rather than dump all code in program.cs. You just earned a new subscriber, thanks for your hard work! Would be great to see a part 3 for frontend form.
Really liking this series. Jwt tokens with refresh would be a great addition to the series. There is just no very well explained video out there.Hope we get to this at somepoint.Great work
Thanks for getting this out quickly. I got to the end of part one last week and then realised "Oh man it was Part 1!" :) I was a bit disappointed, but I was also pretty confident it wouldnt be too long. Cheers
A part 3 taking this into controllers and a fuller API would be nice using the modern .Net6 and the existing foundation work from part 1 and 2. A little app the consumes the API would be good too show it come full circle.
It was a fun to watch the 2 parts and trying to put in the source code at the same time you did. I have all the code, now but when i try to access my database within swagger i get the following error message: "title": "An error occurred while processing your request.", "status": 500, "detail": "Keyword not supported: 'trust server certificate'." I verified the connection string several times and it works. The 'certificate' is set to false. Any ideas?
@@IAmTimCorey Yeah, it looks like a problem in the connection string. I watched both parts of the videos a 3rd time now to verify that there are no typing errors in. Its all fine but what i noticed are the spaces in my string which are missing in yours: Data Source=sql-server2017;User ID=sa;Password=********;Connect Timeout=60;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False Since the string is auto configured by Visual Studio, i dont know where to change it. My SQL-Server runs on a Windows Server 2012 R2 in a Hyper-V Machine on my LAN.
So, a connection string is unique to your setup. If you are copying mine, that won't work. It looks like your data source is referencing an instance of SQL called sql-server2017. If you are using a named instance and not the default, it should look like this: .\sql-server2017 (with the dot and slash at the beginning).
@@IAmTimCorey Its working now. I have 2 json files: appsettings.json + appsettings.Development.json and I had to change both files. My connection string looks like this, now: "Data Source=sql-server2017;Initial Catalog=MinimalAPIUserDB;User ID=sa;Password=My_secret_password" Thank you very much for your help. :)
Hi Tim, Great tutorial. Do you have a course on Dapper using the generic repository you have used in this course that shows how you will handle pagination and lazy loading? The LoadData... does not support this, or am I missing something? How will you implement this?
Pagination is typically handled at the database. It is just two extra parameters you pass in the call (pageNumber and pageSize). As for lazy loading, that's on you. You don't make the call to Dapper until you need the data.
I love your video series. I'm newish to using APIs, do you have something that shows how to use the api in something simple like a winform or console app? Also, how to use dapper with more complex objects, including one to one or one to many, etc. Can't wait to see your next video!
Thanks for the great work Tim, i would love to see another application making tutorial from start to finish like the tournament tracker, a lot has changed with visual studio and would love to catch up with your lessons!
Great series! Thanks for the content! Learned alot! Maybe a suggestion for Part 3 could be to look at how you would go about do unit or integration tests with Dapper? Seem people seem to want to do mock, others have in memory database and some suggest using real databases. Would be nice to hear your thoughts on this and how you would go about testing this.
Great two part course. I have a similar question - you use static classes and methods. Wouldn't this affect the ability to write unit tests? I'd love to hear your thinking on this.
Another great video Tim, thanks! Suggestion for part 3, a simple Blazor (server and WebAssembly version maybe?) that shows how to connect to and consume the API?
Brilliant video! Just brilliant! It’s incredible how many things I understood about API from this video. But you mentioned that there are some specifics with how API is being consumed by Blazor WebAssembly, Mr. Corey? Would you kindly point me to the video about that (I saw only some parts of TimCO Retail Manager about WebAssembly)?
Cheers for another good video, couple of things I would change, include not found logic in endpoints where an id is used and correct the inconsistency with the routing for the delete action. The way it was still requires the id in the query string where you have another endpoint expecting it as part of the route
Hello, another great video and tutorial as always. I really like how you make the dapper query stuff generic in the data access class. Can I ask how you would do that when your SQL returns multiple object or multiple sets.
It depends on what you mean. By default, SQL returns records (it doesn't know about objects). So one record would be one object. However, sometimes people want to take one record and turn it into multiple objects. That would be up to you once you have the data back from Dapper. However, you can return multiple result sets from a query. In that case, yes, Dapper can handle that natively. Here is a video on that and more that Dapper can do: ua-cam.com/video/eKkh5Xm0OlU/v-deo.html
@@IAmTimCorey Just a quick question, I’m trying to populate a list of type ITenants and I want to use the api method gettenants which I set to public but the problem the method returns IResult, therefore I cannot populate the IList. Please help me Thanks
Hi Tim, Thanks for this video. I have created Minimal API db layer for my application. Almost all sceanrios are working but I am getting null exception when I try to get the reulst for Select statement and output parameter together. How can I achieve select result and Output parameter value using QueryAsync Dapper method. Appreciate your help.
Hi Tim, Thanks for taking the time to put these tutorials together, I appreciate that you explained some of the differences and tooling between .NET 6 and previous versions. I learned a lot and as always was able to follow closely with the tutorial. One question I do have is when considering whether it is better to expand into controllers when you are working with multiple tables, or is it better to create multiple Api classes with different names? (I.E. Users & Products)
Hi Tim. I'm new to .NET but have been tasked with creating some web apps for my company. I followed this tutorial all the way to the end, and then realized I don't know how to modify the final product to output to a Razor Page instead of your API. Is there anywhere I could find that information? I am not savvy enough yet to understand how to adapt your code for a different interface, but would love to learn how. For reference, I am creating a report from stored procedures, passing in several parameters, but otherwise it is the same basic setup as your tutorial. Any guidance would be greatly appreciated!
Cool layout and structure, was a breeze until I tried adding Logging (Serilog) to the private static async Tasks that the endpoints are mapped to, what's the trick? Maybe have part 3 where you add Serilog and Azure Key Vault :) . Thank you by the way I really did like the project structure! Wow seeing a lot of sugestions for a Part 3, maybe I'm 4 or 5. MS admits there are no best practices yet, perhaps you can make some...
Great Video as always - Could you do a Video on Authentication / Authorization for ASP.NetCore / Blazor for Windows Authentication (Using Active Directory on prem) ? Would be very happy about that... :)
This tutorial is excellent in presenting a clean way of separating data access code. One thing I feel like it's missing is using this in conjunction with relational objects which is the benefit of using a relational database in the first place. For this example, say I added in a Blogpost model and created a BlogpostData class. Blogposts are owned by a user, so when a Blogpost is created the primary key of the user who created it needs to be stored as a foreign key in the Blogpost table. When creating a new blogpost, would it be appropriate to make calls from the UserData class inside the Blogpost class in order to complete this transaction?
These can be relational objects and they wouldn't change much. Let's take your BlogPost example. So a User creates a BlogPost object. You want to transmit that to the API. You could send a nested object, and that is the right call in some circumstances. However, when you write that to the database, you don't need to make two calls. Instead, you write to the spBlogPost_Save stored procedure. That procedure is going to take in all of the parts of that blog post, including the UserId who created it. You probably can stop there. You don't need to call the user table because you just used the UserId as a foreign key. So no transaction necessary because you aren't writing to two tables, just one. So one API call to BlogPost (should we call it BlogPostPOST?) gives you all of the data you need. The way the caller got that UserId would be that they called the User endpoint (GET) to get all of the users, or they used the authenticated user to the API if they can only post under their own account.
Thanks Tim these two videos have been a great help. For people looking to use 'minimal' APIs for not so minimal workloads check out Nick Chapsas "In defence of .NET Minimal APIs. Refactoring" there are some good ideas for giving minimal APIs some of the 'structure' of the controller approach. And a lot of code very similar to Tim's which is always a plus!
Hello! This 2 part series was really helpful, however I am trying to hook up the data access to a wpf project and I am not quite sure how I would do that. For example how would I get all the users into something like a list or ObservableCollection inside of a viewmodel?
Hey Tim, thanks for this great tutorial, I wanted to ask if you could make some tutorials about clean architecture and that kinda stuff, it will be super helpful, I'm currently trying to learn those topics, and the first place that I thought I can find the best tutorials about it will be your channel, but unfortunately, you haven't covered them yet, I think no one can explain those topics better than you :D, that was just a suggestion, Thanks
Having some user id weirdness when creating new users. My first 4 from initial db publish were created as 1-4 as expected; however, the swagger created users have ids above 1000. Any idea why they werent created incrementally as 5, 6, 7, etc? I am using .Net6 and my nuget package version numbers were identical to yours.
This is a feature of SQL. When you restart SQL Server, it skips ID numbers (which doesn’t hurt anything). The reason why is because if there were records that weren’t actually written before the shutdown, they might have IDs that are beyond 4 (the last know ID). If that were true but you had picked up at 5 in the meantime, you would have an ID conflict. Jumping to 1000 ensures that won’t be the case. Basically, it is a data safety mechanism after a SQL shutdown. You won’t normally see it in production because you will rarely shut down your SQL Server.
This happened to me also, Karen was 1002 for me. I think I restarted for an update between days from watching the 1st part yesterday and this part today. Good video!
Your service implementations should be internal sealed, especially living in a segregated domain. Callers shouldn't have access to the implementations, only the interfaces. After you do that, the service registration should be handled in the same domain (via extension). Now the caller needs to know NOTHING about the inner workings of your data layer (i.e. how they should be registered, the underlying implementations, etc.). Also, both of your services should have been registered as Transient. Neither of those services have a state, there is no reason to have them live longer than they need to. Also, a little nit-pickey, but your HttpDelete should have the id parameter coming from the route, not the query string. It is more "restful" this way. I just found you here on UA-cam, I really like what I've seen so far. Great job.
Would you consider a constructor in the api class that injects IUserData as opposed to adding it as a parameter in each method or make the UserData class static and available everywhere?
Great video, thanks. Would it be easy to configure and return the Identity value from newly inserted row? I have the SQL part fine, but not sure about the C# code portion.
We pass that value in the body, not on the URL. We do that for any action that changes data. Otherwise, you could accidentally bookmark a URL that included an action that changed a value. We only add the parameter on the URL when it is a read action (a GET).
It was simple explanation and demonstration of web api. loved it. I am going to use this concept in my new project. You are talking about the controllers . Can you have any demo around it?
The TimCo Retail Manager series here on UA-cam uses a full API with controllers (first in .NET Framework and then we upgrade it to .NET Core): ua-cam.com/play/PLLWMQd6PeGY0bEMxObA6dtYXuJOGfxSPx.html If you want to see API design in more depth, I just released a course on ASP.NET Core Web API (.NET 6): www.iamtimcorey.com/p/web-api-from-start-to-finish
Thanks Tim, Got one query, If I used Dapper with Controller/ services, I will be injecting context interface to Service class. How should i define scope for dbConext class. Singelton or Scoped ?
Tim, could you PLEASE show how to adapt your SqlDataAccess LoadData and one of the data class methods to make the dapper call to map both the primary model and a related child model. I love this data access library, and it's working great for single tables with no related data anywhere else, but I'm having problems adapting it to multi-object mapping the way you did back in your advanced dapper video.
Very helpful video. If the data models were in their own project (say, DataModels), I take it you would need to add references to both the DataModels and DataAccess projects in the MinimalAPIDemo project? Is there any way to achieve a separation of concerns, e.g. MinimalAPIDemo -> DataAccess -> DataModels?
2 роки тому+1
I would like to ask another question, if I separate Models into another project is it ok to reference them from API project and from BlazorServer project which will call API? So I have same models used in API and BlazorServer? Or is it better to define new models inside BlazorServer app.
Hi Tim, for the delete and update endpoints, what should be done if record does not exists with the given? at the moment, we are returning 200 ok even though we haven't updated or deleted anything in the case of no record existing
It depends. For delete, you might want to return a 200 because the operation completed successfully. But if you do that, you might also want to return the number of records affected. Or you might want to return a 404, indicating the record was not found. Update is similar, although you might want to create the record or you might want to return an error code indicating failure.
Hi, thanks for the great tutorial. I have programmed an API and used a Sql Local database with Dapper as datastorage. In the table I have a column as bit with default value 0. If I execute a PUT via the API, the value is set to 1 as desired (VS DB = true, SSMS = 1) in the database, but unfortunately the GET query always returns the value false. Debugging has shown that the value is already received as a return value in the DataAccess (db.Load...) as false. I am using VS2019 and the project is running on .NetCore 6. Do you have any idea what can cause the problem? Greetings and Kind Regards
Thanks for the great tutorial! For some reason, when I retrieve data from the database, the FirstName and LastName fields have a length of 50 characters with trailing spaces. However, in the database, they are stored without spaces.
When you set up your files in the database, you chose nchar or char for your field type. They will always give you that many characters using trailing spaces. You want nvarchar or varchar to get a variable length result.
@@IAmTimCorey Yes, indeed, I just didn’t notice that when creating the table I specified nchar(50) for the fields instead of nvarchar(50). Thank you very much!
Excellent video as always. Thank you for sharing. Could you please provide some additional hint on how to get a different connection ID into our data access beside the Default one cleanly? Some time you may need to connect to different databases using the same DataAccess class.
The third parameter, which is optional, on LoadData and SaveData is the name of the connection string to find in appsettings. Just pass in a different connection string name and it will use that connection string instead.
Just wanted to point out that ConfigureApi method doesn't necessarily need to be an extension method. If you remove "this" from the argument list, then you just need to call it through the class name and supply 'app' as an argument: Api.ConfigureApi(app); instead of app.ConfigureApi();
Hi Tim, loving the help you are providing, i have a question though. When i post a new user, the id goes from 4 to 1005... why is that and how do i go about changing it, to have be 5 automatically.
Don't worry about it and definitely don't try to change it. The basics are that SQL writes inserts to temporary storage before it inserts them into your table. If your SQL Server reboots unexpectedly (such as on a dev machine when you restart), it isn't sure if it has processed all of those entries when you start back up before you start asking for new ID numbers. So, it intentionally skips 1000 so that it can use those numbers for any that were still in the queue without causing any problems. The ID number is not something you should try to control. Let SQL do that. The only thing that matters is that it increments. Whether that next value is 1 away or 1000 doesn't matter to SQL.
Hi Tim, how would one structure a larger project with many endpoints. Having a HUGE Api.cs does not seem viable (if you are exposing lets say a 100 endpoints).
@@IAmTimCorey okay, you mentioned that you can transition to controllers, would that be as simple as call UseControllers() in program.cs and start adding controllers the traditional way?
Thanks Tim! Great 2 part series here. I'm still new to .Net Core and I'm starting out with Razor Pages. How would I adopt this DataAccess layer inside a Razor Page display? Also still learning C#.
It is the same data access layer. Just put it in dependency injection (in the Program.cs file) in your Razor Pages app and then ask for it whenever you need to access data.
Thanks Tim for great stuff. I really like your videos and presentation style. Can we do authorization and data annotation validations with minimal APIs?
I was just wondering Tim, I watched your last video and wanted to ask why separate the class library from the database api? Is it to make it cleaner or is there some sort of security reason? Or maybe reusability?
It can make it more reusable, but probably not in the way you think. I like to separate my business logic and data access from my UI layer (and yes, an API is a UI layer - it isn't a GUI, but it is a UI). That way, if I ever need to change out the UI layer, I have cleaner separation to do so. It also allows me to use the same library to connect to a desktop app directly. That way, the API can serve the data or the desktop app can access it directly (which improves performance). It also encourages best practices of separating your concerns. The UI should not know about the business logic and the business logic should not know about the database.
a part 3 will be good to see some advance things for example pagination or lazy loading and many to many , one to many thank you
I will add it to the list. Thanks for the suggestion.
@@IAmTimCorey also versioning of the API
perhaps rest models that will limit data we send towards frontend
It's not often I look at code and say to myself, "That is a beautiful solution."
I usually think, "There has got to be a better way" (e.g. whenever I look at Entity Framework or nHibernate code).
This is a beautiful solution.
Thank you!
This two part series is awesome. I especially appreciate that you showed us everything using Dapper. You have a gift of making complex topics understandable. Thank you so much for sharing your talent with all of us.
You are welcome.
I appreciate your presentation style immensely. The asides to talk about why you use Dapper for instance and your reasoning for why you put things in separate files as just two examples of many, adds a significant amount of value.
Thanks for sharing.
Thanks Tim .. i am such a huge fan of your work and efforts for making such great videos.
I cannot thank you enough. I learnt a lot from your tutorials..
To be very honest with you .. Your tutorials give me two things
1. A whole lot of learning knowledge
2. It also does the ASMR thing when watching your tutorials really late..
So thank you much more..
Stay happy and blessed
You're very welcome!
Tim is the stepdad who came back and actually cares about our mom! Thank you
Uh, you are welcome. Now do your homework.
First of all, love your videos! Few people are able to explain these things as succinctly as you. I would probably have stuck with the Controller way with this example but this is great way of covering all of the bases with minimal APIs.
Thanks!
Thank you for all of your great work, Tim! You're helping out alot and I'm so happy everytime I see your name among the suggestions when I'm searching for a tutorial. Love from Sweden!
You are welcome.
I very much like these 2 minimalAPI videos. Really very good Tim.
Thanks!
Originally, I ignored Dapper since I found ORMs to be more of a pain than their worth (e.g. Entity Framework.) However, I just completed your Blazor course that used Dapper and it seemed very good. I am now using Dapper in one of my own (quick and dirty console app) projects and I have to admit, I am very impressed. It was VERY easy to implement and it is very intuitive. They did a really good job putting it together. So yeah, I will be using it going forward.
Oh as an aside, your courses are EXCELLENT! Definitely worth the money.
Awesome! I'm glad it was helpful. Thanks for sharing.
Yet again amazing stuff man. It would be nice to see authorization and roles or claims working with the mapping methods. Or any newer security features that net 6 comes with
I will add it to the list. Thanks for the suggestion.
@@IAmTimCorey I second this! Amazing stuff here. Seeing an implementation of Identity or custom authentication using Dapper instead of EF would be great.
@@IAmTimCorey Adding some identity and authentication using Dapper will be great!
I too would like see security features AND how to publish everything to something like Azure.
This is not meant to be a criticism because I obviously appreciate your help very much, but unless both of these are included, your videos are nothing more than academic exercises.
You have consistently won my trust that the first thing I open your video is a thumb up.
Awesome!
Thank you, I appreciate the fact that you provide detailed information and that you take time to explain everything!
You are welcome.
So stoked for this. It’s perfect for a project I’m doing at work, and I was waiting for this to publish 🙂
Awesome!
Hey Tim, I hope you see this comment! I think it would be awesome to see a part 3 or part 4 that goes over writing tests for this minimal setup! Thanks!
I will add it to the list. Thanks for the suggestion.
I second that, not only tests, but adding more real complex scenarios, such as, pagination, dynamic queries/filters, caching, etc… Nice videos Tim!!!!
Thanks Tim! As always, the best tutorials on UA-cam.
Glad you like them!
Wonderful. Amazing how simple it seems to keep it simple while at the same time do complex things.
Thanks!
These two videos were very helpful. Thank you.
You are welcome.
This is brilliant Tim, thanks. Got me from .NET6 zero to running in no time. A session on consuming the micro service would be very useful, especially deserialization. Also struggling to work out the best way to get a return value from a stored procedure in Dapper.
I am glad it was helpful.
I'm sorry to bug you but I now have a problem which is driving me nuts and I can't find an answer anywhere. When you wrap the results of the db calls in Response.OK it creates an object which holds the model in a value property with the response status code. The problem is that when the JSON comes back on some calls it passes that wrapper object out e.g. {"value": {"obj_property1": "string","obj_property2": int},"statusCode": 200} and on some others it passes the raw object model e.g.{"obj_property1": "string","obj_property2": int}. It also shows in the response body in Swagger. Is there something that controls this and can be used to force the response body to be just the object model, or is it a bug?
Now I wrote that, I think I have the answer, something to do with using var results = data.getWhatever in the mapped function. If I use a model type instead of var e.g. Users results = data.GetUsers then it never passes out the Response.OK wrapper in the JSON
I'm using .net 5 I don't suppose I can continue the video with that. So far I've been using it to follow up but I guess I'd have to update it this night. Great video so far though. You practically answered a lot of questions I've been asking. All your videos are really great
Yep, this is a .NET 6 video. I'm glad you are finding value in it.
Hello Tim! Extraordinary tutorials. I'd love to see even more parts on this. Authorization, Tests and more of something that I don't know of ;D
Maybe some do's and don'ts as well.
Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
This series was fantastic, thanks Tim!
You are welcome.
you are my mentor, i like c# development more and more because of you ,thanks man...
My pleasure!
GREAT content! Love how you made it practical rather than dump all code in program.cs. You just earned a new subscriber, thanks for your hard work! Would be great to see a part 3 for frontend form.
Thanks for the Sub! Also, keep an eye out for Tim's upcoming course suggestion sight. Would love to have you post your ideas there once its available.
Really liking this series. Jwt tokens with refresh would be a great addition to the series. There is just no very well explained video out there.Hope we get to this at somepoint.Great work
Thanks for the videos (both part 1 and 2). Such a great way to learn with so much explanations
You are welcome.
Awesome! This was exactly what I needed after completing your Blazor Server course.
Great to hear!
cool api example.
Suggestion - you could have made the manual testing at the end as unit tests - perhaps part 3 ?
I liked the startup.cs idea too. seemed like a clean separation even if it was busy work.
Thanks for getting this out quickly. I got to the end of part one last week and then realised "Oh man it was Part 1!" :) I was a bit disappointed, but I was also pretty confident it wouldnt be too long. Cheers
You are welcome.
4 days delay in youtube notification
Thanks for the part 2!
You are welcome.
As always Tim, a great video, much appreciated, as they say "Every day's a school day" :)
You are welcome.
A part 3 taking this into controllers and a fuller API would be nice using the modern .Net6 and the existing foundation work from part 1 and 2. A little app the consumes the API would be good too show it come full circle.
Thanks for the suggestion.
Tim, you are the best!!, much appreciated!!
Thank you!
Great video, Tim. Thank you. A simple part 3 would be fantastic if showing how to consume the data in a Blazor Server App.
I actually did that in an upcoming API course.
Holy balls, at 26:00 or so, all the build up to this clicked. That makes sooooo much life easier.
Awesome!
It was a fun to watch the 2 parts and trying to put in the source code at the same time you did. I have all the code, now but when i try to access my database within swagger i get the following error message:
"title": "An error occurred while processing your request.",
"status": 500,
"detail": "Keyword not supported: 'trust server certificate'."
I verified the connection string several times and it works. The 'certificate' is set to false.
Any ideas?
I'm not sure what you mean by "it works" in reference to your connection string. It sounds like that is the issue here.
@@IAmTimCorey Yeah, it looks like a problem in the connection string. I watched both parts of the videos a 3rd time now to verify that there are no typing errors in. Its all fine but what i noticed are the spaces in my string which are missing in yours:
Data Source=sql-server2017;User ID=sa;Password=********;Connect Timeout=60;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False
Since the string is auto configured by Visual Studio, i dont know where to change it. My SQL-Server runs on a Windows Server 2012 R2 in a Hyper-V Machine on my LAN.
So, a connection string is unique to your setup. If you are copying mine, that won't work. It looks like your data source is referencing an instance of SQL called sql-server2017. If you are using a named instance and not the default, it should look like this: .\sql-server2017 (with the dot and slash at the beginning).
@@IAmTimCorey Its working now. I have 2 json files: appsettings.json + appsettings.Development.json and I had to change both files. My connection string looks like this, now:
"Data Source=sql-server2017;Initial Catalog=MinimalAPIUserDB;User ID=sa;Password=My_secret_password"
Thank you very much for your help. :)
Hi Tim, Great tutorial. Do you have a course on Dapper using the generic repository you have used in this course that shows how you will handle pagination and lazy loading? The LoadData... does not support this, or am I missing something? How will you implement this?
Pagination is typically handled at the database. It is just two extra parameters you pass in the call (pageNumber and pageSize). As for lazy loading, that's on you. You don't make the call to Dapper until you need the data.
I love your video series. I'm newish to using APIs, do you have something that shows how to use the api in something simple like a winform or console app? Also, how to use dapper with more complex objects, including one to one or one to many, etc. Can't wait to see your next video!
Here is a video on Advanced Dapper: ua-cam.com/video/eKkh5Xm0OlU/v-deo.html
Here is a video on calling APIs: ua-cam.com/video/aWePkE2ReGw/v-deo.html
Tx u ❤️, please keep doing more like this sir
You are welcome.
Thanks for the great work Tim, i would love to see another application making tutorial from start to finish like the tournament tracker, a lot has changed with visual studio and would love to catch up with your lessons!
One like that is coming.
Great series! Thanks for the content! Learned alot! Maybe a suggestion for Part 3 could be to look at how you would go about do unit or integration tests with Dapper? Seem people seem to want to do mock, others have in memory database and some suggest using real databases. Would be nice to hear your thoughts on this and how you would go about testing this.
I will add it to the list. Thanks for the suggestion.
Great two part course. I have a similar question - you use static classes and methods. Wouldn't this affect the ability to write unit tests? I'd love to hear your thinking on this.
Another great video Tim, thanks! Suggestion for part 3, a simple Blazor (server and WebAssembly version maybe?) that shows how to connect to and consume the API?
Brilliant video! Just brilliant! It’s incredible how many things I understood about API from this video.
But you mentioned that there are some specifics with how API is being consumed by Blazor WebAssembly, Mr. Corey? Would you kindly point me to the video about that (I saw only some parts of TimCO Retail Manager about WebAssembly)?
Cheers for another good video, couple of things I would change, include not found logic in endpoints where an id is used and correct the inconsistency with the routing for the delete action. The way it was still requires the id in the query string where you have another endpoint expecting it as part of the route
was waiting for part 2. thank you, MASTER.
You are welcome.
Hello, another great video and tutorial as always. I really like how you make the dapper query stuff generic in the data access class. Can I ask how you would do that when your SQL returns multiple object or multiple sets.
It depends on what you mean. By default, SQL returns records (it doesn't know about objects). So one record would be one object. However, sometimes people want to take one record and turn it into multiple objects. That would be up to you once you have the data back from Dapper. However, you can return multiple result sets from a query. In that case, yes, Dapper can handle that natively. Here is a video on that and more that Dapper can do: ua-cam.com/video/eKkh5Xm0OlU/v-deo.html
useful just implemented this in my blazor server app after finishing start to finish blazor server!
Nice!
@@IAmTimCorey Just a quick question, I’m trying to populate a list of type ITenants and I want to use the api method gettenants which I set to public but the problem the method returns IResult, therefore I cannot populate the IList.
Please help me
Thanks
Hi Tim, Thanks for this video. I have created Minimal API db layer for my application. Almost all sceanrios are working but I am getting null exception when I try to get the reulst for Select statement and output parameter together. How can I achieve select result and Output parameter value using QueryAsync Dapper method. Appreciate your help.
Hi Tim,
Thanks for taking the time to put these tutorials together, I appreciate that you explained some of the differences and tooling between .NET 6 and previous versions. I learned a lot and as always was able to follow closely with the tutorial.
One question I do have is when considering whether it is better to expand into controllers when you are working with multiple tables, or is it better to create multiple Api classes with different names? (I.E. Users & Products)
Thanks Tim for this two series of dapper. Related to SQL I prefer to create a clean script directo in SQL and manage all the code there.
Very interesting, thank you so much!
You are welcome.
Thanks for this part 2
You are welcome.
the best tutorial i've found, great explanation thanks a lot mr.tim for these kind of content. keep up
You are welcome.
Awesome video Tim. Thanks for sharing
You are welcome.
Hi Tim. I'm new to .NET but have been tasked with creating some web apps for my company. I followed this tutorial all the way to the end, and then realized I don't know how to modify the final product to output to a Razor Page instead of your API. Is there anywhere I could find that information? I am not savvy enough yet to understand how to adapt your code for a different interface, but would love to learn how. For reference, I am creating a report from stored procedures, passing in several parameters, but otherwise it is the same basic setup as your tutorial. Any guidance would be greatly appreciated!
Thanks for this minimal API video. Ver well explained!
You are welcome.
Thanks for the time, great video!
You are welcome.
Hi @IAmTimCorey, thanks for this video. I would like to ask: Which architectural pattern did you use for this solution? Is it LAYERED or Pipe-Filter?
Cool layout and structure, was a breeze until I tried adding Logging (Serilog) to the private static async Tasks that the endpoints are mapped to, what's the trick? Maybe have part 3 where you add Serilog and Azure Key Vault :) . Thank you by the way I really did like the project structure! Wow seeing a lot of sugestions for a Part 3, maybe I'm 4 or 5. MS admits there are no best practices yet, perhaps you can make some...
Great Video as always - Could you do a Video on Authentication / Authorization for ASP.NetCore / Blazor for Windows Authentication (Using Active Directory on prem) ?
Would be very happy about that... :)
I will add it to the list. Thanks for the suggestion.
This tutorial is excellent in presenting a clean way of separating data access code. One thing I feel like it's missing is using this in conjunction with relational objects which is the benefit of using a relational database in the first place. For this example, say I added in a Blogpost model and created a BlogpostData class. Blogposts are owned by a user, so when a Blogpost is created the primary key of the user who created it needs to be stored as a foreign key in the Blogpost table. When creating a new blogpost, would it be appropriate to make calls from the UserData class inside the Blogpost class in order to complete this transaction?
These can be relational objects and they wouldn't change much. Let's take your BlogPost example. So a User creates a BlogPost object. You want to transmit that to the API. You could send a nested object, and that is the right call in some circumstances. However, when you write that to the database, you don't need to make two calls. Instead, you write to the spBlogPost_Save stored procedure. That procedure is going to take in all of the parts of that blog post, including the UserId who created it. You probably can stop there. You don't need to call the user table because you just used the UserId as a foreign key. So no transaction necessary because you aren't writing to two tables, just one. So one API call to BlogPost (should we call it BlogPostPOST?) gives you all of the data you need. The way the caller got that UserId would be that they called the User endpoint (GET) to get all of the users, or they used the authenticated user to the API if they can only post under their own account.
Amazing tutorial with more useful details, Thank you 🔅🔅🤗🤗
You are welcome.
Thanks Tim these two videos have been a great help. For people looking to use 'minimal' APIs for not so minimal workloads check out Nick Chapsas "In defence of .NET Minimal APIs. Refactoring" there are some good ideas for giving minimal APIs some of the 'structure' of the controller approach. And a lot of code very similar to Tim's which is always a plus!
Awesome, great explanation as always!!
Thanks!
Excelent video as usual.
Thank you!
Great Video! Thanks for sharing
You are welcome.
Hello! This 2 part series was really helpful, however I am trying to hook up the data access to a wpf project and I am not quite sure how I would do that. For example how would I get all the users into something like a list or ObservableCollection inside of a viewmodel?
You take the List you get from Dapper and convert it into the ObservableCollection.
Hey Tim, thanks for this great tutorial, I wanted to ask if you could make some tutorials about clean architecture and that kinda stuff, it will be super helpful, I'm currently trying to learn those topics, and the first place that I thought I can find the best tutorials about it will be your channel, but unfortunately, you haven't covered them yet, I think no one can explain those topics better than you :D, that was just a suggestion, Thanks
Thanks For Tutorial .
So Useful.
You are welcome.
Having some user id weirdness when creating new users. My first 4 from initial db publish were created as 1-4 as expected; however, the swagger created users have ids above 1000. Any idea why they werent created incrementally as 5, 6, 7, etc? I am using .Net6 and my nuget package version numbers were identical to yours.
This is a feature of SQL. When you restart SQL Server, it skips ID numbers (which doesn’t hurt anything). The reason why is because if there were records that weren’t actually written before the shutdown, they might have IDs that are beyond 4 (the last know ID). If that were true but you had picked up at 5 in the meantime, you would have an ID conflict. Jumping to 1000 ensures that won’t be the case. Basically, it is a data safety mechanism after a SQL shutdown. You won’t normally see it in production because you will rarely shut down your SQL Server.
@@IAmTimCorey That makes sense. Thank you.
This happened to me also, Karen was 1002 for me. I think I restarted for an update between days from watching the 1st part yesterday and this part today. Good video!
Hi Tim, great demo! why do you want to hide the api methods (private)? wouldn't that make them harder to test?
Your service implementations should be internal sealed, especially living in a segregated domain. Callers shouldn't have access to the implementations, only the interfaces. After you do that, the service registration should be handled in the same domain (via extension). Now the caller needs to know NOTHING about the inner workings of your data layer (i.e. how they should be registered, the underlying implementations, etc.).
Also, both of your services should have been registered as Transient. Neither of those services have a state, there is no reason to have them live longer than they need to.
Also, a little nit-pickey, but your HttpDelete should have the id parameter coming from the route, not the query string. It is more "restful" this way.
I just found you here on UA-cam, I really like what I've seen so far. Great job.
Would you consider a constructor in the api class that injects IUserData as opposed to adding it as a parameter in each method or make the UserData class static and available everywhere?
An API is a bit different from a normal class. The method is acting as the constructor.
Great video, thanks. Would it be easy to configure and return the Identity value from newly inserted row? I have the SQL part fine, but not sure about the C# code portion.
Here is a video to help explain it: ua-cam.com/video/b8Zev2qU5IE/v-deo.htmlsi=W764KBPGWwiXQJ6R
Thanks Tim, but what I don’t understand is, why you don’t have have a parameter (id) in the app.MapDelete, while you have it in the method.
We pass that value in the body, not on the URL. We do that for any action that changes data. Otherwise, you could accidentally bookmark a URL that included an action that changed a value. We only add the parameter on the URL when it is a read action (a GET).
@@IAmTimCorey Thanks for the explanation Tim I was wondering this too
It was simple explanation and demonstration of web api. loved it. I am going to use this concept in my new project. You are talking about the controllers . Can you have any demo around it?
The TimCo Retail Manager series here on UA-cam uses a full API with controllers (first in .NET Framework and then we upgrade it to .NET Core): ua-cam.com/play/PLLWMQd6PeGY0bEMxObA6dtYXuJOGfxSPx.html
If you want to see API design in more depth, I just released a course on ASP.NET Core Web API (.NET 6): www.iamtimcorey.com/p/web-api-from-start-to-finish
@@IAmTimCorey Thanks for quick response. Does this course include how to consume minimal web api?
It covers consuming an API. It doesn’t matter if it is minimal or not.
Great!, do you have an exemple using relationed tables?
Do you have the generic implementation for MultipleQueryAsync for the SqlDataAccess?
Thanks Tim, Got one query, If I used Dapper with Controller/ services, I will be injecting context interface to Service class. How should i define scope for dbConext class. Singelton or Scoped ?
Tim, could you PLEASE show how to adapt your SqlDataAccess LoadData and one of the data class methods to make the dapper call to map both the primary model and a related child model. I love this data access library, and it's working great for single tables with no related data anywhere else, but I'm having problems adapting it to multi-object mapping the way you did back in your advanced dapper video.
Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
@@IAmTimCorey Thanks, I saw there was already a similar suggestion, so I registered on the suggestion app, and added my vote.
Very helpful video. If the data models were in their own project (say, DataModels), I take it you would need to add references to both the DataModels and DataAccess projects in the MinimalAPIDemo project? Is there any way to achieve a separation of concerns, e.g. MinimalAPIDemo -> DataAccess -> DataModels?
I would like to ask another question, if I separate Models into another project is it ok to reference them from API project and from BlazorServer project which will call API? So I have same models used in API and BlazorServer? Or is it better to define new models inside BlazorServer app.
Hi Tim, for the delete and update endpoints, what should be done if record does not exists with the given? at the moment, we are returning 200 ok even though we haven't updated or deleted anything in the case of no record existing
It depends. For delete, you might want to return a 200 because the operation completed successfully. But if you do that, you might also want to return the number of records affected. Or you might want to return a 404, indicating the record was not found. Update is similar, although you might want to create the record or you might want to return an error code indicating failure.
Hi,
thanks for the great tutorial.
I have programmed an API and used a Sql Local database with Dapper as datastorage.
In the table I have a column as bit with default value 0.
If I execute a PUT via the API, the value is set to 1 as desired (VS DB = true, SSMS = 1) in the database, but unfortunately the GET query always returns the value false.
Debugging has shown that the value is already received as a return value in the DataAccess (db.Load...) as false.
I am using VS2019 and the project is running on .NetCore 6.
Do you have any idea what can cause the problem?
Greetings and Kind Regards
Not sure. Sounds like a good debugging challenge.
"Karen is now number 5" Imma be careful. But for real tho Mr. Corey this is high value lesson. Thanks man.
edit: Thank god. Karen is out
lol
I have been looking forward to this video!
Great!
Thanks for the great tutorial!
For some reason, when I retrieve data from the database, the FirstName and LastName fields have a length of 50 characters with trailing spaces. However, in the database, they are stored without spaces.
When you set up your files in the database, you chose nchar or char for your field type. They will always give you that many characters using trailing spaces. You want nvarchar or varchar to get a variable length result.
@@IAmTimCorey Yes, indeed, I just didn’t notice that when creating the table I specified nchar(50) for the fields instead of nvarchar(50). Thank you very much!
Excellent video as always. Thank you for sharing. Could you please provide some additional hint on how to get a different connection ID into our data access beside the Default one cleanly? Some time you may need to connect to different databases using the same DataAccess class.
The third parameter, which is optional, on LoadData and SaveData is the name of the connection string to find in appsettings. Just pass in a different connection string name and it will use that connection string instead.
Amazing video!, I would like to know what is the best way to unit test a minimal api.
Postman
Just wanted to point out that ConfigureApi method doesn't necessarily need to be an extension method. If you remove "this" from the argument list, then you just need to call it through the class name and supply 'app' as an argument: Api.ConfigureApi(app); instead of app.ConfigureApi();
True. All extension methods can be regular methods instead.
Hi Tim, loving the help you are providing, i have a question though. When i post a new user, the id goes from 4 to 1005... why is that and how do i go about changing it, to have be 5 automatically.
Don't worry about it and definitely don't try to change it. The basics are that SQL writes inserts to temporary storage before it inserts them into your table. If your SQL Server reboots unexpectedly (such as on a dev machine when you restart), it isn't sure if it has processed all of those entries when you start back up before you start asking for new ID numbers. So, it intentionally skips 1000 so that it can use those numbers for any that were still in the queue without causing any problems. The ID number is not something you should try to control. Let SQL do that. The only thing that matters is that it increments. Whether that next value is 1 away or 1000 doesn't matter to SQL.
Thank you Tim!
You are welcome.
Hi Tim, how would one structure a larger project with many endpoints. Having a HUGE Api.cs does not seem viable (if you are exposing lets say a 100 endpoints).
You would probably use a full API. Minimal APIs are more for minimal projects (microservices, small APIs, etc.)
@@IAmTimCorey okay, you mentioned that you can transition to controllers, would that be as simple as call UseControllers() in program.cs and start adding controllers the traditional way?
Yes.
@@IAmTimCorey Okay - sweet thanks a bunch for all the great content !
@IAmTimCorey how would you handle user authentication and authorisation with this setup? Should you embed those logic in the stored procedure itself?
You can use AuthN/AuthZ in a Minimal API almost exactly like you would in a full API. You don't need to put anything in the stored procedures.
@@IAmTimCorey in other words use .net core identity for authN & authZ?
So the UserData class is basically a Unit Of Work pattern?
Thanks Tim! Great 2 part series here. I'm still new to .Net Core and I'm starting out with Razor Pages. How would I adopt this DataAccess layer inside a Razor Page display? Also still learning C#.
It is the same data access layer. Just put it in dependency injection (in the Program.cs file) in your Razor Pages app and then ask for it whenever you need to access data.
Thanks Tim for great stuff. I really like your videos and presentation style. Can we do authorization and data annotation validations with minimal APIs?
Yep. I actually have a course coming out soon that covers how to do this.
Thank you for a very great video. See you on the next. :-)
You are welcome.
Thank you
Would be great to provide a video about domain driven design specially aggregates and event sourcing
I will add it to the list. Thanks for the suggestion.
I was just wondering Tim, I watched your last video and wanted to ask why separate the class library from the database api? Is it to make it cleaner or is there some sort of security reason? Or maybe reusability?
It can make it more reusable, but probably not in the way you think. I like to separate my business logic and data access from my UI layer (and yes, an API is a UI layer - it isn't a GUI, but it is a UI). That way, if I ever need to change out the UI layer, I have cleaner separation to do so. It also allows me to use the same library to connect to a desktop app directly. That way, the API can serve the data or the desktop app can access it directly (which improves performance). It also encourages best practices of separating your concerns. The UI should not know about the business logic and the business logic should not know about the database.