使用Entity Framework将数据从excel插入到sql server [英] insert data from excel to sql server using Entity Framework

问题描述

我尝试将数据从.xls文件传递到sql server但是它仍然没有工作错误消息说关键字不支持元数据，我正在使用Entity Framework我的代码;

I try to passing data from .xls file to sql server but its still not working the error messages says Keyword not support metadata, I'm using Entity Framework its my code;

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string conn = ConfigurationManager.ConnectionStrings["BaruEntities"].ConnectionString;
                SqlConnection con = new SqlConnection(conn);
                string query = "Insert into TrashExcel(No Pin,Nama, No HP,Tgl Lahir, No KTP) Values('" + ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "','" + ds.Tables[0].Rows[i][2].ToString() + "','" + ds.Tables[0].Rows[i][3].ToString() + "','" + ds.Tables[0].Rows[i][4].ToString() + "')";
                con.Open();
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.ExecuteNonQuery();
                con.Close();
}

如果你有解决方案请告诉我，我会很感激谢谢

If you have solution for this please tell me and I'll appreciate it Thanks

推荐答案

当列名中有空格时使用参数和括号

Use parameters and brackets when you have spaces in the column names

string query = "Insert into TrashExcel([No Pin],[Nama], [No HP],[Tgl Lahir], [No KTP]) Values(@p1,@p2,@p3,@p4,@p5)";
using(SqlConnection con = new SqlConnection(conn))
{
 con.Open();
 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
 {
  using(SqlCommand cmd = new SqlCommand(query, con))
  {
    cmd.Parameters.AddWithValue("@p1", (string)ds.Tables[0].Rows[i][0]);
    cmd.Parameters.AddWithValue("@p2", (string)ds.Tables[0].Rows[i][1]);
    cmd.Parameters.AddWithValue("@p3", (string)ds.Tables[0].Rows[i][2]);
    cmd.Parameters.AddWithValue("@p4", (string)ds.Tables[0].Rows[i][3]);
    cmd.Parameters.AddWithValue("@p5", (string)ds.Tables[0].Rows[i][4]);
    cmd.ExecuteNonQuery();
  }
 }
}

首先，你没有使用实体框架。你正在使用Ado.net

还有一个库你可以找到一个名为LinqtoExcel的nuget数据包



有了这个库，你可以使用excel中的每个工作表作为一个表，如Sql

和每一行作为一个实体。

Firstly u are not using entity framework . U are using Ado.net
And there is a library that you can find as a nuget packets named LinqtoExcel

With this library you can use Every sheets in excel as a table like in Sql
and every row as an entity.

这篇关于使用Entity Framework将数据从excel插入到sql server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！