DAAB(Data Access Application Block) in Enterprise Library 2.0 - (5회)

DAAB(Data Access Application Block) in Enterprise Library 2.0 - (5회)

	키워드 Enterprise Library 2.0 Data Access Application Block 난이도 중급 작성일 2006-04-09 오후 10:11:55 조회수 1205회 평가점수 5.00 (2명 평가 평균) 첨부파일 (파일없음) 요약설명 DAAB(Data Access Application Block) in Enterprise Library for .NET 2.0은 DAC(Data Access Component) 레이어에서의 반복적인 ADO.NET 코딩량을 감소시켜주며 DB 커넥션 관리와 같은 사용자가 실수하기 쉬운 부분을 관리해주는 DAAB(Data Access Application Block)이 Enterprise Library에 통합된 버전이다. 또한 Enterprise Library 1.x에 있던 DAAB를 ADO.NET 2.0의 변화된 기능에 맞게 수정한 것이 DAAB(Data Access Application Block) in Enterprise Library for .NET 2.0이다.  wooys

[ExecuteScalar를사용하여단일값조회] [고객의ID로고객의신용등급을조회하는등의경우와같이특정값을조회하고자하는경우ExecuteScalar를사용하면된다.   사용 예제1 - C#) Database db = DatabaseFactory.CreateDatabase(); string sqlCommand = "GetProductName"; int productId = 7; DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand,  productId); string productName = (string)db.ExecuteScalar(dbCommand); 사용 예제2 - C#) 아래와 같은Stored Procedure가DB에 만들어져 있다고 가정할 때 CREATE PROCEDURE GetProductName @ProductID int AS SELECT ProductName FROM Products WHERE ProductID = @ProductID Database db = DatabaseFactory.CreateDatabase(); string sqlCommand = "GetProductName"; DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, productID); string productName =(string)db.ExecuteScalar(dbCommand);]   [하나의트랜잭션안에서복수의업데이트수행] [대표적인트랜잭션설명예제인뱅크어플리케이션예제와같이DAAB를사용하여Data Access코드를작성할때하나의트랜잭션안에서여러개의업데이트가트랜잭션이보장되도록작동하게하기위해서는ExecuteNonQuery의오버로드중에서ADO.NET의트랜잭션을파라미터로받아들이는것을사용하면된다. 이는결국COM+와같은자동트랜잭션처리가아닌매뉴얼트랜잭션처리이기는하다. (COM+의분산트랜잭션과DAAB도같이사용될수있다.이에대한자세한내용은프레임워크개발가이드중트랜잭션처리가이드를참고한다) 이외에도T-SQL과같은쿼리문장자체에서트랜잭션이보장되도록할수도있다.예를들면하나의Stored Procedure내에원하는업데이트문장들을BEGIN TRASACTION, END TRANSACTION, ROLLBACK TRANSACTION으로묶는것이다.   첫번째예제는위에설명한바대로ExecuteNonQuery의트랜잭션처리를보여준다. 사용전에아래와같은Stored Procedure가DB에이미생성되어있어야한다. CREATE PROCEDURE credit    @AccountNo CHAR(20),    @Amount SMALLMONEY AS INSERT Credits VALUES (@AccountNo, @Amount) GO   CREATE PROCEDURE debit     @AccountNo CHAR(20),     @Amount SMALLMONEY AS INSERT Debits VALUES (@AccountNo, @Amount) GO   사용 예제1 - C#) public bool Transfer(int transactionAmount, int sourceAccount, int destinationAccount) {  bool result = false;              // Create the database object, using the default database service. The  // default database service is determined through configuration.  Database db = DatabaseFactory.CreateDatabase();    // Two operations, one to credit an account, and one to debit another  // account.  string sqlCommand = "CreditAccount";  DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);    db.AddInParameter(creditCommand, "AccountNo", DbType.Int32, sourceAccount);  db.AddInParameter(creditCommand, "Amount", DbType.Int32, transactionAmount);    sqlCommand = "DebitAccount";  DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);    db.AddInParameter(debitCommand, "AccountNo", DbType.Int32, destinationAccount);  db.AddInParameter(debitCommand, "Amount", DbType.Int32, transactionAmount);    using (DbConnection connection = db.CreateConnection())  {    connection.Open();    DbTransaction transaction = connection.BeginTransaction();      try    {      // Credit the first account.      db.ExecuteNonQuery(creditCommand, transaction);      // Debit the second account.      db.ExecuteNonQuery(debitCommand, transaction);        // Commit the transaction.      transaction.Commit();                          result = true;    }    catch    {      // Roll back the transaction.      transaction.Rollback();     }    connection.Close();                    return result;  } } ]   [데이터를XML로받기(ExecuteXMLReader사용)] [SQL Server 2000/2005에서는SELECT문장끝에FOR XML을붙이게되면쿼리된데이터를XML형태로클라이언트에게돌려준다.즉DB가XML데이터형식을지원한다는이야기인데DAAB내에있는SqlDatabase클래스에는이를위해ExecuteXMLReader라는메서드가준비되어있다. 이메서드는전진전용의XML데이터스트림인XMLReader오브젝트를리턴한다.물론이경우에사용한쿼리문장에는FOR XML절이들어가있어야한다. ExecuteXMLReader를사용해서복수개의행을XML형태로조회하는예제는아래와같다.   사용 예제1 - C#)   SqlDatabase dbSQL = DatabaseFactory.CreateDatabase("EntLibQuickStartsSql") as SqlDatabase;   // Use "FOR XML AUTO" to have SQL return XML data. string sqlCommand = "SELECT ProductID, ProductName FROM Products FOR XML AUTO"; DbCommand dbCommand = dbSQL.GetSqlStringCommand(sqlCommand);   XmlReader productsReader = null; StringBuilder productList = new StringBuilder();   try {  productsReader =dbSQL.ExecuteXmlReader(dbCommand);    // Iterate through the XmlReader and put the data into our results.  while (!productsReader.EOF)  {    if (productsReader.IsStartElement())    {      productList.Append(productsReader.ReadOuterXml());      productList.Append(Environment.NewLine);    }  }   } finally {  // Close the Reader.  if (productsReader != null)  {    productsReader.Close();  }    // Explicitly close the connection. The connection is not closed  // when the XmlReader is closed.  if (dbCommand.Connection != null)  {    dbCommand.Connection.Close();  }        }   위코드에나온것과같이SqlCommand오브젝트의ExecuteXMLReader는아직까지CommandBehavior.CloseConnection을지원하지않기때문에Reader를사용한후에명시적으로커넥션을닫아주어야한다. ]

 

출처 :http://www.gosu.net