Executing query with parameters

Question

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

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.

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

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

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

Answer 1

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

This is a good article for beginners with ADO.Net

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

Answer 2

    using SqlCommand cmd= conn.CreateCommand())
    {
                        cmd.CommandText = @"INSERT INTO TABLE (COLUMNS) VALUES (@Id, @account etc...


                        cmdUser.Parameters.Add(new SqlParameter("@User", SqlDbType.UniqueIdentifier) { Value = UserTypeID });
                        cmdUser.Parameters.Add(new SqlParameter("@Id", SqlDbType.UniqueIdentifier) { Value = ApprovalTypeID });
                        cmdUser.Parameters.Add(new SqlParameter("@AccountId", SqlDbType.UniqueIdentifier) { Value = UserID });
                        cmdUser.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 100) { Value = Name });
                        cmdUser.Parameters.Add(new SqlParameter("@JobTitle", SqlDbType.NVarChar, 100) { Value = Surname });
                        cmdUser.Parameters.Add(new SqlParameter("@PhoneNumber", SqlDbType.Bit) { Value = Active });
    cmdUser.ExecuteNonQuery();
}

Answer 3

        try
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Insert_User", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (cn.State != ConnectionState.Open)
                    cn.Open();
                cmd.Parameters.Add("Id", SqlDbType.NVarChar).Value = "00A640BD-1A0D-499D-9155-BA2B626D7B68";
                cmd.Parameters.Add("AccountId", SqlDbType.NVarChar).Value = "DCBA241B-2B06-48D7-9AC1-6E277FBB1C2A";
                cmd.Parameters.Add("FirstName", SqlDbType.NVarChar).Value = "Mark";
                cmd.Parameters.Add("LastName", SqlDbType.NVarChar).Value = "Wahlberg";
                cmd.Parameters.Add("JobTitle", SqlDbType.NVarChar).Value = "Actor";
                cmd.Parameters.Add("PhoneNumber", SqlDbType.NVarChar).Value = "9889898989";

                return cmd.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

and for stored procedure, in sql:

    create procedure [Insert_User]
(
@id as nvarchar(100),
@accid as nvarchar(100),
@fname as nvarchar(100),
@lname as nvarchar(100),
@jobtitle as nvarchar(100),
@phone as nvarchar(100)
)
    INSERT INTO [DB].[dbo].[User]
               ([Id]
               ,[AccountId]
               ,[FirstName]
               ,[LastName]
               ,[JobTitle]
               ,[PhoneNumber]
              )
         VALUES
               (@id
               ,@accid
               ,@fname
               ,@lname
               ,@jobtitle
               ,@phone])

also, you can use text boxes or other input type controls to set values. You can change dataType, as you wish, such as uniqueidentifier, int, etc. If one or more of values are set as identifire, eg. AccountID, remove them from query.

Answer 4

Frankly, ADO.NET makes it hard to do things like this correctly. Tools like Dapper exist to make that easier:

dbConn.Execute(
     @"insert into [DB].[dbo].[User] ( [Id], [AccountId], [FirstName], [LastName],
                                       [JobTitle], [PhoneNumber] )
       values ( @id, @accountId, @firstName, @lastName, @jobTitle, @phoneNumber )",
       new { id, accountId, firstName, lastName, jobTitle, phoneNumber });

This will deal with all the parameterization for you, efficiently, effectively, and safely.

There are similar APIs for executing queries and populating objects.

Answer 5

The above answers definitely good solutions for the question. However, I would like to recommend more dynamic and robust solution.

Here is your controller (in my example Ajax call)

public JsonResult RecordThis(FormCollection fc)
{
    SqlRecord.RecordThis(fc);
    return View();
}

Then, on your Model (in example SqlRecord) create "Record" function;

public static void Record(FormCollection fc)
{
   var sql = "INSERT INTO RecordTable VALUES ('@Email','@Name','GETDATE()')";
   var parameters = BuildSqlParams(fc);
   ExecuteInlineSqlWithParams(sql, yourconnectionstringhere, parameters);
}

Then, add these two functions below to be used for any function like "Record". You can add conditions as well such as excluding tokens.

public static List<SqlParameter> BuildSqlParams(FormCollection fc)
{
     var parameters = new List<SqlParameter>();
     foreach(var key in fc.AllKeys)
     {   
         if(key != "__RequestVerificationToken")
            parameters.Add(new SqlParameter("@"+key+"", fc[key]));
     }
     return parameters;
}

public static void ExecuteInlineSqlWithParams(string sql, string cnn, List<SqlParameter> parameters )
{
   var connection = new SqlConnection(cnn);
   var command = new SqlCommand(sql, connection);
   command.CommandType = CommandType.Text;
   foreach(var param in parameters)
   {
      command.Parameters.Add(param);
   }
   using (connection)
   {
      connection.Open();
      command.ExecuteNonQuery();
      connection.Close();
   }
}

Answer 6

You'll need the System.Data.SqlCommand class.

Change the fixed values to named parameters. For example:

INSERT INTO [TABLE] (Column1) values (@Value1) // the @Value1 is the named parameter

Example:

var connection = new SqlConnection("connectionstring");
var command = connection.CreateCommand();
command.CommandText = "insert...."; // sql command with named parameters

// set the named parameter values
command.Parameters["@Value1"] = "Mark wa...";

// execute 
command.ExecuteNonQuery();

SqlCommand reference: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx