I'm ultimately trying to save a number of records I've stored in a concurrent list within my Function App into a SQL Server database with parameterized values, one of which is an encrypted column (not sure if relevant).

Currently, I'm iterating over each record and extracting the values, parameterizing them, and saving them to the database; doing this for each record. Though, from what I understand this is highly inefficient and I'm told creating a single string with the parameterized values of each record, then running that single string as a SQL Server query would be more efficient.

Could someone explain to me how I might achieve something like this or if I'm perhaps mistaken?

Thank you!

CodePudding user response：

[C#]

You can use Table-valued parameters to send multiple rows in a single SQL query. The flow would be

• Define a table type. The schema would be same as the parameters to be inserted.
• Create a DataTable with the exact same names and types of the table type.
• Pass the DataTable as parameter in the query.

Sample

CREATE TYPE MyTableType AS TABLE
    ( mytext TEXT,
      num INT );

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    DataTable table = new DataTable();
    // Add columns and rows. The following is a simple example.
    table.Columns.Add("mytext", typeof(string));
    table.Columns.Add("num", typeof(int));
    for (var i = 0; i < 10; i  )
    {
        table.Rows.Add(DateTime.Now.ToString(), DateTime.Now.Millisecond);
    }

    SqlCommand cmd = new SqlCommand(
        "INSERT INTO MyTable(mytext, num) SELECT mytext, num FROM @TestTvp",
        connection);

    cmd.Parameters.Add(
        new SqlParameter()
        {
            ParameterName = "@TestTvp",
            SqlDbType = SqlDbType.Structured,
            TypeName = "MyTableType",
            Value = table,
        });

    cmd.ExecuteNonQuery();
}

Reference : https://docs.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching#table-valued-parameters

[JAVA]

You can use PreparedStatement, create batch of the rows (ps.addBatch()) to be inserted and insert batch in one go (ps.executeBatch()).

Sample :

PreparedStatement ps= con.prepareStatement("INSERT INTO Sample VALUES (?, ?, ?, ?)");

for(int i; i<10; i  ){
  ps.setString(1, "String1");
  ps.setString(2, "String2");
  ps.setString(3, "String3");
  ps.setInt(4, 1000);

  ps.addBatch();
}

ps.executeBatch();

If there are a lot of records to be inserted, you can create multiple batches and insert them inside the loop itself.