Store Procedure

Stored procedure adalah satu atau lebih perintah-perintah SQL yang disimpan dalam sebuah DBMS. Pemrograman SQL ini sering disebut dengan T-SQL (Transact SQL). Stored procedure ini mirip dengan sebuah fungsi yang umum dalam pemrograman. Stored procedure bisa menerima inputan dari parameter dan dapat juga bisa mengembalikan data lewat result sets maupun output parameternya. Adapun keuntungan dari stored procedure ini adalah :
• Mudah untuk dikelola. Contohnya kita bisa mengoptimalkan serta merubah perintah dalam stored procedure tanpa mengkompilasi ulang aplikasi yang menggunakannya.
• Meningkatkan performa DBMS. Karena stored procedure merupakan kumpulan dari banyak perintah, kita dapat menyelesaikan banyak perintah hanya dalam satu koneksi ke DBMS, dan jika DBMS terletak pada komputer yang terpisah stored procedure akan mengurangi total waktu untuk menyelesaikan tugas yang kompleks secara drastis.
• Meningkatkan keamanan DBMS. Contohnya kita bisa mengijinkan Windows account untuk menjalankan perintah-perintah dalam aplikasi yang dibuat untuk menggunakan beberapa stored procedure tapi Windows account tersebut tidak boleh mengakses tabel secara langsung.

Ex:
CREATE PROCEDURE TransferDana
@rekeningAsal char(10),
@rekeningTujuan char(10),
@jumlahDana money
AS
BEGIN TRANSACTION
/* Kurangi Saldo dari rekening asal */
UPDATE DataNasabah
SET Saldo = Saldo - @jumlahDana
WHERE no_rekening = @rekeningAsal
/* Tambah Saldo pada rekening tujuan */
UPDATE DataNasabah
SET Saldo = Saldo + @jumlahDana
WHERE no_rekening = @rekeningTujuan
IF (@@ERROR > 0)
ROLLBACK
ELSE
COMMIT
GO

Menggunakan select case procedure:

CREATE PROCEDURE getNumber
@OrderNo char(6)OUTPUT
as
SELECT @OrderNo=Max(cOrderNo) FROM Orders
SELECT @OrderNo=
CASE
WHEN @OrderNo >=0 and @OrderNo < 9
Then '00000'+Convert(char,@OrderNo+1)
WHEN @OrderNo >=9 and @OrderNo < 99
Then '0000'+Convert(char,@OrderNo+1)
WHEN @OrderNo >=99 and @OrderNo < 999
Then '000'+Convert(char,@OrderNo+1)
WHEN @OrderNo >=999 and @OrderNo < 9999
Then '00'+Convert(char,@OrderNo+1)
WHEN @OrderNo >=9999 and @OrderNo < 99999
Then '0'+Convert(char,@OrderNo+1)
WHEN @OrderNo >=99999
Then Convert(char,@OrderNo+1)
END
RETURN

Export Data from Grid to Excel File Using DataSet

Dim loSavefd As New SaveFileDialog
Dim loResult As New DialogResult
Dim lsFilePath As String = String.Empty
Dim lsAssessment As String = String.Empty
Dim lsFileName As String = String.Empty
Dim lsCharacter As String = "/\*:?<>~|'"""
Dim liData As Integer
Dim lbOpen As Boolean = False
Dim liMergeAss, liMergeAss2 As Integer
Dim liCount, liCount2 As Integer
Dim loXlApp As New Excel.Application
Dim loXlWorkBook As Excel.Workbook = loXlApp.Workbooks.Add
Dim loXlWorkSheet As Excel.Worksheet = loXlWorkBook.ActiveSheet()

Try
liMergeAss = 3
liMergeAss2 = 3
liCount = 0
liCount2 = 0

Dim frmMsgBx As New Form
Dim lblMsgText As New System.Windows.Forms.Label
Dim btnMsgOpen, btnMsgSave, btnMsgCancel As New System.Windows.Forms.Button

