Getting data from SQL Server with C#

Question

I built a C# app that reads data from a SQL Server table and then creates a .csv file with this data.

    public static DataTable getData()
    {
        ConnectToHostCMA();
        SqlDataAdapter sda = null;
        DataTable dt = new DataTable();

        dt.TableName = "dataInfo";

        try
        {
            /**************** A modifier l'instance de Sage**************/
            string rq = string.Format(@"SELECT ID_CODART_0, ID_CODUNI_0, ID_CODSIT_0, ID_CODTVA_0, ID_CODGES_0, ST_DES_0,  ID_NUMDOC1_0,ST_TYPART_0,ST_MAT_0,DT_MAJ_0,ST_TXTCDE_0,ST_CODCOM_0,ST_CLAREA_0, NU_DEL_0,ST_AFF_0,ST_RIS_0, ST_INA_0,ID_CODUTI_0, CODLONFAM_0, ID_CODFOU_0, ST_REFFOU_0, PST_ART_SIT_0 FROM TESTTUN.YART");

            sda = new SqlDataAdapter(rq, cnSqlCMA);
            sda.Fill(dt);
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (sda != null)
                sda.Dispose();

            closeConneHostCMA();
        }
        return dt;
    }

    public static void createFiles(DataTable data,  String pathImport)
    {
        try
        {
            string dateSys = DateTime.Now.ToString().Replace("/", "-").Replace(':','-');


            string nomFile = String.Format("ART-{0}", dateSys);
            string path = String.Format(@"{0}\{1}.csv", pathImport, nomFile);

            if (!File.Exists(path))
            {
                using (StreamWriter sw = File.CreateText(path))
                {
                    string ligne1 = string.Format("{0};{1};{2};{3};{4};{5};{6};{7};{8};{9};{10};{11};{12};{13};{14};{15};{16};{17};{18};{19};{20};{21}", "ID_CODART", "ID_CODUNI", "ID_CODSIT", "ID_CODTVA", "ID_CODGES", "ST_DES", "ID_NUMDOC1", "ST_TYPART", "ST_MAT", "DT_MAJ", "ST_TXTCDE", "ST_CODCOM", "ST_CLAREA", "NU_DEL", "ST_AFF", "ST_RIS", "ST_INA", "ID_CODUTI", "CODLONFAM", "ID_CODFOU", "ST_REFFOU", "PST_ART_SIT");
                    sw.WriteLine(ligne1);
                    foreach (DataRow dr in data.Rows)
                    {
                        string ID_CODART = dr["ID_CODART_0"].ToString();
                        string ID_CODUNI = dr["ID_CODUNI_0"].ToString();
                        string ID_CODSIT = dr["ID_CODSIT_0"].ToString();
                        string ID_CODTVA = dr["ID_CODTVA_0"].ToString();
                        string ID_CODGES = dr["ID_CODGES_0"].ToString();
                        string ST_DES = dr["ST_DES_0"].ToString().Replace(';','-');
                        string ID_NUMDOC1 = dr["ID_NUMDOC1_0"].ToString();
                        string ST_TYPART = dr["ST_TYPART_0"].ToString().Replace("2", "STO").Replace("1", "HST");
                        string ST_MAT = dr["ST_MAT_0"].ToString();
                        string DT_MAJ = dr["DT_MAJ_0"].ToString().Replace("1753-01-01 00:00:00.000", "2016-03-02 00:00:00.000");
                        string ST_TXTCDE = dr["ST_TXTCDE_0"].ToString();
                        string ST_CODCOM = dr["ST_CODCOM_0"].ToString();
                        string ST_CLAREA = dr["ST_CLAREA_0"].ToString();
                        string NU_DEL = dr["NU_DEL_0"].ToString();
                        string ST_AFF = dr["ST_AFF_0"].ToString();
                        string ST_RIS = dr["ST_RIS_0"].ToString();
                        string ST_INA = dr["ST_INA_0"].ToString();
                        string ID_CODUTI = dr["ID_CODUTI_0"].ToString();
                        string CODLONFAM = dr["CODLONFAM_0"].ToString();
                        string ID_CODFOU = dr["ID_CODFOU_0"].ToString();
                        string ST_REFFOU = dr["ST_REFFOU_0"].ToString();
                        string PST_ART_SIT = dr["PST_ART_SIT_0"].ToString();

                        string ligne = string.Format("{0};{1};{2};{3};{4};{5};{6};{7};{8};{9};{10};{11};{12};{13};{14};{15};{16};{17};{18};{19};{20};{21}", ID_CODART, ID_CODUNI, ID_CODSIT, ID_CODTVA, ID_CODGES, ST_DES, ID_NUMDOC1, ST_TYPART, ST_MAT, DT_MAJ, ST_TXTCDE, ST_CODCOM, ST_CLAREA, NU_DEL, ST_AFF, ST_RIS, ST_INA, ID_CODUTI, CODLONFAM, ID_CODFOU, ST_REFFOU, PST_ART_SIT);
                        sw.WriteLine(ligne);
                    }
                }
            }
        }
        catch (Exception ex)
        {

            throw;
        }
    }

