ODBC DSN Using in ASP.Net

In this article you will learn how to do DSN connectivity to a database using an ODBC driver in ASP.NET.

A Data Source Name (DSN) provides connectivity to a database using an ODBC driver. The DSN contains database name, directory, database driver, User ID, password and other information. Once you create a DSN for a specific database, you can use the DSN in an application to call information from the database.

There are the following three types of DSNs:

  1. System DSN: Can be used by anyone with access to the machine. The DSN information is stored in the registry.

  2. User DSN: Created for a specific user. Also stored in the registry.

  3. File DSN: DSN information is stored in a text file with .DSN extension.

This article explains:

  1. How to create an ODBC DSN.
  2. How to add in Server Explorer.
  3. How to use in ASP.Net web site.

1. How to create ODBC DSN

To create an ODBC DSN go to the Control Panel first then find Administrative Tools.

Administrative

Go To ODBC Data Sources.

ODBC Data

That will open a dialog. Go to the System DSN Tab and click on the Add button to add a new System DSN.

System DSN

Select Data Source Type and Click to Finish.

Select Data Source

Provide a name for your DSN and Description (if you want) and select the server name or IP Address of your SQL Server System IP Address.

DSN

Select Authentication Type as either Windows or SQL Server Authentication in which your server works with the client systems and click Next.

IP Address

Now check the checkbox to change the default Database:

Database

Click Next again.

Again

Now test your ODBC Data Source.

ODBC

Data Source

You can see now your DSN name in the DSN name List.

DSN name List

2. How to Add in Server Explorer

In Visual Studio Open Server Explorer.

Right-click on Data Connections and click Add Connection…

Add Connection

Select The ODBC Data Source from the list to use the DSN and click OK for next.

ODBC Data Source

Select your DSN Name from all the System data source names and click OK.

click OK

Now you can see all the Tables of your Database.

your Data base

Table Data:

Table Data

3. How to Use in ASP.NET web site

Now let's see how to use a DSN in ASP.NET Web forms. Go to Visual Studio and create a web site.

create a web site

This is an "ASP.NET Empty Web Site" solution.

site solution

Now go to web.config and add a ConnectionString Object with the name of DSN Name.

ConnectionString

Now add a Web Form to get any table by DSN.

Web Form

Specify the name of the page.

page

Now add a Grid View Control to the page to show the table to the control.

control

Write the code to execute the command by OdbcDataAdapter Class object and use the ConfigurationManager to get the connection string.

OdbcDataAdapter

Code

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data.Odbc;  
  8. using System.Configuration;  
  9. using System.Data;  
  10. public partial class _Default : System.Web.UI.Page  
  11. {  
  12.     protected void Page_Load(object sender, EventArgs e)  
  13.     {  
  14.         if (!IsPostBack)  
  15.         {  
  16.             BindTable();  
  17.         }  
  18.     }  
  19.         public void BindTable()  
  20.         {  
  21.             OdbcDataAdapter da = new OdbcDataAdapter("Select * from Employee",  
  22.                 ConfigurationManager.ConnectionStrings["MyConStr"].ConnectionString);  
  23.             DataTable dt = new DataTable();  
  24.             da.Fill(dt);  
  25.             GridView1.DataSource = dt;  
  26.             GridView1.DataBind();  
  27.         }  
  28. }  
Output

Output