The stored procedure provides many advantages in the data-driven application. With stored procedures, database operations can be encapsulated in a single command, optimize for achieving optimal performance and enhanced by additional security; use stored procedures, you can also get reuse of SQL statements, saving development time. In .NET, we can develop secure database programs in conjunction with the stored procedures feature provided by SQL Server.
Like the general database application development process, the application development process using the stored procedure is also divided into the following procedures: design the database, design stored procedures, connect the database, write commands, execute commands, and turn off database connections. Below I will introduce one by one by one in a simple user registration program.
Design database
For the sake of simplicity, the fields used by the user database have only four: user ID UID, user name wuse, user password PWD, email address Email. Open SQL Server's Enterprise Manager, create a new database Grid_Web, then create a table WebUser, design the database table below.
Design stored procedure
After designing the database, you can start designing stored procedures. The stored procedure can be divided into two types: there is a stored procedure without parameters. Since the two stored procedures are not only the same when programming the stored procedure, we have two stored procedures.
The stored procedures you need to design are inserted into the database, in actual use, you can use the stored procedure to implement common database processing, etc. The stored procedure without parameters uses the default value as the value of the database table, which is implemented as follows:
Create Procedure DT_INSERTUSER1
AS
INSERT INTO Webuser Values ("Lotusswan", "123456", "Lotusswan@mycom.com")
Go
The stored procedure with parameters uses variables as the database table š value, and the implementation of the above implementation is small and 4, just put the default value for variable parameters:
Create Procedure DT_INSERTUSER
@User char (12),
@Pwd char (12),
@Email char (30)
AS
INSERT INTO Webuser Values (@ user, @ pwd, @ email)
Go
Connect to the database
After designing the database and stored procedures, we can start designing applications. The first thing to do is to connect the database, connect the database through the SQLConnection object, OLEDBConnection object, ODBCConnection object, and more. Since we use the SQL Server database, consider the efficiency factor, we use the SQLConnection object.
The main step of connecting the database is to use the CONNECTIONSTRING attribute of SqlConnection. Here we use the connection statement:
String connStr;
CONNSTR = "data source = jiang; integrated security = SSPI; Initial Catalog = Grid_Web";
SqlConnection conn = new SqlConnection (connStr);
The security mode used in the above code is SSPI, or the user name / password can also be connected to the database, but the latter way username and password are placed in the program, obviously not safe. When using SSPI, the user name is used asPnet, so the connection method must be correct when the ASPNET has access to data and executes the stored procedure, otherwise an error will occur.
Write and execute commands
The SQLCOMMAND object can be an edit command, and several attributes of the SQLCommand object need to be set up for the stored procedure. CommandText Gets or sets the Transact-SQL statement or stored procedure to be executed for the data source, which is set to DT_INSERTUSER or DT_INSERTUSER1 corresponding to the parameter and non-parametric stored procedure; CommandType is set to System.Data.commandType.StoredProcedure, use storage The process explains the value of the CommandText property; the execution of the parameters and non-parametric stored procedures is that the parameters property can be found in the following code. Once you have the properties of the SQLCommand object, you can call the ExecutenoQuery function to execute the command. Unfairted stored procedure is very simple:
Sqlcommand comm = new sqlcommand ();
Comm.commandtext = "DT_INSERTUSER1";
Comm.Connection = conn;
Comm.commandtype = system.data.commandtype.storedProcedure;
Comm.executenonQuery ();
The implementation of the parameter stored procedure is much more complicated:
/ / Add parameters to the CommM, parameter name, and number of numbers are determined by the storage process;
Comm.Parameters.Add ("@ user", system.data.sqldbtype.varchar, 12, "wuse");
Comm.Parameters.Add ("@PWD", SqldbType.varchar, 12, "pwd");
Comm.Parameters.Add ("@ email", sqldbtype.varchar, 30, "email");
/ / The parameter value is assigned to the stored procedure as the parameter value;
Comm.Parameters ["@ User"]. value = utertxt.text;
Comm.Parameters ["@PWD"]. Value = pwdtxt.text;
Comm.Parameters ["@ email"]. value = emailtxt.text;
Comm.executenonQuery ();
After executing the ExecuteNonquery function of the SQLCommand object, a line of data is added to the database.
Turn off database connection
Like other categories, close the database connection is the simplest:
CONN.CLOSE ();
