执行查询与参数 [英] Executing query with parameters

问题描述

我想执行的.sql 从C＃脚本。基本上，该脚本插入一行到几个不同的表。

I want to execute a .sql script from C#. Basically the script inserts a row into few different tables.

关键是我有在C＃code值，我需要传递给的.sql 查询。这些值会在程序执行过程中收集的。

The point is I have values in C# code that I need to pass to the .sql query. These values will be collected during program execution.

下面是我想从C＃code来执行查询：

Here is the query that I want to execute from C# code:

INSERT INTO [DB].[dbo].[User]
           ([Id]
           ,[AccountId]
           ,[FirstName]
           ,[LastName]
           ,[JobTitle]
           ,[PhoneNumber]
          )
     VALUES
           ('00A640BD-1A0D-499D-9155-BA2B626D7B68'
           ,'DCBA241B-2B06-48D7-9AC1-6E277FBB1C2A'
           ,'Mark'
           ,'Wahlberg'
           ,'Actor'
           ,'9889898989'])
GO

该值将时常发生变化时，即，它们位于C＃code和需要传递。

The values will vary from time to time i.e., they are captured in C# code and need to be passed.

任何人都可以请帮我做this..I正在学习C＃和SQL。非常感谢。

Can anyone please help me do this..I am learning both C# and SQL. Thanks a lot.

推荐答案

您可以打开自己可达 SQL注入攻击的在这里，所以最好的做法是使用参数：

You could open yourself up to SQL injection attacks here, so best practice is to use parameters:

using (SqlConnection dbConn = new SqlConnection(connectionString))
{
    dbConn.Open();

    using (SqlTransaction dbTrans = dbConn.BeginTransaction())
    {
        try
        {
            using (SqlCommand dbCommand = new SqlCommand("insert into [DB].[dbo].[User] ( [Id], [AccountId], [FirstName], [LastName], [JobTitle], [PhoneNumber] ) values ( @id, @accountid, @firstname, @lastname, @jobtitle, @phonenumber );", dbConn))
            {
                dbCommand.Transaction = dbTrans;

                dbCommand.Parameters.Add("id", SqlType.VarChar).Value = id;
                dbCommand.Parameters.Add("accountid", SqlType.VarChar).Value = accountId;
                dbCommand.Parameters.Add("firstname", SqlType.VarChar).Value = firstName;
                dbCommand.Parameters.Add("lastname", SqlType.VarChar).Value = lastName;
                dbCommand.Parameters.Add("jobtitle", SqlType.VarChar).Value = jobTitle;
                dbCommand.Parameters.Add("phonenumber", SqlType.VarChar).Value = phoneNumber;

                dbCommand.ExecuteNonQuery();
            }

            dbTrans.Commit();
        }
        catch (SqlException)
        {
            dbTrans.Rollback();

            throw; // bubble up the exception and preserve the stack trace
        }
    }

    dbConn.Close();
}

这是初学者的好文章与ADO.Net

修改 - 正如一些额外的信息，我添加了一个<一个href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx">transaction它，因此如果SQL命令失败，将回滚。

EDIT - Just as a bit of extra info, I've added a transaction to it so if the SQL command fails it will rollback.

这篇关于执行查询与参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！