Re: [sqlite] BUG REPORT using refreshschema on SQLiteCommandBuilder

[ITS Smoynihan]

Joe 

Here is an MS link that describes using/manipulating the command texts , but
I had misinterpreted the instructions .

http://msdn.microsoft.com/en-us/library/tf579hcz.aspx

so , you are correct , no bug , but the behaviour with SqlCommandBuilder is
a bit different which does not null the InsertCommand text ( maybe it
should)

many thanks for prompt responses


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: 20 February 2012 04:30
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] BUG REPORT using refreshschema on SQLiteCommandBuilder


ITS Smoynihan wrote:
> 
> The MS note in
>
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuil
> der.aspx  would not make sense otherwise, if it is going to null out the
> texts 
> 

That page of documentation is specific to the provider for SQL Server.

None of the DbCommandBuilder derived classes appear, at least publically, to
store any text to be used for later reconstructing the commands disposed by
the RefreshSchema method.  

The RefreshSchema method is implemented by the DbCommandBuilder class itself
(part of the .NET Framework), not System.Data.SQLite and the
DbCommandBuilder
class does not null out any texts because there are none stored to null out.

What problem you are reporting here?  Where does any documentation on the
DbCommandBuilder class or the SQLiteCommandBuilder class directly mention
storing command text (via the public interface)?

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG REPORT using refreshschema on SQLiteCommandBuilder

[Joe Mistachkin]

ITS Smoynihan wrote:
> 
> The MS note in
>
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuil
> der.aspx  would not make sense otherwise, if it is going to null out the
> texts 
> 

That page of documentation is specific to the provider for SQL Server.

None of the DbCommandBuilder derived classes appear, at least publically, to
store any text to be used for later reconstructing the commands disposed by
the RefreshSchema method.  

The RefreshSchema method is implemented by the DbCommandBuilder class itself
(part of the .NET Framework), not System.Data.SQLite and the
DbCommandBuilder
class does not null out any texts because there are none stored to null out.

What problem you are reporting here?  Where does any documentation on the
DbCommandBuilder class or the SQLiteCommandBuilder class directly mention
storing command text (via the public interface)?

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG REPORT using refreshschema on SQLiteCommandBuilder

[Joe Mistachkin]

According to MSDN, calling RefreshSchema is supposed to remove all (dispose)
all DbCommand objects associated with the builder.  If the SQL Server
provider
is doing something else it is not following the specification.

http://msdn.microsoft.com/en-us/library/system.data.common.dbcommandbuilder.
refreshschema.aspx 

In your example you use:

SQLiteCommand cmd1 = builder.GetInsertCommand();

And then you call:

builder.RefreshSchema(); 

Which disposes of the command you previously grabbed.  This is by design,
per
the MSDN specification.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG REPORT using refreshschema on SQLiteCommandBuilder

[ITS Smoynihan]

Bug :  RefreshSchema sets SQLiteCommandBuilder command
texts to NULL

 

Effect :   SQLiteCommandBuilder builds the complex command
texts , if u need to append second qry , it fails when the tableAdapter
Update is called later.

 

Hi guys , sent this also last week as non-subscribed member. Appreciate if
you can take a look , it means that you cannot append to the command texts
built by SqlCommandBuilder. The example of getting back the value of the DB
auto-inc after an insert would be very common , and is causing a lot of
problems in a project I am working on

 

Many thanks

 

 

Using SqlClient.SqlCommandBuilder

 

See MS class
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuil
der.aspx

 

If you modify the command text , you must call RefreshSchema
  , to refresh the underlying T-Sql commands

To generate INSERT, UPDATE, or DELETE statements, the SqlCommandBuilder uses
the SelectCommand
  property to retrieve a required set of metadata
automatically. If you change the SelectCommand
  after the metadata has been retrieved, such as after
the first update, you should call the RefreshSchema
  method to update the metadata.

In this example , used to retrieve the value of an auto-inc after an insert
has been done on e.g. a grid , note the modified command text

private void button20_Click_1(object sender, EventArgs e)

{

string qry = @" SELECT DepartmentID,name,groupname,modifieddate
from 

humanResources.department";

SqlDataAdapter mAdaptDept = new SqlDataAdapter(qry, gConnSqlDb);

DataTable mTableDept = new DataTable();

SqlCommandBuilder builder = new SqlCommandBuilder(mAdaptDept);

SqlCommand cmd1 = builder.GetInsertCommand();

 

//  debug to get value of cmd1.CommandText

//  INSERT INTO [humanResources].[department] ([name],
[groupname], 

//  [modifieddate]) VALUES (@p1, @p2, @p3)

 

 

cmd1.CommandText += "; SELECT SCOPE_IDENTITY() AS
DepartmentID;";

 

 //  MUST call this to refresh the T-SQL generated commands

builder.RefreshSchema(); 

// debug to get value of cmd1.CommandText

 

 

//  debug to get value of cmd1.CommandText

//  INSERT INTO [humanResources].[department] ([name],
[groupname], 

//  [modifieddate]) VALUES (@p1, @p2, @p3); SELECT
SCOPE_IDENTITY() AS DepartmentID;

 

mAdaptDept.InsertCommand = cmd1;

 

//   mAdaptDept.Update(mTableDept);

//   when called later , the new auto-inc is retrieved and inserted to the
datatable

 

 

}

Using Sqlite. SQLiteCommandBuilder , similar snippet

  private void button1_Click(object sender, EventArgs e)

{

 

string qry = @" select
employeeId,lastName,FirstName,Title,birthdate from employees";

SQLiteDataAdapter mAdaptDept = new SQLiteDataAdapter(qry,
gConnTest);

DataTable mTableDept = new DataTable();

SQLiteCommandBuilder builder = new
SQLiteCommandBuilder(mAdaptDept);

SQLiteCommand cmd1 = builder.GetInsertCommand();

 

// debug to get value of cmd1.CommandText

 

//  INSERT INTO [Employees] ([LastName], [FirstName],
[Title], [BirthDate]) 

//  VALUES (@param1, @param2, @param3, @param4)

 

cmd1.CommandText += "; SELECT last_insert_rowid() AS
employeeId;";

 

//  MUST call this to refresh the T-SQL generated commands

builder.RefreshSchema(); 

// debug to get value of cmd1.CommandText

 

// has gone to NULL

 

mAdaptDept.InsertCommand = cmd1;  

 

//   mAdaptDept.Update(mTableDept);

//   when called later , crash !! . no command text !!

 

}

 

 

Sean J Moynihan

Information Technology ServiceS

086-816-8801

042-9336059

www.itss.ie

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users