Export sql data to xml and csv

Some quick remarks:

• Don't start variable names with underscores unless they're class-wide private ones.

• Local variables should be camelCased. OutputXML doesn't follow that rule.

• Split your code into smaller methods that don't intermix data retrieval and file writing. Your method does multiple things, depending on some (odd) logic. Even its name doesn't make sense: it's called SqlExtract yet it generates CSV or XML files depending on seemingly arbitrary conditions.

• Don't hardcode your connection string. Put it in a .config file and access it via the ConfigurationManager.

• Why do _con.Open(); and _con.Close();? The using takes care of this.

• Don't loop through table1.Rows. Instead have a method that transforms a DataRow row into a csv line and use that to construct a IEnumerable<T>, which you then can use in combination with string.Join(). Something like var csvContents = string.Join(Environment.NewLine, table1.Rows.Cast<DataRow>().Select(x => DataRowToCsvLine(x)));. That's one line (instead of 20) which expresses far better what your code does (and yes, the DataRowToCsvLine method is also a couple of lines, but it doesn't pollute the main logic).

• row[1].ToString() == "END": are you sure it is always going to be "END"? Or is it possible it can be "end"? Consider using string.Equals() instead, which allows for case insensitive comparisons. Same for fileName == "item1" etc.

• Properly dispose of StringWriter with a using block. Example: https://social.msdn.microsoft.com/Forums/vstudio/en-US/7d949d5c-a41c-4dc7-bbcb-429761f851d1/is-calling-flush-and-close-necesary-for-stringwriter-and-xmltextwriter?forum=netfxbcl

None of the above might improve the speed of your code, but it will make it much more maintainable and consistent.

Something along these lines: where your "entry point" is extension method DataTable.SqlToFile() (see DoWork below as a usage example). I'd suggest breaking your main logic of "serializing" datatable as xml/csv into separate methods - SqlToFile (saves "serialized" datatable into a file), SqlExtract (extracts datatable rows as xml or csv), and 2 separate methods SqlAsXML() and SqlAsCSV() to get xml and csv strings respectively. To serialize into xml, I'm using GetXML() ( or you can use WriteXML() as you did). And to serialize into csv, by using StringBuilder() and iterating in Rows, "saving" each row as comma-delimited string.

In addition, data access logic is implemented as its own class.

public static class SQLExtensions
{
    public static bool SqlToFile(this System.Data.DataTable dt, string xFilePath, string fileName)
    {
        try
        {
            string result = dt.SqlExtract(fileName);
            System.IO.File.WriteAllText(xFilePath, result);
            return true;
        }
        catch (System.IO.IOException ex)
        {
            return false;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public static string SqlExtract(this System.Data.DataTable dt, string fileName)
    {
        string result = String.Empty;
        var xmlfiles = new[] { "item1", "item2", "item3" };
        if (xmlfiles.Contains(fileName))
        {
            result = dt.SqlAsXML(fileName);
        }
        else
        {
            string delimiter = ";";
            result = dt.FilterData().SqlAsCSV(delimiter);
        }
        return result;
    }

    public static string SqlAsXML(this System.Data.DataTable dt, string fileName)
    {
        var ds = new System.Data.DataSet();
        ds.Tables.Add(dt);
        string xml = ds.GetXml();
        return xml;
    }
    #region CSV related
    public static IEnumerable<System.Data.DataRow> FilterData(this System.Data.DataTable dtIns)
    {
        var list = dtIns
                        .Rows.Cast<System.Data.DataRow>()
                        .Where(
                                r => !((r[1].ToString() == "002" && dtIns.Rows.IndexOf(r) > 41) || (r[1].ToString() == "END" && dtIns.Rows.IndexOf(r) > 4))
                            )
                        ;
        return list;
    }
    public static string SqlAsCSV(this System.Data.DataTable dt, string delimiter = ",")
    {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < dt.Rows.Count - 1; i++)
        {
            System.Data.DataRow dr = dt.Rows[i];
            sb.AppendLine(dr.RowDataAsCSV(delimiter));
        }
        return sb.ToString();
    }
    public static string SqlAsCSV(this IEnumerable<System.Data.DataRow> rows, string delimiter = ",")
    {
        StringBuilder sb = new StringBuilder();
        foreach (var row in rows)
        {
            sb.AppendLine(row.RowDataAsCSV(delimiter));
        }
        return sb.ToString();
    }
    public static string RowDataAsCSV(this System.Data.DataRow row, string delimiter = ",")
    {
        string rowdata = String.Join(delimiter, row.ItemArray.Select(f => f.ToString()));
        return rowdata;
    }
    #endregion
}

public class DataAccess
{
    public async Task< System.Data.DataTable> DataAsync(string connectionInfo, string commandSQL)
    {
        System.Data.DataTable dt = new System.Data.DataTable("data");

        using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionInfo))
        {
            try
            {
                await connection.OpenAsync().ConfigureAwait(false);
            }
            catch (InvalidOperationException ex)
            {
                return null;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                return null;
            }
            using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(commandSQL, connection))
            {
                using (var r = await command.ExecuteReaderAsync(System.Data.CommandBehavior.SequentialAccess).ConfigureAwait(false))
                {
                    dt.Load(r);
                }
            }
            connection.Close();
        }

        return dt;
    }
}

public class MainMainMain
{
    public async Task DoWork(string commandSQL, string xFilePath, string fileName)
    {
        string connectionString = @"Data Source=ipaddress; Initial Catalog=name; User ID=username; Password=password";
        var da = new DataAccess();
        var dt = await da.DataAsync(connectionString, commandSQL ).ConfigureAwait(false);
        if (dt != null)
        {
            bool converted = dt.SqlToFile(xFilePath, fileName);
        }
        else
        {
            //oh-ho
        }
    }
}

• furthermore, you could add common interface, and have AsXML and AsCSV as implmentations.