Please help ADO.NET issue

[nashak]

I have created a select statement and then used commandbuilder to
generate other statements. However, I have landed with an issue that
MSDN says cannot be handled. The table in my datasource created a
identity value when a new row is inserted and I cannot get the value of
this PKey back using the commandbuilder's insert statement. What are my
options other than using a stored procedure?

Can I create a sql insert statement at run time (dynamically). I.e. I
get a dataset with my select statement. Now without mentioning the
column names in my INSERT statement, can I create a INSERT statement
that get the column names (from some property of column collection etc.
etc.) and then pass parameters for the values of these fields. Also,
can I generate these parameters dynamically from the dataschema of my
dataset?

I hope I've explained my situation. Please let me know if that is not
the case.

Thanks very much

 

[Sahil Malik]

Yes you can do Dynamic SQL. In this case that'd work just fine. Dump
CommandBuilder in that case and resort to creating your own dynamic sql.

You can use the column names from the dataset/table, just make sure that
columnmappings are correct.

And the parameters and contents of the dynamic sql can be easily derived by
looking at the dataviewrowstate constants (modified/added/et.al.) on
individual rows. I don't think it lets you go to the depth of "such and such
column was updated" without actually comparing individual values.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[nashak]

Hello Sahil,

Thanks for your response.

One quick question - If I code manually the Insert statement (that
needs the identity value from datasource), can I still use the
cmdbuilder's methods for update and delete since I do not have any
problems in these cases. My dataset tables match exactly to one data
table in datasource and hence seems like a good fit.

Also, I used something like the following. (I have tried to provide as
much info but please let me know if something is not clear)

Sub InsertNewRow(TableName as ....)
Dim drBlankRow As DataRow

cmdSelectStudy.Connection = sqlconn1
cmdSelectStudy.CommandText = "select * from " & TableName
cmdSelectStudy.CommandType = CommandType.Text
daStudy.SelectCommand = Me.cmdSelectStudy
dtstudy = New DataTable(TableName)
dsEmptyStudy.Tables.Add(dtstudy)
daStudy.FillSchema(dsEmptyStudy, SchemaType.Source, TableName)
drBlankRow = dtstudy.NewRow
dtstudy.Rows.Add(drNewRow)
....calls another method to insert contents in the row.......
daStudy.InsertCommand.CommandText = *******************
daStudy.Update(dsEmptyStudy, TableName)
EndSub
My FillSchema fills my datatable with appropriate schema and column
names. Now how do I generate parameters dynamically for each column of
the datatable and how do I generate the INSERT statement dynamically.

I want to do something like:
Insert into (TableName) (drNewRow.ColumnName from some property)
values (drNewRow[0], drNewRow[1],..... etc. etc.)

Thanks

 

[Sahil Malik]

Nashak,

Yes you can use CommandBuilder generated commands alongwith your own
commands though I must say I'm not a fan of the efficiency of
CommandBuilder's commands.
Also, In your code - atleast for SqlServer, FillSchema is an expensive call.

Now how do I generate parameters dynamically for each column of
the datatable and how do I generate the INSERT statement dynamically.

You would have to iterate through your table, looking at various ColumnNames
and generate the Insert statement based upon that. How you access the
ColumnNames from a row is something like this --

drYourRow.Table.Columns[0].ColumnName.

HTH?

i

Hello Sahil,

Thanks for your response.

One quick question - If I code manually the Insert statement (that
needs the identity value from datasource), can I still use the
cmdbuilder's methods for update and delete since I do not have any
problems in these cases. My dataset tables match exactly to one data
table in datasource and hence seems like a good fit.

Also, I used something like the following. (I have tried to provide as
much info but please let me know if something is not clear)

Sub InsertNewRow(TableName as ....)
Dim drBlankRow As DataRow

cmdSelectStudy.Connection = sqlconn1
cmdSelectStudy.CommandText = "select * from " & TableName
cmdSelectStudy.CommandType = CommandType.Text
daStudy.SelectCommand = Me.cmdSelectStudy
dtstudy = New DataTable(TableName)
dsEmptyStudy.Tables.Add(dtstudy)
daStudy.FillSchema(dsEmptyStudy, SchemaType.Source, TableName)
drBlankRow = dtstudy.NewRow
dtstudy.Rows.Add(drNewRow)
...calls another method to insert contents in the row.......
daStudy.InsertCommand.CommandText = *******************
daStudy.Update(dsEmptyStudy, TableName)
EndSub
My FillSchema fills my datatable with appropriate schema and column
names. Now how do I generate parameters dynamically for each column of
the datatable and how do I generate the INSERT statement dynamically.

I want to do something like:
Insert into (TableName) (drNewRow.ColumnName from some property)
values (drNewRow[0], drNewRow[1],..... etc. etc.)

Thanks

 

[nashak]

Hello,

Thanks a lot for your help. I will try to do that.

Unfortunately, our requirement is not to have any sql statement that is
hard coded in our datalayer. We have to hard code SELECT since that is
needed by commandbuilder. Hence the reason to use FillSchema so that we
do not create columns for our dataset with the column names of table in
datasource.

What do you propose in this situation?

thanks

BTW - Are you the one who's written the book by Apress? I have it and
love it.

 

[Sahil Malik]

Yes Nashak, I am the guy who'se written the book for Apress. I am very very
honored that you liked something I worked extremely hard on. It took me 8
months 4-5 hours a day to churn that out - it's a lot of work. , so
needless to say I feel very good when I hear a few good words. I'm smiling
right now like the picture on the back of the book shows me LOL.

BTW, this newsgroup is the first place I am divulging this, I just began
work on another book for Apress - ADO.NET 2.0 - and that will be even better
(LOL). In the meanwhile there is a lot of .NET 2.0 information on my blog.

Incidentally, for your question ---

Look at the Microsoft DAAB - they use Parameter Caching to prevent multiple
hits to the database. You could use something similar. It is even possible
to set dependencies in Sql Server 2000 (well via triggers that touch a file
so you use file dependency instead) - to refresh the parameter/schema cache.
This will obviously have to be architected right but it's do-able

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik