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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

 

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

Answer 6

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

Answer 7

www.queryadataset.com is what you are looking for.

 

Ad.

Answer 8

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();
                 }

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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