miércoles, 18 de junio de 2014

ADO.NET, query, string, execute, command



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();
    }
}