|
|
using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Xml;
using System.Xml.XPath;
namespace RetrieveUsingADO
{
class Program
{
static void Main(string[] args)
{
displaySettings();
Console.WriteLine("Executing GatherDataADORead");
GatherDataADORead();
Console.WriteLine("Executing GatherDataADOInsert");
GatherDataADOInsert();
Console.WriteLine("Executing GatherDataADOInsertDirect");
GatherDataADOInsertDirect();
Console.WriteLine("Executing GatherDataXmlReading");
GatherDataXmlReading();
Console.WriteLine("Executing GatherDataADODataSetQuery");
GatherDataADODataSetQuery();
Console.WriteLine("Files created ");
} // end of main
# region GatherData
public static void GatherDataADOInsert()
{
DataSet dataSet = new DataSet();
DataTable dt = new DataTable("ADOInsert");
dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
dataSet.Tables.Add(dt);
DataRow dr;
for (int i = 0; i <= 500; i++)
{
dr = dt.NewRow();
dr[0] = i;
dr[1] = ADOInserting();
dt.Rows.Add(dr);
} //end of for
dataSet.WriteXml("c:\\ADOInsert.xml");
}// end of GatherDataADOInsert
public static void GatherDataADORead()
{
DataSet dataSet = new DataSet();
DataTable dt = new DataTable("ADORead");
dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
dataSet.Tables.Add(dt);
DataRow dr;
for (int i = 0; i <= 500; i++)
{
dr = dt.NewRow();
dr[0] = i;
dr[1] = ADOReading();
dt.Rows.Add(dr);
} //end of for
dataSet.WriteXml("c:\\ADORead.xml");
} //end of GatherDataADORead()
public static void GatherDataADOInsertDirect()
{
DataSet dataSet = new DataSet();
DataTable dt = new DataTable("ADOInsertDirect");
dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
dataSet.Tables.Add(dt);
DataRow dr;
for (int i = 0; i <= 500; i++)
{
dr = dt.NewRow();
dr[0] = i;
dr[1] = ADOInsertingDirect();
dt.Rows.Add(dr);
} //end of for
dataSet.WriteXml("c:\\ADOInsertDirect.xml");
} //end of GatherDataADOInsertDirect()
public static void GatherDataXmlReading()
{
DataSet dataSet = new DataSet();
DataTable dt = new DataTable("csharpxmlReading");
dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
dataSet.Tables.Add(dt);
DataRow dr;
for (int i = 0; i <= 500; i++)
{
dr = dt.NewRow();
dr[0] = i;
dr[1] = xmlReading();
dt.Rows.Add(dr);
} //end of for
dataSet.WriteXml("c:\\csharpxmlReading.xml");
} //end of GatherDataXmlReading()
public static void GatherDataADODataSetQuery()
{
DataSet dataSet = new DataSet();
DataTable dt = new DataTable("ADODataSetQuery");
dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
dataSet.Tables.Add(dt);
DataRow dr;
for (int i = 0; i <= 500; i++)
{
dr = dt.NewRow();
dr[0] = i;
dr[1] = ADODataSetQuery();
dt.Rows.Add(dr);
Console.WriteLine(" Passs {0} Over ", i);
} //end of for
dataSet.WriteXml("c:\\ADODataSetQuery.xml");
} //end of GatherDataXmlReading()
# endregion GatherData
# region ActualFunctions
/// <summary>
/// This function reads from customer table using ado and then sums up the values of the first column.
/// </summary>
/// <returns></returns>
private static long ADOReading()
{
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
stopWatch.Start();
SqlConnection Conn;
String connectionstring;
connectionstring = @"Server=B4-2F-333-F2Z9\SQL2005;" +
"Database=AdventureWorks;" +
"user id=sa;" +
"password=sa;" +
"Connection timeout = 5 ;" +
"Pooling='false';";
Conn = new SqlConnection(connectionstring);
try
{
Conn.Open();
}
catch (Exception ex)
{
// Connection failed
Console.WriteLine(ex.Message);
Console.ReadLine();
return(-1);
}
SqlCommand cmd = new SqlCommand("select * from sales.customer", Conn);
SqlDataReader dr = cmd.ExecuteReader();
double i = 0;
while (dr.Read())
{
i = i + Convert.ToDouble(dr[0]);
}
dr.Close();
cmd.Dispose();
Conn.Close();
//Console.WriteLine(i.ToString());
stopWatch.Stop();
return ((long)(stopWatch.ElapsedTicks / 100)) ;
}//end ADO Reading
/// <summary>
/// This function inserts using ADO into customers table using a stored procedure
/// </summary>
/// <returns></returns>
private static long ADOInserting()
{
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
stopWatch.Start();
SqlConnection Conn;
String connectionstring;
connectionstring = @"Server=B4-2F-333-F2Z9\SQL2005;" +
"Database=AdventureWorks;" +
"user id=sa;" +
"password=sa;" +
"Connection timeout = 5 ;" +
"Pooling='false';";
Conn = new SqlConnection(connectionstring);
try
{
Conn.Open();
}
catch (Exception ex)
{
// Connection failed
Console.WriteLine(ex.Message);
Console.ReadLine();
return (-1);
}
SqlCommand cmd = new SqlCommand("Sales.uspInsertIntoCustomer", Conn);
cmd.CommandType = CommandType.StoredProcedure ;
int runResult = cmd.ExecuteNonQuery();
cmd.Dispose();
Conn.Close();
//Console.WriteLine(i.ToString());
stopWatch.Stop();
return ((long)(stopWatch.ElapsedTicks / 100));
}//end ADO Inserting
/// <summary>
/// This function inserts into customer table without stored procedure using ADO
/// </summary>
/// <returns></returns>
private static long ADOInsertingDirect()
{
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
stopWatch.Start();
SqlConnection Conn;
String connectionstring;
connectionstring = @"Server=B4-2F-333-F2Z9\SQL2005;" +
"Database=AdventureWorks;" +
"user id=sa;" +
"password=sa;" +
"Connection timeout = 5 ;" +
"Pooling='false';";
Conn = new SqlConnection(connectionstring);
try
{
Conn.Open();
}
catch (Exception ex)
{
// Connection failed
Console.WriteLine(ex.Message);
Console.ReadLine();
return (-1);
}
//INSERT into [Sales].[Customer] (TerritoryID , CustomerType)values(10,'I')
SqlCommand cmd = new SqlCommand("insert into sales.customer(TerritoryID , CustomerType) values(10,'I')", Conn);
cmd.CommandType = CommandType.Text;
int runResult = cmd.ExecuteNonQuery();
cmd.Dispose();
Conn.Close();
//Console.WriteLine(i.ToString());
stopWatch.Stop();
return ((long)(stopWatch.ElapsedTicks / 100));
}//end ADOInsertingDirect
/// <summary>
/// This function loads a sample xml file and then appies a condition(xpath) to create a subset. It then sums the values in first column of the subset
/// </summary>
/// <returns></returns>
private static long xmlReading()
{
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
stopWatch.Start();
XPathNavigator nav;
XPathDocument docNav;
XPathNodeIterator NodeIter;
String strExpression;
// Open the XML.
docNav = new XPathDocument(@"c:\LinQInsertDirect.xml");
// Create a navigator to query with XPath.
nav = docNav.CreateNavigator();
//strExpression = "/bookstore/book/title[../price>10.00]";
strExpression = "/NewDataSet/LinQInsertDirect/Pass[../Pass>250]";
// Select the node and place the results in an iterator.
NodeIter = nav.Select(strExpression);
int i = 0 ;
while (NodeIter.MoveNext())
{
i = i + NodeIter.Current.ValueAsInt;
};
stopWatch.Stop();
return ((long)(stopWatch.ElapsedTicks / 100));
}//end xmlReading
/// <summary>
/// This function reads from customer table using ado into s dataset then sums up the values of the first column.
/// </summary>
/// <returns></returns>
private static long ADODataSetQuery()
{
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
stopWatch.Start();
SqlConnection Conn;
String connectionstring;
connectionstring = @"Server=B4-2F-333-F2Z9\SQL2005;" +
"Database=AdventureWorks;" +
"user id=sa;" +
"password=sa;" +
"Connection timeout = 5 ;" +
"Pooling='false';";
SqlDataAdapter dAdapter = new SqlDataAdapter("select * from sales.customer", connectionstring);
dAdapter.TableMappings.Add("sales.customer", "customer");
DataSet ds = new DataSet();
dAdapter.Fill(ds);
DataRow[] customerDataRow = ds.Tables[0].Select( "CustomerID > 250");
double summ = 0 ;
foreach( DataRow tempRow in customerDataRow )
{
summ = summ + Convert.ToDouble(tempRow[0]);
}
stopWatch.Stop();
return ((long)(stopWatch.ElapsedTicks / 100));
}//end ADO Reading
# endregion ActualFunctions
# region Helpers
private static void displaySettings()
{
if (Stopwatch.IsHighResolution)
{
Console.WriteLine("Operations timed using the system's high-resolution performance counter.");
}
else
{
Console.WriteLine("Operations timed using the DateTime class.");
}
long frequency = Stopwatch.Frequency;
Console.WriteLine(" Timer frequency in ticks per second = {0}",
frequency);
long nanosecPerTick = (1000L * 1000L * 1000L) / frequency;
Console.WriteLine(" Timer is accurate within {0} nanoseconds",
nanosecPerTick);
}
# endregion Helpers
}
}
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
Submit an article or tip