Since the release of our first ADO.NET data providers in June, 2002, DataDirect Technologies has been fully committed to boosting operational performance in real-world environments such as Microsoft SQL Server.
The material provided here includes code samples that can reduce application development time by providing working C# source code showing typical database access tasks in ADO.NET, using the DataDirect Connect for ADO.NET SQL Server data provider.
To ensure that code examples work, you must create the sample tables. In addition, you must modify the connection strings in the examples to work in your environment. Finally, make sure you include the "using" directive for System.Data and DDTek.SQLServer in your project:
Many of the samples in this product brief use the emp and dept tables. You can create the tables using an ISQL script, or by using the data provider.
The following script can be run in ISQL. See the Microsoft SQL Server documentation for details.
The sample tables used in this Product Brief can be created with the data provider, as shown in the following code example:
The DataReader provides the fastest but least flexible way to retrieve data from the database. Data is returned as a read-only, forward-only stream of data that is returned one record at a time. If you need to retrieve many records rapidly, using a DataReader would require fewer resources than using a DataSet, which would need large amounts of memory to hold the results.
In the following code example, you execute a simple query on a Microsoft SQL Server database and read the results using a DataReader. This example uses the emp table.
The following code example uses the emp table to show how to use a local transaction:
The following code shows how to use a distributed transaction across two connections to two different Microsoft SQL Server servers. The example uses the emp table.
When you use distributed transactions, you must add System.EnterpriseServices to the Solution Reference list. In addition, the application must be strongly named.
To do this:
A CommandBuilder object can be used to generate the Insert, Update, and Delete statements for a DataAdapter.
The following code example uses the emp sample table with the CommandBuilder to update a DataSet:
When updating a row at the data source, the DataSet uses the SQL provided in UpdateCommand of the Data Adapter. The Update statement can use parameters that contain the unique identifier, such as the primary key, and the columns to be updated, as shown in the following example:
The parameterized query statements define the parameters that will be created. Refer to the DataDirect Connect for ADO.NET User's Guide and Reference for more information about using parameters with the SQL Server data provider.
The following code example shows how to provide an UpdateCommand to a DataAdapter for use in synchronizing changes made to a DataSet with the actual data on the SQL Server server, using data from the emp table. The example uses the Parameters.Add method to create the parameters for the Update statement, fills a DataSet, programmatically makes changes to the DataSet, then synchronizes the changes back to the database.
You call stored procedures using a Command object. When you issue a command on a stored procedure, you must set the CommandType of the Command object to StoredProcedure, or use the ODBC/JDBC escape syntax. For information on using the ODBC/JDBC escape syntax with the data provider, refer to the the DataDirect Connect for ADO.NET User's Guide.
The following code shows how to execute a stored procedure on a SQL Server database and read the results using a DataReader. The sample data is in the emp table.
First, execute the following code to create the stored procedure:
Now, use the following code example to execute the GetEmpSalary stored procedure:
You can use the ExecuteScalar method of the Command object to return a single value, such as a sum or a count, from the database. The ExecuteScalar method returns the value of the first column of the first row of the result set. If you know the result set has only one row and one column, you can use this method to speed up retrieval of the value.
The following code example retrieves the number of employees who make more than $50000. This example uses the emp table.
The data provider handles database server warnings through the InfoMessage delegates on the Connection objects.
The following example shows how to retrieve a warning generated by a Microsoft SQL Server server:
Add the following code to a method and call it:
DataDirect Technologies is committed to providing the fastest and most functional ADO.NET managed data providers so that you can develop a robust .NET solution with unrivaled performance.
These code examples, which provide working C# code for typical data access tasks, can help you to get started quickly and optimize your development when you use the DataDirect Connect for ADO.NET SQL Server data provider with your ADO.NET application.
DataDirect Technologies offers the following ADO.NET managed code data providers:
|DataDirect Connect for ADO.NET 3.0||DB2 data provider
Oracle data provider
Sybase data provider
SQL Server data provider
|DataDirect SequeLink for ADO.NET 5.5 data provider|