Object relations. OOP using ADO.NET

Question

0.00/5 (No votes)

See more:

Hi,
I am new to C# and I am trying to make a windows application. Three layer architecture I am trying to follow.I have two classes Product and Category.
Here is my code...............

C#

 public class Category
    {
        private int categoryID; 
        private string categoryCreatedDate;
        private string categoryName;
        private string categoryDescription;
      

        public int CategoryID
        {
            get { return categoryID; }
            set { categoryID = value; }
        }

        public string CategoryName
        {
            get { return categoryName; }
            set { categoryName = value; }
        }       

        public string CategoryDescription
        {
            get { return categoryDescription; }
            set { categoryDescription = value; }
        }    

        public string CategoryCreatedDate
        {
            get { return categoryCreatedDate; }
            set { categoryCreatedDate = value; }
        }

        public virtual Products Products
        {
            get;
            set;
        }
                          
    }

//----------------------
 public class Products
   {
       private int categoryID;
       private ProductStatus productStatus;
       public int CategoryID
       {
           get { return categoryID; }
           set { categoryID = value; }
       }
      
       private int productID;

       public int ProductID
       {
           get { return productID; }
           set { productID = value; }
       }
       private string productName;

       public string ProductName
       {
           get { return productName; }
           set { productName = value; }
       }
       private string productDescription;

       public string ProductDescription
       {
           get { return productDescription; }
           set { productDescription = value; }
       }
       private string productType;

       public string ProductType
       {
           get { return productType; }
           set { productType = value; }
       }
       private string productReason;

       public string ProductReason
       {
           get { return productReason; }
           set { productReason = value; }
       }
        

       public ProductStatus ProductStatus
       {
           get { return productStatus; }
           set { productStatus = value; }
       }


       public virtual Category Category
       {
           get;
           set;
       }
      
   }

C#

----------------------DAL/CategoryDB----------------
 public class CategoryDB
    {

        private OleDbCommand mycommand;
        private dbconnection getConnection;
        private List<category>  CategoryList; 

        public CategoryDB()
        {
            mycommand = new OleDbCommand();
            getConnection = new dbconnection();
            CategoryList = new List<category>();
        }

        public List<category> getCategory() 
        {
            OleDbDataReader reader;

            mycommand.Connection = getConnection.openConnection();
            mycommand.CommandText = "select * from Category";
            mycommand.ExecuteNonQuery();
            reader = mycommand.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Category categories = new Category();
                    Products products = new Products();
                    categories.CategoryName = reader["categoryName"].ToString();
                    categories.CategoryDescription = reader["categoryDescription"].ToString();
                    categories.CategoryCreatedDate = reader["categoryCreatedDate"].ToString();
                   
                  
                    CategoryList.Add(categories);
                }
             }

            return CategoryList;        
        }
    }

-----------------------DAL/ProductDB----------------------------

C#

public class ProductDB
    {
        private OleDbCommand mycommand;
        private dbconnection getConnection;
        private List<products> productList;

        public ProductDB()
        {
            mycommand = new OleDbCommand();
            getConnection = new dbconnection();
            productList = new List<products>();
        }

        public List<products> getProducts(Category category)
        {
            OleDbDataReader reader;

            mycommand.Connection = getConnection.openConnection();
            mycommand.CommandText = "select * from Product where categoryID like @categoryID";
            mycommand.Parameters.AddWithValue("@categoryID", category.CategoryID);
            mycommand.ExecuteNonQuery();
            reader = mycommand.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Products products_obj = new Products();
                    products_obj.ProductID = Convert.ToInt32(reader["productID"]);
                    products_obj.ProductDescription = reader["productDescription"].ToString();
                    products_obj.ProductName = reader["productName"].ToString();
                    products_obj.ProductType = reader["productType"].ToString();
                    products_obj.ProductReason = reader["productReason"].ToString();
                    products_obj.CategoryID =Convert.ToInt32( reader["categoryID"]);
                    productList.Add(products_obj);
                }
            }
           return productList;
        }
    }

--------------------------------Presentation Layer---------------
By doing this I can call each others methods

C#

List<products> product_list = new List<products>();
       List<category> category_list = new List<category>();

       ProductDB prodDB_obj = new ProductDB();
       Products product_obj = new Products();

Problem is this , how to related objects.
How I can create an object of Category and get all the products in it. as I am using Product property in Category class.
(That is if I give category name in a textbox , it should display all the products in that category in a datagrid)
Or the way I am doing is wrong.. please help. thanks in advance.

Posted 3-Jul-12 11:14am

Member 1565922

Updated 3-Jul-12 15:35pm

Rahul Rajat Singh

v2

Add a Solution

Comments

DamithSL 3-Jul-12 23:30pm

which version of .net framework are you using?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

2irfanshaikh · Accepted Answer · 2012-07-03T18:28:00

Solution 1

Make the Products property to return List of Products
i.e

C#

public virtual List<products> Products</products>

Change

C#

public List<products> getProducts(Category category)</products>

