Below are some examples for Working with SQL and Oracle connection to bind data to dropdown and retreive values from datatable in asp.net.
Bind output of Oracle query to dropdown
OracleConnection conn = new OracleConnection();conn.ConnectionString = ConfigurationManager.ConnectionStrings["Conne
tionString"].ConnectionString;
OracleCommand cmd = new OracleCommand("SELECT EMPLOYEE_ID ID, EMPLOYEE_NAME VALUE FROM EMPLOYEE MASTER",
conn);
OracleDataAdapter oda = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
oda.Fill(ds);
Dropdown.DataSource = ds;
Dropdown.DataTextField = "VALUE";
Dropdown.DataValueField = "ID";
Dropdown.DataBind();
Retrieving Datatable as an output from Oracle query
protected virtual DataTable ConnectAndQuery()
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
conn.Open();
OracleCommand command = conn.CreateCommand();
string sql = "SELECT EMPLOYEE_ID ID, EMPLOYEE_NAME VALUE FROM EMPLOYEE MASTER";
command.CommandText = sql;
OracleDataReader reader = command.ExecuteReader();
DataTable dtusertables = new DataTable();
dtusertables.Load(reader);
if (null != reader && !reader.IsClosed)
{
reader.Close();
}
return dtusertables;
}
Bind output of Sql query to dropdown
SqlConnection conn = new SqlConnection("ConnectionString");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT EMPLOYEE_ID ID, EMPLOYEE_NAME VALUE FROM EMPLOYEE MASTER";
cmd.Connection = conn;
conn.Open();
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
conn.Close();
Dropdown.DataSource = dt;
Dropdown.DataTextField = "VALUE";
Dropdown.DataValueField = "ID";
Dropdown.DataBind();
Retrieving Datatable as an output from Sql query
protected virtual DataTable ConnectAndQuery()
{
string sql = "SELECT EMPLOYEE_ID ID, EMPLOYEE_NAME VALUE FROM EMPLOYEE MASTER";
using (SqlConnection conn = new SqlConnection("ConnectionString"))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(reader);
conn.Close();
return dt;
}
}
}