使用 Vertica 数据适配器

Vertica 数据适配器 (VerticaDataAdapter) 可使客户端能够在数据集和 Vertica 数据库之间交换数据。该适配器是 DbDataAdapter 的实施。例如，您可以使用 VerticaDataAdapter 仅读取数据；或者也可以从数据库将数据读取到数据集中，然后将数据集中已更改的数据写回数据库。

批量更新

使用 Update() 方法更新数据集时，您可以选择在调用 Update() 之前使用 UpdateBatchSize() 方法，以便减少客户端在执行更新期间与服务器通信的次数。UpdateBatchSize 的默认值为 1。如果对数据集使用多个 rows.Add() 命令，则您可以将批大小更改为最佳大小以加快客户端完成更新所需执行的操作。

使用数据适配器从 Vertica 读取数据：

以下示例详细介绍如何对 VMart 架构执行 select 查询并将结果加载到 DataTable，然后将 DataTable 的内容输出到控制台。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
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();

            // Try/Catch any exceptions
                   try
            {
                using (_conn)
                {
                    // Create the command
                    VerticaCommand command = _conn.CreateCommand();
                    command.CommandText = "select product_key, product_description " +
                        "from product_dimension where product_key < 10";

                        // Associate the command with the connection
                        command.Connection = _conn;

                        // Create the DataAdapter
                        VerticaDataAdapter adapter = new VerticaDataAdapter();
                        adapter.SelectCommand = command;

                        // Fill the DataTable
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        //  Display each row and column value.
                        int i = 1;
                        foreach (DataRow row in table.Rows)
                        {
                            foreach (DataColumn column in table.Columns)
                            {
                                Console.Write(row[column] + "\t");
                            }
                            Console.WriteLine();
                            i++;
                        }
                    Console.WriteLine(i + " rows returned.");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            _conn.Close();
        }
    }
}

从 Vertica 将数据读取到数据集并更改数据：

以下示例显示了如何使用数据适配器从 VMart 架构的维度表读取数据以及将数据插入到其中。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
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();

                  // Try/Catch any exceptions
                    try
            {
                using (_conn)
                {

                            //Create a data adapter object using the connection
                            VerticaDataAdapter da = new VerticaDataAdapter();

                            //Create a select statement that retrieves data from the table
                            da.SelectCommand = new
                        VerticaCommand("select * from product_dimension where product_key < 10",
                        _conn);
                            //Set up the insert command for the data adapter, and bind variables for some of the columns
                            da.InsertCommand = new
                        VerticaCommand("insert into product_dimension values( :key, :version, :desc )",
                        _conn);
                    da.InsertCommand.Parameters.Add(new VerticaParameter("key", VerticaType.BigInt));
                    da.InsertCommand.Parameters.Add(new VerticaParameter("version", VerticaType.BigInt));
                    da.InsertCommand.Parameters.Add(new VerticaParameter("desc", VerticaType.VarChar));
                    da.InsertCommand.Parameters[0].SourceColumn = "product_key";
                    da.InsertCommand.Parameters[1].SourceColumn = "product_version";
                    da.InsertCommand.Parameters[2].SourceColumn = "product_description";
                    da.TableMappings.Add("product_key", "product_key");
                    da.TableMappings.Add("product_version", "product_version");
                    da.TableMappings.Add("product_description", "product_description");

                            //Create and fill a Data set for this dimension table, and get the resulting DataTable.
                            DataSet ds = new DataSet();
                    da.Fill(ds, 0, 0, "product_dimension");
                    DataTable dt = ds.Tables[0];

                            //Bind parameters and add two rows to the table.
                            DataRow dr = dt.NewRow();
                    dr["product_key"] = 838929;
                    dr["product_version"] = 5;
                    dr["product_description"] = "New item 5";
                    dt.Rows.Add(dr);
                    dr = dt.NewRow();
                    dr["product_key"] = 838929;
                    dr["product_version"] = 6;
                    dr["product_description"] = "New item 6";
                    dt.Rows.Add(dr);
                    //Extract the changes for the added rows.
                            DataSet ds2 = ds.GetChanges();

                            //Send the modifications to the server.
                            int updateCount = da.Update(ds2, "product_dimension");

                           //Merge the changes into the original Data set, and mark it up to date.
                            ds.Merge(ds2);
                    ds.AcceptChanges();
                    Console.WriteLine(updateCount + " updates made!");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            _conn.Close();
        }
    }
}