Learning Notes (iii)--Application of database commands

Learning Notes (iii)--Application of database commands

One, stored procedures

(i) Basic concepts

The stored procedure is the SQL statement inside the SQL database system, the advantage is that it can improve the execution efficiency, improve the security of the database and reduce the network traffic.

(ii) Creating a stored procedure

1. Basic grammar

CREATE PROCEDURE procedure_name

{@parameter Data_type}

As

--specific statements

Go

--can be added without, go means another page, if the bottom of the statement can not be added

2. Insert user in stored procedure Usp_insertuser

GO

CREATE PROCEDURE Usp_insertuser

(@No VARCHAR (10)

, @Password VARCHAR (20))

As

BEGIN

INSERT Tb_user

(No,password)

VALUES

(@No

, Hashbytes (' MD5 ', @Password));

END

(iii) calling a stored procedure

String strSQL = "Data source=localhost;initial catalog=######;integrated security=true";//Database link string

String sql = "Usp_insertuser";//the name of the stored procedure to invoke

SqlConnection conn = new SqlConnection (strSQL);//sql database connection object, with database link string as parameter

SqlCommand comstr = new SqlCommand (SQL, conn),//sql statement execution object, first argument is the statement to execute, and the second is a database connection object

Comstr.commandtype = commandtype.storedprocedure;//Because the stored procedure is to be used, the set execution type is stored procedure

Set parameters for the stored procedure in turn

COMSTR.PARAMETERS.ADD ("@Param1", Sqldbtype.text). Value = "# # #";

Conn. Open ();//Opening a database connection

MessageBox.Show (Comstr.executenonquery (). ToString ());//Execute stored Procedure

SqlDataAdapter SqlDataAdapter1 = new SqlDataAdapter (COMSTR);

DataTable dt=new DataTable ();

SqlDataAdapter1.Fill (DT);

Datagridview1.datasource = DT;

Conn. Close ();//Closed connection

Second, object-oriented _ class

(i) Definition of class

1. Basic grammar

< access Modifiers >class< class name >

{ class Member (field, property, method, Event)}

2. Modifiers for class

< P align= "left" and public
Private
Protected	Protected access. Only for this class and subclass access, the instance cannot access the
Internal /td>
Protected Internal

(ii) Instantiation of classes

1. Basic grammar

< class name > < Instance Name >= New < class name >

{ parameter of constructor }

(c) Constructors

Also known as the construction method

< access Modifiers > return value type Method name ( parameter list )

{

Method body

}

Cases

Attached: Course examples

Code under the login control:

This. user.no = This.txb_UserNo.Text.Trim ();

Clears the text box's text to the user's corresponding property after clearing the trailing and ending spaces;

This. User.password = This.txb_Password.Text.Trim ();

This.                                                             User.login (); Call the user's method login, and the text of each text box as a parameter;

MessageBox.Show (this.                                             User.message); Displays the login message in the message box;

if (!this. User.hasloggedin)//login fails if the user does not complete the login;

{

This.txb_Password.Focus (); The Password text box gets the focus;

This.txb_Password.SelectAll (); All text in the Password text box is selected;

}

We can only use this short code to complete the login process, but we also need to write code for the related class.

Because referencing SQL database operations classes, namespaces are typically introduced when database operations are performed. So when writing the class, we need to write the following code above the page:

using system.data;                                                                                          // Contains a variety of data objects;

using system.data.sqlclient;                                                                       The          //contains all kinds of objects required to access SQL Server;

Using System.Configuration; Contains the configuration Manager required to access the configuration file, and a reference to System.Configuration must be added to the reference in this project beforehand;

At this time we can write related classes:

public class User

{

Public attribute: User number;

public string No

{

Get

Set

}

Public attribute: password;

public string Password

{

Get

Set

}

Public property: Whether to complete the login;

public bool Hasloggedin

{

Get

Set

}

Public attribute: message;

(used to return validation results)

public string Message

{

Get

Set

}

Public method: Login;

public void LogIn ()

{

SqlConnection SqlConnection = new SqlConnection (); Declaring and instantiating SQL connections;

SqlConnection.ConnectionString =

configurationmanager.connectionstrings["SQL"].                               ToString (); Configuration Manager reads the connection string from app. config;

SqlCommand SqlCommand = Sqlconnection.createcommand (); Call the SQL connection method CreateCommand to create the SQL command, which binds the SQL connection;

Sqlcommand.commandtext = "Usp_selectusercount"; //Specify the command text for the SQL command; the command text is the stored procedure name; (2.5 knowledge is applied .) ）

Sqlcommand.commandtype = CommandType.StoredProcedure; The type of the SQL command is set to the stored procedure;

SqlCommand.Parameters.AddWithValue ("@No", this.                                         No); Adds the name and value of the parameter to the parameter collection of the SQL command;

SqlCommand.Parameters.AddWithValue ("@Password", this. Password);

Sqlconnection.open (); Open the SQL connection;

int rowCount = (int) sqlcommand.executescalar (); Call the SQL command's method executescalar to execute the command and accept a single result (that is, scalar);

            sqlconnection.close ();                                                                          //close the SQL connection;

if (RowCount = = 1)//If the lost user number corresponding to the 1 line record;

{

                this. Hasloggedin = true;                                                                   //complete the login;

This. Message = "Login successful.                                                            "; give the correct hint;

}

else//otherwise;

{

This.                                                               Hasloggedin = false; Login not completed;

This. Message = "The user number/password is wrong, please re-enter!"                                         "; Give the wrong hint;

}

}

}

Learning Notes (iii)--Application of database commands