Database Connection


	Database Connection

ปรับปรุง : 2551-02-13 (แยกกลุ่ม)

1. OLEDB on Microsoft Access

1.1 นำข้อมูลเข้าตารางด้วย Insert ผ่านการเชื่อมต่อแบบ OLEDBConnection

Imports system.data.oledb
Dim strdb As String = "c:\dthai97.mdb"
Dim strconn As String="provider=microsoft.jet.oledb.4.0;data source="& strdb
Dim strsql As String 
Dim cn As New System.Data.OleDb.OleDbConnection(strconn)
Dim cm As New OleDbCommand(strsql, cn)
Dim dr As OleDbDataReader
strsql = "insert into tbthai values(" & TextBox1.Text
strsql &= ",'" & TextBox2.Text & "'"
strsql &= "," & TextBox3.Text & ")"
cm = New OleDbCommand(strsql, cn)
cn.Open()
dr = cm.ExecuteReader
cn.Close()

1.2 นำข้อมูลมาแสดงด้วย Select ผ่านการเชื่อมต่อแบบ OLEDBConnection

Imports system.data.oledb
Dim strdb As String = "c:\dthai97.mdb"
Dim strconn As String="provider=microsoft.jet.oledb.4.0;data source="& strdb
Dim strsql As String = ""
Dim cn As New System.Data.OleDb.OleDbConnection(strconn)
Dim cm As New OleDbCommand(strsql, cn)
Dim dr As OleDbDataReader
Dim s As String = ""
strsql = "select fid,fname,fsalary from tbthai"
cm = New OleDbCommand(strsql, cn)
cn.Open()
dr = cm.ExecuteReader()
While dr.Read()
s &= dr.Item(0) &","& dr.Item(1) &","& dr.Item(2) &Chr(10)
End While
cn.Close()

1.3 เลือกข้อมูลด้วย where และ like

<%@ Page Language="VB" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<%@import Namespace="system.data.oledb" %>
<! http://www.thaiabc.com/download/search.zip >
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server"><title>Untitled Page</title></head>
<body><form id="form1" runat="server"><div> 
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<%        
If (Len(TextBox1.Text) > 1) Then
Dim strdb As String = "c:\search.mdb"
Dim strconn As String = "provider=microsoft.jet.oledb.4.0;data source=" & strdb
Dim cn As New System.Data.OleDb.OleDbConnection(strconn)
Dim sql As String = "select name,describ,address "
sql &= "from webdat "
sql &= "where describ like '%" & TextBox1.Text & "%'"
Dim cm = New OleDbCommand(sql, cn)
Dim dr As OleDbDataReader
Dim s As String = ""
cn.Open()
dr = cm.ExecuteReader()
While dr.Read()
s &= dr.Item(0) & "," & dr.Item(1) & "," 
s &= dr.Item(2) & Chr(10)
End While
cn.Close()
Response.Write("<pre>" & s)
End If
%>
</div></form></body></html>

2. SQL Server

2.1 นำข้อมูลเข้าตารางด้วย Insert ผ่านการเชื่อมต่อแบบ SQLConnection

Imports system.data.sqlclient
Dim conn As SqlConnection
Dim myCommand As SqlCommand
Dim ra As Integer
Dim sql As String
conn = New SqlConnection("server=.\sqlexpress;" + _
"user id=thai;password=thai2007;database=dbthai")
conn.Open()
sql = "insert into tbthai values (5,'abc')"
myCommand = New SqlCommand(sql, conn)
ra = myCommand.ExecuteNonQuery() ' ra = 1
MessageBox.Show("New Row Inserted " & ra)
conn.Close()

2.2 นำข้อมูลมาแสดงด้วย Select ผ่านการเชื่อมต่อแบบ SQLConnection

Imports system.data.sqlclient
Dim conn As SqlConnection
Dim myCommand As SqlCommand
Dim s As String = ""
conn = New SqlConnection("server=.\sqlexpress;" + _
"user id=thai;password=thai2007;database=dbthai")
conn.Open()
myCommand = New SqlCommand("select * from tbthai", conn)
Dim reader As SqlDataReader = myCommand.ExecuteReader()
While reader.Read()
s &= reader(1) & Chr(10) 'only name
End While
MessageBox.Show(s)
conn.Close()

3. MySQL Connection / ODBC

3.1 นำข้อมูลมาแสดงด้วย Select ผ่านการเชื่อมต่อแบบ MySQLConnection

