I liked here how used comments to show in the beginning what we're going to do. This way i could already do it myself before i watched you do it. Best way to practice.
Wow, was that a trip... I learned a lot, both from the material and from messing it up and having to recover. Took me maybe a week, and ran over it a few times, but among other things I learned more about reverting to previous versions in Git and paying attention to the UI in VS to keep better track of exactly which class you're working on ("Is he in the API models? Or the UI models?" I now know what to look for to actually answer that!) As always, fantastic material and I really appreciate that you've offered this for free. Just being able to follow along and wind up with something functional feels like an achievement.
I've learnt so much from you Tim, with I think the most important two phrases being (in your words) "Minimum Viable Product" and to "keep moving". I find myself being so bogged down with trying to do something the "correct way", that I end up literally getting nowhere. Build it now - refactor later - don't let perfection become the enemy of progress. For me - this whole series is worth watching for those phrases alone - Thank you so much.
Thanks again Tim! Definitely a remarkable work leading to one understand the meaning of Debugging & Refactoring and the worth of each of the said activities! Sure did cost me around some time to finish up successfully with the feel of achievement!
This video gave me so much motivation and excitement about personal progress and moving forwards. Thank you very much! Really appreciate it! All the best wishes to you!
When you talk about models across the ui and api at ~35:00 how does that fit with the idea of data transfer objects (dto)? Would/ could you have a 6th lib for those?
The models in my data access layer are really DTOs. I just don't call them that. They transport data between the database and the UI and they don't contain business logic. That's basically the definition of a DTO. Good question.
Since this is transactional data - my preference is to perform the entire sale transaction at the stored procedure. By iteratively performing individual inserts from C#, you're setting yourself up for future pain if there's a failure anywhere in the C# loops. You'll be left with a partial transaction in the DB - a nightmare scenario.
The problem is that we have a set of data to transfer over to SQL. The only alternative is to create a TVP in SQL and then use that in a stored procedure to transfer all of the data over. You are right about a failure in C# being a problem, which is why in an upcoming video, I replace this section with a C#-side SQL transaction with rollback in case of issue (including exceptions).
I'm surprised nobody else brought up the fact that there needs to be one transaction for saving both sale and saledetails. You might also want to change @@IDENTITY to SCOPE_IDENTITY(), though. The problem with getting Id the way you are doing is that it does not guarantee the right value, and so this would never fly in the real world. I think with Dapper you need to treat it like a query, where you do the insert first and then the select right after, like connection.Query('sproc', parameter, etc)
Good catch. I'm going to have to rework that a bit. Because of the work I'm doing to prepare the details, I'm not sure if I'll create one stored procedure for both or if I'll start a transaction in code. Not a huge fan of transactions from code but it might be a viable option. Thanks for pointing out the issues.
Tim, I found an easy way to return the SaleID: 1. At the end of the sp, use "select scope_identity() as Id" to return the value. 2. When calling the sp, use {var result = sql.SaveData("dbo.spSale_Insert", sale, "TRMData")} then you will get the ID returned properly.
@@SuperDre74 it works, you just need to dig further and check how to modify your SqlDataAccess.SaveData method,. The details can be seen for example in this StackOverflow answer: stackoverflow.com/questions/8270205/how-do-i-perform-an-insert-and-return-inserted-identity-with-dapper/47110425
@@SuperDre74 In my case I've ended up with following changes: public T1 SaveData(string storedProcedure, T2 parameters, string connectionStringName) { string connectionString = GetConnectionString(connectionStringName); using (IDbConnection cnn = new SqlConnection(connectionString)) { return cnn.Query(storedProcedure, parameters, commandType: CommandType.StoredProcedure).FirstOrDefault(); } }
@@SuperDre74 also please do not forget to publish your migration (changes) to procedure when you do any. And be ready that the identity column for Sale and SaleDetail can jump up by 1000, it happens after SQL server restart, as described in Tim's answers in one of the comments.
Nah. Software is an iterative processes. You can still write your code with best practices, but you don't need to do it in the one pass. Making perfect code in one pass only works in pre-planned demos.
According to Uncle Bob: "Clean, extendible code that doesn't work is great, because I can make it work." "Ugly code that works will be useless tomorrow when the requirements change."
He is talking about the end product, not while you are building it. If you build something quick and ugly and then leave it as is and move on, yes, that's a problem. If you get something working, and then refactor it to make it "right", that is smart development.
Hi Tim, Thanks as always for the great content, Would you consider changing the mechanism by which an item is added to the cart? In a real world scenario, a retail has hundreds of items, finding an item in a list to add it to the cart will be cumbersome, It might be nice to provide a search option for the items, or even better: entry via barcode.
I'm hoping to add that in. For the initial version, I wanted something quick and easy. We can add in other entry mechanisms as part of our refactoring or as other options (various modes).
Apologies if this is considered necro posting on YT. I did some searching after getting to the point where the insert into dbo.Sale failed to set/return the Id. The Id can be returned through Dapper with a few changes: 1.Change the insert query in spSale_Insert to 'insert into ... output inserted.Id values ...;' 2.Remove 'select @Id = @@Identity;' from spSale_Insert 3a. Change SqlDataAccess.SaveData to return an int 3b. Change connection.Execute into 'return connection.Query(...).SingleOrDefault()' SaveData appears to work just fine even if called with a stored procedure with no output value. Thank you for the courses, I've really been enjoying them.
Thanks for the suggestion. The issue here, though, is that the number returned from an insert/update is typically the number of records affected. If you change what that value is, you may confuse other developers.
on getting inserted ID i suggest to create another method on DataAccess with ExcecuteScalar, and procedure INSERT INTO Sale(CashierId,SaleDate,SubTotal,Tax,Total) OUTPUT INSERTED.Id VALUES (@CashierId,@SaleDate,@SubTotal,@Tax,@Total);
Hello Tim. Thanks a lot for these very good video. I'm writing the code as the same time I listen to the video. But I'm blocking at the minute 46:40 because in my SaleController (where you put your break point at the console writeline) the sale parameter is empty on my side. And I cannot figure out why. Do you have an idea? Thank again for all your job
When sending data into an API, it can be a bit tricky because it expects the object to be shaped a certain way. Make sure that your names exactly match the properties and make sure the data you are sending into those types are correct.
4 ปีที่แล้ว
@@IAmTimCorey You're right, that was the problem. Thanks for your help.... and these very good tutorials. Now I 've to find out what's I did wrong in the module Upgrading to .NET Core: Adding JWT Authentication to Our API
I had this same issue (on the paid course) and I'm glad this was answered. In my situation, I accidentally had one called SalesDetails instead of SaleDetail. So, good catch. @IAmTimCorey, I know you don't want to use the same model, but is there where using an interface would work better? That way, if you utilized ISaleModel, then you know the fields will match up?
Many thanks for the lesson Tim, took me a few sittings to get through it all but managed it. Keep up the great work. One thing I was sad about was the separate SP to get the ID back, you should have paused the recording and then shown it all as one save and return new ID as that would be what is needed in the real world. No need for the spSale_Lookup.
I debated that but at the end of the day, this is exactly what I would have done to get the product out the door. Then I would refactor it to be better. That is what I will be doing. I don't want to represent the perfect path when that isn't the reality in development.
at 29:20 you are using a foreach loop to send the data on button click right? I am making a form which uses 2 listboxes and a few textboxes to then Submit it. how would i go about doing that i am assuming foreach won't do it because i am not putting stuff into a different ListBox as you are doing with Cart also i am loving these series and i am just trying to create my own thing based upon this course! EDIT; i think i've managed to make it work by just adding the stuff without the foreach
I wanna "switch" from Qt c++/qml to Wpf and Xamarin because of your Videos. MVVP/WPF looks now at the beginning a little bit confusing to me, it seems to be there are some black magic behinde the scenes. But the power and productivity behinde it seems to be really amazing. I am very fascinated. Thanks for your Videos dude, these are very great! Keep it up.
Hello Tim I have been trying to figure out how at the 46:50 mark the SaleController is getting the values of type API's SaleModel rather than type UI's SaleModel. I have made a chart on OneNote mapping everything out and I've gone over the source code file, but it's not clicking. Later in the video you pass the API SaleModel parameter in SaleController to the SaveSale method which has a foreach loop. I don't understand how the API SaleModel parameter in the SaleController has any products in its SaleDetails List. If you would be so kind as to explain when that List is assigned those products I would truly appreciate it.
When you transfer data to an API or get data from an API, you don't pass C# structures. What happens is that it converts the C# structure to JSON for the transfer. On the other end, C# takes the JSON and converts it to the requested model. So on the WPF end, we are passing a WPF model to the API. It gets converted to JSON, then on the API side it gets converted to the model we specified (the API's SalesModel). You don't even have to have the same "shape" model. You could send FirstName and LastName but the model that receives could receive just FirstName. The LastName value would be dropped silently.
@@IAmTimCorey Got it. Ok so upon calling the PostAsJsonAsync we call the controller and that's when the conversion from c# to json happens. I did notice that both model classes need to have the same name and that the detailModel property names need to match as well in order for the conversion to work from c# to json. Thank you
Hello Tim, just a comment. While watching this video I remembered you usually recommend appending "async" to the end of async methods' name. Shouldn't the GetAll() method be named GetAllAsync()? (ProductEndpoint class). Maybe it is something you have already modified, I see the video has almost one year, but I'm watching it now for the first time. Thanks.
Hi Tim, I don't know if someone asked about this earlier. The SaleDetail table has the 'PurchasePrice' column. Every time I come across it, it gets me thinking this is the price the seller paid for the item. I don't know if I'm clear about it. Down the road in a fully working application you might want to introduce some report that will extract the total margin, total value of sales etc. That being the case you'll find yourself creating a way to store the purchases which will probably have the same PurchasePrice column as the price the seller paid. If you'll want to calculate the margin you'll find yourself extracting one PurchasePrice (you paid) from another PurchasePrice (you charged from your customer). It might be confusing. To me, the application should always reflect the point of view of the owner: the owner is purchasing (hence PurchasePrice), the owner is selling (hence RetailPrice). Already in the UI library the ProductModel reflects the price as RetailPrice (that I prefer). As always, great content. Keep up the good work.
There are three different prices to keep track of: Inventory PurchasePrice, Product RetailPrice, and SaleDetail PurchasePrice. Each is different. The one in the inventory indicates how much the item cost the owner to purchase. The one in the product indicates how much the item is listed for in the store. The one in the sale detail indicates how much the person actually paid for it (if they used a coupon, it would be smaller than retail, etc.) From these three values, we can create any type of report we want.
@@IAmTimCorey Yes, I agree with you, they are different, that's why I'm used to naming them different to avoid any confusion down the line, for me or anyone coming after me to review the code. Thanks for the reply :)
Not only that, but I keep thinking 'PurchasePrice' in SaleDetail is the actual price per item, not the total, and IMHO it would be better that way and replace Tax with TaxRate.
At about point 1:22:00 in this video, you mentioned that it would be probably wise to not make many database calls. I agree and would like to add that using EF, you could have replaced some 10 lines of code with just 2 lines. You should probably get over your EF bias. A good place to start is probably to read "Entity Framework Core In Action" by Jon P. Smith.
The problem with EF (one of them) is that it is very easy to pull more data than you think you are. If you put a method call in the where, EF is actually pulling every record and then running the where (which won't be an issue until you go to production and try that against a table with a million records). Its gotchas like that that give me pause. I will be teaching EF Core 3 (where they helped "fix" problems like the above with messaging) but "simple" things in EF are just hiding dangers below the surface. Here are some of my thoughts: www.iamtimcorey.com/blog/137806/entity-framework
@@IAmTimCorey I read through the link you posted and I see you have some good points. But, in my opinion, the very reason why most people would use EF at all, and as you stated the reason why you wouldn't, is precisely because it generates code. Imagine having to write boring manual code for 30-100 or more entities. The fact that the bulk of the code is generated for you is precisely why you should be interested in such an option. The code generated is C#, so I don't understand why you wouldn't want to support it. Do you want to tell me the generated code is above your level of C# so that you wouldn't want to support it? Anyway, I am looking forward to your EF Core 3 course. I believe that one would definitely be interesting, giving the way you approach C# development.
The issue I have is people saying that generated code is easier. It IS easier to create, but it is harder to maintain and generally less performant unless you really know your stuff (which gets back to the point of you not needing to generate your code). A good rule of thumb is that 10% of EF queries are slow and should be tweaked manually or replaced with stored procedures. Are people benchmarking their app and doing the work to identify the slow queries and replace them? Nope. So they are shipping slow data access layers because they were "easier" and "faster to create". Back in the day, we had FrontPage for web development. You could drag and drop to create a web page but it was messy on the back-end, slower than it should be, and more complicated to work with the HTML. EF is in that same pattern. Here is a good example of a simple EF vs. Dapper performance test: exceptionnotfound.net/dapper-vs-entity-framework-core-query-performance-benchmarking-2019/ Now this isn't scientific, which I actually like because this is what a newer developer would do - write code and assume since it worked that it was fine. Notice the performance differences. EF is about 10x slower than Dapper at doing the same thing. Again, not scientific and your mileage may vary. The point is that just writing EF code can very easily result in SLOW production code. At that point, you are committed to EF so you have to learn the depth of EF to figure out how to improve the performance. Next thing you know, you've spent more time fixing EF than you would have had you just wrote the queries you needed to in order to use Dapper. The EF course will be an interesting one. We will spend a lot of time learning how to diagnose performance issues and how to fix them.
Hi Tim, I am new to C# and just wondering in the Data Access layer, for example ProductData.GetProducts, (1:04:00) shouldn't it be an asynchronous operation async Task GetProducts() ? what is the difference or why it is not necessary to do async? Thanks!
We will convert it to async further on in the series. It doesn't have to be because the API is implicitly asynchronous whether your calls are or not, but we still should make the call async.
Nice video watched the the whole thing. re new identity field, here is how I do it using Dapper (which I have a base class for). 1. Add this line to the end of the dynamic dapper parms base.Parms.Add("@Id", dbType: DbType.Int32, direction: ParameterDirection.Output); 2. in the stored procedure I used SET @Id = SCOPE_IDENTITY() // probably the same as select @Id=@@Identity; like you have 3. back to the c# code... var affectedrows = await connection.ExecuteAsync(sql: "spSale_Insert", param: base.Parms, commandType: System.Data.CommandType.StoredProcedure); int newID = base.Parms.Get("NewId"); return newID; Hopefully this make sense. The key point is to include the extra Dapper parameter for the newly created @@Identity and then retrieve it after executing the insert stored procedure thereby eliminating the extra spSale_Loopkup stored procedure. thanks
Thanks for the suggestion. I was trying to avoid that way, since it means adding a new Save method overload that uses DynamicParameters (currently I pass in type T as the parameters). I am passing in the Id field using my method but it isn't returning it to the model. I'm going to have to look into if I can get away with doing it the same way I'm doing it or if I have to write a new method to convert my model to parameters and back again after the data comes back.
Hey Tim, This topic intrigued me, so I tried something in a small project I'm working on. There's no need to add a new Save method, you just have to refactor it a bit so that it takes a dynamic object as extra parameter. You need to pass the DataModel as this extra parameter. Then, create the DynamicParameters in the Save method and add @Id to it as output. After executing the stored procedure, just use the DynamicParameters.Get to set the dynamic object's id. Something like this : internal static void CreateData(string storedProcedure, T parameters, dynamic obj) { DynamicParameters parameter = new DynamicParameters(); parameter.Add("@Id", DbType.Int32, direction: ParameterDirection.Output); using (IDbConnection connection = new SqlConnection(GetConnection())) { connection.Execute(storedProcedure, parameters, commandType: CommandType.StoredProcedure); obj.Id = parameter.Get("@Id"); } } You probably would like to replace the creating of parameter to a separate method. Kind regards, Serge
Great course, Tim! Learning a lot. Just one question, how would you map nested SQL relationships to the c# models? For example, if you wanted to fetch all cashier records and for each of them, all their sales, and for each sale, the sale detail as well. How would I go about creating a model that supports this kind of query? I'd really appreciate the help, thanks!
I demo some options here: th-cam.com/video/eKkh5Xm0OlU/w-d-xo.html Note that your best bet might be multiple SQL calls. It sounds inefficient but it is actually the best choice sometimes. Otherwise you end up with a really messy call that is hard on SQL (this is where Entity Framework gets into trouble as well).
I had some problems getting the ProductId. So I went up the chain: Sale Controller wasn't receiving => saleVM wasn't sending => ProductEndpoint wasn't receiving => ProductController wasn't sending => SqlDataAccess wasn't receiving => Stored Procedure didn't return the Id Column. I must have missed the addition of it way back when it was created.
Apologies for the late comment, as I just started to watch this playlist. When I try to save a sale an exception is thrown due to the SaleDate being 1/1/0001. I'm guessing I missed where some sort of system date is assigned to the SaleDate property before the spSale_Insert stored procedure is called OR is a default value being assigned within SQL Server? Thanks. Great playlist, btw.
In the SaleDBModel, we set the default value of the SaleDate field to be DateTime.UtcNow. We could have also done it as the default value in the table.
I originally had this issue and it was because of a misspelling of my column name for createDate vs. createdDate. It caused me to get a null which also caused 1/1/0001. Once the saleModel and Table column name matched it fixed the problem.
Hey guys in SaveData we seem to pass all the values to our stored procedure through the item object and its property values. Is there a way to just pass a type like an int rather than an object containing properties? I have a stored procedure for deleting rows and I got it to work, but only by creating a whole new model class called Tester(I was testing) and giving it an int property to use as the number for which row# to delete. More importantly Tim if you're reading this does your SQL Database course on your site teach C# to SQL communication because that's where I'm struggling. Also good luck on the live videos later today. I'd love to join, but I figure I should learn the basics before I go on trying to convert everything over to .NetCore.
Yep, you can pass just one value (your int) if you would like. Check out my Dapper videos on this channel (search the channel for Dapper) and you will see how to do it.
Hi Tim, So if I wanted a MVC front end, instead of WPF, would it be best to create a full new MVC project in the solution, or add the code inside the API project?
The cart needs to be a client-side item. Otherwise, you will have a massive data issue to deal with down the road. How long do you keep a cart? How do you link a cart back to a session? If you try to use the cart information to update the quantity on hand, you need to consider cart abandonment, since abandoned carts will be holding your inventory. There's a lot of issues to work through with trying to do server-side carts. Not that they can't be done, but they aren't simple.
Hi Tim. I am returning to this project, after taking a break for an extended period. When I try and run it, I receive a "Bad Request" error message. Can you point me to a previous video, for a refresher to resolve this. I am thinking that my "token" has expired, and I need to do something with Swagger, but my length of time away from the project, has me "grasping at straws", somewhat. Any help would be appreciated, by this Patreon subscriber...
If you are trying to use a token from when you previously were working in this then yes, the token has expired. You would need to authenticate again to get the new token. However, if you are trying to log in and it isn't working, you might want to check the database to be sure your credentials are still in place. Check the config file to see what your Entity Framework database name is and then check in that database in the Users table to be sure your user information is in there. If it is, you might have forgotten your password, in which case you will need to create a new account (the easiest thing to do).
Hello Tim. I found some interesting spot. It calls a bug and it took me for a wile to figure out where I did a mistake. I will try to explain as clear as possible. When you moved `Config Helper` and add `appsetings` to Web.config in API project. I Did exactly as you did. So the bug is, when ever I call calculateTax() in the API side it failed parse our taxRate, it catching information from a Web.config and I see a string representation of this key but it does not convert it to decimal. So I played a lot, and when I have changed to it does work. But In WPF side it works with "." So as far as I understand some how, a have two different locales in my WPF and API. I have added you to my GitHub repository to be able to see wat is an Issue with it. It may be also useful for others if they have same kind of bug. Thank you for good lessons.
Thanks for sharing how you approached working through the issues. That is often more valuable then the solution itself. Thanks for the thought of sharing the code, but I don't have time to review it.
Not sure why the data is null when hitting break point in the SaleController. I am OK getting the data up to the line of code 'using (HttpResponseMessage response = await _apiHelper.ApiClient.PostAsJsonAsync("/api/Sale", sale))' but when I hit that break point in the SaleController and check the sale object it says salesdetails are null? Any suggestions would be appreciated.
This is a common issue. Usually it means that your model in your UI does not match the model your API expects. What happens is that the front-end encodes your model into JSON. On the API side, it attempts to decode your JSON into your API's model. If names don't match or you are missing a property, you will have an issue.
Hey Tim thank you very much for your advice!!! I found a small typo in the model that I probably would not have noticed if you hadn't suggested where to look. Works like a charm now. Man those details are important!!!! Excellent learning point for me though.
Hi Everyone, Just want to ask if below is a good approach for this kind of application For every transaction on Sales View, the application will create a new transaction on the API that will hold the temporary data (Selected Products for the current client) and every time the user(cashier) add a new item in the Cart, it will post the product details in API and API will store or save it for a temporary table, just in case the application crash on the cashier's computer or the cashier needs to print the receipt of the last client, the application will be able to get all selected products of the current client and also to avoid the so much data on the network to push in API (I hope everyone understands above, English is just my second lang. :)
That would be possible. How necessary it would be is something I would think through. It is a lot of extra overhead and complexity to add but I'm not sure it adds enough value. If your app is crashing a lot, it would seem like you should fix that. If it doesn't crash a lot, this feature is rarely, if ever, getting used.
@@IAmTimCorey , coming from a point of sale background, there's actually a good use-case here: "Suspending" a sale. E.g. customer forgets their wallet in their car, clerk saves sale (without completing it), serves another customer in line, then original customer returns with wallet and transaction is recalled. In this instance the original items and pricing should be recalled, but only committed when final.
System.Data.SqlClient.SqlException: 'Cannot insert the value NULL into column 'Id', table 'HRMData.dbo.Sale'; column does not allow nulls. INSERT fails. The statement has been terminated.'
That means you didn't set up the Id column to be an IDENTITY column. Adding that means the column will have an auto-incremented value inserted into it.
Good video Tim but PLEASE PLEASE not so long time videos when has to do with tutorials applications from the begin to end, it is very difficult to watch
I am not sure what you are asking. This series is about building a real-world application. We are going to spend a lot of time working on it because that's what you do in the real world. I also have one-off videos for teaching the various topics we see in this course. This course isn't about teaching those topics necessarily. It is about applying what you already know into a real application.
I guess all these stored procedures are going to be removed when this eventually is converted to .netcore. I would hate to have to make a change in a database field that we want to propagate all the way up tot the c# code :-O
Nope, the stored procedures will stay. There isn't really another option for changing your SQL database. That will have to be reflected all the way up. Even if you use EF, you are still changing from database to code. You are just pretending you aren't by having EF write the changes. For us, if we need to make a change, we can use refactoring to make the change up through.
I disagree that returning the Id as a return type rather than as an output variable is a better option. Return types can be error codes. By returning an Id that way, you aren't specifying what the value represents, so it could be mistaken for something else. By using an output variable, it is obvious what that Id represents.
@@IAmTimCorey Yeah, maybe. But it didn´t work anyway, so let´s forget about this "idea". I am curious to learn about your solution for output parameters. The way you got the SaleId back is working and that is fine, and I used this way in earlier solutions, too but it always seemed to be somewhat clumsy. There has to be a better way!
Thanks again for this great lesson. Even though I am watching it a bit later than the course ran, I have some suggestions and I ran into a bug, that may go unnoticed for a long time. First the bug: you attempted to move the taxRate configuration from app.config in TRMDespTopUI project to web.config in the Api layer, TRMDataManager. But, you did NOT remove the value from App.config. The bug is you are still reading this value from app.config. This may become nasty at some point. Because I deleted this value, I ran into tax trouble. Still ned to find out how to read from Web.Config properly. I have a refactoring suggestion. Please rename the projects to something that is better descriptive for what each project should do. Suggestions: TRMDataManager, call it APILayer , TRMData, call it TRMDatabaselayer or something like that. The desktop is OK, but TRMDesktopUI.Library may be called TRMDestopBackend.Library. A smaller suggestion, the dapper data access methods can be static, so no need to instantiate an object first. Finally, I liked that you present a small outline on what is going on. I the starter course you give homework immediately. There i miss a bit of background of the concepts to use and some explanations to prevent starting in total confusion. It helps to introduce the core concepts briefly and then I can try it for myself first. That's it for today. Thank you for listening :-).
Just found what happens. The desktop reads from App.config and the api from web.config. I did not find a good solution. I think finally we must refactor this to always get the taxRate using the API. If I were the customer for this app, I definitely would not approve the present solution.
Thanks for the detailed write-up. The tax rate bug is one that is already in the issue tracking system (#26). We will fix it in an upcoming video. I'll consider the project renames. As for the Dapper class being static, later on we add transactions to the Dapper class, which means we cannot make it static. Thanks again for your suggestions.
Also Apologies for the late comment. In SaleData.cs and retrieving sale.Id with making another call to database with "spSale_Lookup". Couldn't we have retrieved the sale.Id with the previous database save Here: " sql.SaveData("dbo.spSale_Insert", sale, "TRMData"); " Because at the end of the storedProcedure there is a "select @Id = @@IDENTITY;" that is returned?Or doesn't work that way here? so we wouldn't have to do this look up: sale.Id = sql.LoadData("spSale_Lookup", new { sale.CashierId, sale.SaleDate }, "TRMData").FirstOrDefault(); Just curious don't want to call unnecessary resources with database.
Hi Tim, Will there be a video on creating a custom keypad to send keypresses for touchscreen devices to key in their credentials on the login screen and quantity for the sales page?
We might develop a self-serve kiosk device but as for keypress events, they are no different than buttons. When the button is pushed, you send that key to the textbox. Touch vs mouse click isn't any different.
ok i tried sending the keypresses to the textbox but whenever a button is pressed the textbox loses focus. how should i go about approaching this problem?
Hi! I want to make sure I understand the connections between all the projects: (-> means uses) TRMDesktopUI -> TRMDesktopUI.Library -> TRMDataManager -> TRMDataManager.Library -> TRMData If that is true, is that ok for the TRMDataManager.Library to use the method GetProductById (Minuit 1:07:00) ? Thanks!!
I am having problems with saving the SaleModel. I call sql.SaveData("dbo.spSale_Insert", sale, "TRMData") and it throws an exception in SqlDataAccess that says "System.Data.SqlClient.SqlException HResult=0x80131904 Message=Procedure or function 'spSale_Insert' expects parameter '@Id', which was not supplied. Source=.Net SqlClient Data Provider" My spSale_Insert is like this: CREATE PROCEDURE [dbo].[spSale_Insert] @Id int output, @CashierId nvarchar(128), @SaleDate datetime2, @SubTotal money, @Tax money, @Total money AS begin set nocount on; insert into dbo.Sale(CashierId, SaleDate, SubTotal, Tax, Total) values (@CashierId, @SaleDate, @SubTotal, @Tax, @Total); select @Id = @@Identity; end I call the SqlDataAccess SaveData like this: sql.SaveData("dbo.spSale_Insert", sale, "TRMData"); My SalDBModel.cs looks like this: public class SaleDBModel { public int Id { get; set; } public string CashierId { get; set; } public DateTime SaleDate { get; set; } = DateTime.UtcNow; public decimal SubTotal { get; set; } public decimal Tax { get; set; } public decimal Total { get; set; } } When I inspect the sale value at runtime, everything looks fine but Id is defaulted to 0. I don't know how to debug this further. Thanks for the course - I bought the paid version so I have source as well.
I had a similar issue with spSaleDetail_Insert and after some digging I found that I left off the identity keyword in the SaleDetail.sql (TRMData >> dbo >> Tables) Correct: [Id] INT NOT NULL PRIMARY KEY Identity, Incorrect: [Id] INT NOT NULL PRIMARY KEY,
I liked here how used comments to show in the beginning what we're going to do. This way i could already do it myself before i watched you do it. Best way to practice.
Awesome!
Wow, was that a trip... I learned a lot, both from the material and from messing it up and having to recover. Took me maybe a week, and ran over it a few times, but among other things I learned more about reverting to previous versions in Git and paying attention to the UI in VS to keep better track of exactly which class you're working on ("Is he in the API models? Or the UI models?" I now know what to look for to actually answer that!)
As always, fantastic material and I really appreciate that you've offered this for free. Just being able to follow along and wind up with something functional feels like an achievement.
Well done. Sticking with it when it is difficult is hard, but it is what gives you even more learning opportunities.
I've learnt so much from you Tim, with I think the most important two phrases being (in your words) "Minimum Viable Product" and to "keep moving". I find myself being so bogged down with trying to do something the "correct way", that I end up literally getting nowhere. Build it now - refactor later - don't let perfection become the enemy of progress. For me - this whole series is worth watching for those phrases alone - Thank you so much.
I am glad it was helpful.
Almost 2 hours tutorial. Now this is what I like to see. :)
Glad you are enjoying it.
Thanks again Tim!
Definitely a remarkable work leading to one understand the meaning of Debugging & Refactoring and the worth of each of the said activities! Sure did cost me around some time to finish up successfully with the feel of achievement!
They are definitely important skills to have and grow.
This video gave me so much motivation and excitement about personal progress and moving forwards. Thank you very much! Really appreciate it! All the best wishes to you!
You are welcome.
When you talk about models across the ui and api at ~35:00 how does that fit with the idea of data transfer objects (dto)? Would/ could you have a 6th lib for those?
The models in my data access layer are really DTOs. I just don't call them that. They transport data between the database and the UI and they don't contain business logic. That's basically the definition of a DTO. Good question.
Great tutorial, I would like to see the use of Azure pipelines for CI/CD for each project in the solution.
Thanks for the suggestion. I'll add it to the list.
Hello Tim, again a great video, thank you! Thumbs up, as always!
You are welcome.
Since this is transactional data - my preference is to perform the entire sale transaction at the stored procedure. By iteratively performing individual inserts from C#, you're setting yourself up for future pain if there's a failure anywhere in the C# loops. You'll be left with a partial transaction in the DB - a nightmare scenario.
The problem is that we have a set of data to transfer over to SQL. The only alternative is to create a TVP in SQL and then use that in a stored procedure to transfer all of the data over. You are right about a failure in C# being a problem, which is why in an upcoming video, I replace this section with a C#-side SQL transaction with rollback in case of issue (including exceptions).
Awsome video ! It helped me a lot to correct my actual way of posting data into my API.
I am glad it was so helpful.
I was waiting for you to hardcode the credentials, I was tired of it a few videos ago 😂😂
lol
I'm surprised nobody else brought up the fact that there needs to be one transaction for saving both sale and saledetails. You might also want to change @@IDENTITY to SCOPE_IDENTITY(), though. The problem with getting Id the way you are doing is that it does not guarantee the right value, and so this would never fly in the real world. I think with Dapper you need to treat it like a query, where you do the insert first and then the select right after, like connection.Query('sproc', parameter, etc)
Good catch. I'm going to have to rework that a bit. Because of the work I'm doing to prepare the details, I'm not sure if I'll create one stored procedure for both or if I'll start a transaction in code. Not a huge fan of transactions from code but it might be a viable option. Thanks for pointing out the issues.
Tim,
I found an easy way to return the SaleID:
1. At the end of the sp, use "select scope_identity() as Id" to return the value.
2. When calling the sp, use {var result = sql.SaveData("dbo.spSale_Insert", sale, "TRMData")}
then you will get the ID returned properly.
Thanks for sharing.
Nope, doesn't work with the SaveData as defined by the previous course
@@SuperDre74 it works, you just need to dig further and check how to modify your SqlDataAccess.SaveData method,. The details can be seen for example in this StackOverflow answer:
stackoverflow.com/questions/8270205/how-do-i-perform-an-insert-and-return-inserted-identity-with-dapper/47110425
@@SuperDre74 In my case I've ended up with following changes:
public T1 SaveData(string storedProcedure, T2 parameters, string connectionStringName)
{
string connectionString = GetConnectionString(connectionStringName);
using (IDbConnection cnn = new SqlConnection(connectionString))
{
return cnn.Query(storedProcedure, parameters, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
}
@@SuperDre74 also please do not forget to publish your migration (changes) to procedure when you do any. And be ready that the identity column for Sale and SaleDetail can jump up by 1000, it happens after SQL server restart, as described in Tim's answers in one of the comments.
"MVP says we make things work before making them prettier"
Uncle Bob screams.
Nah. Software is an iterative processes. You can still write your code with best practices, but you don't need to do it in the one pass. Making perfect code in one pass only works in pre-planned demos.
According to Uncle Bob:
"Clean, extendible code that doesn't work is great, because I can make it work."
"Ugly code that works will be useless tomorrow when the requirements change."
He is talking about the end product, not while you are building it. If you build something quick and ugly and then leave it as is and move on, yes, that's a problem. If you get something working, and then refactor it to make it "right", that is smart development.
@@IAmTimCorey ah, so MVP != release candidate. I misunderstood.
Loved the tangent. 😊
I'm glad.
Hi Tim,
Thanks as always for the great content,
Would you consider changing the mechanism by which an item is added to the cart?
In a real world scenario, a retail has hundreds of items, finding an item in a list to add it to the cart will be cumbersome,
It might be nice to provide a search option for the items, or even better: entry via barcode.
I'm hoping to add that in. For the initial version, I wanted something quick and easy. We can add in other entry mechanisms as part of our refactoring or as other options (various modes).
Apologies if this is considered necro posting on YT. I did some searching after getting to the point where the insert into dbo.Sale failed to set/return the Id. The Id can be returned through Dapper with a few changes:
1.Change the insert query in spSale_Insert to 'insert into ... output inserted.Id values ...;'
2.Remove 'select @Id = @@Identity;' from spSale_Insert
3a. Change SqlDataAccess.SaveData to return an int
3b. Change connection.Execute into 'return connection.Query(...).SingleOrDefault()'
SaveData appears to work just fine even if called with a stored procedure with no output value.
Thank you for the courses, I've really been enjoying them.
Thanks for the suggestion. The issue here, though, is that the number returned from an insert/update is typically the number of records affected. If you change what that value is, you may confuse other developers.
That's very nice,
Thank you very much!
on getting inserted ID i suggest to create another method on DataAccess with ExcecuteScalar, and procedure
INSERT INTO Sale(CashierId,SaleDate,SubTotal,Tax,Total)
OUTPUT INSERTED.Id
VALUES (@CashierId,@SaleDate,@SubTotal,@Tax,@Total);
Thanks for the suggestion.
Thanks, that is an elegant way to do it :D
Thx buddy
Hello Tim. Thanks a lot for these very good video. I'm writing the code as the same time I listen to the video.
But I'm blocking at the minute 46:40 because in my SaleController (where you put your break point at the console writeline) the sale parameter is empty on my side. And I cannot figure out why. Do you have an idea?
Thank again for all your job
When sending data into an API, it can be a bit tricky because it expects the object to be shaped a certain way. Make sure that your names exactly match the properties and make sure the data you are sending into those types are correct.
@@IAmTimCorey You're right, that was the problem. Thanks for your help.... and these very good tutorials. Now I 've to find out what's I did wrong in the module Upgrading to .NET Core: Adding JWT Authentication to Our API
I had this same issue (on the paid course) and I'm glad this was answered. In my situation, I accidentally had one called SalesDetails instead of SaleDetail. So, good catch. @IAmTimCorey, I know you don't want to use the same model, but is there where using an interface would work better? That way, if you utilized ISaleModel, then you know the fields will match up?
Many thanks for the lesson Tim, took me a few sittings to get through it all but managed it. Keep up the great work. One thing I was sad about was the separate SP to get the ID back, you should have paused the recording and then shown it all as one save and return new ID as that would be what is needed in the real world. No need for the spSale_Lookup.
I debated that but at the end of the day, this is exactly what I would have done to get the product out the door. Then I would refactor it to be better. That is what I will be doing. I don't want to represent the perfect path when that isn't the reality in development.
@@IAmTimCorey Perfectly understandable, and looking back probably would do that too. :)
at 29:20 you are using a foreach loop to send the data on button click right? I am making a form which uses 2 listboxes and a few textboxes to then Submit it. how would i go about doing that i am assuming foreach won't do it because i am not putting stuff into a different ListBox as you are doing with Cart also i am loving these series and i am just trying to create my own thing based upon this course! EDIT; i think i've managed to make it work by just adding the stuff without the foreach
I am glad you figured it out.
I wanna "switch" from Qt c++/qml to Wpf and Xamarin because of your Videos. MVVP/WPF looks now at the beginning a little bit confusing to me, it seems to be there are some black magic behinde the scenes. But the power and productivity behinde it seems to be really amazing.
I am very fascinated.
Thanks for your Videos dude, these are very great! Keep it up.
I'm glad you are enjoying them.
Hello Tim I have been trying to figure out how at the 46:50 mark the SaleController is getting the values of type API's SaleModel rather than type UI's SaleModel. I have made a chart on OneNote mapping everything out and I've gone over the source code file, but it's not clicking. Later in the video you pass the API SaleModel parameter in SaleController to the SaveSale method which has a foreach loop. I don't understand how the API SaleModel parameter in the SaleController has any products in its SaleDetails List. If you would be so kind as to explain when that List is assigned those products I would truly appreciate it.
When you transfer data to an API or get data from an API, you don't pass C# structures. What happens is that it converts the C# structure to JSON for the transfer. On the other end, C# takes the JSON and converts it to the requested model. So on the WPF end, we are passing a WPF model to the API. It gets converted to JSON, then on the API side it gets converted to the model we specified (the API's SalesModel). You don't even have to have the same "shape" model. You could send FirstName and LastName but the model that receives could receive just FirstName. The LastName value would be dropped silently.
@@IAmTimCorey Got it. Ok so upon calling the PostAsJsonAsync we call the controller and that's when the conversion from c# to json happens. I did notice that both model classes need to have the same name and that the detailModel property names need to match as well in order for the conversion to work from c# to json. Thank you
Hello Tim, just a comment. While watching this video I remembered you usually recommend appending "async" to the end of async methods' name. Shouldn't the GetAll() method be named GetAllAsync()? (ProductEndpoint class).
Maybe it is something you have already modified, I see the video has almost one year, but I'm watching it now for the first time.
Thanks.
In an API, they are all async so it becomes redundant.
Hi Tim,
I don't know if someone asked about this earlier. The SaleDetail table has the 'PurchasePrice' column. Every time I come across it, it gets me thinking this is the price the seller paid for the item.
I don't know if I'm clear about it. Down the road in a fully working application you might want to introduce some report that will extract the total margin, total value of sales etc.
That being the case you'll find yourself creating a way to store the purchases which will probably have the same PurchasePrice column as the price the seller paid.
If you'll want to calculate the margin you'll find yourself extracting one PurchasePrice (you paid) from another PurchasePrice (you charged from your customer).
It might be confusing.
To me, the application should always reflect the point of view of the owner: the owner is purchasing (hence PurchasePrice), the owner is selling (hence RetailPrice).
Already in the UI library the ProductModel reflects the price as RetailPrice (that I prefer).
As always, great content. Keep up the good work.
There are three different prices to keep track of: Inventory PurchasePrice, Product RetailPrice, and SaleDetail PurchasePrice. Each is different. The one in the inventory indicates how much the item cost the owner to purchase. The one in the product indicates how much the item is listed for in the store. The one in the sale detail indicates how much the person actually paid for it (if they used a coupon, it would be smaller than retail, etc.) From these three values, we can create any type of report we want.
@@IAmTimCorey Yes, I agree with you, they are different, that's why I'm used to naming them different to avoid any confusion down the line, for me or anyone coming after me to review the code.
Thanks for the reply :)
Not only that, but I keep thinking 'PurchasePrice' in SaleDetail is the actual price per item, not the total, and IMHO it would be better that way and replace Tax with TaxRate.
At about point 1:22:00 in this video, you mentioned that it would be probably wise to not make many database calls. I agree and would like to add that using EF, you could have replaced some 10 lines of code with just 2 lines. You should probably get over your EF bias. A good place to start is probably to read "Entity Framework Core In Action" by Jon P. Smith.
The problem with EF (one of them) is that it is very easy to pull more data than you think you are. If you put a method call in the where, EF is actually pulling every record and then running the where (which won't be an issue until you go to production and try that against a table with a million records). Its gotchas like that that give me pause. I will be teaching EF Core 3 (where they helped "fix" problems like the above with messaging) but "simple" things in EF are just hiding dangers below the surface. Here are some of my thoughts: www.iamtimcorey.com/blog/137806/entity-framework
@@IAmTimCorey I read through the link you posted and I see you have some good points. But, in my opinion, the very reason why most people would use EF at all, and as you stated the reason why you wouldn't, is precisely because it generates code. Imagine having to write boring manual code for 30-100 or more entities. The fact that the bulk of the code is generated for you is precisely why you should be interested in such an option. The code generated is C#, so I don't understand why you wouldn't want to support it. Do you want to tell me the generated code is above your level of C# so that you wouldn't want to support it?
Anyway, I am looking forward to your EF Core 3 course. I believe that one would definitely be interesting, giving the way you approach C# development.
The issue I have is people saying that generated code is easier. It IS easier to create, but it is harder to maintain and generally less performant unless you really know your stuff (which gets back to the point of you not needing to generate your code). A good rule of thumb is that 10% of EF queries are slow and should be tweaked manually or replaced with stored procedures. Are people benchmarking their app and doing the work to identify the slow queries and replace them? Nope. So they are shipping slow data access layers because they were "easier" and "faster to create". Back in the day, we had FrontPage for web development. You could drag and drop to create a web page but it was messy on the back-end, slower than it should be, and more complicated to work with the HTML. EF is in that same pattern. Here is a good example of a simple EF vs. Dapper performance test: exceptionnotfound.net/dapper-vs-entity-framework-core-query-performance-benchmarking-2019/ Now this isn't scientific, which I actually like because this is what a newer developer would do - write code and assume since it worked that it was fine. Notice the performance differences. EF is about 10x slower than Dapper at doing the same thing. Again, not scientific and your mileage may vary. The point is that just writing EF code can very easily result in SLOW production code. At that point, you are committed to EF so you have to learn the depth of EF to figure out how to improve the performance. Next thing you know, you've spent more time fixing EF than you would have had you just wrote the queries you needed to in order to use Dapper.
The EF course will be an interesting one. We will spend a lot of time learning how to diagnose performance issues and how to fix them.
Hi Tim, I am new to C# and just wondering in the Data Access layer, for example ProductData.GetProducts, (1:04:00) shouldn't it be an asynchronous operation async Task GetProducts() ? what is the difference or why it is not necessary to do async? Thanks!
We will convert it to async further on in the series. It doesn't have to be because the API is implicitly asynchronous whether your calls are or not, but we still should make the call async.
Nice video watched the the whole thing.
re new identity field, here is how I do it using Dapper (which I have a base class for).
1. Add this line to the end of the dynamic dapper parms
base.Parms.Add("@Id", dbType: DbType.Int32, direction: ParameterDirection.Output);
2. in the stored procedure I used SET @Id = SCOPE_IDENTITY() // probably the same as select @Id=@@Identity; like you have
3. back to the c# code...
var affectedrows = await connection.ExecuteAsync(sql: "spSale_Insert", param: base.Parms, commandType: System.Data.CommandType.StoredProcedure);
int newID = base.Parms.Get("NewId");
return newID;
Hopefully this make sense. The key point is to include the extra Dapper parameter for the newly created @@Identity and then retrieve it after executing the insert stored procedure thereby eliminating the extra spSale_Loopkup stored procedure.
thanks
Thanks for the suggestion. I was trying to avoid that way, since it means adding a new Save method overload that uses DynamicParameters (currently I pass in type T as the parameters). I am passing in the Id field using my method but it isn't returning it to the model. I'm going to have to look into if I can get away with doing it the same way I'm doing it or if I have to write a new method to convert my model to parameters and back again after the data comes back.
Hey Tim,
This topic intrigued me, so I tried something in a small project I'm working on.
There's no need to add a new Save method, you just have to refactor it a bit so that it takes a dynamic object as extra parameter.
You need to pass the DataModel as this extra parameter. Then, create the DynamicParameters in the Save method and add @Id to it as output.
After executing the stored procedure, just use the DynamicParameters.Get to set the dynamic object's id.
Something like this :
internal static void CreateData(string storedProcedure, T parameters, dynamic obj)
{
DynamicParameters parameter = new DynamicParameters();
parameter.Add("@Id", DbType.Int32, direction: ParameterDirection.Output);
using (IDbConnection connection = new SqlConnection(GetConnection()))
{
connection.Execute(storedProcedure, parameters, commandType: CommandType.StoredProcedure);
obj.Id = parameter.Get("@Id"); }
}
You probably would like to replace the creating of parameter to a separate method.
Kind regards, Serge
Great course, Tim! Learning a lot.
Just one question, how would you map nested SQL relationships to the c# models? For example, if you wanted to fetch all cashier records and for each of them, all their sales, and for each sale, the sale detail as well. How would I go about creating a model that supports this kind of query?
I'd really appreciate the help, thanks!
I demo some options here: th-cam.com/video/eKkh5Xm0OlU/w-d-xo.html Note that your best bet might be multiple SQL calls. It sounds inefficient but it is actually the best choice sometimes. Otherwise you end up with a really messy call that is hard on SQL (this is where Entity Framework gets into trouble as well).
@@IAmTimCorey Thanks Tim!
I had some problems getting the ProductId.
So I went up the chain: Sale Controller wasn't receiving => saleVM wasn't sending => ProductEndpoint wasn't receiving
=> ProductController wasn't sending => SqlDataAccess wasn't receiving => Stored Procedure didn't return the Id Column. I must have missed the addition of it way back when it was created.
That will do it. I'm glad you were able to track it down. Doing that is really helpful for improving your debugging skills.
Apologies for the late comment, as I just started to watch this playlist. When I try to save a sale an exception is thrown due to the SaleDate being 1/1/0001. I'm guessing I missed where some sort of system date is assigned to the SaleDate property before the spSale_Insert stored procedure is called OR is a default value being assigned within SQL Server? Thanks. Great playlist, btw.
In the SaleDBModel, we set the default value of the SaleDate field to be DateTime.UtcNow. We could have also done it as the default value in the table.
@@IAmTimCorey Thanks Tim. Much appreciated.
I originally had this issue and it was because of a misspelling of my column name for createDate vs. createdDate. It caused me to get a null which also caused 1/1/0001. Once the saleModel and Table column name matched it fixed the problem.
Another question, What does your APIHelper look like? is that available? thanks
We built that in this video (timecode is where we actually start the build): th-cam.com/video/3eIc68VWxgE/w-d-xo.html
Tim, I'm looking some view about reflection c#. I need to call the methods randomly during run time.
If possible, avoid reflection (it can be slow and usually there is a better way). Maybe look into delegates or events as other options.
@@IAmTimCorey thank you 👍
@@IAmTimCorey can you make some prism framework video
Hey guys in SaveData we seem to pass all the values to our stored procedure through the item object and its property values. Is there a way to just pass a type like an int rather than an object containing properties? I have a stored procedure for deleting rows and I got it to work, but only by creating a whole new model class called Tester(I was testing) and giving it an int property to use as the number for which row# to delete. More importantly Tim if you're reading this does your SQL Database course on your site teach C# to SQL communication because that's where I'm struggling. Also good luck on the live videos later today. I'd love to join, but I figure I should learn the basics before I go on trying to convert everything over to .NetCore.
Yep, you can pass just one value (your int) if you would like. Check out my Dapper videos on this channel (search the channel for Dapper) and you will see how to do it.
"Now we can go ahead and save the sale record"
Proceeds to press save on the document.
Made me laugh
😂
I think you should add the the cost price to sales details data , to get net profit sales in future.
Thanks for the suggestion.
Hi Tim,
So if I wanted a MVC front end, instead of WPF, would it be best to create a full new MVC project in the solution, or add the code inside the API project?
We are going to be adding an MVC project as a separate project. You could add it directly to the API but I prefer the separation.
Put the whole cart inside the API. Makes things like updating the Qty on hand easier.
The cart needs to be a client-side item. Otherwise, you will have a massive data issue to deal with down the road. How long do you keep a cart? How do you link a cart back to a session? If you try to use the cart information to update the quantity on hand, you need to consider cart abandonment, since abandoned carts will be holding your inventory. There's a lot of issues to work through with trying to do server-side carts. Not that they can't be done, but they aren't simple.
Not init SaleDetails Property [ In SaleModel in UI.Library ] retun null , it has to be init somewhere to hit the API controller
Hi Tim. I am returning to this project, after taking a break for an extended period. When I try and run it, I receive a "Bad Request" error message. Can you point me to a previous video, for a refresher to resolve this. I am thinking that my "token" has expired, and I need to do something with Swagger, but my length of time away from the project, has me "grasping at straws", somewhat. Any help would be appreciated, by this Patreon subscriber...
If you are trying to use a token from when you previously were working in this then yes, the token has expired. You would need to authenticate again to get the new token. However, if you are trying to log in and it isn't working, you might want to check the database to be sure your credentials are still in place. Check the config file to see what your Entity Framework database name is and then check in that database in the Users table to be sure your user information is in there. If it is, you might have forgotten your password, in which case you will need to create a new account (the easiest thing to do).
Why not call the existing SP for all products, and filter by the product ID?
Because that would return a lot of unnecessary data. That isn't a big deal with four or five records but four or five hundred would be a problem.
Hello Tim. I found some interesting spot. It calls a bug and it took me for a wile to figure out where I did a mistake. I will try to explain as clear as possible. When you moved `Config Helper` and add `appsetings` to Web.config in API project. I Did exactly as you did. So the bug is, when ever I call calculateTax() in the API side it failed parse our taxRate, it catching information from a Web.config and I see a string representation of this key but it does not convert it to decimal. So I played a lot, and when I have changed to it does work. But In WPF side it works with "." So as far as I understand some how, a have two different locales in my WPF and API.
I have added you to my GitHub repository to be able to see wat is an Issue with it.
It may be also useful for others if they have same kind of bug.
Thank you for good lessons.
Thanks for sharing how you approached working through the issues. That is often more valuable then the solution itself. Thanks for the thought of sharing the code, but I don't have time to review it.
you can also add a line to the Web.config file:
Source: docs.microsoft.com/en-us/troubleshoot/aspnet/set-current-culture
Not sure why the data is null when hitting break point in the SaleController. I am OK getting the data up to the line of code
'using (HttpResponseMessage response = await _apiHelper.ApiClient.PostAsJsonAsync("/api/Sale", sale))' but when I hit that break point in the SaleController and check the sale object it says salesdetails are null? Any suggestions would be appreciated.
This is a common issue. Usually it means that your model in your UI does not match the model your API expects. What happens is that the front-end encodes your model into JSON. On the API side, it attempts to decode your JSON into your API's model. If names don't match or you are missing a property, you will have an issue.
Hey Tim thank you very much for your advice!!! I found a small typo in the model that I probably would not have noticed if you hadn't suggested where to look. Works like a charm now. Man those details are important!!!! Excellent learning point for me though.
Please is this tutorial still relevant in todays world??
Definitely.
@@IAmTimCorey Okay, Thank you alot for the update.
Hi Everyone,
Just want to ask if below is a good approach for this kind of application
For every transaction on Sales View, the application will create a new transaction on the API that will hold the temporary data (Selected Products for the current client) and
every time the user(cashier) add a new item in the Cart, it will post the product details in API and API will store or save it for a temporary table, just in case the application crash on the cashier's computer or the cashier needs to print the receipt of the last client, the application will be able to get all selected products of the current client and also to avoid the so much data on the network to push in API (I hope everyone understands above, English is just my second lang. :)
That would be possible. How necessary it would be is something I would think through. It is a lot of extra overhead and complexity to add but I'm not sure it adds enough value. If your app is crashing a lot, it would seem like you should fix that. If it doesn't crash a lot, this feature is rarely, if ever, getting used.
@@IAmTimCorey , coming from a point of sale background, there's actually a good use-case here: "Suspending" a sale. E.g. customer forgets their wallet in their car, clerk saves sale (without completing it), serves another customer in line, then original customer returns with wallet and transaction is recalled. In this instance the original items and pricing should be recalled, but only committed when final.
System.Data.SqlClient.SqlException: 'Cannot insert the value NULL into column 'Id', table 'HRMData.dbo.Sale'; column does not allow nulls. INSERT fails.
The statement has been terminated.'
That means you didn't set up the Id column to be an IDENTITY column. Adding that means the column will have an auto-incremented value inserted into it.
Good video Tim but PLEASE PLEASE not so long time videos when has to do with tutorials applications from the begin to end, it is very difficult to watch
I am not sure what you are asking. This series is about building a real-world application. We are going to spend a lot of time working on it because that's what you do in the real world. I also have one-off videos for teaching the various topics we see in this course. This course isn't about teaching those topics necessarily. It is about applying what you already know into a real application.
I guess all these stored procedures are going to be removed when this eventually is converted to .netcore.
I would hate to have to make a change in a database field that we want to propagate all the way up tot the c# code :-O
Nope, the stored procedures will stay. There isn't really another option for changing your SQL database. That will have to be reflected all the way up. Even if you use EF, you are still changing from database to code. You are just pretending you aren't by having EF write the changes. For us, if we need to make a change, we can use refactoring to make the change up through.
@@IAmTimCorey Can we replace EF with Dapper? And which one would be better for future projects with ASP.NET Core?
A better option to return the Id from the storedprocedure would be:
Set @Id=SCOPE_IDENTITY();
Return @Id;
I disagree that returning the Id as a return type rather than as an output variable is a better option. Return types can be error codes. By returning an Id that way, you aren't specifying what the value represents, so it could be mistaken for something else. By using an output variable, it is obvious what that Id represents.
@@IAmTimCorey
Yeah, maybe. But it didn´t work anyway, so let´s forget about this "idea". I am curious to learn about your solution for output parameters. The way you got the SaleId back is working and that is fine, and I used this way in earlier solutions, too but it always seemed to be somewhat clumsy. There has to be a better way!
Thanks again for this great lesson. Even though I am watching it a bit later than the course ran, I have some suggestions and I ran into a bug, that may go unnoticed for a long time.
First the bug: you attempted to move the taxRate configuration from app.config in TRMDespTopUI project to web.config in the Api layer, TRMDataManager. But, you did NOT remove the value from App.config. The bug is you are still reading this value from app.config. This may become nasty at some point. Because I deleted this value, I ran into tax trouble. Still ned to find out how to read from Web.Config properly.
I have a refactoring suggestion. Please rename the projects to something that is better descriptive for what each project should do. Suggestions: TRMDataManager, call it APILayer , TRMData, call it TRMDatabaselayer or something like that. The desktop is OK, but TRMDesktopUI.Library may be called TRMDestopBackend.Library.
A smaller suggestion, the dapper data access methods can be static, so no need to instantiate an object first.
Finally, I liked that you present a small outline on what is going on. I the starter course you give homework immediately. There i miss a bit of background of the concepts to use and some explanations to prevent starting in total confusion. It helps to introduce the core concepts briefly and then I can try it for myself first.
That's it for today. Thank you for listening :-).
Just found what happens. The desktop reads from App.config and the api from web.config. I did not find a good solution. I think finally we must refactor this to always get the taxRate using the API. If I were the customer for this app, I definitely would not approve the present solution.
Thanks for the detailed write-up. The tax rate bug is one that is already in the issue tracking system (#26). We will fix it in an upcoming video. I'll consider the project renames. As for the Dapper class being static, later on we add transactions to the Dapper class, which means we cannot make it static. Thanks again for your suggestions.
Also Apologies for the late comment.
In SaleData.cs and retrieving sale.Id with making another call to database with "spSale_Lookup". Couldn't we have retrieved the sale.Id with the previous database save Here: " sql.SaveData("dbo.spSale_Insert", sale, "TRMData"); " Because at the end of the storedProcedure there is a "select @Id = @@IDENTITY;" that is returned?Or doesn't work that way here?
so we wouldn't have to do this look up:
sale.Id = sql.LoadData("spSale_Lookup", new { sale.CashierId, sale.SaleDate }, "TRMData").FirstOrDefault();
Just curious don't want to call unnecessary resources with database.
Please disregard the above. You do address this. I will wait and listen for now on ;-)
I'm glad you found your answer.
Hi Tim,
Will there be a video on creating a custom keypad to send keypresses for touchscreen devices to key in their credentials on the login screen and quantity for the sales page?
We might develop a self-serve kiosk device but as for keypress events, they are no different than buttons. When the button is pushed, you send that key to the textbox. Touch vs mouse click isn't any different.
ok i tried sending the keypresses to the textbox but whenever a button is pressed the textbox loses focus. how should i go about approaching this problem?
Hi!
I want to make sure I understand the connections between all the projects:
(-> means uses)
TRMDesktopUI -> TRMDesktopUI.Library -> TRMDataManager -> TRMDataManager.Library -> TRMData
If that is true, is that ok for the TRMDataManager.Library to use the method GetProductById (Minuit 1:07:00) ?
Thanks!!
I am having problems with saving the SaleModel. I call sql.SaveData("dbo.spSale_Insert", sale, "TRMData") and it throws an exception in SqlDataAccess that says "System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Procedure or function 'spSale_Insert' expects parameter '@Id', which was not supplied.
Source=.Net SqlClient Data Provider"
My spSale_Insert is like this:
CREATE PROCEDURE [dbo].[spSale_Insert]
@Id int output,
@CashierId nvarchar(128),
@SaleDate datetime2,
@SubTotal money,
@Tax money,
@Total money
AS
begin
set nocount on;
insert into dbo.Sale(CashierId, SaleDate, SubTotal, Tax, Total)
values (@CashierId, @SaleDate, @SubTotal, @Tax, @Total);
select @Id = @@Identity;
end
I call the SqlDataAccess SaveData like this:
sql.SaveData("dbo.spSale_Insert", sale, "TRMData");
My SalDBModel.cs looks like this:
public class SaleDBModel
{
public int Id { get; set; }
public string CashierId { get; set; }
public DateTime SaleDate { get; set; } = DateTime.UtcNow;
public decimal SubTotal { get; set; }
public decimal Tax { get; set; }
public decimal Total { get; set; }
}
When I inspect the sale value at runtime, everything looks fine but Id is defaulted to 0. I don't know how to debug this further. Thanks for the course - I bought the paid version so I have source as well.
Not sure why it doesn't work. It seems similar to mine.
I had a similar issue with spSaleDetail_Insert and after some digging I found that I left off the identity keyword in the SaleDetail.sql (TRMData >> dbo >> Tables)
Correct:
[Id] INT NOT NULL PRIMARY KEY Identity,
Incorrect:
[Id] INT NOT NULL PRIMARY KEY,