xcodeabc

[Enterprise Library] Data Access Application Block - NonQuery

1. 在未使用 Enterprise Library 之前原本的做法

public void InsertCust1(String connName)
{
    string sql = @"INSERT INTO CUSTOMERS (customerid, companyname, contactname, contacttitle, "
                    + "address, city, region, postalcode, country, phone, fax) "
                    + "VALUES (:a,:b,:c,:d,:e,:f,:g,:h,:i,:j,:k) ";

    String connString = ConfigurationManager.ConnectionStrings[connName].ConnectionString;

    using (OracleConnection connection = new OracleConnection(connString))
    {
        connection.Open();
        OracleTransaction trans = connection.BeginTransaction();

        try
        {
            OracleCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = trans;

            for (int i = 97; i < 108; i++)
            {
                char c = (char)i;
                cmd.Parameters.Add(":" + c, c);
            }

            cmd.ExecuteNonQuery();

            trans.Commit();
        }
        catch
        {
            trans.Rollback();
        }
    }
}

2. 使用 Enterprise Library 後, 程式碼沒有精簡很多, 但可避免程式碼和特定資料庫的 connection 實作產生依賴, 仍值得一用

public void InsertCust2(String connName)
{
    string sql = @"INSERT INTO CUSTOMERS (customerid, companyname, contactname, contacttitle, "
                    + "address, city, region, postalcode, country, phone, fax) "
                    + "VALUES (:a,:b,:c,:d,:e,:f,:g,:h,:i,:j,:k) ";

    Database db = DatabaseFactory.CreateDatabase(connName);

    using (DbConnection dbConnection = db.CreateConnection())
    {
        dbConnection.Open();
        DbTransaction trans = dbConnection.BeginTransaction();

        try
        {
            DbCommand cmd = dbConnection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            for (int i = 97; i < 108; i++)
            {
                char c = (char)i;
                db.AddInParameter(cmd, ":" + c, DbType.String, c);
            }

            db.ExecuteNonQuery(cmd, trans);

            trans.Commit();
        }
        catch
        {
            trans.Rollback();
        }
    }            
}

App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connStrings>
    <add name="testdb" connString="Data Source=test;User Id=testdb;Password=testdb;" providerName="Oracle.DataAccess.Client" />
  </connStrings>
</configuration>

常用 CDN

[Enterprise Library] Data Access Application Block - NonQuery