Recover Milliseconds of a date recorded in an MSSQL Server 2019 BD with VB.Net + ADO.Net

Question

I would like to know how to recover a date as given from the recorded Milliseconds in a MS SQL Server 2019 database using VB.Net + ADO.Net.

Whenever I try to rescue the date, comes in the fomrmato "dd/MM/yyyy HH:mm:ss" I would also like to have the ". fff", but I can’t recover this information in the BD.

*** model code:

Private _DataAlteracao As DateTime

Public Property DataAlteracao() As DateTime
    Get
        Return _DataAlteracao
    End Get
    Set(ByVal value As DateTime)
        _DataAlteracao = value
    End Set
End Property

*** INSERT code:

Public Overloads Function InserirHistorico(entidade As ClsHistoricoDomain) As Object
    Dim objConfirma As Object

    MyBase.LimparStrComando()

    MyBase.StrComando.AppendLine(" INSERT INTO tblHistorico (")
    MyBase.StrComando.AppendLine(" CNPJ, ")
    MyBase.StrComando.AppendLine(" RazaoSocial, ")
    MyBase.StrComando.AppendLine(" NomeFantasia, ")
    MyBase.StrComando.AppendLine(" CodigoSistema, ")
    MyBase.StrComando.AppendLine(" DataAlteracao) ")
    MyBase.StrComando.AppendLine(" VALUES (")
    MyBase.StrComando.Append(IIf(Not String.IsNullOrEmpty(entidade.CNPJ), String.Format("'{0}',{1}", entidade.CNPJ, Environment.NewLine), "NULL,"))
    MyBase.StrComando.Append(IIf(Not String.IsNullOrEmpty(entidade.RazaoSocial), String.Format("'{0}',{1}", entidade.RazaoSocial, Environment.NewLine), "NULL,"))
    MyBase.StrComando.Append(IIf(Not String.IsNullOrEmpty(entidade.NomeFantasia), String.Format("'{0}',{1}", entidade.NomeFantasia, Environment.NewLine), "NULL,"))
    MyBase.StrComando.Append(IIf(Not String.IsNullOrEmpty(entidade.CodigoSistema), String.Format("'{0}',{1}", entidade.CodigoSistema, Environment.NewLine), "NULL,"))
    MyBase.StrComando.Append(IIf(Not String.IsNullOrEmpty(entidade.DataAlteracao), String.Format("'{0}',{1}", entidade.DataAlteracao, Environment.NewLine), "NULL,"))
    MyBase.StrComando.AppendLine(")")

    objConfirma = SQLHelper.ExecuteScalar(StrComando.Remove(StrComando.ToString.LastIndexOf(","), 1).ToString(), True)

    Return objConfirma
End Function

*** SELECT ALL CODE:

Public Overrides Function SelecionarTodos() As List(Of ClsHistoricoDomain)
    MyBase.LimparStrComando()

    MyBase.StrComando.AppendLine(" SELECT CNPJ, ")
    MyBase.StrComando.AppendLine(" RazaoSocial, ")
    MyBase.StrComando.AppendLine(" NomeFantasia, ")
    MyBase.StrComando.AppendLine(" CodigoSistema, ")
    MyBase.StrComando.AppendLine(" DataAlteracao ")
    MyBase.StrComando.AppendLine(" FROM tblHistorico ")

    Return MyBase.RetornaLista(StrComando.ToString())
End Function

Answer 1

The problem is that I mounted in tblHistorico a primary key composed of two fields: "System Code" + "Date Change".

(maybe help someone out) The best I could do was this:

*** Function call to test

Dim strTeste As String

strTeste = GerenciadorBll.Instance.HistoricoBLL.RetornaCodigoHistorico("1", "18-01-2021 15:14:41.897")
mskDataAlteracao.Text = strTeste

*** BLL

Public Function RetornaCodigoHistorico(Codigo As String, DataHistorico As String) As String
    Return GerenciadorDal.Instance.HistoricoDal.RetornaCodigoHistorico(Codigo, DataHistorico)
End Function

*** DAL

