CODEWITHSUNDEEP
c#sql
user3747994
user3747994

Reputation: 129

How can we pass table and schema name as parametrized sql query from c#

I want to pass parameterized schema, table name with some other parameterized values.

db.Database.ExecuteSqlCommand(@"Insert INTO [dbo].[UserTypes][@Schema].[@Table] ([Name],[Description]) VALUES(@NAME, @Description)",
                                         new SqlParameter("@Schema", "dbo"),
                                         new SqlParameter("@Table", "UserTypes"),
                                         new SqlParameter("NAME", "AA"),
                                         new SqlParameter("Description", "Test"));

Upvotes: 1

Views: 529

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062745

You cannot parameterize the object identity, schema, column, etc; you'd need to use string concatenation for those parts - just parameterizeing the values. For example:

string schema = ..., table = ..., name = ..., description = ...;
// TODO: check schema and table are in an expected/allow-list, to prevent SQL injection
db.Database.ExecuteSqlCommand($@"Insert INTO [{schema}].[{table}] ([Name],[Description]) VALUES(@NAME, @Description)",
         new SqlParameter("NAME", name),
         new SqlParameter("Description", description));

Upvotes: 2

Related Questions