插入表＆LT;联系人和GT;表 [英] Insert List<contacts> to table

问题描述

我有这个方法来插入新的公司数据库，但我有一个问题列表＆LT;联系和GT; ，因为我希望能够插入为公司的多个联系。可能有人帮助我吗？

 公共静态布尔AddNewCompany（公司公司名单，LT;联系和GT;联系人，位置本地）
{
    //获取配置DbCommand对象
    的DbCommand COMM = GenericDataAccess.CreateCommand（）;
    //设置商店PROC名称
    comm.CommandText =AddNewCompany;
    //创建新的参数@CompanyName
    的DbParameter参数= comm.CreateParameter（）;
    param.ParameterName =@CompanyName;
    param.Value = company.CompanyName;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add（参数）;    //创建新的参数@CompanyDetail
    参数= comm.CreateParameter（）;
    param.ParameterName =@CompanyDetail;
    param.Value = company.CompanyDetail;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add（参数）;    //创建新的参数@ModifiedDate
    参数= comm.CreateParameter（）;
    param.ParameterName =@ModifiedDate;
    param.Value = DateTime.Now;
    param.DbType = DbType.DateTime;
    comm.Parameters.Add（参数）;    //公司信息
    的foreach（接触变种C）
    {
        //创建新的参数@LabelContactTypeID
        参数= comm.CreateParameter（）;
        param.ParameterName =@LabelContactTypeID;
        param.Value = c.LabelContactTypeID;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add（参数）;        //创建新的参数@ContactDetails
        参数= comm.CreateParameter（）;
        param.ParameterName =@ContactDetails;
        param.Value = c.ContactDetail;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add（参数）;        //创建新的参数@Status
        参数= comm.CreateParameter（）;
        param.ParameterName =@Status;
        param.Value = c.Status;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add（参数）;        //创建新的参数@Notes
        参数= comm.CreateParameter（）;
        param.ParameterName =@Notes;
        param.Value = c.Notes;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add（参数）;
    }
    //公司信息
    //创建新的参数@address
    参数= comm.CreateParameter（）;
    param.ParameterName =@address;
    param.Value = local.Address;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add（参数）;    //创建新的参数@city
    参数= comm.CreateParameter（）;
    param.ParameterName =@city;
    param.Value = local.City;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add（参数）;    //创建新的参数@Province
    参数= comm.CreateParameter（）;
    param.ParameterName =@Province;
    param.Value = local.Province;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add（参数）;    //创建新的参数@邮政code
    参数= comm.CreateParameter（）;
    param.ParameterName =@邮政code;
    param.Value = local.Postal code;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add（参数）;    //创建新的参数@Note
    参数= comm.CreateParameter（）;
    param.ParameterName =@Note;
    param.Value = local.Note;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add（参数）;    //创建新的参数@ModifiedDateLocation
    参数= comm.CreateParameter（）;
    param.ParameterName =@ModifiedDateLocation;
    param.Value = DateTime.Now;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add（参数）;
    尝试
    {
        回报（GenericDataAccess.ExecuteNonQuery（通讯）！= -1）;    }
    抓住
    {
        返回false;
    }
}
 

的Sql店PROC

  ALTER PROCEDURE [DBO]。[AddNewCompany]
@CompanyName为nvarchar（50）
@ CompanyDetail为nvarchar（最大）
@ ModifiedDate日期时间
--ContactInfo
@ LabelContactTypeID INT
@ ContactDetails为nvarchar（MAX）
@状态位
@注意为nvarchar（MAX）
 - 公司所在地
@地址为nvarchar（最大）
@市为nvarchar（50）
@省为nvarchar（50）
@邮政code为nvarchar（10）
@注意为nvarchar（最大）
@ ModifiedDateLocation日期时间如
开始
     -  SET NOCOUNT ON加入到prevent额外的结果集，从
     - 与SELECT语句的干扰。
    SET NOCOUNT ON;     - 在此插入过程语句
    INSERT INTO [TaskManagementSystem_DB]。[DBO]。[公司]
           （[公司名]
           [companyDetail]
           [modifiedDate]）
     VALUES
           （@公司名
            @ CompanyDetail
            @ ModifiedDate）DECLARE @CompanyID INT
SET @CompanyID = SCOPE_IDENTITY（）;    INSERT INTO [TaskManagementSystem_DB]。[DBO]。[Company_Contacts]
           （[companyID]           [labelContactTypeID]
           ，[联系方式]
           ，[状态]
           ，[笔记]）
     VALUES
           （@CompanyID
           @ LabelContactTypeID
           ，@联系方式
           ，@状态
           ，@笔记） INSERT INTO [TaskManagementSystem_DB]。[DBO]。[定位]
           （[地址]
           ，[市]
           ，[省]
           [邮政code]
           ，[注意]
           [modifiedDate]）
     VALUES
           （@地址
           ，@市
           ，@省
           @邮政code
           ，@注意
           @ ModifiedDateLocation）DECLARE @LocationID INT
