Difference Between SqlDataReader.NextResult() And MARS
Sep 19, 2010
hile i have been working with application perfomance tuning part i found an article for doing the same from Microsoft. it suggests me to reduce the round trip between application and database by using Sqlbatch statements. i got two ways to achieve it 1. SqlDataReader.NextResult() - we can use single connection to execute sql batch statements2. MARS (Multiple Active Result Sets) - we can use single connection to open multiple readers and execute the batch statements
When using SqlDataReader.NextResult for 2 queries I am trying to I check if the second query returns any rows, but whether it has any rows or not the repeater2 is never hidden/false . I am doing something like this:
I'm trying to implement a TreeView exactly like this:http://www.matthidinger.com/archive/2009/02/08/asp.net-mvc-recursive-treeview-helper.aspxThe problem is, that I'm using MySQL which doesn't support MARS (Multiple Active Result Sets).So, how would I do this, without being able to use MARS?
I used to use datasets instead of sqldatareaders and I used to be able to do something like this
If dataset.tables(0).Rows(0)(1).ToString()) = "N" Then lbl.Text = dataset.tables(0).Rows(0)(2).ToString()) Else 'Do Nothing End If
This obviously doesn't work with sqldatareaders.I have code to see if the SQLDatareader has any rows but was wondering if there was a way to get the value of each rowI'm guessing this is possible and i've had a look around but can't seem to find anything
Dim conn As SqlConnection = New SqlConnection("server='h'; user id='w'; password='w'; database='w'; pooling='false'") conn.Open() Dim query As New SqlCommand("DECLARE @investor varchar(10), @sql varchar(1000) Select @investor = 69836 select @sql = 'SELECT * FROM OPENQUERY(db,''SELECT * FROM table WHERE investor = ''''' + @investor + ''''''')' EXEC(@sql)", conn) Dim oDR As SqlDataReader = query.ExecuteReader() If oDR.HasRows or dataset.tables(0).Rows(0)(1).ToString()) = "N" Then lbl.Text = dataset.tables(0).Rows(0)(2).ToString()) Else 'Do Nothing End If
That is the code I have at the moment which obviously doesn't work
I have a SQL Server 2008 database and I am working on it in the backend. I am working on asp.net/C#
SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { //how do I read strings here???? }
I know that the reader has values. My SQL command is to select just 1 column from a table. The column contains strings ONLY. I want to read the strings (rows) in the reader one by one. How do I do this?
i need to Get Two rows details in variables using SQLdatareader and using while loop in it.Can i do so,
my query and while loop are getting fired when i debug and see my while loop...but i dnt know how to get details and show or store it, when i will get 2 records on execution of query.
When I query for only one record/row, sqldatareader is giving correct result but when i query for multiple rows, its giving error on the client side. below is my code.
I am trying to run a stored proc from this function. When I run it thorugh the debugger I can see that myReader.HasRows = true and myReader.FieldCount =14.
But the control does not go in the loop while (myReader.Read()) where I am trying to read Version field returned by the stored proc.
i try to use userControl to display SqlDataReader data.
in the main page
public SqlDataReader Data2; ... <uc1:WebUserControl ID="WebUserControl1" RData1="<%=Data2 %>" runat="server" /> and in the userControl Repeater1.DataSource = RData1; Repeater1.DataBind();........
But i keep getting this error
Cannot create an object of type 'System.Data.SqlClient.SqlDataReader' from its string representation '<%=Data2 %>' for the 'RData1' property.
I have a very strange issue, I'm using System.Data.SqlClient. to get data from a SQL Server though a stored procedure. When I test the Application at the Development and Stagging machines it works fine but when I deploy the application on the Production Server I randomly getting an SqlDataReader IndexOutOfRangeException with different column names!.The error appears in 2 requests in each 1000 request (approximative).
public static List<CountryInfo> GetAllCountries(){ List<CountryInfo> Items = new List<CountryInfo>(); try{ using (rdr = SqlHelper.ExecuteReader(Globals.ConnectionString, "unv_spGetAllCountries")) [code]...
I'm using Json.NET and I'm trying to create a JSON string representing a row from a database table to return in an HTTP response. I'm not sure how to do this while I'm reading from the database.The problem is marked by the obnoxious comments /******** ********/
// connect to DB theSqlConnection.Open(); // open the connection SqlDataReader reader = sqlCommand.ExecuteReader();
I'm trying to convert some code from VB to C# on a site i'm working on and can't seem to get the arraylist to function properly.
Here is what we currently have in the codebehind:
[Code]....
Which we then call from the page using:
[Code]....
I want to convert this to C# as the rest of the code on the new site is in C# and I'm having problems with the ArrayList. I've got the other parts of the code all converted fine it's just the GetContent Function that isn't working right. Here's what I have:
[Code]....
I got an error on the "objSDR.Item" part of the code saying "SqlDataReader doesn't have definition for Item" and so if I changed this to the code above. Only problem now is I can't call this from the page using the code below as there is no "Item" in the GetContent Function any more
I know it is recommended to use a using statement with a sqldatareader, but I was wondering if it is necessary when you are setting the datasource of a control to a datasource directly. in some places in my code I do this.
using (SqlDataReader reader = getReader()) { while (reader.Read()) { // do stuff with data } }
when binding to a control I do this.
ddlCustomer.DataSource = getReader(); // get Reader returns a sqldataReader ddlCustomer.DataBind();
In the second case, do I need to use a using statement. Do i have to first declare an SqlDataReader in a using statement, then set the DataSource to that object. Seems like more clutter in the code, so i was hoping binding to the SqlDataReader disploses of the SqlDataReader.
I have a vb class that reads a single row of data from an SQL view. I want to add a property to it that will expose the value of a new bit field. But whatever I do my class always returns the value "false".What am I doing wrong?
Public ReadOnly Property IsPublished() As Boolean Get Return valPublished End Get End Property
Here's the relevant line from my datareader: Me.valPublished = Convert.ToBoolean(theObjectReader("Authorised"))Authorised is my bit field. I have tried it with and without the "ConvertToBoolean" statement.When I response write the value of the property for rows where this field is true, it always shows false.
While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.
If the above claim is true, then why is the following method able to retrieve a value from output parameter before the reader is closed:
public int Something() { using (SqlConnection con = new SqlConnection(this.ConnectionString)) { SqlCommand cmd = new SqlCommand("some_procedure", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output; con.Open(); cmd.ExecuteReader(); return (int)cmd.Parameters["@ID"].Value; } }
You can reset the CommandText property and reuse the SqlCommand object. However, you must close the SqlDataReader before you can execute a new or previous command.
Why must you close sqldatareader before you execute new command?
