public static int AddSupplier(Suppliers sup)
{
int supId = 0;
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Suppliers(SupplierId,SupName) VALUES(@SupId,@SupName)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
// bind parameters
cmd.Parameters.AddWithValue("@SupId", sup.SupplierId);
if (sup.SupName == null)
{
cmd.Parameters.AddWithValue("@SupName", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@SupName", sup.SupName);
}
try
{
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT * FROM Suppliers WHERE SupplierId=@SupId"; // Identity value
SqlCommand selectCommand = new SqlCommand(selectQuery, con);
selectCommand.Parameters.AddWithValue("@SupId", sup.SupplierId);
supId = Convert.ToInt32(selectCommand.ExecuteScalar()); // single value
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(supId);
}
public static List <Suppliers> GetSuppliers()
{
List <Suppliers> suppliers = new List <Suppliers>();
Suppliers sup;
string selectQuery = "SELECT SupplierId, SupName " +
"FROM Suppliers " +
"ORDER BY SupplierId";
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(selectQuery, con))
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
sup = new Suppliers();
sup.SupplierId = (int)dr["SupplierId"];
// Get the SupName column number
int supNameColIndex = dr.GetOrdinal("SupName");
if (dr.IsDBNull(supNameColIndex))
{
sup.SupName = null;
}
else
{
sup.SupName = dr["SupName"].ToString();
}
suppliers.Add(sup);
}
}
}
return(suppliers);
}