internal static DataTable ToDataTable(this LightDataTable source)
{
if (ReferenceEquals(source, null))
{
return(null);
}
var result = new DataTable(source.TableName);
try
{
foreach (var column in source.Columns)
{
result.Columns.Add(column.ColumnName, column.DataType);
}
foreach (var row in source.Rows)
{
var dr = result.Rows.Add();
for (int i = 0; i < source.Columns.Count; i++)
{
dr[i] = row.GetValue(i);
}
}
}
catch (Exception)
{
result.Dispose();
throw;
}
return(result);
}
private async Task <DbTableSchema> GetDbTableSchemaAsync(SQLiteConnection conn, LightDataRow tableStatusRow,
LightDataTable indexData, CancellationToken cancellationToken)
{
var result = new DbTableSchema
{
Name = tableStatusRow.GetString(0).Trim(),
Fields = new List <DbFieldSchema>()
};
var indexes = indexData.Rows.Where(
row => row.GetStringOrDefault(1, string.Empty).EqualsByConvention(result.Name)).ToList();
var foreignKeys = await MyAgent.FetchUsingConnectionAsync(conn, string.Format("PRAGMA foreign_key_list({0});", result.Name),
cancellationToken).ConfigureAwait(false);
var fieldsData = await MyAgent.FetchUsingConnectionAsync(conn, string.Format("PRAGMA table_info({0});", result.Name),
cancellationToken).ConfigureAwait(false);
var createSql = tableStatusRow.GetString(1);
createSql = createSql.Replace("[", "").Replace("]", "").Substring(createSql.IndexOf("(") + 1);
createSql = createSql.Substring(0, createSql.Length - 1);
foreach (var row in fieldsData.Rows)
{
result.Fields.Add(this.GetDbFieldSchema(row, indexes, foreignKeys,
createSql.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries), result.Name));
}
return(result);
}
/// <summary>
/// Convert Value from Type to Type
/// when fail a default value will be loaded.
/// can handle all known types like datetime, time span, string, long etc
/// ex
/// "1115rd" to int? will return null
/// "152" to int? 152
/// </summary>
/// <param name="value"></param>
/// <param name="toType"></param>
/// <returns></returns>
public static object ConvertValue(this object value, Type toType)
{
var data = new LightDataTable();
data.AddColumn("value", toType, value);
data.AddRow(new object[1] {
value
});
return(data.Rows.First().TryValueAndConvert(toType, "value", true));
}
/// <summary>
/// Convert From type to another type,
/// make sure to have the same propertyNames in both or you could also map them by PropertyName Attributes
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <returns></returns>
public static T ToType <T>(this object o)
{
object item;
if (typeof(T) == typeof(IList))
{
return((T)(item = new LightDataTable(o).Rows.Select(x => ((IList)x.ToObject <T>())[0]).ToList()));
}
else
{
return(new LightDataTable(o).Rows.First().ToObject <T>());
}
}
public DbCommandExtended(DbCommand cmd, IRepository provider, Type tableType = null)
{
Provider = provider;
_cmd = cmd;
TableType = tableType?.GetActualType();
if (TableType != null)
{
DataStructure = new LightDataTable(TableType.CreateInstance(), true, null, false, true);
}
else
{
DataStructure = new LightDataTable();
}
}
private static bool FindForeignKey(DbFieldSchema schema, string table,
LightDataTable foreignKeys, string[] createTableSql)
{
var row = foreignKeys.Rows.FirstOrDefault(dr => dr.GetString(3).EqualsByConvention(schema.Name));
if (row.IsNull())
{
return(false);
}
schema.IndexType = DbIndexType.ForeignKey;
schema.IndexName = string.Format("{0}_{1}_fk", table, schema.Name);
schema.RefTable = row.GetString(2).Trim();
schema.RefField = row.GetString(4).Trim();
var action = row.GetString(5);
if (action.EqualsByConvention(DbSchemaExtensions.NativeActionType_SetNull))
{
schema.OnUpdateForeignKey = DbForeignKeyActionType.SetNull;
}
else if (action.EqualsByConvention(DbSchemaExtensions.NativeActionType_Cascade))
{
schema.OnUpdateForeignKey = DbForeignKeyActionType.Cascade;
}
else
{
schema.OnUpdateForeignKey = DbForeignKeyActionType.Restrict;
}
action = row.GetString(6);
if (action.EqualsByConvention(DbSchemaExtensions.NativeActionType_SetNull))
{
schema.OnDeleteForeignKey = DbForeignKeyActionType.SetNull;
}
else if (action.EqualsByConvention(DbSchemaExtensions.NativeActionType_Cascade))
{
schema.OnDeleteForeignKey = DbForeignKeyActionType.Cascade;
}
else
{
schema.OnDeleteForeignKey = DbForeignKeyActionType.Restrict;
}
return(true);
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="table">Table</param>
/// <param name="index">Row index</param>
public LightDataRow(LightDataTable table, int index)
{
this.Table = table;
RowIndex = index;
}
private LightDataTable GetParadigmControlDataHelper(string dataSourceKey)
{
LightDataTable toReturn = new LightDataTable();
string[] arrDataSourceKey = dataSourceKey.Split(new char[] { '|' });
string sourceDB = arrDataSourceKey[0].ToLower();
switch (sourceDB)
{
case "none":
toReturn.Status = "GOOD";
break;
default:
string connStringName = sourceDB + "DB";
ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[connStringName];
if (css == null)
{
toReturn.Status = "ERROR";
toReturn.ErrorMessage = "Unrecognized source DB: " + sourceDB;
break;
}
SqlConnection conn = null;
var dsData = new DataSet();
string procName = arrDataSourceKey[2]; // ex: DBName|sp|MyStoredProcName
try
{
string connString = css.ConnectionString;
conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand(procName, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = conn.ConnectionTimeout;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dsData);
toReturn = LightData.LightDataConverter.ConvertDataSetToLightDataTable(dsData);
toReturn.Status = "GOOD";
}
catch (Exception ex)
{
toReturn.Status = "ERROR";
toReturn.ErrorMessage = ex.Message + ", " + ex.StackTrace;
}
finally
{
if (conn != null)
conn.Close();
}
break;
}
return toReturn;
}
private DbFieldSchema GetDbFieldSchema(LightDataRow fieldStatusRow,
List <LightDataRow> indexes, LightDataTable foreignKeys, string[] createTableSql,
string tableName)
{
var result = new DbFieldSchema
{
Name = fieldStatusRow.GetString(1).Trim(),
NotNull = (fieldStatusRow.GetInt32(3) > 0),
Unsigned = false,
Description = string.Empty,
};
result.Autoincrement = createTableSql.Any(line => line.Trim().StartsWith(
result.Name + " ", StringComparison.OrdinalIgnoreCase) && line.ContainsByConvention("AUTOINCREMENT"));
var rawType = fieldStatusRow.GetString(2).Trim();
result.DataType = rawType.GetBaseDataType();
var typeDetails = "";
if (rawType.Contains("("))
{
typeDetails = rawType.Substring(rawType.IndexOf("(", StringComparison.Ordinal) + 1,
rawType.IndexOf(")", StringComparison.Ordinal)
- rawType.IndexOf("(", StringComparison.Ordinal) - 1)
.Trim()
.Replace("`", "").Replace("'", "").Replace("\"", "");
}
if (!string.IsNullOrEmpty(typeDetails) && (result.DataType == DbDataType.Char ||
result.DataType == DbDataType.VarChar))
{
result.Length = 255;
if (int.TryParse(typeDetails, out int length))
{
result.Length = length;
}
}
if (fieldStatusRow.GetInt32(5) > 0)
{
result.IndexType = DbIndexType.Primary;
if (FindForeignKey(result, tableName, foreignKeys, createTableSql))
{
result.IndexType = DbIndexType.ForeignPrimary;
}
}
else
{
if (!FindForeignKey(result, tableName, foreignKeys, createTableSql))
{
if (!FindIndex(result, tableName, indexes))
{
result.IndexType = DbIndexType.None;
}
}
}
return(result);
}
