DAAB(Data Access Application Block) in Enterprise Library 2.0 - (5회)
| DAAB(Data Access Application Block) in Enterprise Library 2.0 - (5회) |
 |  |  | ||||||||||||||||
| ||||||||||||||||||
 | |  |
[ExecuteScalar를사용하여단일값조회] [고객의ID로고객의신용등급을조회하는등의경우와같이특정값을조회하고자하는경우ExecuteScalar를사용하면된다. 사용 예제1 - C#) string sqlCommand = "GetProductName"; string productName = (string)db.ExecuteScalar(dbCommand);
CREATE PROCEDURE GetProductName Database db = DatabaseFactory.CreateDatabase(); string sqlCommand = "GetProductName"; 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를사용한후에명시적으로커넥션을닫아주어야한다. ] |