BookStock - Samples to Show Simple ADO.NET Connectivity
Add Comment<div align="center"> <table border="0" width="70%" class="outline"> <tr> <td width="25%" class="outline"><b>Download File</b></td> <td width="25%" class="outline"><b>SDK Version</b></td> </tr> <tr> <td width="25%" class="outline"><a href="../../file/bookstock.zip" class="wbox">bookstock.zip</a> (33kb)</td> <td width="25%" class="outline">beta1</td> </tr> </table> </div> <p> <span class="wboxheado">Introduction</span><br> This example consists of 3 samples I have made 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 <b>DataBind</b> TextBoxes to a <b>DataSet</b>. <br> Please note that this code is specific to .NET SDK beta1 and won't work on any other SDK. </p> <p><span class="wboxheado">Requirements</span><br> 1) .NET SDK Beta1 (This example will not work with the future versions.)<br> 2) Ms Access 2000 (Optional, required only if you want to change the database design.)</p> <span class="wboxheado">Database Design</span> <table border="0" width="90%" class="Outline"> <tr> <td width="100%" colspan="3" class="Outline"> <span class="wboxhead">Table Name - bookstock / File Name - book.mdb</span></td> </tr> <tr> <td width="20%" class="Outline"><b>Column Name</b></td> <td width="15%" class="Outline"><b>Data Type</b></td> <td width="65%" class="Outline"><b>Description</b></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> <p> <span class=wboxheado>Code</span><br> 1) <I><b>DataAdd.cs</b> :- Add Records in to the Database (only relevant code shown).</I> </p> <p><img border="0" src="../../img/bookstockdataadd.gif" width="400" height="400"></p> <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.ComponentModel; using System.WinForms; using System.Data.ADO ; using System.Data ; using System.Threading ; <span class="Cmt">//Class to add data into a Ms Access 2000 database. book.mdb using ADO.NET</span> public class DataAdd : System.WinForms.Form { <span class="Cmt">//Required by the Win Forms designer </span> private System.ComponentModel.Container components; private System.WinForms.Label title; private System.WinForms.StatusBar statusBar; private System.WinForms.Button helpme; private System.WinForms.Button save; private System.WinForms.TextBox t_bookstock; private System.WinForms.TextBox t_bookprice; private System.WinForms.TextBox t_bookauthor; private System.WinForms.TextBox t_booktitle; private System.WinForms.TextBox t_bookid; private System.WinForms.Label l_bookstock; private System.WinForms.Label l_bookprice; private System.WinForms.Label l_bookauthor; private System.WinForms.Label l_booktitle; private System.WinForms.Label l_bookid; <span class="Cmt">//The Constructor of the class DataAdd //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 present.</span> public DataAdd() { <span class="Cmt">// Required for Win Form Designer support</span> InitializeComponent(); <span class="Cmt">//put the connection to the database on a Thread so //the Form displays quickly.</span> ThreadStart tsgc = new ThreadStart(GetConnected) ; Thread tgc = new Thread(tsgc) ; tgc.Start() ; <span class="Cmt">//if you don't want threading then omit the above 3 lines and //add the below line //GetConnected() ;</span> } <span class="Cmt">//Clean up any resources being used</span> public override void Dispose() { base.Dispose(); components.Dispose(); } <span class="Cmt">//The main entry point for the application.</span> public static void Main(string[] args) { Application.Run(new DataAdd()); } <span class="Cmt">//Method to get connected with the Database and update the Book Id. //TextBox with the current number of records in the database plus one.</span> public void GetConnected() { <span class="Cmt">//the code below is to connect to the database. //It is put inside a "Try-Catch" to Catch any exceptions //that can occur while connecting to the database.</span> try{ statusBar.Text="Please Wait, Connecting ...." ; string strConn="Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=book.mdb" ; <span class="Cmt">//if you have a ODBC System Dsn use the below code //string strConn="Data Source=YourDsn" ;</span> ADOConnection myConn = new ADOConnection(strConn) ; <span class="Cmt"> //Make a Select Command</span> string strCom = "Select bookid from bookstock" ; ADOCommand myCommand =new ADOCommand(strCom,myConn); myConn.Open(); ADODataReader reader; <span class="Cmt">//Execute the command and get the Data into "reader"</span> myCommand.Execute(out reader) ; int i=0 ; <span class="Cmt">//Get the current number of records present in the database.</span> while(reader.Read()) { i++ ; } i++ ; <span class="Cmt">//update the Book Id textbox with the Number of records //present plus one.</span> t_bookid.Text = i.ToString() ; statusBar.Text="Connected - Now you can Add records"; } catch(Exception e) { MessageBox.Show("Error in connecting!"+e.ToString(),"Error", MessageBox.IconExclamation); } } <span class="Cmt">//Required method for Designer support </span> private void InitializeComponent() { <span class="Cmt">//Here the WinForm Components are declared and Initialized //For explanation purpose this code has been removed //If you want to see the full code download the source code </span> } <span class="Cmt">//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.</span> protected void saveClick(object sender, System.EventArgs e) { <span class="Cmt">//code to save the inputted data in to the database //no code to validate the data implemented</span> 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" ; <span class="Cmt">//if you have a ODBC System Dsn use the below code //string strConn="Data Source=YourDsn" ;</span> ADOConnection myConn = new ADOConnection(strConn) ; myConn.Open(); <span class="Cmt">//the string to get values from the textboxes and //form an "INSERT INTO" SQL statement.</span> string strInsert = "INSERT INTO bookstock (bookid, booktitle," +" bookauthor, bookprice, bookstock) VALUES ( " +t_bookid.Text+", '" +t_booktitle.Text+"' , '" +t_bookauthor.Text+"' , " +t_bookprice.Text+", " +t_bookstock.Text+")"; ADOCommand inst = new ADOCommand(strInsert,myConn) ; <span class="Cmt">//Execute the statement </span> inst.ExecuteNonQuery() ; statusBar.Text="Data Added to Database " ; <span class="Cmt">//reset all the textboxes</span> 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", MessageBox.IconExclamation); } } catch(Exception ed) { MessageBox.Show("Error in Saving "+ed.ToString(), "Error", MessageBox.IconExclamation); } } <span class="Cmt">//This method is called when the help button is clicked.</span> protected void helpClick(object sender, System.EventArgs e) { MessageBox.Show("Book Stock- Data Addition program, by Saurabh Nandu," +"E-mail: saurabh@mastercsharp.com", "About ...", MessageBox.IconInformation); } } }</pre> </td> </tr> </table> <P> </P> <P>2)<I> <b>DataView.cs</b>:- View Records from Database and DataBind (Only relevant code).</I></P> <P><img border="0" src="../../img/bookstockdataview.gif" width="400" height="400"></P> <table border="0" width="100%" class="Code"> <tr> <td width="100%"> <pre>namespace SaurabhData { using System; using System.Drawing; using System.ComponentModel; using System.WinForms; using System.Data.ADO; using System.Data; <span class="Cmt">//Class to demonstrate how to view data from a database using ADO.NET. //This example also uses Data Binding. </span> public class DataView : System.WinForms.Form { <span class="Cmt"> //Required by the Win Forms designer </span> private System.ComponentModel.Container components; private System.WinForms.Button helpme; private System.WinForms.Button lastrec; private System.WinForms.Button nextrec; private System.WinForms.Button previousrec; private System.WinForms.Button firstrec; private System.WinForms.TextBox t_bookstock; private System.WinForms.TextBox t_bookprice; private System.WinForms.TextBox t_bookauthor; private System.WinForms.TextBox t_booktitle; private System.WinForms.TextBox t_bookid; private System.WinForms.Label l_bookstock; private System.WinForms.Label l_bookprice; private System.WinForms.Label l_bookauthor; private System.WinForms.Label l_booktitle; private System.WinForms.Label l_bookid; private System.WinForms.Label label1; private System.WinForms.StatusBar statusBar; private System.Data.DataSet myDataSet ; private ListManager myListManager; <span class="Cmt">//This is the constructor of the class which call 2 methods. //GetConnected() to connect to the database and get the data //into a database. //InitilizeComponents() this method is called to initialize the //form components.</span> public DataView() { <span class="Cmt">//Connect to the Database.</span> GetConnected() ; <span class="Cmt">// Required for Win Form Designer support</span> InitializeComponent(); } <span class="Cmt"> //Clean up any resources being used</span> public override void Dispose() { base.Dispose(); components.Dispose(); } <span class="Cmt">//The main entry point for the application.</span> public static void Main(string[] args) { Application.Run(new DataView()); } <span class="Cmt">//This method connects to the database and returns a Dataset Object.</span> public void GetConnected() { try { <span class="Cmt">//make a ADOConnection</span> string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=book.mdb" ; ADOConnection myConn =new ADOConnection(strCon) ; string strCom="SELECT * FROM bookstock" ; <span class="Cmt">//Make a DataSet</span> myDataSet = new DataSet() ; myConn.Open() ; <span class="Cmt">//Using the ADODataSetCommand execute the query </span> ADODataSetCommand myCommand = new ADODataSetCommand(strCom,myConn) ; <span class="Cmt">//Fill the Data set with the Table 'bookstock'</span> myCommand.FillDataSet(myDataSet,"bookstock") ; <span class="Cmt">//Close the ADOConnection</span> myConn.Close() ; } catch(Exception e) { MessageBox.Show("Error!"+e.ToString(),"Error", MessageBox.IconExclamation); } } <span class="Cmt">//Required method for Designer support </span> private void InitializeComponent() { <span class="Cmt">//Here the WinForm Components are declared and Initialized //For explanation purpose this code has been removed //If you want to see the full code download the source code //Code of just some TextBoxes which are DataBound </span> t_bookid.Location = new System.Drawing.Point(184, 56); t_bookid.TabIndex = 0; t_bookid.Size = new System.Drawing.Size(80, 20); t_bookid.Bindings.Add("Text", myDataSet.Tables["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.Bindings.Add("Text",myDataSet.Tables["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.Bindings.Add("Text",myDataSet.Tables["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.Bindings.Add("Text",myDataSet.Tables["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.Bindings.Add("Text",myDataSet.Tables["bookstock"],"bookauthor"); <span class="Cmt">//call the method to DataBind</span> GetListManager() ; } <span class="Cmt">//This method get the ListManager of DataBinding.</span> private void GetListManager(){ <span class="Cmt">// Get the ListManager for the bound control using the // BindingManager of the Win Form. To get the ListManager, // pass the data source of the desired ListManager to the // BindingManager.</span> myListManager = this.BindingManager[myDataSet.Tables["bookstock"]]; } <span class="Cmt">//To navigate the ListManager, increment the Position property.</span> private void MoveNext() { if (myListManager.Position == myListManager.Count -1) MessageBox.Show("End of records"); else myListManager.Position += 1; } <span class="Cmt">//To navigate the ListManager, increment the Position property.</span> private void MovePrevious() { if (myListManager.Position == 0) MessageBox.Show("First record"); else myListManager.Position -= 1; } <span class="Cmt"> //Move to position 0 in the list.</span> private void MoveFirst(){ myListManager.Position = 0; } <span class="Cmt"> //Move to the count -1 position.</span> private void MoveLast(){ myListManager.Position = myListManager.Count - 1; } <span class="Cmt">//Get Help</span> protected void GoHelp(object sender, System.EventArgs e) { MessageBox.Show("Book Stock- Data Viewing program, by Saurabh Nandu," +" E-mail: saurabh@mastercsharp.com", "About ...", MessageBox.IconInformation); } <span class="Cmt">//Last Record Button Clicked</span> protected void GoLast(object sender, System.EventArgs e) { MoveLast(); } <span class="Cmt"> //Next Record Button Clicked</span> protected void GoNext(object sender, System.EventArgs e) { MoveNext(); } <span class="Cmt">//Previous Record Button Clicked</span> protected void GoPrevious(object sender, System.EventArgs e) { MovePrevious(); } <span class="Cmt"> //First Record Button Clicked</span> protected void GoFirst(object sender, System.EventArgs e) { MoveFirst(); } } }</pre> </td> </tr> </table><P> </P> <P>3) <I><b>DataEdit.cs</b> :- View, Edit, Delete Records in to the Database (only relevant code shown).</I></P> <P><img border="0" src="../../img/bookstockdataedit.gif" width="400" height="450"></P> <table border="0" width="100%" class="Code"> <tr> <td width="100%"> <pre>namespace SaurabhData { using System; using System.Drawing; using System.ComponentModel; using System.WinForms; using System.Data.ADO; using System.Data; <span class="Cmt">//Class for viewing, editing and deleting data in a Ms Access 2000 database</span> public class DataEdit : System.WinForms.Form { <span class="Cmt">//Required by the Win Forms designer </span> private System.ComponentModel.Container components; private System.WinForms.Button delete; private System.WinForms.Button update; private System.WinForms.Button helpme; private System.WinForms.Button lastrec; private System.WinForms.Button nextrec; private System.WinForms.Button previousrec; private System.WinForms.Button firstrec; private System.WinForms.TextBox t_bookstock; private System.WinForms.TextBox t_bookprice; private System.WinForms.TextBox t_bookauthor; private System.WinForms.TextBox t_booktitle; private System.WinForms.TextBox t_bookid; private System.WinForms.Label l_bookstock; private System.WinForms.Label l_bookprice; private System.WinForms.Label l_bookauthor; private System.WinForms.Label l_booktitle; private System.WinForms.Label l_bookid; private System.WinForms.Label label1; private System.WinForms.StatusBar statusBar; private System.Data.DataSet myDataSet ; private ListManager myListManager; private bool isBound=false; <span class=cmt>//This is the Constructor of the class, it calls 2 methods in it. //InitializeComponent().This method initializes the WinForm Components //This GetConnected() method gets connected to the database</span> public DataEdit() { <span class="Cmt">// Required for Win Form Designer support</span> InitializeComponent(); <span class="Cmt">//Connect to the Database.</span> GetConnected() ; } <span class="Cmt">//Clean up any resources being used</span> public override void Dispose() { base.Dispose(); components.Dispose(); } <span class="Cmt">//This method connects to the database and returns a Dataset Object.</span> public void GetConnected() { try { statusBar.Text="Please Wait Connecting to Database..."; <span class="Cmt">//make a ADOConnection</span> string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=book.mdb" ; ADOConnection myConn=new ADOConnection(strCon) ; string strCom="SELECT * FROM bookstock" ; <span class="Cmt">//Make a DataSet</span> myDataSet = new DataSet() ; myConn.Open() ; <span class="Cmt">//Using the ADODataSetCommand execute the query</span> ADODataSetCommand myCommand = new ADODataSetCommand(strCom,myConn) ; <span class="Cmt">//Fill the Data set with the Table 'bookstock'</span> myCommand.FillDataSet(myDataSet,"bookstock") ; <span class="Cmt">//Close the ADOConnection</span> myConn.Close() ; <span class="Cmt">//DataBind the Textboxes //We put the DataBinding code in this method since every time a //record is Edited or Deleted we Update the Database and //re-connect to get a fresh copy of the Updated database //but if a Component is already DataBound you cannot DataBind //it again without releasing the previous bindings , so //we put a simple check to see if the components are Data Bound. </span> if(!isBound) { t_bookid.Bindings.Add("Text",myDataSet.Tables["bookstock"],"bookid"); t_booktitle.Bindings.Add("Text",myDataSet.Tables["bookstock"],"booktitle"); t_bookauthor.Bindings.Add("Text",myDataSet.Tables["bookstock"],"bookauthor"); t_bookprice.Bindings.Add("Text",myDataSet.Tables["bookstock"],"bookprice"); t_bookstock.Bindings.Add("Text",myDataSet.Tables["bookstock"],"bookstock"); <span class="Cmt"> //call the method to DataBind</span> GetListManager() ; isBound=true ; } statusBar.Text="Connected - Now you can Update or Delete Records."; } catch(Exception e) { MessageBox.Show("Error! "+e.ToString(),"Error", MessageBox.IconExclamation); } } <span class="Cmt"> //The main entry point for the application.</span> public static void Main(string[] args) { Application.Run(new DataEdit()); } <span class="Cmt">//Required method for Designer support</span> private void InitializeComponent() { <span class="Cmt">//Here the WinForm Components are declared and Initialized //For explanation purpose this code has been removed //If you want to see the full code download the source code</span> } <span class="Cmt">//This method get the ListManager of DataBinding.</span> private void GetListManager(){ <span class="Cmt"> // Get the ListManager for the bound control using the BindingManager // of the Win Form. To get the ListManager, pass the data source of the // desired ListManager to the BindingManager.</span> myListManager = this.BindingManager[myDataSet.Tables["bookstock"]]; } <span class="Cmt">//Delete Button Clicked //This first deletes the row from the DataSet object and then //It updates the database with the updated DataSet</span> protected void GoDelete(object sender, System.EventArgs e) { try { <span class="Cmt">//connect to the database</span> string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=book.mdb" ; ADOConnection myConn = new ADOConnection(strCon) ; myConn.Open() ; string strDele="SELECT * FROM bookstock" ; ADODataSetCommand myCommand = new ADODataSetCommand(strDele,myConn); <span class="Cmt">//Delete the row from the dataset.</span> myDataSet.Tables["bookstock"].Rows[myListManager.Position].Delete() ; <span class="Cmt">//the 'Update()' method of the ADODataSetCommand updates the //database with the changed DataSet.</span> myCommand.Update(myDataSet,"bookstock"); statusBar.Text="Record Deleted" ; myConn.Close() ; } catch(Exception ed) { MessageBox.Show("Error! "+ed.ToString(),"Error", MessageBox.IconExclamation); } } <span class="Cmt">//Update Button Clicked //This first connects to the database and updates the row //Then is calls the GetConneted() //which again reinitializes the DataSet.</span> protected void GoUpdate(object sender, System.EventArgs e) { int i=myListManager.Position ; try { <span class="Cmt">//connecting to the database</span> string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=book.mdb" ; ADOConnection myConn = new ADOConnection(strCon) ; myConn.Open() ; <span class="Cmt">//update the database</span> 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; ADOCommand myCommand = new ADOCommand(strUpdt,myConn); myCommand.ExecuteNonQuery(); statusBar.Text="Record Updated" ; myConn.Close() ; <span class="Cmt">//make the DataSet and ListManager object 'null' so that we can //give then new values</span> myDataSet=null ; myListManager= null; <span class="Cmt">//remove the bindings to the textboxes //so that they can rebound with the updated Database</span> if(isBound) { t_bookid.Bindings.Remove(0); t_booktitle.Bindings.Remove( 0); t_bookauthor.Bindings.Remove( 0); t_bookprice.Bindings.Remove( 0); t_bookstock.Bindings.Remove( 0); isBound=false; } <span class="Cmt">//call the GetConnected method</span> GetConnected(); } catch(Exception ed) { MessageBox.Show("Error! "+ed.ToString(),"Error", MessageBox.IconExclamation); } myListManager.Position=i ; } <span class="Cmt">//To navigate the ListManager, increment the Position property.</span> private void MoveNext() { if (myListManager.Position == myListManager.Count -1) MessageBox.Show("End of records"); else myListManager.Position += 1; } <span class="Cmt">//To navigate the ListManager, decrement the Position property.</span> private void MovePrevious() { if (myListManager.Position == 0) MessageBox.Show("First record"); else myListManager.Position -= 1; } <span class="Cmt"> //Move to position 0 in the list.</span> private void MoveFirst(){ myListManager.Position = 0; } <span class="Cmt"> //Move to the count -1 position.</span> private void MoveLast(){ myListManager.Position = myListManager.Count - 1; } <span class="Cmt">//Get Help</span> protected void GoHelp(object sender, System.EventArgs e) { MessageBox.Show("Book Stock- Data Viewing program, by Saurabh Nandu," +" E-mail: saurabh@mastercsharp.com", "About ...", MessageBox.IconInformation); } <span class="Cmt">//Last Record Button Clicked</span> protected void GoLast(object sender, System.EventArgs e) { MoveLast(); } <span class="Cmt"> //Next Record Button Clicked</span> protected void GoNext(object sender, System.EventArgs e) { MoveNext(); } <span class="Cmt">//Previous Record Button Clicked</span> protected void GoPrevious(object sender, System.EventArgs e) { MovePrevious(); } <span class="Cmt"> //First Record Button Clicked</span> protected void GoFirst(object sender, System.EventArgs e) { MoveFirst(); } } }</pre> </td> </tr> </table>