Click here to Skip to main content
14,735,271 members
Please Sign up or sign in to vote.
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
Updated 21-Sep-20 5:54am
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

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));
}
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900