locked
Create a view using SMO! RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello all,
    what im trying to do is to create a SQL View using SMO library. Here's an example code that im using:

     Server srv = Session["Connection"] as Server;
        Microsoft.SqlServer.Management.Smo.View NewView = new Microsoft.SqlServer.Management.Smo.View(srv.Databases[cmbDatabases.SelectedItem.Text],txtViewName.Text);
        NewView.TextMode = false;

        foreach (ListItem item in cblFields.Items)
        {
            if (item.Selected)
            {
                Column SelCol = srv.Databases[cmbDatabases.SelectedItem.Text].Tables[cmbTable.SelectedItem.Text].Columns[item.Text];
                NewView.Columns.Add(SelCol);
            }
        }

        try
        {
            NewView.Create();
            lblErrorInfo.ForeColor = System.Drawing.Color.Green;
            lblErrorInfo.Text = "View \'"+txtViewName.Text+"\' has successfuly been created!";
        }
        catch (Exception ex)
        {
            lblErrorInfo.ForeColor = System.Drawing.Color.Red;
            lblErrorInfo.Text = ex.Message + " Error Info: "+ex.InnerException.ToString();
        }
    But when i try to run this code it throws an exception saying:' Parent property of object [] does not match the collection's parent to which it is added.'


    Does anyone have any idea on how to get over this error?

    Thanks in advance,
    Alexander
    Sunday, October 4, 2009 3:38 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    The error was raised because you were trying to add a table's column to your new view.  but the owner of the column is the table. In this case, the table will protect its columns being highjacked by the view. To add a column to the view, you need to create a new column object like the following:
    Column SelCol = new Column(NewView, <column_name (item.Text)>);
    NewView.Columns.Add(SelCol);

    Please note that this is just creating a view header here. For example, if the new column's name is "ID", after you execute the above code, the script generated are just as following:
    CREATE VIEW [dbo].[v_getTestFT] (\t[ID])\r\n AS \r\n

    This is not enough to create a view. You have to define the view's text body with script additionally.  More references can be found here:
    http://msdn.microsoft.com/en-us/library/ms162235.aspx

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, October 6, 2009 4:19 AM