Insert List to table

问题:

I had this method to insert new company to database but I have problem with List<Contacts> because I want to be able to insert multiple contact for company. Could someone help me?

public static bool AddNewCompany(Company company,List<Contacts> contact , Location local)
{
    // get a configured DbCommand object
    DbCommand comm = GenericDataAccess.CreateCommand();

    // Set the stored procedure name 
    comm.CommandText = "AddNewCompany";

    //create new parameter @CompanyName
    DbParameter param = comm.CreateParameter();
    param.ParameterName = "@CompanyName";
    param.Value = company.CompanyName;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @CompanyDetail 
    param = comm.CreateParameter();
    param.ParameterName = "@CompanyDetail";
    param.Value = company.CompanyDetail;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @ModifiedDate
    param = comm.CreateParameter();
    param.ParameterName = "@ModifiedDate";
    param.Value = DateTime.Now;
    param.DbType = DbType.DateTime;
    comm.Parameters.Add(param);

    //Company Info
    foreach (var c in contact)
    {
        //create new parameter @LabelContactTypeID
        param = comm.CreateParameter();
        param.ParameterName = "@LabelContactTypeID";
        param.Value = c.LabelContactTypeID;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add(param);

        //create new parameter @ContactDetails
        param = comm.CreateParameter();
        param.ParameterName = "@ContactDetails";
        param.Value = c.ContactDetail;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add(param);

        //create new parameter @Status
        param = comm.CreateParameter();
        param.ParameterName = "@Status";
        param.Value = c.Status;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add(param);

        //create new parameter @Notes
        param = comm.CreateParameter();
        param.ParameterName = "@Notes";
        param.Value = c.Notes;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add(param);
    }




    //Company Info
    //create new parameter @Address
    param = comm.CreateParameter();
    param.ParameterName = "@Address";
    param.Value = local.Address;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @City
    param = comm.CreateParameter();
    param.ParameterName = "@City";
    param.Value = local.City;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @Province
    param = comm.CreateParameter();
    param.ParameterName = "@Province";
    param.Value = local.Province;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @PostalCode
    param = comm.CreateParameter();
    param.ParameterName = "@PostalCode";
    param.Value = local.PostalCode;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @Note
    param = comm.CreateParameter();
    param.ParameterName = "@Note";
    param.Value = local.Note;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @ModifiedDateLocation  
    param = comm.CreateParameter();
    param.ParameterName = "@ModifiedDateLocation";
    param.Value = DateTime.Now;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);


    try
    {
        return (GenericDataAccess.ExecuteNonQuery(comm) != -1);

    }
    catch
    {
        return false;
    }
}

SQL Server stored procedure:

ALTER PROCEDURE [dbo].[AddNewCompany]
    @CompanyName nvarchar(50),
    @CompanyDetail nvarchar(max),
    @ModifiedDate datetime,
    --ContactInfo
    @LabelContactTypeID  int,
    @ContactDetails nvarchar(MAX),
    @Status bit,
    @Notes nvarchar(MAX),
    -- Company Location 
    @Address nvarchar(max),
    @City nvarchar(50),
    @Province nvarchar(50),
    @PostalCode nvarchar(10),
    @Note nvarchar(max),
    @ModifiedDateLocation datetime
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [TaskManagementSystem_DB].[dbo].[Company] ([companyName],[companyDetail], [modifiedDate])
     VALUES (@CompanyName, @CompanyDetail, @ModifiedDate)

    DECLARE @CompanyID int 
    SET @CompanyID = SCOPE_IDENTITY();

    INSERT INTO [TaskManagementSystem_DB].[dbo].[Company_Contacts] ([companyID], [labelContactTypeID], [contactDetails], [status], [notes])
    VALUES (@CompanyID, @LabelContactTypeID, @ContactDetails, @Status, @Notes)

    INSERT INTO [TaskManagementSystem_DB].[dbo].[Location]([address], [city], [province], [postalCode], [note], [modifiedDate])
    VALUES (@Address, @City, @Province, @PostalCode, @Note, @ModifiedDateLocation)          

    DECLARE @LocationID  INT 
    SET @LocationID = SCOPE_IDENTITY(); 

    INSERT INTO [TaskManagementSystem_DB].[dbo].[Company_location]([companyID], [locationID])
    VALUES (@CompanyID, @LocationID)            
END

回答1:

Your stored procedure is inserting only one contact for the company. If you would like to add another contact (or more of them), write another procedure that will insert new contacts using the CompanyID from the last company inserted to the database.

I can't write you code because I don't know the tables and can't replicate the results, but you should create another procedure from this code

INSERT INTO [TaskManagementSystem_DB].[dbo].[Company_Contacts]
           ([companyID] 

           ,[labelContactTypeID]
           ,[contactDetails]
           ,[status]
           ,[notes])
     VALUES
           (@CompanyID 
           ,@LabelContactTypeID
           ,@ContactDetails
           ,@Status
           ,@Notes)

In the first part just add one contact from the list (the first one that is on index 0 in the list)

Read the CompanyID of company that has been just inserted and for each other contact in the list, using this ID call your new procedure. It will add new contacts that are referenced to the company using the same companyID (referential identity one to many).

EDIT You should do something like this:

