演示代码下载: http://dev.mjxy.cn/a-entlib-Access-the-database-using-stored-procedures.aspx
使用存储过程访问数据库
1.配置文件
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" /> |
05 |
<dataConfiguration defaultDatabase= "QuickStarts Instance" /> |
07 |
<add name= "QuickStarts Instance" connectionString= "Database=EntLibQuickStarts;Server=(local);Integrated Security=SSPI;" |
08 |
providerName= "System.Data.SqlClient" /> |
2.程序代码
02 |
using Microsoft.Practices.EnterpriseLibrary.Data; |
03 |
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration; |
06 |
private Database _db = EnterpriseLibraryContainer.Current.GetInstance<Database>( "QuickStarts Instance" ); |
08 |
private void MainForm_Load( object sender, System.EventArgs e) |
10 |
this .cmbCategory.Items.Clear(); |
13 |
using (IDataReader rd = _db.ExecuteReader( "GetCategories" )) |
17 |
Category item = new Category( |
22 |
this .cmbCategory.Items.Add(item); |
26 |
if ( this .cmbCategory.Items.Count > 0) |
27 |
this .cmbCategory.SelectedIndex = 0; |
30 |
private void cmbCategory_SelectedIndexChanged( object sender, System.EventArgs e) |
32 |
this .dsProducts.Clear(); |
34 |
Category selectedCategory = (Category) this .cmbCategory.SelectedItem; |
35 |
if (selectedCategory == null ) |
40 |
_db.LoadDataSet( "GetProductsByCategory" , |
41 |
this .dsProducts, new string [] { "products" }, |
42 |
selectedCategory.CategoryId); |
46 |
private void btnSave_Click( object sender, System.EventArgs e) |
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); |
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); |
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); |
67 |
int rowsAffected = _db.UpdateDataSet( this .dsProducts, "Products" , |
68 |
insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard); |