Create view using SMO

Hi All

i m building an application in which i have to create database view using SMO.

The problem i m facing is that when i use the View.Columns.Add function to add a particular column to the view i am getting an error .

"Parent property of object [Name] does not match the collection's parent to which it is added."

The following is the piece of code i have written

Server server = new Server(Environment.MachineName);

Database db = server.Databases["AdventureWorks"];

Microsoft.SqlServer.Management.Smo.Table tab1 = db.Tables["ProductModel", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab2 = db.Tables["Product", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab3 = db.Tables["ProductModelProductDescriptionCulture", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab4 = db.Tables["ProductDescription", "Production"];

Column c1=tab1.Columns["Name"];

Column c2 = tab2.Columns["ProductID"];

Column c3 = tab2.Columns["Name"];

Column c4 = tab3.Columns["CultureID"];

Column c5 = tab4.Columns["Description"];

Microsoft.SqlServer.Management.Smo.View dataview = new Microsoft.SqlServer.Management.Smo.View(db, "Trial", "Production");

dataview.TextMode = false;

dataview.Columns.Add(c1);

dataview.Columns.Add(c2);

dataview.Columns.Add(c3);

dataview.Columns.Add(c4);

dataview.Columns.Add(c5);

dataview.Create();

The other way to create view i.e using the Textheader and the TextBody properties is working fine but this one giving some error

Can anyone help me with this

Thanls in advance.

MItesh

Mitesh, the error you're getting is because those columns belong to the parent tables, and you can't just assign them to the view. The TextHeader and TextBody properties are the correct way to build a view, just as they're the correct properties to use to build a stored procedure in SMO.|||

Hey Allen

thanks for the reply

the problems is that i m building an administrative application in which i have to provide a GUI to the user, through which the user can select some columns of a table (using checkboxes) just as they do it in Management Studio.

Now the point here is that it becomes dificult for a naive user to write the create view query for View creation

so the GUI is a must

Can u suggest some other way i can use the View class

Also if i cant use the View.Columns.Add method wats the point in providing such methods

Do help me out with this

Thanks again

Keep posting

|||

Mitesh,

The View object inherits from the TableViewBase object, so the view has the same objects and methods as the table has. That doesn't mean they are always appropriate given the context. You can evaluate the columns in an existing view using the Columns objects but I haven't found a way to define the source table or tables, or the where clause containing your limiting conditions, using SMO objects.

You should be able to build a SQL string, however, from the selected columns and limiting conditions presented in your GUI, and send that string to the TextBody property to build the view, as you require.