Solved

Dataset to CSV

Posted on 2010-09-07
8
561 Views
Last Modified: 2012-08-13
Hello
I have a situation here
I need to query around 300,000 or more rows of data and save the same in csv format. Right now i am running a code but it is successful till 1,50,000 rows. Please tell me any way to get  the whole data in optimized format.

On database i have only select privilage so cannot create Stored procedures;.

thanks and sos

Senz
public void ExecuteSQL(string Query, string strConn)

        {

            try

            {

                

                DataSet dsTable = new DataSet();



                if (strConn == "Success")

                {

                    objClsDB.conDB2.ConnectionTimeout = 0;

                    

                    objClsDB.conDB2.Open();



                    

                    

                    objClsDB.cmdDB2.CommandText = Query;

                    



                    



                    //OleDbDataAdapter da = new OleDbDataAdapter(objClsDB.cmd.CommandText, objClsDB.con);

                    //OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(da);



                    DB2DataAdapter da = new DB2DataAdapter(objClsDB.cmdDB2.CommandText, objClsDB.conDB2);

                    DB2CommandBuilder cBuilder = new DB2CommandBuilder(da);

                    



                    DataTable QTable = new DataTable();

                    QTable.Locale = System.Globalization.CultureInfo.InvariantCulture;

                    

                    da.SelectCommand.CommandTimeout = 5000;

                    da.Fill(dsTable, "RCCM");

                    //da.Fill(QTable);

                    

                   



                    objClsDB.cmdDB2.Dispose();

                    objClsDB.conDB2.Close();

                    objClsDB.conDB2.Dispose();



                    //dgReportResult.DataSource = dsTable;

                    //dgReportResult.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);

                    if (dgReportResult.RowCount > 1)

                    {

                        if (Directory.Exists(@"C:\RCCM"))

                        {

                            DataTable dtTable = dsTable.Tables[0];

                            RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win");

                            objExport.ExportDetails(dtTable, Export.ExportFormat.CSV, "C:\\RCCM\\RCCM_Report.csv");

                            dsTable.Clear();

                            MessageBox.Show(@"RCCM Repository Created at C:\RCCM\.Remove the file to other location to save data.");

                        }

                        else

                        {

                            Directory.CreateDirectory(@"C:\RCCM");

                            MessageBox.Show(@"RCCM Repository Created at C:\RCCM.All the reports will be generated at the created location.");

                        }

                    }                   

                }

                else

                {



                }

            }

            catch (Exception Ex)

            {

                MessageBox.Show(Ex.Message);

            }

        }

Open in new window

0
Comment
Question by:Senz79
  • 4
  • 4
8 Comments
 
LVL 4

Author Comment

by:Senz79
ID: 33618136
The code hands at the line
                    da.Fill(dsTable, "RCCM");
 
0
 
LVL 16

Expert Comment

by:kris_per
ID: 33618678

da.Fill will try to fill all the data in the DataSet and when data is large, this will take more resources and might slow down the system and even might cause issues like yours....

Alternatively try using DataReader....
0
 
LVL 4

Author Comment

by:Senz79
ID: 33618772
There are  around 20 columns from which data is extracted i.e the output will have 20 columns will dataraeader help, Please assist
 

Select 

db2inst1.cm_computer_systems_v.IP_ADDRESS_STRING as IP_Address,

db2inst1.cm_computer_systems_v.FDQN as Domain_Name,

db2inst1.cm_computer_systems_v.SYSTEM_TYPE as Server_Type,

db2inst1.cm_computer_systems_v.FILE_SYSTEM as File_System,

db2inst1.cm_computer_systems_v.OS_NAME as Operating_System_Name,

db2inst1.BB_OPERATINGSYSTEM62_V.OSVERSION_C as Operating_System_Version,

db2inst1.cm_computer_systems_v.CPU_TYPE as CPU_Type,

db2inst1.cm_computer_systems_v.NUM_CPUS as Number_Of_CPU,

db2inst1.cm_computer_systems_v.CPU_SPEED as CPU_Speed,

db2inst1.cm_computer_systems_v.MEMORY_SIZE as Memory_Size,

db2inst1.cm_computer_systems_v.AVAILABLE_SPACE as Available_Space,

db2inst1.cm_computer_systems_v.MANUFACTURER as Manufacturer,

db2inst1.cm_computer_systems_v.MODEL as Model,

db2inst1.cm_computer_systems_v.SERIAL_NUMBER as Serial_Number,



