в 15:31, 26 мая 2010

2010-05-26T15:31:19Z

Admin: 1 версия

2010-05-26T11:42:24Z

1 версия

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

==Deal with Multiple Results==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class MultipleResults {
static void Main(string[] args) {
string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
string sql1 = @"select FirstName from Employee;";
string sql2 = @"select FirstName, LastName from Employee;";
string sql = sql1 + sql2;
SqlConnection conn = new SqlConnection(connString);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader reader = cmd.ExecuteReader();
do{
while(reader.Read()) {
Console.WriteLine("{0} : {1}", reader[0], reader[0]);
}
Console.WriteLine("".PadLeft(60, "="));
} while(reader.NextResult());
reader.Close();
} catch(Exception e) {
Console.WriteLine("Error Occurred: " + e);
} finally {
conn.Close();
}
}
}

</source>

==Execute multiple SELECT statements using a SqlCommand object and read the results using a SqlDataReader object==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteSelect
{
public static void Main()
{
SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText =
"SELECT TOP 5 ProductID, ProductName " +
"FROM Products " +
"ORDER BY ProductID;" +
"SELECT TOP 3 CustomerID, CompanyName " +
"FROM Customers " +
"ORDER BY CustomerID;" +
"SELECT TOP 6 OrderID, CustomerID " +
"FROM Orders " +
"ORDER BY OrderID;";
mySqlConnection.Open();
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
do
{
while (mySqlDataReader.Read())
{
Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]);
Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]);
}
Console.WriteLine("");
} while (mySqlDataReader.NextResult());
mySqlDataReader.Close();
mySqlConnection.Close();
}
}

</source>

==Get column index from SqlDataReader==

<source lang="csharp">
using System;
using System.Data.SqlClient;

class ConnectToSqlConnection {
static void Main(string[] args) {
String sConn = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
String sSQL = "select id, firstname, lastname from Employee";
SqlConnection oConn = new SqlConnection(sConn);
oConn.Open();
SqlCommand oCmd = new SqlCommand(sSQL, oConn);
SqlDataReader oReader = oCmd.ExecuteReader();
int idxID = oReader.GetOrdinal("id");
int idxFirstName = oReader.GetOrdinal("firstname");
int idxLastName = oReader.GetOrdinal("lastname");
while(oReader.Read()) {
Console.WriteLine("{0} {1} {2}",
oReader.GetValue(idxID),
oReader.GetValue(idxFirstName),
oReader.GetValue(idxLastName));
}
}
}

</source>

==how to read column values as C# types using the Get* methods==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class StronglyTypedColumnValues {
public static void Main() {
SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText =
"SELECT TOP 5 ProductID, ProductName, UnitPrice, " +
"UnitsInStock, Discontinued " +
"FROM Products " +
"ORDER BY ProductID";
mySqlConnection.Open();
SqlDataReader productsSqlDataReader =
mySqlCommand.ExecuteReader();
int productIDColPos = productsSqlDataReader.GetOrdinal("ProductID");
int productNameColPos = productsSqlDataReader.GetOrdinal("ProductName");
int unitPriceColPos = productsSqlDataReader.GetOrdinal("UnitPrice");
int unitsInStockColPos = productsSqlDataReader.GetOrdinal("UnitsInStock");
int discontinuedColPos = productsSqlDataReader.GetOrdinal("Discontinued");
Console.WriteLine("ProductID .NET type = " + productsSqlDataReader.GetFieldType(productIDColPos));
Console.WriteLine("ProductName .NET type = " + productsSqlDataReader.GetFieldType(productNameColPos));
Console.WriteLine("UnitPrice .NET type = " + productsSqlDataReader.GetFieldType(unitPriceColPos));
Console.WriteLine("UnitsInStock .NET type = " + productsSqlDataReader.GetFieldType(unitsInStockColPos));
Console.WriteLine("Discontinued .NET type = " + productsSqlDataReader.GetFieldType(discontinuedColPos));
Console.WriteLine("ProductID database type = " + productsSqlDataReader.GetDataTypeName(productIDColPos));
Console.WriteLine("ProductName database type = " + productsSqlDataReader.GetDataTypeName(productNameColPos));
Console.WriteLine("UnitPrice database type = " + productsSqlDataReader.GetDataTypeName(unitPriceColPos));
Console.WriteLine("UnitsInStock database type = " + productsSqlDataReader.GetDataTypeName(unitsInStockColPos));
Console.WriteLine("Discontinued database type = " + productsSqlDataReader.GetDataTypeName(discontinuedColPos));
while (productsSqlDataReader.Read()) {
int productID = productsSqlDataReader.GetInt32(productIDColPos);
Console.WriteLine("productID = " + productID);
string productName = productsSqlDataReader.GetString(productNameColPos);
Console.WriteLine("productName = " + productName);
decimal unitPrice = productsSqlDataReader.GetDecimal(unitPriceColPos);
Console.WriteLine("unitPrice = " + unitPrice);
short unitsInStock = productsSqlDataReader.GetInt16(unitsInStockColPos);
Console.WriteLine("unitsInStock = " + unitsInStock);
bool discontinued = productsSqlDataReader.GetBoolean(discontinuedColPos);
Console.WriteLine("discontinued = " + discontinued);
}
productsSqlDataReader.Close();
mySqlConnection.Close();
}
}
</source>

