asp.net in ADO SQL database notes Summary Continuous update _ Practical Tips

ADO Connection Database
1) Get connection string
Way one: Remember the connection string
connectionstring= "Integrated security=true; Server=. ; Database=dbname "
Mode two: In Visual Studio, click "View" À service Explorer, right-click the data connection on the left, and select Add Connection à service name: For a point. Select the database name, click Advanced, and then copy the connection string at the bottom
2 Configure the connection string in Web.config

Copy Code code as follows:

<connectionStrings>
<addname= "Sqlconnectionstr" connectionstring= "Data source=.;i Nitial catalog=netshopdb;integrated security=true "providername=" System.Data.SqlClient "/>"
</connectionStrings>

3 New SqlConnection class at the DAL layer, containing static methods:
Remember to add the Configuration reference and the using System.Configuration first; namespaces

Copy Code code as follows:

public static string Getconnectionstr ()
{
return configurationmanager.connectionstrings["Sqlconnectionstr"]. ToString ();
}

4 call Getconnectionstr () static method in other classes in the DAL layer

Copy Code code as follows:

String constr= sqlconnection.getconnectionstr ();

How the DAL layer executes SQL statements

ADO operation SQL statement: Way One

Copy Code code as follows:

Public list<student> getData1 (string myID, String myname)
{
Use using a flexible and convenient, use the con without manual shutdown, will automatically close the current connection
Using (SqlConnection con=new SqlConnection (Constr))
{
Open connection
Con. Open ();
String cmdstr = "SELECT * from Ns_user where userid= @myid and username= @myname";
SqlCommand cmd = new SqlCommand (Cmdstr,con);
This uses parameter serialization to prevent an injection attack
Cmd. Parameters.Add (New SqlParameter ("@myid", myID));
Cmd. Parameters.Add (New SqlParameter ("@myname", myname));
Executes the query and returns the first column of the first row in the result set returned by the query. Ignore other columns or rows
Object myresult = cmd. ExecuteScalar ();
Executes Transact-SQL statements on the connection and returns the number of rows affected. (responsible for executing the statement, such as INSERT, delete delete, change 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 Get detailed information about the teacher
Public DataSet Getteacherinfo ()
{
using (SqlConnection conn = new SqlConnection (Dbapp))
{
SqlDataAdapter SDA = new SqlDataAdapter ("select * FROM table1", conn);
DataSet ds = new DataSet (); Define a collection of tables
Sda. Fill (ds, "Teacher");
return DS;
}

}
#endregion

ADO Operations Stored procedures:

Copy Code code 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 stored procedure name
Cmd.commandtype = CommandType.StoredProcedure;
Cmd. Connection = con;

Assigning values to stored procedures
Assignment method One (the "@name" in the first value bracket must be identical to the "@name" in the stored procedure)
Cmd. Parameters.Add (New SqlParameter ("@userID", UserID));

Assignment mode Two (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 a stored procedure
SqlParameter result = new SqlParameter ("@return", SqlDbType.Int);
Result. Direction = ParameterDirection.ReturnValue;
Cmd. Parameters.Add (result);

Cmd. ExecuteNonQuery (); Executing stored procedures

Gets the return value of the stored procedure that is stored in the SQL variable @result
int num = Convert.ToInt32 (cmd. parameters["@return"]. Value); Parameters is a collection ["@return"] that's his index.
return num;
}
}
#endregion

Error log

Copy Code code as follows:

catch (Exception ex)
{//error log
Error. Errorwrite ("Userinsert", ex. Message, DateTime.Now.ToString ());
return null;
}

Database technology
Build a library table
Build a library statement

Copy Code code as follows:

Create DATABASE student--The names of the databases created
On primary---Specify each parameter of the data file
(
Name= ' Studnet3_data ',--all strings are separated by '
Filename= ' E:\lx\student4_data.mdf ',--file name contains path and name. Extension name
SIZE=3MB,----default size, or MB if no size is written
MAXSIZE=100MB,----maximum capacity
FILEGROWTH=1MB---Automatic growth/expansion, if it is, it does not automatically enlarge
)

Log on-----The parameters of the logging file
(
Name= ' Student5_log ',
Filename= ' E:\lx\student4_data.ldf ',
SIZE=1MB,
MAXSIZE=10MB,
filegrowth=10%--10% is% of maximum capacity)
)

SP_HELPDB Student---Query database name
Sp_renamedb Student,stu--Renaming a database
Drop DATABASE Student--deleting databases

Build Table Statement

Copy Code code as follows:

drop TABLE Person--delete table
CREATE TABLE person--Creating a table
(
---Note: The following is the property (field name) before the data type is
ID int PRIMARY key identity (2,1) not NULL,--primary key is to set the primary key to ensure that the column value is unique and not empty, the identity (2,1) starting value is, step is
Name nvarchar is not NULL and---NOT NULL to be null
Sex bit NOT NULL,--bit is bool type
Age int default,--default 18 refers to automatically take defaults
Scroe Decimal (4,1) Check (score<=100)--4 refers to the total number of digits added before and after the decimal point, representing the digits after the decimal point check is a restriction constraint
Cardid int unique--unique refers to a unique key in which multiple columns of data need to be guaranteed to be unique when a column other than a primary key needs to be set as a unique column
)

Action on a table
Modify table structure, add delete constraint

ALTER TABLE person--modifying table structure
--add NameID INT--Add property \ field NameID column, add column
--drop column NameID--delete column

--alter column ID int NOT NULL---Add field is not NULL
--add constraint 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)--Adding a CHECK constraint when modifying a table
--add constraint Uk_cardi Unique (cardid)--Add a unique key constraint when modifying a table
--add constraint df_age default for age--Adding a defaults constraint when modifying a table
--drop constraint ck__person__score__15502e78--delete constraint (format: Drop constraint constraint name)
Modify table information, add (insert) Delete (delete) change (update) check (SELECT)
--Add records <insert table name values (parameters) > <1> strings or date types add ' <2>bit ' or <2> automatic growth columns do not need to add values
Insert person values (' Wang ', 0,23,32,34)
Insert Person (Sex,age,cardid) VALUES (0,23,32)--Selective insertion data ((Sex,age,cardid)) refers to the data to be added manually