With frmMsgBx
.StartPosition = FormStartPosition.CenterScreen
.MinimizeBox = False
.MaximizeBox = False
.Width = 380
.Height = 150
.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow
.ShowIcon = False
.ShowInTaskbar = False
.Text = "Exporting to Excel"
End With

With lblMsgText
.Parent = frmMsgBx
.Left = 30
.Top = 30
.Width = 300
.Height = 13
.Text = "Would you like to open the file or save it to your computer?"
.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
End With

With btnMsgOpen
.Parent = frmMsgBx
.Left = 50
.Top = 80
.Width = 52
.Height = 23
.Text = "Open"
.DialogResult = System.Windows.Forms.DialogResult.OK
End With

With btnMsgSave
.Parent = frmMsgBx
.Left = 110
.Top = 80
.Width = 73
.Height = 23
.Text = "Save"
.DialogResult = System.Windows.Forms.DialogResult.Yes
End With

With btnMsgCancel
.Parent = frmMsgBx
.Left = 250
.Top = 80
.Width = 75
.Height = 23
.Text = "Cancel"
.DialogResult = System.Windows.Forms.DialogResult.Cancel
End With

frmMsgBx.ShowDialog()
lsFileName = txtCompItemId.Text.Trim.Replace("&", "and")
If lsFileName.Substring(0, 1).Equals(".") Then
lsFileName = "Dot " & lsFileName.Remove(0, 1)
End If

For liCtr As Integer = 1 To Len(lsCharacter)
lsFileName = Replace(lsFileName, Mid(lsCharacter, liCtr, 1), " ")
Next liCtr

If frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.Yes Then
With loSavefd
.Filter = "Excel Files|*.xls"
.Title = "Download to Excel File"
.FileName = lsFileName
.ValidateNames = True
loResult = .ShowDialog()
lsFilePath = loSavefd.FileName
End With

loSavefd.Dispose()
End If

If loResult = System.Windows.Forms.DialogResult.OK OrElse frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.OK OrElse frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.Yes Then
If lsFileName.Length > 30 Then
loXlWorkSheet.Name = lsFileName.Substring(0, 30)
Else
loXlWorkSheet.Name = lsFileName
End If

'add the title
With loXlWorkSheet.Cells(1, 1)
.Font.Name = "Arial"
.Font.Bold = True
.Font.ColorIndex = 2
.Font.Size = 18
.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
loXlWorkSheet.Range(loXlWorkSheet.Cells(1, 1), loXlWorkSheet.Cells(1, 4)).Merge()
loXlWorkSheet.Cells(1, 1).Value = txtCompItemId.Text.Trim
loXlWorkSheet.Range(loXlWorkSheet.Cells(1, 1), loXlWorkSheet.Cells(1, 1)).Interior.ColorIndex = 37

'add the definition competency item
With loXlWorkSheet.Cells(2, 1)
.Font.Name = "Arial"
.Font.Bold = True
.VerticalAlignment = XlVAlign.xlVAlignJustify
loXlWorkSheet.Cells(2, 1).Value = "Definition"
End With

With loXlWorkSheet.Cells(2, 2)
.RowHeight = 80
.Font.Italic = True
.Font.Name = "Arial"
.WrapText = True
.VerticalAlignment = XlVAlign.xlVAlignTop
loXlWorkSheet.Cells(2, 2).Value = txtDefinition.Text.Trim
End With
loXlWorkSheet.Range(loXlWorkSheet.Cells(2, 2), loXlWorkSheet.Cells(2, 4)).Merge()

SwapDataSet()

'add the headers table column
Dim i As Integer = moPniDataSetSwap.Tables(0).Columns.Count - 1
For c As Integer = 0 To i
With loXlWorkSheet.Cells(3, c + 1)
.Borders.Weight = XlBorderWeight.xlMedium
.Font.Bold = True
.Font.Size = 12
.WrapText = True
.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
.VerticalAlignment = XlVAlign.xlVAlignTop
loXlWorkSheet.Cells(3, c + 1).Value = moPniDataSetSwap.Tables(0).Columns(c).ColumnName
End With
Next
loXlWorkSheet.Range(loXlWorkSheet.Cells(3, 1), loXlWorkSheet.Cells(3, i + 1)).Interior.ColorIndex = 34

