using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections.Generic;
class SQLtest
{
static string conString =
@"Data Source=.\sqlexpress;integrated Security=sspi;Database=AdventureWorks";
static void Main()
{
ParameterTest1();
ParameterTest2();
}
private static void ParameterTest1()
{
Console.WriteLine("test1-SQL");
SqlConnection sConn = new SqlConnection(conString);
string strSelect =
@"select * from HumanResources.vEmployee where City = @city and JobTitle like @jobTitle";
SqlCommand cmd = new SqlCommand(strSelect, sConn);
cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = "seattle";
cmd.Parameters.Add("@jobTitle", SqlDbType.VarChar).Value = "production supervisor%";
ShowEmp<SqlConnection,SqlCommand>(sConn, cmd,
"employeeID","firstName","lastName","JobTitle","city","countryRegionName");
cmd.Parameters["@jobTitle"].Value = "Production Technician - WC40";
ShowEmp<SqlConnection,SqlCommand>(sConn, cmd,
"employeeID","firstName","lastName","JobTitle","city","countryRegionName");
}
private static void ParameterTest2()
{
Console.WriteLine("test2-OLEDB");
OleDbConnection sConn = new OleDbConnection("Provider=SQLNCLI;"+conString);
string strSelect =
@"select * from HumanResources.vEmployee where City = ? and JobTitle like ?";
OleDbCommand cmd = new OleDbCommand(strSelect, sConn);
cmd.Parameters.Add("@city", OleDbType.VarChar).Value = "seattle";
cmd.Parameters.Add("@jobTitle", OleDbType.VarChar).Value = "production supervisor%";
ShowEmp<OleDbConnection,OleDbCommand>(sConn, cmd,
"employeeID","firstName","lastName","JobTitle","city","countryRegionName");
cmd.Parameters["@jobTitle"].Value = "Production Technician - WC40";
ShowEmp<OleDbConnection,OleDbCommand>(sConn, cmd,
"employeeID","firstName","lastName","JobTitle","city","countryRegionName");
}
private static void ShowEmp<CON,CMD>(CON con, CMD cmd,
string f1, string f2, string f3, string f4, string f5, string f6)
where CON: DbConnection
where CMD: DbCommand
{
con.Open();
IDataReader rdr = (IDataReader)cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}",
rdr[f1],rdr[f2],rdr[f3],
rdr[f4],rdr[f5],rdr[f6] );
}
rdr.Close();
con.Close();
Console.WriteLine("Press enter to continue");
Console.ReadLine();
}
}