Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to update Database from DataSet in ASP.NET

Author: Alanna Kremer
Download Source Code : 1310_UpdateDatabaseFromDataSet.zip

You can use DataSet object to update Database tables in ASP.NET. This article will demonstrate how we can use DataSet to update a database table.

 

When we have to access data from Databases, DataSet and DataTable are the key objects. These are the disconnected objects and we can use it after closing connection to SQL Server. We can manipulate data in DataSet and DataTable and can update the database later to implement changes.

Let’s start our example. You can download source code.

  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
  4. Website > Add New Item > Web Form > Click Add
  5. Add code below in your Web Form

    <asp:Button ID="Button1" runat="server" Text="Update Database"
            onclick="Button1_Click" />
    <br />
    <asp:Label ID="Label1" runat="server" ></asp:Label>
     
  6. Open code behind file and include following namespace.
     
    C#
     
    using System.Data;
    using System.Data.SqlClient;
     
    VB.NET
     
    Imports System.Data
    Imports System.Data.SqlClient

     
  7. Now write code below in Button click event method in code behind file

    C#
     
    protected void Button1_Click(object sender, EventArgs e) {
        string sqlQuery = "Select * From Customers";
        string connString = "Data Source=Local;Initial Catalog=NORTHWND;Integrated Security=True";
        SqlConnection sqlConn= new SqlConnection(connString);
        SqlCommand command = new SqlCommand(sqlQuery, sqlConn);
        SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
        DataSet dataset = new DataSet();
        sqlAdapter.Fill(dataset, "Customers");
        DataTable datatable = new DataTable();
        datatable = dataset.Tables["Customers"];
           
        DataRow datarow = datatable.NewRow();
        datarow["CustomerID"] = "Any";
        datarow["CompanyName"] = "Any Company Name";
        datarow["ContactName"] = "Any Name";
        datarow["City"] = "San Francisco";
        datarow["Region"] = "CA";
        datarow["Country"] = "USA";
        datatable.Rows.Add(datarow);
     
        SqlCommandBuilder commandbuilder = new SqlCommandBuilder(sqlAdapter);
        sqlAdapter.Update(dataset, "Customers");
     
        Label1.Text = "Customers table updated successfully";
     
    }
     
    VB.NET
     
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
     
        Dim sqlQuery As String = "Select * From Customers"
        Dim connString As String = "Data Source=Local;Initial Catalog=NORTHWND;Integrated Security=True"
        Dim sqlConn As New SqlConnection(connString)
        Dim command As New SqlCommand(sqlQuery, sqlConn)
        Dim sqlAdapter As New SqlDataAdapter(command)
        Dim dataset As New DataSet()
        sqlAdapter.Fill(dataset, "Customers")
        Dim datatable As New DataTable()
        datatable = dataset.Tables("Customers")
     
        Dim datarow As DataRow = datatable.NewRow()
        datarow("CustomerID") = "Any"
        datarow("CompanyName") = "Any Company Name"
        datarow("ContactName") = "Any Name"
        datarow("City") = "San Francisco"
        datarow("Region") = "CA"
        datarow("Country") = "USA"
        datatable.Rows.Add(datarow)
     
        Dim commandbuilder As New SqlCommandBuilder(sqlAdapter)
        sqlAdapter.Update(dataset, "Customers")
     
        Label1.Text = "Customers table updated successfully"
     
    End Sub
     
    Here in above code, I have queried entire Customers table from NORTHWIND database. I have created a new instance of SqlConnection by passing connection string. You have to provide your connection string to connect to your SQL Server database. Then I have created a new instance of SqlCommand object and pass the query string and connection object to SqlCommand. A SqlDataAdapter object is created by passing SqlCommand object. Then I have created a new DataSet object and fill the DataSet by using Fill() method of SqlDataAdapter object. I have assigned the Customers table in new DataTable object from collection of “Tables” in DataSet object. Then I have created a new row object of this DataTable. I have set the values of individual Columns. I have ignored some Columns in Customers table because it can allow nulls. Then I have call the Add() method to add this row to table. At the end I have created a new instance of SqlCommandBuilder object by passing SqlDataAdapter object and call the Update() method of by passing DataSet object and table name.
     
  8. Press Ctrl+F5 to see website in browser. Click on button and see the result in SQL Server. Open Customers Table of NORTHWIND Database. You will see new row in the table.

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to do validation using jQuery in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net