The notes of the ado SQL database in asp.net are continuously updated.

ADO connection Database
1) obtain the connection string
Method 1: Remember the connection string
ConnectionString = "Integrated Security = True; server =.; database = DBName"
Method 2: in visual studio, click View> service Resource Manager> right-click data connection on the left and select Add connection> service name as a point. select the database name, click "advanced", and copy the connection string at the bottom.
2) configure the connection string in web. config
Copy codeThe Code is as follows:
<ConnectionStrings>
<Addname = "SQLconnectionStr" connectionString = "Data Source =.; Initial Catalog = NetShopDB; Integrated Security = True" providerName = "System. Data. SqlClient"/>
</ConnectionStrings>

3) create a SqlConnection class on the DAL layer, including static methods:
Remember to add Configuration reference and using System. Configuration; namespace first
Copy codeThe Code is as follows:
Public static string getConnectionStr ()
{
Return ConfigurationManager. ConnectionStrings ["SQLconnectionStr"]. ToString ();
}

4) Call the getConnectionStr () Static Method in other classes of the DAL layer.
Copy codeThe Code is as follows:
String conStr = SqlConnection. getConnectionStr ();


How to execute SQL statements at the DAL Layer

Method 1
Copy codeThe Code is as follows:
Public List <student> getData1 (string myid, string myname)
{
// Using is flexible and convenient. If you use con, you do not need to close it manually. The current connection is automatically closed.
Using (SqlConnection con = new SqlConnection (conStr ))
{
// Open the connection
Con. Open ();
String character STR = "select * from ns_user where userID = @ myid and userName = @ myname ";
SqlCommand cmd = new SqlCommand (reverse STR, con );
// Use parameter serialization to prevent injection attacks.
Cmd. Parameters. Add (new SqlParameter ("@ myid", myid ));
Cmd. Parameters. Add (new SqlParameter ("@ myname", myname ));
// Execute the query and return the first column in the first row of the returned result set. Ignore other columns or rows
// Object myResult = cmd. ExecuteScalar ();
// Execute the Transact-SQL statement on the connection and return the affected number of rows. (Execute statements, such as adding insert, deleting delete, and changing update)
// Int ResultRowCount = cmd. ExecuteNonQuery ();
SqlDataReader sdr = cmd. ExecuteReader ();
List <student> ls = new List <student> ();
While (sdr. Read ())
{
Student s = new student ();
S. Sid = sdr ["sid"]. ToString ();
S. Sname = sdr ["sname"]. ToString ();
Ls. Add (s );
}
Return ls;
}
}
Dataset (remember)
# Region obtain detailed instructor Information
Public DataSet GetTeacherInfo ()
{
Using (SqlConnection conn = new SqlConnection (dbapp ))
{
SqlDataAdapter sda = new SqlDataAdapter ("select * from table1", conn );
DataSet ds = new DataSet (); // defines a set of tables.
Sda. Fill (ds, "teacher ");
Return ds;
}

}
# Endregion


ADO operation stored procedure:
Copy codeThe Code is as follows:
# Region
Public int UserCheck (string userID, string userName)
{
Using (SqlConnection con = new SqlConnection (conStr ))
{
Con. Open ();
SqlCommand cmd = new SqlCommand ();
Cmd. CommandText = "sp_MYLogin"; // sp_MYLogin is the name of the stored procedure.
Cmd. CommandType = CommandType. StoredProcedure;
Cmd. Connection = con;

// Assign a value to the stored procedure
// Assignment Method 1 ("@ name" in the first value bracket must be exactly the same as "@ name" in the Stored Procedure)
Cmd. Parameters. Add (new SqlParameter ("@ userID", userID ));

// Assignment Method 2 (assign a second value)
SqlParameter pwd = new SqlParameter ("@ pwd", SqlDbType. NVarChar, 50 );
Pwd. Value = userName;
Pwd. Direction = ParameterDirection. Input;
Cmd. Parameters. Add (pwd );
// Define a variable to accept the return value of the stored procedure
SqlParameter result = new SqlParameter ("@ return", SqlDbType. Int );
Result. Direction = ParameterDirection. ReturnValue;
Cmd. Parameters. Add (result );

Cmd. ExecuteNonQuery (); // execute the Stored Procedure

// Obtain the return value of the stored procedure in the SQL variable @ result
Int num = Convert. ToInt32 (cmd. Parameters ["@ return"]. Value); // Parameters is a set ["@ return"] Which is his index.
Return num;
}
}
# Endregion

Error Log
Copy codeThe Code is as follows:
Catch (Exception ex)
{// Error Log
Error. ErrorWrite ("UserInsert", ex. Message, DateTime. Now. ToString ());
Return null;
}


Database Technology
Create a database repair table
Database creation statement
Copy codeThe Code is as follows:
Create database student -- Name of the created database
On primary --- specify parameters of the data file
(
Name = 'studnet3 _ data', -- all strings are separated'
Filename = 'e: \ lx \ student4_data.mdf ', -- the file name contains the path and name. Extension
Size = 3 MB, ---- default size. If no value is specified, the default size is MB.
Maxsize = 100 MB, ---- maximum capacity
Filegrowth = 1 MB --- automatic growth/expansion. If yes, automatic expansion is not performed.
)

Log on ----- parameters of the log file
(
Name = 'student5 _ log ',
Filename = 'e: \ lx \ student4_data.ldf ',
Size = 1 MB,
Maxsize = 10 MB,
Filegrowth = 10% -- 10% is the maximum capacity %)
)

Sp_helpdb student --- query the database name
Sp_renamedb student, stu -- rename the database
Drop database student -- delete a database


Table creation statement
Copy codeThe Code is as follows:
Drop table person -- delete a table
Create table person -- create a table
(
--- Note: The following is the attribute (field name) before, and the data type is after
ID int primary key identity () not null, -- primary key is the primary key set to ensure that the column value is unique and not empty. The start value of identity () is, and the step size is
Name nvarchar (10) not null, --- not null indicates that the value cannot be blank.
Sex bit not null, -- bit is bool type
Age int default 18, -- default 18 indicates automatically taking the default value
Scroe decimal (100) check (score <=) -- 4 indicates the total number of digits before and after the decimal point, indicating the number of digits after the decimal point check is a check Restriction
Cardid int unique -- unique refers to the unique key. When multiple columns of data in a table need to be unique, columns other than primary keys need to be set as unique columns.
)

Table operations
Modify Table Structure and add deletion Constraints

Alter table person -- Modify table Structure
-- Add NameID int -- add attribute \ field NameID column and add Column
-- Drop column NameID -- delete a column

