Retrieving and Modifying Data

In This Topic

Introduction
Requirements
General Information
Retrieving and Updating Data using Connected Model
Retrieving and Updating Data using Disconnected Model
Additional Information

Introduction

This tutorial describes how to use SqlCommand, SqlDataReader, and SqlDataTable components.

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.

Note that if you do not use design-time (specifically, if you do not place on a designer SqlConnection component from toolbox), you have to embed license information manually. This is described in the Licensing topic.

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.

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

Applicable only for projects targeting full .NET Framework.

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.

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. Please refer to our Using DataSet Wizard and Using DataSet Manager articles.

Additional Information