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

Question

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

Sexuality

Why don't you use Stored Procedure? It will be a hell lot of simpler than this. – Niemeyer

SqlCommand. You want this all server-side. – Gamut

Are you able to add a new stored procedure to the database? – Hexad

Stored procedure - Yes I can Add. Q: How could I send in SP as parameter structure with over 200 fields with some primitive types and some arrays of bytes, int etc.. (struct similar to this: #12635670) – Sexuality

As mentioned above, you could do this with a SqlCommand. – Polypoid

At a possible cost of performance, you could look into some ORM mapper like LINQ, EF or NHibernate so you can work with type-safe POCO objects and fields rather than SQL queries. Depends on whether performance or code abstraction is a higher priority. – Midden

@mellamokbtheWise, performance may be a concern - tests will show it. thanks for suggestions. – Sexuality

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

Vaniavanilla

With SQL server, ADO.NET I'd an option, so why use OLE DB? – Alkalify

@Syn123, What do You mean "the way ole db works is with strings"? Do you mean that all parameters values are passed as strings and are automatically castled? Check, please, this question too #15135704 – Sexuality

sql server connection still uses ole db I believe, it's just Microsoft's version. Read the unicode section of : msdn.microsoft.com/en-us/library/ms810892.aspx . Ole db providers (ADO.NET) uses the sql port to send string data in clear text back and forth between the sql server and the application. – Vaniavanilla

Answer 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

Hexad

thanks a lot - I will analyze this possibility and will write here my result. Once again thanks at least for that this (OPENXML) still is unknown area for me. Is it fully compatible with SQL Server 2000? – Sexuality

Yeah, OPENXML is "baked in" to the TSQL functionality with Sql Server 2000. 2005 and up replaces OPENXML with some slightly different syntax, but they retain OPENXML for backwards compatibility. Bottomline, OPENXML in Sql Server 2000 is not voo doo, it is there for your usage. – Hexad

Answer 3

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

Kujawa

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

Alkalify

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

Dippy

no any Image fields are presented in this table, but in another cases/tables i have to update binary (BLOB) fields - is SP useless here? – Sexuality

Certainly not an sp might be very useful, it would hide this crap table from your application everywhere you used it. As would an ORM, what it won't do is make the current table design in any way good. – Dippy