-- Alter column ID int not null --- add field not empty
-- Add constraint name (pk_table name_column name | pk_column name)
-- Add constraint pk_ID primary key (ID) -- add a primary key constraint when modifying a table
-- Add constraint ck_score check (score <150) -- add check constraints when modifying a table
-- Add constraint uk_cardi unique (cardid) -- add a unique key constraint when modifying a table
-- Add constraint df_age default 19 for age -- add a default constraint when modifying a table
-- Drop constraint CK _ person _ score _ 15502E78 -- delete a constraint (Format: drop constraint name)
Modify Table information, add (insert) delete (delete) Change (update) query (select)
-- Add record <insert Table Name values (parameter)> <1> string or date type plus ''<2> bit or <2> auto-increment column does not need to be added
Insert person values (12, 'wang)
Insert person (sex, age, cardid) values (, 32) -- selective data insertion (sex, age, cardid) refers to the data to be manually added

-- Modify record <update table name set modify object where condition> <1> modify multiple fields, separated by commas
Update person set age = 19, sex = 0 where ID = 1
Update person set age = 19, age = 12 where ID = 2 --- <1>
Update person set age = 19 + 1 where ID = 2 --- Perform arithmetic operations when modifying Information
Update person set age = 19 + 1 -- if the where clause is not written, all data is modified.

Update person set age = SUBSTRING (age,) where ID = 1 -- substring is the truncation of Characters

-- Delete record <delete table name where condition> (delete all records without where)
Delete person where ID = 1

Query a table
Single Table query
Copy codeThe Code is as follows:
Select * from tableName -- Query and display the entire table (all columns)
Select column name 1, column name 2 from tableName -- display partial Columns
Select column name 1 = 'number', column name 2 = 'brand' from Product1 -- modify the information of one of the columns in a unified manner

-- Modify the column title when displaying data (method 1)
Select 'number' = ProCode, 'brand' = ProTypeName from Product1
-- Modify the column title during display (method 2)
Select column name 1 'number', column name 2 'brand' from Product1

-- Arithmetic operation is performed on the column when it is displayed, and columns are added when it is displayed
Select column name 1, column name 2, column name 3*0.5 'after discount ', Number from Product1

Select distinct column name 1 from tableName -- duplicate rows in the column are deleted when the column is displayed.
Select top 3 * from Product1 -- display the first three columns
Select top 50 percent * from Product1 -- display the first % rows of the total number of rows
-- And is and, or is or, condition not: not column name = 'value'
Select * from tableName where Number = 15 and not age = 12 and Name = 'A' or sex = 1
-- Search for data with a score range of 0 to 100
Select * from Product1 where score <100 and score> = 1
Select * from Product1 where score between 1 and 100
-- In, not in (including but not including) The following are the data for finding the number
Select * from Product1 where Number = 10 or Number = 15 or Number = 20
Select * from Product1 where Number in (10, 15, 20)
Select * from Product1 where not Number in (10, 15, 20)
-- <1> like pattern match % can replace any number of characters <2> _ can replace one character <3> [] is a search range
Select * from Product1 where ProCode like 'd % '-- query the data of the ProCode whose first letter is D.
Select * from Product1 where ProCode like '_ S %' -- query the procode data whose second character is S.
Select * from Product1 where column name 1 like '1% '-- add ''even for float Type''
Select * from Product1 where column name 1 like '_ 4 _' -- search for 3 data with the second character and number of characters
Select * from Product1 where column name 1 like '_ [5-9] %' -- Query column name 1 data with the second character being 5 to 9
-- Search for data that is empty or not empty
Select * from Product1 where proValue is not null
Select * from Product1 where proValue is null
Go -- Sort (desc descending asc ascending if nothing is written, the default Ascending Order is used)
Select * from Product1 order by proValue desc -- Sort provalues in descending order
Select * from Product1 order by proValue asc -- Sort provalues in ascending order
Select * from Product1 order by Number -- Sort the Number by default (ascending)
Select * from Product1 order by Number desc, proValue asc -- the second condition is executed only when the first condition is the same
Go -- Aggregate Function
Select MAX (proValue) from Product1 -- find the maximum value in proValue
Select min (proValue) from Product1 -- find the minimum value in proValue
Select sum (proValue) from Product1 -- query the sum of data in proValue
Select avg (proValue) from Product1 -- query the average value in proValue
Select count (*) from Product1 -- query the number of rows in the table * can also be replaced by a column name.

-- Group by grouping (where is the filtering condition after having grouping before group by. where and having are both filtering conditions)
-- Group: displays the group column information and the group information for statistics.
Select column name 1, max (column name 2), min (column name 3) from tableName where proTypeName = 'Television 'group by column name 1
Select proTypeName, max (proValue), min (proValue) from Product1 group by proTypeName having count (proValue)> 1



Multi-Table query

Copy codeThe Code is as follows:
-- Join inner join queries the information of the two tables
-- From query column Name 1. Name = column Name 2. Name is the join condition (the column content in the join condition must be consistent)
Select * from tableName inner join Product2 on column Name 1. Name = column Name 2. Name
-- Display the queried columns. p1. * Indicates displaying all columns of p1.
Select p1. *, proArea, proBrand from Product1 as p1 inner join Product2 on p1.ProCode = Product2.ProCode
-- Product1 as p1 indicates that an alias p1 is assigned to Product1. as can be omitted.
Select * from Product1 as p1 inner join Product2 as p2 on p1.ProCode = p2.ProCode
-- Where query, omitted as <format: select * from table, table where association condition>
Select * from Product1 p1, Product2 p2 where p1.ProCode = p2.ProCode

-- Outer join -- displays the data associated with the two tables first, and then displays the data not associated with the two tables.
Go -- <format: select * from table left \ right \ full outer join table on join condition>
Select * from Product1 p1 left outer join Product2 p2 on p1.ProCode = p2.ProCode -- left outer join
Select * from Product1 p1 right outer join Product2 p2 on p1.ProCode = p2.ProCode -- right outer join
Select * from Product1 p1 full outer join Product2 p2 on p1.ProCode = p2.ProCode -- all external connections

-- Cross join (also called Cartesian join: Crazy join, n-to-n join, no join condition)
-- Format: select * from Table cross join table
Select * from Product1 cross join Product2
-- Self-join (self-query: used to split a table into two tables)
Select c1. *, c2.Name from ST_class c1, ST_class c2 where c1.ID = c2.Department and c1.Name = 'computer Emy'
-- Nested Query
-- The subquery returns multiple values.
Select * from product1 where ProCode in (select ProCode from Product2 where proArea = 'beijing ')
Select * from product1 where ProCode not in (select ProCode from Product2 where proArea = 'beijing ')
-- A subquery returns a value.
Select * from Product1 where proValue = (select MAX (provalue) from Product1)

-- When a subquery returns multiple values, it can use any (any one of the return results is the smallest one) all (all the return results are the largest ])

