Inserting Data into two seperate tables using MVC and a stored procedure. : The Official Microsoft ASP.NET Forums

Reply

Soulchyld

Member

1 Points

Inserting Data into two seperate tables using MVC and a stored procedure.

Jan 28, 2013 08:07 PM|LINK

I need to inseert data into two tables with the same primary key, I can easily write a stored procedure for this on the SQL Server side, the challenge for me then becomes calling this stored procedure from my code in MVC as well as passing data to the stored proc from a view! the data will be doctors and patients in their respective tables but their details eg address phone will go into one table using foreign keys so as to conform to 3rd Normal Form. Anybody have any ideas on how I would start to implement this?

I hav just had a thought, If I add the data one table at a time without a stored proc and using the Scope_Identity function in sql instead? Would this work?

Reply

ignatandrei

All-Star

134511 Points

21576 Posts

Moderator

MVP

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Jan 28, 2013 08:21 PM|LINK

Soulchyld
then becomes calling this stored procedure from my code in MVC as well as passing data to the stored proc from a view!

Pass from ADO.NET . Search for ADO.NET stored proc and you wil get answer.

More, MVC is database agnostic - please see http://msprogrammer.serviciipeweb.ro/2010/03/29/asp-net-mvc-orm-and-viewmodels/

My .NET blog : http://msprogrammer.serviciipeweb.ro/ |2012 Programmer tools

Reply

Soulchyld

Member

1 Points

32 Posts

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Jan 28, 2013 08:54 PM|LINK

I dont quite understand your code in the link, also is it advisable to mix ef and ado.net?

Reply

ignatandrei

All-Star

134511 Points

21576 Posts

Moderator

MVP

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Jan 28, 2013 09:06 PM|LINK

Soulchyld
is it advisable to mix ef and ado.net?

Yes- if you want Storedprocs.

My .NET blog : http://msprogrammer.serviciipeweb.ro/ |2012 Programmer tools

Reply

Soulchyld

Member

1 Points

32 Posts

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Jan 29, 2013 05:20 PM|LINK

I am very new to MVC so am trying to get my head around it all, I have namaged to use ADO.Net to connect to my Db and am working on calling my stored proc as a method. The problem I seem to be having is in mapping the entities to the stored procedure parameters as you have to map the parameters to their respctive tables. My stored procedure however adds data to two tables and therefore cannot be mapped?

Is there another (maybe easier) way around this?

Basically what I am trying to achive is showing my doctor, patient or nurse details as per the erd included, be able to edit these details and insert new records! (this is where I can see the difficuty coming in)

Reply

ignatandrei

All-Star

134511 Points

21576 Posts

Moderator

MVP

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Jan 30, 2013 03:59 AM|LINK

Soulchyld
y stored procedure however adds data to two tables and therefore cannot be mapped?

If just add and not retrieve data, why you want mapping?

Soulchyld
d, be able to edit these details and insert new records! (this is where I can see the difficuty coming in)

I do not see any difficulty. Just pass parameters to the SP and that will be all.

My .NET blog : http://msprogrammer.serviciipeweb.ro/ |2012 Programmer tools

Reply

Soulchyld

Member

1 Points

32 Posts

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Jan 30, 2013 04:38 PM|LINK

The designer in web developer does not allow me to continue witout mapping for some reason. I tried to map my stored proc by right clicking the table (.edmx file) but then I could only map to that table leaving my stored proc with some unmapped parameters. and a failure to build!

Reply

ignatandrei

All-Star

134511 Points

21576 Posts

Moderator

MVP

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Jan 30, 2013 07:20 PM|LINK

Soulchyld

The designer in web developer does not allow me to continue witout mapping for some reason. I tried to map my stored proc by right clicking the table (.edmx file) but then I could only map to that table leaving my stored proc with some unmapped parameters. and a failure to build!

So the problem is the edmx. I suggested you to call using ADO.NET . Why not ?

My .NET blog : http://msprogrammer.serviciipeweb.ro/ |2012 Programmer tools

