VB6 to VB.Net data transition
I am still coming to grips with finding out the best way to use ADO.NET to perform my database work.
In DAO and ADO I have extensively used Database or Connection objects, and then Recordsets to interact with them.
I have read books, gone through examples, worked through my own solutions, but still feel that I am not exactly grasping the most effective way to do things.
In VB6 I learned fast NOT to use data controls and not to be tempted to use the Data Form wizard to do the work for me. In the end I always finished up rewriting the whole thing from scratch, where I had complete control of exactly what I was doing.
Is the same valid for VB.NET, or is it safe to use the Component Designer or similar tools?
In short, what is the best way to be able to:
Read data from a particular source, then update the same source, or another one?
The particular instance I am working with is a situation where I am reading data from 3 separate .mdb files (three separate connections!) and I want to save processed information into a particular table.
What kind of Data providor is the best to use for this kind of situation?
Dabas
In DAO and ADO I have extensively used Database or Connection objects, and then Recordsets to interact with them.
I have read books, gone through examples, worked through my own solutions, but still feel that I am not exactly grasping the most effective way to do things.
In VB6 I learned fast NOT to use data controls and not to be tempted to use the Data Form wizard to do the work for me. In the end I always finished up rewriting the whole thing from scratch, where I had complete control of exactly what I was doing.
Is the same valid for VB.NET, or is it safe to use the Component Designer or similar tools?
In short, what is the best way to be able to:
Read data from a particular source, then update the same source, or another one?
The particular instance I am working with is a situation where I am reading data from 3 separate .mdb files (three separate connections!) and I want to save processed information into a particular table.
What kind of Data providor is the best to use for this kind of situation?
Dabas
ASKER
Thanks for the comment Arthur, but I do want to teach myself ADO.NET. The idea of Disconnected datasets is quite appealing for my purposes
Dabas
Dabas
Use the OLEDB Provider.
Use the ado.net and use the OleBD provider.
The following is a copy of code that transfer data from text file to oracle. You can modify it to work with .mdb file. when you have 3 separate .mdb files, you will need 3 OleDBConnectin, 3 adpater and 3 Dataset, you can then merge the 3 dataset from different source to one dataset.
The folowing shows 2 dataset and merge them to 1.
Private Sub LoadDataFromTextfileToData
Dim cn As New OleDbConnection()
Dim da_dest As OleDbDataAdapter
Dim ds_dest As New DataSet()
Dim da_source As OleDbDataAdapter
Dim ds_source As DataSet = New DataSet()
Label3.Visible = False
'this method use a Schema.ini file to control the format of the text file
Dim PathtoTextFile As String = Server.MapPath(".") & "\textdata"
Dim oCon As System.Data.OleDb.OleDbCon
"Provider=Microsoft.Jet.OL
"Data Source=" & PathtoTextFile & ";" & _
"Extended Properties='text;HDR=YES;F
da_source = New OleDbDataAdapter("select distinct * from " & inputTextFile, oCon)
da_source.AcceptChangesDur
da_source.Fill(ds_source, "temp")
oCon.Close()
'from oracle database
cn.ConnectionString = Session("CN_STRING")
cn.Open()
da_dest = New OleDbDataAdapter()
Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(da_des
Dim SelectCMD As OleDbCommand = New OleDbCommand(sqlString, cn)
da_dest.SelectCommand = SelectCMD
SelectCMD.CommandTimeout = 30
'create the primary key constrain
da_dest.MissingSchemaActio
da_dest.Fill(ds_dest, "temp")
Dim invalidData As DataTable
If ds_dest.Tables("temp").Row
da_dest.InsertCommand = custCB.GetInsertCommand
'filter out all duplicate data and null values
invalidData = filterInputData(ds_source,
Try
'only pass the valid data
ds_dest.Merge(ds_source.Ge
ds_dest.EnforceConstraints
da_dest.Update(ds_dest, "temp")
Label2.Text = ds_dest.Tables("temp").Row
Catch ex As Exception
Label2.Text = "Error update the database: " & ex.ToString
End Try
Else
Label2.Text = "WARNING!!: the Oracle database already have records in the table. Please delete all records in the table before proceed!"
End If
If invalidData.Rows.Count > 0 Then
Label3.Visible = True
DataGrid1.DataSource = invalidData
DataGrid1.DataBind()
End If
cn.Close()
End Sub
The following is a copy of code that transfer data from text file to oracle. You can modify it to work with .mdb file. when you have 3 separate .mdb files, you will need 3 OleDBConnectin, 3 adpater and 3 Dataset, you can then merge the 3 dataset from different source to one dataset.
The folowing shows 2 dataset and merge them to 1.
Private Sub LoadDataFromTextfileToData
Dim cn As New OleDbConnection()
Dim da_dest As OleDbDataAdapter
Dim ds_dest As New DataSet()
Dim da_source As OleDbDataAdapter
Dim ds_source As DataSet = New DataSet()
Label3.Visible = False
'this method use a Schema.ini file to control the format of the text file
Dim PathtoTextFile As String = Server.MapPath(".") & "\textdata"
Dim oCon As System.Data.OleDb.OleDbCon
"Provider=Microsoft.Jet.OL
"Data Source=" & PathtoTextFile & ";" & _
"Extended Properties='text;HDR=YES;F
da_source = New OleDbDataAdapter("select distinct * from " & inputTextFile, oCon)
da_source.AcceptChangesDur
da_source.Fill(ds_source, "temp")
oCon.Close()
'from oracle database
cn.ConnectionString = Session("CN_STRING")
cn.Open()
da_dest = New OleDbDataAdapter()
Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(da_des
Dim SelectCMD As OleDbCommand = New OleDbCommand(sqlString, cn)
da_dest.SelectCommand = SelectCMD
SelectCMD.CommandTimeout = 30
'create the primary key constrain
da_dest.MissingSchemaActio
da_dest.Fill(ds_dest, "temp")
Dim invalidData As DataTable
If ds_dest.Tables("temp").Row
da_dest.InsertCommand = custCB.GetInsertCommand
'filter out all duplicate data and null values
invalidData = filterInputData(ds_source,
Try
'only pass the valid data
ds_dest.Merge(ds_source.Ge
ds_dest.EnforceConstraints
da_dest.Update(ds_dest, "temp")
Label2.Text = ds_dest.Tables("temp").Row
Catch ex As Exception
Label2.Text = "Error update the database: " & ex.ToString
End Try
Else
Label2.Text = "WARNING!!: the Oracle database already have records in the table. Please delete all records in the table before proceed!"
End If
If invalidData.Rows.Count > 0 Then
Label3.Visible = True
DataGrid1.DataSource = invalidData
DataGrid1.DataBind()
End If
cn.Close()
End Sub
if you want to use ADO.NET, then I might suggest you purchase a copy of:
Database programming with Visual Basic.NET
Carsten Thomsen
a! Press
ISBN 1-893115-29-1
AW
Database programming with Visual Basic.NET
Carsten Thomsen
a! Press
ISBN 1-893115-29-1
AW
ASKER
Thanks folks for your responses:
mjbine: You did not understand the question and that is not an answer, thanks anyhow.
AW: Thanks, I will look into it.
GoodJun: That seems to be quite helpful, although I would appreciate it if you could add a little more comments:
>Dim ds_dest As New DataSet()
>Dim ds_source As DataSet = New DataSet()
What is the difference between these two declarations? When will you use one form, when the other?
>da_source.AcceptChangesDu
What is the objective of this instruction?
> cn.ConnectionString = Session("CN_STRING")
> cn.Open()
Please explain? Or is this Oracle related and irrelevant to my purposes?
>Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(da_des
What is the object of this instruction?
I have many more questions about your example, but will stop here, as I just want to illustrate that the object of my question is to find out what the most efficient way is to program ADO.NET to do what I am doing with DAO and ADO now. My objective is to learn, and what I am looking for is a simple example that comments the different steps needed.
Thanks again!
Dabas
mjbine: You did not understand the question and that is not an answer, thanks anyhow.
AW: Thanks, I will look into it.
GoodJun: That seems to be quite helpful, although I would appreciate it if you could add a little more comments:
>Dim ds_dest As New DataSet()
>Dim ds_source As DataSet = New DataSet()
What is the difference between these two declarations? When will you use one form, when the other?
>da_source.AcceptChangesDu
What is the objective of this instruction?
> cn.ConnectionString = Session("CN_STRING")
> cn.Open()
Please explain? Or is this Oracle related and irrelevant to my purposes?
>Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(da_des
What is the object of this instruction?
I have many more questions about your example, but will stop here, as I just want to illustrate that the object of my question is to find out what the most efficient way is to program ADO.NET to do what I am doing with DAO and ADO now. My objective is to learn, and what I am looking for is a simple example that comments the different steps needed.
Thanks again!
Dabas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear GoodJun:
Thanks for your answers, and apologies for making you work for your points, but I suppose you do realise that my object is to understand what my code is doing, and not just copy and paste because it works. You have been most helpful so far!
1) Concerning .AcceptChangesDuringFill: You say that setting this to False will cause the data changes to be reflected in the underlining database. Seems to be contradictory. If it is False why will it accept the changes?
2) Session: Presently I am working on a Windows Application, so I assume that the Session is completely out of context. Or do you use a Session also with Windows applications? Your link to connectionstring came up with a "The page cannot be displayed"
Lets continue with your example:
'create the primary key constrain
da_dest.MissingSchemaActio
Q: What is this for?
Thanks for your answers, and apologies for making you work for your points, but I suppose you do realise that my object is to understand what my code is doing, and not just copy and paste because it works. You have been most helpful so far!
1) Concerning .AcceptChangesDuringFill: You say that setting this to False will cause the data changes to be reflected in the underlining database. Seems to be contradictory. If it is False why will it accept the changes?
2) Session: Presently I am working on a Windows Application, so I assume that the Session is completely out of context. Or do you use a Session also with Windows applications? Your link to connectionstring came up with a "The page cannot be displayed"
Lets continue with your example:
'create the primary key constrain
da_dest.MissingSchemaActio
Q: What is this for?
Sorry for the link, here is the right link.
http://www.connectionstrings.com/
1) Concerning .AcceptChangesDuringFill: You say that setting this to False will cause the data changes to be reflected in the underlining database. Seems to be contradictory. If it is False why will it accept the changes?
----in the ado.net world, there is a dataset that is a disconnected holder for the records. The dataadapter is used to fill the dataset.When you fill the dataset with data selected from database, the default behavior is treat the records as unchanged (make sense?), when you use the dataadaper.update(dataset)
2) Session: Presently I am working on a Windows Application, so I assume that the Session is completely out of context. Or do you use a Session also with Windows applications? Your link to connectionstring came up with a "The page cannot be displayed"
---- Session if for asp, not for windows. You just use the cn.connectionString="your string" for your app.
Lets continue with your example:
'create the primary key constrain
da_dest.MissingSchemaActio
----By default, the key field in database is not reflected in the dataset (the dataset doesn't know which field is the key field). With the AddWithKey method, the dataset will know which field is key field.
Q: What is this for?
http://www.connectionstrings.com/
1) Concerning .AcceptChangesDuringFill: You say that setting this to False will cause the data changes to be reflected in the underlining database. Seems to be contradictory. If it is False why will it accept the changes?
----in the ado.net world, there is a dataset that is a disconnected holder for the records. The dataadapter is used to fill the dataset.When you fill the dataset with data selected from database, the default behavior is treat the records as unchanged (make sense?), when you use the dataadaper.update(dataset)
2) Session: Presently I am working on a Windows Application, so I assume that the Session is completely out of context. Or do you use a Session also with Windows applications? Your link to connectionstring came up with a "The page cannot be displayed"
---- Session if for asp, not for windows. You just use the cn.connectionString="your string" for your app.
Lets continue with your example:
'create the primary key constrain
da_dest.MissingSchemaActio
----By default, the key field in database is not reflected in the dataset (the dataset doesn't know which field is the key field). With the AddWithKey method, the dataset will know which field is key field.
Q: What is this for?
ASKER
Thanks, GoodJun.
I did not receive the answer I was looking for, but that may be my fault for trying to ask too much at the same time.
I am sure I will "see" you again as I continue to battle through the learning curve.
Arthur:
Thanks to you too for the reference. I have just worked through one gigantic .NET related book, and have ordered another from Amazon. Yours might be the next on the list!
Dabas
I did not receive the answer I was looking for, but that may be my fault for trying to ask too much at the same time.
I am sure I will "see" you again as I continue to battle through the learning curve.
Arthur:
Thanks to you too for the reference. I have just worked through one gigantic .NET related book, and have ordered another from Amazon. Yours might be the next on the list!
Dabas
simply add a reference to the ADO COM library to your solution(references/Add reference/on the COM Tab), and all of your ADO code will work just fine.
AW