--Modify Records <update table name set Modify object Where Condition > <1> modify multiple fields, with comma spacing between
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---Modify information for arithmetic operations
Update person Set age=19+1--if no where is written, all data is modified

Update person set age=substring (age,1,1) where Id=1--substring is an intercept of characters

--Delete Record < delete table name where Condition > (delete all records if you don't add a where)
Delete person where id=1

Queries against tables
Single-Table Query

Copy Code code as follows:

SELECT * FROM tablename--find display of entire table (all columns)
Select Column Name 1, column name 2 from tablename--Show partial columns
Select column Name 1 = ' number ', column name 2 = ' Brand ' from Product1--unify the information in one column to modify

--Change column headings when displayed (method one)
Select ' Numbered ' =procode, ' brand ' =protypename from Product1
--Change column headings when displayed (method two)
Select Column Name 1 ' number ', column name 2 ' brand ' from Product1

--Count the columns when displayed, adding columns when 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--Deletes duplicate rows of the column when displayed
Select Top 3 * from Product1--Displays the first three columns
Select Top Percent * Product1--Displays the first% row of the total number of rows
--and is and, or is or is, the condition is not: not column name = ' value '
SELECT * FROM tablename where number=15 and not age=12 and name= ' AA ' or sex=1
--Find data with a range of 0 to 100 score
SELECT * from Product1 where score<100 and score >=1
SELECT * from Product1 where score between 1 and 100
--in,not in (contains not included) the following are lookup numbers,
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 don't number in (10,15,20)
--<1>like pattern Match% can replace any number of characters <2> _ can replace a character <3>[] is a lookup range
SELECT * from Product1 where procode like ' d% '--Find the Procode data for the first letter D
SELECT * from Product1 where procode like ' _s% '--Find the Procode data for the second character is S
SELECT * from Product1 where column name 1 like ' 1% '--even if float is added '
SELECT * from Product1 where column name 1 like ' _4_ '--Find the second character is and the number of characters is 3 data
SELECT * from Product1 where column name 1 like ' _[5-9]% '--Find the second character is 5 to 9 column name 1 data
--Lookup is null, not empty data
Select *from Product1 where provalue is not null
Select *from Product1 where provalue is null
Go--Sort (desc descending ASC Ascending without writing to default ascending)
SELECT * from Product1 ordered by provalue Desc--Arrange provalue in descending order
SELECT * from Product1 ordered by provalue ASC--Provalue in ascending order
SELECT * from Product1 ordered by number--arranges number by default (ascending)
SELECT * from Product1 ORDER by number desc,provalue ASC--The second condition executes when the first condition is the same
Go--aggregate functions
Select Max (provalue) from Product1--Find the maximum value in Provalue
Select min (provalue) from Product1--Find the smallest value in Provalue
Select SUM (provalue) from Product1--Find the sum of the data in the Provalue
Select AVG (provalue) from Product1--find the average in Provalue
Select COUNT (*) from Product1--Find the number of rows in a table * can also be replaced with a column name

--group by group (where and having are filter conditions after group by, where and having are filters)
--Grouping: You can display the information of grouped columns and the information after grouping to count separately
Select Column name 1,max (column name 2), Min (column name 3) from TableName where protypename= ' TV ' Group By column name 1
Select Protypename,max (Provalue), Min (provalue) from Product1 GROUP by Protypename has count (provalue) >1

Multi-Table Query

Copy Code code as follows:

--INNER JOIN INNER JOIN query two tables Common information
--from Query column name 1. Name= Column Name 2. Name is the association condition (the contents of the columns in the associated condition are consistent)
SELECT * FROM tablename INNER join Product2 on column name 1. Name= Column Name 2. Name
--Displays a partial column after the query, p1.* meaning to display all the columns of the P1
Select P1.*,proarea,probrand from Product1 as P1 inner join Product2 on P1. Procode=product2.procode
--product1 as P1 means to give Product1 an alias P1, as can omit
SELECT * from Product1 as P1 inner joins Product2 as P2 on P1. Procode=p2. Procode
--where query, omitting the 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 that the two tables are associated with, and then displays the data that is not associated
Go--< format: SELECT * FROM Table left\right\full outer JOIN table on association conditions >
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 connection
SELECT * FROM Product1 P1 full outer join Product2 P2 on P1. Procode=p2. Procode--All outer connection

--Cross connection (also called Cartesian connection: Crazy connection, N to n connection, no associated condition)
--Format: SELECT * FROM table Cross Join table
SELECT * from Product1 Cross join PRODUCT2
--Self-Connection (query: is to break a table into two tables to use)
Select C1.*,C2. Name from St_class c1,st_class C2 where c1.id=c2. Department and C1. Name= ' Computer College '
--Nested query
--Subquery returns multiple values
SELECT * from Product1 where Procode to (select Procode from Product2 where proarea= ' Beijing ')
SELECT * from Product1 where Procode isn't in (select Procode from Product2 where proarea= ' Beijing ')
--Subquery Returns a value
select* from Product1 where provalue= (select MAX (provalue) from Product1)

--The subquery can return multiple values with any (returns any of the "smallest" in the result) all (returns all "maximum" in the result)

--Union Query Union ALL (two separate subqueries are connected with UNION ALL)
Select Sname,ssex,sbirthday from STUDENT UNION ALL select Tname,tsex,tbirthday from TEACHER

Stored Procedures

Copy Code code as follows:

--Create/modify (alter) a stored procedure
ALTER PROC sp_smgetcity---sp_smgetcity is the name of the stored procedure
(
@code nvarchar (50)--The data type is the same as the character being compared
@name nvarchar () output,
@grade int=1
-' Note here: The stored procedure assigns an initial value, only in the last parameter '
---A @ symbol is a local variable
---Two @ symbol is a global variable
)
As
Begin
Select @name =name from Tbname where Code like @code + '% ' and grade= @grade the SQL statement to execute in the middle of--begin and end
Print @name--with output parameters
End

declare @outname nvarchar (50)--Define a variable
exec sp_smgetcity ' One ', @outname output--@aa Assign a variable to an output parameter to receive the return value
Select @outname

sp_help sp_smgetcity--View the creation information for a stored procedure named Sp_studentmanager
Sp_helptext sp_smgetcity--View the creation code for a stored procedure named Sp_studentmanager
drop proc sp_smgetcity--Deleting stored procedures

--return can only return integer data
--Delete stored procedure drop proc Sp_name
--EXEC (@aa)--executes @aa (SQL statement), so parentheses, execute the SQL statement in the string

Stored procedures Call Stored procedures
As
Begin
DECLARE @return int
exec @return =sp_checkuser@id, @name--Stored procedures call stored procedures
If @return =0
print ' does not repeat, return only integer '
Else
print ' User registered '
End

Some examples
Database Joint Authorization

Copy Code code 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 () "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 pivot (count (isture) for operatename in (' + @ctitle + ')) B '
EXEC (@sql)

Pager stored procedures, paging stored procedures

Copy Code code as follows:

ALTER PROC Cutpage
(
Table @tablename nvarchar,----pagination
@columnname nvarchar,----pagination columns
@ordertype nvarchar = ' asc ',----sort
@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) + ' and ' +convert (nvarchar), @pageindex *@ PageCount)
EXEC (@aa)--here @aa must be parenthesized ()

