DataAdapter And Stored Proc

[shalini Noronha]

Hi

I have a DataAdapter to which i pass a Stored Proc and a
Connection

How do i pass parameters to my Stored Procs

Kindly Help

 

[Ibrahim]

Hello Shalni

There are a few ways to do it, I'll show you a function that returns an ADO
Command object with parameters populated for a stored procedure:

Public Function InsertPersonCMD(ByVal MyData As DataTable) As
SqlClient.SqlCommand

Dim MyCMD As New SqlClient.SqlCommand

Dim MyParam As SqlClient.SqlParameter

Try

With MyCMD

..CommandText = "InsertPerson"

..CommandType = CommandType.StoredProcedure

'=================================================

'Parameter NameFirstName

'=================================================

MyParam = .CreateParameter()

MyParam.ParameterName = "@Firstname"

MyParam.Direction = ParameterDirection.Input

MyParam.DbType = DbType.String

MyParam.Size = 64

MyParam.Value = MyData.Rows(0).Item("FirstName")

MyCMD.Parameters.Add(MyParam)

'=================================================

'Parameter NameMiddleName

'=================================================

MyParam = .CreateParameter()

MyParam.ParameterName = "@MiddleName"

MyParam.Direction = ParameterDirection.Input

MyParam.DbType = DbType.String

MyParam.Size = 64

MyParam.Value = MyData.Rows(0).Item("MiddleName")

MyCMD.Parameters.Add(MyParam)

'=================================================

'Parameter NameLastName

'=================================================

MyParam = .CreateParameter()

MyParam.ParameterName = "@LastName"

MyParam.Direction = ParameterDirection.Input

MyParam.DbType = DbType.String

MyParam.Size = 64

MyParam.Value = MyData.Rows(0).Item("LastName")

MyCMD.Parameters.Add(MyParam)

'=================================================

'Parameter NameEmailAddress

'=================================================

MyParam = .CreateParameter()

MyParam.ParameterName = "@EmailAddress"

MyParam.Direction = ParameterDirection.Input

MyParam.DbType = DbType.String

MyParam.Size = 64

MyParam.Value = MyData.Rows(0).Item("EmailAddress")

MyCMD.Parameters.Add(MyParam)

'=================================================

'Parameter NameBusinessID

'=================================================

MyParam = .CreateParameter()

MyParam.ParameterName = "@PersonID"

MyParam.Direction = ParameterDirection.Output

MyParam.DbType = DbType.Int32

MyCMD.Parameters.Add(MyParam)

End With

Return MyCMD

Catch erx As Exception

'=========================

'simple throw. add more

'complex error handling

'as required

'=========================

Throw erx

Finally

'???

End Try

End Function

and here is a function that uses it:

Public Overloads Function InsertPerson(ByVal Person As clsPerson) As
clsPerson

Dim objCommand As SqlClient.SqlCommand

Dim MyTable As DataTable

Dim NewPersonID As Integer

'===========================================================================

'after geting a command object populated with parameters the procedure

'executes the command. If there where no errors from the Insert procedure

'then a new person object populated with the Inserted person data is
returned

'===========================================================================

Try

MyTable = Person.DataSource.Tables(0)

objCommand = Me._objDataCMDs.InsertPersonCMD(MyTable)

objCommand.Connection = Me._objConnect

If Me._objConnect.State = ConnectionState.Closed Then

Me._objConnect.Open()

End If

objCommand.ExecuteNonQuery()

'========================================================

'if a valid NewPersonID (greater than zero) is returned

'then we return a person object populated with the data

'for that new person that was inserted

'========================================================

NewPersonID = objCommand.Parameters("@PersonID").Value

If NewPersonID > 0 Then

Return Me.GetPerson(NewPersonID)

Else

Return Nothing

End If

Catch ex As Exception

Throw ex

Finally

objCommand.Dispose()

objCommand = Nothing

End Try

End Function



Hope this helps.
Ibrahim Malluf
http://www.malluf.com

 

[William Ryan]

Assuming there SQL Server procs...


If the params in your proc are "@Param1", "@Param2"

use the command object. A few ways to do this...

cmd.Parameters.Clear (just to be safe, unless you're sure
you haven't added them already)

cmd.Parameters.Add("@Param1", someValue)
cmd.Parameters.Add("@Param2", nextValue)

However, this isn't very efficient, you can define the
type.... cmd.Parameters.Add("@Param1", SqlDBType.Int)
cmd.Parameters.Add("@Param2", SqlDBType.Varchar)

cmd.Paramaters("@Param1").Value = someValue

or you could use the numeric index which is preferable
for performance but not for clariy.

Hope this helps.

Good Luck,

Bill



Cordially,

W.G. Ryan
(e-mail address removed)
www.knowdotnet.com

 

[Michael Lang]

When you define the CRUD commands you specify what type of commands they
are. Either Text or StoredProcedure.

SqlCommand cmdUpdate = new SqlCommand()
cmdUpdate.CommandType = CommandType.StoredProcedure
cmdUpdate.CommandText = "sprocNameHere";
.... now create parameter objects on the command for all the input and output
parameters of the sproc.