Wcf Rest服务中的SQL查询到Linq查询
问题描述:
我将一个sql Transcation查询转换为Linq查询。我已经做了一点点,我试图写其余的linq查询,但我很困惑。Wcf Rest服务中的SQL查询到Linq查询
这是ADO.NET代码与Sql查询。
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;
}
到目前为止,我已经做了这么多的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;
}
}
}
}
我对这个代码混淆..
cmd1.CommandText = "update Reward_Account_Details set Account_Balance=Account_Balance-'" + mopneyTransfer.Amount + "' where Account_Number='" + mopneyTransfer.Sender_Account_No + "'";
任何一个可以帮助我如何完成在linq中查询的其余部分
答
在你使用的内部,你应该发出这样的另一个查询:
var rewardAccount = ctx.Reward_Account_Details
.Where(r => r.Account_Number = moneyTransfer.Sender_Account_No)
.FirstOrDefault();
rewardAccount.Account_Balance -= moneyTransfer.Ammount;
既然你有从HalifaxDatabaseEntities上下文Reward_Account_Details,调用的SaveChanges将节省这种变化也。
编辑
你并不需要显式地打开一个事务。 EF会自动为你做。请参阅以下代码并将其编辑为您的要求。
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;
}
LINQ不会处理事务。它用于读取数据。 – Neolisk
是否有解决这个问题的方法? – Mohammad
不是它的设计。你应该只使用LINQ来读取数据。使用ADO.NET更新数据非常好。如果你想要更先进的东西,看看实体框架。 – Neolisk