Database ADO.net

illustrates the use of adding, modifying, and deleting a row in a DataTable object and synchronizing those changes with the

Posted on by coder 
   
 
/*
Mastering Visual C# .NET
by Jason Price, Mike Gunderloy

Publisher: Sybex;
ISBN: 0782129110
*/

/*
  Example23_3.cs illustrates the use of adding, modifying, and deleting
  a row in a DataTable object and synchronizing those changes with the
  database
*/

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

public class Example23_3
{

  public static void DisplayDataTable(DataTable myDataTable)
  {

    // display the columns for each row in the DataTable,
    // using a DataRow object to access each row in the DataTable
    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"]);
    }

  }

  public static void AddRow(
    DataTable myDataTable
  )
  {

    Console.WriteLine("
Adding a new row with CustomerID of 'T1COM'");

    // step 1: use the NewRow() method of the DataRow object to create
    // a new row in the DataTable
    DataRow myNewDataRow = myDataTable.NewRow();

    // step 2: set the values for the columns of the new row
    myNewDataRow["CustomerID"] = "T1COM";
    myNewDataRow["CompanyName"] = "T1 Company";
    myNewDataRow["ContactName"] = "Jason Price";
    myNewDataRow["Address"] = "1 Main Street";

    // step 3: use the Add() method through the Rows property to add
    // the new DataRow to the DataTable
    myDataTable.Rows.Add(myNewDataRow);

    // step 4: use the AcceptChanges() method of the DataTable to commit
    // the changes
    myDataTable.AcceptChanges();

  }

  public static void ModifyRow(
    DataTable myDataTable
  )
  {

    Console.WriteLine("
Modifying the new row");

    // step 1: set the PrimaryKey property for the DataTable object
    DataColumn[] myPrimaryKey = new DataColumn[1];
    myPrimaryKey[0] = myDataTable.Columns["CustomerID"];
    myDataTable.PrimaryKey = myPrimaryKey;

    // step 2: use the Find() method to locate the DataRow
    // in the DataTable using the primary key value
    DataRow myEditDataRow = myDataTable.Rows.Find("T1COM");

    // step 3: change the column values
    myEditDataRow["CompanyName"] = "Widgets Inc.";
    myEditDataRow["ContactName"] = "John Smith";
    myEditDataRow["Address"] = "1 Any Street";

    // step 4: use the AcceptChanges() method of the DataTable to commit
    // the changes
    myDataTable.AcceptChanges();
    Console.WriteLine("myEditDataRow.RowState = " + myEditDataRow.RowState);

  }

  public static void RemoveRow(
    DataTable myDataTable
  )
  {

    Console.WriteLine("
Removing the new row");

    // step 1: set the PrimaryKey property for the DataTable object
    DataColumn[] myPrimaryKey = new DataColumn[1];
    myPrimaryKey[0] = myDataTable.Columns["CustomerID"];
    myDataTable.PrimaryKey = myPrimaryKey;

    // step 2: use the Find() method to locate the DataRow
    DataRow myRemoveDataRow = myDataTable.Rows.Find("T1COM");

    // step 3: use the Delete() method to remove the DataRow
    myRemoveDataRow.Delete();

    // step 4: use the AcceptChanges() method of the DataTable to commit
    // the changes
    myDataTable.AcceptChanges();

  }

  public static void Main()
  {

    // formulate a string containing the details of the
    // database connection
    string connectionString =
      "server=localhost;database=Northwind;uid=sa;pwd=sa";

    // create a SqlConnection object to connect to the
    // database, passing the connection string to the constructor
    SqlConnection mySqlConnection =
      new SqlConnection(connectionString);

    // formulate a SELECT statement to retrieve the
    // CustomerID, CompanyName, ContactName, and Address
    // columns for the first row from the Customers table
    string selectString =
      "SELECT CustomerID, CompanyName, ContactName, Address " +
      "FROM Customers " +
      "WHERE CustomerID = 'ALFKI'";

    // create a SqlCommand object to hold the SELECT statement
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    // set the CommandText property of the SqlCommand object to
    // the SELECT string
    mySqlCommand.CommandText = selectString;

    // create a SqlDataAdapter object
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();

    // set the SelectCommand property of the SqlAdapter object
    // to the SqlCommand object
    mySqlDataAdapter.SelectCommand = mySqlCommand;

    // create a DataSet object to store the results of
    // the SELECT statement
    DataSet myDataSet = new DataSet();

    // open the database connection using the
    // Open() method of the SqlConnection object
    mySqlConnection.Open();

    // use the Fill() method of the SqlDataAdapter object to
    // retrieve the rows from the table, storing the rows locally
    // in a DataTable of the DataSet object
    Console.WriteLine("Retrieving a row from the Customers table");
    mySqlDataAdapter.Fill(myDataSet, "Customers");

    // get the DataTable object from the DataSet object
    DataTable myDataTable = myDataSet.Tables["Customers"];

    // display the rows in the DataTable object
    DisplayDataTable(myDataTable);

    // add a new row
    AddRow(myDataTable);
    DisplayDataTable(myDataTable);

    // modify a row
    ModifyRow(myDataTable);
    DisplayDataTable(myDataTable);

    // remove a row
    RemoveRow(myDataTable);
    DisplayDataTable(myDataTable);

    // use the Fill() method of the SqlDataAdapter object
    // to synchronize the changes with the database
    mySqlDataAdapter.Fill(myDataSet, "Customers");

    // close the database connection using the Close() method
    // of the SqlConnection object
    mySqlConnection.Close();

  }

}

Print DataTable

Posted on by coder

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

class Program {
static void Main(string[] args) {
DataSet carsInventoryDS = new DataSet(“Inventory”);
carsInventoryDS.ExtendedProperties[“TimeStamp”] = DateTime.Now;
carsInventoryDS.ExtendedProperties[“Company”] = “Name”;

DataColumn carIDColumn = new DataColumn(“CarID”, typeof(int));
carIDColumn.ReadOnly = true;
carIDColumn.Caption = “Car ID”;
carIDColumn.AllowDBNull = false;
carIDColumn.Unique = true;
carIDColumn.AutoIncrement = true;
carIDColumn.AutoIncrementSeed = 0;
carIDColumn.AutoIncrementStep = 1;
carIDColumn.ColumnMapping = MappingType.Attribute;

DataColumn carMakeColumn = new DataColumn(“Make”, typeof(string));
DataColumn carColorColumn = new DataColumn(“Color”, typeof(string));
DataColumn carPetNameColumn = new DataColumn(“PetName”, typeof(string));
carPetNameColumn.Caption = “Name”;

DataTable inventoryTable = new DataTable(“Inventory”);
inventoryTable.Columns.AddRange(new DataColumn[] { carIDColumn, carMakeColumn, carColorColumn, carPetNameColumn });

inventoryTable.PrimaryKey = new DataColumn[] { inventoryTable.Columns[0] };

DataRow carRow = inventoryTable.NewRow();
carRow[“Make”] = “B”;
carRow[“Color”] = “C”;
carRow[“PetName”] = “A”;
inventoryTable.Rows.Add(carRow);
carRow = inventoryTable.NewRow();
carRow[“Make”] = “S”;
carRow[“Color”] = “R”;
carRow[“PetName”] = “E”;
inventoryTable.Rows.Add(carRow);

carsInventoryDS.Tables.Add(inventoryTable);
PrintTable(carsInventoryDS.Tables[“Inventory”]);
}

private static void PrintTable(DataTable dt) {
DataTableReader dtReader = dt.CreateDataReader();
while (dtReader.Read()) {
for (int i = 0; i < dtReader.FieldCount; i++) { Console.Write("{0} = {1} ", dtReader.GetName(i).Trim(), dtReader.GetValue(i).ToString().Trim()); } Console.WriteLine(); } dtReader.Close(); } } [/csharp]

Use DataViewManager to wrap DataSet

Posted on by coder 
   


using System;
using System.Collections.Generic;
using System.ComponentModel;
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=&#039;UK&#039;";
            dataGrid1.SetDataBinding(dvm, "Customers");
        }
    }
    private void InitializeComponent() {
        this.dataGrid1 = new System.Windows.Forms.DataGrid();
        this.getData = new System.Windows.Forms.Button();
        ((System.ComponentModel.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.ComponentModel.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());
    }
}

Populate a DataSet object with multiple DataTable objects by changing the CommandText property of a DataAdapter object's SelectCommand

Posted on by coder 
   


   
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.CommandText = "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.CommandText = "SELECT CustomerID, CompanyName FROM Customers WHERE CustomerID = &#039;ALFKI&#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]);
                }
            }
        }
    }
}

Read data from DataSet

Posted on by coder 
   


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, &#039;=&#039;));
         }
      }
      catch(Exception e)
      {
         Console.WriteLine("Error: " + e);
      }
      finally
      {
         conn.Close();
      }
   }  
}

Using Multi Tabled Datasets

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.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)
                    {
                    }
                }
            }
        }
    }
}

Using Datasets

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