Lección 2: Lectura y escritura de datos
Para ejecutar los comandos de la base de datos, debe tener una conexión abierta y un objeto de comando. En esta lección se describe cómo crear y utilizar un objeto de comando.
DbCommand
Se utiliza DbCommand para enviar un lenguaje estructurado de consultas (SQL) a la base de datos. DbCommand utiliza comandos del lenguaje de manipulación de datos (DML) para recuperar, insertar, actualizar o eliminar datos y también puede usar comandos de Data Definition Language (DDL), que le permite crear tablas y modificar la información de esquema en la base de datos.
El DbCommand requiere una conexión válida abierta para emitir pedidos a la base de datos. La mejor manera de crear un DbCommand es utilizar el método CreateCommand en el objeto DbConnection ,que crea automáticamente el proveedor específico apropiado y también requiere tener un valor válido para sus propiedades CommandText y CommandType:
--------------------------------------VB------------------------------------------
'obtengo la conexión de web.config o app.config
Dim nw = ConfigurationManager.ConnectionStrings("nw")
Dim connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
Dim cmd = connection.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "CustOrderHist"
'cuando se termina de usar no nos debemos olvidar de cerrar la conexión
--------------------------------------CS------------------------------------------
var nw = ConfigurationManager.ConnectionStrings["nw"];
var connection = new SqlConnection(nw.ConnectionString);
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "CustOrderHist";
Los DbParameter
Los procedimientos almacenados (StoredProcedure) suelen requerir parámetros para ejecutarse. Se pueden crear System.Data.Common.DbParameter utilizando el método Parameters.Add en el DbCommand:
--------------------------------------VB------------------------------------------
Dim nw = ConfigurationManager.ConnectionStrings("nw")
Dim connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
'creo el dbcommand
Dim cmd = connection.CreateCommand()
'de tipo porcedimiento almacenado
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "CustOrderHist"
'creo los parametros q necesita
Dim parm = cmd.CreateParameter()
'en este caso el Id del cliente
parm.ParameterName = "@Id"
parm.Value = "ANATR"
cmd.Parameters.Add(parm)
Dim id = cmd.Parameters("@Id").Value
--------------------------------------CS------------------------------------------
var nw = ConfigurationManager.ConnectionStrings["nw"];
var connection = new SqlConnection();
connection.ConnectionString = nw.ConnectionString;
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "CustOrderHist";
DbParameter parm = cmd.CreateParameter();
parm.ParameterName = "@Id";
parm.Value = "ANATR";
cmd.Parameters.Add(parm);
Se puede utilizar el nombre del DbParameter para acceder al parámetro a través del código, ej:
--------------------------------------VB------------------------------------------
Dim id = cmd.Parameters("@Id").Value
--------------------------------------CS------------------------------------------
var id = (string)cmd.Parameters["@Id"].Value;
El Método ExecuteNonQuery
Se ejecuta el método ExecuteNonQuery cuando no se espera una orden para regresar todas las filas,por ejemplo: una inserción, actualización o eliminación. Este método devuelve un entero que representa el número de filas afectadas por la operación.
--------------------------------------VB------------------------------------------
Private Sub menuExecuteNonQuery_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles ExecuteNonQueryToolStripMenuItem.Click
Dim nw = ConfigurationManager.ConnectionStrings("nw")
Dim count As Integer = 0
Using connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
Dim cmd = connection.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = _
"UPDATE Products SET UnitPrice = UnitPrice * 1.1 WHERE ProductID = 10"
connection.Open()
count = cmd.ExecuteNonQuery()
End Using
MessageBox.Show(count.ToString())
End Sub
--------------------------------------CS------------------------------------------
private void menuExecuteNonQuery_Click(object sender, EventArgs e)
{
var nw = ConfigurationManager.ConnectionStrings["nw"];
int count = 0;
using (var connection = new SqlConnection())
{
connection.ConnectionString = nw.ConnectionString;
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"UPDATE Products SET UnitPrice = UnitPrice * 1.1 WHERE ProductID = 10";
connection.Open();
count = cmd.ExecuteNonQuery();
}
MessageBox.Show(count.ToString());
}
Método ExecuteReader
El método ExecuteReader devuelve una instancia de DbDataReader. (un DbDataReader es de sólo avance, sólo lectura, cursor de servidor. se crean mediante la ejecución de uno de los métodos ExecuteReader del DbCommand.
--------------------------------------VB------------------------------------------
Dim nw = ConfigurationManager.ConnectionStrings("nw")
Dim connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
Dim cmd = connection.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT ProductID, UnitPrice FROM Products"
connection.Open()
Dim rdr = cmd.ExecuteReader()
While (rdr.Read())
MessageBox.Show(rdr("ProductID") & ": " & rdr("UnitPrice"))
End While
connection.Close()
--------------------------------------CS------------------------------------------
var nw = ConfigurationManager.ConnectionStrings["nw"];
var connection = new SqlConnection();
connection.ConnectionString = nw.ConnectionString;
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT ProductID, UnitPrice FROM Products";
connection.Open();
DbDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
MessageBox.Show(rdr["ProductID"] + ": " + rdr["UnitPrice"]);
}
connection.Close();
Método ExecuteScalar
Las consultas son a menudo espera que devolver una única fila con una sola columna. En estas situaciones, los resultados pueden ser tratados como un único valor de retorno. Por ejemplo:
--------------------------------------VB------------------------------------------
Dim nw = ConfigurationManager.ConnectionStrings("nw")
Dim connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
Dim cmd = connection.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT COUNT(*) FROM Products"
connection.Open()
Dim count = cmd.ExecuteScalar()
connection.Close()
MessageBox.Show(count.ToString())
--------------------------------------CS------------------------------------------
var nw = ConfigurationManager.ConnectionStrings["nw"];
var connection = new SqlConnection();
connection.ConnectionString = nw.ConnectionString;
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT COUNT(*) FROM Products";
connection.Open();
int count = (int)cmd.ExecuteScalar();
connection.Close();
MessageBox.Show(count.ToString());
DbDataReader
Un objeto DbDataReader proporciona un método de recuperación de datos del dataset. Ofrece un sólo avance, de sólo lectura, cursor de servidor. Para las modificaciones de datos, el DbDataAdapter puede ser una mejor opción.
El método Load del DataTable tiene un parámetro LoadOption que le da la opción de decidir cuál es el DataRowVersion deben recibir los datos entrantes.
| Nombre de miembro | Descripción |
| OverwriteChanges | Los valores de entrada de esta fila se escribirán en las versiones de valores actuales y valores originales de los datos de cada columna. |
| PreserveChanges | Los valores de entrada de esta fila se escribirán en la versión de valores originales de cada columna. La versión actual de los datos de cada columna no se modificará. El valor predeterminado es [This]. |
| Upsert | Los valores de entrada de esta fila se escribirán en la versión actual de cada columna. La versión original de los datos de cada columna no se modificará. |
Uso de varios conjuntos de resultados activos (MARS) para ejecutar varios comandos en una conexión
Utilizando el objeto DbDataReader es uno de los métodos más rápidos para recuperar datos de la base de datos, pero uno de los problemas con DbDataReader es que si se intenta ejecutar otro comando, mientras que el primer comando todavía se está ejecutando, recibirá una InvalidOperationException, diciendo: "Ya hay un DataReader abierto asociado a esta conexión que se debe cerrar primero." Se puede evitar esta excepción al establecer la opción MultipleActiveResultSets de la cadena de conexión a true:
<connectionStrings>
<clear />
<add name="nwMars"
providerName="System.Data.SqlClient"
connectionString=
"Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|Northwind.MDF;
Integrated Security=True;
User Instance=True;
MultipleActiveResultSets=True"/>
</connectionStrings>
MARS hace que nuestra programación sea más fácil , pero tiene un impacto negativo en el rendimiento.
El siguiente ejemplo muestra cómo MARS puede realizar las consultas anidadas,utilizando la tabla de clientes para recuperar el número de pedidos de cada cliente.
--------------------------------------VB------------------------------------------
Private Sub menuMars_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MARSToolStripMenuItem.Click
Dim nw = ConfigurationManager.ConnectionStrings("nwMars")
Using connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
Dim cmd = connection.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT CustomerID, CompanyName FROM Customers"
connection.Open()
Dim rdr = cmd.ExecuteReader()
While rdr.Read()
Dim OrdersCmd = connection.CreateCommand()
OrdersCmd.CommandType = CommandType.Text
OrdersCmd.CommandText = _
"SELECT COUNT(OrderID) FROM Orders WHERE (CustomerID = @CustId)"
Dim parm = OrdersCmd.CreateParameter()
parm.ParameterName = "@CustId"
parm.Value = rdr("CustomerID")
OrdersCmd.Parameters.Add(parm)
Dim qtyOrders = OrdersCmd.ExecuteScalar()
MessageBox.Show( _
rdr("CompanyName").ToString() + ": " + qtyOrders.ToString())
End While
End Using
End Sub
--------------------------------------CS------------------------------------------
private void menuMars_Click(object sender, EventArgs e)
{
var nw = ConfigurationManager.ConnectionStrings["nwMars"];
using(var connection = new SqlConnection())
{
connection.ConnectionString = nw.ConnectionString;
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT CustomerID, CompanyName FROM Customers";
connection.Open();
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var ordersCmd = connection.CreateCommand();
ordersCmd.CommandType = CommandType.Text;
ordersCmd.CommandText =
"SELECT COUNT(OrderID) FROM Orders WHERE (CustomerID = @CustId)";
var parm = ordersCmd.CreateParameter();
parm.ParameterName = "@CustId";
parm.Value = rdr["CustomerID"];
ordersCmd.Parameters.Add(parm);
var qtyOrders = ordersCmd.ExecuteScalar();
MessageBox.Show(rdr["CompanyName"].ToString() + ": "
+ qtyOrders.ToString());
}
}
}
Realizar operaciones de copia masiva con un SqlBulkCopy
A veces se necesitan copiar grandes cantidades de datos de un lugar a otro, de una base de datos a otra, ya sea mediante una interfaz GUI de Windows, como el SQL Server Enterprise Manager o mediante una herramienta de línea de comandos (BCP.exe).
La clase SqlBulkCopy proporciona el método WriteToServer() para copiar datos a una tabla en una base de datos SQL Server.
![clip_image001[4]](http://lh4.ggpht.com/-6NjiA6Dmn5c/TuEyzbTNvRI/AAAAAAAAAxo/1sCV03gcdcg/s1600-h/image4.png "clip_image001[4]")
El siguiente código muestra cómo se puede utilizar un SqlBulkCopy para copiar los datos de la tabla Customers en la base de datos Northwind:
--------------------------------------VB------------------------------------------
Dim nw = ConfigurationManager.ConnectionStrings("nw")
Dim nwConnection = New SqlConnection()
nwConnection.ConnectionString = nw.ConnectionString
Dim bulkCopy = ConfigurationManager.ConnectionStrings("BulkCopy")
Dim bulkConnection = New SqlConnection()
bulkConnection.ConnectionString = bulkCopy.ConnectionString
Dim cmd = nwConnection.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT CustomerID, CompanyName FROM Customers"
nwConnection.Open()
bulkConnection.Open()
Dim rdr = cmd.ExecuteReader()
Dim bc As New SqlBulkCopy(bulkConnection)
bc.DestinationTableName = "StoreList"
bc.WriteToServer(rdr)
nwConnection.Close()
bulkConnection.Close()
--------------------------------------CS------------------------------------------
var nw = ConfigurationManager.ConnectionStrings["nw"];
var nwConnection = new SqlConnection();
nwConnection.ConnectionString = nw.ConnectionString;
var bulkCopy = ConfigurationManager.ConnectionStrings["BulkCopy"];
var bulkConnection = new SqlConnection();
bulkConnection.ConnectionString = bulkCopy.ConnectionString;
var cmd = nwConnection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT CustomerID, CompanyName FROM Customers";
nwConnection.Open();
bulkConnection.Open();
var rdr = cmd.ExecuteReader();
var bc = new SqlBulkCopy(bulkConnection);
bc.DestinationTableName = "CustomerList";
bc.WriteToServer(rdr);
nwConnection.Close();
bulkConnection.Close();
DbDataAdapter
Se utiliza el DbDataAdapter para recuperar y actualizar datos entre una tabla de datos y una base de datos.
DbDataAdapter tiene una propiedad SelectCommand se utiliza al recuperar los datos, esta debe contener un DbCommand válido. Internamente, SelectCommand tiene un método ExecuteReader, que se ejecuta para obtener un DbDataReader para rellenar un DataTable. También tiene InsertCommand, UpdateCommand y DeleteCommand.
El DbDataAdapter se encarga de abrir una conexion y luego la cierra automáticamente.
Utilizando el método Fill
El método Fill se mueve de datos desde un almacén de datos a un DataTable. El siguiente ejemplo muestra cómo una tabla de datos se pueden cargar mediante el método Fill.
--------------------------------------VB------------------------------------------
Dim nw = ConfigurationManager.ConnectionStrings("nw")
Dim connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
Dim cmd = CType(connection.CreateCommand(), SqlCommand)
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT CustomerID, CompanyName FROM Customers"
Dim nwSet As New DataSet("nw")
Dim da As New SqlDataAdapter(cmd)
da.Fill(nwSet, "Customers")
--------------------------------------CS------------------------------------------
var nw = ConfigurationManager.ConnectionStrings["nw"];
var connection = new SqlConnection();
connection.ConnectionString = nw.ConnectionString;
var cmd = (SqlCommand)connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT CustomerID, CompanyName FROM Customers";
var da = new SqlDataAdapter(cmd);
var nwSet = new DataSet("nw");
da.Fill(nwSet, "Customers");
MessageBox.Show("DataSet Filled");
Guardando los cambios a la base de datos utilizando el método Update
El método Update de la DataTable guarda las modificaciones a la base de datos mediante la recuperación de los cambios de la DataTable y despues utiliza los respectivos InsertCommand, UpdateCommand o DeleteCommand para enviarlos a la base de datos fila por fila. El método Update recupera los DataRow que han cambiado la propiedad RowState y envía el cambio a la base de datos.
--------------------------------------VB------------------------------------------
Dim nw = ConfigurationManager.ConnectionStrings("nw")
Dim connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
Dim cmd = connection.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM Customers"
Dim nwSet As New DataSet("nw")
Dim da As New SqlDataAdapter(cmd)
Dim bldr As New SqlCommandBuilder(da)
da.Fill(nwSet, "Customers")
'Modifico la fila existente
Dim customersTable = nwSet.Tables("Customers")
Dim updRow = customersTable.Select("CustomerID='WOLZA'")(0)
updRow("CompanyName") = "New Wolza Company"
'Agrego una nueva fila
customersTable.Rows.Add( _
"AAAAA", "Five A Company")
'borro una fila (no se puede borrar un cliente que tenga ordenes)
Dim delRow = customersTable.Select("CustomerID='PARIS'")(0)
delRow.Delete()
'envio los cambios a la DB
da.Update(nwSet, "Customers")
DataGridView2.DataSource = nwSet
DataGridView2.DataMember = "Customers"
--------------------------------------CS------------------------------------------
var nw = ConfigurationManager.ConnectionStrings["nw"];
var connection = new SqlConnection();
connection.ConnectionString = nw.ConnectionString;
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM Customers";
var da = new SqlDataAdapter(cmd);
var nwSet = new DataSet("nw");
var bldr = new SqlCommandBuilder(da);
da.Fill(nwSet, "Customers");
var customersTable = nwSet.Tables["Customers"];
var updRow = customersTable.Select("CustomerID='WOLZA'")[0];
updRow["CompanyName"] = "New Wolza Company";
customersTable.Rows.Add(
"AAAAA", "Five A Company");
var delRow = customersTable.Select("CustomerID='PARIS'")[0];
delRow.Delete();
da.Update(nwSet, "Customers");
dataGridView2.DataSource = nwSet;
dataGridView2.DataMember = "Customers";
MessageBox.Show("Update Complete");
Guardando los cambios a la base de datos en lotes (Batches)
Todos los comandos: insertar individual, actualizar y eliminar se envían a SQL Server fila por fila.Una forma de aumentar el rendimiento de actualización para enviar los cambios al servidor en lotes mediante la propiedad UpdateBatchSize del DbDataAdapter.
Esta propiedad por defecto es 1, lo que hace que cada cambio que se envían al servidor en una fila por fila. Al establecer el valor 0 indica al DbDataAdapter objeto para crear el mayor tamaño del lote es posible para los cambios.
Una forma de confirmar que los cambios se envían al servidor de base de datos en lotes es registrar un controlador para el evento RowUpdated de la instancia del DbDataAdapter.
El método controlador de eventos recibe el número de filas afectadas en la última tanda.
Cuando UpdateBatchSize se establece en 1, la propiedad RecordsAffected es siempre 1.
En el siguiente ejemplo de código, la tabla Clientes contiene 91 filas. nwDataSet se llena, y luego el campo CustomerName se modifica en todas las 91 líneas. Antes de que el método de Update se ejecute, UpdateBatchSize se cambia a 40. Cuando el método de Update se ejecuta, los cambios se envían a la base de datos como un lote de 40 cambios, otro lote de 40 cambios, y, por último, un lote de 11 cambios. Este código contiene un controlador de eventos RowUpdated del SqlDataAdapter, para recoger información.
--------------------------------------VB------------------------------------------
Private WithEvents da As New SqlDataAdapter()
Private sb as New System.Text.StringBuilder()
private sub rowUpdated(byval sender as Object, _
byval e as SqlRowUpdatedEventArgs) handles da.RowUpdated
sb.Append("Rows: " & e.RecordsAffected.ToString() & vbCrLf)
End Sub
Private Sub menuUpdateBatch_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles UpdateBatchToolStripMenuItem.Click
Dim nw = ConfigurationManager.ConnectionStrings("nw")
Dim connection = New SqlConnection()
connection.ConnectionString = nw.ConnectionString
Dim cmd = connection.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM Customers"
Dim nwSet As New DataSet("nw")
da.SelectCommand = cmd
Dim bldr As New SqlCommandBuilder(da)
da.Fill(nwSet, "Customers")
For Each dr As DataRow In nwSet.Tables("Customers").Rows
dr("CompanyName") = dr("CompanyName").ToString().ToUpper()
Next
sb.Clear()
da.UpdateBatchSize = 40
da.Update(nwSet, "Customers")
MessageBox.Show(sb.ToString())
End Sub
--------------------------------------CS------------------------------------------
private SqlDataAdapter da = new SqlDataAdapter();
private System.Text.StringBuilder sb = new System.Text.StringBuilder();
private void rowUpdated(object sender, SqlRowUpdatedEventArgs e )
{
sb.Append("Rows: " + e.RecordsAffected.ToString() + "\r\n");
}
private void menuUpdateBatch_Click(object sender, EventArgs e)
{
da.RowUpdated += rowUpdated;
var nw = ConfigurationManager.ConnectionStrings["nw"];
var connection = new SqlConnection();
connection.ConnectionString = nw.ConnectionString;
Lesson 2: Reading and Writing Data CHAPTER 2 101
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM Customers";
da.SelectCommand = cmd;
var nwSet = new DataSet("nw");
var bldr = new SqlCommandBuilder(da);
da.Fill(nwSet, "Customers");
//Modificaciones
foreach (DataRow dr in nwSet.Tables["Customers"].Rows)
{
dr["CompanyName"] = dr["CompanyName"].ToString().ToUpper();
}
sb.Clear();
da.UpdateBatchSize = 40;
da.Update(nwSet, "Customers");
da.RowUpdated -= rowUpdated;
MessageBox.Show(sb.ToString());
}
Clases DbProviderFactory
Hay muchas razones para escribir una aplicación que no requiera de una base de datos específica. Con las versiones anteriores de ADO.NET, se puede escribir una aplicación independiente del proveedor mediante el uso de interfaces genéricas:
--------------------------------------VB------------------------------------------
'enumerado de proveedores
Public Enum DbProvider
SqlClient
OleDb
Odbc
End Enum
Public Function GetConnection() As IDbConnection
' obtengo el proveedor del archivo de configuracion
Dim provider As DbProvider = [Enum].Parse( _
GetType(DbProvider), _
ConfigurationSettings.AppSettings("provider").ToString())
Dim connection As IDbConnection = Nothing
Select Case (provider)
Case DbProvider.SqlClient
connection = New System.Data.SqlClient.SqlConnection()
Case DbProvider.OleDb
connection = New System.Data.OleDb.OleDbConnection()
Case DbProvider.Odbc
connection = New System.Data.Odbc.OdbcConnection()
Case DbProvider.Oracle
connection = New System.Data.OracleClient.OracleConnection()
End Select
Return connection
End Function
--------------------------------------CS------------------------------------------
public IDbConnection GetConnection()
{
// Get the provider from the config file
DbProvider provider = (DbProvider)Enum.Parse(
typeof(DbProvider),
(string)ConfigurationManager.AppSettings["provider"]);
IDbConnection connection = null;
switch (provider)
{
case DbProvider.SqlClient:
connection = new System.Data.SqlClient.SqlConnection();
break;
case DbProvider.OleDb:
connection = new System.Data.OleDb.OleDbConnection();
break;
case DbProvider.Odbc:
connection = new System.Data.Odbc.OdbcConnection();
break;
case DbProvider.Oracle:
connection = new System.Data.OracleClient.OracleConnection();
break;
}
return connection;
}
public enum DbProvider
{ SqlClient, OleDb, Odbc, Oracle };
Parte del App.config:
<configuration>
<appSettings>
<add key="provider" value="SqlClient" />
</appSettings>
</configuration>
ADO.NET ofrece clases de base desde la cual el proveedor de las clases específicas puede heredar, . NET Framework sólo admite herencia única, por lo que este enfoque tiene limitaciones si se desea crear una propia clase base, es mejor la implementación de interfaces.
Cada proveedor debe suministrar una subclase de DbProviderFactory que puede crear instancias de clases de su proveedor. Por ejemplo, puede utilizar SqlClientFactory para crear instancias de cualquiera de las clases de SQL Server:
![clip_image002[4]](http://lh3.ggpht.com/-jM-vUlkVKNY/TuEy1elY8mI/AAAAAAAAAx4/gk5I6QoXFT4/s1600-h/image10.png "clip_image002[4]")
Las clases de la DbProviderFactory se implementan como singleton, cada clase proporciona una propiedad de instancia que permite acceder a los métodos y propiedades. Por ejemplo, puede utilizar el código siguiente para crear una nueva conexión mediante el uso de la clase SqlClientFactory:
--------------------------------------VB------------------------------------------
'Obtengo la intancia singleton
Dim factory As DbProviderFactory = SqlClientFactory.Instance
Public Function GetProviderConnection() As DbConnection
Dim connection = factory.CreateConnection()
connection.ConnectionString = "Data Source=.\SQLEXPRESS;" _
& "AttachDbFilename=|DataDirectory|PUBS.MDF;" _
& "Integrated Security=True;User Instance=True"
Return connection
End Function
--------------------------------------CS------------------------------------------
//Get the singleton instance
DbProviderFactory factory = SqlClientFactory.Instance;
public DbConnection GetProviderConnection()
{
var connection = factory.CreateConnection();
connection.ConnectionString = @"Data Source=.\SQLEXPRESS;"
+ "AttachDbFilename=|DataDirectory|Northwind.MDF;"
+ "Integrated Security=True;User Instance=True";
return connection;
}
Utilizando DbException para detectar excepciones de Proveedores
Todos los proveedores heredan excepciones específicas de una clase base común llamada DbException. Usando el bloque try / catch se puede simplemente capturar la DbException genérica en vez de intentar la captura de cada una de las excepciones específicas del proveedor.
Trabajando con SQL Server User-Defined Types (UDTs)
Con SQL Server 2005 y versiones posteriores, puede utilizar Visual Basic 2010 o C #para escribir código que se ejecutará en el servidor SQL. El SQLCLR ofrece la posibilidad de crear tipos definidos por el usuario (UDT) que permiten objetos y estructuras de datos personalizados ser almacenados en una base de datos SQL Server. Los UDTs se pueden especificar como un tipo de columna en una definición de la tabla, que puede ser un tipo de variable en un lote de T-SQL, o pueden ser el tipo de un argumento de una función de T-SQL o un procedimiento almacenado. Esto requiere establecer una referencia al ensamblado que contiene el UDT registrado en SQL Server.
El ejemplo siguiente muestra la creación de una tabla llamada Locations en SQL Server. Antes de crear la tabla, se ejecuta un comando para ver si la tabla ya existe. Si la tabla existe, se borrar y luego se vuelve a crear.
--------------------------------------VB------------------------------------------
Dim nw = ConfigurationManager.ConnectionStrings("nw")
Dim nwConnection = New SqlConnection(nw.ConnectionString)
Dim cmd = nwConnection.CreateCommand()
cmd.CommandText =
"IF EXISTS (SELECT * FROM sys.Tables WHERE Name='Locations') " _
& " DROP TABLE Locations"
nwConnection.Open()
cmd.ExecuteNonQuery()
cmd.CommandText =
"CREATE TABLE Locations(" _
& " ZipCode char(5) PRIMARY KEY NOT NULL, Location Geography)"
cmd.ExecuteNonQuery()
nwConnection.Close()
--------------------------------------CS------------------------------------------
var nw = ConfigurationManager.ConnectionStrings["nw"];
var nwConnection = new SqlConnection(nw.ConnectionString);
var cmd = nwConnection.CreateCommand();
cmd.CommandText =
"IF EXISTS (SELECT * FROM sys.Tables WHERE Name='Locations') "
+ " DROP TABLE Locations";
nwConnection.Open();
cmd.ExecuteNonQuery();
cmd.CommandText =
"CREATE TABLE Locations("
+ " ZipCode char(5) PRIMARY KEY NOT NULL, Location Geography)";
cmd.ExecuteNonQuery();
nwConnection.Close();
Para insertar un objeto a la tabla recién creada, se debe agregar una referencia a Microsoft.SqlServer.Types.dll. Si se tiene SQL Server 2008 instalado, este conjunto se encuentra en la GAC (Global Assembly Cache). Debe buscar el assembly en la siguiente ubicación:
// en un sistema operativo de 64 bits
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies
// en un sistema operativo de 32 bits
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
Ejemplo de como agregar elementos a la tabla:
--------------------------------------VB------------------------------------------
'agregar la referencia a Microsoft.SqlServer.Types.dll
'Imports Microsoft.SqlServer.Types
Dim nw = ConfigurationManager.ConnectionStrings("nw")
Dim nwConnection = New SqlConnection(nw.ConnectionString)
Dim cmd = nwConnection.CreateCommand()
cmd.CommandText = "INSERT Locations VALUES(@zip, @loc)"
Dim zip = New SqlParameter("@zip", "14710") 'Ashville NY
Dim loc = New SqlParameter("@loc", SqlDbType.Udt)
loc.UdtTypeName = "geography"
loc.Value = SqlGeography.STGeomFromText( _
New SqlChars("POINT(42.1018 79.4144)"), 4326)
cmd.Parameters.Add(zip)
cmd.Parameters.Add(loc)
nwConnection.Open()
cmd.ExecuteNonQuery()
zip.Value = "44011"
loc.Value = SqlGeography.STGeomFromText( _
New SqlChars("POINT(41.4484 82.0190)"), 4326)
cmd.ExecuteNonQuery()
zip.Value = "60609"
loc.Value = SqlGeography.STGeomFromText( _
New SqlChars("POINT(41.8121 87.6542)"), 4326)
cmd.ExecuteNonQuery()
nwConnection.Close()
MessageBox.Show("Entries added")
--------------------------------------CS------------------------------------------
//using Microsoft.SqlServer.Types;
//using System.Data.SqlTypes;
var nw = ConfigurationManager.ConnectionStrings["nw"];
var nwConnection = new SqlConnection(nw.ConnectionString);
var cmd = nwConnection.CreateCommand();
cmd.CommandText = "INSERT Locations VALUES(@zip, @loc)";
var zip = new SqlParameter("@zip", "14710"); // Ashville NY
var loc = new SqlParameter("@loc", SqlDbType.Udt);
loc.UdtTypeName = "geography";
loc.Value = SqlGeography.STGeomFromText(
new SqlChars("POINT(42.1018 79.4144)"), 4326);
cmd.Parameters.Add(zip);
cmd.Parameters.Add(loc);
nwConnection.Open();
cmd.ExecuteNonQuery();
zip.Value = "44011";
loc.Value = SqlGeography.STGeomFromText(
new SqlChars("POINT(41.4484 82.0190)"), 4326);
cmd.ExecuteNonQuery();
zip.Value = "60609";
loc.Value = SqlGeography.STGeomFromText(
new SqlChars("POINT(41.8121 87.6542)"), 4326);
cmd.ExecuteNonQuery();
nwConnection.Close();
Resumen de la lección
Esta lección proporciona una descripción detallada de la lectura y escritura de datos.
■ SQL Express es un servidor de base de datos excelente para el desarrollo porque el archivo de base de datos. Mdf se puede colocar en el proyecto y el archivo puede ser configurado para copiar a la carpeta de salida cada vez que se construye la aplicación y ejecutarla.
■ Utilice el objeto DbCommand para enviar un comando SQL a un almacén de datos.También se pueden crear parámetros y pasarlos al objeto DbCommand.
■ El objeto DbDataReader proporciona un método de alto rendimiento de recuperación de datos de un almacén de datos mediante la entrega de un sólo avance, de sólo lectura,cursor de servidor.
■ El objeto SqlBulkCopy puede copiar datos desde varias fuentes a una tabla de SQL Server.
■ Puede utilizar el objeto DbDataAdapter para recuperar y actualizar datos entre una tabla de datos y un almacén de datos. DbDataAdapter puede contener una solapropiedad SelectCommand de datos de sólo lectura, o puede contener SelectCommand, InsertCommand, UpdateCommand y DeleteCommand de datos totalmente actualizable.
■ El objeto DbProviderFactory ayuda a crear código independiente del proveedor, que
puede ser necesario cuando el almacén de datos necesita ser modificados rápidamente.
No hay comentarios:
Publicar un comentario