codenijuan codenijuan - 29 days ago 21
ASP.NET (C#) Question

asp.net C# Connection Class

hi guys i'm trying to create a connection class that can be used through out the entire project by just calling the object.

how can i add command parameter and how can i create a method for datareader?
this is my code:

/// <summary>
/// Summary description for AccessDb
/// </summary>
public class AccessDb
{
OleDbConnection con = new OleDbConnection();
public DataTable dataTable = new DataTable();

public AccessDb()
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
}

public DataTable execDataTable(string command)
{
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand(command, con);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dataTable);
da.Dispose();
}
catch (Exception ex)
{
HttpContext.Current.Response.Write("<script>alert('something wrong with the connection '"+ ex.Message +")</script>");
}
finally
{
con.Close();
}
return dataTable;
}

}


this is how i call the class method

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) {
AccessDb dbcon = new AccessDb();
DataTable dt = dbcon.execDataTable("SELECT * FROM AGESNews");

if (dbcon.dataTable.Rows.Count > 0) {
grd.DataSource = dt;
grd.DataBind();
}
}
}
}

Answer

You should make your ExecDataTable take an OleDbCommand

public DataTable ExecDataTable(OleDbCommand cmd)
{
    try
    {
        con.Open();
        if(cmd.Connection == null)
           cmd.Connection = con;

        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        da.Fill(dataTable);
        da.Dispose();
    }
    catch
    {
        throw;
    }
    finally 
    {
        con.Close();
    }

    return dataTable;
}

So how you will invoke this method.

OleDbCommand cmd = new OleDbCommand("SELECT * FROM AGESNews WHERE ID=@ID");
cmd.Parameters.AddWithValue("@ID", yourParameterIDValue);

DataTable dt = dbcon.execDataTable(cmd);
Comments