Csharp/C Sharp/Database ADO.net/DataSet

DataSet Delete

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.\n", ds.DataSetName);
        foreach (DataTable dt in ds.Tables) {
            Console.WriteLine("{0} Table.\n", dt.TableName);
            for (int curCol = 0; curCol < dt.Columns.Count; curCol++) {
                Console.Write(dt.Columns[curCol].ColumnName.Trim() + "\t");
            }
            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() + "\t");
                }
                Console.WriteLine();
            }
        }
    }
}

DataSet Read

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"] + "\t" +
                                                  theRow["ContactName"]);
        }
        thisConnection.Close();
    }
}

Finding Data

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("AAA");
        if (findRow == null) {
            DataRow thisRow = thisDataSet.Tables["Customers"].NewRow();
            thisRow["CustomerID"] = "AAA";
            thisRow["CompanyName"] = "AAA Ltd.";
            thisDataSet.Tables["Customers"].Rows.Add(thisRow);
            if ((findRow = thisDataSet.Tables["Customers"].Rows.Find("AAA")) != null) {
                Console.WriteLine("added");
            }
        } else {
            Console.WriteLine("AAA already present in database");
        }
        thisAdapter.Update(thisDataSet, "Customers");
        Console.WriteLine("# rows after change: {0}",thisDataSet.Tables["Customers"].Rows.Count);
        thisConnection.Close();
    }
}

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

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"] + "\t" + theRow["FirstName"]);
         }
  }
}

How to perform a SELECT statement and store the returned rows in a DataSet object

using System;
using System.Data;
using System.Data.SqlClient;
class SelectIntoDataSet {
  public static void Main() {
    string connectionString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
    SqlConnection mySqlConnection = new SqlConnection(connectionString);
    string selectString = "SELECT TOP 10 ID, FirstName, LastName FROM Employee ORDER BY ID";
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.rumandText = selectString;
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    Console.WriteLine("Retrieving rows from the Employee table");
    mySqlDataAdapter.Fill(myDataSet, "Employee");
    mySqlConnection.Close();
    DataTable myDataTable = myDataSet.Tables["Employee"];
    foreach (DataRow myDataRow in myDataTable.Rows)
    {
      Console.WriteLine("ID = "+ myDataRow["ID"]);
      Console.WriteLine("FirstName = "+ myDataRow["FirstName"]);
      Console.WriteLine("LastName = "+ myDataRow["LastName"]);
    }
  }
}

how to write and read XML files

   
using System;
using System.Data;
using System.Data.SqlClient;
class WriteAndReadXML {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.rumandText =
          "SELECT TOP 2 CustomerID, CompanyName, ContactName, " +
          "Address " +
          "FROM Customers " +
          "ORDER BY CustomerID";
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
        mySqlDataAdapter.SelectCommand = mySqlCommand;
        DataSet myDataSet = new DataSet();
        mySqlConnection.Open();
        mySqlDataAdapter.Fill(myDataSet, "Customers");
        mySqlConnection.Close();
        myDataSet.WriteXml("myXmlFile.xml");
        myDataSet.WriteXml("myXmlFile2.xml", XmlWriteMode.WriteSchema);
        myDataSet.WriteXmlSchema("myXmlSchemaFile.xml");
        myDataSet.Clear();
        myDataSet.ReadXml("myXmlFile.xml");
        DataTable myDataTable = myDataSet.Tables["Customers"];
        foreach (DataRow myDataRow in myDataTable.Rows) {
            Console.WriteLine("CustomerID = " + myDataRow["CustomerID"]);
            Console.WriteLine("CompanyName = " + myDataRow["CompanyName"]);
            Console.WriteLine("ContactName = " + myDataRow["ContactName"]);
            Console.WriteLine("Address = " + myDataRow["Address"]);
        }
    }
}

Open the XML file and read into a DataSet

using System;
using System.IO;
using System.Data;
public class MainClass {
    static void Main(string[] args) {
        if (args.Length != 1)
            return;
        FileStream fs = new FileStream(args[0], FileMode.Open);
        DataSet ds = new DataSet();
        ds.ReadXml(fs);
        // Use a DataTable to display the members.
        DataTable mt = ds.Tables["member"];
        for (int row = 0; row < mt.Rows.Count; row++) {
            for (int col = 0; col < mt.Columns.Count - 1; col++) {
                Console.WriteLine("{0,-10}{1}",
                    mt.Columns[col].Caption,
                    mt.Rows[row][col].ToString().Trim());
            }
            Console.WriteLine();
        }
        fs.Close();
    }
}

