Csharp/CSharp Tutorial/ADO.Net/SqlConnection
Содержание
- 1 Connection Display
- 2 Connection event: state changed and information
- 3 Connect to SQLserver
- 4 Connect to SqlServer and set the IP Address
- 5 Connect to SqlServer Named Instance
- 6 Connect to SqlServer with Integrated security
- 7 Connect to SqlServer with password and id
- 8 Deal with connection exception
- 9 Get Sql Statistics
- 10 Obtain a nonpooled connection
- 11 Obtain a pooled connection
- 12 Opens and verifies a connection
- 13 Properties of SqlConnection
- 14 Set AttachDbFilename in connection string
- 15 Setting Connection Pooling Options
- 16 Show the connection properties for SqlConnection
- 17 SqlConnection connection state
Connection Display
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class ConnectionDisplay
{
static void Main()
{
SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;user id=administrator;integrated security = true;");
try
{
conn.Open();
Console.WriteLine("\tConnection String: {0}",conn.ConnectionString);
Console.WriteLine("\tDatabase: {0}",conn.Database);
Console.WriteLine("\tDataSource: {0}",conn.DataSource);
Console.WriteLine("\tServerVersion: {0}",conn.ServerVersion);
Console.WriteLine("\tState: {0}",conn.State);
Console.WriteLine("\tWorkstationId: {0}",conn.WorkstationId);
}
catch (SqlException e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
Console.WriteLine("Connection closed.");
}
}
}</source>
Connection event: state changed and information
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; public class MainClass{
public static void Main(){
SqlConnection cn = new SqlConnection();
try
{
cn.StateChange += new StateChangeEventHandler(StateChange);
cn.InfoMessage += new SqlInfoMessageEventHandler(InfoMessage);
cn.ConnectionString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
cn.Open();
cn.ChangeDatabase("Biblio");
}
catch( Exception ex )
{
Console.WriteLine(ex.Message);
}
finally
{
// Unsubscribe from the Events we subscribed to.
cn.StateChange -= new StateChangeEventHandler(StateChange);
cn.InfoMessage -= new SqlInfoMessageEventHandler(InfoMessage);
}
}
private static void StateChange(object sender, System.Data.StateChangeEventArgs e)
{
Console.WriteLine("State was:" + e.OriginalState.ToString() + ". New state = " + e.CurrentState.ToString() );
}
private static void InfoMessage( object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
{
int i;
for (i=0; i < e.Errors.Count; i++ )
{
Console.WriteLine(e.Errors[i].Message);
}
}
}</source>
State was:Closed. New state = Open Could not locate entry in sysdatabases for database "Biblio". No entry found with that name. Make su re that the name is entered correctly.
Connect to SQLserver
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args)
{
string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
SqlConnection conn = new SqlConnection(connString);
try {
conn.Open();
Console.WriteLine("Connection opened.");
}
catch (SqlException e) {
Console.WriteLine("Error: " + e);
}
finally {
conn.Close();
Console.WriteLine("Connection closed.");
}
}
}</source>
Connection opened. Connection closed.
Connect to SqlServer and set the IP Address
<source lang="csharp">using System; using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
string connectString =
"Network Library=dbmssocn;Network Address=127.0.0.1;" +
"Integrated security=SSPI;Initial Catalog=AdventureWorks";
using (SqlConnection connection = new SqlConnection(connectString))
{
connection.Open( );
Console.WriteLine(connection.State);
}
}
}</source>
Connect to SqlServer Named Instance
<source lang="csharp">using System; using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
string sqlConnectString = @"Data Source=(local)\SQLExpress;Integrated security=SSPI;Initial Catalog=master;";
using (SqlConnection connection = new SqlConnection(sqlConnectString))
{
connection.Open( );
Console.WriteLine(connection.State);
Console.WriteLine(connection.DataSource);
Console.WriteLine(connection.ServerVersion);
}
}
}</source>
Connect to SqlServer with Integrated security
<source lang="csharp">using System; using System.Data.SqlClient; using System.Data.OleDb; using System.Data.Odbc;
class Program
{
static void Main(string[] args)
{
string sqlConnectString1 = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
using (SqlConnection connection = new SqlConnection(sqlConnectString1))
{
connection.Open( );
Console.WriteLine("ConnectionString = {0}\n", sqlConnectString1);
Console.WriteLine("State = {0}", connection.State);
Console.WriteLine("DataSource = {0}", connection.DataSource);
Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
}
}
}</source>
Connect to SqlServer with password and id
<source lang="csharp">using System; using System.Data.SqlClient; using System.Data.OleDb; using System.Data.Odbc;
class Program
{
static void Main(string[] args)
{
string sqlConnectString2 = "Data Source=(local);User Id=sa;Password=password;Initial Catalog=AdventureWorks;";
using (SqlConnection connection = new SqlConnection(sqlConnectString2))
{
connection.Open( );
Console.WriteLine("ConnectionString = {0}\n", sqlConnectString2);
Console.WriteLine("State = {0}", connection.State);
Console.WriteLine("DataSource = {0}", connection.DataSource);
Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
}
}
}</source>
Deal with connection exception
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args)
{
string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
SqlConnection conn = new SqlConnection(connString);
try {
conn.Open();
Console.WriteLine("Connection opened.");
}
catch (SqlException e) {
Console.WriteLine("Error: " + e);
}
finally {
conn.Close();
Console.WriteLine("Connection closed.");
}
}
}</source>
Get Sql Statistics
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; using System.Collections;
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection("data source=localhost; initial catalog=SampleDB; Integrated Security=SSPI;");
conn.StatisticsEnabled = true;
conn.Open();
Hashtable ht = (Hashtable)conn.RetrieveStatistics();
foreach (string key in ht.Keys)
{
Console.WriteLine("{0} : {1}", key, ht[key]);
}
}
}</source>
Obtain a nonpooled connection
<source lang="csharp">using System; using System.Data.SqlClient; class MainClass {
public static void Main()
{
using (SqlConnection con = new SqlConnection())
{
// Configure the SqlConnection object"s connection string.
con.ConnectionString =
@"Data Source = .\sqlexpress;" +//local SQL Server instance
"Database = Northwind;" + //the sample Northwind DB
"Integrated Security = SSPI;" + //integrated Windows security
"Pooling = False"; //specify nonpooled connection
con.Open();
}
}
}</source>
Obtain a pooled connection
<source lang="csharp">using System; using System.Data.SqlClient; class MainClass {
public static void Main()
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString =
@"Data Source = .\sqlexpress;" +// local SQL Server instance
"Database = Northwind;" + // the sample Northwind DB
"Integrated Security = SSPI;" + // integrated Windows security
"Min Pool Size = 5;" + // configure minimum pool size
"Max Pool Size = 15;" + // configure maximum pool size
"Connection Reset = True;" + // reset connections each use
"Connection Lifetime = 600"; // set max connection lifetime
con.Open();
}
}
}</source>
Opens and verifies a connection
<source lang="csharp">using System; using System.Data.SqlClient; public class ConnectionTest {
public static void Main()
{
SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI");
con.Open();
Console.WriteLine("Connection is " + con.State.ToString());
con.Close();
Console.WriteLine("Connection is " + con.State.ToString());
}
}</source>
Properties of SqlConnection
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MySqlConnection {
public static void Main() {
string connectionString = "server=localhost;database=Northwind;uid=sa;pwd=sa";
SqlConnection mySqlConnection = new SqlConnection(connectionString);
mySqlConnection.Open();
Console.WriteLine("mySqlConnection.ConnectionString = " + mySqlConnection.ConnectionString);
Console.WriteLine("mySqlConnection.ConnectionTimeout = " + mySqlConnection.ConnectionTimeout);
Console.WriteLine("mySqlConnection.Database = " + mySqlConnection.Database);
Console.WriteLine("mySqlConnection.DataSource = " + mySqlConnection.DataSource);
Console.WriteLine("mySqlConnection.PacketSize = " + mySqlConnection.PacketSize);
Console.WriteLine("mySqlConnection.ServerVersion = " + mySqlConnection.ServerVersion);
Console.WriteLine("mySqlConnection.State = " + mySqlConnection.State);
Console.WriteLine("mySqlConnection.WorkstationId = " + mySqlConnection.WorkstationId);
mySqlConnection.Close();
}
}</source>
Set AttachDbFilename in connection string
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
SqlConnection thisConnection = new SqlConnection(
@"Data Source=.\SQLEXPRESS;" +
@"AttachDbFilename="NORTHWND.MDF";" +
@"Integrated Security=True;Connect Timeout=30;User Instance=true");
thisConnection.Open();
SqlCommand thisCommand = thisConnection.CreateCommand();
thisCommand.rumandText = "SELECT CustomerID, CompanyName from Customers";
SqlDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
Console.WriteLine("\t{0}\t{1}",
thisReader["CustomerID"], thisReader["CompanyName"]);
}
thisReader.Close();
thisConnection.Close();
}
}</source>
Setting Connection Pooling Options
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
SqlConnection connection = new SqlConnection( );
connection.StateChange += new StateChangeEventHandler(connection_StateChange);
connection.ConnectionString = sqlConnectString +
"Connection Timeout=15;Connection Lifetime=0;" +
"Min Pool Size=0;Max Pool Size=100;Pooling=true;";
connection.Open();
connection.Close();
connection.ConnectionString = sqlConnectString +
"Connection Timeout=30;Connection Lifetime=0;" +
"Min Pool Size=0;Max Pool Size=200;Pooling=true;";
connection.Open();
connection.Close();
}
static void connection_StateChange(object sender, StateChangeEventArgs e)
{
Console.WriteLine("\tOriginalState = {0}", e.OriginalState.ToString());
Console.WriteLine("\tCurrentState = {0}", e.CurrentState.ToString());
}
}</source>
Show the connection properties for SqlConnection
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OleDb; using System.Data.SqlClient;
public class MainClass{
static void Main() {
// Create a Connection Object
string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
SqlConnection conn = new SqlConnection(ConnectionString);
// Open the connection
if( conn.State != ConnectionState.Open)
conn.Open();
//
Console.WriteLine( "Connection String :"+conn.ConnectionString
+ ", Workstation Id:"+ conn.WorkstationId.ToString()
+", Packet Size :"+ conn.PacketSize.ToString()
+", Server Version "+ conn.ServerVersion.ToString()
+", DataSource :"+ conn.DataSource.ToString()
+", Server Version:"+ conn.ServerVersion.ToString()
+", Connection Time Out:"+ conn.ConnectionTimeout.ToString() );
// Close the connection
if( conn.State == ConnectionState.Open)
conn.Close();
}
}</source>
Connection String :server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;, Workstat ion Id:nfex, Packet Size :8000, Server Version 09.00.1399, DataSource :(local)\SQLEXPRESS, Server Version:09.00.1399, Connection Time Out:15
SqlConnection connection state
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; class MainClass {
public static void Main()
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
con.Open();
if (con.State == ConnectionState.Open)
{
Console.WriteLine("SqlConnection Information:");
Console.WriteLine(" Connection State = " + con.State);
Console.WriteLine(" Connection String = " + con.ConnectionString);
Console.WriteLine(" Database Source = " + con.DataSource);
Console.WriteLine(" Database = " + con.Database);
Console.WriteLine(" Server Version = " + con.ServerVersion);
Console.WriteLine(" Workstation Id = " + con.WorkstationId);
Console.WriteLine(" Timeout = " + con.ConnectionTimeout);
Console.WriteLine(" Packet Size = " + con.PacketSize);
}
else
{
Console.WriteLine("SqlConnection failed to open.");
Console.WriteLine(" Connection State = " + con.State);
}
}
}
}</source>
SqlConnection Information: Connection State = Open Connection String = server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI; Database Source = (local)\SQLEXPRESS Database = MyDatabase Server Version = 09.00.1399 Workstation Id = nfex Timeout = 15 Packet Size = 8000
