VB.NET でDBと接続する方法

System.Data, System.Data.SqlClient

結果データの取得

Dim connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=pr_cs_sampleDB;Integrated Security=True"
Dim sql As String = "SELECT TITLE,TYPE,PRICE FROM books WHERE TYPE = @type"

Using conn As New SqlConnection(connectionString)
    conn.Open()
    Dim cmd As New SqlCommand(sql, conn)
    cmd.Parameters.AddWithValue("@type", "科学")
    Dim sdr As SqlDataReader = cmd.ExecuteReader()

    Console.WriteLine("TITLE" + vbTab + "TYPE" + vbTab + "PRICE")
    While sdr.Read
        Console.WriteLine("{0}" + vbTab + "{1}" + vbTab + "{2:C}",
            sdr("TITLE"), sdr("TYPE"), sdr("PRICE"))
    End While
End Using

データセットに結果を流し込む

Dim connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=pr_cs_sampleDB;Integrated Security=True"
Dim sql As String = "SELECT TITLE,TYPE,PRICE FROM books WHERE TYPE = @type"
Dim dataset As New DataSet

Using conn As New SqlConnection(connectionString)
    conn.Open()

    Dim da As New SqlDataAdapter(sql, conn) 'データアダプタの作成
    da.SelectCommand.Parameters.AddWithValue("@type", "科学") 'バインド変数の埋め込み
    Dim count As Integer = da.Fill(dataset) 'データセットに結果の表を流し込む

    'データセットの内容を表示する
    For Each table As DataTable In dataset.Tables
        For Each row As DataRow In table.Rows
            For Each column As DataColumn In table.Columns
                Console.Write(column.ColumnName)
                If Not row.IsNull(column) Then
                    Console.WriteLine(vbTab + "{0}", row(column))
                End If
            Next
            Console.WriteLine("----------")
        Next
    Next
End Using

更新系SQLの発行

Dim connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=pr_cs_sampleDB;Integrated Security=True"
Dim sql As String = "INSERT INTO books(BOOK_ID,TITLE,TYPE,PRICE) VALUES(@id, @title, @type, @price)"

Using conn As New SqlConnection(connectionString)
    conn.Open()
    Dim cmd As New SqlCommand(sql, conn)
    cmd.Parameters.AddWithValue("@id", "100001")
    cmd.Parameters.AddWithValue("@title", "5次元の図形")
    cmd.Parameters.AddWithValue("@type", "科学")
    cmd.Parameters.AddWithValue("@price", 3100)
    Dim count As Integer = cmd.ExecuteNonQuery
    Console.WriteLine("{0}行追加しました。", count) ' => 1行追加しました。
End Using

単一の値を取得するSQL

Dim connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=pr_cs_sampleDB;Integrated Security=True"
Dim sql As String = "SELECT COUNT(*) FROM books WHERE TYPE = @type"

Using conn As New SqlConnection(connectionString)
    conn.Open()
    Dim cmd As New SqlCommand(sql, conn)
    cmd.Parameters.AddWithValue("@type", "科学")
    Dim count = CInt(cmd.ExecuteScalar())
    Console.WriteLine("登録されている科学の本の数は {0} です。", count)
End Using

おまけ

使用したデータベース

USE MASTER
IF EXISTS (SELECT * FROM SYSDATABASES WHERE NAME='pr_cs_sampleDB') 
    DROP DATABASE pr_cs_sampleDB
CREATE DATABASE pr_cs_sampleDB
GO
USE pr_cs_sampleDB
CREATE TABLE books
(
  BOOK_ID VARCHAR(6) PRIMARY KEY,
  TITLE VARCHAR(80) NOT NULL,
  TYPE CHAR(12) NOT NULL,
  PRICE MONEY NULL,
  PUBDATE DATETIME NOT NULL DEFAULT (GETDATE())
)
CREATE TABLE authors
(
  AUTHOR_ID VARCHAR(2) PRIMARY KEY,
  NAME VARCHAR(80) NOT NULL,
)
CREATE TABLE bookauthor
(
  AUTHOR_ID VARCHAR(2) REFERENCES authors(AUTHOR_ID),
  BOOK_ID VARCHAR(6) REFERENCES books(BOOK_ID)
  CONSTRAINT PKBA PRIMARY KEY CLUSTERED(AUTHOR_ID, BOOK_ID)
)
INSERT INTO books VALUES ('000001','11次元時空理論','科学',3200,'2011-1-20');
INSERT INTO books VALUES ('000002','異次元生物学','科学',4500,'2011-7-15');
INSERT INTO books VALUES ('000003','吾輩はシュレディンガーの猫である。','小説',700,'2011-8-10');
INSERT INTO authors VALUES ('01','ドライシュタイン');
INSERT INTO authors VALUES ('02','名もなき猫');
INSERT INTO bookauthor VALUES ('01','000001');
INSERT INTO bookauthor VALUES ('01','000002');
INSERT INTO bookauthor VALUES ('02','000002');
INSERT INTO bookauthor VALUES ('02','000003');

SQL Server にデータを追加する方法

sqlcmd -S .\sqlexpress -i .\createSampleDB.sql