O
O
Ogoun Er2015-02-26 21:34:07
SQL
Ogoun Er, 2015-02-26 21:34:07

How to use variables (SqlParameter) in query with ALTER TABLE from .NET application?

I am writing a utility in C#, the utility should be able to automatically create new fields in SQL Server tables.
In this case, the NOT NULL fields must be filled with default values ​​(or given values).
Those. I do something like this:

// формирую команду
//..........................
StringBuilder alter = new StringBuilder("ALTER TABLE [" + _tableName + "] ADD ");
foreach (var c in _createColumns)
    {
        alter.Append("[" + c.Name + "] " + c.DbType);
//.........................
// если встречаю not null поле
if (c.AllowNull==false)
    alter.Append(" NOT NULL DEFAULT(@" + c.Name + ")");
//.........................

// И при выполнении запроса формирую параметры по умолчанию
public SqlParameter[] GetDefaultAlterParameters()
{
        return _createColumns.Select(c => new SqlParameter(c.Name, GetDefaultValue(c.DotNetType))).ToArray();
}

But, the problem is that you cannot use variables in such commands, I get an exception like:
Variables are not allowed in the ALTER TABLE statement.
Is there a way to disable or bypass this restriction? I found an option through the creation of temporary functions , but it turns out ugly.
Or is there a better way to form a solid command line, escaping values ​​as needed?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question