Storing SQL Tables for use in visual studio

Question

Greetings.

I'm trying to create a windows form application that manipulates data from several tables stored on a SQL server.

1) What's the best way to store the data locally, while the application is running? I had a previous program that only modified one table, and that was set up to use a datagridview. However, as I don't necessarily want to view all the tables, I am looking for another way to store the data retrieved by the SELECT * FROM ... query.

2) Is it better to load the tables, make changes within the C# application, and then update the modified tables at the end, or simply perform all operations on the database, remotely (retrieving the tables each time they are needed)?

Thank you.

Answer 1

The DataSet object has methods that give you the ability to manipulate data from multiple tables in memory, and then save those changes back to the database. Whether that's a good idea depends on your requirements and data--are there multiple simultaneous users, how do you need to handle conflicting updates, etc. I usually prefer to write changes back to the DB immediately, but then, I usually work in a web app context.

Answer 2

You can take in one table at a time using a ConnectionString and assign it to a DataTable. You can then make changes to the DataTable in any form you want. Once you are finished making the changes you can commit the changes back to Database by using a DataAdapter.

Here's how to get a table:

DataTable table;
using (SqlDbConnection connection = new SqlDbConnection(connectionString))
            {
                connection.Open();
                using (SqlDbCommand command = new SqlDbCommand(tableName, connection))
                {
                    command.CommandType = CommandType.TableDirect;
                    SqlDbDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                    table = new DataTable(tableName);
                    routeID.Load(dr);
                }
             }

Here's how to commit the table after changes, make sure to assign your DataTable to a DataSet in order to get the changes for commit:

DataSet dataSet = new DataSet();
dataSet.add(table);

using (var adapter = new SqlDbDataAdapter("SELECT * FROM " + tableName, connection))
            {
                using (var builder = new SqlDbCommandBuilder(adapter))
                {

                    adapter.Fill(dataSet, tableName);
                    using (DataSet newSet = dataSet.GetChanges(DataRowState.Added))
                    {
                        builder.QuotePrefix = "[";
                        builder.QuoteSuffix = "]";
                        adapter.InsertCommand = builder.GetInsertCommand();
                        adapter.Update(newSet, tableName);
                    }
                }
            }

There may be a few miss Types, I didn't compile to check for Errors. Good Luck.