public BasicInformation GetBasicInformationID()
{
BasicInformation basicInformation = new BasicInformation();
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL_DBconnect"].ConnectionString);
SqlCommand sqlCommand = new SqlCommand("SELECT MAX(id) FROM basicInformation ");
sqlCommand.Connection = sqlConnection;
sqlConnection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
basicInformation = new BasicInformation
{
ID = reader.GetInt32(reader.GetOrdinal("")),
};
}
}
else
{
basicInformation.Name = "未找到資料";
}
sqlConnection.Close();
return(basicInformation);
}
public BasicInformation GetByID(int id)
{
BasicInformation basicInformation = new BasicInformation();
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL_DBconnect"].ConnectionString);
SqlCommand sqlCommand = new SqlCommand("SELECT * FROM basicInformation WHERE id = @id");
sqlCommand.Connection = sqlConnection;
sqlCommand.Parameters.Add(new SqlParameter("@id", id));
sqlConnection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
basicInformation = new BasicInformation
{
ID = reader.GetInt32(reader.GetOrdinal("id")),
Department = reader.GetInt32(reader.GetOrdinal("department")),
StaffCode = reader.GetInt32(reader.GetOrdinal("staffCode")),
Name = reader.GetString(reader.GetOrdinal("name")),
OnDuty = reader.GetDateTime(reader.GetOrdinal("onDuty")),
Position = reader.GetString(reader.GetOrdinal("position")),
Supervisor = reader.GetInt32(reader.GetOrdinal("supervisor")),
};
}
}
else
{
basicInformation.Name = "未找到資料";
}
sqlConnection.Close();
return(basicInformation);
}
public List <BasicInformation> GetCards()
{
List <BasicInformation> basicInformations = new List <BasicInformation>();
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL_DBconnect"].ConnectionString);
SqlCommand sqlCommand = new SqlCommand("SELECT * FROM basicInformation");
sqlCommand.Connection = sqlConnection;
sqlConnection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
BasicInformation basicInformation = new BasicInformation {
ID = reader.GetInt32(reader.GetOrdinal("id")),
Department = reader.GetInt32(reader.GetOrdinal("department")),
StaffCode = reader.GetInt32(reader.GetOrdinal("staffCode")),
Name = reader.GetString(reader.GetOrdinal("name")),
OnDuty = reader.GetDateTime(reader.GetOrdinal("onDuty")),
Position = reader.GetString(reader.GetOrdinal("position")),
Supervisor = reader.GetInt32(reader.GetOrdinal("supervisor")),
};
basicInformations.Add(basicInformation);
}
}
else
{
Console.WriteLine("資料庫為空！");
}
sqlConnection.Close();
return(basicInformations);
}
public void UpdateCard(BasicInformation basicInformation)
{
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL_DBconnect"].ConnectionString);
SqlCommand sqlCommand = new SqlCommand(
@"UPDATE basicInformation SET department = @department, staffCode = @staffCode, name = @name, onDuty = @onDuty, position = @position, supervisor = @supervisor WHERE id = @id");
sqlCommand.Connection = sqlConnection;
sqlCommand.Parameters.Add(new SqlParameter("@id", basicInformation.ID));
sqlCommand.Parameters.Add(new SqlParameter("@department", basicInformation.Department));
sqlCommand.Parameters.Add(new SqlParameter("@staffCode", basicInformation.StaffCode));
sqlCommand.Parameters.Add(new SqlParameter("@name", basicInformation.Name));
sqlCommand.Parameters.Add(new SqlParameter("@onDuty", basicInformation.OnDuty));
sqlCommand.Parameters.Add(new SqlParameter("@position", basicInformation.Position));
sqlCommand.Parameters.Add(new SqlParameter("@supervisor", basicInformation.Supervisor));
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
public void NewCard(BasicInformation basicInformation)
{
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL_DBconnect"].ConnectionString);
SqlCommand sqlCommand = new SqlCommand(
@"INSERT INTO basicInformation (department,staffCode,name,onDuty,position,supervisor)
VALUES (@department,@staffCode,@name,@onDuty,@position,@supervisor)");
sqlCommand.Connection = sqlConnection;
sqlCommand.Parameters.Add(new SqlParameter("@department", basicInformation.Department));
sqlCommand.Parameters.Add(new SqlParameter("@staffCode", basicInformation.StaffCode));
sqlCommand.Parameters.Add(new SqlParameter("@name", basicInformation.Name));
sqlCommand.Parameters.Add(new SqlParameter("@onDuty", basicInformation.OnDuty));
sqlCommand.Parameters.Add(new SqlParameter("@position", basicInformation.Position));
sqlCommand.Parameters.Add(new SqlParameter("@supervisor", basicInformation.Supervisor));
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
