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

Hello,

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.

Thanks.
adskarcoxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0
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).
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
0
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?
0
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.

http://www.akadia.com/services/dotnet_data_reader.html

you will have something like this assuming labels:

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

Could you expand upon this example?

Thanks.
0
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:

http://docs.oracle.com/cd/B28359_01/win.111/b28375/OracleDataReaderClass.htm
0
slightwv (䄆 Netminder) 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');
	commit;

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" %>

<html>
<title>Gridview Sample</title>

<body>

<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;
	cmd.CommandText = "SELECT ZIR_EMAIL As Email, EMP_DEPARTMENT As Dept FROM MYTABLE WHERE UPPER(UNAME) = :username ";

	OracleDataReader odr;
	Username = "A";

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

	try {
		con.Open();
		odr = cmd.ExecuteReader();

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



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

	} finally {
		con.Close();
		cmd.Dispose();
	}

}


</script>

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

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.