Update big table (a lot of columns). C# .NET

问题:

I have to update a big table with over 270 update fields.

I am relative new to .NET and need advise what is better to use in this case: SqlCommand, some kind of memory-mapped table or DataSet or maybe it exists some kind of auto-generated objects using meta-data from DB? Please help.

Reason: I have an old big Delphi7 application, a part of which is responsible for listening on socket some packets which are marshaled to big structures and, in final step, stored in DB. Now I am porting this part to new C# Service and at least actually i have to preserve the same logic. The problem is that structure is BIG (over 220 fields) and tables where it is stored have near 300 fields. From my structure of 220 fields are deducted/calculated other ~50 fields and all should be update in DB. Actual Delphi code is ugly ant it grew over several years like table itself, something like this:

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

no any dynamic SQL, etc.. Actually I cannot change DB structure.. so that i have to play in code only and I am not sure if it is pertinently to translate code. Table is used for some reports and statistics. Some of calculated/deducted fields have to deal with some constants in source-code.

used dev-tools: MS SQL Server 2000, C# .net2.0, VS2008

回答1:

The simplest solution applies here because the way ole db works is with strings. So, to pass 270, 500, 1000 parameters, all I do is pass a single string, a string containing 270 parameters is probably well under 2kb... which in modern computing... carry over the 1... doesn't have a performance penalty. There's an xml solution here, but that's just apples and oranges, you're still passing the string, it would require extra code to handle the xml however. So... your architecture should look like:

1. Stored procedure on SQL server with 270 input parameters:

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

2. A command object with 270 parameters:

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

Remember, you're still doing a pretty intensive operation, but your benchmark should be the old application. If it's a little bit worse, I wouldn't worry about it, since the framework requires more computing overhead.

I have no idea why it won't format the code...

回答2:

Ok. Since you can add a new stored procedure, I would suggest packaging up all the values, and shipping it down as XML to your stored procedure.

You can find a kinda example here: http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/

The good news, that example I have is older and coded to Sql Server 2000 (with OPENXML).

..

This would be better than sending down 300 parameters to a stored procedure, IMHO.

The other advantage, if you have more than 1 row of data, you can ship that down as well.

......

The "gist" of it:

First, you can get the 2000 "pubs" database here:

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

Now add this stored procedure:

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

/* Example usage */

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

回答3:

Using Simple.data could simplify your code and logic (though it requires .NET 4.0)

回答4:

1. You could split the tables into new tables, then create views with the same name as the old tables which joins, switches, castings and etc., to convert the new tables into the old structures for the reports.

2. If you do use commands (like in the Delphi code you posted), use parameters to prevent SQL injection.

3. With the current DB structure you have using an out of the box ORM could be tiring, since you have loads of columns to map. You could create POCO classes as a type safe model, then use data notations or custom attributes to make the mapping simpler, then create SQL commands on the fly from the attributes.

回答5:

No special rabbit to pull out of the .net hat for this one I'm afraid.

Short of the complexities around "knowing" only some completely independant fields have changed and constructing an update statement only for them , you are stuffed.

Even knowing this would have been better stored as blob doesn't really help you. Likely not to be true in any case.

A parameterised query or stored procedure would look a little neater in the code but that could have been done in delphi anyway.

No way to say from here how it should be done, but one idea that may have mileage is hiding the current table from everything but a small set of functions.

For instance if you were to rename it and then create a view with the current name. None of the stuff reading and (possibly a good bit of code that writes to it) would notice. If you can get to the point where the raw table is only accessed via the view and some stored procedures, then you can start hacking away the structure.

A code (no sql) only would be to insert an ORM style access in between the application(s) and the table. That's a decision that should be based on your skill set and the mix of applications more than anything else.

Unless you can and are prepared to decouple all the applications from the specific implemention that is this table, you are simply polishing faeces. No point in spending valuable resources on that.