在EF中执行SQL语句

朱雀 2023-06-01 11:28 505阅读 0赞

一、为什么要在EF中执行SQL语句  
  使用EF操作数据库，可以避免写SQL语句，完成使用Linq实现，但为什么还要在EF中执行SQL语句呢。如果要写SQL语句，完全可以使用ADO.NET来操作数据库。这样说虽然没错，可是有些时候使用EF操作数据库还是有一些不方便的地方，例如：如果要修改某一条记录，按照EF的正常流程走，需要先把要修改的数据查询出来，然后在去修改，这样不仅麻烦而且性能也低，这时直接使用EF执行SQL语句性能会提高很多。  
    而使用EF执行SQL又比ADO.NET方便，特别是在执行查询语句的时候，EF会把查询到的数据自动保存到数据实体中，省去了使用DataReader的麻烦。同时查询出来的数据还会进行跟踪，如果你修改了查询出的值，之后就可以很方便的使用.SaveChanges()直接更新到数据库了。  
在数据上下文DbContext中有一个Database的属性，Database属性中有两组方法：ExecuteSqlCommand()和SqlQuery()。这两个方法都可以用来执行SQL语句，但这两个方法也有不同点：ExecuteSqlCommand()是不返回结果的，只返回受影响的行数，所以ExecuteSqlCommand()更适合用来执行创建、插入、更新、删除操作（即执行给定的DDL/DML命令）。SqlQuery()则会返回查询到的结果，并将结果保存在数据实体中，所以SqlQuery()更适合执行查询操作。

二、使用ExecuteSqlCommand()执行创建、插入、更新、删除语句  
ExecuteSqlCommand()的使用方法很简单，直接传入SQL语句就可以了，执行完成后会返回受影响的行数。  
在下面的例子中，entity是一个继承自DbContext的对象。

1、执行创建语句

![复制代码][Image 1]

// 执行创建语句
    string strCreateSQL = @"CREATE table test( id int primary key not null,name varchar(16),password varchar(20))";
     // 注意：执行create语句受影响的行数是-1
    int result = entity.Database.ExecuteSqlCommand(strCreateSQL);
    if (result.Equals(-1))
    {
        Console.WriteLine("创建成功!");
    }

![复制代码][Image 1]

2、执行Insert语句

![复制代码][Image 1]

// 执行Insert语句
    string strInsertSQL = @"INSERT INTO test
                                            SELECT 1,'小明','1234' UNION
                                            SELECT 2,'小王','1234' UNION
                                            SELECT 3,'小红','1234'  ";
    int result = entity.Database.ExecuteSqlCommand(strInsertSQL);
    if (result > 0)
    {
           Console.WriteLine("插入成功");
    }

![复制代码][Image 1]

3、执行Update语句

![复制代码][Image 1]

// 执行Update语句
    string strUpdateSQL = @"UPDATE test SET password=@pwd1 WHERE id=@id1;
    UPDATE test SET password=@pwd2 WHERE id=@id2;";
                    SqlParameter[] para =  {
                       new  SqlParameter("@pwd1","ceshi12we"),
                       new  SqlParameter("@id1",1),
                       new  SqlParameter("@pwd2","ceshi127890"),
                       new  SqlParameter("@id2",2),
                    };
    
    int result = entity.Database.ExecuteSqlCommand(strUpdateSQL, para);
    if (result > 0)
    {
        Console.WriteLine("更新成功");
    }

![复制代码][Image 1]

4、执行Delete语句

![复制代码][Image 1]

// 执行删除语句
    string strDelSQL = "delete from test";
    int result = entity.Database.ExecuteSqlCommand(strDelSQL);
    if (result > 0)
    {
         Console.WriteLine("删除成功");
    }

![复制代码][Image 1]

5、执行Drop语句

![复制代码][Image 1]

string strDropSQL = "drop table test";
    int result = entity.Database.ExecuteSqlCommand(strDropSQL);
    if (result.Equals(-1))
    {
        Console.WriteLine("删除成功");
    }

![复制代码][Image 1]

注意：执行DDL语句(create、alter、drop等)返回值是-1，DML(insert、update、delete)返回的是受影响的行数。

三、使用SqlQuery()查询数据  
SqlQuery()是用来执行查询的。SqlQuery()使用前需要指定返回值的类型。返回值类型可以是定义的实体类型，或者基元类型。例如：查询一个用户的完整信息，返回类型就是用户实体类型；如果是统计有多少个用户，返回值就是int类型。  
注意：返回值的个数和名称必须和传入的类型中属性个数、名称相同，不如会报错。

在下面的例子中User是根据数据库表生成的实体类型。

