Insert List<contacts> to table RSS

• • Reply
  

  a_elnajjar82

  Member

  84 Points

  107 Posts

  Insert List<contacts> to table

  Nov 16, 2012 06:31 PM|LINK

  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
      //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 
      // you can clear the parameter and reuse the comm below
      // and get back the companyID
  
      //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);
  
          try
          {
               if (GenericDataAccess.ExecuteNonQuery(comm) == -1) return false;
  
          }
          catch
          {
              return false;
          }
      }
      return true;    
  }

  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

  
  
  
  

• • Reply
  

  _Manvel_

  Contributor

  4240 Points

  922 Posts

  Re: Insert List<contacts> to table

  Nov 16, 2012 06:54 PM|LINK

  You should make use of user defined table types. This MSDN article walkthroughs you through the process of creating/passing/using in sql user defined table type parameters.

  • Marked as answer by decker dong - msft on Nov 22, 2012 04:09 AM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Insert List<contacts> to table

  Nov 18, 2012 12:37 AM|LINK

  a_elnajjar82

  a_elnajjar82

  Hello,

  In fact I think you can just define for your Stored Procdure once, and then re-define parameters again and again in the foreach statement by removing duplicated definations of sqlparameters：

  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
      // you can clear the parameter and reuse the comm below
      // and get back the companyID
  
      //Company Info
      foreach (var c in contact)
      {
          comm.Parameters.RemoveAt(3); comm.Parameters.RemoveAt(4); comm.Parameters.RemoveAt(5); comm.Parameters.RemoveAt(6);
  
          //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);
  
          try
          {
               if (GenericDataAccess.ExecuteNonQuery(comm) == -1) return false;
  
          }
          catch
          {
              return false;
          }
      }
      return true;
  }

  • Edited by decker dong - msft on Nov 22, 2012 04:09 AM
  • Edited by decker dong - msft on Nov 22, 2012 04:09 AM
  • Marked as answer by a_elnajjar82 on Nov 23, 2012 02:04 PM