Código C# - SQLTips y Trucos 

Ejecutar un procedimiento almacenado de SQL desde C#

Con el siguiente código podemos ejecutar un procedimiento almacenado pasándole sus parámeros y recibiendo los resultados en una variable.

Supongamos que tenemos un procedimiento almacenado llamado "getXML", que recibe como parámetro un valor Id de tipo int llamado "@Id" y devuelve un dato de tipo varchar llamado "@XML".

CREATE PROCEDURE [dbo].[getXML]@Id INT, @XML VARCHAR(8000) OUTPUT AS BEGIN .........END

El código en C# para pasarle los parámetros necesarios, ejecutarlo y recibir el resultado sería el siguiente.

using System.Data.SqlClient;using System.Data;

static public string getStringXML(int _id){string _XML = string.Empty;

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ToString());conn.Open();SqlCommand Query = new SqlCommand("getXML", conn);Query.CommandType = CommandType.StoredProcedure;

Query.Parameters.Add("@Id", SqlDbType.Int, 4);Query.Parameters["@Id"].Value = _id;

Query.Parameters.Add("@XML", SqlDbType.VarChar, 8000);Query.Parameters["@XML"].Direction = ParameterDirection.Output;

Query.ExecuteNonQuery();conn.Close();_XML = Query.Parameters["@XML"].Value.ToString();return _XML;}

Otro caso sería ejecutar un procedimiento almacenado que realiza una consulta y devolver un DataSet. Para poder lograr lo anterior creamos un stored procedure llamado [showData] que realiza un select a una tabla:

CREATE PROCEDURE [dbo].[showData]AS BEGIN 

SELECT * FROM TableNameEND

Posteriormente para llamar al procedimiento y devolver un DataSet hacemos la siguiente función en .Net

static public DataSet showData(){DataSet db = new DataSet();

try{SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionName"].ToString());conn.Open();SqlCommand Query = new SqlCommand("showData", conn);Query.CommandType = CommandType.StoredProcedure;SqlDataAdapter SqlDa = new SqlDataAdapter(Query);SqlDa.Fill(db);conn.Close();

}catch { db = null; }

return db;}

Un caso más sería ejecutar un procedimiento almacenado que realiza una consulta y asignar el resultado a un DataReader y posteriormente leerlo, para ello creamos un procedimiento almacenado llamado [getData]:

CREATE PROCEDURE [dbo].[getData]@Type INTAS BEGIN SELECT Id, Campo,Type FROM TableName WHERE Type=@TypeEND

Posteriormente para llamar al procedimiento, ejecutar el DataReader, obtener los datos y asignarlos a un ArrayList y devolver  un arreglo  hacemos la siguiente función en .Net

static public string[] getTypeData(int _idType){ArrayList List = new ArrayList();

try{SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionName"].ToString());conn.Open();

SqlCommand Query = new SqlCommand("getData", conn);Query.CommandType = CommandType.StoredProcedure;Query.Parameters.Add("@Type", SqlDbType.Int);Query.Parameters["@Type"].Value = _idType; 

SqlDataReader reader = Query.ExecuteReader();

while (reader.Read())

{List.Add(reader["Campo"].ToString());}

rveader.Close();conn.Close();}catch { List = null; }

return (string[])List.ToArray(typeof(string));}

IntroduccionEn mi publicacion anterior explique  como crear un procedimiento almacenado con SQL server 2008 express edition y ejecutarlo desde el SQL analyzer, ahora me gustaria mostrar como ejecutar un procedimiento almacenado desde una aplicacion de escritorio desarrollada en Visual Studio 2010 y C#.Para este articulo utilizare el procedimiento almacenado del articulo anterior como ejemplo para esta practica, si no sabes como crear un Stored Procedure o Procedmiento almacenado puedes ver lo dando clic sobre este linkEl primer paso es crear un proyecto WindowsForm , el cual nombraremos “ExecutingStoredProcedure” en Visual Studio 2010 y usando como lenguaje de programacion C#, en caso de que no sepas como crear un proyecto en VS2010 puedes  ver lo dando clic sobre este link, dichas instrucciones trabajan bien con los frameworks 2.0, 3.0 y 4.0 por lo que no deberian tener problemas al trabajar con versiones antiguas del del Visual Studio 2005 o 2008.Despues de tener listo nuestro proyecto de WindowsForm agregaremos los siguientes controles : 1 listView con las siguientes dimensiones: 386, 168.2 Botones con las siguientes dimenciones: 75, 4.Despues de insertar nues tros controles, agreagaremos dos culumnas a nuestro listView las cuales llamaremos “Usuario” y “Contraseña” estos campos pertenecen a una tabla llamada “Table_Users” la cual fue creada previamente para este ejercicio, dicha tabla contiene el registro de unos ususarios y sus respectivas contraseñas como se muestra en la figura 1.0.

  Figura 1.0.Luego de haber agregado nuestras columnas en nuesttro listView en donde mostraremos el contenido de la tabla, cambiaremos el texto de nuestros botones por “Query” y “Stored Procedure” respectivamente como se muestra en la figura 2.0.

