我把数据从sqlserver数据库取出来放到一个dataset里面了，怎么插入到Access数据库里面呢？

Question

我把数据从sqlserver数据库取出来放到一个dataset里面了，怎么插入到Access数据库里面呢？

Answer 1

SqlServer和Access两个数据表的结构一样

Answer 2

  void TestDataSetToAccess(DataSet ds)
        {
            try
            {
                string sql = "insert into tablename(字段名1,字段名2) values(@值1,@值2)";
                System.Data.OleDb.OleDbConnection conn = new OleDbConnection("connstr");
                conn.Open();
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(sql, oleConn);
                    cmd.Parameters.Add("@值1", ds.Tables[0].Rows[i][0]);
                    cmd.Parameters.Add("@值2", ds.Tables[0].Rows[i][2]);
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            catch (Exception e)
            {
                throw (e);
            }
        }

Answer 3

  void TestDataSetToAccess(DataSet ds)
        {
            try
            {
                string sql = "insert into tablename(";
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    if (i == 0)
                    {
                        sql += ds.Tables[0].Columns[i].ColumnName;
                    }
                    else
                    {
                        sql +=","+ ds.Tables[0].Columns[i].ColumnName;
                    }
                }
                sql += ")Values(";                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    if (i == 0)
                    {
                        sql +="@"+ ds.Tables[0].Columns[i].ColumnName;
                    }
                    else
                    {
                        sql += ",@" + ds.Tables[0].Columns[i].ColumnName;
                    }
                }
                sql+=")";                //"字段名1,字段名2) values(@值1,@值2)";
                System.Data.OleDb.OleDbConnection conn = new OleDbConnection("connstr");
                conn.Open();
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(sql, oleConn);
                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        cmd.Parameters.Add(new OleDbParameter("@" + ds.Tables[0].Columns[i].ColumnName, ds.Tables[0].Rows[i][j]));
                    }
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            catch (Exception e)
            {
                throw (e);
            }
        }

Answer 4

这个函数的确好用哦～～但是我的表有40个字段哦～～这样写有点太繁琐了
有没有简便一点的方法？或者有没有从sqlserver数据库表中取数据直接插入到access数据库表的方法？2个表的数据结构还是一样的