VB.NET / SQL 2K - Best Coding Practice? [RESOLVED]

[tailz]

Hi all,

I've been a VB developer since as long as I can remember (im only 24 :P) and our company has decided to make the inevitable transition into .net

On and off I have spent the last year using ASP.net with SQL server in an effort to learn T-SQL but I've picked up on a lot of the .net features.

I'm now developing my first VB.net app using SQL server 2k as the data store. I've decided to put the mountain of dll's I've made from VB6 including all the db-handlers to one side and start a fresh.

Anyway, I've constructed a small database, and altho I have found myself replicating the structure in .net in the form of classes, everything looks structured until I want to essentially suck the data from SQL into the class. In asp.net, I've always dropped a SqlDataAdapter linked to an sqlcommand onto a component so all the db code is created for me and I've never had a prob with that.

Now that Im creating a class, I can't drop sqldataadapters on, and I point blank refuse to believe I have to generate all that evil code (that the component would normally do) manually. The reason Im using classes instead of components is I want to use inhertitance and as a component already inherits from System.ComponentModel.Component, I cant use it.

I know this is a larrrrge ramble (even for meh ) but any pro's or know-it-alls out there who can gimme some advice on what you think is best way on how to read/write from classes to sql-server using sqlcommand objects, would be much appreciated.

An ISBN number would be fine also lol :-)

Thanks

[Asgorath]

Hi
Just add a Imports System.Data.SqlClient on your class and you can declare them inside the class

Regards
Jorge

[mendhak]

Originally posted by tailz
In asp.net, I've always dropped a SqlDataAdapter linked to an sqlcommand onto a component so all the db code is created for me and I've never had a prob with that.

Bad practice. Why? It just is!

... I point blank refuse to believe I have to generate all that evil code (that the component would normally do) manually.

You will have to.

The reason the drag-drop method is discouraged is because you learn very little from it.

Use the SQLClient namespace's classes to implement the functionality in your class. It'll be painful at first, all that typing, but in the end, it's worth it.

[tailz]

Thanks for your replies.

Bad practice. Why? It just is!

I assume thats referring to the use of drag/drop. Like I said, my aim was to learn T-SQL so I used shortcuts in ASP.net and obviously haven't broke habit.

If its bad practise then thats fine, I kinda figured it would be. I take it then components in general are bad practise?

Can anyone please give me a simple example of connecting to an SQL server and using a sqldataadapter on a sqlcommand to get records into a dataset then?

Thanks

[<ABX]

add this to the top of the class (b4 the Option statements if they are present):

System.Data.SqlClient

Then just do something like this:

[vbcdoe]


Dim sqlcon as New SqlConnection("Sql Connection String")
Dim ds as New DataSet
Dim cmd as New SqlCommand("Cmd Here eg. Select * From Tbl ")

sqlCon.Open

Dim da as New DataAdapter(cmd, Sqlcon)

da.Fill(ds)

sqlcon.Close

[/Highlight]

[tailz]

Thanks

Thats the easy part; the issue is with the tablemappings and SqlParameter info - am I gonna have to create the mammoth amount of code just like the Component does?

Ive been looking around for examples and they are all using SQL strings which I have no intention of using, I'll be using SqlCommands

[mendhak]

Originally posted by tailz

If its bad practise then thats fine, I kinda figured it would be. I take it then components in general are bad practise?

No, it's just that when you use the drag drop method for data access, a large chunk of code is generated for you which

a) part of is useless
b) slackens you, and you don't understand what's going on behind the scenes.

[mendhak]

Originally posted by tailz

Thats the easy part; the issue is with the tablemappings and SqlParameter info - am I gonna have to create the mammoth amount of code just like the Component does?

Tablemappings are simply a matter of calling dataadapter.TableMappings.Add() method.

As for using SPs, it's pretty simple too. Example here.

I'd suggest you go through M$"s quickstart tuts.

http://samples.gotdotnet.com/quickst...soverview.aspx

[tailz]

ok thanks

have written a funky class now to handle all the setting up of connections/adapters/commands/parameters etc

all works fine and yes I will be straying from the drag/drop of components. can't believe how much code it produces compared to what you can get away with

thanks for all your help!