-- Union all (join two separate subqueries with union all)
Select SNAME, SSEX, SBIRTHDAY from STUDENT union all select TNAME, TSEX, TBIRTHDAY from TEACHER

Stored Procedure

Copy codeThe Code is as follows:
-- Create/alter a stored procedure
Alter proc sp_SMGetCity --- sp_SMGetCity is the name of the stored procedure
(
@ Code nvarchar (50), -- the data type must be the same as the comparison character
@ Name nvarchar (50) output,
@ Grade int = 1
-- 'Note: the initial value of the stored procedure can only be included in the final parameter'
--- A @ symbol is a local variable
--- Two @ symbols are global variables
)
As
Begin
Select @ name = Name from TBName where Code like @ code + '%' and Grade = @ grade -- between begin and end is the SQL statement to be executed
Print @ name -- with output parameters
End

Declare @ outname nvarchar (50) -- defines a variable
Exec sp_SMGetCity '11', @ outname output -- @ aa assigns a variable to the output parameter to receive the returned value.
Select @ outname

Sp_help sp_SMGetCity -- view the creation information of a stored procedure named sp_studentmanager
Sp_helptext sp_SMGetCity -- view the code for creating a stored procedure named sp_studentmanager
Drop proc sp_SMGetCity -- delete a stored procedure

-- Return: only integer data can be returned.
-- Delete the Stored Procedure drop proc sp_Name
-- Exec (@ aa) -- execute @ aa (SQL statement). Therefore, brackets must be added to execute the SQL statement in the string.

Stored Procedure Call Stored Procedure
As
Begin
Declare @ return int
Exec @ return = sp_checkUser @ id, @ name -- Stored Procedure Call Stored Procedure
If @ return = 0
Print 'no duplicates, return can only return an integer'
Else
Print 'user registered'
End


Examples
Database joint query authorization
Copy codeThe Code is as follows:
Alter view vw_Role
As
Declare @ num int
Declare @ title nvarchar (100)
Declare @ ctitle nvarchar (200)
Set @ ctitle =''
Select @ num = count (*) from dbo. Operate
While (@ num> 0)
Begin
Select @ title = name from (select row_number () over (order by id) 'newid', name from dbo. Operate) ta where newid = @ num
If (@ num> 1)
Set @ ctitle + = @ title + ','
Else
Set @ ctitle + = @ title
Set @ num = @ num-1
End
Declare @ SQL varchar (8000)
Set @ SQL = 'select * from (select 1 "isture", rolename, modulename, operatename, role_ID, Module_id from vw_userrole group by rolename, modulename, operatename, role_ID, Module_id) a random (count (isture) for operatename in ('+ @ ctitle +') B'
Exec (@ SQL)


