LLENAR COMBOX CON PROCEDIMIENTO ALMACENADO
Publicado por HECTOR (55 intervenciones) el 22/12/2015 19:38:49
ALGUIEN SABE COMO PUEDO LLENAR COMBO BOX CON UN PROCEDIMIENTO ALMACENADO.
Valora esta pregunta
0
Iniciar sesiónCrear cuenta
Crear cuentaCrear cuenta
CREATE PROCEDURE [dbo].[pestado]
@accion int=null,
@idestado int=null,
@nombreestado nvarchar(50) = null
AS
SET NOCOUNT ON
if @accion = 1begin
select nombreestado from testado order by nombreestado
end
Public Class DaoEstados
Dim db As New Conexion.DatabasePublic Function ObtenerListaEstadosforCommbo() As Object
Try
Dim arrParams As New ArrayList
arrParams.Add(New System.Data.SqlClient.SqlParameter("@accion", 1))
Return db.ExecuteSP("pestado", arrParams)
Catch ex As Exception
Throw ex
End Try
End Function
End Class
Dim daoc as new daoestados
ComboEstado.DataSource = daoc.ObtenerListaEstadosforCommbo
ComboEstado.DisplayMember = "NombreEstado"
ComboEstado.SelectedIndex = -1
Imports System.DataImports System.Data.SqlClient
Public Class Database
Dim connStr As String = "Data Source=TUPC\SQLEXPRESS;Initial Catalog=tubd;Integrated Security=True"
Dim conn As New SqlConnection
Public Sub New()
conn.ConnectionString = connStr
End Sub
Public Sub OpenConnection()
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Catch ex As Exception
Throw ex
End Try
End Sub
Public Sub CloseConnection()
Try
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
Catch ex As Exception
Throw ex
End Try
End Sub
Public Function ExecuteSP(ByVal spName As String, ByVal params As ArrayList) As DataTable
Try
'crear comando
Dim command As New SqlCommand
command.CommandType = CommandType.StoredProcedure
command.CommandText = spName
command.CommandTimeout = 5
command.Connection = conn
For Each param As SqlParameter In params
command.Parameters.Add(param)
Next
'metodo de ejecución
Dim ds As New DataSet 'objeto creado vacio
Dim da As New SqlDataAdapter(command)
da.Fill(ds)
'asegurarnos de que se obtuvo una respuesta de la BD
If ds.Tables.Count > 0 Then
Return ds.Tables(0)
Else
Return Nothing
End If
Catch ex As Exception
Throw ex
End Try
End Function
Public Function ExecuteQuery(ByVal sql As String) As DataTable
Try
Dim command As New SqlCommand
command.CommandType = CommandType.Text
command.CommandText = sql
command.CommandTimeout = 5
command.Connection = conn
Dim ds As New DataSet
Dim da As New SqlDataAdapter(command)
da.Fill(ds)
If ds.Tables.Count > 0 Then
Return ds.Tables(0)
Else
Return Nothing
End If
Catch ex As Exception
Throw ex
End Try
End Function
Public Sub ExecuteNonQuery(ByVal sql As String)
Try
Dim command As New SqlCommand
command.CommandType = CommandType.Text
command.CommandText = sql
command.CommandTimeout = 5
command.Connection = conn
command.ExecuteNonQuery()
Catch ex As Exception
Throw ex
End Try
End Sub
Public Function ExecuteSPWithTransaction(ByVal spName As String, ByVal params As ArrayList) As DataTable
Dim transaction As SqlTransaction = conn.BeginTransaction(IsolationLevel.Snapshot)
Try
Dim command As New SqlCommand
command.CommandType = CommandType.StoredProcedure
command.CommandText = spName
command.CommandTimeout = 5
command.Connection = conn
For Each param As SqlParameter In params
command.Parameters.Add(param)
Next
command.Transaction = transaction
Dim ds As New DataSet
Dim da As New SqlDataAdapter
da.Fill(ds)
transaction.Commit()
If ds.Tables.Count > 0 Then
Return ds.Tables(0)
Else
Return Nothing
End If
Catch ex As Exception
transaction.Rollback()
Throw ex
End Try
End Function
Public Function ExecuteSPWithOutParam(ByVal spName As String, ByVal params As ArrayList, ByVal indexParamSalida As Int16) As Object
Try
Dim command As New SqlCommand
command.CommandType = CommandType.StoredProcedure
command.CommandText = spName
command.CommandTimeout = 5
command.Connection = conn
For Each param As SqlParameter In params
command.Parameters.Add(param)
Next
Dim ds As New DataSet
Dim da As New SqlDataAdapter(command)
da.Fill(ds)
Dim obj As Object = command.Parameters.Item(indexParamSalida).Value
Return obj
Catch ex As Exception
Throw ex
End Try
End Function
End Class