Author: coder

DataSet Read

Posted on by coder 
   


using System;
using System.Data;            // Use ADO.NET namespace
using System.Data.SqlClient;  // Use SQL Server data provider namespace
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, ContactName FROM Customers", thisConnection);

        DataSet thisDataSet = new DataSet();

        thisAdapter.Fill(thisDataSet, "Customers");
        foreach (DataRow theRow in thisDataSet.Tables["Customers"].Rows) {
            Console.WriteLine(theRow["CustomerID"] + "	" +
                                                  theRow["ContactName"]);
        }

        thisConnection.Close();
    }
}

For each row in DataSet, reference the column data by column name

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

         DataSet thisDataSet = new DataSet();

         thisAdapter.Fill(thisDataSet, "Employee");

         foreach (DataRow theRow in thisDataSet.Tables["Employee"].Rows)
         {
            Console.WriteLine(theRow["ID"] + "	" + theRow["FirstName"]);
         }
  }
}

DataSet Delete

Posted on by coder

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

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

class Program {
static void Main(string[] args) {
DataSet theCarsInventory = new DataSet();
SqlConnection cn = new SqlConnection(“server=(local);User ID=sa;Pwd=;database=Cars”);
SqlDataAdapter da = new SqlDataAdapter(“SELECT * FROM Inventory”, cn);
SqlCommandBuilder invBuilder = new SqlCommandBuilder(da);
da.Fill(theCarsInventory, “Inventory”);
PrintDataSet(theCarsInventory);
try {
theCarsInventory.Tables[“Inventory”].Rows[1].Delete();
da.Update(theCarsInventory, “Inventory”);
} catch (Exception e) {
Console.WriteLine(e.Message);
}
theCarsInventory = new DataSet();
da.Fill(theCarsInventory, “Inventory”);
PrintDataSet(theCarsInventory);
}
static void PrintDataSet(DataSet ds) {
Console.WriteLine(“Tables in '{0}' DataSet.
“, ds.DataSetName);
foreach (DataTable dt in ds.Tables) {
Console.WriteLine(“{0} Table.
“, dt.TableName);
for (int curCol = 0; curCol < dt.Columns.Count; curCol++) { Console.Write(dt.Columns[curCol].ColumnName.Trim() + " "); } for (int curRow = 0; curRow < dt.Rows.Count; curRow++) { for (int curCol = 0; curCol < dt.Columns.Count; curCol++) { Console.Write(dt.Rows[curRow][curCol].ToString().Trim() + " "); } Console.WriteLine(); } } } } [/csharp]

Use the Merge() method

Posted on by coder 
   


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

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

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.CommandText =
      "SELECT ID, FirstName, LastName, Address " +
      "FROM Customers " +
      "WHERE ID IN (&#039;001&#039;, &#039;002&#039;, &#039;003&#039;)";
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    mySqlDataAdapter.Fill(myDataSet, "Customers");

    mySqlCommand.CommandText =
      "SELECT ID, FirstName, LastName, Address " +
      "FROM Customers " +
      "WHERE CustomerID IN (&#039;008&#039;, &#039;009&#039;)";
    DataSet myDataSet2 = new DataSet();
    mySqlDataAdapter.Fill(myDataSet2, "Customers2");

    mySqlCommand.CommandText =
      "SELECT TOP 5 ProductID, ProductName, UnitPrice " +
      "FROM Products " +
      "ORDER BY ProductID";
    DataSet myDataSet3 = new DataSet();
    mySqlDataAdapter.Fill(myDataSet3, "Products");

    mySqlConnection.Close();

    myDataSet.Merge(myDataSet2);

    myDataSet.Merge(myDataSet3, true, MissingSchemaAction.Add);

    foreach (DataTable myDataTable in myDataSet.Tables) {
      Console.WriteLine("
Reading from the " + myDataTable + "DataTable");
      foreach (DataRow myDataRow in myDataTable.Rows) {
        foreach (DataColumn myDataColumn in myDataTable.Columns) {
          Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]);
        }
      }
    }
  }
}

Populate a DataSet with multiple DataTable objects using multiple SELECT statements

Posted on by coder 
   


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

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

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText =
      "SELECT TOP 2 ID, FirstName, LastName " +
      "FROM Employee " +
      "ORDER BY ID;" +
      "SELECT ID, LastName " +
      "FROM MyEmployee " +
      "WHERE ID = 9;";

    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    int numberOfRows = mySqlDataAdapter.Fill(myDataSet);
    Console.WriteLine("numberOfRows = " + numberOfRows);
    mySqlConnection.Close();

    myDataSet.Tables["Table"].TableName = "Employee";
    myDataSet.Tables["Table1"].TableName = "MyEmployee";

    foreach (DataTable myDataTable in myDataSet.Tables) {
      Console.WriteLine("
Reading from the " +
        myDataTable.TableName + "DataTable");
      foreach (DataRow myDataRow in myDataTable.Rows)
      {
        foreach (DataColumn myDataColumn in myDataTable.Columns)
        {
          Console.WriteLine(myDataColumn + "= " +
            myDataRow[myDataColumn]);
        }
      }
    }
  }
}

Populate a DataSet object with multiple DataTable objects

Posted on by coder 
   

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

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

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText =
      "SELECT TOP 2 ID, FirstName, LastName " +
      "FROM Employee " +
      "ORDER BY ID";
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Employee");
    Console.WriteLine("numberOfRows = " + numberOfRows);

    mySqlDataAdapter.SelectCommand.CommandText =
      "SELECT ID, Name " +
      "FROM Customers " +
      "WHERE ID = &#039;001&#039;";
    numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Customers");
    Console.WriteLine("numberOfRows = " + numberOfRows);

    mySqlConnection.Close();

    foreach (DataTable myDataTable in myDataSet.Tables) {
      Console.WriteLine("
Reading from the " +
        myDataTable.TableName + "DataTable");
      foreach (DataRow myDataRow in myDataTable.Rows)
      {
        foreach (DataColumn myDataColumn in myDataTable.Columns)
        {
          Console.WriteLine(myDataColumn + "= " +
            myDataRow[myDataColumn]);
        }
      }
    }
  }
}

Populate a DataSet object using a SELECT statement

Posted on by coder 
   
 

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

class PopulateDataSetUsingSelect {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.CommandText =
          "SELECT TOP 5 ProductID, ProductName, UnitPrice " +
          "FROM Products " +
          "ORDER BY ProductID";
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
        mySqlDataAdapter.SelectCommand = mySqlCommand;
        DataSet myDataSet = new DataSet();
        mySqlConnection.Open();
        Console.WriteLine("Retrieving rows from the Products table");
        int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products");
        Console.WriteLine("numberOfRows = " + numberOfRows);
        mySqlConnection.Close();
        DataTable myDataTable = myDataSet.Tables["Products"];
        foreach (DataRow myDataRow in myDataTable.Rows) {
            Console.WriteLine("ProductID = " + myDataRow["ProductID"]);
            Console.WriteLine("ProductName = " + myDataRow["ProductName"]);
            Console.WriteLine("UnitPrice = " + myDataRow["UnitPrice"]);
        }
    }
}