public void ExportToTable(string tableName, Row[] data)
{
if (OPT.GetBool("db.save.backup"))
{
scriptTable(tableName, true);
}
using (SqlCommand sqlCmd = new SqlCommand("", sqlCon))
{
sqlCmd.Connection.Open();
if (OPT.GetBool("db.save.drop"))
{
scriptTable(tableName, false);
sqlCmd.CommandText = string.Format("DROP TABLE {0}", tableName);
sqlCmd.ExecuteNonQuery();
string script = new StreamReader(string.Format(@"{0}\{1}_{2}_so.sql",
scriptDir,
tableName,
DateTime.Now.ToString("hhMMddyyy"))).ReadToEnd();
db.ExecuteNonQuery(script);
}
else
{
sqlCmd.CommandText = string.Format("TRUNCATE TABLE {0}", tableName);
} sqlCmd.ExecuteNonQuery();
sqlCmd.Connection.Close();
}
SqlCommand insertCmd = tManager.RDBCore.InsertStatement;
insertCmd.Connection = sqlCon;
insertCmd.CommandText = insertCmd.CommandText.Replace("<tableName>", tableName);
int rows = data.Length;
tManager.RDBTab.ProgressMax = rows;
for (int r = 0; r < rows; r++)
{
Row row = data[r];
using (SqlCommand sqlCmd = insertCmd)
{
foreach (SqlParameter sqlParam in sqlCmd.Parameters)
{
sqlParam.Value = row[sqlParam.ParameterName];
}
sqlCmd.Connection.Open();
sqlCmd.ExecuteNonQuery();
sqlCmd.Connection.Close();
}
if ((r * 100 / rows) != ((r - 1) * 100 / rows))
{
tManager.RDBTab.ProgressVal = r;
}
}
tManager.RDBTab.ProgressVal = 0;
tManager.RDBTab.ProgressMax = 100;
}
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);
}
