BookStock - Samples to Show Simple ADO.NET Connectivity

By: Saurabh Nandu - Thursday, January 18, 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/bookstock.zip" class="wbox">bookstock.zip</a> (33kb)</td> <td width="25%" class="outline">beta1</td> </tr> </table> </div> Introduction 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 DataBind TextBoxes to a DataSet. Please note that this code is specific to .NET SDK beta1 and won't work on any other SDK.  Requirements 1) .NET SDK Beta1 (This example will not work with the future 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.ComponentModel; using System.WinForms; using System.Data.ADO ; using System.Data ; using System.Threading ; //Class to add data into a Ms Access 2000 database. book.mdb using ADO.NET public class DataAdd : System.WinForms.Form { //Required by the Win Forms designer 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; //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. 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() ; } //Clean up any resources being used public override void Dispose() { base.Dispose(); components.Dispose(); } //The main entry point for the application. public static void Main(string[] args) { Application.Run(new DataAdd()); } //Method to get connected with the Database and update the Book Id. //TextBox with the current number of records in the database plus one. public void GetConnected() { //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. try{ statusBar.Text="Please Wait, Connecting ...." ; string strConn="Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=book.mdb" ; //if you have a ODBC System Dsn use the below code //string strConn="Data Source=YourDsn" ; ADOConnection myConn = new ADOConnection(strConn) ; //Make a Select Command string strCom = "Select bookid from bookstock" ; ADOCommand myCommand =new ADOCommand(strCom,myConn); myConn.Open(); ADODataReader reader; //Execute the command and get the Data into "reader" myCommand.Execute(out reader) ; 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"; } catch(Exception e) { MessageBox.Show("Error in connecting!"+e.ToString(),"Error", MessageBox.IconExclamation); } } //Required method for Designer support private void InitializeComponent() { //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 } //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. 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" ; //if you have a ODBC System Dsn use the below code //string strConn="Data Source=YourDsn" ; ADOConnection myConn = new ADOConnection(strConn) ; myConn.Open(); //the string to get values from the textboxes and //form an "INSERT INTO" SQL statement. 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) ; //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", MessageBox.IconExclamation); } } catch(Exception ed) { MessageBox.Show("Error in Saving "+ed.ToString(), "Error", MessageBox.IconExclamation); } } //This method is called when the help button is clicked. 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>   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.WinForms; using System.Data.ADO; using System.Data; //Class to demonstrate how to view data from a database using ADO.NET. //This example also uses Data Binding. public class DataView : System.WinForms.Form { //Required by the Win Forms designer 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; //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. public DataView() { //Connect to the Database. GetConnected() ; // Required for Win Form Designer support InitializeComponent(); } //Clean up any resources being used public override void Dispose() { base.Dispose(); components.Dispose(); } //The main entry point for the application. public static void Main(string[] args) { Application.Run(new DataView()); } //This method connects to the database and returns a Dataset Object. public void GetConnected() { try { //make a ADOConnection string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=book.mdb" ; ADOConnection myConn =new ADOConnection(strCon) ; string strCom="SELECT * FROM bookstock" ; //Make a DataSet myDataSet = new DataSet() ; myConn.Open() ; //Using the ADODataSetCommand execute the query ADODataSetCommand myCommand = new ADODataSetCommand(strCom,myConn) ; //Fill the Data set with the Table 'bookstock' myCommand.FillDataSet(myDataSet,"bookstock") ; //Close the ADOConnection myConn.Close() ; } catch(Exception e) { MessageBox.Show("Error!"+e.ToString(),"Error", MessageBox.IconExclamation); } } //Required method for Designer support private void InitializeComponent() { //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 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"); //call the method to DataBind GetListManager() ; } //This method get the ListManager of DataBinding. private void GetListManager(){ // 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. myListManager = this.BindingManager[myDataSet.Tables["bookstock"]]; } //To navigate the ListManager, increment the Position property. private void MoveNext() { if (myListManager.Position == myListManager.Count -1) MessageBox.Show("End of records"); else myListManager.Position += 1; } //To navigate the ListManager, increment the Position property. private void MovePrevious() { if (myListManager.Position == 0) MessageBox.Show("First record"); else myListManager.Position -= 1; } //Move to position 0 in the list. private void MoveFirst(){ myListManager.Position = 0; } //Move to the count -1 position. private void MoveLast(){ myListManager.Position = myListManager.Count - 1; } //Get Help 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); } //Last Record Button Clicked protected void GoLast(object sender, System.EventArgs e) { MoveLast(); } //Next Record Button Clicked protected void GoNext(object sender, System.EventArgs e) { MoveNext(); } //Previous Record Button Clicked protected void GoPrevious(object sender, System.EventArgs e) { MovePrevious(); } //First Record Button Clicked 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.WinForms; using System.Data.ADO; using System.Data; //Class for viewing, editing and deleting data in a Ms Access 2000 database public class DataEdit : System.WinForms.Form { //Required by the Win Forms designer 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; //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 public DataEdit() { // Required for Win Form Designer support InitializeComponent(); //Connect to the Database. GetConnected() ; } //Clean up any resources being used public override void Dispose() { base.Dispose(); components.Dispose(); } //This method connects to the database and returns a Dataset Object. public void GetConnected() { try { statusBar.Text="Please Wait Connecting to Database..."; //make a ADOConnection string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=book.mdb" ; ADOConnection myConn=new ADOConnection(strCon) ; string strCom="SELECT * FROM bookstock" ; //Make a DataSet myDataSet = new DataSet() ; myConn.Open() ; //Using the ADODataSetCommand execute the query ADODataSetCommand myCommand = new ADODataSetCommand(strCom,myConn) ; //Fill the Data set with the Table 'bookstock' myCommand.FillDataSet(myDataSet,"bookstock") ; //Close the ADOConnection myConn.Close() ; //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. 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"); //call the method to DataBind GetListManager() ; isBound=true ; } statusBar.Text="Connected - Now you can Update or Delete Records."; } catch(Exception e) { MessageBox.Show("Error! "+e.ToString(),"Error", MessageBox.IconExclamation); } } //The main entry point for the application. public static void Main(string[] args) { Application.Run(new DataEdit()); } //Required method for Designer support private void InitializeComponent() { //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 } //This method get the ListManager of DataBinding. private void GetListManager(){ // 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. myListManager = this.BindingManager[myDataSet.Tables["bookstock"]]; } //Delete Button Clicked //This first deletes the row from the DataSet object and then //It updates the database with the updated DataSet 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" ; ADOConnection myConn = new ADOConnection(strCon) ; myConn.Open() ; string strDele="SELECT * FROM bookstock" ; ADODataSetCommand myCommand = new ADODataSetCommand(strDele,myConn); //Delete the row from the dataset. myDataSet.Tables["bookstock"].Rows[myListManager.Position].Delete() ; //the 'Update()' method of the ADODataSetCommand updates the //database with the changed DataSet. myCommand.Update(myDataSet,"bookstock"); statusBar.Text="Record Deleted" ; myConn.Close() ; } catch(Exception ed) { MessageBox.Show("Error! "+ed.ToString(),"Error", MessageBox.IconExclamation); } } //Update Button Clicked //This first connects to the database and updates the row //Then is calls the GetConneted() //which again reinitializes the DataSet. protected void GoUpdate(object sender, System.EventArgs e) { int i=myListManager.Position ; try { //connecting to the database string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=book.mdb" ; ADOConnection myConn = new ADOConnection(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; ADOCommand myCommand = new ADOCommand(strUpdt,myConn); myCommand.ExecuteNonQuery(); statusBar.Text="Record Updated" ; myConn.Close() ; //make the DataSet and ListManager object 'null' so that we can //give then new values myDataSet=null ; myListManager= null; //remove the bindings to the textboxes //so that they can rebound with the updated Database 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; } //call the GetConnected method GetConnected(); } catch(Exception ed) { MessageBox.Show("Error! "+ed.ToString(),"Error", MessageBox.IconExclamation); } myListManager.Position=i ; } //To navigate the ListManager, increment the Position property. private void MoveNext() { if (myListManager.Position == myListManager.Count -1) MessageBox.Show("End of records"); else myListManager.Position += 1; } //To navigate the ListManager, decrement the Position property. private void MovePrevious() { if (myListManager.Position == 0) MessageBox.Show("First record"); else myListManager.Position -= 1; } //Move to position 0 in the list. private void MoveFirst(){ myListManager.Position = 0; } //Move to the count -1 position. private void MoveLast(){ myListManager.Position = myListManager.Count - 1; } //Get Help 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); } //Last Record Button Clicked protected void GoLast(object sender, System.EventArgs e) { MoveLast(); } //Next Record Button Clicked protected void GoNext(object sender, System.EventArgs e) { MoveNext(); } //Previous Record Button Clicked protected void GoPrevious(object sender, System.EventArgs e) { MovePrevious(); } //First Record Button Clicked protected void GoFirst(object sender, System.EventArgs e) { MoveFirst(); } } }</pre> </td> </tr> </table>

BookStock - Samples to Show Simple ADO.NET Connectivity

Comments