public static int InsertElement(ElementDTO element, string pasport, SqlConnection connection)
{
// if one of the attributes (aside from Name) is null or empty, this is an empty element and do nothing
if (String.IsNullOrEmpty(element.State))
{
return(1);
}
if (String.IsNullOrEmpty(element.Name) || String.IsNullOrEmpty(element.NeedOfInvestment) || String.IsNullOrEmpty(element.AmountOfInvestment))
{
throw new DataException("Element can't be only partially empty.");
}
string query = "INSERT INTO [student3].[dbo].[ELEMENT]([name], [state], [need_of_investment], [amount_of_investment], [notes], [pasport]) " +
"VALUES (@name, @state, @need_of_investment, @amount_of_investment, @notes, @pasport);";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = element.Name;
cmd.Parameters.Add("@state", SqlDbType.SmallInt).Value = Convert.ToInt32(element.State);
cmd.Parameters.Add("@need_of_investment", SqlDbType.VarChar).Value = Convert.ToInt32(element.NeedOfInvestment);
cmd.Parameters.Add("@amount_of_investment", SqlDbType.VarChar).Value = Convert.ToInt32(element.AmountOfInvestment);
cmd.Parameters.Add("@notes", SqlDbType.VarChar).Value = element.Notes;
cmd.Parameters.Add("@pasport", SqlDbType.VarChar).Value = pasport;
int ret = 0;
try
{
ret = cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException) { } // ignore them for now
return(ret);
}
public static List <ElementDTO> GetPasportElementsByID(string id, SqlConnection connection)
{
List <ElementDTO> elements = new List <ElementDTO>();
if (String.IsNullOrEmpty(id))
{
return(elements);
}
string query = "SELECT * FROM ELEMENT WHERE pasport=@PASPORT;";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@PASPORT", id);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ElementDTO elementDTO = new ElementDTO()
{
Name = Convert.ToString(reader["name"]),
State = Convert.ToString(reader["state"]),
NeedOfInvestment = Convert.ToString(reader["need_of_investment"]),
AmountOfInvestment = Convert.ToString(reader["amount_of_investment"]),
Notes = Convert.ToString(reader["notes"])
};
elements.Add(elementDTO);
}
}
}
return(elements);
}