How can I Pass a Table Name to SqlCommand?

by

SqlCommand.Parameters are supported for Data manipulation language operations not Data definition language operations.

Even if you use DML, you can’t parameterize your table names or column names etc.. You can parameterize only your values.

Data manipulation language = 

SELECT ... FROM ... WHERE ...
INSERT INTO ... VALUES ...
UPDATE ... SET ... WHERE ...
DELETE FROM ... WHERE ...

Data definition language =

CREATE TABLE ... 
DROP TABLE ... ;
ALTER TABLE ... ADD ... INTEGER;

You can’t use DROP statement with parameters.

If you really have to use drop statement, you might need to use string concatenation on your SqlCommand. (Be aware about SQL Injection) You might need to take a look at the term called Dynamic SQL

Also use using statement to dispose your SqlConnection and SqlCommand like;

using(SqlConnection con = new SqlConnection(ConnectionString))
using(SqlCommand cmd = con.CreateCommand())
{
   cmd.CommandText = "drop table " + "SampleTable";
   con.Open()
   cmd.ExecuteNonQuery();
}

More Related Contents:

  1. When should “SqlDbType” and “size” be used when adding SqlCommand Parameters?
  2. Under what circumstances is an SqlConnection automatically enlisted in an ambient TransactionScope Transaction?
  3. Is SqlCommand.Dispose() required if associated SqlConnection will be disposed?
  4. Insert into C# with SQLCommand
  5. How to run multiple SQL commands in a single SQL connection?
  6. How do I translate a List into a SqlParameter for a Sql In statement? [duplicate]
  7. MetadataException: Unable to load the specified metadata resource
  8. ADO.NET |DataDirectory| where is this documented?
  9. Get the generated SQL statement from a SqlCommand object?
  10. Get connection string from App.config
  11. When using Trusted_Connection=true and SQL Server authentication, will this affect performance?
  12. MySqlCommand Command.Parameters.Add is obsolete
  13. How to create a DataTable in C# and how to add rows?
  14. Handling ExecuteScalar() when no results are returned
  15. ado.net Closing Connection when using “using” statement
  16. How to pass a table-value parameter
  17. How to keep single SQL Server connection instance open for multiple request in C#?
  18. Check if a record exists in the database
  19. DataTable equivalent in Java [duplicate]
  20. How to use SqlCommand to CREATE DATABASE with parameterized db name?
  21. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  22. How do I create a DataTable, then add rows to it?
  23. Add WHERE clauses to SQL dynamically / programmatically
  24. Unable to connect to localDB in VS2012 – “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”
  25. C# SQL insert command
  26. How to in-code supply the password to a connection string in an ADO.Net Entity Data Model
  27. .net connection pooling
  28. ADO.Net Entity Framework An entity object cannot be referenced by multiple instances of IEntityChangeTracker
  29. Passing array to a SQL Server Stored Procedure
  30. Why does var evaluate to System.Object in “foreach (var row in table.Rows)”?

Leave a Comment