public async Task <dynamic> WriteTable(string table, Row[] data) { actionSW = new Stopwatch(); actionSW.Start(); Tabs.Styles.rdbTab rTab = TabMan.RDBTab; int rowCnt = data.Length; try { if (await OpenConnection()) { if (configMan["DropOnExport", "DB"]) { await Execute($"drop table {table}"); } else { await Execute($"truncate table {table}"); } DataTable dataTbl = generateDataTable(table); for (int r = 0; r < data.Length; r++) //loop rows { DataRow newRow = dataTbl.NewRow(); Row refRow = TabMan.RDBCore[r]; for (int c = 0; c < dataTbl.Columns.Count; c++) { string colName = dataTbl.Columns[c].ColumnName; newRow[c] = refRow[colName]; } dataTbl.Rows.Add(newRow); } executeBulk(dataTbl); } else { } //Todo: handle exception bro } catch (Exception ex) { MessageBox.Show($"An error occured during the export!\nMessage: {ex.Message}\nStack-Trace: {ex.StackTrace}"); } finally { CloseConnection(); rTab.ResetProgress(); actionSW.Stop(); Logs.Manager.Instance.Enter(Sender.DATABASE, Level.DEBUG, $"{table} exported loaded in {actionSW.ElapsedMilliseconds}ms"); } return(false); }
public List <Row> FetchTable(int rowCount, string tableName) { Tabs.Styles.rdbTab rTab = tManager.RDBTab; rTab.ProgressMax = rowCount; List <Row> data = new List <Row>(rowCount); List <LuaField> fieldList = rCore.FieldList; string selectStatement = generateSelect(tableName); using (SqlCommand sqlCmd = new SqlCommand(selectStatement, sqlCon)) { sqlCmd.CommandTimeout = 0; sqlCmd.Connection.Open(); using (SqlDataReader sqlRdr = sqlCmd.ExecuteReader()) { int curRow = 0; while (sqlRdr.Read()) { Row newRow = new Row(fieldList); for (int i = 0; i < fieldList.Count; i++) { LuaField field = fieldList[i]; if (field.Show) { object fieldVal = sqlRdr[field.Name]; switch (field.Type) { case "short": newRow[i] = Convert.ToInt16(sqlRdr[field.Name]); break; case "ushort": newRow[i] = Convert.ToUInt16(sqlRdr[field.Name]); break; case "int": newRow[i] = (fieldVal.GetType() == typeof(DBNull)) ? 0 : Convert.ToInt32(fieldVal); break; case "uint": newRow[i] = Convert.ToInt32(sqlRdr[field.Name]); break; case "long": newRow[i] = Convert.ToInt64(sqlRdr[field.Name]); break; case "byte": byte val = new byte(); newRow[i] = (Byte.TryParse(fieldVal.ToString(), out val)) ? val : 0; break; case "bitfromvector": newRow[i] = Convert.ToInt32(sqlRdr[field.Name]); break; case "datetime": newRow[i] = Convert.ToDateTime(sqlRdr[field.Name]); break; case "decimal": newRow[i] = Convert.ToDecimal(sqlRdr[field.Name]); break; case "single": newRow[i] = Convert.ToSingle(sqlRdr[field.Name]); break; case "double": newRow[i] = Convert.ToDouble(sqlRdr[field.Name]); break; case "sid": newRow[i] = Convert.ToInt32(sqlRdr[field.Name]); break; case "string": newRow[i] = Convert.ToString(sqlRdr[field.Name]); break; case "stringbylen": newRow[i] = Convert.ToString(sqlRdr[field.Name]); break; case "stringbyref": newRow[i] = Convert.ToString(sqlRdr[field.Name]); break; } } else { switch (field.Type) { case "bitvector": newRow[i] = new BitVector32(0); break; case "byte": newRow[i] = Convert.ToByte(field.Default); break; case "int": newRow[i] = (newRow.KeyIsDuplicate(field.Name)) ? newRow.GetShownValue(field.Name) : field.Default; break; case "short": newRow[i] = Convert.ToInt16(field.Default); break; case "string": newRow[i] = field.Default.ToString(); break; } } } data.Add(newRow); curRow++; if (((curRow * 100) / rowCount) != ((curRow - 1) * 100 / rowCount)) { rTab.ProgressVal = curRow; } } } } rTab.ProgressVal = 0; rTab.ProgressMax = 100; return(data); }
public Row[] FetchTable(int rowCount, string tableName) { Tabs.Styles.rdbTab rTab = tManager.RDBTab; rTab.ProgressMax = rowCount; Row[] data = new Row[rowCount]; Cell[] fieldList = rCore.CellTemplate; string selectStatement = generateSelect(tableName); using (SqlCommand sqlCmd = new SqlCommand(selectStatement, sqlCon)) { sqlCmd.CommandTimeout = OPT.GetInt("db.connection.timeout"); sqlCmd.Connection.Open(); using (SqlDataReader sqlRdr = sqlCmd.ExecuteReader()) { int curRow = 0; while (sqlRdr.Read()) { Row newRow = new Row((Cell[])fieldList.Clone()); for (int i = 0; i < fieldList.Length; i++) { Cell field = fieldList[i]; if (field.Visible) { int fieldOrdinal = sqlRdr.GetOrdinal(field.Name); switch (field.Type) { case CellType.TYPE_SHORT: goto case CellType.TYPE_INT_16; case CellType.TYPE_INT_16: newRow[i] = sqlRdr[fieldOrdinal] as short? ?? default(short); break; case CellType.TYPE_USHORT: goto case CellType.TYPE_UINT_16; case CellType.TYPE_UINT_16: newRow[i] = sqlRdr[fieldOrdinal] as ushort? ?? default(ushort); break; case CellType.TYPE_INT: goto case CellType.TYPE_INT_32; case CellType.TYPE_INT_32: newRow[i] = sqlRdr[fieldOrdinal] as int? ?? default(int); break; case CellType.TYPE_UINT: goto case CellType.TYPE_UINT_32; case CellType.TYPE_UINT_32: newRow[i] = sqlRdr[fieldOrdinal] as uint? ?? default(uint); break; case CellType.TYPE_LONG: newRow[i] = sqlRdr[fieldOrdinal] as long? ?? default(long); break; case CellType.TYPE_BYTE: { object fieldVal = sqlRdr[fieldOrdinal]; byte val = new byte(); newRow[i] = (Byte.TryParse(fieldVal.ToString(), out val)) ? val : 0; } break; case CellType.TYPE_BIT_FROM_VECTOR: newRow[i] = Convert.ToInt32(sqlRdr[fieldOrdinal]); break; case CellType.TYPE_DECIMAL: newRow[i] = Convert.ToDecimal(sqlRdr[fieldOrdinal]); break; case CellType.TYPE_SINGLE: { decimal v1 = sqlRdr[fieldOrdinal] as decimal? ?? default(decimal); newRow[i] = decimal.ToSingle(v1); break; } case CellType.TYPE_DOUBLE: newRow[i] = sqlRdr[fieldOrdinal] as double? ?? default(double); break; case CellType.TYPE_STRING: newRow[i] = Convert.ToString(sqlRdr[fieldOrdinal]); break; case CellType.TYPE_STRING_BY_LEN: { string szVal = Convert.ToString(sqlRdr[fieldOrdinal]); newRow[field.Dependency] = szVal.Length + 1; newRow[i] = szVal; } break; case CellType.TYPE_STRING_BY_REF: newRow[i] = Convert.ToString(sqlRdr[fieldOrdinal]); break; } } else { // TODO: Look me over closer switch (field.Type) { case CellType.TYPE_BIT_VECTOR: newRow[i] = new BitVector32(0); break; case CellType.TYPE_BYTE: newRow[i] = Convert.ToByte(field.Default); break; case CellType.TYPE_INT: newRow[i] = newRow.KeyIsDuplicate(field.Name) ? newRow.GetShownValue(field.Name) : field.Default; break; case CellType.TYPE_SHORT: newRow[i] = Convert.ToInt16(field.Default); break; case CellType.TYPE_STRING: newRow[i] = field.Default.ToString(); break; } } } data[curRow] = newRow; curRow++; if ((curRow * 100 / rowCount) != ((curRow - 1) * 100 / rowCount)) { rTab.ProgressVal = curRow; } } } } rTab.ProgressVal = 0; rTab.ProgressMax = 100; return(data); }