So far in this video series, we have not written any sql queries to retrieve data using linq to sql. We write a linq query, and the underlying LINQ to SQL provider dynamically generates the T-SQL required. For some reason if we want to have complete over the SQL that is executed against the database, then we can use ExecuteQuery() or ExecuteCommand() methods of the DataContext class.
Text version of the video
http://csharp-video-tutorials.blogspo...
Slides
http://csharp-video-tutorials.blogspo...
LINQ to SQL Tutorial - All Text Articles & Slides
http://csharp-video-tutorials.blogspo...
LINQ to SQL Tutorial Playlist
https://www.youtube.com/playlist?list...
Dot Net, SQL, Angular, JavaScript, jQuery and Bootstrap complete courses
https://www.youtube.com/user/kudvenka...
In this video we will discuss how to use ExecuteQuery() and ExecuteCommand() methods to directly execute SQL queries. Let us understand this with an example.
Use the following code to execute the above query using DataContext object's ExecuteQuery() method
using (SampleDataContext dbContext = new SampleDataContext())
{ IEnumerable[Student] students = dbContext.ExecuteQuery[Student]("Select * from Students where Gender='Male'"); foreach (Student student in students) { Console.WriteLine(student.FirstName + " " + student.LastName); }
}
In the above example, we have hardcoded Gender. If you want to parameterize the query, then use the following syntax.
using (SampleDataContext dbContext = new SampleDataContext())
{ IEnumerable[Student] students = dbContext.ExecuteQuery[Student]("Select * from Students where Gender={0}", "Male"); foreach (Student student in students) { Console.WriteLine(student.FirstName + " " + student.LastName); }
}
If you want to perform an Insert, Update or Delete then use ExecuteCommand() method. This method returns the number of rows affected by the query. The following code updates all 4 male student's gender to Female.
using (SampleDataContext dbContext = new SampleDataContext())
{ int count = dbContext.ExecuteCommand("Update Students set Gender='Female' where Gender='Male'"); Console.WriteLine("Rows Updated = {0}", count);
}
Is it a good practice to use ExecuteQuery() or ExecuteCommand() methods to directly execute SQL queries?
No, use these methods only if absolutely necessary, that is when LINQ to SQL is not able to generate optimal SQL queries that you are expecting. In most of the cases LINQ to SQL does a pretty decent job in generating optimal sql queries. When we use ExecuteQuery() or ExecuteCommand() methods we loose the expressive power of LINQ and the advantage of having strongly-typed variables in queries.
What is the difference between ExecuteQuery and ExecuteCommand methods in linq
ExecuteQuery is used to perform a Select, while ExecuteCommand is used to perform Insert, Update, Delete or for calling a stored procedure.
Part 14 How to directly execute sql queries using Linq to SQL asp.net core docker | |
| 47 Likes | 47 Dislikes |
| 19,148 views views | 524K followers |
| Education | Upload TimePublished on 18 Sep 2014 |
Không có nhận xét nào:
Đăng nhận xét