none
save entered data to database using stored procedure with parameters RRS feed

  • Question

  • Hi All.

    My WPF project window form has fields, for instance, FirstName, LastName, EmailAddress. The form binding to database with entity framework. How to save entered data using stored procedure with parameters @FirstName, @LastName, @EmailAddress?

    Thanks.

    Monday, March 23, 2020 5:54 PM

All replies

  • Hello,

    For code first see

    https://www.entityframeworktutorial.net/entityframework6/code-first-insert-update-delete-stored-procedure-mapping.aspx


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 23, 2020 7:21 PM
    Moderator
  • Why do you need to use a stored procedure? Why can you just let EF do its thing and persist  the data using EF entity on the EF model?
    Monday, March 23, 2020 8:16 PM
  • Hi Karen Payne.

    In my project I created a EF Model via wizard. And I had ability to select tables and views. But check box Import selected stored procedure is disabled. How to select stored procedures?

    Thanks.

    Monday, March 23, 2020 8:40 PM
  • I need stored procedure to save entered data. The stored procedure has parameters that are related to controls fields on my WPF window. It also include multiple joins.
    • Edited by zleug Monday, March 23, 2020 9:56 PM
    Monday, March 23, 2020 8:53 PM
  • I need stored procedure to save entered data. The stored procedure has parameters that are related to controls fields on my WPF window. It also include multiple joins.

    All control data should have corresponding database table columns in a table for data persistence, regardless of you using a sproc or the EF model object.  Multiple joins can be done by a Linq-2-Entities query using EF.

    What is the purpose of using EF if all you're doing is using EF to execute sprocs? You can do that without using EF and just use ADO.NET, SQL command objects and sprocs.

    Monday, March 23, 2020 10:48 PM
  • I new in EF. In project I'm using EF Code First. I need:

    1. Call sprocedure.
    2. Assign parameters of sproc to values from WPF widows form control.
    3. Save data to database using that sproc.

    My sproc look like:

    ALTER procedure [dbo].[sp_Name]
        @Code nvarchar(50),
        @FirstName nvarchar(150),
        @LastName nvarchar(150),
        @Address nvarchar(300),
        @Department nvarchar(50),

    @EmployeeName nvarchar(150),
    AS
    SELECT body of procedure

    I also created class
    public class ClassName {
    public string Code { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Address { get; set; }
    public string Department { get; set; }
    public string EmployeeName { get; set; }
    }

    I searched decision online and found that

    public class SalesPerson
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public decimal TotalSales { get; set; }
    }

    You call this procedure by passing SalePerson as the type parameter to the SqlQuery method:

    var salesPeople=await context.Database.SqlQuery<SalesPerson>("AllSalesPeople").ToListAsync();

    where AllSalesPeople is stored procedure

    How to solve the problem?

    Thanks.








    • Edited by zleug Tuesday, March 24, 2020 12:07 AM
    Monday, March 23, 2020 11:42 PM
  • Maybe, the link will help you. I have never used a sproc with EF.

    https://hub.packtpub.com/entity-framework-code-first-accessing-database-views-and-stored-procedures/

    There is an EF forum where you can get help too.

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    Tuesday, March 24, 2020 12:38 AM
  • Hi Karen Payne.

    In my project I created a EF Model via wizard. And I had ability to select tables and views. But check box Import selected stored procedure is disabled. How to select stored procedures?

    Thanks.

    The disabled option is solely for when creating classes via the first option in the wizard dialog which creates a .edmx file (which you should avoid). For code first as you want after the wizard has finished you need to write code discussed in the link in my first reply, nothing is automatic here.

    This means you need to spend time with the link I provided.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 24, 2020 1:25 AM
    Moderator
  • Can you show how to save record using stored procedure based on my description in previous post? If you will give me detail/brief explanation I will be appreciate. 

            private void SaveButton_Click(object sender, RoutedEventArgs e)
            {
                model.Code = CodeTextBox.Text;
                model.FirstName = FNameTextBox.Text;
                model.LastName = LNameTextBox.Text;
                model.Address = AddressTextBox.Text;
                model.Department = DeptComboBox.SelectedItem.ToString();
                model.EmployeeName = EmpComboBox.SelectedItem.ToString();
                db.tbName.Add(model);

                db.SaveChanges();

            }

    That is sample save data to one table. But how to save same data using existing stored procedure? 

    Thanks.



    • Edited by zleug Tuesday, March 24, 2020 3:26 AM
    Tuesday, March 24, 2020 3:11 AM
  • I'm not sure if you will be able to figure this out, but that's how we do it using EF (although we use DB first, but our models are all generated in code using Reverse POCO generator). See if you'll be able to adapt it for your case:

     SqlParameter adjustmentIdParameter = new SqlParameter("@AdjustmentId", SqlDbType.Int)
     {
         Value = adjustmentId
     };
     SqlParameter lineItemsParameter = new SqlParameter("@LineItems", SqlDbType.Structured)
     {
         TypeName = "SiriusType_AdjustmentLineItem"
     };
     if (lineItems.ToList().Count > 0)
     {
         AdjustLinesCollection collection = new AdjustLinesCollection();
         collection.AddRange(lineItems);
         lineItemsParameter.Value = collection;
     }
     _siriusContext.CoreContext.ExecuteStoreCommand(@"execute dbo.SiriusSP_SaveAdjustmentLineI
        @AdjustmentId = @AdjustmentId,
         @lineItems = @lineItems; ",
           adjustmentIdParameter, lineItemsParameter);
     if (commit)
     {
         SqlParameter salespointParameter = new SqlParameter("@salespoint", SqlDbType.Char, 6)
         {
             Value = salespoint
         };

    The most important line here is ExecuteStoreCommand - this is how you invoke the SP.

    Also, a side note, but I do hope your procedure name doesn't really start with sp_. You should never use sp_ for naming custom procedures as the sp_ is reserved for MS procedures, so you don't want to use the same prefix.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, March 24, 2020 3:57 AM
    Moderator
  • Hi,

    Has your issue been resolved?

    If so, please click "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 9, 2020 7:41 AM