locked
How to create a dataset from stored procedure RRS feed

  • Question

  • User1839056048 posted
    Hi
    i want to make a dataset from stored procedure.
    following is my stored procedure
    create PROCEDURE [dbo].[Sp_addbusmaster](

    @bname NVARCHAR(50),
    @model NVARCHAR(50),
    @regno NVARCHAR(50),
    @seats NVARCHAR(50),
    @uname nvarchar(50))
    as

    begin

    insert into busmaster(bus_name,model,regno,seats,uname)values(@bname,@model,@regno,@seats,@uname)
    select MAX(bus_id) from Busmaster where uname=@uname
    end
    above stored procedure is working fine
    my requirement is after successful insertion in to busmaster .i need the max(bus_id) from busmaster using dataset
    how it is possible


    Regards
    Baiju
    Saturday, January 16, 2016 3:23 PM

Answers

  • User1124521738 posted

    actually, you're going to be better off using an output parameter for your sproc and also to use Scope_Identity() rather than max(id)

    http://forums.asp.net/t/1813565.aspx?Return+scope_identity+from+a+stored+procedure+in+SQL+itself+how+

    http://www.codeproject.com/Articles/748619/ADO-NET-How-to-call-a-stored-procedure-with-output

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 16, 2016 3:37 PM
  • User614698185 posted

    Hi Baiju,

    From your code, you use a output parameter called @bus_id, you could add parameter @bus_id and also setting its Direction as Output, like below:

    String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "Sp_addbusmaster";
    cmd.Parameters.Add("@bname", SqlDbType.VarChar).Value = txtbname.Text.Trim();
    ...
    cmd.Parameters.Add("@bus_id", SqlDbType.Int).Direction = ParameterDirection.Output; 
    cmd.Connection = con;
    try
    {
        con.Open();
        cmd.ExecuteNonQuery() ;
        string id = cmd.Parameters["@bus_id"].Value.ToString() ; 
        lblMessage.Text = "Record inserted successfully. ID = " + bus_id;
    } 

    Best Regards,

    Candice Zhou

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 18, 2016 6:58 AM

All replies

  • User1124521738 posted

    actually, you're going to be better off using an output parameter for your sproc and also to use Scope_Identity() rather than max(id)

    http://forums.asp.net/t/1813565.aspx?Return+scope_identity+from+a+stored+procedure+in+SQL+itself+how+

    http://www.codeproject.com/Articles/748619/ADO-NET-How-to-call-a-stored-procedure-with-output

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 16, 2016 3:37 PM
  • User614698185 posted

    Hi Baiju,

    From your code, you use a output parameter called @bus_id, you could add parameter @bus_id and also setting its Direction as Output, like below:

    String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "Sp_addbusmaster";
    cmd.Parameters.Add("@bname", SqlDbType.VarChar).Value = txtbname.Text.Trim();
    ...
    cmd.Parameters.Add("@bus_id", SqlDbType.Int).Direction = ParameterDirection.Output; 
    cmd.Connection = con;
    try
    {
        con.Open();
        cmd.ExecuteNonQuery() ;
        string id = cmd.Parameters["@bus_id"].Value.ToString() ; 
        lblMessage.Text = "Record inserted successfully. ID = " + bus_id;
    } 

    Best Regards,

    Candice Zhou

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 18, 2016 6:58 AM
  • User1633621018 posted

    Hi,

    If you need the last inserted id from your table you should use Scope_identity in place of a select query after insert.

    Change this line:
    select MAX(bus_id) from Busmaster where uname=@uname
    with
    SELECT SCOPE_IDENTITY()
    

    Also check if your code looks like below sample:

    // Using stored procedure to insert a new row and retrieve the identity value
       static void InsertPerson(String connectionString, String firstName, String lastName) {
          String commandText = "dbo.InsertPerson";
    
          using (SqlConnection conn = new SqlConnection(connectionString)) {
             using (SqlCommand cmd = new SqlCommand(commandText, conn)) {
                cmd.CommandType = CommandType.StoredProcedure;
    
                cmd.Parameters.Add(new SqlParameter("@FirstName", firstName));
                cmd.Parameters.Add(new SqlParameter("@LastName", lastName));
                SqlParameter personId = new SqlParameter("@PersonID", SqlDbType.Int);
                personId.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(personId);
    
                conn.Open();
                cmd.ExecuteNonQuery();
    
                Console.WriteLine("Person Id of new person:{0}", personId.Value);
             }
          }
       }
    
    CREATE PROCEDURE [dbo].[InsertPerson] 
    -- Add the parameters for the stored procedure here
    @FirstName nvarchar(50),@LastName nvarchar(50),
    @PersonID int output
    AS
    BEGIN
        insert [dbo].[Person](LastName,FirstName) Values(@LastName,@FirstName)
        set @PersonID=SCOPE_IDENTITY()
    END
    Go

    Saturday, January 23, 2016 1:23 PM