SQL Server 2008

SQL Server aims to make data management self-tuning, self-organizing and self-maintaining.

SQL Server 2008 includes better compression features, which also helps in improving scalability. It enhanced the indexing algorithms and introduced the notion of filtered indexes. It includes capabilities for transparent encryption of data (TDE) as well as compression of backups. It also provide support for structured and semi-structured data. SQL Server 2008 also supports the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be built around the Entity Data Model.

Providers for SQL Server 2008

.NET Framework Data Provider For SQL Server (SqlConnection)
.NET Framework Data Provider for OLE DB (oledbConnection)
.NET Framework Data Provider For ODBC (odbcConnection)
SQL Server Native Client 10.0 OLE DB Provider
SQL Server Native Client 10.0 ODBC Driver
SQLXML 4.0 OLEDB Provider
Context Connection

.NET Framework Data Provider for SQL Server

A .NET Framework data provider is used for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, placed in an ADO.NET Dataset in order to be exposed to the user in an ad hoc manner, combined with data from multiple sources, or remoted between tiers. It Provides data access for Microsoft SQL Server version 7.0 or later. Uses the System.Data,sqlclient namespace.

This is the number one to use if we want our .NET application or website to connect to an SQL Server.

The .NET Framework Data Provider for SQL Server uses its own protocol to communicate with SQL Server. It is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB or Open Database Connectivity (ODBC) layer.

Sample Code

using System.Data.SqlClient;
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
//execute queries, etc
myConnection.Close();

Standard Security

Syntax:

Data Source=urServerAddress;Initial Catalog=urDataBase;
User Id=urUsername;Password=urPassword;

OR:

Server=urServerAddress;Database=urDataBase;UserID=urUsername;Password=urPassword;
Trusted_Connection=False;

Trusted Connection

Syntax:

Data Source=urServerAddress;Initial Catalog=urDataBase;Integrated Security=SSPI;

OR:

Server=urServerAddress;Database=urDataBase;Trusted_Connection=True;

Using SQL Server instance

Syntax:

Server=urServerName\theInstanceName;Database=urDataBase;Trusted_Connection=True;

Using IP address,Port

Syntax:

Data Source=192.168.1.11,1433;Network Library=DBMSSOCN;Initial Catalog=urDataBase;
User ID=urUsername;Password=urPassword;

Asynchronous processing

Syntax:

Server=urServerAddress;Database=urDataBase;Integrated Security=True;Asynchronous Processing=True;

Database mirroring

Syntax:

Data Source=urServerAddress;Failover Partner=urMirrorServerAddress;
Initial Catalog=urDataBase;Integrated Security=True;

Using SQL Server Express instance

Syntax:

Server=.\SQLExpress;AttachDbFilename=c:\database\testproject\urdbfile.mdf;Database=urdbname;Trusted_Connection=Yes;

Using the data directory SQL Server Express instance

Syntax:

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|urdbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Using an User Instance on SQL Server Express instance

Syntax:

Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\urdb.mdf;User Instance=true;

The User Instance functionality creates a new SQL Server instance on the fly during connects.

Enabling MARS (Multiple Active Result Sets)

Syntax:

Server=urServerAddress;Database=urDataBase;Trusted_Connection=True;
MultipleActiveResultSets=true;

Trusted Connection from a CE device

Syntax:

Data Source=urServerAddress;Initial Catalog=urDataBase;Integrated Security=SSPI;
User ID=urDomain\urUsername;Password=urPassword;

.NET Framework Data Provider for OLE DB

The .NET Framework Data Provider for OLE DB is Recommended for middle-tier applications using Microsoft SQL Server 6.5 or earlier, or any OLE DB provider that supports the OLE DB interfaces listed in OLE DB Interfaces Used by the OLE DB .NET Data Provider in the .NET Framework SDK. The .NET Framework Data Provider for OLE DB automatically pools connections using OLE DB session pooling. Connection string arguments can be used to enable or disable OLE DB services including pooling.

Sample Code

using System.Data.OleDb;
OleDbConnection myConnection = new OleDbConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
//execute queries, etc
myConnection.Close();

Bridging to SQL Native Client OLE DB

Syntax:

Provider=SQLNCLI10;Server=urServerAddress;Database=urDataBase;Uid=urUsername;
Pwd=urPassword;

.NET Framework Data Provider for ODBC

The .NET Data Provider for ODBC access to data sources that are connected to using an ODBC driver.

The ODBC .NET Data Provider is an add-on component to the .NET Framework. It provides access to native ODBC drivers the same way that the OLE DB .NET Data Provider provides access to native OLE DB Providers A .NET Framework data provider also serves as a bridge between a data source and an ADO.NET DataSet. For data sources exposed by using ODBC. Uses the System.Data.Odbc namespace.

Sample Code

using System.Data.Odbc;
OdbcConnection myConnection = new OdbcConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
//execute queries, etc
myConnection.Close();

Bridging to SQL Native Client 10.0 ODBC Driver

Syntax:

Driver={SQL Server Native Client 10.0};Server=urServerAddress;Database=urDataBase;
Uid=urUsername;Pwd=urPassword;

SQL Server Native Client 10.0 OLE DB Provider