Reply

Soulchyld

Member

1 Points

32 Posts

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Jan 31, 2013 08:49 PM|LINK

Ok, heres the ADO.Net code I wrote, this code compiles ok but does not do anything when I populate the form and submit it! Here is the method I wrote in the controller

[HttpPost]
        public ActionResult Create(NewDoctor doc)
        {
            try
            {
                // Creates an ADO.NET type connection to the database so I can call the stored proc InsertDctor
                NewDoctor Doc = new NewDoctor();
                Doc = doc;
                SqlConnection con = new SqlConnection("Data Source=SOULCHYLD\\SQLEXPRESS ; initial catalog= HospitalSystem.Models.HospitalDB ");
                con.Open();
                SqlCommand command = new SqlCommand("InsertDoctor", con);
                command.CommandType = CommandType.StoredProcedure;
                //mapping the parameters to their respective columns
                command.Parameters.Add(new SqlParameter("@surname",SqlDbType.Text,10,"Surname"));
                command.Parameters.Add(new SqlParameter("@firstname", SqlDbType.Text, 10, "FirstName"));
                command.Parameters.Add(new SqlParameter("@address1", SqlDbType.Text, 10, "AddressLine1"));
                command.Parameters.Add(new SqlParameter("@address2", SqlDbType.Text, 10, "AddressLine2"));
                command.Parameters.Add(new SqlParameter("@town", SqlDbType.Text, 10, "Town"));
                command.Parameters.Add(new SqlParameter("@county", SqlDbType.Text, 10, "County"));
                command.Parameters.Add(new SqlParameter("@postcode", SqlDbType.Text, 10, "PostCode"));
                command.Parameters.Add(new SqlParameter("@phone", SqlDbType.Text, 10, "PhoneNo"));
                command.Parameters.Add(new SqlParameter("@email", SqlDbType.Text, 10, "Email"));
                command.Parameters.Add(new SqlParameter("@mobile", SqlDbType.Text, 10, "MobileNo"));
                command.Parameters.Add(new SqlParameter("@dob", SqlDbType.Date, 10, "DateOfBirth"));
                command.Parameters.Add(new SqlParameter("@gender", SqlDbType.Text, 10, "Gender"));
                command.Parameters.Add(new SqlParameter("@specialisation", SqlDbType.Text, 10, "Spcialisation"));
                command.Parameters.Add(new SqlParameter("@certCopy", SqlDbType.Text, 10, "CertificateCopy"));
                command.Parameters.Add(new SqlParameter("@DateOfGrad", SqlDbType.Date, 10, "GraduationDate"));
                command.Parameters.Add(new SqlParameter("@certBod", SqlDbType.Text, 10, "CertifyingBody"));

                command.Parameters[0].Value = Doc.Surname;
                command.Parameters[1].Value = Doc.FirstName;
                command.Parameters[2].Value = Doc.AddressLine1;
                command.Parameters[3].Value = Doc.AddressLine2;
                command.Parameters[4].Value = Doc.Town;
                command.Parameters[5].Value = Doc.County;
                command.Parameters[6].Value = Doc.PostCode;
                command.Parameters[7].Value = Doc.PhoneNo;
                command.Parameters[8].Value = Doc.Email;
                command.Parameters[9].Value = Doc.MobileNo;
                command.Parameters[10].Value = Doc.DateOfBirth;
                command.Parameters[11].Value = Doc.Gender;
                command.Parameters[12].Value = Doc.Specialisation;
                command.Parameters[13].Value = Doc.CertificateCopy;
                command.Parameters[14].Value = Doc.GraduationDate;
                command.Parameters[14].Value = Doc.CertifyingBody;

                int i = command.ExecuteNonQuery();



                return RedirectToAction("Index");
            }
            catch
            {
                return View(doc);
            }
        }

Here is my code for the view!

@model HospitalSystem.Inserts_ADO.NewDoctor

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>

