Jounral CS Class

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

///

/// Summary description for Employees
///

public class Emp
{
public Emp()
{
//
// TODO: Add constructor logic here
//
}
public static int Employee_Insert(string Fname, string Lname, string Mname, string Mobile, int EType, int Salary)
{
string SQL = "Employee_Insert";
SqlParameter[] par = new SqlParameter[7];
par[0] = new SqlParameter("@Fname", Fname);
par[1] = new SqlParameter("@Lname", Lname);
par[2] = new SqlParameter("@Mname", Mname);
par[3] = new SqlParameter("@Mobile", Mobile);
par[4] = new SqlParameter("@EType", EType);
par[5] = new SqlParameter("@Salary", Salary);
par[6] = new SqlParameter("@IsActive", "1");
return SQLHelper.excute(SQL, par);
}
public static int Salary_Insert(int EID, int Day, int Month, int Year, int Present, double OT, DateTime date)
{
string SQL = "Salary_Insert";
SqlParameter[] par = new SqlParameter[8];
par[0] = new SqlParameter("@EID", EID);
par[1] = new SqlParameter("@Day", Day);
par[2] = new SqlParameter("@Month", Month);
par[3] = new SqlParameter("@Year", Year);
par[4] = new SqlParameter("@Present", Present);
par[5] = new SqlParameter("@date", date);
par[6] = new SqlParameter("@OT", OT);
par[7] = new SqlParameter("@IsActive", "1");
return SQLHelper.excute(SQL, par);
}
public static int Uppad_Insert(int EID, int Day, int Month, int Year, DateTime date, int Rs)
{
string SQL = "Uppad_Insert";
SqlParameter[] par = new SqlParameter[7];
par[0] = new SqlParameter("@EID", EID);
par[1] = new SqlParameter("@Day", Day);
par[2] = new SqlParameter("@Month", Month);
par[3] = new SqlParameter("@Year", Year);
par[4] = new SqlParameter("@Date", date);
par[5] = new SqlParameter("@Rs", Rs);
par[6] = new SqlParameter("@Type", "1");
return SQLHelper.excute(SQL, par);
}
public static int Loan_Insert(int EID, int Day, int Month, int Year, DateTime date, int Rs)
{
string SQL = "Loan_Insert";
SqlParameter[] par = new SqlParameter[7];
par[0] = new SqlParameter("@EID", EID);
par[1] = new SqlParameter("@Day", Day);
par[2] = new SqlParameter("@Month", Month);
par[3] = new SqlParameter("@Year", Year);
par[4] = new SqlParameter("@Date", date);
par[5] = new SqlParameter("@Rs", Rs);
par[6] = new SqlParameter("@Type", "2");
return SQLHelper.excute(SQL, par);
}
public static int SalSlip_Insert(string name, int Pday, int ADay,int Loan,double salary,int uppad,int month,int year,float OT,int Tid)
{
string SQL = "SalSlip_Insert";
SqlParameter[] par = new SqlParameter[10];
par[0] = new SqlParameter("@name",name);
par[1] = new SqlParameter("@Pday", Pday);
par[2] = new SqlParameter("@Aday", ADay);
par[3] = new SqlParameter("@Loan", Loan);
par[4] = new SqlParameter("@salary", salary);
par[5] = new SqlParameter("@uppad",uppad);
par[6] = new SqlParameter("@month",month);
par[7] = new SqlParameter("@year", year);
par[8] = new SqlParameter("@OT", OT);
par[9] = new SqlParameter("@Tempid",Tid);
return SQLHelper.excute(SQL, par);
}


public static DataTable FillEmployee()
{
string SQL = "Select * from Emp_Master";
return SQLHelper.GetData(SQL);
}
public static DataTable FillSal()
{
string SQL = "Select * from SalSlip";
return SQLHelper.GetData(SQL);
}
public static DataTable FillSal(int month)
{
string SQL = "Select * from SalSlip where month="+month;
return SQLHelper.GetData(SQL);
}
public static DataTable FillUppdad()
{
string SQL = "SELECT Emp_Master.Fname, SUM(Emp_UppadLoan.Rs) AS Total "+
"FROM Emp_Master INNER JOIN Emp_UppadLoan ON Emp_Master.Eid = Emp_UppadLoan.Eid "+
"GROUP BY Emp_Master.Fname, Emp_UppadLoan.Type HAVING (Emp_UppadLoan.Type = 1)";
return SQLHelper.GetData(SQL);
}
public static DataTable FillLoan()
{
string SQL = "SELECT Emp_Master.Fname , SUM(Emp_UppadLoan.Rs) AS Total " +
"FROM Emp_Master INNER JOIN Emp_UppadLoan ON Emp_Master.Eid = Emp_UppadLoan.Eid " +
"GROUP BY Emp_Master.Fname, Emp_UppadLoan.Type HAVING (Emp_UppadLoan.Type = 2)";
return SQLHelper.GetData(SQL);
}
public static DataTable FillEmployeeWiseUppad(string Ename)
{
//string SQL = "SELECT Emp_UppadLoan.Date, Emp_UppadLoan.Rs FROM Emp_Master INNER JOIN " +
// " Emp_UppadLoan ON Emp_Master.Eid = Emp_UppadLoan.Eid WHERE (Emp_Master.Fname = '"+Ename+"') AND (Emp_UppadLoan.Type = 1) ";
string SQL = "SELECT Emp_UppadLoan.Year, Emp_UppadLoan.Date as Date,Emp_UppadLoan.id ,Emp_UppadLoan.Rs as Rs, Emp_UppadLoan.Month "+
"FROM Emp_Master INNER JOIN Emp_UppadLoan ON Emp_Master.Eid = Emp_UppadLoan.Eid "+
"WHERE (Emp_Master.Fname = '"+Ename+"') AND (Emp_UppadLoan.Type = 1)";
return SQLHelper.GetData(SQL);
}
public static DataTable FillEmployeeWiseLoan(string Ename)
{
//"SELECT Emp_UppadLoan.Date, Emp_UppadLoan.id,Emp_UppadLoan.Rs,Emp_UppadLoan.Month FROM Emp_Master INNER JOIN " +
// " Emp_UppadLoan ON Emp_Master.Eid = Emp_UppadLoan.Eid WHERE (Emp_Master.Fname = '"+Ename+"') AND (Emp_UppadLoan.Type = 2) ";
string SQL = "SELECT Emp_UppadLoan.Year, Emp_UppadLoan.Date as Date,Emp_UppadLoan.id ,Emp_UppadLoan.Rs as Rs, Emp_UppadLoan.Month " +
"FROM Emp_Master INNER JOIN Emp_UppadLoan ON Emp_Master.Eid = Emp_UppadLoan.Eid " +
"WHERE (Emp_Master.Fname = '" + Ename + "') AND (Emp_UppadLoan.Type = 2)";
return SQLHelper.GetData(SQL);
}

public static void Delete(int ID, string Tabel, string Column)
{
string SQL = "Delete from " + Tabel + " where " + Column + " =" + ID;
SQLHelper.ExcuteRecord(SQL);
}
public static DataTable select_update(int Eid)
{
string SQL = "select * from Emp_Master where Eid=" + Eid;
return SQLHelper.selected_update(SQL);
}
public static DataTable GetPresent(int Month)
{
string SQL = "SELECT Salary_Master.Day, Salary_Master.id, Emp_Master.Fname, Salary_Master.Present, Salary_Master.OT " +
"FROM Emp_Master INNER JOIN Salary_Master ON Emp_Master.Eid = Salary_Master.EID " +
"WHERE (Salary_Master.Month ="+Month+") order by Salary_Master.Day";
return SQLHelper.selected_update(SQL);
}
public static DataTable GetPresent_Name(int EID)
{
string SQL = "SELECT Salary_Master.Day, Salary_Master.id, Emp_Master.Fname, Salary_Master.Present, Salary_Master.OT " +
"FROM Emp_Master INNER JOIN Salary_Master ON Emp_Master.Eid = Salary_Master.EID " +
"WHERE (Salary_Master.EID =" + EID + ") order by Salary_Master.Day";
return SQLHelper.selected_update(SQL);
}
public static DataTable GetPresent_year(int Year)
{
string SQL = "SELECT Salary_Master.Day, Salary_Master.id, Emp_Master.Fname, Salary_Master.Present, Salary_Master.OT " +
"FROM Emp_Master INNER JOIN Salary_Master ON Emp_Master.Eid = Salary_Master.EID " +
"WHERE (Salary_Master.Year =" + Year + ") order by Salary_Master.Day";
return SQLHelper.selected_update(SQL);
}
public static DataTable GetPresent_MonthAndYear(int Month,int Year)
{
string SQL = "SELECT Salary_Master.Day, Salary_Master.id, Emp_Master.Fname, Salary_Master.Present, Salary_Master.OT " +
"FROM Emp_Master INNER JOIN Salary_Master ON Emp_Master.Eid = Salary_Master.EID " +
"WHERE (Salary_Master.Month =" + Month + " and Salary_Master.Year =" + Year +") order by Salary_Master.Day ";
return SQLHelper.selected_update(SQL);
}
public static DataTable GetPresent_EmpAndYear(int EID, int Year)
{
string SQL = "SELECT Salary_Master.Day, Salary_Master.id, Emp_Master.Fname, Salary_Master.Present, Salary_Master.OT " +
"FROM Emp_Master INNER JOIN Salary_Master ON Emp_Master.Eid = Salary_Master.EID " +
"WHERE (Salary_Master.Eid =" + EID + " and Salary_Master.Year =" + Year + ") order by Salary_Master.Day ";
return SQLHelper.selected_update(SQL);
}
public static DataTable GetPresent(int Month,int Eid)
{
string SQL = "SELECT Salary_Master.Day, Salary_Master.id, Emp_Master.Fname, Salary_Master.Present, Salary_Master.OT " +
"FROM Emp_Master INNER JOIN Salary_Master ON Emp_Master.Eid = Salary_Master.EID " +
"WHERE (Salary_Master.Month =" + Month + " and Salary_Master.Eid =" + Eid + ") order by Salary_Master.Day ";
return SQLHelper.selected_update(SQL);
}
public static DataTable GetPresent(int Month, int Eid,int Year)
{
string SQL = "SELECT Salary_Master.Day, Salary_Master.id, Emp_Master.Fname, Salary_Master.Present, Salary_Master.OT " +
"FROM Emp_Master INNER JOIN Salary_Master ON Emp_Master.Eid = Salary_Master.EID " +
"WHERE (Salary_Master.Month =" + Month + " and Salary_Master.Year ="+Year+" and Salary_Master.Eid =" + Eid + ") order by Salary_Master.Day ";
return SQLHelper.selected_update(SQL);
}
public static void Emp_Update(int Eid, string Fname, string Lname, string Mname, string Mobile, int EType, int Salary)
{
string SQL = "Update Emp_Master SET Fname='" + Fname + "',Lname='" + Lname + "',Mname='" + Mname + "',Mobile='" + Mobile + "',EType='" + EType + "',Salary='" + Salary + "' where Eid=" + Eid;
SQLHelper.ExcuteRecord(SQL);
}
public static void Present_Update(int id, double ot,int present)
{
string SQL = "Update Salary_Master SET ot="+ot+ ",Present="+present+ " where id="+id;
SQLHelper.ExcuteRecord(SQL);
}

public static void Uppad_Update(int id,int Rs)
{
string SQL = "Update Emp_UppadLoan SET Rs="+Rs+ " where id=" +id;
SQLHelper.ExcuteRecord(SQL);
}
public static void Insert_Day(string Day)
{
string SQL = "Update Days Set HoliDay='"+Day+"' where id=1";
SQLHelper.ExcuteRecord(SQL);
}
public static DataTable FillCombo()
{
string SQL = "select Eid,Fname from Emp_Master";
return SQLHelper.GetData(SQL);
}
public static DataTable GetSalary(int Month)
{
string SQL = "SELECT Emp_Master.Fname, SUM(Salary_Master.Present) AS Present, SUM(Salary_Master.OT) AS OT, " +
" Salary_Master.EID FROM Emp_Master INNER JOIN Salary_Master ON Emp_Master.Eid = Salary_Master.EID AND Salary_Master.Month = "+Month+" " +
"GROUP BY Emp_Master.Fname, Salary_Master.EID";
return SQLHelper.GetData(SQL);
}
}

