Sql Transcation Query to Linq Query in Wcf Rest Service

Question

I am converting one sql Transcation query to Linq query . I have done little bit and i am trying to write rest of the linq query but i am confused ..

Here is the ADO.NET Code with Sql Query.

 public bool RewardToCurrentTranscation(MoneyTransfer mopneyTransfer)
        {
            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 Reward_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 Reward_Account_Details set Account_Balance=Account_Balance-'" + mopneyTransfer.Amount + "' where Account_Number='" + mopneyTransfer.Sender_Account_No + "'";
                b = cmd1.ExecuteNonQuery();

                cmd1.CommandText = "update Current_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("Reward_Account_Wdraw", 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("Current_Account_Dposit", 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();
                        return true;
                        //con.Close();
                    }

                }
                else
                    trans.Rollback();
                return false;
            }
            return false;
        }

So far i have done this much with Linq ...

public bool MoneyTranfer(MoneyTransfer mopneyTransfer)
    {


        int i = Convert.ToInt32(mopneyTransfer.Amount);
        int j = Convert.ToInt32(mopneyTransfer.Amount1);

        using (HalifaxDatabaseEntities ctx = new HalifaxDatabaseEntities())
        {

            var foundAccount = (from a in ctx.Current_Account_Details where a.Account_Number.Equals(mopneyTransfer.Sender_Account_No)
                                select a).Distinct().FirstOrDefault();

            using (var dbContextTransaction = ctx.Database.BeginTransaction())
            {
                var acctDetails = (from a in ctx.Current_Account_Details
                                   where a.Account_Number.Equals(mopneyTransfer.Sender_Account_No)
                                   select a).Distinct().FirstOrDefault();

                if (acctDetails != null)
                {
                    ctx.SaveChanges();
                    dbContextTransaction.Commit();
                      }
                else
                {
                    dbContextTransaction.Rollback();
                    return false;
                }
            }

        }
    }

I am confused on this code ..

 cmd1.CommandText = "update Reward_Account_Details set Account_Balance=Account_Balance-'" + mopneyTransfer.Amount + "' where Account_Number='" + mopneyTransfer.Sender_Account_No + "'";

Can any one help me how to complete the rest of the query in linq

No it's by design. You should only use LINQ for reading data. Updating data using ADO.NET is perfectly fine. If you want something more advanced, look into entity framework. — Victor Zakharov, Oct 20 '17 at 01:54
Also be aware of SQL injection, use parameters. https://stackoverflow.com/questions/601300/what-is-sql-injection — Victor Zakharov, Oct 20 '17 at 01:59
You may also want to look at Dapper [link](https://github.com/StackExchange/Dapper) as a lightweight alternative to Entity Framework. — Jim B, Oct 20 '17 at 03:39

Robert · Accepted Answer · 2017-10-20T10:09:37.717

Inside your using you should issue another query like this:

var rewardAccount = ctx.Reward_Account_Details
    .Where(r => r.Account_Number = moneyTransfer.Sender_Account_No)
    .FirstOrDefault();

rewardAccount.Account_Balance -= moneyTransfer.Ammount;

Since you got the Reward_Account_Details from your HalifaxDatabaseEntities context, calling SaveChanges will save this change also.

EDIT

You do not need to explicitly open a transaction. EF will do it for you autmatically. See following code and edit it to your requirements.

public bool MoneyTranfer(MoneyTransfer mopneyTransfer)
{
    int i = Convert.ToInt32(mopneyTransfer.Amount);
    int j = Convert.ToInt32(mopneyTransfer.Amount1);

    try
    {
        using (HalifaxDatabaseEntities ctx = new HalifaxDatabaseEntities())
        {
            var foundAccount = (from a in ctx.Current_Account_Details where a.Account_Number.Equals(mopneyTransfer.Sender_Account_No)
                                select a).Distinct().FirstOrDefault();

            var acctDetails = (from a in ctx.Current_Account_Details
                               where a.Account_Number.Equals(mopneyTransfer.Sender_Account_No)
                               select a).Distinct().FirstOrDefault();

            if(acctDetails == null)
                return false;

            var rewardAccount = ctx.Reward_Account_Details
                .Where(r => r.Account_Number = moneyTransfer.Sender_Account_No)
                .FirstOrDefault();

            rewardAccount.Account_Balance -= moneyTransfer.Ammount;

            ctx.SaveChanges();

            return true;
        }
    }
    catch(Exception)
    {
        //Maybe log it.
    }
    return false;
}

Shall i call save change method after this rewardAccount.Account_Balance -= line ?? — , Oct 20 '17 at 09:58
I have an variable called amount . I am comparing it inside the if statements. Where i need to add this variable like (amount >0) — , Oct 20 '17 at 10:14
Have you actually tried anything rather than asking constantly how to code your business logic? — Robert, Oct 20 '17 at 10:21

Sql Transcation Query to Linq Query in Wcf Rest Service

1 Answers1