C#: SqlCommand für Abfrage mit datetime und convert funktioniert nicht

[Faby]

Hallo!

Es geht um folgende Zeile in meinem Projekt:

cmd.CommandText = "SELECT * FROM Calibration WHERE CONVERT(char(8), end_time, 112) BETWEEN '" + dt1.ToString("yyyyMMdd") + "' AND '" + dt2.ToString("yyyyMMdd") + "' ";

Zum Datenbankinhalt: end_time ist vom Typ datetime. Inhalte liegen in folgendem Format vor: "02.01.2011 23:59:59" und werden anhand von "CONVERT" und ".ToString" in das Format 20110102 gebracht. "cmd" ist vom Typ SqlCommand (using System.Data.SqlClient). dt1 sowie dt2 sind dateTimePicker Objekte die im Ereignis mit dem Wert gefüllt werden:

private void dateTimePicker1_ValueChanged(object sender, EventArgs e)

{

     dt1 = dateTimePicker1.Value;

}

Problem: Die Suche nach einem einzelnen Datum funktioniert einwandfrei. Ich verwende hierzu nicht BETWEEN sondern LIKE nur für dt1:

...WHERE CONVERT(char(8), end_time, 112) LIKE '%" + dt1.ToString("yyyyMMdd") +"%' ";

Wenn ich nun mit der ersten Anweisung über BETWEEN nach einem Datumsbereich suche, zeigt er mir keine Ergebnisse an, auch mit groß gewähltem Bereich über die Kalender.

Gibt es ein Problem mit der Konvertierung?

Danke!

Grüße,

Faby

Bearbeitet 26. Januar 2012 von Faby

[Gooose]

Ich würde eher mit Parametern arbeiten. SqlParameterCollection.AddWithValue bietet dir eine Übersicht. Hier mal ein Beispiel unter der Verwendung der Adventureworks-Datenbank:

SqlCommand cmd = new SqlCommand("select * from [AdventureWorks].[HumanResources].[Employee] WHERE HireDate BETWEEN @lowest AND @highest",conn);


cmd.Parameters.AddWithValue("lowest", new DateTime(2000, 1, 1));

cmd.Parameters.AddWithValue("highest", new DateTime(2002, 1, 1));

Die String-Verkettung fällt weg, und die Lösung ist weniger fehleranfällig.

[raiserle]

Hallo!

Zum Datenbankinhalt:

end_time ist vom Typ datetime.

Inhalte liegen in folgendem Format vor: "02.01.2011 23:59:59" und werden anhand von "CONVERT" und ".ToString" in das Format 20110102 gebracht.

Ich gehe in der Annahme: MySQL?

Aber datetime hat doch das Format 2011-01-02.

Alternativ wenn Type datetime - auch noch mit Uhrzeit 2011-01-02 00:00:00

Und @Gooose:

Das ist zwar schön und gut, trägt aber nucht zur Lösung des Problems bei

Bearbeitet 27. Januar 2012 von raiserle
einfach noch mehr dazu....

[Gum'Mib'Aer]

Ich gehe in der Annahme: MySQL?

Aber datetime hat doch das Format 2011-01-02.

Alternativ wenn Type datetime - auch noch mit Uhrzeit 2011-01-02 00:00:00[...]

SqlCommand ist für MSSQL, MySQL wäre MySqlCommand.

Daher trägt deine Antwort überhaupt nicht zur Lösung des Problems bei.

[...]

Und @Gooose:

Das ist zwar schön und gut, trägt aber nucht zur Lösung des Problems bei

Die Lösung von Goose hingegen ist richtig und entspricht der empfohlenen Herangehensweise.

[Gooose]

Aber datetime hat doch das Format 2011-01-02.

Das Format ist regionsabhängig. Bsp.:

CultureInfo ci = CultureInfo.GetCultureInfo("fr-FR");

Console.WriteLine(DateTime.Now.ToString(ci));

// Ausgabe: 27/01/2012 07:11:46

Je nach dem an welchem Rechner gearbeitet wird, kann der String anders aussehen. Deswegen sollte man nach Möglichkeit mit DateTime Objekten arbeiten.

Alternativ wenn Type datetime - auch noch mit Uhrzeit 2011-01-02 00:00:00

Da die Spalte in der DB auch vom Typ DateTime ist, sollte ein Mapping kein problem sein.

