Database ADO.net

Deleting Data Using SqlCommandBuilder

Posted on by coder 
   


/*
 * C# Programmers Pocket Consultant
 * Author: Gregory S. MacBeth
 * Email: gmacbeth@comporium.net
 * Create Date: June 27, 2003
 * Last Modified Date:
 * Version: 1
 */
using System;
using System.Data;
using System.Data.SqlClient;

namespace Client.Chapter_13___ADO.NET
{
    public class DeletingDataUsingCommandBuilder
    {
        static void Main(string[] args)
        {
            SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
            SqlDataAdapter MyDataAdapter = new SqlDataAdapter("SELECT * FROM Test", MyConnection);
            SqlCommandBuilder MyCmd = new SqlCommandBuilder(MyDataAdapter);
            DataSet MyDataSet = new DataSet();

            MyDataAdapter.Fill(MyDataSet);

            DataColumn[] MyKey = new DataColumn[1];

            MyKey[0] = MyDataSet.Tables[0].Columns[0];
            MyDataSet.Tables[0].PrimaryKey = MyKey;

            DataRow FindMyRow = MyDataSet.Tables[0].Rows.Find(1);

            FindMyRow.Delete();
            MyDataAdapter.Update(MyDataSet);
        }
    }
}

use the ExecuteNonQuery() method to run INSERT, UPDATE,and DELETE statements

Posted on by coder 
   


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

class ExecuteInsertUpdateDelete
{
  public static void DisplayRow(SqlCommand mySqlCommand, string ID){
    mySqlCommand.CommandText ="SELECT ID, FirstName FROM Employee WHERE ID = '" + ID + "'";

    SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();

    while (mySqlDataReader.Read())
    {
      Console.WriteLine("mySqlDataReader[" ID"] = " +
        mySqlDataReader["ID"]);
      Console.WriteLine("mySqlDataReader[" FirstName"] = " +
        mySqlDataReader["FirstName"]);
    }

    mySqlDataReader.Close();
  }

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

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText ="INSERT INTO Employee (ID, FirstName) VALUES (" +
      "  9, 'Jason')";

    mySqlConnection.Open();

    int numberOfRows = mySqlCommand.ExecuteNonQuery();
    Console.WriteLine("Number of rows added = " + numberOfRows);
    DisplayRow(mySqlCommand, "9");

    mySqlCommand.CommandText = "UPDATE Employee SET FirstName = 'New' WHERE ID = '9'";

    numberOfRows = mySqlCommand.ExecuteNonQuery();
    Console.WriteLine("Number of rows updated = " + numberOfRows);
    DisplayRow(mySqlCommand, "9");

    mySqlCommand.CommandText ="DELETE FROM Employee WHERE ID = '9'";

    numberOfRows = mySqlCommand.ExecuteNonQuery();
    Console.WriteLine("Number of rows deleted = " + numberOfRows);

    mySqlConnection.Close();
  }
}

GetFactoryClasses, GetFactory

Posted on by coder 
   
 


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

class MainClass {
    public static void Main(string[] args) {
        using (DataTable providers = DbProviderFactories.GetFactoryClasses()) {
            foreach (DataRow prov in providers.Rows) {
                Console.WriteLine(" Name:{0}", prov["Name"]);
                Console.WriteLine("   Description:{0}", prov["Description"]);
                Console.WriteLine("   Invariant Name:{0}",prov["InvariantName"]);
            }
        }

        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");

        using (IDbConnection con = factory.CreateConnection()) {
            con.ConnectionString = @"Data Source = .sqlexpress;Database = Northwind; Integrated Security=SSPI";
            using (IDbCommand com = con.CreateCommand()) {
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "Ten Most Expensive Products";
                con.Open();
                using (IDataReader reader = com.ExecuteReader()) {
                    Console.WriteLine(Environment.NewLine);
                    Console.WriteLine("Price of the Ten Most Expensive Products.");
                    while (reader.Read()) {
                        Console.WriteLine("  {0} = {1}", reader["TenMostExpensiveProducts"], reader["UnitPrice"]);
                    }
                }
            }
        }
    }
}

Create Connection

Posted on by coder 
   
 


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