End

exec cutpage ' sm_class ', ' ClassID '

Transaction Transaction

Copy Code code as follows:

Explain-------transaction key statement
BEGIN TRANSACTION---transaction keyword TRANSACTION

DECLARE @errorSum INT
SET @errorSum = 0--initialized to, no error

Update Bank SET money=money+1000 where name= ' John '
SET @errorSum = @errorSum +@ @error
Update Bank SET money=money-1000 where name= ' dick '
SET @errorSum = @errorSum +@ @error-Cumulative error (@ @error non 0)

If @errorSum <>0
Begin
print ' does not succeed, there are errors, the error code is: '
Print @errorsum
ROLLBACK TRANSACTION
End
Else
Begin
print ' Success '
SELECT * FROM Bank
Commit TRANSACTION
End

Trigger Trigger

Copy Code code as follows:

--Concept: A special stored procedure that changes the data in this table (add) and automatically executes the statement that implements the definition
--Features: cascading modifications across related tables
--Keywords: trigger

Alter TRIGGER Trigger_name_f
On Sm_class
For update--(for is before adding or deleting changes after the implementation after add and remove changes, instead of all "increase | delete | change" All do not execute, before triggering, but do not change the value)
As
Begin
If update (remark)---Determine whether the remark column in the Sm_class table has data changes
Begin
SELECT * FROM inserted---table containing modified new values
SELECT * FROM deleted----The table containing the old modified values
print ' remark column changed '
End
Else
print ' Other columns changed '
print ' Test trigger, which is displayed when the table Sm_class is modified, and for the phrase "
End

