Shulou(Shulou.com)06/02 Report--

VB.NET calls the stored procedure of SQL Server. In view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

VB.NET programming language for the operation of the database, we 51CTO has also introduced a lot before. For example, the realization method of VB.NET database compression, the specific operation skills of VB.NET operating MySql database and so on.

The definition of data link is omitted, and myConn is the link object. ProcName is the stored procedure name.

No return value is returned when 1.VB.NET calls the SQL Server stored procedure

Private Function SqlProc1 (ByVal ProcName As String) As Boolean 'defines the data link part omitted, myConn is the link object ProcName is the stored procedure name Dim myCommand As New SqlClient.SqlCommand (ProcName, myConn) With myCommand .CommandType = CommandType.StoredProcedure .Parameters.Add ("@ CodeType", SqlDbType.VarChar, 20). Value = "grade code" Try .ExecuteNonQuery () Return True Catch ex As Exception Return False End Try End Function

2.VB.NET calls the SQL Server stored procedure to return a normal value

Private Function SqlProc1 (ByVal ProcName As String) As String

'define the data link part omitted, and myConn is the link object

Dim myCommand As New SqlClient.SqlCommand (ProcName, myConn)

With myCommand

.CommandType = CommandType.StoredProcedure

.Parameters. Add ("@ CodeType", SqlDbType.VarChar, 20). Value = "grade code"

.Parameters. Add ("@ NewCode", SqlDbType.VarChar, 20)

Direction = ParameterDirection.Output

Try

.ExecuteNonQuery ()

Return .parameters (1) .Value ()

Catch ex As Exception

Return "No Encoding Generation"

End Try

End Function

3.VB.NET calls the SQL Server stored procedure to return the dataset

'VB.NET code

Private Function SqlProc2 (ByVal ProcName As String

ByVal Param1 As String) As DataSet

'define the command object and use the save procedure

Dim myCommand As New SqlClient.SqlCommand

MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.CommandText = ProcName

MyCommand.Connection = myConn

'define a data adapter and set the parameters

Dim myDapter As New SqlClient.SqlDataAdapter (myCommand)

MyDapter.SelectCommand.Parameters.Add

("@ name", SqlDbType.VarChar, 20) .Value = Param1

'define a dataset object and populate the dataset

Dim myDataSet As New DataSet

Try

MyDapter.Fill (myDataSet)

Catch ex As Exception

End Try

Return myDataSet

End Function

Stored procedure code

Create Proc Test @ name varchar (20) As

Select * From EC_Grade where cGradeName=@name

GO

* if you modify part of the contents of the stored procedure, it can be used as a query.

CREATE Proc Test

@ name varchar =''

It should be noted here that 200 is the length of the query condition, which can be determined according to the actual situation.

However, it is not recommended for long query conditions.

As

Declare @ sql1 varchar 8000

If @ name''

Select @ sql1='Select * From EC_Grade where'+ @ name

Else

Select @ sql1='Select * From EC_Grade'

Exec (@ sql1)

GO

This is the answer to the question about the stored procedure of VB.NET calling SQL Server. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.