Create a program that performs file IO and database interact

Create a program that performs file IO and database interaction via SQL.  The program needs to read data from the provided file: \"Project.csv\" and insert the data into a database. Then the program needs to create a report from the database sorted by price descending. The report should be in the format demonstrated below.

id (primary key - generated by the database)

cpuname

performance

price

Project.csv contents:

Solution

this answer in C# lnaguage.

Run Query to create table:

CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
cpuname varchar(255) NOT NULL,
perfomance varchar(255),
price double(10),

)

Use following functions to implement your requirements.

private void ExcelConn(string FilePath)
{
  
constr = string.Format(@\"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"\"Excel 12.0 Xml;HDR=YES;\"\"\", FilePath);
Econ = new OleDbConnection(constr);

}

private void connection()  

{  

sqlconn = ConfigurationManager.ConnectionStrings[\"SqlCom\"].ConnectionString;  

        con = new SqlConnection(sqlconn);  

    }  

private void InsertExcelRecords(string FilePath)
{
ExcelConn(FilePath);
  
Query = string.Format(\"Select [CPU Name],[Perfomance],[Price] FROM [{0}]\", \"Sheet1$\");
OleDbCommand Ecom = new OleDbCommand(Query, Econ);
Econ.Open();
  
DataSet ds=new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
connection();
//creating object of SqlBulkCopy
SqlBulkCopy objbulk = new SqlBulkCopy(con);
//assigning Destination table name
objbulk.DestinationTableName = \"Employee\";
//Mapping Table column
objbulk.ColumnMappings.Add(\"CPU Name\", \"Name\");
objbulk.ColumnMappings.Add(\"Perfomance\", \"City\");
objbulk.ColumnMappings.Add(\"Price\", \"Address\");
  
//inserting Datatable Records to DataBase
con.Open();
objbulk.WriteToServer(Exceldt);
con.Close();
}

protected void Button1_Click(object sender, EventArgs e)

   {    string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);    InsertExcelRecords(CurrentFilePath);  

}