Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Original question:

Hi,

I have a Dictionary<string,> with let say 20-60 items.

in database I have table with 3 columns:
1. key, 2.item_key, 3.item_value

what I need to do is to create/update data in that table by data from dictionary.

My question is what strategy would you choose to save data?

My ideas how to do it:
1. for cycle in transaction with individual Sql inserts/updated commands
- not sure if that is a good idea, i think that there has to be some better way how to do it
2. bulk insert, I don't really have to much experience with bulk operations would it be possible to do it when I also need sometime update not just insert?
3.SqlDataAdapter? no idea how to do it...

What do you think that would be best?


Edited question:

I decided to specify my question little bit more, because some of the solutions that you suggested doesn't exactly fit to my situation. However I'd like to thank you all for your ideas and I will also provide my working solution.

Question:

I have a SQL server 2005, and I need to insert/update 20-60 items from c# dictionary into my database. What would be the best strategy to do that?
Posted
Updated 2-Sep-15 4:55am
v2

I'd convert the dictionary to a DataTable and use a SqlBulkCopy to insert them in a single go. See here: https://msdn.microsoft.com/en-us/library/ex21zs8x(v=vs.110).aspx[^]

[edit]Why did I type DataAdapter? I dunno...[/edit]
 
Share this answer
 
v2
Comments
Jörgen Andersson 28-Aug-15 18:13pm    
Am I allowed to plug my own high performance solution here? http://www.codeproject.com/Articles/890048/A-generic-Collection-to-DataTable-Mapper
OriginalGriff 29-Aug-15 2:24am    
Please do!
Oh, you already did! :laugh:
xszaboj 2-Sep-15 11:05am    
I really like it, but sorry cannot accept as a solution, because it is not possible to also update data, only insert new one.
My solutions to this problem:
1.
C#
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.
C#
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.
C#
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:
SQL
CREATE PROCEDURE usp_SaveClientApplicationsData
    @clientData nText,
    @urn varchar(20)
AS
BEGIN
    Declare @hDoc int
    exec sp_xml_preparedocument @hDoc OUTPUT, @clientData

    --Update
    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
    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
 
Share this answer
 
Instantiate Command
Set CommandText
Add parameters
Begin transaction
Foreach
  Set parameters
  Execute command
Commit or Rolback as appropriate
 
Share this answer
 
v2
Comments
Jörgen Andersson 28-Aug-15 18:11pm    
you forgot the Command.Prepare()
PIEBALDconsult 28-Aug-15 18:39pm    
Never used it.
Jörgen Andersson 28-Aug-15 18:46pm    
It's doing quite a lot for performance since the database doesn't need to parse the query more than once.
But it's not useful for stored procedures.
PIEBALDconsult 28-Aug-15 19:30pm    
"It's doing quite a lot for performance "
Prove it.

"the database doesn't need to parse the query more than once"
As usual. At least for SQL Server.

Jörgen Andersson 29-Aug-15 16:45pm    
Doing a lot for the performance is of course only valid if you're reusing the Command with the same CommandText and the same Parameters (but different values)

I believe you might be mixing up "Parsing the Command" with Reusing the plan.

When you run Command.Prepare() the Command is sent to the server for parsing and creation of a plan. A Handle for the plan ID is returned to the Command.
So when you execute a prepared command the Plan ID is sent together with the Parameter values and the parsing as well as the plan creation is bypassed.

If you don't prepare a Command before execution it will be parsed and a plan will be created on the first execution.
On the subsequent executions of the Command it will still get parsed on every execution but the plan will get reused from the plan cache if suitable.
So the parsing of the Command will happen on every execution if the Command is not prepared.

Take a look at the code at http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlCommand.cs,6d1f148f36c73572
EXECTYPE is the Enum for whether the command is prepared or not.
_prepareHandle is the Plan ID
A simple approach would be
Convert your Dictionary into XML , and use SQL Bulk insert/Update feature.
and yest if you have both data at the same time in your Dictionary, then you may need to add one more Operation Flag of data.
and convert that into XML,
Create a stored procedure and pass this XML to Stored procedure and
Iterate this XML in Loop by using FLAG you can decide what action is to be done Either Insert or Update Or Delete
Your Application Wont hamper since your are passing those XML Data at one Go, and Let the SQL server Handle its Internal Operation (Insert,Update/Delete)
If you perform this operation from GUI interface then it need to Open/Close connection again and again in Loop.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900