Retrieving and Modifying Data using dotConnect for SQL Server

Retrieving and Modifying Data Introducing

This tutorial describes how to use SqlCommand, SqlDataReader, and SqlDataTable components using dotConnect for SQL Server.

Requirements

This walkthrough supposes that you know how to connect to server, how to create the necessary objects on the server, and how to insert the data to the created tables.

General Information

As we know, an original function of any database application is establishing a connection to a data source and working with data contained in it. The .NET Framework data providers of ADO.NET serve as a bridge between an application and a data source, and allow you to execute commands as well as to retrieve data by using a DataReader or a DataAdapter. Updating data involves using the Command and DataAdapter objects; and it may also involve using transactions.

Let's make some classification to get better understanding of ADO.NET model. There are two approaches to work with data: connected and disconnected models. You can use classes of the connected model for establishing connection and setting transactions, for fetching data and updating datasources. These classes interoperate with database directly: SqlProviderFactory, SqlConnection, SqlTransaction, SqlDataAdapter, SqlCommand, SqlParameter, and SqlDataReader.

The objects, which represent the disconnected model of ADO.NET, don't interoperate with datasource immediately. These classes provide the ability to work off-line against your data storage: DataSet, DataTable, DataColumn, DataRow, Constraints, DataRelation, DataView, and DataRowView.

We will use in our samples the classes from both models.

The goal of this tutorial is to retrieve and update the data from the table dept (the appropriate DDL/DML script is located at \Program Files\Devart\dotConnect\SQL Server\Samples\tables.sql - the default path of dotConnect for SQL Server installation).

Retrieving and Updating Data using the Connected Model

In this sample we are using SqlCommand and SqlDataReader to retrieve and manipulate data. For more information, refer to the description of these classes in our documentation.