Und @Gooose: Das ist zwar schön und gut, trägt aber nucht zur Lösung des Problems bei

Die Vergleiche in der Query sind alphanumerisch. Dies kann dazu führen, das falsche oder keine Ergebnisse zurückgegeben werden. Wenn man keinen Einfluss mehr auf die Eingabeparameter DT1 und DT2 hätte, kann folgendes funktionieren.

SELECT * FROM [AdventureWorks].[HumanResources].[Employee]

WHERE HireDate BETWEEN CAST('2000-01-01' AS DATETIME) 

AND CAST('2002-01-01' AS DATETIME)

Hierbei müsste man sich aber wiederum auf die Ländereinstellungen des DBMS verlassen. Bei dieser Darstellung sind im angloamerikanischen Raum gerne mal Tag und Monat vertauscht. Das sind später Fehler, die schwer zu finden sind.

Die Verwendung von DateTime Objekten nimmt dir diese Arbeit ab.

Bei floating-points ist das Problem übrigens ähnlich, wenn man mit Strings arbeitet. Auch die String Repräsentation von Zahlen sind länderabhängig. (Dezimal- / Tausendertrenner)

[Gooose]

Hier mal ein Thread wo es um ein ähnliches Thema geht:

MSSQL / SqlCE DateTime Where Global halten

[Faby]

Danke für die Antworten! Zunächst die Frage: Was versteht man unter "Mapping"? Etwa das Suchen/Selektieren?

Ich habe mit Parametern noch nie gearbeitet und sollte dazu auch sagen, dass ich aus der C++ Welt komme und mich erst seit kurzem mit C# beschäftige.

Würde das so funktionieren? (Ich habe am Wochenende keinen Zugriff auf das Projekt)

cmd.CommandText = "SELECT * FROM Calibration WHERE CONVERT(char(8), end_time, 112) BETWEEN  @lowest AND @highest";

cmd.Parameters.AddWithValue("lowest", dt1.ToString("yyyyMMdd"));

cmd.Parameters.AddWithValue("highest", dt2.ToString("yyyyMMdd"));

Grüße,

Faby

[Gooose]

Was versteht man unter "Mapping"? Etwa das Suchen/Selektieren?

Hierbei ist das Datentyp-Mapping zwischen .NET und SQL-Server gemeint. Hier mal eine Übersicht: Mapping CLR Parameter Data

Ich habe mit Parametern noch nie gearbeitet ...

Schau dir mal oben den Link an. Da ist es gut erklärt. Gerade am Anfang ist die MSDN ein guter Freund. Innerhalb des Visual Studios kommst du meistens direkt auf die passenden MSDN Seiten, wo du weitere Erklärungen zu .Net Klassen, - Methoden und - Properties findest.

Ich habe am Wochenende keinen Zugriff auf das Projekt

Die MS SQL Server Express Version kannst du dir kostenlos runterladen. Die Beispiel Datenbank Adventure Works findest du unter Codeplex. So kannst du dich projektunabhängig mit dem Thema beschäftigen.

Probiere einfach mal ein wenig was aus. Dann bekommst du ein Gefühl für die Technik und merkst schneller was zielführend ist und was nicht.

Wenn dt1 und dt2 vom Typ System.DateTime sind, kann die Abfrage so aussehen:

cmd.CommandText = "SELECT * FROM Calibration WHERE end_time BETWEEN  @lowest AND @highest";

cmd.Parameters.AddWithValue("lowest", dt1);

cmd.Parameters.AddWithValue("highest", dt2);

Das Ziel des Ganzen ist es ja, auf die Datentyp Umwandlung nach Möglichkeit zu verzichten.

Nimm dir die Zeit, und versuche nachzuvollziehen, was dort passiert. Das hilft dir, wenn du zukünftig auf ein ähnliches Problem triffst.

[Faby]

Nimm dir die Zeit, und versuche nachzuvollziehen, was dort passiert. Das hilft dir, wenn du zukünftig auf ein ähnliches Problem triffst.

Danke! Ich besorge mir gerade die benötigten Dateien.

Mir stellen sich einige Fragen bezüglich SqlParameterCollection.AddWithValue:

command.Parameters.Add("@ID", SqlDbType.Int);

