locked
How we can use SQL query in DataSet Or DataTable

    Question

  • How we can use SQL query in DataSet Or DataTable

    I means to say can we use group by clause and other clause that we generally used  with database table.

    Saturday, June 17, 2006 1:26 PM

All replies

  • Hi, Sunil

    You can use any valid select query to populate dataset or datatable, check this code

    Dim cn As New Odbc.OdbcConnection(ClsMain.ConneStr)

    cn.Open()

    Dim da As Odbc.OdbcDataAdapter

    Dim ds As New Data.DataSet

    Dim stmt As String

    stmt = "select Department, count(*) from Emp_Master group by  Department"

    da = New Odbc.OdbcDataAdapter(stmt, cn)

    da.Fill(ds, "EmpDept")

    In this code i connect to mysql database databse with odbc connection, you can change the connection string as per your data sourec, and change the Sql query to populate the dataset.

    Happy coding

    Saturday, June 17, 2006 2:52 PM
  • hi,

    i don't know anyway to do the same thing as group by in SQL to a single table , but you can use relations to group by a parent table http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndive/html/data05312002.asp

    table columns has expression property you can use it to search for solution , if you reach to any solution for this plz let me know

    best regards

    Saturday, June 17, 2006 3:30 PM
  • Atanu Sir

    I don't want to populate the dataset from SQL query.

    I had already fill whole data in dataset. Now I want the particuler records from dataset by executing query in dataset. So please guide me to how I can do the same.

     

    Sunil Sinha

    Monday, June 19, 2006 6:57 AM
  • You can use Select Method of DataTable Class (input the Filter Expression) .

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatatableclassselecttopic.asp

    Thanks.

    Monday, June 19, 2006 4:14 PM
  •  

    Atanu Sir

    I don't want to populate the dataset from SQL query.

    I had already fill whole data in dataset. Now I want the particuler records from dataset by executing query in dataset. So please guide me to how I can do the same.

     

    Sunil Sinha

    Tuesday, June 20, 2006 1:52 PM
  • to select a set of rows you can use a dataview

    DataView dv = new DataView(MyDataset.Tables["TableName"], "Column1 = " + TestNumtextBox.Text + "and Column2 Like '%" + VehNumtextBox.Text+ "%'","", DataViewRowState.CurrentRows);

    you can iterate through the rows in dataview like this

    foreach (DataRowView drv in dv)

    {

    value = (int)drv["column1"];

    }

    or you can use an array of rows like

    DataRow[] rows = table.select ("the same filter string as Dataview")method the string in the filter property will be the same as SQL Where clause

    but the other operation like group by ... etc in SQL Select Clause you will find it in expression as the link i posted b4

    hope this helps

    • Proposed as answer by shakalama Tuesday, June 30, 2009 6:00 AM
    Tuesday, June 20, 2006 10:20 PM
  • www.queryadataset.com is what you are looking for.

     

    Ad.

    Friday, May 04, 2007 12:37 AM

  • Hi

    U can use this code. It will definitely solve ur problems

    DataRow[] drNew = dsRecords.Tables[0].Select("SizeId=" + ddlEquipmentSize.SelectedValue);
                   
                    foreach (DataRow drVal in drNew)
                    {
                       string SizeName = drVal["EquipmentName"].ToString();
                     }
    Wednesday, May 27, 2009 8:17 PM
  • www.queryadataset.com is what you are looking for.

     

    Ad.


    That was a clever idea, but with LINQ now available, it really out shines that.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Proposed as answer by Adrian Moore Thursday, January 21, 2010 1:44 PM
    Wednesday, May 27, 2009 8:55 PM
  • Use a DataView to query a DataTable or you can use LINQ.

    //LINQ Example
    DataRow row = customers.AsEnumerable()
         .Where(i => i.Field<Int32>("CustID") == 4)
         .FirstOrDefault();

    //LINQ - Strongly Typed DataSet
    DataRow row = ds.Customers.AsEnumerable()
         .Where(i => i.CustID == 4)
         .FirstOrDefault();

    //DataTable Example
    DataRow row = customers.Select("CustID = 4")[0];

    //DataView Example
    DataView dv = new DataView(customers);
    dv.RowFilter = "CustID = 4";


    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Proposed as answer by YMaod Wednesday, April 14, 2010 8:54 AM
    Wednesday, May 27, 2009 9:01 PM
  • Sorry Moderators

    I didn't realize the date on this until after I replied.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, May 27, 2009 9:57 PM
  • you know ,,,,, no one can gives you what you want exactly you have to learn well all the keys of manupilating Dataset and tables collections of it so ,,, i was the same look for what u r looking for and i got book its very small and good called  Beginning C# 2005 Database you easy downlaod it through google search am sure you know what u do ;) and check chapter 8 Introduction Dataset and DataAdapter


    by the way ,,,  if you not interest about my ideas ,, maybe you have to look how to fill Datagridview from Database using Parameters ,,, here is the key of Filtering or pick some Data from your Database with specific parameters and this parameters could be any input control or maybe depened on click of some place on your DataGrid control 

     i knew i didnt help you with direct code but i gives you the idea of what i think you looking for

    happy solution    Sunil Sinha
    • Proposed as answer by HESHAM HEMDAN Tuesday, June 02, 2009 3:04 PM
    Friday, May 29, 2009 9:33 AM
  • The correct way of doing this is to open the dataset and choose the table you want to execute the sql query on and then right click the table and choose add query. Once this is done the query designer will open and just enter your sql query in the bottom where the existing one is if any. Finally in your code when filling the table adapter instead of using .fill use your new custom query .MyCustomQuery. Thanks Charlie
    • Proposed as answer by Charlieit123 Tuesday, July 24, 2012 6:08 AM
    Tuesday, July 24, 2012 6:08 AM