Question Adding a variable data value to a DataTable

andrewmanuja

Well-known member
Joined
May 30, 2019
Messages
75
Programming Experience
Beginner
Hi All,

I want to add a variable int value to a DataTable column. I have used one of the Microsoft's solution around DataTable to perform this task.

Basically, i got two tables in my SQL Server named, "BulkCopyDemoDifferentColumns" and "BulkCopyDemoMatchingColumns" and the intention is, I want to fetch some data from the BulkCopyDemoDifferentColumns and copy them into BulkCopyDemoMatchingColumns table.

This operation is working fine. But the issue is when I want to add the "SupplyID' value as a column to the datatable(the object name is "newProducts"), I am not getting the data populated to the "newProducts" datatable.

Please find the code I have used to perform the task below;

C#:
    class Program
    {
        static void Main(string[] args)

        {
            string connectionString = GetConnectionString();
            // Open a connection to the AdventureWorks database.
            using (SqlConnection sourceConnection = new SqlConnection(connectionString))
            {
                sourceConnection.Open();

                // Perform an initial count on the destination table.
                SqlCommand commandRowCount = new SqlCommand("SELECT COUNT(*) FROM " + "dbo.BulkCopyDemoMatchingColumns;", sourceConnection);
                long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
                Console.WriteLine("Starting row count = {0}", countStart);
                commandRowCount.Parameters.Clear();

                // Get data from the source table as a SqlDataReader.
                SqlCommand commandSourceData = new SqlCommand("SELECT ProdID, ProdName, " + "ProdNum " +
                "FROM BulkCopyDemoDifferentColumns WHERE ProdStatus=@aProdStatus;", sourceConnection);
                commandSourceData.Parameters.AddWithValue("@aProdStatus", 0);
                SqlDataReader reader = commandSourceData.ExecuteReader();
              
                // Create a table with some rows.
                DataTable newProducts = MakeTable();
              
                // Create the SqlBulkCopy object.
                // Note that the column positions in the source DataTable
                // match the column positions in the destination table so
                // there is no need to map columns.
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sourceConnection))
                {
                    bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns";

                    try
                    {
                        // Write unchanged rows from the source to the destination.
                        bulkCopy.WriteToServer(reader);
                        // bulkCopy.WriteToServer(newProducts, DataRowState.Unchanged);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        reader.Close();
                    }
                }

                // Perform a final count on the destination
                // table to see how many rows were added.
                long countEnd = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
                Console.WriteLine("Ending row count = {0}", countEnd);
                Console.WriteLine("{0} rows were added.", countEnd - countStart);
                Console.WriteLine("Press Enter to finish.");
                Console.ReadLine();
            }
        }

        private static DataTable MakeTable()
        {
            string connectionString = GetConnectionString();

            // Open a connection to the AdventureWorks database.
            using (SqlConnection sourceConnection = new SqlConnection(connectionString))
            {
                sourceConnection.Open();
                //Below code is used to retrieve the next SupplyID number to insert into the "newProducts" DataTable
                    SqlCommand cmd = new SqlCommand("SELECT isnull(max(cast(SupplyID as int)), 0)+1 FROM tbl_Supply", sourceConnection);

                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);

                    int a = 0;
                    a = Convert.ToInt32(dt.Rows[0][0].ToString());             

                // Create a new DataTable named NewProducts.
                DataTable newProducts = new DataTable("NewProducts");

                // Add four column objects to the table.
                DataColumn productID = new DataColumn();
                productID.DataType = System.Type.GetType("System.Int32");
                productID.ColumnName = "ProductID";
                //productID.AutoIncrement = true;
                newProducts.Columns.Add(productID);

                DataColumn productName = new DataColumn();
                productName.DataType = System.Type.GetType("System.String");
                productName.ColumnName = "Name";
                newProducts.Columns.Add(productName);

                DataColumn productNumber = new DataColumn();
                productNumber.DataType = System.Type.GetType("System.String");
                productNumber.ColumnName = "ProductNumber";
                newProducts.Columns.Add(productNumber);

                DataColumn supplyID = new DataColumn();
                supplyID.DataType = System.Type.GetType("System.Int32");
                supplyID.ColumnName = "SupplyID";
                newProducts.Columns.Add(supplyID);             

                //Below command is used to fetch the data from the "BulkCopyDemoDifferentColumns" table to fill the "newProducts" DataTable where the ProdStatus =0
                SqlCommand commandSourceData = new SqlCommand("SELECT ProdID, ProdName, " + "ProdNum " +
               "FROM BulkCopyDemoDifferentColumns WHERE ProdStatus=@aProdStatus;", sourceConnection);
               commandSourceData.Parameters.AddWithValue("@aProdStatus", 0);

               SqlDataReader reader1 = commandSourceData.ExecuteReader();
              
               if(reader1.HasRows )
               {
                   while(reader1.Read())
                   {
                       Console.WriteLine("{0}\t{1}", reader1.GetInt32(0),
                       reader1.GetString(1));
                   }
               }
            
               //THIS IS WHERE I BELIEVE THE DATA IS NOT GET UPDATED TO THE "newProducts" DATATABLE
               foreach(DataRow row in newProducts.Rows)
                {
                    row["SupplyID"] = a;
                }
            
               reader1.Close();
               sourceConnection.Close();
               return newProducts;
            }
        }

        private static string GetConnectionString()
        // To avoid storing the connection string in your code,
        // you can retrieve it from a configuration file.
        {
            return "Data Source=55504-CGHS\\SQLEXPRESS; " +
            " Integrated Security=true;" +
            "Initial Catalog=AdventureWorks;"+
            "MultipleActiveResultSets=true;";
        }       
    }

