Asked by:
Bulk Save Issue: Operand type clash: datetime2 is incompatible with int

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
BEGINSET 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
);
ENDDataBase 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
)
GOExecuting 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 withSQL Command objects, and you should address it from that angle and notan EF issue.You can also pass a List<T> of custom objects with properties to matchwhat the datatable represents instead of using a datatable.You can also execute T-SQL or use a sproc within EF by using thebackdoor and not need a second connection string one for EF and one forclassic 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 plentyof talk about your issue. Maybe you'll find a solution.Monday, October 17, 2011 11:08 AM