I have try this code to import data to SQL table but it is showing up the eroor as can't convert datetime or int type of column into string or can't map.
But it is working well while I am importing only string columns.
So Please can anyone try to figure out the error and can send me the correct code....!!!!
protected void Button_Click(object sender, EventArgs e)
{
//Creating object of datatable
DataTable tblcsv = new DataTable();
//creating columns
tblcsv.Columns.Add("assetnumber", typeof(int));
tblcsv.Columns.Add("leasenumber", typeof(string));
tblcsv.Columns.Add("equip_description", typeof(string));
tblcsv.Columns.Add("equip_cost", typeof(decimal));
tblcsv.Columns.Add("equip_status", typeof(string));
tblcsv.Columns.Add("equip_statusdate",typeof(DateTime));
tblcsv.Columns.Add("siccode", typeof(string));
tblcsv.Columns.Add("serialnumber", typeof(string));
tblcsv.Columns.Add("equip_codedesc", typeof(string));
tblcsv.Columns.Add("equip_listprice", typeof(decimal));
tblcsv.Columns.Add("model", typeof(string));
tblcsv.Columns.Add("manufacturer_year", typeof(string));
tblcsv.Columns.Add("neworused", typeof(string));
tblcsv.Columns.Add("manufacturer", typeof(string));
tblcsv.Columns.Add("assetdisposition_date",typeof(DateTime));
tblcsv.Columns.Add("asset_description", typeof(string));
//getting full file path of Uploaded file
string CSVFilePath = Path.GetFullPath(FileUpload.PostedFile.FileName);
//Reading All text
string ReadCSV = File.ReadAllText(CSVFilePath);
//spliting row after new line
foreach (string csvRow in ReadCSV.Split('\n'))
{
if (!string.IsNullOrEmpty(csvRow))
{
//Adding each row into datatable
tblcsv.Rows.Add();
int count = 0;
foreach (string FileRec in csvRow.Split(','))
{
tblcsv.Rows[tblcsv.Rows.Count - 1][count] = FileRec;
count++;
}
}
}
//Calling insert Functions
InsertCSVRecords(tblcsv);
}
private void InsertCSVRecords(DataTable tblcsv)
{
connection();
//creating object of SqlBulkCopy
SqlBulkCopy objbulk = new SqlBulkCopy(con);
//assigning Destination table name
objbulk.DestinationTableName = "FilesData";
//Mapping Table column
objbulk.ColumnMappings.Add("assetnumber", "assetnumber");
objbulk.ColumnMappings.Add("leasenumber", "leasenumber");
objbulk.ColumnMappings.Add("equip_description", "equip_description");
objbulk.ColumnMappings.Add("equip_cost", "equip_cost");
objbulk.ColumnMappings.Add("equip_status", "equip_status");
objbulk.ColumnMappings.Add("equip_statusdate", "equip_statusdate");
objbulk.ColumnMappings.Add("siccode", "siccode");
objbulk.ColumnMappings.Add("serialnumber", "serialnumber");
objbulk.ColumnMappings.Add("equip_codedesc", "equip_codedesc");
objbulk.ColumnMappings.Add("equip_listprice", "equip_listprice");
objbulk.ColumnMappings.Add("model", "model");
objbulk.ColumnMappings.Add("manufacturer_year", "manufacturer_year");
objbulk.ColumnMappings.Add("neworused", "neworused");
objbulk.ColumnMappings.Add("manufacturer", "manufacturer");
objbulk.ColumnMappings.Add("assetdisposition_date", "assetdisposition_date");
objbulk.ColumnMappings.Add("asset_description", "asset_description");
//inserting Datatable Records to DataBase
con.Open();
objbulk.WriteToServer(tblcsv);
con.Close();
}