Note
The Database name is "AdventureWorks".
"BulkCopyDemoDifferentColumns" table got the fields, ProdID,ProdName,ProdNum,ProdStatus
"BulkCopyDemoMatchingColumns" table got the fields, ProductID, Name,ProductNumber,SupplyID
"tbl_Supply" table got the field, SupplyID

In order to write the variable value (which is "a" representing the next "SupplyID"), I have used foreach method. I doubt whether there is an error within the respective block of code.

Appreciate your feedback on below two questions?

1. So far, the above code get me the data (ProductID, Name, ProdcutNumber) into the "BulkCopyDemoMatchingColumns" table EXCEPT for the "SupplyID" data value?

2. I am performing this operation between two tables which are in the same database. Will that be alright?

Thanking you in advance.

Kind regards,

Andrew
 
Last edited by a moderator:
Firstly, please format your code snippets. I suspect that I have mentioned this before but, if I haven't, I have now. Reading unformatted code is difficult enough is small chunks but the bit wad of code you posted is just way too hard.

Secondly, that wad of code really is too big. You need to do more to narrow down the problem. Just dumping all your code into a post is not the way to go. Firstly, you need to debug your code properly, i.e. set a breakpoint and then step through the code line by line, testing the state at each step to see if it matches your expectations. You then post only the code relevant to the problem and highlight exactly where the issue is, i.e. where the state differs from your expectations, and explain exactly how it differs. From what I can see, if you had done that then the issue would be glaringly obvious and you likely wouldn't have needed to ask a question at all. That's why you need to ALWAYS debug your code first; you will often be able to fix the issue yourself and, if you can't, you can provide us with ALL the relevant information specific to the issue. I suggest that you concentrate on the MakeTable method.
 
Hi,

Thanks a lot for the respond.

Sorry about not formatting, but just wondering how can I format only the code I'm copying onto the forum?

Meantime, I'll work on the said method and I revert you back with an update on the progress.

Thank you.

Kind regards,

Andrew
 
You can paste your code between code tags, just like this ::

nD918n6.gif


Doing this helps to keep the original indentation as well as using the color legends makes the code more readable for us.
 
Hi All,

Thank you very much for the guidance provided.

Finally, after a long battle, I was able to successfully load the data into the destination table with the said variable.

Kind regards,

Andrew
 
Back
Top Bottom