CODEWITHSUNDEEP
c#datasetibm-midrange
Enrique
Enrique

Reputation: 153

dataadapter to fill a table(as400)

First sorry for my lousy english.

Now on the question, I have a dataset holding 2 tables, each table have over a hundread fields is there a way to insert all the data on the datatable without have to assign each field?.

I hear a dataadapter can acomplish this but since the source is another data, I have been unable to archive this, or I am stuck to asign each field with a variable?.

At the moment I am strong mapping the source to the insert.

        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;
        }

Edit: The source is a access file I am trying to copy the data to a as400 database, now I am missing how to iterate with all the datatable.

Upvotes: 0

Views: 836

Answers (2)

Mike Wills
Mike Wills

Reputation: 21255

I don't know about @ValiRossi's solution; I have never done that. But I would not create your INSERT statement like that. That could lead to SQL injection issues.

It should be parameterized. Unfortunately, my good examples are at work, but it looks something like:

    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();
            }
        }
    }

Upvotes: 2

ValiRossi
ValiRossi

Reputation: 219

I don,t know if this helps, but I had a similar issue recently. I took data from one type of database and wanted to insert it into another. What I needed was the datarow.setadded() method to make the rows actually insert.

foreach (DataRow dr in dt.Rows) { dr.SetAdded(); }

da.Update(dt);

Upvotes: 1

Related Questions