Custom Select/Insert/Delete Command for generated table adapters

[Xster]

Hi,
I've used vs2005's data tools to generate a xsd file which loads data from an SQL server to datatables which are bound to some scheduling component controls. I'm using this designer tool because I don't know how to bind controls to tables to reflect changes.

But the problem is the auto generated table adapters have all their insert, select and delete commands in a design.vb file which resets all the time and need some changes to work with my sql tables. I tried some hack to add functions since the design.vb declares the class as partial but I can't override the default behavior to add something in the SQL when a row is added in the data table. Can I customize the auto generated table adapter?

 

[cjard]

Hi,
I've used vs2005's data tools to generate a xsd file which loads data from an SQL server to datatables which are bound to some scheduling component controls. I'm using this designer tool because I don't know how to bind controls to tables to reflect changes.

Good good.. I dont know either, and I refuse to go and find out because I regard it as menial crap that I shouldnt have to do. The IDE makes a very good job of it. If you really want to see the code behind the scenes, click Show All FIles on Solution Explorer toolbar, and look at the MyDataSet.Designer.vb
The binding code is stored in Form1.Designer.vb

See.. no voodoo! ALl forms are laid out in code just like everything else, its just that the autogenerated files are hidden

But the problem is the auto generated table adapters have all their insert, select and delete commands in a design.vb file which resets all the time and need some changes to work with my sql tables.

Yep

I tried some hack to add functions since the design.vb declares the class as partial but I can't override the default behavior to add something in the SQL when a row is added in the data table. Can I customize the auto generated table adapter?

Yes. Open the XSD in the DataSet designer, CLick on the TableAdapter itsel;f, and look at the Properties window. You see S/I/U/D command sections and you can expand them to customise the SQL. If youre going to get too clever, use stored procedures instead

 

[Xster]

Oh sick, nice feature. Another well hidden feature found. But how do I change values assigned to parameters besides looking at its declaration.

 

[Xster]

or is it possible to catch events from the bindingsource or datatable and perhaps from the eventargs to know which row was added and call my own function for database insert?

 

[cjard]

Oh sick, nice feature. Another well hidden feature found. But how do I change values assigned to parameters besides looking at its declaration.

Huh? Values assigned to parameters come from the datatable columns. Change the column value?

Your datatable is:

COlA, ColB, ColC
Apple, Green, Sweet
Olive, Green, Bitter
Fish, Silver, Salty

Your statements are:
INSERT INTO table COLA, :COLB, :COLC)

Suppose the Apple row is new, IDE will effectively call:
INSERT INTO table (Apple, Green, Sweet)

Suppose Olive row is updated:
UPDATE table SET COLB = Green, ColC = Bitter WHERE ColA = Olive

Suppose Fish row is marked for deletion:
DELETE FROM table WHERE ColA = Fish

 

[cjard]

or is it possible to catch events from the bindingsource or datatable and perhaps from the eventargs to know which row was added and call my own function for database insert?

Why would you want to do that? What incredibly hard problem are you inventing in your mind that youre seeking a solution for?

 

[Xster]

Why would you want to do that? What incredibly hard problem are you inventing in your mind that youre seeking a solution for?

Lol, I'm planning the invasion of the world.
Na, it's because we have this custom function generate the key in the database, so select, update and delete works but insert command must take parameter from a function rather than the datatable.
So Insert (code,a,b,c) values (@code,@a,@b,@c), @code does not come from the datatable

 

[cjard]

Lol, I'm planning the invasion of the world.
Na, it's because we have this custom function generate the key in the database, so select, update and delete works but insert command must take parameter from a function rather than the datatable.
So Insert (code,a,b,c) values (@code,@a,@b,@c), @code does not come from the datatable

Doesnt matter.. @code will be updated by the database to a new value, which will then be copied back into the table

Set your datatable column:
AutoIncrement = True
AutoIncSeed = -1
AutoIncStep = -1

Every new row you make will be:
-1
-2
-3
...

When these go off the to the DB, the DB calcs new values from SELECT MAX or SELECT @@IDENTITY or something like that, yeah? Well that value will be placed back in the @code variable, and the client will see the change and update the datatable with it when its done

It does it for all variables, and if you had e.g. a trigger that LowerCased the new string values, then as soon as you save "THIS" it will become "this" etc

Basicaly youre trying to fix a problem that doesnt exist.. Get on with something useful