The code below demonstrates the complete implementation of populating dropdownlist in c# using stored procedure.
Sample.aspx:
<form id="form1" runat="server">
<div>
<table align="center">
<tr>
<td class="sup_heading_text">
Select Data
</td>
<td>
<asp:DropDownList ID="ddlist" runat="server" Width="200px" CssClass="ddl">
</asp:DropDownList>
</td>
</tr>
</table>
</form>
Sample.aspx.cs: CODE BEHIND
using System;
using System.Linq;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Oracle.DataAccess.Types;
using Oracle.DataAccess.Client;
namespace DROPDOWNLIST
{
public partial class POPULATEDD : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillDropDownlist();
}
}
private void FillDropDownlist()
{
DataTable dt_ddl = OracleCon.DataTable_WITHSP("GET_DROPDOWNDATA", new object[] { "" });
ddlist.DataSource = dt_ddl;
ddlist.DataTextField = "COMPANYNAME";
ddlist.DataValueField = "POLICYID";
ddlist.DataBind();
ddlist.Items.Insert(0, "- SELECT -");
}
}
}
Web.Config:
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.0" />
</system.web>
<connectionStrings>
<add name="constr" connectionString="User Id=USERNAME;Password=PASSWORD;Data Source=MYDB"/>
</connectionStrings>
</configuration>
Connections.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Reflection;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace DROPDOWNLIST
{
public static class OracleCon
{
static OracleConnection con;
static OracleCommand cmd;
static OracleDataReader dr;
static OracleDataAdapter da;
static DataTable dt = new DataTable();
static DataSet ds=new DataSet();
static string Result = string.Empty;
private static void CreateConnection()
{
try
{
con = new OracleConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
cmd = new OracleCommand();
cmd.Connection = con;
}
catch (OracleException exe)
{
}
}
//The below method can be used to retrieve the datatable when passed the storedprocedure along with input parameters
public static DataTable DataTable_WITHSP(string Procedure_Name, object[] Parameters)
{
try
{
CreateConnection();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Procedure_Name;
OracleParameter[] Discoverd_Parameters = GetParametersNames(Procedure_Name);
AssignParameterValues(Discoverd_Parameters, Parameters);
foreach (OracleParameter p in Discoverd_Parameters)
{
//check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
cmd.Parameters.Add(p);
}
da = new OracleDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
}
catch (OracleException exe)
{
//do nothing
}
return dt;
}
public static void ExecuteQuery(string query)
{
try
{
CreateConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (OracleException exe)
{
}
}
public static DataTable ExecuteDataTable(string query)
{
try
{
CreateConnection();
cmd.CommandText = query;
da = new OracleDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
}
catch (OracleException exe)
{
}
return dt;
}
public static OracleDataReader ExecuteReader(string query)
{
try
{
CreateConnection();
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
con.Open();
dr = cmd.ExecuteReader();
return dr;
}
catch (OracleException exe)
{
return dr;
}
}
public static void Closeconnection()
{
if (con.State != ConnectionState.Closed)
{
con.Close();
con.Dispose();
}
}
public static OracleParameter[] GetParametersNames(string Procedure_Name)
{
OracleParameter[] discoveredParameters = null;
CreateConnection();
cmd.CommandText = Procedure_Name;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
// Deriving parameters
OracleCommandBuilder.DeriveParameters(cmd);
// View list of the returned parameters
discoveredParameters = new OracleParameter[cmd.Parameters.Count]; ;
cmd.Parameters.CopyTo(discoveredParameters, 0);
cmd.Parameters.Clear();
con.Close();
return discoveredParameters;
}
private static void AssignParameterValues(OracleParameter[] commandParameters, object[] parameterValues)
{
try
{
if ((commandParameters == null) || (parameterValues == null))
{
//do nothing if we get no data
return;
}
// we must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
}
//iterate through the OracleParameters, assigning the values from the corresponding position in the
//value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
if (parameterValues[i] != null && parameterValues[i].GetType().IsArray)
{
//For Handling BLOB Parameters
if (parameterValues[i].GetType().FullName.Equals("System.Byte[]"))
{
commandParameters[i].CollectionType = OracleCollectionType.None;
commandParameters[i].OracleDbType = OracleDbType.Blob;
commandParameters[i].Size = ((Array)parameterValues[i]).Length;
commandParameters[i].Value = parameterValues[i];
}
else
{
Array ar = (Array)parameterValues[i];
if (ar.Length > 0)
{
commandParameters[i].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
commandParameters[i].Size = ar.Length;
commandParameters[i].Value = parameterValues[i];
}
else
{
commandParameters[i].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
commandParameters[i].Size = 0;
commandParameters[i].ArrayBindStatus = new OracleParameterStatus[] { OracleParameterStatus.NullInsert };
string[] vals = { null };
commandParameters[i].Value = vals;
}
}
}
else
{
commandParameters[i].Value = parameterValues[i];
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
No comments:
Post a Comment