The storage process of the page splitter and the paging storage process.
Copy codeThe Code is as follows:
Alter proc cutpage
(
@ Tablename nvarchar (100), ---- paging table
@ Columnname nvarchar (100), ---- pagination Column
@ Ordertype nvarchar (100) = 'asc ', ---- sorting method
@ Pageindex int = 1,
@ Pagecount int = 1
)
As
Begin
Declare @ aa nvarchar (max );
Set @ aa = 'select * from
(Select *, ROW_NUMBER () over (order by '+ @ columnname + ''+ @ ordertype +') as uprow from '+ @ tablename +') as newtable
Where uprow between '+ cast (@ pageindex-1) * @ pagecount + 1 as nvarchar (100) + 'and' + convert (nvarchar (100), @ pageindex * @ pagecount)
Exec (@ aa) -- Here @ aa must be enclosed in parentheses ()

End

Exec cutpage 'sm _ class', 'classid'

Transaction
Copy codeThe Code is as follows:
--- Explanation of key transaction statements ----
Begin transaction --- transaction keyword TRANSACTION

DECLARE @ errorSum INT
SET @ errorSum = 0 -- Initialization is, that is, no error

Update bank SET money = money + 1000 where name = 'zhang san'
SET @ errorSum = @ errorSum + @ error
Update bank SET money = money-1000 where name = 'Li si'
SET @ errorSum = @ errorSum + @ error -- indicates whether an error is accumulated (@ error is non-zero)

If @ errorSum <> 0
Begin
Print 'unsuccessful, there is an error, the error code is :'
Print @ errorsum
Rollback transaction
End
Else
Begin
Print 'successfully'
Select * from Bank
Commit TRANSACTION
End

Trigger
Copy codeThe Code is as follows:
-- Concept: A special stored procedure that modifies (adds, deletes, and modifies) The data in the table and automatically runs the defined statement.
-- Feature: cascade modification across related tables
-- Keyword: trigger

Alter trigger trigger_name_f
On SM_Class
For update -- (for is executed after addition, deletion, and modification before addition, deletion, and modification. instead is not executed for all [add, delete, modify] operations. It is triggered before, but not changed)
As
Begin
If update (remark) --- determines whether the remark column in The SM_Class table changes data
Begin
Select * from inserted --- the table that stores the modified New Value
Select * from deleted ---- the table that stores the modified old value
Print 'remark column changed'
End
Else
Print 'other columns changed'
Print 'test trigger. This statement is displayed when the table SM_Class is modified, and this statement is in front'
End

Cursor
Copy codeThe Code is as follows:
-- A cursor is similar to reading a row by a SQL dateReader.
-- It is generally used as a last resort. It takes a long time, the server is under high pressure, and more memory and bandwidth are consumed,
-- The cursor is used to read the traversal of a set.

Declare cur_test cursor
For
Select pid, pname from ns_product

Open cur_test
Declare @ id uniqueidentifier;
Declare @ name nvarchar (50 );
-- Read a row (the first read is of course the first)
Fetch next from cur_test into @ id, @ name

-- Check whether the data is read. If the status is zero, the data is read (@ FETCH_STATUS = 0)
While @ FETCH_STATUS = 0
Begin
Print @ id
Print @ name
Print '----------------------'
-- Then read the next row
Fetch next from cur_test into @ id, @ name
End
Close cur_test
Deallocate cur_test


Scattered knowledge points
1) truncation string: substring (field name, starting position (first 1), intercepting length)
Select SUBSTRING (age, 1, 1) from person where ID = 2
2) about GUID:
Select NEWID ()
Insert person values ('','', NEWID ())
3) insert a table (person2) to another table (person1) (corresponding to the number of columns)
Insert person1
Select column, column, column from person2
4) Condition Statement (Note: braces {} in C # should be replaced by begin end in SQL)
Copy codeThe Code is as follows:
Declare @ x int, @ y int
Set @ x = 1
Set @ y = 2
If @ x> @ y
Print 'x> y'
Else
Print 'x <y'


Select code, name, grade,
Case Grade
When '1' then' Province'
When '2' then' City'
When '3' then' County'
End 'level'
From SM_PostCode

-------------------------------------------------------------
While (select MAX (DEGREE) from SCORE) <85
Begin
If (select MAX (DEGREE) from SCORE where CNO = '3-105 ')> = 100
Break
End

5) determine whether there is if exists (select * from TBName where CityName = '22 ')

