存储过程实现 [英] Stored Procedure Implementation
问题描述
存储过程名称:Find_Emp
命令/查询=
SELECT * FROM EMP0000 INNER JOIN EMP0001 ON EMP0001.EMP_NO = EMP0000.EMP_NO WHERE EMP0000.EMP_NO =@empno
在我使用Visual Studio 2010的GUI中,我有一个ListView,其中列出了员工列表..
我想在ListView的SelectedIndexChanged事件中调用该过程。
我的问题是不知道如何在visual studio 2010中调用此程序..请帮助吗?
我觉得这很复杂..我正在使用Visual Studio 2010(vb.net)..你的代码在c#中?我是对的吗?
顺便说一下这里的一些细节:
模块:< br $> b $ b
公共 Sub Recordset_Connection( ByVal nMode As 整数)
如果 nMode = 0 然后
RstFileRead = 没什么
RstFileRead = 新 ADODB .Recordset
RstFileRead.Open(strFileRead,Conn,ADODB.CursorTypeEnum.adOpenForwardOnly,ADODB.LockTypeEnum.adLockReadOnly)
ElseIf nMode = 1 然后
RstFileUpdate = 没什么
RstFileUpdate = 新 ADODB.Recordset
RstFileUpdate.Open(strFileUpdate,Conn,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockOptimistic)
结束 如果
结束 Sub
在我的表格中,我创建了一个如下所示的子程序:
私人 Sub LoadListEmp()
Dim LvItem 作为 新 ListViewItem
ListView1.Items.Clear()
strFileRead = TIMESHEET:Recordset_Connection( 0 )
使用 RstFileRead
< span class =code-keyword>不 .EOF
LvItem = 新 ListViewItem
LvItem = ListView1.Items.Add(.Fields( EMP_NO)。Value)
LvItem.SubItems.Add(.Fields( L_NAME)。Value& ,& (.Fields( F_NAME)。Value))
LvItem.SubItems.Add( .Fields( DEPT)。Value)
.MoveNext()
循环
结束 使用
结束 Sub
-I在Form_Load事件中调用此过程,因此它将使用TIMESHEET中提供的查询列出ListView中的Employees,这是一个存储过程..
TIMESHEET查询:
SELECT * FROM EMP0000 INNER JOIN EMP0001 ON EMP0001.EMP_NO = EMP0000.EMP_NO INNER JOIN ATT0000 ON ATT0000.EMP_NO = EMP0000.EMP_NO
我有另一个存储过程提供如下命令:
FIND_EMP查询:
SELECT * FROM EMP0000 INNER JOIN EMP0001 ON EMP0001.EMP_NO = EMP0000 .EMP_NO INNER JOIN ATT0000 ON ATT0000.EMP_NO = EMP0000.EMP_NO WHERE EMP_0000.EMP_NO = @empno
现在我要拨打FIND_EMP使用Visual Studio 2010存储过程..当我单击或选择ListView中的项目时,如何调用FIND_EMP过程?
谢谢!
我创建了一个基本的Sql助手类。试试这个
公共 类 SqlHelper
Implements IDisposable
私有 sqlConnection As SqlConnection
公共 功能 ExecuteReader(procedureName As String ,sqlParameters As SqlParameter()) As SqlDataReader
尝试
Dim dataReader As SqlDataReader
Me .Open()
Dim sqlCommand = 新 SqlCommand(procedureName,sqlConnection)
sqlCommand.CommandType = C ommandType.StoredProcedure
如果 sqlParameters IsNot Nothing 然后
对于 每个参数作为 SqlParameter 在 sqlParameters
sqlCommand.Parameters.Add(parameter)
下一步
结束 如果
dataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
返回 dataReader
Catch ex 作为例外
投掷 ex
结束 尝试
结束 功能
公共 功能 ExecuteReader(sqlQuery As String ) As SqlDataReader
尝试
Dim dataReader As SqlDataReader
我 .Open()
Dim sqlCommand = < span class =code-keyword>新 SqlCommand(sqlQuery,sqlConnection)
sqlCommand.CommandType = CommandType.Text
dataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
返回 dataReader
Catch ex As 例外
投掷 ex
结束 尝试
结束 功能
私有 Sub 打开()
如果 sqlConnection 没什么 然后
sqlConnection = 新 SqlConnection(AppConfig.ConnectionString)
sqlConnection.Open()
结束 如果
结束 Sub
公开 Sub 关闭()
如果 sqlConnection IsNot 没什么 然后
sql Connection.Close()
结束 如果
结束 子
公开 Sub Dispose()
如果 sqlConnection IsNot Nothing 然后
sqlConnection.Dispose()
sqlConnection = 没有
结束 如果
结束 Sub
结束 类
公共 类 AppConfig
公开 共享 ReadOnly 属性 ConnectionString() As 字符串
获取
返回 ConfigurationManager .ConnectionStrings( ConnectionString)。ConnectionString
结束 获取
结束 属性
结束 类
希望这有帮助
我可以开发你的薪资系统。
谢谢
Raj
rajgaurav2702@gmail.com
_ hooray! need help for my project .. I am developing a Payroll System .. I have a Stored Procedure created using SSMS 2005 ..details:
Stored Procedure Name: Find_Emp Command/Query= SELECT * FROM EMP0000 INNER JOIN EMP0001 ON EMP0001.EMP_NO =EMP0000.EMP_NO WHERE EMP0000.EMP_NO=@empno
In my GUI using Visual Studio 2010, I have a ListView where the list of employee listed..
I want to call the procedure in SelectedIndexChanged Event of the ListView ..
My problem is don''t have an idea how to call this procedure in visual studio 2010 .. any help please ?
It''s quite complicated I think .. I am using Visual Studio 2010(vb.net) .. your code is in c# ? am I right ?
by the way here''s some details:
in module:
Public Sub Recordset_Connection(ByVal nMode As Integer)
If nMode = 0 Then
RstFileRead = Nothing
RstFileRead = New ADODB.Recordset
RstFileRead.Open(strFileRead, Conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly)
ElseIf nMode = 1 Then
RstFileUpdate = Nothing
RstFileUpdate = New ADODB.Recordset
RstFileUpdate.Open(strFileUpdate, Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
End If
End Sub
in my form , I create a sub procedure like the one below:
Private Sub LoadListEmp()
Dim LvItem As New ListViewItem
ListView1.Items.Clear()
strFileRead = "TIMESHEET" : Recordset_Connection(0)
With RstFileRead
Do While Not .EOF
LvItem = New ListViewItem
LvItem = ListView1.Items.Add(.Fields("EMP_NO").Value)
LvItem.SubItems.Add(.Fields("L_NAME").Value & "," & (.Fields("F_NAME").Value))
LvItem.SubItems.Add(.Fields("DEPT").Value)
.MoveNext()
Loop
End With
End Sub
-I call this procedure in Form_Load Event so it will list the Employees in ListView with the query provided in the TIMESHEET which is a Stored Procedure..
TIMESHEET Query:
SELECT * FROM EMP0000 INNER JOIN EMP0001 ON EMP0001.EMP_NO =EMP0000.EMP_NO INNER JOIN ATT0000 ON ATT0000.EMP_NO = EMP0000.EMP_NO
I have another Stored Procedure provided with the command as below:
FIND_EMP Query:
SELECT * FROM EMP0000 INNER JOIN EMP0001 ON EMP0001.EMP_NO =EMP0000.EMP_NO INNER JOIN ATT0000 ON ATT0000.EMP_NO = EMP0000.EMP_NO WHERE EMP_0000.EMP_NO = @empno
Now I want to call the FIND_EMP Stored Procedure using Visual Studio 2010.. When I click or Select an item in ListView, how to call the FIND_EMP procedure ?
thanks!
I have created a basic Sql helper class. Try this
Public Class SqlHelper Implements IDisposable Private sqlConnection As SqlConnection Public Function ExecuteReader(procedureName As String, sqlParameters As SqlParameter()) As SqlDataReader Try Dim dataReader As SqlDataReader Me.Open() Dim sqlCommand = New SqlCommand(procedureName, sqlConnection) sqlCommand.CommandType = CommandType.StoredProcedure If sqlParameters IsNot Nothing Then For Each parameter As SqlParameter In sqlParameters sqlCommand.Parameters.Add(parameter) Next End If dataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection) Return dataReader Catch ex As Exception Throw ex End Try End Function Public Function ExecuteReader(sqlQuery As String) As SqlDataReader Try Dim dataReader As SqlDataReader Me.Open() Dim sqlCommand = New SqlCommand(sqlQuery, sqlConnection) sqlCommand.CommandType = CommandType.Text dataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection) Return dataReader Catch ex As Exception Throw ex End Try End Function Private Sub Open() If sqlConnection Is Nothing Then sqlConnection = New SqlConnection(AppConfig.ConnectionString) sqlConnection.Open() End If End Sub Public Sub Close() If sqlConnection IsNot Nothing Then sqlConnection.Close() End If End Sub Public Sub Dispose() If sqlConnection IsNot Nothing Then sqlConnection.Dispose() sqlConnection = Nothing End If End Sub End Class Public Class AppConfig Public Shared ReadOnly Property ConnectionString() As String Get Return ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString End Get End Property End Class
Hope this helps
I can develope your Payroll System.
Thanks
Raj
rajgaurav2702@gmail.com
这篇关于存储过程实现的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!