Sqlbulkcopy 似乎对我不起作用 [英] Sqlbulkcopy doesn't seem to work for me

问题描述

我创建了一个数据表并尝试通过 SqlBulkCopy 插入该数据表，但不知何故它似乎对我不起作用......

I have created a datatable and trying to insert that datatable through SqlBulkCopy but somehow it doesn't seem to work for me....

我得到了错误，

The given value of type DateTime from the data source cannot be converted
to type decimal of the specified target column.

我的数据源是，

DataTable dt = new DataTable();
    dt.Columns.Add(new DataColumn("EmpId", typeof(Int64)));
    dt.Columns.Add(new DataColumn("FromDate", typeof(DateTime)));
    dt.Columns.Add(new DataColumn("ToDate", typeof(DateTime)));
    dt.Columns.Add(new DataColumn("DaysPresent", typeof(decimal)));
    dt.Columns.Add(new DataColumn("OpeningAdvance", typeof(double)));
    dt.Columns.Add(new DataColumn("AdvanceDeducted", typeof(double)));
    dt.Columns.Add(new DataColumn("RemainingAdvance", typeof(double)));
    dt.Columns.Add(new DataColumn("SalaryGiven", typeof(double)));
    dt.Columns.Add(new DataColumn("CreatedDate", typeof(DateTime)));

    foreach (GridViewRow row in gridEmployee.Rows) 
    {
        if (row.RowType == DataControlRowType.DataRow)
        {
            DataRow dr = dt.NewRow();
            dr["EmpId"] = Convert.ToInt64(((HiddenField)row.Cells[0].FindControl("HiddenId")).Value);
            dr["FromDate"] = Convert.ToDateTime(GetMonthNumberFromAbbreviation(fromdate[1].ToString()) + '/' + fromdate[0].ToString() + '/' + fromdate[2].ToString());
            dr["ToDate"] = Convert.ToDateTime(GetMonthNumberFromAbbreviation(todate[1].ToString()) + '/' + todate[0].ToString() + '/' + todate[2].ToString());
            dr["DaysPresent"] = Convert.ToDecimal(((TextBox)row.Cells[3].FindControl("TxtDaysPresent")).Text);
            dr["OpeningAdvance"] = Convert.ToDouble(((TextBox)row.Cells[4].FindControl("txtOpeningAdv")).Text);
            dr["AdvanceDeducted"] = Convert.ToDouble(((TextBox)row.Cells[5].FindControl("TxtAdvanceDeducted")).Text);
            dr["RemainingAdvance"] = Convert.ToDouble(((TextBox)row.Cells[6].FindControl("TxtClosingAdvance")).Text);
            dr["SalaryGiven"] = Convert.ToDouble(((TextBox)row.Cells[7].FindControl("TxtSalary")).Text);
            dr["CreatedDate"] = Convert.ToDateTime(System.DateTime.Now.ToString());
            dt.Rows.Add(dr);
        }
    }
    SqlBulkCopy sbc = new SqlBulkCopy(connectionString);
    sbc.DestinationTableName = "SalaryDetails";
    sbc.WriteToServer(dt);
    sbc.Close();

我的目标表看起来像这样，

And my destination Table looks like this,

替代文字 http://img231.imageshack.us/img231/5448/mytable.jpg

推荐答案

您需要指定一个列映射，因为您在 DataTable 和目标表中的列数不同.

You need to specify a column mapping, as you don't have the same number of columns in the DataTable and the destination table.

你可以这样做:

sbc.ColumnMappings.Add("EmpId", "EmpId");
sbc.ColumnMappings.Add("FromDate", "FromDate");
// and so on, with the rest of the columns; and after that comes
sbc.WriteToServer(dt);
sbc.Close();

这篇关于Sqlbulkcopy 似乎对我不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！