SQL批量插入/更新策略？ [英] SQL bulk insert/update strategy?

问题描述

原始问题：



您好，



我有一个字典<字符串>比方说20-60项。


数据库中的
我有3列表：

1. key，2.item_key，3 .item_value



我需要做的是通过字典中的数据创建/更新该表中的数据。



我的问题是您选择保存数据的策略是什么？



我的想法如何做：

1. for cycle在单独的Sql插入/更新命令的事务中

- 不确定这是不是一个好主意，我认为必须有更好的方法如何做到这一点

2批量插入，我真的没有太多批量操作的经验，当我还需要更新而不仅仅是插入时可以做到这一点吗？

3.SqlDataAdapter？不知道怎么做...



你认为这最好的是什么？





编辑问题：



我决定更多地指出我的问题，因为有些解决方案是你的建议并不完全符合我的情况。不过我要感谢大家的想法，我也会提供我的工作解决方案。



问题：



我有一个SQL server 2005，我需要从c＃dictionary插入/更新20-60项到我的数据库中。这样做的最佳策略是什么？

解决方案

我将字典转换为DataTable并使用SqlBulkCopy将它们一次性插入。请参阅此处： https://msdn.microsoft.com/en-us/ library / ex21zs8x（v = vs.110）.aspx [ ^ ]



为什么我输入DataAdapter？我不知道... [/ edit]

我对这个问题的解决方案：

1.

  public   bool  SaveDataToClientApplicationTable（ string  urn ，IDictionary< string，> 字典）
 {
  var  table = CreateTable （瓮，字典）; 
 
 使用（SqlBulkCopy bulkcopy =  new  SqlBulkCopy（DatabaseHelper.MainConnectionString））
 {
 bulkcopy.DestinationTableName =   dbo.ClientApplications; 
 尝试 
 {
 bulkcopy.WriteToServer（table）; 
  return   true ; 
} 
  catch （例外e）{
 Debug.WriteLine（e）; 
  return   false ; 
} 
} 
} 
 
 私有 DataTable CreateTable（ string  urn，IDictionary< string，> 字典）{
 DataTable table =  new  DataTable（）; 
 table.Columns.Add（  URN）; 
 table.Columns.Add（  FieldName）; 
 table.Columns.Add（  FieldValue）; 
  foreach （ var  item  in 字典）
 {
  var  row = table.NewRow（）; 
行[ 0 ] = urn; 
行[ 1 ] = item.Key; 
行[ 2 ] = item.Value; 
 table.Rows.Add（row）; 
} 
  return 表; 
} 

这个解决方案是由OriginalGriff建议的，我很喜欢它，因为它很简单，但它不允许你只更新行插入它们。



2.

  public   bool  SaveDataToClientApplicationTable（ string  urn，IDictionary< string，string> dictionary）
 {
  var  oldDictionary = GetAllDataFromClientApplicationTable（urn）; 
 使用（SqlConnection con =  new  SqlConnection（DatabaseHelper.MainConnectionString））
 { 
 con.Open（）; 
  var  tran = con.BeginTransaction（）; 
 尝试 
 {
 SaveToClientApplicationsTable（dictionary，oldDictionary，urn，con，tran）; 
 tran.Commit（）; 
  return   true ; 
} 
  catch （例外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 字典）
 {
 使用（SqlCommand cmd =  new  SqlCommand（ ，con，tran））
 {
  if （oldDictionary.ContainsKey（item.Key））
 {
 cmd.CommandText =  更新ClientApplications设置FieldValue = @FieldValue其中URN = @URN和FieldName = @FieldName; 
} 
  else  
 {
 cmd.CommandText =  插入ClientApplications（URN，FieldName，FieldValue）值（@URN，@ FieldName，@ FieldValue）; 
} 
 cmd.Parameters.AddWithValue（  @ URN，urn） ; 
 cmd.Parameters.AddWithValue（  @ FieldName，item.Key）; 
 cmd.Parameters.AddWithValue（  @ FieldValue，item.Value）; 
 cmd.ExecuteNonQuery（）; 
} 
} 
} 

工作解决方案也很简单，但效果不是很好。由PIEBALDconsult建议。



3.

  public   bool  SaveDataToClientApplicationTable2（ string  urn，IDictionary< string，> 字典）
 {
  var  doc = CreateDocument（dictionary）; 
 尝试 
 {
 使用（SqlConnection con =  new  SqlConnection（DatabaseHelper.MainConnectionString））
 {
 使用（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（）; 
} 
} 
 返回  true ; 
} 
  catch （例外e）
 {
  return   false ; 
} 
} 
 
 私有 XDocument CreateDocument（IDictionary< string， > 字典）
 {
 XDocument doc =  new  XDocument（ new  XElement（  root））; 
  foreach （ var  item  in 字典）
 {
  var  client =  new  XElement（ 客户端）; 
 client.SetAttributeValue（  FieldName，item.Key）; 
 client.SetAttributeValue（  FieldValue，item.Value）; 
 doc.Root.Add（client）; 
} 
 返回 doc; 
} 

SQL：

  CREATE   PROCEDURE  usp_SaveClientApplicationsData 
  @ clientData   nText ，
  @ urn   varchar （< span class =code-digit> 20 ）
  AS  
  BEGIN  
 声明  @ hDoc   int  
  exec  sp_xml_preparedocument  @ hDoc   OUTPUT ， @ clientData  
 
   -   更新 
 更新 ClientApplications 
 设置 
 FieldValue = XMLClient.FieldValue 
  FROM   OPENXML （ @ hDoc ，'  root / Client'）
  WITH （FieldName  varchar （< span class =code-digit> 25 ），FieldValue  varchar （ 255 ）） XMLClient 
  WHERE  ClientApplications.URN =  @ urn  和 ClientApplications.FieldName = XMLClient.FieldName 
   -   插入 
 将 插入 ClientApplications（URN，FieldName，FieldValue）
 选择  @ urn ，XMLClient.FieldName，XMLClient.FieldValue 
  FROM   OPENXML （ @ hDoc ，'  root / Client'， 1 ）
  WITH （FieldName  varchar （ 25 ），FieldValue  varchar （ 255 ））XMLClient 
 其中 XMLClient.FieldName    （选择 FieldName  from  ClientApplications 其中​​ urn =  @ urn ）
 
  END  
 < span class =code-keyword> GO  

我正在使用的解决方案，由Ravindranath_Kanojiya建议。



我发现但不符合我要求的另一个解决方案是：

http://www.aspsnippets.com/Articles/SqlBulkCopy --Bulk-Insert-records-and-Update-existing-rows-if-record-exists-using-C-and-VBNet.aspx [ ^ ]



你至少需要SQL Server 2008而我有2005年。



谢谢再次提出你的想法，如果你有任何想法如何改善我的工作解决方案或想法如何改进它请写评论请:)。



谢谢

实例化命令
设置CommandText 
 添加参数 
开始交易
 Foreach 
 设置参数 
执行命令
适当的提交或回滚

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?

解决方案

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]

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
    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

Instantiate Command
Set CommandText
Add parameters
Begin transaction
Foreach
  Set parameters
  Execute command
Commit or Rolback as appropriate

这篇关于SQL批量插入/更新策略？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！