bool CheckTableIsExisted(string dbPath, string tableName)
{
if (string.IsNullOrEmpty(dbPath) || !File.Exists(dbPath) ||
string.IsNullOrEmpty(tableName))
{
return(false);
}
bool rc = false;
SQLiteCommand Sql_command;
SQLiteDataReader Sql_reader;
string strCmd =
"SELECT name FROM sqlite_master " +
"WHERE type = 'table' AND name = '" + tableName + "'";
try
{
System.Data.SQLite.SQLiteConnection SqlConn = CreateSqlConnection(dbPath);
Sql_command = new SQLiteCommand(strCmd, SqlConn);
Sql_reader = Sql_command.ExecuteReader();
while (Sql_reader.Read())
{
rc = true;
}
CloseSqlConnection(SqlConn);
}
catch
{
rc = false;
}
return(rc);
}
/// <summary>
/// Get row datas by indicating table
/// </summary>
/// <param name="strTableName"></param>
/// <param name="strSqlWhere"></param>
/// <returns></returns>
List <Dictionary <string, object> > SelectTable(DatabaseName eDatabaseName, string strTableName, string strSqlWhere)
{
string databaseFilePath = GetDatabasePath(eDatabaseName);
SQLiteCommand Sql_command;
SQLiteDataReader Sql_reader;
string str_SelectCmd = string.Empty;
string setLimt = string.Empty;
SQLiteConnection SqlConn = CreateSqlConnection(databaseFilePath);
Dictionary <string, object> dicFieldValues = null;
List <Dictionary <string, object> > lstSelectResults = new List <Dictionary <string, object> >();
List <string> lstTable = getTableList(SqlConn);
if (lstTable.Contains(strTableName.ToLower()))
{
str_SelectCmd = string.Format("SELECT * FROM [{0}]", strTableName);
if (!string.IsNullOrEmpty(strSqlWhere))
{
str_SelectCmd += string.Format(" WHERE {0}", strSqlWhere);
}
Sql_command = new SQLiteCommand(str_SelectCmd, SqlConn);
Sql_reader = Sql_command.ExecuteReader();
List <object> lstObjects = new List <object>();
List <string> lstFieldNames = getFieldList(SqlConn, strTableName);
while (Sql_reader.Read())
{
dicFieldValues = new Dictionary <string, object>();
foreach (string field in lstFieldNames)
{
dicFieldValues.Add(field, Sql_reader[field]);
}
lstSelectResults.Add(dicFieldValues);
}
}
//return list_rtn;
return(lstSelectResults);
}
List <Dictionary <string, object> > SelectTable(DatabaseName eDatabaseName, string strTableName, List <string> lstCol, string strSqlWhere)
{
string databaseFilePath = GetDatabasePath(eDatabaseName);
SQLiteCommand Sql_command;
SQLiteDataReader Sql_reader;
string str_SelectCmd = string.Empty;
string setLimt = string.Empty;
SQLiteConnection SqlConn = CreateSqlConnection(databaseFilePath);
Dictionary <string, object> dicFieldValues = null;
List <Dictionary <string, object> > lstSelectResults = new List <Dictionary <string, object> >();
// set columns we want to select
string strCol = string.Empty;
if (lstCol != null)
{
foreach (string col in lstCol)
{
strCol += col + " ";
}
}
if (!string.IsNullOrEmpty(strCol.Trim()))
{
strCol = strCol.Trim().Replace(" ", ", ");
}
else
{
strCol = "*";
}
List <string> lstTable = getTableList(SqlConn);
if (lstTable.Contains(strTableName.ToLower()))
{
str_SelectCmd = string.Format("SELECT {0} FROM {1}", strCol, strTableName);
if (!string.IsNullOrEmpty(strSqlWhere))
{
str_SelectCmd += string.Format(" WHERE {0}", strSqlWhere);
}
Sql_command = new SQLiteCommand(str_SelectCmd, SqlConn);
Sql_reader = Sql_command.ExecuteReader();
List <object> lstObjects = new List <object>();
List <string> lstFieldNames = getFieldList(SqlConn, strTableName);
while (Sql_reader.Read())
{
if (strCol.Equals("*"))
{
dicFieldValues = new Dictionary <string, object>();
foreach (string field in lstFieldNames)
{
dicFieldValues.Add(field, Sql_reader[field]);
}
lstSelectResults.Add(dicFieldValues);
}
else
{
dicFieldValues = new Dictionary <string, object>();
foreach (string field in lstCol)
{
dicFieldValues.Add(field, Sql_reader[field]);
}
lstSelectResults.Add(dicFieldValues);
}
}
}
return(lstSelectResults);
}
