C# Creating and Populating an Oracle Database

Question

To start i don't have access to the database, we hit a problem with our university account and waiting for it to be reset. How ever i do have code that is giving me some problems and frankly i'm not sure i have even gone about this in the right way.

I am only trying to make a simple test program that creates a table, populates it then reads it.

If this isn't enough information i am sorry i should of waited but its bugging me getting these little errors in the code before i even get to a point i can compile to test tomorrow.

Here is the code to create the table, there does not seem to be any errors in the code

    static void buildTable()
    {
        try
        {                
            string sqlBuild = "CREATE TABLE Item ("
                               + "Item_ID VARCHAR2(1),"
                               + "Item_Name VARCHAR2(16),"
                               + "Price_Consumer VARCHAR(5)"
                               + " ); ";
            OracleCommand cmd = new OracleCommand(sqlBuild, con);
            Connect();
            cmd.ExecuteNonQuery();
            Close();
        }
        catch (Exception e)
        {

            Console.WriteLine(e.ToString());
        }
    }

Here is the code to populate the table and this is where i am getting errors.

        static void populateTable()
    {
        string[,] items;
        items = new string[5,3] { { "1", "Mozzarella", "9.99" }, { "2", "Peperoni", "12.99" }, { "3", "Meat Feast", "14.99" }, { "4", "Chicken Tikka", "12.99" }, { "5", "Spicy Vegetarian", "11.99" } };
        try
        {
            OracleCommand cmd = new OracleCommand();
            OracleDataReader r = cmd.ExecuteReader();
            r.Read();
            for (int i = 1; i < 6; i++)
            {
                for(int j = 1; j < 4; j++)
                {
                    OracleCommand comd = new OracleCommand();
                    comd.Connection = con;
                    comd.CommandText = "insert into Item(Item_ID, Item_Name, Price_Consumer) values(" items[i, j].ToString() + ", " + items[i, j].ToString() + ", " + items[i, j].ToString() ");";
                    Connect();
                    comd.ExecuteNonQuery();
                    Close();
                }                    
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
        }
    }
}

the errors are on the items[i,j], it tells me it expects an " ; "

lastly this is what im near 100% will work as this i have done in the past, but i have never tried to use c# to create or populate a table.

        static void itemList()
    {
        string s = "\n";
        try
        {
            Connect();
            OracleCommand cmd = new OracleCommand(sql, con);
            OracleDataReader r = cmd.ExecuteReader();
            r.Read();
            while (r.Read())
            {
                s = s + r["Item_ID"].ToString() + ", " + r["Item_Name"].ToString() + ", " + "£" +r["Price_Consumer"].ToString();

            }
            Close();
            Console.WriteLine(s);
        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
        }
    }

here is the addtional code that might be relevant

        static void Connect()
    {
        con = new OracleConnection();
        con.ConnectionString = "User Id=username;Password=password;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))(CONNECT_DATA=(SERVICE_NAME=SID)))";
        con.Open();
        Console.WriteLine("Connected to Oracle" + con.ServerVersion);
    }

    static void Close()
    {
        con.Close();
        con.Dispose();
    }

Declarations

    static OracleConnection con;
    static string sql = "select * from Item";

Answer 1

Your internal loop is not needed. When you try to insert a row, you don't call the insert one time for each column, but you call the insert one time for each row

Another problem are your columns of type VARCHAR, this means that you need to insert strings there, but you don't do it correctly. This could be solved putting single quotes around those string to be recognized as such by the database engine.

for (int i = 1; i < 6; i++)
{
    comd.CommandText = @"insert into Item(Item_ID, Item_Name, Price_Consumer) 
                       values('" + items[i, 0].ToString() + "', '" + 
                       items[i, 1].ToString() + "', '" + 
                       items[i, 2].ToString() '");";

However while this will work for your simple example, this is still wrong. You should never concatenate string to build an sql command. This leads to Sql Injection and to a parsing error if the value to insert contains a single quote.

The only correct way to do it is through a parameterized query as this

comd.CommandText = @"insert into Item(Item_ID, Item_Name, Price_Consumer) 
                               values(:ID, :Name, :Price)";

comd.Parameters.AddWithValue(":ID", items[i, 0].ToString());
comd.Parameters.AddWithValue(":Name",items[i, 1].ToString());
comd.Parameters.AddWithValue(":Price",items[i, 2].ToString());

(As a side benefit, look at how the command is more understandable now)

Answer 2

This line is not well formatted and is missing two + symbols:

comd.CommandText = "insert into Item(Item_ID, Item_Name, Price_Consumer) values(" +
    items[i, j].ToString() + ", " + 
    items[i, j].ToString() + ", " + 
    items[i, j].ToString() + ");";

If you split it in a similar way to the above then it is easier to spot the errors

Answer 3

You are missing a '+' sign here