VB.NET using inline sql

[Wendy Elizabeth]

I have the following questions about VB.NET interfacing with sql server 2000:
1. I have heard that VB.NET can run with inline SQL. Can you show me how to
use
inline sql to access a sql server 2000 database?
2. How do you setup stored procedures using Visual basic.net?
3. What is better to use and why, either inline sql or stored procedures?
4. Can you execute a visual basic.net program from a DTS package? If so,
show would you accomplish this task?

[Herfried K. Wagner [MVP]]

"Wendy Elizabeth" <We************@discussions.microsoft.com> schrieb:

I have the following questions about VB.NET interfacing with sql server
2000:
1. I have heard that VB.NET can run with inline SQL. Can you show me how
to
use
inline sql to access a sql server 2000 database?

If you are referring to the ability of mixing SQL with VB.NET code, this
cannot be done in current versions of the VB.NET programming language, and
will not be supported in the upcoming release of Visual Studio 2005.

The release of VS following on Whidbey (VS 2005) has the codename Orcas and
will include what's currently called LINQ (Language Integrated Query). It's
planned to support LINQ in both VB and C#. LINQ will allow to formulate SQL
queries directly inside the source code, regardless of the data the query is
working on. This means that you can query instances of .NET classes or
different DBMS which provide support for such queries.

The LINQ Project
<URL:http://msdn.microsoft.com/netframework/future/linq/>

Overview of Visual Basic 9.0
<URL:http://msdn.microsoft.com/library/en-us/dnvs05/html/vb9overview.asp>

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://classicvb.org/petition/>

[Cor Ligthert [MVP]]

Wendy,

Just open a form. Go to the toolbox and drag a sqldatadapter wizard on your
form and follow the steps, than you all kind of SQL code that is created if
yu open your designer part of a form.

With that wizard you can as well make stored procedures. I prefer at the
start to use dynamic SQL (sqlStrings). It is almost no problem to change
that when you are ready to a SP.

(It is easy to build everything by hand as well if you are more used to it).

For pure datatransaction SQL it is better to use stored procedures. However
if you have to make them complex to use them in a general way and they are
not often used, than dynamic SQL seems to be better, because the SP has to
be compiled every time at first use.

If are ready with the wizard and than rightclick on the icon
(sqldataadapter), than you can select to generate Dataset to create a
strongly typed dataset from that.

If you want to use an SQL string inside the code (by instance to create a
new datatable) than you can process it with the
system.data.sqlclient.sqlcommand class methods as
executequery (this gives nothing back) (except the rows affected)
executescalar(this gives back the first value)

or read a resultset with
system.data.sqlclient.sqldatareader

From a DTS package you can make a VB6 program, that you have to convert to
VBNet if you want to use that.

There is inbuild in the current and next VBNet version 'Expression syntax'
to process dataset/datatables. (This is not SQL, what a lot of people at the
start think and than have problems).

The build in expression language (or better a new one) is planned to be
extended for more general use in future, be aware it is not SQL syntax.
(However, it will as every expression syntax look like that. SQL was not
the first expression language).

I hope this helps,

Cor

"Wendy Elizabeth" <We************@discussions.microsoft.com> schreef in
bericht news:F5**********************************@microsof t.com...

I have the following questions about VB.NET interfacing with sql server
2000:
1. I have heard that VB.NET can run with inline SQL. Can you show me how
to
use
inline sql to access a sql server 2000 database?
2. How do you setup stored procedures using Visual basic.net?
3. What is better to use and why, either inline sql or stored procedures?
4. Can you execute a visual basic.net program from a DTS package? If so,
show would you accomplish this task?

[Wendy Elizabeth]

Cor Ligthert:

Thank you very much for your assistance! How do you setup dynamic SQL?

"Cor Ligthert [MVP]" wrote:

Wendy,

Just open a form. Go to the toolbox and drag a sqldatadapter wizard on your
form and follow the steps, than you all kind of SQL code that is created if
yu open your designer part of a form.

With that wizard you can as well make stored procedures. I prefer at the
start to use dynamic SQL (sqlStrings). It is almost no problem to change
that when you are ready to a SP.

(It is easy to build everything by hand as well if you are more used to it).

For pure datatransaction SQL it is better to use stored procedures. However
if you have to make them complex to use them in a general way and they are
not often used, than dynamic SQL seems to be better, because the SP has to
be compiled every time at first use.

If are ready with the wizard and than rightclick on the icon
(sqldataadapter), than you can select to generate Dataset to create a
strongly typed dataset from that.

If you want to use an SQL string inside the code (by instance to create a
new datatable) than you can process it with the
system.data.sqlclient.sqlcommand class methods as
executequery (this gives nothing back) (except the rows affected)
executescalar(this gives back the first value)

or read a resultset with
system.data.sqlclient.sqldatareader

From a DTS package you can make a VB6 program, that you have to convert to
VBNet if you want to use that.

There is inbuild in the current and next VBNet version 'Expression syntax'
to process dataset/datatables. (This is not SQL, what a lot of people at the
start think and than have problems).

The build in expression language (or better a new one) is planned to be
extended for more general use in future, be aware it is not SQL syntax.
(However, it will as every expression syntax look like that. SQL was not
the first expression language).

I hope this helps,

Cor

"Wendy Elizabeth" <We************@discussions.microsoft.com> schreef in
bericht news:F5**********************************@microsof t.com...

I have the following questions about VB.NET interfacing with sql server
2000:
1. I have heard that VB.NET can run with inline SQL. Can you show me how
to
use
inline sql to access a sql server 2000 database?
2. How do you setup stored procedures using Visual basic.net?
3. What is better to use and why, either inline sql or stored procedures?
4. Can you execute a visual basic.net program from a DTS package? If so,
show would you accomplish this task?

[rviray]

Wendy - if your definition of "inline SQL" is to execute a string like

Dim strSelect as string = "Select * from tbl" <-- can build the string
with whatever SQL Query you would like...I would not recommend
this...even though it is pretty flexible, but has alot of security
holes and you need to be "anal" when you code this way...

but if you need an example...here is some psuedo code:
Dim strInfo as string
dim conn as SqlConnection = New SqlConnection(<connection string>)
dim cmd as SqlCommand
strInfo = "<build query>"
conn.open()
cmd = New SqlCommand(strInfo, conn)
<execute>
...

Personally, I always use Stored Procedure, it is more secure and easier
to support (IMHO)...Here is some psuedo code:

dim conn as SqlConnection = New SqlConnection(<connection string>)
Dim cmd As SqlCommand = New SqlCommand(<store proc name>, conn)
cmd.CommandType = CommandType.StoredProcedure
Dim prm1 As SqlParameter = cmd.Parameters.Add("@storeprocvariablename",
SqlDbType.VarChar, 15)
prm1.Value = "<parameter value that you want to pass>"
conn.Open()
Dim myReader As SqlDataReader = cmd.ExecuteReader()
<loop through myReader for values>
As for Dymamic Sql, it is basically a stored procedure where you build
a string/varchar variable and execute the string...I tend to stay away
from this for this reason....Dynamic SQL (correct me if I am wrong)
will always take the same amount of time to execute because the query
is not machine compiled (or whatever SQL does to optimize queries).
Versus, Store procs...Stored procs get optimized (in most cases) and
you will notice that a query will take less longer to execute on
subsequent calls to the store procs...

anyway, hope this helps.

Ralph
--
rviray
------------------------------------------------------------------------
rviray's Profile: http://www.msusenet.com/member.php?userid=4211
View this thread: http://www.msusenet.com/t-1871082249