Microsoft TS Microsoft .NET Framework 3.5, ADO.NET Application Development

You work as an application developer at Contoso.com. You use Microsoft .NET Framework 3.5 and Microsoft ADO.NET to develop an application that will connect to the Microsoft SQL Server 2005 database. You have
completed the following code segment. (Line numbers are for reference only.)
01 String myConnString = "User ID = ;
02 password = ;
03 Initial Catalog = pubs;
04 Data Source = myServer";
05 SqlConnection myConnection = new SqlConnection(myConnString);
06 SqlCommand myCommand = new SqlCommand();
07 DbDataReader myReader;
08 myCommand.CommandType = CommandType.Text;
09 myCommand.Connection = myConnection;
10 myCommand.CommandText = "Select * from Table1;
11 Select * from Table2;";
12 int RecordCount = 0;
13 try
14 {
15 myConnection.Open();
16
17 }
18 catch (Exception ex)
19 {
20 }
21 finally
22 {
23 myConnection.Close();
24 }
You need to compute the total number of records processed by the Select queries in the RecordCount variable.
What should you do?

You work as an application developer at Contoso.com. You use Microsoft .NET Framework 3.5 and Microsoft
ADO.NET to develop an application that will connect to the Microsoft SQL Server 2005 database. You have completed the following code segment. (Line numbers are for reference only.)
DataTable tbl = new DataTable();
tbl.Columns.Add("Price", typeof(double));
//Other columns added
//Fill data
You need to retrieve the maximum value in the Price column of the tbl DataTable.
What should you do?

You work as an application developer at Contoso.com. You use Microsoft .NET Framework 3.5 and Microsoft
ADO.NET to develop an application that will connect to the Microsoft SQL Server 2005 database. Your application contains the following code. The Customer and Invoice tables are found in the invoice records of
customers that reside in the database. Your application contains the following code.
DataSet ds = new DataSet();
DataTable tblCust = ds.Tables.Add("Customer");
DataTable tblInv = ds.Tables.Add("Invoice");
DataColumn colPar = tblCust.Columns.Add("ID", typeof(int));
tblCust.Constraints.Add("PKey", colPar, true);
tblInv.Columns.Add("InvNo", typeof(string));
DataColumn colChild = tblInv.Columns.Add("CustomerID", typeof(int));
DataRow[] relatedRows;
//Retrieve data for Customer and Invoice
You need to retrieve the invoice details from the tblInv DataTable for customer records that have a value 1 in the ID column of the tblCust DataTable.
What should you do?

You work as an application developer at Contoso.com. You use Microsoft .NET Framework 3.5 and Microsoft
ADO.NET to develop an application that will connect to the Microsoft SQL Server 2005 database. The database has a table named Categories that has a primary key identity column named CategoryID. The application
inserts new records with the following stored procedure.
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName) SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
Your application contains the following code.
SqlDataAdapter adapter = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM
dbo.Categories",connection);
adapter.InsertCommand = new SqlCommand("dbo.InsertCategory", connection);
adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
adapter.InsertCommand.Parameters.Add(new SqlParameter("@CategoryName", SqlDbType.NVarChar,
15,"CategoryName"));
You want to retrieve the identity value for the newly created record. What should you do?

You work as an application developer at Contoso.com. You use Microsoft .NET Framework 3.5 and Microsoft
ADO.NET to develop an application. You have completed the following code segment. (Line numbers are for reference only.)
01 DataTable dtProducts = new DataTable();
02 dtProducts.Columns.Add("ProductID");
03 dtProducts.Columns.Add("ProductName");
04 dtProducts.Columns.Add("CategoryID");
05 dtProducts.Rows.Add(1, "ccie", 1);
06 dtProducts.Rows.Add(2, "ccnp", 2);
07 dtProducts.Rows.Add(3, "ccsp", 1);
08 dtProducts.Rows.Add(4, "ccna", 2);
09 DataTable dtCategories = new DataTable();
10 dtCategories.Columns.Add("CategoryID");
11 dtCategories.Columns.Add("CategoryName");
12 dtCategories.Rows.Add(1, "CISCO products");
13 dtCategories.Rows.Add(2, "a+");
14 var products = dtProducts.AsEnumerable();
15 var categories = dtCategories.AsEnumerable();
16
17 foreach (var element in result) {
18 Console.WriteLine(element);
19 }
Furthermore, when the DataTables are related, the dtProducts DataTable and the CategoryID column as the dtCategories DataTable have a matching value. You need to make an insertion on line 16 so that the product information and the product category of each product are shown.
What should you do?

You work as an application developer at Contoso.com. You use Microsoft .NET Framework 3.5 and Microsoft
ADO.NET to develop an application that will connect to the Microsoft SQL Server 2005 database. Your application contains the following code.
string sql = "Select InvoiceNo, OrderAmount from Orders";
SqlDataAdapter adp = new SqlDataAdapter(sql, con);
DataTable tblOrders = new DataTable();
adp.Fill(tblOrders);
You want the following: If the value of the OrderAmount column is greater than 1500, then a discount of 6 percent is calculated. Furthermore, you need to create a DataColumn object named Discount that contains the discount value for each row in the tblOrders DataTable.
What should you do?

You work as an application developer at Contoso.com. You use Microsoft .NET Framework 3.5 and Microsoft ADO.NET to develop an application. The application uses the Microsoft OLE DB Provider as the data provider for Oracle. The application throws an error when you try to retrieve the Oracle BLOB data. You need to make sure that the Oracle BLOB data can be retrieved.
What should you do?

You work as an application developer at Contoso.com. You use Microsoft .NET Framework 3.5 and Microsoft ADO.NET to develop an application that will connect to the Microsoft SQL Server 2005 database. Your application contains the following code. (Line numbers are for reference only.)
01 using (SqlConnection connection = new SqlConnection(connectionString)) {
02 SqlCommand cmd = new SqlCommand(queryString, connection);
03 connection.Open();
04
05 while (sdrdr.Read()){
06 //use the data in the reader
07 }
08 }
You need to make sure that the memory is used efficiently when retrieving BLOBs from the database.
What should you do?

You work as an application developer at Contoso.com. You use Microsoft .NET Framework 3.5 and Microsoft ADO.NET to develop an application that will connect to the Microsoft SQL Server 2005 database.
You need to separate the security-related exceptions from the other exceptions for database operations at run time.
What should you do?