SQL Stored procedure creation and application instance in asp.net C #

What is a stored procedure?

Store common or complex tasks in advance with SQL statements and a specified name, when you call the database tutorial to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically complete the command.

Advantages of stored procedures

1. the stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution.
2. when performing complex operations on the database (for example, performing update, insert, query, and delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database.
3. The stored procedure can be reused to reduce the workload of database developers.
4. High security. You can set that only one user has the right to use the specified stored procedure.

Create a stored procedure
**************************************** *********

Syntax

Create procedure [owner.] procedure_name [; number]
[{@ Parameter data_type}
[Varying] [= default] [output]
] [,... N]

[
{Recompile | encryption | recompile, encryption}]

[For replication]

As SQL _statement [... n]
Parameters

Owner

   

Name of the user id that owns the stored procedure. The owner must be the name of the current user or the name of the role to which the current user belongs.

Procedure_name

Name of the new stored procedure. The process name must comply with the identifier rules and must be unique to the database and its owner.

; Number

Is an optional integer used to group processes with the same name, so that the same group of processes can be removed with a drop procedure statement. For example, the process used by an application named orders can be named orderproc; 1, orderproc; 2, and so on. The drop procedure orderproc statement removes the entire group. If the name contains a bound identifier, the number should not be included in the identifier and the appropriate delimiters should be used before and after procedure_name.

@ Parameter

Parameters in the process. One or more parameters can be declared in the create procedure statement. You must provide the value of each declared parameter during execution (unless the default value of this parameter is defined or set to equal to another parameter ). A stored procedure can have a maximum of 2.100 parameters.

Use the @ symbol as the first character to specify the parameter name. The parameter name must comply with the identifier rules. Each process parameter is only used for this process. The same parameter name can be used in other processes. By default, parameters can only replace constants, but cannot replace the names of table names, column names, or other database objects.

Data_type

The data type of the parameter. All data types except table can be used as parameters for stored procedures. However, the cursor data type can only be used for output parameters. If you specify the cursor data type, you must also specify the varying and output keywords. There is no limit on the maximum number of output parameters that can be cursor data types.

Varying

Specify the result set supported by the output parameter (dynamically constructed by the stored procedure, and the content can be changed ). Only applicable to cursor parameters.

Default

The default value of the parameter. If the default value is defined, the process can be executed without specifying the value of this parameter. The default value must be a constant or null. If the like keyword is used for this parameter, the default value can contain wildcards (%, _, [], and [^]).

Output

Indicates that the parameter is a return parameter. The value of this option can be returned to execute. The output parameter can be used to return information to the call process. The text, ntext, and image parameters can be used as output parameters. The output parameter using the output keyword can be a placeholder cursor.

N

It indicates that up to 2.100 parameter placeholders can be specified.

{Recompile | encryption | recompile, encryption}

Recompile indicates that SQL server does not cache the plan of the process, and the process will be re-compiled at runtime. Use the recompile option when you use an atypical or temporary value instead of overwriting the execution plan cached in the memory.

Encryption indicates the entries in the SQL server encrypted syscomments table that contain the create procedure statement text. Encryption prevents the process from being published as part of SQL server replication.

For replication

The storage process created for replication cannot be executed on the subscription server .. Stored procedures created using the for replication option can be used for filtering stored procedures and can only be executed during replication. This option cannot be used with the with recompile option.

As

Specifies the operation to be performed in the process.

SQL _statement

Any number and types of transact-SQL statements to be included in the process. But there are some restrictions.

N

Yes indicates that this process can contain placeholders for multiple transact-SQL statements.

**************************************** ******

Note: * the surrounding part comes from ms online books.

The following are some examples I have written to help you understand.

Create a t_city table in the database.

Id cityname short

1 Beijing bj

2 Wuhan wh

3 Guangzhou gz

 

1. Select all contents in the table and return a result set.

Create procedure myprocedure_all

As

Select * from t_city

Return

2. Query and return the result set based on input parameters.

Create procedure myprocedure_para

@ Cityname nvarchar (50 ),

@ Short nvarchar (50)

As

Select * from t_city where cityname = @ cityname and short = @ short

Return

3. Stored procedure with output parameters (returns the id and of the first two records)

Create procedure myprocedure_output

@ Sum int output

As

Select @ sum = sum (id) from (select top 2 * from t_city) as tmptable

Return

Now let's look at an application stored procedure instance in c #.

In the c # code, we will use the new class, system. data. sqlclient. parameter. Objects of this class are designed to represent parameters in the stored procedure. Therefore, the constructor needs to know the name, data type, and the size of the discussed parameters.

<% @ Import namespace = "system. data" %>
<% @ Import namespace = "system. data. sqlclient" %>

<Html>
<Head> <title> using stored procedures with parameters </title> <Body>
<Form runat = "server" method = "post">
Enter a state code:
<Asp Tutorial: textbox id = "txtregion" runat = "server"/>
<Asp: button id = "btnsubmit" runat = "server"
Text = "search" onclick = "submit"/>
<Br/>
<Asp: datagrid id = "dgoutput" runat = "server"/>
</Form>
</Body>
</Html>

<Script language = "c #" runat = "server">
Private void submit (object sender, eventargs e)
{
String strconnection = "server = 224 numeca; database = northwind; user id = sa; password = sa ";
Sqlconnection objconnection = new sqlconnection (strconnection );
Sqlcommand objcommand = new sqlcommand ("sp_sp_mersbystate", objconnection );
Objcommand. commandtype = commandtype. storedprocedure;

Sqlparameter objparameter = new sqlparameter ("@ region", sqldbtype. nvarchar, 15 );

/* Create a parameter named @ region and declare it as nvchar (15), which matches the declaration in the stored procedure. The second parameter of the constructor of this version is always a member of the system. data. sqldbtype enumeration. This enumeration has 24 members, indicating all the data types you may need. */
 

Objcommand. parameters. add (objparameter );

/* The second line adds parameters to the parameter set of the command object. This operation is often forgotten */

Objparameter. direction = parameterdirection. input;

/* Set the direction attribute of the parameter object to determine whether it will be used to pass information to the stored procedure or receive information from it. Parameterdirection. input is actually the default value of this attribute, but it is helpful to put it into the code from the perspective of maintenance and readability. */

Objparameter. value = txtregion. text;

/* Set the value attribute of the parameter to the text attribute of the txtregion text box. */

Objconnection. open ();

Objconnection. open ();
Dgoutput. datasource = objcommand.exe cutereader ();
Dgoutput. databind ();

Objconnection. close ();
}
</Script>