vb.net中如何调用存储过程
此页涉及到的存储过程的创建，在存储过程的创建中有源码
1、调用无参数存储过程
dim conn As New SqlConnection
dim FBasicDataRecord as new BasicDataRecord()
conn.ConnectionString = "Data Source=(local);Initial Catalog=LineManager;User ID=sa;Password=1"
conn.Open()
Dim cmd As SqlCommand
cmd = New SqlCommand("GetNewestData", conn) -------要调用存储过程名称
cmd.CommandType = CommandType.StoredProcedure -----------关键，要想调用存储过程，需将
commandtype类型设置为storedprocedure
Dim myReader As SqlDataReader
Try
myReader = cmd.ExecuteReader() -------------执行存储过程
If myReader.Read() Then
FBasicDataRecord.mySetDateTime = myReader.GetDateTime(0)
FBasicDataRecord.myHalfHourCost = myReader.GetDecimal(1)
FBasicDataRecord.myUnitTime = myReader.GetInt32(2)
FBasicDataRecord.myPrepareTime = myReader.GetInt32(3)
FBasicDataRecord.myLeastCurrency = myReader.GetDecimal(4)
FBasicDataRecord.myUserName = myReader.GetString(5)
Return True
Else
Return False
End If
Catch ex As Exception
Return ex.Message
Finally
conn.close()
End Try
2、调用有参数存储过程
Dim cmd As SqlCommand
dim conn As New SqlConnection
conn.ConnectionString = "Data Source=(local);Initial Catalog=LineManager;User ID=sa;Password=1"
conn.Open()
cmd = New SqlCommand("ProcCharge", conn) --------存储过程名称
cmd.CommandType = CommandType.StoredProcedure ----------改变类型
Dim a As New SqlParameter("@CardNo", SqlDbType.VarChar) ------------将存储过程中涉及的参数加入进来，并附
值。就像给函数的各个参数赋值一样
a.Value = myCharge.myCardNo
cmd.Parameters.Add(a)
Dim b As New SqlParameter("@ChargeDateTime", SqlDbType.DateTime)
b.Value = myCharge.myChargeDateTime
cmd.Parameters.Add(b)
Dim c As New SqlParameter("@Charge", SqlDbType.Int)
c.Value = myCharge.myCharge
cmd.Parameters.Add(c)
Dim d As New SqlParameter("@UserName", SqlDbType.VarChar)
d.Value = myCharge.myUserName
cmd.Parameters.Add(d)
Dim e As New SqlParameter("@LastBalance", SqlDbType.Float)
e.Value = myCharge.myLeastBalance
cmd.Parameters.Add(e)
Dim f As New SqlParameter("@CurrentBalance", SqlDbType.Decimal)
f.Value = myCharge.myCurrentBalance
cmd.Parameters.Add(f) ----------------参数块
Try
Return cmd.ExecuteNonQuery() > 0 ------------------执行存储过程。当存储过程涉及到的是插入删除或
更新操作时，用executenonquery（），而如果是查询，则用executereader（）
Catch ex As Exception
MsgBox(ex.Message)
Return False
Finally
conn.Close()
End Try
3、调用有返回值的存储过程
dim conn As New SqlConnection
conn.ConnectionString = "Data Source=(local);Initial Catalog=LineManager;User ID=sa;Password=1"
conn.Open()
Dim cmd As SqlCommand
cmd = New SqlCommand("procBalanceUserName", myConn.myConn) ---------存储过程名称
cmd.CommandType = CommandType.StoredProcedure ----------改变类型
cmd.Parameters.Add(New SqlParameter("@CardNo", SqlDbType.VarChar)) -------添加参数，此为输入参数
cmd.Parameters("@CardNo").Value = CardNo
cmd.Parameters.Add(New SqlParameter("@MachineNo", SqlDbType.VarChar))
cmd.Parameters("@MachineNo").Value = MachineNo
cmd.Parameters.Add(New SqlParameter("@Balance", SqlDbType.Float, 20)) ---------定义输出参数
cmd.Parameters("@Balance").Direction = ParameterDirection.Output -------输出参数用此标识
'输出参数必须有指定长度，否则不会成功。
cmd.Parameters.Add(New SqlParameter("@UserName", SqlDbType.VarChar, 10)) -------输出参数必须是名字，类
型，长度。不可省略。否则，会出错误。
cmd.Parameters("@UserName").Direction = ParameterDirection.Output
Try
cmd.ExecuteReader() ---------------执行存储过程
myBalance = cmd.Parameters("@Balance").Value.ToString() ----------将存储过程的返回值赋给定义的变量
myUserName = cmd.Parameters("@UserName").Value.ToString()
Catch ex As Exception
MsgBox(ex.Message)
Finally
Conn.Close()
End Try
总结一下，调用存储过程，首先，实例化sqlcommand，然后修改commandtype类型，如果参数，则给参数赋值或定义输出参数。然后执行。
