Storing SQL Tables for use in visual studio

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)?

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.

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.