C# Tutorial

C# String

C# Array

C# Flow Control

C# Class and Object

C# Inheritance

C# Interface

C# Collection

C# Generic

C# File I/O

C# Delegate and Event

C# Exception

C# Process and Thread

C# ADO.NET Database Operations

C# DataSet: Update Database

In this tutorial, we'll demonstrate how to use ADO.NET DataSet and SqlDataAdapter to update a SQL Server database. We'll use a simple example of an Employee table in a database.

  • Set up the database

Create a new database and a table called 'Employees':

CREATE DATABASE EmployeeDB;

USE EmployeeDB;

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

Insert some sample data into the 'Employees' table:

INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Doe');
INSERT INTO Employees (FirstName, LastName) VALUES ('Jane', 'Smith');
  • Create a C# Console Application

Create a new C# Console Application project in Visual Studio and add the following namespaces:

using System;
using System.Data;
using System.Data.SqlClient;
  • Set up the connection string

Create a connection string with the necessary credentials to connect to your SQL Server instance:

string connectionString = "Server=localhost;Database=EmployeeDB;User Id=myUser;Password=myPassword;";
  • Load data from the database into a DataSet

Create a method called LoadEmployees to load the data from the 'Employees' table into a DataSet:

static DataSet LoadEmployees(SqlConnection connection)
{
    string selectQuery = "SELECT * FROM Employees";
    SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
    DataSet dataSet = new DataSet();
    
    connection.Open();
    adapter.Fill(dataSet, "Employees");
    connection.Close();
    
    return dataSet;
}
  • Update the database using DataSet and SqlDataAdapter

Create a method called UpdateEmployeeName that updates an employee's name in the DataSet and then updates the database:

static void UpdateEmployeeName(SqlConnection connection, DataSet dataSet, int employeeId, string newFirstName, string newLastName)
{
    DataRow[] foundRows = dataSet.Tables["Employees"].Select($"EmployeeID = {employeeId}");

    if (foundRows.Length > 0)
    {
        DataRow row = foundRows[0];
        row["FirstName"] = newFirstName;
        row["LastName"] = newLastName;
    }

    SqlDataAdapter adapter = new SqlDataAdapter();
    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
    adapter.UpdateCommand = builder.GetUpdateCommand();
    adapter.InsertCommand = builder.GetInsertCommand();
    adapter.DeleteCommand = builder.GetDeleteCommand();

    adapter.Update(dataSet.Tables["Employees"]);
}
  • Test updating the database

In the Main method, load the data from the database into a DataSet, update the data, and then update the database:

static void Main(string[] args)
{
    SqlConnection connection = new SqlConnection(connectionString);
    DataSet dataSet = LoadEmployees(connection);

    UpdateEmployeeName(connection, dataSet, 1, "Johnathan", "Doe");

    Console.WriteLine("Database updated successfully.");
    Console.ReadLine();
}

When you run the Console Application, you should see the message "Database updated successfully." If you query the 'Employees' table in the database, you'll see that the employee's name has been updated.

This tutorial shows you how to use ADO.NET DataSet and SqlDataAdapter to update a SQL Server database in C#. DataSet provides a flexible, in-memory representation of data that can be used to perform various operations on data without interacting directly with the database. SqlDataAdapter acts as a bridge between the DataSet and the database, enabling you to load data, make changes, and update the database.

  1. C# DataSet update database example:

    Using a DataSet to update a database in C# involves loading data from the database into the DataSet, making changes, and then updating those changes back to the database. Below is a simple example using SQL Server:

    using System;
using System.Data;
using System.Data.SqlClient;

class Program {
    static void Main() {
        // Connection string to the SQL Server database
        string connectionString = "Data Source=(local);Initial Catalog=YourDatabase;Integrated Security=True";

        // SQL query to select data
        string selectQuery = "SELECT * FROM YourTable";

        // Create a DataSet and fill it with data from the database
        DataSet dataSet = new DataSet();
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            adapter.Fill(dataSet, "YourTable");
        }

        // Modify the data in the DataSet
        DataTable dataTable = dataSet.Tables["YourTable"];
        foreach (DataRow row in dataTable.Rows) {
            // Modify the data as needed
            row["ColumnName"] = "NewValue";
        }

        // Update the changes back to the database
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

            // Update the database with changes from the DataSet
            adapter.Update(dataSet, "YourTable");
        }

        Console.WriteLine("Database updated successfully.");
    }
}

  2. Updating database with DataSet in C#:

    This example demonstrates updating a database using a DataSet in C#:

    using System;
using System.Data;
using System.Data.SqlClient;

class Program {
    static void Main() {
        // Connection string to the SQL Server database
        string connectionString = "Data Source=(local);Initial Catalog=YourDatabase;Integrated Security=True";

        // SQL query to select data
        string selectQuery = "SELECT * FROM YourTable";

        // Create a DataSet and fill it with data from the database
        DataSet dataSet = new DataSet();
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            adapter.Fill(dataSet, "YourTable");
        }