as static method and only allow to pass categoryID as parameter.
i.e.

C#

public static List<products> GetProducts(int categoryID)</products>

and just do the in Category class as

C#

public virtual List<products> Products
{
     get
     {
          return ProductDB.GetProducts(categoryID); 
          //This will return all the products associated with the category
     }
}

For binding the datagrid you have to create a new method in ProductDB class
i.e.

C#

public static List<products> GetProductsByCategoryName(string categoryName)

and your select query will be

SQL

select Product.* from Product INNER JOIN Category ON Category.CategoryID = Product.CategoryID WHERE 
CategoryName like @CategoryName

and code will be

C#

mycommand.Parameters.AddWithValue("@Categoryname", categoryName);

I hope this will help you.

Posted 3-Jul-12 18:28pm

2irfanshaikh

Updated 3-Jul-12 18:37pm

v4

Comments

Member 1565922 4-Jul-12 21:57pm

Thanks a lot. I have done same as you have explained, I am still getting some issues such as null pointer exception. I changed the function definition as you have explained.

public static List<Products> getProductsByCategoryName(string categoryName)
{

OleDbDataReader reader;

mycommand.Connection = getConnection.openConnection();
mycommand.CommandText = "select * from Product Inner Join Category on Product.productID=Category.categoryID where Category.categoryName like @categoryName";
mycommand.Parameters.AddWithValue("@categoryName", categoryName);
mycommand.ExecuteNonQuery();
reader = mycommand.ExecuteReader();

if (reader.HasRows)
{
while (reader.Read())
{
Products products_obj = new Products();
products_obj.ProductID = Convert.ToInt32(reader["productID"]);
products_obj.ProductDescription = reader["productDescription"].ToString();
products_obj.ProductName = reader["productName"].ToString();
products_obj.ProductType = reader["productType"].ToString();
products_obj.ProductReason = reader["productReason"].ToString();
productList.Add(products_obj);
}
}
reader.Close();
reader.Dispose();

return productList;
}
this is my database class
-----------------------dbConnectionClass--------------
public class dbconnection
{
private OleDbConnection myconnection;
private string dbProvider;
private string dbSource;

public dbconnection()
{
dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;";
dbSource = "Data Source=C:\\......\\Products2012.mdb";
myconnection = new OleDbConnection(dbProvider + dbSource);
}

public OleDbConnection openConnection()
{

if ((myconnection.State == ConnectionState.Broken) || (myconnection.State == ConnectionState.Closed))
{
this.myconnection.Open();
}
return this.myconnection;

}
public OleDbConnection closeConnection()
{
if (myconnection.State == ConnectionState.Open)
{
myconnection.Close();
myconnection.Dispose();
}
return myconnection;
}
-----------------------------

Method in CategoryDB is working . but still getting null exception in ProductDB class. I think it is something do with static and non-static property or function call. ( mycommand.Connection = getConnection.openConnection(); is showing null exception).
Please help...
Thanks in advance.

[no name] 5-Jul-12 0:36am

I think the instance of mycommand or getConnection is not created. That why, it is throwing null exception.
use these line of code

if(mycommand == null)
mycommand = new OleDbCommand();
if(getConnection == null)
getConnection = new dbconnection();

i think this will help you.

Member 1565922 5-Jul-12 9:53am

Thanks for the response.I got that working. I have changed the method as under...

public static List<Products> getProductsByCategoryName(string categoryName)
{

OleDbDataReader reader;
string dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;";
string dbSource = "Data Source=C:\\.......\\Products2012.mdb";
OleDbConnection myconnection = new OleDbConnection(dbProvider + dbSource);
myconnection.Open();
OleDbCommand mycmd = new OleDbCommand();
List<Products> productList2 = new List<Products>();

mycmd.Connection = myconnection;
mycmd.CommandText = "SELECT * FROM (Category INNER JOIN Product ON Category.categoryID = Product.categoryID) WHERE (Category.categoryName like @categoryName)";
mycmd.Parameters.AddWithValue("@categoryName", categoryName);
mycmd.ExecuteNonQuery();
reader = mycmd.ExecuteReader();

if (reader.HasRows)
{
while (reader.Read())
{
Products products_obj = new Products();
products_obj.ProductID = Convert.ToInt32(reader["productID"]);
products_obj.ProductDescription = reader["productDescription"].ToString();
products_obj.ProductName = reader["productName"].ToString();
products_obj.ProductType = reader["productType"].ToString();
products_obj.ProductReason = reader["productReason"].ToString();
productList2.Add(products_obj);
}
}
reader.Close();
reader.Dispose();

return productList2;
}

------
But doing like this I am not using my dbconnection class.I am also creating instance variables.Is there any other way of doing it.Just want to get the connection from another class. Thanks in advance. cheers

Member 1565922 5-Jul-12 14:24pm

I got that working. I have changed the method openConnection() into a static method. And now I am creating oledbCommand in each method.
The application works fine now. Thanks a lot.