sql extract data

sql extract data

• Rada

  You can use READTEXT to read a specific block of a content, here is a self-explaning example:
  
  

  public void SqlChunkBlob2File(string DestFilePath) { try { int PictureCol = 0; // position of Picture column in DataReader int BUFFER_LENGTH = 32768; // chunk size SqlConnection cn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;"); // Make sure Photo is non-NULL and return TEXTPTR to it. SqlCommand cmdGetPointer = new SqlCommand("SELECT @Pointer=TEXTPTR(Picture), @Length=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cn); SqlParameter PointerOutParam = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100); PointerOutParam.Direction = ParameterDirection.Output; SqlParameter LengthOutParam = cmdGetPointer.Parameters.Add("@Length", SqlDbType.Int); LengthOutParam.Direction = ParameterDirection.Output; cn.Open(); cmdGetPointer.ExecuteNonQuery(); if(PointerOutParam.Value == null) { cn.Close(); // Add code to handle NULL BLOB. return; } // Set up READTEXT command, parameters, and open BinaryReader. SqlCommand cmdReadBinary = new SqlCommand("READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK", cn); SqlParameter PointerParam = cmdReadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16); SqlParameter OffsetParam = cmdReadBinary.Parameters.Add("@Offset", SqlDbType.Int); SqlParameter SizeParam = cmdReadBinary.Parameters.Add("@Size", SqlDbType.Int); SqlDataReader dr; System.IO.FileStream fs = new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write); int Offset= 0; OffsetParam.Value = Offset; Byte []Buffer = new Byte[BUFFER_LENGTH ]; // Read buffer full of data and write to the file stream. do { PointerParam.Value = PointerOutParam.Value; // Calculate buffer size - may be less than BUFFER_LENGTH for last block. if( (Offset + BUFFER_LENGTH) >= System.Convert.ToInt32(LengthOutParam.Value)) SizeParam.Value = System.Convert.ToInt32(LengthOutParam.Value) - Offset; else SizeParam.Value = BUFFER_LENGTH; dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult); dr.Read(); dr.GetBytes(PictureCol, 0, Buffer, 0, System.Convert.ToInt32(SizeParam.Value)); dr.Close(); fs.Write(Buffer, 0, System.Convert.ToInt32(SizeParam.Value)); Offset += System.Convert.ToInt32(SizeParam.Value); OffsetParam.Value = Offset; }while(Offset < System.Convert.ToInt32(LengthOutParam.Value)); fs.Close(); cn.Close(); } catch(SqlException ex) { MessageBox.Show (ex.Message); } }

  
  
  
  

• MikeBzz

  If you have a 'record id' field in your data table then you can do something like this. Sorry for my pseudo-code, I hope you can understand it.
  
  
  block = 10000
  top = block
  bottom = 0
  
  while (true)
  {
  
  select * from mytable where record_id > bottom and record_id <= top
  
  if ( number of retreived records < block)
  {
  quit
  }
  
  convert records into XML and write out to file
  
  bottom = top
  top = top + block
  
  }
  
  
  
  
• sql extract data