How to create a SQL-statement over more rows in code

[davy_g]

Hi,

I'm writing a program in which I select data from a table in SQL Server 2005, but where my SQL-statement becomes long.
Therefore, for a good overview, I want to split my SELECT-statement over more rows.

How can I split this into more rows?

sql = "SELECT bla bla .... FROM ... WHERE .... IN (SELECT ... FROM .... WHERE .... AND status = 10) bla bla"

When I set this in 1 row it becomes hard to read because I have to scroll all the time.

Is there a way to set this statement into more rows?

e.g.
SELECT ....
FROM ....
WHERE .....
....

 

[ss7thirty]

In VB.NET you can put underscores at the end of a line to mark that it will be extended to the next line:

      Dim str As String = "SELECT * FROM tbl_Thetablenamethatiswaytoolong WHERE " & _
        "this = that and somethingelse = anotherthing and onething = another " & _
        "ORDER BY whatever"

 

[JuggaloBrotha]

this is much less efficient than ss7thirty's suggestion, but technically you can do this:

Dim theSql As String = "SELECT * FROM tbl_Thetablenamethatiswaytoolong "
theSql &= "WHERE this = that and somethingelse = anotherthing and onething = another "
theSql &= "ORDER BY whatever;"

 

[cjard]

Hi,

I'm writing a program in which I select data from a table in SQL Server 2005, but where my SQL-statement becomes long.
Therefore, for a good overview, I want to split my SELECT-statement over more rows.

Actually, if you do your data access properly, you dont ever write an sql statement in code at all; you write it in a textbox in the visual designer and the IDE takes care of how it is represented in code... If you really cant get on with this method, then I strongly recommend that you put the SQL into the application settings instead; Having all your SQLs in one place will make them easier to find and alter in the future.

 

[davy_g]

Thanks a lot! Now I understand what this _ is, I saw it a lot on MSDN.
This makes my code much more readable.

@cjard, I am only a newbie so first I want to try this and when I'm more advanced I can try your method.
I'm learning VB .NET via just a few sites (have no book, anyone knows a good book?) and try to make a small app by myself.
So far I understand a lot of things (which I did not understand that good while trying to lean a bit of Java) and can do basic things like e.g. automatically create a specific string and insert it into the db, or write validationfunctions etc.

By the way, I heard that Murach's book for ADO.NET was the best book to learn how to use ADO.NET.
Is this a book which can be used by n00bs also? Or is it for more advanced programmers?

 

[cjard]

@cjard, I am only a newbie so first I want to try this and when I'm more advanced I can try your method.

Otherwise known as "running before you walk" I'd love to know where you got the idea that putting SQL statements and managing database conenctions directly in your button handler code is the "simple method" and using a visual designer to create properly encapsulated, solid, reliable, simple code is the "advanced method"

By the same argument, you ought to be writing all the code to lay your forms out by hand (Dimming every text box, setting its size, position, text, layout rules and adding it to whatever container), and calling the visual form designer "advanced" - I dont think you will have ever done this. If I ask you to put a button on a form, do you open the designer, drag a button on and youre done.. Or do you start "Dim x as New Button.. x.Text = "OK".. x.Size = blah.. myPanel.Controls.Add(x) ..?

I'm learning VB .NET via just a few sites (have no book, anyone knows a good book?) and try to make a small app by myself.

Learn from Microsoft; it's their language. Any other site is just someone else's interpretation

By the way, I heard that Murach's book for ADO.NET was the best book to learn how to use ADO.NET.
Is this a book which can be used by n00bs also? Or is it for more advanced programmers?

I dont know - I just read what Microsoft write; who else would know better?

 

[davy_g]

Hmm then I may have have understood you wrong.
I thought you meant that SQL-statement were automatically generated by a function that dynamically writes the statement based on it's input (seen it once on a site).
Adding these items, like the simple method, by hand ofcourse works but I know that I eventually want to go further so therefore I thought, why don't learn it the how I should write more professional apps?

