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
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?
I think a first step would be to reduce your reliance on static methods and fields.
If you look at your UpdateLocalDatabase method you'll see that you are passing in strTableName which is used by that method but a method that UpdateLocalDatabase calls (CreateDatabaseConnection) refers to a different global static variable named the same. Most likely the two strTableName variables contain different values and you are not seeing that they are not the same variable.
Also you are trying to write out the global static data set dtsTableContents in UpdateLocalDatabase but if you then look at CreateDatabaseConnection it actually creates a local version of that variable -- again, you have two variables named the same thing where one is global and one is local.
I suspect that the two variables of dtsTableContents is the problem.
My suggestion, again, would be to not have any static methods or variables and, for what you're doing here, try not to use any global variables. Also, refactor and/or rename your methods to match more what they are actually doing.
After endlessly trying to use the DataAdapter.Update(Method) I gave up.. I settled for using an sdf file instead of an mdb.
If I need to update my database, I remove the local database, create a new one, using the same connectionstring. Then I loop over the tables in my dataset, read the column names and types from it and create tables based on that.
After this I loop over my dataset and insert the contents of my dataset which I filled with the information from the server. Below is the code, this is just 'quick and dirty' as a proof of concept but it works for my scenario.
public static void RemoveAndCreateLocalDb(string strLocalDbLocation)
{
try
{
if (File.Exists(strLocalDbLocation))
{
File.Delete(strLocalDbLocation);
}
SqlCeEngine sceEngine = new SqlCeEngine(@"Data Source= " + strLocalDbLocation + ";Persist Security Info=True;Password=MyPass");
sceEngine.CreateDatabase();
}
catch
{ }
}
public static void UpdateLocalDatabase(String strTableName, DataTable dttTable)
{
try
{
// Opening the Connection
sceConnection = CreateDatabaseSQLCEConnection();
sceConnection.Open();
// Creating tables in sdf file - checking headers and types and adding them to a query
StringBuilder stbSqlGetHeaders = new StringBuilder();
stbSqlGetHeaders.Append("create table " + strTableName + " (");
int z = 0;
foreach (DataColumn col in dttTable.Columns)
{
if (z != 0) stbSqlGetHeaders.Append(", "); ;
String strName = col.ColumnName;
String strType = col.DataType.ToString();
if (strType.Equals("")) throw new ArgumentException("DataType Empty");
if (strType.Equals("System.Int32")) strType = "int";
if (strType.Equals("System.String")) strType = "nvarchar (100)";
if (strType.Equals("System.Boolean")) strType = "nvarchar (15)";
if (strType.Equals("System.DateTime")) strType = "datetime";
if (strType.Equals("System.Byte[]")) strType = "nvarchar (100)";
stbSqlGetHeaders.Append(strName + " " + strType);
z++;
}
stbSqlGetHeaders.Append(" )");
SqlCeCommand sceCreateTableCommand;
string strCreateTableQuery = stbSqlGetHeaders.ToString();
sceCreateTableCommand = new SqlCeCommand(strCreateTableQuery, sceConnection);
sceCreateTableCommand.ExecuteNonQuery();
StringBuilder stbSqlQuery = new StringBuilder();
StringBuilder stbFields = new StringBuilder();
StringBuilder stbParameters = new StringBuilder();
stbSqlQuery.Append("insert into " + strTableName + " (");
foreach (DataColumn col in dttTable.Columns)
{
stbFields.Append(col.ColumnName);
stbParameters.Append("@" + col.ColumnName.ToLower());
if (col.ColumnName != dttTable.Columns[dttTable.Columns.Count - 1].ColumnName)
{
stbFields.Append(", ");
stbParameters.Append(", ");
}
}
stbSqlQuery.Append(stbFields.ToString() + ") ");
stbSqlQuery.Append("values (");
stbSqlQuery.Append(stbParameters.ToString() + ") ");
string strTotalRows = dttTable.Rows.Count.ToString();
foreach (DataRow row in dttTable.Rows)
{
SqlCeCommand sceInsertCommand = new SqlCeCommand(stbSqlQuery.ToString(), sceConnection);
foreach (DataColumn col in dttTable.Columns)
{
if (col.ColumnName.ToLower() == "ssma_timestamp")
{
sceInsertCommand.Parameters.AddWithValue("@" + col.ColumnName.ToLower(), "");
}
else
{
sceInsertCommand.Parameters.AddWithValue("@" + col.ColumnName.ToLower(), row[col.ColumnName]);
}
}
sceInsertCommand.ExecuteNonQuery();
}
}
catch { }
}
