Thursday 12.1.11

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace RetrieveHierarchicalDataSet
{
class Program
{
static void Main(string[] args)
{
string sqlConnectString = @"Data Source=Alfred-PC\SQLExpress;" +
"Integrated security=SSPI; Initial Catalog=AdventureWorks;";

string sqlSelectHeader = "SELECT * FROM Sales.SalesOrderHeader";
string sqlSelectDetail = "SELECT * FROM Sales.SalesOrderDetail";

DataSet ds = new DataSet();
SqlDataAdapter da;

//fill the header table in the DataSet
da = new SqlDataAdapter(sqlSelectHeader, sqlConnectString);
da.FillSchema(ds, SchemaType.Source, "SalesOrderHeader");
da.Fill(ds, "SalesOrderHeader");

//fill the detail table in the dataset
da = new SqlDataAdapter(sqlSelectDetail, sqlConnectString);
da.FillSchema(ds, SchemaType.Source, "SalesOrderDetail");
da.Fill(ds, "SalesOrderDetail");

//relate the header and order tables in the dataset
DataRelation dr = new DataRelation("SalesOrderHeader_SalesOrderDetail", ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
ds.Relations.Add(dr);

//output fields from first three header rows with detail
for (int i = 0; i < 3; i++)
{
DataRow rowHeader = ds.Tables["SalesOrderHeader"].Rows[i];
Console.WriteLine("HEADER: OrderID = {0}, Date = {1}, TotalDue = {2}", rowHeader["SalesOrderID"], rowHeader["OrderDate"], rowHeader["TotalDue"]);
foreach (DataRow rowDetail in rowHeader.GetChildRows(dr))
{
Console.WriteLine("\tDETAIL: OrderID = {0}, DetailID = {1}, " + "LineTotal = {2}",
rowDetail["SalesOrderID"], rowDetail["SalesOrderDetailID"], rowDetail["LineTotal"]);

}
}

Console.ReadLine();
}

}
}

---

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;


namespace RetrieveHierarchicalDataSetBatch
{
class Program
{
static void Main(string[] args)
{
string sqlConnectString = @"Data Source=Alfred-PC\SQLExpress;" +
"Integrated security=SSPI;Initial Catalog=AdventureWorks;";

string sqlSelect = "SELECT * FROM Sales.SalesOrderHeader;" +
"SELECT * FROM Sales.SalesOrderDetail";

DataSet ds = new DataSet();
SqlDataAdapter da;

//fill the dataset with header and detail, mapping the default table names
da = new SqlDataAdapter(sqlSelect, sqlConnectString);
da.TableMappings.Add("Table", "SalesOrderHeader");
da.TableMappings.Add("Table1", "SalesOrderDetail");
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds);

//relate the header and order tables in the dataset
DataRelation dr = new DataRelation("SalesOrderHeader_SalesOrderDetail",
ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
ds.Relations.Add(dr);

//output fields from first three header rows with detail
for (int i = 0; i < 3; i++)
{
DataRow rowHeader = ds.Tables["SalesOrderHeader"].Rows[i];
Console.WriteLine("HEADER: OrderID = {0}, Date = {1}, TotalDue = {2}", rowHeader["SalesOrderID"], rowHeader["OrderDate"], rowHeader["TotalDue"]);

foreach (DataRow rowDetail in rowHeader.GetChildRows(dr))
{
Console.WriteLine("\tDetail: OrderID = {0}, DetailID = {1}," +
"LineTotal = {2}", rowDetail["SalesOrderID"], rowDetail["SalesOrderDetailID"], rowDetail["LineTotal"]);
}
}
Console.ReadLine();
}
}
}

---

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using System.Text;

namespace NavigatingParentChildTables
{
class Program
{
static void Main(string[] args)
{
string sqlConnectString = @"Data Source=Alfred-PC\SQLExpress;" +
"Integrated Security=SSPI;Initial Catalog=AdventureWorks;";

string sqlSelect = "SELECT * FROM Sales.SalesOrderHeader;" +
"SELECT * FROM Sales.SalesOrderDetail";

DataSet ds = new DataSet();
SqlDataAdapter da;

//fill the dataset with header and detail
//mapping the default table names
da = new SqlDataAdapter(sqlSelect, sqlConnectString);
da.TableMappings.Add("Table", "SalesOrderHeader");
da.TableMappings.Add("Table1", "SalesOrderDetail");
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds);

//relate teh header and order tables in the DataSet
DataRelation dr = new DataRelation("SalesOrderHeader_SalesOrderDetail",
ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
ds.Relations.Add(dr);

//output fields from first three header rows with detail
for (int i = 0; i < 3; i++)
{
DataRow rowHeader = ds.Tables["SalesOrderHeader"].Rows[i];
Console.WriteLine("HEADER: OrderID = {0}, Date = {1}, TotalDue = {2}", rowHeader["SalesOrderID"], rowHeader["OrderDate"], rowHeader["TotalDue"]);

foreach (DataRow rowDetail in rowHeader.GetChildRows(dr))
{
Console.WriteLine("\tDETAIL: OrderID = {0}, DetailID = {1}, " +
"LineTotal = {2}", rowDetail["SalesOrderID"], rowDetail["SalesOrderDetailID"], rowDetail["LineTotal"]);
}
}
Console.ReadLine();
}
}
}

---

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace ExecutingParameterizedQuery
{
class Program
{
static void Main(string[] args)
{
//sql server parameterized query
Console.WriteLine("---Data Provider for SQL Server");

string sqlConnectString = @"Data Source=Alfred-PC\SQLExpress;" +
"Integrated Security=SSPI;Initial Catalog=AdventureWorks;";

string sqlSelect = "SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue > @TotalDue";

SqlConnection sqlConnection = new SqlConnection(sqlConnectString);
SqlCommand sqlCommand = new SqlCommand(sqlSelect, sqlConnection);

//add the TotalDue parameter to the command
sqlCommand.Parameters.Add("@TotalDue", SqlDbType.Money);

//set the value of the TotalDue parameter
sqlCommand.Parameters["@TotalDue"].Value = 200000;

//use a dataadapter to retrieve the result set into a DataTable
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCommand);
DataTable sqlDt = new DataTable();
sqlDa.Fill(sqlDt);

foreach (DataRow row in sqlDt.Rows)
{
Console.WriteLine("SalesOrderID = {0}, OrderDate = {1}, TotalDue = {2}", row["SalesOrderID"], row["OrderDate"], row["TotalDUe"]);
}

Console.WriteLine();

//OLE DB parameterized query
Console.WriteLine("---Data Provider for OLE DB");
string oledbConnectString = "Provider=SQLOLEDB;" + @"Data Source=Alfred-PC\SQLExpress;" +
"Integrated security=SSPI;Initial Catalog=AdventureWorks;";

string oledbSelect = "SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue > ?";

OleDbConnection oledbConnection = new OleDbConnection(oledbConnectString);
OleDbCommand oledbCommand = new OleDbCommand(oledbSelect, oledbConnection);

//add the TotalDue parameter to the command
oledbCommand.Parameters.Add("@TotalDue", OleDbType.Currency);

//set the value of the TotalDue parameter
oledbCommand.Parameters["@TotalDue"].Value = 200000;

//use a DataAdapter to retrieve the result set into a DataTable
OleDbDataAdapter oledbDa = new OleDbDataAdapter(oledbCommand);
DataTable oledbDt = new DataTable();
oledbDa.Fill(oledbDt);

foreach (DataRow row in oledbDt.Rows)
{
Console.WriteLine("SalesOrderID = {0}, OrderDate = {1}, TotalDue = {2}", row["SalesOrderID"], row["OrderDate"], row["TotalDue"]);
}

Console.ReadLine();
}
}
}

---

Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick
Dim sb As New StringBuilder()
sb.Append("You chose:
")
sb.Append("Programming Language: ")
sb.Append(lstLanguage.Text)
sb.Append("
Total Employees: ")
sb.Append(txtEmpCount.Text)
sb.Append("
Total Locations: ")
sb.Append(txtLocCount.Text)
sb.Append("
Licenses Required: ")
For Each item As ListItem In lstTools.Items
If item.Selected Then
sb.Append(item.Text)
sb.Append(" ")
End If
Next
sb.Append("")
lblSummary.Text = sb.ToString()
End Sub

---