SqlHelper Class

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

///

/// Summary description for SQLHelper
///

public class SQLHelper
{
SqlConnection conn;
DataTable t;
DataSet ds = new DataSet();
DataSet DsMain = new DataSet();
public SQLHelper()
{
//
// TODO: Add constructor logic here
//
}
#region // Connection string
public static string Connect
{
get { return ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString; }
}
#endregion

#region // Excute Insert Command
public static int excute(string sql,SqlParameter[] par)
{
SqlConnection conn = new SqlConnection(SQLHelper.Connect);
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < par.Length; i++)
{
// if (par[i] != null)
{
cmd.Parameters.Add(par[i]);
}
}
conn.Open();
int affected = cmd.ExecuteNonQuery();
conn.Close();
cmd.Dispose();
return affected;
}
#endregion

#region // Fetch The Data
public static DataTable GetData(string sql)
{
SqlDataAdapter adpt = new SqlDataAdapter(sql, SQLHelper.Connect);
DataTable dt = new DataTable();
adpt.Fill(dt);
return dt;
}
#endregion

public static string ExcuteScaler(string sql)
{
SqlConnection conn = new SqlConnection(SQLHelper.Connect);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
try
{
string dy = (string)cmd.ExecuteScalar();
return dy;
}
catch
{
return "";
}
conn.Close();
}

