Da es keinen Sqlserver-Array-Parameter gibt, was ist der beste Weg, um fortzufahren?

Question

Ich muss mehrere Datensätze in Sqlserver erstellen, jeder mit dem gleichen Wert in Spalte A, aber mit einem eindeutigen Wert in Spalte B. Ich habe die Werte für Spalte B in einem Array.

Ich verwende VS2008, aspnet, c# 3.5, sqlserver 2005.

Bin ich besser dran

Option 1.

1 Aufruf einer gespeicherten Prozedur in Sqlserver aus c#-Code, und dann die gesamte Verarbeitung Arbeit in der gespeicherten Prozedur in tsql?

Dies würde beinhalten, kombinieren alle Werte in der c#-Array in eine durch Komma getrennte Zeichenfolge und Übergabe der Zeichenfolge an tsql als Parameter, dann Schleife und brechen die Zeichenfolge in einzelne Werte und einen Datensatz für jeden ein, alle innerhalb einer gespeicherten Prozedur einfügen.

So wie ich das sehe, würde dies bei Bedarf ein einfaches Rollback bedeuten, aber eine sehr umständliche String-Verarbeitung in tsql.

Oder

Option 2.

Doing die Schleife in c# und Übergabe der Daten als sqlparams von c# einen Datensatz zu einer Zeit, um eine gespeicherte proc einfügen jeden Datensatz.

D.h., foreach ( int key in myarray) einen Datensatz einfügen

Ich könnte diesen Code im Schlaf ausführen, aber wie könnte ich einen Rollback durchführen, wenn mitten in der Verarbeitung etwas passiert? Und sollte ich die Schleife in einer einzigen connection.open und connection.close ausführen?

Hat jemand eine andere Möglichkeit, dies zu tun?

Answer 1

Dieses Thema wird hier ausführlich behandelt: Arrays und Listen in SQL 2005

Answer 2

Der einfachste Weg, dies zu implementieren, ist die Verwendung von Option 1: Übergabe des Arrays als abgegrenzte Zeichenkette. Ich habe dies in den Tagen vor SQL2005 in Verbindung mit dieser Option getan TSQL Split-Funktion . Ich würde das Array mit "|" als Begrenzungszeichen übergeben.

Heutzutage serialisiere ich das Array in XML und füge dann den Inhalt in eine Tabellenvariable ein, um ihn mit der Funktion sp_xml_preparedocument gespeicherte Prozedur.

Ich würde die Option 2 nicht verwenden, da sie mehrere Aufrufe an die Datenbank macht.

Answer 3

Beide Optionen haben ihre Vorteile (Option 1 ist ein einziger Hin- und Rückweg, Option 2 verwendet keine umständliche String-Verarbeitung), aber ich würde mich wahrscheinlich für Option 2 entscheiden. Option 1 leidet unter den Größenbeschränkungen von varchars (8000, außer Sie verwenden varchar(MAX) Ich habe keine Ahnung, wie die Leistung bei einer kommagetrennten Datei aussehen würde. varchar(MAX) String, der furchtbar lang ist).

Was das Rollback angeht, ja, führen Sie einfach alle Operationen auf einer einzigen offenen Verbindung aus und verwenden Sie eine SqlTransaction Objekt.

Zum Beispiel...

using(SqlConnection conn = new SqlConnection("connection string"))
{
    conn.Open();

    using(SqlTransaction trans = conn.BeginTrasnaction())
    {
        try
        {
            using(SqlCommand cmd = new SqlCommand("command text", conn, trans))
            {
                SqlParameter aParam = new SqlParameter("a", SqlDbType.Int);
                SqlParameter bParam = new SqlParameter("b", SqlDbType.Int);

                cmd.Parameters.Add(aParam);
                cmd.Parameters.Add(bParam);

                aParam.Value = 1;

                foreach(int value in bValues)
                {
                    bValue = value;

                    cmd.ExecuteNonQuery();
                }
            }

            trans.Commit();
        }
        catch
        {
            trans.Rollback();

            throw; // so the exception can propogate up
        }
    }
}

Answer 4

Ich bin mir nicht sicher, ob dies auf Ihre Situation zutrifft, aber oft, wenn wir ein N-großes Array von Daten an eine gespeicherte Prozedur übergeben müssen, verwenden wir einen Trick mit einer temporären Tabelle. Etwas allein die Linien von:

using (SqlConnection connection = new SqlConnection(connectionstring)) {
   connection.Open();

   string sql = "CREATE TABLE #foo (myvalue [INT]) ";
   using (SqlCommand command = connection.CreateCommand()) {
      command.CommandText = sql;
      command.CommandType = CommandType.Text;

      command.ExecuteNonQuery(); // create the temp table

      foreach (int value in myValuesList) {
         command.CommandText = "INSERT INTO #foo ([myvalue]) VALUES (" + value + ") ";

         command.ExecuteNonQuery();
      }

      command.CommandType = CommandType.StoredProcedure;
      command.CommandText = "StoredProcThatUsesFoo";

      // fill in any other parameters

      command.ExecuteNonQuery();
   }
}

Answer 5

Wenn Sie mehrere Einfügevorgänge in einer Schleife in C# durchführen möchten, sollten Sie sich TransactionScope. Damit können Sie mehrere Aufrufe der gespeicherten Prozedur in eine Transaktion mit Rollback-Funktionen übertragen. Eine weitere Option wäre, dass Sie Ihr Array als XML übergeben könnten, und in der gespeicherten Prozedur könnten Sie diese XML in eine temporäre Tabelle schreddern, um sie in Ihrer Prozedur zu verwenden.

Als letztes sollten Sie Folgendes hinzufügen Tabelle Bewertete Parameter auf Ihrer Wunschliste der Gründe für ein Upgrade auf die nächste Version von SQL Server. Je länger die Wunschliste wird, desto leichter fällt es Ihnen, das Geld für ein Upgrade zu rechtfertigen.