public static bool AddNewCompany(Company company,List<Contacts> contact , Location local)
{
    // get a configured DbCommand object
    DbCommand comm = GenericDataAccess.CreateCommand();
//Set the store Proc name 
comm.CommandText = "AddNewCompany";


//create new parameter @CompanyName
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CompanyName";
param.Value = company.CompanyName;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add(param);

//create new parameter @CompanyDetail 
param = comm.CreateParameter();
param.ParameterName = "@CompanyDetail";
param.Value = company.CompanyDetail;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add(param);

//create new parameter @ModifiedDate
param = comm.CreateParameter();
param.ParameterName = "@ModifiedDate";
param.Value = DateTime.Now;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);

    //Company Info
//add only one contact
    /create new parameter @LabelContactTypeID
        param = comm.CreateParameter();
        param.ParameterName = "@LabelContactTypeID";
        param.Value = contact[0].LabelContactTypeID;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add(param);

    //create new parameter @ContactDetails
    param = comm.CreateParameter();
    param.ParameterName = "@ContactDetails";
    param.Value = contact[0].ContactDetail;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @Status
    param = comm.CreateParameter();
    param.ParameterName = "@Status";
    param.Value = contact[0].Status;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @Notes
    param = comm.CreateParameter();
    param.ParameterName = "@Notes";
    param.Value = contact[0].Notes;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);


//Company Info
//create new parameter @Address
param = comm.CreateParameter();
param.ParameterName = "@Address";
param.Value = local.Address;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add(param);

//create new parameter @City
param = comm.CreateParameter();
param.ParameterName = "@City";
param.Value = local.City;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add(param);

//create new parameter @Province
param = comm.CreateParameter();
param.ParameterName = "@Province";
param.Value = local.Province;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add(param);

//create new parameter @PostalCode
param = comm.CreateParameter();
param.ParameterName = "@PostalCode";
param.Value = local.PostalCode;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add(param);

//create new parameter @Note
param = comm.CreateParameter();
param.ParameterName = "@Note";
param.Value = local.Note;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add(param);

//create new parameter @ModifiedDateLocation  
param = comm.CreateParameter();
param.ParameterName = "@ModifiedDateLocation";
param.Value = DateTime.Now;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add(param);


try
{
    return (GenericDataAccess.ExecuteNonQuery(comm) != -1);

}
catch
{
    return false;
}

}

Create a new method that adds new contact using the part of the procedure I pasted here and insert all other contacts. Pas to this method a companyID.

回答2:

Move the company part above the contact.

Put this in the contact loop

GenericDataAccess.ExecuteNonQuery(comm);

It adds some logic to check for any failure.
It is up to you if you want to continue or immediately return.

public static bool AddNewCompany(Company company,List<Contacts> contact , Location local)
{
    // get a configured DbCommand object
    DbCommand comm = GenericDataAccess.CreateCommand();


    //Set the store Proc name 
    comm.CommandText = "AddNewCompany";



    //create new parameter @CompanyName
    DbParameter param = comm.CreateParameter();
    param.ParameterName = "@CompanyName";
    param.Value = company.CompanyName;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @CompanyDetail 
    param = comm.CreateParameter();
    param.ParameterName = "@CompanyDetail";
    param.Value = company.CompanyDetail;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @ModifiedDate
    param = comm.CreateParameter();
    param.ParameterName = "@ModifiedDate";
    param.Value = DateTime.Now;
    param.DbType = DbType.DateTime;
    comm.Parameters.Add(param);

    //Company Info
    //create new parameter @Address
    param = comm.CreateParameter();
    param.ParameterName = "@Address";
    param.Value = local.Address;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @City
    param = comm.CreateParameter();
    param.ParameterName = "@City";
    param.Value = local.City;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @Province
    param = comm.CreateParameter();
    param.ParameterName = "@Province";
    param.Value = local.Province;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @PostalCode
    param = comm.CreateParameter();
    param.ParameterName = "@PostalCode";
    param.Value = local.PostalCode;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @Note
    param = comm.CreateParameter();
    param.ParameterName = "@Note";
    param.Value = local.Note;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    //create new parameter @ModifiedDateLocation  
    param = comm.CreateParameter();
    param.ParameterName = "@ModifiedDateLocation";
    param.Value = DateTime.Now;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(param);

    // need to split up the SP to add the company here 
    // execute company SP here
    // and get back the companyID
    // can just clear out the parameters and reuse it
    comm.Parameters.Clear();

    //and you can just define the parameters once
    //create new parameter @LabelContactTypeID
    paramTypeID = comm.CreateParameter();
    paramTypeID.ParameterName = "@LabelContactTypeID";       
    paramTypeID.DbType = DbType.StringFixedLength;
    comm.Parameters.Add(paramTypeID);

    //Company Info
    foreach (var c in contact)
    {
        //create new parameter @LabelContactTypeID
        paramTypeID.Value = c.LabelContactTypeID;

        //create new parameter @ContactDetails
        param = comm.CreateParameter();
        param.ParameterName = "@ContactDetails";
        param.Value = c.ContactDetail;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add(param);

        //create new parameter @Status
        param = comm.CreateParameter();
        param.ParameterName = "@Status";
        param.Value = c.Status;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add(param);

        //create new parameter @Notes
        param = comm.CreateParameter();
        param.ParameterName = "@Notes";
        param.Value = c.Notes;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add(param);

        try
        {
             if (GenericDataAccess.ExecuteNonQuery(comm) == -1) return false;
             // this syntax is not what I am used to
             // i useally just comm.ExecuteNonQuer();
        }
        catch
        {
            return false;
        }
    }
    return true;    
}