how to convert sqlrebuilt index to C# [duplicate]

Question

I am trying to run rebuild index using C# from my database query, but I've encountered this problem? Any ideas?

Here is my rebuild index query in C#:

public static string rebuildIndex = "ALTER INDEX ALL @tablename REBUILD PARTITION = @partition_number WITH (ONLINE = ON)";

Here is the code from the respective function:

static void rebuildIndex(string query,string tablename, int parNumber)
{
    using (SqlCommand cmd = new SqlCommand(query, con))
    {
        cmd.Parameters.AddWithValue("@tablename", tablename);
        cmd.Parameters.AddWithValue("@partition_number", parNumber);
        cmd.ExecuteNonQuery();
    }
}

And here is the error msg.

*ERROR yourmessage Incorrect syntax near '@tablename'. [HouseKeep_VC.Program] [1]
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@tablename'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Please do not upload images of code/errors but provide it as text — Klaus Gütter
– Klaus Gütter, Commented Jun 9, 2022 at 4:22
You can't pass table name as well as partition number via bind variable. — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Jun 9, 2022 at 4:45
@DmitryBychenko Looks like it will accept a partition number, but obviously not a table name dbfiddle.uk/… — Charlieface
– Charlieface, Commented Jun 9, 2022 at 8:51

Charlieface · Accepted Answer · 2022-06-09 19:48:08Z

You need to use dynamic SQL for the table name, it cannot be parameterized. Make sure to use QUOTENAME to quote the name correctly

You should also create and dispose the connection, rather than caching it.
You should specify parameter types (and lengths) explicitly.

static void rebuildIndex(string schemaName, string tablename, int parNumber)
{
    const string rebuildIndex = @"
DECLARE @sql nvarchar(max) = '
  ALTER INDEX ALL ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tablename) + '
  REBUILD PARTITION = @partition_number
  WITH (ONLINE = ON);
';

EXEC sp_executesql @sql,
  N'@partition_number int',
  @partition_number = @partition_number;
";

    using (var con = new SqlConnection(YourConString))
    using (var cmd = new SqlCommand(query, con))
    {
        cmd.Parameters.Add("@schemaName", SqlDbType.NVarChar, 128).value = schemaName;
        cmd.Parameters.Add("@tablename", SqlDbType.NVarChar, 128).value = tablename;
        cmd.Parameters.Add("@partition_number", SqlDbType.Int).Value = parNumber;
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

Collectives™ on Stack Overflow

how to convert sqlrebuilt index to C# [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related