miércoles, 28 de octubre de 2009

Crea una tabla, usala y borrala

Primero se deberà crear la tabla para esto uso algunos parametros para que no se dupliquen:
Dim h As String = Hour(Now)
Dim m As String = Minute(Now)
Dim s As String = Second(Now)
Dim hora As String = CStr(h)
Dim minuto As String = CStr(m)
Dim segundo As String = CStr(s)
Dim tabla_temp As String = "Temp_" + hora + minuto + segundo

'Creamos la tabla
campos="(campos1 int, campos2 varchar(100), campo3 varchar(100))"
Dim creatabla As String = "CREATE TABLE " + tabla_temp + campos
Dim comando As New SqlClient.SqlCommand(creatabla, Conexion)
Conexion.Open()
Comando.ExecuteNonQuery()
Conexion.Close()

'llenamos la tabla
Dim campo1 as integer = 1
Dim campo2 as string= "Juan"
Dim campo3 as string= "Perez"
Dim Sentencia1 As String
Sentencia1 = "insert into " + tabla_temp & _
"(campo1," & _
"(campo2," & _
"(campo3)" & _
" values " & _
"('" & campo1 & "';"
"('" & campo2 & "';"
"('" & campo3 & "');"
Dim comando1 As New SqlClient.SqlCommand(Sentencia1, Conexion)
Conexion.Open()
comando1.ExecuteNonQuery()
Conexion.Close()

'podemos modificar algùn valor del campo
Dim campon as string="Pedro"
Dim sentencia3 As String
sentencia3 = "update " + tabla_temp + " set campo2 = "'" & campon & "' Where campo1 = 1
Dim comando3 As New SqlClient.SqlCommand(sentencia3, Conexion)
Conexion.Open()
comando3.ExecuteNonQuery()
Conexion.Close()

'podemos visualizar su contenido en un datagridview
Dim Ds As New DataSet
Dim Da As New SqlClient.SqlDataAdapter("select * from " + tabla_temp, Conexion)
Da.Fill(Ds, tabla_temp)
Dim myDataView As DataView = New DataView(Ds.Tables(tabla_temp))
Me.DataGridView1.DataSource = myDataView

'una vez que se ha terminado de utilizar la tabla la borramos
Dim borratebla As String = "DROP TABLE " + tabla_temp
Dim comando As New SqlClient.SqlCommand(borratabla, Conexion)
Conexion.Open()
comando.ExecuteNonQuery()
Conexion.Close()

Comparte