Code Examples - Microsoft SQL Server

Overview

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:

using System.Data;

using DDTek.SQLServer;

Sample Tables

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.

Creating the sample tables using an ISQL script

The following script can be run in ISQL. See the Microsoft SQL Server documentation for details.

CREATE TABLE emp (

empno INT PRIMARY KEY,

ename VARCHAR(10),

job VARCHAR(9),

mgr INT NULL,

hiredate DATETIME,

sal NUMERIC(7,2),

comm NUMERIC(7,2) NULL,

dept INT)

begin

insert into emp values

(1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4)

insert into emp values

(2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3)

insert into emp values

(3,'TAFT','SALES I',2,'01-02-1996',25000,500,3)

insert into emp values

(4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3)

insert into emp values

(5,'LINCOLN','TECH',6,'06-23-1994',22500,1400,4)

insert into emp values

(6,'GARFIELD','MANAGER',9,'05-01-1993',54000,NULL,4)

insert into emp values

(7,'POLK','TECH',6,'09-22-1997',25000,NULL,4)

insert into emp values

(8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2)

insert into emp values

(9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4)

insert into emp values

(10,'FILLMORE','MANAGER',9,'08-09-1994',56000,NULL,2)

insert into emp values

(11,'ADAMS','ENGINEER',10,'03-15-1996',34000,NULL,2)

insert into emp values

(12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4)

insert into emp values

(13,'MONROE','ENGINEER',10,'12-03-2000',30000,NULL,2)

insert into emp values

(14,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1)

end

CREATE TABLE dept (

deptno INT NOT NULL,

dname VARCHAR(14),

loc VARCHAR(13))

begin

insert into dept values (1,'ACCOUNTING','ST LOUIS')

insert into dept values (2,'RESEARCH','NEW YORK')

insert into dept values (3,'SALES','ATLANTA')

insert into dept values (4, 'OPERATIONS','SEATTLE')

end

Creating the sample tables using the data provider

The sample tables used in this Product Brief can be created with the data provider, as shown in the following code example:

SQLServerConnection Conn;

