使用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(); 
                }
            }
        }

转载于:https://www.cnblogs.com/zengdj/archive/2005/07/17/194713.html