public InstantMultemic GetBank(string sqlConnectString, string sqlQry, string tableName, IDeck <string> keyNames) { try { if (DbHand.Schema == null || DbHand.Schema.DbTables.Count == 0) { InstantSql sqb = new InstantSql(sqlConnectString); } SqlAdapter sqa = new SqlAdapter(sqlConnectString); try { return(sqa.ExecuteInject(sqlQry, tableName, keyNames)); } catch (Exception ex) { throw new SqlException(ex.ToString()); } } catch (Exception ex) { throw ex; } }
public int SimpleUpdate(IMultemic table, bool buildMapping = false, bool updateKeys = false, string[] updateExcept = null, int batchSize = 500) { try { IMultemic tab = table; IList <FieldMapping> nMaps = new List <FieldMapping>(); SqlAdapter afad = new SqlAdapter(_cn); StringBuilder sb = new StringBuilder(); int intSqlbank = 0; sb.AppendLine(@" /* ---- SQL BANK START CMD ------ */ "); int count = 0; foreach (IFigure ir in table) { if (ir.GetType().DeclaringType != tab.FigureType) { if (buildMapping) { SqlMapper imapper = new SqlMapper(tab); } nMaps = tab.Rubrics.Mappings; } foreach (FieldMapping nMap in nMaps) { IDeck <int> co = nMap.ColumnOrdinal; IDeck <int> ko = nMap.KeyOrdinal; MemberRubric[] ic = tab.Rubrics.AsValues().Where(c => nMap.ColumnOrdinal.Contains(c.FigureFieldId)).ToArray(); MemberRubric[] ik = tab.Rubrics.AsValues().Where(c => nMap.KeyOrdinal.Contains(c.FigureFieldId)).ToArray(); if (updateExcept != null) { ic = ic.Where(c => !updateExcept.Contains(c.RubricName)).ToArray(); ik = ik.Where(c => !updateExcept.Contains(c.RubricName)).ToArray(); } string qry = BatchUpdateQuery(ir, nMap.DbTableName, ic, ik, updateKeys).ToString(); sb.Append(qry); count++; } if (count >= batchSize) { sb.AppendLine(@" /* ---- DATA BATCH EDataD CMD ------ */ "); intSqlbank += afad.ExecuteUpdate(sb.ToString()); sb.Clear(); sb.AppendLine(@" /* ---- SQL BANK START CMD ------ */ "); count = 0; } } sb.AppendLine(@" /* ---- DATA BANK END CMD ------ */ "); intSqlbank += afad.ExecuteUpdate(sb.ToString()); return(intSqlbank); } catch (SqlException ex) { _cn.Close(); throw new SqlUpdateException(ex.ToString()); } }
public int SimpleInsert(IMultemic table, bool buildMapping, int batchSize = 1000) { try { IMultemic tab = table; IList <FieldMapping> nMaps = new List <FieldMapping>(); SqlAdapter afad = new SqlAdapter(_cn); StringBuilder sb = new StringBuilder(); int intSqlbank = 0; sb.AppendLine(@" /* ---- SQL BANK START CMD ------ */ "); int count = 0; foreach (IFigure ir in tab) { foreach (FieldMapping nMap in nMaps) { ; MemberRubric[] ic = tab.Rubrics.AsValues().Where(c => nMap.ColumnOrdinal.Contains(c.FigureFieldId)).ToArray(); MemberRubric[] ik = tab.Rubrics.AsValues().Where(c => nMap.KeyOrdinal.Contains(c.FigureFieldId)).ToArray(); string qry = BatchInsertQuery(ir, nMap.DbTableName, ic, ik).ToString(); sb.Append(qry); count++; } if (count >= batchSize) { sb.AppendLine(@" /* ---- DATA BATCH END CMD ------ */ "); intSqlbank += afad.ExecuteInsert(sb.ToString()); sb.Clear(); sb.AppendLine(@" /* ---- SQL BANK START CMD ------ */ "); count = 0; } } sb.AppendLine(@" /* ---- DATA BANK END CMD ------ */ "); intSqlbank += afad.ExecuteInsert(sb.ToString()); return(intSqlbank); } catch (SqlException ex) { _cn.Close(); throw new SqlInsertException(ex.ToString()); } }
public Album <Album <IFigure> > Update(IMultemic table, bool keysFromDeck = false, bool buildMapping = false, bool updateKeys = false, string[] updateExcept = null, BulkPrepareType tempType = BulkPrepareType.Trunc) { try { IMultemic tab = table; if (tab.Count > 0) { IList <FieldMapping> nMaps = new List <FieldMapping>(); if (buildMapping) { SqlMapper imapper = new SqlMapper(tab, keysFromDeck); } nMaps = tab.Rubrics.Mappings; string dbName = _cn.Database; SqlAdapter afad = new SqlAdapter(_cn); afad.DataBulk(tab, tab.FigureType.Name, tempType, BulkDbType.TempDB); _cn.ChangeDatabase(dbName); var nSet = new Album <Album <IFigure> >(); StringBuilder sb = new StringBuilder(); sb.AppendLine(@" /* ---- SQL BANK START CMD ------ */ "); foreach (FieldMapping nMap in nMaps) { sb.AppendLine(@" /* ---- TABLE BULK START CMD ------ */ "); MemberRubric[] ic = tab.Rubrics.AsValues().Where(c => nMap.ColumnOrdinal.Contains(c.FigureFieldId)).ToArray(); MemberRubric[] ik = tab.Rubrics.AsValues().Where(c => nMap.KeyOrdinal.Contains(c.FigureFieldId)).ToArray(); if (updateExcept != null) { ic = ic.Where(c => !updateExcept.Contains(c.RubricName)).ToArray(); ik = ik.Where(c => !updateExcept.Contains(c.RubricName)).ToArray(); } string qry = BulkUpdateQuery(dbName, tab.FigureType.Name, nMap.DbTableName, ic, ik, updateKeys).ToString(); sb.Append(qry); sb.AppendLine(@" /* ---- TABLE BULK END CMD ------ */ "); } sb.AppendLine(@" /* ---- SQL BANK END CMD ------ */ "); var bIMultemic = afad.ExecuteUpdate(sb.ToString(), tab, true); if (nSet.Count == 0) { nSet = bIMultemic; } else { foreach (Album <IFigure> its in bIMultemic.AsValues()) { if (nSet.Contains(its)) { nSet[its].Put(its.AsValues()); } else { nSet.Add(its); } } } sb.Clear(); return(nSet); } else { return(null); } } catch (SqlException ex) { _cn.Close(); throw new SqlUpdateException(ex.ToString()); } }
public Album <Album <IFigure> > BatchUpdate(IMultemic table, bool keysFromDeck = false, bool buildMapping = false, bool updateKeys = false, string[] updateExcept = null, int batchSize = 250) { try { IMultemic tab = table; IList <FieldMapping> nMaps = new List <FieldMapping>(); SqlAdapter afad = new SqlAdapter(_cn); StringBuilder sb = new StringBuilder(); var nSet = new Album <Album <IFigure> >(); sb.AppendLine(@" /* ---- SQL BANK START CMD ------ */ "); int count = 0; foreach (IFigure ir in table) { if (ir.GetType().DeclaringType != tab.FigureType) { if (buildMapping) { SqlMapper imapper = new SqlMapper(tab, keysFromDeck); } nMaps = tab.Rubrics.Mappings; } foreach (FieldMapping nMap in nMaps) { IDeck <int> co = nMap.ColumnOrdinal; IDeck <int> ko = nMap.KeyOrdinal; MemberRubric[] ic = tab.Rubrics.AsValues().Where(c => nMap.ColumnOrdinal.Contains(c.FigureFieldId)).ToArray(); MemberRubric[] ik = tab.Rubrics.AsValues().Where(c => nMap.KeyOrdinal.Contains(c.FigureFieldId)).ToArray(); if (updateExcept != null) { ic = ic.Where(c => !updateExcept.Contains(c.RubricName)).ToArray(); ik = ik.Where(c => !updateExcept.Contains(c.RubricName)).ToArray(); } string qry = BatchUpdateQuery(ir, nMap.DbTableName, ic, ik, updateKeys).ToString(); sb.Append(qry); count++; } if (count >= batchSize) { sb.AppendLine(@" /* ---- DATA BATCH END CMD ------ */ "); Album <Album <IFigure> > bIMultemic = afad.ExecuteUpdate(sb.ToString(), tab); if (nSet.Count == 0) { nSet = bIMultemic; } else { foreach (Album <IFigure> its in bIMultemic.AsValues()) { if (nSet.Contains(its)) { nSet[its].Put(its.AsValues()); } else { nSet.Add(its); } } } sb.Clear(); sb.AppendLine(@" /* ---- SQL BANK START CMD ------ */ "); count = 0; } } sb.AppendLine(@" /* ---- DATA BANK END CMD ------ */ "); Album <Album <IFigure> > rIMultemic = afad.ExecuteUpdate(sb.ToString(), tab, true); if (nSet.Count == 0) { nSet = rIMultemic; } else { foreach (Album <IFigure> its in rIMultemic.AsValues()) { if (nSet.Contains(its)) { nSet[its].Put(its.AsValues()); } else { nSet.Add(its); } } } return(nSet); } catch (SqlException ex) { _cn.Close(); throw new SqlUpdateException(ex.ToString()); } }
public Album <Album <IFigure> > BatchInsert(IMultemic table, bool buildMapping, int batchSize = 1000) { try { IMultemic tab = table; IList <FieldMapping> nMaps = new List <FieldMapping>(); SqlAdapter afad = new SqlAdapter(_cn); StringBuilder sb = new StringBuilder(); var nSet = new Album <Album <IFigure> >(); sb.AppendLine(@" /* ---- SQL BANK START CMD ------ */ "); int count = 0; foreach (IFigure ir in tab) { foreach (FieldMapping nMap in nMaps) { MemberRubric[] ic = tab.Rubrics.AsValues().Where(c => nMap.ColumnOrdinal.Contains(c.FigureFieldId)).ToArray(); MemberRubric[] ik = tab.Rubrics.AsValues().Where(c => nMap.KeyOrdinal.Contains(c.FigureFieldId)).ToArray(); string qry = BatchInsertQuery(ir, nMap.DbTableName, ic, ik).ToString(); sb.Append(qry); count++; } if (count >= batchSize) { sb.AppendLine(@" /* ---- DATA BATCH END CMD ------ */ "); var bIMultemic = afad.ExecuteInsert(sb.ToString(), tab); if (nSet.Count == 0) { nSet = bIMultemic; } else { foreach (Album <IFigure> its in bIMultemic.AsValues()) { if (nSet.Contains(its)) { nSet[its].Put(its.AsValues()); } else { nSet.Add(its); } } } sb.Clear(); sb.AppendLine(@" /* ---- SQL BANK START CMD ------ */ "); count = 0; } } sb.AppendLine(@" /* ---- DATA BANK END CMD ------ */ "); var rIMultemic = afad.ExecuteInsert(sb.ToString(), tab); if (nSet.Count == 0) { nSet = rIMultemic; } else { foreach (Album <IFigure> its in rIMultemic.AsValues()) { if (nSet.Contains(its)) { nSet[its].Put(its.AsValues()); } else { nSet.Add(its); } } } return(nSet); } catch (SqlException ex) { _cn.Close(); throw new SqlInsertException(ex.ToString()); } }