Help - New to VB.NET - selecting data

Help - New to VB.NET - selecting data

Post by Bradley, Pete » Fri, 16 Jun 2006 19:01:58



Couple of comments.

Firstly, ADO.NET uses DataSets and not RecordSets.  Have you looked at
any of the ADO.NET tutorials and documentation?  For example, you might
try:

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/ADOPlusOvervie
w.aspx

and:

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

Secondly, I hope you don't really construct SQL queries in the way you
set out in your example.  It's incredibly insecure to do stuff like
that.  It's also really bad practice to have hard-coded values in your
code.

One excellent reason for using stored procedures is that you can pass in
your user input as parameters.  Stored procedure parameters are
guaranteed to be treated as strings by the database, and not as commands
(unless you do something silly like creating another string in your sp
that includes the parameter by concatentation).

So, in short, if you adopt good coding practices, you have to store all
your query strings somewhere in order not to hard-code them into your
application.  If you're going to do that, you'd might as well store them
in SPs and get all the security advantages of SPs as well.

Just my 2c.  YMMV.

Peter

-----Original Message-----


Sent: 15 June 2006 09:23
To: DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web
Services,.NET Remoting
Subject: [DotNetDevelopment] Help - New to VB.NET - selecting data

Hi Guys,

I have developed several complicated applications in VB 6.0 and I am
struggling with some of the concepts in VB.NET. If anyone could point
me in the right direction I would really appreciate it. I have an SQL
server and an application with about a million SELECT statements. The
books I have bought on VB.NET all seem to want to use stored procedures
- this kind of feels wrong as many of the SELECT statements are unique
and it would amazingly time consuming creating stored procedures for
every SELECT, UPDATE, DELETE, etc.

The kind of thing I'm trying to replicate is something as simeple as
this in VB 6.0....

         Dim prasSYSusers As New ADODB.Recordset

         gstrSQL = "SELECT code, active FROM PhoenixUsers WHERE
password = '" & Trim(Password) & "'"
         prasSYSusers.Open gstrSQL, gconSqlDB, adOpenStatic,
adLockOptimistic
         If prasSYSusers.RecordCount = 1 Then
             prasSYSusers!Active = 1
             prasSYSusers.Update
         End If
         prasSYSusers.Close

gconSqlDB is a global variable holding the connection strin and the
vairable Password is collected from the form. Security isn't an issue
here - I just want to understand the basic concepts. As you may have
gathered I'm really struggling to get off the ground with VB.NET

Thanks!

 
 
 

Help - New to VB.NET - selecting data

Post by RedDo » Fri, 16 Jun 2006 19:15:36


Hi Peter,

Thanks for your response. Firstly I think I said security was not an
issue.

But are you really suggesting that for each of my (over 600!) unique
SELECT statments in my application it is best practice to create a
stored procedure for each? And that's before I even begin with UPDATE,
INSERT and DELETE. I meen that is what everyone is saying so I'm not
doubting you - it just seem so counter-intuitive - and literally months
of work for what was very simple under VB 6.0

 
 
 

Help - New to VB.NET - selecting data

Post by Bradley, Pete » Fri, 16 Jun 2006 19:51:04


Security is always an issue.  Believe me on this.

As to your second paragraph, then yes, I am.  What else are you going to
do?  Hard code them all?

Finally, with a little bit of thought and planning you can probably cut
down the number of SPs you need by using appropriate parameters.

Presumably you have a properly worked out model (in UML or something)
for a piece of software as large as this, with a properly designed Data
Access Layer.  Check out your model and see how you can design common
parameterized stored procedures that your DAL can access.

If you don't do that, you'll have to store your queries in config files
or something.  Hard-coding is guaranteed to come back and bite you in
the end, because it means your data access is distributed widely
throughout your code making maintenance a nightmare - even for the
programmer who wrote the code, never mind the poor devil who comes along
afterwards.

HTH

Peter

-----Original Message-----


Sent: 15 June 2006 11:16
To: DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web
Services,.NET Remoting
Subject: [DotNetDevelopment] Re: Help - New to VB.NET - selecting data

Hi Peter,

Thanks for your response. Firstly I think I said security was not an
issue.

But are you really suggesting that for each of my (over 600!) unique
SELECT statments in my application it is best practice to create a
stored procedure for each? And that's before I even begin with UPDATE,
INSERT and DELETE. I meen that is what everyone is saying so I'm not
doubting you - it just seem so counter-intuitive - and literally months
of work for what was very simple under VB 6.0

 
 
 

Help - New to VB.NET - selecting data

Post by Cerebru » Sat, 17 Jun 2006 14:42:30


Hi RedDog,

Well, if you insist that security isn't an issue here, since you're
trying to grasp the concepts, I wouldn't push it.

I guess you could the 600 SQL statements as is, in your VB.NET code. If
you post the VB.NET code you have written and you're having problems
with, maybe we can help...

 
 
 

Help - New to VB.NET - selecting data

Post by Bradley, Pete » Sat, 17 Jun 2006 16:18:52


Sorry, I don't agree here at all.  A program with 600 SQL statements in
it is not something someone is doing just to grasp concepts.  It's a
major project that should be done properly.

But that's just me.

Peter

-----Original Message-----


Sent: 16 June 2006 06:43
To: DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web
Services,.NET Remoting
Subject: [DotNetDevelopment] Re: Help - New to VB.NET - selecting data

Hi RedDog,

Well, if you insist that security isn't an issue here, since you're
trying to grasp the concepts, I wouldn't push it.

I guess you could the 600 SQL statements as is, in your VB.NET code. If
you post the VB.NET code you have written and you're having problems
with, maybe we can help...