public List<CheckPointsModel> GetCheckPoints(int routeID) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); List<CheckPointsModel> result = new List<CheckPointsModel>(); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand( string.Format("SELECT CheckPointID, CheckPointName, CheckPointDesc FROM dbo.CheckPoints WHERE routeid = {0};", routeID), connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { CheckPointsModel model = new CheckPointsModel(); model.CheckPointID = Convert.ToInt32(reader["CheckPointID"]); model.CheckPointName = reader["CheckPointName"].ToString(); model.CheckPointDesc = reader["CheckPointDesc"].ToString(); model.RouteID = routeID; result.Add(model); } } reader.Close(); } return result; }
public void SaveCheckPoint(CheckPointsModel model) { try { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); string query = "INSERT INTO dbo.CheckPoints (CheckPointName, CheckPointDesc, RouteID) " + "VALUES (@CheckPointName, @CheckPointDesc, @RouteID) "; using (SqlConnection cn = new SqlConnection(connectionString)) using (SqlCommand cmd = new SqlCommand(query, cn)) { cmd.Parameters.Add("@CheckPointName", SqlDbType.VarChar, 50).Value = model.CheckPointName; cmd.Parameters.Add("@CheckPointDesc", SqlDbType.VarChar, 1000).Value = model.CheckPointDesc; cmd.Parameters.Add("@RouteID", SqlDbType.Int).Value = model.RouteID; cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); } } catch (Exception) { } }