SqlCommandBuilder problem with views, when views are not in current database

SqlCommandBuilder problem with views, when views are not in current database

Post by David Brown » Fri, 28 Mar 2003 07:49:43




Quote:> SqlCommandBuilder seems to generate inappropriate sql insert/update/delete
> statements for views which do not belong to the SqlConnection's current
> database.

> The generated sql does not seem to correctly prefix the names of the
> underlying
> tables of the view with the appropriate database name.

> See attached demo project for more.

> Any workarounds ? (Apart from "don't use SqlCommandBuilder :)

How about this one:
Don't use views in another databse.
Instead create views in your own databse of the form:

create view foo as
    select * from otherdb.dbo.foo

This makes maintence and security simpler too.
Maintence is easier since you have just one place where the name of the
other databse is coded.
Security is easier since permissions can be set on your local view instead
of the view in the other database.  The user still needs to be able to
connect to the other database, but doesn't need any special permissions
there.

David

 
 
 

SqlCommandBuilder problem with views, when views are not in current database

Post by Sushil Chordi » Fri, 28 Mar 2003 11:39:19


Hi Stephen,

 I was not able to view your repro as it was blocked from Outlook. I did try
the following scenario and it worked just fine
//Open Connection -- not connecting to NorthWind DataBase
sqlconnection1.Open();
SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

//Sql - Create View foo as select * from NorthWind.dbo.Customers
sqlcommand1.CommandText = "create view foo as\nselect * from
NorthWind.dbo.Customers";
Int32 int321 = sqlcommand1.ExecuteNonQuery(); // -1

//Fill DataSet and initialize command builder
DataSet dataset1 = new DataSet();
SqlDataAdapter sqldataadapter1 = new SqlDataAdapter();
sqldataadapter1.SelectCommand = sqlcommand1;
SqlCommandBuilder sqlcommandbuilder1 = new
SqlCommandBuilder(sqldataadapter1);
Int32 int324 = sqldataadapter1.Fill(dataset1, "Table"); // 91
SqlCommand sqlcommand2 = sqlcommandbuilder1.GetInsertCommand();
String string1 = sqlcommand2.CommandText;

The above returned --->
 "INSERT INTO [NorthWind].[dbo].[Customers]([CustomerID], [CompanyName],
[ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode],

HTH,
Sushil..
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Quote:> SqlCommandBuilder seems to generate inappropriate sql insert/update/delete
> statements for views which do not belong to the SqlConnection's current
> database.

> The generated sql does not seem to correctly prefix the names of the
> underlying
> tables of the view with the appropriate database name.

> See attached demo project for more.

> Any workarounds ? (Apart from "don't use SqlCommandBuilder :)

> Thanks,
> Stephen


 
 
 

SqlCommandBuilder problem with views, when views are not in current database

Post by Stephen Ah » Fri, 28 Mar 2003 13:18:09


Sushil,

In your example, the problem *should* occur if "foo" was defined like this :
 "create view foo as\nselect * from Customers" (i.e. create the view inside
NorthWind), and SqlCommandBuilder's DataAdapter uses a select text
such as : "select * from NorthWind.dbo.Customers".

Thanks,
Stephen


> Hi Stephen,

>  I was not able to view your repro as it was blocked from Outlook. I did
try
> the following scenario and it worked just fine
> //Open Connection -- not connecting to NorthWind DataBase
> sqlconnection1.Open();
> SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

> //Sql - Create View foo as select * from NorthWind.dbo.Customers
> sqlcommand1.CommandText = "create view foo as\nselect * from
> NorthWind.dbo.Customers";
> Int32 int321 = sqlcommand1.ExecuteNonQuery(); // -1

> //Fill DataSet and initialize command builder
> DataSet dataset1 = new DataSet();
> SqlDataAdapter sqldataadapter1 = new SqlDataAdapter();
> sqldataadapter1.SelectCommand = sqlcommand1;
> SqlCommandBuilder sqlcommandbuilder1 = new
> SqlCommandBuilder(sqldataadapter1);
> Int32 int324 = sqldataadapter1.Fill(dataset1, "Table"); // 91
> SqlCommand sqlcommand2 = sqlcommandbuilder1.GetInsertCommand();
> String string1 = sqlcommand2.CommandText;

> The above returned --->
>  "INSERT INTO [NorthWind].[dbo].[Customers]([CustomerID], [CompanyName],
> [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode],


> HTH,
> Sushil..
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.


> > SqlCommandBuilder seems to generate inappropriate sql

insert/update/delete

- Show quoted text -

Quote:> > statements for views which do not belong to the SqlConnection's current
> > database.

> > The generated sql does not seem to correctly prefix the names of the
> > underlying
> > tables of the view with the appropriate database name.

> > See attached demo project for more.

> > Any workarounds ? (Apart from "don't use SqlCommandBuilder :)

> > Thanks,
> > Stephen

 
 
 

SqlCommandBuilder problem with views, when views are not in current database

Post by Stephen Ah » Fri, 28 Mar 2003 13:21:12


David,


message

Quote:> How about this one:
> Don't use views in another databse.
> Instead create views in your own databse of the form:

A good idea, but unfortunately not an option in our case (since there will
be many
databases getting created as required at runtime, not just one, so the view
has to
live inside each DB).

Thanks anyway.
Stephen

 
 
 

SqlCommandBuilder problem with views, when views are not in current database

Post by Sushil Chordi » Sat, 29 Mar 2003 01:33:21


Stephen,
I was able to repro your problem if I created a view in the Northwind
Database as
"create view foo as\nselect * from Customers"
With further investigation I found that creating a view as
"create view foo as\nselect * from NORTHWIND.DBO.Customers"
solved the problem and the Insert command from CommandBuilder was prefixed
properly

HTH,
Sushil.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

> Sushil,

> In your example, the problem *should* occur if "foo" was defined like this
:
>  "create view foo as\nselect * from Customers" (i.e. create the view
inside
> NorthWind), and SqlCommandBuilder's DataAdapter uses a select text
> such as : "select * from NorthWind.dbo.Customers".

> Thanks,
> Stephen



> > Hi Stephen,

> >  I was not able to view your repro as it was blocked from Outlook. I did
> try
> > the following scenario and it worked just fine
> > //Open Connection -- not connecting to NorthWind DataBase
> > sqlconnection1.Open();
> > SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

> > //Sql - Create View foo as select * from NorthWind.dbo.Customers
> > sqlcommand1.CommandText = "create view foo as\nselect * from
> > NorthWind.dbo.Customers";
> > Int32 int321 = sqlcommand1.ExecuteNonQuery(); // -1

> > //Fill DataSet and initialize command builder
> > DataSet dataset1 = new DataSet();
> > SqlDataAdapter sqldataadapter1 = new SqlDataAdapter();
> > sqldataadapter1.SelectCommand = sqlcommand1;
> > SqlCommandBuilder sqlcommandbuilder1 = new
> > SqlCommandBuilder(sqldataadapter1);
> > Int32 int324 = sqldataadapter1.Fill(dataset1, "Table"); // 91
> > SqlCommand sqlcommand2 = sqlcommandbuilder1.GetInsertCommand();
> > String string1 = sqlcommand2.CommandText;

> > The above returned --->
> >  "INSERT INTO [NorthWind].[dbo].[Customers]([CustomerID], [CompanyName],
> > [ContactName], [ContactTitle], [Address], [City], [Region],
[PostalCode],


> > HTH,
> > Sushil..
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.


> > > SqlCommandBuilder seems to generate inappropriate sql
> insert/update/delete
> > > statements for views which do not belong to the SqlConnection's
current

> > > database.

> > > The generated sql does not seem to correctly prefix the names of the
> > > underlying
> > > tables of the view with the appropriate database name.

> > > See attached demo project for more.

> > > Any workarounds ? (Apart from "don't use SqlCommandBuilder :)

> > > Thanks,
> > > Stephen