public void SetString(int ordinal, string value, bool isPrimary = false)
{
if (_found && !isPrimary)
{
_resultSet.SetString(ordinal, value);
}
else if (!_found)
{
_newRecord.SetString(ordinal, value);
}
}
public int StoreOutboxMessage(OutgoingTwilioMessage msgout)
{
int id = 0; // ID of the inserted SQL CE record
// This results to
// INSERT INTO User (FirstName, LastName) VALUES ('test','test'),('test','test'),... ;
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = TableOutbox;
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
SqlCeUpdatableRecord rec = rs.CreateRecord();
try
{
// DEBUG
/*
* App.logger.Log("DBStore.StoreDataEntries(): Storing new entry:\n" +
* "\ttimestamp: " + qlmActivation.Timestamp +
* "\tkey: " + qlmActivation.LicenseKey +
* "\texpiration: " + qlmActivation.ExpirationDateTime);
*/
rec.SetSqlDateTime(1, DateTime.Now);
rec.SetString(2, msgout.From);
rec.SetString(3, msgout.To);
rec.SetString(4, msgout.Action);
rec.SetString(5, msgout.Method);
rec.SetString(6, msgout.Body);
rec.SetString(7, msgout.Client);
rs.Insert(rec);
// Get this inserter record ID
cmd.CommandText = "SELECT @@IDENTITY";
cmd.CommandType = CommandType.Text;
id = Convert.ToInt32(cmd.ExecuteScalar());
msgout.id = id; // Assign id to this message for further referencing from message to its DBStore record
// DEBUG
App.logger.Log("DBStore.StoreOutboxMessage() storing outbox message from " + msgout.From + " to " + msgout.To + ", ID=" + id);
}
catch (Exception ex)
{
var message = "! Error in DBStore.StoreOutboxMessage(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
}
cmd.Dispose();
return(id);
}
private static void SetData(SqlCeUpdatableRecord rec, int index, Type type, TypeConvertableWrapper col)
{
switch (Type.GetTypeCode(type.GetType()))
{
case TypeCode.String:
rec.SetString(index, col.String);
break;
case TypeCode.Int16:
rec.SetInt16(index, col.Int16);
break;
case TypeCode.Int32:
rec.SetInt32(index, col.Int);
break;
case TypeCode.Int64:
rec.SetInt64(index, col.Int);
break;
case TypeCode.Byte:
rec.SetSqlByte(index, col.Byte);
break;
case TypeCode.DateTime:
rec.SetSqlDateTime(index, col.DateTime);
break;
case TypeCode.Decimal:
rec.SetDecimal(index, col.Decimal);
break;
default:
throw new ArgumentException(Type.GetTypeCode(type.GetType()).ToString() + "型には対応していません。");
}
}
public bool StoreInboxMessage(IncomingTwilioMessage msgin)
{
bool retVal = true;
int id = 0; // ID of the inserted SQL CE record
// This results to
// INSERT INTO User (FirstName, LastName) VALUES ('test','test'),('test','test'),... ;
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = TableInbox;
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
SqlCeUpdatableRecord rec = rs.CreateRecord();
try
{
// DEBUG
/*
* App.logger.Log("DBStore.StoreDataEntries(): Storing new entry:\n" +
* "\ttimestamp: " + qlmActivation.Timestamp +
* "\tkey: " + qlmActivation.LicenseKey +
* "\texpiration: " + qlmActivation.ExpirationDateTime);
*/
rec.SetSqlDateTime(1, msgin.Timestamp);
rec.SetString(2, msgin.AccountSid);
rec.SetString(3, msgin.ApiVersion);
rec.SetString(4, msgin.Body);
rec.SetString(5, msgin.From);
rec.SetString(6, msgin.FromCity);
rec.SetString(7, msgin.FromCountry);
rec.SetString(8, msgin.FromState);
rec.SetString(9, msgin.FromZip);
rec.SetString(10, msgin.MessageSid);
rec.SetString(11, msgin.NumMedia);
rec.SetString(12, msgin.NumSegments);
rec.SetString(13, msgin.SmsSid);
rec.SetString(14, msgin.SmsStatus);
rec.SetString(15, msgin.ToState);
rec.SetString(16, msgin.To);
rec.SetString(17, msgin.ToCity);
rec.SetString(18, msgin.ToCountry);
rec.SetString(19, msgin.ToZip);
rec.SetString(20, msgin.MediaURLs);
rs.Insert(rec);
// Get this inserter record ID
cmd.CommandText = "SELECT @@IDENTITY";
cmd.CommandType = CommandType.Text;
id = Convert.ToInt32(cmd.ExecuteScalar());
// DEBUG
App.logger.Log("DBStore.StoreInboxMessage() storing inbox message from '" + msgin.From + "' to '" + msgin.To + "', ID=" + id);
}
catch (Exception ex)
{
var message = "! Error in DBStore.StoreInboxMessage(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
retVal = false;
}
cmd.Dispose();
return(retVal);
}
/// <summary>
/// Returns generated Id.
/// </summary>
/// <returns></returns>
public static Int32 Write_Level1(Level1 level1, SqlCeConnection conn)
{
Dictionary <string, Int32> idBag = new Dictionary <string, int>();
// Level1
using (SqlCeCommand cmd = new SqlCeCommand(level1.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
r.SetString(1, level1.Value); // Payload
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
idBag[level1.GetType().Name] = rs.GetInt32(0);
}
}
foreach (var level2 in level1.levels) // Payload
{
// Level2
using (SqlCeCommand cmd = new SqlCeCommand(level2.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
r.SetInt32(GetOrdinal(r, level2.GetType(), level1.GetType()), idBag[level1.GetType().Name]); // foreign key
r.SetString(1, level2.Value); // payload
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
idBag[level2.GetType().Name] = rs.GetInt32(0);
}
}
foreach (var level3 in level2.levels)
{
using (SqlCeCommand cmd = new SqlCeCommand(level3.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
r.SetInt32(GetOrdinal(r, level3.GetType(), level2.GetType()), idBag[level2.GetType().Name]);
r.SetString(1, level3.Value);
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
idBag[level3.GetType().Name] = rs.GetInt32(0);
}
}
foreach (var level4 in level3.levels)
{
using (SqlCeCommand cmd = new SqlCeCommand(level4.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
r.SetInt32(GetOrdinal(r, level4.GetType(), level3.GetType()), idBag[level3.GetType().Name]);
r.SetString(1, level4.Value);
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
idBag[level4.GetType().Name] = rs.GetInt32(0);
}
}
foreach (var level5 in level4.levels)
{
using (SqlCeCommand cmd = new SqlCeCommand(level5.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
r.SetInt32(GetOrdinal(r, typeof(Level5), typeof(Level4)), idBag[level4.GetType().Name]);
r.SetString(1, level5.Value);
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
}
}
}
}
}
}
return(idBag[level1.GetType().Name]);
}