C# datatable 插入数据库

Question

 string sql1 = "select userid,result,type from tmp_vss_yc_result";
                                        DataSet data_tmp = new DataSet();
                                        OracleCommand cmdd = new OracleCommand(sql1, connection);
                                        OracleDataAdapter da5 = new OracleDataAdapter(cmdd);                                        OracleCommandBuilder cb = new OracleCommandBuilder(da5);
                                        data_tmp = ds;                                        da5.Update(data_tmp.Tables[0]);PS： ds 是一个结构跟 sql1 完全一样的 ，执行不报错 就是插入不到数据库。
     tmp_vss_yc_result 没有主键

Answer 1

OracleCommandBuilder cb = new OracleCommandBuilder(da5);
这个貌似会自动创建 插入 编辑 删除等

Answer 2

OracleCommandBuilder 是可以处理CRUD操作，但是你的是查询操作，你怎么插入数据库？？？CRUD：
Create     创建-------------->insert语句
Read       读取-------------->select语句
Update   更新-------------->update语句
Delete    删除-------------->delete语句你如下代码，是读取选择操作，红色标记部分。
string sql1 = "select userid,result,type from tmp_vss_yc_result";
  DataSet data_tmp = new DataSet();
  OracleCommand cmdd = new OracleCommand(sql1, connection);
  OracleDataAdapter da5 = new OracleDataAdapter(cmdd);  OracleCommandBuilder cb = new OracleCommandBuilder(da5);
  data_tmp = ds;  da5.Update(data_tmp.Tables[0]);

Answer 3

