>I guess part of my point is that all database access should use disconnected datasets. Typically you do not want your application to open a database connection and keep it open. You open the connection, grab the data into a DataSet and close the connection. Then, you pass that DataSet back to your UI where it gets worked on (data entry or whatever). Then, when you're ready to save whatever changes the user has made, you send the changes back through to the data access layer (via a DataSet) and then open your connection again, save the data, close your connection.
>
>I suspect that you're talking about accessing the database and leaving the connection open the entire time the data is being used in the front-end UI. This is not good practice (and if I've mis-interpreted what you were saying, I apologize and please correct me).
>
>~~Bonnie
Hi Bonnie
For me, if i connect to a database, i have to make it sure that I close my connection after i executed commands against it, e.g. insert, update and delete...
I'll be using Datasets only in my app if necessary, e.g. for historical reports and enquiries.
You prefer using datasets even you're inserting data on your windows forms.. I had done some test and compare the load time of a data entry form with datasets and without datasets and the form i created with datasets takes longer to appear.
Please bear with me, i'm quite new with .NET and I'll be needing your help. :)
BTW, below is a sample code (newbie code) that i'm using and prepare not to use a datasets because of form loading is slower..
Imports System.Data.SqlClient
Imports FirebirdSql.Data.FirebirdClient
Public Class FNewCompany
Dim cancel_pressed As Boolean
Private Sub FNewCompany_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub NewCompany()
'Data Entry Validation
If txtname.Text = Nothing Then
MsgBox("Company name is required!")
txtname.Focus()
Exit Sub
End If
If txtaddress1.Text = Nothing Then
MsgBox("Company address1 is required!")
txtaddress1.Focus()
Exit Sub
End If
If txtaddress2.Text = Nothing Then
MsgBox("Company address2 is required!")
txtaddress2.Focus()
Exit Sub
End If
If txtaddress3.Text = Nothing Then
MsgBox("Company address3 is required!")
txtaddress3.Focus()
Exit Sub
End If
If txttelephone.Text = Nothing Then
MsgBox("Telephone is required!")
txttelephone.Focus()
Exit Sub
End If
Dim objConnection As FbConnection
'Establish a DSN-Less Connection to the Remote Database
'Jojo R. de la Cuesta 04/10/2006
'Dim strConnection As String = _
' "Driver={Firebird/Interbase};" & _
' "Server=devserver;" & _
' "Database=devserver:C:\INSURANCE SYSTEM\DB\TAKAFUL.GDB;" & _
' "Uid=SYSDBA;" & _
' "Pwd=masterkey"
objConnection = New FbConnection(FMain.FbConnection1.ConnectionString)
Dim strSql As String = "INSERT INTO COMPANYINFO (name,address1,address2,address3,telephone,fax,mobile,email)" & _
"VALUES('" & txtname.Text & "','" & txtaddress1.Text & "','" & txtaddress2.Text & "','" & txtaddress3.Text & "','" & _
txttelephone.Text & "','" & txtfax.Text & "','" & txtmobile.Text & "','" & txtemail.Text & "')"
Dim dbComm As New FbCommand(strSql, objConnection)
With dbComm.Parameters
.Add("@name", FbDbType.VarChar, 50)
.Add("@address1", FbDbType.VarChar, 50)
.Add("@address2", FbDbType.VarChar, 50)
.Add("@address3", FbDbType.VarChar, 50)
.Add("@telephone", FbDbType.VarChar, 30)
.Add("@fax", FbDbType.VarChar, 30)
.Add("@mobile", FbDbType.VarChar, 30)
.Add("@email", FbDbType.VarChar, 30)
End With
dbComm.Parameters("@name").Value = txtname.Text
dbComm.Parameters("@address1").Value = txtaddress1.Text
dbComm.Parameters("@address2").Value = txtaddress2.Text
dbComm.Parameters("@address3").Value = txtaddress3.Text
dbComm.Parameters("@telephone").Value = txttelephone.Text
dbComm.Parameters("@fax").Value = txtfax.Text
dbComm.Parameters("@mobile").Value = txtmobile.Text
dbComm.Parameters("@email").Value = txtemail.Text
Dim iID As Integer
Try
objConnection.Open()
iID = dbComm.ExecuteScalar()
MessageBox.Show("Record was successfully written to Database", "New Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Message", MessageBoxButtons.OK)
End
Finally
'Check connection state, if open close it
If objConnection.State = ConnectionState.Open Then
objConnection.Close()
End If
End Try
Dim dlgResult As DialogResult = MessageBox.Show("Do you want to add more company?", "Add More", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
If dlgResult = Windows.Forms.DialogResult.Yes Then
txtname.Text = ""
txtaddress1.Text = ""
txtaddress2.Text = ""
txtaddress3.Text = ""
txttelephone.Text = ""
txtfax.Text = ""
txtmobile.Text = ""
txtemail.Text = " "
txtname.Focus()
Else
Me.Close()
End If
End Sub
Private Sub IsNoEntries()
End Sub
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
'IsNoEntries()
If txtname.Text <> " " Then
Dim DlgResult As DialogResult = MessageBox.Show("Cancel Data Entries?", "New Company", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If (DlgResult = Windows.Forms.DialogResult.No) Then
txtname.Focus()
Exit Sub
Else
Me.Close()
End If
Else
Me.Close()
End If
End Sub
Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
NewCompany()
End Sub
Private Sub txtname_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtname.KeyPress
If (e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return)) Then
If txtname.Text = Nothing Then
MsgBox("Company name is required!")
txtname.Focus()
Else
txtaddress1.Focus()
End If
End If
Dim c As Char
c = e.KeyChar
If Char.IsDigit(c) Then
MsgBox("Company name must not contain any numbers!")
End If
End Sub
Private Sub txtaddress1_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtaddress1.KeyPress
If (e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return)) Then
txtaddress2.Focus()
End If
End Sub
Private Sub txtaddress2_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtaddress2.KeyPress
If (e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return)) Then
txtaddress3.Focus()
End If
End Sub
Private Sub txtaddress3_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtaddress3.KeyPress
If (e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return)) Then
txttelephone.Focus()
End If
End Sub
Private Sub txttelephone_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txttelephone.KeyPress
If (e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return)) Then
txtfax.Focus()
End If
If txttelephone.Text <> Nothing Then
Dim c As Char
c = e.KeyChar
If Char.IsLetter(c) Then
MsgBox("Telephone must contain only numeric entry!")
End If
End If
End Sub
Private Sub txtfax_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtfax.KeyPress
If (e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return)) Then
txtmobile.Focus()
End If
' Jojo R .de la Cuesta
' Checks whether input is numeric
If txtfax.Text <> Nothing Then
Dim c As Char
c = e.KeyChar
If Char.IsLetter(c) Then
MsgBox("Fax number must contain only numeric entry!")
End If
End If
End Sub
Private Sub txtmobile_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtmobile.KeyPress
If (e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return)) Then
txtemail.Focus()
End If
If txtmobile.Text <> Nothing Then
Dim c As Char
c = e.KeyChar
If Char.IsLetter(c) Then
MsgBox("Mobile number must contain only numeric entry!")
End If
End If
End Sub
Private Sub txtemail_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtemail.KeyPress
If (e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return)) Then
txtwebsite.Focus()
End If
End Sub
Private Sub txtwebsite_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtwebsite.KeyPress
If (e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return)) Then
BtnSave.Focus()
End If
End Sub
Private Sub txtname_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtname.Leave
' Jojo R. de la Cuesta, 11.10.2006
' Check Duplicate record before inserting new record
' Validation takes place after the company name text box entry
' and not when saving the new entries.
If txtname.Text <> Nothing Then
Dim objConn As New FbConnection(FMain.FbConnection1.ConnectionString)
Dim objCommand As New FbCommand("SP_COMPANY_EXIST", objConn)
objCommand.CommandType = CommandType.StoredProcedure
Dim objParameter As New FbParameter("@name", FbDbType.VarChar, 50)
objCommand.Parameters.Add(objParameter)
objParameter.Direction = ParameterDirection.Input
objParameter.Value = txtname.Text
Dim objOutputParameter As New FbParameter("@RESULT1", FbDbType.Integer)
objCommand.Parameters.Add(objOutputParameter)
objOutputParameter.Direction = ParameterDirection.Output
objConn.Open()
objCommand.ExecuteNonQuery()
If (objCommand.Parameters("@RESULT1").Value >= 1) Then
MsgBox("Duplicate Company Name", MsgBoxStyle.Critical, "New Company")
'ErrorProvider1.SetError(txtname, "Duplicate Name, " & txtname.Text & " Already Exist")
txtname.Focus()
Exit Sub
End If
objConn.Close()
End If
End Sub
End Class>
>
>>>
>BTW, its better to use Stored Procedures to Add, update and delete records than using a datasets in your app. Don't rely on datasets...a bad design of using datasets to a database application may lead to user frustration on the application and to the developer :).>>>
>>>I, respectfully,
totally disagree with your suggestion that using DataSets is bad. It's the
best way, IMHO, to work with disconnected data. I know, a lot of people say to do that all with Business objects, but I disagree.
>>>
>>>We grab our data from SQL Server directly into a DataSet and then pass that DataSet all the way back through the Business tier, the Web Service tier and back to the UI.
>>>
>>>~~Bonnie
>>
>>Hi Bonnie,
>>
>>Yes I know, it's the best way to work with disconnected data as datasets are designed for that reason. You may have use datasets the right way on your database application, if you can read my post with "a bad design of using datasets to a database application".
>>
>>BTW, what type of database application your saying here?
>>
>>Thanks..
>>>
>>>
>>>
>>>
>>>>>Hi
>>>>>I'm starting to use C# a fair bit now in Dot net, and am wondering if
>>>>> there is any resource out there which has a 'Replacement' table for VFP v's C# commands, particualarly data commands
>>>>>(I've got Dot Net for VFP Developers book and also had a look at the VFP toolkit for dot net. These do a good job but dont concentrate on Data type commands.
>>>>>If theere was something like the following:
>>>>>
>>>>>VFP C#
>>>>>--- ---
>>>>>Seek = ???
>>>>>Append Blank = ???
>>>>>Replace = ???
>>>>>Delete = ???
>>>>>
>>>>>I realise that the Dot net world is different (using Datasets, datareaders etc) but maybe there some useful info out there somewhere.
>>>>>
>>>>>regards,
>>>>>Gerard
>>>>
>>>>
>>>>Hi,
>>>>
>>>>Below are the equivalent of VFP to C#. For a clearer idea on this, read Mastering C# Database Programming.
>>>>VFP C#
>>>>
>>>>This are use in conjunction to Datasets, dataadapter, Datatable etc....
>>>>
>>>>>--- ---
>>>>Seek = FIND()-disadvantageous in record search,use STPROC/SQLSelect
>>>>Append Blank = Rows.Add()
>>>>Replace = UPDATE(DataTable)
>>>>Delete = RemoveDataRow.Delete()
>>>>
>>>>BTW, its better to use Stored Procedures to Add, update and delete records than using a datasets in your app. Don't rely on datasets...a bad design of using datasets to a database application may lead to user frustration on the application and to the developer :).
