基础操作

  基础操作是指使用 ADO.NET 的基础方法来查询并返回数据、执行删除或更新操作等。

1、先说说 IQueryCommand

  所有的查询或执行的方法所接收的参数类型不是 string 而是一个 IQueryCommand 接口类型，这是因为操作的对象可能是一个 SQL，或是存储过程，或是表名，因此，Fireasy 中提供了 SqlCommand、ProcedureCommand 和 TableCommand 这三种类型的操作。这三个类型都可以和 string 进行隐式转换。如下所示：

[TestMethod]
public void TestSqlCommand()
{
    SqlCommand sql1 = "select * from products";
    var sql2 = (SqlCommand)"select * from products";
    var sql3 = new SqlCommand("select * from products");
    var sql4 = (ProcedureCommand)"sp_query_products";
}

2、FillDataSet 方法

  FillDataSet 方法执行查询并将结果填充到一个 DataSet 对象中。如下所示：

[TestMethod]
public void TestFillDataSet()
{
    var ds = new DataSet();
    var sql = (SqlCommand)"select * from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    {
        db.FillDataSet(ds, sql, "products");
    }
}

3、ExecuteDataTable 方法

  ExecuteDataTable 方法执行查询并返回一个 DataTable 对象。如下所示：

[TestMethod]
public void TestExecuteDataTable()
{
    var sql = (SqlCommand)"select * from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var tb = db.ExecuteDataTable(sql, "products");
    }
}

4、ExecuteReader 方法

  ExecuteReader 方法执行查询并返回一个 IDataReader 对象，你可以对其进行遍列读取数据。如下所示：

[TestMethod]
public void TestExecuteReader()
{
    var sql = (SqlCommand)"select * from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    using (var reader = db.ExecuteReader(sql))
    {
        while (reader.Read())
        {
            //读取 reader
        }
    }
}

[TestMethod]
public async Task TestExecuteReaderAsync()
{
    var sql = (SqlCommand)"select * from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    using (var reader = await db.ExecuteReaderAsync(sql))
    {
        while (reader.Read())
        {
            //读取 reader
        }
    }
}

5、ExecuteScalar 方法

  ExecuteScalar 方法执行查询并返回第一行的第列的值，它的另一个泛型重载方法可以将查询的结果转为指定的类型。如下所示：

[TestMethod]
public void TestExecuteScalar()
{
    var sql = (SqlCommand)"select count(1) from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var count1 = db.ExecuteScalar(sql);
        var count2 = db.ExecuteScalar<int>(sql);
    }
}

[TestMethod]
public async Task TestExecuteScalarAsync()
{
    var sql = (SqlCommand)"select count(1) from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var count1 = await db.ExecuteScalarAsync(sql);
        var count2 = await db.ExecuteScalarAsync<int>(sql);
    }
}

6、ExecuteEnumerable 方法

  ExecuteEnumerable 方法执行查询并将结果遍列为指定类型的对象。如下所示：

[TestMethod]
public void TestExecuteEnumerable()
{
    var sql = (SqlCommand)"select productid, productname from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    {
        foreach (var item in db.ExecuteEnumerable<Products>(sql))
        {
            Console.WriteLine(item.ProductName);
        }
    }
}

[TestMethod]
public async Task TestExecuteEnumerableAsync()
{
    var sql = (SqlCommand)"select productid, productname from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    {
        foreach (var item in await db.ExecuteEnumerableAsync<Products>(sql))
        {
            Console.WriteLine(item.ProductName);
        }
    }
}

  ExecuteEnumerable 方法也可以用于基元类型（System.Int32、System.DateTime、System.Boolean 等）和 System.String，或可转换的类型，如 System.Drawing.Size 等等，参见 数据转换器。如下所示：

[TestMethod]
public void TestExecuteEnumerable()
{
    var sql = (SqlCommand)"select productname from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    {
        foreach (var item in db.ExecuteEnumerable<string>(sql))
        {
            Console.WriteLine(item);
        }
    }
}

  ExecuteEnumerable 还有一个不使用泛型的重载方法，则返回一个动态对象，使用该方法可以不需要定义数据模型，但在使用对象时，属性名与 SQL 里的字段名称一致，如果使用 select * 则属性名与表字段名称一致。如下所示：

[TestMethod]
public void TestExecuteEnumerable()
{
    var sql = (SqlCommand)"select productid, productname from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    {
        foreach (var item in db.ExecuteEnumerable(sql))
        {
            Console.WriteLine(item.productname);
        }
    }
}

7、ExecuteAsyncEnumerable 方法

  ExecuteAsyncEnumerable 方法是 .Net Standard 2.1 特有的，返回一个异步的 IAsyncEnumerable 接口。如下所示：

[TestMethod]
public async Task TestExecuteAsyncEnumerable()
{
    var sql = (SqlCommand)"select productid, productname from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    {
        await foreach (var item in db.ExecuteAsyncEnumerable(sql))
        {
            Console.WriteLine(item.productname);
        }
    }
}

8、ExecuteNonQuery 方法

  ExecuteNonQuery 用于执行 Update、 Delete 语句，或存储过程。如下所示：

[TestMethod]
public void TestExecuteNonQuery()
{
    using (var db = DatabaseFactory.CreateDatabase())
    {
        db.ExecuteNonQuery((SqlCommand)"delete from products where productid < 100");
        db.ExecuteNonQuery((ProcedureCommand)"sp_delete_products");
    }
}

[TestMethod]
public async Task TestExecuteNonQueryAsync()
{
    using (var db = DatabaseFactory.CreateDatabase())
    {
        await db.ExecuteNonQueryAsync((SqlCommand)"delete from products where productid < 100");
        await db.ExecuteNonQueryAsync((ProcedureCommand)"sp_delete_products");
    }
}

  ExecuteNonQuery 方法返回一个 int 值，表示影响的行数。

9、ExecuteBatch 方法

  ExecuteBatch 方法可以将一组命令合并在一起进行执行。如下所示：

[TestMethod]
public void TestExecuteBatch()
{
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var commands = new IQueryCommand [] 
        {
            (SqlCommand)"delete from products where productid < 100",
            (SqlCommand)"update orders where orderid < 100",
            (ProcedureCommand)"exec sp_delete_products"
        };
        db.ExecuteBatch(commands);
    }
}

[TestMethod]
public async Task TestExecuteBatchAsync()
{
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var commands = new IQueryCommand [] 
        {
            (SqlCommand)"delete from products where productid < 100",
            (SqlCommand)"update orders where orderid < 100",
            (ProcedureCommand)"exec sp_delete_products"
        };
        await db.ExecuteBatchAsync(commands);
    }
}

10、Update 方法

  Update 方法用于将一个 DataTable 中的数据行更新到数据库中。如下所示：

[TestMethod]
public void TestUpdate()
{
    var sql = (SqlCommand)"select * from products";
        
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var tb = db.ExecuteDataTable(sql, "products");
        tb.Rows[0]["productname"] = "test";
        db.Update(tb);
    }
}