Welcome to www.Mastercsharp.com - Master C#, the easy way...

BookStock v3- Samples to Show Simple OleDb Managed Provider Interaction

Download File	SDK Version
bookstockv3.zip (31kb)	v1.0.3705

Introduction
The Book Stock examples described in this article, are three Windows Forms applications that perform various operations like Inset / Update / Delete with Ms Access database using the OleDb Managed Provider.

Requirements
1) .NET SDK v1 (1.0.3705) (This example might not work with the other versions.)
2) Ms Access 2000 (Optional, required only if you want to change the database design.)

Database Schema

Table Name - bookstock / File Name - book.mdb
Column Name	Data Type	Description
bookid	Integer	Primary Key Field. Unique identity number of a book.
booktitle	Text	Title of the book.
bookauthor	Text	Author of the book.
bookprice	Integer	Price of the book.
bookstock	Integer	Stock available of the book.

Code
1) DataAdd.cs :- Add Records in to the Database (only relevant code shown).
This application is a very simple application, the only task it performs is to take data from the user and insert it into the database. I use simple a simple SQL INSERT statement to insert the new book information into the database. I do not perform validation before inserting data into the database, but ideally this should be done.

namespace SaurabhData {
  using System;
  using System.Drawing;
  using System.Windows.Forms;
  using System.Data.OleDb;
  using System.Data;
  using System.Threading ;

  /// <summary>
  ///    Class to add data in to a Ms Access 2000 database book.mdb
  ///    using OleDb Managed Provider .
  /// </summary>
  public class DataAdd : Form
  {
    private System.Windows.Forms.Label title;
    private System.Windows.Forms.StatusBar statusBar;
    private System.Windows.Forms.Button helpme;
    private System.Windows.Forms.Button save;
    private System.Windows.Forms.TextBox t_bookstock;
    private System.Windows.Forms.TextBox t_bookprice;
    private System.Windows.Forms.TextBox t_bookauthor;
    private System.Windows.Forms.TextBox t_booktitle;
    private System.Windows.Forms.TextBox t_bookid;
    private System.Windows.Forms.Label l_bookstock;
    private System.Windows.Forms.Label l_bookprice;
    private System.Windows.Forms.Label l_bookauthor;
    private System.Windows.Forms.Label l_booktitle;
    private System.Windows.Forms.Label l_bookid;
    private string strCon = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=book.mdb" ;

   ///<summary>
   ///  This constructor calls 2 methods InitializeComponent()
   ///  to initialize the Windows Form Components and
   ///  starts a thread on the Method GetConnected()  which
   ///  connects to the Database and returns the number of records present
   ///</summary>
    public DataAdd()
    {
      InitializeComponent();

      //put the connection to the database on a Thread so the Form displays quickly..
      ThreadStart tsgc = new ThreadStart( GetConnected ) ;
      Thread tgc = new Thread( tsgc ) ;
      tgc.Start() ;
      //if you don't want to use threading then omit the above
      //3 lines and add the below line
      //GetConnected() ;
    }

    /// <summary>
    ///    Clean up any resources being used
    /// </summary>
    protected override void Dispose( bool disposing )
    {
      base.Dispose( disposing );
    }

    /// <summary>
    ///    The main entry point for the application.
    /// </summary>
    public static void Main( string[] args )
    {
      Application.Run( new DataAdd() );
    }

    ///<summary>
    /// <para>
    ///	  Required method to get connected with the Database and update the Book Id.
    ///   text box with the current number of records in the database plus one.
    /// </para>
    ///</summary>
    public void GetConnected()
    {
      statusBar.Text= "Please Wait, Connecting ...." ;
      //Make the Connection Object
      OleDbConnection myCon = new OleDbConnection( strCon ) ;
      //Make a Select Command
      string sqlStr = "SELECT bookid FROM bookstock ORDER BY bookid DESC" ;
      OleDbCommand myCmd =new OleDbCommand( sqlStr , myCon );
      try
      {
        myCon.Open();
        int recordCount = (Int16) myCmd.ExecuteScalar();
        //update the Book Id textbox with the Number of records present plus one.
        recordCount++;
        t_bookid.Text = recordCount.ToString() ;
        statusBar.Text="Connected - Now you can add records";
      }
      catch(Exception e)
      {
        MessageBox.Show("Error in connecting! "+e.ToString(), "Error");
      }
      finally
      {
        myCmd.Dispose();
        myCon.Close();
        myCon.Dispose();
      }
    }

