用于更新所有表的公共存储过程 [英] Common stored procedure to update all tables

问题描述

我有一个验证地址的程序，现在我正在使用这个控制台应用程序通过两个程序调用它来输入地址而另一个用于输出地址。



第一个过程返回要验证的地址列表，第二个过程将验证的地址更新回表格。



从控制台（c＃）app我打电话给第一个过程，通过传递经过验证的地址的值来验证每个地址并调用第二个过程



最近，Web服务公开了一种批量验证地址的新方法。我正在寻找一个解决方案只有一个get proc和一个set proc来更新任何带有地址的表，所以其他进程可以使用相同的控制台。



下面是我想到的传递给proc的XML。如何构建动态sql来更新表？

I have a program to validate addresses, right now I am using this console app by calling it with two procedures one for input address and another for output address.

The first procedure returns the list of addresses to be validated and the second one updates validated addresses back to the table.

From console (c#) app I am calling the first procedure, validating each address and calling the second proc by passing values of the validated address

Recently the webservice exposed a new method for validating addresses in bulk. I am looking for a solution to have only one get proc and one set proc to update any table with addresses so the same console can be used by other processes.

Below is the XML I have thought of to pass to the proc. How can I build a dynamic sql to update the table ?

<AddressValidator>
    <ValidationResults>
        <ExtensionData />
        <Results>
            <ValidationResult>
                <ExtensionData />
                <ReturnCode>-1</ReturnCode>
                <ErrorCode>491</ErrorCode>
                <ErrorMessage>Failed DPV because of invalid primary</ErrorMessage>
                <CASSDate>0</CASSDate>
                <ValidatedAddress>
                    <ExtensionData />
                    <InputAIID />
                    <ID>501</ID>
                    <AddressLine1 />
                    <AddressLine2 />
                    <City />
                    <State />
                    <ZipCode />
                    <County />
                    <MailRoomServer />
                    <FirstName />
                    <LastName />
                    <Business />
                </ValidatedAddress>
            </ValidationResult>
        </Results>
    </ValidationResults>
    <Mappings>
        <Map_Type>Input</Map_Type>
        <Data_Table>Addresstable</Data_Table>
        <AddressLine1>MAIL_ADDR1</AddressLine1>
        <AddressLine2>MAIL_ADDR2</AddressLine2>
        <City>MAIL_CITY</City>
        <State>MAIL_STATE</State>
        <ZipCode>MAIL_ZIP</ZipCode>
        <isBadAddress>BadAddressYN</isBadAddress>
        <ErrorCode>SatoriErrCode</ErrorCode>
        <ID>ID</ID>
    </Mappings>
    <Mappings>
        <Map_Type>Output</Map_Type>
        <Data_Table>Addresstable</Data_Table>
        <AddressLine1>VAL_MAIL_ADDR1</AddressLine1>
        <AddressLine2>VAL_MAIL_ADDR2</AddressLine2>
        <City>VAL_MAIL_CITY</City>
        <State>VAL_MAIL_STATE</State>
        <ZipCode>VAL_MAIL_ZIP</ZipCode>
        <isBadAddress>BadAddressYN</isBadAddress>
        <ErrorCode>ErrCode</ErrorCode>
        <ID>ID</ID>
    </Mappings>
</AddressValidator>

推荐答案

请使用更新数据表值参数（ http://msdn.microsoft.com/en -us / library / bb675163％28v = vs.110％29.aspx [ ^ ]）



或TableAdapter（http://msdn.microsoft.com/en-us/library/bz9tthwx.aspx [ ^ ]



用这种方式你可以添加/更新多行或多个表。

Please update your data using Table-Valued Parameters (http://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx[^])

or TableAdapter (http://msdn.microsoft.com/en-us/library/bz9tthwx.aspx[^]

with this way you can add/Update Multiple rows or multiple tables.

这篇关于用于更新所有表的公共存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！