CODEWITHSUNDEEP
c#stored-proceduresentity-framework-6
Abdelrhman Elsaid
Abdelrhman Elsaid

Reputation: 494

C# Entity Framework and stored procedures

I made my database with its stored procedures then attached it with my project Entity Framework database-first.

This function to insert a company info and return its id back and insert it to another table in relation with company table

public string InsertCompany(company company, out int index)
{
        try
        {
            using (vendors_managerEntities db = new vendors_managerEntities())
            {
                db.companies.Add(company);
                db.SaveChanges();
                index = company.id_company;
                return $"{company.name_company} Is Saved";
            }
        }
        catch (Exception ex)
        {
            index = 0;
            return ex.Message;
        }
}

But when I tried to my stored procedure which has been created in database, I couldn't return any value the id always be 0 

public string InsertCompany(company company, out int index)
{
        try
        {
            using (vendors_managerEntities db = new vendors_managerEntities())
            {
                db.SP_insert_companies(company.name_company, company.website_company, company.adress_company, company.county_company, company.decription_company);
                index = company.id_company;
                return $"{company.name_company} Is Saved";
            }
        }
        catch (Exception ex)
        {
            index = 0;
            return ex.Message;
        }
}

I read that I can make it in SQL but I'm looking for a solution in C#, so I opened the stored procedure definition in C# and found the following code and was thinking if can I change its return value because it's not return the id value

public virtual int SP_insert_companies(string name, string website, string address, string country, string description)
{
        var nameParameter = name != null ?
            new ObjectParameter("name", name) :
            new ObjectParameter("name", typeof(string));

        var websiteParameter = website != null ?
            new ObjectParameter("website", website) :
            new ObjectParameter("website", typeof(string));

        var addressParameter = address != null ?
            new ObjectParameter("address", address) :
            new ObjectParameter("address", typeof(string));

        var countryParameter = country != null ?
            new ObjectParameter("country", country) :
            new ObjectParameter("country", typeof(string));

        var descriptionParameter = description != null ?
            new ObjectParameter("description", description) :
            new ObjectParameter("description", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("SP_insert_companies", nameParameter, websiteParameter, addressParameter, countryParameter, descriptionParameter);
}

Please tell me if there's a solution in C# or should I go back to old code without stored procedure in that case?

Upvotes: 1

Views: 320

Answers (2)

JuanR
JuanR

Reputation: 7783

The issue is that you are using a stored procedure to insert the company entity which I suspect does not cause the object to be refreshed by the context:

db.SP_insert_companies(company.name_company, company.website_company, company.adress_company, company.county_company, company.decription_company);

You then try to get the id from the object which is 0 because it hasn't been refreshed:

index = company.id_company;

If you insist on using a stored procedure, what I would suggest is that you have the SP return the id of the company, then grab it from the call and use that as the value of index:

index = db.SP_insert_companies(company.name_company, company.website_company, company.adress_company, company.county_company, company.decription_company);

Once you modify the SP, make sure to update the definition in your code so it knows to make a function that returns a value.

If you prefer to have it in the object itself, then make sure to update it manually, although I don't recommend this as the object is not in true sync with the database:

index = db.SP_insert_companies(company.name_company, company.website_company, company.adress_company, company.county_company, company.decription_company);
company.id_company = index;

Upvotes: 2

user10039288
user10039288

Reputation:

Based on what you're saying it's automatically going to the "Catch" and then the index is already set to 0. So chances are your code elsewhere, not listed is messing up. I suspect wherever your code for saving the company information isn't saving properly. Try manually inputting a company into the DB and then check it with your program. If it returns that it's "Saved" then you know your problem isn't within this method and is a result of your saving method.

Upvotes: 0

Related Questions