Public Function RetornaCodigoHistorico(ByVal codigoHistorico As String, ByVal dataHistorico As String) As String
    MyBase.LimparStrComando()

    MyBase.StrComando.AppendLine(" SELECT ")
    MyBase.StrComando.AppendLine(" DataAlteracao ")
    MyBase.StrComando.AppendLine(" FROM tblHistorico ")
    MyBase.StrComando.AppendLine(String.Format(" WHERE codigoSistema = '{0}' ", codigoHistorico))
    MyBase.StrComando.AppendFormat(" AND DataAlteracao = '{0}' ", dataHistorico)

    Return MyBase.RetornaDataHistorico(StrComando.ToString())
End Function

*** DAL BASE

Protected Function RetornaDataHistorico(ByVal pCommantText As String) As String
    Dim milliSeconds As Int32
    Dim dt As DateTime
    Dim strTempo As String

    Using dataReader As System.Data.SqlClient.SqlDataReader = SQLHelper.ExecuteReader(pCommantText)

        If dataReader.HasRows Then
            dataReader.Read()

            dt = dataReader.GetValue(dataReader.GetOrdinal("DataAlteracao"))
            milliSeconds = dt.Millisecond

            strTempo = dt.ToString() + "." + milliSeconds.ToString()
        Else
            Return Now()
        End If

        Return strTempo
    End Using

End Function

Now it’s a mistake when I want a SELECT ALL to fill a grid...

*** UI - User Interface

Private Sub PreencheGrid(Optional ByVal cod1 As String = Nothing, Optional ByVal cod2 As String = Nothing)

    DgvPadrao.DataSource = Nothing
    DgvPadrao.DataSource = GerenciadorBll.Instance.HistoricoBLL.SelecionarTodos

    If DgvPadrao.Rows.Count > 0 Then

...

*** BLL

Public Function SelecionarTodos() As List(Of ClsHistoricoDomain) Implements ICrud(Of ClsHistoricoDomain, String).SelecionarTodos
    Return GerenciadorDal.Instance.HistoricoDal.SelecionarTodos()
End Function

*** DAL

Public Overrides Function SelecionarTodos() As List(Of ClsHistoricoDomain)
    MyBase.LimparStrComando()

    MyBase.StrComando.AppendLine(" SELECT CNPJ, ")
    MyBase.StrComando.AppendLine(" RazaoSocial, ")
    MyBase.StrComando.AppendLine(" NomeFantasia, ")
    MyBase.StrComando.AppendLine(" CodigoSistema, ")
    MyBase.StrComando.AppendLine(" DataAlteracao ")
    MyBase.StrComando.AppendLine(" FROM tblHistorico ")

    Return MyBase.RetornaLista2(StrComando.ToString())
End Function

** DAL BASE

Protected Function RetornaLista2(ByVal commandText As String, Optional ByVal isSql As Boolean = True) As List(Of ClsHistoricoDomain)

    Dim listaRetorno As List(Of ClsHistoricoDomain) = Nothing

    Try

        If isSql Then
            Using dataReader As System.Data.SqlClient.SqlDataReader = SQLHelper.ExecuteReader(commandText)
                If dataReader.HasRows Then
                    listaRetorno = New List(Of ClsHistoricoDomain)()
                    While dataReader.Read()
                        Dim objeto As ClsHistoricoDomain
                        objeto = Activator.CreateInstance(GetType(ClsHistoricoDomain), dataReader)

                        listaRetorno.Add(objeto)
                    End While
                End If
            End Using
        End If

        Return listaRetorno

    Catch ex As Exception
        Return Nothing
    End Try

End Function

SELECT ALL NOW RETURNS ALL DATES WITHOUT MILLISECONDS!!!

As in table 'tblHistorico' my primary key is composed by 'Codigosistema' + 'Dataalteracao' then I iron myself, as it returns several records with the information of the Composite Primary Key INCOMPLETE...

... I guess I’ll have to change ALL the logic of making system history.... It CANNOT be a composite key with a date that depends on milliseconds... it will have to be an autonumber or something like that... ... that sucks, because of an error in the VB.Net code that does NOT return milliseconds in a search I will have to totally change my idea.