public static void Save(DataTable table, string tableName)
        {
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                SqlCommand cmd = new SqlCommand(string.Format("SELECT TOP 0 * FROM{0}", tableName));
                cmd.Connection = Connection;
                adapter.SelectCommand = cmd;
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                adapter.Fill(table);
                table.TableName = tableName;
                int num=adapter.Update(table);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
            finally
            {
                Connection.Close();
            }

Answer 4

string sql1 = "update tmp_vss_yc_result set type='测试' where userid=1";

Answer 5

5楼 您写的与我写的 区别在哪里？4楼 您看 5楼也是 ，我看网上说 是不用写insert的

Answer 6

应该是没有主键的问题，需要手动写好sql更新语句。

Answer 7

5楼的那个是从选择另一张表中读取的数据赋给DataSet，然后将这个DataSet更新到另一张表中。其实这个跟如下的SQL语句类似。insert into 表一(字段一,字段二,...) select 字段一,字段二,... from 表二5楼的那个方法的两个参数(DataTable table, string tableName)
第一个参数相当于 表二
第二个参数相当于 表一

Answer 8

data_tmp = ds;//将ds的值赋给data_tmp?
还是ds=data_tmp?

Answer 9

我想实现的就是 这个效果啊我的ds 就是从另一个表读取的数据 我赋值给 ds_tmp 了 然后 把 ds_tmp 插入到 tmp_vss_yc_result 表中。我觉得有可能是没有主键的问题 。我试试 写明insert吧

Answer 10

我看LZ的意思应该是把data_tmp的值给ds吧
那么就是ds=data_tmp了

Answer 11

 DataTable dt = ds.Tables[0].Copy();
                                        DataColumn column = new DataColumn();
                                        column.DataType = System.Type.GetType("System.Int32");
                                        column.ColumnName = "id";
                                        column.AutoIncrement = true;
                                        column.AutoIncrementSeed = 1;
                                        column.AutoIncrementStep = 1;                                        dt.Columns.Add(column);
                                        dt.Clear();
                                        for (int y = 0; y < ds.Tables[0].Rows.Count; y++)
                                        {
                                            
                                                dt.ImportRow(ds.Tables[0].Rows[y]);
                                                                                   }
                                        string sql1 = "select id,userid,result,type from tmp_vss_yc_result";
                                        DataSet data_tmp = new DataSet();
                                        OracleCommand cmdd = new OracleCommand(sql1, connection);
                                        OracleDataAdapter da5 = new OracleDataAdapter(cmdd);
                                        da5.Fill(data_tmp,"tmp");
                                        OracleCommandBuilder cb = new OracleCommandBuilder(da5);
                                                                                da5.Update(dt);
我新建一个table 加了一个自增列 copy了 ds的结构 
  然后数据库表 加了一个主键 id还是不行我的数据库表是空的 

Answer 12

public void update_table(dataset ds)
{
  DataTable dt = ds.Tables[0].Copy();
                                        DataColumn column = new DataColumn();
                                        column.DataType = System.Type.GetType("System.Int32");
                                        column.ColumnName = "id";
                                        column.AutoIncrement = true;
                                        column.AutoIncrementSeed = 1;
                                        column.AutoIncrementStep = 1;                                        dt.Columns.Add(column);
                                        dt.Clear();
                                        for (int y = 0; y < ds.Tables[0].Rows.Count; y++)
                                        {
                                            
                                                dt.ImportRow(ds.Tables[0].Rows[y]);
                                                                                   }
                                        string sql1 = "select id,userid,result,type from tmp_vss_yc_result";
                                        DataTable data_tmp = dt;
                                        OracleCommand cmdd = new OracleCommand(sql1, connection);
                                        OracleDataAdapter da5 = new OracleDataAdapter(cmdd);
                                        
                                        OracleCommandBuilder cb = new OracleCommandBuilder(da5);
                                                                               int a=da5.Update(data_tmp);
}

Answer 13

Help you top the question!

Answer 14

我用一种笨办法 结果还是不行 崩溃了                                    if (ds.Tables[0].Rows.Count > 0)
                                    {
                                        StringBuilder a = new StringBuilder();
                                        for (int o = 0; o < ds.Tables[0].Rows.Count; o++)
                                        {
                                            string sqlstr = "insert into vss_yc_result (id,userid,type,result,status) values (vss_id.nextval," + ds.Tables[0].Rows[o][0].ToString() + ",'" + ds.Tables[0].Rows[o][2].ToString() + "','" + ds.Tables[0].Rows[o][1].ToString() + "',0);";
                                            a.Append(sqlstr);
                                        }
                                        OracleCommand cmd = new OracleCommand(a.ToString(), connection);
                                        cmd.ExecuteNonQuery();
                                    }
生成的 sql 我在 oracle里面直接运行没问题，但是 在代码里面用ExecuteNonQuery就报错 shit

Answer 15

if (ds.Tables[0].Rows.Count > 0)
  {
  StringBuilder a = new StringBuilder();
  for (int o = 0; o < ds.Tables[0].Rows.Count; o++)
  {
  string sqlstr = "insert into vss_yc_result (id,userid,type,result,status) values (vss_id.nextval," + ds.Tables[0].Rows[o][0].ToString() + ",'" + ds.Tables[0].Rows[o][2].ToString() + "','" + ds.Tables[0].Rows[o][1].ToString() + "',0);";
  a.Append(sqlstr);
  }
  OracleCommand cmd = new OracleCommand(a.ToString(), connection);
  cmd.ExecuteNonQuery();
  }红色标记部分是错的。还有一个问题就是，及循环ds.Tables[0].Rows.Count次，此时你的a.ToString()是一对SQL语句。
执行多条语句，最好用事务来操作。还是在你基础上改吧if (ds.Tables[0].Rows.Count > 0)
{
    for (int o = 0; o < ds.Tables[0].Rows.Count; o++)
    {
         string sqlstr = "insert into vss_yc_result (id,userid,type,result,status) values("+vss_id.nextval+"," + ds.Tables[0].Rows[o][0].ToString() + ",'" + ds.Tables[0].Rows[o][2].ToString() + "','" + ds.Tables[0].Rows[o][1].ToString() + "',0);";
         OracleCommand cmd = new OracleCommand(sqlstr, connection);
         cmd.ExecuteNonQuery();
     }
}

Answer 16

http://blog.csdn.net/keenweiwei/article/details/6869465