Inside SqlCommandBuilder

[Girish]

CREATE TABLE [dbo].[person] (
[personid] [int] IDENTITY (1, 1) NOT NULL ,
[callsignid] [int] NOT NULL ,
[password] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[genderid] [int] NOT NULL ,
[ageid] [int] NOT NULL ,
[roleid] [int] NOT NULL ,
[creationdate] [datetime] NOT NULL ,
[emailaddress] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uszipcode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intcity] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryid] [int] NULL
) ON [PRIMARY]


[creationdate] [datetime] NOT NULL HAS A DEFAULT function on it "getdate()"


Heres the problem:
Im using the following code to create me a insert statement automatically
for inserting new rows into the database
//_dataCommand is instance of SqlDataAdapter
// _dataTable is instance of DataTable
SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
_dataCommand.InsertCommand = bldr.GetInsertCommand();
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();


Im ONLY setting the following fields for an insert
[callsignid] [password] [name] [genderid] [ageid] [roleid]. The
[creationdate] has a default on it, so I dont really care about setting it.

So heres the Insert statement generated in the profiler.
exec sp_executesql N'INSERT INTO person( callsignid , password , name ,
genderid , ageid , roleid , emailaddress , uszipcode , intcity , countryid )

Im extremely surprised. I was under the impression that the command builder
would try and insert into ALL columns. So where is the value for
creationdate?

Reference (Inside CommandBuilder):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.asp


So now I move onto this bit of code to insert new rows but return me the
AUTONUMBER as well:

SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
SqlCommand cmdInsert = new
SqlCommand(bldr.GetInsertCommand().CommandText, connection.DBConnection);
cmdInsert.CommandText += "; " + getPrimaryKeySQLString();

SqlParameter[] aParams = new
SqlParameter[bldr.GetInsertCommand().Parameters.Count];
bldr.GetInsertCommand().Parameters.CopyTo(aParams, 0);
bldr.GetInsertCommand().Parameters.Clear();

for(int i=0 ; i < aParams.Length; i++)
{
cmdInsert.Parameters.Add(aParams);
}

_dataCommand.InsertCommand = cmdInsert;
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();

BUT, I get an error stating that I cant insert NULL values for creationdate
column. I looked at the profiler and now it seems that the insert statement
generated wants to insert a value (null) for the creationdate. Why exactly?
I want the database to set the default. How can I get around this problem?

Im totally clueless!

Any help would be appreciated.
Thanks,
Girish

 

[William \(Bill\) Vaughn]

See my article on the CommandBuilder
(http://www.betav.com/msdn_magazine.htm)
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

CREATE TABLE [dbo].[person] (
[personid] [int] IDENTITY (1, 1) NOT NULL ,
[callsignid] [int] NOT NULL ,
[password] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[genderid] [int] NOT NULL ,
[ageid] [int] NOT NULL ,
[roleid] [int] NOT NULL ,
[creationdate] [datetime] NOT NULL ,
[emailaddress] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uszipcode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intcity] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryid] [int] NULL
) ON [PRIMARY]


[creationdate] [datetime] NOT NULL HAS A DEFAULT function on it "getdate()"


Heres the problem:
Im using the following code to create me a insert statement automatically
for inserting new rows into the database
//_dataCommand is instance of SqlDataAdapter
// _dataTable is instance of DataTable
SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
_dataCommand.InsertCommand = bldr.GetInsertCommand();
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();


Im ONLY setting the following fields for an insert
[callsignid] [password] [name] [genderid] [ageid] [roleid]. The
[creationdate] has a default on it, so I dont really care about setting it.

So heres the Insert statement generated in the profiler.
exec sp_executesql N'INSERT INTO person( callsignid , password , name ,
genderid , ageid , roleid , emailaddress , uszipcode , intcity , countryid )

Im extremely surprised. I was under the impression that the command builder
would try and insert into ALL columns. So where is the value for
creationdate?

Reference (Inside CommandBuilder):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.asp


So now I move onto this bit of code to insert new rows but return me the
AUTONUMBER as well:

SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
SqlCommand cmdInsert = new
SqlCommand(bldr.GetInsertCommand().CommandText, connection.DBConnection);
cmdInsert.CommandText += "; " + getPrimaryKeySQLString();

SqlParameter[] aParams = new
SqlParameter[bldr.GetInsertCommand().Parameters.Count];
bldr.GetInsertCommand().Parameters.CopyTo(aParams, 0);
bldr.GetInsertCommand().Parameters.Clear();

