ADO .NET
EL NUEVO MODELO DE DATOS DE MICROSOFT ES ADO NET,
ESTE MODELO DESCANSA EN UNA SERIE DE OBJETOS ESPECIALIZADOS QUE FACILITAN EL
PROCESAMIENTO DE UNA BASE DE DATOS.
El problema es comunicar un programa o aplicación
aspx con una base de datos y más que comunicar se pretende que el programa o
aplicación realice una serie de procesos u operaciones con la base de datos o
mejor aún con el conjunto de tablas que contiene una base de datos.
La primera nota a recordar es que una base de datos
puede estar físicamente en el servidor y en algún folder o directorio del disco
duro de dicha maquina servidora, por ejemplo, c:\progfacil\misitio\mibase.mbd,
como se observa la base que se construyó en access (mibase.mbd) se almaceno en
el disco c en el folder progfacil y dentro del subfolder misitio.
Sin embargo también es necesario conocer que así
como existen servidores de páginas (web server), servidores de correo (mail
server), servidores de ftp (ftp server), etc, también existen servidores de
bases de datos (database server), los más comunes son el sqlserver de
microsoft, oracle, mysql, etc, estos servidores también pueden crear,
administrar y procesar una base de datos.
El modo de comunicarse entre nuestro programa o
aplicación aspx y la base de datos (ya sea física o un dbserver), implica que
ambos manejen un lenguaje de programación común, es decir no se puede mandar
una instrucción en csharp, o en basic o pascal a la base de datos y además
esperar que esta ultima la entienda ( para entender esto, una razón muy sencilla
es que la base de datos tendría que conocer o comprender todos los lenguajes de
programación), para resolver este problema de comunicación es que se usa un
lenguaje común de bases de datos que tanto los lenguajes de programación
existentes como las bases de datos entienden, este lenguaje común de bases de
datos es el SQL (structured query languaje) o lenguaje estructurado de
consultas.
En otras palabras, aprender este nuevo lenguaje de
programación el SQL, la buena noticia es que es un lenguaje con muy pocas.
Las principales instrucciones de SQL, que se usan
en este curso son SELECT, INSERT, UPDATE y DELETE.
La pregunta es ahora como mandamos las instrucciones
sql a la base de datos, la respuesta son los
OBJETOS ADO.NET que estamos
analizando en orden y propósito de uso, los estaremos explicando.
OBJETO CONNECTION:- OBJETO QUE SE UTILIZA PARA
ESTABLECER UNA CONECCION O ENLACE A LA BASE DE DATOS.
Este objeto primero se tendrá que crear en el
programa y luego se tendrá que cargar con dos parámetros, el primer parámetro
es el proveedor o la fuente que proporcionara los datos, los proveedores o
fuentes de datos que existen son:
SQLSERVER NET DATA PROVIDER.- QUE SE ESPECIALIZA EN
COMUNICARSE Y PROCESAR BASES DE DATOS CONSTRUIDAS CON MICROSOF SQL SERVER V7.0
OLEDB.NET DATA PROVIDER.- QUE SE ESPECIALIZA EN
COMUNICARSE Y PROCESAR BASES DE DATOS QUE A LA FECHA DEL PRESENTE LIBRO
UTILIZEN ALGUNOS DE LOS SIGUIENTES DRIVERS, SQLOLEDB ( VERSIONES ANTERIORES DE SQL
SERVER DE MICROSOFT), MSDAORA (ORACLE), MICROSOFT.JET ( ACCESS Y ALGUNOS OTROS
DBMS DE MICROSOFT)
ODBC.NET .- BASES DE DATOS QUE USAN ODBC COMO MEDIO
DE COMUNICACION CON OTRAS BASES DE DATOS Y APLICACIONES COMO NOTA A CONSIDERAR
ODBC.NET NO ESTA INCLUIDA POR DEFAULT EN MICROSOFT.NET, SE TIENE QUE BAJAR DE
MICROSOFT.
El segundo parámetro es la propia base de datos con
la cual se comunicara el programa o aplicación.
Ejemplo del objeto CONNECTION
Static OleDbConnection
coneccion;
coneccion = new
OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\progfacil\\tusitio\\mibase.mdb”);
Es una sola string y los dos parámetros mencionados
van separados por el punto y coma.
Ejemplos de los otros proveedores o fuentes
mencionados:
//Provider=MSDAORA; Data Source=ORACLE8i7; User
ID=OLEDB; Password=OLEDB
//Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\bin\LocalAccess40.mdb;
//Provider=SQLOLEDB;Data
Source=MySQLServer;Integrated Security=SSPI;
·
OBJETO COMMAND
Ya establecido el canal o enlace entre el programa
aspx y la base de datos vía el objeto CONECCION, se debe mandar la instrucción SQL
a la propia base de datos, sin embargo en un programa de csharp por supuesto
que no puede contener instrucciones de otros lenguajes de programación como el
de SQL, es por esto que se deberán usar algunos de los otros objetos de ADO.NET
para que estos objetos transporten la instrucción sql hacia la base de datos (y
transporte de regreso al servidor los datos de alguna tabla), uno de estos
objetos es el objeto COMMAND.
Este objeto puede contener directamente una
instrucción SQL y enviarla al objeto conexión ya descrito.
Este objeto command primero se tendrá que crear y
luego cargarle dos parámetros que son:
La instrucción sql y el objeto conneccion que ya se
vio en el párrafo anterior. Ejemplo
OleDbCommand orden;
orden= new
OleDbCommand(“select * from mitabla”, coneccion);
Si está muy grande o muy compleja la instrucción
sql, es más conveniente crearla en una variable string y poner la variable como
parametro ejemplo:
OleDbCommand orden;
String q=“select * from
mitabla”;
orden= new OleDbCommand(q,
coneccion);
Sin embargo ciertas instrucciones de sql, requieren
que se manden los datos a la base de datos, respetando el tipo de dato con los
cuales los creo el software de bases de datos, por ejemplo si edad en access se
declaró como NUMBER, la instrucción sql que pretenda cargar dicho campo, tiene
la obligación de mandarla con este tipo de dato asociado, instrucciones SQL que
permiten cargar o capturar ese campo edad son INSERT o UPADTE.
Para resolver este problema, usaremos en la string
q, unas variables especiales llamadas VARIABLES PARAMETROS que se simbolizan
usando el símbolo @ antes de la variable y además al objeto COMMAND le
agregamos dos instrucciones extras que permiten agregar a la string q el dato y
el tipo de dato, ejemplo, se tienen seis renglones ya capturados en nuestra
tabla y se quiere agregar un séptimo renglón con los siguientes datos, clave=7,
nombre=“rana” peso=3.14 usaremos una instrucción SQL INSERT ej.:
OleDbCommand orden;
String clave=7;string
nombre=“rana”; string peso=3.14;
string q=“insert into mitabla(clave,nombre,peso)
values(@CLAVE, @NOMBRE, @PESO)”;
orden= new OleDbCommand(q,
coneccion);
orden.Parameters.Add(new
OleDbParameter(”@CLAVE”, OleDbType.Integer));
orden.Parameters[”@CLAVE”].Value = clave;
orden.Parameters.Add(new
OleDbParameter(”@NOMBRE”, OleDbType.VarWChar, 40));
orden.Parameters[”@NOMBRE”].Value = nombre;
orden.Parameters.Add(new
OleDbParameter(”@PESO”, OleDbType.Double));
orden.Parameters[”@PESO”].Value = edad;
Observar que para cada variable parámetro, se
tienen que cargar dos elementos, el valor y el tipo de dato correspondiente.
Aunque en valor se manda string's en oledbtype se
hace un mapeo, relación o conversión al tipo de dato que se usó en access,
tener mucho cuidado que exista una relación igual o cuando este programa se
ejecute el servidor les va a mandar un error o excepción de sql que les intenta
decir que el tipo de dato que mandaron a la base de datos, no es igual al que
se usó para crearlo en la base de datos.
Los OLEDBTYPE más comunes son:
BigInt A 64-bit signed integer (DBTYPE_I8). This maps to
Int64.
Binary A stream of binary data (DBTYPE_BYTES). This maps
to an Array of type Byte.
Boolean A Boolean value (DBTYPE_BOOL). This maps to
Boolean.
BSTR A null-terminated character string of Unicode characters
(DBTYPE_BSTR). This maps to String.
Char A character string (DBTYPE_STR). This maps to
String.
Currency A currency value ranging from -263 (or
-922,337,203,685,477.5808) to 2 63 -1 (or +922,337,203,685,477.5807)
with an accuracy to a ten-thousandth of a currency unit (DBTYPE_CY). This maps
to Decimal.
Date Date data, stored as a double (DBTYPE_DATE). The
whole portion is the number of days since December 30, 1899, while the
fractional portion is a fraction of a day. This maps to DateTime.
DBDate Date data in the format yyyymmdd (DBTYPE_DBDATE).
This maps to DateTime.
DBTime Time data in the format hhmmss (DBTYPE_DBTIME). This maps to
TimeSpan.
DBTimeStamp Data and time data in the format yyyymmddhhmmss
(DBTYPE_DBTIMESTAMP). This maps to DateTime.
Decimal A fixed precision and scale numeric value between
-1038 -1 and 10 38 -1 (DBTYPE_DECIMAL). This maps to
Decimal.
Double A floating point number within the range of -1.79E
+308 through 1.79E +308 (DBTYPE_R8). This maps to Double.
Empty No value (DBTYPE_EMPTY). This maps to Empty.
Error A 32-bit error code (DBTYPE_ERROR). This maps to Exception.
Filetime A 64-bit unsigned integer representing the number
of 100-nanosecond intervals since January 1, 1601 (DBTYPE_FILETIME). This maps
to DateTime.
Guid A globally unique identifier (or GUID)
(DBTYPE_GUID). This maps to Guid.
IDispatch A pointer to an IDispatch interface
(DBTYPE_IDISPATCH). This maps to Object. Note This data type is not currently
supported by ADO.NET. Usage may cause unpredictable results.
Integer A 32-bit signed integer (DBTYPE_I4). This maps to
Int32.
IUnknown A pointer to an IUnknown interface
(DBTYPE_UNKNOWN). This maps to Object. Note This data type is not currently
supported by ADO.NET. Usage may cause unpredictable results.
LongVarBinary A long binary value (OleDbParameter only). This
maps to an Array of type Byte.
LongVarChar A long string value (OleDbParameter only). This
maps to String.
LongVarWChar A long null-terminated Unicode string value
(OleDbParameter only). This maps to String. Numeric An exact numeric
value with a fixed precision and scale (DBTYPE_NUMERIC). This maps to Decimal. PropVariant
An automation PROPVARIANT (DBTYPE_PROP_VARIANT). This maps to Object.
Single A floating point number within the range of -3.40E
+38 through 3.40E +38 (DBTYPE_R4). This maps to Single.
SmallInt A 16-bit signed integer (DBTYPE_I2). This maps to
Int16.
TinyInt A 8-bit signed integer (DBTYPE_I1). This maps to
SByte.
UnsignedBigInt A 64-bit unsigned integer (DBTYPE_UI8). This maps
to UInt64.
UnsignedInt A 32-bit unsigned integer (DBTYPE_UI4). This maps
to UInt32.
UnsignedSmallInt A 16-bit unsigned integer (DBTYPE_UI2). This maps
to UInt16.
UnsignedTinyInt A 8-bit unsigned integer (DBTYPE_UI1). This maps
to Byte.
VarBinary A variable-length stream of binary data
(OleDbParameter only). This maps to an Array of type Byte. VarChar A
variable-length stream of non-Unicode characters (OleDbParameter only). This maps
to String.
Variant A special data type that can contain numeric,
string, binary, or date data, as well as the special values Empty and Null
(DBTYPE_VARIANT). This type is assumed if no other is specified. This maps to
Object.
VarNumeric A variable-length numeric value (OleDbParameter
only). This maps to Decimal.
VarWChar A variable-length, null-terminated stream of
Unicode characters (OleDbParameter only). This maps to String.
WChar A null-terminated stream of Unicode characters
(DBTYPE_WSTR). This maps to String.
Fuente:microsoft.net
Aun mas, con el ejemplo anterior el objeto COMMAND está
construido y preparado y cargado pero todavía no se manda desde el programa a
la base de datos, es decir le faltan activar las siguientes tres propiedades,
ejemplo;
OleDbCommand orden;
String clave=7;string
nombre=“rana”;string peso=3.14;
string q=“insert into mitabla(clave,nombre,peso)
values(@CLAVE, @NOMBRE, @PESO)”;
orden= new OleDbCommand(q,
coneccion);
orden.Parameters.Add(new
OleDbParameter(”@CLAVE”, OleDbType.Integer));
orden.Parameters[”@CLAVE”].Value = clave;
orden.Parameters.Add(new
OleDbParameter(”@NOMBRE”, OleDbType.VarWChar, 40));
orden.Parameters[”@NOMBRE”].Value = nombre;
orden.Parameters.Add(new
OleDbParameter(”@PESO”, OleDbType.Double));
orden.Parameters[”@PESO”].Value = edad;
orden.Connection.Open();
orden.ExecuteNonQuery();
orden.Connection.Close()
Sencillo abrir la conexión, mandar o ejecutar la
instrucción y cerrar la conexión.
OBJETOS DATAADAPTER Y DATASET:(dos por uno)
Son los otros dos objetos de ADO.NET que también
permiten transportar una instrucción sql desde el servidor hasta la base de
datos y transportar de regreso hacia el servidor los datos contenidos en alguna
de las tablas.
Con los objetos CONNECTION y COMMAND ya se pueden
efectuar cualquiera de la operaciones SQL descritas (ya estudiaron su tutorial
de SQL), el problema es que pasa con el usuario cuando va a ver base de datos o
mejor aún las tablas que están en la base de datos en disco.
DATASET:- Es una copia en memoria (d la maquina cliente)
de la base de datos (y todas sus tablas) que se encuentra en disco.
DATAADAPTER.- En principio es muy similar al objeto COMMAND es
decir se usa para transportar instrucciones SQL a la base en disco, de hechos
sus formatos e instrucciones son muy similares a los vistos para el objeto
COMMAND, su diferencia principal es que dataadapter está más especializado y
contiene una serie de métodos que facilitan la interacción entre el
DATASET y
la Base de Datos en disco
En particular muchos de los programas que se verán
en temas posteriores solo usan los objetos
CONNECTION, DATAADAPTER y DATASET.
Otra vez, dataadpater se especializa en transportar
LA INSTRUCCION SQL SELECT a la base de datos en disco pero además se utiliza
para cargar la tabla en memoria o dataset del cliente.
Ejemplo:
// abriendo la
coneccion
coneccion = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\progfacil\\tusitio\\mibase.mdb");
// cargando el
adapter con la instruccion sql
canal=new OleDbDataAdapter("select * from
mitabla", coneccion);
// cargando el
dataset
tabla= new
DataSet();
canal.Fill(tabla,
"mitabla");
Como se observa en este ejemplo muy sencillo, el
dataadapter(canal) está funcionando de manera muy similar al primer ejemplo que
se vio del objeto COMMAND pero tengan la seguridad que también se pueden usar
variables parámetros y agregarles los dos tipos de parámetros a este objeto
dataadpater.
Observar que su propiedad FILL carga el
DATASET(tabla) con una de las tablas en disco, recordar que en la base de datos
puede contener muchas tablas.
Además esa propiedad FILL es equivalente a las tres
últimas instrucciones del objeto COMMAND, es decir open, executenonquery y
close, mas fácil verdad.
DATAREADER y DATASET:
Observar que también se usan en forma conjunta,
primero es muy similar en uso y función que el objeto DATAADAPATER, la
diferencia entre datareader y dataadapter es el tipo de base de datos con las
cuales se pueden comunicar, dataadpater se especializan en bases de datos
relacionales y datareader se especializa en archivos, que no se estudian en
este curso.
También es importante mencionar que datareader es
el objeto de ADO.NET más parecido al objeto RESULTSET que uso mucho en el ADO
anterior de microsoft.
EN general se han visto de manera sencilla los
principales objetos ADO.ASP( connection, command, datareader, dataadapter,
dataset), sin embargo la tabla o las tablas o la base de datos que se tiene en
disco o sirviéndola algún servidor de bases de datos, se ha quedado en la
memoria de la máquina del cliente, ADO.NET ha terminado su trabajo y su
función.
Para mandar el dataset al browser se tendrá que
pasar a algún tipo de objeto visible que soporte el browser, los objetos que se
pueden usar para mandar el dataset a pantalla son:
1.- COMPONENTE TABLE DE HTML (USADO EN EL CURSO DE
C SHARP CGI DE PROGRAMACIONFACIL.COM)
2.- COMPONENTE HTMLTABLE DE ASP
3.- COMPONENTE DATAGRID DE ASP NET (USADO EN EL
CURSO DE C SHARPNET DE PROGRAMACIONFACIL.COM)
·
Query
Obtener datos con SQL consulta de selección en ADO.Net
Primera importar los siguientes espacios de nombres. Ahora
definir la cadena de conexión en el Web.Config
using System.Data;
using System.Data.SqlClient;
Now define your Connection
string in the Web.Config
<connectionStrings>
<add name=“conString“
connectionString=“DataSource=.\SQLEXPRESS;Database=Northwind;Integrated
Security=true“/>
</connectionStrings>
Aquí está la función
public DataTable GetData(SqlCommand
cmd)
{
DataTable
dt = new DataTable();
String
strConnString
=System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection
con = new SqlConnection(strConnString);
cmd.Connection = con;
SqlDataAdapter
sda = new SqlDataAdapter();
cmd.CommandType = CommandType.Text;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
}
catch
{
return dt;
}
finally
{
con.Close();
cmd.Dispose();
sda.Dispose();
}
return dt;
}
Para llamar a la función anterior sin ningún parámetro
protected void
Button1_Click(object sender, EventArgs e)
{
String
strQuery = “SELECT * FROM Customers”;
SqlCommand
cmd = new SqlCommand(strQuery);
DataTable dt
= GetData(cmd);
}
Y ahora para llamar a la consulta
anterior usando los parámetros. Que
necesito sólo aquellos registros que
han IdCliente como ALFKI. Aquí estoy
utilizando consultas con parámetros para que SQL Inyecciones pueden evitarse.
Así que la función se llama de esta manera.
protected void
Button1_Click(object sender, EventArgs e)
{
String
strQuery = “SELECT * FROM Customers WHERE CustomerID=@CustomerID”;
SqlCommand
cmd = new SqlCommand(strQuery);
cmd.Parameters.AddWithValue(“@CustomerID”,
“ALFKI”);
DataTable dt = GetData(cmd);
}
Puede enlazar el DataTable devuelto a ningún control DataBound como GridView,
DataGrid, DropDownList y otros de la siguiente manera
DataTable dt =
GetData(cmd);
GridView1.DataSource = dt;
GridView1.DataBind();
·
Execute
Ejecutar un comando (ADO.NET)
|
Comando
|
Devolver valor
|
|
ExecuteReader
|
Devuelve un objeto DataReader.
|
|
ExecuteScalar
|
Devuelve un solo valor escalar.
|
|
ExecuteNonQuery
|
Ejecuta un comando que no devuelve ninguna fila.
|
|
ExecuteXMLReader
|
Devuelve un valor XmlReader.
Solo está disponible para un objeto SqlCommand.
|
Cada objeto command fuertemente tipado admite
también una enumeración CommandType
que especifica cómo se interpreta una cadena de comando, tal como se describe
en la tabla siguiente.
|
CommandType
|
Descripción
|
|
Text
|
Comando de
SQL que define las instrucciones que se van a ejecutar en el origen de datos.
|
|
StoredProcedure
|
Nombre del
procedimiento almacenado. Puede usar la propiedad Parameters de un comando para
tener acceso a los parámetros de entrada y de salida y a los valores
devueltos, independientemente del método Execute al que se llame.
Al usar ExecuteReader, no es posible
el acceso a los valores devueltos y los parámetros de salida hasta que se
cierra DataReader.
|
|
TableDirect
|
Nombre de
una tabla.
|
Ejemplo
En el
ejemplo de código siguiente se muestra cómo se crea un objeto SqlCommand
para ejecutar un procedimiento almacenado mediante el establecimiento de sus
propiedades. Para especificar el parámetro de entrada del
procedimiento almacenado se usa un objeto SqlParameter.
El comando se ejecuta con el método ExecuteReader
y el resultado de SqlDataReader
se muestra en la ventana de consola.
C#
static void GetSalesByCategory(string connectionString,
string categoryName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create the command and set its properties.
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SalesByCategory";
command.CommandType = CommandType.StoredProcedure;
// Add the input parameter and set its properties.
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@CategoryName";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = categoryName;
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
// Open the connection and execute the reader.
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
}
}
else
{
Console.WriteLine("No rows found.");
}
reader.Close();
}
}