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,

  1. CREATE TABLE [dbo].[EMPINFO]  
  2. (  
  3.    [Id] INT NOT NULL PRIMARY KEY,   
  4.    [EmpName] NVARCHAR(MAXNULL,   
  5.    [EmpAddress] NVARCHAR(MAXNULL,   
  6.    [EmpRemarks] NVARCHAR(MAXNULL  
  7. )  
Add new item and choose DataSet,

Data SET

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

add your data Tables

Let’s come into the page and write a code to insert data into EMPINFO table with help of Adapter,
  1. protected void Page_Load(object sender, EventArgs e)  
  2. {  
  3.     try  
  4.     {  
  5.         EmpDataSetTableAdapters.EMPINFOTableAdapter obj = new EmpDataSetTableAdapters.EMPINFOTableAdapter();  
  6.         obj.Insert(1, "TestEmployee""India""none");  
  7.         Response.Write("Record Created");  
  8.     }  
  9.     catch (Exception ex)  
  10.     {  
  11.         Response.Write(ex.Message.ToString());  
  12.     }  
  13. }  
Run and check it on browser and your data table. Record has been inserted without writing any query and connection.

Record

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,

add 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.

connection

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

data table

Finally give function name as ‘CustomInsertQuery’ for,

Choose function name
  1. INSERT INTO EMPINFO  
  2. (Id, EmpName, EmpAddress)  
  3. VALUES (@Id,@EmpName,@EmpAddress)  
mployee info

Here's the code snippet:
  1. try  
  2. {  
  3.     EmpDataSetTableAdapters.EMPINFOTableAdapter obj = new EmpDataSetTableAdapters.EMPINFOTableAdapter();  
  4.     obj.Insert(12, "TestEmployee""India""None");  
  5.     obj.CustomInsertQuery(13, "WithoutRemarks""India");  
  6.     Response.Write("Record Created");  
  7. }  
  8. catch (Exception ex)  
  9. {  
  10.     Response.Write(ex.Message.ToString());  
  11. }  
run

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now