Csharp/CSharp Tutorial/ADO.Net/SqlConnection — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 15:19, 26 мая 2010
Содержание
- 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