Return Value from SQL Server Insert Command Using C#

Return value from SQL Server Insert command using c#

SCOPE_IDENTITY returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

You can use SqlCommand.ExecuteScalar to execute the insert command and retrieve the new ID in one query.

using (var con = new SqlConnection(ConnectionString)) {
    int newID;
    var cmd = "INSERT INTO foo (column_name)VALUES (@Value);SELECT CAST(scope_identity() AS int)";
    using (var insertCommand = new SqlCommand(cmd, con)) {
        insertCommand.Parameters.AddWithValue("@Value", "bar");
        con.Open();
        newID = (int)insertCommand.ExecuteScalar();
    }
}

Execute Insert command and return inserted Id in SQL Server using C#

1. You are not casting, you are aliasing column name to int,

   Use: SELECT CAST(scope_identity() AS int)

2. rowCount is boxed integer. change return statement and return type. Example:

return (int)rowCount;

This would be your required ID.

Ref: SqlCommand.ExecuteScalar MSDN

Execute Insert command and return inserted Id in Sql

The following solution will work with sql server 2005 and above. You can use output to get the required field. inplace of id you can write your key that you want to return. do it like this

FOR SQL SERVER 2005 and above

    using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.ID VALUES(@na,@occ)",con))
    {
        cmd.Parameters.AddWithValue("@na", Mem_NA);
        cmd.Parameters.AddWithValue("@occ", Mem_Occ);
        con.Open();

        int modified =(int)cmd.ExecuteScalar();

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

        return modified;
    }
}

FOR previous versions

    using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ)  VALUES(@na,@occ);SELECT SCOPE_IDENTITY();",con))
    {
        cmd.Parameters.AddWithValue("@na", Mem_NA);
        cmd.Parameters.AddWithValue("@occ", Mem_Occ);
        con.Open();

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

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

how to sql function return value insert into a specific table column.in asp.net

It looks like you need an update statement to update each existing row to set the Age column for the date given in the birthdate column. It would be something like this;

UPDATE [Ehealth].[dbo].[PatientTable] SET [Age] = [dbo].fn_calculateAge([Birth date])

This will work if [Birth date] is the name of the column containing the values you would like to use as the argument for [dbo].fn_calculateAge.

Return a value after inserting record into sql db

You're SaveAddendum mthod doesn't return a value. sOut is local to the method. Change your method to return a value (a string), and then you can display it in the MessageBox. Also, you can remove the sOut, as that isn't needed (and won't do what you're thinking it will unless you declare it as an out parameter):

public static string SaveAddendum(string sType, string sNumber, string sLine1, string sLine2, string sLine3, string sLine4)
{

    // Your code

    return sOut;
}

Then when you call the method, you can do something like this:

string sOut = DataManager.SaveAddendum(txtType.Text, txtNumber.Text, txtLine1.Text, txtLine2.Text, txtLine3.Text, txtLine4.Text);

MessageBox.Show(sOut); 

SQL Insert Query Using C#

I assume you have a connection to your database and you can not do the insert parameters using c #.

You are not adding the parameters in your query. It should look like:

String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username,@password, @email)";

SqlCommand command = new SqlCommand(query, db.Connection);
command.Parameters.Add("@id","abc");
command.Parameters.Add("@username","abc");
command.Parameters.Add("@password","abc");
command.Parameters.Add("@email","abc");

command.ExecuteNonQuery();

Updated:

using(SqlConnection connection = new SqlConnection(_connectionString))
{
    String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username,@password, @email)";

    using(SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.AddWithValue("@id", "abc");
        command.Parameters.AddWithValue("@username", "abc");
        command.Parameters.AddWithValue("@password", "abc");
        command.Parameters.AddWithValue("@email", "abc");

        connection.Open();
        int result = command.ExecuteNonQuery();

        // Check Error
        if(result < 0)
            Console.WriteLine("Error inserting data into Database!");
    }
}

want to store return value of sql select statement in c#

