Solved

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

Posted on 2012-03-12
10
781 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

623 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