Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to get column name (header) in SQLite using C#?

Tags:

c#

sqlite

wpf

Is it possible to get column names(Header) if table and columns are generated code'behind in sqlite?

tried this but it fail:

SQLiteCommand cmd = new SQLiteCommand();

string sSQL = "Select * from tblUser Where username = '" + txtUsername.Text + "'";
cmd.CommandText = sSQL;
cmd.Connection = clsCon.con;
SQLiteDataReader dr2;
dr2 = cmd.ExecuteReader();
string columnName = dr2.GetName(1);
dr2.Read();

if (dr2.HasRows)
{
    MessageBox.Show("Username Already Exist!", "SQLite Test Application", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    txtUsername.Focus();
}
like image 695
Jesson Avatar asked Jun 24 '13 20:06

Jesson


2 Answers

1) make sure the db is open

2) make sure the command is hooked up with the connection

3) make sure you are not getting any errors

4) loop through the column names

var cmd = new SQLiteCommand("select * from t1", db);
var dr = cmd.ExecuteReader();
for (var i = 0; i < dr.FieldCount; i++)
{
    Console.WriteLine(dr.GetName(i));
}
like image 176
muratgu Avatar answered Oct 17 '22 01:10

muratgu


Based on the answer provided by muratgu I created the following method:

/// <summary>
/// Checks if the given table contains a column with the given name.
/// </summary>
/// <param name="tableName">The table in this database to check.</param>
/// <param name="columnName">The column in the given table to look for.</param>
/// <param name="connection">The SQLiteConnection for this database.</param>
/// <returns>True if the given table contains a column with the given name.</returns>
public static bool ColumnExists(string tableName, string columnName, SQLiteConnection connection)
{
    var cmd = new SQLiteCommand($"PRAGMA table_info({tableName})", connection);
    var dr = cmd.ExecuteReader();
    while (dr.Read())//loop through the various columns and their info
    {
        var value = dr.GetValue(1);//column 1 from the result contains the column names
        if (columnName.Equals(value))
        {
            dr.Close();
            return true;
        }
    }

    dr.Close();
    return false;
}
like image 37
Peet Avatar answered Oct 17 '22 00:10

Peet