private string BuildInsert(DataTable table, MapTableInfo objMap, string checkID, string newVariable, string parentCounter)
{
StringBuilder sql = new StringBuilder();
sql.Append(InsertIntoScriptClose(InsertIntoScriptOpen(table.Columns, objMap), table.Columns, table.Rows, objMap, checkID, newVariable, parentCounter));
return(sql.ToString());
}
private string InsertIntoScriptOpen(DataColumnCollection dataColumn, MapTableInfo objMap)
{
StringBuilder sql = new StringBuilder("INSERT INTO " + objMap.TableName + " (");
StringBuilder values = new StringBuilder("VALUES (");
if (objMap.TableName == "UserModulePermission")
{
string test = string.Empty;
}
bool bFirst = true;
int i = 0;
if (objMap.AutoIncrement)
{
i = 1;
}
int colLen = dataColumn.Count;
for (; i < colLen; i++)
{
if (bFirst)
{
bFirst = false;
}
else
{
sql.Append(", ");
values.Append(", ");
}
sql.Append(dataColumn[i].ColumnName);
}
sql.Append(") ");
return(sql.ToString());
}
private string CheckForExistency(string insertQuery, string newVariable, MapTableInfo objTable, DataRow row, List <string> datatype, string oldvar)
{
string condition = " WHERE " + GenerateCondition(objTable.ExistenceCheck, objTable.ExistenceCheckCol, row, datatype, oldvar);
StringBuilder check = new StringBuilder();
check.Append(Environment.NewLine);
check.Append("IF(NOT EXISTS (SELECT 1 FROM ");
check.Append(objTable.TableName);
check.Append(condition);
check.Append("))");
check.Append("BEGIN ");
check.Append(Environment.NewLine);
check.Append(insertQuery);
check.Append("Set " + newVariable + "= SCOPE_IDENTITY()");
check.Append("END ");
check.Append(Environment.NewLine);
check.Append("ELSE ");
check.Append(Environment.NewLine);
check.Append("BEGIN ");
//codition
check.Append("SELECT " + newVariable + " = " + objTable.Variable.Substring(1) + " FROM " + objTable.TableName + " ");
check.Append(condition);
check.Append(Environment.NewLine);
check.Append("END");
check.Append(Environment.NewLine);
return(check.ToString());
}
private string InsertIntoScriptClose(string openScript, DataColumnCollection dataColumn, DataRowCollection dataRow, MapTableInfo objMap, string checkID, string newVariable, string parentCounter)
{
//Update the touched table in original list
foreach (MapTableInfo item in objMapInfoList)
{
if (item.TableName == objMap.TableName)
{
item.Untouched = false;
}
}
StringBuilder values = new StringBuilder();
int variableCounter = 0;
foreach (DataRow row in dataRow)
{
bool moveFurther = false;
if (checkID == "0")
{
moveFurther = true;
}
else
{
if (row.ItemArray[objMap.CompareColumn].ToString() == checkID)
{
moveFurther = true;
}
}
if (moveFurther)
{
StringBuilder insert = new StringBuilder();
List <string> datatype = GetDataType(dataColumn);
insert.Append(openScript);
insert.Append("VALUES (");
int len = row.ItemArray.Length;
int i = 0;
string identityValue = string.Empty;
if (objMap.AutoIncrement)
{
i = 1;
identityValue = row.ItemArray[0].ToString();
}
List <string> valuesList = new List <string>();
for (; i < len; i++)
{
if (i == objMap.CompareColumn && checkID != "0")
{
valuesList.Add(newVariable);
}
else
{
valuesList.Add(changeDataType(row.ItemArray[i], datatype[i]));
}
}
insert.Append(string.Join(",", valuesList));
insert.Append(") ");
insert.Append(Environment.NewLine);
//values.Append(insert);
if (objMap.Variable != string.Empty)
{
string newVar = objMap.Variable + "_" + variableCounter + "_" + parentCounter;
//update if one table has two table dependencies
if (objMap.DependentTable > 0)
{
UpdateDependenttable(objMap.DependentTable, objMap.DependentTableColumn, newVar, identityValue);
}
values.Append(CheckForExistency(insert.ToString(), newVar, objMap, row, datatype, newVariable));
if (newVar != string.Empty)
{
declareVariable.Append("Declare ");
declareVariable.Append(newVar);
declareVariable.Append(" int;");
declareVariable.Append(Environment.NewLine);
}
//getanotherTable
if (objMap.ForeignTable != string.Empty)
{
List <MapTableInfo> objMapLists = SearchTable(objMap.ForeignTable);
foreach (MapTableInfo objMapInfo in objMapLists)
{
values.Append(BuildInsert(dataSet.Tables[objMapInfo.TableNo], objMapInfo, identityValue, newVar, variableCounter + "_" + parentCounter));
}
}
variableCounter++;
}
}
}
return(values.ToString());
}