creating a class that retrieves data from a table

Hello experts,
This is a very cold Sunday  - a day to stay indoors and ask for tips and solutions to my programming problems... and hopefully the experts are also staying home biding their time helping the likes of me...

I am using an unbound dataGridView which displays data retrieved from a table in an Sql database.

I use the code below to get the data from the table - it works fine.

My problem is that the code below resides on the form which has the dataGridView. In all other parts of the application I separated the Visual interface (the form) from code that saves and retrieve data in and out of the  database by using a class which handle these tasks.

I am not able to do so with this part because I am using a dataSet as the data source form my dataGridView. I am new to datasets and dataTable and more used to the dataReader.

I am hoping you will show me how to create a class with a method that allows the dataGridView (which is on the form calss) to use the dataSet which will be in the newly created class

Thanks
private void getDataForGrid()
        {
            SqlConnection con;
            con = new SqlConnection("Server=PC;Database=HelpDesk;integrated security=True");
            SqlCommand comm = new SqlCommand("select ID, dDate as Date, tTime as Time,Name,Test,Phone,Remarks from Appointments where dDate=@dDate", con);
            comm.Parameters.Add("dDate", SqlDbType.Date, 10, "dDate").Value = Convert.ToDateTime(this.txtDate.Text);
            SqlDataAdapter dataAdapter1 = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            dataAdapter1.Fill(ds, "Appointments");
            dataGridView1.DataSource = ds.Tables["Appointments"];

Open in new window

adamtraskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
You can certainly separate the logic out into another class. Why not have the function return either a DataSet or a DataTable back to your UI?

public static class DataAccessLayer
{
	public static DataTable getDataForGrid()
	{
		SqlConnection con;
		con = new SqlConnection("Server=PC;Database=HelpDesk;integrated security=True");
		SqlCommand comm = new SqlCommand("select ID, dDate as Date, tTime as Time,Name,Test,Phone,Remarks from Appointments where dDate=@dDate", con);
		comm.Parameters.Add("dDate", SqlDbType.Date, 10, "dDate").Value = Convert.ToDateTime(this.txtDate.Text);
		SqlDataAdapter dataAdapter1 = new SqlDataAdapter(comm);
		DataSet ds = new DataSet();
		dataAdapter1.Fill(ds, "Appointments");
		return ds.Tables["Appointments"];
	}
}

Open in new window


...and then you can call that function in your UI:

(Wherever you called getDataForGrid previously)
dataGridView1.DataSource = DataAccessLayer.getDataForGrid();

Open in new window

0
adamtraskAuthor Commented:
This is exactly what I am trying to do....

 I actually turned the above code into a function as part of a class. But I have no idea how to have the dataSet that's created in the class returned to the UI.

The code below shows how the class which is used to save, update and delete records in the database has the last part for retrieving data from the databse.

It is in the last part that I am stuck:
public void FetchData(DateTime myDate)


I don't know to connect the dataGridView in the UI to the dataSet in the class, or return the dataSet from the class to the UI where the dataGridView can use it
public class SaveGetAppointments
    {
        private Int32 _ID;
        private DateTime _dDate;
        private DateTime _tTime;
        private string _Name;
        private string _Test;
        private string _Phone;
        private string _Remarks;
        public Int32 ID
        {
            get { return _ID; }
            set { _ID = value; }
        }

        public DateTime dDate
        {
            get { return _dDate; }
            set { _dDate = value; }
        }
        public DateTime tTime
        {
            get { return _tTime; }
            set { _tTime = value; }
        }
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }
        public string Test
        {
            get { return _Test; }
            set { _Test = value; }
        }
        public string Phone
        {
            get { return _Phone; }
            set { _Phone = value; }
        }
        public string Remarks
        {
            get { return _Remarks; }
            set { _Remarks = value; }
        }
        public void SaveAppointmentsData(DateTime dD, DateTime dT, string N, string T, string Ph, string R)
        {
            SqlConnection con = default(SqlConnection);
            SqlCommand comm = default(SqlCommand);
            con = new SqlConnection("Server=PC;Database=HelpDesk;integrated security=True");
            // create the INSERT query for inserting the survey ID into the Questions Table
            string strQuery = "INSERT INTO Appointments(dDate,tTime,Name,Test,Phone,Remarks) VALUES (@dDate,@tTime,@Name,@Test,@Phone,@Remarks);";
            comm = new SqlCommand(strQuery, con);
            comm.Parameters.AddWithValue("@dDate", System.Data.SqlDbType.Date);//Date
            comm.Parameters["@dDate"].Value = dD.ToShortDateString();
            comm.Parameters.AddWithValue("@tTime", System.Data.SqlDbType.Time); //Time
            comm.Parameters["@tTime"].Value = dT.ToShortTimeString();
            comm.Parameters.AddWithValue("@Name", System.Data.SqlDbType.NVarChar);// Name
            comm.Parameters["@Name"].Value = N;
            comm.Parameters.AddWithValue("@Test", System.Data.SqlDbType.NVarChar);// Subject
            comm.Parameters["@Test"].Value = T;
            comm.Parameters.AddWithValue("@Phone", System.Data.SqlDbType.NVarChar); // Type
            comm.Parameters["@Phone"].Value = Ph;
            comm.Parameters.AddWithValue("@Remarks", System.Data.SqlDbType.NVarChar);// remarks
            comm.Parameters["@Remarks"].Value = R;
            try
            {
                con.Open();
                //  Execute the command
                comm.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }
        public void UpdateAppointmentsData(Int32 myID, DateTime dD, DateTime dT, string N, string T, string Ph, string R)
        {
            SqlConnection con = default(SqlConnection);
            SqlCommand comm = default(SqlCommand);
            con = new SqlConnection("Server=PC;Database=HelpDesk;integrated security=True");
            comm = new SqlCommand("UPDATE Appointments set dDate=@dDate, tTime=@tTime,Name=@Name,Test=@Test,Phone=@Phone,Remarks=@Remarks where ID=@ID", con);
            comm.Parameters.AddWithValue("@ID", System.Data.SqlDbType.Int);//ID
            comm.Parameters["@ID"].Value = myID;
            comm.Parameters.AddWithValue("@dDate", System.Data.SqlDbType.Date);//Date
            comm.Parameters["@dDate"].Value = dD.ToShortDateString();
            comm.Parameters.AddWithValue("@tTime", System.Data.SqlDbType.Time); //Time
            comm.Parameters["@tTime"].Value = dT.ToShortTimeString();
            comm.Parameters.AddWithValue("@Name", System.Data.SqlDbType.NVarChar);// Name
            comm.Parameters["@Name"].Value = N;
            comm.Parameters.AddWithValue("@Test", System.Data.SqlDbType.NVarChar);// Subject
            comm.Parameters["@Test"].Value = T;
            comm.Parameters.AddWithValue("@Phone", System.Data.SqlDbType.NVarChar); // Type
            comm.Parameters["@Phone"].Value = Ph;
            comm.Parameters.AddWithValue("@Remarks", System.Data.SqlDbType.NVarChar);// remarks
            comm.Parameters["@Remarks"].Value = R;
            try
            {
                con.Open();
                //  Execute the command
                comm.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }
        public void deleteRecord(Int32 myID)
        {
            SqlConnection con = default(SqlConnection);
            SqlCommand comm = default(SqlCommand);
            con = new SqlConnection("Server=PC;Database=HelpDesk;integrated security=True");
            comm = new SqlCommand("delete Appointments where ID=@ID", con);
            comm.Parameters.AddWithValue("@ID", System.Data.SqlDbType.Int);//ID
            comm.Parameters["@ID"].Value = myID;
            try
            {
                con.Open();
                //  Execute the command
                comm.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }
        public void FetchData(DateTime myDate)
        {
            SqlConnection con;
            con = new SqlConnection("Server=PC;Database=HelpDesk;integrated security=True");
            SqlCommand comm = new SqlCommand("select ID, dDate as Date, tTime as Time,Name,Test,Phone,Remarks from Appointments where dDate=@dDate", con);
            comm.Parameters.Add("dDate", SqlDbType.Date, 10, "dDate").Value = myDate;
            SqlDataAdapter dataAdapter1 = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            dataAdapter1.Fill(ds, "Appointments");
           // dataGridView1.DataSource = ds.Tables["Appointments"];
        }
    }
}

Open in new window

0
käµfm³d 👽Commented:
So the difference between yours and mine is that I opted for a static class and method whereas you are using a non-static class and methods, which means the calling code will need an instance of the class before you can use the method:


...

SaveGetAppointments instance = new SaveGetAppointments();

dataGridView1.DataSource = instance.FetchData(DateTime.Now);

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

CodeCruiserCommented:
There is another problem though. You have FetchData as void
0
adamtraskAuthor Commented:
this is what I am using on my UI which gives me an error:

void can not implicitly convert type "void" to "object"

 private void getDataForGrid()
        {
            DateTime myDate = DateTime.Today;
            SaveAppointments instance = new SaveAppointments();
            dataGridView1.DataSource = instance.FetchData(myDate);
           
           
        }
0
käµfm³d 👽Commented:
See CodeCruiser's comment and note the difference in your FetchData method and the getDataForGrid, specifically what each method returns.
0
käµfm³d 👽Commented:
...and the getDataForGrid
That is, the getDataForGrid method I wrote above ( http:#codeSnippet20-37269742-1 ).
0
adamtraskAuthor Commented:
Guys..... I lost both of you.....

I am still hazy about distinction between "void" "method" and "function"
I guess I am learning by coding, or learning and coding  at the same time....

CodeCruiser points to a problem in creating FetchData as a void. Why is it a problem? And how should I rectify it... or is this a topic that I need to study first.....?

With the given code, is there a course of action that you recommend?

Thanks

0
CodeCruiserCommented:
It is a problem because you want a function which would return a dataset/datatable which you can then bind to a grid. Void means that your function is not returning anything so what would you bind the grid to?
0
adamtraskAuthor Commented:
CodeCruiser... thank you, now it is clearer .

Can you show me how turn that "void" into a function ?  
0
käµfm³d 👽Commented:
This is (essentially) what I posted:

public DataTable getDataForGrid()
{
    ...

    return ds.Tables["Appointments"];
}

Open in new window


...and here is what you have:

public void FetchData(DateTime myDate)
{
    ...
}

Open in new window


Can you see the difference (ignore that I have no parameters and you have one of type DateTime)? What is the return type of both functions? They are:

getDataForGrid: DataTable
FetchData: void

A void return means your function returns nothing. You actually want to return something, and in this case it would be a DataTable (or maybe a DataSet). What your function essentially needs to look like is this:

public DataTable FetchData(DateTime myDate)
{
    ...

    return ds.Tables["Appointments"];
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodeCruiserCommented:
Change void to DataSet and add "return ds;" as the last line in function
0
adamtraskAuthor Commented:
Do you mean as in the code below?
It's generating errors - a red line under the word DataSet and another under "return" at the last line


public DataSet (DateTime myDate)
        {
            SqlConnection con;
            con = new SqlConnection("Server=PC;Database=HelpDesk;integrated security=True");
            SqlCommand comm = new SqlCommand("select ID, dDate as Date, tTime as Time,Name,Test,Phone,Remarks from Appointments where dDate=@dDate", con);
            comm.Parameters.Add("dDate", SqlDbType.Date, 10, "dDate").Value = myDate;
            SqlDataAdapter dataAdapter1 = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            dataAdapter1.Fill(ds, "Appointments");
            return ds;
          
        }

Open in new window

0
adamtraskAuthor Commented:
Thanks you kaufmed:

Now.... I got it.....(I am slow when I get scared :)

Just give me a little time to implement it
0
adamtraskAuthor Commented:
Great...!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.