        // Modify the data in the DataSet
        DataTable dataTable = dataSet.Tables["YourTable"];
        foreach (DataRow row in dataTable.Rows) {
            // Modify the data as needed
            row["ColumnName"] = "NewValue";
        }

        // Update the changes back to the database
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

            // Update the database with changes from the DataSet
            adapter.Update(dataSet, "YourTable");
        }

        Console.WriteLine("Database updated successfully.");
    }
}

  3. How to use DataSet to update database records in C#:

    The process involves loading data into a DataSet, modifying the data, and then updating the database with the changes. Here's an example:

    using System;
using System.Data;
using System.Data.SqlClient;

class Program {
    static void Main() {
        // Connection string to the SQL Server database
        string connectionString = "Data Source=(local);Initial Catalog=YourDatabase;Integrated Security=True";

        // SQL query to select data
        string selectQuery = "SELECT * FROM YourTable";

        // Create a DataSet and fill it with data from the database
        DataSet dataSet = new DataSet();
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            adapter.Fill(dataSet, "YourTable");
        }

        // Modify the data in the DataSet
        DataTable dataTable = dataSet.Tables["YourTable"];
        foreach (DataRow row in dataTable.Rows) {
            // Modify the data as needed
            row["ColumnName"] = "NewValue";
        }

        // Update the changes back to the database
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

            // Update the database with changes from the DataSet
            adapter.Update(dataSet, "YourTable");
        }

        Console.WriteLine("Database updated successfully.");
    }
}

  4. Updating SQL Server database using C# DataSet:

    using System;
using System.Data;
using System.Data.SqlClient;

class Program {
    static void Main() {
        // Connection string to the SQL Server database
        string connectionString = "Data Source=(local);Initial Catalog=YourDatabase;Integrated Security=True";

        // SQL query to select data
        string selectQuery = "SELECT * FROM YourTable";

        // Create a DataSet and fill it with data from the database
        DataSet dataSet = new DataSet();
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            adapter.Fill(dataSet, "YourTable");
        }

        // Modify the data in the DataSet
        DataTable dataTable = dataSet.Tables["YourTable"];
        foreach (DataRow row in dataTable.Rows) {
            // Modify the data as needed
            row["ColumnName"] = "NewValue";
        }

        // Update the changes back to the database
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

            // Update the database with changes from the DataSet
            adapter.Update(dataSet, "YourTable");
        }

        Console.WriteLine("Database updated successfully.");
    }
}

  5. C# SqlDataAdapter and DataSet update database:

    using System;
using System.Data;
using System.Data.SqlClient;

class Program {
    static void Main() {
        // Connection string to the SQL Server database
        string connectionString = "Data Source=(local);Initial Catalog=YourDatabase;Integrated Security=True";

        // SQL query to select data
        string selectQuery = "SELECT * FROM YourTable";

        // Create a DataSet and fill it with data from the database
        DataSet dataSet = new DataSet();
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            adapter.Fill(dataSet, "YourTable");
        }

        // Modify the data in the DataSet
        DataTable dataTable = dataSet.Tables["YourTable"];
        foreach (DataRow row in dataTable.Rows) {
            // Modify the data as needed
            row["ColumnName"] = "NewValue";
        }

        // Update the changes back to the database
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

            // Update the database with changes from the DataSet
            adapter.Update(dataSet, "YourTable");
        }

        Console.WriteLine("Database updated successfully.");
    }
}

  6. Updating multiple tables in database with C# DataSet:

    To update multiple tables in a database using a DataSet, you need to include the tables in the DataSet and handle the updates for each table. Below is an example:

    using System;
using System.Data;
using System.Data.SqlClient;

class Program {
    static void Main() {
        // Connection string to the SQL Server database
        string connectionString = "Data Source=(local);Initial Catalog=YourDatabase;Integrated Security=True";

        // SQL query to select data for each table
        string selectQueryTable1 = "SELECT * FROM Table1";
        string selectQueryTable2 = "SELECT * FROM Table2";

        // Create a DataSet and fill it with data from the database
        DataSet dataSet = new DataSet();
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapterTable1 = new SqlDataAdapter(selectQueryTable1, connection);
            SqlDataAdapter adapterTable2 = new SqlDataAdapter(selectQueryTable2, connection);

            adapterTable1.Fill(dataSet, "Table1");
            adapterTable2.Fill(dataSet, "Table2");
        }

        // Modify the data in the DataSet for Table1
        DataTable dataTableTable1 = dataSet.Tables["Table1"];
        foreach (DataRow row in dataTableTable1.Rows) {
            // Modify the data as needed
            row["ColumnName"] = "NewValue";
        }

        // Modify the data in the DataSet for Table2
        DataTable dataTableTable2 = dataSet.Tables["Table2"];
        foreach (DataRow row in dataTableTable2.Rows) {
            // Modify the data as needed
            row["ColumnName"] = "NewValue";
        }

