DataTableAdapter Design Technique Using DataSet

Introduction

Generally, a Dataset contains a collection of data tables. But we can do so many things with Dataset. So I would like to demonstrate a Data Adapter Design and Usage with a sample application using Dataset. In this topic, we will have a chance to look at Query designer and Query objects.

Before starting, we need to create a sample table in the database. Find the sample script here,

CREATE TABLE [dbo].[EMPINFO]  
(  
   [Id] INT NOT NULL PRIMARY KEY,   
   [EmpName] NVARCHAR(MAX) NULL,   
   [EmpAddress] NVARCHAR(MAX) NULL,   
   [EmpRemarks] NVARCHAR(MAX) NULL  
)  

Add a new item and choose DataSet,

Here you can add your data Tables. I added here a table, EMPINFO. You can add more than one table here and create queries.

Let's come into the page and write a code to insert data into the EMPINFO table with the help of an Adapter,

protected void Page_Load(object sender, EventArgs e)  
{  
    try  
    {  
        EmpDataSetTableAdapters.EMPINFOTableAdapter obj = new EmpDataSetTableAdapters.EMPINFOTableAdapter();  
        obj.Insert(1, "TestEmployee", "India", "none");  
        Response.Write("Record Created");  
    }  
    catch (Exception ex)  
    {  
        Response.Write(ex.Message.ToString());  
    }  
}  

Run and check it on the browser and your data table. The record has been inserted without writing any query or connection.

How to write a custom Query with the help of Dataset Template?

The following screen will help you to create a query for the table with the help of the query option and Query builder.

Right-click on your data table and choose Add, then click Query,

In the next step, you can find different command types like SQL statements and procedures. Choose SQL Statements for this time. For more information, please see the following screen.

This is an excellent time to look at Query builder. We can make queries with the help of a builder and find results.

Finally, give the function name 'CustomInsertQuery' for,

INSERT INTO EMPINFO  
(Id, EmpName, EmpAddress)  
VALUES (@Id,@EmpName,@EmpAddress)  

Here's the code snippet.

try  
{  
    EmpDataSetTableAdapters.EMPINFOTableAdapter obj = new EmpDataSetTableAdapters.EMPINFOTableAdapter();  
    obj.Insert(12, "TestEmployee", "India", "None");  
    obj.CustomInsertQuery(13, "WithoutRemarks", "India");  
    Response.Write("Record Created");  
}  
catch (Exception ex)  
{  
    Response.Write(ex.Message.ToString());  
}  

Summary

This article taught us about DataTableAdapter Design Technique using DataSet.