List<int> Parameter an ADO.NET SQL Where in

EXDW

Hab halt ne klammer zu vergessen - sorry

Ich ging natürlich von .Net 4 aus, daher zeige ich einfach mal beide Varianten:

var connection = TheConnectionFromSomewhere();
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Kunden WHERE Id IN (@Integers)";

var integers = new List<int>{ 1, 2, 3 };

// Net 3.5 braucht ein string[] daher einfach alle einzeln ToString() und dann das IEnumerable<string> einfach zu nem array.
var parameterText = string.Join(", ", integers.Select(i => i.ToString()).ToArray());

// Net 4.0 schluckt alle object Collections und ruft selbstständig ToString auf
parameterText = string.Join(", ", integers);

command.Parameters.Add(new OleDbParameter("@Integers", parameterText));

(Da ich selber mal ein Projekt von .Net 4 auf 3.5 runter portieren musste habe ich ein eigenen string.Join als Extension Method in Benutzung um da das verhalten von .Net 4 nach zu stellen. [Genau wie das IsNullOrWhiteSpace, die Lazy klasse, Enum.HasFlag, Enum.TryParse und natürlich Covariance])

hustbaer

@David W:
Hast du das ausprobiert?

Ich hatte das mit MS-SQL Server und ADO.Net mal probiert, und es ging *nicht*.
Was auch nicht weiter verwunderlich ist.
@Integers ist schliesslich im SQL Statement kein String, also kann man auch keinen String-Parameter dran binden.

EXDW

Das ist genau das was ich im Produktivcode auch so habe Funktioniert Problemlos von auf MSSQL 2008

Am Start bereite ich Statements vor:

