Creating the Data Access Component

The COM+ portion of this application comes in the form of a data access component. This component will provide a means for a Web client to access the database. The example doesn't include all the security, integrity, and transactional elements that you'd normally include. However, it does show the mechanics of creating the connection by using a managed component.

Listing 11-2 shows the code for this example. You'll also find the listing in the Chapter 11\HelpAccess folder of the book's companion content. Notice that this example shows how to pass multiple values back to the client-an important technique for anyone who creates components of any complexity. Microsoft Visual Basic developers would replace the C# ref keyword with the ByRef keyword. The use of the ref keyword is required, even though Strings are reference variables .

Listing 11-2: A simple data access component

 [Guid("DF830C4C-7EB0-48ec-BBDE-98DA886AAFCA"), InterfaceType(ComInterfaceType.InterfaceIsDual)] publicinterfaceIHelpAccess { voidGetTitle(refStringstrTopicNumber, refStringstrTitle, refStringstrContents); voidGetTopic(refStringstrTopicNumber, refStringstrTitle, refStringstrContents); } [Guid("C7E5F3CF-043A-439c-8358-3AD9F21FA3A4"), ClassInterface(ClassInterfaceType.None)] publicclassHelpAccess:ServicedComponent,IHelpAccess { publicHelpAccess() { } #regionIHelpAccessMembers publicvoidGetTitle(refStringstrTopicNumber, refStringstrTitle, refStringstrContents) { SqlConnectionConn;//DatabaseConnection SqlCommandCmd;//DataSelection SqlDataReaderReader;//DataContainer Object[]Data;//OneRowofDataValues //Createaconnectiontotheserver. Conn=newSqlConnection("InitialCatalog=HelpMe;"+ "DataSource=WinServer;"+ "IntegratedSecurity=SSPI;"); //Createacommandforretrievingthedata. Cmd=newSqlCommand("SELECT*FROMHelpInformation"+ "WHERETitle"='"+strTitle+"'",Conn); //Opentheconnectionandexecutethecommand. Conn.Open(); Reader=Cmd.ExecuteReader(); //Loadthefieldcountfromthedatabase. Data=newObject[Reader.FieldCount]; //Determineifthedatabasefoundtherequestedinformation. if(Reader.Read()) { //Ifso,returnthisinformationtotheuser. Reader.GetValues(Data); strTopicNumber=Data[0].ToString(); strTitle=Data[1].ToString(); strContents=Data[2].ToString(); } else { //Otherwise,returnanerrormessage. strContents="Couldn'tfindthesearchvalue:"+strTitle; strTopicNumber="99999"; strTitle="ErrorFindingValue"; } //Closethedatabaseconnection. Cmd.Connection.Close(); } publicvoidGetTopic(refStringstrTopicNumber, refStringstrTitle, refStringstrContents) { SqlConnectionConn;//DatabaseConnection SqlCommandCmd;//DataSelection SqlDataReaderReader;//DataContainer Object[]Data;//OneRowofDataValues //Createaconnectiontotheserver. Conn=newSqlConnection("InitialCatalog=HelpMe;"+ "DataSource=WinServer;"+ "IntegratedSecurity=SSPI;"); //Createacommandforretrievingthedata. Cmd=newSqlCommand("SELECT*FROMHelpInformation"+ "WHERETopicNumber='"+strTopicNumber+ "'",Conn); //Opentheconnectionandexecutethecommand. Conn.Open(); Reader=Cmd.ExecuteReader(); //Loadthefieldcountfromthedatabase. Data=newObject[Reader.FieldCount]; //Determineifthedatabasefoundtherequestedinformation. if(Reader.Read()) { //Ifso,returnthisinformationtotheuser. Reader.GetValues(Data); strTopicNumber=Data[0].ToString(); strTitle=Data[1].ToString(); strContents=Data[2].ToString(); } else { //Otherwise,returnanerrormessage. strContents="Couldn'tfindthesearchvalue:"+ strTopicNumber; strTopicNumber="99999"; strTitle="ErrorFindingValue"; } //Closethedatabaseconnection. Cmd.Connection.Close(); } #endregion } 

 

The two methods in this component operate essentially the same way. The difference is the value they require as input, the search criteria for the database, and the two values supplied as output. Consequently, I'll describe just one of the methods in the paragraphs that follow.

The code begins by creating a connection to the SQL server. The example uses a simple connection string. You must replace the Data Source value with the name of your own server or the example won't work. Make sure you replace this value in both locations in which it appears (once at the beginning of each method). Normally, you'd check for errors at the end of this call. (In fact, you'd normally perform this operation in a trycatch block.)

After the code creates the connection, it creates a command to access the data contained within the database. The formulation of this command is important because it determines the search criteria the database uses to search for the requested data. The code hasn't actually searched for the data yet-it has simply created the command required to perform the task.

At this point, the code opens the connection and executes the command. It still hasn't read the data, but the code has created a SqlDataReader object that can perform the task. The Reader variable does know the number of fields within the requested table at this point, so the code creates the Data array that will hold the information for the requested record upon return from the Reader.Read() method call. If the table doesn't contain the requested information, the Reader.Read() method returns false .

The code is finally ready to retrieve the data values using the Reader.GetValues() method. This method places the information from the three fields within the Data array. It's important to realize that SQL server doesn't guarantee the data will be returned in any particular order, but it is generally returned in the same order in which the fields appear in the table. When you create complex queries using multiple related tables, always access the field values using specific names rather than the array approach used here. In this case, you'd use the Reader.GetValue() method and supply a string containing the requested field name.

Notice that the method returns default values if the database doesn't contain the requested information. At this point, the code closes the database connection and returns the data values to the caller.