в 15:31, 26 мая 2010

2010-05-26T15:31:53Z

Admin: 1 версия

2010-05-26T12:19:08Z

1 версия

Новая страница

==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>
<pre class=codeResult>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.</pre>

==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>
<pre class=codeResult>Connection opened.
Connection closed.</pre>

==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>
<pre class=codeResult>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</pre>

==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>
<pre class=codeResult>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</pre>

← Предыдущая	Версия 15:31, 26 мая 2010
(нет различий)

Csharp/CSharp Tutorial/ADO.Net/SqlConnection - История изменений

в 15:31, 26 мая 2010

Admin: 1 версия