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
Try
    fn = 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
Try
    fn =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
  1. http://www.arip.co.th/rss/rss_news.xml ***
  2. http://www.bangkokbiznews.com/rss/index.xml
  3. http://www.bcoms.net/rss.php?b=tipcomputer
  4. http://gotoknow.org/blog/tutorial/26125
  5. http://norsorpor.com/chooseRSS.php
  6. http://rssthai.com
  7. http://www.thaibusinessnews.com/rss.aspx
  8. http://tourismthailand.org/rss/index.php
  9. http://www.tat.or.th/webservice.asp
  10. http://truehits.net/xml/html/

แนะนำเว็บใหม่ : ผลการจัดอันดับ
รักลำปาง : thcity.com : korattown.com : topsiam.com : มหาวิทยาลัยโยนก
ศูนย์สอบ : รวมบทความ : ไอทีในชีวิตประจำวัน : ดาวน์โหลด : yourname@thaiall.com
ติดต่อ ทีมงาน ชาวลำปาง มีฝันเพื่อการศึกษา Tel.08-1992-7223