|
IDE: Visual Studio 2005
Language: Visual Basic
Question1: "How would I alter a table (specifically add a column) that already contains data?
Question2: "If I am unable to add a column to a dbf file that contains data, then how should I approach this problem?"
Question3: "Should I use an alternitive way to read and write information to a dbf file? (I am looking the quickest way to process data.)"
Situation: I am trying to find away to quickly process information that is contained in a dbf file. Processing consists of removing records, adding records, and adding fields to a dbf file. Most important processing must be exceptionally fast. For example: I am currenly working on a way to programmically remove records that we consider to be "companies". Since, we do alot of mailing we do not want our information to be mailed to other companiies, we just want to mail to residential addresses. So, in this particullar case, I need to filter through all the records and compare them to our company names database and then remove all records that match. Another Situation: On some of the mailing we do, I need to programmically add a field, such as "winning numbers" to and exsisting DBF file that already contains data.
Problem: One problem I am currently experencing is inserting records into a dbf file. The processing time is extremely slow, It might take me 7 minutes or more to insert a few hundred records into a dbf file(using sql queries). This is not exceptable, since we are dealing with a large amount of data. The data files we usually work with are around 5,000 to 200,000 records long. This could be a logical problem. I backup the dbf file, just incase the program crashes, I also copy the dbf file and remove all records (I just need header information) because I need a new file just for company names. (the files I will be working with are the orginal file and the company file, and the backup file). I am importing the dbf file into a dataset then I run a few functions to see if the fullname field matches company names in our database. If there is a match then I remove the name from the original file, then I add a record to the company file. Everything processes fine, except inserting records into the companies dbf. Its my assumption that the problem that I am having is because the file is in a dbf format. I have never experienced a problem like this in any other file format. Could it be my connection string? Should I try an alternative way to connect to data, maybe odbc? I thought about adding a field to the exsisting table and process it that way, however, the program I am working on throws an oledb exception, and will not allow me to alter a table ( add a column) to a dbf file that already contains data. And this will be a problem in future projects. I need a way to add columns to a dbf fie. I know that fox pro has this capabilities. Can I use a fox pro connection string to add this capability. Our company does not like fox pro, and will not use it for any future projects. So here I am stuck between asphalt and a rock. Any suggestions? I will display some code that I think is related to this topic, if I need to post any more I will.
'connection string
cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=dBASE IV;User ID=Admin;Password="
'alter table
Dim sfile As New FileProcessing
Dim qy As String
Dim path As String = \\path
qy = "ALTER TABLE 20368 ADD COLUMN winning char(5)"
sfile.Open_File(path, qy, "DBF")
'error exception
Operation not supported on a table that contains data.
Private Sub Processing()
Dim qy As String
Dim temp() As String
Dim sName As String
Dim sfile As New FileProcessing
Dim dr As DataRow
Dim sTemp As String = ""
Dim item As String = ""
Dim iLength As Integer = 0
Dim sFullname As String = ""
Dim match As Boolean = False
Dim dc As DataColumn
Dim int As Integer = 0
Dim qyCompanies As String
'back up original file
File.Copy(SourceFile, DestinationFile)
temp = txtSearch.Text.Split(".")
sName = temp(0)
For i As Integer = 0 To tbFilter.Rows.Count - 1
If dgView.Item(0, i).Value = False Then
qy = "delete from " + sName + " where fullname = @fullname"
Fullname = tbFilter.Rows(i).Item(1).ToString
sfile.UpdateDBF(SourcePath, qy, "DBF", sFullname)
End If
Next
'create companes dbf
DestinationFile = SourcePath + sName + "c.dbf"
File.Copy(SourceFile, DestinationFile)
qy = "Delete * From " + sName + "c"
sfile.UpdateDBF(SourcePath, qy, "DBF")
'create companies query statement
qyCompanies = "insert into " + sName + "c("
For Each dc In ds.Tables(0).Columns
sTemp = sTemp + "[" + dc.Caption + "],"
Next
sTemp = sTemp.Substring(0, sTemp.Length - 1) + ")"
qyCompanies = qyCompanies + sTemp + "Values("
sTemp = ""
For Each dc In ds.Tables(0).Columns
sTemp = sTemp + "@" + dc.Caption + ","
Next
sTemp = sTemp.Substring(0, sTemp.Length - 1) + ")"
qyCompanies = qyCompanies + sTemp
For i As Integer = 0 To tbFilter.Rows.Count - 1
If dgView.Item(0, i).Value = False Then
Dim itemArray(0) As String
Dim itemp As Integer = 0
For Each dr In tbFilter.Rows
For int = 0 To ds.Tables(0).Columns.Count - 1
itemArray(int) = tbFilter.Rows(i).Item(int).ToString
ReDim Preserve itemArray(int + 1)
Next
Next
sfile.UpdateDBF(SourcePath, qyCompanies, "DBF", itemArray, ds)
End If
Next
File.Move(DestinationFile, CompanyFile)
MsgBox(Now)
End Sub
Public Sub UpdateDBF(ByVal FilePath As String, ByVal qy As String, ByVal FileExt As String, ByVal sFullname As String)
Dim cs As String
Dim cn As OleDbConnection
Dim dc As OleDbCommand
Dim temp As String = ""
Dim size As String
Dim i As Integer = 0
Try
cs = Connection_String(FilePath, FileExt)
cn = New OleDbConnection(cs)
cn.Open()
dc = New OleDbCommand(qy, cn)
temp = "@FULLNAME"
size = sFullname.Length
dc.Parameters.Add(temp, OleDbType.Char, size, "FULLNAME")
dc.Parameters(temp).Value = sFullname
dc.ExecuteNonQuery()
cn.Close()
Catch ex As Exception
Throw ex
End Try
End Sub
| 
index > .NET Framework Data Access and Storage > Adding column to a dbf file