Always use the ExecuteSqlInterpolatedAsync and the FromSqlInterpolated instead of the older methods shown in this video. This will defend your API for SQL Injection attack.
In particular, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into FromSqlRaw or ExecuteSqlRaw. The FromSqlInterpolated and ExecuteSqlInterpolated methods allow using string interpolation syntax in a way that protects against SQL injection attacks.
I always use Scaffold-dbcontext rather than migrations as I find it a much more clean work flow. Is there a way to get the sprocs now? I recall we used to be able to do this back in the day (was it ef or linqtosql?). It was great, it generated a method on the context with mapped parameters and return types 👌
Hi Patrick, i hope you are fine & doing well, is it safe to pass parameters as you passed to query? i hope there isn't any SQL Injection Issue? please confirm about SQL Injections with these queries... if any then how to avoid ? Thanks
One thing I'm stuck on, and isn't shown in this video is how to run a SELECT SP that returns a set of results that doesn't correspond exactly to a table. It's a complex stored procedure that returns a series of joins, etc. ExecuteSqlRawAsync only returns the number of rows affected and I can't run [TableName].FromRawSql since there is no corresponding scaffolded class (I'm using DB first since the database is huge and already existing).
Switch to Dapper, for that sort of thing especially if you are just doing read only operations. Even when you want to update the data you can use to Dapper acquire the objects with raw sql or SPs then attach those objects to a DbContext if you want to update them.
if your procedure returns more than one dataset, you can convert that complex data to json (FOR JSON PATH), map the procedure result to a keyless dto and then deserialize
@@robsonarruda3722 Hey bro, can you please tell me how to do it. I tried but it didn't work I guess I am doing something wrong. Please explain this in more detail.
Thanks , Can you tell if Oracle Entity Framework also we can work with stored Procedures ? and can you show one example of inner join in stored procedure ?
Strictly seen you could change the SP without the need of changing any code and therefore without the need of re-deploying the application! BUT I would not suggest doing that
Thank you very much , only one question , what is your professional opinion regarding using Stored Procedures or coding the logic on service layer side ? appreciate your answer very much.
Imo, stored procedures are a great way to support quick changes to your T-SQL code by you or third-parties, but most of the time you'll get locked-in to database vendor and in the long run this might become an issue.
SP is the best way of doing things. Faster, clearer, no need to learn any custom tech to do something you already know (sql), and no intermediate layer like ef dapper or anything. You need to know C#, .net, sql. Be good at that. This video (7:00) is a good example of the limitations of an ORM. Who is going to change DB in practice? From sql server to oracle...come on nobody.
Hey there, glad to meet again. I am working my way around sql language and still have one major enquiry. Is there a command to test an SQL query without executing it? I wish that i would play with my code and to return results without altering my physical data in the server. Thanks a lot for taking care of this.
if you have SQL that updates a database and you don't want to actually update, start your query with BEGIN TRAN. Then run the SQL. When the sql is done, you can run ROLLBACK TRAN All of this was done in SQL Server Management Studio CREATE TABLE [dbo].[Dogs]( [id] [int] IDENTITY(1,1) NOT NULL, [DogBreedName] [varchar](50) NULL, CONSTRAINT [PK_DogBreeds] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO insert into Dogs(dogbreedname) values('Basset Hound') select * from Dogs ---you should see one row for the basset hound begin tran insert into Dogs(dogbreedname) values('Beagle') select * from Dogs ---you should see two rows now. New row for beagle rollback tran select * from Dogs should see the basset row now
@@bassethoundgang2800 I was only wondering what does this part of your code does or indicate? CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO thanks a lot
Hi Yazan, I've been looking for a guide on using stored procedure to return object into my viewmodel(keyless entity). Appreciate if you can provide a guide on how to do this? Thanks!
Hi Patrick! Great video - thanks a lot. But one very interesting topic is not explained in your video: How can I create a stored procdedure within the DbContext? This DbContext would be the best place to create a stored procedure if a new database will be set up (Code first migrations). Thanks and best greetings from Austria. Jürgen
You could create an empty migration, and add the code to create the SP. when you execute the migration, the SP are going to be created. Is the best way to create the SP from EF.
This only works if the stored proc result cleanly maps to a known entity, so it's not really real life ready. How do I get EF to analyze the parameters and results of stored procedures, and map stored proc names to methods?
it's better to use Stored Procedures only for UPDATE & DELETE OPERATIONS to avoid first fetch record then call UPDATE OR DELETE method in LINQ... for fetching data simply use LINQ....
thank you so much i try it in insert procedure but alwas show error (syntax error at or near "mark") thats first argument ? do you know what that probelm
when I add migration and update the database it continuously add the data procedure [dbo].[some random name] which is already exit what is the solution for this please help
