Monday 11.7.11

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

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

//create the connection
SqlConnection connection = new SqlConnection(sqlConnectString);

//build the query to count including criteria
string selectText = "SELECT COUNT(*) FROM Person.Contact " + "WHERE LastName Like 'A%'";

//create the command to count the records
SqlCommand command = new SqlCommand(selectText, connection);
//execute the command, storing the results
connection.Open();
int recordCount = (int)command.ExecuteScalar();
connection.Close();

Console.WriteLine("Person.Contact records starting with 'A' = {0}", recordCount);

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

---

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

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

//batch query to retrieve the COUNT of records and all of the records
//in the Person.Contact table as two result sets
string sqlSelect = "SELECT COUNT(*) FROM Person.Contact; " +
"SELECT * FROM Person.Contact;";

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

//create a DataReader on the first result set
connection.Open();
SqlDataReader dr = command.ExecuteReader();

//get and output the record count from the
//SELECT COUNT(*) statement
dr.Read();
Console.WriteLine("Record count, using COUNT(*)={0}", dr.GetInt32(0));

//move to the next result in the batch
dr.NextResult();

int count = 0;
//Iterate over the records in the DataReader
while (dr.Read())
{
count++;
}
Console.WriteLine("Record count, iterating over results = {0}", count);
//close the DataReader.
dr.Close();

//create the stored procedure to use in the DataReader
command = new SqlCommand("Person.GetContacts", connection);
command.CommandType = CommandType.StoredProcedure;
//create the output parameter to return @@ROWCOUNT
command.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output;

//create a dataReader for the result set returned by the stored procedure
dr = command.ExecuteReader();

//process the data in the DataReader

//close the DataReader
dr.Close();
//the output parameter containing the Row count is now available
Console.WriteLine("Record count, using @@ROWCOUNT = {0}", command.Parameters["@RowCount"].Value);
}
Console.WriteLine("\nPress any key to continue.");
Console.ReadKey();
}
}
}

---

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

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

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

int rsNumber;

//SQLbatch using a DataSet
Console.WriteLine("-----DataSet----");

//Fill the DataSet with the result of the batch query
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataSet ds = new DataSet();
da.Fill(ds);

rsNumber = 0;
//Iterate over the result sets in the DataTable collection
foreach (DataTable dt in ds.Tables)
{
Console.WriteLine("Result set: {0}", ++rsNumber);

foreach (DataRow row in dt.Rows)
{
//output the first three fields for each record
Console.WriteLine("{0}, {1}, {2}", row[0], row[1], row[2]);
}
Console.WriteLine(Environment.NewLine);
}

using (SqlConnection connection = new SqlConnection(sqlConnectString))
{
//SQL batch using a DataReader
Console.WriteLine("----DataReader----");

//create the DataReader from the batch query
SqlCommand command = new SqlCommand(sqlSelect, connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader();

rsNumber = 0;
//Iterate over the result sets using the NextResult() method

do
{
Console.WriteLine("Result set: {0}", ++rsNumber);
//Iterate over the rows in the DataReader
while (dr.Read())
{
//output the first three fields for each record
Console.WriteLine("{0}, {1}, {2}", dr[0], dr[1], dr[2]);
}
Console.WriteLine(Environment.NewLine);
} while (dr.NextResult());
}

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