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