Issue with Dynamic SQL (Also: How to get SQL Lines from ExecuteNonQuery)

Question

I am using Visual Studio, I'm trying to make a Insert statement using C# and Dynamic SQL.

SQL Command:

comando.CommandText = @"
                      declare @sql nvarchar(max);                                                               
                      set @sql = 'INSERT INTO ' + @curso + '(Data_Local, Deletado) VALUES ( ' + @date + ', 0)';
                      exec (@sql);                                  
                      ";

@curso is the Table Name, it's working, Data_Local is a Datetime value, which is not working, and Deletado is a bit value, which is working.

Declaring @curso:

comando.Parameters.Add("@curso", SqlDbType.NVarChar);
            comando.Parameters["@curso"].Value = idCurso;

Declaring @date

comando.Parameters.Add("@date", SqlDbType.DateTime);
            comando.Parameters["@date"].Value = dateTimePicker2.Value.Date;

Then after:

comando.ExecuteNonQuery();

The error message appears:

'Conversion failed when converting date and/or time from character string.'

What should I do to read the value from the DateTimePicker, insert into the @date variable, and add it to the SQL Command properly?

Also, I have another doubt, where can I see how the SQL Command is sent? I need to see how the lines really are, how is the SQL Command without the variables, with the values instead.

Sorry if my issue is not explained very well, I'll keep trying to make it clear.

Edit:

I tried:

comando.CommandText = @"
                      declare @sql nvarchar(max);                                                               
                      set @sql = 'INSERT INTO ' + @curso + '(Data_Local, Deletado) VALUES ( ' + @date + ', 0)';
                      exec (@sql);                                  
                      ";  

comando.Parameters.Add("@date", SqlDbType.NVarChar);
            comando.Parameters["@date"].Value = "'" + dateTimePicker2.Value.Date + "'";

New error message:

'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.'

Edit. Thanks, solved.

Did you add quote `''` to the date and add the date as a string? — Ilyes, Aug 23 '18 at 13:20
`where can I see how the SQL Command is sent?` use `SQL Server Profiler` https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-2017 — Squirrel, Aug 23 '18 at 13:28
`INSERT INTO ''' + @curso+ '''(Data` Note that @curso should be string — Ilyes, Aug 23 '18 at 13:29
You should realize that this approach is vulnerable to SQL injection attacks. This answer explains the problem and a solution https://stackoverflow.com/a/1246848/109122 — RBarryYoung, Aug 23 '18 at 13:29
If you are passing `@Date` as a parameter then you don't need dynamic SQL, you simply use the value: `... values ( @Date );`. The conversion should be done in your application and the parameter data type ought to be `SqlDbType.DateTime`. — HABO, Aug 23 '18 at 13:36
I (think I) need Dynamic SQL because of the variable table name. — Victor Damázio, Aug 23 '18 at 13:43
I would look closer at your architecture. If you have a bunch of tables with the exact same columns so that you have to pass in the table name like this it usually means you need to redesign some of your data models. — Sean Lange, Aug 23 '18 at 13:49

score 0 · Accepted Answer · answered Aug 23 '18 at 13:43

Rather use a Stored Procedure, its awful if you going to use dynamic SQL in your c# application. Firstly because as a DBA, Adhoc queries like these are difficult to maintain, and secondly its easier to manage and also easier to apply to code.

1) Create a Stored Procedure

CREATE PROCEDURE Insert_Test
(
    @curso VARCHAR(256),
    @date VARCHAR(32)
)

AS

SET NOCOUNT ON

DECLARE @sql NVARCHAR(MAX)

IF ISDATE(@date) = 1 --check if its the correct date
BEGIN

    SET @sql = 'INSERT INTO ' + @curso + '(Data_Local, Deletado) VALUES ( ''' + @date + ''', 0)'

    EXEC (@sql)
END

2) In your c# code

 string tbleName = "Test";
 DateTime dte = DateTime.Now.ToString(); 

 comando.ExecuteSqlCommand("exec Insert_Test @curso, @date", 
                                            new SqlParameter("curso", tbleName),
                                            new SqlParameter("date", dte)
                          );

If you use the dynamic sql approach as suggested here make sure you at the very least wrap your table name in QUOTENAME to help mitigate sql injection vulnerability. As posted here it is wide open. — Sean Lange, Aug 23 '18 at 13:48
using (var command = new SqlCommand("exec Insert_Test " + tbleName + "" + dte.ToString() + "", connectionstring) { CommandType = CommandType.StoredProcedure; connectionstring.Open(); command.ExecuteNonQuery(); }) — mvisser, Aug 24 '18 at 12:04

Issue with Dynamic SQL (Also: How to get SQL Lines from ExecuteNonQuery)

1 Answers1