更新大表(很多列).c# . net

本文关键字：net 更新 | 更新日期: 2023-09-27 18:01:23

我必须更新一个有超过270个更新字段的大表。

我是相对新的。net和需要建议什么是更好地使用在这种情况下:SqlCommand，某种内存映射表或数据集，或者它可能存在某种自动生成的对象使用元数据从DB?请帮助。

原因:我有一个旧的大Delphi7应用程序，其中一部分负责监听套接字上的一些数据包，这些数据包被封送到大结构中，并在最后一步存储在DB中。现在我将这部分移植到新的c#服务中，至少实际上我必须保留相同的逻辑。问题是结构太大(超过220个字段)，存储它的表有近300个字段。从我的220个字段的结构中扣除/计算其他~50个字段，所有字段都应该在DB中更新。实际的Delphi代码是丑陋的，它像表本身一样经过几年的发展，就像这样:

'UPDATE TABLE_NAME ' +
  '  MSG_TYPE = ' + IntToStr(integer(RecvSruct.MSG_TYPE)) + ' ' + 
  ' ,SomeFLOATfield = ' + FloatToStr(RecvSruct.SomeFLOATfield) + ' ' + 
  ... //and other over 270 fileds here
'WHERE ID = ' + IntToStr(obj.ID)

没有任何动态SQL等。实际上我不能改变数据库结构…这样我就只能在代码中发挥作用，我不确定是否有针对性地翻译代码。该表用于一些报表和统计信息。一些计算/扣除的字段必须处理源代码中的一些常量。

使用工具开发:MS SQL Server 2000, c# .net2.0, VS2008

更新大表(很多列).c# . net

最简单的解决方案适用于此，因为ole db的工作方式是字符串。所以，要传递270,500,1000个参数，我所要做的就是传递一个字符串，一个包含270个参数的字符串可能远低于2kb…在现代计算中…结转1…没有性能损失。这里有一个xml解决方案，但这只是苹果和橘子，你仍然传递字符串，但它需要额外的代码来处理xml。所以…您的体系结构应该如下所示:

  1. SQL server上有270个输入参数的存储过程:

     Create Procedure sp_Example1 
 (@param1 [type], @param2 [type], @param3 [type], etc...)
 AS 
 BEGIN
 [SQL statements]
 END

  2. 包含270个参数的命令对象:

    SqlCommand cmd = new SqlCommand("sp_Example1", [sqlconnectionstring]);
cmd.Parameters.Add(New SqlParameter("@param1", param1.value));
cmd.Parameters.Add(New SqlParameter("@param2", param2.value));
cmd.Parameters.Add(New SqlParameter("@param3", param3.value));

请记住，您仍然在执行相当密集的操作，但是您的基准应该是旧的应用程序。如果稍微差一点，我也不会担心，因为框架需要更多的计算开销。

我不知道为什么它不会格式化代码…

好的。因为您可以添加一个新的存储过程，所以我建议打包所有的值，并将其作为XML传送到您的存储过程。

你可以在这里找到一个类似的例子:http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/

好消息是，我的例子是旧的，编码为Sql Server 2000(使用OPENXML)。

.

这比向存储过程发送300个参数要好得多。

另一个好处是，如果你有超过一行的数据，你也可以发送。

……

要点:

首先，您可以在这里获得2000个"酒吧"数据库:

http://www.microsoft.com/en-us/download/details.aspx?id=23654

现在添加这个存储过程:

/* USP */

DROP PROCEDURE dbo.uspTitleUpsert
GO


CREATE  PROCEDURE dbo.uspTitleUpsert (
    @xml_doc TEXT , 
    @numberRowsAffected int output  --return
)
AS 
SET NOCOUNT ON 
DECLARE @hdoc INT -- handle to XML doc
DECLARE @errorTracker int -- used to "remember" the @@ERROR
DECLARE @updateRowCount int
DECLARE @insertRowCount int 

--Create an internal representation of the XML document.    
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc    

-- build a table (variable table) to store the xml-based result set
DECLARE @titleupdate TABLE (  
    identityid int IDENTITY (1,1) , 
title_id varchar(6) , 
title varchar(80) , 
type varchar(32) , 
pub_id varchar(32) , 
price money , 
advance money , 
royalty varchar(32) , 
ytd_sales varchar(32) , 
notes TEXT , 
pubdate datetime
)


--the next call will take the info IN the @hdoc(with is the holder for @xml_doc), and put it IN a variableTable
INSERT @titleupdate
    (
        title_id ,
        title ,
        type ,
        pub_id ,
        price ,
        advance ,
        royalty ,
        ytd_sales ,
        notes ,
        pubdate
    )
SELECT  
    title_id ,
    title ,
    type ,
    pub_id ,
    price ,
    advance ,
    royalty ,
    ytd_sales ,
    notes ,
    getdate() /*dbo.udf_convert_xml_date_to_datetime (pubdate)*/
