Solved

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

Posted on 2012-03-12
10
780 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 77

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 77

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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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 77

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
 
LVL 77

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 77

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 77

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

751 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