How do I get this data(from DB) into a dataset

[Henk1]

Good day all,

I have been playing around with diffirent ways of getting data from DB's.

I have this code:

SqlConnection mySQLConn = new SqlConnection("workstation id=AT007359;packet size=4096;user id=sa;data source=AT007359;persist security info=True;initial catalog=Northwind;password=***********"); //password is taken out for confidentiality sake.
				SqlCommand mySqlComm = new SqlCommand();
				mySqlComm.CommandType = CommandType.Text;
				mySqlComm.CommandText = "SELECT * FROM EMPLOYEES";
				mySqlComm.Connection = mySQLConn;

				mySQLConn.Open();
				mySqlComm.ExecuteNonQuery();
				mySQLConn.Close();

How do I get this data into a dataset? I tried:

dsEmployees = mySqlComm.ExecuteNonQuery();

but get an error that it cannot convert type int to type dataset.

I also want to know what you call this method of getting data from the DB. Is it ADO or what? I need to learn the terms, or else I will look like an idiot even though I know how to do the job.

Thanks
Henk.

 

[obislavu]

You call ExecuteNonQuery() when you are doing insert, update, delete or for example "Select count(*) from ...".
To retrieve data from DB declare a DataSet object and use SqlDataAdapter and Fill() method to load the data from DB.

string sCon = "user id=sa;Password=xxx;Initial Catalog=cat01;Data Source=localhost;Connect Timeout=5";
SqlConnection myConn = new SqlConnection(sCon);
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = new SqlCommand("SELECT name AS Router_Name FROM ROUTER WHERE routerID IN (SELECT DISTINCT routerID FROM PINGS WHERE rtt>20)" , myConn);
myConn.Open();
DataSet DS = new DataSet("DataSetName");
myDataAdapter.FillSchema(DS,SchemaType.Source,sTable);
myDataAdapter.Fill(DS,sTable);
...

-obislavu-

 

[Henk1]

oh. Thanks.

I have used sqldataadapter before, byut never knew what the diffirence was.

I am however free to use SQLDataAdapter at any time, am I not? Whether I am updating, I can still just call my stored procedure, or write inline sql statements?

Am I correct in doing it this way?

Also, why would you like to do things diffirent if you just can use the SQLDataAdapter anyways? Then you can return a value stating if the transaction was successful.

Or am I missing something? What are the best practices around using the method above, or the SQLDataAdapter?

 

[obislavu]

Yes, you can use SqlDataAdapter any time you want to work with a DB whenever to read data from DB or update the source database.
Generally, you use a dataadapter to:
-connect to database
-retrieve data from DB into a DataSet object
-disconnect from the DB
-present this data using GUI
-modify the DataSet object without be connected to the DB
- when update the source data e.g. DB with the changes made on the DataSet object reconnect to DB and commit the changes
From my experience a good way is to use stored procedure and have these stored procedure called from your C# app by providing the parameters and delegate the insert, delete, update to the stored procedure.
The application receive the return code of the stored procedure and take appropiate actions.
The return code could be a string, an integer or a recordset.
Sometimes you need to present data from many tables into a DataGrid and an easy solution is to create a view obvject in the DB which will collect all data need to be presented into the DataGrid and your sql statement will be just:

string sViewObj = "myView";
string sSQL = "SELECT * from " + sViewObj;
myDataAdapter.SelectCommand = new SqlCommand(sSQL,myConn);

When calling Fill() on a DataSet will create a table with the columns prepared by the view object.
Bind a DataGrid object with this DataSet.
The user can modify the data in the grid and using stored procedure the application put back the changes and refresh the DataSet.
There are many ways and that is depending on each application needs.
Another way is to load data into a DataSet, disconnect, modify the data in the DataSet and when finishing connect with DB and use Update() method and CommandBuilder object.
-obislavu-

 

[Henk1]

Hmmm, This is what I thought.

Ok, thanks for the help. I will now start researching into the CommandBuilder Object, as I have never seen this.

Will ask again if I cannot figure it out.

Thanks again.