SunnyInNorway

there are two Scenes in my projects

(1) C# project, we use entityframwork to do mssql database operation.however, I would like to get a value caculated with parameters in the tables cross 3 databases.of course ,we do not want to change any database structure,however, there is one database connected to entityframework.

now we can use stored procedure in entityframwork,
(1) create stored procedure in the database
(2) add stored procedure in data model
(3) call the stored procedure in the code .

btw:if you worked with other people who will be responsible for database, only store procedure name and parameter are right is enough when we import store procedure in datamodel.

string dateString = Convert.ToDateTime(current).ToString("yyyy-MM-dd");<br>var sqlstorePrecedure = $"EXEC dbo.spCalculateReportDaily '{_serviceLocator.SettingsManager.SiteName}','{dateString}'";


var totalProfits = gameServerEntities.Database.SqlQuery(sqlstorePrecedure).Single();

(2) customer charge money function in web page(used in asp.net+c#)，we customer made transaction with bank successful, web page need do following things

(1) add transaction logs to database

(2) update credit for customer,

we have found that there one problem that customer can query the logs to add credit succesffully but they found the credit is not updated.

this kind of inconsistency is cause by two query separately with remote database.

if we used store procedure to handle these two things together or more ,program always call one single procedure to handle all the things ,which will make sure all the function will be done when transaction is ok.

 using (SqlConnection conn = new SqlConnection(ConfigurationData.DBConnStr))
                {

                    if (conn.State == System.Data.ConnectionState.Closed)
                    {
                        conn.Open();
                    }

using (SqlCommand cmd = new SqlCommand("spUpdateCustomerTransaction", conn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@WebTransactionId", new Guid(TransactionId));
                        cmd.Parameters.AddWithValue("@MobileNumber", mobileNumbrer);
                        cmd.Parameters.AddWithValue("@TransactionDate", DateTime.Now.ToString(CultureInfo.InvariantCulture));
                        cmd.Parameters.AddWithValue("@OrderNumber", Session["OrderNumber"].ToString());
                        cmd.Parameters.AddWithValue("@ChargeMoney", money);
                        cmd.Parameters.AddWithValue("@HallName", hallName);

cmd.ExecuteNonQuery();


 }

conn.Close();
}