How can I run raw SQL query in C#? ( With and Without ADO.NET)


I am trying to optimise my query to get data from database, so I would to know how can I directly run a sql query in my C#.

Currently I am using Entity framework with EDMX, so to improve query time, I would like to know how can I run direct sql query?


Asked by:- pika
0
: 7137 At:- 10/21/2019 3:06:06 PM
C# ASP NET SQL query in C#







1 Answers
profileImage Answered by:- Vinnu

You can run SQL query in C#

Without using ADO NET

Note: you need to keep in mind about SQL injection here, so passwing parameters would be ideal

int businessEntityID = 1;
string firstName = "Sander";
string middleName = null;
string lastName = "Rossel";
// create connection to database
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
{
//execute query using  SQL command and passing paramters
using (SqlCommand cmd = new SqlCommand("UPDATE Person.Person SET FirstName = @FirstName, MiddleName = @MiddleName, LastName = @LastName WHERE BusinessEntityID = @BusinessEntityID", connection))
{
    cmd.Parameters.AddWithValue("FirstName", firstName);
    if (middleName == null)
    {
        cmd.Parameters.AddWithValue("MiddleName", DBNull.Value);
    }
    else
    {
        cmd.Parameters.AddWithValue("MiddleName", middleName);
    }
    cmd.Parameters.AddWithValue("LastName", lastName);
    cmd.Parameters.AddWithValue("BusinessEntityID", businessEntityID);
    connection.Open();
    cmd.ExecuteNonQuery();
}
}

With ADO  .NET and Entity framework

Suppose you have SchoolDBEntities and Students table in database

using (var ctx = new SchoolDBEntities())
{
    var studentList = ctx.Students
                        .SqlQuery("Select * from Students")
                        .ToList<Student>();
}

The above query executes Select * from Students SQL in the database to get all students and will be converted into a list of Student entities. The column names in the SQL query must match with the properties of an entity type, otherwise, it will throw an exception.

To Specify parameters you can use as example below:

using (var ctx = new SchoolDBEntities())
{
    var student = ctx.Students
                    .SqlQuery("Select * from Students where StudentId=@id", new SqlParameter("@id", 1))
                    .FirstOrDefault();
}

You can also use Database.ExecuteSqlCommand() insted of SqlQuery() in executing database commands, such as the Insert, Update and Delete command.

using (var ctx = new SchoolDBEntities())
{
    int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update student 
            set studentname ='changed student by command' where studentid=1");

    int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(studentname) 
            values('New Student')");

    int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student 
            where studentid=1");
}

Source of Above example

1
At:- 10/22/2019 3:16:13 PM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use