Ich füge command dem Parameter "@ID" hinzu der vom Typ SqlDbType.Int ist zu?

command.Parameters["@ID"].Value = customerID;

"@ID" wird eine Variable hinzugefügt, die vom Typ SqlDbType ist? D.h. customerID=5 ist äquivalent zu command.Parameters["@ID"].Value=5?

command.Parameters.AddWithValue("@demographics", demoXml);

Wie oben mit customerID nur mit einer einzeiligen Anweisung?

Geht man in diesem Beispiel davon aus, dass der Inhalt einer XML-Datei in "demoXml"

vorher eingelesen wurde?

Das Ziel des Ganzen ist es ja, auf die Datentyp Umwandlung nach Möglichkeit zu verzichten.

Stimmt. Wird für dt1 bspw. bei Auswahl des Datums 29.01.2012 eine Uhrzeit von 00:00:000 gespeichert, damit "lowest" auch wirklich die geringste anzunehmende Uhrzeit ist, um einen Vergleich mit BETWEEN zu ermöglichen?

Somit ist sowohl der DateTimePicker als auch die SQL-Anweisung kompatibel zueinander, da beide vom selben Typ sind? Was passiert, wenn der DateTimePicker von einem anderen System mit unterschiedlicher Spracheinstellung zu dem System übermittelt, das den Befehl an die Datenbank ausführt? Werden beide in eine Art Standardformat gebracht, um Kompatibilität zu erreichen?

Vielen Dank schon mal!

[Gooose]

command.Parameters.Add("@ID", SqlDbType.Int);

Ich füge command dem Parameter "@ID" hinzu der vom Typ SqlDbType.Int ist zu?

command.Parameters["@ID"].Value = customerID;

"@ID" wird eine Variable hinzugefügt, die vom Typ SqlDbType ist? D.h. customerID=5 ist äquivalent zu command.Parameters["@ID"].Value=5?

Hier wird beschrieben wie der Parameter an den Sql-Server übergeben wird. Einfacher währe es hier zu sagen:

command.Parameters.AddWithValue("@ID", customerID);

Das währe das gleiche Verhalten.

command.Parameters.AddWithValue("@demographics", demoXml);

Wie oben mit customerID nur mit einer einzeiligen Anweisung? Geht man in diesem Beispiel davon aus, dass der Inhalt einer XML-Datei in "demoXml" vorher eingelesen wurde?

Davon wird hier ausgegangen.

Wird für dt1 bspw. bei Auswahl des Datums 29.01.2012 eine Uhrzeit von 00:00:000 gespeichert, damit "lowest" auch wirklich die geringste anzunehmende Uhrzeit ist, um einen Vergleich mit BETWEEN zu ermöglichen?

In meinem Beispiel erzeuge ich ja ein DateTime Objekt ohne Zeitangabe. die Zeit wird hier mit Nullen belegt. Du kannst dir das beispielsweise im Command Window des Visual Studios anschauen:

>? new DateTime(2012,1,1).ToString()

"01.01.2012 00:00:00"

Weiteres kannst du unter DateTime in der MSDN nachlesen.

Was passiert, wenn der DateTimePicker von einem anderen System mit unterschiedlicher Spracheinstellung zu dem System übermittelt, das den Befehl an die Datenbank ausführt? Werden beide in eine Art Standardformat gebracht, um Kompatibilität zu erreichen?

Das müsste ich auch ausprobieren Ich denke aber, das mögliche auftretende Probleme durch das Verwenden von Parametern minimiert werden.

Somit ist sowohl der DateTimePicker als auch die SQL-Anweisung kompatibel zueinander, da beide vom selben Typ sind?

Kompatibel ist vielleicht ein wenig ungenau. Wir haben hier zwei Welten. Zum einen haben wir den SQL-Server, zum anderen die .NET Umgebung. Im Endeffekt hilft dir die SqlCommand Klasse SQL Statements an den Sql Server zu schicken. Aus dem Command

SqlCommand cmd = new SqlCommand("select * from [adventureworks].[humanresources].[employee] where hiredate between @lowest and @highest", conn);

cmd.Parameters.AddWithValue("lowest", new DateTime(2000, 1, 1));

cmd.Parameters.AddWithValue("highest", new DateTime(2002, 1, 1));

kommt folgendes am SQL-Server an:

