Author: coder

Use SQL command to insert data into database table

Posted on by coder 
   


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

   class CommandExampleCreateDb
   {
      static void Main() 
      {
         SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
         SqlCommand nonqueryCommand = thisConnection.CreateCommand();

         try 
         {
            thisConnection.Open();

            nonqueryCommand.CommandText = "CREATE DATABASE MyDb";
            Console.WriteLine(nonqueryCommand.CommandText);

            nonqueryCommand.ExecuteNonQuery();
            Console.WriteLine("Database created, now switching");
            thisConnection.ChangeDatabase("MyDb");

            nonqueryCommand.CommandText = "CREATE TABLE MyJava2sTable (COL1 integer)";
            Console.WriteLine(nonqueryCommand.CommandText);
            Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());

            nonqueryCommand.CommandText = "INSERT INTO MyJava2sTable VALUES (99)";
            Console.WriteLine(nonqueryCommand.CommandText);
            Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
         
         } catch (SqlException ex) {
         
            Console.WriteLine(ex.ToString());
         
         } finally {  
         
            thisConnection.Close();
            Console.WriteLine("Connection Closed.");
         
         }
      }
   }

Set CommandType and CommandText of IDbConnection

Posted on by coder 
   
 
using System;
using System.Data;
using System.Data.SqlClient;

class MainClass {
    public static void ExecuteNonQueryExample(IDbConnection con) {
        IDbCommand com = con.CreateCommand();
        com.CommandType = CommandType.Text;
        com.CommandText = "UPDATE Employees SET Title = 'Sales Director' WHERE EmployeeId = '5'";

        int result = com.ExecuteNonQuery();
        Console.WriteLine(result);
    }

    public static void ExecuteReaderExample(IDbConnection con) {
        IDbCommand com = con.CreateCommand();
        com.CommandType = CommandType.StoredProcedure;
        com.CommandText = "Ten Most Expensive Products";

        using (IDataReader reader = com.ExecuteReader()) {
            while (reader.Read()) {
                Console.WriteLine("  {0} = {1}", reader["TenMostExpensiveProducts"], reader["UnitPrice"]);
            }
        }
    }

    public static void ExecuteScalarExample(IDbConnection con) {
        IDbCommand com = con.CreateCommand();
        com.CommandType = CommandType.Text;
        com.CommandText = "SELECT COUNT(*) FROM Employees";

        int result = (int)com.ExecuteScalar();
        Console.WriteLine("Employee count = " + result);
    }

    public static void Main() {
        using (SqlConnection con = new SqlConnection()) {
            con.ConnectionString = @"Data Source = .sqlexpress;Database = Northwind; Integrated Security=SSPI";
            con.Open();
            ExecuteNonQueryExample(con);
            ExecuteReaderExample(con);
            ExecuteScalarExample(con);
        }
    }
}

Deleting Data

Posted on by coder 
   




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

        SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", thisConnection);
        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
        DataSet thisDataSet = new DataSet();
        thisAdapter.Fill(thisDataSet, "Customers");

        Console.WriteLine("# rows before change: {0}",thisDataSet.Tables["Customers"].Rows.Count);
        DataColumn[] keys = new DataColumn[1];
        keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"];
        thisDataSet.Tables["Customers"].PrimaryKey = keys;

        DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI");

        if (findRow != null) {
            findRow.Delete();
            thisAdapter.Update(thisDataSet, "Customers");
        }

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

Deleting Data Using SQLStatements

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 DeletingDataUsingSQLStatements
    {
        static void Main(string[] args)
        {
            SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");

            MyConnection.Open();

            String MyString = "DELETE Test";
            SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);

            MyCmd.ExecuteScalar();
            MyConnection.Close();
        }
    }
}

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"]);
                    }
                }
            }
        }
    }
}