Populate a DataSet object with multiple DataTable objects

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.rumandText =
      "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.rumandText =
      "SELECT ID, Name " +
      "FROM Customers " +
      "WHERE ID = "001"";
    numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Customers");
    Console.WriteLine("numberOfRows = " + numberOfRows);
    mySqlConnection.Close();
    foreach (DataTable myDataTable in myDataSet.Tables) {
      Console.WriteLine("\nReading 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 by changing the CommandText property of a DataAdapter object"s SelectCommand

   
using System;
using System.Data;
using System.Data.SqlClient;
class MultipleDataTables2 {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.rumandText = "SELECT TOP 2 ProductID, ProductName, UnitPrice FROM Products ORDER BY ProductID";
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
        mySqlDataAdapter.SelectCommand = mySqlCommand;
        DataSet myDataSet = new DataSet();
        mySqlConnection.Open();
        int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products");
        Console.WriteLine("numberOfRows = " + numberOfRows);
        mySqlDataAdapter.SelectCommand.rumandText = "SELECT CustomerID, CompanyName FROM Customers WHERE CustomerID = "ALFKI"";
        numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Customers");
        Console.WriteLine("numberOfRows = " + numberOfRows);
        mySqlConnection.Close();
        foreach (DataTable myDataTable in myDataSet.Tables) {
            Console.WriteLine("\nReading 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 with multiple DataTable objects using multiple SELECT statements

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.rumandText =
      "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("\nReading from the " +
        myDataTable.TableName + "DataTable");
      foreach (DataRow myDataRow in myDataTable.Rows)
      {
        foreach (DataColumn myDataColumn in myDataTable.Columns)
        {
          Console.WriteLine(myDataColumn + "= " +
            myDataRow[myDataColumn]);
        }
      }
    }
  }
}

Print DataSet out

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.rumon;
class Program {
    static void Main(string[] args) {
        string cnStr = "uid=sa;pwd=;Initial Catalog=yourDatabase;Data Source=(local)";
        DataSet myDS = new DataSet("Cars");
        SqlDataAdapter dAdapt = new SqlDataAdapter("Select * From Inventory", cnStr);
        DataTableMapping custMap = dAdapt.TableMappings.Add("Inventory", "Current Inventory");
        custMap.ColumnMappings.Add("CarID", "Car ID");
        custMap.ColumnMappings.Add("PetName", "Name of Car");
        try {
            dAdapt.Fill(myDS, "Inventory");
        } catch (Exception ex) {
            Console.WriteLine(ex.Message);
        }
        PrintDataSet(myDS);
    }
    static void PrintDataSet(DataSet ds) {
        Console.WriteLine("Tables in "{0}" DataSet.\n", ds.DataSetName);
        foreach (DataTable dt in ds.Tables) {
            Console.WriteLine("{0} Table.\n", dt.TableName);
            for (int curCol = 0; curCol < dt.Columns.Count; curCol++) {
                Console.Write(dt.Columns[curCol].ColumnName.Trim() + "\t");
            }
            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() + "\t");
                }
                Console.WriteLine();
            }
        }
    }
}

Read data from DataSet

using System;
using System.Data;
using System.Data.SqlClient;
class PopDataset
{
   static void Main()
   {
      string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
      string sql = @"select * from employee";
      SqlConnection conn = new SqlConnection(connString);
      try
      {
         conn.Open();
         SqlDataAdapter da = new SqlDataAdapter(sql, conn);
         DataSet ds = new DataSet();    
         da.Fill(ds, "employee");
         DataTable dt = ds.Tables["employee"];
         foreach (DataRow row in dt.Rows)
         {
            foreach (DataColumn col in dt.Columns)
               Console.WriteLine(row[col]);
            Console.WriteLine("".PadLeft(20, "="));
         }
      }
      catch(Exception e)
      {
         Console.WriteLine("Error: " + e);
      }
      finally
      {
         conn.Close();
      }
   }  
}

ReadXml

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
class Program {
    static void Main(string[] args) {
        DataSet thisDataSet = new DataSet();
        thisDataSet.ReadXml("nwinddata.xml");
        foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows) {
            Console.WriteLine("Customer ID: " + custRow["CustomerID"] +
                              " Name: " + custRow["CompanyName"]);
        }
        Console.WriteLine("Table created by ReadXml is called {0}",
                           thisDataSet.Tables[0].TableName);
    }
}

Use DataViewManager to wrap DataSet

