How to get ID from Inserted record and the table

Question

 

Hi everyone,

 

I like to ask that is regarding the TSQL statement on the Controller.

 

Now,  I have 2 INSERT statement in my controller action and it starts with the 'Payment' to 'Order' tables

 

My first INSERT statement for Payment is working but now my problem is with the 'Order' table

 

In my 'Order' table, I have 2 foreign keys, one is 'Payment_id' and 'Service_id'.

 

 

 

For the Payment_Id, I want to get from the INSERT statement that I created for the Payment.

As for the Services_Id, I want to get the Id based on the 'Services' table that I have and I already have a statement for this  which is:

(SELECT s.Services_id FROM Order o INNER JOIN Services s ON s.Services_id = o.Services_id)

In my controller:

 

public IActionResult SaveDetail(List<Cart_Has_Services> cart, double total, string currency)
       {
           string payment = @"INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount)
                            VALUES('{0}','{1}',{2});";
           int pay = DBUtl.ExecSQL(payment, "Cash", currency, total);
           if (pay == 1)
           {
               DataTable dt = DBUtl.GetTable("SELECT SCOPE_IDENTITY()");
               return View(dt.Rows);
               if(dt.Rows.Count > 0)
               {
                  var pid  = dt.Rows[0]["Payment_Id"].ToString();

               if (cart.Count() != 0)
               {
                   string order = @"INSERT INTO [Order](Order_Name,Order_Description,Order_Quantity,Payment_Id)
                        VALUES('{0}','{1}',{2},{3})";
                   foreach (var item in cart)
                   {
                       int ord = DBUtl.ExecSQL(order, item.Cart_Service, item.Additional_Notes, item.Quantity, pid);
                       if (ord == -1)
                       {
                           return RedirectToAction("CashCheckout");
                       }
                   }
                   return RedirectToAction("Success");
                   }

               else
                   return RedirectToAction("CashCheckout");
           }
           }
           else
           {
               return RedirectToAction("CashCheckout");
           }
       }

 As you can see, I am using SCOPE IDENTITY to get the 'payment_id' because the one that I have inserted into the 'Payment' table and I try to use DBUtl to GetTable and create an If else statement to count to row and get the value but is not working.

 

DButl that I am using:

 

using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Dynamic;
using System.IO;
using System.Reflection;

public static class DBUtl
{
   public static string DB_CONNECTION;
   public static string DB_SQL;        // Added From L09
   public static string DB_Message;

   static DBUtl()
   {
      IConfiguration config =
         new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();
      string env = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT");
      if (env.Equals("Development"))
         DB_CONNECTION = config.GetConnectionString("DefaultConnection");
      else if (env.Equals("Production"))
         DB_CONNECTION = config.GetConnectionString("ProductionConnection");
   }

   public static List<dynamic> GetList(string sql, params object[] list)
   {
      return GetTable(sql, list).ToDynamic();
   }

   public static List<ModelClass> GetList<ModelClass>(string sql, params object[] list)
   {
      return GetTable(sql, list).ToStatic<ModelClass>();
   }

   private static List<DTO> ToStatic<DTO>(this DataTable dt)
   {
      var list = new List<DTO>();
      foreach (DataRow row in dt.Rows)
      {
         DTO obj = (DTO)Activator.CreateInstance(typeof(DTO));
         foreach (DataColumn column in dt.Columns)
         {
            PropertyInfo Prop = obj.GetType().GetProperty(column.ColumnName, BindingFlags.Public | BindingFlags.Instance);
            if (row[column] == DBNull.Value)
               Prop?.SetValue(obj, null);
            else
            {
               //Debug.WriteLine(row[column].GetType() + " " + Prop?.PropertyType);
               if (row[column].GetType() == Prop?.PropertyType)
                  Prop?.SetValue(obj, row[column]);
            }
         }
         list.Add(obj);
      }
      return list;
   }

   private static List<dynamic> ToDynamic(this DataTable dt)
   {
      var dynamicDt = new List<dynamic>();
      foreach (DataRow row in dt.Rows)
      {
         dynamic dyn = new ExpandoObject();
         foreach (DataColumn column in dt.Columns)
         {
            var dic = (IDictionary<string, object>)dyn;
            dic[column.ColumnName] = row[column];
         }
         dynamicDt.Add(dyn);
      }
      return dynamicDt;
   }

   public static DataTable GetTable(string sql, params object[] list)
   {
      for (int i = 0; i < list.Length; i++)
         if (list[i] is string)
            list[i] = list[i].ToString().EscQuote();

      DB_SQL = String.Format(sql, list);

      DataTable dt = new DataTable();
      using (SqlConnection dbConn = new SqlConnection(DB_CONNECTION))
      using (SqlDataAdapter dAdptr = new SqlDataAdapter(DB_SQL, dbConn))
      {
         try
         {
            dAdptr.Fill(dt);
            return dt;
         }

         catch (System.Exception ex)
         {
            DB_Message = ex.Message;
            return null;
         }
      }
   }

   public static int ExecSQL(string sql, params object[] list)
   {
        for (int i = 0; i < list.Length; i++)
            if (list[i] is string)
                list[i] = list[i].ToString().EscQuote();

      DB_SQL = String.Format(sql, list);

      int rowsAffected = 0;
      using (SqlConnection dbConn = new SqlConnection(DB_CONNECTION))
      using (SqlCommand dbCmd = dbConn.CreateCommand())
      {
         try
         {
            dbConn.Open();
            dbCmd.CommandText = DB_SQL;
            rowsAffected = dbCmd.ExecuteNonQuery();
         }

         catch (System.Exception ex)
         {
            DB_Message = ex.Message;
            rowsAffected = -1;
         }
      }
      return rowsAffected;
   }

   public static string EscQuote(this string line)
   {
      return line?.Replace("'", "''");
   }


}

 

How can I achieve this.

 

Please help me

 

Thank you