c# - Export SQL DataBase to WinForm DataSet and then to MDB Database using DataSet

Question

My application is a winform app that relies on a database. At startup of the application it connects to an SQL Database on the server and put this information in a DataSet/DataTable.

If for some reason the database on the server is not accessible, the application has a built in failover and it will get its information from the local database.

If, in a normal scenario, I start the tool it will read from the sql database and if it has been updated on the server (a seperate snippet checks this), it should make sure the local database is up to date and this is where the problem starts.. (see below)

This part works fine and is added as context - this is where we connect to the SQL Database

    public static DataSet dtsTableContents;
    public static DataTable CreateDatabaseSQLConnection()
    {
        try
        {
            string strSqlConnectionString = "Data Source=MyLocation;Initial Catalog=MyCatalog;User=MyUser;Password=MyPassword;";
            SqlCommand scoCommand = new SqlCommand();
            scoCommand.Connection = new SqlConnection(strSqlConnectionString);
            scoCommand.Connection.Open();
            string strQueryToTable = "SELECT * FROM " + strTableName;
            dtsTableContents = new DataSet();
            SqlCommand scmTableInformation = new SqlCommand(strQueryToTable, scnConnectionToDatabase);
            SqlDataAdapter sdaTableInformation = new SqlDataAdapter(scmTableInformation);
            scnConnectionToDatabase.Open();
            sdaTableInformation.Fill(dtsTableContents, strTableName);
            DataTable dttTableInformation = dtsTableContents.Tables[strTableName];
            scnConnectionToDatabase.Close();
            return dttTableInformation;
        }
        catch
        {
            return null;
        }
    }

This snippet is part of the failover method that reads from my local database...

This part works fine and is added as context - this is where we connect to the MDB Database

public static DataTable CreateDatabaseConnection()
    {
        try
        {
            string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyLocation;Persist Security Info=True;JET OLEDB:Database Password=MyPassword;"
            odcConnection = new OleDbConnection(ConnectionString);
            odcConnection.Open();
            string strQueryToTable = "SELECT * FROM " + strTableName;
            DataSet dtsTableContents = new DataSet();
            OleDbCommand ocmTableInformation = new OleDbCommand(strQueryToTable, ocnConnectionToDatabase);
            OleDbDataAdapter odaTableInformation = new OleDbDataAdapter(ocmTableInformation);
            ocnConnectionToDatabase.Open();
            odaTableInformation.Fill(dtsTableContents, strTableName);
            DataTable dttTableInformation = dtsTableContents.Tables[strTableName];
            ocnConnectionToDatabase.Close();
            return dttTableInformation;
        }
        catch
        {
            return null;
        }
    }

From my CreateDatabaseSQLConnection() I have a DataSet. This DataSet is verified to contain all the information from the server database. Now I have been googling around and found myself trying to use this code to update the local database based on this article: http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update(v=vs.71).aspx

public static void UpdateLocalDatabase(string strTableName)
    {
        try
        {
            if (CreateDatabaseConnection() != null)
            {
                string strQueryToTable = "SELECT * FROM " + strTableName;
                OleDbDataAdapter odaTableInformation = new OleDbDataAdapter();
                odaTableInformation.SelectCommand = new OleDbCommand(strQueryToTable, odcConnection);
                OleDbCommandBuilder ocbCommand = new OleDbCommandBuilder(odaTableInformation);
                odcConnection.Open();
                odaTableInformation.Update(dtsTableContents, strTableName);
                odcConnection.Close();
            }
        }
        catch { }
    }

This snippet runs error-free but it does not seem to change anything. Also the time it takes to run this step takes like milliseconds and I would think this to take longer.

I am using the DataSet I obtained from my SQL Connection, this DataSet I am trying to write to my local database.

Might it be the fact that this is a DataSet from an SQL connection and that I can't write this to my mdb connection via my OleDbAdapter or am I just missing the obvious here?

Any help is appreciated.

Thanks,

Kevin

Answer 1

For a straight backup from the external database to the internal backup

I've just been messing around with an sdf and a server based sql database and it has worked.. It's not by all means the finished product but for one column I've got the program to read from the external database and then write straight away to the local .sdf in around 15 lines of code

            SqlConnection sqlCon = new SqlConnection( ExternalDatabaseConnectionString );
            SqlCeConnection sqlCECon = new SqlCeConnection( BackUpConnectionString );
            using ( sqlCon )
                {
                using ( sqlCECon )

                    {
                    sqlCon.Open( );
                    sqlCECon.Open( );
                    SqlCommand get = new SqlCommand( "Select * from [TableToRead]", sqlCon );
                    SqlCeCommand save = new SqlCeCommand( "Update [BackUpTable] set InfoColumn = @info where ID = @id", sqlCECon );
                    SqlDataReader reader = get.ExecuteReader( );
                    if ( reader.HasRows )
                        {
                        reader.Read( );
save.Parameters.AddWithValue("@id", reader.GetString(0));
                            save.Parameters.AddWithValue( "@info", reader.GetString( 1 ));
                            save.ExecuteNonQuery( );
                            }
                        }
                    }

For one row of a database, backing up one column, it works, I'm assuming you will have some sort of auto incremented key like ID?