I am new to Web API and I have a questions: how to import Web API data into a SQL Server database.
My Web API is created with data reader and I have searched in the internet, it seems like it could be import data with data reader. I want to test importing data with Web API, so I can know is my Web API working or not.
Code:
[HttpGet]
public IHttpActionResult GetAll()
{
List<webapi> web = new List<webapi>();
using (SqlCommand cmd = new SqlCommand("SELECT U.UserID, U.Name, U.Mobile, U.Age, U.Birthday, A.Address, A.Country From tbluser U inner join tblAddress A where A.Country=U.Country", connection))
{
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
web.Add(new webapi()
{
UserId = Convert.ToString(sdr.GetValue(0)),
Name = Convert.ToString(sdr.GetValue(01)),
Mobile = Convert.ToString(sdr.GetValue(2)),
Age = (sdr.GetValue(3) != DBNull.Value) ? Convert.ToInt32(sdr.GetValue(3)) : 0,
Birthday = (sdr.GetValue(4) != DBNull.Value) ? Convert.ToDateTime(sdr.GetValue(4)) : (DateTime?)null
Address = Convert.ToString(sdr.GetValue(5)),
Country = Convert.ToString(sdr.GetValue(6))
});
}
return Ok(web);
}
}
[HttpGet]
public IHttpActionResult GetCountry(string country)
{
List<webapi> web = new List<webapi>();
using (SqlCommand cmd = new SqlCommand("SELECT U.UserID, U.Name, U.Mobile, U.Age, U.Birthday, A.Address, A.Country From tbluser U inner join tblAddress A where charindex(@country, A.Country)=1 order by U.UserId", connection))
{
cmd.Parameters.Add("@country", SqlDbType.VarChar);
cmd.Parameters["@country"].Value = country.ToString();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
web.Add(new webapi()
{
UserId = Convert.ToString(sdr.GetValue(0)),
Name = Convert.ToString(sdr.GetValue(01)),
Mobile = Convert.ToString(sdr.GetValue(2)),
Age = (sdr.GetValue(3) != DBNull.Value) ? Convert.ToInt32(sdr.GetValue(3)) : 0,
Birthday = (sdr.GetValue(4) != DBNull.Value) ? Convert.ToDateTime(sdr.GetValue(4)) : (DateTime?)null
Address = Convert.ToString(sdr.GetValue(5)),
Country = Convert.ToString(sdr.GetValue(6))
});
}
}
return Ok(web);
}
}
}
Class:
public class webapi
{
public string UserId { get; set; }
public string Name { get; set; }
public string Mobile { get; set; }
public int Age { get; set; }
public DateTime? Birthday { get; set; }
public string Address { get; set; }
public string Country { get; set; }
}