我如何通过一个GUID值转换为SqlCommand对象的SQL INSERT语句？ [英] How do I pass a GUID value into an SqlCommand object SQL INSERT statement?

问题描述

我有部署下面的描述中.dbproj项目中创建一个SQL Server数据库表：

I have an SQL Server database table created by deploying the following description in a .dbproj project:

CREATE TABLE [dbo].[Tasks]
(
TaskId uniqueidentifier primary key,
State int not null,
)

和我想插入一行到该表具有以下code：

and I want to insert a row into that table with the following code:

using( SqlTransaction transaction = connection.BeginTransaction() ) {
    using( SqlCommand command = connection.CreateCommand() ) {
        command.CommandText = "INSERT INTO Tasks VALUES( \"" +
            Guid.NewGuid().ToString() + "\", 0)";
        command.Transaction = transaction;
        command.ExecuteNonQuery();
        transaction.Commit();
    }
}

在的ExecuteNonQuery（）运行的exeption被抛出话说

when ExecuteNonQuery() runs an exeption is thrown saying

名称[字符串再经过我的GUID presentation]是不是在这方面允许的。

The name [the string representation of the GUID I passed] is not permitted in this context.

这是怎么回事？我也同样将数据插入到一个SQLite表previously和它的工作。我如何通过一个GUID到一个SQL INSERT语句？

What's up? I did the same to insert data into an SQLite table previously and it worked. How do I pass a GUID into an SQL INSERT statement?

推荐答案

使用参数化查询，就像这样：

Use a parameterized query, like so:

command.CommandText = "INSERT INTO Tasks VALUES( @id, 0)";
command.Parameters.Add( "@id", SqlDbType.UniqueIdentifier, 16 ).Value = value;

这种方式，数据库驱动程序格式化你的价值。这是一个很好的做法，也将有助于保护您的数据库从SQL注入攻击。

This way, the database driver formats the value for you. This is a good practice that will also help protect your database from SQL Injection attacks.

另外，也可以让数据库生成的GUID为您提供：

Alternatively, you could let the database generate the guid for you:

command.CommandText = "INSERT INTO Tasks VALUES( NEWID(), 0)";

这篇关于我如何通过一个GUID值转换为SqlCommand对象的SQL INSERT语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！