Tuesday 8.24.11

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

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

string sqlSelect = "SELECT TOP 10 EmployeeID, Title, Gender, HireDate FROM HumanResources.Employee";

SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);

//create a dtable mapping to map the default table name 'Table'
DataTableMapping dtm = da.TableMappings.Add("Table", "mappedContact");

//create column mappings
dtm.ColumnMappings.Add("EmployeeID", "mappedEmployeeID");
dtm.ColumnMappings.Add("Title", "mappedTitle");
dtm.ColumnMappings.Add("Gender", "mappedGender");
dtm.ColumnMappings.Add("HireDate", "mappedHireDate");

//create and fill the DataSet
DataSet ds = new DataSet();
da.Fill(ds);

Console.WriteLine("DataTable name = {0}", ds.Tables[0].TableName);

foreach (DataColumn col in ds.Tables["mappedContact"].Columns)
{
Console.WriteLine("\tDataColumn {0} name = {1}", col.Ordinal, col.ColumnName);
}

Console.WriteLine();

foreach (DataRow row in ds.Tables["mappedContact"].Rows)
{
Console.WriteLine("EmployeeID = {0} \t Title = {1} \n Gender = {2} \t HireDate = {3} ", row["mappedEmployeeID"], row["mappedTitle"], row["mappedGender"],
row["mappedHireDate"]);
Console.WriteLine();
}
}
}
}

---

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

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

string sqlSelect = "SELECT TOP 5 ContactID, FirstName, MiddleName, LastName FROM Person.Contact";

int contactID;
string firstName, middleName, lastName;

//create the connection and the command
SqlConnection connection = new SqlConnection(sqlConnectString);
SqlCommand command = new SqlCommand(sqlSelect, connection);

//Open the connection and build the DataReader
connection.Open();

using (SqlDataReader dr = command.ExecuteReader())
{
Console.WriteLine("--Cast value retrieved by Ordinal---");
while (dr.Read())
{
contactID = Convert.ToInt32(dr[0]);
firstName = Convert.ToString(dr[1]);
middleName = Convert.ToString(dr[2]);
lastName = Convert.ToString(dr[3]);

Console.WriteLine("{0}\t{1}, {2} {3}", contactID, lastName, firstName, middleName);
}
}

using (SqlDataReader dr = command.ExecuteReader())
{
//Get values from the DataReader using generic typed accessors.
Console.WriteLine("\n----Generic typed accessors----");
while (dr.Read())
{
contactID = dr.GetInt32(0);
firstName = dr.GetString(1);
middleName = dr.IsDBNull(2) ? null : dr.GetString(2);
lastName = dr.GetString(3);

Console.WriteLine("{0}\t{1}, {2} {3}", contactID, lastName, firstName, middleName);
}
}

using (SqlDataReader dr = command.ExecuteReader())
{
//get values from the DataReader using SQL Server
//specific typed accessors
Console.WriteLine("\n---SQL Server specific typed accessors---");
while (dr.Read())
{
contactID = (int)dr.GetSqlInt32(0);
firstName = (string)dr.GetSqlString(1);
middleName = dr.IsDBNull(2) ? null : (string)dr.GetSqlString(2);
lastName = (string)dr.GetSqlString(3);

Console.WriteLine("{0}\t{1}, {2} {3}", contactID, lastName, firstName, middleName);
}
}

Console.WriteLine("\nPress any key to continue.");
Console.ReadKey();
}
}
}

---

Public Class Recipe02_07

Public Shared Sub Main(ByVal args As String())
'1st January 1975 at 00:00:00
Dim dt1 As DateTime = DateTime.Parse("Jan 1975")

'12th January 1975 at 18:19:00
Dim dt2 As DateTime = DateTime.Parse("Sunday 12 January 1975 18:19:00")

'12th January 1975 at 00:00:00
Dim dt3 As DateTime = DateTime.Parse("1,12,1975")

'12th January 1975 at 18:19:00
Dim dt4 As DateTime = DateTime.Parse("1/12/1975 18:19:00")

'Current Date at 18:19 showitng UTC offset for local time zone
Dim dt5 As DateTimeOffset = DateTimeOffset.Parse("6:19 PM")

