what is the use of connecting to sql server using odbc

ODBC is generic one & useful for times when the underlying database might change like Oracle database to a SQL server database like wise.

The SQL Server Native client driver has been proven to be much faster & you can access only SQL DB..

can you plz tell the difference between connecting the back end by using sql provider and connecting with ODBC provider. And when do we go for ODBC provider

What you mean by directly connecting to SQL Server?

If you want to access the DB, then you should have drivers [mostly dlls] & use one of the following way.

.NET Framework Data Provider for SQL Server: Provides data access for Microsoft SQL Server. Uses the System.Data.SqlClient namespace.

.NET Framework Data Provider for OLE DB: For data sources exposed by using OLE DB. Uses the System.Data.OleDb namespace.

.NET Framework Data Provider for ODBC: For data sources exposed by using ODBC. Uses the System.Data.Odbc namespace.

.NET Framework Data Provider for Oracle: For Oracle data sources. The .NET Framework Data Provider for Oracle supports Oracle client software version 8.1.7 and later, and uses the System.Data.OracleClient namespace.

EntityClient Provider: Provides data access for Entity Data Model (EDM) applications. Uses the System.Data.EntityClient namespace.

.NET Framework Data Provider for SQL Server Compact 4.0: Provides data access for Microsoft SQL Server Compact 4.0. Uses the System.Data.SqlServerCe namespace.


For detailed info,

http://msdn.microsoft.com/en-us/library/a6cd7c08%28v=vs.110%29.aspx