Question Save data from Unbound DataGridView to Sql Server 2005 and then Bind to records

[ahmed_one]

Hi All Experts,

I am a newbie to C#, my development environment is:

Visual Studio 2010 Ultimate Edition
Sql Server 2005
Programming Language C#**

I've a form with Unbound Datagridview, from which data is save to Sql Server 2005 by looping through For..Each and pass SQL INSERT Statement when user clicks Save button without any problem.

Now what I need is a way that for same Save button, if data is saved in Sql Server then DataGridView will bind to that records so that if user want to Update/Delete/Add data he/she just need to edit the data and again press Save button. All records will share the same unique ID. It's a products database, and when user click save it will generate a transaction Id same for all products user enter when saving. So I think I need to generate a Select statement and retrieve all those records user save through that Unique ID, but then how to bind it to DataGridView datasource and how to Update/Delete through SqlAdapter or other?

Thanks and best regards

Ahmed

 

[jmcilhinney]

That's just a very, very bad way to do things. Just bind the grid from the start. That way you only need one routine to save the data. It doesn't care whether the data is new or existing. You simply call Update on a data adapter and any changes (inserts, updates and deletes) will be saved to the database.

 

[ahmed_one]

Re:Save data from Unbound DataGridView to Sql Server 2005 and then Bind to records C#

Hi,


Thank you very much for your reply....Actually I need to do it with Unbound form so that it will not to heavy for network, let's say if application uses datagridview and there are say 50+ users, then doesn't the bound DataGridView will push more load to server?



1. Save data from unbound DataGridView to Sql Server (which is done).
Now data is saved in Sql Server, and Form which contains DataGridView shows those records which are just saved, if user will try to Add/Edit/Update/Delete operations, it will not do it, instead it will again save those data as new records, which will create ofcoruse, duplicate records for products.


2. Suppose user enter 4 or 5 product details using DataGridView, after user save the data (in step 1), I need to bind those 4-5 records to datagridview so that user can Add/Edit/Update/Delete those records which should available immediately after save operation.


I hope I've explain my problem more clearly this time, I appreciate if you could help me on this.


Thanks and regards


Ahmed

 

[jmcilhinney]

Binding is purely a UI function and has zero effect on network traffic or anything else. Retrieving & saving data has absolutely no direct relationship to data-binding.

 

[ahmed_one]

Re:Save data from Unbound DataGridView to Sql Server 2005 and then Bind to records C#

Binding is purely a UI function and has zero effect on network traffic or anything else. Retrieving & saving data has absolutely no direct relationship to data-binding.

Thank you very much for your reply, ok let's say using following code to bind the DataGridView to database at the form load event:

connectionString = ConfigurationManager.AppSettings["connectionString"];
sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();

command = new SqlCommand("Select ProductName, QuantityPerUnit, UnitPrice from Products", sqlConnection);
           
sqlDataAdapter = new SqlDataAdapter(command);
sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);


   BindingSource bs = new BindingSource();
   DataTable dt = new DataTable();
   DataSet ds = new DataSet();
   sqlDataAdapter.Fill(ds, "Products");
            
   bs.DataSource = ds;

   dgInvDet.DataSource = bs;

the code do bind the datagridview to Database and shows the records based on Sql Query I've mentioned, now what should I do to show user a fresh form with blank DataGridView so that user can present with the screen to make new entries?

Looking forward to hear from you soon.

Ahmed

 

[jmcilhinney]

When you call Fill it will populate the DataTable with the result set of the query. If you want to add the schema, i.e. columns, etc, to the DataTable but not the data then call FillSchema instead.

By the way, you're creating a DataTable and a DataSet and then only using the DataSet. There's no point creating both because when you call Fill you are creating a DataTable inside the DataSet anyway. The thing is, if all you're using is one DataTable then the DataSet is pointless anyway. Get rid of your DataSet altogether. Just create the DataTable and pass it when you call Fill or FillSchema, then use it as the data source.

 

[ahmed_one]

Re:Save data from Unbound DataGridView to Sql Server 2005 and then Bind to records C#

When you call Fill it will populate the DataTable with the result set of the query. If you want to add the schema, i.e. columns, etc, to the DataTable but not the data then call FillSchema instead.

By the way, you're creating a DataTable and a DataSet and then only using the DataSet. There's no point creating both because when you call Fill you are creating a DataTable inside the DataSet anyway. The thing is, if all you're using is one DataTable then the DataSet is pointless anyway. Get rid of your DataSet altogether. Just create the DataTable and pass it when you call Fill or FillSchema, then use it as the data source.

Thank you very much for your help and support, is FillSchema method support Update/Delete method? if I convert:

sqlDataAdapter.Fill(ds, "Products");to

sqlDataAdapter.Fill(ds, SchemaType.Source ,"Products");

Can I pass sqlDataAdapter.Update(ds) the same way as it is in Fill method?

Thanks

Ahmed

 

[jmcilhinney]

That's not what I said to do. I said to call FillSchema instead of Fill, but you're still calling Fill.

Also, whether you call Fill or FillSchema has nothing whatsoever to do with whether you can update or delete. The one and only difference between Fill and FillSchema is that Fill populates the DataTable with the result set of the query and FillSchema doesn't. That is it. What you do with your DataTable after that is completely unaffected. Obviously if the DataTable contains no data then you can't edit or delete any records but that is the case whether you called FillSchema or you called Fill and the query returned no records.

You're really trying to make this difficult when it's not. Just do as I've said and nothing else. Call FillSchema instead of Fill and nothing else changes.

 

[ahmed_one]

That's not what I said to do. I said to call FillSchema instead of Fill, but you're still calling Fill.

Also, whether you call Fill or FillSchema has nothing whatsoever to do with whether you can update or delete. The one and only difference between Fill and FillSchema is that Fill populates the DataTable with the result set of the query and FillSchema doesn't. That is it. What you do with your DataTable after that is completely unaffected. Obviously if the DataTable contains no data then you can't edit or delete any records but that is the case whether you called FillSchema or you called Fill and the query returned no records.

You're really trying to make this difficult when it's not. Just do as I've said and nothing else. Call FillSchema instead of Fill and nothing else changes.

Sorry that was typo error...I was calling FillSchema method.