EnterpriseLibrary数据访问(2)使用存储过程访问数据库

演示代码下载： http://dev.mjxy.cn/a-entlib-Access-the-database-using-stored-procedures.aspx

使用存储过程访问数据库

 

1.配置文件

view source

print?

01	<configuration>

02	<configSections>

03	<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />

04	</configSections>

05	<dataConfiguration defaultDatabase="QuickStarts Instance" />

06	<connectionStrings>

07	<add name="QuickStarts Instance" connectionString="Database=EntLibQuickStarts;Server=(local);Integrated Security=SSPI;"

08	providerName="System.Data.SqlClient" />

09	</connectionStrings>

10	</configuration>

2.程序代码

view source

print?

01	// TODO: Use Enterprise Library Data Block

02	using Microsoft.Practices.EnterpriseLibrary.Data;

03	using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

04

05	// TODO: Create private field for Database

06	private Database _db = EnterpriseLibraryContainer.Current.GetInstance<Database>("QuickStarts Instance");

07

08	private void MainForm_Load(object sender, System.EventArgs e)

09

{

10	this.cmbCategory.Items.Clear();

11

12	// TODO: Use a DataReader to retrieve Categories

13	using (IDataReader rd = _db.ExecuteReader("GetCategories"))

14

{

15	while (rd.Read())

16

{

17	Category item = new Category(

18	rd.GetInt32(0),

19	rd.GetString(1),

20	rd.GetString(2)

21

);

22	this.cmbCategory.Items.Add(item);

23

}

24

}

25

26	if (this.cmbCategory.Items.Count > 0)

27	this.cmbCategory.SelectedIndex = 0;

28

}

29

30	private void cmbCategory_SelectedIndexChanged(object sender, System.EventArgs e)

31

{

32	this.dsProducts.Clear();

33

34	Category selectedCategory = (Category) this.cmbCategory.SelectedItem;

35	if (selectedCategory == null)

36

return;

37

38	// TODO: Retrieve Products by Category

39	//将存储过程返回的表填充到DataSet 使用表名products,selectedCategory.CategoryID是参数

40	_db.LoadDataSet("GetProductsByCategory",

41	this.dsProducts, new string[] { "products" },

42	selectedCategory.CategoryId);

43

44

}

45

46	private void btnSave_Click(object sender, System.EventArgs e)

47

{

48	// TODO: Use the DataSet to update the Database

49	System.Data.Common.DbCommand insertCommand = null;

50	insertCommand = _db.GetStoredProcCommand("HOLAddProduct");

51	_db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);

52	_db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);

53	_db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current);

54

55	System.Data.Common.DbCommand deleteCommand = null;

56	deleteCommand = _db.GetStoredProcCommand("HOLDeleteProduct");

57	_db.AddInParameter(deleteCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);

58	_db.AddInParameter(deleteCommand, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Original);

59

60	System.Data.Common.DbCommand updateCommand = null; updateCommand = _db.GetStoredProcCommand("HOLUpdateProduct");

61	_db.AddInParameter(updateCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);

62	_db.AddInParameter(updateCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);

63	_db.AddInParameter(updateCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);

64	_db.AddInParameter(updateCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current);

65	_db.AddInParameter(updateCommand, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Current);

66

67	int rowsAffected = _db.UpdateDataSet(this.dsProducts, "Products",

68	insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard);

69

70

71

}