using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
//This C# example uses the DataReader to read some of the columns from the Northwind Orders table.
//SqlTypes are used to retrieve the values.
public class DataReaderSample
{
public static void Main()
{
// Use the default SQL Server on this machine.
// Change the values in myConnectionString for user id
// and password.
string myConnectionString =
"server=(local);Persist Security Info=False;Integrated Security=SSPI;initial catalog=northwind";
// Query string to get some records from Orders table
string myQuery = "SELECT OrderID, CustomerID, " +
"OrderDate, Freight, ShipName FROM Orders";
// First column OrderID is int datatype in SQL Server
// and maps to SQLInt32
SqlInt32 oOrderID;
// Second column CustomerID is nchar in SQL Server
// and maps to SQLString
SqlString oCustomerID;
// Third column OrderDate is datetime in SQL Server
// and maps to SQLDateTime
SqlDateTime oOrderDate;
// Fourth column Freight is money in SQL Server and
// maps to SQLMoney
SqlMoney oFreight;
// Fifth column ShipName is nvarchar in SQL Server
// and maps to SQLString
SqlString oShipName;
//Date to compare against order date.
SqlDateTime oMergerDate = new SqlDateTime(1997,11,1);
string sDivision;
//Connect and do the query
SqlConnection myConnection = new SqlConnection(myConnString);
SqlCommand myCommand = new SqlCommand(myQuery,myConnection);
myConnection.Open();
SqlDataReader myDataReader;
myDataReader = myCommand.ExecuteReader();
// Read the rows from the query result.
while (myDataReader.Read())
{
//Get the columns in the row as SqlTypes
oOrderID = myDataReader.GetSqlInt32(0);
oCustomerID = myDataReader.GetSqlString(1);
oOrderDate = myDataReader.GetSqlDateTime(2);
oFreight = myDataReader.GetSqlMoney(3);
oShipName = myDataReader.GetSqlString(4);
//Do something with the data...we just do one
//comparison and print the values.
//Compare the OrderDate with oMergerDate
if (oMergerDate > oOrderDate)
sDivision = "A";
else
sDivision = "B";
Console.Write(sDivision + ", ");
Console.Write(oOrderID + ", ");
Console.Write(oCustomerID + ", ");
Console.Write(oOrderDate + ", ");
Console.Write(oFreight + ", ");
Console.Write(oShipName);
Console.WriteLine();
}
// Always call Close when done reading.
myDataReader.Close();
// Close the connection when done with it.
myConnection.Close();
}
}
Imports System
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Sub Main()
Try
' Use the default SQL Server on this machine.
' Change the values in myConnectionString for user id
' and password.
Dim myConnectionString As String
myConnectionString = _
"server=(local);Persist Security Info=False;Integrated Security=SSPI; _
initial catalog=northwind"
' Query string to get some records from Orders table
Dim myQuery As String
myQuery = "SELECT OrderID, CustomerID, " & _
"OrderDate, Freight, ShipName FROM Orders"
' First column OrderID is int datatype in SQL Server
' and maps to SQLInt32
Dim orderID As SqlInt32
' Second column CustomerID is nchar in SQL Server
' and maps to SQLString
Dim customerID As SqlString
' Third column OrderDate is datetime in SQL Server
' and maps to SQLDateTime
Dim orderDate As SqlDateTime
' Fourth column Freight is money in SQL Server and
' maps to SQLMoney
Dim freight As SqlMoney
' Fifth column ShipName is nvarchar in SQL Server
' and maps to SQLString
Dim shipName As SqlString
' Date to compare against order date.
Dim mergerDate As SqlDateTime = New SqlDateTime(1997, 11, 1)
Dim division As String
' Connect to database and perform query.
Dim myConnection As SqlConnection = New SqlConnection(myConnectionString)
Dim myCommand As SqlCommand = New SqlCommand(myQuery, myConnection)
myConnection.Open()
Dim myDataReader As SqlDataReader
myDataReader = myCommand.ExecuteReader
' Read the rows from the query result.
While myDataReader.Read
' Get the columns in the row as SqlTypes
orderID = myDataReader.GetSqlInt32(0)
customerID = myDataReader.GetSqlString(1)
orderDate = myDataReader.GetSqlDateTime(2)
freight = myDataReader.GetSqlMoney(3)
shipName = myDataReader.GetSqlString(4)
' Do something with the data...we just do one
' comparison and print the values.
' Compare the OrderDate with oMergerDate
If mergerDate.Value > orderDate.Value Then
division = "A"
Else
division = "B"
End If
Console.Write(division & ", ")
Console.Write(orderID.ToString & ", ")
Console.Write(customerID.ToString & ", ")
Console.Write(orderDate.ToString & ", ")
Console.Write(freight.ToString & ", ")
Console.Write(shipName)
Console.WriteLine()
End While
' Always call Close when done reading.
myDataReader.Close()
' Close the connection when done with it.
myConnection.Close()
Catch e As Exception
Console.WriteLine("Exception: {0}", e.ToString)
End Try
End Sub