Sir, i am new in .net technology,I want to insert .csv record in mysql database. I have two table 1st is sla(id,month,year) and 2nd is sla1(From SLA Non-Compliance Report Months/Parameter, Target, Unit, OM,Finance,InbndMRO,InbndRM,Maximo,Payroll,HILAllied,Hardware,Network,Software, DBA, OPM,idsla(F_k),id).These two table are related by foreigen key. I want to insert csv record in 2nd table according to month and year.
Asp.net and Mysql Database.
My code is given below...
protected void Button2_Click(object sender, EventArgs e)
{
string filePath = FileUpload1.PostedFile.FileName;
string filename =Path.GetFileName(filePath);
string ext = Path.GetExtension(filename);
string contenttype = String.Empty;
if (!FileUpload1.HasFile)
{
ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Please select a file')</script>"); //if file uploader has no file selected
}
else
if (FileUpload1.HasFile)
{
try
{
//Set the contenttype based on File Extension
switch (ext)
{
//case ".doc":
// contenttype = "application/vnd.ms-word";
// break;
//case ".docx":
// contenttype = "application/vnd.ms-word";
// break;
//case ".xls":
// contenttype = "application/vnd.ms-excel";
// break;
//case ".xlsx":
// contenttype = "application/vnd.ms-excel";
// break;
case ".csv":
contenttype = "application/vnd.ms-excel";
break;
//case ".pdf":
// contenttype = "application/pdf";
// break;
}
if (contenttype != String.Empty)
{
Stream fs = FileUpload1.PostedFile.InputStream;
StreamReader br = new StreamReader(fs);
string line =br.ReadLine();
string[] vlaue=line.Split(';');
DataTable dt=new DataTable();
DataRow row;
foreach(string dc in Value)
{
dt.Columns.Add(new DataColumn(dc));
}
while(!br.EndOfStream)
{
Value=br.ReadLine().Split(';');
if(Value.Length=dt.Columns.Count)
{
row=dt.NewRow();
row.ItemArray=Value;
dt.Rows.Add(row);
}
}
MySqlConnection con=new MySqlConnection("Server=Localhost;DataBase=password1;user=root;password=nectar");
string inst="insert into sla(month,year) values ('"+DropDownList1.SelectedItem.ToString()+"','"+DropDownList2.SelectedItem.ToString()+"') LOAD DATA LOCAL INFILE 'filePath' INTO TABLE sla1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES (From SLA Non-Compliance Report Months/Parameter, Target, Unit, OM,Finance,InbndMRO,InbndRM,Maximo,Payroll,HILAllied,Hardware,Network,Software, DBA, OPM) set 'idsla'=(select idsla from sla where idsla=@idsla)";
MySqlBulkLoader op=new MySqlBulkLoader(con);
}