db2inst1.CM_APP_SERVERS_PER_HOST_V.TYPE As Software_Type, 

db2inst1.CM_APP_SERVERS_PER_HOST_V.PRODUCT_NAME as Software_Product_Name,

db2inst1.CM_APP_SERVERS_PER_HOST_V.PRODUCT_VERSION as Software_Product_Version,

db2inst1.CM_APP_SERVERS_PER_HOST_V.NAME,

db2inst1.CM_APP_SERVERS_PER_HOST_V.CLASS_NAME,

db2inst1.CM_APP_SERVERS_PER_HOST_V.KEY_NAME,



db2inst1.BB_OPERATINGSYSTEM62_V.KERNELARCHITECTURE_C as Kernel_Architecture,

db2inst1.BB_OPERATINGSYSTEM62_V.KERNELVERSION_C as Version,

db2inst1.BB_COMPUTERSYSTEM40_V.ARCHITECTURE_C as Architecture,

db2inst1.BB_COMPUTERSYSTEM40_V.CREATEDBY_C as Created_By,

db2inst1.BB_COMPUTERSYSTEM40_V.LASTMODIFIEDTIME_C as Last_Modified,

db2inst1.BB_COMPUTERSYSTEM40_V.SIGNATURE_C as Signature,

db2inst1.BB_COMPUTERSYSTEM40_V.LASTMODIFIEDTIME_C as Scan_Date







from

db2inst1.cm_computer_systems_v





Left Outer Join db2inst1.BB_OPERATINGSYSTEM62_V on db2inst1.cm_computer_systems_v.FDQN = db2inst1.BB_OPERATINGSYSTEM62_V.DISPLAYNAME_C

Left Outer Join db2inst1.BB_COMPUTERSYSTEM40_V on db2inst1.cm_computer_systems_v.FDQN = db2inst1.BB_COMPUTERSYSTEM40_V.DISPLAYNAME_C

Left Outer Join db2inst1.CM_APP_SERVERS_PER_HOST_V on db2inst1.cm_computer_systems_v.FDQN = db2inst1.CM_APP_SERVERS_PER_HOST_V.FQDN

Where 

db2inst1.cm_computer_systems_v.IP_ADDRESS_STRING IS NOT NULL



order by db2inst1.cm_computer_systems_v.FDQN desc

Open in new window

0
 
LVL 16

Expert Comment

by:kris_per
ID: 33619031
It would not be an issue, you can write to csv file even without knowing how many columns are there and without knowing the col names. sample code is below:
string connString = "conn string here";



            using (OleDbConnection conn = new OleDbConnection(connString))

            {

                conn.Open();



                string sqlQuery = "SELECT query here.....";

                OleDbCommand command = new OleDbCommand(sqlQuery, conn);



                OleDbDataReader reader = command.ExecuteReader();



                TextWriter textWriter = new StreamWriter("RCCM_Report.csv");

                // write column headers if you want



                while (reader.Read())

                {

                    StringBuilder sb = new StringBuilder();



                    for (int f = 0; f < reader.FieldCount; f++)

                    {

                        string fieldValue = reader[f].ToString();

                        if(fieldValue.Contains(',')) // if value contains comma, enclose the value within quotes

                        {

                            fieldValue = "\"" + fieldValue + "\"";

                        }

                        sb.Append(fieldValue);

                        sb.Append(",");

                    }



                    textWriter.WriteLine(sb.ToString());                    

                }



                reader.Close();

            }

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 16

Expert Comment

by:kris_per
ID: 33619071

Plus to make it more user responsive....you can put this code in a thread and show a progressbar/cancel and in the loop handle the cancel and progress update....
0
 
LVL 4

Author Comment

by:Senz79
ID: 33619085
great let me try i will take some time.....
0
 
LVL 4

Author Comment

by:Senz79
ID: 33619711
It Worked Awesome but is it possible to keep the header or the column names too.
0
 
LVL 16

Accepted Solution

by:
kris_per earned 500 total points
ID: 33620910
Below is the code that adds the col names as header line to csv file:
... // code not shown here

OleDbDataReader reader = command.ExecuteReader();



                TextWriter textWriter = new StreamWriter("RCCM_Report.csv");

                

                string headerLine = string.Empty;

                for (int h = 0; h < reader.FieldCount; h++)

                {

                    headerLine += reader.GetName(h) + ",";

                }



                textWriter.WriteLine(headerLine);



                while (reader.Read())

                {

                    StringBuilder sb = new StringBuilder();



... // code not shown here

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now