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].[WITHDRAW1]
@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 WithDraw
Insert into WithDraw 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 button1_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(textBox1.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 ='" + textBox1.Text + "'and First_Name='" + textBox2.Text + "'";
j = cmd.ExecuteNonQuery();
if (j == 1)
{
label5.Text = "Trnscation is Completed";
trans.Commit();
MessageBox.Show("amount is debited....");
try
{
string ConnectionString = ConfigurationManager.ConnectionStrings["BANKINGTHREETIRE.Properties.Settings.LoginConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(ConnectionString))
{
//Create the SqlCommand object
SqlCommand cmd1 = new SqlCommand("WITHDRAW1", 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", textBox1.Text);
cmd1.Parameters.AddWithValue("@Account_Holder_Name", textBox2.Text);
cmd1.Parameters.AddWithValue("@Amount", textBox3.Text);
cmd1.Parameters.AddWithValue("@Sort_Code", textBox4.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";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
TRANSACTIONPRINT ts = new TRANSACTIONPRINT();
this.Hide();
ts.label1.Text = textBox1.Text.ToString();
ts.label3.Text = textBox2.Text.ToString();
ts.label2.Text = textBox3.Text.ToString();
ts.label4.Text = textBox4.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.
No comments:
Post a Comment