SQL Transaction Operation With Windows Form Application

In this video tutorial we will learn how to update database single  record by using windows from  application and SQL Transaction method. To continue with this session please watch previous video. 

Please share if you think these video are useful .Late video we will learn how to consume WCF ,WCF(REST) service into Angular JS Application, Windows Form Application, Console Application etc . 

Here is what we i want do . First we need to create database in SQL server .Here is the SQL Script to Create the Database  table and insert some data  but we will be doing it by using windows form application .I already created the required table and inserted some sample data.  

CREATE TABLE [dbo].[ACCOUNT](
[Account_Number] [int] IDENTITY(1,1) NOT NULL,
[Tittle] [nvarchar](50) NULL,
[First_Name] [nvarchar](50) NULL,
[Last_Name] [nvarchar](50) NULL,
[Occupation] [nvarchar](50) NULL,
[DOB] [nvarchar](50) NULL,
[Mobile] [nvarchar](50) NULL,
[Home_No] [nvarchar](50) NULL,
[Street_Name] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[Post_Code] [nvarchar](50) NULL,
[Gender] [nvarchar](50) NULL,
[Anual_Salary] [money] NULL,
[statementamount] [money] NULL,
[Email] [nvarchar](50) NULL,
[Nationality] [nvarchar](50) NULL,
[FileName] [nvarchar](500) NULL,
[FileData] [varbinary](max) NULL,
[Type_Of_Account] [nvarchar](50) NULL,
[Branch_Name] [nvarchar](50) NULL,
[Sort_Code] [nvarchar](50) NULL,
[Over_Draft] [money] NULL,
[physicallyChallenged] [nvarchar](50) NULL,

)

Here is the Script to Create Store Procedure .

Create Procedure [dbo].[DEPOSIT1]

@Account_Number [nvarchar](max),

@Account_Holder_Name [nvarchar](max),

@Amount money,

@Sort_Code [nvarchar](max),

@Transcation_Type [nvarchar](max),

@Date [nvarchar](max) 

as

Begin 

select * from  Deposit

Insert into Deposit values(@Account_Number,@Account_Holder_Name,@Amount,@Sort_Code,@Transcation_Type,@Date)

End

GO

we want to update account balance by providing Account Number. 

Here is How we want to design windows form application .

Just double click the button to generate the button event .Copy and paste following codes .

       private void button3_Click(object sender, EventArgs e)
        {
            if (textBox3.Text == "" || textBox2.Text == "" || textBox1.Text == "")
            {
                MessageBox.Show("Please provide information");
                return;
            }
            //int c = System.Convert.ToInt32(textBox3.Text);
            int a = System.Convert.ToInt32(text1.Text);
            int j;
            SqlConnection cn = new SqlConnection(@"Data Source=.;Initial Catalog=Login;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("SELECT(*)FROM ACCOUNT");
            SqlTransaction trans;
            cn.Open();
            trans = cn.BeginTransaction();
            cmd.Connection = cn;

            cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE ACCOUNT set statementamount=statementamount +'" + textBox3.Text + "' where Account_Number ='" + text1.Text + "'and First_Name='" + textBox2.Text + "'";
            j = cmd.ExecuteNonQuery();
            if (j == 1)
            {
                label5.Text = "Trnscation is Completed";
                trans.Commit();
                MessageBox.Show("Deposit is added into your account....");
             
                    string ConnectionString = ConfigurationManager.ConnectionStrings["BANKINGTHREETIRE.Properties.Settings.LoginConnectionString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(ConnectionString))
                    {
                        //Create the SqlCommand object
                        SqlCommand cmd1 = new SqlCommand("DEPOSIT1", con);
                        //Specify that the SqlCommand is a stored procedure
                        cmd1.CommandType = System.Data.CommandType.StoredProcedure;

                        //Add the input parameters to the command object
                        cmd1.Parameters.AddWithValue("@Account_Number", text1.Text);
                        cmd1.Parameters.AddWithValue("@Account_Holder_Name", textBox2.Text);
                        cmd1.Parameters.AddWithValue("@Amount", textBox3.Text);


                        cmd1.Parameters.AddWithValue("@Sort_Code", textBox1.Text);
                        cmd1.Parameters.AddWithValue("@Transcation_Type", comboBox1.SelectedItem.ToString());
                        cmd1.Parameters.AddWithValue("@Date", dateTimePicker1.Text);



                        //Open the connection and execute the query
                        con.Open();
                        cmd1.ExecuteNonQuery();

                        //Retrieve the value of the output parameter
                        MessageBox.Show("Record is inserted successfully");
                        label4.Text = "Record is inserted successfully";
                        TRANSACTIONPRINT ts = new TRANSACTIONPRINT();
                        this.Hide();
                        ts.label1.Text = text1.Text.ToString();
                        ts.label3.Text = textBox2.Text.ToString();
                        ts.label2.Text = textBox3.Text.ToString();
                        ts.label4.Text = textBox1.Text.ToString();
                        ts.label5.Text = comboBox1.SelectedItem.ToString();
                        ts.Show();

                    }
                }
         



            else
                trans.Rollback();
            label5.Text = "Trnscation is Failed";
        }
    }

If done everything correctly you will be able to update specific account balance and will display the message inside the Label control  .