Insert, update, delete, or select operations in SQL using the WCF service model

The Microsoft BizTalk Adapter for SQL Server discovers a set of basic Insert, Select, Update, and Delete operations on SQL Server database tables and views. By using these operations, you can perform simple SQL Insert, Select, Update, and Delete statements qualified by a Where clause on a target table or view. This topic provides instructions on how to perform these operations using the WCF service model.

For more information on how the adapter supports these operations, see Insert, Update, Delete, and Select Operations on Tables and Views with the SQL adapter.

Note

If you are performing operation on tables that have columns of user-defined types, make sure you refer to Operations on Tables and Views with User-Defined Types using the SQL adapter before you start developing your application.

About the Examples Used in this Topic

The example in this topic performs operations on the Employee table. The Employee table is created by running the SQL script provided with the samples. For more information about samples, see Adapter Samples. A sample, EmployeeBasicOps, which is based on this topic, is also provided with the SQL adapter samples.

The WCF Client Class

The name of the WCF client generated for the basic SQL operations that the SQL adapter discovers is based on the name of the table or view, as listed in the following table.

SQL Server Database Artifact	WCF Client Name
Table	TableOp_[Schema]_[TABLE_NAME]Client
View	ViewOp_[Schema]_[VIEW_NAME]Client

[SCHEMA] = Collection of SQL Server artifacts; for example, dbo.

[TABLE_NAME] = The name of the table; for example, Employee.

[VIEW_NAME] = The name of the view; for example, Employee_View.

Method Signature for Invoking Operations on Tables

The following table shows the method signatures for the basic operations on a table. The signatures are the same for a view, except that the view namespace and name replace those of the table.

Operation	Method Signature
Insert	long[] Insert([TABLE_NS].[TABLE_NAME][] Rows);
Select	[TABLE_NS].[TABLE_NAME][] Select(string COLUMNS, string QUERY);
Update	int Update([TABLE_NS].[TABLE_NAME].RowPair[] Rows);
Delete	int Delete([TABLE_NS].[TABLE_NAME][] Rows);

[TABLE_NS] = The name of the table namespace; for example, schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee.

[TABLE_NAME] = The name of the table; for example, Employee.

As an example, the following code shows the method signatures for a WCF client class generated for the Delete, Insert, Select and Update operations on the Employee table under the default “dbo” schema.

public partial class TableOp_dbo_EmployeeClient : System.ServiceModel.ClientBase<TableOp_dbo_Employee>, TableOp_dbo_Employee {      
    public int Delete(schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee[] Rows);  
  
    public long[] Insert(schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee[] Rows);  
  
    public schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee[] Select(string Columns, string Query);  
  
    public int Update(schemas.microsoft.com.Sql._2008._05.TableOp.dbo.Employee.RowPair[] Rows);  
}  

In this snippet, TableOp_dbo_EmployeeClient is the name of the WCF class in the SqlAdapterBindingClient.cs generated by the Add Adapter Service Reference Plug-in.

Parameters for Table Operations

This section provides the parameters required by each table operation

Insert Operation

Insert operation type	RECORDSET
Multiple record	A collection of INSERTRECORDS that should be inserted into the table.

The insert operation returns an array of Long data type and stores the identity values of the inserted rows, if any. If there is no identity column in a table, the return value is NULL.

Select Operation

COLUMN_NAMES	QUERY
A comma-delimited list of column names in the target; for example, "Employee_ID, Designation". The column list specifies the columns of the target that should be returned in the result set. Columns not specified in the column list will be set to their .NET default values in the returned record set. For nillable columns, this value is null.	The contents of a SQL WHERE clause that specifies the target rows of the query; for example, "Designation = 'Manager'". You can set this parameter to null to return all rows of the target.

The return value of the Select operation is a strongly-typed result set that contains the specified columns and rows from the target table or view.

Update Operation

First row of the pair	Second row of the pair
The first record of the record pair corresponds to new values that need to be updated, that is, it corresponds to the SET clause of the UPDATE statement. This can be set using RowPair.After.	The second record of the record pair corresponds to the old values of the rows, that is, it corresponds to the WHERE clause of the UPDATE statement. This can be set using RowPair.Before.

The return value of the Update operation is of Int32 data type, and denotes the number of rows updated.

Important

While specifying the record that has to be updated, you must provide values for all the columns, even if all values are not being updated. For example, if a row has five columns and the Update operation updates only 2 columns, as part of RowPair.Before, you must pass all the 5 column values. However, for RowPair.After, you can specify only the columns that will be updated.

Delete Operation

The Delete operation takes as input a strongly-typed array of records. The return value of the Delete operation is of Int32 data type, and denotes the number of rows deleted.

Creating a WCF Client to Invoke Operations on Tables and Views

The generic set of actions required to perform an operation on SQL Server using a WCF client involves a set of tasks described in Overview of the WCF Service Model with the SQL adapter. This section describes how to create a WCF client to invoke basic Insert, Select, Update, Delete operations on a table.

To create a WCF client to perform operations on tables

1. Create a Visual C# project in Visual Studio. For this topic, create a console application.

2. Generate the WCF client class for the Insert, Select, Update, and Delete operation on the Employee table. For more information about generating a WCF client class, see Generate a WCF Client or WCF Service Contract for SQL Server Artifacts.

   Important

   Before generating the WCF client class, make sure you set the EnableBizTalkCompatibilityMode binding property to false.

3. In the Solution Explorer, add reference to Microsoft.Adapters.Sql and Microsoft.ServiceModel.Channels.