using (SqlCommand command = new SqlCommand()) 
{
  command.Connection = connection;
  ...

Return id after insert C# using SQL Server

Try this:

public int CreateAlbum(string _titel, string _name, string _thumb, int _userid)
{
    // define return value - newly inserted ID
    int returnValue = -1;

    // define query to be executed
    string query = @"INSERT INTO tblFotoalbum (fldAlbumHead, fldAlbumName, fldAlbumThumb, fldUserID_FK)
                              VALUES (@titel, @name, @thumb, @userid);
                     SELECT SCOPE_IDENTITY();"

    // set up SqlCommand in a using block   
    using (objCMD = new SqlCommand(query, connection)) 
    {
        // add parameters using regular ".Add()" method 
        objCMD.Parameters.Add("@titel", SqlDbType.VarChar, 50).Value = _titel;
        objCMD.Parameters.Add("@name", SqlDbType.VarChar, 100).Value = _name;
        objCMD.Parameters.Add("@thumb", SqlDbType.VarChar, 100).Value = _thumb;
        objCMD.Parameters.Add("@userid", SqlDbType.VarChar, 25).Value = _userid;

        // open connection, execute query, close connection
        connection.Open();
        object returnObj = objCMD.ExecuteScalar();

        if(returnObj != null)
        {
           int.TryParse(returnObj.ToString(), out returnValue);
        }

        connection.Close();
    }

    // return newly inserted ID
    return returnValue;
}

Not sure how you can integrate that with your objData class - maybe you need to add a new method to that DAL class for this.

Check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...

How to write oracle insert query for return value in c#?

The error is a small gotcha in ODP.NET, everytime you receive a varchar2 returnValue parameter you must specify the size. So simply adding the line Size=32000, to your example will make it work.

See the full code bellow:

 con.Open();
 OracleCommand  cmd= CreateCommand();
 cmd.CommandText = $@"INSERT INTO {"Customer".GetDoubleQuoted()(
        {"City".GetDoubleQuoted()},
        {"CompanyName".GetDoubleQuoted()},
        {"ContactName".GetDoubleQuoted()},
        {"Country".GetDoubleQuoted()},
        {"CustomerID".GetDoubleQuoted()},
        {"Phone".GetDoubleQuoted()}
     ) VALUES ('Chicago', 'Amisys','Jwk', 'USA','aaa', '123456')
     Returning {"City".GetDoubleQuoted()} into :city";
 cmd.Connection = con;
 cmd.Parameters.Add(new OracleParameter
     {
         ParameterName = ":city",
         OracleDbType = OracleDbType.Varchar2,
         Size = 32000,
         Direction = ParameterDirection.ReturnValue
     });
 cmd.ExecuteNonQuery();
 var value= cmd.Parameters[":city"].Value.ToString();

Note: 32000 is the maximum size for a varchar2 column.

Dapper return inserted value using stored procedures

(Because your 'spAuthors_Insert' is SINGLE ROW based (and not set based), you can do the below).

Your stored procedure needs to perform a SELECT.
(as its very last instruction). A select using one of the three "identity" functions below.

ONE of the three below : (and probably in order of preference, see : What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()? )

SELECT SCOPE_IDENTITY()

or

SELECT IDENT_CURRENT('tablename')

or

SELECT @@IDENTITY

(where your table name is 'Authors')

..

Then your ExecuteAsync

probably needs to be

https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executescalarasync?view=net-6.0

ExecuteScalar(Async)

Because you need to get that single column/row value.

From the documentation:

An asynchronous version of ExecuteScalar(), which executes the command
and returns the first column of the first row in the first returned
result set. All other columns, rows and result sets are ignored.

Note the "first column of the first row". (and do not miss the "first returned result set" as well).

(Aka, make sure the ONLY "Select" in the stored procedure is one of the three IDENTITY functions above.

And to "dot the i" on the c# code.

Task SaveData<T>(

I would change to something like:

Task<long> SaveSingle<T>(

or maybe

Task<K> SaveSingle<T,K>(

(the above is the intention, I haven't tried out the generics definition on the method.

where K is "int" or "long" (INT,BIGINT in ms-sql-server)....and you return the new PrimaryKey.

And cast your result of .ExecuteScalarAsync as long or K.

---

Related Topics