Author: coder

Get column index from SqlDataReader

Posted on by coder 
   

    using System;
    using System.Data.SqlClient;
  
  class ConnectToSqlConnection {
    static void Main(string[] args)  {
      String sConn = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";

        String sSQL = "select id, firstname, lastname from Employee";

      SqlConnection oConn = new SqlConnection(sConn);
      oConn.Open();

      SqlCommand oCmd = new SqlCommand(sSQL, oConn);
      SqlDataReader oReader = oCmd.ExecuteReader();

      int idxID = oReader.GetOrdinal("id");
      int idxFirstName = oReader.GetOrdinal("firstname");
      int idxLastName = oReader.GetOrdinal("lastname");

      while(oReader.Read()) {
        Console.WriteLine("{0} {1} {2}",
          oReader.GetValue(idxID),
          oReader.GetValue(idxFirstName),
          oReader.GetValue(idxLastName));
      }
    }
  }

Execute multiple SELECT statements using a SqlCommand object and read the results using a SqlDataReader object

Posted on by coder 
   


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

class ExecuteSelect
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.CommandText =
      "SELECT TOP 5 ProductID, ProductName " +
      "FROM Products " +
      "ORDER BY ProductID;" +
      "SELECT TOP 3 CustomerID, CompanyName " +
      "FROM Customers " +
      "ORDER BY CustomerID;" +
      "SELECT TOP 6 OrderID, CustomerID " +
      "FROM Orders " +
      "ORDER BY OrderID;";

    mySqlConnection.Open();

    SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();

    do
    {
      while (mySqlDataReader.Read())
      {
        Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]);
        Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]);
      }
      Console.WriteLine("");
    } while (mySqlDataReader.NextResult());

    mySqlDataReader.Close();
    mySqlConnection.Close();
  }
}

Update database using the SqlDataAdapter

Posted on by coder 
   


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

class Class1{
  static void Main(string[] args){
         SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
         SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT ID, FirstName FROM Employee", thisConnection);

         SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

         DataSet thisDataSet = new DataSet();

         thisAdapter.Fill(thisDataSet, "Employee");

         Console.WriteLine("name before change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);

         thisDataSet.Tables["Employee"].Rows[1]["FirstName"] = "Inc";

         thisAdapter.Update(thisDataSet, "Employee");

         Console.WriteLine("name after change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);


  }
}

Delete data from database table using SqlDataAdapter

Posted on by coder 
   


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

   class PropagateDeletes {
      static void Main() {
         string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";

         string qry = @"select * from employee ";

         string del = @"delete from employee where id = @id";

         SqlConnection conn = new SqlConnection(connString);

         try {
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(qry, conn);

            DataSet ds = new DataSet();   
            da.Fill(ds, "employee");
            DataTable dt = ds.Tables["employee"];

            SqlCommand cmd = new SqlCommand(del, conn);
            cmd.Parameters.Add("@id",SqlDbType.Int, 4, "id");
            string filt = @"firstname = 'o' and lastname = 'B'";

            foreach (DataRow row in dt.Select(filt)) {
               row.Delete();
            }
            da.DeleteCommand = cmd;
            da.Update(ds, "employee");

            foreach (DataRow row in dt.Rows) {
               Console.WriteLine(
                  "{0} {1}",
                  row["firstname"].ToString().PadRight(15),
                  row["lastname"].ToString().PadLeft(25));
            }
         } catch(Exception e) {
            Console.WriteLine("Error: " + e);
         } finally {
            conn.Close();
         }
      }  
   }

Add data to database using SqlDataAdapter

Posted on by coder 
   


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

class Class1{
      public static void Main() {
         SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");

         SqlDataAdapter thisAdapter = new SqlDataAdapter( 
            "SELECT ID, FirstName FROM Employee", thisConnection);

         SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

         DataSet thisDataSet = new DataSet();

         thisAdapter.Fill(thisDataSet, "Employee");

         Console.WriteLine("# rows before change: {0}",thisDataSet.Tables["Employee"].Rows.Count);

         DataRow thisRow = thisDataSet.Tables["Employee"].NewRow();
         thisRow["ID"] = "123";
         thisRow["FirstName"] = "Ltd";
         thisDataSet.Tables["Employee"].Rows.Add(thisRow);

         Console.WriteLine("# rows after change: {0}", thisDataSet.Tables["Employee"].Rows.Count);

         thisAdapter.Update(thisDataSet, "Employee");
      }
}

Create connection string with SqlConnectionStringBuilder

Posted on by coder

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

class Program {
static void Main(string[] args) {
SqlConnectionStringBuilder cnStrBuilder = new SqlConnectionStringBuilder();
cnStrBuilder.UserID = “sa”;
cnStrBuilder.Password = “”;
cnStrBuilder.InitialCatalog = “Cars”;
cnStrBuilder.DataSource = “(local)”;
cnStrBuilder.ConnectTimeout = 5;

SqlConnection cn = new SqlConnection();
cn.ConnectionString = cnStrBuilder.ConnectionString;
cn.Open();
ShowConnectionStatus(cn);

string strSQL = “SELECT * FROM Inventory”;
SqlCommand myCommand = new SqlCommand(strSQL, cn);

SqlDataReader myDataReader;
myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

while (myDataReader.Read()) {
for (int i = 0; i < myDataReader.FieldCount; i++) { Console.Write("{0} = {1} ",myDataReader.GetName(i),myDataReader.GetValue(i).ToString().Trim()); } } myDataReader.Close(); ShowConnectionStatus(cn); } private static void ShowConnectionStatus(DbConnection cn) { Console.WriteLine("Database location: {0}", cn.DataSource); Console.WriteLine("Database name: {0}", cn.Database); Console.WriteLine("Timeout: {0}", cn.ConnectionTimeout); Console.WriteLine("Connection state: {0} ", cn.State.ToString()); } } [/csharp]

Create SqlCommand object

Posted on by coder 
   


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

   class CommandExampleSql
   {
      static void Main() 
      {
         SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");

         SqlCommand thisCommand = new SqlCommand();
         Console.WriteLine("Command created.");

         try {
            thisConnection.Open();

            thisCommand.Connection = thisConnection;
 
            thisCommand.CommandText = "SELECT COUNT(*) FROM Employee";
            Console.WriteLine("Ready to execute SQL command: {0}", thisCommand.CommandText);
         } catch (SqlException ex) {
            Console.WriteLine(ex.ToString());
         } finally {
            thisConnection.Close();
            Console.WriteLine("Connection Closed.");
         }
      }
   }