10直接执行SQL语句

1 執行SQL的方法

有两个重要的方法: ExecuteCommand和ExecuteQuery，在DataContext类里。

1.1 ExecuteCommand

执行不返回数据行的 SQL 语句。用来执行DML（insert、update、delete）和 Procedure

1.2 ExecuteQuery

直接在数据库执行 SQL 查询，返回数据行。用来执行Select语句。

2 应用场景

如果 LINQ to SQL 查询不足以进行特殊化工作，则可以使用 ExecuteQuery 方法执行 SQL 查询，然后将查询结果直接转换为物件。

3 执行SELECT 语句

 // 執行 SELECT 語句
        private void btnExecSelect_Click(object sender, EventArgs e)
        {
            // {0} 等價于 string.fromat("SELECT * FROM Customers WHERE CustomerID={0}","ALFKI")
            // 下面寫法簡潔

            // 方法一
            //IEnumerable<Customer> custs = db.ExecuteQuery<Customer>( "select * from Customers where CustomerID={0}", "ALFKI");
            // 方法二
            object[] para = { "ALFKI", "Berlin" };
            IEnumerable<Customer> custs = db.ExecuteQuery<Customer>("SELECT * FROM Customers WHERE CustomerID={0} AND city={1}", para);

            foreach (var c in custs)
            {
                Console.WriteLine("customerID:{0}", c.CustomerID);
            }
        }

4 执行 DML之insert 语句

 // insert 語句
        private void btnInsertSql_Click(object sender, EventArgs e)
        {
            db.Log = Console.Out;

            object[] para = { "JIM", "TOM", "TOM" };
            int ret = db.ExecuteCommand(" INSERT INTO Customers(CustomerID, CompanyName, ContactName) VALUES({0},{1},{2}) ", para);
            MessageBox.Show("insert successfully");
        }

5执行 DML之update语句

 private void btnUpdateSql_Click(object sender, EventArgs e)
        {
            object[] para = { "TOM2", "TOM2", "JIM" };
            int ret = db.ExecuteCommand(" UPDATE Customers SET CompanyName={0}, ContactName={1} WHERE CustomerID={2} ", para);
            MessageBox.Show("update successfully");
        }

6执行 DML之delete语句

        // delete 語句
        private void btnDeleteSql_Click(object sender, EventArgs e)
        {
            int ret = db.ExecuteCommand(" DELETE Customers WHERE CustomerID={0} ", "JIM");
            MessageBox.Show("delete successfully");
        }

7 执行存储过程

        // Exceute Procedure
        private void btnExecProcedure_Click(object sender, EventArgs e)
        {
            object[] para = { "JIM" };
            int ret = db.ExecuteCommand("exec DeleteCustomer {0}", para);
            MessageBox.Show("Exceute successfully");
        }

8 注意

（1）参数数组元素的个数不能少于SQL语句的参数个数（用{}表示的），例如

object[] para = { "JIM", "TOM", "TOM","other","other2" };
int ret = db.ExecuteCommand(" INSERT INTO Customers(CustomerID, CompanyName, ContactName) VALUES({0},{1},{2}) ", para);

否则异常FormatException

（2）如果有任一参数为 null，则会转换成 DBNull.Value。