class MainClass {
    public static void Main(string[] args) {
        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");

        using (IDbConnection con = factory.CreateConnection()) {
            con.ConnectionString = @"Data Source = .sqlexpress;Database = Northwind; Integrated Security=SSPI";
            using (IDbCommand com = con.CreateCommand()) {
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "Ten Most Expensive Products";
                con.Open();
                using (IDataReader reader = com.ExecuteReader()) {
                    Console.WriteLine(Environment.NewLine);
                    Console.WriteLine("Price of the Ten Most Expensive Products.");
                    while (reader.Read()) {
                        Console.WriteLine("  {0} = {1}", reader["TenMostExpensiveProducts"], reader["UnitPrice"]);
                    }
                }
            }
        }
    }
}

Use DB2Connection class to connect to a DB2 database

Posted on by coder 
   


using System;
using System.Data;
using IBM.Data.DB2;

class Db2Connect{
   static void Main() {
       string connString = @"database = sample;";

       DB2Connection conn = new DB2Connection(connString);

       try {
         conn.Open();
         Console.WriteLine("Connection opened.");

         // Display connection properties
         Console.WriteLine("Connection Properties:");   
         Console.WriteLine("	Connection String: {0}", conn.ConnectionString);    
         Console.WriteLine("	Database: {0}",conn.Database);   
         Console.WriteLine("	ServerVersion: {0}",conn.ServerVersion);
         Console.WriteLine("	State: {0}",conn.State);
      } catch (DB2Exception e) {
         Console.WriteLine("Error: " + e);  
      } finally  {
         conn.Close();
         Console.WriteLine("Connection closed.");
      }
   }
}

OleDbConnection Connect to DB2: DB2 connection string

Posted on by coder 
   

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

class Db2Connect{
   static void Main() {
      string connString = @"provider = IBMDADB2.1;persist security info = false;data source = sample;";

      OleDbConnection conn = new OleDbConnection(connString);

      try
      {
         conn.Open();
         Console.WriteLine("Connection opened.");

         Console.WriteLine("Connection Properties:");   
         Console.WriteLine("	Connection String: {0}", conn.ConnectionString);    
         Console.WriteLine("	Database: {0}",conn.Database);   
         Console.WriteLine("	DataSource: {0}",conn.DataSource);
         Console.WriteLine("	ServerVersion: {0}",conn.ServerVersion);
         Console.WriteLine("	State: {0}",conn.State);
      } catch (OleDbException e) {
         Console.WriteLine("Error: " + e);  
      } finally {
         conn.Close();
         Console.WriteLine("Connection closed.");
      }
   }
}

use the Find() and FindRows() methods of a DataView to find DataRowView objects

Posted on by coder

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

class FindingDataRowViews {
public static void Main() {
SqlConnection mySqlConnection =
new SqlConnection(
“server=localhost;database=Northwind;uid=sa;pwd=sa”
);
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
“SELECT CustomerID, CompanyName, Country ” +
“FROM Customers”;
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
mySqlDataAdapter.Fill(myDataSet, “Customers”);
mySqlConnection.Close();
DataTable customersDT = myDataSet.Tables[“Customers”];

string filterExpression = “Country = 'UK'”;
string sortExpression = “CustomerID”;
DataViewRowState rowStateFilter = DataViewRowState.OriginalRows;

DataView customersDV = new DataView();
customersDV.Table = customersDT;
customersDV.RowFilter = filterExpression;
customersDV.Sort = sortExpression;
customersDV.RowStateFilter = rowStateFilter;

foreach (DataRowView myDataRowView in customersDV) {
for (int count = 0; count < customersDV.Table.Columns.Count; count++) { Console.WriteLine(myDataRowView[count]); } Console.WriteLine(""); } int index = customersDV.Find("BSBEV"); Console.WriteLine("BSBEV found at index " + index + " "); DataRowView[] customersDRVs = customersDV.FindRows("BSBEV"); foreach (DataRowView myDataRowView in customersDRVs) { for (int count = 0; count < customersDV.Table.Columns.Count; count++) { Console.WriteLine(myDataRowView[count]); } Console.WriteLine(""); } } } [/csharp]