Post
Ask Question

Strongly Typed Dataset in ASP.Net

The main objective is to activate Intellisense in the dataset so that there are no errors while typing the column names and table names of SQL Server table and display the data in the GridView Control.

Creating SQL Server Database and Table

The following shows how to create the new table:

  1. CREATE TABLE [dbo].[Employee] (  
  2.    [Emp_ID] INT IDENTITY (1, 1) NOT NULL,  
  3.    [Name] NVARCHAR (40) NULL,  
  4.    [Designation] NVARCHAR (100) NULL,  
  5.    [Salary] INT NULL,  
  6.    PRIMARY KEY CLUSTERED ([Emp_ID] ASC)  
  7. );  
Add Some Sample Data to it using the following Query

The following shows how to add sample data to the preceding table:
  1. INSERT INTO [dbo].[Employee]  
  2. ([Name]  
  3.    ,[Designation]  
  4.    ,[Salary])  
  5.    VALUES  
  6.    (<Name, nvarchar(40),>  
  7.    ,<Designation, nvarchar(100),>  
  8.    ,<Salary, int,>)  
  9. GO  

Step 1

Create an Empty ASP.NET web application and add a Webform to it naming it Demo.aspx and drag a GridView control to it.

Gridview

Step 2

Go to Add New Item in the Solution Explorer and add a dataset there naming it Employee.xsd.

data set

Step 3

Go to View -> Server Explorer and add a data connection to your database table and drag the table onto the XSD file.

XSD file

Step 4

Go to the Code Behind file of the Webform and paste the following code there.

Demo.aspx

  1. protected void Page_Load(object sender, EventArgs e)   
  2. {  
  3.     if (!IsPostBack)   
  4.     {  
  5.         EmployeeTableAdapters.EmployeeTableAdapter DA = new EmployeeTableAdapters.EmployeeTableAdapter();  
  6.   
  7.         Employee.EmployeeDataTable employeeDataTable = new Employee.EmployeeDataTable();  
  8.         DA.Fill(employeeDataTable);  
  9.   
  10.         Session["DATATABLE"] = employeeDataTable;  
  11.   
  12.         GridView1.DataSource = from emp in employeeDataTable select new {  
  13.             emp.Emp_ID, emp.Name, emp.Designation, emp.Salary  
  14.         };  
  15.         GridView1.DataBind();  
  16.     }  
  17.   
  18. }  
Explanation of preceding code: 
  1. If it is the get request of the page or simply if it is not a Postback request, then the compiler will execute the code.
  2. When we add a table to the dataset like we did in Step 3, the classes are generated automatically based on the columns present in the table.
  3. So, here EmployeeTableAdapter is a class generated by the dataset from Step 3 and simply we made the instance of it named "DA".
  4. Now, we need to retrieve that table of that DataAdapter, so we used the Dataset name followed by the name of the DataTable. Don't worry, all the classes will be available automatically in the Intellisense of Visual Studio and we also created its instance named "employeeDataTable".
  5. We filled the data in the DataTable from the DataAdapter just like we do in our simple ADO.NET Code.
  6. We are saving this DataTable in a Session Variable so that we can use it in our Next Tutorial that I will post soon.
  7. Now we have used some LINQ Code to fetch the data from the DataTable.
  8. "emp" is a new variable used to fetch the properties of the DataTable. When we write the name of this variable and press (.), the column name will appear automatically. This was our objective.
  9. Finally we bound the data fetched to the GridView.
  10. Save all the changes.

Step 5

Press the F5 key to run the project and you will see how easy it is to fetch data using a Strongly Typed Dataset without making any mistakes.

I hope you like this tutorial.

Please refer to my blog for more tutorials on ASP.NET and other programming Languages. Also like my FB Page.


Similar Articles