6) Add the auto-increment column row_number over (order by **) ROW_NUMBER is the keyword over, which indicates the column to sort.
Select ROW_NUMBER () over (order by classID), * from SM_Class
Select rank () over (order by classID), * from SM_Class --- is also the Automatically increasing row number. If there is a duplicate, the row number will be tied together, And the next value will be automatically added with two

-- The result returned by a query statement is used as the data source table of another query statement.
Select * from (select ROW_NUMBER () over (order by column name 1) Here new column name, * from TBName) Here new table name where new column name between 1 and 3
7) temporary table
Declare @ table (id uniqueidentifier, name varchar (50 ))
-- Execute the insert operation (insert a data entry), return the hid field of the data, and insert it to the @ table id attribute.
Insert images (Hname, Himage) output inserted. Hid into @ table (id) values (@ hname, @ Himage)
Declare @ picid uniqueidentifier
Select @ picid = id from @ table

------------------------------------------------------------

-- The following is a comparison of execution efficiency.
-- SQL statement 1: execution requires s
Declare @ tempTable table (id varchar (20), name int, score datetime)
Insert @ tempTable (id, name, score)
Select userID, userName, userScore from scoreTable
Select * from @ tempTable

-- SQL statement 2: execution only requires s
Drop table # Bicycles
SELECT userID, userName, userScore
INTO # Bicycles
From scoreTable
Select * from # Bicycles

8) Date and Time operations
-- Get Beijing time and international time in the database
Select getdate (), getutcdate ()
-- DateAdd
Select dateadd (YEAR, 2, GETDATE () ---- Add the current YEAR to two years

-- Subtraction of time DateDiff
Select DATEDIFF (HOUR, getdate (), getutcdate () -- the HOUR of the international time minus the HOUR of the previous Beijing time (minus the front edge)

-- Obtain the year, month, and day of the time.
Select year (getdate ()-year (birthday ())
Select year (getdate ()-year ('1970-10-07 ')
9) column and column Conversion
Select * from (select * from TableName) a minute (count (stuName) for columnName in ('A', 'bb ', 'cc', 'dd ')
10) double quotation marks can only be used for table names and column names (either without double quotation marks)
Set @ aa = 'select ClassName "sd" from SM_Class '-- Note: The original 'sd' in ''is now written as" sd"
Exec (@ aa)

----------------- Pay more attention here ------------------------------
Declare @ bb nvarchar (max );
-- When using data values, you can only use ''second e-commerce class''
Set @ bb = 'select * from SM_Class where ClassName = ''second e-commerce class'' -- Note: The original 'second e-commerce class' must be written as ''second e-commerce class''
Exec (@ bb)

11) -- quickly create a table structure
Select c. Cid, c. Ccount into newTB1 from ns_comment c where 1 <> 1
12) -- repeat the key points
Declare @ na nvarchar (10), @ str nvarchar (max );
Set @ str = 'select top 1 @ bb = ClassID from SM_Class'
-- @ Str contains the SQL statement variables. Define the variable in @ str as a string to receive the variable in @ str (I .e. @ na = @ bb)
Exec sp_executesql @ str, n' @ bb nvarchar (10) output', @ na output
Select @ na, @ str
13) ------------- concurrency --------
-- Concept: multiple users interact with an object (database or table) at the same time
-- Problems: dirty data, non-repeated reading, loss of updates, phantom reading)
-- Solution: SQL Server uses locks to ensure transaction integrity (shared locks, exclusive locks, update locks, intention locks, schema locks, and batch update locks)
14)