Workshop

for RuBoard

This workshop will help reinforce the concepts covered in today's lesson.

Quiz

1:	When would you want to catch the InfoMessage event of a connection object?
A1:	The InfoMessage event is fired when the data store returns non “result set information. Typically, this includes warnings, row counts, and other supplementary information. You use InfoMessage if you need to catch this information; for example, when a SQL Server stored procedure returns information using a PRINT statement.
2:	Where might you store connection strings and why?
A2:	Because connection strings often change and because they may contain security information, they should be stored in a safe place that can be modified without recompiling your application. For ASP.NET applications, a good place is in the Web.config file in the appSettings element. From there it can be programmatically read. Other .NET applications can use the same strategy in their own configuration files. For serviced components , you can take advantage of object construction and store the connection string in the Component Services snap-in.
3:	How can you ensure that your application uses connection pooling?
A3:	First, make sure that Pooling is set to true when using SqlClient or that OLE DB session pooling is enabled when using OleDb. Second, make sure that the connection strings used by the application are identical in every way. Finally, make sure that the security context used by the connection strings is the same; for example, when you're using trusted connections with SQL Server.
4:	How can you make sure that two command objects participate in the same logical unit of work?
A4:	Both command objects must have their Transaction property set to the same transaction object. In addition, the transaction must have been created using the BeginTransaction method of the connection objects they will both use.

Exercise

Q1:

Today write a method that executes two statements against the ComputeBooks database that participate in the same transaction.

A1:

One possible solution to today's exercise is the following method, which deletes a particular order from both the OrderDetails and Orders tables: virtual void DeleteOrder(String orderId) { SqlConnection con = new SqlConnection(_connect); SqlTransaction trans; try { con.Open(); trans = con.BeginTransaction(); try { SqlCommand s1 = new SqlCommand( "DELETE FROM OrderDetails WHERE OrderID ='" + orderId + "'", con,trans); SqlCommand s2 = new SqlCommand( "DELETE FROM Orders WHERE OrderID ='" + orderId + "'", con,trans); s1.ExecuteNonQuery(); s2.ExecuteNonQuery(); // all is well trans.Commit(); } catch (SqlException e) { // log the error trans.Rollback(); } } catch (SqlException e) { // connection failed // log error return; } finally { con.Close(); } }

for RuBoard