Download C# code | Download VB code | Download ServerConnect C# | Download ServerConnect VB
This is it guys, my first LINQ code. Huh? I bet you're all gonna say, where have you been? Heheh, iya yah saya kemana aja yah? Ada kok, disini. Anyway, kemarin saya habis nonton video My First LINQ Queries by Beth Massi. Dia kasih contoh gimana cara nge-query isi file yang ada di komputer pake LINQ. Wow, LINQ itu powerful banget yah, saya jadi terinspirasi (halah terinspirasi, maksud lu?) buat nge-query objek2 yang ada di SQL Server pake LINQ. Tenang2, saya bikin nya gak yang susah2 kok. Cuman query database yang ada di satu server ajah, dan bisa dipilih apakah mau ambil system database atau user database.
Bahan2 yang dibutuhkan:
Microsoft Visual Studio .NET 2008
Microsoft SQL Server SDK
Microsoft SQL Server 2005 Express
Yang harus kita kerjakan:
Buat satu windows forms application baru dan beri nama LINQSMO
Tambahkan project ServerConnect ke dalam solution
Add reference ke Microsoft.SqlServer.Smo, Microsoft.SqlServer.ConnectionInfo, dan ExceptionMessageBox untuk project LINQSMO
Add reference ke project ServerConnect
Pada Form1 tambahkan Button dengan nama btnSystemTable, location 0,0, width 150 px dan beri text System Table
Tambahkan Button dengan nama btnUserDefineTable, location 156,0, width 150 px dan beri text User Define Table
Tambahkan DataGridView dengan nama dgTable, location 0,29, size 306, 232
Buka code form1, tambahkan import statement (untuk VB) atau using statement (untuk C#) Microsoft.SqlServer.Management.Smo, Microsoft.SqlServer.Management.Common, Microsoft.SqlServer.MessageBox dan Microsoft.Samples.SqlServer.
C# |
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.MessageBox;
using Microsoft.Samples.SqlServer; |
VB |
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.MessageBox
Imports Microsoft.Samples.SqlServer |
Tambahkan variable _server dengan tipe data Server di Form1
C# |
private Server _server; |
VB |
Private _server As Server |
Tambahkan event handler pada saat form load untuk memanggil ServerConnect
C# |
private void Form1_Load(object sender, EventArgs e)
{
ServerConnection ServerConn = new ServerConnection();
ServerConnect scForm;
DialogResult dr;
this.Show();
ServerConn = new ServerConnection();
scForm = new ServerConnect(ServerConn);
dr = scForm.ShowDialog(this);
if ((dr == DialogResult.OK) &&
(ServerConn.SqlConnectionObject.State == ConnectionState.Open))
{
_server = new Server(ServerConn);
if (_server != null)
{
this.Text = _server.Name;
}
}
else
{
this.Close();
}
} |
VB |
Private
Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
MyBase.Load
Dim ServerConn As
New ServerConnection()
Dim scForm As ServerConnect
Dim dr As DialogResult
Me.Show()
ServerConn = New ServerConnection()
scForm = New ServerConnect(ServerConn)
dr = scForm.ShowDialog(Me)
If dr = Windows.Forms.DialogResult.OK AndAlso ServerConn.SqlConnectionObject.State = ConnectionState.Open Then
_server = New Server(ServerConn)
If
Not (_server Is
Nothing) Then
Me.Text = _server.Name
End
If
Else
Me.Close()
End
If
End
Sub |
Buat method untuk nge-query database yang ada di server (ini dia nih LINQ-nya), beri parameter Boolean isSystemObject untuk memfilter system database dan user database
C# |
private
void RetrieveTable(bool isSystemObject)
{
try
{
var databases = from
Database database in _server.Databases
where database.IsSystemObject == isSystemObject
select
new {database.Name, database.Owner};
dgTable.DataSource = databases.ToList();
}
catch (Exception ex)
{
ExceptionMessageBox exMessageBox = new
ExceptionMessageBox(ex);
exMessageBox.Show(this);
}
}
|
VB |
Private
Sub RetrieveTable(ByVal isSystemObject As
Boolean)
Try
Dim databases = From database As Database In _server.Databases _
Where database.IsSystemObject = isSystemObject _
Select database.Name, database.Owner
dgTable.DataSource = databases.ToList
Catch ex As Exception
Dim exMessageBox As
New ExceptionMessageBox(ex)
exMessageBox.Show(Me)
End
Try
End
Sub |
Tambahkan event handler OnClick untuk btnSystemTable dan panggil method RetrieveTable dengan parameter true. Untuk btnUserTable parameter nya false
C# |
private void btnSystemTable_Click(object sender, EventArgs e)
{
RetrieveTable(true);
}
private void button1_Click(object sender, EventArgs e)
{
RetrieveTable(false);
} |
VB |
Private Sub btnSystemTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSystemTable.Click
RetrieveTable(True)
End Sub
Private Sub btnUserTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUserTable.Click
RetrieveTable(False)
End Sub |
Selesai sudah, aplikasi kita siap untuk dijalankan. I LINQ it.