CODEWITHSUNDEEP
c#.netclass-design
Matthew Carr
Matthew Carr

Reputation: 105

How to pass a DataTable from a class to a page

I'm learning about classes in C# and am trying to build a "DataAccessClass". I have a working "OpenSqlConnection" void and am using it as a guide to build a "OpenSqlDatareader" void. I am attempting to consolidate all my database calls in this class. My boggle is how to pass a DataTable from the class to the aspx page to bind it with a ListView. I have been reading and trying for a day to make this work and cannot figure out what I am doing wrong...

Here's the Page_Load:

// --------------------------------------------------------------------------------
// Populate the Customers page.
// --------------------------------------------------------------------------------


// Define the query
string sqlQuery = " SELECT CustomerID, LastName + ', ' + FirstName AS CustomerName, Email, City, State, Phone"
                            + " FROM Customer"
                            + " ORDER BY LastName, FirstName";

string strErrorMessage = "";

if (DataAccessClass.OpenSqlConnection(out strErrorMessage) == false)
{
    string strErrorType = "Database Connection Error:";
    SendErrorMessageToClient(strErrorType, strErrorMessage);
}

else if (DataAccessClass.OpenSqlDatareader(sqlQuery, out dt, out strErrorMessage) == false)
{
    string strErrorType = "Datareader Error:";
    SendErrorMessageToClient(strErrorType, strErrorMessage);
}

else

{
    // Bind the Listview
    lvCustomers.DataSource = dt;
    lvCustomers.DataBind();
    dt.Dispose();
}

And here's the class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for DataAccessClass
/// </summary>
/// 
public class DataAccessClass
{
    public DataAccessClass()
{
    //
    // TODO: Add constructor logic here
    //
}



// -----------------------------------------------------------------------------------------
// Name: OpenSqlConnection
// Abstract: Open a connection to a SQL Server
// -----------------------------------------------------------------------------------------
public static bool OpenSqlConnection(out string strErrorMessage)
{

    SqlConnection theConnection = new SqlConnection();
    bool blnResult = false;
    string strConnectionString = ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
    strErrorMessage = "";

    if (theConnection == null)
        theConnection = new SqlConnection();

    try
    {
        switch (theConnection.State)
        {
            case ConnectionState.Broken:
                theConnection.Close();
                theConnection.ConnectionString = strConnectionString;
                theConnection.Open();
                blnResult = true;
                break;

            case ConnectionState.Closed:
                theConnection.ConnectionString = strConnectionString;
                theConnection.Open();
                blnResult = true;
                break;

            case ConnectionState.Open:
                blnResult = true;
                break;

            default:
                strErrorMessage = "Connection state is " + theConnection.State.ToString();
                break;
        }
    }
    catch (Exception excError)
    {
        strErrorMessage = excError.Message;
    }

    return blnResult;
}

// -----------------------------------------------------------------------------------------
// Name: OpenSqlDataReader
// Abstract: Open a SQL DataReader
// -----------------------------------------------------------------------------------------
public static bool OpenSqlDatareader(string sqlQuery, out dt, out string strErrorMessage)
{

    SqlConnection theConnection = new SqlConnection();

    bool blnResult = false;
    string strConnectionString = ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
    strErrorMessage = "";

    if (theConnection == null)
        theConnection = new SqlConnection();

    try
    {
        // Declare a SQL Adapter
        SqlDataAdapter da = new SqlDataAdapter(sqlQuery, theConnection);

        // Declare a DataTable
        DataTable dt = new DataTable();

        // Populate the DataTable
        da.Fill(dt);

        // Clean up.
        dt.Dispose();
        da.Dispose();
        theConnection.Close();
    }
    catch (Exception excError)
    {
        strErrorMessage = excError.Message;
    }

    return blnResult;
  }
}

The error message is: "The type or namespace name 'dt' could not be found (are you missing a using directive or an assembly reference?)" Any suggestions?

Upvotes: 2

Views: 631

Answers (3)

Damith
Damith

Reputation: 63065

before calling OpenSqlDatareader you need to declare dt

DataTable dt;// no need to assign 

....
else if (DataAccessClass.OpenSqlDatareader(sqlQuery, out dt, out strErrorMessage) == false)
{
    string strErrorType = "Datareader Error:";
    SendErrorMessageToClient(strErrorType, strErrorMessage);
}
...

change your signature of OpenSqlDatareader with out DataTable dt

public static bool OpenSqlDatareader(string sqlQuery, out DataTable dt, out string strErrorMessage)

inside the method 

// create new  DataTable
dt = new DataTable();

Upvotes: 0

MusiGenesis
MusiGenesis

Reputation: 75296

To add to the list of problems with this code (which you haven't discovered yet because you haven't gotten that far):

In your OpenSqlDataReader method, you need to instantiate your SqlConnection object using the connection string you've defined. Right now you're just creating it with the empty constructor, which means it will not function as an actual connection object.

Also, you'll need to explicitly call .Open() on your connection object. Otherwise, it won't be open and you'll get an exception thrown when you attempt to use it.

Upvotes: 1

Michael Minton
Michael Minton

Reputation: 4495

In Page_Load you'll also need to declare dt.

Upvotes: 0

Related Questions