Monday, April 16, 2007

Read Excel with C# ADO.NET

Today I got a customer requirement where they had requirement to display data from Excel to their Web Site. I had done this with other earlier versions (ASP, VB), but never tried with .NET.

I thought this to be very difficult and might took enough time to fetch and display the data on site. This can be done easily through ADO.NET in very simpler way by manipulating the ConnectionString.

Below is the code snippet:


Sunday, April 8, 2007

Read XML in Dataset with C#

Instead of using looong approach to open, read and parse XML, I prefer reading XML with Dataset and process as required. Reading through Dataset will load all data into DataTable and DataRow and can be parsed as reqd.

Using the standard way to read XML (see other articles in blog for reading XML with recommended method) might be good, but remembering long and many lines of code to read and parse is again tedious.

Below is the code snippet:


Monday, January 8, 2007

Create Typed Dataset using C#

In this article, i will provide complete overview of Dataset, difference between "Typed Dataset" and "Untyped Dataset", advantages/disadvantages of both and one example to how to create Typed Dataset with Visual Studio 2005 with literally no code to write...

Dataset is one of the new technology in ASP.NET and it maps the entire database in iteself which means it can have all the Tables, Columns, Relationship, Constraints etc. Having all these in Dataset, it makes itself a disconnected database in which you can make changes and later you can update against actual database. In other words we can say that Dataset is a mirror copy tables and being disconnected in nature it makes insert/update/delete operations much faster.
I assume you are aware of other ADO.NET components such as Connection, Command, DataAdapter etc..


  • Typed Datasets are Tightly coupled datased which provides Type-Safe operation where fields of tables are mapped as property.
  • Uptyped dataset are one which we create programatically and its loosely coupled.
  • Typed: TypedDataSet.Categories.Name = "Beverages"
  • Untyped: UntypedDataSet.Tables("Categories").Row(0).Item(1)="Beverages"
  • Typed dataset reduces overhead and increases performance as they already have table schema with them.
  • Any type mismatch can be captured at compile time instead of run-time.
  • One disadvantage with Typed Dataset is if your table schema changes, you need to everytime change the Dataset's XSD file (which i will discuss later).

Creating Typed Dataset with Visual Studio 2005

To start creating Typed Dataset demo, I will be using Northwind Database. If you dont have this DB, you can download from MS Site. This will help us to be on same page and later you can try with your own database.

Finally we come here:

1. Start Visual Studio 2005 and create "New Project" and select "Web Application" and name the project as "TypedDatasetDemo". Reason for Web App is because you can see the code generated by visual studio. Same steps are applicable for WebSite too.

2. In "Solution Explorer", right click and select "Add New Item". Select Dataset from Dialog and name as "DSCategory.xsd".

3. Right click Dataset Designer and click Add->Table Adapter... to start with Wizard. You need to create new connection string if you dont have by clicking "New Connection" button. Click Next...

4. Give the name for connection string to be saved so it can be used next time. Click Next...

5. Leave with default selection of "Use SQL Statement" and click Next.

6. Paste the query in SQL Statement text area. "SELECT CategoryID, CategoryName, Description FROM Categories". You can even use "Query Builder..." button to create query by wizard.

7. In next screen, you need to mention the method name to "Fill a DataTable" and "Return a Data Table". This method will be used to fill DataTable and will be bound to control e.g. Grid View. Change the method name to "FillCategory" and "GetCategory".

8. Click "Finish" to complete the wizard.

Thats it... Typed dataset is created which can be used by web pages.

To complete this demo, we will create WebPage and load data into Grid View.

  • Open Web Page and drop "Grid View" and name as "gvCategory"
  • Switch to "Code View" of page and copy the code as mentioned below.

namespace TypedDatasetDemo
public partial class _Default : System.Web.UI.Page
DSCategoryTableAdapters.CategoriesTableAdapter cAdapter = new TypedDatasetDemo.DSCategoryTableAdapters.CategoriesTableAdapter();
DSCategory.CategoriesDataTable cDataTable;
protected void Page_Load(object sender, EventArgs e)
private void BindData()
cDataTable = cAdapter.GetCategory();
gvCategory.DataSource = cDataTable;

My First Blog

Hi... this is my first blog on this site... I created this blog a/c today and thought to start blogging on technical stuff on .NET 2.0, 3.0 and 3.5.

I will be posting articles from basic to advanced level and any solution to any critical issues.

Hope you find this site interactive and helpful :)

You can also let me know if you would like me to discuss on some topics.

Happy coding.....