adding column at runtime

[Guest]

Hi All

I am reading data from excel file and storing it into existing table in sql server. If there are new columns in the excel file I want to add new columns in the table dynamically. I am doing followin
• reading data of excel file into excelDataSet.table
• loading Schema of the table into sqlDataset.table
• finding out any new columns in the excel file and then adding columns in table2 of sqlDataSet as follo
•

For Each excelColumn In excelDataSet.Tables(table1).Column
Dim allStockColumns As DataColumnCollection = sqlDataSet.Tables(table2).Column
Dim findThisColumn(0) As Objec
findThisColumn(0) = Trim(excelColumn.ColumnName

Tr
If Not (allStockColumns Is Nothing) The
If Not (allStockColumns.Contains(findThisColumn(0))) The
'column is missing so add i
Dim myColumn = New DataColumn(Trim(excelColumn.ColumnName)
myColumn.DataType = allStockColumns(1).DataType(
myColumn.AllowDBNull = Tru
sqlDataSet.Tables(table2).Columns.Add(myColumn
End I
End I

Catch ex As Exceptio
logWriter.WriteLine(ex.Message
End Tr

This code segment adds new column in the table2 of the sqlDataSet but it doesn’t update table in the database. How can I update schema of the table in the database

Thanks for help

 

[William Ryan eMVP]

You can add the column but it's not going ot add it to the excel sheet.
Remember that the source and the datatable are totally unrelated. The
DataAdapter moves the data around, but assume that you added a column that
didn't exist in the original query. It would take more than a
Insert/Update/Delete command for another adapter to make it work..it'd take
a DDL command. Moreoever, the first adapter would have to have its logic
changed even if they could make this creation happen automagically somehow
b/c it didn't originally know about any column like the one you added.
Furthermore, a DataAdapter doesn't give a hoot about who it works for, it's
decoupled from your dataset... so it doesn't know or care about that new
column as long as the Commands it given will work.

Think about how much trouble this could cause if it did work. You DBA grants
you write and read permissions but not DDL permissions. In order to allow
this he'd have to grant some other high level permissions that you certainly
don't want to just dole out. You could easily drop a table or god knows
what. I think you'll need to use interop and add this column.

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

Hi All,

I am reading data from excel file and storing it into existing table in

sql server. If there are new columns in the excel file I want to add new
columns in the table dynamically. I am doing following

. reading data of excel file into excelDataSet.table1
. loading Schema of the table into sqlDataset.table2
. finding out any new columns in the excel file and then adding columns in table2 of sqlDataSet as follow
.

For Each excelColumn In excelDataSet.Tables(table1).Columns
Dim allStockColumns As DataColumnCollection = sqlDataSet.Tables(table2).Columns
Dim findThisColumn(0) As Object
findThisColumn(0) = Trim(excelColumn.ColumnName)

Try
If Not (allStockColumns Is Nothing) Then
If Not (allStockColumns.Contains(findThisColumn(0))) Then
'column is missing so add it
Dim myColumn = New DataColumn(Trim(excelColumn.ColumnName))
myColumn.DataType = allStockColumns(1).DataType()
myColumn.AllowDBNull = True
sqlDataSet.Tables(table2).Columns.Add(myColumn)
End If
End If

Catch ex As Exception
logWriter.WriteLine(ex.Message)
End Try

This code segment adds new column in the table2 of the sqlDataSet but it

doesn't update table in the database. How can I update schema of the table
in the database?