BookStock v2 - Samples to Show Simple OleDb.NET Connectivity

By: Saurabh Nandu - Tuesday, June 26, 2001 Add Comment

<div align="center"> <table border="0" width="70%" class="outline"> <tr> <td width="25%" class="outline">Download File</td> <td width="25%" class="outline">SDK Version</td> </tr> <tr> <td width="25%" class="outline"><a href="../../file/bookstockv2.zip" class="wbox">bookstockv2.zip</a> (41kb)</td> <td width="25%" class="outline">beta2</td> </tr> </table> </div> Introduction The <a href="article.aspx?ArticleID=3&&TopicID=1" class="wbox"> bookstock example</a> I had previously created for .NET SDK beta1 consisted of 3 samples to show how to perform basic ADO.NET connectivity with a Ms Access 2000 Database and how to Add, View , Edit , Delete records from it. It also demonstrates how to DataBind Textboxes to a DataSet object. Since a lot of changes have been made from beta1 to beta2, I have updated this sample to show what are the necessary changes required to convert the code from beta1 to beta2. As you would know that in Beta2 there is no System.Data.ADO namespace but it has been replaced by System.Data.OleDb namespace. Also the System.WinForms namespace has been changed to System.Windows.Forms. Converting the beta1 code to beta2 was very easy and I had to just run a few "Replace" operations on the code. One minor change that has occurred although is the change to the DataBinding method of the Controls. Now we no longer use ListManager, we use BindingsManagerBase class to manage DataBinding of controls. To help you have the exact view of what has changed from beta1 to beta2, in my examples I have made the minimum possible change to make the code working on beta2. Review both the codes side by side to have a bigger picture! Some major changes that have occurred from beta1 to beta2 <table border="0" width="100%" class="outline"> <tr> <td width="50%" class="outline">.NET SDK Beta1</td> <td width="50%" class="outline">.NET SDK Beta2</td> </tr> <tr> <td width="50%" class="outline">System.WinForms Namespace</td> <td width="50%" class="outline">System.Windows.Forms Namespace</td> </tr> <tr> <td width="50%" class="outline">System.ADO Namespace</td> <td width="50%" class="outline">System.OleDb Namespace</td> </tr> <tr> <td width="50%" class="outline">ADOConnection class</td> <td width="50%" class="outline">OleDbConnection class</td> </tr> <tr> <td width="50%" class="outline">ADOCommand class</td> <td width="50%" class="outline">OleDbCommand class</td> </tr> <tr> <td width="50%" class="outline">ADODataSetCommand class</td> <td width="50%" class="outline">OleDbDataAdapter class</td> </tr> <tr> <td width="50%" class="outline">ADODataReader class</td> <td width="50%" class="outline">OleDbDataReader class</td> </tr> <tr> <td width="50%" class="outline">ListManager class</td> <td width="50%" class="outline">BindingsManagerBase class</td> </tr> </table>      Requirements 1) .NET SDK Beta2 (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 Design <table border="0" width="90%" class="Outline"> <tr> <td width="100%" colspan="3" class="Outline"> Table Name - bookstock / File Name - book.mdb</td> </tr> <tr> <td width="20%" class="Outline">Column Name</td> <td width="15%" class="Outline">Data Type</td> <td width="65%" class="Outline">Description</td> </tr> <tr> <td width="20%" class="Outline">bookid</td> <td width="15%" class="Outline">Integer</td> <td width="65%" class="Outline">Primary Key Field. Unique identity number of a book.</td> </tr> <tr> <td width="20%" class="Outline">booktitle</td> <td width="15%" class="Outline">Text</td> <td width="65%" class="Outline">Title of the book.</td> </tr> <tr> <td width="20%" class="Outline">bookauthor</td> <td width="15%" class="Outline">Text</td> <td width="65%" class="Outline">Author of the book. </td> </tr> <tr> <td width="20%" class="Outline">bookprice</td> <td width="15%" class="Outline">Integer</td> <td width="65%" class="Outline">Price of the book.</td> </tr> <tr> <td width="20%" class="Outline">bookstock</td> <td width="15%" class="Outline">Integer</td> <td width="65%" class="Outline">Stock available of the book.</td> </tr> </table>   Code 1) DataAdd.cs :- Add Records in to the Database (only relevant code shown). <img border="0" src="../../img/bookstockdataadd.gif" width="400" height="400"> <table border="0" width="100%" class="Code" cellspacing="0" cellpadding="0"> <tr> <td width="100%"> <pre>namespace SaurabhData { using System; using System.Drawing; using System.Windows.Forms;//A Change to System.WinForms in beta1 using System.Data.OleDb; //A change to System.Data.ADO in beta1 using System.Data; using System.Threading ; ///<summary> /// Class to add data in to a Ms Access 2000 database /// book.mdb using OleDb.NET . ///</summary> public class DataAdd :Form { /// <summary> /// Required by the Win Forms designer /// </summary> private System.ComponentModel.Container components; 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; ///<summary> /// The Constructor of the class DataAdd /// <para> /// This constructor calls 2 methods InitializeComponent() /// to initialize the WinForm Components and /// starts a thread on the Method GetConnected() which /// connects to the Database and returns the number of records /// </para> ///</summary> public DataAdd() { // Required for Win Form Designer support 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 threading then omit the above 3 lines and add //the below line //GetConnected(); } /// <summary> /// Clean up any resources being used /// </summary> public override void Dispose() { base.Dispose(); components.Dispose(); } /// <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. textbox with the current number /// of records in the database plus one. /// </para> ///</summary> public void GetConnected() { //the code below is to connect to the database. //It is put inside a try-catch block to Catch any exceptions //that can occur while connecting to the database. try{ statusBar.Text="Please Wait, Connecting ...." ; string strConn="Provider=Microsoft.Jet.OLEDB.4.0;"+ strConn+="Data Source=book.mdb" ; //Make the Connection Object OleDbConnection myConn = new OleDbConnection(strConn) ; //Make a Select Command string strCom = "Select bookid from bookstock" ; OleDbCommand myCommand =new OleDbCommand(strCom,myConn); myConn.Open(); OleDbDataReader reader; //Execute the command and get the DataReader //This has changed from beta1 where we used //myCommand.ExecuteReader(out reader) reader =myCommand.ExecuteReader() ; int i=0 ; //Get the current number of records present in the database. while(reader.Read()) { i++ ; } i++ ; //update the Book Id textbox with the Number of records present //plus one. t_bookid.Text = i.ToString() ; statusBar.Text="Connected - Now you can Add records"; reader.Close(); myConn.Close(); } catch(Exception e) { MessageBox.Show("Error in connecting! "+e.ToString(), "Error"); } } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with an editor /// </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, if 'yes' /// then it proceeds with opening an connection with /// 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 try { if(t_bookid.Text!=""&&t_booktitle.Text!=""&&t_bookauthor.Text!="" &&t_bookprice.Text!=""&&t_bookstock.Text!="") { string strConn="Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=book.mdb"; OleDbConnection myConn = new OleDbConnection(strConn) ; myConn.Open(); //the string to get values from the textboxes and form an "INSERT INTO" // statement. string strInsert = "INSERT INTO bookstock (bookid, booktitle, bookauthor, "; strInsert+="bookprice, bookstock) VALUES ( "; strInsert += t_bookid.Text+", '"; strInsert += t_booktitle.Text+"', '"; strInsert += t_bookauthor.Text+"', "; strInsert += t_bookprice.Text+", "; strInsert += t_bookstock.Text+")"; OleDbCommand inst = new OleDbCommand(strInsert,myConn) ; //Execute the statement inst.ExecuteNonQuery() ; statusBar.Text="Data Added to Database " ; //reset all the textboxes int i=int.Parse(t_bookid.Text); i++; t_bookid.Text=i.ToString() ; t_booktitle.Text="" ; t_bookauthor.Text="" ; t_bookprice.Text="" ; t_bookstock.Text="" ; statusBar.Text="Connected - Now you can Add records"; myConn.Close() ; } else { MessageBox.Show("All fields must be completed.", "Error"); } } catch(Exception ed) { MessageBox.Show("Error in Saving "+ed.ToString(), "Error"); } } ///<summary> /// This method is called when the help button is clicked. ///</summary> protected void helpClick(object sender, System.EventArgs e) { MessageBox.Show("Book Stock for .NET SDK beta2- Data Addtion program, by Saurabh Nandu, http://www.MasterCSharp.com", "About ..."); } } }</pre> </td> </tr> </table>   2) DataView.cs:- View Records from Database and DataBind (Only relevant code). <img border="0" src="../../img/bookstockdataview.gif" width="400" height="400"> <table border="0" width="100%" class="Code"> <tr> <td width="100%"> <pre>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.NET /// This example also uses data binding. ///</summary> public class DataView : System.Windows.Forms.Form { /// <summary> /// Required by the Win Forms designer /// </summary> private System.ComponentModel.Container components; 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; //This replaces ListManager from beta1 ///<summary> /// <para> /// This is the constructor of the class which calls 2 methods. /// GetConnected() to connect to the database and get the data /// into a database. /// </para> /// <para> /// InitilizeComponents() this method is called to initialize the Form. /// </para> ///</summary> public DataView() { //Connect to the Database. GetConnected() ; // Required for Win Form Designer support InitializeComponent(); } /// <summary> /// Clean up any resources being used /// </summary> public override void Dispose() { base.Dispose(); components.Dispose(); } /// <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 ///</summary> public void GetConnected() { try{ //make a OleDbConnection string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ;"; strCon+=Data Source=book.mdb" ; OleDbConnection myConn=new OleDbConnection(strCon) ; string strCom="SELECT * FROM bookstock" ; //Make a DataSet myDataSet = new DataSet() ; myConn.Open() ; //Using the OleDbDataAdapter execute the query OleDbDataAdapter myCommand=new OleDbDataAdapter(strCom,myConn); //Fill the Data set with the Table 'bookstock' myCommand.Fill(myDataSet,"bookstock") ; //Close the OleDbConnection myConn.Close() ; } catch(Exception e) { MessageBox.Show("Error in connecting! "+e.ToString(), "Error"); } } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with an editor /// </summary> private void InitializeComponent() { //Only DataBinding Code shown t_bookid.Location = new System.Drawing.Point(184, 56); t_bookid.TabIndex = 0; t_bookid.Size = new System.Drawing.Size(80, 20); //DataBind the TextBox using the "DataBindings" property of the //textbox control. Since we are DataBinding to the "Text" property //of the TextBox we pass it as the first parameter //The DataSet object is passed as the second parameter //The TableName with the FieldName is passed as the third parameter t_bookid.DataBindings.Add("Text", myDataSet, "bookstock.bookid"); t_bookstock.Location = new System.Drawing.Point(184, 264); t_bookstock.TabIndex = 4; t_bookstock.Size = new System.Drawing.Size(80, 20); t_bookstock.DataBindings.Add("Text", myDataSet, "bookstock.bookstock"); t_booktitle.Location = new System.Drawing.Point(184, 108); t_booktitle.TabIndex = 1; t_booktitle.Size = new System.Drawing.Size(176, 20); t_booktitle.DataBindings.Add("Text", myDataSet, "bookstock.booktitle"); t_bookprice.Location = new System.Drawing.Point(184, 212); t_bookprice.TabIndex = 3; t_bookprice.Size = new System.Drawing.Size(80, 20); t_bookprice.DataBindings.Add("Text", myDataSet, "bookstock.bookprice"); t_bookauthor.Location = new System.Drawing.Point(184, 160); t_bookauthor.TabIndex = 2; t_bookauthor.Size = new System.Drawing.Size(128, 20); t_bookauthor.DataBindings.Add("Text", myDataSet, "bookstock.bookauthor"); //DataBind the Controls with the DataSet object //and 'bookstock' table 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, decrement 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 count -1 position. ///</summary> private void MoveLast(){ myBind.Position = myBind.Count - 1; } ///<summary> /// Get Help ///</summary> protected void GoHelp(object sender, System.EventArgs e) { MessageBox.Show("Book Stock- Data View for .NET SDK beta2, by Saurabh Nandu, http://www.MasterCSharp.com", "About ..."); } ///<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(); } } }</pre> </td> </tr> </table>  3) DataEdit.cs :- View, Edit, Delete Records in to the Database (only relevant code shown). <img border="0" src="../../img/bookstockdataedit.gif" width="400" height="450"> <table border="0" width="100%" class="Code"> <tr> <td width="100%"> <pre>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 using .NET SDK beta2 /// </summary> public class DataEdit : System.Windows.Forms.Form { /// <summary> /// Required by the Win Forms designer /// </summary> private System.ComponentModel.Container components; 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; //In beta2 we use BindingManagerBase instead of ListManager private bool isBound=false; //Variable to check if controls are bound ///<summary> /// <para> /// This is the Constructor of the class, it calls 2 methods in it. /// InitializeComponent()This method initializes the WinForm /// GetConnected()This method gets connected to the database /// </para> ///</summary> public DataEdit() { // Required for Win Form Designer support InitializeComponent(); //Connect to the DataBase. GetConnected() ; } /// <summary> /// Clean up any resources being used /// </summary> public override void Dispose() { base.Dispose(); components.Dispose(); } ///<summary> /// This method connects to the database and returns a Dataset ///</summary> public void GetConnected() { try{ statusBar.Text="Please Wait Connecting to Database..."; //make a OleDbConnection string strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=book.mdb"; OleDbConnection myConn=new OleDbConnection(strCon) ; string strCom="SELECT * FROM bookstock" ; //Make a DataSet myDataSet = new DataSet() ; myConn.Open() ; //Using the OleDbDataAdapter execute the query //In beta2 we use OleDataAdapter instead of ADODataSetCommand //which was used in beta1 to fill a DataSet OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom,myConn); //Fill the Data set with the Table 'bookstock' //The FillDataSet method of ADODataSetCommand has changed to //Fill method of OleDbDataAdapter myCommand.Fill(myDataSet,"bookstock") ; //Close the OleDbConnection myConn.Close() ; //DataBind the TextBoxes if(!isBound) { //In beta2 we use the "DataBindings" property of the TextBox //control to DataBind the Control //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 TableName followed by the FieldName //to which the control will databind 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"]; isBound=true ; } statusBar.Text="Connected - Now you can Update or Delete Records."; } 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()); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with an editor /// </summary> private void InitializeComponent() { //The code here is used to design the form //Please download the file for the complete code } ///<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) { try{ //connect to the database string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=book.mdb" ; OleDbConnection myConn = new OleDbConnection(strCon) ; myConn.Open() ; string strDele="DELETE FROM bookstock WHERE bookid= "+t_bookid.Text; OleDbCommand myCommand = new OleDbCommand(strDele,myConn); //Delete the record from the database myCommand.ExecuteNonQuery(); //Delete the record from the DataSet myDataSet.Tables["bookstock"].Rows[myBind.Position].Delete() ; myDataSet.Tables["bookstock"].AcceptChanges(); statusBar.Text="Record Deleted" ; myConn.Close() ; } catch(Exception ed) { MessageBox.Show("Error in Deleting! "+ed.ToString(), "Error"); } } ///<summary> ///Update Button Clicked /// <para> /// This first connects to the database and updates the row /// Then is calls the GetConnected() /// which again reinitializes the DataSet. /// </para> ///</summary> protected void GoUpdate(object sender, System.EventArgs e) { int i=myBind.Position ; try{ //connecting to the database string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=book.mdb"; OleDbConnection myConn = new OleDbConnection(strCon) ; myConn.Open() ; //update the database string strUpdt = "UPDATE bookstock SET booktitle='" +t_booktitle.Text+"', bookauthor='" +t_bookauthor.Text+"', bookprice=" +t_bookprice.Text+", bookstock=" +t_bookstock.Text+" WHERE bookid= "+t_bookid.Text; OleDbCommand myCommand = new OleDbCommand(strUpdt,myConn); myCommand.ExecuteNonQuery(); statusBar.Text="Record Updated" ; myConn.Close() ; //make the DataSet object 'null' so that we can give then new values myDataSet=null ; myBind= null; //remove the bindings to the textboxes if(isBound) { t_bookid.DataBindings.Clear(); t_booktitle.DataBindings.Clear(); t_bookauthor.DataBindings.Clear(); t_bookprice.DataBindings.Clear(); t_bookstock.DataBindings.Clear(); isBound=false; } //call the GetConnected method GetConnected(); } catch(Exception ed) { MessageBox.Show("Error in Updating! "+ed.ToString(), "Error"); } myBind.Position=i ; } ///<summary> /// To navigate the BindingManagerBase, increment the Position ///</summary> private void MoveNext() { if (myBind.Position == myBind.Count -1) MessageBox.Show("End of records"); else myBind.Position += 1; } ///<summary> /// To navigate the BindingManagerBase, decrement the Position ///</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 count -1 position. ///</summary> private void MoveLast(){ myBind.Position = myBind.Count - 1; } ///<summary> /// Get Help ///</summary> protected void GoHelp(object sender, System.EventArgs e) { MessageBox.Show("Book Stock- Data Editing for .NET SDK beta2, Deleting program, by Saurabh Nandu, http://www.MasterCSharp.com", "About.."); } ///<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(); } } }</pre> </td> </tr> </table>

BookStock v2 - Samples to Show Simple OleDb.NET Connectivity

Comments