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.
