Software Development

software development notes, main category for Cs, Java, and other programming experiences.

Execute complicated SQL query

Posted on by coder 
   


using System;
using System.Data;
using System.Data.OleDb;

public class ExtractInfo {    
 public static void Main () { 
   String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.Employee.mdb";
   OleDbConnection con = new OleDbConnection(connect);
   con.Open();  
   Console.WriteLine("Made the connection to the Sales database");
   OleDbCommand cmd = con.CreateCommand();
   
   cmd.CommandText = "SELECT DISTINCT CustomerName "
            + "FROM Customer, Item, Orders, OrderItem "
            + "WHERE Customer.CustomerID = Orders.CustomerID "
            + "AND Orders.OrderNumber = OrderItem.OrderNumber "
            + "AND OrderItem.ItemNumber = Item.ItemNumber "
            + "AND Description = 'computer'";
   OleDbDataReader reader = cmd.ExecuteReader();
   while(reader.Read())
     Console.WriteLine(reader.GetString(0));
   reader.Close();



   con.Close();
 }
}

Read result set containing all columns

Posted on by coder 


   

using System;
using System.Data;
using System.Data.OleDb;

public class ExtractInfo {    
 public static void Main () { 
   String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.Employee.mdb";
   OleDbConnection con = new OleDbConnection(connect);
   con.Open();  
   Console.WriteLine("Made the connection to the Sales database");
   OleDbCommand cmd = con.CreateCommand();
   
   cmd.CommandText = "SELECT * FROM Employee";
   OleDbDataReader reader = cmd.ExecuteReader();
   Console.WriteLine();
   Console.WriteLine("ID	FirstName	LastName	Salary");  
   while (reader.Read())
     Console.WriteLine("{0}		{1}		{2}	{3}",reader.GetInt32(0),reader.GetString(1),reader.GetString(2), reader.GetDecimal(3));
   reader.Close();


   con.Close();
 }
}

Query more columns and read result set

Posted on by coder 


   


using System;
using System.Data;
using System.Data.OleDb;

public class ExtractInfo {    
 public static void Main () { 
   String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.Employee.mdb";
   OleDbConnection con = new OleDbConnection(connect);
   con.Open();  
   Console.WriteLine("Made the connection to the Sales database");
   OleDbCommand cmd = con.CreateCommand();
   cmd.CommandText = "SELECT First_Name, Last_Name FROM Employee ORDER BY First_Name"; 
   OleDbDataReader reader = cmd.ExecuteReader();
   Console.WriteLine("First_Name		Last_Name");  
   while(reader.Read()) 
     Console.WriteLine("{0}	{1}",reader.GetString(0), reader.GetString(1));
   reader.Close();
   
   con.Close();
 }
}

OleDbConnection: ServerVersion

Posted on by coder 
   
 

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

class MainClass {
    public static void Main() {
        using (OleDbConnection con = new OleDbConnection()) {
            con.ConnectionString = "Provider=SQLOLEDB;" +     
                @"Data Source=.sqlexpress;" + 
                "Initial Catalog=Northwind;" + 
                "Integrated Security=SSPI";    
            con.Open();

            if (con.State == ConnectionState.Open) {
                Console.WriteLine("  Server Version = " + con.ServerVersion);
            } else {
                Console.WriteLine("OleDbConnection failed to open.");
                Console.WriteLine("  Connection State = " + con.State);
            }
        }
    }
}

OleDbConnection: Database

Posted on by coder 
   
 

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

class MainClass {
    public static void Main() {
        using (OleDbConnection con = new OleDbConnection()) {
            con.ConnectionString = "Provider=SQLOLEDB;" +     
                @"Data Source=.sqlexpress;" + 
                "Initial Catalog=Northwind;" + 
                "Integrated Security=SSPI";    
            con.Open();

            if (con.State == ConnectionState.Open) {
                Console.WriteLine("  Database = " + con.Database);
            } else {
                Console.WriteLine("OleDbConnection failed to open.");
                Console.WriteLine("  Connection State = " + con.State);
            }
        }
    }
}

OleDbConnection: DataSource

Posted on by coder 
   
 

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

class MainClass {
    public static void Main() {
        using (OleDbConnection con = new OleDbConnection()) {
            con.ConnectionString = "Provider=SQLOLEDB;" +     
                @"Data Source=.sqlexpress;" + 
                "Initial Catalog=Northwind;" + 
                "Integrated Security=SSPI";    

            con.Open();

            if (con.State == ConnectionState.Open) {
                Console.WriteLine("  Database Source = " + con.DataSource);
            } else {
                Console.WriteLine("OleDbConnection failed to open.");
                Console.WriteLine("  Connection State = " + con.State);
            }
        }
    }
}

OleDbConnection: ConnectionString property

Posted on by coder 
   
 

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

class MainClass {
    public static void Main() {
        using (OleDbConnection con = new OleDbConnection()) {
            con.ConnectionString = "Provider=SQLOLEDB;" +     
                @"Data Source=.sqlexpress;" + 
                "Initial Catalog=Northwind;" + 
                "Integrated Security=SSPI";    

            con.Open();

            if (con.State == ConnectionState.Open) {
                Console.WriteLine("  Connection String = " +
                    con.ConnectionString);
            } else {
                Console.WriteLine("OleDbConnection failed to open.");
                Console.WriteLine("  Connection State = " + con.State);
            }
        }
    }
}