    /// <summary>
    ///    Initialize the various components of the Form
    /// </summary>
    private void InitializeComponent()
    {
      //The code here is used to design the form
      //Please download the file for the complete code
    }

    ///<summary>
    ///	<para>
    ///	This method is called when the "Save" Button is Clicked.
    ///	It checks if Data is entered into all the fields, and then it proceeds
    ///	with opening an connection whit the database and
    ///inserting the new data in it.
    ///	</para>
    ///</summary>
    protected void saveClick(object sender, System.EventArgs e)
    {
      //code to save the inputted data in to the database
      //no code to validate the data implemented
      if( t_bookid.Text != "" && t_booktitle.Text != "" && t_bookauthor.Text != ""
			&& t_bookprice.Text != "" && t_bookstock.Text != "" )
      {
        OleDbConnection myCon = new OleDbConnection( strCon ) ;
        //the string to get values from the textboxes and form an "INSERT INTO"
        // statement.
        string sqlStr =
	"INSERT INTO bookstock (bookid, booktitle, bookauthor,
	bookprice, bookstock) VALUES ( ";
        sqlStr +=	this.ToSQL( t_bookid.Text ) +", '";
        sqlStr +=	this.ToSQL( t_booktitle.Text ) +"', '";
        sqlStr +=	this.ToSQL( t_bookauthor.Text ) +"', ";
        sqlStr +=	this.ToSQL( t_bookprice.Text ) +", ";
        sqlStr +=	this.ToSQL( t_bookstock.Text ) +")";
        OleDbCommand myCmd = new OleDbCommand( sqlStr , myCon ) ;
        try
        {
          myCon.Open();
          //Execute the statement 
          myCmd.ExecuteNonQuery() ;
          statusBar.Text="Data added to database" ;
          //reset all the textboxes
          int recordCount =int.Parse( t_bookid.Text );
          recordCount++ ;
          t_bookid.Text= recordCount.ToString() ;
          t_booktitle.Text="" ;
          t_bookauthor.Text="" ;
          t_bookprice.Text="" ;
          t_bookstock.Text="" ;
          statusBar.Text="Connected - Now you can add records";
        }
        catch( Exception ed )
        {
          MessageBox.Show("Error in inserting! "+ed.ToString(), "Error");
        }
        finally
        {
          myCmd.Dispose();
          myCon.Close() ;
          myCon.Dispose();
        }
      }
      else
      {
        MessageBox.Show("All fields must be completed.", "Error");
      }
    }

    /// <summary>
    ///   This method is used to convert the input string into a valid
    ///   SQL syntax by escaping a single quote (') by 2 single quotes ('')
    /// </summary>
    /// <param name="input">input string</param>
    /// <returns>quoted string</returns>
    private string ToSQL( string input )
    {
      return input.Replace( "'" , "''" );
    }
  }
}

2) DataView.cs:- View Records from Database and DataBind (Only relevant code).
This is a slightly more complex application, it allows you to navigate to the first, previous, next and last positions within the database. In this example I have Data Bound the TextBoxes to the DataSet object. Data Binding enables you to synchronize the Data between the UI (User Interface) and the Data Source. Please note that when I say Data Source I am not referring to only a database, in the new Windows Forms API you can data bind Controls to various data sources like arrays, collections , DataSet's, DataTable's etc.

The BindingManagerBase class from the System.Windows.Forms namespace is used to synchronize all the controls within a Container Control (generally Form Control, but it can be Panel Control, GroupBox Control etc also) that bind to the same Data Source. Hence in my example since all TextBoxes on my Form are data bound to same DataSet object, I just increment / decrement the Position property of the BindingManagerBase object and all the TextBoxes are updated accordingly.

The BindingContext class from the System.Windows.Forms namespace, is used to manage all the different BindingManagerBase objects (and its child classes like CurrencyManager) for a given Control.
So for example, you have various controls on your Form that data bind to various different data sources, you can use the BindingContext object of the Form to get a reference to the BindingManagerBase objects for each data source.

namespace SaurabhData {

  using System;
  using System.Drawing;
  using System.ComponentModel;
  using System.Windows.Forms;
  using System.Data.OleDb;
  using System.Data;


  /// <summary>
  /// Class to demonstrate how to view data from a database using OleDb Managed Provider
  ///This example also uses Data Binding.
  /// </summary>
  public class DataView : System.Windows.Forms.Form {

