Create a program that performs file IO and database interaction via SQL. The pro
ID: 3929018 • Letter: C
Question
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:
CPU Name Performance Price (USD) Intel Core i7-3770K @ 3.50GHz 9,556 $560.50 Intel Core i7-3770 @ 3.40GHz 9,327 $335.55 Intel Core i7-3820 @ 3.60GHz 8,990 $404.38 AMD FX-8350 Eight-Core 8,940 $149.99 Intel Core i7-2600K @ 3.40GHz 8,501 $379.97 Intel Core i7-2600 @ 3.40GHz 8,242 $214.99 Intel Core i7-4720HQ @ 2.60GHz 8,046 NA AMD FX-8320 Eight-Core 8,008 $145.99 Intel Core i7-6700HQ @ 2.60GHz 7,997 $1 509 Intel Core i7-4710HQ @ 2.50GHz 7,826 NA Intel Core i5-6600K @ 3.50GHz 7,762 $239.99 Intel Core i7-4700HQ @ 2.40GHz 7,754 $383.00 Intel Core i7-4700MQ @ 2.40GHz 7,736 $467.40 Intel Core i5-4690K @ 3.50GHz 7,690 $239.99 AMD FX-8150 Eight-Core 7,619 $165.99 Intel Core i7-3630QM @ 2.40GHz 7,604 $304.49 Intel Core i5-4670K @ 3.40GHz 7,598 $249.99 Intel Core i5-4690 @ 3.50GHz 7,542 $224.99 Intel Core i7-3610QM @ 2.30GHz 7,460 $399.99 Intel Core i5-4670 @ 3.40GHz 7,342 $226.99 Intel Core i5-4590 @ 3.30GHz 7,174 $199.99 Intel Core i7-4702MQ @ 2.20GHz 7,146 NA Intel Core i5-3570K @ 3.40GHz 7,130 $477.23Explanation / Answer
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);
}
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.