What's the most simple way to retrieve all data from a table and save it back in .NET 3.5?

Question

I have a number of tables containing some basic (business related) mapping data. What's the most simple way to load the data from those tables, then save the modified values back. (all data should be replaced in the tables)

An ORM is out of question as I would like to avoid creating domain objects for each table.

The actual editing of the data is not an issue. (it is exported into Excel where the data is edited, then the file is uploaded with the modified data)

The technology is .NET 3.5 (ASP.NET MVC) and SQL Server 2005.

Thanks.

Answer 1

An SSIS package would be pretty efficient for what you are doing.

Since everything is being done outside the application in and Excel spreadsheet anyway, this would be easier than doing something in MVC.

If you really want to make it "end user" friendly, then provide a way for them to upload the file, drop it into a folder somewhere, and make sure you schedule a File Watcher Task in SSIS.

Answer 2

Hello,

As you use SQL Server 2005, I suggest to return results as XML instead of standard rowsets. Use FOR XML clause. Than modify this XML and apply to SQL Server back. Please, see for more details

ADDED:

About excel..hm..use dataConnection (OLEDB provider for excel) to open this xls file and then convert it to XML. XML apply to SQL Server

Answer 3

You can write some simple code like this. http://davidhayden.com/blog/dave/archive/2006/03/08/2877.aspx

// Get data in a DataTable
DataTable table = new DataTable();

string northwindConnectionString = "...Northwind...";

using (SqlConnection connection =
    new SqlConnection(northwindConnectionString))
{
    using (SqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "SELECT * FROM Categories";

        connection.Open();
        using (IDataReader dr = command.ExecuteReader
                  (CommandBehavior.CloseConnection))
        {
            table.Load(dr);
        }
    }
}

// Upload DataTable to a Database Table
string destinationConnectionString = "...";

using (SqlBulkCopy copy =
    new SqlBulkCopy(destinationConnectionString))
{
    copy.DestinationTableName = "Categories";
    copy.WriteToServer(table);
}