'Current Date at 18:19 showing an offset of -8 hours from UTC
Dim dt6 As DateTimeOffset = DateTimeOffset.Parse("6:19 PM -8")

'Date set to minvalue to be used later by TryPares
Dim dt7 As DateTime = DateTime.MinValue

'Display the converted DateTime objects
Console.WriteLine("Jan 1975")
Console.WriteLine(dt1)
Console.WriteLine()
Console.WriteLine("Sunday 12 January 1975 18:19:00")
Console.WriteLine(dt2)
Console.WriteLine()
Console.WriteLine("1,12,1975")
Console.WriteLine(dt3)
Console.WriteLine()
Console.WriteLine("1/12/1975 18:19:00")
Console.WriteLine(dt4)
Console.WriteLine()
Console.WriteLine(dt5)
Console.WriteLine()
Console.WriteLine(dt6)
Console.WriteLine()

'try to parse a nondatetime string.
If Not DateTime.TryParse("THis is an invalid date", dt7) Then
Console.WriteLine("Unable to parse.")
Else
Console.WriteLine(dt7)
End If

'Parse only string scontaining LongTimePattern
Dim dt8 As DateTime = DateTime.ParseExact("6:19:00 PM", "h:mm:ss tt", Nothing)


'Parse only strings containing MonthDayPattern.
Dim dt10 As DateTime = DateTime.ParseExact("January 12", "MMMM dd", Nothing)

'display the cnoverted DateTime objects.
Console.WriteLine(dt8)
'Console.WriteLine(dt9)
Console.WriteLine(dt10)

'wait to continue
Console.WriteLine(vbCrLf & "Main method complete. Press Enter")
Console.ReadLine()


End Sub

End Class

---

Imports System
Imports System.IO

Namespace Apress.VisualBasicRecipes.Chapter05
Public Class Recipe05_01

Public Shared Sub Main()
Dim keepGoing = 1

Do
Console.WriteLine("Please enter a file name, or enter Q to exit")

Dim entry = Console.ReadLine()

If (entry <> "Q") Then
Dim File As FileInfo = New FileInfo(entry)


Console.WriteLine("Checking file: " & File.Name)
Console.WriteLine("File exists: " & File.Exists.ToString)

If File.Exists Then
Console.WriteLine("File created: ")
Console.WriteLine(File.CreationTime.ToString)
Console.Write("File last updated: ")
Console.WriteLine(File.LastWriteTime.ToString)
Console.Write("File last accessed: ")
Console.WriteLine(File.LastAccessTime.ToString)
Console.Write("File size: ")
Console.WriteLine(File.Length.ToString)
Console.Write("File attribute list: ")
Console.WriteLine(File.Attributes.ToString)
End If
Console.WriteLine()

'Display directory information
Dim dir As DirectoryInfo = File.Directory

Console.WriteLine("Checking directory: " & dir.Name)
Console.WriteLine("In directory: " & dir.Parent.Name)
Console.Write("Directory exists: ")
Console.WriteLine(dir.Exists.ToString)

If dir.Exists Then
Console.Write("Directory created: ")
Console.WriteLine(dir.CreationTime.ToString)
Console.Write("Directory last updated: ")
Console.WriteLine(dir.LastWriteTime.ToString)
Console.Write("Directory last accessed: ")
Console.WriteLine(dir.LastAccessTime.ToString)
Console.Write("Directory attribute list: ")
Console.WriteLine(File.Attributes.ToString)
Console.Write("Directory contains: ")
Console.WriteLine(dir.GetFiles().Length.ToString & " files")
End If
Console.WriteLine("Press any key to continue")

'display drive information.
Dim drv As DriveInfo = New DriveInfo(File.FullName)

Console.WriteLine("Drive: ")
Console.WriteLine(drv.Name)

If drv.IsReady Then
Console.Write("Drive type: ")
Console.WriteLine(drv.DriveType.ToString)
Console.Write("Drive format: ")
Console.WriteLine(drv.DriveFormat.ToString)
Console.Write("Drive free space: ")
Console.WriteLine(drv.AvailableFreeSpace.ToString)
End If


Console.ReadLine()
Else
keepGoing = 0
End If
Loop Until (keepGoing = 0)
End Sub

End Class
End Namespace