Solved

vb.net is this consider a ado.net disconnected ....

Posted on 2004-08-16
8
369 Views
Last Modified: 2010-05-18
is this considered a disconnected database???

  cmd.CommandText = "Select * from table1"
        dim db As New OleDbDataAdapter
        dim da As New DataTable

       db.SelectCommand = cmd
        db.Fill(da)
        Dgagrid1.DataSource = da

if it is what would it look like if it was connected. If it isn't what would it look like being disconnected.


also what is the best way to go???
0
Comment
Question by:hogan9111
  • 4
  • 2
  • 2
8 Comments
 
LVL 41

Accepted Solution

by:
graye earned 40 total points
Comment Utility
Yes, everything in ADO.Net is disconnected... you don't have much of a choice

The Fill() method of the DataAdapter actually does the Open and Close of the connection object behind the scences.   The database is opened only long enough to pull the data out, and then it's closed

Sure, you can manually open a connection object first (and it will stay open after the Fill() method), but that's not exactly a "connected" database... that just means you haven't closed it yet.  The concept of a disconnected recordset goes well beyond the connection object... it's a technique to pull data to your PC, play with it locally, and put it back.   Keeping a connection object open, wouldn't change the fact that you still pull the data to your PC, play with it, and then put it back.

There really isn't an analog to the traditional connected ADO recordset in ADO.Net.   The closest thing is the DataReader... but it only works for pulling data... (there is no DataWriter).

I see several of your posts here...  Let me see if I can help with a generic example...

' Imports is not required, but make the syntax reasier to read
Imports System.Data.oledb

Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim da As New OleDbDataAdapter
    Dim dt As New DataTable

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' The connection object is now OleDbConnection
        Dim con As New OleDbConnection()
        Dim cmd As New OleDbCommand()
        Dim dr As OleDbDataReader

        ' Can't put the connection string as argument to open method
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\Test.mdb"
        Try
            con.Open()
        Catch ex As Exception
            MsgBox("Yikes! Can't open the database" & vbCr & ex.Message, MsgBoxStyle.Critical, "Error!")
            Exit Sub
        End Try

        ' Create a DataReader object.  You use the "ligthweight" data reader
        ' object for doing things like populating a combobox.  The data reader
        ' is not well suited for doing much else.
        cmd.Connection = con
        cmd.CommandText = "Select * from Table1"
        dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

        ' Fill a combo box with the datareader
        Do While dr.Read = True
            ' the GetString() method assumes you already know the
            ' schema of the table, and that the first element is a string
            ComboBox1.Items.Add(dr.GetString(0))
        Loop
        ' The close to the DataReader is required, because the DataAdapter
        ' below internally uses a datareader object... and you can only have
        ' one datareader object open at a time.
        dr.Close()

        ' Use a DataAdapter to fill a DataTable.  This is the "heavyweight"
        ' method used to fill either tables or the new "dataset" object.
        ' There is an implied con.open() and con.close() within the Fill
        ' method itself... so the data adapter is normally only connected
        ' to the database as long as it takes to do the fill()/update()
        da.SelectCommand = cmd
        da.Fill(dt)

        ' Bind the datagrid with the new dataset
        DataGrid1.DataSource = dt

        ' Since we explicitly opened the Connection object, it remains open
        ' after the Fill method.  We could have let the DataAdapter do the
        ' open and close for us, but I like to work the error messages up front.
        con.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dt_changes As DataTable
        Dim cb As OleDbCommandBuilder
        Dim rows As Integer

        ' Let's see if anybody made any changes to the table via the datagrid
        dt_changes = dt.GetChanges()

        If Not IsNothing(dt_changes) Then
            Try
                ' Create the SQL insert/delete/update commands that will be
                ' used by the data adapter's update method below.  This is
                ' optional... you *could* populate the 3 properties of the
                ' data adapter yourself (but why?) You might also noice that
                ' the cb object is not reference anywhere... it's magic!
                cb = New OleDbCommandBuilder(da)
                ' Update the changes to the database.  Remember, You can only
                ' update a table that has a primary key!
                rows = da.Update(dt_changes)
                MsgBox(rows & " updated")
            Catch ex As Exception
                MsgBox("Yikes, Can't save the changes!" & vbCrLf & ex.Message, MsgBoxStyle.Exclamation)
            End Try
        Else
            MsgBox("Hey, there's nothing to udpate")
        End If

        ' No need to close any database connections... it's already closed
        ' because this time we let the DataAdapter do the open/close for us
    End Sub
