iDB2Commands in Visual Studio 2010

Question

These are the basic things I know about iDB2Commands to be used in Visual Studio 2010. Could you please help me how could I extract data from DB2? I know INSERT, DELETE and Record Count. But SELECT or Extract Data and UPDATE I don't know.

Imports IBM.Data.DB2
Imports IBM.Data.DB2.iSeries

Public conn As New iDB2Connection
Public str As String = "Datasource=10.0.1.11;UserID=edith;password=edith;DefaultCollection=impexplib"

Dim cmdUpdate As New iDB2Command
Dim sqlUpdate As String

conn = New iDB2Connection(str)
conn.Open()

'*****Delete Records and working fine
sqlUpdate = "DELETE FROM expusers WHERE username<>@username"
cmdUpdate.Parameters.Add("username", iDB2DbType.iDB2Date)
cmdUpdate.Parameters("username").Value = ""

'*****Insert Records and working fine
sqlUpdate = "INSERT INTO expusers (username, password, fullname) VALUES (@username, @password, @fullname)"

cmdUpdate.Parameters.Add("username", iDB2DbType.iDB2VarChar)
cmdUpdate.Parameters.Add("password", iDB2DbType.iDB2VarChar)
cmdUpdate.Parameters.Add("fullname", iDB2DbType.iDB2VarChar)

cmdUpdate.Parameters("username").Value = txtUsername.Text
cmdUpdate.Parameters("password").Value = txtPassword.Text
cmdUpdate.Parameters("fullname").Value = "Editha D. Gacusana"

'*****Count Total Records and working fine
Dim sqlCount As String
Dim cmd As New iDB2Command

sqlCount = "SELECT COUNT(*) AS count FROM import"
cmd = New iDB2Command(Sql, conn)

Dim count As Integer
count = Convert.ToInt32(cmd.ExecuteScalar)

'*****Update Records and IT IS NOT WORKING AT ALL
sqlUpdate = "UPDATE expusers SET password = @password WHERE RECNO = @recno"

cmdUpdate.Parameters.Add("recno", iDB2DbType.iDB2Integer)
cmdUpdate.Parameters.Add("password", iDB2DbType.iDB2VarChar)

cmdUpdate.Parameters("recno").Value = 61
cmdUpdate.Parameters("password").Value = txtPassword.Text

cmdUpdate.Connection = conn
cmdUpdate.CommandText = sqlUpdate
cmdUpdate.ExecuteNonQuery()
conn.Close()

Please help me how to code the SELECT query wherein I could extract/fetch data from DB2 Database. Also, how could i update the records in the database.

Thanks!

Answer 1

Instead of ExecuteNonQuery(), look at ExecuteReader(). I don't have VS2010 installed, but try something like this:

iDB2Command cmdSelect = new iDB2Command("SELECT username, password, fullname FROM expusers", conn);
cmdSelect.CommandTimeout = 0;

iDB2DataAdapter da = new iDB2DataAdapter(cmdSelect);

DataSet ds = new DataSet();

da.Fill(ds, "item_master");

GridView1.DataSource = ds.Tables["expusers"];
GridView1.DataBind();

Session["TaskTable"] = ds.Tables["expusers"];

da.Dispose();
cmdSelect.Dispose();

cn.Close();

See: http://gugiaji.wordpress.com/2011/12/29/connect-asp-net-to-db2-udb-for-iseries/

If you aren't trying to bind to a grid, look at iDB2Command.ExecuteReader() and iDB2DataReader()