![复制代码][Image 1]

string strSQL = "SELECT * FROM Users WHERE ID>=10 ORDER BY ID DESC";
    var info = entity.Database.SqlQuery<User>(strSQL);
    foreach (var item in info)
    {
           Console.WriteLine("ID:" + item.ID + " " + "登录名:" + item.LoginName + " " + "密码:" + item.Password);
    }

![复制代码][Image 1]

运行结果：

![1033738-20170728105312118-93571413.png][]

前面说过返回值的个数和名称必须和传入的类型中属性个数、名称相同，不如会报错。如果将SQL语句修改为只查询ID、登录名、密码会出现下面的错误：

![1033738-20170728105806727-1437452098.png][]

如果只想查询ID、登录名、密码该怎么办呢？那就需要单独定义一个类（只包含ID、登录名、密码三个属性）来保存数据.

新定义的类，只包含ID、登录名、密码三个属性：

![复制代码][Image 1]

public class newUser
    {
            public int ID { get; set; }
    
            public string LoginName { get; set; }
    
            public string Password { get; set; }
    }

![复制代码][Image 1]

// 方法四：SqlQuery
    try
    {
                    string strSQL = "SELECT ID,LoginName,Password FROM Users WHERE ID>=10 ORDER BY ID DESC";
                    var info = entity.Database.SqlQuery<newUser>(strSQL);
                    foreach (var item in info)
                    {
                        Console.WriteLine("ID:" + item.ID + " " + "登录名:" + item.LoginName + " " + "密码:" + item.Password);
                    }
    }
    catch (Exception ex)
    {
                    Console.WriteLine(ex.Message);
    }

![复制代码][Image 1]

运行结果：

![1033738-20170728110611508-188661716.png][]

![复制代码][Image 1]

返回值是基元类型：

查询用户数量，返回int类型

![复制代码][Image 1]

// 查询用户数量
    string strSQL = "SELECT COUNT(*) FROM test";
    var result = entity.Database.SqlQuery<int>(strSQL);
    // 注意：必须使用循环才会真正的去数据库执行SQL语句，否则不会再数据库执行SQL语句（EF的延迟加载）
    foreach(var item in result)
    {
         Console.WriteLine("用户数量:" + item.ToString());
    }

![复制代码][Image 1]

运行结果：

![1033738-20170728112859586-516877691.png][]

四、使用DbSet<T>下的SqlQuery()  
在每个数据实体集合DbSet<T>下也有一个SqlQuery()，功能与上面介绍的一样，只不过DbSet<T>下的SqlQuery()只能返回DbSet<T>中包含的类型,DbSet<T>下的SqlQuery()在返回数据的同时还会让数据库上下文（DBContext）跟踪返回数据的状态，如果返回的数据发生了修改，就可以使用SaveChanges()将结果直接保存回数据库。而Database.SqlQuery()查出的结果则不能跟踪返回数据的状态。

1、使用实体集合下面的SqlQuery()方法

string strSQL = "SELECT * FROM Users WHERE UserID='002068'";
    User user = entity.Users.SqlQuery(strSQL).FirstOrDefault();
    user.Password = "测试实体下面的SqlQuery方法";
    // 调用SaveChanges()方法可以更新Password字段
    entity.SaveChanges();

2、使用Database下的SqlQuery()方法

如果希望使用Database下的SqlQuery()查询出的数据在修改后也能保存到数据库，可以使用下面的代码：

![复制代码][Image 1]

string strSQL = "SELECT * FROM Users WHERE UserID='002068'";
    User user = entity.Database.SqlQuery<User>(strSQL).FirstOrDefault();
    user.Password = "测试Database下面的SqlQuery方法";
    // 设置这条数据的状态是：Modified，这样可以通知数据上下文，这条记录也被修改了
    entity.Entry<User>(user).State = System.Data.Entity.EntityState.Modified;
    // 调用SaveChanges()方法不可以更新Password字段
    entity.SaveChanges();

![复制代码][Image 1]

转载于:https://www.cnblogs.com/dgg2015/p/11152830.html

[Image 1]: 
[1033738-20170728105312118-93571413.png]: /images/20230601/c697627c137e4c858b00dc6a9006e692.png
[1033738-20170728105806727-1437452098.png]: /images/20230601/bce2beef590e4871bbc1b84ab3c60889.png
[1033738-20170728110611508-188661716.png]: /images/20230601/86d2f5cc2ef6431d999d1ab179fd9cce.png
[1033738-20170728112859586-516877691.png]: /images/20230601/e43fab49f59a4bc59de549575b1ba9a9.png