How do I edit the strings inside of individual datatables inside of a dictionary in C#?

Question

0.00/5 (No votes)

See more:

I am trying to write a script in C# that lets me grab information from a SQLite database, clean out the information (i.e. remove symbols like |, ~, /,\, etc.) and store that into a SQL Server Database. SO far, I am able to grab information from a SQLite database in the form of turning each table in the SQLite database into a DataTable class and in order to store the whole database, I stored those DataTable classes inside of a Dictionary where the key is the table name and the value is the DataTable.

What I have tried:

This is my current code

public static IDictionary<string, DataTable> GetSQLiteData(string liteConString, IList<string> tableNames)
{
    SQLiteConnection liteCon = new SQLiteConnection(liteConString);
    liteCon.Open();

    IDictionary<string, DataTable> allTables = new Dictionary<string, DataTable>();

    foreach (string table in tableNames)
    {
        string selectQuery = "SELECT * FROM " + table;
        allTables.Add(table, new DataTable());

        SQLiteCommand cmd = new SQLiteCommand(selectQuery, liteCon);
        SQLiteDataAdapter sqliteDAP = new SQLiteDataAdapter(cmd);
        sqliteDAP.Fill(allTables[table]);
    }

    return allTables;
}

The value of the DataTable properties inside of the allTables dictionary are stored within the Rows value of DataTable, so how would I go about editing each individual row inside of DataTable to remove symbols such as |, ~, -, etc.

Posted 21-Sep-20 3:24am

stevenlam505

Updated 21-Sep-20 4:54am

Add a Solution

Comments

BillWoodruff 21-Sep-20 9:51am

do the DataTables have columns of different Types ... i.e. Int, String ... and you want to edit only those columns/cells of Type String ? or, do you know the names of specific columns that contain strings ? is each datatable identical in structure ?

stevenlam505 21-Sep-20 9:58am

Each datatable is different however I do know what specific columns in each datatable I want to alter. All the columns in each DataTable are strings.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BillWoodruff · Accepted Answer · 2020-09-21T04:54:00

I'm going to answer this a little more elaborately than your scenario ... "All the columns in each DataTable are strings" ... requires, in the hope it will be useful to others.

Suppose only certain columns are Type String: select all Columns of Type String:

IEnumerable<DataColumn> stringcolumns = table.Columns.Cast<DataColumn>().Where((DataColumn clm) => clm.DataType == typeof(string));

You already know how to loop through all the rows, then loop though all the columns in each row.

So, in the innermost loop, you can change or remove whatever:

string value = row[col].ToString();

if (value.Contains('|'))
{
    row.SetField(col, value.Replace("|", string.Empty));
}