My solutions to this problem:
1.
public bool SaveDataToClientApplicationTable(string urn, IDictionary<string,> dictionary)
{
var table = CreateTable(urn, dictionary);
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(DatabaseHelper.MainConnectionString))
{
bulkcopy.DestinationTableName = "dbo.ClientApplications";
try
{
bulkcopy.WriteToServer(table);
return true;
}
catch (Exception e) {
Debug.WriteLine(e);
return false;
}
}
}
private DataTable CreateTable(string urn, IDictionary<string,> dictionary) {
DataTable table = new DataTable();
table.Columns.Add("URN");
table.Columns.Add("FieldName");
table.Columns.Add("FieldValue");
foreach (var item in dictionary)
{
var row = table.NewRow();
row[0] = urn;
row[1] = item.Key;
row[2] = item.Value;
table.Rows.Add(row);
}
return table;
}
This solution was suggested by OriginalGriff and I like it very much because it is simple, but it doesn't allow you to also update the rows just insert them.
2.
public bool SaveDataToClientApplicationTable(string urn, IDictionary<string, string> dictionary)
{
var oldDictionary = GetAllDataFromClientApplicationTable(urn);
using (SqlConnection con = new SqlConnection(DatabaseHelper.MainConnectionString))
{
con.Open();
var tran = con.BeginTransaction();
try
{
SaveToClientApplicationsTable(dictionary, oldDictionary, urn, con, tran);
tran.Commit();
return true;
}
catch (Exception e)
{
Debug.WriteLine(e);
tran.Rollback();
return false;
}
}
}
private void SaveToClientApplicationsTable(IDictionary<string, string> dictionary, IDictionary<string, string> oldDictionary, string urn, SqlConnection con, SqlTransaction tran)
{
foreach (var item in dictionary)
{
using (SqlCommand cmd = new SqlCommand("", con, tran))
{
if (oldDictionary.ContainsKey(item.Key))
{
cmd.CommandText = "Update ClientApplications Set FieldValue = @FieldValue Where URN = @URN and FieldName = @FieldName";
}
else
{
cmd.CommandText = "Insert into ClientApplications (URN, FieldName, FieldValue) Values(@URN, @FieldName, @FieldValue)";
}
cmd.Parameters.AddWithValue("@URN", urn);
cmd.Parameters.AddWithValue("@FieldName", item.Key);
cmd.Parameters.AddWithValue("@FieldValue", item.Value);
cmd.ExecuteNonQuery();
}
}
}
Working solution that is also very simple, but not so effective. Suggested by PIEBALDconsult.
3.
public bool SaveDataToClientApplicationTable2(string urn, IDictionary<string,> dictionary)
{
var doc = CreateDocument(dictionary);
try
{
using (SqlConnection con = new SqlConnection(DatabaseHelper.MainConnectionString))
{
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "usp_SaveClientApplicationsData";
cmd.Parameters.Add(new SqlParameter("@clientData", System.Data.SqlDbType.NText));
cmd.Parameters[0].Value = doc.ToString();
cmd.Parameters.AddWithValue("@urn", urn);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
}
}
return true;
}
catch (Exception e)
{
return false;
}
}
private XDocument CreateDocument(IDictionary<string,> dictionary)
{
XDocument doc = new XDocument(new XElement("root"));
foreach (var item in dictionary)
{
var client = new XElement("Client");
client.SetAttributeValue("FieldName", item.Key);
client.SetAttributeValue("FieldValue", item.Value);
doc.Root.Add(client);
}
return doc;
}
SQL:
CREATE PROCEDURE usp_SaveClientApplicationsData
@clientData nText,
@urn varchar(20)
AS
BEGIN
Declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT, @clientData
Update ClientApplications
Set
FieldValue = XMLClient.FieldValue
FROM OPENXML(@hDoc, 'root/Client')
WITH (FieldName varchar(25), FieldValue varchar(255)) XMLClient
WHERE ClientApplications.URN = @urn and ClientApplications.FieldName = XMLClient.FieldName
Insert into ClientApplications (URN, FieldName, FieldValue)
Select @urn, XMLClient.FieldName, XMLClient.FieldValue
FROM OPENXML(@hDoc, 'root/Client',1)
WITH (FieldName varchar(25), FieldValue varchar(255)) XMLClient
Where XMLClient.FieldName not in (select FieldName from ClientApplications where urn = @urn)
END
GO
solution that I am currently using suggested by Ravindranath_Kanojiya.
Another solution that I found but doesn't fit my requirements is this:
http://www.aspsnippets.com/Articles/SqlBulkCopy--Bulk-Insert-records-and-Update-existing-rows-if-record-exists-using-C-and-VBNet.aspx[
^]
you need at least SQL Server 2008 and I have 2005.
Thank you all again for your ideas and if you have any ideas how to improve my working solution or idea how to improve it write me a comment please :).
Thanks