using local variable value in SQL statement in C#

Question

I am trying to subtract number of days from a date dynamically in sql atatement in

C# code I have is
int myInt=6;       //its value will vary
  var q = SELECT [DATE] FROM DB WHERE  [Date ] < DATEADD(dd, **myint**, GETDATE())";

is there way to pass this variable in sql statement ?

I tried this

 var q = SELECT [DATE] FROM DB WHERE  [Date ] < DATEADD(dd, [coumln]+"'+myint+'", GETDATE())";

Pass it in to where? C# code or SQL. You seem to have a SQL statement written where you have said C# — Wheels73
– Wheels73, Commented Apr 28, 2017 at 9:23
It would be a even better solution to do the date adding in C# and avoid SQL Server to run a add-function to all rows. — Daniel Stackenland
– Daniel Stackenland, Commented Apr 28, 2017 at 11:55

Milney · Accepted Answer · 2017-04-28 09:24:28Z

7

Yes indeed, you should pass this as a parameter;

string connectionString = "YourConnectionString";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(
                "SELECT [DATE] FROM DB WHERE [Date] < DATEADD(dd, @MyInt, GETDATE())", connection))
            {                    
                command.Parameters.Add(new SqlParameter("MyInt", myInt));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DateTime date = reader.GetDateTime(0);
                    Console.WriteLine("Date: {0}",
                        date);
                }
            }
        }

You could also use something like LinqToSql: https://msdn.microsoft.com/en-us/library/bb425822.aspx Or an ORM like Dapper: https://github.com/StackExchange/Dapper

answered Apr 28, 2017 at 9:24

Milney

6,4472 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

murmansk Over a year ago

can i do it without being parameterized

Milney Over a year ago

Technically yes you can, but you really do not want to - it is worse in terms of performance, maintenance and security (SQL Injection attacks). Please use Parameters.... Why would you not want to use them?

Collectives™ on Stack Overflow

using local variable value in SQL statement in C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related