Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

c# - How do I pass a GUID value into an SqlCommand object SQL INSERT statement?

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,
)

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();
    }
}

when ExecuteNonQuery() runs an exeption is thrown saying

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Use a parameterized query, like so:

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

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.

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...