Executing a stored procedure...

  • Thread starter Thread starter BobRoyAce
  • Start date Start date
B

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
..
..
..
 
Thanks Jim...

I downloaded and installed the Enterprise Library, but now what do I
reference to be able to use SqlHelper?
 
BobRoyAce said:
Thanks Jim...

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

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
 
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).
Click to expand...

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
 
Back
Top