如何将 DataSet 中的数据保存到 T-SQL 表

• 下载源代码 - 16.9 KB

引言

本文将帮助您理解将 DataSet 中的数据保存到 T-SQL 表的过程。当我们需要从外部资源提取数据到 DataSet 并希望将这些数据保存到我们自己的数据库时，这通常是必需的。

背景

我尝试详细解释这个主题，但需要对 C#、SQL 和 XML 有基本的了解才能理解本文。在本文中，我们将执行以下操作：

1. 创建一个示例 DataSet。
2. 将此 DataSet 转换为 XML 字符串。
3. 将此 XML 字符串发送到存储过程，该存储过程将解析 XML 并将数据保存到 T-SQL 表。
4. 分析将执行步骤 3 的 T-SQL 过程。

创建示例数据

以下是创建 SampleData 表的 SQL 脚本。此脚本将创建一个空表。

CREATE TABLE SampleData
(
 [Name]  varchar(50) NOT NULL,
 [Address] varchar(50) NOT NULL,
 [Phone]  varchar(15) NOT NULL
)

下面的 C# 代码将示例数据获取到 DataSet 中。我们为 DataTable 创建了三个不同的列，名为 Name、Address 和 Phone。

/// <summary>
/// This method is used to populate sample data.
/// Instead of this method, you can call a method which will
/// populate data from external data sources.
/// </summary>
/// <returns>DataSet with a sample data.</returns>
private static DataSet GetDataSet()
{
    DataSet ds = new DataSet();
    DataTable dt = new DataTable("Table");
    
    dt.Columns.Add("Name", Type.GetType("System.String"));
    dt.Columns.Add("Address", Type.GetType("System.String"));
    dt.Columns.Add("Phone", Type.GetType("System.String"));
    
    DataRow dr = dt.NewRow();
    dr["Name"] = "Sandeep Aparajit";
    dr["Address"] = "Redmond USA";
    dr["Phone"] = "425-000-0000";
    dt.Rows.Add(dr);
    
    dr = dt.NewRow();
    dr["Name"] = "Anthony Gill";
    dr["Address"] = "Ohio USA";
    dr["Phone    "] = "625-000-0000";
    dt.Rows.Add(dr);
    
    ds.Tables.Add(dt);
    return ds    ;
}

将 DataTable (DataSet) 转换为 XML 字符串

这一个重要的步骤。在这里，我们将实际将 DataTable 转换为其等效的 XML 字符串。我们使用 DataSet.WriteXML() 方法从 DataSet 中获取 XML 字符串。

/// <summary>
/// This method will convert the supplied DataTable 
/// to XML string.
/// </summary>
/// <param name="dtBuildSQL">DataTable to be converted.</param>
/// <returns>XML string format of the DataTable.</returns>
private static string ConvertDataTableToXML(DataTable dtData)
{
    DataSet dsData = new DataSet();
    StringBuilder sbSQL;
    StringWriter swSQL;
    string XMLformat;
    try
    {
        sbSQL = new StringBuilder();
        swSQL = new StringWriter(sbSQL);
        dsData.Merge(dtData, true, MissingSchemaAction.AddWithKey);
        dsData.Tables[0].TableName = "SampleDataTable";
        foreach (DataColumn col in dsData.Tables[0].Columns)
        {
            col.ColumnMapping = MappingType.Attribute;
        }
        dsData.WriteXml(swSQL, XmlWriteMode.WriteSchema);
        XMLformat = sbSQL.ToString();
        return XMLformat;
    }
    catch (Exception sysException)
    {
        throw sysException;
    }
}

Main 方法

这是 Main 方法，它将调用上述方法进行转换。一旦 DataSet 转换为 XML 字符串，我们将调用 sp_InsertData 存储过程并将此 XML 字符串作为参数传递。该存储过程负责解析 XML 并将数据插入到 T-SQL 表中。

static void Main(string[] args)
{
    // Get the sample data into DataSet.
    DataSet dsData = GetDataSet();
    
    // Get the XML format of the data set.
    String xmlData = ConvertDataTableToXML(dsData.Tables[0]);
    
    // Create a SQLConnection object.
    // TODO: Specify the correct connection string as on you computer.
    SqlConnection conn = new SqlConnection
    ("Data Source=.;Initial Catalog=DBName;Integrated Security=SSPI;");
    
    // Create the SQlCommand object which will be used to insert the data
    // into T-SQL tables.
    SqlCommand command = new SqlCommand
    ("sp_InsertData '" + xmlData + "'", conn);
    
    // Open the SQL Connection.
    conn.Open();
    
    // Execute the stored procedure mentioned above .
    command.ExecuteNonQuery();
    
    // Close the SQL Connection.
    conn.Close();
}

存储过程：Sp_InsertData

存储过程 sp_InsertData 是此操作的核心，因为它解析 XML 字符串并将数据插入到 T-SQL 表中。

/******************************************************************************
* Stored Procedure : sp_InsertData
* Author : Sandeep Aparajit 
* Description : This stored procedure will accept the data as 
* an XML data table. It will parse the data table and will
* insert the data into the SampleData table.
* Date : 05 Nov 08
* Revision :
******************************************************************************/
CREATE PROCEDURE sp_InsertData
(@xmlString VARCHAR(MAX))
AS
BEGIN
    /* Initialize a handle for the XmlDocument */ 
    DECLARE @xmlHandle INT 
    
    /* 
    Create a table variable to store the extract XmlData into Relational 
    Database Format. Unlike temporary tables, Table variables are 
    automatically removed from memory by SQL Server when the procedure 
    has completed. So, there is no need to try and drop them at the 
    end of the procedure. 
    */ 
    DECLARE @stagingTable TABLE 
    ( 
        [Name] VARCHAR(50), 
        [Address] VARCHAR(50), 
        [Phone] VARCHAR(50)
    ) 

    
    /* 
    Create the XmlDocument using the handle above and the Xml 
    string as parameters. If your stored procedure has an varchar input 
    parameter named @xmlString, it would look like this instead: 
    EXEC sp_xml_preparedocument @xmlHandle output,@xmlString 
    */ 
    EXEC sp_xml_preparedocument @xmlHandle output, @xmlString 
    
    /* 
    Use the OPENXML method to query the XmlDocument starting at 
    /NewDataSet/SampleDataTable node.
    */ 
    INSERT INTO @stagingTable 
    SELECT   [Name] ,
        [Address],
        [Phone] 
    FROM OPENXML (@xmlHandle, '/NewDataSet/SampleDataTable',1) 
        WITH ([Name] varchar(50) '@Name', 
            [Address] varchar(50) '@Address', 
            [Phone] varchar(50) '@Phone'
             )


    
    /*Insert the records into the table variable turning the XML structured 
    data into relational data. We are now free to query the table variable 
    just as if it were a regular table for use with data manipulation, cursors, etc... 
    It could also be used for generated reports and counts in ways that might 
    be simpler to code in SQL Server vs XSL.*/ 
    INSERT INTO SampleData ([Name], 
            [Address], 
            [Phone]) 
    (SELECT [Name] ,
        [Address],
        [Phone]
    FROM @stagingTable)



    
    /* Remove the document from memory */ 
    EXEC sp_xml_removedocument @xmlHandle
END

其他

请访问 我的博客，获取更多有趣的的文章 :)

历史

• 文章的初始版本于 2008 年 11 月 5 日发布。