How to execute an SQL statement from a DataSet?

bouli

Hello gurus, I have a request for you. I have imported a flat file (delimited text file) into a DataTable from a DataSet. I wish to run an SQL statement such as "SELECT toto as X, count(titi) as Y from MY_TABLE GROUP BY X, ORDER BY X" where 'toto' and 'titi' are field from the MY_TABLE. How is it possible to do that in C#? I hope my question is clear. If not, let me know, I'll try to explain in better way. Best regards. Fred.

There is no spoon.

leppie

You can use LINQ for that if you have VS2008, else it's not really possible.

xacc.ide - now with TabsToSpaces support
IronScheme - 1.0 alpha 4a out now (29 May 2008)

Vimalsoft Pty Ltd

hi bouli Firstly your Question is not Clear , you have imported a Flat File into a Datatable from a Dataset, its not making sense at all. Please Tell us Step by Step and show us your code, where you import and so we can understand what you mean and we will take it from there. Hope it helps

Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za

bouli

Okay, Sorry for my poor english. I have sucessfully imported a CSV file into a DataSet. I need to execute an SQL statement using COUNT, GROUP BY and ORDER BY. The request is done and works fine. I need to run it in C#. While the data remain in a DataSet, I would like to know how I can execute the SQL statement on this DataSet? Best regards. Fred.

There is no spoon.

Vimalsoft Pty Ltd

Hi man here is the Code.

public Dataset GetData()
{
Dataset dsdata = new Dataset();
/* As you said you, you have Successfully imported data into the Dataset
* So, i dont know how you did it, but you should end up with the same thing as
* the above decleation of a Dataset and the Following Code will show you how to query a Dataset
*
*/
String Constr = @"User ID=sa;Password=;Database=MyDB; Server =MYSERVER";

      SqlConnection con = new SqlConnection(Constr);  

      SqlCommand cmdselect = new SqlCommand("SELECT toto as X, count(titi)as Y from MY\_TABLE GROUP BY X, ORDER BY X");
      
      cmdselect.CommandTimeout = 0 ;

      cmdselect.Connection = con; 

      SqlDataAdapter da = new SqlDataAdapter(cmdselect);
      //This is the Most important Part, if you have one table from the Dataset,you can leave it like this 
      da.Fill(dsdata);

      return dsdata;

  }

Tell me if you have Problems (vuyiswamb) Skype

Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za

bouli

Hi, As I said, the data are already in a DataSet loaded in memory from a CSV file and not in a database. Best regards. Fred.

There is no spoon.

Vimalsoft Pty Ltd

ok, I was showing you, if the data is already in the dataset. then your Dataset will have a name and a datatable, then from the above code. see , you will end up with a dataset object.

Dim strFileName As String = "MyFile.csv"
Dim strPath As String = "C:\"
Dim strCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + strPath + ";Extended
Properties='text;HDR=Yes;FMT=Delimited'"
Dim con As New OleDbConnection(strCon)
Dim cmdGetCsv As New OleDbCommand("SELECT * from " +
strFileName, con)
Dim csvAdapter As New OleDbDataAdapter(cmdGetCsv)
'Dim csvReader As OleDbDataReader
con.Open()

Dim dsCsv As DataSet = New DataSet

csvAdapter.Fill(dsCsv)

you can change your Select statement to your liking. Hopr this Helps

Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za