Yeni başlayanlar ve Foxpro 2.x'ten geçenler için VFP (Sayfa 2 / 2)

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();

  }

}

using System;

using System.Data;

using System.Data.SqlClient;

 

class test

{

  static void Main()

 {

    string strCreate = 

@"CREATE TABLE #ulkeler (ulke varchar(20))

INSERT INTO #ulkeler VALUES ('canada')

INSERT INTO #ulkeler VALUES ('france')

INSERT INTO #ulkeler VALUES ('germany')";

 

   string strByCountry = 

@"select * from HumanResources.vEmployee vEmp

inner join #ulkeler iVal 

on vEmp.countryRegionName = iVal.ulke";

 

    string strByID = 

@"CREATE TABLE #employees (empid int)

INSERT INTO #employees VALUES (3)

INSERT INTO #employees VALUES (5)

INSERT INTO #employees VALUES (7)

INSERT INTO #employees VALUES (8)

INSERT INTO #employees VALUES (10)

INSERT INTO #employees VALUES (282)

INSERT INTO #employees VALUES (286)

 

select * from HumanResources.vEmployee vEmp

inner join #employees iVal 

on vEmp.employeeID = iVal.empid";

 

   SqlConnection sConn = new SqlConnection(

       @"Data Source=.\sqlexpress;integrated Security=sspi;Database=AdventureWorks");

 

   sConn.Open();

   SqlCommand cmdCreate    = new SqlCommand(strCreate, sConn);

   SqlCommand cmdByCountry = new SqlCommand(strByCountry, sConn);

   SqlCommand cmdByID  = new SqlCommand(strByID, sConn);

 

   cmdCreate.ExecuteNonQuery();

   ShowResult(cmdByCountry.ExecuteReader());

 

   ShowResult(cmdByID.ExecuteReader());

 

// iki temp dosyayi kullan

   SqlCommand cmdAdd = new SqlCommand("INSERT INTO #ulkeler VALUES ('united states')", sConn);

   cmdAdd.ExecuteNonQuery();

   SqlCommand cmdMix  = 

      new SqlCommand(

@"select * from HumanResources.vEmployee vEmp

inner join #employees e on vEmp.employeeID = e.empid

inner join #ulkeler u on vEmp.countryRegionName = u.ulke"

      ,sConn);

   ShowResult(cmdMix.ExecuteReader());

 

   sConn.Close();

 

 }

 

 private static void ShowResult(SqlDataReader rdr)

 {

   while (rdr.Read())

   {

     Console.WriteLine("{0}\t{1,-15}\t{2,-20}\t{3,-15}\t{4}",

       rdr["employeeID"],

       rdr["firstName"],rdr["lastName"],

       rdr["city"], rdr["countryRegionName"] );

   }

   rdr.Close();

   Console.WriteLine("".PadRight(80, '-'));

 }

}