for(int i=0 ; i < aParams.Length; i++)
{
cmdInsert.Parameters.Add(aParams);
}

_dataCommand.InsertCommand = cmdInsert;
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();

BUT, I get an error stating that I cant insert NULL values for creationdate
column. I looked at the profiler and now it seems that the insert statement
generated wants to insert a value (null) for the creationdate. Why exactly?
I want the database to set the default. How can I get around this problem?

Im totally clueless!

Any help would be appreciated.
Thanks,
Girish

 

[Girish]

Its after reading your article that I have these questions Bill.

I do an add new row on the DataTable object. I *do not* set a value for the
field creationdate.

So when I get the sql from the sqlcommandbuilder when setting the
insertcommand on the dataadapter, I get a sql statement that is inserting a
NULL value into the field creationdate.

But

In the second case where the Update method runs and the sqlcommandbuilder
builds another sql statement - the insert statement is *not* inserting a
null for the creation date. I can see this in the sql profiler.

What makes it build a different sql statement at Update time?

Thanks,
Girish

See my article on the CommandBuilder
(http://www.betav.com/msdn_magazine.htm)
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

CREATE TABLE [dbo].[person] (
[personid] [int] IDENTITY (1, 1) NOT NULL ,
[callsignid] [int] NOT NULL ,
[password] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT

NULL

,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[genderid] [int] NOT NULL ,
[ageid] [int] NOT NULL ,
[roleid] [int] NOT NULL ,
[creationdate] [datetime] NOT NULL ,
[emailaddress] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

,
[uszipcode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intcity] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryid] [int] NULL
) ON [PRIMARY]


[creationdate] [datetime] NOT NULL HAS A DEFAULT function on it "getdate()"


Heres the problem:
Im using the following code to create me a insert statement automatically
for inserting new rows into the database
//_dataCommand is instance of SqlDataAdapter
// _dataTable is instance of DataTable
SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
_dataCommand.InsertCommand = bldr.GetInsertCommand();
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();


Im ONLY setting the following fields for an insert
[callsignid] [password] [name] [genderid] [ageid] [roleid]. The
[creationdate] has a default on it, so I dont really care about setting it.

So heres the Insert statement generated in the profiler.
exec sp_executesql N'INSERT INTO person( callsignid , password , name ,
genderid , ageid , roleid , emailaddress , uszipcode , intcity , countryid )

Im extremely surprised. I was under the impression that the command builder
would try and insert into ALL columns. So where is the value for
creationdate?

Reference (Inside CommandBuilder):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.asp

So now I move onto this bit of code to insert new rows but return me the
AUTONUMBER as well:

SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
SqlCommand cmdInsert = new
SqlCommand(bldr.GetInsertCommand().CommandText, connection.DBConnection);
cmdInsert.CommandText += "; " + getPrimaryKeySQLString();

SqlParameter[] aParams = new
SqlParameter[bldr.GetInsertCommand().Parameters.Count];
bldr.GetInsertCommand().Parameters.CopyTo(aParams, 0);
bldr.GetInsertCommand().Parameters.Clear();

for(int i=0 ; i < aParams.Length; i++)
{
cmdInsert.Parameters.Add(aParams);
}

_dataCommand.InsertCommand = cmdInsert;
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();

BUT, I get an error stating that I cant insert NULL values for creationdate
column. I looked at the profiler and now it seems that the insert statement
generated wants to insert a value (null) for the creationdate. Why exactly?
I want the database to set the default. How can I get around this problem?

Im totally clueless!

Any help would be appreciated.
Thanks,
Girish

 

[William \(Bill\) Vaughn]

Your code and only your code determines what is used when the Update method
runs. The CommandBuilder constructs the UpdateCommand, InsertCommand and
DeleteCommand (hopefully) once and only once as you are building your
DataAdapter. These commands are stored behind the scenes and once you setup
a CommandBuilder, the Update method uses these pre-built command--it does
not create more. The columns selected are hard-coded in the generated
commands--they do not change at runtime (as with ADOc).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Its after reading your article that I have these questions Bill.

I do an add new row on the DataTable object. I *do not* set a value for the
field creationdate.

So when I get the sql from the sqlcommandbuilder when setting the
insertcommand on the dataadapter, I get a sql statement that is inserting a
NULL value into the field creationdate.

But

In the second case where the Update method runs and the sqlcommandbuilder
builds another sql statement - the insert statement is *not* inserting a
null for the creation date. I can see this in the sql profiler.