    private System.Windows.Forms.Button helpme;
    private System.Windows.Forms.Button lastrec;
    private System.Windows.Forms.Button nextrec;
    private System.Windows.Forms.Button previousrec;
    private System.Windows.Forms.Button firstrec;
    private System.Windows.Forms.TextBox t_bookstock;
    private System.Windows.Forms.TextBox t_bookprice;
    private System.Windows.Forms.TextBox t_bookauthor;
    private System.Windows.Forms.TextBox t_booktitle;
    private System.Windows.Forms.TextBox t_bookid;
    private System.Windows.Forms.Label l_bookstock;
    private System.Windows.Forms.Label l_bookprice;
    private System.Windows.Forms.Label l_bookauthor;
    private System.Windows.Forms.Label l_booktitle;
    private System.Windows.Forms.Label l_bookid;
    private System.Windows.Forms.Label label1;
    private System.Windows.Forms.StatusBar statusBar;
    private System.Data.DataSet myDataSet ;
    private BindingManagerBase myBind;



    ///<summary>
    ///	This is the constructor of the class which calls 2 methods.
    ///	GetConnected() to connect to the database and get the data
    ///	from the database.
    ///	InitilizeComponents() this method is called to initialize the form components.
    ///</summary>
    public DataView()
    {
      //Connect to the Database.
      GetConnected() ;
      InitializeComponent();
    }

    /// <summary>
    ///    Clean up any resources being used
    /// </summary>
    protected override void Dispose( bool disposing )
    {
      base.Dispose( disposing );
    }

    /// <summary>
    ///    The main entry point for the application.
    /// </summary>
    public static void Main(string[] args) {
      Application.Run(new DataView());
    }

    ///<summary>
    ///	This method connects to the database and returns a Dataset Object.
    ///</summary>
    public void GetConnected()
    {
      string strCon = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=book.mdb" ;
      string sqlStr = "SELECT * FROM bookstock" ;
      //make a OleDbConnection object
      OleDbConnection myCon	=new OleDbConnection( strCon ) ;
      //Make a DataSet object
      myDataSet = new DataSet() ;
      try
      {
        //Using the OleDbDataAdapter execute the query
        OleDbDataAdapter myAdapter = new OleDbDataAdapter( sqlStr , myCon ) ;
        //Fill the DataSet with the Table 'bookstock'
        myAdapter.Fill(myDataSet,"bookstock") ;
        myAdapter.Dispose();
      }
      catch(Exception e)
      {
        MessageBox.Show("Error in connecting! "+e.ToString(), "Error");
      }
      finally
      {
        //Close the OleDbConnection
        myCon.Close() ;
        myCon.Dispose();
      }
    }

    private void InitializeComponent()
    {

      //The code here is used to design the form
      //Please download the file for the complete code

      //DataBind the TextBox using the "DataBindings" property of the
      //TextBox control. Since we are Data Binding to the "Text" property
      //of the TextBox we pass it as the first parameter
      //The DataSet object is passed as the second parameter
      //The Table Name with the Field Name is passed as the third parameter
      t_bookid.DataBindings.Add("Text", myDataSet, "bookstock.bookid");

      t_bookstock.DataBindings.Add("Text", myDataSet, "bookstock.bookstock");

      t_booktitle.DataBindings.Add("Text", myDataSet, "bookstock.booktitle");

      t_bookprice.DataBindings.Add("Text", myDataSet, "bookstock.bookprice");

      t_bookauthor.DataBindings.Add("Text", myDataSet, "bookstock.bookauthor");

      //Get the BindingManagerBase object for this data source
      myBind= this.BindingContext [myDataSet, "bookstock"];
    }

    ///<summary>
    ///	To navigate the BindingManagerBase, increment the Position property.
    ///</summary>	
    private void MoveNext()
    {
      if (myBind.Position == myBind.Count -1)
       	MessageBox.Show("End of records");
      else
        myBind.Position += 1;
    }

    ///<summary>
    ///	To navigate the BindingManagerBase, increment the Position property.
    ///</summary>
    private void MovePrevious(){
      if (myBind.Position == 0)
        MessageBox.Show("First record");
      else
        myBind.Position -= 1;
    }

    ///<summary>
    ///	Move to position 0 in the list.
    ///</summary>
    private void MoveFirst(){
      myBind.Position = 0;
    }

    ///<summary>
    /// 	Move to the last position.
    ///</summary>
    private void MoveLast(){
      myBind.Position = myBind.Count - 1;
    }

    ///<summary>
    ///	Last Record Button Clicked
    ///</summary>
    protected void GoLast(object sender, System.EventArgs e)
    {
      MoveLast();
    }