'add the values table
liData = moPniDataSetSwap.Tables(0).Rows.Count - 1

For r As Integer = 0 To moPniDataSetSwap.Tables(0).Rows.Count - 1
'column 1
With loXlWorkSheet.Cells(r + 4, 1)
.VerticalAlignment = XlVAlign.xlVAlignTop
.ColumnWidth = 15
.WrapText = True
.Font.Bold = True
.Borders.Weight = XlBorderWeight.xlThin
If moPniDataSetSwap.Tables(0).Rows(liData)(0).ToString.Trim.Equals("") Then
liCount = liCount + 1
lsAssessment = moPniDataSetSwap.Tables(0).Rows(liData)(0).ToString.Trim
loXlWorkSheet.Range(loXlWorkSheet.Cells(liMergeAss, 1), loXlWorkSheet.Cells(liMergeAss + liCount, 1)).Merge()
Else
liMergeAss = liMergeAss + liCount + 1
liCount = 0
lsAssessment = moPniDataSetSwap.Tables(0).Rows(liData)(0).ToString.Trim
loXlWorkSheet.Cells(r + 4, 1).Value = lsAssessment
End If
End With

'column 2
With loXlWorkSheet.Cells(r + 4, 2)
.VerticalAlignment = XlVAlign.xlVAlignTop
.ColumnWidth = 40
.WrapText = True
.Borders.Weight = XlBorderWeight.xlThin
If moPniDataSetSwap.Tables(0).Rows(liData)(1).ToString.Trim.Equals("") AndAlso lsAssessment.Equals("") Then
liCount2 = liCount2 + 1
loXlWorkSheet.Range(loXlWorkSheet.Cells(liMergeAss2, 2), loXlWorkSheet.Cells(liMergeAss2 + liCount2, 2)).Merge()
Else
liMergeAss2 = liMergeAss2 + liCount2 + 1
liCount2 = 0
loXlWorkSheet.Cells(r + 4, 2).Value = moPniDataSetSwap.Tables(0).Rows(liData)(1).ToString.Replace(";", "").Trim
End If
End With

'column 3,4
For c As Integer = 2 To i
With loXlWorkSheet.Cells(r + 4, c + 1)
.VerticalAlignment = XlVAlign.xlVAlignTop
.ColumnWidth = 40
.WrapText = True
.Borders.Weight = XlBorderWeight.xlThin
loXlWorkSheet.Cells(r + 4, c + 1).Value = moPniDataSetSwap.Tables(0).Rows(liData)(c).ToString.Replace(";", "").Trim
End With
Next
liData -= 1
Next
liCount = loXlWorkBook.Sheets.Count

For no As Integer = liCount To 2 Step -1
loXlWorkSheet = loXlWorkBook.Sheets.Item(no)
loXlWorkSheet.Delete()
Next

If frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.OK Then
lbOpen = True
loXlApp.Visible = True
loXlWorkBook.Activate()
ElseIf loResult = System.Windows.Forms.DialogResult.OK Then
loXlApp.DisplayAlerts = False
loXlWorkBook.SaveAs(lsFilePath)
loXlWorkBook.Close(True)
loXlApp.DisplayAlerts = True
loXlApp.Quit()
MsgBox("Download Complete", MsgBoxStyle.Information, "Information")
End If
frmMsgBx.Dispose()
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Information")
Finally
moPniDataSetSwap.Clear()
Runtime.InteropServices.Marshal.ReleaseComObject(loXlApp)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkBook)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkSheet)
loXlApp = Nothing
loXlWorkBook = Nothing
loXlWorkSheet = Nothing
End Try

Import Excel File to Grid Using DataSet

untuk mengambil file excel kita menggunakan file dialog dan dialog result.

Dim fd As New OpenFileDialog
Dim result As New DialogResult
Dim loXlApp As New Excel.ApplicationClass
Dim loXlWorkBook As New Excel.Worksheet
Dim loXlWorkSheet As Excel.Workbook = Nothing

Try
With fd
.Filter = "Excel Files|*.xls"
.Title = "Browse Excel File"
result = .ShowDialog()
End With
If result = System.Windows.Forms.DialogResult.OK Then
grdPnisEntry.Columns.Clear()
grdPnisEntry.Clear()

Dim column As PowerGridColumn
Dim row As DataRow

loXlWorkSheet = loXlApp.Workbooks.Open(fd.FileName)
loXlWorkBook = loXlWorkSheet.Worksheets(1)

Dim range As Excel.Range
range = loXlWorkBook.UsedRange
Dim rCnt, cCnt, i As New Integer
Dim Obj As New Object
Dim value As String = String.Empty
Dim mopniDataSet As New System.Data.DataSet
mopniDataSet.Clear()
mopniDataSet.Tables.Add(New System.Data.DataTable)

For rCnt = 3 To range.Rows.Count
row = mopniDataSet.Tables(0).NewRow
For cCnt = 1 To range.Columns.Count
Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
If rCnt = 3 Then
If cCnt = 1 Then
column = New PowerGridColumn()
column.Name = "clmDefinition"
column.FieldName = "Definition"
column.ReadOnly = True
grdPnisEntry.Columns.Add(column)
mopniDataSet.Tables(0).Columns.Add(column.Name)
column = New PowerGridColumn()
column.Name = "clmSysKey"
column.FieldName = "SysKey"
column.ReadOnly = False
column.Visible = False
grdPnisEntry.Columns.Add(column)
mopniDataSet.Tables(0).Columns.Add(column.Name)
miColumnCount = miColumnCount + 2
End If
column = New PowerGridColumn()
column.Name = Obj.value
column.FieldName = Obj.value
column.ReadOnly = True
grdPnisEntry.Columns.Add(column)
mopniDataSet.Tables(0).Columns.Add(column.Name)
miColumnCount = miColumnCount + 1
Else
If Obj.value = String.Empty AndAlso rCnt > 4 Then
row(0) = CType(range.Cells(2, 2), Excel.Range).Value
i = grdPnisEntry.Rows - 1
row(cCnt + 1) = grdPnisEntry.Cell((cCnt + 1), i)
Else
row(0) = CType(range.Cells(2, 2), Excel.Range).Value
row(cCnt + 1) = Obj.value
End If
If cCnt = range.Columns.Count Then
mopniDataSet.Tables(0).Rows.Add(row)
End If
End If
Next cCnt
Next

grdPnisEntry.DataSource = mopniDataSet.Tables(0)
grdPnisEntry.Columns(1).GroupIndex = 1
grdPnisEntry.Columns(1).Width = 100
grdPnisEntry.Columns(1).SetColumnVisibility(False, 2)
grdPnisEntry.Columns(4).Width = 300
grdPnisEntry.Columns(5).Width = 300
grdPnisEntry.Columns(6).Width = 300
grdPnisEntry.DoExpandAllGroups()
Dim liCount As Integer = 0
txtImportExcel.Text = fd.FileName
fd.Dispose()
loXlWorkSheet.Close()
loXlApp.Quit()
Me.lbImportExcel = True
txtAssessmentTypeDesc.Clear()
txtAssessmentTypeDesc.ReadOnly = True
mSetHyperlinks(False)
miCompItemId = CInt(Sync.Item(Me.Name, "COMPETENCY_ITEM_ID").Value)
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Information")
Finally
If result = System.Windows.Forms.DialogResult.OK Then
// untuk melepas resource dari object excel yg kita buat.
Runtime.InteropServices.Marshal.ReleaseComObject(loXlApp)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkBook)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkSheet)
loXlApp = Nothing
loXlWorkBook = Nothing
loXlWorkSheet = Nothing
End If
End Try

Create Custom Message Box VB.NET

Dim frmMsgBx As New Form
Dim lblMsgText As New System.Windows.Forms.Label
Dim btnMsgOpen, btnMsgSave, btnMsgCancel As New System.Windows.Forms.Button

With frmMsgBx
.StartPosition = FormStartPosition.CenterScreen
.MinimizeBox = False
.MaximizeBox = False
.Width = 380
.Height = 150
.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow
.ShowIcon = False
.ShowInTaskbar = False
.Text = "Exporting to Excel"
End With

With lblMsgText
.Parent = frmMsgBx
.Left = 30
.Top = 30
.Width = 300
.Height = 13
.Text = "Would you like to open the file or save it to your computer?"
.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
End With

With btnMsgOpen
.Parent = frmMsgBx
.Left = 50
.Top = 80
.Width = 52
.Height = 23
.Text = "Open"
.DialogResult = System.Windows.Forms.DialogResult.OK
End With

With btnMsgSave
.Parent = frmMsgBx
.Left = 110
.Top = 80
.Width = 73
.Height = 23
.Text = "Save"
.DialogResult = System.Windows.Forms.DialogResult.Yes
End With

With btnMsgCancel
.Parent = frmMsgBx
.Left = 250
.Top = 80
.Width = 75
.Height = 23
.Text = "Cancel"
.DialogResult = System.Windows.Forms.DialogResult.Cancel
End With

frmMsgBx.ShowDialog()

Web Service in .NET

Web service adalah komponen layakan aplikasi yang didesain untuk mendukung interaksi antar aplikasi dan integrasi aplikasi yang biasanya diserialisasi dengan menggunakan XML (eXtensible Markup Language) (dalam teknologi WSE (Web Service Enchanment) memungkinkan web service untuk diserialisasi sebagai binary) dan dapat diakses melalui protokol terbuka yaitu SOAP (Simple Object Access Protocol) dengan bahasa WSDL (Web Service Description Language) dan terdaftar dalam UUDI (Universal Discovery Description and Integration). XML sendiri adalah sebuah standart yang digunakan untuk mendefinisikan data dalam format yang sederhana dan fleksibel.
Web Service menjadi populer saat ini karena web service mampu mengintegrasikan aplikasi yang berbeda platform secara lebih sederhana dan mampu memperbaiki kelemahan dari teknologi sistem terdistribusi lainnya seperti RPC (Remote Procedure Call), Java RMI (Java Remote Method Invocation) maupun arsitektur CORBA (Common Object Request Broker Architecture). Kelemahan utama dari sistem terdistribusi tersebut adalah tidak mendukung heterogenoitas karena masing-masing teknologi mempunyai standar protokol sendiri-sendiri.
Web services sendiri merupakan komponen yang independen terhadap platform ataupun bahasa. Web services menggunakan protokol HTTP (HyperText Transfer Protocol) yang merupakan protokol yang sangat umum digunakan. Dimana protokol tersebut sangat mendukung heterogenoitas dan interoperabilitas serta memudahkan integrasi. Untuk lebih jelasnya bisa dilihat ilustrasi dari cara kerja web service seperti yang diilustrasikan pada gambar dibawah ini :



using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Configuration;
using System.Collections.Generic;
using TesoLibrary;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Service : System.Web.Services.WebService
{
private GuideLineLogic guideLineLogic;
private ProjectMemberLogic projectMemberLogic;
private EmployeeLogic employeeLogic;
private EvaluationGroupLogic evaluationGroupLogic;
private EvaluationItemLogic evaluationItemLogic;
private ProjectLogic projectLogic;

public Service () {
Util.Instance.ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
this.guideLineLogic = new GuideLineLogic();
this.projectMemberLogic = new ProjectMemberLogic();
this.employeeLogic = new EmployeeLogic();
this.evaluationGroupLogic = new EvaluationGroupLogic();
this.evaluationItemLogic = new EvaluationItemLogic();
this.projectLogic = new ProjectLogic();
}

[WebMethod]
public void InsertGuideLine(GuideLine item)
{
this.guideLineLogic.Insert(item);
}

[WebMethod]
public void UpdateGuideLine(GuideLine item)
{
this.guideLineLogic.Update(item);
}

[WebMethod]
public void DeleteGuideLine(GuideLine item)
{
this.guideLineLogic.Delete(item);
}

[WebMethod]
public GuideLine FetchGuideLine(string criteria)
{
return this.guideLineLogic.GetObject(criteria);
}

[WebMethod]
public List RetrieveGuideLine()
{
return this.guideLineLogic.GetObjects();
}

[WebMethod]
public void InsertProjectMember(ProjectMember item)
{
this.projectMemberLogic.Insert(item);
}

[WebMethod]
public void UpdateProjectMember(ProjectMember item)
{
this.projectMemberLogic.Update(item);
}

[WebMethod]
public void DeleteProjectMember(ProjectMember item)
{
this.projectMemberLogic.Delete(item);
}

[WebMethod]
public ProjectMember FetchProjectMember(string criteria)
{
return this.projectMemberLogic.GetObject(criteria);
}

[WebMethod]
public List RetrieveProjectMember()
{
return this.projectMemberLogic.GetObjects();
}

[WebMethod]
public void InsertEmployee(Employee item)
{
this.employeeLogic.Insert(item);
}

[WebMethod]
public void UpdateEmployee(Employee item)
{
this.employeeLogic.Update(item);
}

[WebMethod]
public void DeleteEmployee(Employee item)
{
this.employeeLogic.Delete(item);
}

[WebMethod]
public Employee FetchEmployee(string criteria)
{
return this.employeeLogic.GetObject(criteria);
}

[WebMethod]
public List RetrieveEmployee()
{
return this.employeeLogic.GetObjects();
}

[WebMethod]
public void InsertEvaluationGroup(EvaluationGroup item)
{
this.evaluationGroupLogic.Insert(item);
}

[WebMethod]
public void UpdateEvaluationGroup(EvaluationGroup item)
{
this.evaluationGroupLogic.Update(item);
}

[WebMethod]
public void DeleteEvaluationGroup(EvaluationGroup item)
{
this.evaluationGroupLogic.Delete(item);
}

[WebMethod]
public EvaluationGroup FetchEvaluationGroup(string criteria)
{
return this.evaluationGroupLogic.GetObject(criteria);
}

[WebMethod]
public List RetrieveEvaluationGroup()
{
return this.evaluationGroupLogic.GetObjects();
}

[WebMethod]
public void InsertEvaluationItem(EvaluationItem item)
{
this.evaluationItemLogic.Insert(item);
}

[WebMethod]
public void UpdateEvaluationItem(EvaluationItem item)
{
this.evaluationItemLogic.Update(item);
}

[WebMethod]
public void DeleteEvaluationItem(EvaluationItem item)
{
this.evaluationItemLogic.Delete(item);
}

[WebMethod]
public EvaluationItem FetchEvaluationItem(string criteria)
{
return this.evaluationItemLogic.GetObject(criteria);
}

[WebMethod]
public List RetrieveEvaluationItem()
{
return this.evaluationItemLogic.GetObjects();
}

[WebMethod]
public void InsertProject(Project item)
{
this.projectLogic.Insert(item);
}

[WebMethod]
public void UpdateProject(Project item)
{
this.projectLogic.Update(item);
}

[WebMethod]
public void DeleteProject(Project item)
{
this.projectLogic.Delete(item);
}

[WebMethod]
public Project FetchProject(string criteria)
{
return this.projectLogic.GetObject(criteria);
}

[WebMethod]
public List RetrieveProject()
{
return this.projectLogic.GetObjects();
}

[WebMethod]
public bool LoginUser(string name, string password)
{
List list = new List();
foreach (Employee item in list)
{
if (item.UserName.Trim().Equals(name))
{
if (item.Password.Trim().Equals(password))
{
return true;
}
}
}
return false;
}
}

Using MVC Concept in .NET

