Questions about Dataset Data adapter and how to show data in a form.

Question

I wonder when you should use a data set and Data adapter and when not to.

Do you have to use data adapter if you want to work disconnected from the database? If you use Data adapter, must you also use dataset?

I understand that dataset is a representation of tables in memory and data adapter is somehow a link between a dataset and the database.

I have this code:

string connectionString = "data source = KONTORET\\sqlexpress; Initial Catalog = testDatabas; Trusted_Connection = True";

SqlConnection connection = new SqlConnection(connectionString);

connection.Open();

string selectStatement = "select * from Cars";

SqlCommand command = new SqlCommand(selectStatement, connection);

As I understand it you doesn't need data adapter when you work like this. Do you use a dataset even if you don't say so explicitly?

I would also like to know how to show this in a form.

Bitini Lumbet · Answer

ds = new DataSet(); adapter.Fill(ds table); dataGridView1.DataSource = ds.table; dataGridView1.DataBind();

Lars Persson · Answer

Great! Thank you all!

Ehtesham · Answer

*)SqlDataAdapter can work with disconnected and connected architecture SqlCommand can only work on Connected *)SqlDataAdapter can directly update the DB(for instance change in datagridview can directly reflect database table) if used with SqlCommandBuilder *)SqlDataAdapter can fill DataTableDataSet *)Sqlcommand can use Command.Executereader to read the data(which has better performance then Fill method of DataAdapter)

Senthilkumar · Answer

Hi The DataAdapter is providing connectivity to the sql server and source of the data needs to be stored. The sql data adapter accepts the data set or datatable to store the information. If the result returns in the multiple then DataSet can handle internally. If it returns single table result then DataTable preferred.

Satyapriya Nayak · Answer

Hi Lars using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; namespace Dataset_and_Datatable_windows { public partial class Form1 : Form { string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings[dsn]; OleDbCommand com; OleDbDataAdapter oledbda; string str; DataSet ds; DataTable dt; public Form1() { InitializeComponent(); } private void button1_Click(object sender EventArgs e) { //Using Dataset OleDbConnection con = new OleDbConnection(ConnectionString); con.Open(); str = select * from employee; com = new OleDbCommand(str con); oledbda = new OleDbDataAdapter(com); ds = new DataSet(); oledbda.Fill(ds employee); dataGridView1.DataMember = employee; dataGridView1.DataSource = ds; con.Close(); //Using Datatable OleDbConnection con = new OleDbConnection(ConnectionString); dt = new DataTable(); com = new OleDbCommand(); com.Connection = con; con.Open(); com.CommandText = Select * from employee; oledbda = new OleDbDataAdapter(com); oledbda.Fill(dt); dataGridView1.DataMember = employee; dataGridView1.DataSource = dt; con.Close(); } } } Thanks

Lars Persson · Answer

Thanks! A new question: This works fine: stringconnectionString = data source = KONTORET\sqlexpress; Initial Catalog = testDatabas; Trusted_Connection = True ; SqlConnection connection = new SqlConnection (connectionString); connection.Open(); SqlDataAdapter adapter = new SqlDataAdapter ( SELECT * FROM Bilar  connection); DataTable table = new DataTable (); adapter.Fill(table); dataGridView1.DataSource = table; But this doesn't why? Same connectionsame adapter and then: DataSet DS = new DataSet (); adapter.Fill(DS); dataGridView1.DataSource = DS.Tables; Edit: This works: dataGridView.DataSource = DS.Tables[0];

Bitini Lumbet · Answer

Data adapter and datase are ado.net technology - so you use them when you want to work with a copy of your database - so you can work disconnected from the database and you data will be updates automatically when connected. When you connect diretly to your data base you do not need dataset. I think that help.

Questions about Dataset, Data adapter and how to show data in a form.