In this video tutorial we will learn how to update two table records by using windows from application. To continue with this session you must have knowledge about Object Oriented Programming Languages (C#) because we will be using c# programming languages to write the code .
Software Required :
1.Microsoft SQL Server .
2.Microsoft SQL Management Studio.
3.visual studio 2017 .
First we need to create database in SQL server .I already created database and procedure in previous lesson . So please follow previous lesson . Only things we need to do is create new class , interface and implement the interface .Here is the class ..
Double the button to generate event ,copy and paste the following codes .
[DataContract]
public class MopneyTransfer
{
string sender_account_no;
string sender_name;
string sender_sort_code;
string amount1;
string transcation_type;
string date;
string receiver_account_no;
string receiver_name;
string receiver_sort_code;
string amount2;
string transcation_type1;
string date1;
[DataMember]
public string Sender_Account_No
{
get { return sender_account_no; }
set { sender_account_no = value; }
}
[DataMember]
public string Sender_Name
{
get { return sender_name; }
set { sender_name = value; }
}
[DataMember]
public string Sender_Sort_Code
{
get { return sender_sort_code; }
set { sender_sort_code = value; }
}
[DataMember]
public string Amount
{
get { return amount1; }
set { amount1 = value; }
}
[DataMember]
public string Transcation_Type
{
get { return transcation_type; }
set { transcation_type = value; }
}
[DataMember]
public string Date
{
get { return date; }
set { date = value; }
}
[DataMember]
public string Receiver_Account_No
{
get { return receiver_account_no; }
set { receiver_account_no = value; }
}
[DataMember]
public string Receiver_Name
{
get { return receiver_name; }
set { receiver_name = value; }
}
[DataMember]
public string Receiver_Sort_Code
{
get { return receiver_sort_code; }
set { receiver_sort_code = value; }
}
[DataMember]
public string Amount1
{
get { return amount2; }
set { amount2 = value; }
}
[DataMember]
public string Transcation_Type1
{
get { return transcation_type1; }
set { transcation_type1 = value; }
}
[DataMember]
public string Date1
{
get { return date1; }
set { date1 = value; }
}
}
Here is the Interface ..
[ServiceContract]
public interface IMyCompanyConfidentialService
{
[OperationContract, TransactionFlow(TransactionFlowOption.Allowed)]
string Transcation(MopneyTransfer mopneyTransfer);
}
Here is the implementation of the interface .
[OperationBehavior(TransactionScopeRequired = true, TransactionAutoComplete = true)]
public string Transcation(MopneyTransfer mopneyTransfer)
{
string Message;
int amount = System.Convert.ToInt32(mopneyTransfer.Amount);
int amount1 = System.Convert.ToInt32(mopneyTransfer.Amount1);
SqlConnection cn = new SqlConnection(ConnectionString);
string sql = "select Account_Balance from Account_Details where Account_Number='" + mopneyTransfer.Sender_Account_No + "'";
SqlCommand cmd = new SqlCommand(sql, cn);
if (cn.State == ConnectionState.Closed)
cn.Open();
//amount = int.Parse(cmd.ExecuteScalar().ToString());
if (amount > 0)
{
int b;
int b1;
SqlCommand cmd1 = new SqlCommand();
SqlTransaction trans;
if (cn.State == ConnectionState.Closed)
cn.Open();
trans = cn.BeginTransaction();
cmd1.Connection = cn;
cmd1.CommandType = CommandType.Text;
cmd1.Transaction = trans;
cmd1.CommandText = "update Account_Details set Account_Balance=Account_Balance-'" + mopneyTransfer.Amount + "' where Account_Number='" + mopneyTransfer.Sender_Account_No + "'";
b = cmd1.ExecuteNonQuery();
cmd1.CommandText = "update Account_Details set Account_Balance=Account_Balance+'" + mopneyTransfer.Amount1 + "' where Account_Number='" + mopneyTransfer.Receiver_Account_No + "'";
b1 = cmd1.ExecuteNonQuery();
if (b == 1 && b1 == 1)
{
trans.Commit();
using (SqlConnection con = new SqlConnection(ConnectionString))
{
//Create the SqlCommand object
//Create the SqlCommand object
SqlCommand cmd3 = new SqlCommand("WITHDRAW1", con);
//Specify that the SqlCommand is a stored procedure
cmd3.CommandType = System.Data.CommandType.StoredProcedure;
//Add the input parameters to the command object
cmd3.Parameters.AddWithValue("@Account_Number", mopneyTransfer.Sender_Account_No);
cmd3.Parameters.AddWithValue("@Account_Holder_Name", mopneyTransfer.Sender_Name);
cmd3.Parameters.AddWithValue("@Amount", mopneyTransfer.Amount);
cmd3.Parameters.AddWithValue("@Sort_Code", mopneyTransfer.Sender_Sort_Code);
cmd3.Parameters.AddWithValue("@Transcation_Type", mopneyTransfer.Transcation_Type);
cmd3.Parameters.AddWithValue("@Date", mopneyTransfer.Date);
SqlCommand cmd2 = new SqlCommand("DEPOSIT1", con);
//Specify that the SqlCommand is a stored procedure
cmd2.CommandType = System.Data.CommandType.StoredProcedure;
//Add the input parameters to the command object
cmd2.Parameters.AddWithValue("@Account_Number", mopneyTransfer.Receiver_Account_No);
cmd2.Parameters.AddWithValue("@Account_Holder_Name", mopneyTransfer.Receiver_Name);
cmd2.Parameters.AddWithValue("@Amount", mopneyTransfer.Amount1);
cmd2.Parameters.AddWithValue("@Sort_Code", mopneyTransfer.Receiver_Sort_Code);
cmd2.Parameters.AddWithValue("@Transcation_Type", mopneyTransfer.Transcation_Type1);
cmd2.Parameters.AddWithValue("@Date", mopneyTransfer.Date1);
//Open the connection and execute the query
con.Open();
cmd2.ExecuteNonQuery();
cmd3.ExecuteNonQuery();
//con.Close();
}
}
else
trans.Rollback();
}
Message = "Record is inserted successfully";
return Message;
}
Here is the windows form design .
Double the button to generate event ,copy and paste the following codes .
private void button1_Click(object sender, EventArgs e)
{
//MyService.Service1Client myservice = new WindowsFormsBankWfcApplication.MyService.Service1Client();
MyService.MyCompanyConfidentialServiceClient my = new WindowsFormsBankWfcApplication.MyService.MyCompanyConfidentialServiceClient("NetTcpBinding_IMyCompanyConfidentialService");
MyService.MopneyTransfer ms = new WindowsFormsBankWfcApplication.MyService.MopneyTransfer();
ms.Sender_Account_No = textBox5.Text;
ms.Sender_Name = textBox4.Text;
ms.Sender_Sort_Code = textBox6.Text;
ms.Amount = textBox9.Text;
ms.Transcation_Type = textBox10.Text;
ms.Date = dateTimePicker1.Text;
ms.Receiver_Account_No = textBox1.Text;
ms.Receiver_Name = textBox2.Text;
ms.Receiver_Sort_Code = textBox7.Text;
ms.Amount1 = textBox3.Text;
ms.Transcation_Type1 = textBox8.Text;
ms.Date1 = dateTimePicker2.Text;
my.Transcation(ms);
label7.Text = "Money transfered completed";
}
If you done everything correctly you will get the expected message in label control.
No comments:
Post a Comment