using MDAA to execute a literal SQL (db.ExecuteScalar like sqlCommand.ExecuteScalar) Database db = DatabaseFactory.CreateDatabase("QuickStarts Instance"); int count = (int)db.ExecuteScalar(
CommandType.Text, "SELECT COUNT(*) FROM Customers");
count为什么是0? a a a
最后发现,我执行了BAT文件确实在.\SQLEXPRESS下创建了数据库EntLibQuickStarts; 因为Syntax error 所以各个表中没有数据。
然后又用Query在SQL Server 2005下(数据库是DYLANBOX)创建了EntLibQuickStarts,表中有数据。
所以用 connectionString: Data Source=DYLANBOX;Initial Catalog=EntLibQuickStarts;Integrated Security=True 才正确
db.ExecuteDataSet Database db = DatabaseFactory.CreateDatabase();
DataSet ds = db.ExecuteDataSet(
CommandType.Text, "SELECT * From Customers");
dataGrid1.DataSource = ds.Tables[0];
注意:DatabaseFactory.CreateDatabase()只是定义一个Dabase reference
db.ExecuteScalar / db.ExecuteDataSet 等 responsible for opening and closing the connection to the real database defined in the configuration file
db.ExecuteReader("") for procedure using (IDataReader dataReader = _db.ExecuteReader("GetCategories"))
{ // Processing code while (dataReader.Read())
{
Category item =new Category(
dataReader.GetInt32(0),
dataReader.GetString(1),
dataReader.GetString(2)); this.cmbCategory.Items.Add(item);
}
}
// Procedure in Categories.sql // OSQL -S (local)\SQLEXPRESS -E -i Categories.sql CREATE PROCEDURE dbo.GetCategories
AS /* SET NOCOUNT ON */
SELECT CategoryID, CategoryName, Description
FROM Categories
RETURN
GO
不用存储过程怎么写?
substitude of above 'using procedure' DataSet ds = _db.ExecuteDataSet(CommandType.Text, "Select * from categories"); foreach (DataRow dr in ds.Tables[0].Rows)
{
Category item =new Category(int.Parse(dr[0].ToString()), dr[1].ToString(), dr[2].ToString()); this.cmbCategory.Items.Add(item);
}
--///////////////////////////////////////////////////////////////////// CREATE Procedure GetProductsByCategory
(
@CategoryID int
)
AS
SELECT
ProductID, ProductName, CategoryID, UnitPrice, LastUpdate
FROM
Products
WHERE
CategoryID = @CategoryID
RETURN 0
GO
//不用存储过程 String sel =string.Format("SELECT Products.* FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID WHERE Categories.CategoryID = {0}", selectedCategory.CategoryId);
题外话: cast untype dataset to type dataset
_db.ExecuteDataSet返回的是一个UnTyped dataSet,转为Typed // load the generic data from the data layer into a strongly typed dataset dsTyped.Load( dsUntyped.Tables[0].CreateDataReader(),
LoadOption.OverwriteChanges, dsTyped.mytablename);