CODEWITHSUNDEEP
c#sqldatatable
lemunk
lemunk

Reputation: 2636

Fill datatable in a class, return table to use in another class

Ok Title may not be clear so let me explain. I have 2 classes, "Form1" and "webinfo".

in "webinfo" I run the following code:

class WebInfo
{
    string myConnVRM = "Data Source = datascource;" +
                          "Initial Catalog = catalog;" +
                          "Persist Security Info=True;" +
                          "User ID=ID;" +
                          "Password=PASS;" +
                          "providerName=System.Data.SqlClient;";
    public WebInfo()
    {

    }

    public void GetVRMs(string vRMs, string start, string end, string acc)
    {
        DataTable vrmTable = new DataTable();
        SqlConnection connVRM = new SqlConnection(myConnVRM);
        connVRM.Open();
        SqlCommand cmdVRM = new SqlCommand("SELECT Ac, Vrm, Make, Model, MamengineSize, date FROM ReturnValue WHERE convert(varchar,[Date],101) between @StartDate and @EndDate and [AC]=@Acct", connVRM);
        cmdVRM.Parameters.AddWithValue("@acct", acc);
        cmdVRM.Parameters.AddWithValue("@from", start);
        cmdVRM.Parameters.AddWithValue("@too", end);
        SqlDataAdapter vrmAdapter = new SqlDataAdapter(cmdVRM);
        vrmAdapter.Fill(vrmTable);

        //bind to data grid and display??
    }
}

as you can see I connect to a server, run a query and finally fill my table with the results.

my form class then calls GetVRms method, passes the relevent info.

But what I need to do is now bind this table to the datagrid on my form so i can display. Only problem is how do i use the table from this class?

Am i totally over thinking this? is it as simple as webinfo.vrmtable?

Many Thanks In Advance

Upvotes: 0

Views: 22279

Answers (6)

dipak
dipak

Reputation: 2033

Your SQL seems wrong here as well. 

   SqlCommand cmdVRM = new SqlCommand("SELECT Ac, Vrm, Make, Model, MamengineSize, date FROM ReturnValue WHERE convert(varchar,[Date],101) between @StartDate and @EndDate and [AC]=@Acct", connVRM);
        cmdVRM.Parameters.AddWithValue("@acct", acc);
        cmdVRM.Parameters.AddWithValue("@StartDate", start);
        cmdVRM.Parameters.AddWithValue("@EndDate", end);

OR

    string SQL = "SELECT Ac, Vrm, Make, Model, MamengineSize, date FROM ReturnValue WHERE convert(datetime,[Date],101) between '"+ start +"' and '"+ end +"' and [AC]=" + acc+ "; ";
          SqlCommand cmdVRM = new SqlCommand(SQL, connVRM);

Now you can use it the way you like it.

// As DataTable is ref
public void GetVRMs(string vRMs, string start, string end, string acc, DataTable dtVM)
{
if(dtVM == null) throw new Exception("Message");
   ......
}

OR

public DataTable GetVRMs(string vRMs, string start, string end, string acc)
{
  DataTable dtVM=new DataTable();
  .......

  Return dtVM;
}

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1062770

It really depends on the intent. If that is a utility method, then make it return the data, i.e. 

public DataTable GetVRMs(string vRMs, string start, string end, string acc)
{
    ...
    return vrmTable;
}

and access as var data = obj.GetVRMs(...).

If the table needs to be retained, then store it somewhere

public DataTable TheTable {get;set;}
public void GetVRMs(string vRMs, string start, string end, string acc)
{
    ...
    TheTable = vrmTable;
}

and access as:

obj.GetVRMs(..);
var data = obj.TheTable;

notes:

  • GetVRMs is perhaps over-abbreviated
  • DataTable works, but a lot of people would suggest a type model is a better metaphor
  • you need using statements etc to avoid issues (most obviously: saturating the connection pool)

Upvotes: 2

reshma k
reshma k

Reputation: 544

you can use return type as DataTable instead of void for GetVRMs method to return table i.e. vrmTable and then assign it to datasource of datagrid

Upvotes: 1

Manu Clementz
Manu Clementz

Reputation: 1797

Why don't you simply return the DataTable ?

public DataTable GetVRMs(string vRMs, string start, string end, string acc) 
{        
    [...]
    return vrmTable;
}

Upvotes: 1

tomfanning
tomfanning

Reputation: 9660

Change 

    public void GetVRMs(string vRMs, string start, string end, string acc)

to

    public DataTable GetVRMs(string vRMs, string start, string end, string acc)

then put

    return vrmTable;

at the end of the method.

Then bind to the table in your UI like this:

    WebInfo wi = new WebInfo();
    DataTable dt = wi.GetVRMs(....);
    myDataGridView.DataSource = dt;
    myDataGridView.DataBind();

Upvotes: 1

Maheep
Maheep

Reputation: 5605

Define a public property vrmtable and access it from anywhere using object of WebInfo class.

class WebInfo
{
    public DataTable vrmTable{get;} 

    string myConnVRM = "Data Source = datascource;" +
                          "Initial Catalog = catalog;" +
                          "Persist Security Info=True;" +
                          "User ID=ID;" +
                          "Password=PASS;" +
                          "providerName=System.Data.SqlClient;";
    public WebInfo()
    {

    }

    public void GetVRMs(string vRMs, string start, string end, string acc)
    {
        SqlConnection connVRM = new SqlConnection(myConnVRM);
        vrmTable == new DataTable();
        connVRM.Open();
        SqlCommand cmdVRM = new SqlCommand("SELECT Ac, Vrm, Make, Model, MamengineSize, date FROM ReturnValue WHERE convert(varchar,[Date],101) between @StartDate and @EndDate and [AC]=@Acct", connVRM);
        cmdVRM.Parameters.AddWithValue("@acct", acc);
        cmdVRM.Parameters.AddWithValue("@from", start);
        cmdVRM.Parameters.AddWithValue("@too", end);
        SqlDataAdapter vrmAdapter = new SqlDataAdapter(cmdVRM);
        vrmAdapter.Fill(vrmTable);

        //bind to data grid and display??
    }
}

Upvotes: 1

Related Questions