Friday 10.21.11

using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace RetrieveDataIntoDataTable
{
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 FirstName, LastName FROM Person.Contact";

//create a data adapter
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);

//fill a data table using DataAdapter and output to console
DataTable dt = new DataTable();
da.Fill(dt);

Console.WriteLine("---DataTable---");
foreach (DataRow row in dt.Rows)
Console.WriteLine("{0} {1}", row[0], row["LastName"]);

//fill a DataSet using DataAdapter and output to console
DataSet ds = new DataSet();
da.Fill(ds, "Contact");

Console.WriteLine("\n---DataSet; DataTable count = {0}---", ds.Tables.Count);
Console.WriteLine("[TableName = {0}]", ds.Tables[0].TableName);
foreach (DataRow row in ds.Tables["Contact"].Rows)
Console.WriteLine("{0} {1}", row[0], row[1]);

}
}
}

---

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


namespace RetrieveValuesDataTable
{
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";

//create a data adapter
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);

//fill a DataTable using DataAdapter and output to console
DataTable dt = new DataTable();
da.Fill(dt);

//accessing rows using indexer
Console.WriteLine("---Index loop over DataRowCollection---");
for (int i = 0; i < 5; i++)
{
DataRow row = dt.Rows[i];
Console.WriteLine("Row = {0}\tContactID = {1}\tFirstName = {2}\tLastName={3}", i, row[0], row["FirstName"], row[2, DataRowVersion.Default]);
}

//accessing rows using foreach loop
Console.WriteLine("\n--foreach loop over DataRowCollection---");
int j = 0;
foreach (DataRow row in dt.Rows)
{
j++;
Console.WriteLine("Row = {0}\tContactID = {1}\tFirstName = {2}\tLastName = {3}", j, row[0], row["FirstName"], row["LastName", DataRowVersion.Default]);
}

//accessing DataTable values directly
Console.WriteLine("\n---Accessing FirstName value in row 3 (ContactID = 12) directly ----");
Console.WriteLine("FirstName = {0}", dt.Rows[2][1]);
Console.WriteLine("FirstName = {0}", dt.Rows[2]["FirstName"]);
Console.WriteLine("FirstName = {0}", dt.Rows[2]["FirstName", DataRowVersion.Default]);
Console.WriteLine("FirstName = {0}", dt.Rows[2][dt.Columns[1]]);
Console.WriteLine("FirstName = {0}", dt.Rows[2][dt.Columns["FirstName"]]);
Console.WriteLine("FirstName = {0}", dt.Rows[2].Field(1));
Console.WriteLine("FirstName = {0}", dt.Rows[2].Field("FirstName"));
Console.WriteLine("FirstName = {0}", dt.Rows[2].Field(dt.Columns[1]));

}
}
}

---

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

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

string sqlSelect = "SELECT * FROM Person.Contact";
string sqlSelectEmpty = "SELECT * FROM Person.Contact WHERE ContactID = 0";

Console.WriteLine("---QUERY RETURNS NON-EMPTY RESULT SET---");
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataTable dt = new DataTable();
da.Fill(dt);
Console.WriteLine("DataTable has records = {0}", dt.Rows.Count > 0);

using (SqlConnection connection = new SqlConnection(sqlConnectString))
{
SqlCommand command = new SqlCommand(sqlSelect, connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader();

Console.WriteLine("DataReader has records using HasRows property = {0}", dr.HasRows);

Console.WriteLine("DataReader has records using Read() method = {0}", dr.Read());

dr.Close();
}

Console.WriteLine();
Console.WriteLine("---QUERY RETURNS EMPTY RESULT SET---");
da = new SqlDataAdapter(sqlSelectEmpty, sqlConnectString);
dt = new DataTable();
da.Fill(dt);
Console.WriteLine("DataTable has records = {0}", dt.Rows.Count > 0);

using (SqlConnection connection = new SqlConnection(sqlConnectString))
{
SqlCommand command = new SqlCommand(sqlSelectEmpty, connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader();

Console.WriteLine("DataReader has records using HasRows property = {0}", dr.HasRows);

Console.WriteLine("DataReader has records using Read() method = {0}", dr.Read());

dr.Close();
}

}
}
}

---

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Partial Class DataReader
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'create the command and the connection objects
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("NorthWind").ConnectionString
Dim con As New SqlConnection(connectionString)
Dim sql As String = "SELECT * FROM Employees"
Dim cmd As New SqlCommand(sql, con)

con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()

'the read method also returns a Boolean value indicating whether there are more rows to read

Dim htmlStr As New StringBuilder("")
Do While reader.Read()
htmlStr.Append("

")
' htmlStr.Append(reader("Title of Courtesy"))
htmlStr.Append(" ")
htmlStr.Append(reader.GetString(1))
htmlStr.Append(" ")
htmlStr.Append(reader.GetString(2))
htmlStr.Append(" - employee from ")
htmlStr.Append(reader.GetDateTime(6).ToString("d"))
htmlStr.Append("

")
Loop

reader.Close()
con.Close()
HtmlContent.Text = htmlStr.ToString()
End Sub
End Class

---

Imports System.Web.Configuration
Imports System.Data
Imports System.Data.SqlClient

Partial Class ExecuteScalar
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString
Dim con As New SqlConnection(connectionString)
Dim sql As String = "SELECT COUNT(*) FROM Employees"
Dim cmd As New SqlCommand(sql, con)

'open the connection & get the COUNT(*) value
con.Open()
Dim numEmployees As Integer = CInt(cmd.ExecuteScalar())
con.Close()

'display the information
HtmlContent.Text = "Total Employees: " & numEmployees.ToString() & "

"

End Sub
End Class