private bool CheckTheAvailabilityOfTheMovie(int id)
{
string queryString =
"SELECT count(*) FROM BusyFilmCopy WHERE ClientId IS NULL AND FilmId='" + id + "'";
cmd.CommandText = queryString;
cmd.Connection = connection;
int i = 0;
try
{
connection.Open();
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
while (rs.Read())
{
i = Convert.ToInt16(rs[0]);
}
rs.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (i == 0)
{
MessageBox.Show("Извините, копии данного фильма закончились. Выберите другой фильм.");
NextButton.Enabled = false;
return(false);
}
else
{
NextButton.Enabled = true;
return(true);
}
}
/// <summary>
/// Adds the series.
/// </summary>
/// <param name="receive">The receive.</param>
/// <param name="StudyInstanceUid">The study instance uid.</param>
/// <param name="ConnectionString">The connection string.</param>
/// <param name="AETitle">The AE title.</param>
/// <param name="dataset">The dataset.</param>
/// <returns></returns>
private static string AddSeries(DateTime receive, string StudyInstanceUid, string ConnectionString, string AETitle, DicomDataSet dataset)
{
string seriesInstance = dataset.GetValue <string>(DicomTag.SeriesInstanceUID, string.Empty);
if (string.IsNullOrEmpty(seriesInstance))
{
throw new ArgumentException("Missing dicom tag", "Series Instance UID");
}
if (!RecordExists(ConnectionString, "Series", "SeriesInstanceUID = '" + seriesInstance + "'"))
{
DateTime? sd = dataset.GetValue <DateTime?>(DicomTag.SeriesDate, null, GetDate);
DateTime? st = dataset.GetValue <DateTime?>(DicomTag.SeriesTime, null, GetDate);
SqlCeResultSet rs = SqlCeHelper.ExecuteResultSet(ConnectionString, "Series");
SqlCeUpdatableRecord series = rs.CreateRecord();
series.SetValue(0, seriesInstance);
series.SetValue(1, dataset.GetValue <string>(DicomTag.Modality, string.Empty));
series.SetValue(2, dataset.GetValue <string>(DicomTag.SeriesNumber, string.Empty));
string seriesDate = GetDateString(sd, st);
if (seriesDate.Length > 0)
{
series.SetValue(3, seriesDate);
}
series.SetValue(4, dataset.GetValue <string>(DicomTag.SeriesDescription, string.Empty));
series.SetValue(5, dataset.GetValue <string>(DicomTag.InstitutionName, string.Empty));
series.SetValue(6, GetDateString(receive, receive));
series.SetValue(7, AETitle);
series.SetValue(8, StudyInstanceUid);
rs.Insert(series);
rs.Close();
}
return(seriesInstance);
}
public SymbolListLoaderManager(Size imageSize, int countPerPage, SqlCeResultSet resultSet, int totalPages)
{
this.symbolLoaderList = new SymbolListLoader[3];
for (int i = 0; i < this.symbolLoaderList.Length; i++)
{
this.symbolLoaderList[i] = new SymbolListLoader(imageSize, countPerPage, resultSet);
}
this.resultSet = resultSet;
this.totalPages = totalPages;
if (totalPages > 0)
{
this.symbolLoaderList[0].Load(true, 0);
}
if (totalPages > 1)
{
this.symbolLoaderList[1].Load(false, 1);
}
}
public Lista CarregarProximaLista()
{
using (SqlCeCommand command = CreateCommand("SELECT TOP(1) * FROM LISTA WHERE SORTEADA = 0 ORDER BY ORDEM_SORTEIO")) {
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
if (resultSet.Read())
{
int idLista = resultSet.GetInt32(resultSet.GetOrdinal("ID_LISTA"));
return(new Lista {
IdLista = idLista,
OrdemSorteio = resultSet.GetInt32(resultSet.GetOrdinal("ORDEM_SORTEIO")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME")),
Quantidade = resultSet.GetInt32(resultSet.GetOrdinal("QUANTIDADE")),
CandidatosDisponiveis = CandidatosDisponiveisLista(idLista)
});
}
else
{
return(null);
}
}
}
}
private void SaveHistory()
{
StringBuilder q = new StringBuilder();
connection.Open();
var transaction = connection.BeginTransaction();
SqlCeCommand sqlCeCommand = connection.CreateCommand();
sqlCeCommand.CommandType = CommandType.TableDirect;
sqlCeCommand.CommandText = "HistoryTransaction";
sqlCeCommand.Transaction = transaction;
SqlCeResultSet result = sqlCeCommand.ExecuteResultSet(ResultSetOptions.Updatable);
SqlCeUpdatableRecord rec = result.CreateRecord();
foreach (DataRow item in dtHistory.Rows)
{
string encrypt = Cryptography.RSA2.Encrypt(item["Body"].ToString());
// q.Append(@"INSERT INTO [HistoryTransaction] ([IsGroup], [AccountName],
// [ServerID], [GroupName], [Body], [DateTime], [PIC]) VALUES ");
// q.AppendFormat("({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')",
// 1, _xmppClient.Username, _xmppClient.XmppDomain,
// _roomJid.Bare, encrypt, DateTime.Parse(item["Body"].ToString()), item["PIC"].ToString());
rec.SetValue(1, 1);
rec.SetValue(2, _xmppClient.Username);
rec.SetValue(3, _xmppClient.XmppDomain);
rec.SetValue(4, _roomJid.Bare);
rec.SetValue(5, encrypt);
rec.SetValue(6, DateTime.Parse(item["DateTime"].ToString()));
rec.SetValue(7, item["PIC"].ToString());
result.Insert(rec);
}
result.Close();
result.Dispose();
transaction.Commit();
connection.Close();
}
public void ExecuteResultSetCallsInstrumentationFireCommandExecutedEvent()
{
int executeCount = 0;
int failedCount = 0;
var mockProvider = new Mock <IDataInstrumentationProvider>();
mockProvider.Setup(p => p.FireCommandExecutedEvent(It.IsAny <DateTime>()))
.Callback <DateTime>(dt => ++ executeCount);
mockProvider.Setup(
p => p.FireCommandFailedEvent(It.IsAny <string>(), It.IsAny <string>(), It.IsAny <Exception>()))
.Callback <string, string, Exception>((c, cs, ex) => ++ failedCount);
var ceDb = GetDatabase(mockProvider.Object);
using (DbCommand command = ceDb.GetSqlStringCommand(queryString))
{
SqlCeResultSet reader = ceDb.ExecuteResultSet(command);
reader.Close();
}
Assert.AreEqual(1, executeCount);
Assert.AreEqual(0, failedCount);
}
public void CanExecuteQueryThroughDataReaderUsingTransaction()
{
SqlCeDatabase db = (SqlCeDatabase)this.db;
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbCommand command = db.GetSqlStringCommand(insertString))
{
using (RollbackTransactionWrapper transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
{
using (SqlCeResultSet reader = db.ExecuteResultSet(command, transaction.Transaction))
{
Assert.AreEqual(1, reader.RecordsAffected);
reader.Close();
}
}
Assert.AreEqual(ConnectionState.Open, connection.State);
command.Connection.Close();
}
}
}
public static Subscriber GetSubscriber(string licencePlates)
{
string sql = "select licencePlates, validTo from subscriber where licencePlates=@licencePlates";
Subscriber subscriber = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@licencePlates", licencePlates);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordLicencePlates = rs.GetOrdinal("licencePlates");
int ordValidTo = rs.GetOrdinal("validTo");
rs.ReadFirst();
subscriber = new Subscriber();
subscriber.LicencePlates = rs.GetString(ordLicencePlates);
subscriber.ValidTo = rs.GetDateTime(ordValidTo);
}
return(subscriber);
}
public FormTableViewer getViewer(GiangManh.MM.Database.Tables table)
{
string sql = "SELECT * FROM {0} ";
switch (table)
{
case Database.Tables.Marks:
sql += "ORDER BY st_id";
break;
case Database.Tables.Students:
sql += "ORDER BY gr_id ASC, no ASC";
break;
}
SqlCeResultSet r = db.Select(string.Format(sql, table));
FormTableViewer viewer = new FormTableViewer();
viewer._MMHostForm = this;
viewer.MdiParent = this;
viewer.Text = string.Format("{0} {1}", viewer.Text, table);
viewer.grid.DataSource = r;
FormTableViewerTag tag = new FormTableViewerTag(db, table);
tag.expand = string.Format("{0}", table);
viewer.Tag = tag;
viewer.grid.DataError += new DataGridViewDataErrorEventHandler(grid_DataError);
viewer.grid.CellValueChanged += new DataGridViewCellEventHandler(grid_CellValueChanged);
string[] caption = db.TableCaptions(table);
if (caption.Length == viewer.grid.Columns.Count)
{
for (int i = 0; i < viewer.grid.Columns.Count; i++)
{
viewer.grid.Columns[i].HeaderText = caption[i];
}
}
return(viewer);
}
private void FlushCalls(SqlCeResultSet resultSet)
{
//a lock is already taken at this point
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
int childOrdinal = resultSet.GetOrdinal("ChildId");
int parentOrdinal = resultSet.GetOrdinal("ParentId");
int threadOrdinal = resultSet.GetOrdinal("ThreadId");
foreach (KeyValuePair <int, SortedDictionary <int, SortedList <int, int> > > threadKvp in m_calls.Graph)
{
int threadId = threadKvp.Key;
foreach (KeyValuePair <int, SortedList <int, int> > parentKvp in threadKvp.Value)
{
int parentId = parentKvp.Key;
foreach (KeyValuePair <int, int> hitsKvp in parentKvp.Value)
{
int childId = hitsKvp.Key;
int hits = hitsKvp.Value;
bool result = resultSet.Seek(DbSeekOptions.FirstEqual, threadId, parentId, childId);
if (result && resultSet.Read())
{
//found it, update the hit count and move on
hits += (int)resultSet[hitsOrdinal];
resultSet.SetInt32(hitsOrdinal, hits);
resultSet.Update();
}
else
{
//not in the db, create a new record
CreateRecord(resultSet, threadId, parentId, childId, hits);
}
}
parentKvp.Value.Clear();
}
}
}
public bool CheckTable <T>(T TableItem, ref string errMsg) where T : class, new()
{
SetPropertyInfosAndUniqueIdentifier(TableItem);
string sqlStatement = ConstructSQLStatementCheckTable();
try
{
PrepareAndOpenConnection();
SqlCeCommand command = GetSqlCeCommand(Connection, sqlStatement);
SqlCeResultSet ResultSet = command.ExecuteResultSet(ResultSetOptions.Scrollable);
if (ResultSet.HasRows)
{
while (ResultSet.Read())
{
object result = ResultSet.GetValue(2);
if ((string)result == typeof(T).Name)
{
return(true);
}
}
}
errMsg = "无法创建数据表！";
return(false);
}
catch (SqlCeException sqlexception)
{
throw new Exception(sqlexception.Message + sqlexception.StackTrace);
}
catch (Exception exception)
{
throw new Exception(exception.Message + exception.StackTrace);
}
finally
{
ReturnToPreviousConnectionState();
}
}
private void btnCarregaGrid_Click(object sender, EventArgs e)
{
SqlCeConnection cn = new SqlCeConnection(stringConexao());
if (cn.State == ConnectionState.Closed)
{
try
{
cn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
try
{
// define o command para usar a tabela e não a consulta
SqlCeCommand cmd = new SqlCeCommand(nomeTabela, cn);
cmd.CommandType = CommandType.TableDirect;
// pega a tabela
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
// carrega o resultado no grid
dgvBanco.DataSource = rs;
}
catch (SqlCeException sqlexception)
{
MessageBox.Show(sqlexception.Message, "Erro.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Erro.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static SqlCeResultSet ExecuteScalar(String sqlstring, ResultSetOptions rsopts)
{
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = sqlstring;
command.CommandType = System.Data.CommandType.Text;
try
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
SqlCeResultSet result = command.ExecuteResultSet(rsopts);
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connection.Close();
}
}
protected void btnLogin_Click(object sender, EventArgs e)
{
con.Open();
string query = "select * from empleat where usu = @usuario and psw = @password and dataBaixa IS NOT NULL";
SqlCeCommand command = new SqlCeCommand(query, con);
command.Parameters.AddWithValue("@usuario", txtbUser.Text);
command.Parameters.AddWithValue("@password", txtbPass.Text);
//SqlCeDataReader reader = command.ExecuteReader();
//SqlCeDataReader reader = command.ExecuteResultSet(ResultSetOptions.Scrollable);
SqlCeResultSet results = command.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows) //Si el usuario existe lo redireccionamos a la pagina Home
{
results.ReadFirst();
Session["idEmpleat"] = results.GetInt32(0);
Session["idEmpresa"] = results.GetInt32(1);
FormsAuthentication.RedirectFromLoginPage(txtbUser.Text, chkRecuerda.Checked);
}
else //Si el usuario no existe
{
lblMensaje.Text = "Usuario y/o contraseña incorrectos.";
dvMensaje.Visible = true;
txtbUser.Text = "";
txtbPass.Text = "";
txtbUser.Focus();
}
con.Close();
}
private void LoadDatabase(string fileName)
{
try
{
this._axfTableInfos = new List <AXF_TableInfo>();
_sqlCeDb = new SqlCeDb();
if (this._sqlCeDb.Open(fileName))
{
SqlCeResultSet set = new SqlCeResultSet("GEOMETRY_COLUMNS", this._sqlCeDb);
List <GeometryColumn> list = set.GetGeometryColumns();
foreach (GeometryColumn geometryColumn in list)
{
this._axfTableInfos.Add(new AXF_TableInfo(this._sqlCeDb, geometryColumn.TableName, geometryColumn.GeometryType));
}
}
Close();
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
}
private void FlushSamples(SqlCeResultSet resultSet)
{
//now to update the samples table
foreach (KeyValuePair <int, SortedList <int, int> > sampleKvp in m_samples)
{
if (sampleKvp.Value.Count == 0)
{
continue;
}
int threadOrdinal = resultSet.GetOrdinal("ThreadId");
int functionOrdinal = resultSet.GetOrdinal("FunctionId");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
foreach (KeyValuePair <int, int> threadKvp in sampleKvp.Value)
{
if (!resultSet.Seek(DbSeekOptions.FirstEqual, threadKvp.Key, sampleKvp.Key))
{
//doesn't exist in the table, we need to add it
var row = resultSet.CreateRecord();
row[threadOrdinal] = threadKvp.Key;
row[functionOrdinal] = sampleKvp.Key;
row[hitsOrdinal] = threadKvp.Value;
resultSet.Insert(row, DbInsertOptions.PositionOnInsertedRow);
}
else
{
resultSet.Read();
resultSet.SetValue(hitsOrdinal, (int)resultSet[hitsOrdinal] + threadKvp.Value);
resultSet.Update();
}
}
sampleKvp.Value.Clear();
}
}
public void ExecuteQueryThroughDataReaderUsingNullCommandAndNullTransactionThrows()
{
using (SqlCeResultSet reader = db.ExecuteResultSet(null, (DbTransaction)null)) { }
}
public void ExecuteResultSetUsingNullCommandThrows()
{
using (SqlCeResultSet reader = db.ExecuteResultSet((DbCommand)null)) { }
}
public SqlCeResultSet ExecuteResultSet(ResultSetOptions options, SqlCeResultSet resultSet)
{
var index = 0;
Debug.Assert(CommandTexts.Length == 1 || CommandTexts.Length == 2);
if (commandTexts.Length > 1)
{
command.CommandText = commandTexts[index++];
// Not dispatching to interceptors here because that was already done before calling ExecuteReader.
// This call is simply an implementation detail of how the SQL CE provider handles the command text.
var cAffectedRecords = command.ExecuteNonQuery();
// If first command doesn't affect any records, then second query should not return any rows
//
if (cAffectedRecords == 0)
{
command.CommandText = "select * from (" + CommandTexts[index] + ") as temp where 1=2;";
}
else
{
command.CommandText = commandTexts[index];
}
}
else
{
command.CommandText = commandTexts[index];
}
try
{
return command.ExecuteResultSet(options, resultSet);
}
catch (SqlCeException e)
{
// index == 1, means there are multiple commands in this SqlCeMultiCommand. Which indicates Server generated keys scenario.
// This check will only work under the assumptions that:
// 1. SqlCeMultiCommand is used only for Server generated keys scenarios.
// 2. Server generated keys query generate exactly 2 commands.
//
if (index == 1)
{
// Wrap in inner exception and let user know that DML has succeeded.
throw new SystemException(EntityRes.GetString(EntityRes.ADP_CanNotRetrieveServerGeneratedKey), e);
}
else
{
throw;
}
}
}
private void FlushTimings(SqlCeResultSet resultSet)
{
foreach (KeyValuePair <int, List <long> > timingKvp in m_timings)
{
if (timingKvp.Value.Count == 0)
{
continue;
}
int funcOrdinal = resultSet.GetOrdinal("FunctionId");
int minOrdinal = resultSet.GetOrdinal("RangeMin");
int maxOrdinal = resultSet.GetOrdinal("RangeMax");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
for (int t = 0; t < timingKvp.Value.Count; ++t)
{
bool foundBin = true;
long time = timingKvp.Value[t];
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, time))
{
foundBin = false;
}
if (foundBin)
{
resultSet.Read();
var id = resultSet.GetInt32(funcOrdinal);
if (id != timingKvp.Key)
{
if (!resultSet.Read())
{
foundBin = false;
}
}
if (foundBin)
{
var min = resultSet.GetInt64(minOrdinal);
var max = resultSet.GetInt64(maxOrdinal);
if (id != timingKvp.Key || time < min || time > max)
{
foundBin = false;
}
}
}
if (foundBin)
{
//we've got a usable bin, increment and move on
var hits = resultSet.GetInt32(hitsOrdinal);
resultSet.SetInt32(hitsOrdinal, hits + 1);
resultSet.Update();
continue;
}
//didn't find a bin, create a new one for this entry
var row = resultSet.CreateRecord();
row[funcOrdinal] = timingKvp.Key;
row[minOrdinal] = time;
row[maxOrdinal] = time;
row[hitsOrdinal] = 1;
resultSet.Insert(row, DbInsertOptions.KeepCurrentPosition);
//we need to bin-merge
//start by seeking to the first record for this function
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
{
resultSet.ReadFirst();
}
else
{
resultSet.Read();
}
var mergeId = resultSet.GetInt32(funcOrdinal);
if (mergeId != timingKvp.Key)
{
resultSet.Read();
}
mergeId = resultSet.GetInt32(funcOrdinal);
//we know at least one exists, cause we just inserted one
Debug.Assert(mergeId == timingKvp.Key);
//Search for the merge that produces the smallest merged bucket
long lastMin = resultSet.GetInt64(minOrdinal);
int lastHits = resultSet.GetInt32(hitsOrdinal);
bool shouldMerge = resultSet.Read();
//these store all the data about the best merge so far
long smallestRange = long.MaxValue;
long bestMin = 0;
long bestMax = 0;
int mergedHits = 0;
for (int b = 0; b < kTimingBuckets && shouldMerge; ++b)
{
long max = resultSet.GetInt64(maxOrdinal);
long range = max - lastMin;
if (range < smallestRange)
{
smallestRange = range;
bestMin = lastMin;
bestMax = max;
mergedHits = lastHits + resultSet.GetInt32(hitsOrdinal);
}
lastMin = resultSet.GetInt64(minOrdinal);
lastHits = resultSet.GetInt32(hitsOrdinal);
//if this read fails, we have insufficient buckets to bother merging
shouldMerge = resultSet.Read();
}
if (shouldMerge)
{
//seek to the first (lower) bin
resultSet.Seek(DbSeekOptions.FirstEqual, timingKvp.Key, bestMin);
resultSet.Read();
//expand this bin to include the next one
resultSet.SetInt64(maxOrdinal, bestMax);
resultSet.SetInt32(hitsOrdinal, mergedHits);
//go to the now redundant bin
resultSet.Update();
resultSet.Read();
//delete the bin
resultSet.Delete();
}
}
#if FALSE
//DEBUG ONLY HACK: display buckets
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
{
resultSet.ReadFirst();
}
else
{
resultSet.Read();
}
var tempId = resultSet.GetInt32(funcOrdinal);
if (tempId != timingKvp.Key)
{
resultSet.Read();
}
Console.WriteLine("Buckets for function {0}:", timingKvp.Key);
for (int b = 0; b < kTimingBuckets; ++b)
{
long min = resultSet.GetInt64(minOrdinal);
long max = resultSet.GetInt64(maxOrdinal);
int hits = resultSet.GetInt32(hitsOrdinal);
Console.WriteLine("[{0}, {1}]: {2}", min, max, hits);
resultSet.Read();
}
#endif
}
}
public void Dispose()
{
if (_sqlCmd != null)
{
_resultSet.Dispose();
_sqlCmd.Dispose();
_record = null;
_resultSet = null;
_sqlCmd = null;
}
}
/// <summary>
/// Gats all of the indexes for a given table.
/// </summary>
/// <param name="connectionString">The connection string used to connect to the target database.</param>
/// <param name="table"></param>
/// <returns></returns>
public IndexSchema[] GetTableIndexes(string connectionString, TableSchema table)
{
string sql = string.Format("SELECT DISTINCT INDEX_NAME as [IndexName] FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = '{0}' AND SUBSTRING(COLUMN_NAME, 1,5) <> '__sys' ORDER BY INDEX_NAME ASC", table.Name);
var indexNames = new List <string>();
var indices = new List <IndexSchema>();
using (SqlCeCommand cmd = GetCeCommand(connectionString, sql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
while (results.Read())
{
indexNames.Add((string)results["IndexName"] ?? String.Empty);
}
}
}
foreach (string indexName in indexNames)
{
//Erik Ejlskov - corrected SQL statement to include table name!
string getIndexSql = string.Format(@"SELECT
INDEX_NAME as [Name],
PRIMARY_KEY as [IsPrimaryKey],
[UNIQUE] as [IsUnique],
[CLUSTERED] as [IsClustered],
[COLUMN_NAME] as [ColumnName]
FROM
INFORMATION_SCHEMA.INDEXES
WHERE
INDEX_NAME = '{0}' AND
TABLE_NAME = '{1}'
ORDER BY
[Name],
[ORDINAL_POSITION]", indexName, table.Name);
// string indexName = String.Empty;
bool isPrimaryKey = false;
bool isUnique = false;
bool isClustered = false;
var memberColumns = new List <string>();
bool read = false;
using (SqlCeCommand cmd = GetCeCommand(connectionString, getIndexSql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
while (results.Read())
{
if (!read)
{
// indexName = (string)results["Name"];
isPrimaryKey = (bool)results["IsPrimaryKey"];
isUnique = (bool)results["IsUnique"];
isClustered = (bool)results["IsClustered"];
memberColumns.Add((string)results["ColumnName"]);
read = true;
}
else
{
memberColumns.Add((string)results["ColumnName"]);
} // if(!read)
} // while
indices.Add(new IndexSchema(table, indexName, isPrimaryKey, isUnique, isClustered, memberColumns.ToArray()));
} // using(results)
} // using(cmd)
} //foreach(indexName)
return(indices.ToArray());
}
public static void RunResultsetTest(MainForm frm, PFSQLServerCE35 db)
{
SqlCeResultSet res = null;
string query = string.Empty;
_msg.Length = 0;
_msg.Append("Running RunResultsetTest ...");
Program._messageLog.WriteLine(_msg.ToString());
try
{
query = frm.txtQuery.Text;
db.OpenConnection();
res = db.RunQueryResultset(query);
if (res.HasRows)
{
db.returnResult += new PFSQLServerCE35.ResultDelegate(OutputResults);
//sqlce.returnResultAsString += new PFSQLServerCE.ResultAsStringDelegate(OutputResultsAsString);
db.ProcessResultSet(res);
res.Close();
res = db.RunQueryResultset(query);
db.returnResultAsString += new PFSQLServerCE35.ResultAsStringDelegate(OutputExtractFormattedData);
db.ExtractDelimitedDataFromResultSet(res, "~", "\r\n", true);
res.Close();
res = db.RunQueryResultset(query);
db.SaveResultSetToXmlFile(res, @"c:\temp\TestCe35Res.xml");
res.Close();
res = db.RunQueryResultset(query);
db.SaveResultSetWithSchemaToXmlFile(res, @"c:\temp\TestCe35ResPlus.xml");
res.Close();
res = db.RunQueryResultset(query);
db.SaveResultSetToXmlSchemaFile(res, @"c:\temp\TestCe35Res.xsd");
res.Close();
res = db.RunQueryResultset(query);
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(PFSQLServerCE35.ConvertResultSetToDataTable(res, "ResultTable"));
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
res.Close();
}
db.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("...RunResultsetTest Finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
internal static void StartTempLogging(bool consolemode, bool logmode, bool dumpmode)
{
bool initialized = false;
if (consolemode)
{
Console.Clear();
Console.Title = "TempServe";
Console.BufferHeight = Console.WindowHeight = 20;
Console.BufferWidth = Console.WindowWidth = 80;
Console.CursorVisible = false;
Console.WriteLine("Initializing ...");
}
SqlCeResultSet rs = null;
using (var mon = new W83627DHG())
{
fc = mon;
var cpu = new CPU();
var asp0800 = new SMBus.ASP0800();
var ard = new Arduino();
#if NVIDIA
Debugger.Launch();
var nv = new NVidiaGPU();
#endif
if (consolemode)
{
SetConsoleCtrlHandler(
x =>
{
running = false;
mon.Dispose();
Console.CursorVisible = true;
return(x != CtrlTypes.CTRL_C_EVENT);
}
, true);
}
Dictionary <string, Sample> min = null;
Dictionary <string, Sample> max = null;
var avg5 = new Queue <Dictionary <string, Sample> >(60);
var sw = Stopwatch.StartNew();
while (running)
{
var elapsed = sw.Elapsed;
var alldata = cpu.GetData().
Concat(mon.GetData()).
Concat(asp0800.GetData()).
Concat(ard.GetData()).
#if NVIDIA
Concat(nv.GetData()).
#endif
ToList();
var now = DateTime.Now;
foreach (var smp in alldata)
{
smp.Time = now;
}
lastsample = alldata;
if (!initialized)
{
if (consolemode)
{
var height = alldata.Count + 2;
Console.BufferHeight = Console.WindowHeight = height;
}
if (logmode)
{
rs = BuildDatabase(alldata.Where(x => x.Log).ToList());
}
initialized = true;
}
if (logmode)
{
RecordSample(rs, alldata);
}
if (dumpmode)
{
using (var w = new StringWriter())
{
mon.DumpRawByteData(w);
File.AppendAllText("W83627DHG.log", w.ToString());
}
using (var w = new StringWriter())
{
mon.DumpSIOByteData(w);
File.AppendAllText("Super-IO.log", w.ToString());
}
//using (var w = new StringWriter())
//{
// asp0800.DumpRawByteData(w);
// File.AppendAllText("ASP0800b.log", w.ToString());
//}
using (var w = new StringWriter())
{
asp0800.DumpRawWordData(w);
File.AppendAllText("ASP0800w.log", w.ToString());
}
}
if (consolemode)
{
Console.Title = string.Format("TempServe - Running for: {3}d {0:00}:{1:00}:{2:00}",
elapsed.Hours, elapsed.Minutes, elapsed.Seconds, elapsed.Days);
while (avg5.Count >= 60)
{
avg5.Dequeue();
}
var hashdata = alldata.ToDictionary(x => x.Name, x => x);;
avg5.Enqueue(hashdata);
if (min == null)
{
min = hashdata;
}
else
{
min = HashMap((x, y) => x.Value > y.Value ? y : x, min, hashdata);
}
if (max == null)
{
max = hashdata;
}
else
{
max = HashMap((x, y) => x.Value < y.Value ? y : x, max, hashdata);
}
using (var output = new StringWriter())
{
output.WriteLine("{0,-18}{1,10}{2,10}{3,10}{4,10}{5,10}{6,10}",
"Sensor", "Current", "Min", "Max", "Avg(10)", "Avg(30)", "Avg(60)");
foreach (var s in alldata)
{
var avg = avg5.Reverse().ToArray();
var fs = BuildFormatString(s);
output.WriteLine(fs, s.Name, s.Value,
min[s.Name].Value,
max[s.Name].Value,
avg.Take(10).Select(x => x[s.Name]).Average(x => x.Value),
avg.Take(30).Select(x => x[s.Name]).Average(x => x.Value),
avg.Take(60).Select(x => x[s.Name]).Average(x => x.Value)
);
}
Console.Clear();
Console.Write(output.ToString());
}
}
var taken = Math.Min((sw.Elapsed - elapsed).TotalMilliseconds, 100) + 3;
Thread.Sleep(1000 - (int)taken);
}
}
}
private void FlushTimings(SqlCeResultSet resultSet)
{
foreach(KeyValuePair<int, List<long>> timingKvp in m_timings)
{
if(timingKvp.Value.Count == 0)
continue;
int funcOrdinal = resultSet.GetOrdinal("FunctionId");
int minOrdinal = resultSet.GetOrdinal("RangeMin");
int maxOrdinal = resultSet.GetOrdinal("RangeMax");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
for(int t = 0; t < timingKvp.Value.Count; ++t)
{
bool foundBin = true;
long time = timingKvp.Value[t];
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, time))
{
foundBin = false;
}
if(foundBin)
{
resultSet.Read();
var id = resultSet.GetInt32(funcOrdinal);
if(id != timingKvp.Key)
{
if(!resultSet.Read())
{
foundBin = false;
}
}
if(foundBin)
{
var min = resultSet.GetInt64(minOrdinal);
var max = resultSet.GetInt64(maxOrdinal);
if(id != timingKvp.Key || time < min || time > max)
foundBin = false;
}
}
if(foundBin)
{
//we've got a usable bin, increment and move on
var hits = resultSet.GetInt32(hitsOrdinal);
resultSet.SetInt32(hitsOrdinal, hits + 1);
resultSet.Update();
continue;
}
//didn't find a bin, create a new one for this entry
var row = resultSet.CreateRecord();
row[funcOrdinal] = timingKvp.Key;
row[minOrdinal] = time;
row[maxOrdinal] = time;
row[hitsOrdinal] = 1;
resultSet.Insert(row, DbInsertOptions.KeepCurrentPosition);
//we need to bin-merge
//start by seeking to the first record for this function
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
resultSet.ReadFirst();
else
resultSet.Read();
var mergeId = resultSet.GetInt32(funcOrdinal);
if(mergeId != timingKvp.Key)
resultSet.Read();
mergeId = resultSet.GetInt32(funcOrdinal);
//we know at least one exists, cause we just inserted one
Debug.Assert(mergeId == timingKvp.Key);
//Search for the merge that produces the smallest merged bucket
long lastMin = resultSet.GetInt64(minOrdinal);
int lastHits = resultSet.GetInt32(hitsOrdinal);
bool shouldMerge = resultSet.Read();
//these store all the data about the best merge so far
long smallestRange = long.MaxValue;
long bestMin = 0;
long bestMax = 0;
int mergedHits = 0;
for(int b = 0; b < kTimingBuckets && shouldMerge; ++b)
{
long max = resultSet.GetInt64(maxOrdinal);
long range = max - lastMin;
if(range < smallestRange)
{
smallestRange = range;
bestMin = lastMin;
bestMax = max;
mergedHits = lastHits + resultSet.GetInt32(hitsOrdinal);
}
lastMin = resultSet.GetInt64(minOrdinal);
lastHits = resultSet.GetInt32(hitsOrdinal);
//if this read fails, we have insufficient buckets to bother merging
shouldMerge = resultSet.Read();
}
if(shouldMerge)
{
//seek to the first (lower) bin
resultSet.Seek(DbSeekOptions.FirstEqual, timingKvp.Key, bestMin);
resultSet.Read();
//expand this bin to include the next one
resultSet.SetInt64(maxOrdinal, bestMax);
resultSet.SetInt32(hitsOrdinal, mergedHits);
//go to the now redundant bin
resultSet.Update();
resultSet.Read();
//delete the bin
resultSet.Delete();
}
}
#if FALSE
//DEBUG ONLY HACK: display buckets
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
resultSet.ReadFirst();
else
resultSet.Read();
var tempId = resultSet.GetInt32(funcOrdinal);
if(tempId != timingKvp.Key)
resultSet.Read();
Console.WriteLine("Buckets for function {0}:", timingKvp.Key);
for(int b = 0; b < kTimingBuckets; ++b)
{
long min = resultSet.GetInt64(minOrdinal);
long max = resultSet.GetInt64(maxOrdinal);
int hits = resultSet.GetInt32(hitsOrdinal);
Console.WriteLine("[{0}, {1}]: {2}", min, max, hits);
resultSet.Read();
}
#endif
}
}
private void FlushCalls(SqlCeResultSet resultSet)
{
//a lock is already taken at this point
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
int childOrdinal = resultSet.GetOrdinal("ChildId");
int parentOrdinal = resultSet.GetOrdinal("ParentId");
int threadOrdinal = resultSet.GetOrdinal("ThreadId");
foreach(KeyValuePair<int, SortedDictionary<int, SortedList<int, int>>> threadKvp in m_calls.Graph)
{
int threadId = threadKvp.Key;
foreach(KeyValuePair<int, SortedList<int, int>> parentKvp in threadKvp.Value)
{
int parentId = parentKvp.Key;
foreach(KeyValuePair<int, int> hitsKvp in parentKvp.Value)
{
int childId = hitsKvp.Key;
int hits = hitsKvp.Value;
bool result = resultSet.Seek(DbSeekOptions.FirstEqual, threadId, parentId, childId);
if(result && resultSet.Read())
{
//found it, update the hit count and move on
hits += (int) resultSet[hitsOrdinal];
resultSet.SetInt32(hitsOrdinal, hits);
resultSet.Update();
}
else
{
//not in the db, create a new record
CreateRecord(resultSet, threadId, parentId, childId, hits);
}
}
parentKvp.Value.Clear();
}
}
}
/// <summary>
/// Gets all of the table keys for a given table.
/// </summary>
/// <param name="connectionString">The connection string used to connect to the target database.</param>
/// <param name="table"></param>
/// <returns></returns>
public TableKeySchema[] GetTableKeys(string connectionString, TableSchema table)
{
var keyNames = new List <string>();
string sql = string.Format("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_TABLE_NAME = '{0}' ", table.Name);
using (SqlCeCommand cmd = GetCeCommand(connectionString, sql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
while (results.Read())
{
keyNames.Add(results[0].ToString());
} // while
}
}
var keys = new List <TableKeySchema>();
string name = string.Empty;
string fkTable = string.Empty;
string pkTable = string.Empty;
foreach (string keyName in keyNames)
{
sql = string.Format("SELECT KCU1.TABLE_NAME AS FK_TABLE_NAME, KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME, KCU1.COLUMN_NAME AS FK_COLUMN_NAME, KCU2.TABLE_NAME AS UQ_TABLE_NAME, KCU2.CONSTRAINT_NAME AS UQ_CONSTRAINT_NAME, KCU2.COLUMN_NAME AS UQ_COLUMN_NAME, KCU2.ORDINAL_POSITION AS UQ_ORDINAL_POSITION, KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION WHERE KCU1.TABLE_NAME = '{0}' AND KCU2.TABLE_NAME = RC.UNIQUE_CONSTRAINT_TABLE_NAME AND KCU1.CONSTRAINT_NAME = '{1}' ORDER BY FK_TABLE_NAME, FK_CONSTRAINT_NAME, FK_ORDINAL_POSITION", table.Name, keyName);
using (SqlCeCommand cmd = GetCeCommand(connectionString, sql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
bool hasRows = false;
var pkCols = new List <string>();
var fkCols = new List <string>();
while (results.Read())
{
hasRows = true;
name = (string)results["FK_CONSTRAINT_NAME"];
fkTable = (string)results["FK_TABLE_NAME"];
pkTable = (string)results["UQ_TABLE_NAME"];
fkCols.Add((string)results["FK_COLUMN_NAME"]);
pkCols.Add((string)results["UQ_COLUMN_NAME"]);
} // while
if (hasRows)
{
keys.Add(new TableKeySchema(table.Database, name, fkCols.ToArray(), fkTable, pkCols.ToArray(), pkTable));
}
}
}
}
keyNames.Clear();
sql = string.Format("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE UNIQUE_CONSTRAINT_TABLE_NAME = '{0}' ", table.Name);
using (SqlCeCommand cmd = GetCeCommand(connectionString, sql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
while (results.Read())
{
keyNames.Add(results[0].ToString());
} // while
}
}
foreach (string keyName in keyNames)
{
//Then get keys pointing to this table
sql = string.Format("SELECT KCU1.TABLE_NAME AS FK_TABLE_NAME, KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME, KCU1.COLUMN_NAME AS FK_COLUMN_NAME, KCU2.TABLE_NAME AS UQ_TABLE_NAME, KCU2.CONSTRAINT_NAME AS UQ_CONSTRAINT_NAME, KCU2.COLUMN_NAME AS UQ_COLUMN_NAME, KCU2.ORDINAL_POSITION AS UQ_ORDINAL_POSITION, KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION WHERE KCU2.TABLE_NAME = '{0}' AND KCU1.TABLE_NAME <> '{0}' AND KCU2.TABLE_NAME = RC.UNIQUE_CONSTRAINT_TABLE_NAME AND KCU1.CONSTRAINT_NAME = '{1}' ORDER BY FK_TABLE_NAME, FK_CONSTRAINT_NAME, FK_ORDINAL_POSITION", table.Name, keyName);
using (SqlCeCommand cmd = GetCeCommand(connectionString, sql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
bool hasRows = false;
var pkCols = new List <string>();
var fkCols = new List <string>();
while (results.Read())
{
hasRows = true;
name = (string)results["FK_CONSTRAINT_NAME"];
pkTable = (string)results["UQ_TABLE_NAME"];
pkCols.Add((string)results["UQ_COLUMN_NAME"]);
fkTable = (string)results["FK_TABLE_NAME"];
fkCols.Add((string)results["FK_COLUMN_NAME"]);
} // while
if (hasRows)
{
keys.Add(new TableKeySchema(table.Database, name, fkCols.ToArray(), fkTable, pkCols.ToArray(), pkTable));
}
}
}
}
return(keys.ToArray());
}
/// <summary>
/// Gets all columns for a given table.
/// </summary>
/// <param name="connectionString">The connection string used to connect to the target database.</param>
/// <param name="table"></param>
/// <returns></returns>
public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table)
{
//Erik Ejlskov - exclude system columns
string getColumnSql = string.Format(@"SELECT
COLUMN_NAME as [Name],
COLUMN_DEFAULT as [Default],
IS_NULLABLE as [IsNullable],
DATA_TYPE as [DataType],
CHARACTER_MAXIMUM_LENGTH as [Length],
NUMERIC_PRECISION as [Precision],
NUMERIC_SCALE as [Scale],
AUTOINC_SEED,
AUTOINC_INCREMENT,
COLUMN_HASDEFAULT,
COLUMN_FLAGS
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = '{0}' AND
COLUMN_FLAGS <> 98 AND
COLUMN_FLAGS <> 114
ORDER BY
ORDINAL_POSITION", table.Name);
var columns = new List <ColumnSchema>();
using (SqlCeCommand cmd = GetCeCommand(connectionString, getColumnSql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
while (results.Read())
{
var extendedProperties = new List <ExtendedProperty>();
if (!results.IsDBNull(7))
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, true, DbType.Boolean));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, results["AUTOINC_SEED"].ToString(), DbType.String));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, results["AUTOINC_INCREMENT"].ToString(), DbType.String));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, false, DbType.Boolean));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, "0", DbType.String));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, "0", DbType.String));
}
if (results["COLUMN_HASDEFAULT"] != null)
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, results["Default"].ToString(), DbType.String));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, string.Empty, DbType.String));
}
var name = (string)results["Name"];
var nativeType = (string)results["DataType"];
//Erik Ejlskov - should be "timestamp" instead
if (nativeType == "rowversion")
{
nativeType = "timestamp";
}
DbType dataType = GetDbTypeFromString(nativeType);
if ((dataType == DbType.Guid && results.GetInt32(10) == 378) || (dataType == DbType.Guid && results.GetInt32(10) == 282))
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsRowGuidColumn, true, DbType.Boolean));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsRowGuidColumn, false, DbType.Boolean));
}
int size;
int.TryParse(results["Length"].ToString(), out size);
byte precision;
byte.TryParse(results["Precision"].ToString(), out precision);
int scale;
int.TryParse(results["scale"].ToString(), out scale);
bool allowNull = GetBoolFromYesNo((string)results["IsNullable"]);
var s = new ColumnSchema(table, name, dataType, nativeType, size, precision, scale, allowNull, extendedProperties.ToArray());
columns.Add(s);
} // while(read)
} // using(results)
} // using(command)
return(columns.ToArray());
}
static void RecordSample(SqlCeResultSet rs, IEnumerable<Sample> data)
{
var rec = rs.CreateRecord();
foreach (var smp in data)
{
if (smp.Log)
{
rec[smp.Name] = smp.Value;
}
}
rs.Insert(rec);
}
public SqlCeResultSetWrapper(DatabaseConnectionWrapper connection, SqlCeResultSet innerResultSet)
{
this.connection = connection;
this.connection.AddRef();
this.InnerResultSet = innerResultSet;
}
private T HydrateEntity <T>(string entityName, SqlCeResultSet results, bool fillReferences)
where T : new()
{
var objectType = typeof(T);
// object item = Activator.CreateInstance(objectType);
T item = new T();
object rowPK = null;
var fields = Entities[entityName].Fields;
CheckOrdinals(entityName);
foreach (var field in fields)
{
var value = results[field.Ordinal];
if (value != DBNull.Value)
{
if (field.DataType == DbType.Object)
{
if (fillReferences)
{
// get serializer
var itemType = item.GetType();
var deserializer = GetDeserializer(itemType);
if (deserializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
field.FieldName, entityName));
}
var @object = deserializer.Invoke(item, new object[] { field.FieldName, value });
field.PropertyInfo.SetValue(item, @object, null);
}
}
else if (field.IsRowVersion)
{
// sql stores this an 8-byte array
field.PropertyInfo.SetValue(item, BitConverter.ToInt64((byte[])value, 0), null);
}
else if (field.PropertyInfo.PropertyType.UnderlyingTypeIs <TimeSpan>())
{
// SQL Compact doesn't support Time, so we're convert to ticks in both directions
var valueAsTimeSpan = new TimeSpan((long)value);
field.PropertyInfo.SetValue(item, valueAsTimeSpan, null);
}
else
{
field.PropertyInfo.SetValue(item, value, null);
}
}
if (field.IsPrimaryKey)
{
rowPK = value;
}
}
// TODO: cache this maybe for perf?
ReferenceAttribute[] referenceFields = null;
// autofill references if desired
if (referenceFields == null)
{
referenceFields = Entities[entityName].References.ToArray();
}
if ((fillReferences) && (referenceFields.Length > 0))
{
//FillReferences(item, rowPK, referenceFields, true);
FillReferences(item, rowPK, referenceFields, false);
}
return(item);
}
public static void RunResultsetToDataTableTest(MainForm frm, PFSQLServerCE35 db)
{
SqlCeResultSet res = null;
string query = string.Empty;
_msg.Length = 0;
_msg.Append("Running RunResultsetToDataTableTest ...");
Program._messageLog.WriteLine(_msg.ToString());
try
{
query = frm.txtQuery.Text;
db.OpenConnection();
res = db.RunQueryResultset(query);
if (res.HasRows)
{
DataTable tab = PFSQLServerCE35.ConvertResultSetToDataTable(res);
_msg.Length = 0;
_msg.Append("Number of rows in table: ");
_msg.Append(tab.Rows.Count);
Program._messageLog.WriteLine(_msg.ToString());
int inx = 0;
int maxInx = tab.Rows.Count - 1;
int inxCol = 0;
int maxInxCol = tab.Columns.Count - 1;
for (inx = 0; inx <= maxInx; inx++)
{
_msg.Length = 0;
DataRow row = tab.Rows[inx];
for (inxCol = 0; inxCol <= maxInxCol; inxCol++)
{
DataColumn col = tab.Columns[inxCol];
_msg.Append(col.ColumnName);
_msg.Append(": ");
_msg.Append(row[inxCol].ToString());
if (inxCol < maxInxCol)
{
_msg.Append(", ");
}
}
Program._messageLog.WriteLine(_msg.ToString());
}
}
else
{
_msg.Length = 0;
_msg.Append("No rows in resultset");
Program._messageLog.WriteLine(_msg.ToString());
}
db.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("...RunResultsetToDataTableTest Finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
/// <summary>
/// Creates a new instance of the <see cref="ResultSetImpl"/> class
/// </summary>
public ResultSetImpl(SqlCeResultSet resultSet)
{
_resultSet = resultSet;
}
public void ExecuteResultSetUsingNullCommandThrows()
{
SqlCeDatabase db = (SqlCeDatabase)this.db;
using (SqlCeResultSet reader = db.ExecuteResultSet((DbCommand)null)) {}
}
public void Open(SqlCeConnection sqlConn)
{
_sqlCmd = sqlConn.CreateCommand();
_sqlCmd.CommandText = String.Concat("SELECT * FROM ", _tableName);
_resultSet = _sqlCmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
_record = _resultSet.CreateRecord();
}
public void SqlCeReadAfterUpdateTest()
{
SqlCeEngine LEngine = new SqlCeEngine(@"Data Source=TestDatabase.sdf");
if (!File.Exists("TestDatabase.sdf"))
{
LEngine.CreateDatabase();
}
using (SqlCeConnection LConnection = new SqlCeConnection("Data Source=TestDatabase.sdf"))
{
LConnection.Open();
using (SqlCeCommand LCommand = LConnection.CreateCommand())
{
LCommand.CommandText = "select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Test'";
if ((int)LCommand.ExecuteScalar() != 0)
{
LCommand.CommandText = "drop table Test";
LCommand.ExecuteNonQuery();
}
LCommand.CommandText = "create table Test ( ID int not null, Name nvarchar(20), constraint PK_Test primary key ( ID ) )";
LCommand.ExecuteNonQuery();
LCommand.CommandText = "insert into Test ( ID, Name ) values ( 1, 'Joe' )";
LCommand.ExecuteNonQuery();
}
using (SqlCeTransaction LTransaction = LConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
using (SqlCeCommand LCommand = LConnection.CreateCommand())
{
LCommand.CommandType = System.Data.CommandType.TableDirect;
LCommand.CommandText = "Test";
LCommand.IndexName = "PK_Test";
LCommand.SetRange(DbRangeOptions.Default, null, null);
using (SqlCeResultSet LResultSet = LCommand.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Sensitive | ResultSetOptions.Updatable))
{
if (!LResultSet.Read())
{
throw new Exception("Expected row");
}
if ((string)LResultSet[1] != "Joe")
{
throw new Exception("Expected Joe row");
}
LResultSet.SetValue(1, "Joes");
LResultSet.Update();
LResultSet.ReadFirst();
//if (!LResultSet.Read())
// throw new Exception("Expected row");
if ((string)LResultSet[1] != "Joes")
{
throw new Exception("Expected Joes row");
}
LResultSet.SetValue(1, "Joe");
LResultSet.Update();
}
}
LTransaction.Commit(CommitMode.Immediate);
}
catch
{
LTransaction.Rollback();
throw;
}
}
using (SqlCeTransaction LTransaction = LConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
}
}
}
private void button1_Click(object sender, RoutedEventArgs e)
{
int recordCount = 10000;
using (PopupForm p = new PopupForm())
{
p.X_NotifyStr = "Record Count";
if (p.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
int outValue;
if (!int.TryParse(p.X_Result, out outValue))
{
return;
}
recordCount = outValue;
}
else
{
return;
}
}
string testDb = DateTime.Now.Second.ToString() + "test.sdf";
string testTable = "testTable";
if (!App.MainEngineer.CreateDatabase(new LoginInfo_SSCE()
{
DbName = testDb
}))
{
return;
}
App.MainEngineer.Open(new CoreEA.LoginInfo.LoginInfo_SSCE()
{
DbName = testDb, Pwd = "", IsEncrypted = false
});
if (!App.MainEngineer.IsOpened)
{
throw new Exception("Can't Open");
}
//List<CreateTableArgs> argsList=new List<CreateTableArgs>();
//CreateTableArgs args=new CreateTableArgs();
//args.allowNulls = false;
//args.dataLength = 0;
//args.dataType="int";
//args.fieldName="id";
//args.isUnique = false;
//args.isPrimaryKey = false;
//argsList.Add(args);
BaseTableSchema tableSchame = new BaseTableSchema();
tableSchame.Columns.Add(new BaseColumnSchema()
{
ColumnName = "id", ColumnType = "int"
});
try
{
App.MainEngineer.CreateTable(tableSchame);
string sqlCmd = string.Empty;
SqlCeConnection conn = new SqlCeConnection(string.Format("Data source={0}", testDb));
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = conn;
conn.Open();
Stopwatch watch = new Stopwatch();
watch.Start();
for (int i = 0; i < recordCount; i++)
{
sqlCmd = string.Format("insert into {0} values ({1})", testTable, i);
cmd.CommandText = sqlCmd;
cmd.ExecuteNonQuery();
}
watch.Stop();
long sqlDirectTime = watch.ElapsedMilliseconds;
watch.Reset();
watch.Start();
cmd.CommandText = string.Format("INSERT INTO {0} VALUES (?)", testTable);
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Prepare();
for (int i = 0; i < recordCount; i++)
{
cmd.Parameters[0].Value = i;
cmd.ExecuteNonQuery();
}
watch.Stop();
long sqlParaTime = watch.ElapsedMilliseconds;
watch.Reset();
watch.Start();
cmd.CommandText = testTable;
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet st = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
SqlCeUpdatableRecord rec = st.CreateRecord();
for (int i = 0; i < recordCount; i++)
{
rec.SetInt32(0, i);
st.Insert(rec);
}
watch.Stop();
long sqlceResultSetTime = watch.ElapsedMilliseconds;
//watch.Start();
//cmd.CommandText = testTable;
//cmd.CommandType = CommandType.TableDirect;
//SqlCeResultSet st = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
//SqlCeUpdatableRecord rec =
//for (int i = 0; i < recordCount; i++)
//{
// rec.SetInt32(0, i);
// st.Insert(rec);
//}
//watch.Stop();
long sqlceUpdateResultSetTime = 100;// watch.ElapsedMilliseconds;
cmd.Dispose();
conn.Close();
MessageBox.Show(string.Format("Test Result is \r\nDirect sql command used {0} \r\nUse parameters used{1}\r\nUse SqlceResultSet used{2}\r\nUpdate Sqlce ResultSet{3}\r\n", sqlDirectTime, sqlParaTime, sqlceResultSetTime, sqlceUpdateResultSetTime));
}
catch (Exception ee)
{
ProcessException.DisplayErrors(ee);
}
}
private void FlushSamples(SqlCeResultSet resultSet)
{
//now to update the samples table
foreach(KeyValuePair<int, SortedList<int, int>> sampleKvp in m_samples)
{
if(sampleKvp.Value.Count == 0)
continue;
int threadOrdinal = resultSet.GetOrdinal("ThreadId");
int functionOrdinal = resultSet.GetOrdinal("FunctionId");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
foreach(KeyValuePair<int, int> threadKvp in sampleKvp.Value)
{
if(!resultSet.Seek(DbSeekOptions.FirstEqual, threadKvp.Key, sampleKvp.Key))
{
//doesn't exist in the table, we need to add it
var row = resultSet.CreateRecord();
row[threadOrdinal] = threadKvp.Key;
row[functionOrdinal] = sampleKvp.Key;
row[hitsOrdinal] = threadKvp.Value;
resultSet.Insert(row, DbInsertOptions.PositionOnInsertedRow);
}
else
{
resultSet.Read();
resultSet.SetValue(hitsOrdinal, (int) resultSet[hitsOrdinal] + threadKvp.Value);
resultSet.Update();
}
}
sampleKvp.Value.Clear();
}
}
private void CheckOrdinals(SqlCeResultSet results)
{
if (m_bookOrdinals.Count == 0)
{
for (int i = 0; i < results.FieldCount; i++)
{
m_bookOrdinals.Add(results.GetName(i), i);
}
}
}
private void CreateRecord(SqlCeResultSet resultSet, int threadId, int parentId, int childId, int hits)
{
//a lock is not needed
var row = resultSet.CreateRecord();
row["ThreadId"] = threadId;
row["ParentId"] = parentId;
row["ChildId"] = childId;
row["HitCount"] = hits;
resultSet.Insert(row, DbInsertOptions.PositionOnInsertedRow);
}
/// <summary>
/// Click Event handler for the Query and Bind button
/// </summary>
private void btnExecute_Click(object sender, EventArgs e)
{
//Clear the text in the filter textbox above the datagrid
tbCommand.Text = String.Empty;
// Disable the button till we are done quering and binding
btnExecute.Enabled = false;
try
{
// Dispose previous views bound to the currently active RS
if (null != view1)
{
((IDisposable)view1).Dispose();
}
if (null != view2)
{
((IDisposable)view2).Dispose();
}
// Dispose previous SqlCeCommand and previous SqlCeResultSet
if (null != this.command)
{
this.command.Dispose();
}
if (null != this.resultSet)
{
this.resultSet.Dispose();
}
//Creates a Command with the associated connection
this.command = this.connection.CreateCommand();
// Use the SqlCeResultSet if the "Use DataSet" menu item is not
// checked
if (false == this.menuItemUseDataSet.Checked)
{
// Queury the Orders table in the Northwind database
this.command.CommandText = "SELECT * FROM Orders";
ResultSetOptions options = ResultSetOptions.Scrollable | ResultSetOptions.Sensitive;
if (this.menuItemUpdatable.Checked)
{
options |= ResultSetOptions.Updatable;
}
this.resultSet = this.command.ExecuteResultSet(options);
this.dataGrid.DataSource = null;
// Bind the result set to the controls
this.BindData();
}
else
{
//Retrieve the columns we are interested in from the Orders table
//Note that we do not specify this in the SqlCeResultSet queury above
// because we demonstrate the use of the Ordinals property in the
// ResultSetView.
string query = @"SELECT [Customer ID], [Ship Name],[Ship City]
,[Ship Country] FROM Orders";
this.command.CommandText = query;
table = new DataTable("Orders");
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
SqlCeDataAdapter adapter = new SqlCeDataAdapter(this.command);
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
this.dataGrid.DataSource = null;
this.BindData();
}
btnExecute.Enabled = true;
}
catch (InvalidOperationException ex)
{
btnExecute.Enabled = true;
MessageBox.Show(String.Format(System.Globalization.CultureInfo.CurrentCulture,
"Exception while Performing Query/Bind: \n {0}", ex.ToString()));
}
catch (SqlCeException ex)
{
btnExecute.Enabled = true;
ShowErrors(ex);
}
}
