Re: updating database views via tableadapter?

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Darren,

"Darren Sim" <darrens@xxxxxxxxxxxx> wrote in message news:uuaZVBDUHHA.4668@xxxxxxxxxxxxxxxxxxxxxxx
Hi Miha,

I have looked at codesmith recently - however it looked like it might take a bit of time to get to grips with using it and my project deadline is looming ever closer. How steep a learning curve is involved in using these tools?

CodeSmith might be very easy to use if you already have proper templates (a bunch of them come with CS). Otherwise you have to code a bit to create a template that in turns generates code for you.

As per ORM products they might suite your needs even better - they usually take care of different databases automatically (they support a certain set of different databases) - you just say which one you want to use. There is a lerning curve though but once you get grip of it you'll be much more productive. I strongly recommend you to take a look at LLBLGenPro - it would help you dealing with different databases and much more.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

The problem I have came up against is the application I am developing needs to run against different database types, and needs to be completed in a very short time (I am the only developer on this project at the moment). I made the decision to use dataset(s) to hold the working data and act as the logic layer, but I am by no means expert in this technology as yet. My prior experience is in Java and Delphi - datasets are fairly new to me..

Thanks for your suggestions - I will defineately look further into these tools for future development.


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message news:DB81D57A-07E4-44FA-8D9E-8B759BD84C21@xxxxxxxxxxxxxxxx
Darren,

I would recommend you a template based code generator (CodeSmith is an excellent choice) that does the boring code for you. Using proper template it can generate CRUD sql statements for every given table automatically.

Another step forward is to use an ORM product, that again does a lot of boring stuff for you. LLBLGenPro would be an excellent choice.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Darren Sim" <darrens@xxxxxxxxxxxx> wrote in message news:O9HVRdCUHHA.4384@xxxxxxxxxxxxxxxxxxxxxxx
Excellent, thank you Miha for the quick response - your help has been greatly appreciated.

All that is left now is to write some code :)

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message news:6F8F6FC4-1359-45E1-A798-75BDFF18C084@xxxxxxxxxxxxxxxx
Yes, you need to pull whatever data you need. So, if you are going to do updates or deletes you would need a key for each table affected.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Darren Sim" <darrens@xxxxxxxxxxxx> wrote in message news:%23EFXMUCUHHA.3996@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for your help :)

One final question - if writing my own update etc commands do I need to pull the respective tables key values through in order to do this?

Thanks again.
Darren Sim

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message news:49BCAF9A-765A-471C-9621-D866FA7A9A54@xxxxxxxxxxxxxxxx
Even if the view was updateable I would recommend writing sql statements :-)

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Garik" <garik_ma@do not spam.yahoo.com> wrote in message news:A78D8BC9-8FB1-41C3-9698-1E6E304FF87C@xxxxxxxxxxxxxxxx
Probably, your view is not updatable.

So, you must write Update/Delete/Insert commands manually, as mentioned Miha.

Garik

"Darren Sim" wrote:

There is a view on the database created using the sql in the select
statement on the code snippet. I was having problems using the table that
so I defined it in the select statement.

da.Update(review, "reviewquestions");

reviewquestions is the view on the database.


if I use the code
public void saveReview(ref DataSet review)

{

string select = "SELECT * from reviewquestions";




OleDbDataAdapter da = new OleDbDataAdapter(select, conn.ConnectionString);

OleDbCommandBuilder cb = new OleDbCommandBuilder(da);

da.Update(review, "reviewquestions");



}


I get the following error.
"Dynamic SQL generation is not supported against a SelectCommand that does
not return any base table information."


"Garik" <garik_ma@do not spam.yahoo.com> wrote in message
news:696BBA30-5987-4DD1-AA30-D49A36D004DD@xxxxxxxxxxxxxxxx
> Or create a view in the database and use that created view as a > table.
>
> -- > Regards,
> Garik Melkonyan
> MCP, MCAD, MCSD .NET
>
>
> "Darren Sim" wrote:
>
>> Sorry, I suppose more detail would help.
>>
>> The problem I am experiencing is that when I run the update >> message I get
>> an
>> error stating that
>>
>> "Dynamic SQL generation is not supported against multiple base >> tables"
>>
>> The code I am using for this is below, it works fine against a >> table
>> taken
>> directly from the source database, but encounters the above >> problem when
>> trying to update a view.
>>
>>
>>
>> public void saveReview(ref DataSet review)
>>
>> {
>>
>> string select = "SELECT s.done_id, a.areaname,
>> a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest,
>> q.need,s.score,s.evidence, d.catcutoff";
>>
>> select = select + " FROM tblcategory AS cat, tblarea AS a, >> tblquestion as
>> q,
>> tblsesscore as s, tbldonecut as d";
>>
>> select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = >> cat.cat_id
>> and
>> s.question_id = q.question_id and d.done_id = s.done_id and >> d.cat_id =
>> cat.cat_id";
>>
>>
>>
>>
>> OleDbDataAdapter da = new OleDbDataAdapter(select,
>> conn.ConnectionString);
>>
>> OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
>>
>> da.Update(review, "reviewquestions");
>>
>>
>>
>> }
>>
>>
>>
>> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
>> news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@xxxxxxxxxxxxxxxx
>> > What is the problem?
>> >
>> > -- >> > Miha Markic [MVP C#, INETA Country Leader for Slovenia]
>> > RightHand .NET consulting & development www.rthand.com
>> > Blog: http://cs.rthand.com/blogs/blog_with_righthand/
>> >
>>
>>
>>













.

Relevant Pages

  • Re: updating database views via tableadapter?
    ... Or create a view in the database and use that created view as a table. ... MCP, MCAD, MCSD .NET ... "Darren Sim" wrote: ... The problem I am experiencing is that when I run the update message I get an ...
    (microsoft.public.dotnet.framework.adonet)
  • updating database views via tableadapter?
    ... one of my tables has been built from a database view. ... update fine using the update method of a tableadapter - however there seems ... Darren Sim ...
    (microsoft.public.dotnet.framework.adonet)