IDE :: SQLDataReader To Object Optimalization?
Feb 2, 2011
I`m bussy building a ORM for our applications.I don`t want to use all of microsoft data functions like ADO.NET and Datasets because i would like to understande the base and manipulate data by code. I also want to make it later work like building a database out of the code in stead of the other way around.
I have build a simple appliacation inside VB.NET with a dataset and ADO.NET Entity.. When loading 250.000 records into objects it takes about 4.5 second. I`m using datareader to load a record of a database into a object dynamicly. I started at 15 seconds,later 8 and nu 6.5 seconds for the total amount of records.I started with TmpObject.ColumnName = Reader ("ColumnName"), later i figured out Reader.GetValues(TmpObjectArray) in combination with TmpObject.ColumnName = TmpObjectArray(0) is faster. Now i tought if i can set the properties as pointer to a object array so the values are placed onto the object instead of on a different object before it could be faster.
' PropertyArray
Dim tPropertyArray()
As PropertyInfo = tProperties.Values.ToArray[code]......
View 4 Replies
ADVERTISEMENT
Apr 13, 2010
Some people have argued that the C# 4.0 feature introduced with the dynamic keyword is the same as the "everything is an Object" feature of VB. However, any call on a dynamic variable will be translated into a delegate once and from then on, the delegate will be called. In VB, when using Object, no caching is applied and each call on a non-typed method involves a whole lot of under-the-hood reflection, sometimes totaling a whopping 400-fold performance penalty.
View 3 Replies
Mar 2, 2010
I want to write a routine that loads a table (or query) and outputs it to a text file, with the field names in the first row.If I load the data via an adaptor class into a datatable, I can access the field names by something like DataTab.Columns(i).ColumnName.
As I only want to read the data from the table, using SqlDataReader would be more efficient. But how can I then find what the header or column names are? The following code fails with a message that the SqlDataReader class does not have a member called 'Columns ':
Dim sqlCmd As SqlClient.SqlCommand = New SqlCommand(SQL, Connection)
Connection.Open()
Dim Reader As SqlClient.SqlDataReader = sqlCmd.ExecuteReader()
Dim FName0 As String = Reader.Columns(0).ColumnName() '<==FAILS
Is there a way to find the column names of an SqlDataReader object?
View 2 Replies
Jan 12, 2012
I have got this error No default member found for type 'VB$AnonymousDelegate_0(Of SqlDataReader,String,Object)'.
My Code is below
dsBranch.Tables.Add(GetDataTableFromSQLReader(dr, "")) - Calling
Private Function GetDataTableFromSQLDataReader(ByVal dr As SqlDataReader, ByVal TableName As String)
[code]....
View 1 Replies
Jan 24, 2012
Does anyone know a Good tutorial on how to use The SqlDataReader in VB.Net ?
View 4 Replies
Aug 27, 2011
I'm not sure about this code. What I wanted to do is to count the number of records in the table that I selected and then add a value of 1 to it to generate a transaction number.
I'm not sure on how to count the number of rows in the reader. So far, this is my code.
Dim intTransaction As Integer = 0
Try
con.Open()
[Code]....
View 2 Replies
Feb 23, 2012
How can I reuse(reopen) same Datareader after calling Close() ??
View 1 Replies
May 10, 2010
My SQL query shows 8 rows when executed but the following code only shows 7. Apparently, it�s missing the first row.
...
Dim temp as String = Nothing
Dim r As SqlDataReader = objCmd.ExecuteReader()
If r.Read() = True Then
[Code]......
View 2 Replies
Jan 28, 2010
Can i use SQLDataReader instead of a Recordset. I want to achieve the following result in an SQLDataReader.
Dim dbConn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sqlstr As String = "SELECT Name,Status FROM table1 WHERE id=" +
[Code].....
can i replace recordset with SQLDataReader and if I can can you please show me the changes in code.
View 4 Replies
Oct 2, 2009
I would like to pass two values to a stored procedure. I am using a sqlreader to export data to an excel sheet. I need the user to be able to select a range, rather that the whole sql table. the code works fine without adding parameters, but I need the user to be able to select a range, rather that the whole table.
Dim data As String = ""
sqlCon.Open()
sqlCmd = New SqlCommand(strSQL, sqlCon)
[code]....
View 1 Replies
Oct 29, 2010
I am returning one row from the database, and I want to convert the SQLDataReader to a string format, so I can pass it to my webservice.
Dim rdr As SqlDataReader = sqlcmd.ExecuteReader
If rdr.HasRows Then
rdr.Read()
[Code].....
View 1 Replies
Jun 21, 2011
I'm building a custom calendar control that queries our database to display corporate events. Here is the situation, there is an EndDate value stored and on the dev system one of the events have a NULL value. No big deal since it's only a test system, but might as well check before trying to use it anyway on the safe side. I figured the following code would work:
While dr.Read()
corporateTable.Rows.Add(New Object() { _
Convert.ToDateTime(dr("EventBeginDate")) _
[code]....
The thing that really gets me is, at one point I had this:
, IIf(dr.IsDBNull(column), Convert.ToDateTime(dr("EventBeginDate")).AddDays(1), Convert.ToDateTime(dr("EventEndDate"))) _
Thinking that it should work because it should only evaluate the dr() if it's not NULL. However, it kept erroring out at the end because the value was in fact NULL.Why is it that even though I'm checking if it's NULL before using the value, it errors out at the part that doesn't get called unless it's not NULL? Does it have to do with the fact that I'm using the IIF() and it's evaluating the whole statement? Or, using the dr(), it evaluates at runtime?
View 2 Replies
Mar 19, 2012
I am writing a simple XML web service and want to return raw XML. I am doing this by creating a string literal that represents the XML. My data source is a SqlDataReader object that contains the results of a stored proc call. The way I am doing it right now is by using a while loop and reading values from the item property like so :
[Code]...
View 1 Replies
Dec 23, 2009
I just want to verify something. I believe it is likely that if I apply the using command to a SqlDataReader, that it will both close the data reader and dispose of it. For example:
Using sdr As SqlDataReader = cm.ExecuteReader()
Dim someInt As Integer = sdr.GetInt32(0)
'other details and actions
End Using
Will that close the sdr SqlDataReader after it exits the Using code block. (I believe it will, but just want to verify.)
View 2 Replies
Jun 2, 2009
What would happen if you call Close() on a SqlConnection object before you call Close() on a SqlDataReader using that connection?
Actually, what I really want to know is whether or not the order in which you Close them matters. Does calling SqlConnection.Close() completely close the connection, or will it remain open if you do not call Close() on a SqlDataReader using that connection?
I don't think I really understand how connection closing works.
View 3 Replies
Jul 11, 2009
How do i get the number of columns in a sqlDataReader after executing sql statement ?
View 4 Replies
Nov 16, 2007
I am a beginner in VB.Net. I have a table tblName; which consist of columns 'Name' and 'ID'. I want to populate the comboBox, where displaymember = 'Name' and ValueMember = 'ID'. In short, when a user selects a 'Name', return value should be the 'ID'. I can do it using DataAdapter. Another way of doing is; using a Listbox in parralel with the comboBox, which will be hidden, to save the 'ID' .
[Code]...
View 9 Replies
Jun 22, 2010
I need to populate all the values of a specific column in each rows of my table from my database. The specific column that i need is the "position",I'm using sqldatareader here's my code. I know that I need to use looping statements and the items.add properties of the combobox but I dont know how will I do it..hope you help me here..
Private Sub cboPosition_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboPosition.Click
scmd = New SqlCommand("SELECT KPIPOSTCODE FROM KKPIPOSITION", sqlcon)
sqlcon.Open()
scmd.CommandType = CommandType.Text
[code].....
View 8 Replies
Oct 3, 2009
I am currently using a sql data reader (in vb.net) to extract an article object via a stored proc from a SQL Server 2008 database. Part of this object includes the two properties shown below:
theArticle.Truthfulness = ((myReader.GetInt32(myReader.GetOrdinal("Truthfulness"))))
theArticle.Relevance = ((myReader.GetInt32(myReader.GetOrdinal("Relevance"))))
My problem is that the Truthfulness and Relevance may return a null value and this is causing the function to fall over.I think I understand why. I am asking for an integer value (getin32) and because null is returned it fails.How do I accommodate the null value from the database so it does not fall over?
View 5 Replies
Feb 13, 2012
My question is: Using SqlDataReader. Suppose I have SQLdr instantiated. I can get data from some column using:
SQLdr("some_column")
Now if I know that column "Value" has a some value, I can get it this way:
eg. 1
[Code]...
My motivation is to have easy-on-the-eyes code (not too dense on the page with long expressions). I have one application I'm studying where there are hundreds of expressions like eg. 1. If I could get the ordinal values at the start, I could use expressions like eg. 2 instead. As a bonus, I would eliminate redundant calls to GetOrdinal for queries that return thousands of rows.
however, this is clearly erroneous if the ordinal values might change between calls to SQLdr.Read().
View 11 Replies
Jul 29, 2010
This is incredibly urgent, I need to present this application in 3 and a half hours.My application checks against a data source to see if a value exists in the database and changes values depending on whether or not the value in question was found.The problem is that I've run the sql query with the value in question in SSMS and no rows were returned, and yet, my DataReader says it has rows.[code]val will only be returned True if the NumberToCheck (in this example 3235553469) exists in the database.Having copied the value of NumberToCheck into SSMS and testing the query there, I can verify that the query does work as expected.No, I can't populate a DataSet because of the volume of information in the table (+/- 9.5m rows). Even with the 'WHERE' filter, the query is too heavy on resources and eventually ends in an OutOfMemory Exception which is why I went with a DataReader.
View 4 Replies
Mar 9, 2010
My code where errors occurred:
strSQL = "select * from transactions where TransactionID='" + txtTransactionID.Text + "'"
ReaderQuery(strSQL)
While dr.Read
[code].....
The weird thing about this, is that when I first run it, I was able to Bind it to all the Textbox and thought everything went smoothly, but when I run it again without any changes in that code, that error suddenly appears... sometimes that error went to other dr.item(""
View 6 Replies
Feb 9, 2010
Some tables I am dealing with have null values and are throwing errors. So far ive tried a few solutions to deal with the nulls with no success.Here are the code samples from my efforts so far;
If (r("datemodified").Equals(DBNull.Value)) Then
datemodified = String.Empty
Else[code]....
i have ran the query and it is working ok (i.e all the cols exist)
View 2 Replies
Apr 6, 2010
Scenario is this; I've a sqldatareader query that seems to be returning nothing when I try to convert the sqldatareader to a datatable using datatable.load.So I debug into it, I grab the verbose SQL query before it goes into the sqldatareader, just to make sure it's formatted correctly. I copy and paste this into SQL server to run it and see if it returns anything. It does, one row.I go back to visual studio and let the program continue, I create a datatable and try to load the sqldatareader but it just returns an empty reader. I'm baffled as to what's going on. [code] Ok I've done a loop to count the rows of the datareader before using the datatable.load method. In my test query I'm getting 4 rows (the correct amount) in the datareader, as soon as I use the datatable.load method it says the datatable is empty, what's going on?! So it seems a datareader is a one-way thing, iterate through it to get the row count and it's essentially empty as far as any other bit of code is concerned. Didn't realise it was forward only and wouldn't 'reset' once read through.
View 1 Replies
Jan 12, 2012
Dim myreader As SqlDataReader = cmd.ExecuteReader
'read information from the database and give the values to the arguments(column_1, and column_2)
While myreader.Read
column_1 = myreader.GetString(1)
column_2 = myreader.GetString(2)
.....
I get an error when the data returned from myreader.GetString(2) is NULL. How do I check for nulls before assigning column_2 a value?
View 3 Replies
Jan 14, 2010
I am running queries using sqlcommand and am trying to get the results from sqldatareader into a dataset table. It works fine when loading the datareader data as a row into the dataset table and writes it to a crystal report.I would like to have depending on the query results have the data in the appropriate columns and it only happens if the results are from all fields.
Dim CommandObj2 As New SqlCommand(sql, cnn)
sqldr = CommandObj2.ExecuteReader()
While sqldr.Read()
Dim dataRow As DataRow = ds.Tables("NTable").NewRow()
[code]....
MsgBox(ds.NTable.Rows.Count, MsgBoxStyle.OkOnly, "Records Count")My dataset and table have the same name, NTable. Question again is how to populate the dataset columns accordingly.
View 5 Replies
Jul 8, 2010
Some tables I am dealing with have null values and are throwing errors. So far ive tried a few solutions to deal with the nulls with no success.
Here are the code samples from my efforts so far;
If (r("datemodified").Equals(DBNull.Value)) Then
datemodified = String.Empty
[code].....
View 1 Replies
Aug 21, 2011
Here's a simple code snippet of a larger function as an example.
Using conn as New SqlConnection("conn string")
Using find as new SqlCommand("ExampleProc",conn)
Dim rParam as new SqlParameter("@RESULT",SqlDbType.Int)
[code]....
I know the Output parameter is returned after the SqlDataReader is closed. From what I think I know, the Using statement will call .Dispose on the SqlDataReader, so will that basically wipe out the Output parameter? If so, what is the best method to call a SqlDataReader that also contains Output parameters that closes and disposes everything correctly? Let me add that based on what I've read you only have access to the Output parameter using a SqlDataReader after you call .Close on the SqlDataReader.
View 2 Replies
Feb 7, 2011
I have a SqlDataReader and need to read a varbinary(max) column from it using the SqlDataReader.GetBytes() method. This method populates a byte array and therefore needs to know what length of data to read.This is where I get confused.. Clearly I want to read all the data that has been returned from the database in this row/column so what 'length' parameter should I pass?As far as I can see, the SqlDataReader doesn't provide any methods to discover what length of data is available, therefore this method seems fairly awkward to me.I'm tempted to just pass int.MaxValue here and forget about the issue but something about this doesn't sit right with me.
I am aware that I can instead call
byte[] value = (byte[])dataReader["columnName"];
.. and this seems to completely take care of the length issue internally. However I am working with a set of complicated code generation templates that have been built around the SqlDataReader.GetXXXX() methods. So I am tied into using GetBytes and need to understand its proper usage.
View 7 Replies
Jun 21, 2012
I have a SqlDataReader and need to read a varbinary(max) column from it using the SqlDataReader.GetBytes() method. This method populates a byte array and therefore needs to know what length of data to read.This is where I get confused.. Clearly I want to read all the data that has been returned from the database in this row/column so what 'length' parameter should I pass?As far as I can see, the SqlDataReader doesn't provide any methods to discover what length of data is available, therefore this method seems fairly awkward to me.I'm tempted to just pass int.MaxValue here and forget about the issue but something about this doesn't sit right with me.
View 2 Replies
