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
}