==Read data from SqlDataReader==

<source lang="csharp">
using System;
using System.Data.SqlClient;

class ConnectToSqlConnection {
static void Main(string[] args) {
String sConn = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
String sSQL = "select id, firstname, lastname from Employee";
SqlConnection oConn = new SqlConnection(sConn);
oConn.Open();
SqlCommand oCmd = new SqlCommand(sSQL, oConn);
SqlDataReader oReader = oCmd.ExecuteReader();
int idxID = oReader.GetOrdinal("id");
int idxFirstName = oReader.GetOrdinal("firstname");
int idxLastName = oReader.GetOrdinal("lastname");
while(oReader.Read()) {
Console.WriteLine("{0} {1} {2}",
oReader.GetValue(idxID),
oReader.GetValue(idxFirstName),
oReader.GetValue(idxLastName));
}
}
}

</source>

==Reference data in SqlDataReader by column name==

<source lang="csharp">
using System;
using System.Data.SqlClient;
class pubsdemo
{
static void Main(string[] args)
{
String sConn = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
String sSQL = "select id, firstname, lastname from Employee";
SqlConnection oConn = new SqlConnection(sConn);
oConn.Open();
SqlCommand oCmd = new SqlCommand(sSQL, oConn);
SqlDataReader oReader = oCmd.ExecuteReader();
while(oReader.Read()) {
Console.WriteLine("{0} {1} {2}",
oReader["id"],
oReader["firstname"],
oReader["lastname"]);
}
}
}

</source>

==SqlDataReader Ordinal Indexer==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class OrdinalIndexer
{
static void Main(string[] args)
{
string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
string sql = @"select FirstName, LastName from Employee";
SqlConnection conn = new SqlConnection(connString);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader reader = cmd.ExecuteReader();
Console.WriteLine("\t{0} {1}", "First Name".PadRight(25),"Last Name".PadRight(20));
Console.WriteLine("\t{0} {1}", "============".PadRight(25), "============".PadRight(20));
while(reader.Read()) {
Console.WriteLine(" {0} | {1}",
reader[0].ToString().PadLeft(25),
reader[1].ToString().PadLeft(20));
}
reader.Close();
} catch(Exception e) {
Console.WriteLine("Error Occurred: " + e);
} finally {
conn.Close();
}
}
}

</source>

==Use A Data Reader==

<source lang="csharp">
/*
* C# Programmers Pocket Consultant
* Author: Gregory S. MacBeth
* Email: gmacbeth@comporium.net
* Create Date: June 27, 2003
* Last Modified Date:
* Version: 1
*/
using System;
using System.Data;
using System.Data.SqlClient;
namespace Client.Chapter_13___ADO.NET
{
public class UsingADataReader
{
static void Main(string[] args)
{
SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
MyConnection.Open();
SqlCommand MyCommand = new SqlCommand("SELECT * FROM CaseInfo", MyConnection);
SqlDataReader MyDataReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (MyDataReader.Read())
{
Console.WriteLine(MyDataReader[0] + " " + MyDataReader[1]);
}
MyConnection.Close();
}
}
}

</source>

==use the GetOrdinal() from DataReader to get the numeric positions of a column==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class UsingColumnOrdinals {
public static void Main() {
SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText =
"SELECT TOP 5 ProductID, ProductName, UnitPrice, " +
"UnitsInStock, Discontinued " +
"FROM Products " +
"ORDER BY ProductID";
mySqlConnection.Open();
SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader();
int productIDColPos = productsSqlDataReader.GetOrdinal("ProductID");
int productNameColPos = productsSqlDataReader.GetOrdinal("ProductName");
int unitPriceColPos = productsSqlDataReader.GetOrdinal("UnitPrice");
int unitsInStockColPos = productsSqlDataReader.GetOrdinal("UnitsInStock");
int discontinuedColPos = productsSqlDataReader.GetOrdinal("Discontinued");
while (productsSqlDataReader.Read()) {
Console.WriteLine("ProductID = " + productsSqlDataReader[productIDColPos]);
Console.WriteLine("ProductName = " + productsSqlDataReader[productNameColPos]);
Console.WriteLine("UnitPrice = " + productsSqlDataReader[unitPriceColPos]);
Console.WriteLine("UnitsInStock = " + productsSqlDataReader[unitsInStockColPos]);
Console.WriteLine("Discontinued = " + productsSqlDataReader[discontinuedColPos]);
}
productsSqlDataReader.Close();
mySqlConnection.Close();
}
}
</source>

==Use While loop to read query result data from SqlDataReader==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class DataLooper{
static void Main(string[] args) {
string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
string sql = @"select FirstName from Employee";
SqlConnection conn = new SqlConnection(connString);
try {
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read()) {
Console.WriteLine("{0}", reader[0]);
}
reader.Close();
}
catch(Exception e)
{
Console.WriteLine("Error Occurred: " + e);
}
finally
{
conn.Close();
}
}
}

</source>

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

Csharp/C Sharp/Database ADO.net/SqlDataReader - История изменений

в 15:31, 26 мая 2010

Admin: 1 версия