Microsoft Enterprise Library June 2005--Data Access Application Block访问Oracle数据库（1）

Microsoft Enterprise Library June 2005--Data Access Application Block访问Oracle数据库（1）

  前几日有幸拜读了Rickie Lee的《Microsoft patterns & practices Enterprise Library系列分析文章》（请参见http://rickie.cnblogs.com/archive/2005/02/21/104472.aspx）。赶紧去网站下载了Microsoft patterns & practices Enterprise Library，并安装使用。下面是我利用Enterprise Library实现访问oracle，并完成添加，删除，修改等数据库操作.
   1.新建一个windows应用程序解决方案,使用Enteprise Library配置工具创建配置文件(这些在Rickie Lee的文章已经有详细描述，我就不多写了)。
    这是我的配置文件
    ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝App.config===================================
    <configuration>
  <configSections>
    <section name="enterpriselibrary.configurationSettings" type="System.Configuration.IgnoreSectionHandler, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  </configSections>
  <enterpriselibrary.configurationSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" applicationName="Application" xmlns="http://www.microsoft.com/practices/enterpriselibrary/08-31-2004/configuration">
  <configurationSections>
    <configurationSection name="dataConfiguration" encrypt="false">
      <storageProvider xsi:type="XmlFileStorageProviderData" name="XML File Storage Provider" path="dataConfiguration.config" />
      <dataTransformer xsi:type="XmlSerializerTransformerData" name="Xml Serializer Transformer">
        <includeTypes />
      </dataTransformer>
    </configurationSection>
  </configurationSections>
  <keyAlgorithmStorageProvider xsi:nil="true" />
  <includeTypes />
</enterpriselibrary.configurationSettings>
</configuration>

======================dataConfiguration.config========================
<?xml version="1.0" encoding="utf-8"?>
<dataConfiguration>
  <xmlSerializerSection type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=1.1.0.0, Culture=neutral, PublicKeyToken=null">
    <enterpriseLibrary.databaseSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" defaultInstance="Oracle Instance" xmlns="http://www.microsoft.com/practices/enterpriselibrary/08-31-2004/data">
      <databaseTypes>
        <databaseType name="Oracle Server" type="Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase, Microsoft.Practices.EnterpriseLibrary.Data, Version=1.1.0.0, Culture=neutral, PublicKeyToken=null" />
      </databaseTypes>
      <instances>
        <instance name="Oracle Instance" type="Oracle Server" connectionString="Oracle Connection String" />
      </instances>
      <connectionStrings>
        <connectionString xsi:type="OracleConnectionStringData" name="Oracle Connection String">
          <parameters>
            <parameter name="pwd" value="zl" isSensitive="true" />
            <parameter name="uid" value="zl" isSensitive="false" />
            <parameter name="server" value="workserver" isSensitive="false" />
          </parameters>
          <packages />
        </connectionString>
      </connectionStrings>
    </enterpriseLibrary.databaseSettings>
  </xmlSerializerSection>
</dataConfiguration>

2.程序运行的主界面
 

3.程序代码如下：

//注意命名空间的引用
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Configuration;

