Utilizzo di DateTime in SqlParameter per stored procedure, errore di formattazione

Sto cercando di chiamare una stored procedure (su un server SQL 2005) da C #, .NET 2.0 utilizzando DateTime come valore per un SqlParameter . Il tipo SQL nella stored procedure è ‘datetime‘.

L’esecuzione di sproc da SQL Management Studio funziona correttamente. Ma ogni volta che lo chiamo da C # ottengo un errore sul formato della data.

Quando eseguo SQL Profiler per guardare le chiamate, quindi copio incollare la chiamata exec per vedere cosa sta succedendo. Queste sono le mie osservazioni e note su ciò che ho tentato:

1) Se passo DateTime direttamente come DateTime o convertito in SqlDateTime , il campo è circondato da un PAIR di apici singoli, come

 @Date_Of_Birth=N''1/8/2009 8:06:17 PM'' 

2) Se passo il DateTime come stringa, ottengo solo le virgolette singole

3) L’uso di SqlDateTime.ToSqlString() non risulta in una stringa datetime formattata in UTC (anche dopo la conversione in ora universale)

4) L’utilizzo di DateTime.ToString() non determina una stringa datetime formattata in UTC.

5) L’impostazione manuale di DbType per SqlParameter su DateTime non modifica le osservazioni di cui sopra.

Quindi, le mie domande allora, è come diavolo posso ottenere C # per passare il tempo correttamente formattato in SqlParameter ? Sicuramente questo è un caso di uso comune, perché è così difficile lavorare? Non riesco a convertire DateTime in una stringa compatibile con SQL (ad esempio ‘2009-01-08T08: 22: 45’)

MODIFICARE

RE: BFree, il codice per eseguire effettivamente lo sproc è il seguente:

 using (SqlCommand sprocCommand = new SqlCommand(sprocName)) { sprocCommand.Connection = transaction.Connection; sprocCommand.Transaction = transaction; sprocCommand.CommandType = System.Data.CommandType.StoredProcedure; sprocCommand.Parameters.AddRange(parameters.ToArray()); sprocCommand.ExecuteNonQuery(); } 

Per approfondire ciò che ho provato:

 parameters.Add(new SqlParameter("@Date_Of_Birth", DOB)); parameters.Add(new SqlParameter("@Date_Of_Birth", DOB.ToUniversalTime())); parameters.Add(new SqlParameter("@Date_Of_Birth", DOB.ToUniversalTime().ToString())); SqlParameter param = new SqlParameter("@Date_Of_Birth", System.Data.SqlDbType.DateTime); param.Value = DOB.ToUniversalTime(); parameters.Add(param); SqlParameter param = new SqlParameter("@Date_Of_Birth", SqlDbType.DateTime); param.Value = new SqlDateTime(DOB.ToUniversalTime()); parameters.Add(param); parameters.Add(new SqlParameter("@Date_Of_Birth", new SqlDateTime(DOB.ToUniversalTime()).ToSqlString())); 

EDIT aggiuntivo

Quello che pensavo fosse più probabile che funzionasse:

 SqlParameter param = new SqlParameter("@Date_Of_Birth", System.Data.SqlDbType.DateTime); param.Value = DOB; 

Risultati in questo valore nella chiamata exec come si vede in SQL Profiler

 @Date_Of_Birth=''2009-01-08 15:08:21:813'' 

Se modifico questo per essere:

 @Date_Of_Birth='2009-01-08T15:08:21' 

Funziona, ma non analizzerà con una coppia di virgolette singole e non convertirà correttamente in DateTime con lo spazio tra la data e l’ora e con i millisecondi alla fine.

Aggiornamento e successo

Ho copiato / incollato il codice sopra dopo la richiesta dal basso. Ho rifilato le cose qua e là per essere conciso. Risulta che il mio problema era nel codice che ho lasciato fuori, che sono sicuro che qualcuno di voi avrebbe individuato in un istante. Avevo avvolto le mie chiamate sproc all’interno di una transazione. Si scopre che semplicemente non stavo facendo transaction.Commit() !!!!! Mi vergogno a dirlo, ma ce l’hai.