1. Entity
   
   using System;
   using System.Collections.Generic;
   using System.Text;
   
   namespace TesoLibrary
   {
   public class Employee
   {
   private string _empId;
   private string _name;
   private string _role;
   private string _roleApp;
   private string _userName;
   private string _password;
   
   public string EmpId
   {
   get { return _empId; }
   set { _empId = value; }
   }
   
   public string Name
   {
   get { return _name; }
   set { _name = value; }
   }
   
   public string Role
   {
   get { return _role; }
   set { _role = value; }
   }
   
   public string RoleApp
   {
   get { return _roleApp; }
   set { _roleApp = value; }
   }
   
   public string UserName
   {
   get { return _userName; }
   set { _userName = value; }
   }
   
   public string Password
   {
   get { return _password; }
   set { _password = value; }
   }
   
   public Employee(string empId, string name, string role, string roleApp, string userName, string password)
   {
   this._empId = empId;
   this._name = name;
   this._role = role;
   this._roleApp = roleApp;
   this._userName = userName;
   this._password = password;
   }
   
   public Employee()
   {
   this._empId = string.Empty;
   this._name = string.Empty; ;
   this._role = string.Empty; ;
   this._roleApp = string.Empty; ;
   this._userName = string.Empty; ;
   this._password = string.Empty; ;
   }
   }
   }
   
   
2. Data Access
   
   using System;
   using System.Collections.Generic;
   using System.Text;
   using System.Data;
   using System.Data.SqlClient;
   
   namespace TesoLibrary
   {
   public class EmployeeDataAccess: DataAccessAbstract
   {
   protected override SqlCommand GetInsertCommand(Employee item)
   {
   SqlCommand command = new SqlCommand();
   command.CommandText = "insert into employee(empid, name, role, roleapp, username, password) values(" + "'" + item.EmpId + "','" + item.Name + "','" + item.Role + "','" + item.RoleApp + "','" + item.UserName + "','" + item.Password + "')";
   return command;
   }
   
   protected override SqlCommand GetUpdateCommand(Employee item)
   {
   SqlCommand command = new SqlCommand();
   command.CommandText = "update employee set name = '" + item.Name + "'" + ", role = '" + item.Role + "'" + ", roleapp = '" + item.RoleApp + "'" + ", username = '" + item.UserName + "'" + ", password = '" + item.Password + "'" + " where empid = '" + item.EmpId + "'";
   return command;
   }
   
   protected override SqlCommand GetDeleteCommand(Employee item)
   {
   SqlCommand command = new SqlCommand();
   command.CommandText = "delete from employee where empid = '" + item.EmpId + "'" ;
   return command;
   }
   
   protected override SqlCommand GetObjectCommand(K criteria)
   {
   SqlCommand command = new SqlCommand();
   command.CommandText = "select empid, name, role, roleapp, username, password from employee where empid = " + criteria;
   return command;
   }
   
   protected override SqlCommand GetObjectsCommand()
   {
   SqlCommand command = new SqlCommand();
   command.CommandText = "select empid, name, role, roleapp, username, password from employee";
   return command;
   }
   
   protected override Employee MapObject(SqlDataReader reader)
   {
   Employee item = new Employee(reader["empid"].ToString(), reader["name"].ToString(), reader["role"].ToString(), reader["roleapp"].ToString(), reader["username"].ToString(), reader["password"].ToString());
   return item;
   }
   }
   }
   
   
3. Logic
   
   using System;
   using System.Collections.Generic;
   using System.Text;
   
   namespace TesoLibrary
   {
   public class EmployeeLogic
   {
   public EmployeeDataAccess _dataAccess = new EmployeeDataAccess();
   
   public void Insert(Employee item)
   {
   this._dataAccess.Insert(item);
   }
   
   public void Update(Employee item)
   {
   this._dataAccess.Update(item);
   }
   
   public void Delete(Employee item)
   {
   this._dataAccess.Delete(item);
   }
   
   public Employee GetObject(string criteria)
   {
   return this._dataAccess.GetObject(criteria);
   }
   
   public List GetObjects()
   {
   return this._dataAccess.GetObjects();
   }
   
   public Employee AuthenticateUser(string userName, string password)
   {
   string criteria = " where Username='" + userName + "' and Password='" + password + "'";
   return this._dataAccess.GetSpecificObject(criteria);
   }
   
   }
   }