CODEWITHSUNDEEP
c#sqlasp.net.netentity-framework-6
Ron T
Ron T

Reputation: 407

More efficient way of run SQL query

I am trying to see if there is a shorter way of writing the code to run the SQL query. I was using Entity Framework before but it seems to load way slower than using SQL commands. Any suggestion would be great. Thanks in advance!

Here is the code to my SQL commands:

        string query = "Select Count(*) From Employee Where Email = @Email And Password = @Password";
        string queryEmployeeId = "Select EmployeeId From Employee Where Email =@Email and Password = @Password";
        string queryAdmin = "Select Admin From Employee Where Email =@Email and Password = @Password";
        string queryFirstName = "Select FirstName From Employee Where Email =@Email and Password = @Password";
        int result = 0;
        int employeeId = 0;
        int admin = 0;
        string employeeFirstName;

        using (SqlConnection connection = new SqlConnection(@"Data Source=198.71.227.2;Initial Catalog=TaskManager;Integrated Security=False;User ID=;Password=;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False"))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Email", txtEmail.Text);
                command.Parameters.AddWithValue("@Password", txtPassword.Text);
                connection.Open();
                result = (int)command.ExecuteScalar();
            }

            using (SqlCommand command = new SqlCommand(queryEmployeeId, connection))
            {
                command.Parameters.AddWithValue("@Email", txtEmail.Text);
                command.Parameters.AddWithValue("@Password", txtPassword.Text);
                employeeId = (int)command.ExecuteScalar();
            }

            using (SqlCommand command = new SqlCommand(queryAdmin, connection))
            {
                command.Parameters.AddWithValue("@Email", txtEmail.Text);
                command.Parameters.AddWithValue("@Password", txtPassword.Text);
                admin = (int)command.ExecuteScalar();
            }

            using (SqlCommand command = new SqlCommand(queryFirstName, connection))
            {
                command.Parameters.AddWithValue("@Email", txtEmail.Text);
                command.Parameters.AddWithValue("@Password", txtPassword.Text);
                employeeFirstName = (string)command.ExecuteScalar();
            }
        }

        if (result > 0)
        {
            Session["EmployeeId"] = employeeId;
            Session["Admin"] = admin;
            Session["EmployeeFirstName"] = employeeFirstName;
            Response.Redirect("~/MyJobSheet.aspx");
        }

Originally, this was my code for the Entity Framework:

        string username = txtEmail.Text;
        string password = txtPassword.Text;

        using (TaskManagerEntities myEntities = new TaskManagerEntities())
        {
            var employee = (from a in myEntities.Employees
                            where a.Email == username && a.Password == password
                            select new { a.EmployeeId, a.Admin, a.Email, a.Password, a.FirstName }).SingleOrDefault();

            if (employee != null)
            {
                Session["EmployeeId"] = employee.EmployeeId;
                Session["Admin"] = employee.Admin;
                Session["EmployeeFirstName"] = employee.FirstName;
                Response.Redirect("~/MyJobSheet.aspx");
            }

Upvotes: 0

Views: 132

Answers (2)

Pedro Benevides
Pedro Benevides

Reputation: 1980

ADO.NET will always be more efficient that any ORM, because its more "low level", what you can do is turn off some features that Entity Framework provide, when you are performing read only query's. For example you case AsNoTracking() for getting your entities, but is not necessary to keep your context tracking them.

var blogs2 = context.Blogs 
                    .Where(b => b.Name.Contains(".NET")) 
                    .AsNoTracking()

Or you can use Dapper, to make a Repository for Read-Only query's for each entity, it uses ADO.Net approach, but is more productive to work than pure ADO.Net

Upvotes: 0

Dalton
Dalton

Reputation: 1354

Write a single Stored procedure which returns a table with the following columns EmployeeID, Admin, EmployeeFirstname .Also the check whether the employee exists can be done in the Stored procedure itself (Better to user IF exists instead of count(*)).

By doing this there will be only one database call instead of 4.Also as Steve mentioned make sure that the Email column is indexed

Upvotes: 2

Related Questions