Create and use Ado.net (iii) in asp.net

Ado|asp.net| Create and use Ado.net in asp.net (iii)

Use Ado.net to connect to a data source
In Ado.net, you can use the Connection object to connect to the specified data source. To connect to Microsoft SQL Server version 7.0 or later, use the SqlConnection object for the SQL Server. NET Framework data Provider. To use the OLE DB Provider (SQLOLEDB) for SQL Server to connect to an OLE DB data source or connect to a Microsoft SQL Server version 6.x or earlier, use the OLE DB. NET Framework number According to the provider's OleDbConnection object. To connect to an ODBC data source, use the OdbcConnection object for the ODBC. NET Framework data Provider. To connect to an Oracle data source, use the OracleConnection object for the Oracle. NET Framework Data Provider.

1. Connect to SQL Server using Ado.net
The SQL Server. NET Framework data Provider uses the SqlConnection object to provide connections to Microsoft SQL Server version 7.0 or later.

The SQL Server. NET Framework Data Provider supports a connection string format similar to the OLE DB (ADO) connection string format. For valid string format names and values, see Schedule 1

The following code example demonstrates how to create and open a connection to a SQL Server (version 7.0 or later) database.

[Visual Basic]
Dim myconn As SqlConnection = New SqlConnection ("Data source=localhost;integrated security=sspi;initial Catalog= Northwind ")
MyConn.Open ()
[C #]
SqlConnection nwindconn = new SqlConnection ("Data source=localhost; Integrated SECURITY=SSPI; "+
"Initial Catalog=northwind");
Nwindconn.open ();
It is recommended that you always turn off Connection after you have finished using it. This can be accomplished using the close or Dispose method of the Connection object.

Integrated Security and ASP.net
SQL Server integrated security (also known as a trusted connection) is the safest way to connect to SQL Server because it does not expose the user identity and password in the connection string. It is recommended that you use this method to authenticate the connection. Integrated security uses the current security identity or tag of the process being executed. For desktop applications, the security identification or token is usually the identity of the currently logged-on user.

The security identity of the ASP.net application can be set to one of several different options. For a better understanding of the security identity used by asp.net applications when connecting to SQL Server using integrated security, see the Ado.net code series that I wrote asp.net for Security or refer to MSDN

Name
Default
Description
Name
Default value
Description

Application Name

The name of the application, or '. Net SqlClient Data Provider ' If no application name is provided.
Application Name

The name of the application, or ". Net SqlClient Data Provider" (if no application name is provided).

AttachDbFileName

-or-

Extended Properties

-or-

Initial File Name

The name of the primary file, including the full path name, and an attachable database.

The database name must is specified with the keyword ' database '.
AttachDbFileName

Or

Extended Properties

Or

Initial file name

The name of the primary file that can connect to the database, including the full pathname.

You must use the keyword "database" to specify the name of the database.

Connect Timeout

-or-

Connection Timeout
15
The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an ER Ror.
Connection Timeout setting

Or

Connection Timeout
15
The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.

Current Language

The SQL Server Language record name.
Current language

SQL Server language record name.

Data Source

-or-

Server

-or-

Address

-or-

Addr

-or-

Network address

The name or network address of the instance of SQL Server to which to connect.
Data source

Or

Server

Or

Address

Or

Addr

Or

Network address

The name or network address of the instance of SQL Server to which you want to connect.

Encrypt
' False '
When True, SQL Server uses SSL encryption to all data sent between the client and server if the server has a certificate installed. Recognized values are true, false, yes, and No.
Encryption
' False '
When this value is true, if the server side has a certificate installed, SQL Server uses SSL encryption for all data that is transferred between the client and the server. The recognizable values are true, false, yes, and No.

Initial Catalog

-or-

Database

The name of the database.
Initial directory

Or

Database

The name of the database.

Integrated security

-or-

Trusted_Connection
' False '
When false, the User ID and Password are specified the connection. When True, the current Windows account credentials are used for authentication.

Recognized values are true, false, yes, no, and SSPI (strongly recommended), which is equivalent to true.
Integrated security

Or

Trusted_Connection
' False '
When false, the user ID and password are specified in the connection. When True, authentication is performed using the current Windows account credentials.

The recognized value is true, false, yes, no, and SSPI (strongly recommended) equivalent to True.

Network Library

-or-

Net
' DBMSSOCN '
The network library used to establish a connection to a instance of SQL Server. Supported values include DBNMPNTW (Named pipes), DBMSRPCN (Multiprotocol), Dbmsadsn (Apple Talk), Dbmsgnet (VIA), DBMSLPCN (Shared Memory) and DBMSSPXN (ipx/spx), and DBMSSOCN (TCP/IP).

The corresponding network DLL must is installed on the system to which you connect. If you don't specify a network and use a local server (for example, "." or "(local)"), the shared memory is used.
Network Library

Or

Internet
' DBMSSOCN '
A network library that is used to establish a connection to an instance of SQL Server. Supported values include DBNMPNTW (Named Pipes), DBMSRPCN (Multiprotocol), Dbmsadsn (Apple Talk), Dbmsgnet (VIA), DBMSLPCN (Shared memory) and DBMSSPXN (ipx/spx) and DBMSSOCN (TCP/IP).

The corresponding network DLL must be installed on the system to which you want to connect. If you do not specify a network, use a local server (such as "." or "(local)"), the shared memory is used.

Packet Size
8192
Size in bytes of the network packets used to communicate with a instance of SQL Server.
Packet size
8192
The size, in bytes, of the network packet used to communicate with an instance of SQL Server.

Password

-or-

Pwd

The password for the SQL Server account logging in not recommended. To maintain a-high level of security, it's strongly recommended that you use the integrated security or Trusted_connectio n keyword instead.).
Password

Or

Pwd

The password for the SQL Server account logon (not recommended for use.) In order to maintain the highest level of security, it is strongly recommended that you use the integrated or trusted_connection keyword instead.

Persist Security Info
' False '
When set to False or no (strongly recommended), security-sensitive information, such as the password, are not returned as P Art of the connection if the connection is open or has ever been in a open state. Resetting the connection string resets all connection string values including the password. Recognized values are true, false, yes, and No.
Continuous security information
' False '
When the value is set to False or no (strongly recommended), security-sensitive information, such as a password, will not be returned as part of the connection if the connection is open or is always open. Resetting the connection string resets all connection string values, including passwords. The recognizable values are true, false, yes, and No.

User ID

The SQL Server login account (not recommended. To maintain a-high level of security, it's strongly recommended that you use the integrated security or Trusted_connectio n keyword instead.).
User ID

SQL Server logon account (not recommended for use.) In order to maintain the highest level of security, it is strongly recommended that you use the integrated or trusted_connection keyword instead.

Workstation ID
The local computer name
The name of the workstation connecting to SQL Server.
Workstation ID
Local Computer name
The name of the workstation that is connected to SQL Server.




Name
Default
Description
Name
Default value
Description

Connection Lifetime
0
When a connection are returned to the pool, its creation time is compared with the "current", and the connection is dest royed If this time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful into clustered configurations to force load balancing between a running server and a server just brought Ne.

A value of zero (0) causes pooled connections to have the maximum connection.
Connection lifetime
0
When a connection is returned to the pool, its creation time is compared to the current time, and if the length of time (in seconds) exceeds the value specified by Connection Lifetime, the connection is destroyed. This is useful in a clustered configuration (used to enforce load balancing between a running server and a server that is just put online).

A value of 0 (0) will cause the pool connection to have the maximum connection timeout.

Connection Reset
' True '
Determines whether the database connection is reset when being drawn from the pool. For Microsoft SQL Server version 7.0, setting to false avoids making a additional Server round trips when obtaining a conn Ection, but you must to be aware this connection state, such as database context, are not being reset.
Connection Reset
' True '
Determines whether the database connection is reset when extracting a database connection from the pool. For Microsoft SQL Server version 7.0, setting to False avoids an additional server round trip when a connection is made, but note that the connection state, such as the database context, is not reset at this time.

Enlist
' True '
When True, the pooler automatically enlists the connection in the creation thread ' s current transaction context. Recognized values are true, false, yes, and No.
Registration
' True '
When this value is true, the pool program automatically enlists the connection in the current transaction context of the creation thread. The recognizable values are true, false, yes, and No.

Max Pool Size
100
The maximum number of connections allowed in the pool.
Maximum Pool size
100
The maximum number of connections allowed in the pool.

Min Pool Size
0
The minimum number of connections allowed in the pool.
Minimum pool Size
0
The minimum number of connections allowed in the pool.

Pooling
' True '
When True, the SqlConnection object was drawn from the appropriate pool, or if necessary, are created and added to the Appro Priate pool. Recognized values are true, false, yes, and No.
Pool
' True '
When True, the system extracts the SqlConnection object from the pool or, if necessary, creates the object and adds it to the appropriate pool. The recognizable values are true, false, yes, and No.