Membuat aplikasi database menggunakan SQLServer dapat
dilakukan memakai Driver OLEDB (System.Data.OleDb) maupun SQLServer Native
(System.Data.SqlClient). Jika menggunakan OleDb cara pemrogramannya mirip
dengan OleDb saat membuat aplikasi database menggunakan Access, hanya saja
ConnectionString-nya diganti sebagai berikut :
cn = new OleDbConnection("Provider=SQLOLEDB;Data
Source=localhost;Persist Security Info=True;Initial Catalog=world;Integrated
Security=True");
atau
cn = new
OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Persist Security
Info=True;User ID=sa;Initial Catalog=world;Password=");
Apabila menggunakan SQLClient, berikut program selengkapnya :
using
System;
using
System.Collections.Generic;
using
System.ComponentModel;
using
System.Data;
using
System.Drawing;
using
System.Text;
using
System.Windows.Forms;
using
System.Data.SqlClient;
using
System.IO;
namespace
AllDB
{
public partial class FSQLServer : Form
{
public SqlConnection cn;
public DataSet ds;
public DataTable tb;
public FSQLServer()
{
InitializeComponent();
}
private void FSQLServer_Load(object
sender, EventArgs e)
{
int i;
//cn = new SqlConnection("Data
Source=localhost;Initial Catalog=world;Integrated Security=True");
cn = new SqlConnection("Data
Source=localhost;Initial Catalog=world;Persist Security Info=True;User
ID=sa;Password=");
cn.Open();
SqlCommand cm = new
SqlCommand("SELECT name, address, phone FROM contacts", cn);
SqlDataReader rd;
rd = cm.ExecuteReader();
ds = new DataSet();
tb = new DataTable();
tb.TableName =
"contacts";
ds.Tables.Add(tb);
ds.Load(rd, LoadOption.Upsert, tb);
for (i = 0; i <
tb.Columns.Count; i++)
{
tb.Columns[i].MaxLength = -1;
}
rd.Close();
cn.Close();
bs.DataSource = ds;
bs.DataMember =
"contacts";
gContacts.DataSource = bs;
}
private void bs_CurrentChanged(object
sender, EventArgs e)
{
if ((bs.Count > 0) &&
(bs.DataMember == "contacts"))
{
tName.Text =
((DataRowView)bs[bs.Position])["name"].ToString();
tAddress.Text = ((DataRowView)bs[bs.Position])["address"].ToString();
tPhone.Text =
((DataRowView)bs[bs.Position])["phone"].ToString();
}
}
private void bFirst_Click(object
sender, EventArgs e)
{
bs.Position = 0;
}
private void bPrev_Click(object sender,
EventArgs e)
{
bs.Position = bs.Position - 1;
}
private void bNext_Click(object sender,
EventArgs e)
{
bs.Position = bs.Position + 1;
}
private void bLast_Click(object sender,
EventArgs e)
{
bs.Position = bs.Count - 1;
}
private void bAdd_Click(object sender,
EventArgs e)
{
bs.AddNew();
}
private void bEdit_Click(object sender,
EventArgs e)
{
//
}
private void bSave_Click(object sender,
EventArgs e)
{
SqlCommand cm = new SqlCommand();
cn.Open();
if (((DataRowView)bs[bs.Position])["name"].ToString()
== "")
{
cm.Dispose();
cm = new
SqlCommand("INSERT INTO contacts (name, address, phone) VALUES (@name,
@address, @phone)", cn);
}
else
{
cm.Dispose();
cm = new
SqlCommand("UPDATE contacts SET name = @name, address = @address, phone =
@phone WHERE name = @Original_name", cn);
}
cm.Parameters.Add("@name", SqlDbType.VarChar, 0,
"").Value = tName.Text;
cm.Parameters.Add("@address", SqlDbType.VarChar, 0,
"").Value = tAddress.Text;
cm.Parameters.Add("@phone", SqlDbType.VarChar, 0,
"").Value = tPhone.Text;
if
(((DataRowView)bs[bs.Position])["name"].ToString() != "")
{
cm.Parameters.Add("@Original_name", SqlDbType.VarChar, 0,
"").Value =
((DataRowView)bs[bs.Position])["name"].ToString();
}
cm.ExecuteNonQuery();
cn.Close();
((DataRowView)bs[bs.Position])["name"]
= tName.Text;
((DataRowView)bs[bs.Position])["address"] = tAddress.Text;
((DataRowView)bs[bs.Position])["phone"] = tPhone.Text;
bs.EndEdit();
}
private void bCancel_Click(object sender,
EventArgs e)
{
bs.CancelEdit();
}
private void bDelete_Click(object
sender, EventArgs e)
{
cn.Open();
SqlCommand cm = new
SqlCommand("DELETE FROM contacts WHERE name = @Original_name", cn);
cm.Parameters.Add("@Original_name", SqlDbType.VarChar, 0,
"").Value =
((DataRowView)bs[bs.Position])["name"].ToString();
cm.ExecuteNonQuery();
cn.Close();
bs.RemoveAt(bs.Position);
}
private void bRefresh_Click(object sender,
EventArgs e)
{
int i;
gContacts.DataSource = null;
bs.DataSource = null;
ds.Dispose();
tb.Dispose();
cn.Open();
SqlCommand cm = new
SqlCommand("SELECT name, address, phone FROM contacts WHERE name LIKE
'%" + tFind.Text.Trim() + "%'", cn);
SqlDataReader rd;
rd = cm.ExecuteReader();
ds = new DataSet();
tb = new DataTable();
tb.TableName =
"contacts";
ds.Tables.Add(tb);
ds.Load(rd, LoadOption.Upsert, tb);
for (i = 0; i <
tb.Columns.Count; i++)
{
tb.Columns[i].MaxLength = -1;
}
rd.Close();
cn.Close();
bs.DataSource = ds;
bs.DataMember =
"contacts";
gContacts.DataSource = bs;
}
}
}
Jika SQLServer yang digunakan
adalah versi SQLExpress, maka databasenya berupa sebuah file *.MDF.
ConnectionString-nya harus diubah menjadi seperti di bawah ini :
cn = new
SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=" +
Path.GetDirectoryName(Application.ExecutablePath) + "\\world.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=True");
