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.
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.
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!
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.
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.
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.
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.
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.
I haven't used that extension before in one of my videos. I'll have to change that. Since it does not use pre-written procedures, it will make it a bit tougher to debug slow queries in SQL, but that seems like a bit of a non-issue with simple queries.
This is an example of how I implemented generic GetAll and Insert methods using Contrib: public async Task GetAllAsync() where T : class { using IDbConnection connection = new SqlConnection(_connectionString); var rows = await connection.GetAllAsync(); return rows; } public async Task InsertAsync(T entity) where T : class { using IDbConnection connection = new SqlConnection(_connectionString); var entityId = await connection.InsertAsync(entity); return entityId; } In this case I would then use this in the UserData class like so: public async Task GetUsers() { var users = await _sqlDataAccess.GetAllAsync(); return users; } Note that the UserModel class need to have the table decorator from Contrib: [Table("User")] public class UserModel { //... }
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😌.
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...
If you are using an SP you can return an integer value or if you switch on NOCOUNT you can return any data such as SELECT SCOPE_IDENTITY(), or SELECT FIRSTNAME, LASTNAME FROM USERS WHERE ID = SCOPE_IDENTITY() to get back the ID. If you were writing your own SQL code you can combine multiple statements so "SET NOCOUNT ON; INSERT INTO USER (FIRSTNAME, LASTNAME) VALUES (@0, @1); SELECT SCOPE_IDENTITY()
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.
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! ♥
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
for the pre-script reasoning....the EXISTS keyword is used here rather than say IF (SELECT COUNT(*) FROM [USER]) > 0 because EXIST will stop retrieving rows as soon as it finds one...so IF NOT EXISTS (SELECT 1 FROM [USER]) will only ever return one row regardless how many rows there are. Its the exist keyword which is making all the speed difference not the 1 or * which if you used in an EXIST statement would be an unmeasurable difference. In normal selects * would be slower cos it has to enumerate the columns first. COUNT(*) I believe always produces an index or table scan so it could be much slower if the table was large even with an exist (it is only returning a single row anyways)
Thanks for sharing. I agree on the stored procedure vs ad-hoc caching. What I believe I was trying to communicate is that stored procs that are used are always cached (after the plan is created), whereas ad-hoc isn't always cached (if the signature changed) so you get a 100% vs a possible 100% cache reuse. Also, you are right about exists. 👍
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 an FYI. I had some strange versioning issues. 1. I had to go to the UserDB Properties and change my target platform (Sql Server 2019). 2. I also had to choose the Generate Script option in the Publish window, to get the system to recognize my unique setup. It failed loading the schema and data when I just tried to publish.
The first one does depend on where you publish it. If I publish it to LocalDB, I actually roll it back to SQL 2016. As for it not doing the publish correctly, that's interesting. I wonder what the configuration setting is that is preventing it.
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.
Yep, that would be ideal, and it is something I've been meaning to do for a while. I would like to abstract it to a NuGet package. Basically a Dapper helper.
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.
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.
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. :)
That takes developing a bit of logic, but that's normal. Think about how SQL stores data. It stores data in rows. How do you get a complex object back in one row? You can't. What you need to do is either create a query that puts all of the data in a row (which would be a model without sub-models) or you need to do more than one query. Let's talk through an example of more than one query. Let's say you have a set of people and those people have one or more addresses per person. OK, so the first query would be "get all the people you want". That gives you the "main" model's data but leaves the List empty in each PersonModel. Now, do another query that is selects all of the addresses for all of the people selected in the original query. Then in C#, use LINQ to put those AddressModels into the correct PersonModels. That seems like a lot of work, but that is extremely efficient compared to what EF is doing. Also, when you do that, you start to rethink if you need all of that data. Odds are you don't actually need it all. That makes your application even more efficient. For instance, maybe you don't look at each person's list of addresses but you do want to know how many addresses they have. Cool. You can return that number as a column in the first query. Then, if you do want to actually look at the addresses a person has, you can load just those addresses up when requested.
@@IAmTimCorey The main difference is that I did not isolated DataAccess into its own DLL. I've put all objects and calls through Dapper into the API's models. Which I guess is fine for me. Small DB, only for our entreprise using... The other big difference is the technical level but I cannot compete with God himself :)
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?
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!
In a Restful API, a Post doesn't return an object, it returns the URI for the newly created object. Often the caller just wanted to create somthing, why bother them with a (potentially big) new object in return that will take place on the network ? As for inserts in DBb, that is what SQL does, and what EF does as well, so I would find that logical. Particurlarly Updates actually, more than Inserts.
I can control their scope and distribute them as needed via dependency injection. That allows me to replace them with mocked versions for unit testing, and it allows me to decide if I want different versions used in different places or if I want to use the same instance (Transient vs Singleton).
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)
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.
24:44 "Notice there's a 'publish' file down here. Now if I double click on it..." I cannot see this. Where is this file? Where did you double click, please?
Is there a similar VS 'project' for MariaDB (MySql)? I couldn't find anything that allows the same management, source control etc. as this SQL Server project.
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.
Because they don't have to be. Await basically "unwraps" a task so that you can work with the output. Since we don't care about what's in the Task, we don't have to await it. These methods are just shortcuts with set parameters.
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.
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.
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 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. The moment you mentioned possibly DRY violation (51:45) I thought of making private property like Connection => new SqlConnection(…); and then reuse this inside our using statements like so: using IDbConnection connection = Connection; Would this properly dispose all connections or there will be penalties?
@@hardyyau this was my thought as well. Property like that will not have backing field and will behave more like a method. In our method, after leaving the using scope there will be no reference to the connection imo
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
Great tutorial, Tim! But what if my stored procedure has parameters? I am using this exactly same code of the video, but when i change to my procedure, it returns a HTTP 500 code, with the message "The procedure or function 'procedure_name' expects parameter '@parameter_name', which was not provided." Can you help me?
The stored procedures I'm using are using parameters too. For instance, at 1:08:33, on line 32 you will see the spUser_Insert stored procedure being called. I am passing in two parameters (FirstName and LastName). So the stored procedure definition will have two parameters that match those names. If I wanted to pass in another parameter but didn't have a variable named the same as the parameter, I would just add it like so: "{ user.FirstName, user.LastName, Age = 34 }" where @Age was declared in the stored procedure.
Hi Tim. If I want to connect to different data providers, for example SQL connection and oledbconnection, i create two repository sqlserverrepository and Oracle repository that inherit from generic repository interface. Should I change in every repository the IDBconnection for each different method or create a factory method that choose the idbconnection and create only once repository that get the IDBconnection from this factory?
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?
The database enforces uniqueness, so Single vs First does not matter. We don't need to check to be sure there aren't two entries with the same primary key. So the difference comes down to performance. FirstOrDefault is more performant than SingleOrDefault. So in this case, I chose FirstOrDefault.
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
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
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.
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.
You only need to await a call when you need to do something with the results. If the rest of the code doesn't rely on the results of your async call, you can skip the await. The caller will await the call, since it needs to do something with the results, but by not adding an await where you aren't waiting on the results, you lower the overhead of that call (every await adds overhead).
@@IAmTimCorey I'm not a developer but trying to broaden my knowledge. A lightbulb has just gone off and now have a new understanding. When I set a method to return Task but I am returning an IEnumerable I didn't stop to think I could also return the task! The API call I assume will then await and get the result from the Task. Makes far more sense now. I can see some improvements to make in my own code now. Thanks for the reply do appreciate the time taken to do that.
In the SaveData method in SqlDataAcess class, shouldn't you return a new object with the new Id of the saved object, or at least return the Id of it? I often need to use that Id in further processing of data. In the Stored procedure I use "select SCOPE_IDENTITY()" after inserting data. Is there a "better" way of getting the latest saved object? Sorry if this was unclear, English is not my native language...
It is not always necessary to return an ID. However, if you want to return an ID, here is how to do it: th-cam.com/video/b8Zev2qU5IE/w-d-xo.htmlsi=kIdT7lA6ndz9DpXp
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?
I disagree with the "no stored procedures" or "no business logic in the database" recommendations. We need security in depth. If the database layer is fully permissive then you are relying on applications to do all of your security. That's fine as long as those applications all have the same business logic. Also, don't forget that there is a security issue here - your connection to the database. If a user has access to it, they can bypass the business logic and insert data directly that might be harmful/malformed. Plus, if you use stored procedures, you can lock down your database much more than if you use ad hoc queries.
@@IAmTimCorey Tightly coupling the API to a specific DB is generally frowned upon to my understanding. In this example, wouldn't your business logic become dependent on a SQL Server backend? BL in SPs are also more difficult to unit test, can be more difficult to maintain when complexity is involved, etc.... Always exceptions of course and tradeoffs to be made.
@@IAmTimCorey Ok thank you for your advise. I understand the security point of view. I asked this question because we recently feel pain when we had to change our backend database from SQL Server to Oracle. The product that I am working on has a SQL server database. But recently a new client wanted Oracle as the backend instead of SQL server. Then we had to do a lot of work in converting SQL SPs into Oracle SPs. That was a real headache. But almost all the EF queries ran successfully without doing any changes with Oracle as the backend. This was my main concern.
Stored procedures are not faster or better than ad hoc, and some logic is always better in your app (unless you are an expert on creating set based queries then loops and the like should not be in your SPs) but store procedures most definitely keep your developers and app away from the data. If your app only uses stored procedures no one is ever going to be able to accidentally write a DELETE FROM USERS WHERE NAME=NAME type query, its just not possible if that user does not have access to the data but only to the SPs.
Great video. If I wanted to write integration tests for this database, what approach would you suggest for keeping the test DB schema synced with the prod version (or db project) and for resetting the DB to a known state between tests. With EF, this is fairly straightforward, but coming up with an approach to use without EF is a little tricky. Any ideas?
Is it possible to do this with business models that require a constructor? I like my models to require that their creation be done through a constructor so that they are instantiated properly and always in a valid state. Or does this technique require a paramterless constructor?
@@IAmTimCorey Thanks for reply. I will see this video soon. In one of your material i have heard that we need to be careful to choose singleton to connect with db. Should I think about another scope in production apps or singleton is perfectly fine?
at 54:18 the loadData did return something the other method SaveData doesnot return anything, why i can't add void if it return nothing, and why it didnot scream for an error without return statment ???!!!!!!!!!!!!
We are using asynchronous programming here. An async method should not return void unless it is an event. Otherwise, it should return Task. That Task object is what allows the caller to await the results. It tells the caller when the task is complete. When we use async and return Task, we just return the T value and the system wraps it in a Task. If we don't want to return anything (void), we return just a Task. That means we don't need a return statement, but the system will still send back the Task to allow for awaits.
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.
Well, since we built the stored procedure in the database project, it is all right there in our solution. As far as writing T-SQL in C#, I prefer not to do that because we are mixing our two systems. We are relying on C# to provide the SQL statement, which means SQL cannot optimize the statement until we pass it over. That's not ideal. The issue isn't as much scalability as it is one of being easy to maintain. If you are evaluating long-running or expensive queries, knowing where they are located is a real time-saver. If you think finding the stored procedure is inconvenient, try having a SQL statement that was dynamically generated that you have to figure out where it came from in your source code.
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.
I always do it this way because an IEnumerable can return 0, 1, or many records. I don't usually have a need to create a separate method with a different name (since you cannot overload a method by only changing the return type) just to limit the results to one record.
Firstly, thanks for the fantastic video as always. I have a question. In the one-liner method, "public TaskGetUsers() => ", I see that even the method has "Task" however it did not give compile time error to force you to write "async" before the Task keyword and same for "await" keyword too. Why is that so ? Though in the other method "GetUser" you wrote async keyword. So a bit lost.
I have never before accessed a SQL Server database "operationally" through Visual Studio - I have SQL Server Management Studio (SSMS), which I use to work on databases. I cannot find the database server MSSQLLocalDB through my local instance of SSMS. Should I be able to?
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.
We use just the SQL data connection (the one that ADO.NET uses as well) to connect to SQL. We just use Dapper to execute the SQL commands and translate the returned data into C# objects.
This example of post deployment script with connection string and stored procedures are set to work with SQL Server. How would this work with Sqlite database? I guess replacing SQL server connection string with path to Sqlite database is the way to do it?
@@IAmTimCorey You mentioned that this design/structure can be used with Sqlite. How can we create tables when sqlite database is created? And when it is needed, insert some sample data? Thanks
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?
Sir, is the IConfiguration on class SqlDataAccess (DataAccess Library) referring to appsetting.json on MinimalApi project? What happen if i have multiple project in a solution with appsetting.json on it?
The UI layer always provides the dependencies. The UI is the only project type that has appsettings.json (or should be). That means your library will work perfectly with the different UIs you have, using the correct appsettings.json for each.
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.
I am glad my content has been helpful.
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.
I’m glad my content has been helpful.
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!
I appreciate the kind words.
Right now, I understand dapper, it's really cool to use to any database, SQL server + stored procedure is magic. Thanks Tim.
You are welcome.
OH MY GOD , thank u so much , this is very handy , i think every beginner dev should watch this video , thanks bro
You are welcome.
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.
I am glad it was so helpful.
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.
You are welcome.
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.
You are welcome.
Hi Tim, can't find the words to describe my thoughts now. Your contents are awesome, that's it, thank you very much!
You are welcome.
FINALLY. My most waiting Topic this month. Thanks Tim
You are welcome.
Hello Corey your videos ready amazing.. since I've been watching these videos I have broadly understanding in programming
Glad you like them!
Thanks!
Thank you!
Wow! Powerful way to setup data access. I am sold.
Great!
Outstanding work here. This has help me design a clearer roadmap to migrate framework project to core replace linqtosql Orm. Thanks again!
Thank you!
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.
This is my 10th hour of watching your explanation. Mind-blowing ❣
I’m glad they have been helpful.
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.
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.
I haven't used that extension before in one of my videos. I'll have to change that. Since it does not use pre-written procedures, it will make it a bit tougher to debug slow queries in SQL, but that seems like a bit of a non-issue with simple queries.
@@IAmTimCorey I would like to see this, count this as a +1
This is an example of how I implemented generic GetAll and Insert methods using Contrib:
public async Task GetAllAsync() where T : class
{
using IDbConnection connection = new SqlConnection(_connectionString);
var rows = await connection.GetAllAsync();
return rows;
}
public async Task InsertAsync(T entity) where T : class
{
using IDbConnection connection = new SqlConnection(_connectionString);
var entityId = await connection.InsertAsync(entity);
return entityId;
}
In this case I would then use this in the UserData class like so:
public async Task GetUsers()
{
var users = await _sqlDataAccess.GetAllAsync();
return users;
}
Note that the UserModel class need to have the table decorator from Contrib:
[Table("User")]
public class UserModel
{
//...
}
Wow... this tool is soooo awesome!!! How did I survive for so long without it!
I am glad you found it.
I love working with SQL and this Video was Awsome, thank you.
You are welcome.
Thanks Tim, looking forward to part two!
Coming soon!
This is amazing so far. Thank you Tim. Glad I found your videos.
You are welcome.
Just wanted to say thank you for helping me learn about web development in general. I will soon contribute to this awesome cause!
You are welcome.
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😌.
Great!
Great Tutorial, Thanks. Looking to speed up my web that utilize EF6, Wow, all in an hour. Tim you are awesome!
You are welcome.
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...
If you are using an SP you can return an integer value or if you switch on NOCOUNT you can return any data such as SELECT SCOPE_IDENTITY(), or SELECT FIRSTNAME, LASTNAME FROM USERS WHERE ID = SCOPE_IDENTITY() to get back the ID. If you were writing your own SQL code you can combine multiple statements so "SET NOCOUNT ON; INSERT INTO USER (FIRSTNAME, LASTNAME) VALUES (@0, @1); SELECT SCOPE_IDENTITY()
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.
Great!
Awesome video, this has really helped me with my new project. Thanks so much Tim.
You are welcome.
An absolutely great tutorial, thank you, Tim!
You are welcome.
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! ♥
Excellent!
Brilliant as always Tim.
Thank you!
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
for the pre-script reasoning....the EXISTS keyword is used here rather than say IF (SELECT COUNT(*) FROM [USER]) > 0 because EXIST will stop retrieving rows as soon as it finds one...so IF NOT EXISTS (SELECT 1 FROM [USER]) will only ever return one row regardless how many rows there are. Its the exist keyword which is making all the speed difference not the 1 or * which if you used in an EXIST statement would be an unmeasurable difference. In normal selects * would be slower cos it has to enumerate the columns first. COUNT(*) I believe always produces an index or table scan so it could be much slower if the table was large even with an exist (it is only returning a single row anyways)
Thanks for sharing. I agree on the stored procedure vs ad-hoc caching. What I believe I was trying to communicate is that stored procs that are used are always cached (after the plan is created), whereas ad-hoc isn't always cached (if the signature changed) so you get a 100% vs a possible 100% cache reuse.
Also, you are right about exists. 👍
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
Awesome!
Just an FYI. I had some strange versioning issues.
1. I had to go to the UserDB Properties and change my target platform (Sql Server 2019).
2. I also had to choose the Generate Script option in the Publish window, to get the system to recognize my unique setup. It failed loading the schema and data when I just tried to publish.
The first one does depend on where you publish it. If I publish it to LocalDB, I actually roll it back to SQL 2016. As for it not doing the publish correctly, that's interesting. I wonder what the configuration setting is that is preventing it.
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.
Yep, that would be ideal, and it is something I've been meaning to do for a while. I would like to abstract it to a NuGet package. Basically a Dapper helper.
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.
Beautifully simple solution. Thanks Tim!
You are welcome.
Thanks a lot sensei Tim Corey, regards from some place in Perú.
You are welcome!
Excellent "starting from scratch" video.
Thank you!
Its like you are reading my mind on what I want/need
Awesome!
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.
I really need part 2. Waiting for it. Good job.
Great!
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. :)
That takes developing a bit of logic, but that's normal. Think about how SQL stores data. It stores data in rows. How do you get a complex object back in one row? You can't. What you need to do is either create a query that puts all of the data in a row (which would be a model without sub-models) or you need to do more than one query.
Let's talk through an example of more than one query. Let's say you have a set of people and those people have one or more addresses per person. OK, so the first query would be "get all the people you want". That gives you the "main" model's data but leaves the List empty in each PersonModel. Now, do another query that is selects all of the addresses for all of the people selected in the original query. Then in C#, use LINQ to put those AddressModels into the correct PersonModels.
That seems like a lot of work, but that is extremely efficient compared to what EF is doing. Also, when you do that, you start to rethink if you need all of that data. Odds are you don't actually need it all. That makes your application even more efficient. For instance, maybe you don't look at each person's list of addresses but you do want to know how many addresses they have. Cool. You can return that number as a column in the first query. Then, if you do want to actually look at the addresses a person has, you can load just those addresses up when requested.
This is awesome! Great video! I never take enough advantage of generics to be honest.
Thank you!
Very nice presentation. Thanks for sharing your Knowledge. Keep going........
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!
Sounds great!
@@IAmTimCorey The main difference is that I did not isolated DataAccess into its own DLL. I've put all objects and calls through Dapper into the API's models. Which I guess is fine for me. Small DB, only for our entreprise using...
The other big difference is the technical level but I cannot compete with God himself :)
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?
Awesome content for beginner to be a LEO ;). Thank you.
You are welcome.
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!
In a Restful API, a Post doesn't return an object, it returns the URI for the newly created object.
Often the caller just wanted to create somthing, why bother them with a (potentially big) new object in return that will take place on the network ?
As for inserts in DBb, that is what SQL does, and what EF does as well, so I would find that logical. Particurlarly Updates actually, more than Inserts.
Hi Tim, what are the benefits of creating the SqlDataAccess and UserData classes as non-static classes as opposed to static classes?
I can control their scope and distribute them as needed via dependency injection. That allows me to replace them with mocked versions for unit testing, and it allows me to decide if I want different versions used in different places or if I want to use the same instance (Transient vs Singleton).
@@IAmTimCorey Thank you, that makes sense.
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)
Thank you very much for this awesome video 😇
You are welcome.
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.
Thanks Tim for this! Exactly what i need at the moment
Glad it was helpful!
24:44 "Notice there's a 'publish' file down here. Now if I double click on it..."
I cannot see this. Where is this file? Where did you double click, please?
The highlighted file in the Solution Explorer. It ends with ".publish"
@@IAmTimCorey Thank you!
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...
You are welcome.
Is there a similar VS 'project' for MariaDB (MySql)? I couldn't find anything that allows the same management, source control etc. as this SQL Server project.
No, there isn't.
@@IAmTimCorey thanks Tim for the prompt reply. That will save me hours of searching, wondering what I've missed! 😀
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.
Hi,Tim.
Why InsertUser,UpdateUser and DeleteUser are not async methods is you use Task as return type ?
Because they don't have to be.
Await basically "unwraps" a task so that you can work with the output. Since we don't care about what's in the Task, we don't have to await it. These methods are just shortcuts with set parameters.
@Janne is correct. Thanks for helping out!
I had the exact same question. Thanks!
@@janne_kekalainen many thanks !
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.
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.
You always have timely videos.
I'm glad.
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 ?
Thanks Tim,
How can I return a list of users and use multi mapping with this way
for example return data depend on tow tables
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.
That isn't in the plan for the next video (which is already recorded). However, I'll keep that in mind for the future.
Hey Tim. The moment you mentioned possibly DRY violation (51:45) I thought of making private property like Connection => new SqlConnection(…); and then reuse this inside our using statements like so: using IDbConnection connection = Connection; Would this properly dispose all connections or there will be penalties?
That would keep the connection open for the lifetime of our app. We don't want to do that. We want that connection to be short-lived.
Doesn't the using IDbConnection = Connection will dispose the connection?
Is it because it is a property and what if we convert it to a method?
@@hardyyau this was my thought as well. Property like that will not have backing field and will behave more like a method. In our method, after leaving the using scope there will be no reference to the connection imo
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
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/
Great tutorial, Tim! But what if my stored procedure has parameters? I am using this exactly same code of the video, but when i change to my procedure, it returns a HTTP 500 code, with the message "The procedure or function 'procedure_name' expects parameter '@parameter_name', which was not provided."
Can you help me?
The stored procedures I'm using are using parameters too. For instance, at 1:08:33, on line 32 you will see the spUser_Insert stored procedure being called. I am passing in two parameters (FirstName and LastName). So the stored procedure definition will have two parameters that match those names. If I wanted to pass in another parameter but didn't have a variable named the same as the parameter, I would just add it like so: "{ user.FirstName, user.LastName, Age = 34 }" where @Age was declared in the stored procedure.
Hi Tim. If I want to connect to different data providers, for example SQL connection and oledbconnection, i create two repository sqlserverrepository and Oracle repository that inherit from generic repository interface.
Should I change in every repository the IDBconnection for each different method or create a factory method that choose the idbconnection and create only once repository that get the IDBconnection from this factory?
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?
The database enforces uniqueness, so Single vs First does not matter. We don't need to check to be sure there aren't two entries with the same primary key. So the difference comes down to performance. FirstOrDefault is more performant than SingleOrDefault. So in this case, I chose FirstOrDefault.
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
Thanks for sharing.
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
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
You won't have to wait long.
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.
You only need to await a call when you need to do something with the results. If the rest of the code doesn't rely on the results of your async call, you can skip the await. The caller will await the call, since it needs to do something with the results, but by not adding an await where you aren't waiting on the results, you lower the overhead of that call (every await adds overhead).
@@IAmTimCorey I'm not a developer but trying to broaden my knowledge. A lightbulb has just gone off and now have a new understanding. When I set a method to return Task but I am returning an IEnumerable I didn't stop to think I could also return the task! The API call I assume will then await and get the result from the Task. Makes far more sense now. I can see some improvements to make in my own code now. Thanks for the reply do appreciate the time taken to do that.
In the SaveData method in SqlDataAcess class, shouldn't you return a new object with the new Id of the saved object, or at least return the Id of it? I often need to use that Id in further processing of data. In the Stored procedure I use "select SCOPE_IDENTITY()" after inserting data.
Is there a "better" way of getting the latest saved object?
Sorry if this was unclear, English is not my native language...
It is not always necessary to return an ID. However, if you want to return an ID, here is how to do it: th-cam.com/video/b8Zev2qU5IE/w-d-xo.htmlsi=kIdT7lA6ndz9DpXp
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?
I disagree with the "no stored procedures" or "no business logic in the database" recommendations. We need security in depth. If the database layer is fully permissive then you are relying on applications to do all of your security. That's fine as long as those applications all have the same business logic. Also, don't forget that there is a security issue here - your connection to the database. If a user has access to it, they can bypass the business logic and insert data directly that might be harmful/malformed. Plus, if you use stored procedures, you can lock down your database much more than if you use ad hoc queries.
@@IAmTimCorey Tightly coupling the API to a specific DB is generally frowned upon to my understanding. In this example, wouldn't your business logic become dependent on a SQL Server backend? BL in SPs are also more difficult to unit test, can be more difficult to maintain when complexity is involved, etc.... Always exceptions of course and tradeoffs to be made.
No because you don’t rely on the logic in the stored procedures. Also, the SP logic is limited.
@@IAmTimCorey Ok thank you for your advise. I understand the security point of view. I asked this question because we recently feel pain when we had to change our backend database from SQL Server to Oracle. The product that I am working on has a SQL server database. But recently a new client wanted Oracle as the backend instead of SQL server. Then we had to do a lot of work in converting SQL SPs into Oracle SPs. That was a real headache. But almost all the EF queries ran successfully without doing any changes with Oracle as the backend. This was my main concern.
Stored procedures are not faster or better than ad hoc, and some logic is always better in your app (unless you are an expert on creating set based queries then loops and the like should not be in your SPs) but store procedures most definitely keep your developers and app away from the data. If your app only uses stored procedures no one is ever going to be able to accidentally write a DELETE FROM USERS WHERE NAME=NAME type query, its just not possible if that user does not have access to the data but only to the SPs.
Great video. If I wanted to write integration tests for this database, what approach would you suggest for keeping the test DB schema synced with the prod version (or db project) and for resetting the DB to a known state between tests. With EF, this is fairly straightforward, but coming up with an approach to use without EF is a little tricky. Any ideas?
Is it possible to do this with business models that require a constructor? I like my models to require that their creation be done through a constructor so that they are instantiated properly and always in a valid state. Or does this technique require a paramterless constructor?
What lifetime scope do you choose to configure UserData in program.cs? Singleton, Scoped, Transient?
A Singleton. We do that here: th-cam.com/video/5tYSO5mAjXs/w-d-xo.html
@@IAmTimCorey Thanks for reply. I will see this video soon. In one of your material i have heard that we need to be careful to choose singleton to connect with db. Should I think about another scope in production apps or singleton is perfectly fine?
at 54:18 the loadData did return something the other method SaveData doesnot return anything, why i can't add void if it return nothing, and why it didnot scream for an error without return statment ???!!!!!!!!!!!!
We are using asynchronous programming here. An async method should not return void unless it is an event. Otherwise, it should return Task. That Task object is what allows the caller to await the results. It tells the caller when the task is complete. When we use async and return Task, we just return the T value and the system wraps it in a Task. If we don't want to return anything (void), we return just a Task. That means we don't need a return statement, but the system will still send back the Task to allow for awaits.
01:05:10 Can you please tell me when should we use "await" Key word in many lines of Method Body
You use the await keyword when you need to wait on the async method to complete before continuing in your code.
Thank you for this Tim 🚀🚀 really helpful
You are welcome.
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.
Well, since we built the stored procedure in the database project, it is all right there in our solution. As far as writing T-SQL in C#, I prefer not to do that because we are mixing our two systems. We are relying on C# to provide the SQL statement, which means SQL cannot optimize the statement until we pass it over. That's not ideal. The issue isn't as much scalability as it is one of being easy to maintain. If you are evaluating long-running or expensive queries, knowing where they are located is a real time-saver. If you think finding the stored procedure is inconvenient, try having a SQL statement that was dynamically generated that you have to figure out where it came from in your source code.
Amazing as always!
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.
I always do it this way because an IEnumerable can return 0, 1, or many records. I don't usually have a need to create a separate method with a different name (since you cannot overload a method by only changing the return type) just to limit the results to one record.
Firstly, thanks for the fantastic video as always. I have a question. In the one-liner method, "public TaskGetUsers() => ", I see that even the method has "Task" however it did not give compile time error to force you to write "async" before the Task keyword and same for "await" keyword too. Why is that so ? Though in the other method "GetUser" you wrote async keyword. So a bit lost.
If you are not awaiting anything, you don't mark the method as async even if you are returning a Task.
@@IAmTimCorey Thank you so much for the reply.
Tim i didnt understand how to enter editorconfig settings i watched the video but i couldnt find to enter settings of the editorconfig file
Real thanks to Tim and community for helping out in comments!
You are welcome.
I have never before accessed a SQL Server database "operationally" through Visual Studio - I have SQL Server Management Studio (SSMS), which I use to work on databases. I cannot find the database server MSSQLLocalDB through my local instance of SSMS. Should I be able to?
Yes, it will have the same path as what you see in Visual Studio. Usually something like (local)\MSSQLLocalDB
01:01:45 If you put async in Method signature can you please tell me what is the Method Implementation
If you mark a method as async, you have to await something inside of the method.
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.
You are welcome.
Why not add "Async" to the name of the async methods? (LoadData & SaveData)
Did you use Dapper here both to connect to the database and to execute stored procedures, agropos APIs?
We use just the SQL data connection (the one that ADO.NET uses as well) to connect to SQL. We just use Dapper to execute the SQL commands and translate the returned data into C# objects.
This example of post deployment script with connection string and stored procedures are set to work with SQL Server. How would this work with Sqlite database? I guess replacing SQL server connection string with path to Sqlite database is the way to do it?
SSDT doesn't work with SQLite.
@@IAmTimCorey You mentioned that this design/structure can be used with Sqlite. How can we create tables when sqlite database is created? And when it is needed, insert some sample data? Thanks
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?
public async Task ProcessarPedidoCreditoTeste(PedidoModel pedido)
{
int Msg = 0;//OUTPUT PARAMETER
await _db.SaveData("sp_IbCPanel_PedidoCredito", new { pedido.iduser, pedido.idempresa, pedido.idavalista, pedido.idgestor, pedido.idpedido, Msg });
return Msg;
}
Thanks' Tim for another awesome video.
You are welcome.
Is the UserData basically the same thing as a Service?
Looks like that. It reminds me of repository pattern here.
Sir, is the IConfiguration on class SqlDataAccess (DataAccess Library) referring to appsetting.json on MinimalApi project? What happen if i have multiple project in a solution with appsetting.json on it?
The UI layer always provides the dependencies. The UI is the only project type that has appsettings.json (or should be). That means your library will work perfectly with the different UIs you have, using the correct appsettings.json for each.
@@IAmTimCorey noted sir.. thank you
Excellent video
Thanks!