I want to insert a record in DB and then need to return a row

by

One way to do what you want is to modify @voucherNo to be an OUTPUT parameter, and add a new OUTPUT parameter to your query to return the value of SCOPE_IDENTITY().

@voucherNo varchar(max)
@ScopeIdentity numeric(38,0)

And modify that last SELECT statement to set the value of @ScopeIdentity parameter.

SELECT @ScopeIdentity = SCOPE_IDENTITY()

Then use SqlCommand.ExecuteNonQuery to execute the query, and on completion, read the values from the output parameters.

using (SqlCommand command = new SqlCommand(connection, commandText))
{
    int rowsAffected = command.ExecuteNonQuery();
    if (rowsAffected > 0)
    {
        var voucherNo = (string)command.Parameters["@voucherNo].Value;
        var identity = (decimal)command.Parameters["@ScopeIdentity"].Value;
    }
}

More Related Contents:

  1. Taking lot of time to read from SQL Server using c#
  2. How do I clean SqlDependency from SQL Server memory?
  3. Adding multiple parameterized variables to a database in c#
  4. SQL WHERE clause matching values with trailing spaces
  5. Calling SQL Defined function in C#
  6. When is it better to write “ad hoc sql” vs stored procedures [duplicate]
  7. Passing datatable to a stored procedure
  8. Search SQL by date ASP.NET
  9. What are good ways to prevent SQL injection? [duplicate]
  10. NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
  11. What represents a double in sql server?
  12. Entity Framework 6 GUID as primary key: Cannot insert the value NULL into column ‘Id’, table ‘FileStore’; column does not allow nulls
  13. Distinct in Linq based on only one field of the table
  14. Entity Framework/SQL2008 – How to Automatically Update LastModified fields for Entities?
  15. populate treeview from list of file paths in wpf
  16. How to pass User Defined Table Type as Stored Procedured parameter in C#
  17. SQL Insert Query Using C#
  18. Map category parent id self referencing table structure to EF Core entity
  19. Entity Framework error: Cannot insert explicit value for identity column in table
  20. Executing query with parameters
  21. insert datetime value in sql database with c#
  22. Accessing a resource via codebehind in WPF
  23. Getting the Last Insert ID with SQLite.NET in C#
  24. Join and Include in Entity Framework
  25. How to pass string array in SQL parameter to IN clause in SQL
  26. Check if a SQL table exists
  27. Add third party dll reference in ssis script component
  28. Mapping columns in a DataTable to a SQL table with SqlBulkCopy
  29. Querying data using Entity Framework from dynamically created table
  30. SQL connection string for microsoft access 2010 .accdb

Leave a Comment