My output is just like I want it, but in some cases, when there is no data, the function generates an empty .csv file. How can I get just not empty data so that the createFiles() function will not generate empty files?

Why dont you check if your datatable has any rows, if it does not then do not create the file. — peeyush singh
– peeyush singh, Commented Oct 7, 2019 at 9:05
In your createFiles˛ method, just before try, you can add if (dataTable.Rows.Count == 0) return; or something like that — Nino
– Nino, Commented Oct 7, 2019 at 9:05
@PushkarPhule it seems like the table will always be != null... — Nino
– Nino, Commented Oct 7, 2019 at 9:06
Unrelated, but you should: put using around sda and remove the explicit Dispose call from Finally; remove the pointless String.Format call; remove the pointless catch blocks that do absolutely nothing; stop changing datetime separators with Replace and directly call DateTime.Now.ToString("dd-MM-yyyy HH-mm-ss"); use Path.Combine to get the file name instead of string.Format; fix the race condition at File.Exists because the file can be created after you check, but before you open it; use better approaches to generating CSV. — GSerg
– GSerg, Commented Oct 7, 2019 at 9:07

Guy · Accepted Answer · 2019-10-07 09:07:06Z

3

Just check if the DataTable is not empty

public static void createFiles(DataTable data, String pathImport)
{
    if(data.Rows.Count == 0)
    {
        return;
    }

    // ...
}

No need to check for null, you are creating new instance in getData().

edited Oct 7, 2019 at 9:07

answered Oct 7, 2019 at 9:06

Guy

51.2k10 gold badges49 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

J. Guerra · Accepted Answer · 2019-10-07 09:08:44Z

0

You can check if the DataTable "data" in function "createFiles" has rows. For example:

if(data != null && data.Rows > 0)

answered Oct 7, 2019 at 9:08

J. Guerra

864 bronze badges

Comments

Dale K · Accepted Answer · 2019-10-07 09:14:12Z

0

Based on your question, which is

How can I get just not empty data so that the createFiles() function will not generate empty files.

I understand that you don't want to generate an empty file. That'd be possible by checking the total rows of your DataTable. The query should be look like this:

if (yourDT.Rows.Count <= 0) // Read if the query returns 0 rows
{
    // Do your code here, for example: create a new data or just ignore it and don't generate a file
}

edited Oct 7, 2019 at 9:14

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Oct 7, 2019 at 9:08

Calvin Chin

764 bronze badges

Comments

Pushkar Phule · Accepted Answer · 2019-10-07 09:15:48Z

0

before start genratting file check if your data table is not empty

if (dataTable1 .Rows.Count > 0)
{
 // your code goes here
}
else
{
    return;
}

edited Oct 7, 2019 at 9:15

answered Oct 7, 2019 at 9:06

Pushkar Phule

17011 bronze badges

Collectives™ on Stack Overflow

Getting data from SQL Server with C#

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related