using System;
using System.Collections.Generic;
using System.ruponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
public class Form1 : Form {
    public Form1() {
        InitializeComponent();
    }
    private void getData_Click(object sender, EventArgs e) {
        string orders = "SELECT * FROM Orders";
        string customers = "SELECT * FROM Customers";
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["northwind"].ConnectionString)) {
            SqlDataAdapter da = new SqlDataAdapter(orders, con);
            DataSet ds = new DataSet();
            da.Fill(ds, "Orders");
            da = new SqlDataAdapter(customers, con);
            da.Fill(ds, "Customers");
            ds.Relations.Add("CustomerOrders",
                ds.Tables["Customers"].Columns["CustomerID"],
                ds.Tables["Orders"].Columns["CustomerID"]);
            DataViewManager dvm = new DataViewManager(ds);
            dvm.DataViewSettings["Customers"].RowFilter = "Country="UK"";
            dataGrid1.SetDataBinding(dvm, "Customers");
        }
    }
    private void InitializeComponent() {
        this.dataGrid1 = new System.Windows.Forms.DataGrid();
        this.getData = new System.Windows.Forms.Button();
        ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
        this.SuspendLayout();
        this.dataGrid1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
                    | System.Windows.Forms.AnchorStyles.Left)
                    | System.Windows.Forms.AnchorStyles.Right)));
        this.dataGrid1.DataMember = "";
        this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
        this.dataGrid1.Location = new System.Drawing.Point(13, 13);
        this.dataGrid1.Size = new System.Drawing.Size(488, 374);
        this.getData.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
        this.getData.Location = new System.Drawing.Point(426, 393);
        this.getData.Size = new System.Drawing.Size(75, 23);
        this.getData.Text = "Get Data";
        this.getData.UseVisualStyleBackColor = true;
        this.getData.Click += new System.EventHandler(this.getData_Click);
        this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
        this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
        this.ClientSize = new System.Drawing.Size(513, 428);
        this.Controls.Add(this.getData);
        this.Controls.Add(this.dataGrid1);
        ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
        this.ResumeLayout(false);
    }
    private System.Windows.Forms.DataGrid dataGrid1;
    private System.Windows.Forms.Button getData;
    [STAThread]
    static void Main() {
        Application.EnableVisualStyles();
        Application.SetCompatibleTextRenderingDefault(false);
        Application.Run(new Form1());
    }
}

Use the Merge() method

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.rumandText =
      "SELECT ID, FirstName, LastName, Address " +
      "FROM Customers " +
      "WHERE ID IN ("001", "002", "003")";
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    mySqlDataAdapter.Fill(myDataSet, "Customers");
    mySqlCommand.rumandText =
      "SELECT ID, FirstName, LastName, Address " +
      "FROM Customers " +
      "WHERE CustomerID IN ("008", "009")";
    DataSet myDataSet2 = new DataSet();
    mySqlDataAdapter.Fill(myDataSet2, "Customers2");
    mySqlCommand.rumandText =
      "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("\nReading from the " + myDataTable + "DataTable");
      foreach (DataRow myDataRow in myDataTable.Rows) {
        foreach (DataColumn myDataColumn in myDataTable.Columns) {
          Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]);
        }
      }
    }
  }
}

Using Datasets

/*
 * 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 UsingDatasets
    {
        static void Main(string[] args)
        {
            SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
            SqlDataAdapter MyAdapter = new SqlDataAdapter("SELECT * FROM CaseInfo", MyConnection);
            DataSet MyDataSet = new DataSet();
            MyAdapter.Fill(MyDataSet, "MyTable");
            MyDataSet.WriteXml(@"c:\MyDatSet.xml");
        }
    }
}

Using Multi Tabled Datasets

/*
 * 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.OleDb;
namespace Client.Chapter_13___ADO.NET
{
    public class UsingMultiTabledDatasets
    {
        static void Main(string[] args)
        {
            OleDbConnection MyConnection = new OleDbConnection(@"Provider=Microsft.Jet.OLEDB.4.0; Data Source = c:\MyAccessDB.mdb");
            OleDbDataAdapter MyAdapter = new OleDbDataAdapter("SELECT Column1, Column2, Column3 FROM MyTable", MyConnection);
            DataSet MyDataSet = new DataSet();
            MyAdapter.Fill(MyDataSet, "MyTable");
            foreach (DataTable MyTable in MyDataSet.Tables)
            {
                foreach (DataColumn MyColumn in MyTable.Columns)
                {
                    foreach (DataRow MyRow in MyTable.Rows)
                    {
                    }
                }
            }
        }
    }
}