DataTableAdapter Design Technique Using DataSet

Generally a Dataset contains 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 sample application using Dataset. In this topic we will have a chance to look on Query designer and Query objects.

Before going to start we need to create a sample table in 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 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 EMPINFO table with help of 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 browser and your data table. Record has been inserted without writing any query and connection.



How to write a custom Query with help of Dataset Template

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

Right click to your data table and choose Add, then click Query,



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



This is the good time to look on Query builder. We can make queries with help of builder and find results.



Finally give function name as ‘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());
}