uno. Prólogo:
El procedimiento almacenado es un conjunto de declaraciones SQL diseñadas para completar funciones específicas, que se compilan y almacenan en la base de datos. El usuario ejecuta un procedimiento almacenado especificando su nombre y proporcionando parámetros (si el procedimiento almacenado tiene parámetros). Los procedimientos almacenados son un objeto importante en la base de datos y cualquier aplicación de base de datos bien diseñada debería utilizar procedimientos almacenados. En general, los procedimientos almacenados tienen las siguientes ventajas:
◆ Los procedimientos almacenados permiten la programación de componentes estándar
◆ Los procedimientos almacenados pueden lograr una velocidad de ejecución más rápida
◆ Los procedimientos almacenados pueden reducir el tráfico de la red
◆ Los procedimientos almacenados se pueden utilizar completamente como mecanismo de seguridad
El autor de este artículo presentará le explicamos la aplicación de procedimientos almacenados en aplicaciones de bases de datos .NET y cómo usarlos junto con el objeto SqlDataAdapter, el objeto DataSet, etc. en ADO.NET para mejorar el rendimiento general de las aplicaciones de bases de datos .NET.
dos. Requisitos del sistema:
Herramientas de desarrollo: Visual Studio.NET
Sistema de administración de bases de datos: SQL Server 2000 (incluida la base de datos Pubs utilizada en el programa de muestra)
3. Cree un procedimiento almacenado simple:
aquí le presentaré cómo usar Visual Studio.NET IDE para crear un procedimiento almacenado. Es muy fácil e intuitivo crear procedimientos almacenados utilizando Visual Studio.NET IDE. Siempre que navegue hasta la base de datos Pubs en el explorador del servidor y expanda el nodo, encontrará varios objetos de la base de datos, incluidos los procedimientos almacenados, como se muestra en la Figura 1. Espectáculo.
Haga clic derecho en el nodo del procedimiento almacenado para que aparezca un menú que contiene el comando "Nuevo procedimiento almacenado". Después de crear un nuevo procedimiento almacenado, la plantilla de código que se muestra a continuación aparecerá en la ventana de edición de código en el IDE:
CREAR PROCEDIMIENTO dbo.StoredProcedure1
/*
(
@ parámetro1 tipo de datos = valor predeterminado,
@ parámetro2 tipo de datos SALIDA )
*/
COMO
/* ESTABLECER NO CUENTA EN */
DEVOLVER
La plantilla de código anterior se ajusta a las reglas de sintaxis simplificadas para crear procedimientos almacenados. Las reglas de sintaxis completas son las siguientes:
CREATE PROC [ EDURE ] número_procedimiento [ ;
[ { @parameter tipo_datos }
[VARIAN] [= predeterminado] [SALIDA]
] [ ,...n ]
[ CON
{RECOMPILAR | CIFRADO | RECOMPILAR, CIFRADO } ]
[ PARA REPLICACIÓN ]
COMO declaración_sql [...n]
Debido a limitaciones de espacio, el significado de cada parámetro no se presentará aquí. Los lectores interesados pueden consultar la información sobre el sistema de administración de bases de datos SQL Server 2000.
A continuación, presentaré brevemente cada componente gramatical en esta plantilla de código. La instrucción CREATE PROCEDURE crea un procedimiento almacenado, seguido del nombre del procedimiento almacenado. Los componentes en "/*...*/" son los parámetros del procedimiento almacenado, que pueden incluir parámetros de entrada y parámetros de salida. El contenido que sigue a la palabra clave AS es el cuerpo principal del procedimiento almacenado, que es cualquier número y tipo de declaraciones SQL contenidas en el procedimiento almacenado. La palabra clave RETURN indica el final del procedimiento almacenado y puede devolver un valor de estado entero a la persona que llama. Creemos un procedimiento almacenado simple sin parámetros y usémoslo:
CREAR PROCEDIMIENTO dbo.up_GetPublisherInfo
COMO
SELECCIONE pub_id, pub_name, ciudad, estado, país
DE editores
DEVOLVER
Después de crear el procedimiento almacenado anterior, guárdelo. Una vez guardado, el nodo correspondiente al procedimiento almacenado aparecerá en Server Explorer. Tenga en cuenta también que la palabra clave CREATE en la ventana de edición de código ha cambiado a la palabra clave ALTER, que se utiliza para cambiar cualquier procedimiento almacenado existente. Para ejecutar el procedimiento almacenado anterior, simplemente haga clic en su nodo y seleccione "Ejecutar procedimiento almacenado" en el menú emergente del botón derecho. El resultado de la operación es el siguiente:
Cuatro. Cree un procedimiento almacenado con parámetros:
arriba creamos un procedimiento almacenado simple sin parámetros, pero en aplicaciones reales a menudo se usan muchos procedimientos almacenados con parámetros. Los procedimientos almacenados con parámetros se utilizan generalmente para actualizar datos o insertar datos. A continuación podemos usar el mismo método de operación para crear un procedimiento almacenado con parámetros:
CREAR PROCEDIMIENTO dbo.up_UpdatePublisherInfo
(
@pub_id carácter (4),
@pub_name varchar (40),
@ciudadvarchar(20),
@carácter de estado (2),
@país varchar (30)
)
COMO
ACTUALIZAR editores
SET pub_name = @pub_name, ciudad = @ciudad, estado = @estado,
país = @país
DONDE ( pub_id = @pub_id )
DEVOLVER
En el código anterior para crear un procedimiento almacenado, declaramos las variables-parámetros locales del procedimiento almacenado agregando un signo "@" antes del nombre. También declaramos el tipo de cada parámetro y determinamos el valor de dirección de cada parámetro. es, indica si el parámetro es tipo de entrada o tipo de salida o tipo de entrada-salida o tipo de valor de retorno. Los usuarios pueden llamar al procedimiento almacenado a través del nombre del procedimiento almacenado correspondiente y los parámetros correctos y válidos. Además, puede agregar parámetros de salida a los parámetros utilizando la palabra clave OUTPUT. Consulte las reglas de sintaxis anteriores para conocer métodos específicos. Los parámetros de salida pueden devolver información relevante a la persona que llama.
El procedimiento almacenado anterior puede actualizar la información del editor correspondiente en la tabla de editores. Puede ejecutarlo haciendo clic en el nodo del procedimiento almacenado y seleccionando "Ejecutar procedimiento almacenado" en el menú emergente contextual. Una vez ejecutado, aparecerá un cuadro de diálogo para ingresar información del editor en el IDE (como se muestra en la Figura 3). Complete la información de actualización correcta y válida en este cuadro de diálogo. Tenga en cuenta que el valor de pub_id debe existir en la tabla original y luego haga clic en el botón "Aceptar" para actualizar los datos.
cinco. Cree una aplicación de base de datos con un procedimiento almacenado simple:
A continuación, usaremos el procedimiento almacenado anterior sin parámetros para crear una aplicación de base de datos, que también usa el objeto SqlDataAdapter y el objeto DataSet en ADO.NET. El objeto SqlDataAdapter sirve como puente entre la base de datos de SQL Server y el objeto DataSet para conectar los dos. El objeto SqlDataAdapter contiene dos métodos de uso común: el método Fill() y el método Update(). El método Fill() puede obtener los datos correspondientes de la base de datos y completarlos en el objeto DataSet, y el método Update(), como su nombre indica, actualiza el conjunto de datos. Antes de llamar al método Fill(), debemos configurar la propiedad SelectCommand del objeto SqlDataAdapter, que en realidad es un objeto SqlCommand. La propiedad SelectCommand contiene declaraciones SQL válidas y puede obtener los datos correspondientes de la base de datos y completarlos en el objeto DataSet.
Primero, creamos una aplicación Windows Forms, el lenguaje de programación es C#. Después de crear un nuevo proyecto en Visual Studio.NET, agregue una nueva clase al proyecto: la clase Publishers, que encapsula la lógica empresarial de conectarse a la base de datos backend y obtener el objeto del conjunto de datos. Los pasos son los siguientes:
1. Agregue las referencias de espacios de nombres necesarias: usando System.Data.SqlClient
2. Agregue las siguientes variables necesarias a esta clase:
private SqlConnection cnPubs;
cmdPubs privados de SqlCommand;
SqlDataAdapter daPubs privados;
dsPubs de conjunto de datos privados;
3． En el constructor de esta clase, complete la conexión a la base de datos backend y obtenga el objeto SqlDataAdapter y otra lógica empresarial:
public Publishers()
{
intentar
{
//Crear un objeto de conexión a la base de datos
cnPubs = new SqlConnection( "servidor=localhost;seguridad integrada=true;base de datos=pubs" );
//Crea un objeto SqlCommand y especifica su tipo de comando como un procedimiento almacenado
cmdPubs = nuevo SqlCommand();
cmdPubs.Conexión = cnPubs;
cmdPubs.CommandType = CommandType.StoredProcedure;
cmdPubs.CommandText = "up_GetPublisherInfo";
//Crea un objeto SqlDataAdapter y establece su propiedad SelectCommand en el objeto SqlCommand anterior
daPubs = nuevo SqlDataAdapter();
daPubs.SelectCommand = cmdPubs;
//Crear un objeto conjunto de datos
dsPubs = nuevo conjunto de datos();
}
captura (excepción) {}
}
4. Finalmente, se proporciona un método GetPublisherInfo() para esta clase, que llena el objeto DataSet con el objeto SqlDataAdapter y devuelve el objeto DataSet lleno. El método es el siguiente (vale la pena señalar que el objeto SqlDataAdapter abrirá implícitamente la conexión de la base de datos y. abra implícitamente la conexión de la base de datos después de obtener los datos y cierre la conexión formalmente, lo que significa que el objeto DataSet funciona en modo sin conexión y cuando abre explícitamente la conexión de la base de datos y obtiene los datos, el objeto SqlDataAdapter no cerrará la conexión):
Conjunto de datos público GetPublisherInfo().
{
// Llama al método Fill() del objeto SqlDataAdapter y devuelve el objeto del conjunto de datos
daPubs.Fill(dsPubs);
devolver dsPubs;
}
Después de completar el diseño de la clase Publishers, agregamos un control DataGrid al formulario principal y lo usamos para mostrar los datos en el objeto DataSet. Primero agregue las siguientes variables miembro a la clase de formulario principal:
publicaciones de editores privados;
conjunto de datos privado ds;
Después de eso, modifique el constructor de la clase de formulario principal de la siguiente manera:
Formulario público1()
{
//
// Requerido para la compatibilidad con Windows Forms Designer
//
InicializarComponente();
//
// TODO: Agregar cualquier código de constructor después de la llamada a InitializeComponent
// pubs = nuevos editores();
ds = pubs.GetPublisherInfo();
dataGrid1.DataSource = ds.Tables[0];
}
De esta manera, tan pronto como se inicie la aplicación, los datos correspondientes obtenidos de la base de datos Pubs utilizando el procedimiento almacenado anterior sin parámetros se mostrarán en el control DataGrid del formulario principal. El diagrama de ejecución del programa es el siguiente:
6. Cree una aplicación de base de datos con un procedimiento almacenado con parámetros:
Arriba creamos una aplicación con un procedimiento almacenado sin parámetros, y ahora crearemos una aplicación de base de datos más compleja. En las aplicaciones de bases de datos reales, a menudo necesitamos obtener datos y actualizarlos, insertarlos o eliminarlos. En este momento, necesitamos usar procedimientos almacenados con parámetros. Al mismo tiempo, cuando usemos el objeto SqlDataAdapter, lo llamaremos Update(). método. El método Update() completará automáticamente la operación correspondiente en función de los cambios en cada registro en el objeto DataTable en el objeto DataSet. El objeto SqlDataAdapter también contiene propiedades como UpdateCommand, InsertCommand, DeleteCommand, etc. Estas propiedades son en realidad objetos SqlCommand. El método Update() selecciona los atributos apropiados según el tipo de operación.
Cuando utilizamos procedimientos almacenados con parámetros para crear aplicaciones de bases de datos, generalmente usamos la clase SqlParameter, que encapsula varias propiedades y métodos relacionados con los parámetros Sql. Las propiedades incluyen ParameterName, SqlDBType, Direction, Size, Value, SourceColumn y SourceVersion, etc. Entre ellos, ParameterName, SqlDBType, Direction, Size y otros atributos se utilizan para hacer coincidir los parámetros definidos en el procedimiento almacenado. Por ejemplo, el objeto SqlParameter definido a continuación se utiliza para hacer coincidir el parámetro "@pub_id " en el procedimiento almacenado up_UpdatePublisherInfo definido previamente.
SqlParameter updParam = new SqlParameter( "@pub_id", SqlDbType.Char, 4 );
En la definición anterior, aunque el atributo Dirección no se proporciona explícitamente, su valor predeterminado es Entrada, por lo que satisface nuestras necesidades. Y si la propiedad Direction de un objeto SqlParameter es InputOutput o Output o ReturnValue, entonces su propiedad Direction debe indicarse claramente. Por ejemplo, el siguiente código declara claramente que la propiedad Direction de un objeto SqlParameter es Output.
oParam.Direction = ParameterDirection.Output;
La propiedad SourceColumn se utiliza para hacer coincidir el objeto DataColumn en un objeto DataTable. Esta coincidencia puede importar implícitamente el objeto SqlParameter requerido cuando se llama al método Update() para actualizar el objeto DataTable. Si esta propiedad no se declara al definir, debe indicar explícitamente la propiedad SourceColumn del objeto SqlParameter en su código.
El valor predeterminado de la propiedad SourceVersion es el valor actual en el campo correspondiente del objeto DataRow, que es el valor que se actualizará en la base de datos. Por supuesto, la propiedad SourceVersion también puede apuntar al valor original en el campo correspondiente del objeto DataRow, es decir, el valor inicial obtenido de la base de datos. En un sistema de procesamiento de transacciones de bases de datos, la cuestión de la sincronización de datos es muy importante. Construyamos un procedimiento almacenado que pueda detectar la sincronización de datos.
CREAR PROCEDIMIENTO dbo.up_UpdatePublisherName
(
@pub_id carácter(4),
@pub_name varchar(40),
@Original_pub_name varchar(40)
)
COMO
si existe (seleccione pub_id
de los editores
donde (pub_id = @pub_id) Y (pub_name = @Original_pub_name))
Comenzar
ACTUALIZAR editores SET pub_name = @pub_name
DONDE (pub_id = @pub_id)
Fin
DEVOLVER
A continuación, llamamos al procedimiento almacenado en la aplicación anterior para actualizar el nombre del editor. Primero, mejore su clase de lógica empresarial: clase de editores según la aplicación original:
1. Agregue un nuevo objeto SqlCommand que pueda usarse como propiedad UpdateCommand del objeto SqlDataAdapter:
cmdUpdPubs SqlCommand privado;
2. Actualice la función constructora Publishers() de esta clase para agregar lo siguiente:
// Crea otro objeto SqlCommand que haga referencia al procedimiento almacenado que actualiza el nombre del editor
cmdUpdPubs = nuevo SqlCommand();
cmdUpdPubs.Conexión = cnPubs;
cmdUpdPubs.CommandType = CommandType.StoredProcedure;
cmdUpdPubs.CommandText = "up_UpdatePublisherName";
//Agregar los parámetros necesarios al objeto SqlCommand anterior
cmdUpdPubs.Parameters.Add( "@pub_id", SqlDbType.Char, 4, "pub_id");
cmdUpdPubs.Parameters.Add ("@nombre_pub", SqlDbType.VarChar, 40, "nombre_pub");
SqlParameter updParam = nuevo SqlParameter
("@Original_pub_name", SqlDbType.VarChar, 40, "pub_name");
updParam.SourceVersion = DataRowVersion.Original;
cmdUpdPubs.Parameters.Add(updParam);
3． Especifique la propiedad UpdateCommand del objeto SqlDataAdapter como el objeto SqlCommand definido anteriormente:
daPubs.UpdateCommand = cmdUpdPubs;
4. Agregar método UpdatePublisherName():
public void UpdatePublisherName(DataSet dsChanges)
{
//Actualiza todos los cambios
daPubs.Update(dsChanges);
}
Una vez completada la clase de lógica empresarial de la aplicación, agregue un botón llamado "Actualizar conjunto de datos" en el formulario principal y agregue la función de respuesta al evento del botón de la siguiente manera:
private void button1_Click(object sender, System.EventArgs e) { if ( ds.HasChanges() ) { pubs.UpdatePublisherName( ds.GetChanges() ); ds.Clear(); ds = pubs.GetPublisherInfo();
Hasta ahora, la clase de lógica empresarial y la clase de formulario principal de la aplicación se han actualizado. Ahora la aplicación puede actualizar el contenido relevante en la base de datos de acuerdo con los cambios del usuario.
Siete. Resumen:
este artículo le presenta los conocimientos básicos de los procedimientos almacenados y cómo combinar objetos SqlDataAdapter, objetos DataSet, etc. para crear aplicaciones basadas en datos en aplicaciones de bases de datos .NET. En este artículo, hemos utilizado dos tipos de procedimientos almacenados: uno es un procedimiento almacenado simple sin parámetros, que es relativamente fácil de usar, el otro es un procedimiento almacenado con parámetros y debe llamar a este tipo de procedimiento almacenado. al objeto SqlParameter. Al mismo tiempo, no es difícil descubrir que encapsular la lógica empresarial de actualización de datos en un procedimiento almacenado es un buen método de diseño que puede mejorar la manejabilidad, escalabilidad y seguridad de la base de datos de la aplicación. De manera similar, la lógica empresarial para insertar y eliminar datos se puede encapsular en procedimientos almacenados y utilizar en aplicaciones de manera similar. Finalmente, espero que este artículo sea de gran ayuda para todos.