    ///<summary>
    ///	Next Record Button Clicked
    ///</summary>
    protected void GoNext(object sender, System.EventArgs e)
    {
      MoveNext();
    }

    ///<summary>
    /// 	Previous Record Button Clicked
    ///</summary>
    protected void GoPrevious(object sender, System.EventArgs e)
    {
      MovePrevious();
    }

    ///<summary>
    /// 	First Record Button Clicked
    ///</summary>
    protected void GoFirst(object sender, System.EventArgs e)
    {
      MoveFirst();
    }
  }
}

3) DataEdit.cs :- View, Edit, Delete Records in to the Database (only relevant code shown).
The last application of this example shows you the actual benefits of Data Binding your Windows Forms Controls. In this example besides enabling record navigation (as shown in above application), you can also Update and Delete records.
As I said earlier Data Binding helps synchronize the data between the UI and the data source, so as soon as you make any update/changes to as of the records (TextBoxes) and click any button the changes are persisted back to the data source, a DataSet in our case, from then its in your hands if you want to reconcile the DataSet with the DataBase or not.

One important point to note is that while Data Binding to multiple controls always call the EndCurrentEdit method of the BindingManagerBase class indicating that you have finished making the changes before updating the database.
In case you fail to call the EndCurrentEdit method and your Data Source is a DataSet the changes will get reflected in the DataSet, but the RowState property of the DataRow that was updated will remain as Unchanged. Now if you call the Update method (of the OleDbDataAdapter / SqlDataAdapter class) on such a DataSet no changes will be reflected in the database, but if you are writing the result out to a XML file using the WriteXml method (of the DataSet object) then the changes will be correctly reflected.
If you call the EndCurrentEdit method then the RowState property of the DataRow that was updated will be changed to Modified and now when you call the Update method (of the OleDbDataAdapter / SqlDataAdapter ) the changes will be correctly reflected in the database, but if you write the changes to a XML file using the WriteXml method (of the DataSet class) these changes will not get reflected. You will have to call the AcceptChanges method on the DataSet if you want these changes to be correctly reflected to your XML file.

namespace SaurabhData {

  using System;
  using System.Drawing;
  using System.ComponentModel;
  using System.Windows.Forms;
  using System.Data.OleDb;
  using System.Data;

  /// <summary>
  ///   Class for viewing, editing and deleting data in a
  ///   Ms Access 2000 database book.mdb
  /// </summary>
  public class DataEdit : System.Windows.Forms.Form {

    private System.Windows.Forms.Button delete;
    private System.Windows.Forms.Button update;
    private System.Windows.Forms.Button helpme;
    private System.Windows.Forms.Button lastrec;
    private System.Windows.Forms.Button nextrec;
    private System.Windows.Forms.Button previousrec;
    private System.Windows.Forms.Button firstrec;
    private System.Windows.Forms.TextBox t_bookstock;
    private System.Windows.Forms.TextBox t_bookprice;
    private System.Windows.Forms.TextBox t_bookauthor;
    private System.Windows.Forms.TextBox t_booktitle;
    private System.Windows.Forms.TextBox t_bookid;
    private System.Windows.Forms.Label l_bookstock;
    private System.Windows.Forms.Label l_bookprice;
    private System.Windows.Forms.Label l_bookauthor;
    private System.Windows.Forms.Label l_booktitle;
    private System.Windows.Forms.Label l_bookid;
    private System.Windows.Forms.Label label1;
    private System.Windows.Forms.StatusBar statusBar;
    private System.Data.DataSet myDataSet ;
    private BindingManagerBase myBind;
    private string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=book.mdb" ;
    private OleDbConnection myCon;
    private OleDbDataAdapter myAdapter ;


    ///<summary>
    ///	<para>
    ///	 This is the Constructor of the class, it calls 2 methods in it.
    ///	 GetConnected()- This method fills the DataSet
    ///	 InitializeComponent() - This method initializes the WinForm Components
    ///	</para>
    ///</summary>
    public DataEdit() {

      //Connect to the Database.
      GetConnected() ;
      InitializeComponent();
    }

    /// <summary>
    ///    Clean up any resources being used
    /// </summary>
    protected override void Dispose( bool disposing )
    {
      base.Dispose( disposing );
      if( myAdapter != null )
        myAdapter.Dispose();
      if( myCon != null )
        myCon.Dispose();
    }

    ///<summary>
    ///	This method connects to the database and returns a Dataset Object.
    ///</summary>
    public void GetConnected()
    {
      //make a OleDbConnection object
      myCon = new OleDbConnection( strCon ) ;
      string sqlStr = "SELECT * FROM bookstock" ;
      //Make a DataSet object
      myDataSet = new DataSet() ;
      //Using the OleDbDataAdapter execute the query
      myAdapter = new OleDbDataAdapter( sqlStr , myCon ) ;
      //Build the Update and Delete SQL Statements
      OleDbCommandBuilder myBuilder = new OleDbCommandBuilder( myAdapter );
      try
      {
        //Fill the DataSet with the Table 'bookstock'
        myAdapter.Fill(myDataSet,"bookstock") ;
      }
      catch(Exception e)
      {
        MessageBox.Show("Error in connecting! "+e.ToString(), "Error");
      }
    }

    /// <summary>
    ///    The main entry point for the application.
    /// </summary>
    public static void Main(string[] args) {
      Application.Run(new DataEdit());
    }

    private void InitializeComponent()
    {
      //The code here is used to design the form
      //Please download the file for the complete code

      //Since we are Binding to the Text property of the TextBox we 
      //pass it as the first parameter
      //As the second parameter we pass the DataSet object
      //The third parameter contains the Table Name followed by the Field Name
      //to which the control will data bind
      t_bookid.DataBindings.Add("Text", myDataSet, "bookstock.bookid");
      t_booktitle.DataBindings.Add("Text", myDataSet, "bookstock.booktitle");
      t_bookauthor.DataBindings.Add("Text", myDataSet, "bookstock.bookauthor");
      t_bookprice.DataBindings.Add("Text", myDataSet, "bookstock.bookprice");
      t_bookstock.DataBindings.Add("Text", myDataSet, "bookstock.bookstock");

      //Set the BindingManagerBase
      //Pass the DataSet object and the TableName to bind
      myBind= this.BindingContext [myDataSet, "bookstock"];
    }

    ///<summary>
    /// 	Delete Button Clicked
    ///	<para>
    ///	 This first deletes the row from the DataSet object and then
    ///	 It updates the database with the updated DataSet
    ///	</para>
    ///</summary>
    protected void GoDelete(object sender, System.EventArgs e)
    {
      //Remove the selected record from the DataSet
      myDataSet.Tables[0].Rows[myBind.Position].Delete();
      //Update the database
      myAdapter.Update( myDataSet , "bookstock" );
    }

    ///<summary>
    /// 	Update Button Clicked
    ///	 This method first connects to the database and updates the changes
    ///</summary>
    protected void GoUpdate(object sender, System.EventArgs e)
    {
      //Its very important to call the EndCurrentEdit method
      //of the BindingManagerbase while dealing with complex
      //Databindings, else your changes will not reflect in the
      //database correctly
      myBind.EndCurrentEdit() ;
      //Update the database
      myAdapter.Update( myDataSet , "bookstock" );
    }

    ///<summary>
    ///	To navigate the ListManager, increment the Position property.
    ///</summary>	
    private void MoveNext()
    {
      if (myBind.Position == myBind.Count -1)
        MessageBox.Show("End of records");
      else
        myBind.Position += 1;
    }

    ///<summary>
    ///	To navigate the ListManager, increment the Position property.
    ///</summary>
    private void MovePrevious(){
      if (myBind.Position == 0)
        MessageBox.Show("First record");
      else
        myBind.Position -= 1;
    }

    ///<summary>
    ///	Move to position 0 in the list.
    ///</summary>
    private void MoveFirst(){
      myBind.Position = 0;
    }

    ///<summary>
    /// 	Move to the last position.
    ///</summary>
    private void MoveLast(){
      myBind.Position = myBind.Count - 1;
    }

    ///<summary>
    /// 	Last Record Button Clicked
    ///</summary>
    protected void GoLast(object sender, System.EventArgs e)
    {
      MoveLast();
    }

    ///<summary>
    /// 	Next Record Button Clicked
    ///</summary>
    protected void GoNext(object sender, System.EventArgs e)
    {
      MoveNext();
    }

    ///<summary>
    /// 	Previous Record Button Clicked
    ///</summary>
    protected void GoPrevious(object sender, System.EventArgs e)
    {
      MovePrevious();
    }

    ///<summary>
    /// 	First Record Button Clicked
    ///</summary>
    protected void GoFirst(object sender, System.EventArgs e)
    {
      MoveFirst();
    }
  }
}

Saurabh Nandu - 24 February 2002