exec sp_executesql N'select * from [adventureworks].[humanresources].[employee] where hiredate between @lowest and @highest',N'@lowest datetime,@highest datetime',@lowest='2000-01-01 00:00:00',@highest='2002-01-01 00:00:00'

Mit Hilfe von sp_executesql können SQL-Statements mit Parametern aufgerufen werden. Genau dabei hilft dir die SqlCommand Klasse.

Am Rande:

Was würdest du bei einem Insert / Update Statement machen? Wie würde wohl das SQL ohne die Verwendung von Parametern aussehen, wenn die Tabelle 10 oder mehr Spalten hat?

Mein Tipp:

Wenn ich mich in neue Themengebiete einarbeite, versuche ich die Komplexität so gering wie möglich zu halten.

In diesem Fall, baue dir erst mal eine einfache Konsolen-Anwendung, die nichts weiter macht als dieses Statement abzusetzen. Wenn Fehler auftreten, kann man diese leichter lokalisieren.

[Faby]

Danke Goose.

Das Insert / Update Statement kommt in naher Zukunft. Spätestens wenn ich mich für mein Abschlussprojekt damit beschäftigen muss Gerade beschäftige ich mich mit den zurückgegebenen Ergebnissen des SQL Statements. Ist es möglich ein SqlDataReader Objekt aus einer Methode zurückzugeben und ihn dann mit Read() weiterzuverwenden? Mein Problem: Da ich den Reader mit Close() schließen sollte, ihn danach aber mit return zurückgebe, kann ich kein Read() mehr darauf anwenden, was ja ziemlich verständlich ist. Wie jedoch gebe ich die Ergebnisse zurück, um sie danach weiterzuverwenden? Verwendet man da noch SqlDataReader?

[Gooose]

Nehmen wir doch mal das obere Beispiel mit der Adventureworks DB:

Mal angenommen eine Klasse für Employee würde so aussehen:

public class Employee

{

    public int EmployeeID { get; set; }

    public string LoginID { get; set; }

    public DateTime? BirthDate { get; set; }

}

Wenn man jetzt eine Liste von Employee aus der Datenbank haben wollte, könnte die Abfrage so ausehen:

SqlCommand cmd = new SqlCommand("select * from [adventureworks].[humanresources].[employee] where hiredate between @lowest and @highest", conn);

cmd.Parameters.AddWithValue("lowest", new DateTime(2000, 1, 1));

cmd.Parameters.AddWithValue("highest", new DateTime(2002, 1, 1));


SqlDataReader reader = cmd.ExecuteReader();

List<Employee> result = new List<Employee>();

while (reader.Read())

{

    Employee currentEmployee = new Employee();

    currentEmployee.LoginID = (string)reader["LoginID"];

    currentEmployee.EmployeeID = (int)reader["EmployeeID"];

    currentEmployee.BirthDate = (DateTime?)reader["BirthDate"];

    result.Add(currentEmployee);

}


reader.Close();

conn.Close();

Jetzt könnte man die Liste zurückgeben und damit weiterarbeiten. Dies ist auch ein Beispiel dafür, wie man die SQL-Datentypen wieder "zurückmappt".

Kurze Anmerkung zu dem "DateTime?":

Beim SQL-Server lassen sich Datumsspalten nullen, was unter .NET nicht geht. Schau dir hierzu mal Nullable Types (C# Programming Guide) an. Wenn du unter .NET mit Datenbanken arbeitest, solltest du dich mit den Datentypen genau auseinandersetzen. Das erspart dir später viel Arbeit

Schaue dir auch die beiden close Methoden an:

SqlConnection.Close Method

SqlDataReader.Close Method

Das Freigeben von Ressourcen ist gerade bei Datenbanken wichtig.

[Faby]

Erneut Danke. Jetzt wird das ganze etwas klarer. Die Art und Weise wie in C# Speicherplatz allokiert wird ist neu für mich. Eine List habe ich zuvor noch nie verwendet.

Grüße,

Faby

[Gooose]

Sicherlich wird auch speicher allokiert, doch ich meinte hier Resourcen im allgemeinen, sprich z.b. Anzahl von offenen Verbindungen zu einer Datenbank. Listen sind grundlegende Datenstrukturen. Vielleicht solltest du dich mit den Grundlagen auch nochmal auseinandersetzen.