使用SQLXML覆盖Schema.ini并进行批量上传

• 下载源文件 - 77.6 KB

引言

本文介绍如何动态覆盖Schema.ini文件以方便批量上传，以及如何使用SQLXML功能将数据批量上传到SqlServer 2000表中。

背景

你的web应用程序在使用excel进行批量上传时，是否遇到过Jet引擎没有将列值视为文本的问题？

在本文中，我们将学习如何克服这个问题，并查看一个将批量数据上传到SQL Server 2000表的示例代码，使用SQLXML功能（尽管还有数百种不同的方法可以做到这一点！）。

使用代码

我每两周会收到一个很大的文件，需要上传到SQL Server表中。这些列的值带有前导零。

包含示例数据的文件看起来像这样

// sample Provider_file.txt
provider ID,License
0000018, 079376
0000019, 076185
0000021, 034733
0000024, 032921
0000025, 034551
0000043, 079331
0000107, 035911
0000272, 075555
0000295, 074591
0000331, 057518
0000342, 074673
0000371, 079491

现在，我可以使用OPENROWSET功能或DTS将此文件上传到目标表。但是，我的web应用程序中有一个小的文件上传界面，我用它来上传文件。这也使我的客户能够随时在线上传数据。

我在代码中所做的是使用日期时间戳保存此文件，以确保我不覆盖以前的文件。

<label>UploadFile</label>
<INPUT id=txtFileToUpload type=file name=txtFileToUpload 

runat="server" /> 
&nbsp;&nbsp; 
<INPUT id=btnUpLoad onclick=doProcess(); 
       type=button value=Process name=btnUpLoad 
       Runat="server" />     

然后将文件内容读取到数据集。

Dim cnn As OleDb.OleDbConnection

Dim dsCSV As New DataSet("Providers") cnn = 
    New OleDb.OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0;Data

Source=" + filePath + ";Extended Properties=Text")
Dim mySelect As String = String.Empty

mySelect = "Select * from [" & fileName & "]"

Dim da As New OleDb.OleDbDataAdapter(mySelect, cnn)
da.Fill(dsCSV)

现在，在将数据选择到数据集时，我发现包含带有前导零的数据的列没有被视为文本列类型，即使在excel中将列类型更改为文本，然后将文件转换为CSV文件也是如此。这显然意味着当数据读取到数据集时，前导零将会消失。然后我发现，为了让Jet引擎知道数据列的数据类型，我需要将包含列数据类型的Schema.ini文件放到包含数据文件的文件夹中。否则，Jet引擎会根据数据的前几行将列解释为数字。

Schema.ini覆盖

[PROVIDER_FILE_02232007090418.txt]
ColNameHeader = True
Format = CSVDelimited
CharacterSet=ANSI
Col1=IfProv Char Width 20
Col2=License Char Width 20

Schema.ini文件的第1行包含需要应用模式的目标文件名，包括文件扩展名。第2行说明目标文件中是否存在列标题。第3行包含目标文件的格式。第4行包含字符集。第4行之后，您可以开始声明列名及其数据类型。请注意，Schema.ini文件应与您尝试创建的目标数据文件位于同一文件夹中。

在我的应用程序中，由于上传是每两周进行一次，并且由于我需要保留以前上传的文件，因此我必须想出一个方法来更改Schema.ini文件中文件名（第1行）。所以我决定每次上传时都动态创建Schema.ini文件，方法是输入第一行。这段代码看起来像这样

fileStream = New FileStream(fileName, 

FileMode.Truncate)
writer = New StreamWriter(fileStream)
writer.WriteLine("[" & UploadfileName & "]")
writer.WriteLine("ColNameHeader = True")
writer.WriteLine("Format = CSVDelimited")
writer.WriteLine("CharacterSet=ANSI")
writer.WriteLine("Col1=IfProv Char Width 20")
writer.WriteLine("Col2=License Char Width 20")

将数据读取到数据集后，在获取XML输出之前重命名表和列名（如果需要）。

If Not dsCSV Is Nothing Then
   dsCSV.Tables(0).TableName = "Provider"
dsCSV.Tables(0).Columns(0).ColumnName = "IfProv"
   dsCSV.Tables(0).Columns(1).ColumnName = "License"
End If

重命名表名和列名后，XML数据将如下所示

<Providers>
    <Provider>
        <License>79376</License>
        <ifprov>0000018</ifprov>
    </Provider>
    <Provider>
        <License>76185</License>
        <ifprov>0000019</ifprov>
    </Provider> 
</Providers>

将XML数据发送到存储过程

将XML传递给存储过程进行处理。

SqlHelper.ExecuteNonQuery(ConnectionString, _
        CommandType.StoredProcedure, _
        "BULK_UPLOAD_XML_DATA", _
        New SqlParameter() { _
        New SqlParameter("@PROVDATA", xmlProviderData), 

returnParameter _
            })

使用SQLXML上传数据

创建一个包含两列的表，如下所示。

CREATE TABLE TBL_PROVIDER(IfProv VARCHAR(20) 

NULL,License VARCHAR(20) NULL)

使用SQLXML功能将XML数据上传到表的存储过程如下所示

CREATE PROC BULK_UPLOAD_PROVIDER_DATA
@PROVDATA 

    NTEXT 
AS 
    /*PARSE XML AND KEEP IT READY FOR CONSUMPTION IN THE MEMORY */ 
    EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT,@PROVDATA 
    
    --DUMP DATA INTO THE TBL_PROVIDER 
    INSERT INTO 
        TBL_PROVIDER (IfProv,License)
        SELECT * FROM OPENXML
              (@IDOC, '/Providers/Provider')  WITH (IfProv
                    VARCHAR(10)   'IfProv', License
        VARCHAR(10) 'License') WHERE IfProv IS NOT NULL AND License IS NOT _ 
            NULL 
        
    /*REMOVE THE DOCUMENT FROM THE    MEMORY */ 
    
    EXEC SP_XML_REMOVEDOCUMENT @IDOC

总结

我一直很喜欢使用SQL Server的SQLXML功能，我希望本文中的Schema.ini覆盖以及SQLXML功能能够帮助很多人解决他们日常的批量上传问题。

免责声明：源代码包含Microsoft的SQLHelper辅助类，用于进行数据库调用，我在我的大多数应用程序中都使用它（我相信许多读者也使用它！），它不是我编写的。我将其包含在源代码zip文件中，只是为了确保代码下载后可以正常工作。