Ancora non so cosa sta succedendo con la syntax che torno dal profiler. Un collega ha guardato con la sua istanza del profiler dal suo computer e ha restituito una syntax corretta. Guardare le stesse esecuzioni di SAME dal mio profiler ha mostrato la syntax errata. Ha funzionato come un’arma rossa, facendomi credere che ci fosse un problema di syntax della query invece della risposta molto più semplice e vera, ovvero che dovevo eseguire la transazione!

Ho contrassegnato una risposta come corretta e ho dato alcuni voti positivi agli altri perché, dopo tutto, hanno risposto alla domanda, anche se non hanno risolto il mio specifico problema.

Come stai configurando lo SqlParameter ? È necessario impostare la proprietà SqlDbType su SqlDbType.DateTime e quindi passare direttamente DateTime al parametro (NON convertire in una stringa, quindi si richiede un sacco di problemi).

Dovresti essere in grado di ottenere il valore nel DB. In caso contrario, ecco un esempio molto semplice di come farlo:

 static void Main(string[] args) { // Create the connection. using (SqlConnection connection = new SqlConnection(@"Data Source=...")) { // Open the connection. connection.Open(); // Create the command. using (SqlCommand command = new SqlCommand("xsp_Test", connection)) { // Set the command type. command.CommandType = System.Data.CommandType.StoredProcedure; // Add the parameter. SqlParameter parameter = command.Parameters.Add("@dt", System.Data.SqlDbType.DateTime); // Set the value. parameter.Value = DateTime.Now; // Make the call. command.ExecuteNonQuery(); } } } 

Penso che parte del problema qui sia che si teme che il tempo in UTC non venga trasmesso a SQL Server. A tal fine, non si dovrebbe, perché SQL Server non sa che un particolare momento si trova in un particolare locale / fuso orario.

Se si desidera memorizzare il valore UTC, quindi convertirlo in UTC prima di passarlo a SQL Server (a meno che il server non abbia lo stesso fuso orario del codice client che genera il DateTime , e anche allora, questo è un rischio, IMO). SQL Server memorizzerà questo valore e al suo ritorno, se si desidera visualizzarlo in ora locale, è necessario farlo da soli (cosa che la struttura DateTime farà facilmente).

Tutto ciò detto, se si esegue la conversione e quindi si passa la data UTC convertita (la data ottenuta chiamando il metodo ToUniversalTime , non convertendo in una stringa) nella stored procedure.

E quando si ottiene il valore, chiamare il metodo ToLocalTime per ottenere l’ora nel fuso orario locale.

Ecco come aggiungo i parametri:

 sprocCommand.Parameters.Add(New SqlParameter("@Date_Of_Birth",Data.SqlDbType.DateTime)) sprocCommand.Parameters("@Date_Of_Birth").Value = DOB 

Presumo che quando scrivi DOB non ci siano virgolette.

Stai utilizzando un controllo di terze parti per ottenere la data? Ho avuto problemi con il modo in cui il valore del testo è generato da alcuni di essi.

Infine, funziona se si digita l’attributo .Value del parametro senza fare riferimento a DOB?

Basta usare:

  param.AddWithValue("@Date_Of_Birth",DOB); 

Questo si prenderà cura di tutti i tuoi problemi.

Se usi Microsoft.ApplicationBlocks.Data , renderà la chiamata ai tuoi sproc una singola riga

 SqlHelper.ExecuteNonQuery(ConnectionString, "SprocName", DOB) 

Oh e penso che casperOne sia corretto … se si vuole assicurare il datetime corretto su più fusi orari, basta convertire il valore in UTC prima di inviare il valore a SQL Server

 SqlHelper.ExecuteNonQuery(ConnectionString, "SprocName", DOB.ToUniversalTime())