how to clear the data table when passing stoered procedure in c#

Question

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 13-Jul-15 19:20pm

Manidas Payyoli

Updated 13-Jul-15 19:24pm

v2

Add a Solution

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

_Asif_ 14-Jul-15 3:13am

You can also use hint from here regarding how to use XML in the SELECT

http://stackoverflow.com/questions/14892692/xml-as-parameter-in-stored-procedure-sql-server

Suvendu Shekhar Giri 14-Jul-15 1:58am

You can use
dt2=new DataTable();

But from your code it doesn't look like you have any issue clearing datatable though.

[no name] 14-Jul-15 2:11am

but when I am trying to insert 1.5M data ,showing out of memory exeption."pergrid" is nothing but grid! wnt to dis play 1.5m data in grid

Sreekanth Mothukuru 14-Jul-15 2:15am

I suggest you to try BCP feature with C# to insert bulk records into SQL instead of what you were doing.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2015-07-13T20:10:00

Solution 1

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.

Posted 13-Jul-15 20:10pm

Wendelius

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.