. NET notes

[Csharp] <p> 1. the statement string str_conn = "server = .; database = Employees; Trusted_Connection = SSPI; "; </p> <p> 2. use DataSet to obtain data returned by the database </p> [csharp] string str_conn = "server = .; database = Employees; Trusted_Connection = SSPI; "; string SQL = string. format ("select * from Admin where LoginID = '{0}' and Password = '{1}'", this. textBoxUser. text = loginId, this. textBoxPas. text = loginPwd); SqlConnection sqlconn = new SqlConnection (str_conn); sqlconn. open (); SqlDataAdapter sda = new SqlDataAdapter (SQL, str_conn); DataSet ds1 = new DataSet (); sda. fill (ds1); int count = ds1.Tables [0]. rows. count; CREATE stored procedure: create proc [EDURE] stored procedure name @ parameter 1 data type = default value OUTPUT ,......, @ Parameter n data type = default output as SQL statement GO T-sqlDECLARE @ variable name data type SET @ variable name = value SELECT @ variable name = value IF (condition) BEGIN Statement 1 Statement 2 ...... Endelse begin Statement 1; Statement 2 ;...... ENDWHILE (condition) BEGIN Statement 1 Statement 2 ...... Break end trigger: create trigger trigger_name ON table_name [with encryption] FOR [DELETE, INSERT, UPDATE] AS T-SQL statement GO instance: create trigger trig_transInfo ON transInfo for insert as declare @ type char (4), @ outMoney money declare @ myCardIDchar (10), @ balance money select @ type = transType, @ outMoney = transMoney, @ myCardID = cardIDFROM inserted IF (@ type = 'taobao') UPDATE bank SET currentMoney = currentMoney-@ outMoney WHER E cardID = @ myCardID else update bank SET currentMoney = currentMoney + @ outMoney WHERE cardID = @ myCardID ..... Go transaction: begin transaction/* -- defines variables for errors during TRANSACTION execution -- */DECLARE @ errorSum INTSET @ errorSum = 0 -- Initialization is 0, that is, there is no error/* -- Transfer: Zhang San's account is less than 1000 yuan, li Si account more than 1000 yuan */UPDATE bank SET currentMoney = currentMoney-1000 WHERE customerName = 'zhang san' SET @ errorSum = @ errorSum + @ errorUPDATE bank SET currentMoney = currentMoney + 1000 WHERE customerName = 'Li si' SET @ errorSum = @ errorSum + @ error -- whether there is a cumulative error IF @ errorSum <> 0 -- IF a BEGIN print error occurs, roll back the transaction 'rollback T Ransaction end else begin print 'transaction successful, transaction committed, written to hard disk, permanent save 'committransaction ENDGOprint 'view the balance after the transfer transaction 'select * FROMbank GO cursor: l use the declare cursor statement to associate the Transact-SQL CURSOR with the SELECT statement. L In addition, The declare cursor statement also defines the features of the CURSOR, such as the name of the CURSOR and whether the CURSOR is read-only or only-in. L declare the cursor lDECLARE cursor_name CURSORl FORSELECT... FROM table WHERE... L open the cursor lOPEN cursor_namel read result set data lFETCH next from cursor_namel according to @ FETCH_STATUS to determine whether to continue reading data lWHILE @ FETCH_STATUS = 0 lBEGINl T-SQL statement l fetch next from cursor_namelENDl close the cursor cursor_namel Delete cursor lDEALLOCATE cursor_name DataReader class: • Permanent connection required • Read-only and Read-only access • The Read method enables Reader to move forward to the next record. When multiple result sets exist, separate the select statements with semicolons, and use the NextResult method to access the next result set, which can save the number of connections to the database. • Execute a given query, retrieve rows from the data stream, and bind the result set to a given instance of the DataReader class • There are two methods for fields in the access record: • 1. Item attribute (directly retrieve the specified entry of the index) 2. GetXXX method (this method automatically converts the type according to the suffix) such as myReader. getString (1 );