Csharp/CSharp Tutorial/ADO.Net/SqlCommand Select

CommandReader

using System;
using System.Data;
using System.Data.SqlClient;
    class CommandReader
    {
        static void Main()
        {
            SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind");
            string sql = @"select firstname,lastname from employees";
            SqlCommand cmd = new SqlCommand(sql, conn);
            Console.WriteLine("Command created and connected.");
            try
            {
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Console.WriteLine("Employee name: {0} {1}",
                       rdr.GetValue(0),
                       rdr.GetValue(1)
                    );
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                conn.Close();
                Console.WriteLine("Connection Closed.");
            } 
        }
    }

Execute Scalar Example

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
class Program {
    static void Main(string[] args) {
        SqlConnection thisConnection = new
        SqlConnection(@"Server=(local)\sqlexpress;Integrated Security=True;" +
                  "Database=northwind");
        thisConnection.Open();
        SqlCommand thisCommand = thisConnection.CreateCommand();
        thisCommand.rumandText = "SELECT COUNT(*) FROM Customers";
        Object countResult = thisCommand.ExecuteScalar();
        Console.WriteLine("Count of Customers = {0}", countResult);
        thisConnection.Close();
    }
}

Execute the command to get Scalar value

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            SqlConnection connection = new SqlConnection(sqlConnectString);
            string selectText = "SELECT COUNT(*) FROM Person.Contact";
            SqlCommand command = new SqlCommand(selectText, connection);
            connection.Open( );
            int recordCount = (int)command.ExecuteScalar( );
            connection.Close( );
            Console.WriteLine(recordCount);
        }
    }

Executing a Query That Does Not Return a Result Set

/*
CREATE TABLE MyTable(
    Id int NOT NULL PRIMARY KEY,
    Field1 nvarchar(50) NOT NULL,
    Field2 nvarchar(50) NOT NULL,
)
INSERT INTO MyTable VALUES (1, "Field 1.1", "Field 2.1");
INSERT INTO MyTable VALUES (2, "Field 1.2", "Field 2.2");
INSERT INTO MyTable VALUES (3, "Field 1.3", "Field 2.3");
*/
using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=MyDatabase;";
            string sqlDelete = "DELETE FROM MyTable WHERE Id = 2";
            SqlConnection connection = new SqlConnection(sqlConnectString);
            SqlCommand command = new SqlCommand(sqlDelete, connection);
            connection.Open( );
            int rowsAffected = command.ExecuteNonQuery( );
            Console.WriteLine("{0} row(s) affected.", rowsAffected);
            Console.WriteLine("Record with Id = 2 deleted.");
            connection.Close( );
        }
    }

Executing a Query That Returns Multiple Result Sets with DataSet

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT TOP 3 * FROM Sales.SalesOrderHeader;" +
                "SELECT TOP 3 * FROM Sales.SalesOrderDetail";
            int rsNumber;
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataSet ds = new DataSet( );
            da.Fill(ds);
            rsNumber = 0;
            foreach (DataTable dt in ds.Tables){
                Console.WriteLine("Result set: {0}", ++rsNumber);
                foreach (DataRow row in dt.Rows){
                    Console.WriteLine("{0}, {1}, {2}", row[0], row[1], row[2]);
                }
            }
        }
    }

Executing a Query That Returns Multiple Result Sets with SqlDataReader

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT TOP 3 * FROM Sales.SalesOrderHeader;" +
                "SELECT TOP 3 * FROM Sales.SalesOrderDetail";
            int rsNumber;
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataSet ds = new DataSet( );
            da.Fill(ds);
            rsNumber = 0;
            using (SqlConnection connection = new SqlConnection(sqlConnectString)){
                SqlCommand command = new SqlCommand(sqlSelect, connection);
                connection.Open( );
                SqlDataReader dr = command.ExecuteReader( );
                rsNumber = 0;
                do{
                    Console.WriteLine("Result set: {0}", ++rsNumber);
                    while (dr.Read( )){
                        Console.WriteLine("{0}, {1}, {2}", dr[0], dr[1], dr[2]);
                    }
                    Console.WriteLine(Environment.NewLine);
                } while (dr.NextResult( ));
            }
        }
    }

Get table list

using System;
using System.Data.SqlClient;
public class GetTableList
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
        string SQL = "SELECT TABLE_TYPE, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(SQL, con);
        SqlDataReader r;
        con.Open();
        r = cmd.ExecuteReader();
        while (r.Read()){
           Console.WriteLine(r[0] + ": " + r[1]);
        }
        con.Close();
    }
}

How to perform a SELECT statement using ADO.NET

using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
  public static void Main()
  {
    string connectionString ="server=localhost;database=Northwind;uid=sa;pwd=sa";
    SqlConnection mySqlConnection = new SqlConnection(connectionString);
    string selectString = "SELECT ID, FirstName FROM Employee";
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.rumandText = selectString;
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    string dataTableName = "Employee";
    mySqlDataAdapter.Fill(myDataSet, dataTableName);
    DataTable myDataTable = myDataSet.Tables[dataTableName];
    foreach (DataRow myDataRow in myDataTable.Rows)
    {
      Console.WriteLine("ID = " + myDataRow["ID"]);
      Console.WriteLine("FirstName = " + myDataRow["FirstName"]);
    }
    mySqlConnection.Close();
  }
}

Simple Query

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
class MainClass
{
  [STAThread]
  static void Main(string[] args)
  {
    string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
    using ( SqlConnection conn = new SqlConnection( cstr ) )
    {
      conn.Open();
      SqlCommand cmd = new SqlCommand( "select * from Employee", conn );
      SqlDataReader rdr = cmd.ExecuteReader();
      while ( rdr.Read() )
      {
        System.Console.WriteLine( "{0}", rdr.GetString( 1 ) );
      }
      rdr.Close();
    }
  }
}