Last active
March 30, 2018 12:38
-
-
Save Fhernd/d2b6adf129e4f65bc8585530c57f4d07 to your computer and use it in GitHub Desktop.
Parametrización de comando SQL y procedimiento almacenado. OrtizOL.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Data; | |
using System.Data.SqlClient; | |
namespace R906ParametrizarComandoSql | |
{ | |
class R906Programa | |
{ | |
static void Main(string[] args) | |
{ | |
using (SqlConnection conexion = new SqlConnection()) | |
{ | |
conexion.ConnectionString = @"Data source =.\SQLEXPRESS; Initial catalog = Northwind;Integrated Security=SSPI"; | |
conexion.Open(); | |
Console.WriteLine("Ejemplo con parametrización de comando SQL:"); | |
ParametrizarComando(conexion, "5", "Cleaner"); | |
Console.WriteLine("\nEjemplo con parametrización de procedimiento almacenado:"); | |
ParametrizarProcedimientoAlmacenado(conexion, "Seafood", "1999"); | |
} | |
Console.WriteLine("\nPresione Enter para continuar..."); | |
Console.ReadLine(); | |
} | |
public static void ParametrizarComando(SqlConnection conexion, string idEmpleado, String titulo) | |
{ | |
using (SqlCommand comando = conexion.CreateCommand()) | |
{ | |
comando.CommandType = CommandType.Text; | |
comando.CommandText = "UPDATE Employees SET Title = @title WHERE EmployeeId = @id"; | |
SqlParameter paramentro1 = comando.CreateParameter(); | |
paramentro1.ParameterName = "@title"; | |
paramentro1.SqlDbType = SqlDbType.VarChar; | |
paramentro1.Value = titulo; | |
comando.Parameters.Add(paramentro1); | |
comando.Parameters.Add("@id", SqlDbType.Int).Value = idEmpleado; | |
int resultado = comando.ExecuteNonQuery(); | |
if (resultado == 1) | |
{ | |
Console.WriteLine("El empleado con ID {0} ahora tiene el título de {1}", idEmpleado, titulo); | |
} | |
else | |
{ | |
Console.WriteLine("No se pudo actualizar el titulo del empleado con ID {0}", idEmpleado); | |
} | |
} | |
} | |
public static void ParametrizarProcedimientoAlmacenado(SqlConnection conexion, string categoria, string ahnio) | |
{ | |
using (SqlCommand comando = conexion.CreateCommand()) | |
{ | |
comando.CommandType = CommandType.StoredProcedure; | |
comando.CommandText = "SalesByCategory"; | |
comando.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = categoria; | |
comando.Parameters.Add("@OrdYear", SqlDbType.NVarChar).Value = ahnio; | |
using (IDataReader lector = comando.ExecuteReader()) | |
{ | |
Console.WriteLine("Ventas por categoría {0} y año {1}:", categoria, ahnio); | |
while (lector.Read()) | |
{ | |
Console.WriteLine(" {0} = {1}", lector["ProductName"], lector["TotalPurchase"]); | |
} | |
} | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment