SqlDataAdapter and existing DataSet

[Patrick Kristiansen]

Hello!

I'm currently writing a web service that will synchronize data between
a client computer (Microsoft Access database) and a central server
(Microsoft SQL Server). I'm making a method on the webservice with the
following signature

void Synchronize(int clientID, DataSet data)

The method will go through every table in the dataset, and having
designed it in a way so the corresponding table on the SQL Server
roughly has the same schema as the one found in the Access database.

Now, using the SqlDataAdapter (and possibly the SqlCommandBuilder) how
would you suggest that I perform the synchronization? The client makes
sure the correct records are sent, so don't worry about that. I just
want to know if there is any built in methods that make it possible to
merge the records into the existing SQL Server database table.

Thanks in advance,
Patrick

 

[Angel J. Hernández]

Hi there... as a matter of fact I had to do something like that a couple of
weeks ago (I use .NET remoting instead of a web service)... Getting to the
point... I wrote a couple of methods (the ones i think will be usefeful to
u)... I don't use any data
adapter but an insert statement instead and call an command's
executenonquery (this approach is faster than using the adapter). The code I
use to get the insert statements is shown below...


// This method returns an array of string containing the insert
statements
public static string[] CreateInsertString(DataTable table) {
string[] retval = null;
System.Text.StringBuilder sb = null;

if (table != null && table.Rows.Count > 0) {
retval = new string[table.Rows.Count];
for(int x = 0; x < table.Rows.Count; x++) {
sb = new System.Text.StringBuilder();
sb.Append("Insert Into ");
sb.Append(table.TableName);
sb.Append(GetColumnNames(table));
sb.Append(" Values " +"( ");

for(int y = 0; y < table.Columns.Count; y++)
sb.Append(GetValueFromColumn(table.Rows[x][y]));
sb.Remove(sb.Length - 2, 1);
sb.Append(")");
retval[x] = sb.ToString();
}
}
return retval;
}


// This method gets the table's columns names
private static string GetColumnNames(DataTable table) {
System.Text.StringBuilder retval = new System.Text.StringBuilder();

retval.Append("(");

foreach(DataColumn column in table.Columns)
retval.Append(column.ColumnName + ", ");

retval.Remove(retval.Length - 2, 1);

retval.Append(")");

return retval.ToString();
}


// This method get the column's value and perform a conversion, add
sibgle quotes to strings if needed
private static string GetValueFromColumn(object obj) {
string retval;

if (obj.GetType().Equals(Type.GetType("System.Int16")) ||
obj.GetType().Equals(Type.GetType("System.Int32")) ||
obj.GetType().Equals(Type.GetType("System.Int64")) ||
obj.GetType().Equals(Type.GetType("System.Long")) ||
obj.GetType().Equals(Type.GetType("System.Float")) ||
obj.GetType().Equals(Type.GetType("System.Decimal")) ||
obj.GetType().Equals(Type.GetType("System.Double")) ||
obj.GetType().Equals(Type.GetType("System.Single")))
retval = (obj.ToString().IndexOf(',') > -1
? obj.ToString().Replace(',','.') : obj.ToString()) +", ";
else retval = "'"+obj.ToString().Trim()+"', ";

return retval;
}


This is working today if it worked for me it me work for u...

Regards,