Cursor Cursor

Copy Code code as follows:

--cursor is similar to a row of SQL Datereader reads
Usually in the last resort, the time is long, the server pressure, eat more memory, broadband,
--the cursor is the traversal read 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 one line (first read of course is the first line)
FETCH NEXT from Cur_test into @id, @name

--Determine if the data is read, the status is zero description read the data (@ @FETCH_STATUS =0)
While @ @FETCH_STATUS =0
Begin
Print @id
Print @name
print '----------------------'
--and then read the next line
FETCH NEXT from Cur_test into @id, @name
End
Close Cur_test
Deallocate cur_test

Scattered knowledge points
1 Intercept string: substring (field name, starting position (first 1), length of intercept)
Select SUBSTRING (age,1,1) from where id=2
2) About GUIDs:
Select NEWID ()
Insert person values (', ', NEWID ())
3 Insert a table (Person2) into another table (Person1) (Number of columns to correspond)
Insert Person1
Select columns, columns, columns from Person2
4 Conditional statement (Note: curly braces {} In C # are replaced with begin end in SQL)

Copy Code code 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 ' Rank '
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) to determine if there is an if exists (SELECT * from Tbname where cityname= ' 22 ')

6 Add auto Grow column Row_number over (ORDER BY * *) row_number is the keyword over which column to sort by
Select Row_number () over (order by ClassID), * from Sm_class
Select Rank () over (order by ClassID), * The From Sm_class---is also the line number that automatically grows, and if duplicates occur, the next value automatically adds two.