FROM  
    -- use the correct XPath .. the second arg ("2" here) distinquishes
    -- between textnode or an attribute, most times with
    --.NET typed datasets, its a "2"
    --This xpath MUST match the syntax of the DataSet
 OPENXML (@hdoc, '/TitlesDS/Titles', 2) WITH (  
    title_id varchar(6) , 
    title varchar(80) , 
    type varchar(32) , 
    pub_id varchar(32) , 
    price money , 
    advance money , 
    royalty varchar(32) , 
    ytd_sales varchar(32) , 
    notes TEXT , 
    pubdate varchar(32)
)  

EXEC sp_xml_removedocument @hdoc

select * from @titleupdate

SET NOCOUNT OFF


Update 
    dbo.titles 
set 
    title = vart.title , 
    type = vart.type , 
    pub_id = vart.pub_id , 
    price = vart.price , 
    advance  = vart.advance , 
    royalty  = vart.royalty , 
    ytd_sales  = vart.ytd_sales , 
    notes  = vart.notes , 
    pubdate  = vart.pubdate 
FROM
    @titleupdate vart , dbo.titles realTable
WHERE
    (rtrim(upper(realTable.title_id))) = ltrim(rtrim(upper(vart.title_id)))
    and
    exists 
    (
        select null from dbo.titles innerRealTable where (rtrim(upper(innerRealTable.title_id))) = ltrim(rtrim(upper(vart.title_id)))
    )

Select @updateRowCount = @@ROWCOUNT
INSERT INTO dbo.titles
    (
        title_id ,
        title ,
        type ,
        pub_id ,
        price ,
        advance ,
        royalty ,
        ytd_sales ,
        notes ,
        pubdate
    )
Select
    title_id ,
    title ,
    type ,
    pub_id ,
    price ,
    advance ,
    royalty ,
    ytd_sales ,
    notes ,
    pubdate
FROM
    @titleupdate tu
WHERE
    not exists 
    (
        select null from dbo.titles innerRealTable where (rtrim(upper(innerRealTable.title_id))) = ltrim(rtrim(upper(tu.title_id)))
    )
Select @insertRowCount = @@ROWCOUNT
print '/@insertRowCount/'
select @insertRowCount
print ''
print '/@updateRowCount/'
select @updateRowCount
print ''

select @numberRowsAffected = @insertRowCount + @updateRowCount

--select * from titles
SET NOCOUNT OFF

GO


--GRANT EXECUTE on dbo.uspTitleUpsert TO pubsuser

GO

/*示例用法*/

EXEC dbo.uspTitleUpsert
'
<TitlesDS>
    <Titles>
        <title_id>PN3333</title_id>
        <title>Peanut Cooking</title>
        <type>trad_cook</type>
        <pub_id>0877</pub_id>
        <price>3.33</price>
        <advance>4444.00</advance>
        <royalty>1</royalty>
        <ytd_sales>33</ytd_sales>
        <notes>Peanut Cooking Notes</notes>
        <pubdate></pubdate>
    </Titles>
    <Titles>
        <title_id>SSMS4444</title_id>
        <title>Sql Server Management Studio</title>
        <type>programming</type>
        <pub_id>0877</pub_id>
        <price>13.33</price>
        <advance>5444.00</advance>
        <royalty>2</royalty>
        <ytd_sales>33</ytd_sales>
        <notes>Sql Server Management Studio Notes</notes>
        <pubdate></pubdate>
    </Titles>   
</TitlesDS>
'
, 0

Using Simple。数据可以简化你的代码和逻辑(虽然它需要。net 4.0)

  1. 您可以将表拆分为新表，然后创建与旧表同名的视图，用于连接，切换，铸造等，以将新表转换为报告的旧结构。

  2. 如果你使用命令(像你发布的Delphi代码)，使用参数来防止SQL注入。

  3. 对于当前的DB结构，您使用开箱即用的ORM可能会很累人，因为您有大量的列要映射。您可以将POCO类创建为类型安全模型，然后使用数据符号或自定义属性使映射更简单，然后根据这些属性动态地创建SQL命令。

恐怕没有特别的兔子从。net的帽子里拉出来。

没有"知道"只有一些完全独立的字段发生了变化，并且只为它们构造一个更新语句的复杂性，你就满足了。

即使知道这将被更好地存储为blob也没有真正帮助你。在任何情况下都不可能是真的。

参数化查询或存储过程在代码中看起来整洁一些，但无论如何都可以在delphi中完成。

没有办法从这里说应该怎么做，但是有一个想法可能很有意义，那就是对除了一小部分函数之外的所有函数隐藏当前表。

例如，如果您要重命名它，然后使用当前名称创建视图。阅读和(可能是写它的代码)都不会注意到。如果您可以达到只通过视图和一些存储过程访问原始表的程度，那么您可以开始删除结构。

代码(不是sql)只能在应用程序和表之间插入ORM样式的访问。这个决定应该基于你的技能和应用程序的组合，而不是其他任何东西。

除非您能够并且准备好将所有应用程序从这个表的特定实现中解耦，否则您只是在抛光粪便。没必要把宝贵的资源浪费在这上面。

相关文章：