用于填充表的数据适配器(as400)

Question

首先为我蹩脚的英语道歉。

现在关于这个问题，我有一个数据集包含2个表，每个表有超过100个字段有没有一种方法来插入所有的数据表上的数据而不必分配每个字段?

我听说数据适配器可以完成这一点，但由于源是另一个数据，我一直无法存档，或者我被困为每个字段分配一个变量。

目前，我正在强映射源到插入。

        try
        {
            connection.Open();
            DateTime fecha1 = Convert.ToDateTime(ds.Tables[0].Rows[0][4].ToString());
            DateTime fecha2 = Convert.ToDateTime(ds.Tables[0].Rows[0][12].ToString());
            DateTime fecha3 = Convert.ToDateTime(ds.Tables[0].Rows[0][61].ToString());
            query = "INSERT INTO HDZSNC.RELTRM VALUES ('" + ds.Tables[0].Rows[0][0] + "','"
            + ds.Tables[0].Rows[0][1] + "'," + ds.Tables[0].Rows[0][2] + ",'" + ds.Tables[0].Rows[0][3] + "',"
            + String.Format("{0:yyyyddMM}", fecha1) + "," + ds.Tables[0].Rows[0][5] + ",'" + ds.Tables[0].Rows[0][6] + "',"
            + ds.Tables[0].Rows[0][7] + "," + ds.Tables[0].Rows[0][8] + "," + ds.Tables[0].Rows[0][9] + ","
            + ds.Tables[0].Rows[0][10] + "," + ds.Tables[0].Rows[0][11] + "," + String.Format("{0:yyyyddMM}", fecha2) + ","
            + ds.Tables[0].Rows[0][13] + "," + ds.Tables[0].Rows[0][14] + "," + ds.Tables[0].Rows[0][15] + ","
            + ds.Tables[0].Rows[0][16] + "," + ds.Tables[0].Rows[0][17] + "," + ds.Tables[0].Rows[0][18] + ","
            + ds.Tables[0].Rows[0][19] + "," + ds.Tables[0].Rows[0][20] + "," + ds.Tables[0].Rows[0][21] + ","
            + ds.Tables[0].Rows[0][22] + "," + ds.Tables[0].Rows[0][23] + "," + ds.Tables[0].Rows[0][24] + ","
            + ds.Tables[0].Rows[0][25] + "," + ds.Tables[0].Rows[0][26] + "," + ds.Tables[0].Rows[0][27] + ","
            + ds.Tables[0].Rows[0][28] + "," + ds.Tables[0].Rows[0][29] + "," + ds.Tables[0].Rows[0][30] + ","
            + ds.Tables[0].Rows[0][31] + "," + ds.Tables[0].Rows[0][32] + "," + ds.Tables[0].Rows[0][33] + ","
            + ds.Tables[0].Rows[0][34] + "," + ds.Tables[0].Rows[0][35] + "," + ds.Tables[0].Rows[0][36] + ","
            + ds.Tables[0].Rows[0][37] + "," + ds.Tables[0].Rows[0][38] + "," + ds.Tables[0].Rows[0][39] + ","
            + ds.Tables[0].Rows[0][40] + "," + ds.Tables[0].Rows[0][41] + "," + ds.Tables[0].Rows[0][42] + ","
            + ds.Tables[0].Rows[0][43] + "," + ds.Tables[0].Rows[0][44] + "," + ds.Tables[0].Rows[0][45] + ","
            + ds.Tables[0].Rows[0][46] + "," + ds.Tables[0].Rows[0][47] + "," + ds.Tables[0].Rows[0][48] + ","
            + ds.Tables[0].Rows[0][49] + "," + ds.Tables[0].Rows[0][50] + "," + ds.Tables[0].Rows[0][51] + ","
            + ds.Tables[0].Rows[0][52] + "," + ds.Tables[0].Rows[0][53] + "," + ds.Tables[0].Rows[0][54] + ","
            + ds.Tables[0].Rows[0][55] + "," + ds.Tables[0].Rows[0][56] + "," + ds.Tables[0].Rows[0][57] + ","
            + ds.Tables[0].Rows[0][58] + "," + ds.Tables[0].Rows[0][59] + "," + ds.Tables[0].Rows[0][60] + ","
            + String.Format("{0:yyyyddMM}", fecha3) + "," + ds.Tables[0].Rows[0][62] + "," + ds.Tables[0].Rows[0][63] + ","
            + ds.Tables[0].Rows[0][64] + "," + ds.Tables[0].Rows[0][65] + "," + ds.Tables[0].Rows[0][66] + ","
            + ds.Tables[0].Rows[0][67] + "," + ds.Tables[0].Rows[0][68] + "," + ds.Tables[0].Rows[0][69] + ","
            + ds.Tables[0].Rows[0][70] + "," + ds.Tables[0].Rows[0][71] + "," + ds.Tables[0].Rows[0][72] + ","
            + ds.Tables[0].Rows[0][73] + "," + ds.Tables[0].Rows[0][74] + "," + ds.Tables[0].Rows[0][75] + ","
            + ds.Tables[0].Rows[0][76] + "," + ds.Tables[0].Rows[0][77] + "," + ds.Tables[0].Rows[0][78] + ","
            + ds.Tables[0].Rows[0][79] + "," + ds.Tables[0].Rows[0][80] + "," + ds.Tables[0].Rows[0][81] + ","
            + ds.Tables[0].Rows[0][82] + "," + ds.Tables[0].Rows[0][83] + "," + ds.Tables[0].Rows[0][84] + ","
            + ds.Tables[0].Rows[0][85] + "," + ds.Tables[0].Rows[0][86] + "," + ds.Tables[0].Rows[0][87] + ","
            + ds.Tables[0].Rows[0][88] + "," + ds.Tables[0].Rows[0][89] + "," + ds.Tables[0].Rows[0][90] + ","
            + ds.Tables[0].Rows[0][91] + "," + ds.Tables[0].Rows[0][92] + "," + ds.Tables[0].Rows[0][93] + ","
            + ds.Tables[0].Rows[0][94] + "," + ds.Tables[0].Rows[0][95] + "," + ds.Tables[0].Rows[0][96] + ","
            + ds.Tables[0].Rows[0][97] + "," + ds.Tables[0].Rows[0][98] + "," + ds.Tables[0].Rows[0][99] + ","
            + ds.Tables[0].Rows[0][100] + "," + ds.Tables[0].Rows[0][101] + "," + ds.Tables[0].Rows[0][102] + ","
            + ds.Tables[0].Rows[0][103] + "," + ds.Tables[0].Rows[0][104] + "," + ds.Tables[0].Rows[0][105] + ","
            + ds.Tables[0].Rows[0][106] + "," + ds.Tables[0].Rows[0][107] + ")";
            oledbAdapter.InsertCommand = connection.CreateCommand();
            oledbAdapter.InsertCommand.CommandText = query;
            oledbAdapter.InsertCommand.ExecuteNonQuery();
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("No se puede abrir la coneccion a 400 " + ex, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return;
        }

编辑:源是一个访问文件，我试图将数据复制到as400数据库，现在我错过了如何迭代所有的数据表。

Answer 1

我不知道@ValiRossi的解决方案;我从来没有那样做过。但是我不会这样创建你的INSERT声明。这可能导致SQL注入问题。

应该参数化。不幸的是，我的好例子在工作中，但它看起来像:

    public void CreateNewLeaveRequestDate(DayRequested dayRequested)
    {
        /*
            INSERT INTO MPRLRREQDP 
            (REQUEST_ID, DATE_OF_LEAVE, TIME_OF_LEAVE, HOURS_REQUESTED, REQUEST_TYPE, RELATIONSHIP, NATURE_OF_ILLNESS, 
                ADDED_TO_TIMESHEET, EMPLOYEE_ID, TIMESHEET_CODE)
            VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)                                   
         */
        StringBuilder sb = new StringBuilder();
        sb.Append("INSERT INTO MPRLRREQDP ");
        sb.Append("(REQUEST_ID, DATE_OF_LEAVE, TIME_OF_LEAVE, HOURS_REQUESTED, REQUEST_TYPE, RELATIONSHIP, NATURE_OF_ILLNESS, ");
        sb.Append("ADDED_TO_TIMESHEET, EMPLOYEE_ID, TIMESHEET_CODE) ");
        sb.Append("VALUES(@REQUEST_ID, @DATE_OF_LEAVE, @TIME_OF_LEAVE, @HOURS_REQUESTED, @REQUEST_TYPE, @RELATIONSHIP, @NATURE_OF_ILLNESS, ");
        sb.Append("@ADDED_TO_TIMESHEET, @EMPLOYEE_ID, @TIMESHEET_CODE)");
        using (iDB2Connection conn = new iDB2Connection(ConfigurationManager.ConnectionStrings["IbmIConnectionString"].ConnectionString))
        {
            using (iDB2Command cmd = new iDB2Command(sb.ToString(), conn))
            {
                cmd.Parameters.Add("@REQUEST_ID", iDB2DbType.iDB2Decimal).Value = dayRequested.RequestId;
                cmd.Parameters.Add("@DATE_OF_LEAVE", iDB2DbType.iDB2Date).Value = Convert.ToDateTime(dayRequested.DateOfLeave).Date;
                cmd.Parameters.Add("@TIME_OF_LEAVE", iDB2DbType.iDB2Time).Value = Convert.ToDateTime(dayRequested.TimeOfLeave).ToString("HH.mm.ss");
                cmd.Parameters.Add("@HOURS_REQUESTED", iDB2DbType.iDB2Decimal).Value = dayRequested.HoursRequested;
                cmd.Parameters.Add("@REQUEST_TYPE", iDB2DbType.iDB2Decimal).Value = dayRequested.RequestType;
                cmd.Parameters.Add("@RELATIONSHIP", iDB2DbType.iDB2Char).Value = dayRequested.Relationship;
                cmd.Parameters.Add("@NATURE_OF_ILLNESS", iDB2DbType.iDB2Char).Value = dayRequested.NatureOfIllness;
                cmd.Parameters.Add("@ADDED_TO_TIMESHEET", iDB2DbType.iDB2Decimal).Value = false;
                cmd.Parameters.Add("@EMPLOYEE_ID", iDB2DbType.iDB2Decimal).Value = dayRequested.EmployeeId;
                cmd.Parameters.Add("@TIMESHEET_CODE", iDB2DbType.iDB2Char).Value = dayRequested.TimesheetCode;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

Answer 2

我不知道这是否有帮助，但我最近遇到了类似的问题。我从一种类型的数据库中获取数据，并希望将其插入到另一种数据库中。我需要的是datarow.setadded()方法，使行实际插入。

foreach (DataRow dr in data . rows){dr.SetAdded ();}

da.Update (dt);