--the result returned by one query statement as the data source table for another query statement
SELECT * FROM (select Row_number () 1) Here is the new column name, * from Tbname) where the new table name where the new column name between 1 and 3
7) Temporary table
Declare @table table (ID uniqueidentifier,name varchar (50))
--Perform an insert operation (insert bar data) and return the HID field of this data to the @table table ID property
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 one: execution requires s
Declare @tempTable table (ID varchar (), name Int,score datetime)
Insert @tempTable (Id,name,score)
Select Userid,username,userscore from Scoretable
SELECT * FROM @tempTable

--sql statement II: Execution only requires s
DROP TABLE #Bicycles
SELECT Userid,username,userscore
Into #Bicycles
From scoretable
SELECT * FROM #Bicycles

8 about date and time operations
--access to Beijing time and international time in the database
Select GETDATE (), getUTCDate ()
--Increased time (increased type [year/month/day], increment, to who plus [current time/date]) DateAdd
Select DATEADD (Year,2,getdate ())----Add the current year to two years

--The subtraction of time DateDiff
Select DATEDIFF (Hour,getdate (), getUTCDate ())--international hours minus the current time in Beijing (back minus front)

--Get the year, month, day in time
Select year (GETDATE ())-year (Birthday ())
Select year (GETDATE ())-year (' 1988-10-07 ')
9) row and column conversion
SELECT * FROM (SELECT * to tablename) a pivot (count (stuname)) for columnName in (' AA ', ' BB ', ' cc ', ' DD ')
10 double quotes can only be used for table and column names (without double quotes)
Set @aa = ' Select ClassName ' SD ' from Sm_class '--note: ' The original ' SD ' is now written as ' SD '
EXEC (@aa)

-----------------pay more attention here------------------------------
declare @bb nvarchar (max);
--use of the data value can only be used ' electric business second class '
Set @bb = ' SELECT * from Sm_class where classname= ' ' Electric quotient class '-Note: The original ' Electric business second class ' to be written ' electric Business Second class '
EXEC (@bb)

11)--Create a table structure quickly
Select C.cid,c.ccount into newTB1 from ns_comment C where 1<>1
12)--Key to remember
declare @na nvarchar (a), @str nvarchar (max);
Set @str = ' Select top 1 @bb =classid from Sm_class '
--@str variables that contain SQL statements, define the variables in @str n is a string that receives the variable in @str (that is, @na= @bb)
exec sp_executesql@str, N ' @bb nvarchar output ', @na output
Select @na, @str
-------------Concurrency Problem--------
-Concept: Multiple users interact with an object (library, table) at the same time
--Problem: dirty data, non-duplicate read, lost update, Phantom read
--resolution: SQL Server uses locks to ensure transactional integrity (shared locks, exclusive locks, update locks, intent locks, schema locks, batch update locks)
14)