8
Answers

How to get ID from Inserted record and the table

Yong Kevin

Yong Kevin
4y
966
1
 
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:
  1. (SELECT s.Services_id FROM Order o INNER JOIN Services s ON s.Services_id = o.Services_id)  
In my controller:
 
  1. public IActionResult SaveDetail(List<Cart_Has_Services> cart, double total, string currency)  
  2.        {  
  3.            string payment = @"INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount)   
  4.                             VALUES('{0}','{1}',{2});";  
  5.            int pay = DBUtl.ExecSQL(payment, "Cash", currency, total);  
  6.            if (pay == 1)  
  7.            {  
  8.                DataTable dt = DBUtl.GetTable("SELECT SCOPE_IDENTITY()");  
  9.                return View(dt.Rows);  
  10.                if(dt.Rows.Count > 0)  
  11.                {  
  12.                   var pid  = dt.Rows[0]["Payment_Id"].ToString();  
  13.                 
  14.                if (cart.Count() != 0)  
  15.                {  
  16.                    string order = @"INSERT INTO [Order](Order_Name,Order_Description,Order_Quantity,Payment_Id)   
  17.                         VALUES('{0}','{1}',{2},{3})";  
  18.                    foreach (var item in cart)  
  19.                    {  
  20.                        int ord = DBUtl.ExecSQL(order, item.Cart_Service, item.Additional_Notes, item.Quantity, pid);  
  21.                        if (ord == -1)  
  22.                        {  
  23.                            return RedirectToAction("CashCheckout");  
  24.                        }  
  25.                    }  
  26.                    return RedirectToAction("Success");  
  27.                    }   
  28.                  
  29.                else  
  30.                    return RedirectToAction("CashCheckout");  
  31.            }  
  32.            }  
  33.            else  
  34.            {  
  35.                return RedirectToAction("CashCheckout");  
  36.            }  
  37.        }  
 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:
 
  1. using Microsoft.Extensions.Configuration;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Diagnostics;  
  7. using System.Dynamic;  
  8. using System.IO;  
  9. using System.Reflection;  
  10.   
  11. public static class DBUtl  
  12. {  
  13.    public static string DB_CONNECTION;  
  14.    public static string DB_SQL;        // Added From L09  
  15.    public static string DB_Message;  
  16.   
  17.    static DBUtl()  
  18.    {  
  19.       IConfiguration config =  
  20.          new ConfigurationBuilder()  
  21.             .SetBasePath(Directory.GetCurrentDirectory())  
  22.             .AddJsonFile("appsettings.json")  
  23.             .Build();  
  24.       string env = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT");  
  25.       if (env.Equals("Development"))  
  26.          DB_CONNECTION = config.GetConnectionString("DefaultConnection");  
  27.       else if (env.Equals("Production"))  
  28.          DB_CONNECTION = config.GetConnectionString("ProductionConnection");  
  29.    }  
  30.   
  31.    public static List<dynamic> GetList(string sql, params object[] list)  
  32.    {  
  33.       return GetTable(sql, list).ToDynamic();  
  34.    }  
  35.   
  36.    public static List<ModelClass> GetList<ModelClass>(string sql, params object[] list)  
  37.    {  
  38.       return GetTable(sql, list).ToStatic<ModelClass>();  
  39.    }  
  40.   
  41.    private static List<DTO> ToStatic<DTO>(this DataTable dt)  
  42.    {  
  43.       var list = new List<DTO>();  
  44.       foreach (DataRow row in dt.Rows)  
  45.       {  
  46.          DTO obj = (DTO)Activator.CreateInstance(typeof(DTO));  
  47.          foreach (DataColumn column in dt.Columns)  
  48.          {  
  49.             PropertyInfo Prop = obj.GetType().GetProperty(column.ColumnName, BindingFlags.Public | BindingFlags.Instance);  
  50.             if (row[column] == DBNull.Value)  
  51.                Prop?.SetValue(obj, null);  
  52.             else  
  53.             {  
  54.                //Debug.WriteLine(row[column].GetType() + " " + Prop?.PropertyType);   
  55.                if (row[column].GetType() == Prop?.PropertyType)  
  56.                   Prop?.SetValue(obj, row[column]);  
  57.             }  
  58.          }  
  59.          list.Add(obj);  
  60.       }  
  61.       return list;  
  62.    }  
  63.   
  64.    private static List<dynamic> ToDynamic(this DataTable dt)  
  65.    {  
  66.       var dynamicDt = new List<dynamic>();  
  67.       foreach (DataRow row in dt.Rows)  
  68.       {  
  69.          dynamic dyn = new ExpandoObject();  
  70.          foreach (DataColumn column in dt.Columns)  
  71.          {  
  72.             var dic = (IDictionary<string, object>)dyn;  
  73.             dic[column.ColumnName] = row[column];  
  74.          }  
  75.          dynamicDt.Add(dyn);  
  76.       }  
  77.       return dynamicDt;  
  78.    }  
  79.   
  80.    public static DataTable GetTable(string sql, params object[] list)  
  81.    {  
  82.       for (int i = 0; i < list.Length; i++)  
  83.          if (list[i] is string)  
  84.             list[i] = list[i].ToString().EscQuote();  
  85.   
  86.       DB_SQL = String.Format(sql, list);  
  87.   
  88.       DataTable dt = new DataTable();  
  89.       using (SqlConnection dbConn = new SqlConnection(DB_CONNECTION))  
  90.       using (SqlDataAdapter dAdptr = new SqlDataAdapter(DB_SQL, dbConn))  
  91.       {  
  92.          try  
  93.          {  
  94.             dAdptr.Fill(dt);  
  95.             return dt;  
  96.          }  
  97.   
  98.          catch (System.Exception ex)  
  99.          {  
  100.             DB_Message = ex.Message;  
  101.             return null;  
  102.          }  
  103.       }  
  104.    }  
  105.   
  106.    public static int ExecSQL(string sql, params object[] list)  
  107.    {  
  108.         for (int i = 0; i < list.Length; i++)  
  109.             if (list[i] is string)  
  110.                 list[i] = list[i].ToString().EscQuote();  
  111.   
  112.       DB_SQL = String.Format(sql, list);  
  113.   
  114.       int rowsAffected = 0;  
  115.       using (SqlConnection dbConn = new SqlConnection(DB_CONNECTION))  
  116.       using (SqlCommand dbCmd = dbConn.CreateCommand())  
  117.       {  
  118.          try  
  119.          {  
  120.             dbConn.Open();  
  121.             dbCmd.CommandText = DB_SQL;  
  122.             rowsAffected = dbCmd.ExecuteNonQuery();  
  123.          }  
  124.   
  125.          catch (System.Exception ex)  
  126.          {  
  127.             DB_Message = ex.Message;  
  128.             rowsAffected = -1;  
  129.          }  
  130.       }  
  131.       return rowsAffected;  
  132.    }  
  133.   
  134.    public static string EscQuote(this string line)  
  135.    {  
  136.       return line?.Replace("'", "''");  
  137.    }  
  138.   
  139.   
  140. }  
 
How can I achieve this.
 
Please help me
 
Thank you 
 
Answers (8)
Next Recommended Forum