PL/SQL Example Code (C#.NET 4.0)


I'm from a C#.NET 4.0 + Linq background and I just started with PL/SQL.  I am trying to query a database and obtain two values which I can then use in my C# code.  

Here is the C# code I have so far:

string sqlstmt1 = "SELECT ZIR_EMAIL As Email, EMP_DEPARTMENT As Dept " +
                                        "FROM MYTABLE " +
                                         "WHERE " +
                                         "UPPER(UNAME) == " + UserName;

OracleConnection oraConn = new OracleConnection(ConfigurationManager.ConnectionStrings["CONN"].ConnectionString);
OracleCommand oraCmd = new OracleCommand(sqlstmt1, oraConn);
oraCmd.CommandType = CommandType.Text;

oraCmd.CommandTimeout = 300;
DataSet ds = new DataSet();
OracleDataAdapter oraDA = new OracleDataAdapter(oraCmd);
OracleCommandBuilder oraCB = new OracleCommandBuilder(oraDA);

I need to put the values of Email and Dept into C# string variables, but I am not certain how to accomplish this.

Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Here is a complete example.

Database setup:
	drop table mytable purge;
	create table mytable(uname char(1), ZIR_EMAIL char(1), emp_department char(1));
	insert into mytable values('A','1','2');
	insert into mytable values('B','3','4');

Open in new window

Stand-alone web page:
<%@ import namespace = "System" %>
<%@ import namespace = "System.Data" %>
<%@ import namespace = "Oracle.DataAccess.Client" %>
<%@ import namespace = "Oracle.DataAccess.Types" %>

<title>Gridview Sample</title>


<script language="c#" runat="server">

public void Page_Load(object sender, EventArgs e)
		Response.Write("Here: " + DateTime.Now.ToString());

	OracleConnection con = new OracleConnection("User Id=bud;Password=bud;Data Source=bud;");
	String Username;

	OracleCommand cmd = new OracleCommand();
	cmd.Connection = con;
	cmd.CommandType = CommandType.Text;

	OracleDataReader odr;
	Username = "A";

	OracleParameter param1 = cmd.Parameters.Add("username", OracleDbType.Varchar2, 50, Username, ParameterDirection.Input);

	try {
		odr = cmd.ExecuteReader();

		email.Text = odr[0].ToString();
		dept.Text = odr[1].ToString();

	} catch (Exception ex) {
		Response.Write("Error: " + ex.Message);

	} finally {



<form runat="server">
Email: <asp:label id="email" runat="server" />
Dept: <asp:label id="dept" runat="server" />

Open in new window

slightwv (䄆 Netminder) Commented:
Just like your last question:

PL/SQL is a procedureal language.  You are doing straight SQL.  There is no 'PL' in it.

A select statement will return many rows.  What object are you going to use to hold the multiple values?

Just like your last question, I suggest a gridview.  I even provided a working example, although in VB.Net.  Porting it should be pretty simple.
slightwv (䄆 Netminder) Commented:
>>   "UPPER(UNAME) == " + UserName;

Also, use BIND variables for this.  See my code in your other question.  Not using BIND variables opens you up to SQL injection and causes more work on the database (hard parsing).
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

adskarcoxAuthor Commented:
slightwv - thanks for the reply.  I understand the SQL part, I just need a code sample for the remainder.  Specifically, I need to know what code to add to what I already have (above) that will ultimately allow me to get the values of Email and Dept into C# variables.

Also, thanks for the heads up on using bind variables.
adskarcoxAuthor Commented:
Clarification: my SQL query will only return one row.  I need to put data from this returned row into C# variables for further processing.  Thanks.
slightwv (䄆 Netminder) Commented:
>>that will ultimately allow me to get the values of Email and Dept into C# variables.

To do what with them?

Say the table has 10 rows.  A variable can only store a single value.  What do you want to do with the other 9 values?
slightwv (䄆 Netminder) Commented:
>>Clarification: my SQL query will only return one row.

Use a data reader.  Call the read method then assign the label/textbox/variable values appropriately.

Look at the following example.  Just don't go into the while loop.

you will have something like this assuming labels:

label1.Text = rdr["Email"].ToString();
label2.Text = rdr["Dept"].ToString();
adskarcoxAuthor Commented:
Could someone provide source code that is compatible with Oracle (preferably using my code provided above)?
adskarcoxAuthor Commented:
slightwv - the example link you provided does not appear to be for Oracle.

Could you expand upon this example?

slightwv (䄆 Netminder) Commented:
>>Could you expand upon this example?

What is to expand?  Oracle has a datareader as well.  Check the docs for ODP.Net but the syntax is 99% exact.

Here is the doc link with basically the same example.  Just don't use the loop for a single row and use the code I posted above:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.