I wrote this code:
SqlConnection Conn = new SqlConnection("ConnectionString");
SqlCommand cmd = new SqlCommand("select * from tablename", conn);
SqlDataReader dr = cmd.ExecuteReader();
datatable dt = new datatable();
dt.load(dr);
But I get an exception as shown below when I load data into the datatable because I have an xml column with a big size (102 MB).
Exception of type 'System.OutOfMemoryException' was thrown.
I'm very grateful if someone can give me the solution for this exception.
Solutions to your problem:
select * way and reimplement it in fetch-next-batch size, i.e. repeatedly fetch batches of fixed predefined size, process them, mark as processed somewhere and go on.
stream fashion i.e. use the reader how is supposed to be: one row at a time
selects one after another, which can be dog slow. You should clarify. Another option is to pull out the relevant XML values with XQuery direct in the SQLHere is a conceptual example. T-SQL shreds XML data type column into a rectangular format. The c# side of the equation will not have any problem.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, product VARCHAR(20), xmldata XML);
INSERT INTO @tbl (product, xmldata) VALUES
('vase', N'<root>
<r>red</r>
<r>blue</r>
<r>yellow</r>
</root>'),
('phone', N'<root>
<r>black</r>
<r>white</r>
</root>');
-- DDL and sample data population, start
SELECT id, product
, c.value('(./text())[1]', 'VARCHAR(20)') AS color
FROM @tbl CROSS APPLY xmldata.nodes('/root/r') AS t(c);
Output
+----+---------+--------+
| id | product | color |
+----+---------+--------+
| 1 | vase | red |
| 1 | vase | blue |
| 1 | vase | yellow |
| 2 | phone | black |
| 2 | phone | white |
+----+---------+--------+
select *grabs all the data in the table, you can't handle huge datasets in such a simple way.readerto read one row at a time in a loop. Process each row as you go along. And dispose your connection and reader objects withusing