Solved

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

Posted on 2012-03-12
10
775 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:adskarcox
  • 6
  • 4
10 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37711481
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37711487
>>   "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
 

Author Comment

by:adskarcox
ID: 37711508
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
 

Author Comment

by:adskarcox
ID: 37711512
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37711514
>>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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37711531
>>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
 

Author Comment

by:adskarcox
ID: 37711747
Could someone provide source code that is compatible with Oracle (preferably using my code provided above)?
0
 

Author Comment

by:adskarcox
ID: 37711998
slightwv - the example link you provided does not appear to be for Oracle.

Could you expand upon this example?

Thanks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37712573
>>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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37714605
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now