public returnval merge(returnval val)
{
if (val.rows == null && val.childs == null)
{
SD.DataTable DataTable;
using (SDSc.SqlConnection SqlConnection = new SDSc.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["moodleConnectionString1"].ConnectionString)) {
DataTable = new SD.DataTable();
(new SDSc.SqlDataAdapter(new SDSc.SqlCommand()
{
CommandText = string.Format(
"SELECT {0}{1}{2};"
, selectcols.Replace("@currentuser", "1")
, dbname == null ? "" : string.Format(" FROM {0}", dbname)
, whereparam == null ? "" : string.Format(" WHERE {0}", whereparam
.Replace("@currentuser", "1")
.Replace("@currentlogin", "1"))
),
Connection = SqlConnection
})).Fill(DataTable);
};
val.rows = new List <object[]>();
foreach (SD.DataRow DataRow in DataTable.Rows)
{
val.rows.Add(DataRow.ItemArray);
}
}
else
{
using (SDSc.SqlConnection SqlConnection = new SDSc.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["moodleConnectionString1"].ConnectionString)) {
SqlConnection.Open();
SDSc.SqlTransaction SqlTransaction = SqlConnection.BeginTransaction();
try {
merge(val, SqlTransaction);
if (val.childs != null)
{
foreach (returnval subtable in val.childs)
{
thistables.table(subtable.name).merge(subtable, SqlTransaction);
if (subtable.childs != null)
{
foreach (returnval subtable2 in subtable.childs)
{
thistables.table(subtable2.name).merge(subtable2, SqlTransaction);
}
}
}
}
SqlTransaction.Commit();
} catch (Exception e) {
SqlTransaction.Rollback();
throw e;
}
}
}
return(val);
}
public static string getTable(string json)
{
try {
returnval rv = json;
var ttt = thistables.table(rv.name);
string tmp = ttt.merge(rv);
tmp.Replace(",null,", ",a,");
return(tmp);
} catch (Exception e) { return(e.Message); }
}
public returnval merge(returnval val, SDSc.SqlTransaction SqlTransaction)
{
valss lcols1;
valss lcols2;
SD.DataTable DataTable;
SDSc.SqlCommand SqlCommand;
if (val.rows != null)
{
for (int i = 0; i < val.rows.Count; i++)
{
DataTable = new SD.DataTable();
lcols1 = new valss();
if (Convert.ToString(val.rows[i][0])[0] == 'D')
{
if (!delete)
{
throw new Exception("No delete permission");
}
val.rows[i][0] = Convert.ToInt32(Convert.ToString(val.rows[i][0]).Substring(1));
for (int j = 0; j < cols.Count; j++)
{
if (cols[j].pk)
{
lcols1.Add(cols[j].name, val.rows[i][j]);
}
}
;
SqlCommand = new SDSc.SqlCommand()
{
CommandText = string.Format(
"DELETE FROM {0} WHERE {1};SELECT {2} FROM {0} WHERE {1};"
, dbname
, lcols1.sets
, selectcols
),
Connection = SqlTransaction.Connection,
Transaction = SqlTransaction
};
lcols1.Parameters(SqlCommand);
(new SDSc.SqlDataAdapter(SqlCommand)).Fill(DataTable);
for (int ii = 0, length = DataTable.Columns.Count; ii < length; ii++)
{
val.rows[i][ii] = null;
}
}
else if (Convert.ToString(val.rows[i][0])[0] == 'I' || Convert.ToInt32(val.rows[i][0]) < 0)
{
if (!insert)
{
throw new Exception("No insert permission");
}
if (Convert.ToString(val.rows[i][0])[0] == 'I')
{
val.rows[i][0] = Convert.ToInt32(Convert.ToString(val.rows[i][0]).Substring(1));
lcols2 = new valss();
for (int j = 0; j < cols.Count; j++)
{
if (cols[j].pk)
{
lcols2.Add(cols[j].name, val.rows[i].Length > j ? val.rows[i][j] : null);
}
if (cols[j].insert)
{
lcols1.Add(cols[j].name, val.rows[i].Length > j ? val.rows[i][j] : null);
}
}
SqlCommand = new SDSc.SqlCommand()
{
CommandText = string.Format(
"INSERT INTO {0}({1})VALUES({2});SELECT {3} FROM {0} WHERE {4};"
, dbname
, lcols1.cols
, lcols1.values
, selectcols
, lcols2.sets
),
Connection = SqlTransaction.Connection,
Transaction = SqlTransaction
};
lcols1.Parameters(SqlCommand);
(new SDSc.SqlDataAdapter(SqlCommand)).Fill(DataTable);
val.rows[i] = DataTable.Rows[0].ItemArray;
}
else
{
for (int j = 0; j < cols.Count; j++)
{
if (cols[j].insert)
{
lcols1.Add(cols[j].name, val.rows[i].Length > j ? val.rows[i][j] : null);
}
}
SqlCommand = new SDSc.SqlCommand()
{
CommandText = string.Format(
"INSERT INTO {0}({1})VALUES({2});SELECT {3} FROM {0} WHERE[{4}]=@@IDENTITY;"
, dbname
, lcols1.cols
, lcols1.values
, selectcols
, pk
),
Connection = SqlTransaction.Connection,
Transaction = SqlTransaction
};
lcols1.Parameters(SqlCommand);
(new SDSc.SqlDataAdapter(SqlCommand)).Fill(DataTable);
if (val.childs != null)
{
foreach (var child in val.childs)
{
foreach (var childrow in child.rows)
{
if (Convert.ToInt32(childrow[child.childcol]) == Convert.ToInt32(val.rows[i][0]))
{
childrow[child.childcol] = DataTable.Rows[0].ItemArray[0];
}
}
}
}
val.rows[i] = DataTable.Rows[0].ItemArray;
}
}
else
{
if (!update)
{
throw new Exception("No update permission");
}
lcols2 = new valss();
for (int j = 0; j < cols.Count; j++)
{
if (cols[j].pk)
{
lcols2.Add(cols[j].name, val.rows[i][j]);
}
else if (cols[j].update)
{
lcols1.Add(cols[j].name, val.rows[i].Length > j ? val.rows[i][j] : null);
}
}
SqlCommand = new SDSc.SqlCommand()
{
CommandText = string.Format(
"UPDATE {0} SET {1} WHERE {2};SELECT {3} FROM {0} WHERE {2};"
, dbname
, lcols1.sets
, lcols2.sets
, selectcols
),
Connection = SqlTransaction.Connection,
Transaction = SqlTransaction
};
lcols1.Parameters(SqlCommand);
lcols2.Parameters(SqlCommand);
(new SDSc.SqlDataAdapter(SqlCommand)).Fill(DataTable);
val.rows[i] = DataTable.Rows[0].ItemArray;
}
}
}
return(val);
}
