In my web application, i am reading the all the openoffice data into data table and i inserted to data base by using sqlbulkcopy. here i have client column and it is not in data table once i added to data table but how can i read all the values of client. Anyone helpme
My code is:
This is in .cs page
string ddlclient = ddlClient.SelectedItem.Text;
string ddlval = ddlTypes.SelectedValue;
DataTable dtColumns = new DataTable();
dtColumns.Columns.Add("ColumnNames", typeof(string));
dtColumns.Columns.Add("ID", typeof(int));
dtColumns.Rows.Add("ID", 1);
dtColumns.Rows.Add("Description", 1);
dtColumns.Rows.Add("Credits", 1);
dtColumns.Rows.Add("QTY%", 1);
dtColumns.Rows.Add("Amount", 1);
dtColumns.Rows.Add("Amount %", 1);
dtColumns.Rows.Add("Client",1);
DataRow[] dr = dtColumns.Select(" ID='" + ddlval + "'");
ReadExcelSheet obj = new ReadExcelSheet();
DataTable dt = obj.Read(fullpath, 1, dr, ddlclient);
SqlBulkCopy objbulk = new SqlBulkCopy(con);
objbulk.DestinationTableName = "Adjustments";
objbulk.ColumnMappings.Add("ID", "ID");
objbulk.ColumnMappings.Add("Description", "Description");
objbulk.ColumnMappings.Add("Credits", "Credits");
objbulk.ColumnMappings.Add("QTY%", "QTY %");
objbulk.ColumnMappings.Add("Amount", "Amount");
objbulk.ColumnMappings.Add("Amount %","Amount %");
objbulk.ColumnMappings.Add("Client" ,ddlClient.SelectedItem.Text);
//inserting Datatable Records to DataBase
con.Open();
objbulk.WriteToServer(dt);
con.Close();
and my Reading code is in class file:
public DataTable Read(string strPath,int isheet,DataRow[] dr1,string ddlclient)
{
foreach (var dr in dr1)
{
columnnames = dr.ItemArray[0].ToString();
dtAdjustments.Columns.Add(columnnames, typeof(string));
}
try
{
int iRow = 1;
int iEmpty = 0;
bool boolcontinue = true;
while (boolcontinue)
{
DataRow drNew = dtAdjustments.NewRow();
// DataRow drnew2 = dtexcel.NewRow();
ID = ((XText)oSheet.getCellByPosition(0, iRow)).getString();
if (!string.IsNullOrEmpty(ID))
{
iEmpty = 0;
int i = 0;
for (int iCol = 0; iCol < 6; iCol++)
{
drNew[i] = ((XText)oSheet.getCellByPosition(iCol, iRow)).getString();
i++;
}
dtAdjustments.Rows.Add(drNew);
iRow++;
}
else
{
iEmpty++;
}
Thank you