更有效的SQL查询运行方式

本文关键字：查询 运行方式 SQL 有效 | 更新日期: 2023-09-27 18:26:00

我想看看是否有一种更短的方法来编写代码来运行SQL查询。我以前使用实体框架，但它的加载速度似乎比使用SQL命令慢得多。任何建议都很好。提前感谢！

以下是我的SQL命令的代码：

        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");
        }

最初，这是我为实体框架编写的代码：

        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");
            }

更有效的SQL查询运行方式

编写一个存储过程，该过程返回一个包含以下列EmployeeID、Admin、EmployeeFirstname的表。此外，可以在存储过程本身中检查员工是否存在（存在对用户更好的IF而不是count（*））。

通过这样做，将只有一个数据库调用，而不是4个。此外，正如Steve所提到的，请确保电子邮件列被索引为

ADO.NET总是比任何ORM都更有效率，因为它更"低级"，当您执行只读查询时，您可以关闭实体框架提供的一些功能。例如，您使用AsNoTracking()来获取实体，但不必让上下文跟踪它们。

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

或者，您可以使用Dapper，为每个实体创建只读查询存储库，它使用ADO.Net方法，但比纯ADO.Net 更高效

相关文章：