EntityFramework中执行sql语句

// 执行创建语句
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("创建成功!");
}

// 执行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("插入成功");
}

// 执行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("更新成功");
}

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

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

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

public class newUser
{
        public int ID { get; set; }

        public string LoginName { get; set; }

        public string Password { get; set; }
}

// 方法四：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);
}

// 查询用户数量
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());
}

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

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

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();