从xml导入到sql c时出错 [英] Error importing from xml to sql c#

问题描述

大家好，



我将XML文件导入sql表时遇到问题。



我收到消息abca9d附近的语法不正确，这将成为guid的一部分< idmedia> 33abca9d-c05c-467a-b1e4-9f600181bfea



代码我用的就是这个。我哪里错了？

Hello everyone,

I have a problem with importing an XML file into a sql table.

I get the message "Incorrect syntax near abca9d" that would be part of the guid <idmedia>33abca9d-c05c-467a-b1e4-9f600181bfea

The code I used is this. Where am I wrong?

private void cboImportaMedia_Click(object sender, EventArgs e)
{
    string connetionString = null;
    SqlConnection connection;
    SqlCommand command ;
    SqlDataAdapter adpter = new SqlDataAdapter();
    DataSet ds = new DataSet();
    XmlReader xmlFile ;
    string sql = null;


    string _IdMedia = null;
    string _Type = null;
    string _Name = null;
    string _Label = null;
    string _Content = null;
    string _DateCreation = null;
    string _DateUpdate = null;
    string _IdSession = null;

    connetionString = "Data Source='" + cboIstanza.Text + "';Initial Catalog='" + cboDataBase.Text + "';Integrated Security=SSPI;";

    connection = new SqlConnection(connetionString);

    xmlFile = XmlReader.Create("Media.xml", new XmlReaderSettings());
    ds.ReadXml(xmlFile);
    int i = 0;
    connection.Open();
    for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
    {
        _IdMedia = Convert.ToString(ds.Tables[0].Rows[i].ItemArray[0]);
        _Type = ds.Tables[0].Rows[i].ItemArray[1].ToString();
        _Name = ds.Tables[0].Rows[i].ItemArray[1].ToString();
        _Label = ds.Tables[0].Rows[i].ItemArray[1].ToString();
        _Content = ds.Tables[0].Rows[i].ItemArray[1].ToString();
        _DateCreation = ds.Tables[0].Rows[i].ItemArray[1].ToString();
        _DateUpdate = ds.Tables[0].Rows[i].ItemArray[1].ToString();
        _IdSession = ds.Tables[0].Rows[i].ItemArray[1].ToString();
        sql = "INSERT INTO Media VALUES(" + _IdMedia + ", " + _Type + "," + _Name + ", " +_Label + ", " + _Content + ", "+_DateCreation +", "+ _DateUpdate +", "+ _IdSession +")";
        command = new SqlCommand(sql, connection);
        adpter.InsertCommand = command;
        adpter.InsertCommand.ExecuteNonQuery();
    }
    connection.Close();
    MessageBox.Show("Importazione avvenuta con successo ");

推荐答案

您收到错误，因为GUID必须作为字符串传递，即用撇号括起来。实际上有一种更好的方法来插入数据集中的所有记录。你看，你已经有 SqlDataAdapter [ ^ ]那里，你只是没有正确使用它。试试这个：

You get the error because the GUID has to be passed as a string i.e. enclosed in apostrophes. Actually there is a better way to insert all the records from the dataset. You see you already have the SqlDataAdapter[^] there, you are just not using it properly. try this:

// do not form the command using concatenation, always use parameters!
const string insertCommandText = "INSERT INTO Media VALUES(@IdMedia,@Type,@Name,@Label,@Content,@DateCreation,@DateUpdate,@IdSession)";

// all the classes implement IDisposable, you should wrap them in using statement
using (var connection = new SqlConnection(connetionString))
using (var adapter = new SqlDataAdapter())
using (var insertCommand = new SqlCommand(insertCommandText, connection))
{
    insertCommand.Parameters.Add("@IdMedia", SqlDbType.UniqueIdentifier, 16, "IdMedia");
    // add mapping for the other parameters here

    // leverage the SqlDataAdapter
    adapter.InsertCommand = insertCommand;
    adapter.Update(ds.Tables[0]);
}

正如您所看到的，您无需逐个删除行。这假设行在已添加 [< a href =http://msdn.microsoft.com/en-us/library/system.data.datarowstate(v=vs.110).aspx\"target =_ blanktitle =New Window> ^ ]状态，但它们应该是在插入之前从XML文件中读取它们。

As you can see, you don't need to go trough the rows one by one. This assumes the rows are in Added[^] state, but they should be when you read them from the XML file just before insertion.

你有两个问题......

第一个是最关键的 - 你使用字符串连接来创建你的查询......这会打开你的应用程序基于SQL注入的攻击...

考虑使用 parametrized [ ^ ]查询......

You have two problems...
The first is the most critical - you are using string concatenation to create your query...This opens your application to SQL injection based attacks...
Consider to use parametrized[^] queries...

sql = "INSERT INTO Media VALUES(@IdMedia, @Type, @Name, @Label, @Content, @DateCreation, @DateUpdate, @IdSession)";
command = new SqlCommand(sql, connection);

command.Parameters.Add("@IdMedia", SqlDbType.NVarChar);
command.Parameters.Add("@Type", SqlDbType.NVarChar);
// and so on...

adpter.InsertCommand = command;
adpter.InsertCommand.ExecuteNonQuery();

第二个问题是你正在使用字符串值 - 如IdMedia - 但不要将它们括在引号之间，因此SQL无法正确解释它们...您的实际查询如下所示：

The second problem is that you are using string values - like IdMedia - but do not enclose them between quotes, so SQL can not interpret them correctly...Your actual query looks like this:

INSERT INTO Media VALUES(33abca9d-c05c-467a-b1e4-9f600181bfea, ...

当SQL点击第一个' - '符号时，它无法解释查询...

但是你幸运的是，如果你解决了第一个问题并转向参数化查询，你也解决了第二个问题： - ）

When SQL hits the first '-' sign it fails to interpret the query...
But you are fortunate if you solve the first problem and move to parametrized queries you also solve the second problem :-)

这篇关于从xml导入到sql c时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！