CODEWITHSUNDEEP
c#sql-servercsvdatatableado.net
Soulef
Soulef

Reputation: 41

Getting data from SQL Server with C#

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?

Upvotes: 0

Views: 110

Answers (4)

Pushkar Phule
Pushkar Phule

Reputation: 170

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

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

Upvotes: 0

Calvin Chin
Calvin Chin

Reputation: 76

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
}

Upvotes: 0

J. Guerra
J. Guerra

Reputation: 86

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

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

Upvotes: 0

Guy
Guy

Reputation: 50899

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

Upvotes: 3

Related Questions