#region // Count no of Records
public static int CountData(string sql)
{
SqlConnection conn = new SqlConnection(SQLHelper.Connect);
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
int count = (int)cmd.ExecuteScalar();
conn.Close();
return count;
}
#endregion

#region // Excute Query For Delete Update
public static void ExcuteRecord(string sql)
{
SqlConnection conn = new SqlConnection(SQLHelper.Connect);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
#endregion

public static int AffectedRows(string sql)
{
SqlConnection conn = new SqlConnection(SQLHelper.Connect);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
try
{
int rs = (int)cmd.ExecuteScalar();
return rs;
}
catch
{
return 0;
}
conn.Close();

}


#region // For Update Query Excuate
public static DataTable selected_update(string sql)
{
SqlDataAdapter adpt = new SqlDataAdapter(sql, SQLHelper.Connect);
DataTable dt = new DataTable();
adpt.Fill(dt);
return dt;
}
#endregion

#region // Fetch Data in Format Like TreeView

public DataTable yet()
{
t = new DataTable("Total_List");
DsMain.Tables.Add(t);
DataColumn c1 = new DataColumn("CategoryID", Type.GetType("System.String"), "");
DataColumn c2 = new DataColumn("CategoryDesc", Type.GetType("System.String"), "");
DataColumn c3 = new DataColumn("Sub ID", Type.GetType("System.String"), "");
DataColumn c4 = new DataColumn("CategoryLevel", Type.GetType("System.String"), "");
DataColumn c5 = new DataColumn("IsActive", Type.GetType("System.Boolean"), "");
t.Columns.Add(c1);
t.Columns.Add(c2);
t.Columns.Add(c3);
t.Columns.Add(c4);
t.Columns.Add(c5);
LoadResults(0);
return DsMain.Tables["Total_List"];
}
int SubID = 0;
private void LoadResults(int inParentID)
{
if (inParentID == 0)
{
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM CMSForumPCCategory Where SubID=0 order by linkorder asc" , SQLHelper.Connect);
conn = new SqlConnection(SQLHelper.Connect);
conn.Open();
da.Fill(ds, "Search_List" + inParentID);
conn.Close();
}
else
{
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM CMSForumPCCategory Where SubID = " + inParentID, SQLHelper.Connect);
//SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM CMSForumPCCategory Where SubID = " + inParentID, SQLHelper.Connect);
conn.Open();
da.Fill(ds, "Search_List" + inParentID);
conn.Close();
}
DataTable ParentTable;
ParentTable = ds.Tables["Search_List" + inParentID];
foreach (DataRow parentrow in ParentTable.Rows)
{
DataRow newRow;
newRow = t.NewRow();

String ID = Convert.ToString(parentrow.ItemArray[0]);
newRow["CategoryID"] = ID;

String CategoryLevel = Convert.ToString(parentrow.ItemArray[4]);
newRow["CategoryLevel"] = CategoryLevel;

Boolean IsActive = Convert.ToBoolean(parentrow.ItemArray[3]);
newRow["IsActive"] = IsActive;

String Name = String.Concat(Convert.ToString(parentrow.ItemArray[1]));
newRow["CategoryDesc"] = (string)Name;

if (inParentID == 0)
{
newRow["Sub ID"] = (string)SubID.ToString();
}
else
{
SubID = Convert.ToInt32(parentrow.ItemArray[2]);
newRow["Sub ID"] = (string)SubID.ToString();
}
t.Rows.Add(newRow);
LoadResults(Convert.ToInt32(parentrow.ItemArray[0]));
}
}
#endregion
}