4. Open the Program.cs file and create a client as described in the snippet below.

   
             TableOp_dbo_EmployeeClient client = new TableOp_dbo_EmployeeClient("SqlAdapterBinding_TableOp_dbo_Employee");  
   client.ClientCredentials.UserName.UserName = "<Enter user name here>";  
   client.ClientCredentials.UserName.Password = "<Enter password here>";  
   

   In this snippet, TableOp_dbo_EmployeeClient is the WCF client defined in SqlAdapterBindingClient.cs. This file is generated by the Add Adapter Service Reference Plug-in. SqlAdapterBinding_TableOp_dbo_Employee is the name of the client endpoint configuration and is defined in the app.config. This file is also generated by the Add Adapter Service Reference Plug-in and contains the binding properties and other configuration settings.

   Note

   In this snippet, you use the binding and endpoint address from the configuration file. You can also explicitly specify these values in your code. For more information on the different ways of specifying client binding, see Configure a Client Binding for the SQL Adapter.

5. Open the client as described in the snippet below:

   try  
   {  
      Console.WriteLine("Opening Client...");  
      client.Open();  
   }  
   catch (Exception ex)  
   {  
      Console.WriteLine("Exception: " + ex.Message);  
      throw;  
   }  
   

6. Invoke the Insert operation on the Employee table.

   long[] recordsInserted;  
   
   schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee[] insertRecord =  
   new schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee[1];  
   
   insertRecord[0] = new schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee();  
   insertRecord[0].Name = "John Smith";  
   insertRecord[0].Designation = "Manager";  
   insertRecord[0].Salary = 500000;  
   
   try  
   {  
      Console.WriteLine("Inserting new table entry...");  
      recordsInserted = client.Insert(insertRecord);  
   }  
   catch (Exception ex)  
   {  
      Console.WriteLine("Exception: " + ex.Message);  
      throw;  
   }  
   
   Console.WriteLine("Record inserted");  
   Console.WriteLine("Press any key to continue ...");  
   Console.ReadLine();  
   

   You can replace the preceding code snippet to perform Select, Update, or Delete operations as well. You can also append the code snippets to perform all operation in a single application. For code snippets on how to perform these operations.

7. Close the client as described in the snippet below:

   client.Close();  
   Console.WriteLine("Press any key to exit...");  
   Console.ReadLine();  
   

8. Build the project and then run it. The application inserts a record in the Employee table.

Select Operation

The following code shows a Select operation that targets the Employee table. The Select operation selects the last record inserted into the table. The returned records are written to the console.

schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee[] selectRecords;  
  
try  
{  
   Console.WriteLine("Selecting Row...");  
   selectRecords = client.Select("*", "where [Employee_ID] = (select IDENT_CURRENT('Employee'))");  
}  
  
catch (Exception ex)  
{  
   Console.WriteLine("Exception: " + ex.Message);  
   throw;  
}  
  
Console.WriteLine("The details of the newly added employee are:");  
Console.WriteLine("********************************************");  
for (int i = 0; i < selectRecords.Length; i++)  
{  
   Console.WriteLine("Employee ID        : " + selectRecords[i].Employee_ID);  
   Console.WriteLine("Employee Name      : " + selectRecords[i].Name);  
   Console.WriteLine("Employee Desigation: " + selectRecords[i].Designation);  
   Console.WriteLine();  
}  
Console.WriteLine("********************************************");  
Console.WriteLine("Press any key to continue ...");  
Console.ReadLine();  

Update Operation

The following code shows an Update operation that targets the Employee table.

int result;  
  
schemas.microsoft.com.Sql._2008._05.TableOp.dbo.Employee.RowPair updateRecordPair =  
   new schemas.microsoft.com.Sql._2008._05.TableOp.dbo.Employee.RowPair();  
  
schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee updateRecord =   
   new schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee();  
  
schemas.microsoft.com.Sql._2008._05.TableOp.dbo.Employee.RowPair[] updateArray =  
   new schemas.microsoft.com.Sql._2008._05.TableOp.dbo.Employee.RowPair[1];  
  
updateRecord = insertRecord[0];  
updateRecord.Name = "Jeff Smith";  
  
updateRecordPair.After = updateRecord;  
updateRecordPair.Before = selectRecords[0];  
  
updateArray[0] = updateRecordPair;  
  
try  
{  
   Console.WriteLine("Updating the database...");  
   result = client.Update(updateArray);  
}  
catch (Exception ex)  
{  
   Console.WriteLine("Exception: " + ex.Message);  
   throw;  
}  
  
Console.WriteLine("Updated Record for {0}", updateRecordPair.Before.Name);  
Console.WriteLine("The new name for the employee is {0}", updateRecordPair.After.Name);  
Console.WriteLine("Press any key to continue ...");  
Console.ReadLine();  

Delete Operation

The following code shows a Delete operation that targets the Employee table.

int deleteSuccess;  
  
schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee[] deleteRecords =  
   new schemas.microsoft.com.Sql._2008._05.Types.Tables.dbo.Employee[1];  
  
deleteRecords = client.Select("*", "where [Employee_ID] = (select IDENT_CURRENT('Employee'))");  
  
Console.WriteLine("Following employees will be deleted from the database:");  
for (int i = 0; i < deleteRecords.Length; i++)  
{  
   Console.WriteLine("Name: {0}", deleteRecords[i].Name);  
}  
Console.WriteLine("Press any key to begin deletion...");  
Console.ReadLine();  
  
try  
{  
   Console.WriteLine("Deleting employee record...");  
   deleteSuccess = client.Delete(deleteRecords);  
}  
  
catch (Exception ex)  
{  
   Console.WriteLine("Exception: " + ex.Message);  
   throw;  
}  

See Also

Develop applications using the WCF Service model