Module Module1 Sub Main() Dim resConsulta As SqlDataReader = Nothing Dim conexionSQL As SqlConnection = New SqlConnection() Dim cmd As SqlCommand conexionSQL.ConnectionString = "Server=localhost;initial catalog=Adventureworks; Trusted_Connection=TRUE" conexionSQL.Open() cmd = New SqlCommand("SELECT * FROM Person.Contact WHERE ContactID<=10", conexionSQL) resConsulta = cmd.ExecuteReader() While resConsulta.Read() Console.WriteLine(resConsulta("FirstName").ToString() + " " + resConsulta("LastName").ToString()) End While Console.ReadLine() resConsulta.Close() cmd.Dispose() conexionSQL.Close() End Sub End Module
Conectar a SQL con Visual Basic
Started by Alberto Dominguez, sep 24 2010 05:37
5 replies to this topic
#1
Escrito 24 septiembre 2010 - 05:37
Saludos,
Alberto Dominguez
Enterprise Architect y Trainer
MCT, MCPD, MCITP, MCITP Dynamics, MCSE, MCSA, MCTS, MCP...
ITIL V3 Foundation Qualification in IT Service Management
Perfil Profesional Twitter
Alberto Dominguez
Enterprise Architect y Trainer
MCT, MCPD, MCITP, MCITP Dynamics, MCSE, MCSA, MCTS, MCP...
ITIL V3 Foundation Qualification in IT Service Management
Perfil Profesional Twitter
#2
Escrito 24 septiembre 2010 - 06:04
Otro ejemplo que inserta valores en una tabla con un parametro:
Module Module1 Sub Main() Dim resConsulta As SqlDataReader = Nothing Dim conexionSQL As SqlConnection = New SqlConnection() Dim cmd As SqlCommand conexionSQL.ConnectionString = "Server=localhost;initial catalog=Adventureworks; Trusted_Connection=TRUE" conexionSQL.Open() cmd = New SqlCommand("SELECT * FROM dbo.Prueba", conexionSQL) resConsulta = cmd.ExecuteReader() While resConsulta.Read() Console.WriteLine(resConsulta("Nombre").ToString() + " " + resConsulta("Edad").ToString()) End While resConsulta.Close() cmd.Parameters.Add("@parametro1", SqlDbType.NVarChar) cmd.Parameters("@parametro1").Value = "Jose" cmd.CommandText = "INSERT INTO dbo.Prueba (Nombre,Edad) VALUES (@parametro1,50)" cmd.ExecuteNonQuery() cmd = New SqlCommand("SELECT * FROM dbo.Prueba", conexionSQL) resConsulta = cmd.ExecuteReader() While resConsulta.Read() Console.WriteLine(resConsulta("Nombre").ToString() + " " + resConsulta("Edad").ToString()) End While Console.ReadLine() cmd.Dispose() conexionSQL.Close() End Sub End Module
Saludos,
Alberto Dominguez
Enterprise Architect y Trainer
MCT, MCPD, MCITP, MCITP Dynamics, MCSE, MCSA, MCTS, MCP...
ITIL V3 Foundation Qualification in IT Service Management
Perfil Profesional Twitter
Alberto Dominguez
Enterprise Architect y Trainer
MCT, MCPD, MCITP, MCITP Dynamics, MCSE, MCSA, MCTS, MCP...
ITIL V3 Foundation Qualification in IT Service Management
Perfil Profesional Twitter
#3
Escrito 24 septiembre 2010 - 21:45
Module Module1 Sub Main() Dim resConsulta As SqlDataReader = Nothing Dim conexionSQL As SqlConnection = New SqlConnection() Dim cmd As SqlCommand conexionSQL.ConnectionString = "Server=localhost;initial catalog=Adventureworks; Trusted_Connection=TRUE" conexionSQL.Open() cmd = New SqlCommand("SELECT * FROM Person.Contact WHERE ContactID<=10", conexionSQL) resConsulta = cmd.ExecuteReader() While resConsulta.Read() Console.WriteLine(resConsulta("FirstName").ToString() + " " + resConsulta("LastName").ToString()) End While Console.ReadLine() resConsulta.Close() cmd.Dispose() conexionSQL.Close() End Sub End Module
Está lectura es "Conectada". También puedes hacerla "desconectada" utilizando un DataSet, actualizando, añadiendo o borrando registros, y luego vuelcas la Tabla del DataSet a la BD. (Una explicación muy rápida y vulgar, xD)
Dependiendo para lo que sea, es buena una u otra.
#4
Escrito 26 septiembre 2010 - 19:47
Si efectivamente, el DATASET mantendría los datos en cache, además también puedes aplicar "LINQ to DataSet" para acceder a los datos del mismo...
Dejo Ejemplo de carga de datos al DataSet No Tipado y Tipado:
Dejo Ejemplo de carga de datos al DataSet No Tipado y Tipado:
Imports System.Data.SqlClient Public Class LinqToDatasets 'Demo 1 Public Shared Sub LinqToUnTypedDataset() Console.WriteLine("Demo Dataset 1 - Untyped Datasets") 'Crear y Abrir conexion a Base de datos Dim DBConn As New SqlConnection DBConn.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True" DBConn.Open() 'Llenar Nuevo DataSet No Tipado Dim Adaptador As New SqlDataAdapter("Select * from Sales.Product", DBConn) Dim DS1 As New DataSet Adaptador.Fill(DS1) 'Query con LINQ to DataSet para obtener los 10 productos mas caros Dim MostExpensiveProducts = From P In DS1.Tables(0) _ Order By P("ListPrice") Descending _ Take 10 _ Select P 'Escribe Resultados en la consola For Each P In MostExpensiveProducts Console.WriteLine(String.Format("{0} - costs:{1}", P("Name"), P("ListPrice"))) Next 'Actualiza datos a la base de datos Adaptador.Update(DS1) End Sub 'Demo 2 Public Shared Sub LinqToTypedDataset() Console.WriteLine("Demo Dataset 2 - Typed Datasets") 'Crear y Abrir conexion a Base de datos Dim DBConn As New SqlConnection DBConn.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorksLT;Integrated Security=True" DBConn.Open() 'Llenar Nuevo DataSet Tipado Dim Adaptador As New dsProductsTableAdapters.ProductTableAdapter Adaptador.Connection = DBConn Dim dtProducts As New dsProducts.ProductDataTable Adaptador.Fill(dtProducts) 'Query con LINQ to DataSet para obtener los 10 productos mas caros Dim MostExpensiveProducts = From P In dtProducts _ Order By P.ListPrice Descending _ Take 10 _ Select P 'Escribe Resultados en la consola For Each P In MostExpensiveProducts Console.WriteLine(String.Format("{0} - costs:{1}", _ P.Name, P.ListPrice)) Next 'Actualiza datos a la base de datos Adaptador.Update(dtProducts) End Sub End Class
Saludos,
Alberto Dominguez
Enterprise Architect y Trainer
MCT, MCPD, MCITP, MCITP Dynamics, MCSE, MCSA, MCTS, MCP...
ITIL V3 Foundation Qualification in IT Service Management
Perfil Profesional Twitter
Alberto Dominguez
Enterprise Architect y Trainer
MCT, MCPD, MCITP, MCITP Dynamics, MCSE, MCSA, MCTS, MCP...
ITIL V3 Foundation Qualification in IT Service Management
Perfil Profesional Twitter
#5
Escrito 27 septiembre 2010 - 10:44
Otro ejemplo, en este caso escribimos un dato en un campo de una tabla vía un DATASET:
Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim Adaptador As New ClientesTableAdapters.ClientesTableAdapter Dim dtClientes As New Clientes.ClientesDataTable() Adaptador.Fill(dtClientes) Dim Dato1 = (From P In dtClientes Select P).First() TextBox1.Text = Dato1.Nombre.ToString() Dato1.Nombre = "Pedro" Adaptador.Update(dtClientes) Dato1 = (From P In dtClientes Select P).First() TextBox1.Text = Dato1.Nombre.ToString() End Sub End Class
Saludos,
Alberto Dominguez
Enterprise Architect y Trainer
MCT, MCPD, MCITP, MCITP Dynamics, MCSE, MCSA, MCTS, MCP...
ITIL V3 Foundation Qualification in IT Service Management
Perfil Profesional Twitter
Alberto Dominguez
Enterprise Architect y Trainer
MCT, MCPD, MCITP, MCITP Dynamics, MCSE, MCSA, MCTS, MCP...
ITIL V3 Foundation Qualification in IT Service Management
Perfil Profesional Twitter
#6
Escrito 28 septiembre 2010 - 12:48
Actualizando un DataGridView y haciendo select sobre el DataSet que ponemos como Datasource del DataGridView:
Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'EmpleadosDataSet.ListinTelefonos' table. You can move, or remove it, as needed. Me.ListinTelefonosTableAdapter.Fill(Me.EmpleadosDataSet.ListinTelefonos) End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Me.ListinTelefonosTableAdapter.Update(Me.EmpleadosDataSet.ListinTelefonos) DataGridView1.DataSource = EmpleadosDataSet.ListinTelefonos.Select("ID=2") End Sub End Class
Saludos,
Alberto Dominguez
Enterprise Architect y Trainer
MCT, MCPD, MCITP, MCITP Dynamics, MCSE, MCSA, MCTS, MCP...
ITIL V3 Foundation Qualification in IT Service Management
Perfil Profesional Twitter
Alberto Dominguez
Enterprise Architect y Trainer
MCT, MCPD, MCITP, MCITP Dynamics, MCSE, MCSA, MCTS, MCP...
ITIL V3 Foundation Qualification in IT Service Management
Perfil Profesional Twitter
0 usuarios están leyendo este tema
0 miembro/s, 0 invitado/s, 0 usuario/s anónimo/s