        // Update the changes back to the database for Table1
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapterTable1 = new SqlDataAdapter(selectQueryTable1, connection);
            SqlCommandBuilder builderTable1 = new SqlCommandBuilder(adapterTable1);

            // Update the database with changes from the DataSet for Table1
            adapterTable1.Update(dataSet, "Table1");
        }

        // Update the changes back to the database for Table2
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapterTable2 = new SqlDataAdapter(selectQueryTable2, connection);
            SqlCommandBuilder builderTable2 = new SqlCommandBuilder(adapterTable2);

            // Update the database with changes from the DataSet for Table2
            adapterTable2.Update(dataSet, "Table2");
        }

        Console.WriteLine("Database updated successfully.");
    }
}

  7. Updating records with DataTable and DataSet in C#:

    using System;
using System.Data;
using System.Data.SqlClient;

class Program {
    static void Main() {
        // Connection string to the SQL Server database
        string connectionString = "Data Source=(local);Initial Catalog=YourDatabase;Integrated Security=True";

        // SQL query to select data
        string selectQuery = "SELECT * FROM YourTable";

        // Create a DataSet and fill it with data from the database
        DataSet dataSet = new DataSet();
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            adapter.Fill(dataSet, "YourTable");
        }

        // Modify the data in the DataSet
        DataTable dataTable = dataSet.Tables["YourTable"];
        foreach (DataRow row in dataTable.Rows) {
            // Modify the data as needed
            row["ColumnName"] = "NewValue";
        }

        // Update the changes back to the database
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);

            // Specify the Insert, Update, and Delete commands manually if needed
            adapter.InsertCommand = new SqlCommand("INSERT INTO YourTable (ColumnName) VALUES (@Value)", connection);
            adapter.UpdateCommand = new SqlCommand("UPDATE YourTable SET ColumnName = @Value WHERE ID = @ID", connection);
            adapter.DeleteCommand = new SqlCommand("DELETE FROM YourTable WHERE ID = @ID", connection);

            // Update the database with changes from the DataSet
            adapter.Update(dataSet, "YourTable");
        }

        Console.WriteLine("Database updated successfully.");
    }
}

  8. Updating Access database with C# DataSet:

    using System;
using System.Data;
using System.Data.OleDb;

class Program {
    static void Main() {
        // Connection string to the Access database
        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YourDatabase.accdb;Persist Security Info=False;";

        // SQL query to select data
        string selectQuery = "SELECT * FROM YourTable";

        // Create a DataSet and fill it with data from the database
        DataSet dataSet = new DataSet();
        using (OleDbConnection connection = new OleDbConnection(connectionString)) {
            OleDbDataAdapter adapter = new OleDbDataAdapter(selectQuery, connection);
            adapter.Fill(dataSet, "YourTable");
        }

        // Modify the data in the DataSet
        DataTable dataTable = dataSet.Tables["YourTable"];
        foreach (DataRow row in dataTable.Rows) {
            // Modify the data as needed
            row["ColumnName"] = "NewValue";
        }

        // Update the changes back to the database
        using (OleDbConnection connection = new OleDbConnection(connectionString)) {
            OleDbDataAdapter adapter = new OleDbDataAdapter(selectQuery, connection);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

            // Update the database with changes from the DataSet
            adapter.Update(dataSet, "YourTable");
        }

        Console.WriteLine("Database updated successfully.");
    }
}

  9. Committing changes to database using C# DataSet:

    using System;
using System.Data;
using System.Data.SqlClient;

class Program {
    static void Main() {
        // Connection string to the SQL Server database
        string connectionString = "Data Source=(local);Initial Catalog=YourDatabase;Integrated Security=True";

        // SQL query to select data
        string selectQuery = "SELECT * FROM YourTable";

        // Create a DataSet and fill it with data from the database
        DataSet dataSet = new DataSet();
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            adapter.Fill(dataSet, "YourTable");
        }

        // Modify the data in the DataSet
        DataTable dataTable = dataSet.Tables["YourTable"];
        foreach (DataRow row in dataTable.Rows) {
            // Modify the data as needed
            row["ColumnName"] = "NewValue";
        }

        // Update the changes back to the database
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            connection.Open();
            SqlTransaction transaction = connection.BeginTransaction();

            try {
                SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

                // Set the transaction for the adapter
                adapter.SelectCommand.Transaction = transaction;
                adapter.InsertCommand.Transaction = transaction;
                adapter.UpdateCommand.Transaction = transaction;
                adapter.DeleteCommand.Transaction = transaction;

                // Update the database with changes from the DataSet
                adapter.Update(dataSet, "YourTable");

                // Commit the transaction
                transaction.Commit();

                Console.WriteLine("Changes committed successfully.");
            } catch (Exception ex) {
                // Rollback the transaction in case of an exception
                transaction.Rollback();
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}
Copyright © devanddep.com | All Rights Reserved