End Class
0
 
LVL 19

Expert Comment

by:arif_eqbal
Comment Utility
You are using DataAdapter to connect to the dataBase, So its pretty simple, If the DataAdapter has a live (Open) Connection then it is Connected If the Connection object used by DataAdpter is closed it is disconnected.

If you are not explicitly opening the Connection the DataAdapter does it implicitly and it closes the connection immediately after fetching the records of the query, so it is Disconnected.

0
 

Author Comment

by:hogan9111
Comment Utility
Ok, now for the big thing, which method is best. I noticed that there are components in the the toolbox that will provide wizards and does the coding for me. Now I am familiar with doing all the coding myself. Which way is the best way to do it. Keep in mind my programs could grow in the future and I don't want to be stuck, plus I want to do it the best way. I guess I am unfamilar of what the difference between the two is?????
0
 

Author Comment

by:hogan9111
Comment Utility
Also I need to have a crystal report attached to the database, I noticed by using the components and following the wizard it create something that I can call in the wizard for crystal report.  Unless I need to hard code it to look at this:

dim da As New DataTable

which way is better????
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:hogan9111
Comment Utility
also what is the Dataset1.xsd?? Do I use that to connect crystal reports???
0
 
LVL 19

Expert Comment

by:arif_eqbal
Comment Utility
Well hogan9111

As for the Drop down stuff on the toolbox (wizards etc.) I am not in much favor of them. They might be generating optimized codes, but if you are a good programmer you could as well do that manully. Somehow, I feel the code we write manually might be better since the generated code would be a general purpose code.

To get my point look at some of the Queries generated by them, in the Where clause of the Query they'll have all the fields ANDed together, they can't do without it because the autogenerated code could not know beforehand what columns might change and what not. But we have the design in our hands we are armed with this knowledge that comparing against only the Primary Key field (or probably one more field) will be good enough. So I feel its good if you are familira with doing all the coding yourself it might take a few more minutes but you stay in control of the code.

As for the  Dataset1.xsd, its actually the XML schema of the Dataset. It has quite a few good, perhaps the most important is that it allows the tabels and columns to be accessed as properties of the dataset. Normally we access the column value as
Dataset1.Tables("Table1").Columns("Col1")
However if we create the schema (.xsd file) we can access the columns as
Dataset1.Table1.Col1

0
 

Author Comment

by:hogan9111
Comment Utility
so the dataset1.xsd is just a physical look of the fields, I could just create my own physical look right. What is the difference btw the hardcoding way and the actual file.



0
 
LVL 41

Expert Comment

by:graye
Comment Utility
Since the XSD file is just an XML-based text file, it really doesn't matter how it gets created.   For example, SQL Server has a native feature for creating XSD files.   Or you could use the Visual Studio wizards, or create it by hand.

However, the XSD "standard" would need to be followed... (you couldn't just create a different tag out of the blue).

Version 1.0 of the XSD schema standard has been released May 2001 and can be found at http://www.w3.org/TR/xmlschema-0/, http://www.w3.org/TR/xmlschema-1/ along with http://www.w3.org/TR/xmlschema-2/. The working draft of XSD 1.1 can be found at http://www.w3.org/TR/2003/WD-xmlschema-11-req-20030121/. (stolen from http://www.developer.com/net/net/article.php/3388311)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This is about my first experience with programming Arduino.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now