I have implemented CRUD operations using ASP.net. Every API method is working fine but the problem is , in the front end - if someone is putting same primary key its giving a particular Exception error which is obvious. You guys can see the code snippet:

[HttpPost]
        [Route("~/api/feestable/Registerfees")]
        public HttpResponseMessage Registerfees(feestable fee)
        {
            var response = Request.CreateResponse(HttpStatusCode.OK);
            DataTable table = new DataTable();
            string myconnection = ConfigurationManager.AppSettings["mycon"];
            try
            {
                using (SqlConnection con = new SqlConnection(myconnection))
                {
                    con.Open();
                    SqlCommand sqlCmd = new SqlCommand();
                    sqlCmd.CommandType = CommandType.Text;
                    sqlCmd.CommandText = @"insert into dbo.feestable (feeid,Regno,Tuitionfees,Transportfees,Stationaryfees,Securityfees,Admissionfees,Others,Total) values ('"   fee.feeid   @"','"   fee.Regno   @"','"   fee.Tuitionfees   @"','"   fee.Transportfees   @"','"   fee.Stationaryfees   @"','"   fee.Securityfees   @"','"   fee.Admissionfees   @"','"   fee.Others   @"','"   fee.Total   @"')";
                    sqlCmd.Connection = con;
                    SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
                    da.Fill(table);
                    
                }
                

                response = Request.CreateResponse(HttpStatusCode.OK);
                response.Content = new StringContent("Inserted Successfully", Encoding.UTF8, "application/json");
                return response;
            }
            catch (Exception ex)
            {
                response = Request.CreateResponse(HttpStatusCode.ExpectationFailed);
                response.Content = new StringContent(ex.Message, Encoding.UTF8, "application/json");
                return response;
            }

        }

I want, without giving that particular exception a simple error message like "ID Already Exist" and the same I want show in the front end. it should not give any error Response in the console. Can anybody help me out ?

CodePudding user response：

I suggest you look into ExecuteNonQuery - is returns number of rows updated/deleted/inserted by your query.

The idea would be to update the SQL command to execute only if there is no record with feeid provided.

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = @"
    IF NOT EXISTS (SELECT 1 FROM dbo.feestable WHERE feeid = "   fee.feeid   ") 
    BEGIN
        insert into dbo.feestable (feeid...
    END";
sqlCmd.Connection = con;
var res = sqlCmd.ExecuteNonQuery();
if (res > 0) {
// inserted successfully
}
else {
// record already exists
}

Also, please have a look into using SQL Parameters in your queries to avoid SQL injections. There are some examples here: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand