none
Bulk Save Issue: Operand type clash: datetime2 is incompatible with int RRS feed

  • Question

  • Hi,

    I'm currently working on EntityFramework 4.0 based DataAccess Layer in my DotNet Application. I didn't find a way to do a bulk Save (Insert or Update of more than 2000 records). So I've created a Stored Procedure that accepts a table-valued parameter in SQL 08, and for that I'm passing a DataTable from my DataAccess Layer. The DataTypes in that table-valued parameter are Int,DateTime and Varchar. It is working fine when i execute it from the SQL side. But I'm getting an error like "Operand type clash: datetime2 is incompatible with int" if i pass any DateTime field and "Conversion failed when converting the nvarchar value to data type int" if i pass any Varchar field. I'm attaching my code and the SP below. Any help on this is appreciated. Thanks

    ___________________________________________________________________________________________________

    Dotnet Code:

            public void SaveFixedDateEntryListByDataSet(List<IFixedDateEntry> fixedDateEntryList)
            {
                try
                {
                    if (fixedDateEntryList != null && fixedDateEntryList.Count > 0)
                    {
                        DataTable fixedDateEntryDataTable = new DataTable("fixedDateEntryDataTable");
                        fixedDateEntryDataTable.Columns.Add("fd_key_id",typeof(Int32));
                        fixedDateEntryDataTable.Columns.Add("actn_dt", typeof(DateTime));
                        fixedDateEntryDataTable.Columns.Add("evnt_type_cd", typeof(Int32));
                        fixedDateEntryDataTable.Columns.Add("vrf_actn_cd", typeof(Int32));
                        fixedDateEntryDataTable.Columns.Add("val_dt", typeof(DateTime));
                        fixedDateEntryDataTable.Columns.Add("clndr_stat_cd", typeof(Int32));
                        fixedDateEntryDataTable.Columns.Add("clndr_actn_stat_cd", typeof(Int32));
                        fixedDateEntryDataTable.Columns.Add("upd_user_id", typeof(String));
                        fixedDateEntryDataTable.Columns.Add("upd_ts", typeof(DateTime));
                        fixedDateEntryDataTable.Columns.Add("vrf_actn_user_id", typeof(String));
                        fixedDateEntryDataTable.Columns.Add("vrf_actn_ts", typeof(DateTime));
                        fixedDateEntryDataTable.Columns.Add("evnt_key_id", typeof(Int32));
                        fixedDateEntryDataTable.Columns.Add("crt_user_id", typeof(String));
                        fixedDateEntryDataTable.Columns.Add("crt_ts", typeof(DateTime));

                        foreach (FixedDateEntry fixedDateEntry in fixedDateEntryList)
                        {
                            DataRow dataRow = fixedDateEntryDataTable.NewRow();

                            dataRow["fd_key_id"] = fixedDateEntry.KeyId;
                            dataRow["actn_dt"] = fixedDateEntry.ActionDate;
                            dataRow["evnt_type_cd"] = (int)fixedDateEntry.EventTypeCode;
                            dataRow["vrf_actn_cd"] = (int)fixedDateEntry.VerificationActionCode;
                            dataRow["val_dt"] = fixedDateEntry.ValueDate;
                            dataRow["clndr_stat_cd"] = (int)fixedDateEntry.CalendarStatusCode;
                            dataRow["clndr_actn_stat_cd"] = (int)fixedDateEntry.CalendarActionStatusCode;
                            dataRow["crt_user_id"] = fixedDateEntry.RecordLog.CreateUserID;
                            dataRow["crt_ts"] = fixedDateEntry.RecordLog.CreateTimestamp;
                            dataRow["upd_user_id"] = fixedDateEntry.RecordLog.UpdateUserID;
                            dataRow["upd_ts"] = fixedDateEntry.RecordLog.UpdateTimestamp;
                            dataRow["vrf_actn_user_id"] = fixedDateEntry.VerificationActionUserId;
                            dataRow["vrf_actn_ts"] = fixedDateEntry.VerificationActionTimeStamp;
                            dataRow["evnt_key_id"] = fixedDateEntry.EventKeyId;
                           
                            fixedDateEntryDataTable.Rows.Add(dataRow);
                        }

                        SqlConnection mySqlConnection = new SqlConnection(NewConnectionString);
                        SqlCommand mSqlDbCommand;
                        SqlParameter sqlParameter;
                        mSqlDbCommand = new SqlCommand("pr_save_fd_fixed_date_entries_by_dataset", mySqlConnection);
                        mSqlDbCommand.CommandType = CommandType.StoredProcedure;
        
                        mySqlConnection.Open();
        
                        sqlParameter = new SqlParameter("@fixedDateEntryCollection", SqlDbType.Structured);
                        sqlParameter.Value = fixedDateEntryDataTable;
                        mSqlDbCommand.Parameters.Add(sqlParameter);
                        mSqlDbCommand.ExecuteNonQuery();
                        mySqlConnection.Close();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
     
    Stored Procedure:

    CREATE PROCEDURE [dbo].[pr_save_fd_fixed_date_entries_by_dataset]
    (
     @fixedDateEntryCollection AS [dbo].[fixed_date_entry_Param] READONLY
    )
    AS
    BEGIN

    SET FMTONLY OFF

     MERGE t_fd_evnt_clndr AS FD_CL
     USING
     (
      SELECT * FROM @fixedDateEntryCollection
     )AS tempTable
     ON (
       (FD_CL.evnt_type_cd = tempTable.evnt_type_cd)
      AND
       (FD_CL.fd_key_id = tempTable.fd_key_id)
      AND
       (FD_CL.actn_dt = tempTable.actn_dt)
      )
     WHEN MATCHED THEN
      UPDATE SET
       FD_CL.fd_key_id = tempTable.fd_key_id,
       FD_CL.actn_dt = tempTable.actn_dt,
       FD_CL.evnt_type_cd = tempTable.evnt_type_cd,
       FD_CL.vrf_actn_cd = tempTable.vrf_actn_cd,
       FD_CL.val_dt = tempTable.val_dt,
       FD_CL.clndr_stat_cd = tempTable.clndr_stat_cd,
       FD_CL.clndr_actn_stat_cd = tempTable.clndr_actn_stat_cd,
       FD_CL.upd_user_id = tempTable.upd_user_id,
       FD_CL.upd_ts = tempTable.upd_ts,
       FD_CL.vrf_actn_user_id = tempTable.vrf_actn_user_id,
       FD_CL.vrf_actn_ts = tempTable.vrf_actn_ts,
       FD_CL.evnt_key_id = tempTable.evnt_key_id
       
     WHEN NOT MATCHED BY TARGET THEN
      INSERT
      (
       fd_key_id,
       actn_dt,
       evnt_type_cd,
       vrf_actn_cd,
       val_dt,
       clndr_stat_cd,
       clndr_actn_stat_cd,
       crt_user_id,
       crt_ts,
       upd_user_id,
       upd_ts,
       vrf_actn_user_id,
       vrf_actn_ts,
       evnt_key_id
      )
      VALUES
      (
       tempTable.fd_key_id,
       tempTable.actn_dt,
       tempTable.evnt_type_cd,
       tempTable.vrf_actn_cd,
       tempTable.val_dt,
       tempTable.clndr_stat_cd,
       tempTable.clndr_actn_stat_cd,
       tempTable.crt_user_id,
       tempTable.crt_ts,
       tempTable.upd_user_id,
       tempTable.upd_ts,
       tempTable.vrf_actn_user_id,
       tempTable.vrf_actn_ts,
       tempTable.evnt_key_id
      );
    END

    DataBase User Defined Table Type:

    CREATE TYPE [dbo].[fixed_date_entry_Param] AS TABLE(
     [fd_key_id] [int] NOT NULL,
     [actn_dt] [datetime] NOT NULL,
     [evnt_type_cd] [int] NOT NULL,
     [vrf_actn_cd] [int] NOT NULL,
     [val_dt] [datetime] NOT NULL,
     [clndr_stat_cd] [int] NOT NULL,
     [clndr_actn_stat_cd] [int] NOT NULL,
     [crt_user_id] [varchar](40) NOT NULL,
     [crt_ts] [datetime] NOT NULL,
     [upd_user_id] [varchar](40) NOT NULL,
     [upd_ts] [datetime] NOT NULL,
     [vrf_actn_user_id] [varchar](40) NULL,
     [vrf_actn_ts] [datetime] NULL,
     [evnt_key_id] [int] NULL
    )
    GO

    Executing the Stored Procedure:

    declare @p1 dbo.fixed_date_entry_Param

    insert into @p1 values(273,'11/1/2011 19:07',1902,101,'11/3/2011 0:00',302,402,'Value Updater','10/16/2011 19:07','Value Updater','10/16/2011 19:07','Value Updater','10/16/2011 19:07',43)

    exec pr_save_fd_fixed_date_entries_by_dataset @fixedDateEntryCollection=@p1

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Sunday, October 16, 2011 3:48 PM

All replies

  • On 10/16/2011 11:48 AM, MuthuKumar Rajendran wrote:
    > Hi,
    >
    > I'm currently working on EntityFramework 4.0 based DataAccess Layer in
    > my DotNet Application. I didn't find a way to do a bulk Save (*Insert or
    > Update of more than 2000 records*). So I've created a Stored Procedure
    > that accepts a table-valued parameter in SQL 08, and for that I'm
    > passing a DataTable from my DataAccess Layer. The DataTypes in that
    > table-valued parameter are Int,DateTime and Varchar. It is working fine
    > when i execute it from the SQL side. But I'm getting an error like
    > "*Operand type clash: datetime2 is incompatible with int*" if i pass any
    > DateTime field and "*Conversion failed when converting the nvarchar
    > value to data type int*" if i pass any Varchar field. I'm attaching my
    > code and the SP below. Any help on this is appreciated. Thanks
    >
     
    This is not an EF issue. You are using a sproc and classic ADO.NET with
    SQL Command objects, and you should address it from that angle and not
    an EF issue.
     
    You can also pass a List<T> of custom objects with properties to match
    what the datatable represents instead of using a datatable.
     
     
    You can also execute T-SQL or use a sproc within EF by using the
    backdoor and not need a second connection string one for EF and one for
    classic ADO.NET and SQL Command object.
     
     
     
    Sunday, October 16, 2011 8:19 PM
  • Thanks for your reply darnold.

       I know that, this is not an EF issue. I mentioned that I did not find a way to achieve this bulk save in EF. So I've moved back to traditional ADO.NET way. Once this is done, I can move this to EF by creating "Function import" for the same stored proc. and I'll execute it through EF.

       All i need now is to find a solution to the above issue... (Operand type clash). Any help on this is welcome.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, October 17, 2011 7:53 AM
  • On 10/17/2011 3:53 AM, MuthuKumar Rajendran wrote:
    > Thanks for your reply darnold.
    >
    > I know that, this is not an EF issue. I mentioned that I did not find a
    > way to achieve this bulk save in EF. So I've moved back to traditional
    > ADO.NET way. Once this is done, I can move this to EF by creating
    > "Function import" for the same stored proc. and I'll execute it through EF.
    >
    > All i need now is to find a solution to the above issue... (Operand type
    > clash). Any help on this is welcome.
    >
     
    If you take the error message and use Bing or Google, you'll find plenty
    of talk about your issue. Maybe you'll find a solution.
     
    Monday, October 17, 2011 11:08 AM