Friday 10.14.11

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

namespace RetrieveSingleValueFromQuery
{
class Program
{
//the ExecuteScalar() method of the Command object returns a single value from the dta soruce rather than a collection of records as
//a table or data stream
static void Main(string[] args)
{
string sqlConnectString = @"Data Source=Alfred-PC\SQLExpress; Integrated security=SSPI; Initial Catalog=AdventureWorks;";

string sqlSelect = "SELECT COUNT(*) FROM Person.Contact";

SqlConnection connection = new SqlConnection(sqlConnectString);

//create the scalar command and open the connection
SqlCommand command = new SqlCommand(sqlSelect, connection);
connection.Open();

//execute the scalar SQl statement and store resuts.
int count = Convert.ToInt32(command.ExecuteScalar());
connection.Close();

Console.WriteLine("Record count in Person.Contact = {0}", count);

Console.WriteLine();
Console.ReadLine();
}
}
}

---

using System;
using System.Data.SqlClient;

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

string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact " +
"WHERE ContactID BETWEEN 10 and 14";


SqlConnection connection = new SqlConnection(sqlConnectString);

//create the command and open the conneciton
SqlCommand command = new SqlCommand(sqlSelect, connection);
connection.Open();

//create the DataReader to retrieve data
using (SqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
//output fields from DataReader row
Console.WriteLine("ContactID = {0}\tFirstName = {1}\tLastName = {2}", dr["ContactID"], dr["LastName"], dr["FirstName"]);
}
}
connection.Close();
}
}
}

---

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

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

string sqlSelect = @"SELECT ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt FROM Person.Contact";

SqlConnection connection = new SqlConnection(sqlConnectString);

//create the command and open the connection
SqlCommand command = new SqlCommand(sqlSelect, connection);
connection.Open();

SqlDataReader dr = command.ExecuteReader();
dr.Read();

//output fields from the first DataRow reader using different techniques
Console.WriteLine("ContactID = {0}", dr[0]);
Console.WriteLine("Title = {0}", dr["Title"]);
Console.WriteLine("FirstName = {0}", dr.IsDBNull(3) ? "NULL" : dr.GetString(3));
Console.WriteLine("MiddleName = {0}", dr.IsDBNull(4) ? "NULL" : dr.GetSqlString(4));
Console.WriteLine("LastName = {0}", dr.IsDBNull(5) ? "NULL" : dr.GetSqlString(5).Value);
Console.WriteLine("EmailAddress = {0}", dr.GetValue(7));
Console.WriteLine("EmailPromotion = {0}", int.Parse(dr["EmailPromotion"].ToString()));

//get the column ordinal for the Phone attribute and use it to output the column
int coPhone = dr.GetOrdinal("Phone");
Console.WriteLine("Phone = {0}", dr[coPhone]);

//get the column name for the PasswordHash attribute
//and use it to output the column
string cnPasswordHash = dr.GetName(10);
Console.WriteLine("PasswordHash = {0}", dr[cnPasswordHash]);

//create an object array and load the row into it
object[] o = new object[dr.FieldCount];
//output the passwordsalt attribute from the object array
dr.GetValues(o);
Console.WriteLine("PasswordSalt = {0}", o[11].ToString());
}
}
}