What makes it build a different sql statement at Update time?

Thanks,
Girish

See my article on the CommandBuilder
(http://www.betav.com/msdn_magazine.htm)
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

CREATE TABLE [dbo].[person] (
[personid] [int] IDENTITY (1, 1) NOT NULL ,
[callsignid] [int] NOT NULL ,
[password] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT

NULL

,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[genderid] [int] NOT NULL ,
[ageid] [int] NOT NULL ,
[roleid] [int] NOT NULL ,
[creationdate] [datetime] NOT NULL ,
[emailaddress] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

,
[uszipcode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intcity] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryid] [int] NULL
) ON [PRIMARY]


[creationdate] [datetime] NOT NULL HAS A DEFAULT function on it "getdate()"


Heres the problem:
Im using the following code to create me a insert statement automatically
for inserting new rows into the database
//_dataCommand is instance of SqlDataAdapter
// _dataTable is instance of DataTable
SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
_dataCommand.InsertCommand = bldr.GetInsertCommand();
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();


Im ONLY setting the following fields for an insert
[callsignid] [password] [name] [genderid] [ageid] [roleid]. The
[creationdate] has a default on it, so I dont really care about

setting
it.

So heres the Insert statement generated in the profiler.
exec sp_executesql N'INSERT INTO person( callsignid , password , name ,
genderid , ageid , roleid , emailaddress , uszipcode , intcity , countryid )

Im extremely surprised. I was under the impression that the command builder
would try and insert into ALL columns. So where is the value for
creationdate?

Reference (Inside CommandBuilder):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.asp

So now I move onto this bit of code to insert new rows but return me the
AUTONUMBER as well:

SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
SqlCommand cmdInsert = new
SqlCommand(bldr.GetInsertCommand().CommandText, connection.DBConnection);
cmdInsert.CommandText += "; " + getPrimaryKeySQLString();

SqlParameter[] aParams = new
SqlParameter[bldr.GetInsertCommand().Parameters.Count];
bldr.GetInsertCommand().Parameters.CopyTo(aParams, 0);
bldr.GetInsertCommand().Parameters.Clear();

for(int i=0 ; i < aParams.Length; i++)
{
cmdInsert.Parameters.Add(aParams);
}

_dataCommand.InsertCommand = cmdInsert;
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();

BUT, I get an error stating that I cant insert NULL values for creationdate
column. I looked at the profiler and now it seems that the insert statement
generated wants to insert a value (null) for the creationdate. Why exactly?
I want the database to set the default. How can I get around this problem?

Im totally clueless!

Any help would be appreciated.
Thanks,
Girish

 

[Kevin Yu [MSFT]]

Hi Girish,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you think the SQL statement generated
by command builder is wrong. If there is any misunderstanding, please feel
free to let me know.

I agree with Bill's idea, the generated command depends on the
SqlDataAdapter you used when you're creating the commandbuilder object.
Could you please paste your command text for SelectCommand here? With the
select command, can we see what is generated for InsertCommand more easily.
Thanks for your cooperation.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Girish]

Oh no, I dont think the command being generated is wrong. Im just trying to
understand what its exactly doing.

Heres a simple script:
----------------------
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter();

//just a wrapper around the SqlConnection instance
ConnectionProvider conn = new ConnectionProvider();
conn.OpenConnection();

//fetch the meta data before an insert
String query = "Select * from person where personid=-1";
sda.SelectCommand = new SqlCommand(query, conn.DBConnection);
sda.Fill(dt);

DataRow dr = dt.NewRow();
dr["callsignid"] = 1;
dr["password"] = "test";
dr["name"] = "girish";
dr["genderid"] = 1;
dr["ageid"] = 1;
dr["roleid"] = 1;

//add new row here
dt.Rows.Add(dr);

SqlCommandBuilder scb = new SqlCommandBuilder(sda);
sda.InsertCommand = scb.GetInsertCommand();
Console.Write(sda.InsertCommand.CommandText);

sda.Update(dt);
Console.Write(sda.InsertCommand.CommandText);


Heres the Schema of the table:
-------------------------------
CREATE TABLE [dbo].[person] (
[personid] [int] IDENTITY (1, 1) NOT NULL ,
[callsignid] [int] NOT NULL ,
[password] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[genderid] [int] NOT NULL ,
[ageid] [int] NOT NULL ,
[roleid] [int] NOT NULL ,
[creationdate] [datetime] NOT NULL ,
[emailaddress] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uszipcode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intcity] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryid] [int] NULL
) ON [PRIMARY]

