2

My code is construct to read data in a datagridView named (dg) from my database.

Its actually work well whit a SqlDataAdapter.

First Is it a good idea to change my SqlDataAdapter for a SqlCommand ?

If YES

I want to use this for change my SqlDataAdapter.

//SqlCommand cmd = new SqlCommand("Command String", con);
//SqlDataReader readdata;

CODE

SqlConnection con = new SqlConnection(dc.Con);

SqlDataAdapter da = new SqlDataAdapter();
con.Open();
da.SelectCommand = new SqlCommand("SELECT * FROM tblContacts", con);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();

dg.DataSource = dt;
John Saunders
  • 160,644
  • 26
  • 247
  • 397
FrankSharp
  • 2,552
  • 10
  • 38
  • 49

1 Answers1

2

I typically use the DataAdapter for data access when I need to do data-binding to controls. It's very convenient and efficient in those scenarios. Otherwise, I use the Command objects directly. Performance-wise, I'm inclined to agree w/ punzki. There shouldn't be much difference between the two.

http://msforums.ph/forums/p/9057/9057.aspx

Actually, from what I remember, SqlDataAdapter uses SqlDataReader to retrieve records. So It's always good to use SQLDataReader when you're going to just retrieve data from the backend. But if you're going to retrieve data and then update (insert, update, delete) data later on, then it's better to use SqlDataAdapter. I think it's more efficient that way.

http://msforums.ph/forums/t/29256.aspx

There IS an effect on performance.

SqlDataReader is no doubt faster than a SqlDataAdapter as the DataReader reads data in a forward only mode and you can get a specific type of value returned back to you, such as a string or int etc... however with the SqlDataAdapter, it will fill a datatable or dataset will records it finds in your select statement, taking with it the correct value type for the columns and is a disconnected representation of in memory database and is ideal and easier to use if you are going to show large amounts of records to a binding source, as with a SqlDataReader, it is not possible but to only obtain a value for a column you specify per row.

The SqlDataAdapter also allows you to Update, Delete or Insert rows into the Dataset/DataTable which is an advantage and will execute the appropriate command, if you implemented it correctly, based on how the rows were modified in the Dataset/DataTable.

SqlDataAdapter is expensive compared to a fast forward read on the SqlDataReader, and has more advantages but entirely depends on your solution and what you require.

You are stating that you are going to show alot of records, whilst that is all very well, it would be even better for the benefit of the performance and memory usage to only obtain records that you require to be shown and a SqlDataAdapter would be suitable for this also but still you are required to select records which are the ones you will most likely show to the user, either by input search criteria, or perhaps by paging.

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/c2d762fd-f4a0-4875-8bb8-42f7480e97c8/

Emaad Ali
  • 1,483
  • 5
  • 19
  • 42
  • And how I use the SQLDataReader in this situation. The only way I can its from sqlAdapter. It's a lack of knowing. – FrankSharp Sep 23 '11 at 17:10
  • Why you want to use SQLDataReader? – Emaad Ali Sep 23 '11 at 17:12
  • 1
    For Information Check links http://stackoverflow.com/questions/1676753/sqldataadapter-vs-sqldatareader http://www.codeproject.com/KB/aspnet/RetrieveMoreData.aspx – Emaad Ali Sep 23 '11 at 17:14
  • Frank check links i posted in comments and answer for information. – Emaad Ali Sep 23 '11 at 17:15
  • 1
    For coding in SQLDataReader check these links http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader%28v=vs.71%29.aspx – Emaad Ali Sep 23 '11 at 17:16
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/3732/discussion-between-franksharp-and-emaad-ali) – FrankSharp Sep 23 '11 at 17:24