Microsoft SQL Server 2008 Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 features such as Multiple Active Result Sets (MARS), User-Defined Types (UDT), and XML data type support. While also providing new functionality above and beyond that supplied by the Microsoft Data Access Components (MDAC such as Multiple Active Result Sets (MARS), User-Defined Types (UDT), and XML data type support.

This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.

Code

Include "Provider=SQLNCLI10" in the connection string to use this provider.

Standard Security

Syntax:

Provider=SQLNCLI10;Server=urServerAddress;Database=urDataBase;Uid=urUsername;
Pwd=urPassword;

Trusted Connection

Syntax:

Provider=SQLNCLI10;Server=urServerAddress;Database=urDataBase; Trusted_Connection=yes;

Database mirroring

Syntax:

Provider=SQLNCLI10;Data Source=urServerAddress;Failover Partner=urMirrorServerAddress;
Initial Catalog=urDataBase;Integrated Security=True;

This is an example to pointing out the failover functionality. We can also use this with the other connection strings options.

Using SQL Server Instance

Syntax:

Provider=SQLNCLI10;Server=urServerName\theInstanceName;Database=urDataBase;
Trusted_Connection=yes;

Using Database SQL Server Express instance

Syntax:

Provider=SQLNCLI10;Server=.\SQLExpress;AttachDbFilename=c:\database\testproject\
urdbfile.mdf;Database=dbname; Trusted_Connection=Yes;

Using the data directory SQL Server Express instance

Syntax:

Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|urdbfile.mdf;
Database=dbname;Trusted_Connection=Yes;

Prompt for username and password

Syntax:

oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Provider=SQLNCLI10;Server=urServerAddress;DataBase=urDataBase;

Enabling MARS (Multiple Active Result Sets)

Syntax:

Provider=SQLNCLI10;Server=urServerAddress;Database=urDataBase; Trusted_Connection=yes;
MARS Connection=True;

Encrypt data sent over network

Syntax:

Provider=SQLNCLI10;Server=urServerAddress;Database=urDataBase;Trusted_Connection=yes;
Encrypt=yes;

SQL Server Native Client 10.0 ODBC Driver

The SQL Server Native Client 10.0 driver supports connecting to SQL 7.0 and later.

SQL Server supports ODBC, via the SQL Server Native Client ODBC driver, as one of the native APIs for writing C, C++, and Microsoft Visual Basic applications that communicate with SQL Server. The SQL Server-specific versions of the ODBC functions are implemented in the SQL Server Native Client ODBC driver. The driver passes SQL statements to SQL Server and returns the results of the statements to the application.

Code

Include "Driver={SQL Server Native Client 10.0}" in the connection string to use this driver.

Standard security

Syntax:

Driver={SQL Server Native Client 10.0};Server=urServerAddress;Database=urDataBase;

Uid=urUsername;Pwd=urPassword;

Trusted Connection

Syntax:

Driver={SQL Server Native Client 10.0};Server=urServerAddress;Database=urDataBase;

Trusted_Connection=yes;

Database mirroring

Syntax:

Driver={SQL Server Native Client 10.0};Server=urServerAddress;Failover_Partner=
urMirrorServerAddress;Database=urDataBase;Trusted_Connection=yes;

Using SQL Server Instance

Syntax:

Driver={SQL Server Native Client10.0};Server=urServerName\theInstanceName;Database=

urDataBase;Trusted_Connection=yes;

Using a database file SQL Server Express instance

Syntax:

Driver={SQL Server Native Client 10.0};Server=.\SQLExpress;AttachDbFilename=
c:\database\testproject\urdbfile.mdf; Database=dbname;Trusted_Connection=Yes;

Using the data directory SQL Server Express instance

Syntax:

Driver={SQL Server Native Client10.0};Server=.\SQLExpress;AttachDbFilename=
|DataDirectory|urdbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Prompt for username and password

Syntax:

oConn.Properties("Prompt") = adPromptAlways
Driver={SQL Server Native Client 10.0};Server=urServerAddress;Database=urDataBase;

Enabling MARS (Multiple Active Result Sets)

Syntax:

Driver={SQL Server Native Client 10.0};Server=urServerAddress;Database=urDataBase;
Trusted_Connection=yes; MARS_Connection=yes;

MARS is not supported in ADO.NET 1.0 nor ADO.NET . Use ADO.NET for MARS functionality

Encrypt data sent over network

Syntax:

Driver={SQL Server Native Client10.0};Server=urServerAddress;Database=urDataBase;
Trusted_Connection=yes;Encrypt=yes;

SQL XML 4.0 OLEDB Provider

The SQLXMLOLEDB provider can execute commands only in the "write to an output stream" mode of ADO. It is an OLE DB provider that exposes the Microsoft SQLXML functionality through ADO. The SQLXMLOLEDB provider is not a rowset provider; it can only execute commands in the "write to an output stream" mode of ADO.

Code

Include "Provider=SQLXMLOLEDB.4.0;Data Provider=providername" in the connection string to use this provider.

Using SQL Server Native Client provider

Syntax:

Provider=SQLXMLOLEDB.4.0;Data Provider=SQLNCLI10;Data Source=urServerAddress;Initial Catalog=urDataBase;User Id=urUsername;Password=urPassword;

Context Connection

The context connection lets us to execute Transact-SQL statements in the same context that your code was invoked in the first place. In order to obtain the context connection, we must use the "context connection" connection string keyword. It is used to create a database connection (SqlConnection) inside an CLR stored procedure.

Context Connection

Sample Code

using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
// Use the connection
}