Posting Sales Data to an API - A TimCo Retail Manager Video 

I am glad it was helpful.

Awesome!

Glad it was helpful!

Well done. Sticking with it when it is difficult is hard, but it is what gives you even more learning opportunities.

They are definitely important skills to have and grow.

Thanks for the suggestion. I'll add it to the list.

You are welcome.

Glad you are enjoying it.

You are welcome.

I am glad it was so helpful.

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 glad.

lol

I'm glad you are enjoying them.

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.

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. :)

There are a few issues with GUIDs though - first, you do need to have a plan for duplication. They are not guaranteed to be unique. Yes, they probably will be but duplication does happen and you have to have a plan for it beyond just crashing your application. Second, GUIDs are far less efficient than integers when it comes to storing data. When data gets stored in SQL, it gets stored by the order of the primary key (most likely the ID field). That means the data is written to the disk in order. If you use an incrementing integer, every record gets put at the end. Simple and quick. If you use a GUID, every time a record gets inserted, the correct spot to insert the record needs to be found. Then the data on disk needs to be shifted so that a new record can be added in the middle so that the records on disk are in order. Third, when you are looking at records manually, it is almost impossible to see and remember a GUID. It makes matching record IDs something that only SQL can do well. Fourth, searching using numbers is much more efficient in SQL vs. searching a text field. Finally, a GUID takes up more memory on disk than an integer does.

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.

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.

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).

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

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!

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.

Thanks for the suggestion.

Thanks for the suggestion.

Thanks, that is an elegant way to do it :D

Thx buddy

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.

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.

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.

I demo some options here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-eKkh5Xm0OlU.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!

In an API, they are all async so it becomes redundant.

That will do it. I'm glad you were able to track it down. Doing that is really helpful for improving your debugging skills.

I am glad you figured it out.

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.

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?

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.

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!

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.

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.

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

We built that in this video (timecode is where we actually start the build): ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-3eIc68VWxgE.html

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

😂

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.

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.

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.

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.

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.

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).

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 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,

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.

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?

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?

Please disregard the above. You do address this. I will wait and listen for now on ;-)

I'm glad you found your answer.

Definitely.

@@IAmTimCorey Okay, Thank you alot for the update.