public void ExecuteResultSetWithBadCommandCallsInstrumentationFireCommandFailedEvent()
{
SqlCeDatabase db = (SqlCeDatabase)this.db;
int executeCount = 0;
int failedCount = 0;
DataInstrumentationProvider instrumentation = (DataInstrumentationProvider)db.GetInstrumentationEventProvider();
instrumentation.commandExecuted += delegate(object sender, CommandExecutedEventArgs e) { executeCount++; };
instrumentation.commandFailed += delegate(object sender, CommandFailedEventArgs e) { failedCount++; };
try
{
using (DbCommand command = db.GetSqlStringCommand("select * from junk"))
{
SqlCeResultSet reader = db.ExecuteResultSet(command);
reader.Close();
command.Connection.Close();
}
}
catch
{
}
Assert.AreEqual(0, executeCount);
Assert.AreEqual(1, failedCount);
}
public void ExecuteResultSetWithBadCommandCallsInstrumentationFireCommandFailedEvent()
{
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);
SqlCeDatabase ceDb = GetDatabase(mockProvider.Object);
try
{
using (DbCommand command = ceDb.GetSqlStringCommand("select * from junk"))
{
SqlCeResultSet reader = ceDb.ExecuteResultSet(command);
reader.Close();
}
}
catch { }
Assert.AreEqual(0, executeCount);
Assert.AreEqual(1, failedCount);
}
public void ShouldHaveCorrectRowsAffectedAfterInsertCommand()
{
int count = -1;
SqlCeResultSet reader = null;
DbCommand command = null;
try
{
SqlCeDatabase db = (SqlCeDatabase)this.db;
command = db.GetSqlStringCommand(insertString);
reader = db.ExecuteResultSet(command);
count = reader.RecordsAffected;
}
finally
{
if (reader != null)
{
reader.Close();
}
if (command != null)
{
command.Connection.Close();
command.Dispose();
}
string deleteString = "Delete from Region where RegionId = 99";
DbCommand cleanupCommand = this.db.GetSqlStringCommand(deleteString);
this.db.ExecuteNonQuery(cleanupCommand);
}
Assert.AreEqual(1, count);
}
/// <summary>
/// Copy Other Connection Table to SQLCE
/// So The DestConn is should be SqlCeConnection
/// </summary>
/// <param name="srcConn"></param>
/// <param name="destConn">Must Sqlce Connection Due to SqlCeResultSet Object</param>
/// <param name="queryString"></param>
/// <param name="destTableName"></param>
public static void CopyTable(
DbConnection srcConn,
SqlCeConnection destConn,
string queryString,
string destTableName)
{
IDbCommand srcCommand = srcConn.CreateCommand();
srcCommand.CommandText = queryString;
if (destConn.State == ConnectionState.Closed)
{
destConn.Open();
}
SqlCeCommand destCommand = destConn.CreateCommand();
destCommand.CommandType = CommandType.TableDirect; //基于表的访问，性能更好
destCommand.CommandText = destTableName;
IDataReader srcReader = srcCommand.ExecuteReader();
SqlCeResultSet resultSet = destCommand.ExecuteResultSet(
ResultSetOptions.Sensitive | //检测对数据源所做的更改
ResultSetOptions.Scrollable | //可以向前或向后滚动
ResultSetOptions.Updatable); //允许更新数据
object[] values;
SqlCeUpdatableRecord record;
//这个方法由于前面ADO读取的列信息已经被排序，所以和数据库中真实的RECORD排序冲突。
//所以使用下面的新的方法，使用列名寻找
//while (srcReader.Read())
//{
// // 从源数据库表读取记录
// values = new object[srcReader.FieldCount];
// srcReader.GetValues(values);
// // 把记录写入到目标数据库表
// record = resultSet.CreateRecord() ;
// record.SetValues(values);
// resultSet.Insert(record);
//}
while (srcReader.Read())
{
values = new object[srcReader.FieldCount];
srcReader.GetValues(values);
record = resultSet.CreateRecord();
for (int i = 0; i < srcReader.FieldCount; i++)
{
record[srcReader.GetName(i)] = srcReader.GetValue(i);
}
resultSet.Insert(record);
}
srcReader.Close();
resultSet.Close();
}
/// <summary>
/// Adds the study.
/// </summary>
/// <param name="receive">The receive.</param>
/// <param name="PatientId">The patient id.</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 AddStudy(DateTime receive, string PatientId, string ConnectionString, string AETitle, DicomDataSet dataset)
{
string studyInstance = dataset.GetValue <string>(DicomTag.StudyInstanceUID, string.Empty);
if (string.IsNullOrEmpty(studyInstance))
{
throw new ArgumentException("Missing dicom tag", "Study Instance UID");
}
_newStudy = false;
if (!RecordExists(ConnectionString, "Studies", "StudyInstanceUID = '" + studyInstance + "'"))
{
DateTime? sd = dataset.GetValue <DateTime?>(DicomTag.StudyDate, null, GetDate);
DateTime? st = dataset.GetValue <DateTime?>(DicomTag.StudyTime, null, GetDate);
SqlCeResultSet rs = SqlCeHelper.ExecuteResultSet(ConnectionString, "Studies");
SqlCeUpdatableRecord study = rs.CreateRecord();
study.SetValue(0, studyInstance);
string sDate = GetDateString(sd, st).Trim();
if (sDate.Length > 0)
{
study.SetValue(1, sDate);
}
study.SetValue(2, dataset.GetValue <string>(DicomTag.AccessionNumber, string.Empty));
study.SetValue(3, dataset.GetValue <string>(DicomTag.StudyID, string.Empty));
study.SetValue(4, dataset.GetValue <string>(DicomTag.ReferringPhysicianName, string.Empty));
study.SetValue(5, dataset.GetValue <string>(DicomTag.StudyDescription, string.Empty));
study.SetValue(6, dataset.GetValue <string>(DicomTag.AdmittingDiagnosesDescription, string.Empty));
string age = dataset.GetValue <string>(DicomTag.PatientAge, string.Empty);
if (age != string.Empty && age.Length > 0)
{
age = age.Substring(0, 4);
}
study.SetValue(7, age);
study.SetValue(8, dataset.GetValue <double>(DicomTag.PatientSize, 0));
study.SetValue(9, dataset.GetValue <double>(DicomTag.PatientWeight, 0));
study.SetValue(10, dataset.GetValue <string>(DicomTag.Occupation, string.Empty));
study.SetValue(11, dataset.GetValue <string>(DicomTag.AdditionalPatientHistory, string.Empty));
study.SetValue(12, dataset.GetValue <string>(DicomTag.InterpretationAuthor, string.Empty));
study.SetValue(13, PatientId);
sDate = GetDateString(receive, receive).Trim();
if (sDate.Length > 0)
{
study.SetValue(14, sDate);
}
study.SetValue(15, AETitle);
rs.Insert(study);
rs.Close();
_newStudy = true;
}
return(studyInstance);
}
private IEnumerable <T> Map(SqlCeResultSet resultSet)
{
var list = new List <T>();
while (resultSet.Read())
{
list.Add(Map((SqlCeDataReader)resultSet));
}
resultSet.Close();
return(list);
}
public void ExecuteResultSet_ShouldNotCloseConnection()
{
SqlCeDatabase db = (SqlCeDatabase)this.db;
using (DbCommand command = db.GetSqlStringCommand(queryString))
{
SqlCeResultSet reader = db.ExecuteResultSet(command);
reader.Close();
Assert.AreEqual(ConnectionState.Open, command.Connection.State);
command.Connection.Close();
}
}
public static void CopyTable(
IDbConnection srcConnection,
SqlCeConnection destConnection,
string queryString,
string destTableName)
{
IDbCommand srcCommand = srcConnection.CreateCommand();
srcCommand.CommandText = queryString;
SqlCeCommand destCommand = destConnection.CreateCommand();
destCommand.CommandType = CommandType.Text;
destCommand.CommandText = "delete from " + destTableName;
destCommand.ExecuteNonQuery();
destCommand.CommandType = CommandType.TableDirect; //基于表的访问，性能更好
destCommand.CommandText = destTableName;
try
{
IDataReader srcReader = srcCommand.ExecuteReader();
SqlCeResultSet resultSet = destCommand.ExecuteResultSet(
ResultSetOptions.Sensitive | //检测对数据源所做的更改
ResultSetOptions.Scrollable | //可以向前或向后滚动
ResultSetOptions.Updatable); //允许更新数据
object[] values;
SqlCeUpdatableRecord record;
while (srcReader.Read())
{
// 从源数据库表读取记录
values = new object[srcReader.FieldCount];
srcReader.GetValues(values);
// 把记录写入到目标数据库表
record = resultSet.CreateRecord();
record.SetValues(values);
resultSet.Insert(record);
}
srcReader.Close();
resultSet.Close();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
}
public void ReadCompactDirect()
{
var con = new SqlCeConnection(@"Data Source=Database1.sdf");
con.Open();
var cmd = new SqlCeCommand("SELECT s1,s2 FROM TablSpecial", con);
SqlCeResultSet rec = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
while (rec.Read())
{
MessageBox.Show(rec["s1"] + @" " + rec["s2"]);
}
rec.Close();
con.Close();
}
private static DicomCommandStatusType AddImage(DateTime receive, string sopInstance, string StudyInstanceUid, string SeriesInstanceUid,
string ConnectionString, string AETitle, DicomDataSet dataset, string ImageDirectory)
{
if (string.IsNullOrEmpty(sopInstance))
{
throw new ArgumentException("Missing dicom tag", "SOP Instance UID");
}
if (!RecordExists(ConnectionString, "Images", "SOPInstanceUID = '" + sopInstance + "'"))
{
string fileName = ImageDirectory + sopInstance + ".dcm";
SqlCeResultSet rs = SqlCeHelper.ExecuteResultSet(ConnectionString, "Images");
SqlCeUpdatableRecord image = rs.CreateRecord();
image.SetValue(0, sopInstance);
image.SetValue(1, SeriesInstanceUid);
image.SetValue(2, StudyInstanceUid);
if (HasValue(dataset, DicomTag.InstanceNumber))
{
image.SetValue(3, dataset.GetValue <int>(DicomTag.InstanceNumber, 0));
}
image.SetValue(4, fileName);
image.SetValue(5, dataset.GetValue <string>(DicomTag.TransferSyntaxUID, DicomUidType.ImplicitVRLittleEndian));
image.SetValue(6, dataset.GetValue <string>(DicomTag.SOPClassUID, string.Empty));
image.SetValue(7, dataset.GetValue <string>(DicomTag.StationName, string.Empty));
image.SetValue(8, GetDateString(DateTime.Now, DateTime.Now));
image.SetValue(9, AETitle);
rs.Insert(image);
rs.Close();
//
// store the file
//
if (!Directory.Exists(ImageDirectory))
{
Directory.CreateDirectory(ImageDirectory);
}
dataset.Save(fileName, DicomDataSetSaveFlags.None);
}
else
{
return(DicomCommandStatusType.DuplicateInstance);
}
return(DicomCommandStatusType.Success);
}
private void CountTheNumberOfClientFilms()
{
string queryString =
"SELECT FilmId FROM BusyFilmCopy WHERE ClientId='" + ClientId + "';";
cmd.CommandText = queryString;
cmd.Connection = connection;
int i = 0;
try
{
connection.Open();
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
while (rs.Read())
{
i++;
}
}
else
{
i = 0;
}
rs.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (i >= 0 && i < 5)
{
comboBox2.Enabled = true;
}
else
{
NextButton.Enabled = false;
comboBox2.Enabled = false;
MessageBox.Show("Извините, кол-во фильмов на руках превышено. Верните хотя бы один фильм.");
}
}
/// <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");
}
_newSeries = false;
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));
seriesDate = GetDateString(receive, receive);
if (seriesDate.Length > 0)
{
series.SetValue(6, seriesDate);
}
series.SetValue(7, AETitle);
series.SetValue(8, StudyInstanceUid);
rs.Insert(series);
rs.Close();
_newSeries = true;
}
return(seriesInstance);
}
private void RetrivingHistoryInformation(ViewClientCard clientCard, int ClientId)
{
string queryString = "SELECT InventNum, FilmName, DistrDate, ReturnDate, CountOfDay, FilmPrice, Penny, TotalAmount FROM ClientHistory WHERE ClientId='" + ClientId + "';";
cmd.CommandText = queryString;
cmd.Connection = connection;
try
{
connection.Open();
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
while (rs.Read())
{
string[] rowI = new string[] { (rs["InventNum"]).ToString(), (rs["FilmName"]).ToString(),
(rs["DistrDate"]).ToString(), (rs["ReturnDate"]).ToString(), (rs["CountOfDay"]).ToString(),
(rs["FilmPrice"]).ToString(), (rs["Penny"]).ToString(), (rs["TotalAmount"]).ToString() };
clientCard.ClientDataGridView.Rows.Add(rowI);
for (int i = 0; i < clientCard.ClientDataGridView.RowCount; i++)
{
DataGridViewRow dataGridViewRow = clientCard.ClientDataGridView.Rows[i];
if (dataGridViewRow.IsNewRow)
{
continue;
}
if (dataGridViewRow.Cells["Дата возврата"].Value.ToString() != "-")
{
dataGridViewRow.DefaultCellStyle.BackColor = Color.BlueViolet;
}
}
}
}
rs.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void CanExecuteResultSetWithCommand()
{
SqlCeDatabase db = (SqlCeDatabase)this.db;
using (DbCommand command = db.GetSqlStringCommand(queryString))
{
SqlCeResultSet reader = db.ExecuteResultSet(command);
string accumulator = "";
while (reader.Read())
{
accumulator += ((string)reader["RegionDescription"]).Trim();
}
reader.Close();
command.Connection.Close();
Assert.AreEqual("EasternWesternNorthernSouthern", accumulator);
}
}
// возвращает одну запись справочника
public Ean GetEan(string barcode)
{
Ean ean = null;
string commandText = @" SELECT top(1) s.artcode, s.ean13, s.names, s.koef, p.qty, s.nds, s.Manufacturer
FROM sprean s
LEFT OUTER JOIN pereuchet p ON s.artcode = p.artcode
WHERE s.ean13 = @barcode
ORDER BY (CASE WHEN p.qty IS NULL THEN 0 ELSE p.qty END) DESC, s.artcode desc ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = connect.CreateCommand())
{
command.CommandText = commandText;
command.Parameters.Add("barcode", SqlDbType.NVarChar).Value = barcode;
using (SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
try
{
if (res.HasRows)
{
res.ReadFirst();
{
ean = new Ean();
ean.ArtCode = res.GetInt32(0);
ean.Ean13 = res.GetString(1);
ean.Name = res.GetString(2);
ean.Koef = res.GetInt32(3);
ean.ControlQty = res.IsDBNull(4) ? 0 : res.GetInt32(4);
ean.Nds = res.GetInt32(5);
ean.Manufacturer = res.GetString(6);
}
}
}
finally
{
res.Close();
}
}
}
}
return(ean);
}
private void CheckAge(int FilmId, int ClientId)
{
string queryString =
"SELECT Film.[Целевая аудитория 18+], ClientPersonalCard.Возраст FROM Film, ClientPersonalCard WHERE Film.Id='" + FilmId + "' AND ClientPersonalCard.Id='" + ClientId + "'";
cmd.CommandText = queryString;
cmd.Connection = connection;
string isFilmFor18 = "";
int clientAge = 0;
try
{
connection.Open();
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
while (rs.Read())
{
isFilmFor18 = rs[0].ToString();
clientAge = Convert.ToInt16(rs[1]);
}
rs.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (isFilmFor18 == "Нет")
{
NextButton.Enabled = true;
}
else if (isFilmFor18 == "Да" && clientAge < 18)
{
NextButton.Enabled = false;
MessageBox.Show("Извините, но ваш возраст не соответствует возрастному ограничению фильма.");
}
else if (isFilmFor18 == "Да" && clientAge >= 18)
{
NextButton.Enabled = true;
}
}
private void FillComboBoxOfPreviousPartsOfFilm()
{
comboBox1.Items.Clear();
string queryString =
"SELECT Название + ' ' + Год, Id FROM Film";
cmd.CommandText = queryString;
cmd.Connection = connection;
try
{
connection.Open();
SqlCeResultSet reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
string FilmName = "";
int FilmId = 0;
while (reader.Read())
{
FilmName = (reader[0]).ToString();
FilmId = Convert.ToInt16(reader[1]);
String[] substrings = FilmName.Split(' ');
FilmName = "";
for (int i = 0; i < substrings.Length; i++)
{
if (i == substrings.Length - 1)
{
FilmName += "(" + substrings[i] + ")";
break;
}
FilmName += substrings[i] + " ";
}
comboBox1.Items.Add(FilmName + " | " + "FilmId=" + FilmId);
}
reader.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void MegaInsertCompactDirect()
{
var con = new SqlCeConnection(@"Data Source=Database1.sdf");
con.Open();
var cmd = new SqlCeCommand("SELECT s1,s2 FROM TablSpecial", con);
SqlCeResultSet rec = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
DateTime d = DateTime.Now;
for (int i = 1; i <= 10000; ++i)
{
var r = rec.CreateRecord();
r["s1"] = "new";
r["s2"] = "n";
rec.Insert(r);
}
MessageBox.Show(DateTime.Now.Subtract(d).ToString());
rec.Close();
con.Close();
}
private bool DontDoubleCopy(int FilmId, int ClientId)
{
string queryString =
"SELECT FilmId, ClientId FROM BusyFilmCopy WHERE FilmId='" + FilmId + "' AND ClientId='" + ClientId + "'";
cmd.CommandText = queryString;
cmd.Connection = connection;
int count = 0;
try
{
connection.Open();
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
count++;
}
rs.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (count != 0)
{
NextButton.Enabled = false;
MessageBox.Show("Извините, у вас уже есть копия данного фильма.");
return(false);
}
else
{
NextButton.Enabled = true;
return(true);
}
}
public void CanExecuteQueryThroughDataReaderUsingTransaction()
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbCommand command = db.GetSqlStringCommand(insertString))
{
using (var 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();
}
}
}
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);
}
}
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();
}
// Permite realizar la integración de datos de a partir de un dataset serializado
// El dataset debe contener tablas con nombres y campos iguales a los creados en la base de
// datos
public bool IntegrarDatos(string sSerializedData, bool bUpdateCurrentRows, IEstadoCarga Estado)
{
StringReader sr = new StringReader(sSerializedData);
string sLine = null;
string[] sFields = null;
string[] sFieldsTypes = null;
string[] sValues = null;
SqlCeResultSet rs = null;
SqlCeUpdatableRecord record = null;
int I = 0;
int J = 0;
int nIndex = 0;
int nTableCount = 0;
int nRowCount = 0;
int nTotalRowCount = 0;
int nTables = 0;
int nRows = 0;
int nTotalRows = 0;
int nProgresoTabla = 0;
int nProgresoTotal = 0;
DataTable dtNucleo = null;
DataRow row = null;
object FieldValue = null;
try
{
// Se lee la liena con el número de tablas serializadas y el numero total de filas a procesar
sLine = sr.ReadLine();
nTableCount = System.Convert.ToInt32(sLine.Substring(12));
sLine = sr.ReadLine();
nTotalRowCount = System.Convert.ToInt32(sLine.Substring(15));
nProgresoTotal = 0;
nTables = 0;
nTotalRows = 0;
this.OpenConnection();
while (!Estado.Cancelado)
{
// Se obtiene el nombre y cantidad de registros de cada tabla serializada
string sTableName = null;
sLine = sr.ReadLine();
if (sLine == null)
{
break;
}
sTableName = sLine.Substring(7);
sLine = sr.ReadLine();
nRowCount = System.Convert.ToInt32(sLine.Substring(10));
if (nRowCount > 0)
{
nProgresoTabla = 0;
nRows = 0;
Estado.IniciarTabla(sTableName);
// Se revisa si es una tabla del nucleo y se actualiza
// Revisar esto
dtNucleo = null;
if (bUpdateCurrentRows)
{
// Se filtra la información del indice de llave primario, para la busqueda de
// de las filas actuales
m_dvPK.RowFilter = "TABLE_NAME = '" + sTableName + "'";
}
else
{
// Si es una tabla del nucleo si eliminan las filas actuales
if (dtNucleo != null)
{
dtNucleo.Rows.Clear();
}
}
// Se obtiene el objeto ResultSet por medio del cual se hará la actualización
// especificando el indice de llave primaria de la tabla
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = (SqlCeConnection)this.Connection;
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = sTableName;
if (bUpdateCurrentRows)
{
cmd.IndexName = System.Convert.ToString(m_dvPK[0]["CONSTRAINT_NAME"]);
rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Sensitive | ResultSetOptions.Scrollable);
}
else
{
rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
}
// se obtienen los nombres de los campos
sLine = sr.ReadLine();
sFields = sLine.Split('|');
// se obtienen los tipos de datos de las columnas
sLine = sr.ReadLine();
sFieldsTypes = sLine.Split('|');
// Se procesa cada fila que venga serializada en la cadena
sLine = sr.ReadLine();
bool bInsertRecord = false;
while ((sLine != null) & (!Estado.Cancelado))
{
if (sLine.Trim() == string.Empty)
{
break;
}
// Se obtienen los valores que vienen en el registro
sValues = sLine.Split('|');
// Se obtienen los valores de llave primaria del registro
// Se crea la matriz de objetos para guardar los valores de la llave primaria de cada registro
bInsertRecord = true;
if (bUpdateCurrentRows)
{
// Se obtiene la llave primaria del registro
object[] RecordKey = new object[m_dvPK.Count];
for (I = 0; I < m_dvPK.Count; I++)
{
for (J = 0; J < sFields.GetUpperBound(0); J++)
{
if (System.Convert.ToString(m_dvPK[I]["COLUMN_NAME"]).ToUpper() == sFields[J])
{
RecordKey[I] = GetColumnValue(sFieldsTypes[J], sValues[J]);
}
}
}
// se busca el registro actual y luego se actualizan los datos
// si no se encuentra se inserta un nuevo registro
if (rs.Seek(DbSeekOptions.FirstEqual, RecordKey))
{
bInsertRecord = false;
// Se obtiene la fila a modificar
rs.Read();
if (dtNucleo != null)
{
row = dtNucleo.Rows.Find(RecordKey);
}
// Se actualizan los valores de cada columna en el registro en la base de datos y si
// se esta procesando una tabla del nucleo tambien se actualiza en memoria
if (dtNucleo != null && row != null)
{
for (I = 0; I < sFields.GetUpperBound(0); I++)
{
try
{
nIndex = rs.GetOrdinal(sFields[I]);
FieldValue = GetColumnValue(rs.GetFieldType(nIndex).ToString(), sValues[I]);
rs.SetValue(nIndex, FieldValue);
nIndex = row.Table.Columns.IndexOf(sFields[I]);
if (nIndex >= 0)
{
row[nIndex] = FieldValue;
}
}
catch (Exception ex)
{
throw new InvalidOperationException("Field: " + sFields[I] + "\r\n" + "Type: " + rs.GetFieldType(nIndex).ToString() + "\r\n" + "Value: " + sValues[I] + "\r\n" + ex.Message);
}
}
}
else
{
for (I = 0; I < sFields.GetUpperBound(0); I++)
{
try
{
nIndex = rs.GetOrdinal(sFields[I]);
FieldValue = GetColumnValue(rs.GetFieldType(nIndex).ToString(), sValues[I]);
rs.SetValue(nIndex, FieldValue);
}
catch (Exception ex)
{
throw new InvalidOperationException("Field: " + sFields[I] + "\r\n" + "Type: " + rs.GetFieldType(nIndex).ToString() + "\r\n" + "Value: " + sValues[I] + "\r\n" + ex.Message);
}
}
}
rs.Update();
}
}
if (bInsertRecord)
{
// Se crea el nuevo registro
record = rs.CreateRecord();
if (dtNucleo != null)
{
row = dtNucleo.NewRow();
}
else
{
row = null;
}
// Se actualizan los valores de cada columna en el registro
if (dtNucleo != null && row != null)
{
for (I = 0; I < sFields.GetUpperBound(0); I++)
{
try
{
nIndex = rs.GetOrdinal(sFields[I]);
FieldValue = GetColumnValue(rs.GetFieldType(nIndex).ToString(), sValues[I]);
record.SetValue(nIndex, FieldValue);
nIndex = row.Table.Columns.IndexOf(sFields[I]);
if (nIndex >= 0)
{
row[nIndex] = FieldValue;
}
}
catch (Exception ex)
{
throw new InvalidOperationException("Field: " + sFields[I] + "\r\n" + "Type: " + rs.GetFieldType(nIndex).ToString() + "\r\n" + "Value: " + sValues[I] + "\r\n" + ex.Message);
}
}
}
else
{
for (I = 0; I < sFields.GetUpperBound(0); I++)
{
try
{
nIndex = rs.GetOrdinal(sFields[I]);
FieldValue = GetColumnValue(rs.GetFieldType(nIndex).ToString(), sValues[I]);
record.SetValue(nIndex, FieldValue);
}
catch (Exception ex)
{
throw new InvalidOperationException("Field: " + sFields[I] + "\r\n" + "Type: " + rs.GetFieldType(nIndex).ToString() + "\r\n" + "Value: " + sValues[I] + "\r\n" + ex.Message);
}
}
}
// Se almacena el nuevo registro
try
{
rs.Insert(record, DbInsertOptions.KeepCurrentPosition);
if (dtNucleo != null && row != null)
{
dtNucleo.Rows.Add(row);
row.AcceptChanges();
}
}
catch (Exception ex)
{
object[] values = new object[rs.FieldCount + 1];
record.GetValues(values);
throw ex;
}
}
// Se registra el avance de la tabla
nRows += 1;
nTotalRows += 1;
if ((nRows % 100) == 0 || nRows == nRowCount)
{
Estado.ProgresoTabla = System.Convert.ToInt32((nRows * 100 / nRowCount));
Estado.ProgresoTotal = System.Convert.ToInt32(nTotalRows * 100 / nTotalRowCount);
}
// Se se lee el siguiente registro
sLine = sr.ReadLine();
}
rs.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rs != null)
{
if (!rs.IsClosed)
{
rs.Close();
rs = null;
}
}
this.CloseConnection();
sr.Close();
}
return(true);
}
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());
}
}
private void RetrivingInfoFromBusyFilmCopy(ViewClientCard clientCard, int clientId)
{
string queryString = "SELECT Film.Жанр, Film.Id, Film.Название, Film.[Тип носителя], BusyFilmCopy.CopyId, BusyFilmCopy.DistrDate FROM Film, BusyFilmCopy WHERE BusyFilmCopy.ClientId='" + clientId + "' AND BusyFilmCopy.FilmId=Film.Id;";
cmd.CommandText = queryString;
cmd.Connection = connection;
try
{
connection.Open();
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
string filmInvNum = "";
short flag = 0;
if (rs.HasRows)
{
while (rs.Read())
{
int FilmId = Convert.ToInt32(rs["Id"]);
int CopyId = Convert.ToInt32(rs["CopyId"]);
if (FilmId >= 1 && FilmId < 10)
{
if (CopyId >= 1 && CopyId < 10)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "00" + FilmId + "0" + CopyId;
}
if (CopyId >= 10 && CopyId < 100)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "00" + FilmId + "" + CopyId;
}
}
else if (FilmId >= 10 && FilmId < 100)
{
if (CopyId >= 1 && CopyId < 10)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "0" + FilmId + "0" + CopyId;
}
if (CopyId >= 10 && CopyId < 100)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "0" + FilmId + "" + CopyId;
}
}
else if (FilmId >= 100 && FilmId < 1000)
{
if (CopyId >= 1 && CopyId < 10)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "" + FilmId + "0" + CopyId;
}
if (CopyId >= 10 && CopyId < 100)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "" + FilmId + "" + CopyId;
}
}
string filmPrice = new Film((rs["Тип носителя"]).ToString()).RentalPrice.ToString();
DateTime distributionDate = Convert.ToDateTime(rs["DistrDate"]);
//DateTime returnedDate = DateTime.Now;
//Convert.ToDateTime(rs["ReturnDate"]);
int daysCount = Rental.GetExpireDays(distributionDate, DateTime.Now);
if (daysCount != 0)
{
flag++;
clientCard.ClientDataGridView.Rows.Add(new string[] { filmInvNum, (rs["Название"]).ToString(), distributionDate.ToShortDateString(), "-", daysCount.ToString(), filmPrice, Rental.CountOfPenny(distributionDate, DateTime.Now).ToString(), Rental.GetTotalRentalAmount(filmPrice, distributionDate, DateTime.Now).ToString() });
}
else
{
clientCard.ClientDataGridView.Rows.Add(new string[] { filmInvNum, (rs["Название"]).ToString(), distributionDate.ToShortDateString(), "-", daysCount.ToString(), filmPrice, "-", Rental.GetTotalRentalAmount(filmPrice, distributionDate, DateTime.Now).ToString() });
}
if (flag != 0)
{
clientCard.DebtTextBox.Text = "Да";
}
else
{
clientCard.DebtTextBox.Text = "Нет";
}
}
}
rs.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void FillOneRowInGrid()
{
string queryString = "SELECT TOP 1 Film.Жанр, Film.Id, Film.Название, Film.[Тип носителя], BusyFilmCopy.CopyId, BusyFilmCopy.DistrDate FROM Film, BusyFilmCopy WHERE BusyFilmCopy.ClientId IS NULL AND BusyFilmCopy.FilmId=Film.Id AND BusyFilmCopy.FilmId='" + FilmId + "';";
cmd.CommandText = queryString;
cmd.Connection = connection;
try
{
connection.Open();
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
string filmInvNum = "";
if (rs.HasRows)
{
while (rs.Read())
{
int FilmId = Convert.ToInt32(rs["Id"]);
int CopyId = Convert.ToInt32(rs["CopyId"]);
if (FilmId >= 1 && FilmId < 10)
{
if (CopyId >= 1 && CopyId < 10)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "00" + FilmId + "0" + CopyId;
}
if (CopyId >= 10 && CopyId < 100)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "00" + FilmId + "" + CopyId;
}
}
else if (FilmId >= 10 && FilmId < 100)
{
if (CopyId >= 1 && CopyId < 10)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "0" + FilmId + "0" + CopyId;
}
if (CopyId >= 10 && CopyId < 100)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "0" + FilmId + "" + CopyId;
}
}
else if (FilmId >= 100 && FilmId < 1000)
{
if (CopyId >= 1 && CopyId < 10)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "" + FilmId + "0" + CopyId;
}
if (CopyId >= 10 && CopyId < 100)
{
filmInvNum = (rs["Жанр"]).ToString().Substring(0, 2).ToUpper() + "" + FilmId + "" + CopyId;
}
}
string filmPrice = new Film((rs["Тип носителя"]).ToString()).RentalPrice.ToString();
DateTime distributionDate = DateTime.Now;
//Convert.ToDateTime(rs["DistrDate"]);
//DateTime returnedDate = DateTime.Now;
//Convert.ToDateTime(rs["ReturnDate"]);
int daysCount = 0;
//Rental.GetExpireDays(distributionDate, returnedDate);
ClientDataGridView.Rows.Add(new string[] { filmInvNum, (rs["Название"]).ToString(), distributionDate.ToShortDateString(), "-" /*returnedDate.ToShortDateString()*/, daysCount.ToString(), filmPrice, "-" /*Rental.Penny.ToString()*/, filmPrice /*Rental.totalRentalAmount(filmPrice, distributionDate, returnedDate).ToString()*/ });
this.DebtTextBox.Text = "Нет";
}
}
rs.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}