Recent twitter entries...

Bind a Dataset to a Listbox using OleDbDataAdapter

Posted by Brad | Posted in C# | Posted on 06-07-2011

Need to bind a web control to an OleDb data source in your C# web application, here’s the magic. In this case I am binding a ListBox control with an ID of ListBox1 to the ProductName field in the Products table of our old friend the Northwind database.

These are the using statements that you need to apply including the ConfigurationManager to get your web.config connection string conveniently

using System.Data;
using System.Data.OleDb;
using System.Configuration;

Here is our class that does all the work

protected void DataSetFromOleDbDemo()
    {
      string strSQL = "SELECT * FROM Products WHERE CategoryID = 1";
 
      var conString = ConfigurationManager.ConnectionStrings["Northwind"];
      string Conn = conString.ConnectionString;
 
      try
      {
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(strSQL, Conn))
        {
          DataSet ds = new DataSet();
          adapter.Fill(ds, "ProductInfo");
 
          foreach (DataRow dr in ds.Tables["ProductInfo"].Rows)
          {
            ListBox1.Items.Add(dr["ProductName"].ToString());
          }
        }
      }
      catch (Exception)
      {
 
        throw;
      }
    }

And your OleDb connection string to put in the connectionStrings section of your web.config is as follows

<connectionStrings>
    <add name="Northwind" connectionString="Provider=SQLOLEDB;server=YOURSERVER;database=Northwind;uid=USERNAME;password=PASSWORD;" />
</connectionStrings>

 

 

Continue Reading

Binding a GridView in C# with a parameterized command

Posted by Brad | Posted in C# | Posted on 14-07-2010

With two controls on your asp.net page a DropDownList (ID ddlDropDownList) and a GridView (ID gvGridView), to retrieve the SelectedValue from the DropDownList and use that in the where clause of the query used to bind data to your GridView the following will help.

Required using statements

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

ADO.NET block of code to base your bind on using an SQLDataAdapter so you can still have paging on the GridView.

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNSTRINGNAME"].ConnectionString))
    {
      using (SqlCommand cmdGrid = new SqlCommand("SELECT * FROM TABLE WHERE FIELD = @PARAMETER", con))
      {
      cmdGrid.Parameters.AddWithValue("@PARAMETER", ddlDropDownList.SelectedValue);
      con.Open();
 
      using (SqlDataAdapter da = new SqlDataAdapter(cmdGrid))
      {
        DataTable dt = new DataTable();
        da.Fill(dt);           
        gvGridView.DataSource = dt;
        gvGridView.DataBind();
      }
      }
    }

The structure of the code is with using statments to ensure the garbage collection is carried out efficiently.

Continue Reading

Databinding a GridView in C# with ADO.NET

Posted by Brad | Posted in C# | Posted on 13-07-2010

Something simple, you want to use ADO.NET to bind data from SQL Server to a GridView, here is your code behind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
public partial class Default2 : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    string conn = ConfigurationManager.ConnectionStrings["STRINGNAME"].ConnectionString;
    SqlConnection con = new SqlConnection(conn);
 
    using (con)
    {
      SqlCommand cmd = new SqlCommand("SELECT * FROM Table", con);
      con.Open();
      SqlDataReader rdr = cmd.ExecuteReader();
      gv.DataSource = rdr;
      gv.DataBind();
    }
  }
}

Here is your control

<asp:GridView ID="gv" runat="server" />

Continue Reading

How to store and retrieve information using web.config

Posted by Brad | Posted in C# | Posted on 30-06-2010

So you like the idea of storing variables centrally, and want to know how to implement that in your c# web site.

Simply add the following node to your web.config if it does not already exist

<configuration>
  <appSettings>
    <add key="brad" value="this came from web.config"/>
  </appSettings>
</configuration>

Then import the Configuration namespace at the top of your codebehind with

using System.Web.Configuration;

Finally in your webform or codebehind file to retrieve the stored value from the key/value pair stored in web.config simply use this statement

yourVariable = WebConfigurationManager.AppSettings["Brad"];

Continue Reading