[C#]

using Devart.Data.SqlServer;
...
class Program
{
void PrintDept(SqlConnection connection)
{
SqlCommand command = connection.CreateCommand();
command.CommandText = "select * from dept";
// Call the Close method when you are finished using the SqlDataReader
// to use the associated SqlConnection for any other purpose.
// Or put the reader in the using block to call Close implicitly.
using (SqlDataReader reader = command.ExecuteReader())
{
// printing the column names
for (int i = 0; i < reader.FieldCount; i++)
Console.Write(reader.GetName(i).ToString() + "\t");
Console.Write(Environment.NewLine);
// Always call Read before accesing data
while (reader.Read())
{
// printing the table content
for (int i = 0; i < reader.FieldCount; i++)
Console.Write(reader.GetValue(i).ToString() + "\t");
Console.Write(Environment.NewLine);
}
}
}
void ModifyDept(SqlConnection connection)
{
SqlCommand command = connection.CreateCommand();
command.CommandText = "UPDATE DEPT SET LOC='VEGAS' WHERE DEPTNO > 20";
// return value of ExecuteNonQuery (i) is the number of rows affected by the command
int i = command.ExecuteNonQuery();
Console.WriteLine(Environment.NewLine + "Rows in DEPT updated: {0}", i + Environment.NewLine);
}
static void Main(string[] args)
{
using (SqlConnection conn
= new SqlConnection("User Id=sa;Server=localhost;Initial Catalog=Test"))
{
try
{
conn.Open();
Program program = new Program();
// printing out the Dept table to console
program.PrintDept(conn);
// updating records in Dept
program.ModifyDept(conn);
// printing out the Dept table to console
program.PrintDept(conn);
}
catch (SqlException ex)
{
Console.WriteLine("Exception occurs: {0}", ex.Message);
}
finally
{
Console.ReadLine();
}
}
}
}

[Visual Basic]

Imports Devart.Data.SqlServer
...
Module Module1
Sub PrintDept(ByVal connection As SqlConnection)
Dim command As SqlCommand = connection.CreateCommand()
command.CommandText = "select * from dept"
' Call the Close method when you are finished using the SqlDataReader
' to use the associated SqlConnection for any other purpose.
' Or put the reader in the using block to call Close implicitly.
Using reader As SqlDataReader = command.ExecuteReader()
' printing the column names
For i As Integer = 0 To reader.FieldCount - 1
Console.Write(reader.GetName(i).ToString() & VbCrlf)
Next i
Console.Write(Environment.NewLine)
' Always call Read before accesing data
While reader.Read()
' printing the table content
For i As Integer = 0 To reader.FieldCount - 1
Console.Write(reader.GetValue(i).ToString() & VbCrlf)
Next
Console.Write(Environment.NewLine)
End While
End Using
End Sub

Sub ModifyDept(ByVal connection As SqlConnection)
Dim command As SqlCommand = connection.CreateCommand()
command.CommandText = "UPDATE DEPT SET LOC='VEGAS' WHERE DEPTNO > 20"
' return value of ExecuteNonQuery (i) is the number of rows affected by the command
Dim i As Integer = command.ExecuteNonQuery()
Console.WriteLine(Environment.NewLine & "Rows in DEPT updated: {0}", i & Environment.NewLine)
End Sub

Sub Main()
Using conn _
As New SqlConnection("User Id=sa;Server=localhost;Initial Catalog=Test")
Try
conn.Open()
' printing out the Dept table to console
Module1.PrintDept(conn)
' updating records in Dept
Module1.ModifyDept(conn)
' printing out the Dept table to console
Module1.PrintDept(conn)
Catch ex As SqlException
Console.WriteLine("Exception occurs: {0}", ex.Message)
Finally
Console.ReadLine()
End Try
End Using
End Sub
End Module

Retrieving and Updating Data using the Disconnected Model

A traditional way for working with DataTable and DataSet assumes consecutive creating and initializing Connection, Command, DataAdapter, and CommandBuilder objects. Devart SqlDataTable and SqlDataSet have advanced features that make work with data easier. Even more, with our components you can retrieve and manipulate data in design time. For more information, refer to the descriptions of these classes.

Here is a small sample that demonstrates usage of SqlDataTable.

[C#]

public void UseDataTable()
{
SqlDataTable myDataTable
= new SqlDataTable("SELECT * FROM Dept", "User Id=sa;Server=localhost;Initial Catalog=Test");
try
{
// FetchAll=true means to retrieve data from server entirely when DataTable is opened.
// By default, FetchAll is set to false - only minimal quantity of rows is requested at once,
// which leads to better initial response time and less network traffic.
myDataTable.FetchAll = true;
// populating DataTable with data from data source
myDataTable.Active = true;
// modifying the third record
myDataTable.Rows[3]["DName"] = "Researches";
// Update method executes the appropriate commands (delete, insert, or update) in the data source.
Console.WriteLine(myDataTable.Update() + " rows updated.");
// printing the DataTable content
foreach (DataRow myRow in myDataTable.Rows)
{
foreach (DataColumn myCol in myDataTable.Columns)
{
Console.Write(myRow[myCol] + "\t");
}
Console.WriteLine();
}
}
finally
{
//Active=false does not clear the data, but frees the resources allocated on the server, if any.
myDataTable.Active = false;
}
}

[Visual Basic]

Public Sub UseDataTable()
Dim myDataTable As SqlDataTable _
As New SqlDataTable("SELECT * FROM Dept", "User Id=sa;Server=localhost;Initial Catalog=Test")
Try
' FetchAll=true means to retrieve data from server entirely when DataTable is opened.
' By default, FetchAll is set to false - only minimal quantity of rows is requested at once,
' which leads to better initial response time and less network traffic.
myDataTable.FetchAll = True
' populating DataTable with data from data source
myDataTable.Active = True
' modifying the third record
myDataTable.Rows(3)("DName") = "Researches"
' Update method executes the appropriate commands (delete, insert, or update) in the data source.
Console.WriteLine(myDataTable.Update() & " rows updated.")
Dim myRow As DataRow
Dim myCol As DataColumn
' printing the DataTable content
For Each myRow In myDataTable.Rows
For Each myCol In myDataTable.Columns
Console.Write(myRow(myCol) & VbCrlf)
Next myCol
Console.WriteLine()
Next myRow
Finally
' Active=false does not clear the data, but frees the resources allocated on the server, if any.
myDataTable.Active = False
End Try
End Sub

SqlDataSet can be easily created with Devart DataSet Wizard and visually managed with Devart DataSet Manager. 

Additional Information

This tutorial describes only basic ways of working with data. Besides, you can take advantage of using stored procedures and typed datasets.