0

Using what's available in the System.Data.SQLite namespace and using C# I'm doing database operations. However, there is one problem I can't seem to get figured out.

If I want to create a table which is NAMED DYNAMICALLY AT CREATION, I thought the way to do that would be ...

command.CommandText = @"CREATE TABLE IF NOT EXISTS @tableName( ... )";

and then do this ...

command.Parameters.AddWithValue("@tableName", tableName);

and then do the 

command.ExecuteNonQuery();

But what's happening is that I'm getting a new table named "@tableName" instead of whatever the variable tableName is set to.

How can I get this to work correctly?

  • 1
    Possible duplicate of: https://stackoverflow.com/questions/23357481/how-can-i-pass-a-table-name-to-sqlcommand You can find your answer there – Guillermo Gerard Dec 26 '18 at 19:02
  • That does come to the same conclusion that I ended up at - using string concatenation, What I found was that you can use string concatenation for statements, but for values you have to use the Parameters.AddWithValue. That was my key takeaway, and the thing that I think will help people who end up here in the future. Anyway, yes, same problem, same solution. That other answer is just a bit more convoluted. –  Dec 26 '18 at 22:10

2 Answers2

0

Can you create procedure instead of inline query . you can manage easy in stored procedure like this .`CREATE PROCEDURE Dynamic_SP

  @Table_Name sysname AS BEGIN

  SET NOCOUNT ON;

  DECLARE @DynamicSQL nvarchar(4000)

  SET @DynamicSQL = N'SELECT * FROM ' + @Table_Name

  EXECUTE sp_executesql @DynamicSQL END
Sreenivas Gurram
  • 116
  • 4
0

The way that I approach problems like this is to simplify as much as I can. In this case, simple string substitution should easily resolve your problem without the need to use parameters.

For example:

        command.Text = $@"CREATE TABLE IF NOT EXISTS {tableName}( ... )";

or

        command.Text = string.Format(@"CREATE TABLE IF NOT EXISTS {0}( ... )", tableName);
competent_tech
  • 44,465
  • 11
  • 90
  • 113
  • Turns out I should have just treated it like any other string. CommandText = @"CREATE TABLE IF NOT EXISTS " + tableName + "( ...". That was it. Unbelievably simple. Thanks for triggering that with your answer. –  Dec 26 '18 at 19:45