使用批量插入和预定义的语句

可以使用带有参数的预定义的语句批量加载数据。如果遇到任何错误，您还可以使用事务回退批量加载。

如果要加载大批量数据（超过 100 MB），请考虑使用直接批量插入。

以下示例详细介绍使用包含在数组中的数据以及参数和事务来批量加载数据。

可以通过以下命令创建在此示例中使用的 test 表：

=> CREATE TABLE test (id INT, username VARCHAR(24), email VARCHAR(64), password VARCHAR(8));

使用参数和事务的示例批量插入

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
            // Create arrays for column data
                    int[] ids = {1, 2, 3, 4};
            string[] usernames = {"user1", "user2", "user3", "user4"};
            string[] emails = { "user1@example.com", "user2@example.com","user3@example.com","user4@example.com" };
            string[] passwords = { "pass1", "pass2", "pass3", "pass4" };
            // create counters for accepted and rejected rows
                    int rows = 0;
            int rejRows = 0;
            bool error = false;
            // Create the transaction
                    VerticaTransaction txn = _conn.BeginTransaction();
            // Create the parameterized query and assign parameter types
                    VerticaCommand command = _conn.CreateCommand();
            command.CommandText = "insert into TEST values (@id, @username, @email, @password)";
            command.Parameters.Add(new VerticaParameter("id", VerticaType.BigInt));
            command.Parameters.Add(new VerticaParameter("username", VerticaType.VarChar));
            command.Parameters.Add(new VerticaParameter("email", VerticaType.VarChar));
            command.Parameters.Add(new VerticaParameter("password", VerticaType.VarChar));
            // Prepare the statement
                    command.Prepare();

                    // Loop through the column arrays and insert the data
                    for (int i = 0; i < ids.Length; i++)            {
                command.Parameters["id"].Value = ids[i];
                command.Parameters["username"].Value = usernames[i];
                command.Parameters["email"].Value = emails[i];
                command.Parameters["password"].Value = passwords[i];
                try
                {
                    rows += command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("\nInsert failed - \n  " + e.Message + "\n");
                    ++rejRows;
                    error = true;
                }
            }
            if (error)
            {
                // Roll back if errors
                        Console.WriteLine("Errors. Rolling Back Transaction.");
                Console.WriteLine(rejRows + " rows rejected.");
                txn.Rollback();
            }
            else
            {
                // Commit if no errors
                        Console.WriteLine("No Errors. Committing Transaction.");
                txn.Commit();
                Console.WriteLine("Inserted " + rows + " rows. ");
            }
            _conn.Close();
        }
    }
}