/// <summary>
/// The Delete.
/// </summary>
/// <param name="SqlConnectString">The SqlConnectString<see cref="string"/>.</param>
/// <param name="cards">The cards<see cref="IFigures"/>.</param>
/// <returns>The <see cref="IDeck{IDeck{IFigure}}"/>.</returns>
public IDeck <IDeck <IFigure> > Delete(string SqlConnectString, IFigures cards)
{
try
{
if (sqaf == null)
{
sqaf = new Sqlbase(SqlConnectString);
}
try
{
bool buildmap = true;
if (cards.Count > 0)
{
BulkPrepareType prepareType = BulkPrepareType.Drop;
return(sqaf.Delete(cards, true, buildmap, prepareType));
}
return(null);
}
catch (SqlException ex)
{
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
}
public Album <Album <IFigure> > SetBank(string SqlConnectString, IMultemic cards, bool Renew)
{
try
{
if (sqaf == null)
{
sqaf = new InstantSql(SqlConnectString);
}
try
{
bool buildmap = true;
if (cards.Count > 0)
{
BulkPrepareType prepareType = BulkPrepareType.Drop;
if (Renew)
{
prepareType = BulkPrepareType.Trunc;
}
var ds = sqaf.Update(cards, Renew, buildmap, true, null, prepareType);
if (ds != null)
{
IMultemic im = (IMultemic)Summon.New(cards.GetType());
im.Rubrics = cards.Rubrics;
im.FigureType = cards.FigureType;
im.FigureSize = cards.FigureSize;
im.Add(ds["Failed"].AsValues());
return(sqaf.Insert(im, Renew, false, prepareType));
}
else
{
return(null);
}
}
return(null);
}
catch (SqlException ex)
{
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// The Insert.
/// </summary>
/// <param name="table">The table<see cref="IFigures"/>.</param>
/// <param name="keysFromDeckis">The keysFromDeckis<see cref="bool"/>.</param>
/// <param name="buildMapping">The buildMapping<see cref="bool"/>.</param>
/// <param name="updateKeys">The updateKeys<see cref="bool"/>.</param>
/// <param name="updateExcept">The updateExcept<see cref="string[]"/>.</param>
/// <param name="tempType">The tempType<see cref="BulkPrepareType"/>.</param>
/// <returns>The <see cref="IDeck{IDeck{IFigure}}"/>.</returns>
public IDeck <IDeck <IFigure> > Insert(IFigures table, bool keysFromDeckis = false, bool buildMapping = false, bool updateKeys = false, string[] updateExcept = null, BulkPrepareType tempType = BulkPrepareType.Trunc)
{
return(BulkInsert(table, keysFromDeckis, buildMapping, updateKeys, updateExcept, tempType));
}
/// <summary>
/// The BulkInsert.
/// </summary>
/// <param name="table">The table<see cref="IFigures"/>.</param>
/// <param name="keysFromDeckis">The keysFromDeckis<see cref="bool"/>.</param>
/// <param name="buildMapping">The buildMapping<see cref="bool"/>.</param>
/// <param name="updateKeys">The updateKeys<see cref="bool"/>.</param>
/// <param name="updateExcept">The updateExcept<see cref="string[]"/>.</param>
/// <param name="tempType">The tempType<see cref="BulkPrepareType"/>.</param>
/// <returns>The <see cref="IDeck{IDeck{IFigure}}"/>.</returns>
public IDeck <IDeck <IFigure> > BulkInsert(IFigures table, bool keysFromDeckis = false, bool buildMapping = false, bool updateKeys = false, string[] updateExcept = null, BulkPrepareType tempType = BulkPrepareType.Trunc)
{
try
{
IFigures tab = table;
if (tab.Any())
{
IList <FieldMapping> nMaps = new List <FieldMapping>();
if (buildMapping)
{
SqlMapper imapper = new SqlMapper(tab, keysFromDeckis);
}
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);
IDeck <IDeck <IFigure> > nSet = new Album <IDeck <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.FieldId)).ToArray();
MemberRubric[] ik = tab.Rubrics.AsValues().Where(c => nMap.KeyOrdinal.Contains(c.FieldId)).ToArray();
if (updateExcept != null)
{
ic = ic.Where(c => !updateExcept.Contains(c.RubricName)).ToArray();
ik = ik.Where(c => !updateExcept.Contains(c.RubricName)).ToArray();
}
string qry = BulkInsertQuery(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 ------ */ ");
IDeck <IDeck <IFigure> > bIFigures = afad.ExecuteInsert(sb.ToString(), tab, true);
if (nSet.Count == 0)
{
nSet = bIFigures;
}
else
{
foreach (IDeck <IFigure> its in bIFigures.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 SqlInsertException(ex.ToString());
}
}
/// <summary>
/// The DataBulk.
/// </summary>
/// <param name="cards">The cards<see cref="FigureCard[]"/>.</param>
/// <param name="buforTable">The buforTable<see cref="string"/>.</param>
/// <param name="prepareType">The prepareType<see cref="BulkPrepareType"/>.</param>
/// <param name="dbType">The dbType<see cref="BulkDbType"/>.</param>
/// <returns>The <see cref="bool"/>.</returns>
public bool DataBulk(FigureCard[] cards, string buforTable, BulkPrepareType prepareType = BulkPrepareType.None, BulkDbType dbType = BulkDbType.TempDB)
{
try
{
IFigures deck = null;
if (cards.Any())
{
deck = cards.ElementAt(0).Figures;
if (_cn.State == ConnectionState.Closed)
{
_cn.Open();
}
try
{
if (dbType == BulkDbType.TempDB)
{
_cn.ChangeDatabase("tempdb");
}
if (!DbHand.Temp.DataDbTables.Have(buforTable) || prepareType == BulkPrepareType.Drop)
{
string createTable = "";
if (prepareType == BulkPrepareType.Drop)
{
createTable += "Drop table if exists [" + buforTable + "] \n";
}
createTable += "Create Table [" + buforTable + "] ( ";
foreach (MemberRubric column in deck.Rubrics.AsValues())
{
string sqlTypeString = "varchar(200)";
List <string> defineStr = new List <string>()
{
"varchar", "nvarchar", "ntext", "varbinary"
};
List <string> defineDec = new List <string>()
{
"decimal", "numeric"
};
int colLenght = column.RubricSize;
sqlTypeString = SqlNetType.NetTypeToSql(column.RubricType);
string addSize = (colLenght > 0) ? (defineStr.Contains(sqlTypeString)) ? (string.Format(@"({0})", colLenght)) :
(defineDec.Contains(sqlTypeString)) ? (string.Format(@"({0}, {1})", colLenght - 6, 6)) : "" : "";
sqlTypeString += addSize;
createTable += " [" + column.RubricName + "] " + sqlTypeString + ",";
}
createTable = createTable.TrimEnd(new char[] { ',' }) + " ) ";
SqlCommand createcmd = new SqlCommand(createTable, _cn);
createcmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
throw new SqlInsertException(ex.ToString());
}
if (prepareType == BulkPrepareType.Trunc)
{
string deleteData = "Truncate Table [" + buforTable + "]";
SqlCommand delcmd = new SqlCommand(deleteData, _cn);
delcmd.ExecuteNonQuery();
}
try
{
DataReader ndr = new DataReader(cards);
SqlBulkCopy bulkcopy = new SqlBulkCopy(_cn);
bulkcopy.DestinationTableName = "[" + buforTable + "]";
bulkcopy.WriteToServer(ndr);
}
catch (SqlException ex)
{
throw new SqlInsertException(ex.ToString());
}
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw new SqlInsertException(ex.ToString());
}
}
/// <summary>
/// The Delete.
/// </summary>
/// <param name="table">The table<see cref="IFigures"/>.</param>
/// <param name="keysFromDeckis">The keysFromDeckis<see cref="bool"/>.</param>
/// <param name="buildMapping">The buildMapping<see cref="bool"/>.</param>
/// <param name="tempType">The tempType<see cref="BulkPrepareType"/>.</param>
/// <returns>The <see cref="IDeck{IDeck{IFigure}}"/>.</returns>
public IDeck <IDeck <IFigure> > Delete(IFigures table, bool keysFromDeckis = false, bool buildMapping = false, BulkPrepareType tempType = BulkPrepareType.Trunc)
{
return(BulkDelete(table, keysFromDeckis, buildMapping, tempType));
}
/// <summary>
/// The BulkDelete.
/// </summary>
/// <param name="table">The table<see cref="IFigures"/>.</param>
/// <param name="keysFromDeckis">The keysFromDeckis<see cref="bool"/>.</param>
/// <param name="buildMapping">The buildMapping<see cref="bool"/>.</param>
/// <param name="tempType">The tempType<see cref="BulkPrepareType"/>.</param>
/// <returns>The <see cref="IDeck{IDeck{IFigure}}"/>.</returns>
public IDeck <IDeck <IFigure> > BulkDelete(IFigures table, bool keysFromDeckis = false, bool buildMapping = false, BulkPrepareType tempType = BulkPrepareType.Trunc)
{
try
{
IFigures tab = table;
if (tab.Any())
{
IList <FieldMapping> nMaps = new List <FieldMapping>();
if (buildMapping)
{
SqlMapper imapper = new SqlMapper(tab, keysFromDeckis);
}
nMaps = tab.Rubrics.Mappings;
string dbName = _cn.Database;
SqlAdapter adapter = new SqlAdapter(_cn);
adapter.DataBulk(tab, tab.FigureType.Name, tempType, BulkDbType.TempDB);
_cn.ChangeDatabase(dbName);
IDeck <IDeck <IFigure> > nSet = new Album <IDeck <IFigure> >();
StringBuilder sb = new StringBuilder();
sb.AppendLine(@" /* ---- SQL BANK START CMD ------ */ ");
foreach (FieldMapping nMap in nMaps)
{
sb.AppendLine(@" /* ---- TABLE BULK START CMD ------ */ ");
string qry = BulkDeleteQuery(dbName, tab.FigureType.Name, nMap.DbTableName).ToString();
sb.Append(qry);
sb.AppendLine(@" /* ---- TABLE BULK END CMD ------ */ ");
}
sb.AppendLine(@" /* ---- SQL BANK END CMD ------ */ ");
IDeck <IDeck <IFigure> > bIFigures = adapter.ExecuteDelete(sb.ToString(), tab, true);
if (nSet.Count == 0)
{
nSet = bIFigures;
}
else
{
foreach (IDeck <IFigure> its in bIFigures.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 SqlDeleteException(ex.ToString());
}
}
