Sql server procedure select as Alert for insert procedure

Question

I have a table and two fields. Their value is sent through ajax to webmethod. myCollection is array with values of a column in the table, lvl is a value of first field and ddl is a value of the second. These values needs to be inserted in the database with "InsertObject" procedure. For now I have a int success that send the value 1 to ajax and then I have alert that data is inserted successfully in the database if it is 0 than I have alert that it is not.

I have one loop in the InsertObject procedure that should check if specific combination of elements already exist in the database and it return select "Record already exist" if they exist.

I need help with this select. It should be somehow sent to webmethod and to ajax so I can alert the user that he try's new combination and elements can't be saved to the database.

Can someone please help with these ? Thanks in advance!

 [WebMethod(EnableSession = true)]
        public static int GetCollection(List<myCollection> omyCollection, int ddl, string lvl)
        {   //Datatable is filled with foreach loop

        DataTable tableMain = new DataTable();
        tableMain.Columns.Add("CharID");
        tableMain.Columns.Add("CharaValue");

        foreach (myCollection mycol in omyCollection)
        {
            string label = mycol.label;
            string opis = mycol.opis;

            tableMain.Rows.Add(label, opis);

        }

        int success = 0;
        string conn = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
        using (SqlConnection connection = new SqlConnection(conn))

            try
            {   
                //Check if values allready exist in the database 

                connection.Open();
                SqlCommand cmdCount = new SqlCommand("getDuplicate", connection);
                cmdCount.CommandType = CommandType.StoredProcedure;
                cmdCount.Parameters.AddWithValue("@ObjectName", lvl);


                var count = (string)cmdCount.ExecuteScalar();



                if (count == null)
                {
                    //Database insert
                    SqlCommand cmdProc = new SqlCommand("InsertObject", connection);
                    cmdProc.CommandType = CommandType.StoredProcedure;

                    //Parameters are sent to the database
                    cmdProc.Parameters.AddWithValue("@ObjectName", lvl);
                    cmdProc.Parameters.AddWithValue("@BCID", ddl);
                    cmdProc.Parameters.AddWithValue("@CharaValueParamether", tableMain);


                    cmdProc.ExecuteNonQuery();

                    //If the values are inserted send value 1 to ajax
                    success = 1;

                }

                else
                {

                }

            }

            catch
            {

            }
            finally
            {
                connection.Close();

            }

        return success;


    }

Answer 1

you just need to create a response Object

public enum InsertStatus{
success = 0,
failure =1 
}
public class InsertResponse
{
 public InsertStatus status {get;set;}
 public string Message {get;set;}
 }

and while returning your response if you found multiple entries just make a object with failure status and set the message that "Record already exist" and if you will able to insert record in database then set status to success and set message that "Record Inserted Successfully"

for eg

     public static InsertResponse GetCollection(List<myCollection> omyCollection, int ddl, string lvl)
            { 
     // code
var count = (string)cmdCount.ExecuteScalar();
   if ( count >1 )
return new InsertResponse(){status =failure,Message = "Record Already Exists"};
// code

// similarly after success
return new InsertResponse(){status =success,Message = "Record Inserted"};
    }

you noticed that I have changed the return type of function to Insert Response.