public static DataTable GetColumnStatus(this SQLiteConnection @this, string tableName)
{
tableName = SQLiteString.QuoteIdentifier(tableName);
var sql = $"PRAGMA table_info({tableName});";
return(@this.Select(sql));
}
public static void CopyAllData(this SQLiteConnection @this, string tableFrom, string tableTo)
{
tableFrom = SQLiteString.QuoteIdentifier(tableFrom);
tableTo = SQLiteString.QuoteIdentifier(tableTo);
DataTable dt1 = @this.Select(string.Format("select * from {0} where 1 = 2;", tableFrom));
DataTable dt2 = @this.Select(string.Format("select * from {0} where 1 = 2;", tableTo));
var dic = new Dictionary <string, bool>();
foreach (DataColumn dataColumn in dt1.Columns)
{
if (dt2.Columns.Contains(dataColumn.ColumnName))
{
if (!dic.ContainsKey(dataColumn.ColumnName))
{
dic[dataColumn.ColumnName] = true;
}
}
}
foreach (DataColumn dataColumn in dt2.Columns)
{
if (dt1.Columns.Contains(dataColumn.ColumnName))
{
if (!dic.ContainsKey(dataColumn.ColumnName))
{
dic[dataColumn.ColumnName] = true;
}
}
}
var sb = new StringBuilder();
foreach (var kvp in dic)
{
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append("`");
sb.Append(kvp.Key);
sb.Append("`");
}
var sb2 = new StringBuilder();
sb2.Append("insert into `");
sb2.Append(tableTo);
sb2.Append("`(");
sb2.Append(sb.ToString());
sb2.Append(") select ");
sb2.Append(sb.ToString());
sb2.Append(" from `");
sb2.Append(tableFrom);
sb2.Append("`;");
@this.ExecuteNonQuery(sb2.ToString());
}
public static void RenameTable(this SQLiteConnection @this, string tableFrom, string tableTo)
{
tableFrom = SQLiteString.QuoteIdentifier(tableFrom);
tableTo = SQLiteString.QuoteIdentifier(tableTo);
var sql = $"alter table {tableFrom} rename to {tableTo};";
@this.ExecuteNonQuery(sql);
}
public static bool TableExists(this SQLiteConnection @this, string tableName)
{
if (string.IsNullOrEmpty(tableName))
{
return(false);
}
tableName = SQLiteString.QuoteIdentifier(tableName);
var sql = $"SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = {tableName}";
object result = @this.ExecuteScalar(sql);
if (result is long rows)
{
return(rows == 1);
}
return(false);
}
public static StringBuilder AppendQuotedLiteral(this StringBuilder @this, string value)
{
value = SQLiteString.QuoteLiteral(value);
@this.Append(value);
return(@this);
}
public static StringBuilder AppendQuotedIdentifier(this StringBuilder @this, string value)
{
value = SQLiteString.QuoteIdentifier(value);
@this.Append(value);
return(@this);
}
public static void DropTable(this SQLiteConnection @this, string tableName)
{
tableName = SQLiteString.QuoteIdentifier(tableName);
@this.ExecuteNonQuery($"drop table if exists {tableName};");
}