[RESOLVED] [2.0] there has to be a better way to do this.

[Crash893]

Hi all

I want to make a sub that i send a Query and maybe some other variables (server location and table name) and i want it to return a datatable in a dataset that i can then feed into other subs for data manipulation as well as fill a DGV

the question is , if i have the sub return as a dataset how can i do more than one thing with it with out having to re run the sub again and again.

is it efficient to do something like "Dataset DS = subprocdure(query)" and then use DS in my code and if i do this wont it cause a duplication of data? (basicly ill have two containers sitting in memory)


Here is my code ( and please PLEASE feel free to correct anything else you think should be improved)


Ps yes i know the sub is names stupid thats just while im playing with it

private void button2_Click(object sender, EventArgs e)
        {
         string query = "SELECT gci_requests.id, gci_requests.ref_num, gci_requests.summary, gci_requests.description, gci_requests.open_date, gci_requests.last_modified_date, gci_requests.close_date, gci_requests.resolve_date, gci_requests.status, gci_requests.rootcause, gci_requests.priority, gci_requests.urgency, gci_requests.problem, gci_requests.type, gci_requests.category_name, gci_requests.assignee_adaccount, gci_requests.reportedby_adaccount, gci_requests.customer_adaccount, gci_requests.group_name FROM mdb_rpt.dbo.gci_requests gci_requests WHERE gci_requests.group_name='USAT-Publishing Solutions'";
 
         
            dataGridView1.DataMember = "gci_requests";
            dataGridView1.DataSource = Test(query); //<--- this is what i want to do better becuase i might need to use it somewhere else
       
        }
        private DataSet Test(string query)
        //worktodo
            // add more prams for server and tablename
            // improve catch to post error
        {
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection("Server=gci-mocsqdb01;Database=mdb_rpt;Uid=caiahd_rpt;Pwd=dashboard;");//Driver={SQL Server};
            try
            {
                conn.Open();
                String SelectCmdString = query;
                SqlDataAdapter da = new SqlDataAdapter(SelectCmdString, conn);
                da.Fill(ds, "gci_requests");
            }
            catch
            { MessageBox.Show("database connection Failed"); }
            finally
            {
                conn.Close();
            }
             return ds;
        }

[nemaroller]

Hi all

I want to make a sub that i send a Query and maybe some other variables (server location and table name) and i want it to return a datatable in a dataset that i can then feed into other subs for data manipulation as well as fill a DGV

the question is , if i have the sub return as a dataset how can i do more than one thing with it with out having to re run the sub again and again.

is it efficient to do something like "Dataset DS = subprocdure(query)" and then use DS in my code and if i do this wont it cause a duplication of data? (basicly ill have two containers sitting in memory)


Here is my code ( and please PLEASE feel free to correct anything else you think should be improved)


Ps yes i know the sub is names stupid thats just while im playing with it

private void button2_Click(object sender, EventArgs e)
        {
          publisherObjectController = PublisherObjectController.Create();
          publisherObjectController.Load();
         }

Put your Test method in a separate class. Rename the method to Load().

Then you can truly use it anywhere.

As far as calling the sub again and again:

It comes down to how many times you expect to get the same data. If the data will stay the same between calls, you may want to load it once and cache it. If the data changes, there's no point in caching.

[Crash893]

im not sure i follow

how do i make it a class?

[jmcilhinney]

Have your method take a DataSet as a parameter. Then you can create one DataSet outside the method and keep passing it in each time. That way you end up with only one DataSet containing multiple DataTables.

Alternately you could forget the DataSet altogether. Do you actually need a DataSet? DataTables can happily exist on there own with no DataSet. If you don't need to create DataRelations between the tables then does a DataSet really serve a useful purpose?

[Crash893]

that is a great idea

( i probably dont but the idea is that in the future i might need to do releation work in the future so i might as well figure it out now)


EDIT

do i still need to Return the passed in Dataset? im a little fuzzy about this

[jmcilhinney]

You already have a reference to the DataSet so why would you need another?

[Crash893]

You already have a reference to the DataSet so why would you need another?

well when you put it that way it does seem kind of obvious