[creationdate] [datetime] NOT NULL HAS A DEFAULT function on it "getdate()"

Heres the output from the Console.Write
-----------------------------------------

1) INSERT INTO
person( callsignid , password , name , genderid , ageid , roleid ,
creationdate , emailaddress , educationlevelid , uszipcode , city ,
countryid )
VALUES ( @p1 , @p2 , @p3 , @p4 , @p5 , @p6 , @p7 , @p8 , @p9 , @p10 ,
@p11 , @p12 )

2) INSERT INTO
person( callsignid , password , name , genderid , ageid , roleid ,
emailaddress , educationlevelid , uszipcode , city , countryid )
VALUES ( @p1 , @p2 , @p3 , @p4 , @p5 , @p6 , @p7 , @p8 , @p9 , @p10 ,
@p11 )


Heres the problem
-------------------

1) As you can see, the creation date is not set in the second sql statement
generated. Why?
2) educationlevelid, uszipcode, city and countryid are all set in the second
sql statement - but I have not set them when I created the new row. Why are
they there in the sql statement?
3) The problem I have is that I really need a sql statement to be generated
that does not force me to insert a value for the creation date - because - I
have a default set on it. The solution that I read in one of Bill's posts
for returning the PK of an insert does not take into account columns that
are "not nullable and have defaults". Im just wondering if there is a
solution.

Thanks,
Girish

 

[Kevin Yu [MSFT]]

Hi Girish,

Here are the answers to your questions:

1. The first output of insert command was generated from the SELECT
statement "Select * from person where personid=-1" you have entered. When
you call DataAdapter.Update method, the CommandBuilder object will first
communicate with the server to refresh the InsertCommand. When it founds
that the creationdata doesn't allows nulls and it has a default value, it
removes creationdate from the statement to avoid throwing an exception.
That's why we see two statements different from each other.

2. The CommandBuilder generates insert command according to your Select
Command. It doesn't depends on which column has been assigned a value to.
If you doesn't assign a value to a certain column, a Null value will be
used when generating the insert command.

3. I think currently, we have achieved our goal with the creationdate field
"not nullable and have defaults". Since the insert command doesn't contain
the creationdate, that field will be filled by the default value. We
needn't assign value to the column in C# code.

Does this answer your question? If there is any thing unclear, please feel
free to let me know.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Girish]

Thanks for your reply Kevin. And thanks for continuously monitoring this
thread.

I still have a doubt unfortunately.

You said below in 1) that the SqlCommandBuilder checks the database if a
default is set in the column or not. This "checking" happens only on the
Update method of the DataAdapter and not the GetInsertCommand() method of
the SqlCommandBulilder. Am I right?

If this is the case, then look at the following code:


//_dataCommand is instance of SqlDataAdapter
// _dataTable is instance of DataTable
//getPrimaryKeySQLString() is an abstract method that needs to be
implemented by anyone using this base class.
// This method is used for fetching the text such as "SELECT
SCOPE_IDENTITY() as personid" etc

SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
SqlCommand cmdInsert = new
SqlCommand(bldr.GetInsertCommand().CommandText, connection.DBConnection);
cmdInsert.CommandText += "; " + getPrimaryKeySQLString();

SqlParameter[] aParams = new
SqlParameter[bldr.GetInsertCommand().Parameters.Count];
bldr.GetInsertCommand().Parameters.CopyTo(aParams, 0);
bldr.GetInsertCommand().Parameters.Clear();

for(int i=0 ; i < aParams.Length; i++)
{
cmdInsert.Parameters.Add(aParams);
}

_dataCommand.InsertCommand = cmdInsert;
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);



With this bit of code, it does not check if there is a default set for the
date field. Is there a way around this problem?

Thanks,
Girish

 

[Kevin Yu [MSFT]]

Hi Girish,

What you assume is right. Yes, the return value of GetInsertCommand will be
refreshed before updating. However, in the code you have provided, the
update command is contructed by yourself, not by the CommandBuilder. So the
update command will not be refreshed.

In this case, it is not recommended to use CommandBuilder, since you need
to use customized InsertCommand. I think you can use text command or stored
procedure instead, as CommandBuilder is designed for general SQL commands.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Girish]

Thanks Kevin. Issue has been resolved. You have been a big help.

Girish