How to get ID from Inserted record and the table

Yong Kevin

5y1.4kLearn iOS Programming

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, 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 GetList(string sql, params object[] list)
{
return GetTable(sql, list).ToDynamic();
}
public static List GetList(string sql, params object[] list)
{
return GetTable(sql, list).ToStatic();
}
private static List ToStatic(this DataTable dt)
{
var list = new List();
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 ToDynamic(this DataTable dt)
{
var dynamicDt = new List();
foreach (DataRow row in dt.Rows)
{
dynamic dyn = new ExpandoObject();
foreach (DataColumn column in dt.Columns)
{
var dic = (IDictionary)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

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
My God
how do you suppose to return int with IActionResult ??
change your return type to Int or return something else like view or emptyResult anything or use JsonResult.
public JsonResult GetLastInsertedPayment()
{

using (SqlConnection con=new SqlConnection(@"Your connectionString"))
    {
        using(SqlCommand cmd=new SqlCommand("INSERT INTO Payment VALUES(@Payment_Method,@Currency_Type,@Total_Amount)",con))
        {
          cmd.CommandType=CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Payment_Method", "Cash");
            cmd.Parameters.AddWithValue("@Currency_Type", currency);
            cmd.Parameters.AddWithValue("@Total_Amount", total);
            con.Open();

        int payment_id =Convert.ToInt32(cmd.ExecuteScalar());  // here you get

            if (con.State == System.Data.ConnectionState.Open)
                con.Close();
               return Json(new { Result = payment_id; });
        }
}
}
public int GetLastInsertedPayment()
{

using (SqlConnection con=new SqlConnection(@"Your connectionString"))
    {
        using(SqlCommand cmd=new SqlCommand("INSERT INTO Payment VALUES(@Payment_Method,@Currency_Type,@Total_Amount)",con))
        {
          cmd.CommandType=CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Payment_Method", "Cash");
            cmd.Parameters.AddWithValue("@Currency_Type", currency);
            cmd.Parameters.AddWithValue("@Total_Amount", total);
            con.Open();

        int payment_id =Convert.ToInt32(cmd.ExecuteScalar());  // here you get

            if (con.State == System.Data.ConnectionState.Open)
                con.Close();

return payment_id ;
        }
}
}
+2
Asma Khalid5y ago
Instead of using SCOPE_IDENTITY() use following i.e.
SELECT @@IDENTITY
Mark answer as accpted if helpful

Thanks & Regards,
Asma Khalid

For More Visit: www.asmak9.com or www.bytezaar.com
+1
Yong Kevin5y ago
Hi Sachin,

I try to put in my controller:

It shows an error where the IActionResult does not work and the rest of the IActionResult

+2
Sachin Singh5y ago
No it is simply like this
using (SqlConnection con=new SqlConnection(@"Your connectionString"))
    {
        using(SqlCommand cmd=new SqlCommand("INSERT INTO Payment VALUES(@Payment_Method,@Currency_Type,@Total_Amount)",con))
        {
          cmd.CommandType=CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Payment_Method", "Cash");
            cmd.Parameters.AddWithValue("@Currency_Type", currency);
            cmd.Parameters.AddWithValue("@Total_Amount", total);
            con.Open();

        int payment_id =Convert.ToInt32(cmd.ExecuteScalar());  // here you get

            if (con.State == System.Data.ConnectionState.Open)
                con.Close();


        }
}
+2
Yong Kevin5y ago
Hi Kiran,

Sorry, can you be more specific about your answer cause I don't know what it means

Thank you
+2
Yong Kevin5y ago
Hi Sachin, Is it like something like this
using (SqlConnection con=new SqlConnection(@"Your connectionString"))
    {
        using(SqlCommand cmd=new SqlCommand("INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount) output INSERTED.Payment_id VALUES(@Payment_Method,@Currency_Type,@Total_Amount)",con))
        {
            cmd.Parameters.AddWithValue("@Payment_Method", "Cash");
            cmd.Parameters.AddWithValue("@Currency_Type", currency);
            cmd.Parameters.AddWithValue("@Total_Amount", total);
            con.Open();

            int payment_id =Convert.ToInt32(cmd.ExecuteScalar());

            if (con.State == System.Data.ConnectionState.Open)
                con.Close();

            return payment_id ;
        }
}
I tried and is not working when I put in my controller
or I can include your code in my DButl?

Thank you
+2
Sachin Singh5y ago
two ways
1. Just Use ExecuteScalar() [Insert into table values (your values)]
2. Return Scope identity with ExecuteScalar() [insert into table table values(); select Scope_Identity()]

for both methods you need to create one more generic method as shown below.
public static int ExecSQLForScalar(string sql, params object[] list)
   {


      DB_SQL = String.Format(sql, list);

      int insertedRow = 0;
      using (SqlConnection dbConn = new SqlConnection(DB_CONNECTION))
      using (SqlCommand dbCmd = dbConn.CreateCommand())
      {
         try
         {
            dbConn.Open();
            dbCmd.CommandText = DB_SQL;
            insertedRow =Convert.ToInt32(dbCmd.ExecuteScalar());
         }

         catch (System.Exception ex)
         {
            DB_Message = ex.Message;
            rowsAffected = -1;
         }
      }
      return insertedRow;
   }
+2
Kiran Mohanty5y ago
You can use ExecuteScalar() method while inserting data into Payment table, which will return you payment_id. based on that you can change your if condition as if ( pay > 0) or something like that.
+2