locked
Create a view using SMO! RRS feed

  • Question

  • 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

  • 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