First define your service model
public class Busstopmodel
{
[Key]
public int BUSSTOPS_ID { get; set; }
public string BUSSTOP_DESCRIPTION { get; set; }
public string BUSSTOP_COORDINATES { get; set; }
public string BUSSTOP_SEARCH_NAME { get; set; }
public string message { get; set; }
public DateTime ARCHIVE_DATE { get; set; }
}
and here is a function that gets data from the DB
public List<Busstopmodel> GetPlaces()
{
string StrCon = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection connection = new SqlConnection(StrCon);
SqlCommand command = new SqlCommand();
command.CommandText = "spx_getplaces";
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
List<Busstopmodel> lstplaces = new List<Busstopmodel>();
DataTable dtplaces = new DataTable();
da.SelectCommand = command;
try
{
connection.Open();
da.Fill(dtplaces);
if (dtplaces.Rows.Count > 0)
{
for (int i = 0; i < dtplaces.Rows.Count; i++)
{
Busstopmodel m = new Busstopmodel();
if(dtplaces.Rows[i]["BUSSTOPS_ID"] != DBNull.Value)
m.BUSSTOPS_ID = Convert.ToInt32(dtplaces.Rows[i]["BUSSTOPS_ID"]);
if (dtplaces.Rows[i]["BUSSTOP_COORDINATES"] != DBNull.Value)
m.BUSSTOP_COORDINATES = dtplaces.Rows[i]["BUSSTOP_COORDINATES"].ToString();
if (dtplaces.Rows[i]["BUSSTOP_DESCRIPTION"] != DBNull.Value)
m.BUSSTOP_DESCRIPTION = dtplaces.Rows[i]["BUSSTOP_DESCRIPTION"].ToString();
if (dtplaces.Rows[i]["BUSSTOP_SEARCH_NAME"] != DBNull.Value)
m.BUSSTOP_SEARCH_NAME = dtplaces.Rows[i]["BUSSTOP_SEARCH_NAME"].ToString();
lstplaces.Add(m);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return lstplaces ;
}
Write a code to retrieve this method in your controller
// GET api/values
public IEnumerable<Busstopmodel> Get()
{
TolaTaxiService.Services.Database.Database db = new TolaTaxiService.Services.Database.Database();
IEnumerable<Busstopmodel> busstopmodel = db.GetPlaces();
if (busstopmodel == null)
{
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
}
return busstopmodel;
}
calls this in JQuery when the page loads
<script src="Js/jquery-1.11.0.min.js"></script>
<script type="text/javascript">
$(document).ready(function ()
{
e.preventDefault();
var data = [];
$.getJSON("http://localhost:39192/api/Values", null, function (data) {
data = $.map(data, function (item, a) {
return "<option value=" + item.Value + ">" + item.Text + "</option>";
});
$("#Select1").html(data.join(""));
});
});