使用Data access block

一、配置Data block所需参数
1,应用程序的配置文件(*.exe.config或者*.dll.config或者Web.config)

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="enterpriselibrary.configurationSettings" type="Microsoft.Practices.EnterpriseLibrary.Configuration.ConfigurationManagerSectionHandler, Microsoft.Practices.EnterpriseLibrary.Configuration" />
  </configSections>
  <enterpriselibrary.configurationSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" defaultSection="" 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" />
</enterpriselibrary.configurationSettings> 
</configuration>

2,配置数据库连接串(dataConfiguration.config)

<?xml version="1.0" encoding="utf-8"?>
<dataConfiguration>
  <xmlSerializerSection type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=1.0.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="InstanceWebinpuy" xmlns="http://www.microsoft.com/practices/enterpriselibrary/08-31-2004/data">
      <databaseTypes>
        <databaseType name="Sql Server" type="Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase, Microsoft.Practices.EnterpriseLibrary.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
      </databaseTypes>
      <instances>
        <instance name="InstanceWebinpuy" type="Sql Server" connectionString="Sql Connection String" />
      </instances>
      <connectionStrings>
        <connectionString name="Sql Connection String">
          <parameters>
            <parameter name="database" value="BMS_Webinput" isSensitive="false" />
            <parameter name="Integrated Security" value="False" isSensitive="false" />
            <parameter name="server" value="192.168.1.28" isSensitive="false" />
          </parameters>
        </connectionString>
      </connectionStrings>
    </enterpriseLibrary.databaseSettings>
  </xmlSerializerSection>
</dataConfiguration>

二、执行Sql语句

public string GetCustomerList()
        {
            // DataReader that will hold the returned results        
            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            string sqlCommand = "Select CustomerID, Name, Address, City, Country, PostalCode " +
                "From Customers";
            DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);

            StringBuilder readerData = new StringBuilder();

            // The ExecuteReader call will request the connection to be closed upon
            // the closing of the DataReader. The DataReader will be closed 
            // automatically when it is disposed.
            using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper))
            {
                // Iterate through DataReader and put results to the text box.
                // DataReaders cannot be bound to Windows Form controls (e.g. the
                // resultsDataGrid), but may be bound to Web Form controls.
                while (dataReader.Read())
                {
                    // Get the value of the 'Name' column in the DataReader
                    readerData.Append(dataReader["Name"]);
                    readerData.Append(Environment.NewLine);
                }
            }

            return readerData.ToString();
        }

三、调用存储过程
1、插入新记录并从存储过程获取返回值
存储过程：

Create   PROCEDURE usp_AddGroup
 @StaffID VARCHAR(36),
 @GroupName VARCHAR(40),
 @Count  INT
AS
 IF EXISTS(SELECT * FROM StaffGroup WHERE StaffID=@StaffID AND GroupName=@GroupName)
  RETURN 1
 ELSE
  INSERT StaffGroup (GroupName,StaffID,MaxCount) VALUES(@GroupName,@StaffID,@Count)
 RETURN @@ERROR
GO

调用代码：

private void button4_Click(object sender, System.EventArgs e)
        {
            //@RETURN_VALUE,RETURN_VALUE,
            Database db = DatabaseFactory.CreateDatabase();
            DBCommandWrapper cmd = db.GetStoredProcCommandWrapper("usp_AddGroup");
            cmd.AddInParameter("@StaffID",DbType.String,"3290F849-031F-49B5-8CEE-0F98AA789731");
            cmd.AddInParameter("@GroupName",DbType.String,"yyyooo");
            cmd.AddInParameter("@Count",DbType.Int32,10);
            cmd.AddParameter("RetVal7",DbType.Int32,ParameterDirection.ReturnValue,"",DataRowVersion.Current,null);
            db.ExecuteNonQuery(cmd);
            int a = (int)cmd.GetParameterValue("RetVal7");
            MessageBox.Show(a.ToString());
        }

2、返回记录集并获取存储过程返回值
存储过程：

CREATE procedure usp_GetValidStaffs
            AS
                Select * from staff where Isdelete = 0 and ShowOnHomePage=1
                RETURN 8
            GO

调用代码：

