public DataTable SearchDocument(DStaff staff)
{
DataTable DtResult = new DataTable("staff");
SqlConnection SqlCon = new SqlConnection();
try
{
SqlCon.ConnectionString = Conection.cn;
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlCon;
SqlCmd.CommandText = "spsearch_staff_document";
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter ParSearch = new SqlParameter();
ParSearch.ParameterName = "@texsearch";
ParSearch.SqlDbType = SqlDbType.VarChar;
ParSearch.Size = 50;
ParSearch.Value = staff.SearchText;
SqlCmd.Parameters.Add(ParSearch);
SqlDataAdapter SqlData = new SqlDataAdapter(SqlCmd);
SqlData.Fill(DtResult);
}
catch (Exception ex)
{
DtResult = null;
}
return(DtResult);
}
public string Delete(DStaff staff)
{
string resp = "";
SqlConnection SqlCon = new SqlConnection();
try
{
SqlCon.ConnectionString = Conection.cn;
SqlCon.Open();
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlCon;
SqlCmd.CommandText = "spdelete_staff";
SqlCmd.CommandType = CommandType.StoredProcedure;
//Initialize variables
SqlParameter ParId = new SqlParameter();
ParId.ParameterName = "@id"; //same name as in the procedure
ParId.SqlDbType = SqlDbType.Int;
ParId.Value = staff.Id;
SqlCmd.Parameters.Add(ParId);
//Execute the command
resp = SqlCmd.ExecuteNonQuery() == 1 ? "OK" : "Not deleted";
}
catch (Exception ex)
{
resp = "Something went wrong: " + ex.Message;
}
finally
{
if (SqlCon.State == ConnectionState.Open)
{
SqlCon.Close();
}
}
return(resp);
}
public DataTable Login(DStaff staff)
{
DataTable DtResult = new DataTable("staff");
SqlConnection SqlCon = new SqlConnection();
try
{
SqlCon.ConnectionString = Conection.cn;
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlCon;
SqlCmd.CommandText = "splogin";
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter ParUsername = new SqlParameter();
ParUsername.ParameterName = "@username";
ParUsername.SqlDbType = SqlDbType.VarChar;
ParUsername.Size = 20;
ParUsername.Value = staff.Username;
SqlCmd.Parameters.Add(ParUsername);
SqlParameter ParPassword = new SqlParameter();
ParPassword.ParameterName = "@password";
ParPassword.SqlDbType = SqlDbType.VarChar;
ParPassword.Size = 20;
ParPassword.Value = staff.Password;
SqlCmd.Parameters.Add(ParPassword);
SqlDataAdapter SqlData = new SqlDataAdapter(SqlCmd);
SqlData.Fill(DtResult);
}
catch (Exception ex)
{
DtResult = null;
}
return(DtResult);
}
public string Edit(DStaff staff)
{
string resp = "";
SqlConnection SqlCon = new SqlConnection();
try
{
SqlCon.ConnectionString = Conection.cn;
SqlCon.Open();
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlCon;
SqlCmd.CommandText = "spedit_staff";
SqlCmd.CommandType = CommandType.StoredProcedure;
//Initialize variables
SqlParameter ParId = new SqlParameter();
ParId.ParameterName = "@id";
ParId.SqlDbType = SqlDbType.Int;
ParId.Value = staff.Id;
//ParId.Direction = ParameterDirection.Output;
SqlCmd.Parameters.Add(ParId);
SqlParameter ParName = new SqlParameter();
ParName.ParameterName = "@name";
ParName.SqlDbType = SqlDbType.VarChar;
ParName.Size = 20;
ParName.Value = staff.Name;
SqlCmd.Parameters.Add(ParName);
SqlParameter ParSurname = new SqlParameter();
ParSurname.ParameterName = "@surname";
ParSurname.SqlDbType = SqlDbType.VarChar;
ParSurname.Size = 40;
ParSurname.Value = staff.Surname;
SqlCmd.Parameters.Add(ParSurname);
SqlParameter ParGender = new SqlParameter();
ParGender.ParameterName = "@gender";
ParGender.SqlDbType = SqlDbType.VarChar;
ParGender.Size = 1;
ParGender.Value = staff.Gender;
SqlCmd.Parameters.Add(ParGender);
SqlParameter ParDob = new SqlParameter();
ParDob.ParameterName = "@dob";
ParDob.SqlDbType = SqlDbType.Date;
ParDob.Value = staff.Dob;
SqlCmd.Parameters.Add(ParDob);
SqlParameter ParDocumentNumber = new SqlParameter();
ParDocumentNumber.ParameterName = "@document_number";
ParDocumentNumber.SqlDbType = SqlDbType.VarChar;
ParDocumentNumber.Size = 8;
ParDocumentNumber.Value = staff.Document_Number;
SqlCmd.Parameters.Add(ParDocumentNumber);
SqlParameter ParAddress = new SqlParameter();
ParAddress.ParameterName = "@address";
ParAddress.SqlDbType = SqlDbType.VarChar;
ParAddress.Size = 100;
ParAddress.Value = staff.Address;
SqlCmd.Parameters.Add(ParAddress);
SqlParameter ParPhone = new SqlParameter();
ParPhone.ParameterName = "@phone";
ParPhone.SqlDbType = SqlDbType.VarChar;
ParPhone.Size = 10;
ParPhone.Value = staff.Phone;
SqlCmd.Parameters.Add(ParPhone);
SqlParameter ParEmail = new SqlParameter();
ParEmail.ParameterName = "@email";
ParEmail.SqlDbType = SqlDbType.VarChar;
ParEmail.Size = 50;
ParEmail.Value = staff.Email;
SqlCmd.Parameters.Add(ParEmail);
SqlParameter ParAccess = new SqlParameter();
ParAccess.ParameterName = "@access";
ParAccess.SqlDbType = SqlDbType.VarChar;
ParAccess.Size = 20;
ParAccess.Value = staff.Access;
SqlCmd.Parameters.Add(ParAccess);
SqlParameter ParUsername = new SqlParameter();
ParUsername.ParameterName = "@username";
ParUsername.SqlDbType = SqlDbType.VarChar;
ParUsername.Size = 20;
ParUsername.Value = staff.Username;
SqlCmd.Parameters.Add(ParUsername);
SqlParameter ParPassword = new SqlParameter();
ParPassword.ParameterName = "@password";
ParPassword.SqlDbType = SqlDbType.VarChar;
ParPassword.Size = 20;
ParPassword.Value = staff.Password;
SqlCmd.Parameters.Add(ParPassword);
//Execute the command
resp = SqlCmd.ExecuteNonQuery() == 1 ? "OK" : "Not Edited";
}
catch (Exception ex)
{
resp = "Something went wrong: " + ex.Message;
}
finally
{
if (SqlCon.State == ConnectionState.Open)
{
SqlCon.Close();
}
}
return(resp);
}