@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)

    <fieldset>
        <legend>Enter</legend>


        <div class="editor-label">
            @Html.LabelFor(model => model.Surname)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Surname)
            @Html.ValidationMessageFor(model => model.Surname)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.FirstName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.FirstName)
            @Html.ValidationMessageFor(model => model.FirstName)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Gender)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Gender)
            @Html.ValidationMessageFor(model => model.Gender)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.DateOfBirth)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.DateOfBirth)
            @Html.ValidationMessageFor(model => model.DateOfBirth)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.AddressLine1)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.AddressLine1)
            @Html.ValidationMessageFor(model => model.AddressLine1)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.AddressLine2)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.AddressLine2)
            @Html.ValidationMessageFor(model => model.AddressLine2)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Town)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Town)
            @Html.ValidationMessageFor(model => model.Town)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.County)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.County)
            @Html.ValidationMessageFor(model => model.County)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.PostCode)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.PostCode)
            @Html.ValidationMessageFor(model => model.PostCode)
        </div>
        
        <div class="editor-label">
            @Html.LabelFor(model => model.Email)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Email)
            @Html.ValidationMessageFor(model => model.Email)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.PhoneNo)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.PhoneNo)
            @Html.ValidationMessageFor(model => model.PhoneNo)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.MobileNo)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.MobileNo)
            @Html.ValidationMessageFor(model => model.MobileNo)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Specialisation)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Specialisation)
            @Html.ValidationMessageFor(model => model.Specialisation)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.GraduationDate)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.GraduationDate)
            @Html.ValidationMessageFor(model => model.GraduationDate)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.CertifyingBody)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.CertifyingBody)
            @Html.ValidationMessageFor(model => model.CertifyingBody)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.CertificateCopy)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.CertificateCopy)
            @Html.ValidationMessageFor(model => model.CertificateCopy)
        </div>


        <p>
            <input type="submit" value="Create" />
        </p>
    </fieldset>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

Reply

Soulchyld

Member

1 Points

32 Posts

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Jan 31, 2013 08:51 PM|LINK

Here is the definition for my stored proc that works when I run it in SQL Server management Studio

CREATE PROCEDURE [dbo].[spInsertDoc]
	-- Add the parameters for the stored procedure here
	@surname nvarchar(50),
	@firstname nvarchar(50),
	@address1 nvarchar(50),
	@address2 nvarchar(50),
	@town nvarchar(50),
	@county nvarchar(50),
	@postcode nvarchar(12),
	@phone nvarchar(50),
	@email nvarchar(50),
	@mobile nvarchar(50),
	@dob nvarchar(50),
	@gender nvarchar,
	@spcialisation nvarchar(50),
	@certCopy nvarchar(50),
	@DateOfGrad date,
	@certBod nvarchar(50)
	
	
	
	
AS
DECLARE @tmpTable TABLE(ID INT)

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO Detail(Surname,FirstName,Address1,Address2,Town,County,PostCode,PhoneNo,Email,MobileNo,DateOfBirth,Gender)
	OUTPUT inserted.DetailID INTO @tmpTable
	VALUES(@surname,@firstname,@address1,@address2,@town,@county,@postcode,@phone,@email,@mobile,@dob,@gender)
	
	INSERT INTO Doctor(Specialisation,DateOfGraduation,CertifyingBody,CertificateCopy,DetailNo)
	VALUES(@spcialisation,@DateOfGrad,@certBod,@certCopy,(SELECT ID FROM @tmpTable))
END

Inserting Data into two seperate tables using MVC and a stored procedure. RSS

9 replies

Soulchyld

Inserting Data into two seperate tables using MVC and a stored procedure.

ignatandrei

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Soulchyld

Soulchyld

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

ignatandrei

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Soulchyld

Soulchyld

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

ignatandrei

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Soulchyld

Soulchyld

Soulchyld

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

ignatandrei

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Soulchyld

Soulchyld

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

Soulchyld

Re: Inserting Data into two seperate tables using MVC and a stored procedure.

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft