Need to convert the DataTable return type to strin

Question

I have a function to return the list of tables having primary key in a datatable, but now the need is to get the table list in the string return type.

My method is as follows:

public DataTable GetAllPrimaryKeyTables 
   (string localServer, string userName, string password, string selectedDatabase)
{

    // Create the datatable 
    DataTable dtListOfPrimaryKeyTables = new DataTable("tableNames");

    SqlConnectionStringBuilder objConnectionString = new SqlConnectionStringBuilder();
    objConnectionString.DataSource = localServer; ;
    objConnectionString.UserID = userName;
    objConnectionString.Password = password;
    objConnectionString.InitialCatalog = selectedDatabase;

    // Query to select primary key tables.
    string selectPrimaryKeyTables = @"SELECT 
                                           TABLE_NAME
                                          AS
                                           TABLES
                                        FROM 
                                           INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                                       WHERE 
                                           CONSTRAINT_TYPE = 'PRIMARY KEY'
                                    ORDER BY
                                           TABLE_NAME";

    // put your SqlConnection and SqlCommand into using blocks! 
    using(SqlConnection sConnection = new SqlConnection(objConnectionString.ConnectionString))
    using(SqlCommand sCommand = new SqlCommand(selectPrimaryKeyTables, sConnection))
    {
        try
        {
            // Create the dataadapter object 
            SqlDataAdapter sDataAdapter = new SqlDataAdapter(selectPrimaryKeyTables, sConnection);

            // Fill the datatable - no need to open the connection, the SqlDataAdapter will do that all by itself  
            // (and also close it again after it is done) 
            sDataAdapter.Fill(dtListOfPrimaryKeyTables);

        }
        catch(Exception ex)
        {
            //All the exceptions are handled and written in the EventLog. 
            EventLog log = new EventLog("Application");
            log.Source = "MFDBAnalyser";
            log.WriteEntry(ex.Message);
        }
    }

    // return the data table to the caller 
    return dtListOfPrimaryKeyTables;
}

But now I want this logic to be called in the function below...I have tried but it is not done.

public class PrimaryKeyChecker : IMFDBAnalyserPlugin
{
    public string RunAnalysis(string ConnectionString)
    {
        return "string";
    }
}

I need to adjust the returntype of the function to string type and the whole logic to be covered in the RunAnalysis method

Would you guys please help me!!!

Answer 1

using System.Linq;
...

public string GetAllPrimaryKeyTableNames(string localServer, string userName, string password, string selectedDatabase) {
    DataTable table = GetAllPrimaryKeyTables(localServer, userName, password, selectedDatabase);

    return string.Join(",", table.AsEnumerable().Select(r => r.ItemArray[0]).ToArray());
}

This will return a string containing your table names separated by a comma.

EDIT

I see in your question you have your method named RunAnalysis. Feel free to change the method name in my answer to whatever you need it to be, as well as the parameters. The important part is the string.Join usage with LINQ.

Answer 2

Not sure I 100% understand your question, but it appears that all you need to do is either:

1. Run the code you've got, and convert it into a string
2. Refactor the code into datareaders and just pass it back directly, omitting the use of DataSets/DataTables.

To adapt your PrimaryKeyChecker code and return a string of tables, you could write something like this:

public string RunAnalysis(string localServer, string userName, string password, string selectedDatabase)
{
    DataTable dt = GetAllPrimaryKeyTables(localServer, userName, password, selectedDatabase);
    StringBuilder sb = new StringBuilder();
    foreach (DataRow dr in dt.Rows)
    {
        sb.AppendLine(dr.IsNull(0) ? "" : dr[0].ToString());
    }
    return sb.ToString();
}

However I would recommend at the very least, returning a List so it can be easily searched and filtered and used for presentation on the UI.

I apologise if I've completely misunderstood your question.

Answer 3

return (from rowItem in dt.AsEnumerable()
               select Convert.ToString(rowItem["TABLES"])).ToList();

Edit: returns your table names as an IList collection.

Answer 4

To convert a DataTable to a string, you can use the DataTable's ability to write itself to Xml, and then convert the Xml to a string.