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;
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
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: