Click here to Skip to main content
15,917,473 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Help!!!!!
im trying to pass data table through stored procedure..Reading file,creating sp,table etc.i want to insert 1.5million data but cant insert fully.here where I have o give datatable.clear()?

C#
using (StreamReader sr = new StreamReader(file_path))
      {

          string line1;

          while ((line1 = sr.ReadLine()) != null)   //only * records inserted if v giv </>condition
          {


              line1 = line1.TrimEnd(',');


              string[] table_data = line1.Split("\r\n".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);


              table_data = table_data.Select(x => x.Replace("NaN", "0")).ToArray();   //?



              if (count_records > 0)
              {



                  (from line3 in table_data.Skip(0) select dt1.Rows.Add(line3.Split(",".ToCharArray()))).ToList();                                //v dont want to skip() anything

              }



              else if (count_records == 0)
              {


                  var line2 = from first_line in table_data[0].Split(",".ToCharArray()) select new DataColumn(first_line.Trim().ToUpper().Replace('-', '_').Replace(" ", ""));

                  dt1.Columns.AddRange(line2.ToArray());


                  create_table(table_name);
                  createtype_perdraw();
                  stored_procedure_select();

              }


              count_records++;


              if (count_records == 1000)
              {

                  //insert_table(table_name);

                  insert_sp(dt1);

                  dt1.Rows.Clear();
                  count_records = 1;


              }

///this is d part of calling stored pro.error(out of memory excptn) showing near to reader

    public void insert_sp(DataTable dt2)
        {
            .//int count = 0;



            using (SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\MyPractice\browse_fie\browse_fie\browse.mdf;Integrated Security=True"))
            {

                {



                    SqlCommand selectCommand = new SqlCommand("SP_PERDRAW", con);
                    selectCommand.CommandType = CommandType.StoredProcedure;

                   

                    SqlParameter Param = selectCommand.Parameters.AddWithValue("@per", dt2);

                    Param.SqlDbType = SqlDbType.Structured;

                    con.Open();
                    pergrid.ItemsSource = selectCommand.ExecuteReader();/// HERE SHOWING OUT OF MEMORY EXCEPTION
                    pergrid.ItemsSource = dt2.DefaultView;
                    dt2.Rows.Clear();
                    con.Close();



                }

            }
        }
          
//how can i insert fully?
Posted
Updated 13-Jul-15 19:24pm
v2
Comments
_Asif_ 14-Jul-15 1:57am    
Is pergrid some sort of Grid that will hold all the data that has been inserted via SP? and this means that 1.5 M rows will be shown at the this grid in the end?
[no name] 14-Jul-15 2:12am    
yes pergrid is grid.i want to insert and display simultaneously on the grid.
_Asif_ 14-Jul-15 2:43am    
If this is the case then you will surely hit an out of memory exception. Let's assume you have 1.5 M rows to insert and display.

Total Rows = 1500000
Each Row size (In Bytes Est) = 512

Total Size in Bytes = Total Rows * Each Row Size = 768000000
Total Size in M Bytes = 733 MB approx

If the business forces you to show all the records (this is insane requirement) then you have to do Server side paging. That is fetch only one page data from the database and shows in the grid
[no name] 14-Jul-15 2:48am    
okey.but I can insert?
_Asif_ 14-Jul-15 3:08am    
Offcourse but again One go solution won't be practical. You need to insert in batches (I think you are already doing it by creating a batch of 1000). A good way is to create an XML of the data and pass that data to the SP in parameter and process the data in the SP.

Take a look at this http://stackoverflow.com/questions/15128999/passing-xml-string-parameter-to-sql-server-stored-procedure

1 solution

If I interpret the code correctly, you try to pass a large amount of rows as a paramter to stored procedure which then again returns a result set which is used as a data source for a grid.

Why do you need 1.5 million rows as a parameter? If you're inserting a large amount of data into the database I suggest using for example SqlBulkCopy[^] instead of a huge parameter.
 
Share this answer
 
Comments
[no name] 14-Jul-15 2:14am    
I usd bulk copy also but I cant pass that procedure through c#.could u show me how to pass? this s what I used sp
create procedure dbo.InsertZipCode
AS
BEGIN
SET NOCOUNT ON;
BULK
INSERT ZIPCodes from 'e:\5-digit Commercial.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
END
[no name] 14-Jul-15 2:29am    
how can I call this bulk sp in c#
Wendelius 14-Jul-15 2:55am    
I didn't mean bulk copy on server side but on client side. Please have a look at the link I included in the answer. The idea of using bulk copy on the client side is that you don't pass a large amount of data at once to the server but you handle smaller chunks of data. There's a nice example in the documentation of SqlBulkCopy.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900