How to post the data into database web api C#

You can do like this :

otherwise do like this to read from excel and save to database

[Route("UploadFile")]
public ActionResult UploadFile(int ID)
{
try
{
Response res = new Response();
SqlConnection con = null;
string Connectionstring = _config.GetValue<string>("ConnectionString");
con = new SqlConnection(Connectionstring);
var file = Request.Form.Files[0];
string FileName = Request.Form.Files[0].FileName.ToString();
string folderName = "UploadedFiles\\";
string webRootPath = _hostingEnvironment.ContentRootPath;
string newPath = Path.Combine(webRootPath, folderName);
FileInfo files = new FileInfo(Path.Combine(newPath, FileName));
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
if (file.Length > 0)
{
string fileName = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"');
string fullPath = Path.Combine(newPath, fileName);
using (var stream = new FileStream(fullPath, FileMode.Open,FileAccess.ReadWrite,FileShare.ReadWrite))
{
file.CopyTo(stream);
stream.Flush();
stream.Dispose();
stream.Close();
}
}
string webRootPaths = _hostingEnvironment.WebRootPath;
if (string.IsNullOrWhiteSpace(_hostingEnvironment.WebRootPath))
{
string WebRootPath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot");
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2010;
string basePath = files.FullName.ToString();
FileStream sampleFile = new FileStream(basePath, FileMode.Open);
IWorkbook workbook = application.Workbooks.Open(sampleFile);
IWorksheet worksheet = workbook.Worksheets[0];
DataTable dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
SqlCommand sqlcmd = new SqlCommand("Sp_Insert_Update_ThirdParty_Users", con);
con.Open();
sqlcmd.CommandType = CommandType.StoredProcedure;
foreach(DataRow row in dataTable.Rows)
{
sqlcmd.Parameters.Clear();
sqlcmd.Parameters.AddWithValue("@User_Name", Convert.ToString(row["User_Name"]));
sqlcmd.Parameters.AddWithValue("@Password", Convert.ToString(row["Password"]));
sqlcmd.Parameters.AddWithValue("@Group", Convert.ToString(row["Group"]));
sqlcmd.Parameters.AddWithValue("@Is_Served", false);
sqlcmd.Parameters.AddWithValue("@Is_Active", false);
sqlcmd.Parameters.AddWithValue("@CreatedBy", ID);
sqlcmd.ExecuteNonQuery();
}
con.Close();
sampleFile.Close();
return Json("Upload Successful.");
}
catch (System.Exception ex)
{
return Json("Upload Failed: " + ex.Message);
}
}

Hi,

We can call like this

using (var client = new HttpClient())
{
client.BaseAddress = new Uri("http://localhost:58745");
client.DefaultRequestHeaders.Accept.Clear();
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
var response = client.GetAsync("api/values/10").Result;
if (response.IsSuccessStatusCode)
{
string responseString = response.Content.ReadAsStringAsync().Result;
var modelObject = response.Content.ReadAsAsync().Result;
}
}

you can write the code for database insertion in same web api

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishvjit Shinde6y ago
You can do like this :
otherwise do like this to read from excel and save to database
[Route("UploadFile")]
        public ActionResult UploadFile(int ID)
        {
            try
            {
                Response res = new Response();
                SqlConnection con = null;
                string Connectionstring = _config.GetValue<string>("ConnectionString");
                con = new SqlConnection(Connectionstring);
                var file = Request.Form.Files[0];
                string FileName = Request.Form.Files[0].FileName.ToString();
                string folderName = "UploadedFiles\\";
                string webRootPath = _hostingEnvironment.ContentRootPath;
                string newPath = Path.Combine(webRootPath, folderName);
                FileInfo files = new FileInfo(Path.Combine(newPath, FileName));
                if (!Directory.Exists(newPath))
                {
                    Directory.CreateDirectory(newPath);
                }
                if (file.Length > 0)
                {
                    string fileName = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"');
                    string fullPath = Path.Combine(newPath, fileName);
                    using (var stream = new FileStream(fullPath, FileMode.Open,FileAccess.ReadWrite,FileShare.ReadWrite))
                    {
                        file.CopyTo(stream);
                        stream.Flush();
                        stream.Dispose();
                        stream.Close();
                    }
                }
                string webRootPaths = _hostingEnvironment.WebRootPath;
                if (string.IsNullOrWhiteSpace(_hostingEnvironment.WebRootPath))
                {
                    string WebRootPath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot");
                }
                ExcelEngine excelEngine = new ExcelEngine();
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2010;
                string basePath = files.FullName.ToString();
                FileStream sampleFile = new FileStream(basePath, FileMode.Open);
                IWorkbook workbook = application.Workbooks.Open(sampleFile);
                IWorksheet worksheet = workbook.Worksheets[0];
                DataTable dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
                SqlCommand sqlcmd = new SqlCommand("Sp_Insert_Update_ThirdParty_Users", con);
                con.Open();
                sqlcmd.CommandType = CommandType.StoredProcedure;
                foreach(DataRow row in dataTable.Rows)
                {
                    sqlcmd.Parameters.Clear();
                    sqlcmd.Parameters.AddWithValue("@User_Name", Convert.ToString(row["User_Name"]));
                    sqlcmd.Parameters.AddWithValue("@Password", Convert.ToString(row["Password"]));
                    sqlcmd.Parameters.AddWithValue("@Group", Convert.ToString(row["Group"]));
                    sqlcmd.Parameters.AddWithValue("@Is_Served", false);
                    sqlcmd.Parameters.AddWithValue("@Is_Active", false);
                    sqlcmd.Parameters.AddWithValue("@CreatedBy", ID);
                    sqlcmd.ExecuteNonQuery();
                }
                con.Close();
                sampleFile.Close();
                return Json("Upload Successful.");
            }
            catch (System.Exception ex)
            {
                return Json("Upload Failed: " + ex.Message);
            }
        }
Vishvjit Shinde6y ago
Hi,
We can call like this
using (var client = new HttpClient())
            {
                client.BaseAddress = new Uri("http://localhost:58745");
                client.DefaultRequestHeaders.Accept.Clear();
                client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                var response = client.GetAsync("api/values/10").Result;
                if (response.IsSuccessStatusCode)
                {
                    string responseString = response.Content.ReadAsStringAsync().Result;
                    var modelObject = response.Content.ReadAsAsync().Result;
                }
            }
OR
you can write the code for database insertion in same web api
Salman Beg6y ago
Hi.
First get the result of one web api , which is json. In the back end only call another web api which will take the json value of 1st value as a parameter.
In the 2nd web api write your logic to insert it into database.

https://www.c-sharpcorner.com/article/calling-web-api-using-httpclient/

https://www.tutorialsteacher.com/webapi/consuming-web-api-in-dotnet-using-httpclient
Rupa Maya6y ago
hello vishvjit, i have read the excel data using web api in json format and want to store the read data into database using web api without MVC .... how can we call get method web api into post method web api method ... is this much clear ?
Vishvjit Shinde6y ago
Hi Rupa,
Will you elaborate on your Question.