C# DateTime To SQL Server DateTime

C# Function

        public bool WriteAlarmEvent(IOPCDAClientLib.AlarmRecord alarm)
        {
            bool result = false;
            using (_cmd = new SqlCommand("AlarmRecordAdd", _connect))
            {
                _cmd.CommandType = System.Data.CommandType.StoredProcedure;
                _cmd.Parameters.Add("@TagName", System.Data.SqlDbType.NVarChar, 200);
                _cmd.Parameters.Add("@AlarmState", System.Data.SqlDbType.NVarChar, 100);
                _cmd.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar);
                _cmd.Parameters.Add("@Value", System.Data.SqlDbType.NVarChar, 100);
                _cmd.Parameters.Add("@EventStampDate", System.Data.SqlDbType.Date);
                _cmd.Parameters.Add("@EventStampTime", System.Data.SqlDbType.Time);
                _cmd.Parameters.Add("@Provider", System.Data.SqlDbType.NVarChar, 100);
                _cmd.Parameters.Add("@Area", System.Data.SqlDbType.NVarChar, 100);
                _cmd.Parameters.Add("@Type", System.Data.SqlDbType.NVarChar, 100);
                _cmd.Parameters.Add("@Priority", System.Data.SqlDbType.Int,1);
                _cmd.Parameters.Add("@MilliSec", System.Data.SqlDbType.Int,1);
                _cmd.Parameters.Add("@Category", System.Data.SqlDbType.NVarChar, 100);
                _cmd.Parameters.Add("@CheckValue", System.Data.SqlDbType.NVarChar, 100);
                _cmd.Parameters.Add("@Operator", System.Data.SqlDbType.NVarChar, 100);
                _cmd.Parameters.Add("@DomainName", System.Data.SqlDbType.NVarChar, 100);
                _cmd.Parameters.Add("@UserFullName", System.Data.SqlDbType.NVarChar, 100);
                _cmd.Parameters.Add("@OperatorNode", System.Data.SqlDbType.NVarChar, 100);
                _cmd.Parameters.Add("@AlarmId", System.Data.SqlDbType.Int);

                _cmd.Parameters["@TagName"].Value = alarm.TagName ?? "";
                _cmd.Parameters["@AlarmState"].Value = alarm.AlarmState ?? "";
                _cmd.Parameters["@Description"].Value = alarm.Description ?? "";
                _cmd.Parameters["@Value"].Value = alarm.Value ?? "";
                _cmd.Parameters["@EventStampDate"].Value = DateTime.Now.ToString("yyyy/MM/dd");
                _cmd.Parameters["@EventStampTime"].Value = DateTime.Now.ToString("HH:mm:ss.fff");
                _cmd.Parameters["@Provider"].Value = alarm.Provider ?? "";
                _cmd.Parameters["@Area"].Value = alarm.Area ?? "";
                _cmd.Parameters["@Type"].Value = alarm.Type ?? "";
                _cmd.Parameters["@Priority"].Value = alarm.Priority;
                _cmd.Parameters["@MilliSec"].Value = 0;
                _cmd.Parameters["@Category"].Value = alarm.Category ?? "";
                _cmd.Parameters["@CheckValue"].Value = alarm.CheckValue;
                _cmd.Parameters["@Operator"].Value = alarm.Operator ?? "";
                _cmd.Parameters["@DomainName"].Value = alarm.DomainName ?? "";
                _cmd.Parameters["@UserFullName"].Value = alarm.UserFullName ?? "";
                _cmd.Parameters["@OperatorNode"].Value = alarm.OperatorNode ?? "";
                _cmd.Parameters["@AlarmId"].Value = alarm.AlarmId;
                _connect.Open();
                try
                {
                    if(ConnectStatus())
                    {
                        _cmd.ExecuteNonQuery();
                        _connect.Close();
                        result = true;
                    }
                    else
                    {
                        result = false;
                    }

                }
                catch(Exception ex)
                {
                    result = false;
                    throw ex;
                }
                finally
                {
                    _connect.Close();
                }
            }
            return result;
        }

SQL Stored Prodceduce

ALTER PROCEDURE [dbo].[AlarmRecordAdd]
    @TagName nvarchar(200), @AlarmState nvarchar(100), @Description nvarchar(MAX),
    @Value nvarchar(100), @EventStampDate datetime, @EventStampTime datetime,
    @Provider nvarchar(100), @Area nvarchar(100), @Type nvarchar(100), @Priority int,
    @MilliSec int, @Category nvarchar(100), @CheckValue nvarchar(100), @Operator nvarchar(100),
    @DomainName nvarchar(100), @UserFullName nvarchar(100), @OperatorNode nvarchar(100),
    @AlarmId int
AS
 
DECLARE @EventTimeStamp datetime
DECLARE @EventDate char(10)
DECLARE @EventTime char(12)
 
BEGIN
    IF @EventStampDate IS NOT NULL AND @EventStampTime IS NOT NULL
    BEGIN
        SET @EventDate = CONVERT(varchar(10), @EventStampDate, 111)
        SET @EventTime = CONVERT(varchar(12), @EventStampTime, 114)
        SET @EventTimeStamp = CONVERT(datetime, @EventDate + ' ' + @EventTime, 121)
        SET @MilliSec = DATEPART(MS,@EventTimeStamp)
    END
    ELSE
    BEGIN
        SET @EventTimeStamp = '1900-01-01 07:07:07'
    END
    
    IF NOT EXISTS(SELECT TOP 1 EventStamp FROM AlarmRecord WHERE EventStamp = @EventTimeStamp AND TagName = @TagName AND AlarmState = @AlarmState )
    BEGIN
        INSERT INTO AlarmRecord(TagName, AlarmState, [Description], [Value], EventStamp,EventStampUTC, Provider, Area,
        [Type], Priority, MilliSec, Category, CheckValue, Operator, DomainName, UserFullName, OperatorNode,
        AlarmId, WriteTimestamp)
        VALUES(@TagName, @AlarmState, @Description, @Value, @EventTimeStamp,@EventTimeStamp, @Provider, @Area, @Type, @Priority,
        @MilliSec, @Category, @CheckValue, @Operator, @DomainName, @UserFullName, @OperatorNode, @AlarmId, GETDATE())
    END
    
    SELECT 0
END

執行結果

若要加入毫秒，則 DateTime.Now.ToString("HH:mm:ss.fff"); 要加在ss後面