.net Database Skills

[Rads78 0]

Ok... banyak perbezaan antara VS6 dan .NET dari segi cara penggunaan database. Aku realise yang .NET kalau connect direct using ODBC.NET misalnya untuk access datareader camnih

Dim drData as ODBCDataReader
Dim cmdCommand as new ODBCCommand()
Dim cnConnect as new ODBCConnection()

cnConnect.ConnectionString = "bla..bla..bla.."

cmdCommand.CommandText = "select * from myTable"
cmdCommand.Connection = cnConnect
cmdCommand.Connection.Open()
drData = cmdCommand.ExecuteReader()

bla.. bla..bla..

akan buka banyak connection especially kalau kita perlu membandingkan between several data readers dan we cant close the data readers while comparing it. Too many connection ke database at the same time is bad..........

So sekarang ini cara yang aku guna adalah

untuk data reader

Aku create class aku sendiri dimana aku buka data reader, move semua data tuh ke datatable, close data reader / connection/ command, create almost similar functionality as VB6 recordset (.move, .EOF,.getFields(int),.ColumnCoun,tbla..bla..)

aku juga create function yang lain-lain untuk berinteraksi dengan database dimana pada pandangan aku perlu seperti

returnLinear(return first field, first columnfrom table)

returnTable (for datagrid datasource)

returnArray(for ListBoxes, comboboxes)

so... apa cara korang connect dengan database using .NET dan camner korang block daripada using too much connections to the database??

[1kHz 0]

Dalam ADO.NET rasanya penggunaan DataReader dah tak byk dah..

Sebab lebih senang gunakan gandingan DataAdapter dan DataSet/DataTable..

Utk retrieve a table is as simple as

Dim conn As New SqlConnection("Your Connection String")
Dim da As New SqlDataAdapter()
da.SelectCommand = new SqlCommand("SELECT * FROM MyTable", conn)
Dim dt As New DataTable
da.Fill(dt)

Utk operasi lain spt run sql yg bukan query atau yg pulangkan nilai scalar boleh guna SqlCommand punya method ExecuteNonQuery dan ExecuteScalar etc ..

Ini ADO.NET, jgn gunakan style ADO lama lagi..

[1kHz 0]

Contoh aku guna Sql punya clasess, but almost the same with others like Odbc, OleDb, Oracle or whatever..

Edited July 27, 2005 by 1kHz

[Rads78 0]

thanks... but i though you cant compare the value in the table as the same way as you compare it using data reader

[1kHz 0]

Part yg "compare" tu aku tak paham maksud ko.. Code yg ko paste tu pun takde mende compare pun.. Maybe u can explain more?

[Rads78 0]

yeah... compare camnih

myData1 = cmdCommand1.ExecuteReader
myData2 = cmdCommand1.ExecuteReader

if string.compare(myData1.GetValue(0),myData2.GetValue(0)) = 0 then
      'record compared
else
      'different record
end if

itu yg aku problem ndak compare guna datatable

[1kHz 0]

Cara mcm ni boleh juga, tapi tengok mcm ko cakap kena guna 2 connection..

Cara lain yg boleh aku nampak ialah load ke 2 set of data dalam DataTable dan compare dgn iterate Rows dlm DataTable tu..

Dim dt1 As DataTable = GetTheAwesomeData()
Dim dt2 As DataTable = GetAnotherAwesomeData()

For i As Integer = 0 To dt1.Rows.Count
  If ( dt1.Rows(i)(0).ToString().Equals( dt2.Rows(i)(0).ToString() ) )
    ' yeah babeh
  Else
    ' oh no
  End If
End For

I've never encounter a problem like this yet.. Tapi kalau dilihat semula, mungkin penyelesaian lebih baik sebenarnya boleh dibuat at the SQL level.. ye lah, misalnya kalau nak kumpul a set of values yg tak sama, mcm ni guna SQL statement pun boleh..

[1kHz 0]

Eh, kejap.. ni just nak compare 2 scalar value dari DB ke?

Mcm ni..

Dim conn As New SqlConnection("blah blah blah")
Dim cmd1 As New SqlCommand("SELECT COUNT(1) FROM Table1", conn)
Dim cmd2 As New SqlCommand("SELECT COUNT(1) FROM Table2", conn)

conn.Open();
Dim count1 As Integer = CInt(cmd1.ExecuteScalar())
Dim count2 As Integer = CInt(cmd2.ExecuteScalar())

If (count1 = count2)
  ' yeah babeh
Else
  ' oh no
End If
conn.Close()

Sebenarnya kalo mcm ni cuma SATU connection je yg dibuka, sbb mereka guna conn yg sama...

Dan sekali lagi, kadang2 benda2 mcm ni lebih mudah dibuat dari SQL je..