How to Use an Application that Interacts with the SQL Server?
- Last UpdatedSep 11, 2024
- 2 minute read
This example illustrates how to use an application which interacts with SQL server.
PROCEDURE
NAMESPACE USED
System
System.Collections.Generic
System.Text
System.Data.sqlClient
System.Data
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace HumanResource
{
public class EmployeeMaster
{
private string _Server;
public string ServerName
{
get { return _Server; }
set { _Server = value; }
}
private string _User;
public string User
{
get { return _User; }
set { _User = value; }
}
private string _Password;
public string Password
{
get { return _Password; }
set { _Password = value; }
}
private string _ID;
public string EmployeeID
{
get { return _ID; }
set { _ID = value; }
}
private string _Name;
public string Name
{
get { return _Name; }
set { _Name = value; }
}
private string _Address;
public string Address
{
get { return _Address; }
set { _Address = value; }
}
static SqlConnection objConnect;
static string sConnection = "";
public EmployeeMaster(string sServer, bool bWindowsAuthent, string sUser, string sPasswd)
{
string sdatabaseName = "HRMS";
if (sServer == string.Empty)
sServer = "skeltanb54";
if ((sUser == string.Empty) || (sPasswd == string.Empty))
{
bWindowsAuthent = true;
sConnection = "Data Source=" + sServer + "; Database=" + sdatabaseName + ";
Trusted_Connection = true" ;
}
else
{
sConnection = "Data Source=" + sServer + "; Database=" + sdatabaseName + ";UID=" + sUser +
"; PWD=" + sPasswd;
}
//objConnect = new SqlConnection(sConnection);
//objConnect.Open();
}
~EmployeeMaster()
{
if (objConnect != null)
objConnect = null;
}
public SqlConnection GetDataBaseConnection()
{
try
{
if (objConnect == null)
{
objConnect = new SqlConnection(sConnection);
objConnect.Open();
}
else
{
if ( objConnect.State == ConnectionState.Closed)
objConnect.Open();
}
}
catch (Exception ex)
{
throw ex;
}
return objConnect;
}
public void CloseDatabaseConnectio()
{
objConnect.Close();
objConnect = null;
}
public string GetEmployeeName(string id)
{
try
{
string sSQL = "select * from Employees where employeeID = '" + id + "'";
GetDataBaseConnection();
SqlDataAdapter adp = new SqlDataAdapter(sSQL, objConnect);
DataSet ds = new DataSet();
adp.Fill(ds);
_Name = ds.Tables[0].Rows[0]["Name"].ToString();
_ID = ds.Tables[0].Rows[0]["EmployeeID"].ToString();
_Address = ds.Tables[0].Rows[0]["Address"].ToString();
return ds.Tables[0].Rows[0]["Name"].ToString();
}
catch (Exception ex)
{
throw ex;
}
}
public void GetEmployeeDetails(string id)
{
try
{
string sSQL = "select * from Employees where employeeID = '" + id + "'";
GetDataBaseConnection();
SqlDataAdapter adp = new SqlDataAdapter(sSQL, objConnect);
DataSet ds = new DataSet();
adp.Fill(ds);
_Name = ds.Tables[0].Rows[0]["Name"].ToString();
_ID = ds.Tables[0].Rows[0]["EmployeeID"].ToString();
_Address = ds.Tables[0].Rows[0]["Address"].ToString();
}
catch (Exception ex)
{
throw ex;
}
}
public string GetConnectEmployeeName( SqlConnection SqlConn, string id)
{
try
{
string sSQL = "select * from Employees where employeeID = '" + id + "'";
SqlDataAdapter adp = new SqlDataAdapter(sSQL, SqlConn);
DataSet ds = new DataSet();
adp.Fill(ds);
_Name = ds.Tables[0].Rows[0]["Name"].ToString();
ID = ds.Tables[0].Rows[0]["EmployeeID"].ToString();
_Address = ds.Tables[0].Rows[0]["Address"].ToString();
return ds.Tables[0].Rows[0]["Name"].ToString();
}
catch (Exception ex)
{
throw ex;
}
}
}
}
Table Structure
Employees – Main table to store employee details
CREATE TABLE [dbo].[Employees](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DOJ] [datetime] NULL
) ON [PRIMARY]
Table to store Leave Balance
CREATE TABLE [dbo].[LeaveBalance](
[ID] [int] NULL,
[CL_Balance] [numeric](18, 2) NULL,
[SL_Balance] [numeric](18, 2) NULL
) ON [PRIMARY]