Quote

Various Methods of Connecting to SQL database using .Net (In C#)

Various Methods of Connecting to SQL database using .Net (In C#)

 

This tutorial is for Novice user who wants to know about SQL Database connection using .net. There are different methods of connecting SQL database using C#. It would be very good start for new users, if he/she tries to understand data access stack first which is give below (reference from : MSDN)

             From above figure, it is clear that there are three different methods of connecting to SQL Server Database. These are the basic method of connecting to SQL server database. Beside these methods, you can also use Microsoft’s Data Access Application Block to access SQL. You can get more help on DAAB at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp. There are so many other ways of connecting to SQL database. You wan put as many layers as you want between your database and your presentation layer. Here I am going to use following for methods:

 

1. Using OLE DB Provider (Bridge Provider)
   
2. Using ODBC DM (Bridge Provider)
   
3. Using SQL Server .Net Provider (Native Providers)
   
4. Using DAAB (Follows best Practice)

 

Required Environment and tools:

 

SQL Server 2000/2005

Northwind database (You can get it from here)

Visual Studio 2005

Data Access Application Block. (You can get it from here)

Add reference of Microsoft.Practices.EnterpriseLibrary.Data.dll and Microsoft.Practices.EnterpriseLibrary.Common.dll to your project.

GUI:

 

>

Code:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.OleDb; // for Oledb provider

using System.Data.Odbc;

using System.Data.SqlClient;

using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

 

namespace SQLConnectionMethods

{

public partial class SQLConnectionMethods : Form

{

static string OledbConnectionString = "Provider=sqloledb;Data Source=mn02ws1patelc;Initial Catalog=Northwind;User Id=sa;Password=;";

static string ODBCConnectionString = "Driver={SQL Server};Server=mn02ws1patelc;Database=Northwind;Uid=sa;Pwd=;";

static string SQLServerDotNetConnectionString = "Data Source=mn02ws1patelc;Initial Catalog=Northwind;User Id=sa;Password=;";

static string selectCommand = "Select * from products";

DataTable dtproducts;

public SQLConnectionMethods()

{

InitializeComponent();

}

private void btnOledb_Click(object sender, EventArgs e)

{

OleDbConnection cnOledb = new OleDbConnection(OledbConnectionString);

OleDbCommand cmdOledb = new OleDbCommand(selectCommand, cnOledb);

OleDbDataAdapter daOledb = new OleDbDataAdapter(cmdOledb);

dtproducts = new DataTable();

daOledb.Fill(dtproducts);

grdSQLConnectionMethods.DataSource = dtproducts;

}

private void btnODBC_Click(object sender, EventArgs e)

{

OdbcConnection cnOdbc = new OdbcConnection(ODBCConnectionString);

OdbcCommand cmdOdbc = new OdbcCommand(selectCommand, cnOdbc);

OdbcDataAdapter daOdbc = new OdbcDataAdapter(cmdOdbc);

dtproducts = new DataTable();

daOdbc.Fill(dtproducts);

grdSQLConnectionMethods.DataSource = dtproducts;

}

private void btnSQLServerNet_Click(object sender, EventArgs e)

{

SqlConnection cnSQLServerDotNet = new SqlConnection(SQLServerDotNetConnectionString);

SqlCommand cmdSQLServerDotNet = new SqlCommand(selectCommand, cnSQLServerDotNet);

SqlDataAdapter daSQLServerDotNet = new SqlDataAdapter(cmdSQLServerDotNet);

dtproducts = new DataTable();

daSQLServerDotNet.Fill(dtproducts);

grdSQLConnectionMethods.DataSource = dtproducts;

}

private void btnDAAB_Click(object sender, EventArgs e)

{

SqlDatabase dbDaab = new SqlDatabase(SQLServerDotNetConnectionString);

dtproducts = new DataTable();

dtproducts = dbDaab.ExecuteDataSet(CommandType.Text, selectCommand).Tables[0];

grdSQLConnectionMethods.DataSource = dtproducts;

}

}

}