SET @LocationID = SCOPE_IDENTITY（）;
INSERT INTO [TaskManagementSystem_DB]。[DBO]。[Company_location]
           （[companyID]
           [locationID]）
     VALUES
           （@CompanyID
           @ LocationID）
结束
 

解决方案

您存储过程只插入一个用于本公司联系。如果你想添加其他联系人（或多种），写将插入使用CompanyID从上次公司插入到数据库中新的联系人另一个过程。

我不能给你写code，因为我不知道该表并不能复制的结果，但你应该从这个code创建另一个程序

  INSERT INTO [TaskManagementSystem_DB]。[DBO]。[Company_Contacts]
           （[companyID]           [labelContactTypeID]
           ，[联系方式]
           ，[状态]
           ，[笔记]）
     VALUES
           （@CompanyID
           @ LabelContactTypeID
           ，@联系方式
           ，@状态
           ，@笔记）
 

在第一部分中只需添加从列表中的一个接触（即索引0在列表中的第一个）

读取已刚插入和列表中的相互接触，使用这个ID调用你的新程序的公司的CompanyID。这将增加使用相同的companyID（指涉身份一对多）被引用到公司新的联系。

修改您应该做这样的事情：

 公共静态布尔AddNewCompany（公司公司名单，LT;联系和GT;联系人，位置本地）
{
    //获取配置DbCommand对象
    的DbCommand COMM = GenericDataAccess.CreateCommand（）;
//设置商店PROC名称
comm.CommandText =AddNewCompany;
//创建新的参数@CompanyName
的DbParameter参数= comm.CreateParameter（）;
param.ParameterName =@CompanyName;
param.Value = company.CompanyName;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add（参数）;//创建新的参数@CompanyDetail
参数= comm.CreateParameter（）;
param.ParameterName =@CompanyDetail;
param.Value = company.CompanyDetail;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add（参数）;//创建新的参数@ModifiedDate
参数= comm.CreateParameter（）;
param.ParameterName =@ModifiedDate;
param.Value = DateTime.Now;
param.DbType = DbType.DateTime;
comm.Parameters.Add（参数）;    //公司信息
//只添加一个联系人
    /新建参数@LabelContactTypeID
        参数= comm.CreateParameter（）;
        param.ParameterName =@LabelContactTypeID;
        param.Value =接触[0] .LabelContactTypeID;
        param.DbType = DbType.StringFixedLength;
        comm.Parameters.Add（参数）;    //创建新的参数@ContactDetails
    参数= comm.CreateParameter（）;
    param.ParameterName =@ContactDetails;
    param.Value =接触[0] .ContactDetail;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add（参数）;    //创建新的参数@Status
    参数= comm.CreateParameter（）;
    param.ParameterName =@Status;
    param.Value =接触[0] .STATUS;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add（参数）;    //创建新的参数@Notes
    参数= comm.CreateParameter（）;
    param.ParameterName =@Notes;
    param.Value =接触[0] .Notes;
    param.DbType = DbType.StringFixedLength;
    comm.Parameters.Add（参数）;
//公司信息
//创建新的参数@address
参数= comm.CreateParameter（）;
param.ParameterName =@address;
param.Value = local.Address;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add（参数）;//创建新的参数@city
参数= comm.CreateParameter（）;
param.ParameterName =@city;
param.Value = local.City;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add（参数）;//创建新的参数@Province
参数= comm.CreateParameter（）;
param.ParameterName =@Province;
param.Value = local.Province;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add（参数）;//创建新的参数@邮政code
参数= comm.CreateParameter（）;
param.ParameterName =@邮政code;
param.Value = local.Postal code;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add（参数）;//创建新的参数@Note
参数= comm.CreateParameter（）;
param.ParameterName =@Note;
param.Value = local.Note;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add（参数）;//创建新的参数@ModifiedDateLocation
参数= comm.CreateParameter（）;
param.ParameterName =@ModifiedDateLocation;
param.Value = DateTime.Now;
param.DbType = DbType.StringFixedLength;
comm.Parameters.Add（参数）;
尝试
{
    回报（GenericDataAccess.ExecuteNonQuery（通讯）！= -1）;}
抓住
{
    返回false;
}
 

}

创建，增加了使用我这里贴的程序的一部分，新接触一个新的方法和插入所有其他联系人。 PAS对此方法的companyID。

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 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
    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 store proc

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 added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    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

解决方案

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.

这篇关于插入表＆LT;联系人和GT;表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！