Saturday, August 6, 2011

sql Dat Adapter


  1. Sql data Adapter:

Insert/update/Delete Records:

1.      Establish the connection to the sql server by using sql connection class.
        SqlConnection cnn = new SqlConnection("Data Source = .\\SQLEXPRESS; Initial Catalog = master;Integrated Security= true");

2.      Open the connection
3.      Execute the sql query to insert/update/delete the record(s) by using sql data adapter.
            SqlDataAdapter da = new SqlDataAdapter(new SqlCommand("Insert into student values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "')",cnn));
4.      Use one of the following combinations of properties and methods as per the requirements.
·        InsertCommand.ExecuteNonQuery();
·        UpdateCommand.ExecuteNonQuery();
·        DeleteCommand.ExecuteNonQuery();
·        SelectCommand.ExecuteNonQuery();

da.InsertCommand.ExecuteNonQuery();
            cnn.Close();

Alternately u can use SelectCommand.ExecuteNonQuery(); to perform insert/delete/update operations as shown below.
cnn.Open();
            SqlDataAdapter da = new SqlDataAdapter("Insert into student values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "'",cnn);
            da.SelectCommand.ExecuteNonQuery();
            cnn.Close();

5.      Finally close the connection.

Binding Source

Binding Source:

Retrieving Information from database by using Binding Source:
  1. Establish the connection to the sql server by using sql connection class.
  2. SqlConnection cnn = new SqlConnection("Data Source = abc;
    Initial Catalog = master;Integrated Security= true");
  3. Execute the sql query to retrieve information by using sql data adapter.
  4. SqlDataAdapter da = new SqlDataAdapter("select * from student", cnn);
  5. Fill the values in Data Set by using the sql data adapter.
  6. da.Fill(ds, "student");
  7. Bind the dataset as data source to the binding source.
  8. bs.DataSource = ds.Tables["student"];
  9. Bind the values to the controls.
  10. textBox1.DataBindings.Add("Text", bs, "studnetid");
  11. Repeat the syntax for all the controls. Use the binding source methods to navigate between the values.
  12. bs.MoveFirst();bs.MovePrevious();bs.MoveNext();bs.MoveLast();