We have a third party application that accept calls using an XML RPC mechanism for calling stored procs.
We send a ZIP-compressed dataset containing multiple tables with a bunch of update/delete/insert using this mechanism. On the other end, a CLR sproc decompress the data and gets the dataset.
Then, the following code gets executed:
using (var conn = new SqlConnection("context connection=true"))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
try
{
foreach (DataTable table in ds.Tables)
{
string columnList = "";
for (int i = 0; i < table.Columns.Count; i++)
{
if (i == 0)
columnList = table.Columns[0].ColumnName;
else
columnList += "," + table.Columns[i].ColumnName;
}
var da = new SqlDataAdapter("SELECT " + columnList + " FROM " + table.TableName, conn);
var builder = new SqlCommandBuilder(da);
builder.ConflictOption = ConflictOption.OverwriteChanges;
da.RowUpdating += onUpdatingRow;
da.Update(ds, table.TableName);
}
}
catch (....)
{
.....
}
}
Here's the event handler for the RowUpdating event:
public static void onUpdatingRow(object sender, SqlRowUpdatingEventArgs e)
{
if ((e.StatementType == StatementType.Update) && (e.Command == null))
{
e.Command = CreateUpdateCommand(e.Row, sender as SqlDataAdapter);
e.Status = UpdateStatus.Continue;
}
}
and the CreateUpdateCommand method:
private static SqlCommand CreateUpdateCommand(DataRow row, SqlDataAdapter da)
{
string whereClause = "";
string setClause = "";
SqlConnection conn = da.SelectCommand.Connection;
for (int i = 0; i < row.Table.Columns.Count; i++)
{
char quoted;
if ((row.Table.Columns[i].DataType == Type.GetType("System.String")) ||
(row.Table.Columns[i].DataType == Type.GetType("System.DateTime")))
quoted = '\'';
else
quoted = ' ';
string val = row[i].ToString();
if (row.Table.Columns[i].DataType == Type.GetType("System.Boolean"))
val = (bool)row[i] ? "1" : "0";
bool isPrimaryKey = false;
for (int j = 0; j < row.Table.PrimaryKey.Length; j++)
{
if (row.Table.PrimaryKey[j].ColumnName == row.Table.Columns[i].ColumnName)
{
if (whereClause != "")
whereClause += " AND ";
if (row[i] == DBNull.Value)
whereClause += row.Table.Columns[i].ColumnName + "=NULL";
else
whereClause += row.Table.Columns[i].ColumnName + "=" + quoted +
val + quoted;
isPrimaryKey = true;
break;
}
}
/* Only values for column that is not a primary key can be modified */
if (!isPrimaryKey)
{
if (setClause != "")
setClause += ", ";
if (row[i] == DBNull.Value)
setClause += row.Table.Columns[i].ColumnName + "=NULL";
else
setClause += row.Table.Columns[i].ColumnName + "=" + quoted +
val + quoted;
}
}
return new SqlCommand("UPDATE " + row.Table.TableName + " SET " + setClause + " WHERE " + whereClause, conn);
}
However, this is really slow when we have a lot of records.
Is there a way to optimize this or an entirely different way to send lots of udpate/delete on several tables? I would really much like to use TSQL for this but can't figure a way to send a dataset to a regular sproc.
Additional notes:
We cannot directly access the SQLServer database.
We tried without compression and it was slower.
