DataBase Operations Using DataSet

Let's see in this article how to use a dataset.

Consider a table called employee having two columns employeeid and eployeename.

Here we will use the five buttons insert, update, delete, first, previous, next and new.

Using  Microsoft.VisualBasic;
Class declarations
sqlConnection Con;sqldataadapter da;sqlcomandbuilder cb;dataset ds;int rno;

Under form load

Con=new  OleDbConnection("User Id=sa;pwd=123;database=dbname");
Da=new sqldataadapter("select * from employee",con);
Ds=new dataset();
Da.missingschemaschema=MissingSchemaAction.AddWithkey;
Da.Fill(ds,"employee");
Showdata();
Private  Void ShowData()
{
    textBox1.Text=ds.tables[].Rows[rno][0].ToString();
    textBox2.Text=ds.tables[].Rows[rno][1].ToString();
}

Under First Button

Rno=0;
ShowData();

Under previous  Button

if( rno>0);
{rno-=1; showdata();}
Else
messageBox.Show("First Record");

Under Next  Button

if( rno<ds.Tables[0].Rows.Count-1)
    {rno+=1; showdata();}
Else
messageBox.Show("Last  Record");

Under Last  Button

Rno=ds.Tables[0].rows.count-1;
Showdata();

Under New Button

Textbox1.text=textbox2.text="";
Textbox1.readonly=false;textBox1.Focus();

How to add a New Record

To add a new record to the datatable adopt the following process:

1. create a new empty row by calling method NewRow() on DataTable

2. Assign values to new row by treating it as a single dimensional array

3. call add method on data table and add row to RowsCollection.

Under InserButton

DataRow dr=ds.Tables[0].NewRow();
Dr[0]=textBox1.Text;
Dr[1]=textBox2.Text;
Ds.tables[0].Rows.Add(dr);
MessageBox.show("Record Added");
textBox1.Readonly=true;
button1.PerformClick();

Under Update Button

Ds.tables[0].Rows[rno][1]=textBox1.Text;

Under DeleteButton

Ds.tables[0].Rows[rno].Delete();
MessageBox.show("Record Deleted");

Under save Button

Cb=new SqlCommandBuilder(da);
Da.Update(ds,"students");
MessageBox.Show("Data Saved to database");

Under search Button

Int employeeid=int.parse(interaction.inputBox("Enter id to search","search",""100,100));
Datarow dr=ds.tables[0].Rows.Finf(employeeid);
If(dr!=null)
{
    Textbox1.text=dr[0].ToString();
    TextBox2.Text=dr[1].ToString();
}
Else
MessageBox.Show("Invalid  id");}