InsertProjectStatement = new Statement();
InsertProjectStatement.SetQuery(@"insert into dbo.Projects (ID, PName)
				                  values (@ProjectId, @ProjectName)");

Um sie später dann stets mit neuen werten auf zu rufen:

InsertProjectStatement.SetParameters(new Parameter("@ProjectId", id, SqlDbType.UniqueIdentifier),
                                     new Parameter("@ProjectName", pName, SqlDbType.NVarChar));
database.ExecuteReader(InsertProjectStatement);

"Database", "Statement" und "Parameter" sind eigene wrapper damits einfacher ist.

public class Database
{
    ...
    public void ExecuteReader(Statement statement)
    {
        try
        {		
            CloseReader();
            var command = statement.ToCommand();
            command.Connection = _connection;
            _data = command.ExecuteReader();
        }
        catch (Exception ex)
        {
            throw new Exception(Localizer.Format("Shared_Database_ExecutionFailed", "[ERRORMESSAGE]", ex.Message), ex);
        }
    }
    ...
}

public class Parameter
{
    public Parameter(string name, object value, SqlDbType type)
        : this(name, value, type, ParameterDirection.Input, 0)
    { }

    public Parameter(string name, object value, SqlDbType type, ParameterDirection direction)
        : this(name, value, type, direction, 0)
    { }

    public Parameter(string name, object value, SqlDbType type, ParameterDirection direction, int size)
    {
        Name = name;
        Value = value;
        Type = type;
        Direction = direction;
        Size = size;
    }

    public string Name { get; set; }
    public object Value { get; set; }
    public SqlDbType Type { get; set; }
    public int Size { get; set; }
    public ParameterDirection Direction { get; set; }
}

public class Statement
{
    private SqlCommand _command;
    private List<Parameter> _parameters;

    public Statement()
    {
        _command = new SqlCommand();
        _parameters = new List<Parameter>();
    }
    ...
    public void SetQuery(string query)
    {
        _command.CommandText = query;
        _command.CommandType = CommandType.Text;
    }

    public void SetParameters(params Parameter[] values)
    {
        _parameters.Clear();
        _parameters.AddRange(values);
    }

    public SqlCommand ToCommand()
    {
        _command.Parameters.Clear();
        foreach (var value in _parameters)
        {
            var parameter = new SqlParameter();
            parameter.ParameterName = value.Name;
            parameter.Value = value.Value;
            parameter.SqlDbType = value.Type;
            parameter.Direction = value.Direction;
            if (value.Size > 0)
                parameter.Size = value.Size;
            _command.Parameters.Add(parameter);
        }
        return _command;
    }
}

Das ist der unveränderte Produktivcode, nur etwas auf das wichtigste gekürzt.
http://msdn.microsoft.com/de-de/library/h8f14f0z.aspx
http://msdn.microsoft.com/de-de/library/system.data.sqlclient.sqlcommand.parameters.aspx

Merke gerade das ich Sql*** statt OleDb*** verwende, aber wenn ich Google finde ich auch funktionierende Lösungen mit OleDb.
Siehe:
http://authors.aspalliance.com/aspxtreme/sys/data/oledb/OleDbParameterClass.aspx
http://www.java2s.com/Tutorial/CSharp/0560__ADO.Net/OleDbParameterExample.htm
http://www.java2s.com/Tutorial/CSharp/0560__ADO.Net/OleDbParameterExample.htm
http://www.mycsharp.de/wbb2/thread.php?postid=323636

EXDW

Ah ich merk erst jetzt das du nicht die Übergabe des Parameters meinst sondern den Parameter Typen ^^ - brauch wo [noch ]einen Kaffee Hab nix gesagt und behaupte ganz frech das man da auch ein Integer Array geben kann statt es zu Joinen

//Dazu
SqlDbType.Binary könnte gehen, muss man nur nach ne byte Array basteln...

parameter.Value = myIntegers.Select(i => Convert.ToByte(i)).ToArray();
parameter.SqlDbType = SqlDbType.Binary

GPC

hustbaer schrieb:

@David W:
Hast du das ausprobiert?

Ich hatte das mit MS-SQL Server und ADO.Net mal probiert, und es ging *nicht*.
Was auch nicht weiter verwunderlich ist.
@Integers ist schliesslich im SQL Statement kein String, also kann man auch keinen String-Parameter dran binden.

Das kann ich alles nur bestätigen. Mir ist schleierhaft wie man hier mit DbParameter zum Ziel kommt. Einfacher ist es die Query mit String.Format zusammenzuhacken, wobei man da vorher auf SQL-Injection prüfen sollte.

hustbaer

Also wenn, dann könnte ich mir sowieso nur vorstellen, dass es mit einem SQL Statemelt ala "... IN (SELECT field FROM @tabVar)" funktioniert.

Als @tabVar müsste man dann einen DataTable oder was auch immer übergeben. Geht aber soweit ich weiss nicht.

EXDW

GPC schrieb:

... Einfacher ist es die Query mit String.Format zusammenzuhacken ...

Nur weil es einfacher ist wird es noch lange nicht richtiger.

@hustbaer
Man müsste ne DataTable mit SqlDbType.Structured passen können.

Zum Testen müsst ich erst eine Umgebung aufsetzen, bin ich grad zu faul.
Egal wie ein SQL Command bauen wir nie "irgendwie" zusammen, das ist einfach total unsauber.

(Wir selber lösen sowas lieber mit Stored Procedures. Am Ende wollen wir in der Applikation so wenig gebaute SQL statements wie möglich.)

GPC

David W schrieb:

GPC schrieb:

... Einfacher ist es die Query mit String.Format zusammenzuhacken ...

Nur weil es einfacher ist wird es noch lange nicht richtiger.

Mein Ansatz ist nicht nur einfacher, sondern auch richtiger als deiner, denn er funktioniert immerhin

EXDW

GPC schrieb:

Mein Ansatz ist nicht nur einfacher,

Genauso wie code Copy&Pasting, unsauber ist es trotzdem.

GPC schrieb:

sondern auch richtiger als deiner, denn er funktioniert immerhin

Sagte doch das ich es nicht getestet habe, und teilweise sogar hier im Forum getippt.

Solche sachen löst man richtig, aber warum sollte ich das für euch tun?
Dein zusammengebauter String mag funktionieren, ist aber trotzdem nicht richtig. Das ist ein Verbrechen an der Codequalität.

GPC

David W schrieb:

GPC schrieb:

Mein Ansatz ist nicht nur einfacher,

Genauso wie code Copy&Pasting, unsauber ist es trotzdem.

In einer perfekten Welt benutzt man ORM und alle sind happy. Leider gibt es divese Gründe, warum man es manchmal nicht kann.

GPC schrieb:

sondern auch richtiger als deiner, denn er funktioniert immerhin

Sagte doch das ich es nicht getestet habe, und teilweise sogar hier im Forum getippt.

Es geht nicht um Schreibfehler oder sowas.. es geht einfach darum, dass Code à la

var values = new int[] { 1, 2, 3, 4 };
string query = "SELECT col FROM table WHERE keyCol IN (@parameters);";
var cmd = new SqlCommand(query, connection);
cmd.Parameters.Add(new SqlParameter("parameters", String.Join(", ", value.Select(v => "'" + v + "'"))));
...

rein funktionell von den verwendeten Klassen nicht unterstützt wird. Deshalb muss man entweder auf Stored Procedures, ORM oder eben plain strings ausweichen.

EXDW

GPC schrieb:

...rein funktionell von den verwendeten Klassen nicht unterstützt wird...

Aha, und SqlDbType.Structured ist nur zur deko da?
Dieses eine Statement mag so nicht funktionieren (ich habe doch schon von ganz anderen sachen gesprochen, meine Postings gelesen?), wenn ihr das getestet habt glaub ich das doch gerne, aber dann sucht man eben die richtige Lösung.

Ein zusammenbau eines SQL Statements mittels string.Format oder + ist maximal gut um mal kurz was zu testen, aber das hat in der finalen Applikation am ende nichts zu suchen. Selbst wenn es auf ein Stored Procedure raus läuft (falls man keine saubere Lösung findet).

//Edit: Falsche tags verwendet ^^

hustbaer

Sieht so aus als müsste es mit SqlDbType.Structured wirklich funktionieren:

http://msdn.microsoft.com/en-us/library/bb675163.aspx

Davon abgesehen...

David W schrieb:

GPC schrieb:

Mein Ansatz ist nicht nur einfacher,

Genauso wie code Copy&Pasting, unsauber ist es trotzdem.

GPC schrieb:

sondern auch richtiger als deiner, denn er funktioniert immerhin

Sagte doch das ich es nicht getestet habe, und teilweise sogar hier im Forum getippt.

Naja. Etwas ad-hoc und ungetestet ins Forum zu tippen ist ja nicht grundsätzlich verkehrt. Es sollte aber zumindest die Chance haben zu funktionieren. Dein Vorschlag hatte diese Chance nie. Sowas hilft keinem. Und erst einen nicht funktionierenden Vorschlag zu liefern, und auf den Hinweis dass der Vorschlag eben nicht funktioniert ein "Solche sachen löst man richtig, aber warum sollte ich das für euch tun?" nachzuschieben, das grenzt an Verarsche.

David W schrieb:

Ein zusammenbau eines SQL Statements mittels string.Format oder + ist maximal gut um mal kurz was zu testen, aber das hat in der finalen Applikation am ende nichts zu suchen. Selbst wenn es auf ein Stored Procedure raus läuft (falls man keine saubere Lösung findet).

Das ist ein etwas naiver Ansatz, der in der Realität nicht immer funktioniert.

Zwei Beispiele:

#1: Das selbe wie hier (Liste von Werten an SQL-Server übergeben), nur mit SQL Server 2005. Das geht nämlich nicht "richtig", weil die Funktionen dafür einfach fehlen. Man kann es also nur "falsch" oder "falscher" machen. Wobei die "saubereren" Lösungen real dann oft "falscher" sind, da sie 10x oder 100x langsamer sind.

#2: Bulk-Upload (INSERT) von dynamisch generierten Daten aus einen C++ Programm über ADO. Sagen wir so 100K bis 1 Mio. Zeilen. Ebenfalls mit SQL Server 2005. Sag mir eine "richtige" Variante das zu machen, und ich sag dir eine Variante die 10x bis 100x schneller läuft.

BTW: Evangelist sein ist OK, aber nur wenn man sich wirklich wirklich gut mit etwas auskennt

EXDW

hustbaer schrieb:

blablubb ...das grenzt an Verarsche.

Es war doch nur ein schubs in eine Richtung. Ein Forum hat auch ein gewissen lernaspekt ^^

hustbaer schrieb:

David W schrieb:

Ein zusammenbau eines SQL Statements mittels string.Format oder + ist maximal gut um mal kurz was zu testen, aber das hat in der finalen Applikation am ende nichts zu suchen. Selbst wenn es auf ein Stored Procedure raus läuft (falls man keine saubere Lösung findet).

Das ist ein etwas naiver Ansatz, der in der Realität nicht immer funktioniert.

Och dann ist alles was ich bisher in der Richtung gemacht habe eine riesen Ausnahme :D. Ich nennen so etwas konsequentes beachten der Codequalität.

hustbaer schrieb:

Zwei Beispiele:

#1: Das selbe wie hier (Liste von Werten an SQL-Server übergeben), nur mit SQL Server 2005. Das geht nämlich nicht "richtig", weil die Funktionen dafür einfach fehlen. Man kann es also nur "falsch" oder "falscher" machen. Wobei die "saubereren" Lösungen real dann oft "falscher" sind, da sie 10x oder 100x langsamer sind.

Entweder so hier: http://itworksonmymachine.wordpress.com/2008/08/03/table-valued-parameter-in-sql-server-2005/ (gerade gefunden) oder man schaut nach Stored Procedures...

hustbaer schrieb:

#2: Bulk-Upload (INSERT) von dynamisch generierten Daten aus einen C++ Programm über ADO. Sagen wir so 100K bis 1 Mio. Zeilen. Ebenfalls mit SQL Server 2005. Sag mir eine "richtige" Variante das zu machen, und ich sag dir eine Variante die 10x bis 100x schneller läuft.

BTW: Evangelist sein ist OK, aber nur wenn man sich wirklich wirklich gut mit etwas auskennt

In unseren Tool übertragen wir, wenn es hoch kommt, auch ein paar Millionen an Datensätzen zu nem SQL Server 2005 hoch, und zwar zu einem Stored Procedure. Der Flaschenhals ist die Netzwerkverbindung Dauert meistens ~1 Sekunde.

//Dazu:
Ich glaub wir schweifen vom eigentlichen Thema ab, lasst uns hier mit diesem Thema Schluss machen (am ende bezichtigt ihr/du mir eh nur Unkenntnis und ich bin genervt ^^)

hustbaer

David W schrieb:

hustbaer schrieb:

Zwei Beispiele:

#1: Das selbe wie hier (Liste von Werten an SQL-Server übergeben), nur mit SQL Server 2005. Das geht nämlich nicht "richtig", weil die Funktionen dafür einfach fehlen. Man kann es also nur "falsch" oder "falscher" machen. Wobei die "saubereren" Lösungen real dann oft "falscher" sind, da sie 10x oder 100x langsamer sind.

Entweder so hier: http://itworksonmymachine.wordpress.com/2008/08/03/table-valued-parameter-in-sql-server-2005/ (gerade gefunden) oder man schaut nach Stored Procedures...

Wenn du das (XML) unter "richtig machen" verstehst, dann haben wir sehr verschiedene Auffassungen davon was gut und richtig ist.
Und was meinst du mit "oder man schaut nach Stored Procedures"? Sag mir wie, oder sag du weisst es nicht. "Na guckt man halt" ist uninteressant. Ich habe geguckt. Lange. Ausführlich. Wiederholt. Es geht nicht sauber.

hustbaer schrieb:

#2: Bulk-Upload (INSERT) von dynamisch generierten Daten aus einen C++ Programm über ADO. Sagen wir so 100K bis 1 Mio. Zeilen. Ebenfalls mit SQL Server 2005. Sag mir eine "richtige" Variante das zu machen, und ich sag dir eine Variante die 10x bis 100x schneller läuft.

BTW: Evangelist sein ist OK, aber nur wenn man sich wirklich wirklich gut mit etwas auskennt

In unseren Tool übertragen wir, wenn es hoch kommt, auch ein paar Millionen an Datensätzen zu nem SQL Server 2005 hoch, und zwar zu einem Stored Procedure. Der Flaschenhals ist die Netzwerkverbindung Dauert meistens ~1 Sekunde.

Mit C++ und ADO (*nicht* ADO.Net)?
Mit C# ist das nämlich kein Thema, da gibt's ja ne eigene Klasse nur dafür.
Mit diversen Commandline-Tools auch kein Thema.

Falls wirklich C++ und ADO, dann sag mir bitte wie, das würde mich wirklich interessieren. Ich habe ausgiebigst gesucht und nur eine einzige Lösung gefunden, die du aber sicher nicht als "richtig" durchgehen lassen wirst.

EXDW

hustbaer schrieb:

Wenn du das (XML) unter "richtig machen" verstehst, dann haben wir sehr verschiedene Auffassungen davon was gut und richtig ist.
Und was meinst du mit "oder man schaut nach Stored Procedures"? Sag mir wie, oder sag du weisst es nicht. "Na guckt man halt" ist uninteressant. Ich habe geguckt. Lange. Ausführlich. Wiederholt. Es geht nicht sauber.

Das mit dem XML hab ich nur auf die Schnelle gefunden da ich auf Arbeit war und nicht so viel Zeit investieren wollte.

> http://www.codeproject.com/KB/database/PassingArraysIntoSPs.aspx
Das sieht schon sehr sauber aus, und scheint auch auf SQL 2000 zu gehen.
Unten "Passing a DataTable to a SP for insertion into a table" sieht sehr interessant aus.
Es wird Microsoft Application Blocks erwähnt, da scheints ein paar Hilfsklassen zu geben, habe aber nicht durch gesehen.

Mit C++ und ADO (*nicht* ADO.Net)?
Mit C# ist das nämlich kein Thema, da gibt's ja ne eigene Klasse nur dafür.
Mit diversen Commandline-Tools auch kein Thema.

Falls wirklich C++ und ADO, dann sag mir bitte wie, das würde mich wirklich interessieren. Ich habe ausgiebigst gesucht und nur eine einzige Lösung gefunden, die du aber sicher nicht als "richtig" durchgehen lassen wirst.

Achso C++, hab ich scheinbar überlesen, bei C++ kann ich nicht mit reden, war auch nie das Thema. Warum kommst du mit deinem zweiten Beispiel plötzlich auf C++? Hier ist der C# Bereich, also red ich auch davon.

Ehrlich gesagt habe ich gar keine Lust mich näher damit zu beschäftigen. Ich leb lieber in meiner Welt mit den Applikationen wo man SQL Statements nicht formatiert zusammenbaut. Ja die gibt es, ist keine Traumwelt

/Edit: Die URL tags sind doof

hustbaer

David W schrieb:

hustbaer schrieb:

Wenn du das (XML) unter "richtig machen" verstehst, dann haben wir sehr verschiedene Auffassungen davon was gut und richtig ist.
Und was meinst du mit "oder man schaut nach Stored Procedures"? Sag mir wie, oder sag du weisst es nicht. "Na guckt man halt" ist uninteressant. Ich habe geguckt. Lange. Ausführlich. Wiederholt. Es geht nicht sauber.

Das mit dem XML hab ich nur auf die Schnelle gefunden da ich auf Arbeit war und nicht so viel Zeit investieren wollte.

> http://www.codeproject.com/KB/database/PassingArraysIntoSPs.aspx
Das sieht schon sehr sauber aus, und scheint auch auf SQL 2000 zu gehen.
Unten "Passing a DataTable to a SP for insertion into a table" sieht sehr interessant aus.

Jo, kenne ich.
Das ist jetzt natürlich total subjektiv. Aber für mich ist das weitaus unsauberer, als eine vernünftige dynamic-SQL Lösung.
Und vor allem massiv viel langsamer.
Dann noch lieber über XML Variablen, obwohl auch die massiv viel langsamer sind.

Ehrlich gesagt habe ich gar keine Lust mich näher damit zu beschäftigen. Ich leb lieber in meiner Welt mit den Applikationen wo man SQL Statements nicht formatiert zusammenbaut. Ja die gibt es, ist keine Traumwelt

Blub

List&lt;int&gt; Parameter an ADO.NET SQL Where in

List<int> Parameter an ADO.NET SQL Where in