Executing a stored procedure...

[BobRoyAce]

I have stored procedures with parameters in a SQL Server 2005 DB and
want to execute them (primarily to update, delete, and insert records
to tables). Could someone please provide simple code to do this. I
found sample code using something called SqlHelper, but SqlHelper
doesn't seem to be available in VS 2005 (Visual Basic) unless I'm
missing something. I am creating the parameters as follows:

Dim SQLParams(12) As SqlClient.SqlParameter

Try
SQLParams(0) = New System.Data.SqlClient.SqlParameter
With SQLParams(0)
.ParameterName = "@pkUserIDOriginal"
.DbType = DbType.Int32
.Direction = ParameterDirection.Input
.Value = _@pkUserIDOriginal
End With
..
..
..

 

[Jim Hughes]

SQL Helper is part of the Enterprise Library, available for download at
http://www.microsoft.com/downloads/...70-406B-4F2A-B723-97BA84AE80B5&displaylang=en

A quick Google search reveals many examples:

http://www.google.com/search?hl=en&q=ado.net+"stored+procedure"+vb&btnG=Google+Search

 

[BobRoyAce]

Thanks Jim...

I downloaded and installed the Enterprise Library, but now what do I
reference to be able to use SqlHelper?

 

[Jim Hughes]

Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/entlib.asp
for links that will help get you started.

 

[Matt Noonan]

Thanks Jim...

I downloaded and installed the Enterprise Library, but now what do I
reference to be able to use SqlHelper?

Actually, SqlHelper as a class has been completely replaced by the Data
Access Application Block (DAAB). The Enterprise Library has it's own factory
classes to allow you to connect to SQL Server and Oracle, without directly
referencing the SQL classes.

Here are some examples of how to use it:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp

 

[Marc Scheuner]

I have stored procedures with parameters in a SQL Server 2005 DB and

want to execute them (primarily to update, delete, and insert records
to tables).

This is in C# - should be easily converted to VB.NET, too:

// necessary using statements
using System.Data;
using System.Data.SqlClient;

// set up your connection string - see www.connectionstrings.com
// for many many samples

string dbConnectionString =
"server=<yourserver>;database=<yourdatabase>;integrated
security=SSPI";

// create a connection to your database
SqlConnection sqlcon = new SqlConnection(dbConnectionString);

// create your command for calling the sproc
SqlCommand cmd = new SqlCommand("your_sproc_name", sqlcon);
cmd.CommandType = CommandType.StoredProc;

// create your parametesr

cmd.Parameters.Add("@param1", SqlDbType.int).Value = 15;
cmd.Parameters.Add("@param2", SqlDbType.string).Value = "some text";
cmd.Parameters.Add("@param3", SqlDbType.string).Value = "more text";

// now open your connection and execute the stored proc

sqlcon.Open();
cmd.ExecuteNonQuery();
sqlcon.Close();


That's it Of course, you'd need to add some error checking /
handling to make this robust - we'll leave that as an exercise to the
reader

Marc