using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.OracleClient; //注意命名空间的引用 using Microsoft.Practices.EnterpriseLibrary.Data; using Microsoft.Practices.EnterpriseLibrary.Configuration; namespace EnterpriseDDAB { /// <summary> /// Form1 的摘要说明。 /// </summary> public class Form1 : System.Windows.Forms.Form { private DataSet m_DataSet = new DataSet(); private System.Windows.Forms.DataGrid dataGrid1; private System.Windows.Forms.Button btnDataSet; private System.Windows.Forms.Button btnUpdateDS; private System.Windows.Forms.Button btnDBCmdWrp; /// <summary> /// 必需的设计器变量。 /// </summary> private System.ComponentModel.Container components = null; public Form1() { // // Windows 窗体设计器支持所必需的 // InitializeComponent(); // // TODO: 在 InitializeComponent 调用后添加任何构造函数代码 // } /// <summary> /// 清理所有正在使用的资源。 /// </summary> protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows 窗体设计器生成的代码 /// <summary> /// 设计器支持所需的方法 - 不要使用代码编辑器修改 /// 此方法的内容。 /// </summary> private void InitializeComponent() { this.dataGrid1 = new System.Windows.Forms.DataGrid(); this.btnDataSet = new System.Windows.Forms.Button(); this.btnDBCmdWrp = new System.Windows.Forms.Button(); this.btnUpdateDS = new System.Windows.Forms.Button(); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit(); this.SuspendLayout(); // // dataGrid1 // this.dataGrid1.DataMember = ""; this.dataGrid1.Dock = System.Windows.Forms.DockStyle.Top; this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText; this.dataGrid1.Location = new System.Drawing.Point(0, 0); this.dataGrid1.Name = "dataGrid1"; this.dataGrid1.Size = new System.Drawing.Size(824, 472); this.dataGrid1.TabIndex = 0; // // btnDataSet // this.btnDataSet.Anchor = System.Windows.Forms.AnchorStyles.Bottom; this.btnDataSet.Location = new System.Drawing.Point(16, 512); this.btnDataSet.Name = "btnDataSet"; this.btnDataSet.Size = new System.Drawing.Size(104, 23); this.btnDataSet.TabIndex = 1; this.btnDataSet.Text = "ExecuteDataSet"; this.btnDataSet.Click += new System.EventHandler(this.btnDataSet_Click); // // btnDBCmdWrp // this.btnDBCmdWrp.Anchor = System.Windows.Forms.AnchorStyles.Bottom; this.btnDBCmdWrp.Location = new System.Drawing.Point(136, 512); this.btnDBCmdWrp.Name = "btnDBCmdWrp"; this.btnDBCmdWrp.Size = new System.Drawing.Size(184, 23); this.btnDBCmdWrp.TabIndex = 2; this.btnDBCmdWrp.Text = "DBCommandWrapper ExecuteDataSet"; this.btnDBCmdWrp.Click += new System.EventHandler(this.btnDBCmdWrp_Click); // // btnUpdateDS // this.btnUpdateDS.Anchor = System.Windows.Forms.AnchorStyles.Bottom; this.btnUpdateDS.Location = new System.Drawing.Point(344, 512); this.btnUpdateDS.Name = "btnUpdateDS"; this.btnUpdateDS.Size = new System.Drawing.Size(144, 23); this.btnUpdateDS.TabIndex = 7; this.btnUpdateDS.Text = "Update DataSet"; this.btnUpdateDS.Click += new System.EventHandler(this.btnUpdateDS_Click); // // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(6, 14); this.ClientSize = new System.Drawing.Size(824, 542); this.Controls.Add(this.dataGrid1); this.Controls.Add(this.btnUpdateDS); this.Controls.Add(this.btnDataSet); this.Controls.Add(this.btnDBCmdWrp); this.Name = "Form1"; this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen; this.Text = "Execute DataSet"; this.Load += new System.EventHandler(this.Form1_Load); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit(); this.ResumeLayout(false); } #endregion /// <summary> /// 应用程序的主入口点。 /// </summary> [STAThread] static void Main() { Application.Run(new Form1()); } private void Form1_Load(object sender, System.EventArgs e) { } private void btnDataSet_Click(object sender, System.EventArgs e) { //利用DAAB Database db = DatabaseFactory.CreateDatabase(); string file = db.ConfigurationName; m_DataSet = db.ExecuteDataSet(CommandType.Text,"select * from test"); this.dataGrid1.DataSource = m_DataSet.Tables[0].DefaultView; } private void btnUpdateDS_Click(object sender, System.EventArgs e) { Database db = DatabaseFactory.CreateDatabase(); DataSet dsTest = m_DataSet.GetChanges(); //构造InsertCommand,UpdateCommand,DeleteCommand DBCommandWrapper insertCommandWrapper = db.GetStoredProcCommandWrapper("InsertTest"); insertCommandWrapper.AddInParameter("aid", DbType.String, "id", DataRowVersion.Current); insertCommandWrapper.AddInParameter("aname", DbType.String, "name", DataRowVersion.Current); DBCommandWrapper deleteCommandWrapper = db.GetStoredProcCommandWrapper("DeleteTest"); deleteCommandWrapper.AddInParameter("aid", DbType.String, "id", DataRowVersion.Current); DBCommandWrapper updateCommandWrapper = db.GetStoredProcCommandWrapper("UpdateTest"); updateCommandWrapper.AddInParameter("aid", DbType.String, "id", DataRowVersion.Current); updateCommandWrapper.AddInParameter("aname", DbType.String, "name", DataRowVersion.Current); int rowsAffected = db.UpdateDataSet(dsTest, "test", insertCommandWrapper, updateCommandWrapper, deleteCommandWrapper, UpdateBehavior.Standard); } private void btnDBCmdWrp_Click(object sender, System.EventArgs e) { /*使用传统的方法访问Oracle OracleConnection conn = new OracleConnection("Password=zl;User ID=zl;server=workserver"); conn.Open(); OracleDataAdapter da = new OracleDataAdapter("select * from employees",conn); da.Fill(m_DataSet,"employees"); this.dataGrid1.DataSource = m_DataSet.Tables[0].DefaultView; */ //利用DAAB和DBCommandWrapper Database db = DatabaseFactory.CreateDatabase(); string file = db.ConfigurationName; //构造一个DBCommandWrapper，并执行 DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper("select * from test"); db.LoadDataSet(dbCommandWrapper,m_DataSet,"test"); //db.ExecuteDataSet(dbCommandWrapper); this.dataGrid1.DataSource = m_DataSet.Tables[0].DefaultView; } } }


4.存储过程代码如下：
create or replace procedure DeleteTest
(aId in varchar2)
is
begin
     delete * from test where id=aid;
end;

create or replace procedure InsertTest
(aId in varchar2,aName in varchar2)
is
begin
     insert into test(id,name) values(aid,aname);
end;

create or replace procedure UpdateTest
(aId in varchar2,aName in varchar2)
is
begin
     update test set name=aName where id = aId;
end;