Figura 2.0.

CodificacionAhora empezaremos a modelar la logica de negocios de nuestra aplicacion.El siguiente codigo realiza una consulta de la Tabla_Users y despues lo vacia al listView para que el usuario pueda verlo.con = new SqlConnection(@”Data Source = HOME-A30C6C1EFD\SQLEXPRESS; Initial Catalog = Test; Integrated Security = SSPI;”);con.Open();query = “SELECT * FROM Table_Users”;cmd = new SqlCommand(query, con);dr = cmd.ExecuteReader();while(dr.Read()){//Este renglon te permite insertar cada uno de los datos obtenidos de la consulta en la “Usuario”value = listView1.Items.Add(dr.GetValue(0).ToString());//Este renglon te permite insertar cada uno de los datos obtenidos de la consulta en la “Contraseña”value.SubItems.Add(dr.GetValue(1).ToString());}con.Close();Ahora veremos como se como realizar la misma operacion pero esta vez  ejecutando un procedimiento almacenado “MiPrimerStoredProcedure”.con = new SqlConnection(@”Data Source = HOME-A30C6C1EFD\SQLEXPRESS; Initial Catalog = Test; Integrated Security = SSPI;”);con.Open();// crear un objecto tipo command  para identificar// el procedimiento almacenado en esta caso ingresamos nombre del procedimiento en lugar de un query.cmd = new SqlCommand(“MiPrimerStoredProcedure”,con);// Indicamos al sistema el tipo de comando a ejecutar en este caso nuestro procedimiento almacenado// Por dafault el metodo SqlCommand() recibe un query y por eso es necesario definirlocmd.CommandType = CommandType.StoredProcedure;// execute the commanddr = cmd.ExecuteReader();while (dr.Read()){value = listView1.Items.Add(dr.GetValue(0).ToString());value.SubItems.Add(dr.GetValue(1).ToString());}con.Close();

Como podran observar en ambos segmentos de codigo solo varian en una linea de codigo lo cual nos hace questionar de que nos sirve este tipo de funciones que nos da SQL si no vemos reduccion de codigo?, como recordaran un procedimiento almacenado es una funcion o rutina el cual puede ejecutar uno o mas queries lo que nos da mucha ventaja al momento de ejecutarlos ya que no tendremos que escribir cada una de las funciones para nuestras consultas en lo cual nos ahorra tiempo en este tipo de  tareas.A continuacion mostrare el codigo completo:using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;namespace ExecutingStoredProcedure{public partial class Form1 : Form{public Form1(){InitializeComponent();}string query;string stored_procedure = “MiPrimerStoredProcedure”;//Esta variable almacena el nombre del stored procedure que vamos a ejecutarSqlConnection con;SqlCommand cmd;SqlDataReader dr;ListViewItem value;void Query_Select(){con = new SqlConnection(@”Data Source = HOME-A30C6C1EFD\SQLEXPRESS; Initial Catalog = Test; Integrated Security = SSPI;”);con.Open();query = “SELECT * FROM Table_Users”;cmd = new SqlCommand(query, con);dr = cmd.ExecuteReader();while(dr.Read()){//Este renglon te permite insertar cada uno de los datos obtenidos de la consulta en la “Usuario en nuestro listView en su respectiva columna”value = listView1.Items.Add(dr.GetValue(0).ToString());//Este renglon te permite insertar cada uno de los datos obtenidos de la consulta en la “Contraseña en nuestro listView en su respectiva columna”value.SubItems.Add(dr.GetValue(1).ToString());}con.Close();

}void Stored_Procedure(){con = new SqlConnection(@”Data Source = HOME-A30C6C1EFD\SQLEXPRESS; Initial Catalog = Test; Integrated Security = SSPI;”);con.Open();// 1. create a command object identifying// the stored procedurecmd = new SqlCommand(stored_procedure,con);// 2. set the command object so it knows// to execute a stored procedurecmd.CommandType = CommandType.StoredProcedure;// execute the commanddr = cmd.ExecuteReader();// iterate through results, printing each to Listviewwhile (dr.Read()){value = listView1.Items.Add(dr.GetValue(0).ToString());value.SubItems.Add(dr.GetValue(1).ToString());}con.Close();}private void button1_Click(object sender, EventArgs e)// Boton “Query”{listView1.Items.Clear();Query_Select();}private void button2_Click(object sender, EventArgs e)// Boton “Stored Procedure”{listView1.Items.Clear();Stored_Procedure();}}}Por ultimo solo presionaremos el boton F5 y a continuacion el sistema mostrara nuestra aplicacion como se muestra en la figura 3.0.

Figura 3.0.En este punto solo debemos dar click a cualquiera de nuestros botones para ejecutar nuestra consulta y acontinuacion obtener nuestros registros como seve en la figura 4.0.

Figura 4.0.

ConclusionesEspero que este articulo les sea de utilidad y de su agrado y por mi parte considero este un tema esencial para cualquier programador ya debe conocer,el como invocar procedimientos almacenados desde sus interfaces o aplicaciones, y me gustaria agregar que esta semana tratare este mismo tema pero con el uso de parametros de entrada lo caul es de gran utildad al momento de realizar consultas refinadas, insercion de un nuevo registro y eliminacion de datos especificos.

Creando la Base de Datoshttp://hwongu.blogspot.com/2011/02/c-aplicacion-de-escritorio-sistemas-de.htmlCreando la capa de datoshttp://hwongu.blogspot.com/2011/02/c-aplicacion-de-escritorio-sistemas-de_22.htmlCreando la capa de negocioshttp://hwongu.blogspot.com/2011/02/c-aplicacion-de-escritorio-sistemas-de_5596.htmlCreando la capa de presentacionhttp://hwongu.blogspot.com/2011/02/c-aplicacion-de-escritorio-sistemas-de_23.htmlCreando los reportes en crystal reporthttp://hwongu.blogspot.com/2011/02/c-aplicacion-de-escritorio-sistemas-de_5944.html

http://desdeceronetsql2.wordpress.com/2012/10/25/recuperar-1-valor-devuelto-de-procedimiento-almacenado-en-c/

http://geeks.ms/blogs/quintas/archive/2007/07/13/parameters-addwithvalue-with-cuidadito.aspx

Primeramente, voy a crearme una tabla de SQL Server de clientes:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[cliente](     [idCliente] [bigint] IDENTITY(1,1) NOT NULL,     [nombres] [nchar](50) COLLATE Modern_Spanish_CI_AS NULL,     [apellidos] [nchar](50) COLLATE Modern_Spanish_CI_AS NULL,

     [direccion] [nchar](50) COLLATE Modern_Spanish_CI_AS NULL,     [email] [nchar](100) COLLATE Modern_Spanish_CI_AS NULL, CONSTRAINT [PK_cliente] PRIMARY KEY CLUSTERED(     [idCliente] ASC)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

Luego, creamos algunos datos en la tabla (ese trabajito les dejo como tarea).Ahora, voy a crear un procedimiento almacenado que inserta datos en mi tabla de clientes:

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[InsertarCliente]           @nombres nchar(50),      @apellidos nchar(50),      @direccion nchar(50),      @email nchar(100)AS      Insert into cliente values(@nombres,@apellidos,@direccion,@email)

Finalmente, en C# hacemos una llamada al procedimiento almacenado.El código sería el siguiente:

private void button4_Click(object sender, EventArgs e)

        {        

            try

            {

                string ConnectionString = ”Data Source=.\SQLEXPRESS;Initial

Catalog=MiBD;Integrated Security=True”;

                SqlConnection cnn = new SqlConnection(ConnectionString);

                SqlCommand cmd = new SqlCommand("InsertarCliente", cnn);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@nombres", SqlDbType.Char, 50);

                cmd.Parameters.Add("@apellidos", SqlDbType.Char, 50);

                cmd.Parameters.Add("@direccion", SqlDbType.Char, 50);

                cmd.Parameters.Add("@email", SqlDbType.Char, 100);

                cmd.Parameters["@nombres"].Value = textBox2.Text;

                cmd.Parameters["@apellidos"].Value = textBox3.Text;

                cmd.Parameters["@direccion"].Value = textBox4.Text;

                cmd.Parameters["@email"].Value = textBox5.Text;

                cnn.Open();

                cmd.ExecuteNonQuery();

                MessageBox.Show("Los datos fueron insertados correctamente");

                cnn.Close();

                this.button4.Visible = false;

                this.button1.Visible = true;

                CargarClientes();

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.ToString());

            }

}