SQL transactions (stored procedures and transactions) and SQL transaction stored procedures

SQL transactions (stored procedures and transactions) and SQL transaction stored procedures

<1>

C # two more methods for executing SQL transactions (summarized by myself)

1. stored procedures. Now we will demonstrate how to execute SQL transactions through stored procedures.

2. Use the Transaction provided in C.

SQL Server transaction syntax

SQL server transaction usage

<1> Create a stored procedure,

Use sales -- specify database create table bb -- create bb table (ID int not null primary key, -- account Moneys money -- transfer amount) -- insert into bb values ('1', 2000) into two pieces of data in the bb table -- 2000 yuan in account 1 insert into bb values ('2', 3000) -- account 2 has 3000 yuan goif (exists (select * from sys. objects where name = 'proc _ bb ')) -- If the stored procedure exists, delete the drop proc Proc_bbgocreate proc Proc_bb -- the Stored Procedure named Proc_bb has three parameters (@ fromID int, -- transfer account @ toID int, -- account receiving the transfer @ momeys money -- Transfer Account amount) asbegin tran -- start to execute the transaction update bb set Moneys = Moneys-@ momeys where ID = @ fromID --- the first operation executed, original transfer amount-transfer amount update bb set Moneys = Moneys + @ momeys where ID = @ toID -- perform the second operation, accept the original transfer amount + transfer amount if @ ERROR <> 0 -- determines if any of the two statements has an ERROR. (If the preceding SQL statement is not executed incorrectly, 0 is returned.) beginrollback tran -- start transaction rollback, return 0 endelse -- if both statements are successfully executed begin commit tran -- execute the transaction operation endgoexec Proc_bb 2000, -- execute this stored procedure; the transfer account is 1. The receiving account is 2. The transfer amount is 2000 yuan.

Execute the above stored procedure in C #

WebForm2.aspx. cs page

Using System; using System. collections. generic; using System. linq; using System. web; using System. web. UI; using System. web. UI. webControls; using System. configuration; using System. data. sqlClient; namespace user activation {public partial class WebForm2: System. web. UI. page {protected void Page_Load (object sender, EventArgs e) {} protected void button#click (object sender, EventArgs e) {string connStr = ConfigurationManager. connectionStrings ["ConnStr"]. connectionString; using (SqlConnection conn = new SqlConnection (connStr) {conn. open (); using (SqlCommand cmd = conn. createCommand () {// SqlParameter sqlParameter = new SqlParameter (); SqlParameter [] sqlParameters = {new SqlParameter ("@ toID", ToID. text. trim (), new SqlParameter ("fromID", FromID. text. trim (), new SqlParameter ("@ money", Money. text. trim ()}; cmd. commandText = "exec Proc_bb @ toID, @ fromID, @ money"; cmd. parameters. addRange (sqlParameters); int I = cmd. executeNonQuery (); if (I> 0) {Msg. text = "transfer successful";} else {Msg. text = "Transfer failed ";}}}}}}

WebForm2.aspx page

<% @ Page Language = "C #" AutoEventWireup = "true" CodeBehind = "WebForm2.aspx. cs "Inherits =" user activation. webForm2 "%> 






Zookeeper