I've ordered a book which should be delivered on friday so I have a basis from where to start. Just searching for some tutorials to learn a programming language is not the way to go.

 

[JuggaloBrotha]

here's how I handle Access database (this is still a work in progress)

 

[cjard]

Hmm then I may have have understood you wrong.
I thought you meant that SQL-statement were automatically generated by a function that dynamically writes the statement based on it's input (seen it once on a site).

Er.. Youve lost me. Here it is again: MS have a tool that is quite clever. You tell it what table you want to read and write and it looks at the table, looks up the primary key, uses some sensible logic to work out the statements that will download data from that table, and also update data to it* and it.. er.. writes them in a good, parameterized, logical, well encapsualted way. In seconds

*the logic is:

SELECT * FROM table
INSERT INTO table VALUES(<parameters>))
UPDATE table SET <fields = parameters> WHERE <primary key fields = original parameters>
DELETE FROM table WHERE <primary key fields = original parameters>

As you can see, PK is essential for the tool to work out the queries to update the rows

Adding these items, like the simple method, by hand ofcourse works but I know that I eventually want to go further so therefore I thought, why don't learn it the how I should write more professional apps?

Well, the grammar in that sentence got me a bit confused, but really the proper way to write OO (object oriented) software is to have all your stuff in relevant places. If you have a class that represents your Car, dont store the colour of your car in the Garage class.. Its an attribute of the car. Similarly, your button handler code is for doing a small amount of GUI related code. It is not for building connection strings, opening databases, reading select statement output etc. This is the concept of encapsulation.. a bit like tidying your house. Sure, throwing everything you own on the floor is an organizational style (preferred by teenagers it would appear) but eventually you realise that putting books on the book shelf, dvds in the dvd cabinet, newspapers in the paper basket and food in the fridge is a good idea

I've ordered a book which should be delivered on friday so I have a basis from where to start. Just searching for some tutorials to learn a programming language is not the way to go.

Quick tip.. If that book is advertised as .NET 2 and the data access sections mentions the words "DataAdapter" (it should mention "TableAdapter") or "CurrencyManager" (it should mention "BindingSource") or refers to datasets like this:

Dim ds as New DataSet
ds.Tables("MyTableName").Rows

Instead of this:

Dim myDS as New MyTypedDataSet
myDs.MyTableName.Rows


Then return it for a refund

 

[cjard]

here's how I handle Access database (this is still a work in progress)

Umm.. Why would you do that when youre on .NET 2, and you have at your disposal a tool that can generate code that is type-safe and more hack-safe than what is there, in seconds, using just a few mouse clicks?

For reasons I lay out in the PQ link in my signature, you should never, ever build sql queries using string concatenation (without parameters). There just arent any good reasons for doing so.. Of course, to generate dynamic sql you need to use string concat, but the clue is to string concat a parameterized statement..

 

[JuggaloBrotha]

Umm.. Why would you do that when youre on .NET 2, and you have at your disposal a tool that can generate code that is type-safe and more hack-safe than what is there, in seconds, using just a few mouse clicks?

Because I only got vs2005 a week ago and my work still uses vs2003 so I use the vs2003 methodology in both places

 

[cjard]

Because I only got vs2005 a week ago and my work still uses vs2003 so I use the vs2003 methodology in both places

Well.. you should definitely consider parameterizing your queries..

 

[JuggaloBrotha]

that's what i've been reading up on, too bad i cant do that in vs 2003

 

[cjard]

that's what i've been reading up on, too bad i cant do that in vs 2003

Should do; ADO 2.5 had parameterized queries.. Infact.. Theyve been around ever since I started java in '96, and probably a good long time before that; ever since the first RDBMS that supported stored procedures was available.

What part did you not succeed with in making a paramterized query in 2003?

 

[JuggaloBrotha]

Should do; ADO 2.5 had parameterized queries.. Infact.. Theyve been around ever since I started java in '96, and probably a good long time before that; ever since the first RDBMS that supported stored procedures was available.

What part did you not succeed with in making a paramterized query in 2003?

Using the DataAdapter to set the parameters.