none
Creating a database view using SQLCommand RRS feed

  • Question

  • Is it possible to Create a database indexed view with parameters using System.Data.SqlClient.SqlCommand class? If not then what is the recommended way to achieve this?

    I am trying to create a  view for a query like this,  "SELECT CustomerName, Status FROM customer WHERE status = 'Active'".

    The value Active is provided by the user therefore I want to create a SqlParameter for it and use it with the SqlCommand. Here is the code sample

     

    string strSQL = "Create View MyView WITH SCHEMABINDING AS SELECT CustomerName, Status FROM dbo.Customer WHERE status = @P1";

    using (SqlCommand cmd = new SqlCommand(strSQL, _conn))

    {

          cmd.Parameters.AddWithValue("@P1", "Active");
          cmd.ExecuteNonQuery();
    }

     

    CODE THROWS EXCEPTION: System.Data.SqlClient.SqlException : Incorrect syntax near the keyword 'View'. Incorrect syntax near the keyword 'SELECT'.

     

    If I don't use the parameter and concatenate the value Active in the sql string then it works, but I don't want to manually concatenate the user specified value, because then I will have to handle special characters like single quotes, Sql injection etc.

    Please advise

    Thanks

     

     


     

    Monday, March 22, 2010 7:13 PM

Answers

  • SQL Server does not provide a way to create a view with a parameter.  Fundamentally, the view is the text for the query. The database engine requires you to supply all of this text as a single string.

    If you are worried that the user this is running at will perform a SQL injection, I'm wondering why that user is even allowed to do a CREATE VIEW in the first place... 

    SQL Server provides some alternatives.  However, even with these choices you will not be able to embed the parameter value at the time you run the data defininition (DDL) statement.  The parameter value is supplied at run time.  Considering however that CREATE VIEW is probably not the right design, and one of these might be better anyways.  Since you haven't described the reasoning behind why you needed a view, I can't say for sure.

    1.  Consider writing a stored procedure.

    2.  Consider writing a table-valued UDF (user defined function).

     

    Monday, March 22, 2010 10:44 PM

All replies

  • SQL Server does not provide a way to create a view with a parameter.  Fundamentally, the view is the text for the query. The database engine requires you to supply all of this text as a single string.

    If you are worried that the user this is running at will perform a SQL injection, I'm wondering why that user is even allowed to do a CREATE VIEW in the first place... 

    SQL Server provides some alternatives.  However, even with these choices you will not be able to embed the parameter value at the time you run the data defininition (DDL) statement.  The parameter value is supplied at run time.  Considering however that CREATE VIEW is probably not the right design, and one of these might be better anyways.  Since you haven't described the reasoning behind why you needed a view, I can't say for sure.

    1.  Consider writing a stored procedure.

    2.  Consider writing a table-valued UDF (user defined function).

     

    Monday, March 22, 2010 10:44 PM
  • Thanks for your response, you confirmed my suspicion about view creation.

    The application I work on allows users to configure the meta data for via an administrator utility, it is like a OR-mapping tool. Currently we allow our users to define meta-data in terms of business objects and relationships which maps to the tables in the database. To provide additional reporting capabilities we are adding support for Indexed View therefore I need to create the DDL statement from the administrator utility. I  was hoping to use the parameters since we publish this functionality as an API.

    Thanks again for your response, I will see if we can use a stored procedure instead.

     

    Monday, March 22, 2010 11:34 PM