如何在c＃中传递stoered过程时清除数据表 [英] how to clear the data table when passing stoered procedure in c#

问题描述

帮助!!!!!

我试图通过存储过程传递数据表。读取文件，创建sp，表等。我想插入150万个数据，但不能完全插入。我在哪里o给datatable.clear（）？

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()?

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?

推荐答案

如果我正确地解释了代码，你尝试将大量行作为参数传递给存储过程，然后再返回一个结果集，用作网格的数据源。



为什么你需要150万行作为参数？如果您要将大量数据插入数据库，我建议使用例如 SqlBulkCopy [ ^ ]而不是一个巨大的参数。

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.

这篇关于如何在c＃中传递stoered过程时清除数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！