private void button5_Click(object sender, System.EventArgs e)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DBCommandWrapper cmd = db.GetStoredProcCommandWrapper("usp_GetValidStaffs");
            cmd.AddParameter("RetVal7",DbType.Int32,ParameterDirection.ReturnValue,"",DataRowVersion.Current,null);
            DataSet ds = db.ExecuteDataSet(cmd);
            dataGrid1.SetDataBinding(ds,"Table");
            int a = (int)cmd.GetParameterValue("RetVal7");
            MessageBox.Show(a.ToString());
        }

3、返回记录集并通过输出参数获取返回值
存储过程：

CREATE procedure usp_GetValidStaffs
                @Count INT OUTPUT
            AS
                Select * from staff where Isdelete = 0 and ShowOnHomePage=1
                SET  @Count = 8
            GO

调用代码：

private void button6_Click(object sender, System.EventArgs e)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DBCommandWrapper cmd = db.GetStoredProcCommandWrapper("usp_GetValidStaffs");
            cmd.AddOutParameter("@Count",DbType.Int32,4);
            DataSet ds = db.ExecuteDataSet(cmd);
            dataGrid1.SetDataBinding(ds,"Table");
            int a = (int)cmd.GetParameterValue("@Count");
            MessageBox.Show(a.ToString());
        }

4、DataRearder与输出参数
存储过程：

CREATE procedure usp_GetValidStaffs
                @Count INT OUTPUT
            AS
                Select * from staff where Isdelete = 0 and ShowOnHomePage=1
                SET  @Count = 8
            GO

调用代码：

private void button8_Click(object sender, System.EventArgs e)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DBCommandWrapper cmd = db.GetStoredProcCommandWrapper("usp_GetValidStaffs");
            cmd.AddOutParameter("@Count",DbType.Int32,4);
            using(IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read()) 
                {
                    MessageBox.Show(dr.GetString (1));
                };
            }
            object o = cmd.GetParameterValue("@Count");
            MessageBox.Show(o.ToString());
        }

5、DataRearder与返回值
存储过程：

CREATE procedure usp_GetValidStaffs
            AS
                Select * from staff where Isdelete = 0 and ShowOnHomePage=1
                RETURN 8
            GO

调用代码：

private void button7_Click(object sender, System.EventArgs e)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DBCommandWrapper cmd = db.GetStoredProcCommandWrapper("usp_GetValidStaffs");
            cmd.AddParameter("RetVal7",DbType.Int32,ParameterDirection.ReturnValue,"",DataRowVersion.Current,null);
            using(IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read()) 
                {
                    MessageBox.Show(dr.GetString (1));
                };
            }
            object o = cmd.GetParameterValue("RetVal7");
            MessageBox.Show(o.ToString());
        }

四、事务处理：

public static bool OpretRapportFraskabelon (string CVR, int maanedValoer)
        {
            try
            {
                db = DatabaseFactory.CreateDatabase();
            }
            catch(Exception ex)
            {
                throw new PensamDBException("Fejl i opret databasen",ex); 
            }
            using (IDbConnection connection = db.GetConnection())
            {
                connection.Open();
                IDbTransaction transaction = connection.BeginTransaction();
                try
                {
                    int year = maanedValoer / 12 + 1800;
                    int month = maanedValoer % 12 + 1;
                    DBCommandWrapper cmdWrapper = db.GetStoredProcCommandWrapper("sp_OpretRapportFraSkabelon");
                    cmdWrapper.AddInParameter("@CVR", DbType.String,CVR);
                    cmdWrapper.AddInParameter("@Year", DbType.Int32,year);
                    cmdWrapper.AddInParameter("@Month", DbType.Int32,month);
                    cmdWrapper.AddParameter  ("RetVal",DbType.Int32,ParameterDirection.ReturnValue,"",DataRowVersion.Current,null);
                    db.ExecuteNonQuery(cmdWrapper);
                    int result = (int)cmdWrapper.GetParameterValue("RetVal");
                    return (result == 0);
                }
                catch(PensamDBException ex)
                {
                    throw ex;
                }
                catch(Exception ex)
                {
                    // Rollback transaction 
                    transaction.Rollback();
                    throw new PensamDBException("Fejl i opret ny indebertning fra a older one",ex);
                } 
                finally
                {
                    connection.Close(); 
                }
            }
        }