в 15:31, 26 мая 2010

2010-05-26T15:31:53Z

Admin: 1 версия

2010-05-26T12:19:04Z

1 версия

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

==CommandReader==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class CommandReader
{
static void Main()
{
SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind");
string sql = @"select firstname,lastname from employees";
SqlCommand cmd = new SqlCommand(sql, conn);
Console.WriteLine("Command created and connected.");
try
{
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine("Employee name: {0} {1}",
rdr.GetValue(0),
rdr.GetValue(1)
);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
Console.WriteLine("Connection Closed.");
}
}
}</source>

==Execute Scalar Example==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
class Program {
static void Main(string[] args) {
SqlConnection thisConnection = new
SqlConnection(@"Server=(local)\sqlexpress;Integrated Security=True;" +
"Database=northwind");
thisConnection.Open();
SqlCommand thisCommand = thisConnection.CreateCommand();
thisCommand.rumandText = "SELECT COUNT(*) FROM Customers";
Object countResult = thisCommand.ExecuteScalar();
Console.WriteLine("Count of Customers = {0}", countResult);
thisConnection.Close();
}
}</source>

==Execute the command to get Scalar value==

<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(sqlConnectString);
string selectText = "SELECT COUNT(*) FROM Person.Contact";
SqlCommand command = new SqlCommand(selectText, connection);
connection.Open( );
int recordCount = (int)command.ExecuteScalar( );
connection.Close( );
Console.WriteLine(recordCount);
}
}</source>

==Executing a Query That Does Not Return a Result Set==

<source lang="csharp">/*
CREATE TABLE MyTable(
Id int NOT NULL PRIMARY KEY,
Field1 nvarchar(50) NOT NULL,
Field2 nvarchar(50) NOT NULL,
)
INSERT INTO MyTable VALUES (1, "Field 1.1", "Field 2.1");
INSERT INTO MyTable VALUES (2, "Field 1.2", "Field 2.2");
INSERT INTO MyTable VALUES (3, "Field 1.3", "Field 2.3");
*/
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=MyDatabase;";
string sqlDelete = "DELETE FROM MyTable WHERE Id = 2";
SqlConnection connection = new SqlConnection(sqlConnectString);
SqlCommand command = new SqlCommand(sqlDelete, connection);
connection.Open( );
int rowsAffected = command.ExecuteNonQuery( );
Console.WriteLine("{0} row(s) affected.", rowsAffected);
Console.WriteLine("Record with Id = 2 deleted.");
connection.Close( );
}
}</source>

==Executing a Query That Returns Multiple Result Sets with DataSet==

<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;";
string sqlSelect = "SELECT TOP 3 * FROM Sales.SalesOrderHeader;" +
"SELECT TOP 3 * FROM Sales.SalesOrderDetail";
int rsNumber;
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataSet ds = new DataSet( );
da.Fill(ds);
rsNumber = 0;
foreach (DataTable dt in ds.Tables){
Console.WriteLine("Result set: {0}", ++rsNumber);
foreach (DataRow row in dt.Rows){
Console.WriteLine("{0}, {1}, {2}", row[0], row[1], row[2]);
}
}
}
}</source>

==Executing a Query That Returns Multiple Result Sets with SqlDataReader==

<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;";
string sqlSelect = "SELECT TOP 3 * FROM Sales.SalesOrderHeader;" +
"SELECT TOP 3 * FROM Sales.SalesOrderDetail";
int rsNumber;
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataSet ds = new DataSet( );
da.Fill(ds);
rsNumber = 0;
using (SqlConnection connection = new SqlConnection(sqlConnectString)){
SqlCommand command = new SqlCommand(sqlSelect, connection);
connection.Open( );
SqlDataReader dr = command.ExecuteReader( );
rsNumber = 0;
do{
Console.WriteLine("Result set: {0}", ++rsNumber);
while (dr.Read( )){
Console.WriteLine("{0}, {1}, {2}", dr[0], dr[1], dr[2]);
}
Console.WriteLine(Environment.NewLine);
} while (dr.NextResult( ));
}
}
}</source>

==Get table list==

<source lang="csharp">using System;
using System.Data.SqlClient;
public class GetTableList
{
public static void Main()
{
string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
string SQL = "SELECT TABLE_TYPE, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataReader r;
con.Open();
r = cmd.ExecuteReader();
while (r.Read()){
Console.WriteLine(r[0] + ": " + r[1]);
}
con.Close();
}
}</source>

==How to perform a SELECT statement using ADO.NET==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
public static void Main()
{
string connectionString ="server=localhost;database=Northwind;uid=sa;pwd=sa";
SqlConnection mySqlConnection = new SqlConnection(connectionString);
string selectString = "SELECT ID, FirstName FROM Employee";
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText = selectString;
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
string dataTableName = "Employee";
mySqlDataAdapter.Fill(myDataSet, dataTableName);
DataTable myDataTable = myDataSet.Tables[dataTableName];
foreach (DataRow myDataRow in myDataTable.Rows)
{
Console.WriteLine("ID = " + myDataRow["ID"]);
Console.WriteLine("FirstName = " + myDataRow["FirstName"]);
}
mySqlConnection.Close();
}
}</source>

==Simple Query==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
class MainClass
{
[STAThread]
static void Main(string[] args)
{
string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
using ( SqlConnection conn = new SqlConnection( cstr ) )
{
conn.Open();
SqlCommand cmd = new SqlCommand( "select * from Employee", conn );
SqlDataReader rdr = cmd.ExecuteReader();
while ( rdr.Read() )
{
System.Console.WriteLine( "{0}", rdr.GetString( 1 ) );
}
rdr.Close();
}
}
}</source>

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

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

в 15:31, 26 мая 2010

Admin: 1 версия