In the following program we have created a WinForm. In this form we plan to show all the contents of a table. Here too we have used the OLE DB .NET Data Provider. We plan to add functionality to add, delete and modify the table.
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb ;
namespace AdoProgram1
{
class bank
{
OleDbDataAdapter dest ;
DataSet ds ;
DataTable dt ;
public bank ( )
{
string str =" Data Source = c:\\bank.mdb; Provider = Microsoft.Jet.OLEDB.4.0";
string cmd = "SELECT accno, name, balance from account" ;
dest= new OleDbDataAdapter( cmd, str ) ;
OleDbCommandBuilder obd = new OleDbCommandBuilder( dest ) ;
ds = new DataSet ( ) ;
dest.Fill ( ds, "account" ) ;
dt = ds.Tables["account"] ;
}
public void addnewrec ( int id, String name, int bal )
{
DataRow r = getrowbyid ( id ) ;
if ( r == null )
{
DataRow nrow = dt.NewRow ( ) ;
nrow[0] = id ;
nrow[1] = name ;
nrow[2] = bal ;
dt.Rows.Add ( nrow ) ;
dest.Update ( ds, "account" ) ;
}
else
{
MessageBox.Show (" Record Already Exists") ;
}
}
public void deleterec ( int id )
{
string exp = " accno = " + id ;
DataRow[] r = dt.Select ( exp ) ;
r[0].Delete ( ) ;
dest.Update ( ds, "account" ) ;
}
public void updaterec ( int id, String name, int bal )
{
string exp = " accno = " + id ;
DataRow[] r = dt.Select ( exp ) ;
r[0][1] = name ;
r[0][2] = bal ;
dest.Update ( ds, "account" ) ;
}
public DataRow getrowbyid ( int id )
{
try
{
string exp = " accno =" + id ;
DataRow[] r = dt.Select ( exp ) ;
return r[0] ;
}
catch ( Exception e )
{
return null ;
}
}
public DataSet dset
{
get
{
return ds ;
}
}
}
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.TextBox name;
private System.Windows.Forms.TextBox bal;
private System.Windows.Forms.TextBox acc;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Button searchbut;
private bank b = new bank ( ) ;
private int m_oper ;
private System.Windows.Forms.Button commitbut;
private System.Windows.Forms.DataGrid dgrid;
private System.Windows.Forms.GroupBox groupBox2;
private System.Windows.Forms.GroupBox groupBox1;
private System.Windows.Forms.Button addbut;
private System.Windows.Forms.Button updatebut;
private System.Windows.Forms.Button delbut;
private System.ComponentModel.Container components = null;
public Form1( )
{
m_oper = 0 ;
InitializeComponent( ) ;
dgrid.SetDataBinding ( b.dset, "account" ) ;
}
protected override void Dispose( bool disposing )
{
if( disposing )
{
if ( components != null )
{
components.Dispose ( ) ;
}
}
base.Dispose( disposing ) ;
}
#region Windows Form Designer generated code
private void InitializeComponent ( )
{
//Wizard generated code found in Zip file
}
#endregion
[STAThread]
static void Main ( )
{
Application.Run ( new Form1 ( ) ) ;
}
private void searchbut_Click ( object sender, System.EventArgs e)
{
DataRow r = b.getrowbyid ( Int32.Parse ( acc.Text ) ) ;
if ( r != null )
{
name.Text = r[1].ToString() ;
bal.Text = r[2].ToString ( ) ;
name.Enabled = true ;
bal.Enabled = true ;
}
else
MessageBox.Show ( "Record not found" ) ;
}
private void addbut_Click ( object sender, System.EventArgs e )
{
acc.Enabled = true ;
name.Enabled = true ;
bal.Enabled = true ;
commitbut.Enabled = true ;
searchbut.Enabled = false ;
m_oper = 1 ;
}
private void updatebut_Click ( object sender, System.EventArgs e )
{
acc.Enabled = true ;
name.Enabled = false ;
bal.Enabled = false ;
commitbut.Enabled = true ;
searchbut.Enabled = true ;
m_oper = 2 ;
}
private void delbut_Click ( object sender, System.EventArgs e )
{
acc.Enabled = true ;
name.Enabled = false ;
bal.Enabled = false ;
commitbut.Enabled = true ;
searchbut.Enabled = true ;
m_oper = 3 ;
}
private void commitbut_Click ( object sender, System.EventArgs e )
{
int a = Int32.Parse ( acc.Text ) ;
string n = name.Text ;
int balance = Int32.Parse ( bal.Text ) ;
switch ( m_oper )
{
case 1:
b.addnewrec ( a, n, balance ) ;
break ;
case 2:
b.updaterec ( a, n, balance ) ;
break ;
case 3:
b.deleterec ( a ) ;
break ;
}
acc.Text = "" ;
name.Text = "" ;
bal.Text = "" ;
commitbut.Enabled = false ;
searchbut.Enabled = false ;
acc.Enabled = false ;
name.Enabled = false ;
bal.Enabled = false ;
}
}
}
In this program we have added a control called the Data Grid. We have used the Data Grid control to view the Data. This control is wrapped in a class called the DataGrid class derived from the Control class. We get the following output when we run this program.
To add a new DataRow, we must press the Add button, fill in the entries as shown and press Commit. The values will get added. To delete a row we must type in the accno and press Search. All the values would be shown in the corresponding textboxes. After pressing Commit, the row will get deleted. Same works for updating a row. We must type in the accno and press Search. After doing this we should press Commit for the changes to be made in the data.
If we press the small, depressed arrow on the side of accno field, the rows get sorted according to descending values of accno. On pressing it again they get sorted according to ascending values of accno.
We have wrapped all functionality of adding, deleting and updating the database in a class called bank.
In the constructor of the bank class, we initialized the connection and command string passed it to the constructor of the DataAdapter class. Next we filled the DataSet with account and extracted the table in object dt of the DataTable class.
We have written different functions for adding, deleting and updating rows. We plan to add rows in the table only if the accno is unique. This means every account holder should have just one row. So no two rows with same accno are added. To check this we have written a new function getrowbyid( ). In this method we have used the Select( ) method which gets an array of all DataRow objects that match the filter criteria. The filter criterion in our case is the accno. If such a row is found then it is returned. If such a row is not found an exception will be thrown. If an exception is thrown, we catch it and return just a null. In the addnewrec( ) method if a null is returned by the getrowbyid( ) method it means that no such row is already present. If its so we add a new row. The logic is same as the previous program. If not we just flash a message.
For deletion we have again used the Select( ) method. This method will return the collection of rows satisfying the selection criterion. We will definitely have just one row in the collection because in our table the accno is not repeated. (Such a field, which has unique value and no two rows have the same value, is called the primary key of the table). The Delete( ) method just deletes the specified row. After deleting it from the DataSet we call he Update( ) method to reconcile the changes.
Updating is done by initializing the corresponding field with new values and just calling the Update( ) method. A property dset is also written which gets the DataSet ds.
As shown in the form there are three buttons provided for the three operations viz:- addition of a row, deletion of a row and updating a row, one for searching the table and another for committing the operation. The whole logic behind the working of the form is that we have created a private data member of the bank class in the form class. We have also added an int m_oper, which will hold an int denoting which operation has to be performed. The default operation is 0 i.e. nothing. Events are added for all the buttons.
When the "Add" button is clicked all the texboxes are enabled. The "Commit" button is enabled and the "Search" button is disabled. This is so because we don't have to search the table to add a row. Finally and most importantly m_oper is set to value 1.
When the "Update" button is clicked the Acc no textbox is enabled. The Search button is enabled. This is because we have to search for the record and then update it. On pressing "Search" the search_click( ) event gets called. In this event we first get the row to be updated using the following statement:
DataRow r = b.getrowbyid ( Int32.Parse ( acc.Text ) ) ;
Int32.Parse( ) method converts the text in the acc textbox to an int. This int is passed to the getrowbyid( ) method which returns the specified selected row. After getting the row we set the textboxes with the values from the row and enable them.
name.Text = r[1].ToString ( ) ;
bal.Text = r[2].ToString ( ) ;
We can then change the text in the textboxes to the new values. This is all done in the search_click( ) event. After the control returns back to updatebut_Click( ) event m_oper is set to 2.
When the "Delete" button is clicked a similar thing happens and m_oper is set to 3. We do not change the text in the textboxes and hence the values, because we plan to delete the row and not update it.
After all this is done we press the "Commit" button. This calls the corresponding event. In this event we kept a switch on m_oper and called the corresponding methods of the bank class.
No comments:
Post a Comment