Executing query with parameters

user_1465893 0
Viewed 49567

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.

sql .net c#
6 Answers

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