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