Solved

Store Procedure

Posted on 2011-03-07
5
238 Views
Last Modified: 2012-05-11
Hi ,

am using inline insert query in my code.

  cmd.CommandText = "INSERT Person (EmployeeNo,NamePrefix,FirstName, MiddleName, LastName, NameSuffix,Contractor,HighestEducationalLevel, BirthDate, Gender ) VALUES("
                                                   + item.EmployeeNumber + ","
                                                   + "'" + item.Title + "'" + ","
                                                   + "'" + item.PreferredName + "'" + ","
                    //+ "'" + item.MiddleName + "'" + ","
                                                   + "'" + item.MiddleName.Replace("'", "''") + "'" + ","
                                                    + "'" + item.Surname.Replace("'", "''") + "'" + ","
                                                    + "'" + item.Suffix + "'" + ","
                                                    + "'" + item.PayPoint + "'" + ","
                                                    + "'" + item.HighestEducationLevel + "'" + ","
                                                    + "'" + item.BirthDate + "'" + ","
                                                    + "'" + item.Sex + "'" + ")";

                cmd1.CommandText = "INSERT HOMEADDRESS (EmployeeNo,AddressLine1,AddressLine2,AddressLine3,City,State,PostCode ) VALUES("
                                                   + item.EmployeeNumber + ","
                                                    + "'" + item.ResidentialAddress1+ "'" + ","
                                                    + "'" + item.ResidentialAddress2 + "'" + ","
                                                    + "'" + item.ResidentialAddress3 + "'" + ","
                                                    + "'" + item.ResidentialAddress2 + "'" + ","
                                                    + "'" + item.ResidentialAddress3 + "'" + ","
                                                    + "'" + item.ResidentialPostCode + "'" + ")";

Open in new window


Any one give me an idea how can i combine both and write it in one Store procedure in that case i can clean up some mess in my code.

Thanks in Advance!
0
Comment
Question by:Sha1395
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

by:BurnieP
ID: 35062642
You can use semicolumn to separate the 2 calls.

...
+ "'" + item.Sex + "'" + ");"
+ "INSERT HOMEADDRESS (EmployeeNo,AddressLine1,AddressLine2,AddressLine3,City,State,PostCode ) VALUES("
+ ...

0
 

Author Comment

by:Sha1395
ID: 35062884
Thanks for your comment BurnieP.The reason i want to do in SP is

This my actual Insert command

               jobCmd.CommandText = "INSERT HOMEADDRESS (EmployeeNo,StateCode) VALUES("
                                                     + item.EmployeeNumber + ","
                                                      + "'" + item.CityLocation+ "'" + ")";


Am pulling value for City Location to State Code.

For Example

if the city location is Detroit then am passing the value "MI" to State code in DB.

How can i achieve,i believe its easy thru SP.
0
 
LVL 11

Accepted Solution

by:
lenordiste earned 500 total points
ID: 35063221
in short you could write this stored procedure:
CREATE PROCEDURE InsertPersonalInformation
    @EmployeeNo varchar(50), 
    @NamePrefix varchar(50) ,
    @StateCode varchar(50) 
AS 

    SET NOCOUNT ON;
  INSERT Person (@EmployeeNo,@NamePrefix)
  INSERT HOMEADDRESS (@EmployeeNo,@StateCode)
GO

Open in new window


you would then call your stored procedure like so:
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@EmployeeNo", item.EmployeeNumber));
cmd.Parameters.Add(new SqlParameter("@NamePrefix", item.NamePrefix));
cmd.Parameters.Add(new SqlParameter("@StateCode", item.StateCode));
cmd.ExecuteNonQuery();

Open in new window

0
 
LVL 11

Expert Comment

by:lenordiste
ID: 35063241
oh and don't forget to specify the stored procedures name in the constructor of SqlCommand:
SqlCommand cmd  = new SqlCommand(      "InsertPersonalInformation", conn);
0
 

Author Comment

by:Sha1395
ID: 35063362
Hi lenordiste,

Thanks for your SP can you help to replace the string inside SP.

For example

am getting city location ="Detroit"

then for State code ="MI" ( i have to insert this value to my State Code Column)

how can i write "Case"Statetment or if condition inside SP ?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now