将datatable一次插入access,如何搞?

[江南野鹤]

1 SqlBulkCopy 只有sql才可用的
2 本想将多个insert语句 拼在一起执行,无奈access不支持
2 只有用 sqldataadapter.update(dataset,tablename); 这个了
代码如下,执行后未插入数据, 已知 dt中有数据的

                OleDbDataAdapter adapt = new OleDbDataAdapter("select id,ItemGuid,StartTime from " + tableName, conn);
                adapt.Fill(dt);
                var cmd = new OleDbCommand("insert into " + tableName + "(ID,ItemGuid,StartTime) values(@id,@ItemGuid,@StartTime)", conn);
                cmd.Parameters.Add("@Id", OleDbType.Integer, 4, "Id");
                cmd.Parameters.Add("@ItemGuid", OleDbType.VarChar, 40, "itemGuid");
                cmd.Parameters.Add("@StartTime", OleDbType.VarChar, 20, "StartTime");
                adapt.InsertCommand = cmd;


                if (dt != null)
                {
                    try
                    {
                        adapt.Update(dt);
                    }
                    catch (Exception e)
                    {
                        return e.Message;
                    }
                }

[孟子E章]

方法
http://dotnet.aspx.cc/file/Insert-DataSet-Into-Access.aspx

[江南野鹤]

来回答啊

[江南野鹤]

[Quote=引用 2 楼 的回复:]
方法
http://dotnet.aspx.cc/file/Insert-DataSet-Into-Access.aspx
[/Quote]

看了,我的问题在哪儿啊?

[孟子E章]

你的新数据呢？

OleDbDataAdapter adapt = new OleDbDataAdapter("select id,ItemGuid,StartTime from " + tableName, conn);
adapt.Fill(dt);
var cmd = new OleDbCommand("insert into " + tableName + "(ID,ItemGuid,StartTime) values(@id,@ItemGuid,@StartTime)", conn);
cmd.Parameters.Add("@Id", OleDbType.Integer, 4, "Id");
cmd.Parameters.Add("@ItemGuid", OleDbType.VarChar, 40, "itemGuid");
cmd.Parameters.Add("@StartTime", OleDbType.VarChar, 20, "StartTime");
adapt.InsertCommand = cmd;    
dt.Rows.Add(new object[]{1,"AA","BB"});
adapt.Update(dt);

[江南野鹤]

dt就是新数据啊.
我就想把dt插入到表中.

你的代码能执行成功吗? 第二种方法我怎么看着晕呢?
[Quote=引用 5 楼 的回复:]
你的新数据呢？


C# code

OleDbDataAdapter adapt = new OleDbDataAdapter("select id,ItemGuid,StartTime from " + tableName, conn);
adapt.Fill(dt);
var cmd = new OleDbCommand("insert into " + tableName +……
[/Quote]

[孟子E章]

如果id是自动增长的主键，你应该去掉
OleDbDataAdapter adapt = new OleDbDataAdapter("select ItemGuid,StartTime from " + tableName, conn);
adapt.Fill(dt);
var cmd = new OleDbCommand("insert into " + tableName + "(ItemGuid,StartTime) values(@ItemGuid,@StartTime)", conn);
cmd.Parameters.Add("@ItemGuid", OleDbType.VarChar, 40, "itemGuid");
cmd.Parameters.Add("@StartTime", OleDbType.VarChar, 20, "StartTime");
adapt.InsertCommand = cmd;
dt.Rows.Add(new object[] { "AA", "BB3" });
adapt.Update(dt);

[孟子E章]

你是同一个表，插入的是新数据。旧的数据是不插入的

[江南野鹤]

System.Data.DataSet ds = new System.Data.DataSet();
    System.Data.DataTable dataTable1;
    System.Data.DataRow dr;

    String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|aspxWeb2.mdb";
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
      OleDbDataAdapter adapter = new OleDbDataAdapter("select id, [Title],[Description] from TestTable", connection);
      adapter.Fill(ds, "TestTable");
      dataTable1 = ds.Tables["TestTable"];
//以下的 这个 table 是什么??
      DataTableMapping dtmTable = new DataTableMapping("Table", "TestTable");
      adapter.TableMappings.Add(dtmTable);
      OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
      //以下这段 是将给dataTable1 插入数据,可是下面也没用到啊?
      for (int i = 0; i < 8; i++)
      {
        dr = dataTable1.NewRow();
        dr["Title"] = "【孟子E章】" + i.ToString();
        dr["Description"] = "【孟子E章】" + i.ToString();
        dataTable1.Rows.Add(dr);
      }
      adapter.Update(ds);
    }

[江南野鹤]

dt是从sql读取的数据,要往access里插入啊. id不是自增长的. 对于access.dt里的数据就是新数据啊


[Quote=引用 8 楼 的回复:]
你是同一个表，插入的是新数据。旧的数据是不插入的
[/Quote]

[江南野鹤]

还有问题就是 ,dt里的列数要大于 access中表的列数,这个该怎么搞?

[孟子E章]

多字段，我给你的例子中都有了啊，你都不看也没办法了。测试代码

System.Data.DataTable dataTable1 = new System.Data.DataTable("TestTableXXXXXXXXXXXXXXXXXX");
System.Data.DataRow dr;
dataTable1.Columns.Add(new System.Data.DataColumn("UserId", typeof(System.Int32)));
dataTable1.Columns.Add(new System.Data.DataColumn("Title", typeof(System.String)));
dataTable1.Columns.Add(new System.Data.DataColumn("Description1", typeof(System.String)));
dataTable1.Columns.Add(new System.Data.DataColumn("Description2", typeof(System.String)));
dataTable1.Columns.Add(new System.Data.DataColumn("Description3", typeof(System.String)));
dataTable1.Columns.Add(new System.Data.DataColumn("Description4", typeof(System.String)));
dataTable1.Columns[0].AutoIncrement = true;
dataTable1.PrimaryKey = new System.Data.DataColumn[] { dataTable1.Columns["UserId"] };

//生成示例数据
for (int i = 0; i < 8; i++)
{
  dr = dataTable1.NewRow();
  dr["Title"] = "【孟子E章】" + i.ToString();
  dr["Description1"] = "【1孟子E章】" + i.ToString();
  dr["Description2"] = "【2孟子E章】" + i.ToString();
  dr["Description3"] = "【3孟子E章】" + i.ToString();
  dr["Description4"] = "【4孟子E章】" + i.ToString();
  dataTable1.Rows.Add(dr);
}


String tableName = "TestTable";
String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|aspxWeb.mdb;";
OleDbConnection conn = new OleDbConnection(connectionString);

OleDbDataAdapter adapt = new OleDbDataAdapter();
var cmd = new OleDbCommand("insert into " + tableName + "(id,ItemGuid,StartTime) values(@id,@ItemGuid,@StartTime)", conn);
cmd.Parameters.Add("@id", OleDbType.Integer, 40, "UserId");
cmd.Parameters.Add("@ItemGuid", OleDbType.VarChar, 40, "Title");
cmd.Parameters.Add("@StartTime", OleDbType.VarChar, 20, "Description3");
adapt.InsertCommand = cmd;
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapt);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
adapt.Update(dataTable1);

[江南野鹤]

搞定了 谢谢老大!
需要先将 dt转成一个新表!

[江南野鹤]

OleDbCommandBuilder builder = new OleDbCommandBuilder(adapt);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";

builder 虽然定义了,下面未用到啊!

[zzh_my]

不错。。借鉴了。。。