Retrieve data from database and bulk index them-Sql Server

Elastic Stack Elasticsearch
1

Hi all,

I have some records in SQL Server management studio and I want to index them to my ES index.
At present I'm indexing them individually which is taking a long time. I would like to bulk index them. I tried using the Bulk Descriptor it is throwing an error saying > the object to index, if id not set manually it will be inferred from the object

below is my code.

string connectionString = ConfigurationManager.ConnectionStrings["MyDbConn"].ToString();
                using (cn = new SqlConnection(connectionString))
                {
                    cmd = new SqlCommand();
                    cmd.CommandText = ConfigurationManager.AppSettings["dbName"];
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = cn;

                    cn.Open();
                    
                    //to index the students response into the ES 
                    try
                    {
                        client = ConfigSettings.connection();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message.ToString());
                        throw;
                    }

                    reader = cmd.ExecuteReader();

                        while (reader.Read())
                        {
                            var descriptor = new BulkDescriptor();
                            descriptor.Index<Class1>(b=>b.Document(new Class1
                            {
                                Id = Int32.Parse(reader[0].ToString()),
                                Title = reader[1].ToString(),
                                BodyContent = reader[2].ToString()  
                            }, z => z.Refresh()));
                        }

Client.Bulk(descriptor);

Client settings

        configvalue1 = ConfigurationManager.AppSettings["url"];
        var pool = new SingleNodeConnectionPool(new Uri(configvalue1));
        var defaultIndex = "dbtrial";

        settings = new ConnectionSettings(pool)
         .DefaultIndex(defaultIndex)
         .MapDefaultTypeNames(m => m.Add(typeof(Class1), "records"))

        client = new ElasticClient(settings);
        client.createIndex(defaultIndex);

Is there any way other than descriptor to index thousands of documents in a efficient way?

Thanks In Advance

2

I tried something like this and it is working fine for indexing(it is taking between 3300ms to 4500ms for indexing 1000 documents.So I'm not sure if this can be reduced further using any other method). I'm not sure of the performance if there are some millions of records.

                   List<Class1> feedlist = new List<Class1>();                        
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        class1 c = new Class1
                        {
                            Id = Int32.Parse(reader[0].ToString()),
                            Title = reader[1].ToString(),
                            BodyContent = reader[2].ToString()  
                        };
                        feedlist.Add(c);
                    }
                    client.IndexMany(feedlist, "dbtrial", "records");

Any better methods for indexing millions on records can be suggested. TIA