Conn = new SQLServerConnection("host=nc-star;port=1433;

User ID=test01;Password=test01; Database Name=Test");

try

{

Conn.Open();

}

catch (SQLServerException ex)

{

// Connection failed

Console.WriteLine(ex.Message);

return;

}

string[] DropTableSQL = {"drop table emp", "drop table dept"};

for (int x=0; x<=1; x++)

{

try

{

// Drop the tables, don't care if they don't exist

SQLServerCommand DBCmd = new SQLServerCommand(DropTableSQL[x], Conn);

DBCmd.ExecuteNonQuery();

}

catch (SQLServerException ex)

{

}

// Create the tables

string CreateEmpTableSQL = "CREATE TABLE emp

(empno INT PRIMARY KEY NOT NULL,"

+"ename VARCHAR(10) NOT NULL,"

+"job VARCHAR(9) NOT NULL,"

+"mgr INT,"

+"hiredate DATETIME NOT NULL,"

+"sal NUMERIC(7,2) NOT NULL,"

+"comm NUMERIC(7,2),"

+"dept INT NOT NULL)";

string CreateDeptTableSQL = "CREATE TABLE dept ("

+"deptno INT NOT NULL,"

+"dname VARCHAR(14),"

+"loc VARCHAR(13))";

try

{

SQLServerCommand DBCmd = new SQLServerCommand(CreateEmpTableSQL, Conn);

DBCmd.ExecuteNonQuery();

DBCmd.CommandText = CreateDeptTableSQL;

DBCmd.ExecuteNonQuery();

}

catch (Exception ex)

{

//Create tables failed

Console.WriteLine (ex.Message);

return;

}

// Now insert the records

string[] InsertEmpRecordsSQL = {

"insert into emp values

(1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4)",

"insert into emp values

(2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3)",

"insert into emp values

(3,'TAFT','SALES I',2,'01-02-1996',25000,500,3)",

"insert into emp values

(4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3)",

"insert into emp values

(5,'LINCOLN','TECH',6,'06-23-1994',22500,1400,4)",

"insert into emp values

(6,'GARFIELD','MANAGER',9,'05-01-1993',54000,NULL,4)",

"insert into emp values

(7,'POLK','TECH',6,'09-22-1997',25000,NULL,4)",

"insert into emp values

(8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2)",

"insert into emp values

(9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4)",

"insert into emp values

(10,'FILLMORE','MANAGER',9,'08-09-1994',56000, NULL,2)",

"insert into emp values

(11,'ADAMS','ENGINEER',10,'03-15-1996',34000, NULL,2)",

"insert into emp values

(12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4)",

"insert into emp values

(13,'MONROE','ENGINEER',10,'12-03-2000',30000,NULL,2)",

"insert into emp values

(14,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1)"};

string[] InsertDeptRecordsSQL = {

"insert into dept values (1,'ACCOUNTING','ST LOUIS')",

"insert into dept values (2,'RESEARCH','NEW YORK')",

"insert into dept values (3,'SALES','ATLANTA')",

"insert into dept values (4, 'OPERATIONS','SEATTLE')"};

// Insert dept table records first

for (int x = 0; x<InsertDeptRecordsSQL.Length; x++)

{

try

{

SQLServerCommand DBCmd =

new SQLServerCommand(InsertDeptRecordsSQL[x], Conn);

DBCmd.ExecuteNonQuery();

}

catch (Exception ex)

{

Console.WriteLine (ex.Message);

return;

}

// Now the emp table records

for (int x = 0; x<InsertEmpRecordsSQL.Length; x++)

{

try

{

SQLServerCommand DBCmd =

new SQLServerCommand(InsertEmpRecordsSQL[x], Conn);

DBCmd.ExecuteNonQuery();

}

catch (Exception ex)

{

Console.WriteLine (ex.Message);

return;

}

Console.WriteLine ("Tables created Successfully!");

// Close the connection

Conn.Close();

Retrieving Data Using a DataReader

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.

Open connection to SQL Server database

SQLServerConnection Conn;

try

{

Conn = new SQLServerConnection("host=nc-star;port=1433;

User ID=test01;Password=test01; Database Name=Test");

Conn.Open();

Console.WriteLine ("Connection successful!");

}

catch (Exception ex)

{

// Connection failed

Console.WriteLine(ex.Message);

return;

}

try

{

// Create a SQL command

string strSQL = "SELECT ename FROM emp WHERE sal>50000";

SQLServerCommand DBCmd = new SQLServerCommand(strSQL, Conn);

SQLServerDataReader myDataReader;

myDataReader = DBCmd.ExecuteReader();

while (myDataReader.Read())

{

Console.WriteLine("High salaries: " + myDataReader["ename"].ToString());

}

myDataReader.Close();

// Close the connection

Conn.Close();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

return;

}

Using a Local Transaction

The following code example uses the emp table to show how to use a local transaction:

SQLServerConnection Conn;

Conn = new SQLServerConnection("host=nc-star;port=1433; User ID=test01;

Password=test01;Database Name=Test");

try

{

Conn.Open();

Console.WriteLine ("Connection successful!");

}

catch (Exception ex)

{

// Connection failed

Console.WriteLine(ex.Message);

return;

}

SQLServerCommand DBCmd = new SQLServerCommand();

SQLServerTransaction DBTxn = null;

try

{

DBTxn = Conn.BeginTransaction();

// Set the Connection property of the Command object

DBCmd.Connection = Conn;

// Set the text of the Command to the INSERT statement

DBCmd.CommandText = "insert into emp VALUES

(16,'HAYES','ADMIN',6,'17-APR-2002',18000,NULL,4)";

// Set the transaction property of the Command object

DBCmd.Transaction = DBTxn;

// Execute the statement with ExecuteNonQuery, because we are not

// returning results

DBCmd.ExecuteNonQuery();

// Now commit the transaction

DBTxn.Commit();

// Display any exceptions

Console.WriteLine ("Transaction Committed!");

}

catch (Exception ex)

{

// Display any exceptions

Console.WriteLine (ex.Message);

// If anything failed after the connection was opened, roll back the

// transaction

if (DBTxn != null)

{

DBTxn.Rollback();

}

// Close the connection

Conn.Close();

Using the Distributed 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.

NOTES:

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:

Open a command window and go to the application directory. Then, run the following command:

sn –k SolutionName.snk
Delete the AssemblyInfo.cs file from the Solution.
Microsoft Distributed Transaction Coordinator must be running on all clients and servers.

using System;

using System.EnterpriseServices;

using System.Reflection;

using DDTek.SQLServer;

[assembly: ApplicationName("yourapplicationname")]

[assembly: AssemblyKeyFileAttribute(@"..\..\yourapplicationname.snk")]

namespace DistTransaction

{

/// <summary>

/// Summary description for Class1.

/// </summary>

public class Class1

{

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main(string[] args)

{

SQLServerConnection Conn1;

Conn1 = new SQLServerConnection("host=nc-star;port=1433;

User ID=test01;Password=test01;

Database Name=Test;Enlist=true");

SQLServerConnection Conn2;

Conn2 = new SQLServerConnection("host=nc-star;port=1433;

User ID=test07;Password= test07;

Database Name=test;Enlist=true");

try

{

DistributedTran myDistributedTran = new DistributedTran();

myDistributedTran.TestDistributedTransaction(Conn1, Conn2);

Console.WriteLine("Success!!");

}

catch (Exception e)

{

System.Console.WriteLine("Error returned: " + e.Message);

}

/// <summary>

/// To use distributed transactions in .NET, we need a ServicedComponent

/// derived class with transaction attribute declared as "Required".

/// </summary>

[Transaction(TransactionOption.Required) ]

public class DistributedTran : ServicedComponent

{

/// <summary>

/// This method executes two SQL statements.

/// If both are successful, both are committed by DTC after the

/// method finishes. However, if an exception is thrown, both will be

/// rolled back by DTC.

/// </summary>

[AutoComplete]

public void TestDistributedTransaction(SQLServerConnection Conn1,

SQLServerConnection Conn2)

{

// The following Insert statement goes to the first server, orca.

// This Insert statement does not produce any errors.

string DBCmdSql1 = "Insert into emp VALUES

(16,'HAYES','ADMIN',6,'17-NOV-2002',18000,

NULL,4)";

string DBCmdSql2 = "Delete from emp WHERE sal > 100000";

try

{

Conn1.Open();

Conn2.Open();

Console.WriteLine ("Connection successful!");

}

catch (Exception ex)

{

// Connection failed

Console.WriteLine(ex.Message);

return;

}

SQLServerCommand DBCmd1 = new SQLServerCommand(DBCmdSql1, Conn1);

SQLServerCommand DBCmd2 = new SQLServerCommand(DBCmdSql2, Conn2);

DBCmd1.ExecuteNonQuery();

DBCmd2.ExecuteNonQuery();

Conn1.Close();

Conn2.Close();

Console.WriteLine("Success!! ");

}

Using the CommandBuilder

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:

SQLServerConnection Conn;

Conn = new SQLServerConnection("host=nc-star; port=4100;User ID=test01;

Password=test01;Database Name=Test ");

try

{

Conn.Open();

Console.WriteLine ("Connection successful!");

}

catch (Exception ex)

{

// Connection failed

Console.WriteLine(ex.Message);

return;

}

SQLServerDataAdapter myDataAdapter = new SQLServerDataAdapter();

SQLServerCommand DBCmd = new SQLServerCommand("select * from emp",Conn);

myDataAdapter.SelectCommand = DBCmd;

// Set up the CommandBuilder

SQLServerCommandBuilder CommBuild =

new SQLServerCommandBuilder(myDataAdapter);

DataSet myDataSet = new DataSet();

try

{

myDataAdapter.Fill(myDataSet);

// Now change the salary of the first employee

DataRow myRow;

myRow = myDataSet.Tables["Table"].Rows[0];

myRow["sal"] = 95000;

// Tell the DataAdapter to resync with the SQL Server server.

// Without the CommandBuilder, this line would fail.

myDataAdapter.Update(myDataSet);

Console.WriteLine ("Update with CommandBuilder Successful!");

}

catch (Exception ex)

{

// Display any exceptions

Console.WriteLine (ex.Message);

}

// Close the connection

Conn.Close();

Updating Data in 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:

[C#]

string updateSQL As String = "UPDATE emp SET sal = ?, job = ? +

= WHERE empno = ?;

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.

SQLServerConnection Conn =

new SQLServerConnection("host=nc-star;port=4100;User ID=test01;

Password=test01;Database Name=Test");

try

{

string selectText = "select sal, job, empno from emp";

string updateText = "update emp set sal = ?, job = ? where empno = ?";

SQLServerDataAdapter adapter = new SQLServerDataAdapter(selectText, Conn);

SQLServerCommand updateCommand = new SQLServerCommand(updateText, Conn);

adapter.UpdateCommand = updateCommand;

updateCommand.Parameters.Add("@sal", SQLServerDbType.Int, 15, "SAL");

updateCommand.Parameters.Add("@job", SQLServerDbType.VarChar, 9, "JOB");

updateCommand.Parameters.Add("@empno", SQLServerDbType.Int, 15, "empno");

DataSet myDataSet = new DataSet("emp");

adapter.Fill(myDataSet, "emp");

// Give employee number 11 a promotion and a raise

DataRow changeRow = myDataSet.Tables["emp"].Rows[11];

changeRow["sal"] = "35000";

changeRow["job"] = "MANAGER";

// Send back to database

adapter.Update(myDataSet, "emp");

myDataSet.Dispose();

}

catch (Exception ex)

{

// Display any exceptions

Console.WriteLine (ex.Message);

}

Console.WriteLine("DataSet Updated Successfully!");

// Close the connection

Conn.Close();

Calling a Stored Procedure for SQL Server

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.

Creating the stored procedure

First, execute the following code to create the stored procedure:

// Open connection to SQL Server database

SQLServerConnection Conn;

Conn = new SQLServerConnection("host=nc-star;port=4100;User ID=test01;

Password=test01;Database Name=Test");

try

{

Conn.Open();

Console.WriteLine ("Connection successful!");

}

catch (Exception ex)

{

// Connection failed

Console.WriteLine(ex.Message);

return;

}

string spCreate = "CREATE PROCEDURE GetEmpSalary(@empno int,@sal

numeric(7,2) output)AS SELECT @sal = sal from emp where empno = @empno";

try

{

SQLServerCommand DBCmd=new SQLServerCommand(spCreate, Conn);

DBCmd.ExecuteNonQuery();

}

catch (Exception ex)

{

//Create procedure failed

Console.WriteLine (ex.Message);

return;

}

Console.WriteLine ("Procedure Created Successfully!");

Executing the stored procedure

Now, use the following code example to execute the GetEmpSalary stored procedure:

// Open connection to SQL Server database

SQLServerConnection Conn;

try

{

Conn = new SQLServerConnection("host=nc-star;port=4100;User ID=test01;

Password=test01;Database Name=Test");

Conn.Open();

Console.WriteLine ("Connection successful!");

}

catch (Exception ex)

{

// Connection failed

Console.WriteLine(ex.Message);

return;

}

// Make a command object for the stored procedure

// You must set the CommandType of the Command object

// to StoredProcedure

SQLServerCommand DBCmd = new SQLServerCommand("GetEmpSalary",Conn);

DBCmd.CommandType = CommandType.StoredProcedure;

// The stored procedure expects one input and one output parameter

// Define the parameters for the stored procedure

// We don't need to specify the direction of the parameter, since the

default is INPUT

DBCmd.Parameters.Add("@empno", SQLServerDbType.Int, 10).Value = 5;

// Output parameter

DBCmd.Parameters.Add("@sal", SQLServerDbType.Numeric, 10).Direction =

ParameterDirection.Output;

SQLServerDataReader myDataReader;

try

{

myDataReader = DBCmd.ExecuteReader();

myDataReader.Close();

}

catch (Exception ex)

{

// Display any exceptions

Console.WriteLine (ex.Message);

}

Console.WriteLine("Procedure Executed Successfully!");

// Close the connection

Conn.Close();

Retrieving a Scalar Value

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.

// Open connection to SQL Server database

SQLServerConnection Conn;

Conn = new SQLServerConnection("host=nc-star;port=4100;User ID=test01;

Password=test01;Database Name=Test");

try

{

Conn.Open();

Console.WriteLine ("Connection successful!");

}

catch (Exception ex)

{

// Connection failed

Console.WriteLine(ex.Message);

return;

}

// Make a command object

SQLServerCommand salCmd = new SQLServerCommand("select count(sal) from emp

where sal>50000",Conn);

try

{

int count = (int)salCmd.ExecuteScalar();

Console.WriteLine("Count of Salaries >$50,000 : "

+ Convert.ToString(count));

}

catch (Exception ex)

{

// Display any exceptions

Console.WriteLine(ex.Message);

}

// Close the connection

Conn.Close();

Retrieving Warning Information

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:

// Define an event handler

public void myHandler(object sender, SQLServerInfoMessageEventArgs e)

{

// Display any warnings

Console.WriteLine ("Warning Returned: " + e.Message);

Add the following code to a method and call it:

// Define an event handler

public void myHandler(object sender, SQLServerInfoMessageEventArgs e)

{

// Display any warnings

Console.WriteLine ("Warning Returned: " + e.Message);

}

Add the following code to a method and call it:

SQLServerConnection Conn;

Conn = new SQLServerConnection("host=nc-star;port=4100;User ID=test01;

Password=test01;Database Name=Test");

SQLServerCommand DBCmd = new SQLServerCommand

("print 'This is a Warning.'",Conn);

SQLServerDataReader myDataReader;

try

{

Conn.InfoMessage += new SQLServerInfoMessageEventHandler(myHandler);

Conn.Open();

myDataReader = DBCmd.ExecuteReader();

// This will throw a SQLServerInfoMessageEvent as the print

// statement generates a warning.

}

catch (Exception ex)

{

// Display any exceptions in a messagebox

MessageBox.Show (ex.Message);

}

// Close the connection

Conn.Close();

Robust Applications with Connect for ADO.NET Data Providers

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

DataDirect
ODBC connectors

Real-Time Access to XML Data

DataDirect
ODBC Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers

OpenEdge

Telerik

Kendo UI

Test Studio

NativeScript

Rollbase

Corticon

Telerik Platform

Kinvey

Code Examples - Microsoft SQL Server

Overview

Sample Tables

Creating the sample tables using an ISQL script

Creating the sample tables using the data provider

Retrieving Data Using a DataReader

Using a Local Transaction

Using the Distributed Transaction

Using the CommandBuilder

Updating Data in a DataSet

Calling a Stored Procedure for SQL Server

Creating the stored procedure

Executing the stored procedure

Retrieving a Scalar Value

Retrieving Warning Information

Robust Applications with Connect for ADO.NET Data Providers