Imports MySql.Data.MySqlClient
Dim conn As New MySqlConnection
conn.ConnectionString = "server=127.0.0.1;" _
& "user id=admin;password=p;database=mysql"
Dim cmd As New MySqlCommand
cmd.Connection = conn
cmd.CommandText = "select user,password from user"
Try
conn.Open()
Dim s As String = ""
Dim r As MySqlDataReader = cmd.ExecuteReader()
While r.Read()
s &= r(0) & r(1) & Chr(10)
End While
MsgBox(s)
Catch myerror As MySqlException
MsgBox("error: " & myerror.Message)
End Try
conn.Close()

3.2 นำข้อมูลเข้าตารางด้วย Delete ผ่านการเชื่อมต่อแบบ MySQLConnection

Imports MySql.Data.MySqlClient
Dim uindex As Integer = Val(DataGridView1.CurrentRow.Index.ToString)
Dim uid As String = DataGridView1.Rows(uindex).Cells(0).Value
Dim conn As New MySqlConnection
conn.ConnectionString = "server=127.0.0.1;" _
& "user id=admin;password=p;database=mysql"
' "user id=admin;password=p;database=mysql;charset=tis620"
Dim cmd As New MySqlCommand
cmd.Connection = conn
cmd.CommandText = "delete from user where user ='" & uid & "'"
Try
conn.Open()
cmd.ExecuteNonQuery()
Catch myerror As MySqlException
MsgBox("error: " & myerror.Message)
End Try
conn.Close()

3.3 MySQL ODBC in VB.NET

Imports System.Data.Odbc
Public Class Form1
Private Sub Form1_Load( ...
  Dim connstr As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
  "SERVER=127.0.0.1;DATABASE=mysql;UID=admin;PASSWORD=p;OPTION=3"
  Dim conn As New OdbcConnection(connstr)
  conn.Open()
  Dim cmd As New OdbcCommand
  cmd.Connection = conn
  cmd.CommandText = "select user,password from user"
  Try
    Dim s As String = ""
    Dim r As OdbcDataReader = cmd.ExecuteReader()
    While r.Read()
      s &= r(0) & r(1) & Chr(10)
    End While
    MsgBox(s)
  Catch myerror As OdbcException
    MsgBox("error: " & myerror.Message)
  End Try
  conn.Close()
End Sub
End Class

3.4 MySQL ODBC in Webservice

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.Odbc
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class Service
  Inherits System.Web.Services.WebService
  <WebMethod()> _
  Public Function listuserpassword(ByVal u As String) As String
    Dim connstr As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
    "SERVER=127.0.0.1;DATABASE=mysql;UID=admin;PASSWORD=p;OPTION=3"
    Dim conn As New OdbcConnection(connstr)
    conn.Open()
    Dim cmd As New OdbcCommand
    cmd.Connection = conn
    cmd.CommandText = "select user,password from user where user='" & u & "'"
    Dim s As String = ""
    Dim r As OdbcDataReader = cmd.ExecuteReader()
    While r.Read()
      s &= r(1) & Chr(10)
    End While
    conn.Close()
    Return s
  End Function
End Class

<%@ Import Namespace="service" %>
<%
Try
  Dim a As New Service
  Response.Write(a.listuserpassword("101"))
Catch ex As Exception
End Try
%>

4. File Stream

4.1 ใช้ FileStream Class และอ่านทีละไบท์แปลงเป็น Integer

Imports system.io
Dim fn As FileStream = New FileStream("c:\z1.txt", FileMode.Open)
Dim data As String = ""
Dim d As Integer = fn.ReadByte()
Do While (d <> -1)
data &= Chr(d)
d = fn.ReadByte()
Loop
MessageBox.Show(data)
fn.Close()

4.2 ใช้ FileStream Class และอ่านทีละไบท์ คุม error ด้วย Try .. Catch

Imports system.io
Dim fn As FileStream
Tryfn = New FileStream("c:\x1.txt", FileMode.Open)
Dim data As Integer
Dim c() As Char
c &= Chr(Asc("ก"))
Do
data = fn.ReadByte()
If (data <> -1) Then c &= Chr(data)
Loop While (data <> -1)
MessageBox.Show(c)
fn.Close() ' file size = 3 bytes
Catch ex As Exception
MsgBox("can not open")
End Try

4.3 ใช้ FileStream + StreamReader และอ่านทีละบรรทัด

Imports system.io
Try
Dim path As String = "c:\x1.txt"
If Not File.Exists(path) Then Exit Sub
Dim fn As FileStream = New FileStream(path, FileMode.Open)
Dim sr As StreamReader = New StreamReader(fn, Encoding.Default)
Do While sr.Peek() >= 0
MsgBox(sr.ReadLine()) ' show each line
Loop
fn.Seek(0, SeekOrigin.Begin)
MsgBox(sr.ReadToEnd()) ' show all in msgbox
fn.Close()
Catch ex As Exception
MsgBox(ex)
End Try

4.4 FileStream แบบ OpenOrCreate + Write Method แบบ 1

Imports system.io
Dim fn As FileStream
Tryfn =New FileStream("c:\x1.txt",FileMode.OpenOrCreate,FileAccess.Write)
Dim s(2) As Byte
s(0) = Asc("ก")
s(1) = Asc("ฮ")
fn.Write(s, 0, s.Length - 1)
fn.Close() ' file size = 2 bytes
Catch ex As Exception
MsgBox("can not open")
End Try

4.5 FileStream แบบ Create + Write Method แบบ 2

Imports system.io
Dim fw As FileStream
Dim b() As Byte = {Asc("a"), Asc("b"), Asc("c")}
Try
fw = New FileStream("c:\y.txt", FileMode.Create, FileAccess.Write)
fw.Write(b, 0, b.Length)
fw.Close()
Catch ex As Exception
MsgBox("can not open")
End Try
Me.Close() ' 3 bytes

4.6 Streamwriter แบบ encoding ภาษาไทยให้ถูกต้อง

Imports system.io
Imports system.text
Dim sw As StreamWriter 
sw = New StreamWriter("c:\z1.txt", False, Encoding.GetEncoding("windows-874"))
Dim s As String = "กขค"
sw.Write(s) ' 3 bytes
sw.Close()
sw = New StreamWriter("c:\z2.txt")
Dim i As Integer = 1234
sw.Write(i) ' 4 bytes
sw.Close()

4.7 อ่าน และเขียน image เสมือนการคัดลอก

Dim fr As FileStream = New FileStream("c:\y.jpg", FileMode.Open)
Dim fw As FileStream = New FileStream("c:\x.jpg", FileMode.Create, FileAccess.Write)
Dim i As Integer
Dim b() As Byte = {0}
For i = 0 To fr.Length - 1
b(0) = fr.ReadByte()
fw.Write(b, 0, 1)
Next
fw.Close()
fr.Close()

4.8 โหลดภาพจากอินเทอร์เน็ตมาเป็นแฟ้มลงเครื่องเรา 1 ภาพ

Imports system.io
Imports System.Net
Dim fw As FileStream = New FileStream("c:\x.jpg", FileMode.Create, FileAccess.Write)
Dim req As WebRequest = WebRequest.Create("http://www.thaiall.com/me/picme.jpg")
Dim resp As WebResponse = req.GetResponse()
Dim webstream As Stream = resp.GetResponseStream()
Dim data(1000) As Byte
Dim dataLen As Integer
Do
dataLen = webstream.Read(data, 0, 1000)
If dataLen = 0 Then Exit Do
fw.Write(data, 0, dataLen)
Loop
resp.Close()
fw.Close()
WebBrowser1.Url = New Uri("c:\x.jpg")

4.9 โหลดภาพจากอินเทอร์เน็ตมาหลายภาพลงเครื่องพร้อมกัน

Imports system.io
Imports System.Net
Dim fw As FileStream
Dim req As WebRequest
Dim resp As WebResponse
Dim webstream As Stream
Dim data(10000) As Byte
Dim dataLen, i As Integer
For i = 101 To 110
fw = New FileStream("c:\x" & i & ".jpg", FileMode.Create, FileAccess.Write)
req = WebRequest.Create("http://www.yonok.ac.th/wallpic/wyn" & i & "t.jpg")
resp = req.GetResponse()
webstream = resp.GetResponseStream()
Do
dataLen = webstream.Read(data, 0, 10000)
If dataLen = 0 Then Exit Do
fw.Write(data, 0, dataLen)
Loop
Button1.Text = i
resp.Close()
fw.Close()
Refresh()
Next

5. XML (eXtensible Markup Language)

5.1 ตัวอย่างแฟ้ม .XML

+ http://www.thaiall.com/xml/index.html

+ http://truehits.net/xml/html/

+ http://www.codeproject.com/vb/net/parsefilecode.asp
+ http://thaiall.com/xml/family.xml
<?xml version="1.0" encoding="UTF-8"?>
<family>
<name gender="Male">
<firstname>Tom</firstname>
<lastname>Smith</lastname>
</name>
<name gender="Female">
<firstname>Dale</firstname>
<lastname>Smith</lastname>
</name>
<name gender="Male">
<firstname>Burin</firstname>
<lastname>Rujjanapan</lastname>
</name>
</family>

5.2 อ่านข้อมูลจาก XML แบบ XmlTextReader และ .read()

Imports System.IO
Imports System.Xml
Dim m_xmlr As XmlTextReader
m_xmlr = New XmlTextReader("C:\family.xml")
' XmlTextReader("http://thaiall.com/xml/family.xml")
m_xmlr.WhitespaceHandling = WhitespaceHandling.None
m_xmlr.Read() : MsgBox(m_xmlr.GetAttribute("version"))
m_xmlr.Read() ' family
While Not m_xmlr.EOF
m_xmlr.Read() ' name
If Not m_xmlr.IsStartElement() Then Exit While
Dim g = m_xmlr.GetAttribute("gender")
m_xmlr.Read() ' get all fields
Dim fn = m_xmlr.ReadElementString("firstname")
Dim ln = m_xmlr.ReadElementString("lastname")
MsgBox("Gender:"& g&" Name:"& fn&" LastName:"& ln)
End While
m_xmlr.Close()

5.3 อ่านข้อมูลจาก XML แบบ XmlDocument และ .selectnodes()

Try
Dim m_xmld As XmlDocument = New XmlDocument() 
m_xmld.Load("C:\family.xml")
' m_xmld.Load("http://thaiall.com/xml/family.xml")
Dim m_nodelist As XmlNodeList = m_xmld.SelectNodes("/family/name")
Dim m_node As XmlNode
For Each m_node In m_nodelist
Dim g = m_node.Attributes.GetNamedItem("gender").Value
Dim fn = m_node.ChildNodes.Item(0).InnerText
Dim ln = m_node.ChildNodes.Item(1).InnerText
MsgBox("Sex:" & g& " Name:" & fn& " LastName:" & ln)
Next
Catch errorVariable As Exception
Console.Write(errorVariable.ToString())
End Try

5.4 อ่านข้อมูลจาก XML แบบ ReadXml และ item

http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
Imports System.IO
Imports System.Xml
Dim DS As New DataSet()
DS.ReadXml("C:\family.xml")
' DS.ReadXml("http://thaiall.com/xml/family.xml")
Dim I As Integer
For I = 0 To DS.Tables(0).Rows.Count - 1
  DS.Tables(0).Rows(I).Item("gender") = _
  UCase(DS.Tables(0).Rows(I).Item("gender"))
Next
For I = 0 To DS.Tables(0).Rows.Count - 1
  MsgBox(DS.Tables(0).Rows(I).Item("gender"))
Next
DS.WriteXml("C:\family.xml")

5.5 อ่านด้วย ReadXML ส่งเข้า Dataset แล้ววน loop 3 ระดับ

Imports System.IO
Imports System.Xml
Imports Microsoft.VisualBasic.ControlChars
Dim DS As New DataSet()
DS.ReadXml("http://truehits.net/xml/education.xml")
Dim I, j, k As Integer
Dim s As String = ""
For I = 0 To DS.Tables.Count - 1
  For j = 0 To DS.Tables(I).Rows.Count - 1
    For k = 0 To DS.Tables(I).Columns.Count - 1
       s &= DS.Tables(I).Rows(j).Item(k) & " "
    Next
    s &= crlf
  Next
Next
MsgBox(s)
' Msgbox(DS.Tables(1).Rows(1).Item("url"))

5.6 ตัวอย่างส่วนหนึ่งของ http://truehits.net/xml/education.xml

<?xml version="1.0" encoding="windows-874"?>
<!DOCTYPE data [
<!ELEMENT data (member*)>
<!ATTLIST data
date CDATA #IMPLIED
category CDATA #IMPLIED>
<!ELEMENT member (url, website, sort, sortallmember, 
uniqueip, uniquesession, pageviews,sortcategory )>
<!ELEMENT url (#PCDATA)>
<!ELEMENT website (#PCDATA)>
<!ELEMENT sort (#PCDATA)>
<!ELEMENT sortallmember (#PCDATA)>
<!ELEMENT uniqueip (#PCDATA)>
<!ELEMENT uniquesession (#PCDATA)>
<!ELEMENT pageviews (#PCDATA)>
<!ELEMENT sortcategory (#PCDATA)>]>
<data date="22/01/2008" category="education">
<member>
<url>www.vcharkarn.com</url>
<website>วิชาการ.คอม</website>
<sort>47</sort>
<sortallmember>10062</sortallmember>
<uniqueip>37195</uniqueip>
<visitor>55461</visitor>
<uniquesession>68010</uniquesession>
<pageviews>159442</pageviews>
<sortcategory>1</sortcategory>
</member>
<member>
<url>www.tlcthai.com</url>
<website>Technology Learning Center - TL Center</website>
<sort>71</sort>
<sortallmember>10062</sortallmember>
<uniqueip>26313</uniqueip>
<visitor>36100</visitor>
<uniquesession>49719</uniquesession>
<pageviews>172757</pageviews>
<sortcategory>2</sortcategory>
</member>
</data>

6. RSS (Really Simple Syndication)

From :

+ http://www.codersource.net/asp_net_rss_feeds.aspx (RssFeeds.Zip)

+ http://www.thaiall.com/rss

+ http://truehits.net/xml/html/ [xml,rss]

RSS stands for (Really Simple Syndication : รวมกลุ่มง่ายพร้อมใช้). Basically RSS feeds are xml files which are provided by many websites so you can view their contents on your own websites rather than browsing their site. Suppose you are a movie lover and you want to get the list of top 5 movies from 10 websites. One way will be to visit all 10 websites and see the top 5 list. This method though is used in general by lot of people but its quite tiring method. It will take you 10-15 minutes to browse all the websites and see the top 5 list of movies. One easy way will be if those movie websites provides RSS feeds to be used by the users. If they provide RSS feeds you can embed them in your page and now you don't have to browse all the websites since the information is available on a single page. Hence, this saves you a time and a lot of browsing.

Most of the Blogs websites provide RSS feeds so you can embed your or someone's else latest entries of blog on your website. In this article we will see how we can embed RSS feeds to our webform using Asp.net.

Code Sample
Public Class Form1
    Dim i As Integer = 0
    Dim DS As New DataSet()
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles MyBase.Load
        DS.ReadXml("http://www.arip.co.th/rss/rss_news.xml")
        DataGridView1.DataSource = DS.Tables(i)
    End Sub
    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, _
        ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
        Handles DataGridView1.CellContentClick
        i = i + 1
        If DS.Tables.Count = i Then i = 0
        DataGridView1.DataSource = DS.Tables(i) ' RSS = 4 tables
    End Sub
End Class

<?xml version="1.0" encoding="windows-874" ?>
<rss version="2.0">
<channel>
<title>RSS by Truehits.net</title>
<link>http://truehits.net</link>
<description>Truhits.net Thailand Web Directory,Web Ranking</description>
<language>th-TH</language>
<copyright>Copyright 2006 truehits.net</copyright>
<lastBuildDate>Sun,03 Feb 2008</lastBuildDate>
<item>
<title>อันดับ 1: www.tlcthai.com (หมวดหลัก การศึกษา)</title>
<description>มีค่า UIP:38,355 , USS:70,709 , PV:288,698</description>
<link>http://education.truehits.net/index.php</link>
<pubDate>Sun,03 Feb 2008</pubDate>
</item>
<item>
<title>อันดับ 2: www.vcharkarn.com (หมวดหลัก การศึกษา)</title>
<description>มีค่า UIP:35,313 , USS:56,004 , PV:134,502</description>
<link>http://education.truehits.net/index.php</link>
<pubDate>Sun,03 Feb 2008</pubDate>
</item>
<item>
<title>อันดับ 3: learners.in.th (หมวดหลัก การศึกษา)</title>
<description>มีค่า UIP:30,256 , USS:45,735 , PV:97,665</description>
<link>http://education.truehits.net/index.php</link>
<pubDate>Sun,03 Feb 2008</pubDate>
</item>
</channel>
</rss>

เว็บไซต์ของไทยบริการ RSS Feed

http://www.arip.co.th/rss/rss_news.xml ***
http://www.bangkokbiznews.com/rss/index.xml
http://www.bcoms.net/rss.php?b=tipcomputer
http://gotoknow.org/blog/tutorial/26125
http://norsorpor.com/chooseRSS.php
http://rssthai.com
http://www.thaibusinessnews.com/rss.aspx
http://tourismthailand.org/rss/index.php
http://www.tat.or.th/webservice.asp
http://truehits.net/xml/html/