public bool deleteDocCheckProduct(string _DCode)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" delete from DocCheckProducts where DCode ='"+_DCode+"'");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
sb = new StringBuilder();
sb.Append(" delete from DocCheckProductDetails where DCode ='" + _DCode + "'");
string sqldelete;
sqldelete = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqldelete;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public bool IsExist()
{
try
{
using (var connection = new SqlCeConnection(ConnectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var command = connection.CreateCommand())
{
var statement = "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = @tableName";
command.CommandText = statement;
command.Parameters.AddWithValue("tableName", tableName);
var count = Convert.ToInt32(command.ExecuteScalar());
return count > 0;
}
}
}
}
catch (SqlCeLockTimeoutException ex)
{
Thread.Sleep(TimeSpan.FromMilliseconds(500));
return IsExist();
}
catch (Exception ex)
{
LogOrSendMailToAdmin(ex);
throw;
}
}
/// <summary>Usuwa z bazy danych rzędy, których wartości w wybranych kolumnach są równe podanym w parametrze.</summary>
/// <param name="where">
/// <para>Zawiera wartości, jakie znajdują się w usuwanych rekordach.</para>
/// <para>Wartości dla kolumn, dla których nie sprawdzamy wartości mają pozostać jako null.</para>
/// </param>
public void delete(WierszTabeli where)
{
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
SqlCeCommand delete = new SqlCeCommand("DELETE FROM " + where.NazwaTabeli + klauzulaWhere(where), connection);
connection.Open();
SqlCeTransaction traksakcja = connection.BeginTransaction();
try
{
delete.ExecuteNonQuery();
traksakcja.Commit();
}
catch (SqlCeException SQLe)
{
InvalidOperationException e = new InvalidOperationException("Wystąpił błąd przy próbie wykonania polecenia " + delete.CommandText, SQLe);
try
{
traksakcja.Rollback();
}
catch (InvalidOperationException IOe)
{
e = new InvalidOperationException("Wstąpił błąd przy próbie cofnięcia transakcji \npo błędzie polecenia " + delete.CommandText, IOe);
}
throw e;
}
}
}
static void CreateDatabase(string dbFileName, string connectionString) {
var dbFile = new FileInfo(dbFileName);
if (dbFile.Exists)
dbFile.Delete();
dbFile.Create().Close();
var script = File.ReadAllText("PocoDbSqlSchema.sql");
var splitScripts = script.Split(new[] {"GO"}, StringSplitOptions.RemoveEmptyEntries)
.Select(s => s.Replace("\n", "").Replace("\r", "")).Where(s => !String.IsNullOrWhiteSpace(s)).ToList();
using (var connection = new SqlCeConnection(connectionString)) {
connection.Open();
using (var trans = connection.BeginTransaction(IsolationLevel.Serializable)) {
foreach (var commandText in splitScripts) {
using (var command = connection.CreateCommand()) {
command.CommandText = commandText;
if (command.ExecuteNonQuery() == 0)
throw new InvalidOperationException("Failed to build db");
}
}
trans.Commit();
}
}
}
public void Commit()
{
var connectionString = repository.GetConnectionString();
using (var conn = new SqlCeConnection(connectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
foreach (var command in commands)
{
try
{
SQLCeHelper.ResetParameterNullValue(command);
command.Transaction = trans;
command.Connection = conn;
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new KoobooException(e.Message + "SQL:" + command.CommandText, e);
}
}
trans.Commit();
}
}
//Execute post content events
foreach (var action in postActions)
{
action();
}
Clear();
}
public void RunApplication(string[] args)
{
// example command line args:
// ClearCanvas.Dicom.DataStore.SetupApplication.Application "<TrunkPath>\Dicom\DataStore\AuxiliaryFiles\empty_viewer.sdf" "<TrunkPath>\Dicom\DataStore\AuxiliaryFiles\CreateTables.clearcanvas.dicom.datastore.ddl"
string databaseFile = args[0];
string scriptFile = args[1];
File.Delete(databaseFile);
string connectionString = String.Format("Data Source=\"{0}\"", databaseFile);
SqlCeEngine engine = new SqlCeEngine(connectionString);
engine.CreateDatabase();
engine.Dispose();
StreamReader reader = new StreamReader(scriptFile);
string scriptText = reader.ReadToEnd();
reader.Close();
SqlCeConnection connection = new SqlCeConnection(connectionString);
connection.Open();
SqlCeTransaction transaction = connection.BeginTransaction();
SqlCeCommand command = new SqlCeCommand();
command.Connection = connection;
command.CommandText = scriptText;
command.ExecuteNonQuery();
transaction.Commit();
connection.Close();
}
protected override void BeforeEachExample()
{
base.BeforeEachExample();
dbConnection = new SqlCeConnection(DatabaseIntegrationSetup.TestDBConnectionString);
dbConnection.Open();
transaction = dbConnection.BeginTransaction();
}
public void ReadTo(Action<SqlCeDataReader> readerAction)
{
try
{
using (var connection = new SqlCeConnection(ConnectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var command = connection.CreateCommand())
{
command.CommandText = _commandText;
SetParametersToCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
readerAction.Invoke(reader);
}
}
}
}
}
}
catch (SqlCeLockTimeoutException ex)
{
Thread.Sleep(TimeSpan.FromMilliseconds(500));
ReadTo(readerAction);
}
catch (Exception ex)
{
LogOrSendMailToAdmin(ex);
throw;
}
}
//private string sqlGetId = "SELECT @@IDENTITY";
public void insert(GimnasticarUcesnik gimnasticar, RezultatskoTakmicenje rezTak,
Sprava sprava, KvalifikacioniStatus newKvalStatus)
{
SqlCeConnection conn = new SqlCeConnection(ConfigurationParameters.ConnectionString);
SqlCeCommand cmd = new SqlCeCommand(insertSQL, conn);
cmd.Parameters.Add("@rez_takmicenje_id", SqlDbType.Int).Value = rezTak.Id;
cmd.Parameters.Add("@gimnasticar_id", SqlDbType.Int).Value = gimnasticar.Id;
cmd.Parameters.Add("@sprava", SqlDbType.TinyInt).Value = sprava;
cmd.Parameters.Add("@kval_status", SqlDbType.TinyInt).Value = newKvalStatus;
SqlCeTransaction tr = null;
try
{
conn.Open();
tr = conn.BeginTransaction();
cmd.Transaction = tr;
int recordsAffected = cmd.ExecuteNonQuery();
if (recordsAffected != 1)
{
throw new InfrastructureException(Strings.DatabaseAccessExceptionMessage);
}
/*SqlCeCommand cmd2 = new SqlCeCommand(sqlGetId, conn, tr);
object id = cmd2.ExecuteScalar();
if (!Convert.IsDBNull(id))
entity.Id = Convert.ToInt32(id);*/
tr.Commit(); // TODO: this can throw Exception and InvalidOperationException
}
catch (SqlCeException ex)
{
// in Open()
if (tr != null)
tr.Rollback(); // TODO: this can throw Exception and InvalidOperationException
throw new InfrastructureException(Strings.getFullDatabaseAccessExceptionMessage(ex), ex);
}
catch (InvalidOperationException ex)
{
// in ExecuteNonQuery(), ExecureScalar()
if (tr != null)
tr.Rollback();
throw new InfrastructureException(Strings.getFullDatabaseAccessExceptionMessage(ex), ex);
}
// za svaki slucaj
catch (Exception)
{
if (tr != null)
tr.Rollback();
throw;
}
finally
{
conn.Close();
}
}
public void ChangeCountAdd(Int32 aId_PlacementActivityLine, Int32 aCount)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
Int32 id_ActivityLine = -1;
using (SqlCeCommand sqlCommand = new SqlCeCommand("SELECT al.Id_ActivityLine FROM ActivityLine AS al " +
"WHERE al.Id_PlacementActivityLine = @Id_PlacementActivityLine AND al.BinCode = N''", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add(new SqlCeParameter("@Id_PlacementActivityLine", aId_PlacementActivityLine));
object obj = sqlCommand.ExecuteScalar();
if (obj != null)
{
id_ActivityLine = (Int32)obj;
}
}
if (id_ActivityLine == -1)
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO ActivityLine(Id_PlacementActivityLine, BinCode, ProcessedQty) " +
"VALUES (@Id_PlacementActivityLine, N'', @Count)", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add(new SqlCeParameter("@Id_PlacementActivityLine", aId_PlacementActivityLine));
sqlCommand.Parameters.Add(new SqlCeParameter("@Count", aCount));
sqlCommand.ExecuteNonQuery();
}
}
else
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("UPDATE ActivityLine SET ProcessedQty = ProcessedQty + @Count " +
"WHERE Id_ActivityLine = @Id_ActivityLine", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add(new SqlCeParameter("@Id_ActivityLine", id_ActivityLine));
sqlCommand.Parameters.Add(new SqlCeParameter("@Count", aCount));
sqlCommand.ExecuteNonQuery();
}
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}
public void addColumn()
{
if (!columnExists(table, column))
{
SqlCeConnection conn = new SqlCeConnection(ConfigurationParameters.ConnectionString);
string errMsg = "Neuspesna promena baze.";
SqlCeTransaction tr = null;
try
{
conn.Open();
tr = conn.BeginTransaction();
addColumnCommand.Connection = conn;
addColumnCommand.Transaction = tr;
addColumnCommand.ExecuteNonQuery();
updateColumnCommand.Connection = conn;
updateColumnCommand.Transaction = tr;
updateColumnCommand.ExecuteNonQuery();
tr.Commit();
}
catch (SqlCeException e)
{
// in Open()
if (tr != null)
tr.Rollback(); // TODO: this can throw Exception and InvalidOperationException
throw new InfrastructureException(errMsg, e);
}
catch (InvalidOperationException e)
{
// in ExecuteNonQuery(), ExecureScalar()
if (tr != null)
tr.Rollback();
throw new InfrastructureException(errMsg, e);
}
// za svaki slucaj
catch (Exception)
{
if (tr != null)
tr.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
static void Main(string[] args)
{
Debug.Assert(args.Length == 3);
if (args.Length != 3)
{
Usage();
return;
}
String database = args[0];
String password = args[1];
String batch = args[2];
using (StreamReader reader = File.OpenText(batch))
{
using (SqlCeConnection conn = new SqlCeConnection(String.Format(@"Data Source = {0}; Password = '******'", database, password)))
{
conn.Open();
using (SqlCeTransaction transaction = conn.BeginTransaction())
{
try
{
while (!reader.EndOfStream)
{
String commandText = Read(reader);
if (commandText.Length > 0)
{
Console.WriteLine(commandText);
using (SqlCeCommand command = conn.CreateCommand())
{
command.CommandText = commandText;
command.ExecuteNonQuery();
Console.WriteLine();
}
}
}
transaction.Commit();
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message);
transaction.Rollback();
}
}
conn.Close();
}
}
}
public static void Exec(string query, SqlCeConnection conn, Dictionary<string, object> parameters = null)
{
SqlCeTransaction trans = conn.BeginTransaction();
using (SqlCeCommand com = new SqlCeCommand(query, conn))
{
com.Transaction = trans;
if (parameters != null)
{
foreach (var element in parameters)
{
com.Parameters.AddWithValue(element.Key, element.Value);
}
}
com.ExecuteNonQuery();
trans.Commit(CommitMode.Immediate);
com.Dispose();
}
}
public void Execute()
{
try
{
using (var connection = new SqlCeConnection(ConnectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var command = connection.CreateCommand())
{
command.CommandText = _commandText;
SetParametersToCommand(command);
try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
}
catch (SqlCeLockTimeoutException ex)
{
Thread.Sleep(TimeSpan.FromMilliseconds(500));
Execute();
}
catch (Exception ex)
{
LogOrSendMailToAdmin(ex);
throw;
}
}
public static void DeleteFeed(int feedId)
{
try
{
using (SqlCeConnection conn = new SqlCeConnection(Properties.Settings.Default.nReaderConnectionString))
{
conn.Open();
using(SqlCeTransaction tran = conn.BeginTransaction())
{
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "delete from [Topic] where [FeedId] = @FeedId";
cmd.Parameters.Add(new SqlCeParameter("@FeedId", feedId));
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText = "delete from [Feed] where [Id] = @FeedId";
cmd.Parameters.Add(new SqlCeParameter("@FeedId", feedId));
cmd.ExecuteNonQuery();
tran.Commit();
}
conn.Close();
}
using (dsTableAdapters.FeedTableAdapter ta = new dsTableAdapters.FeedTableAdapter())
{
ta.Fill(feeds);
}
}
catch
{
var dr = feeds.GetErrors();
if (dr.Length > 0)
{
Notificator.NotifyMessage(dr[0].RowError);
}
throw;
}
}
public bool insert_phoneinfo(object[] obj)
{
string sql =
"INSERT INTO phone ([SERIAL_NUMBER], [NUMBER], [NUMBER_CHANGE_COUNT], [PAYMENT_COUNT], [LEFT_CHARGE], [ORDER]) " +
"VALUES (@SERIAL_NUMBER, @NUMBER, @NUMBER_CHANGE_COUNT, @PAYMENT_COUNT, @LEFT_CHARGE, @ORDER)";
//string sql =
// "INSERT INTO phone ([SERIAL_NUMBER], [NUMBER]) " +
//"VALUES (@SERIAL_NUMBER, @NUMBER)";
SqlCeConnection conn = new SqlCeConnection(connString);
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
conn.Open();
SqlCeTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd.Parameters.Add("@SERIAL_NUMBER", obj[0].ToString());
cmd.Parameters.Add("@NUMBER", obj[1].ToString());
cmd.Parameters.AddWithValue("@NUMBER_CHANGE_COUNT", 0);
cmd.Parameters.AddWithValue("@PAYMENT_COUNT", 0);
cmd.Parameters.AddWithValue("@LEFT_CHARGE", 0);
cmd.Parameters.AddWithValue("@ORDER", 0);
//cmd.Parameters.Add("@NUMBER_CHANGE_COUNT", (Int32)1);
//cmd.Parameters.Add("@PAYMENT_COUNT", (Int32)1);
//cmd.Parameters.Add("@LEFT_CHARGE", (Int32)1);
//cmd.Parameters.Add("@ORDER", (Int32)1);
try
{
cmd.ExecuteNonQuery();
tran.Commit();
}
finally
{
conn.Close();
}
return true;
}
/// <summary>
/// Delete the specified Supplier from the database
/// </summary>
/// <param name="supplierID"></param>
/// <returns></returns>
public int SupplierDelete(Supplier aSupplier)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
if (compactDatabaseType)
{
if (aSupplier.SupplierID != 0)
{
SqlCeTransaction transaction = null;
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
try
{
transaction = conn.BeginTransaction();
string commandText =
"UPDATE LICENSES SET _SUPPLIERID = 1 WHERE _SUPPLIERID = @nSupplierID";
SqlCeParameter[] spParams = new SqlCeParameter[1];
spParams[0] = new SqlCeParameter("@nSupplierID", aSupplier.SupplierID);
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
commandText =
"DELETE FROM SUPPLIERS WHERE _SUPPLIERID = @nSupplierID";
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddWithValue("@nSupplierID", aSupplier.SupplierID);
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (SqlCeException ex)
{
transaction.Rollback();
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (Exception ex)
{
transaction.Rollback();
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
}
}
}
else
{
AuditWizardDataAccess lAuditWizardDataAccess = new AuditWizardDataAccess();
lAuditWizardDataAccess.SupplierDelete(aSupplier);
}
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out");
}
return(0);
}
private string GetBulkCopyFailedData(DataTable table, SqlCeBulkCopyColumnMappingCollection columnMappings)
{
StringBuilder errorMessage = new StringBuilder("Bulk copy failures:" + Environment.NewLine);
SqlCeConnection connection = null;
SqlCeTransaction transaction = null;
SqlCeBulkCopy bulkCopy = null;
int failedRecords = 0;
try
{
connection = new SqlCeConnection(ConnectionStringKey.Value);
connection.Open();
transaction = connection.BeginTransaction();
SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
options = options |= SqlCeBulkCopyOptions.KeepNulls;
bulkCopy = new SqlCeBulkCopy(connection, options, transaction);
bulkCopy.DestinationTableName = DatabaseMap.TargetTableName;
foreach (SqlCeBulkCopyColumnMapping mapping in columnMappings)
{
bulkCopy.ColumnMappings.Add(mapping);
}
DataTable oneRecordTable = table.Clone();
foreach (DataRow row in table.Rows)
{
oneRecordTable.Rows.Clear();
oneRecordTable.ImportRow(row);
try
{
bulkCopy.WriteToServer(oneRecordTable);
}
catch (Exception ex)
{
failedRecords++;
DataRow faultyDataRow = oneRecordTable.Rows[0];
errorMessage.AppendFormat("Error: {0}{1}", ex.Message, Environment.NewLine);
errorMessage.AppendFormat("Row data: {0}", Environment.NewLine);
foreach (DataColumn column in oneRecordTable.Columns)
{
errorMessage.AppendFormat(
"\tColumn {0} - [{1}]{2}",
column.ColumnName,
faultyDataRow[column.ColumnName].ToString(),
Environment.NewLine);
}
if (failedRecords >= 100)
{
errorMessage.Append("Too many failed records, aborting fail record collection");
break;
}
}
}
}
catch (Exception ex)
{
errorMessage.Append("Unable to document SqlBulkCopy errors. See inner exceptions for details.");
errorMessage.Append(ex.ToString());
}
finally
{
if (transaction != null)
{
transaction.Rollback();
}
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
return(errorMessage.ToString());
}
private void button2_Click(object sender, EventArgs e)
{
listBox1.Items.Clear();
pen = new Pen(Color.Red, 1);
if (baglanti == 1)
{
try { serialPort1.Write("?"); } //Kayit Sayisi
catch (Exception)
{ baglanti_kesildi(); }
try { kayit_no_kucuk = serialPort1.ReadChar(); } //Kayit_no_kucuk
catch (Exception)
{ baglanti_kesildi(); }
try { kayit_no_buyuk = serialPort1.ReadChar(); } //Kayit_no_buyuk
catch (Exception)
{ baglanti_kesildi(); }
kayit_no_buyuk -= 32;
try { tur = serialPort1.ReadChar(); } //Tur
catch (Exception)
{ baglanti_kesildi(); }
if (tur == 1)
{
kayit_sayisi = 8192;
}
else
{
kayit_sayisi = (kayit_no_buyuk * 256 + kayit_no_kucuk) / 8;
}
}
if (baglanti == 1)
{
//yaz = File.AppendText("d:\\deneme.txt");
yaz = new StreamWriter(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\FMCWTSLOG.txt");
yaz.Write("Cihaz Numarası: ");
reportViewer1.LocalReport.Refresh();
try { serialPort1.Write("0"); } //Seri numarası
catch (Exception)
{ baglanti_kesildi(); }
text = "";
for (i = 0; i < 4; i++)
{
if (baglanti == 1)
{
try { rx_data = serialPort1.ReadChar(); }
catch (Exception)
{ baglanti_kesildi(); }
int_data[i] = rx_data + 48;
}
}
for (i = 0; i < 4; i++)
{
text += Convert.ToChar(int_data[i]);
}
yaz.WriteLine(text);
}
if (baglanti == 1)
{
//yaz = File.AppendText("d:\\deneme.txt");
yaz.Write("Kayıt Tarihi: ");
yaz.WriteLine(DateTime.Now);
yaz.WriteLine("");
yaz.WriteLine("Anlık Değerler: ");
yaz.Write("Giris Suyu İletkenliği: ");
try { serialPort1.Write("1"); } //Giris Suyu İletkenliği
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data1 = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
rx_data = rx_data * 256 + rx_data1;
giris_iletkenligi_textBox.Text = rx_data.ToString();
yaz.WriteLine(rx_data + "uS");
}
if (baglanti == 1)
{
yaz.Write("Üretim Suyu İletkenliği: ");
try { serialPort1.Write("2"); } //Üretim Suyu İletkenliği
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
uretim_iletkenligi_textBox.Text = rx_data.ToString();
yaz.WriteLine(rx_data + "uS");
}
if (baglanti == 1)
{
yaz.Write("Su Sıcaklığı: ");
try { serialPort1.Write("3"); } //Sıcaklık
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
su_sicakliği_textBox.Text = rx_data.ToString();
yaz.WriteLine(rx_data + "°C");
}
if (baglanti == 1)
{
yaz.Write("Çalışma Saati: ");
try { serialPort1.Write("4"); } //Çalışma Saati
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data1 = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
rx_data = rx_data * 256 + rx_data1;
calisma_saati_textBox.Text = rx_data.ToString();
calisma_saati = rx_data;
yaz.WriteLine(rx_data + " Saat");
}
if (baglanti == 1)
{
yaz.Write("Alarm Sayısı: ");
try { serialPort1.Write("5"); } //Alarm Sayisi
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data1 = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
rx_data = rx_data1 * 256 + rx_data;
alarm_sayisi_textBox.Text = rx_data.ToString();
alarm_sayisi = rx_data;
yaz.WriteLine(rx_data);
}
if (baglanti == 1)
{
yaz.Write("Dezenfeksiyon Sayısı: ");
try { serialPort1.Write("6"); }//Dezenfeksiyon Sayisi
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data1 = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
rx_data = rx_data1 * 256 + rx_data;
dezenfeksiyonsayisi_textBox.Text = rx_data.ToString();
dezenfeksiyon_sayisi = rx_data;
yaz.WriteLine(rx_data);
}
if (baglanti == 1)
{
yaz.WriteLine("");
yaz.WriteLine("Ayarlanan Değerler:");
yaz.Write("Üretim İletkenlik Limiti: ");
try { serialPort1.Write("7"); }//Üretim İletkenlik Limit Değeri
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
yaz.WriteLine(rx_data);
}
if (baglanti == 1)
{
yaz.Write("Giriş İletkenlik Limiti: ");
try { serialPort1.Write("8"); }//Giriş İletkenlik Limiti
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data1 = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
rx_data = rx_data * 256 + rx_data1;
yaz.WriteLine(rx_data);
}
if (baglanti == 1)
{
yaz.Write("Dezenfeksiyon Süresi:");
try { serialPort1.Write("9"); }//Dezenfeksiyon Süresi
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
yaz.WriteLine(rx_data);
}
if (baglanti == 1)
{
yaz.Write("Durulama Süresi:");
try { serialPort1.Write(":"); }//Durulama Süresi
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
yaz.WriteLine(rx_data);
}
if (baglanti == 1)
{
yaz.Write("Temizleme Süresi:");
try { serialPort1.Write(";"); }//Temizleme Süresi
catch (Exception)
{ baglanti_kesildi(); }
try { rx_data = serialPort1.ReadByte(); }
catch (Exception)
{ baglanti_kesildi(); }
yaz.WriteLine(rx_data);
}
if (baglanti == 1)
{
yaz.WriteLine("");
yaz.WriteLine("Alarm Kayıtları:");
try { serialPort1.Write("<"); }//Alarm Log
catch (Exception)
{ baglanti_kesildi(); }
if (alarm_sayisi > 0)
{
if (alarm_sayisi > 10)
{
alarm_sayisi = 10;
}
for (j = 0; j < alarm_sayisi; j++)
{
text = "";
for (i = 0; i < 16; i++)
{
if (baglanti == 1)
{
try { rx_data = serialPort1.ReadChar(); }
catch (Exception)
{ baglanti_kesildi(); }
int_data[i] = rx_data;
}
}
alarm_kodu = int_data[0];
for (i = 1; i < 15; i++)
{
text += Convert.ToChar(int_data[i]);
}
switch (alarm_kodu)
{
case 49:
{
text += " Yüksek İletkenlik Alarmı";
break;
}
case 50:
{
text += " Şebeke Basınç Alarmı";
break;
}
case 51:
{
text += " Seviye Sensörü Alarmı";
break;
}
case 52:
{
text += " Yüksek Sıcaklık Alarmı";
break;
}
case 53:
{
text += " Termik Alarmı";
break;
}
case 54:
{
text += " Yüksek Giriş İletkenlik Alarmı";
break;
}
}
listBox1.Items.Add(text);
yaz.WriteLine(text);
}
}
else
{
listBox1.Items.Add("Kayıt Yok");
}
}
if (baglanti == 1)
{
if (kayit_sayisi > 0)
{
try { serialPort1.Write("="); }//Tüm kayıtlar
catch (Exception)
{ baglanti_kesildi(); }
for (i = 0; i < kayit_sayisi; i++)
{
try { rx_data = serialPort1.ReadByte(); }//gun
catch (Exception)
{ baglanti_kesildi(); }
ay_string[i] = rx_data;
try { rx_data = serialPort1.ReadByte(); }//ay
catch (Exception)
{ baglanti_kesildi(); }
gun_string[i] = rx_data;
try { rx_data = serialPort1.ReadByte(); }//saat
catch (Exception)
{ baglanti_kesildi(); }
saat_string[i] = rx_data;
try { rx_data = serialPort1.ReadByte(); }//giris iletkenlik buyuk
catch (Exception)
{ baglanti_kesildi(); }
giris_iletkenlik[i] = rx_data;
try { rx_data = serialPort1.ReadByte(); }//giris iletkenlik kucuk
catch (Exception)
{ baglanti_kesildi(); }
giris_iletkenlik[i] = giris_iletkenlik[i] * 256 + rx_data;
try { rx_data = serialPort1.ReadByte(); }//uretim iletkenlik
catch (Exception)
{ baglanti_kesildi(); }
uretim_iletkenlik[i] = rx_data;
try { rx_data = serialPort1.ReadByte(); }//sicaklik
catch (Exception)
{ baglanti_kesildi(); }
sicaklik_string[i] = rx_data;
try { rx_data = serialPort1.ReadByte(); }//# dur biti
catch (Exception)
{ baglanti_kesildi(); }
if (rx_data != 35)
{
baglanti_kesildi();
}
}
yaz.Close();
yaz.Dispose();
/* try { serialPort1.Write(">"); }// Hand-shake protocol
* catch (Exception)
* { baglanti_kesildi(); }
* try { rx_data = serialPort1.ReadByte(); }//hand-shake return
* catch (Exception)
* { baglanti_kesildi(); }
*/
rx_data = 62;
if ((baglanti == 1) && (rx_data == 62))
{
SqlCeConnection myCon = new SqlCeConnection();
myCon.ConnectionString = @"Data Source = C:\Users\Erkan\Desktop\WindowsFormsApplication1\WindowsFormsApplication1\Database1.sdf;" +
"Password = q1w2e3r4.";
myCon.Open();
SqlCeTransaction myTrans;
myTrans = myCon.BeginTransaction();
try
{
string mySqlCode = "delete from reportTable ";
SqlCeCommand myCommand = new SqlCeCommand(mySqlCode, myCon);
for (i = 0; i < kayit_sayisi; i++)
{
verim[i] = ((giris_iletkenlik[i] - uretim_iletkenlik[i]) * 100 / (giris_iletkenlik[i] + 1));
mySqlCode = "insert into reportTable (girisIletkenlik,uretimIletkenlik,sicaklik,verim,saat,gun,ay ) values (" + giris_iletkenlik[i].ToString() + "," + uretim_iletkenlik[i].ToString() + "," + sicaklik_string[i].ToString() + "," + verim[i].ToString() + "," + saat_string[i].ToString() + "," + gun_string[i].ToString() + "," + ay_string[i].ToString() + ")";
myCommand = new SqlCeCommand(mySqlCode, myCon);
myCommand.Transaction = myTrans;
myCommand.ExecuteNonQuery();
}
myTrans.Commit();
this.reportTableTableAdapter.Fill(this.database1DataSet.reportTable);
reportViewer1.LocalReport.Refresh();
}
catch (Exception)
{
// myTrans.Rollback();
}
finally
{
myCon.Close();
this.reportTableTableAdapter.Fill(this.database1DataSet.reportTable);
reportViewer1.LocalReport.Refresh();
reportViewer1.RefreshReport();
}
}
}
else
{
yaz.WriteLine("Kayıt Yok");
yaz.Close();
yaz.Dispose();
}
}
else
{
calisma_saati_textBox.Clear();
giris_iletkenligi_textBox.Clear();
uretim_iletkenligi_textBox.Clear();
su_sicakliği_textBox.Clear();
alarm_sayisi_textBox.Clear();
dezenfeksiyonsayisi_textBox.Clear();
yaz.Close();
yaz.Dispose();
}
}
public override int DeleteProfiles(string[] usernames)
{
int rowsAffected = 0;
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand("DELETE FROM aspnet_Profile WHERE @UserId = UserId", conn))
{
cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
SqlCeTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
cmd.Parameters[0].Value = GetUserId(username);
rowsAffected += cmd.ExecuteNonQuery();
}
tran.Commit();
}
catch (SqlCeException)
{
throw;
}
finally
{
if (tran != null)
tran.Rollback();
}
}
}
return rowsAffected;
}
public bool saveDocCheckProductDetail(DocCheckProductDetail docCheckProductDetail)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("INSERT INTO DocCheckProductDetails(DCode,PCode,PName,PUnit,NumProduct,CreateDate)");
sb.Append(" VALUES (@DCode,@PCode,@PName,@PUnit,@NumProduct,@CreateDate)");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@DCode", SqlDbType.NVarChar).Value = docCheckProductDetail.DocCheckProduct.Code;
com.Parameters.Add("@PCode", SqlDbType.NVarChar).Value = docCheckProductDetail.Product.BarCode;
com.Parameters.Add("@PName", SqlDbType.NVarChar).Value = docCheckProductDetail.Product.Name;
com.Parameters.Add("@PUnit", SqlDbType.NVarChar).Value = docCheckProductDetail.Product.Unit;
com.Parameters.Add("@NumProduct", SqlDbType.NVarChar).Value = docCheckProductDetail.NumProduct;
com.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = DateTime.Now;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
//
// MembershipProvider.DeleteUser
//
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
int rowsAffected = 0;
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand("DELETE FROM [aspnet_UsersInRoles] " +
" WHERE UserId = @UserId AND @ApplicationId = @ApplicationId", conn))
{
cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = GetUserId(username);
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;
SqlCeTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM [aspnet_Membership] " +
" WHERE UserId = @UserId AND ApplicationId = @ApplicationId";
cmd.ExecuteNonQuery();
if (deleteAllRelatedData)
{
cmd.Parameters.Clear();
cmd.CommandText = "DELETE FROM aspnet_Profile WHERE UserId = @userId";
cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = GetUserId(username);
cmd.ExecuteNonQuery();
// reinstate the application id parameter
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;
}
cmd.CommandText = "DELETE FROM [aspnet_Users] " +
" WHERE UserId = @UserId AND ApplicationId = @ApplicationId";
rowsAffected = cmd.ExecuteNonQuery();
tran.Commit();
}
catch (SqlCeException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteUser");
throw new ProviderException(exceptionMessage);
}
else
{
throw;
}
}
catch (Exception)
{
if (tran != null)
tran.Rollback();
}
}
}
if (rowsAffected > 0)
return true;
return false;
}
private void calldatawithapi()
{
try
{
DateTime dt1 = DateTime.Now;
var request = (HttpWebRequest)WebRequest.Create("http://5cosmeda.homeunix.com:89/ApiAx/api/Ax/GetAsset");
request.Timeout = 40000;
using (var response = (HttpWebResponse)request.GetResponse())
{
var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
var Listitem = JsonConvert.DeserializeObject <RetItems>(responseString);
using (SqlCeConnection con = new SqlCeConnection(strcon))
{
int i = 0;
con.Open();
SqlCeTransaction tx = con.BeginTransaction();
try
{
if (Listitem.ListAssets.Count > 0)
{
string sqldelete = @"DELETE FROM MAS_ASSET";
SqlCeCommand cmdelete = new SqlCeCommand(sqldelete, con);
cmdelete.ExecuteNonQuery();
string ASSETID = string.Empty;
foreach (var item in Listitem.ListAssets)
{
string NAME = item.NAME.Replace("'", "");
string sql = @"INSERT MAS_ASSET (ASSETID,NAME) VALUES ('" + item.ASSETID + "','" + NAME + "');";
SqlCeCommand cm = new SqlCeCommand(sql, con);
cm.Transaction = tx;
cm.ExecuteNonQuery();
i++;
}
tx.Commit();
}
}
catch (Exception ex)
{
var x = i;
MessageBox.Show(ex.ToString() + "รับข้อมูลผิดพลาด");
}
con.Close();
}
}
//********************************************************
var requestdepart = (HttpWebRequest)WebRequest.Create("http://5cosmeda.homeunix.com:89/ApiAx/api/Ax/GetDepartmentAsset");
requestdepart.Timeout = 5000;
using (var responsedepart = (HttpWebResponse)requestdepart.GetResponse())
{
var responseString = new StreamReader(responsedepart.GetResponseStream()).ReadToEnd();
var Listitem = JsonConvert.DeserializeObject <RetItems>(responseString);
using (SqlCeConnection con = new SqlCeConnection(strcon))
{
con.Open();
try
{
if (Listitem.ListDepartmentAsset.Count > 0)
{
string sqldelete = @"DELETE FROM Department";
SqlCeCommand cmdelete = new SqlCeCommand(sqldelete, con);
cmdelete.ExecuteNonQuery();
string DPID = string.Empty;
foreach (var item in Listitem.ListDepartmentAsset)
{
string NAME = item.NAME.Replace("'", "");
string sql = @"INSERT Department (DPID,DPNAME) VALUES ('" + item.CODE + "','" + NAME + "');";
SqlCeCommand cm = new SqlCeCommand(sql, con);
cm.ExecuteNonQuery();
}
}
MessageBox.Show("รับข้อมูลสินทรัพย์เรียบร้อย");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString() + "รับข้อมูลผิดพลาด");
}
con.Close();
}
}
DateTime dt2 = DateTime.Now;
string timea = "ใช้เวลา " + timeDiff(dt1, dt2);
MessageBox.Show(timea);
}
catch (Exception ex)
{
MessageBox.Show("เกิดข้อผิดพลาด" + ex.Message);
}
}
// В версии 2 не было поддержки миграций (исправляем только для SQL Server и SQL Server CE).
private static void FixDatabase(IConnectionSettings connectionSettings, IMigrationMetadata migrationMetadata)
{
DbConnection connection = null;
DbTransaction transaction = null;
try
{
switch (connectionSettings.ProviderInvariantName)
{
case DataConfiguration.SqlServerProviderInvariantName:
connection = new SqlConnection(connectionSettings.ConnectionString);
break;
case DataConfiguration.SqlServerCompactProviderInvariantName:
connection = new SqlCeConnection(connectionSettings.ConnectionString);
break;
default:
return;
}
try
{
connection.Open();
}
catch (DbException exception)
{
Logger.Error(exception.Message, exception);
return; // Ожидаем что база данных не существует.
}
string migrationId;
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT MigrationId FROM __MigrationHistory ORDER BY MigrationId";
DbDataReader dataReader = null;
try
{
dataReader = command.ExecuteReader();
if (!dataReader.Read())
{
throw new InvalidOperationException("!dataReader.Read()");
}
migrationId = (string)dataReader["MigrationId"];
if (migrationId.Equals(migrationMetadata.Id))
{
return;
}
if (dataReader.NextResult())
{
throw new InvalidOperationException("reader.NextResult()");
}
}
catch (DbException exception)
{
Logger.Error(exception.Message, exception); // Ожидаем что таблицы не существует.
return;
}
finally
{
if (null != dataReader)
{
dataReader.Close();
dataReader.Dispose();
}
}
}
if (!migrationId.EndsWith("_InitialCreate", StringComparison.OrdinalIgnoreCase))
{
throw new InvalidOperationException("migrationId == " + migrationId);
}
transaction = connection.BeginTransaction();
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = "DELETE FROM __MigrationHistory WHERE MigrationId = @MigrationId";
AddParameterTo(command, "MigrationId", migrationId);
command.ExecuteNonQuery();
}
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText =
"INSERT INTO __MigrationHistory (MigrationId, ContextKey, Model, ProductVersion) VALUES (@MigrationId, @ContextKey, @Model, @ProductVersion)";
AddParameterTo(command, "MigrationId", migrationMetadata.Id);
AddParameterTo(command, "ContextKey", $"{typeof(Configuration).Namespace}.{nameof(Configuration)}");
AddParameterTo(command, "Model", Convert.FromBase64String(migrationMetadata.Target));
AddParameterTo(command, "ProductVersion", "6.1.3-40302");
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception exception)
{
Logger.Error(exception.Message, exception);
transaction?.Rollback();
throw;
}
finally
{
if (null != connection)
{
connection.Close();
connection.Dispose();
}
}
}
/// <summary>
/// Función que elimina un StoreCategoryEntity de la base de datos.
/// </summary>
/// <param name="storeCategory">StoreCategoryEntity a eliminar</param>
/// <param name="scope">Estructura interna para evitar problemas de referencia circular.</param>
/// <exception cref="ArgumentNullException">
/// Si <paramref name="storeCategory"/> no es un <c>StoreCategoryEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// Si una DbException ocurre cuando se accede a la base de datos
/// </exception>
public void Delete(StoreCategoryEntity storeCategory, Dictionary <string, IEntity> scope)
{
if (storeCategory == null)
{
throw new ArgumentException("The argument can't be null");
}
try
{
// Abrir una nueva conexión e inicializar una transacción si es necesario
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
// Carga la entidad para garantizar eliminar todos los datos antiguos.
storeCategory = this.Load(storeCategory.Id, true);
if (storeCategory == null)
{
throw new UtnEmallDataAccessException("Error al recuperar datos al intentar eliminar.");
}
// Crea un nuevo command para eliminar
string cmdText = "DELETE FROM [StoreCategory] WHERE idStoreCategory = @idStoreCategory";
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(cmdText, dbConnection, dbTransaction);
// Agrega los valores de los parametros
SqlCeParameter parameterID = dataAccess.GetNewDataParameter("@idStoreCategory", DbType.Int32);
parameterID.Value = storeCategory.Id;
sqlCommand.Parameters.Add(parameterID);
// Ejecuta el comando
sqlCommand.ExecuteNonQuery();
// Elimina los objetos relacionados
// Confirma la transacción si se inicio dentro de la función
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Eliminamos la entidad de la lista de entidades cargadas en memoria
inMemoryEntities.Remove(storeCategory.Id);
// Eliminamos la entidad del scope
if (scope != null)
{
string scopeKey = storeCategory.Id.ToString(NumberFormatInfo.InvariantInfo) + "StoreCategory";
scope.Remove(scopeKey);
}
}
catch (DbException dbException)
{
// Anula la transaccion
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Relanza una excepcion personalizada
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Cierra la conexión si fue abierta dentro de la Función
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
/// <summary>
/// Función que guarda un StoreCategoryEntity en la base de datos.
/// </summary>
/// <param name="storeCategory">StoreCategoryEntity a guardar</param>
/// <param name="scope">Estructura interna para evitar problemas con referencias circulares</param>
/// <exception cref="ArgumentNullException">
/// Si <paramref name="storeCategory"/> no es un <c>StoreCategoryEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// Si una DbException ocurre cuando se accede a la base de datos
/// </exception>
public void Save(StoreCategoryEntity storeCategory, Dictionary <string, IEntity> scope)
{
if (storeCategory == null)
{
throw new ArgumentException("The argument can't be null");
}
// Crear una clave unica para identificar el objeto dentro del scope interno
string scopeKey = storeCategory.Id.ToString(NumberFormatInfo.InvariantInfo) + "StoreCategory";
if (scope != null)
{
// Si se encuentra dentro del scope lo retornamos
if (scope.ContainsKey(scopeKey))
{
return;
}
}
else
{
// Crea un nuevo scope si este no fue enviado
scope = new Dictionary <string, IEntity>();
}
try
{
// Crea una nueva conexion y una nueva transaccion si no hay una a nivel superior
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
string commandName = "";
bool isUpdate = false;
// Verifica si se debe hacer una actualización o una inserción
if (storeCategory.IsNew || !DataAccessConnection.ExistsEntity(storeCategory.Id, "StoreCategory", "idStoreCategory", dbConnection, dbTransaction))
{
commandName = "INSERT INTO [StoreCategory] (idStoreCategory, IDCATEGORY, IDSTORE, [TIMESTAMP] ) VALUES( @idStoreCategory, @idCategory,@idStore, GETDATE()); ";
}
else
{
isUpdate = true;
commandName = "UPDATE [StoreCategory] SET idCategory = @idCategory, idStore = @idStore , timestamp=GETDATE() WHERE idStoreCategory = @idStoreCategory";
}
// Se crea un command
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(commandName, dbConnection, dbTransaction);
// Agregar los parametros del command .
SqlCeParameter parameter;
if (!isUpdate && storeCategory.Id == 0)
{
storeCategory.Id = DataAccessConnection.GetNextId("idStoreCategory", "StoreCategory", dbConnection, dbTransaction);
}
parameter = dataAccess.GetNewDataParameter("@idStoreCategory", DbType.Int32);
parameter.Value = storeCategory.Id;
sqlCommand.Parameters.Add(parameter);
FillSaveParameters(storeCategory, sqlCommand);
// Ejecutar el command
sqlCommand.ExecuteNonQuery();
scopeKey = storeCategory.Id.ToString(NumberFormatInfo.InvariantInfo) + "StoreCategory";
// Agregar la entidad al scope actual
scope.Add(scopeKey, storeCategory);
// Guarda las colecciones de objetos relacionados.
// Guardar objetos relacionados con la entidad actual
// Actualizar
// Cierra la conexión si fue abierta en la función
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Actualizar los campos new y changed
storeCategory.IsNew = false;
storeCategory.Changed = false;
}
catch (DbException dbException)
{
// Anula la transaccion
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Relanza una excepcion personalizada
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Cierra la conexión si fue inicializada
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
/// <summary>
/// Imports the data into SQL CE. This is identical to <see cref="ImportIntoSqlServer" /> except for the names of the classes
/// SqlCeConnection/SqlConnection, SqlCeBulkCopy/SqlBulkCopy, SqlCeBulkCopyOptions/SqlBulkCopyOptions
/// </summary>
/// <param name="backupFile">The backup file.</param>
private static void ImportIntoSqlCe(IBackupFile backupFile)
{
using (var ds = GenerateDataSet(backupFile))
{
backupFile.SchemaVersion = GetDataSchemaVersion(ds);
using (var cn = new SqlCeConnection(backupFile.ConnectionString))
{
cn.Open();
using (var tran = cn.BeginTransaction())
{
if (backupFile.SchemaVersion == GalleryDataSchemaVersion.V2_6_0)
{
Migrate26Controller.UpgradeData(ds, GalleryDataSchemaVersion.V3_2_1, backupFile.GalleryDataStore, cn, tran);
}
if (backupFile.SchemaVersion < GalleryDataSchemaVersion.V4_0_0)
{
MigrateController.UpgradeToCurrentSchema(ds);
}
DropSelfReferencingAlbumConstraint(backupFile, cn, tran);
ClearData(backupFile, cn, tran);
if (backupFile.IncludeMembershipData)
{
// SqlBulkCopy requires SQL permissions equivalent to that provided in the db_ddladmin or db_owner roles.
using (var bulkCopy = new SqlCeBulkCopy(cn, SqlCeBulkCopyOptions.KeepIdentity, tran))
{
bulkCopy.BulkCopyTimeout = 3600; // 1 hour
foreach (var tableName in backupFile.MembershipTables)
{
bulkCopy.ColumnMappings.Clear();
bulkCopy.DestinationTableName = Utils.GetSqlName(tableName, backupFile.GalleryDataStore, "dbo");
foreach (DataColumn dc in ds.Tables[tableName].Columns)
{
bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
// Write from the source to the destination.
try
{
bulkCopy.WriteToServer(ds.Tables[tableName]);
}
catch (Exception ex)
{
// Add a little info to exception and re-throw.
if (!ex.Data.Contains("SQL Bulk copy error"))
{
ex.Data.Add("SQL Bulk copy error", String.Format(CultureInfo.CurrentCulture, "Error occurred while importing table {0}.", tableName));
}
throw;
}
}
}
}
if (backupFile.IncludeGalleryData)
{
// Tables to skip: Event, MediaQueue, Synchronize
// SqlBulkCopy requires SQL permissions equivalent to that provided in the db_ddladmin or db_owner roles.
using (var bulkCopy = new SqlCeBulkCopy(cn, SqlCeBulkCopyOptions.KeepIdentity, tran))
{
bulkCopy.BulkCopyTimeout = 3600; // 1 hour
foreach (var tableName in backupFile.GalleryTableNames)
{
if (backupFile.SchemaVersion == GalleryDataSchemaVersion.V2_6_0 && Array.IndexOf(GalleryTableNamesToIgnoreDuringUpgrade, tableName) >= 0)
{
continue; // Don't import certain tables when upgrading because we want to keep the 3.0 data
}
bulkCopy.DestinationTableName = Utils.GetSqlName(tableName, backupFile.GalleryDataStore);
// Don't need to map the columns like we did in the membership section because it works without it.
// Write from the source to the destination.
try
{
bulkCopy.WriteToServer(ds.Tables[tableName]);
}
catch (Exception ex)
{
// Add a little info to exception and re-throw.
if (!ex.Data.Contains("SQL Bulk copy error"))
{
ex.Data.Add("SQL Bulk copy error", String.Format(CultureInfo.CurrentCulture, "Error occurred while importing table {0}.", tableName));
}
throw;
}
}
}
}
if (backupFile.SchemaVersion == GalleryDataSchemaVersion.V2_6_0)
{
Migrate26Controller.AddMissingMeta(backupFile.GalleryDataStore, cn, tran);
}
RestoreSelfReferencingAlbumConstraint(backupFile, cn, tran);
tran.Commit();
}
}
}
CompactSqlCeDb(backupFile);
}
public int update_LogPaymentApproval(object[] obj)
{
//string sql =
// "INSERT INTO phone ([SERIAL_NUMBER], [NUMBER], [NUMBER_CHANGE_COUNT], [PAYMENT_COUNT], [LEFT_CHARGE], [ORDER]) " +
//"VALUES (@SERIAL_NUMBER, @NUMBER, @NUMBER_CHANGE_COUNT, @PAYMENT_COUNT, @LEFT_CHARGE, @ORDER)";
int result = -1;
string sql = "UPDATE log_payment " +
"SET [CASH] = @CASH, [APPROVAL_NUMBER] = @APPROVAL_NUMBER, [APPROVAL_TIME] = GETDATE() " +
"WHERE [IDX] = @IDX";
SqlCeConnection conn = new SqlCeConnection(connString);
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
conn.Open();
SqlCeTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd.Parameters.AddWithValue("@CASH", obj[0]);
cmd.Parameters.Add("@APPROVAL_NUMBER", obj[1]);
cmd.Parameters.AddWithValue("@IDX", obj[2]);
//cmd.Parameters.AddWithValue("@NUMBER_CHANGE_COUNT", 0);
//cmd.Parameters.Add("@NUMBER_CHANGE_COUNT", (Int32)1);
//cmd.Parameters.Add("@PAYMENT_COUNT", (Int32)1);
//cmd.Parameters.Add("@LEFT_CHARGE", (Int32)1);
//cmd.Parameters.Add("@ORDER", (Int32)1);
try
{
result = cmd.ExecuteNonQuery();
tran.Commit();
}
finally
{
conn.Close();
}
return result;
}
/// <summary>
/// Migrates the data in a membership database from ASP.NET 2.0 to ASP.NET 4.0 compatibility
/// by copying the data from the old database to a new database
/// </summary>
/// <param name="oldConnStr"></param>
/// <param name="newConnStr"></param>
public void MigrateMembershipDatabaseToAspNet40(string oldConnStr, string newConnStr)
{
if (String.IsNullOrWhiteSpace(oldConnStr))
throw new ArgumentException("Parameter cannot be null or empty", "oldConnStr");
if (String.IsNullOrWhiteSpace(newConnStr))
throw new ArgumentException("Parameter cannot be null or empty", "newConnStr");
SqlCeConnection oldConn = new SqlCeConnection(oldConnStr);
SqlCeConnection newConn = new SqlCeConnection(newConnStr);
SqlCeTransaction newTrans = null;
SqlCeCommand oldCmd = null;
SqlCeCommand newCmd = null;
System.Collections.Generic.Dictionary<String, Guid> apps = new System.Collections.Generic.Dictionary<String, Guid>();
System.Collections.Generic.Dictionary<String, Guid> roles = new System.Collections.Generic.Dictionary<String, Guid>();
try
{
oldConn.Open();
newConn.Open();
newTrans = newConn.BeginTransaction();
/**********************************************************************************
* aspnet_Applications
*********************************************************************************/
oldCmd = new SqlCeCommand("SELECT DISTINCT ApplicationName FROM aspnet_Users", oldConn);
using (SqlCeDataReader reader = oldCmd.ExecuteReader())
{
while (reader.Read())
{
string name = reader.GetString(0);
Guid id = Guid.NewGuid();
newCmd = new SqlCeCommand(@"
SELECT ApplicationId FROM aspnet_Applications
WHERE ApplicationName = @ApplicationName", newConn);
newCmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = name;
object value = newCmd.ExecuteScalar();
if (value != null && value != DBNull.Value)
{
apps.Add(name, (Guid)value);
}
else
{
apps.Add(name, id);
newCmd = new SqlCeCommand(
@"INSERT INTO aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName, Description)
VALUES (@ApplicationId, @ApplicationName, @LoweredApplicationName, NULL)", newConn, newTrans);
newCmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = id;
newCmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = name;
newCmd.Parameters.Add("@LoweredApplicationName", SqlDbType.NVarChar, 256).Value = name.ToLowerInvariant();
newCmd.ExecuteNonQuery();
}
}
}
/**********************************************************************************
* aspnet_Users
*********************************************************************************/
oldCmd = new SqlCeCommand(
@"SELECT PKID, ApplicationName, Username, LastActivityDate
FROM aspnet_Users", oldConn);
using (SqlCeDataReader reader = oldCmd.ExecuteReader())
{
while (reader.Read())
{
Guid userId = reader.GetGuid(0);
string applicationName = reader.GetString(1);
string userName = reader.GetString(2);
DateTime lastActivityDate = reader.GetDateTime(3);
newCmd = new SqlCeCommand(
@"INSERT INTO [aspnet_Users]
([ApplicationId]
,[UserId]
,[UserName]
,[LoweredUserName]
,[IsAnonymous]
,[LastActivityDate])
VALUES
(@ApplicationId
,@UserId
,@UserName
,@LoweredUserName
,@IsAnonymous
,@LastActivityDate)", newConn, newTrans);
newCmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = apps[applicationName];
newCmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = userId;
newCmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = userName;
newCmd.Parameters.Add("@LoweredUserName", SqlDbType.NVarChar, 256).Value = userName.ToLowerInvariant();
newCmd.Parameters.Add("@IsAnonymous", SqlDbType.Bit).Value = false;
newCmd.Parameters.Add("@LastActivityDate", SqlDbType.DateTime).Value = lastActivityDate;
newCmd.ExecuteNonQuery();
}
}
/**********************************************************************************
* aspnet_Membership
*********************************************************************************/
oldCmd = new SqlCeCommand(
@"SELECT ApplicationName, PKID, Password, Email, PasswordQuestion, PasswordAnswer,
IsApproved, IsLockedOut, CreationDate, LastLoginDate, LastPasswordChangedDate, LastLockedOutDate,
FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart, Comment
FROM aspnet_Users", oldConn);
using (SqlCeDataReader reader = oldCmd.ExecuteReader())
{
while (reader.Read())
{
string applicationName = reader.GetString(0);
Guid userId = reader.GetGuid(1);
string password = reader.GetString(2);
string email = reader.GetString(3);
string passwordQuestion = reader.GetString(4);
string passwordAnswer = reader.GetString(5);
bool isApproved = reader.GetBoolean(6);
bool isLockedOut = reader.GetBoolean(7);
DateTime creationDate = reader.GetDateTime(8);
DateTime lastLoginDate = reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.GetDateTime(10);
DateTime lastLockedOutDate = reader.GetDateTime(11);
int failedPasswordAttemptCount = reader.GetInt32(12);
DateTime failedPasswordAttemptWindowsStart = reader.GetDateTime(13);
int failedPasswordAnswerAttemptCount = reader.GetInt32(14);
DateTime failedPasswordAnswerAttemptWindowStart = reader.GetDateTime(15);
string comment = reader.GetString(16);
newCmd = new SqlCeCommand(
@"INSERT INTO [aspnet_Membership]
([ApplicationId]
,[UserId]
,[Password]
,[PasswordFormat]
,[PasswordSalt]
,[Email]
,[LoweredEmail]
,[PasswordQuestion]
,[PasswordAnswer]
,[IsApproved]
,[IsLockedOut]
,[CreateDate]
,[LastLoginDate]
,[LastPasswordChangedDate]
,[LastLockoutDate]
,[FailedPasswordAttemptCount]
,[FailedPasswordAttemptWindowStart]
,[FailedPasswordAnswerAttemptCount]
,[FailedPasswordAnswerAttemptWindowStart]
,[Comment])
VALUES
(@ApplicationId
,@UserId
,@Password
,@PasswordFormat
,@PasswordSalt
,@Email
,@LoweredEmail
,@PasswordQuestion
,@PasswordAnswer
,@IsApproved
,@IsLockedOut
,@CreateDate
,@LastLoginDate
,@LastPasswordChangedDate
,@LastLockoutDate
,@FailedPasswordAttemptCount
,@FailedPasswordAttemptWindowStart
,@FailedPasswordAnswerAttemptCount
,@FailedPasswordAnswerAttemptWindowStart
,@Comment)", newConn, newTrans);
newCmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = apps[applicationName];
newCmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = userId;
newCmd.Parameters.Add("@Password", SqlDbType.NVarChar, 128).Value = password;
newCmd.Parameters.Add("@PasswordFormat", SqlDbType.Int).Value = PasswordFormat.GetHashCode();
newCmd.Parameters.Add("@PasswordSalt", SqlDbType.NVarChar, 128).Value =
(PasswordFormat == MembershipPasswordFormat.Encrypted) ? String.Empty : encryptionKey;
newCmd.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = email;
newCmd.Parameters.Add("@LoweredEmail", SqlDbType.NVarChar, 256).Value = email.ToLowerInvariant();
newCmd.Parameters.Add("@PasswordQuestion", SqlDbType.NVarChar, 256).Value = passwordQuestion;
newCmd.Parameters.Add("@PasswordAnswer", SqlDbType.NVarChar, 128).Value = passwordAnswer;
newCmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = isApproved;
newCmd.Parameters.Add("@IsLockedOut", SqlDbType.Bit).Value = isLockedOut;
newCmd.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = creationDate;
newCmd.Parameters.Add("@LastLoginDate", SqlDbType.DateTime).Value = lastLoginDate;
newCmd.Parameters.Add("@LastPasswordChangedDate", SqlDbType.DateTime).Value = lastPasswordChangedDate;
newCmd.Parameters.Add("@LastLockoutDate", SqlDbType.DateTime).Value = lastLockedOutDate;
newCmd.Parameters.Add("@FailedPasswordAttemptCount", SqlDbType.Int).Value = failedPasswordAttemptCount;
newCmd.Parameters.Add("@FailedPasswordAttemptWindowStart", SqlDbType.DateTime).Value = failedPasswordAttemptWindowsStart;
newCmd.Parameters.Add("@FailedPasswordAnswerAttemptCount", SqlDbType.Int).Value = failedPasswordAnswerAttemptCount;
newCmd.Parameters.Add("@FailedPasswordAnswerAttemptWindowStart", SqlDbType.DateTime).Value = failedPasswordAnswerAttemptWindowStart;
newCmd.Parameters.Add("@Comment", SqlDbType.NText).Value = comment;
newCmd.ExecuteNonQuery();
}
}
/**********************************************************************************
* aspnet_Roles
*********************************************************************************/
oldCmd = new SqlCeCommand(@"SELECT Rolename, ApplicationName FROM aspnet_Roles", oldConn);
using (SqlCeDataReader reader = oldCmd.ExecuteReader())
{
while (reader.Read())
{
string roleName = reader.GetString(0);
string applicationName = reader.GetString(1);
Guid roleId = Guid.NewGuid();
roles.Add(applicationName + ":" + roleName, roleId);
newCmd = new SqlCeCommand(
@"INSERT INTO apsnet_Roles (ApplicationId, RoleId, RoleName, LoweredRoleName, Description)
VALUES (@ApplicationId, @RoleId @RoleName, @LoweredRoleName, NULL)", newConn, newTrans);
newCmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = apps[applicationName];
newCmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = roleId;
newCmd.Parameters.Add("@RoleName", SqlDbType.NVarChar, 256).Value = roleName;
newCmd.Parameters.Add("@LoweredRoleName", SqlDbType.NVarChar, 256).Value = roleName.ToLowerInvariant();
newCmd.ExecuteNonQuery();
}
}
/**********************************************************************************
* aspnet_UsersInRoles
*********************************************************************************/
oldCmd = new SqlCeCommand(
@"SELECT U.PKID, UR.Rolename, UR.ApplicationName
FROM aspnet_UsersInRoles UR
INNER JOIN aspnet_Users U ON UR.Username = U.UserName
AND UR.ApplicationName = U.ApplicationName", oldConn);
using (SqlCeDataReader reader = oldCmd.ExecuteReader())
{
while (reader.Read())
{
Guid userId = reader.GetGuid(0);
string roleName = reader.GetString(1);
string applicationName = reader.GetString(2);
newCmd = new SqlCeCommand(
@"INSERT INTO aspnet_UsersInRoles (UserId, RoleId)
VALUES (@UserId, @RoleId)", newConn, newTrans);
newCmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = userId;
newCmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = roles[applicationName + ":" + roleName];
newCmd.ExecuteNonQuery();
}
}
newTrans.Commit();
}
catch (Exception ex)
{
if (newTrans != null)
newTrans.Rollback();
throw ex;
}
finally
{
if (oldCmd != null)
oldCmd.Dispose();
oldConn.Dispose();
newConn.Dispose();
}
}
public void InsertPlacementActivity(XmlResponseGetDocPlacementResult aResultGetDocPlacement)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
// Удаляем все позиции выбранного документа
using (SqlCeCommand sqlCommand = new SqlCeCommand("DELETE FROM PlacementActivityLine", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
// Удаляем выбранный документ
using (SqlCeCommand sqlCommand = new SqlCeCommand("DELETE FROM PlacementActivityHeader", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
if (aResultGetDocPlacement.Document.Count > 0)
{
// Вставляем новую запись выбранного документа
using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO PlacementActivityHeader(No) VALUES(@No)", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add(new SqlCeParameter("@No", aResultGetDocPlacement.Document.No));
sqlCommand.ExecuteNonQuery();
}
// Вставляем новые позиции выбранного документа
using (SqlCeCommand sqlCommand = new SqlCeCommand("PlacementActivityLine", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.TableDirect;
using (SqlCeResultSet rs = sqlCommand.ExecuteResultSet(ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord rec = rs.CreateRecord();
Int32 id_PlacementActivityHeader = ObjectFactory.PlacementActivityHeader(sqlTran).Id_PlacementActivityHeader;
rec["Id_PlacementActivityHeader"] = id_PlacementActivityHeader;
Int32 count = aResultGetDocPlacement.Document.Count;
for (Int32 k = 0; k < count; k++)
{
rec["ItemNo"] = aResultGetDocPlacement.Document.Line(k).ItemNo;
rec["ItemNo2"] = aResultGetDocPlacement.Document.Line(k).ItemNo2;
rec["Description"] = aResultGetDocPlacement.Document.Line(k).Description;
rec["UnitOfMeasure"] = aResultGetDocPlacement.Document.Line(k).UnitOfMeasure;
rec["QtyPlacement"] = aResultGetDocPlacement.Document.Line(k).QtyPlacement;
rec["ProcessedQty"] = aResultGetDocPlacement.Document.Line(k).ProcessedQty;
rec["BinCode"] = aResultGetDocPlacement.Document.Line(k).BinCode;
rec["BinContQty"] = aResultGetDocPlacement.Document.Line(k).BinContQty;
rec["LineNo"] = aResultGetDocPlacement.Document.Line(k).LineNo;
if (aResultGetDocPlacement.Document.Line(k).QuantityInPackage == 0)
{
rec["QuantityInPackage"] = 1;
}
else
{
rec["QuantityInPackage"] = aResultGetDocPlacement.Document.Line(k).QuantityInPackage;
}
rs.Insert(rec);
}
}
}
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}
/// <summary>
/// TopUpCustomerDeposit
/// </summary>
/// <param name="Id"></param>
/// <param name="Deposit"></param>
/// <returns></returns>
public static bool TopUpCustomerDeposit(string Id, decimal Deposit)
{
var sql = @"UPDATE Customer SET Deposit = (Deposit + @Deposit) WHERE Id = @Id";
var ret = false;
using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
{
SqlCeTransaction tx = null;
try
{
conn.Open();
tx = conn.BeginTransaction();
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("Id", SqlDbType.NVarChar, 50)
{
Value = Id
},
new SqlCeParameter("Deposit", SqlDbType.Decimal)
{
Value = Deposit
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
ret = command.ExecuteNonQuery() > 0;
sql = @"INSERT INTO DepositDetail (CustomerId,Detail,ItemSalesId,UpdateTime)
VALUES (@CustomerId,@Detail,@ItemSalesId,@UpdateTime)";
command = conn.CreateCommand();
parameters = new[]
{
new SqlCeParameter("CustomerId", SqlDbType.NVarChar, 50)
{
Value = Id
},
new SqlCeParameter("Detail", SqlDbType.NVarChar, 200)
{
Value = "增值---" + Deposit + " 元"
},
new SqlCeParameter("ItemSalesId", SqlDbType.NVarChar, 50)
{
Value = ""
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
command.ExecuteNonQuery();
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
}
finally
{
conn.Close();
}
}
return(ret);
}
//
// System.Web.Security.RoleProvider methods.
//
//
// RoleProvider.AddUsersToRoles
//
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
foreach (string rolename in roleNames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
if (username.IndexOf(',') > 0)
{
throw new ArgumentException("User names cannot contain commas.");
}
foreach (string rolename in roleNames)
{
if (IsUserInRole(username, rolename))
{
throw new ProviderException("User is already in role.");
}
}
}
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand("INSERT INTO [aspnet_UsersInRoles] " +
" (UserId, RoleId) " +
" Values(@UserId, @RoleId)", conn))
{
SqlCeParameter userParm = cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
SqlCeParameter roleParm = cmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier);
SqlCeTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in roleNames)
{
userParm.Value = GetUserId(username);
roleParm.Value = GetRoleId(rolename);
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (SqlCeException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "AddUsersToRoles");
}
else
{
throw;
}
}
}
}
}
public bool updateProduct(Product product)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" update Products ");
sb.Append(" set Name ='" + product.Name + "',");
sb.Append(" Code ='" + product.Code + "',");
sb.Append(" BarCode ='" + product.BarCode + "',");
sb.Append(" Unit ='" + product.Unit + "',");
sb.Append(" Price1 ='" + product.Price1 + "',");
sb.Append(" Price3 ='" + product.Price3 + "',");
sb.Append(" Price4 ='" + product.Price4 + "',");
sb.Append(" CreateDate ='" + DatetimeUtil.convertToDateThai2(product.CreateDate.ToString("dd/MM/yyyy")) + "'");
sb.Append(" where BarCode ='" + product.BarCode + "'");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
//
// RoleProvider.DeleteRole
//
public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
if (!RoleExists(roleName))
{
throw new ProviderException("Role does not exist.");
}
if (throwOnPopulatedRole && GetUsersInRole(roleName).Length > 0)
{
throw new ProviderException("Cannot delete a populated role.");
}
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand("DELETE FROM [aspnet_Roles] " +
" WHERE RoleId = @RoleId AND ApplicationId = @ApplicationId", conn))
{
cmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = GetRoleId(roleName);
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;
using (SqlCeCommand cmd2 = new SqlCeCommand("DELETE FROM [aspnet_UsersInRoles]" +
" WHERE RoleId = @RoleId", conn))
{
cmd2.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = GetRoleId(roleName);
SqlCeTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd2.Transaction = tran;
cmd2.ExecuteNonQuery();
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (SqlCeException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteRole");
return(false);
}
else
{
throw;
}
}
}
}
}
return(true);
}
/// <summary>
/// Función que guarda un UserActionClientDataEntity en la base de datos.
/// </summary>
/// <param name="userActionClientData">UserActionClientDataEntity a guardar</param>
/// <param name="scope">Estructura interna para evitar problemas con referencias circulares</param>
/// <exception cref="ArgumentNullException">
/// Si <paramref name="userActionClientData"/> no es un <c>UserActionClientDataEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// Si una DbException ocurre cuando se accede a la base de datos
/// </exception>
public void Save(UserActionClientDataEntity userActionClientData, Dictionary <string, IEntity> scope)
{
if (userActionClientData == null)
{
throw new ArgumentException("The argument can't be null");
}
// Crear una clave unica para identificar el objeto dentro del scope interno
string scopeKey = userActionClientData.Id.ToString(NumberFormatInfo.InvariantInfo) + "UserActionClientData";
if (scope != null)
{
// Si se encuentra dentro del scope lo retornamos
if (scope.ContainsKey(scopeKey))
{
return;
}
}
else
{
// Crea un nuevo scope si este no fue enviado
scope = new Dictionary <string, IEntity>();
}
try
{
// Crea una nueva conexion y una nueva transaccion si no hay una a nivel superior
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
string commandName = "";
bool isUpdate = false;
// Verifica si se debe hacer una actualización o una inserción
if (userActionClientData.IsNew || !DataAccessConnection.ExistsEntity(userActionClientData.Id, "UserActionClientData", "idUserActionClientData", dbConnection, dbTransaction))
{
commandName = "INSERT INTO [UserActionClientData] (idUserActionClientData, ACTIONTYPE, START, STOP, IDTABLE, IDREGISTER, IDCOMPONENT, IDSERVICE, [TIMESTAMP] ) VALUES( @idUserActionClientData, @actionType,@start,@stop,@idTable,@idRegister,@idComponent,@idService, GETDATE()); ";
}
else
{
isUpdate = true;
commandName = "UPDATE [UserActionClientData] SET actionType = @actionType, start = @start, stop = @stop, idTable = @idTable, idRegister = @idRegister, idComponent = @idComponent, idService = @idService , timestamp=GETDATE() WHERE idUserActionClientData = @idUserActionClientData";
}
// Se crea un command
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(commandName, dbConnection, dbTransaction);
// Agregar los parametros del command .
SqlCeParameter parameter;
if (!isUpdate && userActionClientData.Id == 0)
{
userActionClientData.Id = DataAccessConnection.GetNextId("idUserActionClientData", "UserActionClientData", dbConnection, dbTransaction);
}
parameter = dataAccess.GetNewDataParameter("@idUserActionClientData", DbType.Int32);
parameter.Value = userActionClientData.Id;
sqlCommand.Parameters.Add(parameter);
FillSaveParameters(userActionClientData, sqlCommand);
// Ejecutar el command
sqlCommand.ExecuteNonQuery();
scopeKey = userActionClientData.Id.ToString(NumberFormatInfo.InvariantInfo) + "UserActionClientData";
// Agregar la entidad al scope actual
scope.Add(scopeKey, userActionClientData);
// Guarda las colecciones de objetos relacionados.
// Guardar objetos relacionados con la entidad actual
// Actualizar
// Cierra la conexión si fue abierta en la función
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Actualizar los campos new y changed
userActionClientData.IsNew = false;
userActionClientData.Changed = false;
}
catch (DbException dbException)
{
// Anula la transaccion
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Relanza una excepcion personalizada
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Cierra la conexión si fue inicializada
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
//
// RoleProvider.RemoveUsersFromRoles
//
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
foreach (string rolename in roleNames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
foreach (string rolename in roleNames)
{
if (!IsUserInRole(username, rolename))
{
throw new ProviderException("User is not in role.");
}
}
}
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand("DELETE FROM [aspnet_UsersInRoles]" +
" WHERE UserId = @UserId AND RoleId = @RoleId", conn))
{
SqlCeParameter userParm = cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
SqlCeParameter roleParm = cmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier);
SqlCeTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in roleNames)
{
userParm.Value = GetUserId(username);
roleParm.Value = GetRoleId(rolename);
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (SqlCeException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "RemoveUsersFromRoles");
}
else
{
throw;
}
}
}
}
}
private void InitializeDatabase()
{
string connectionString = ConnectionString;
Debug.AssertStringNotEmpty(connectionString);
string dbFilePath = ConnectionStringHelper.GetDataSourceFilePath(connectionString);
if (File.Exists(dbFilePath))
{
return;
}
using (SqlCeEngine engine = new SqlCeEngine(ConnectionString))
{
engine.CreateDatabase();
}
using (SqlCeConnection conn = new SqlCeConnection(ConnectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand())
{
conn.Open();
SqlCeTransaction transaction = conn.BeginTransaction();
try
{
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.CommandText = @"
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'ELMAH_Error'";
object obj = cmd.ExecuteScalar();
if (obj == null)
{
cmd.CommandText = @"
CREATE TABLE ELMAH_Error (
[ErrorId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT newid(),
[Application] NVARCHAR(60) NOT NULL,
[Host] NVARCHAR(50) NOT NULL,
[Type] NVARCHAR(100) NOT NULL,
[Source] NVARCHAR(60) NOT NULL,
[Message] NVARCHAR(500) NOT NULL,
[User] NVARCHAR(50) NOT NULL,
[StatusCode] INT NOT NULL,
[TimeUtc] DATETIME NOT NULL,
[Sequence] INT IDENTITY (1, 1) NOT NULL,
[AllXml] NTEXT NOT NULL
)";
cmd.ExecuteNonQuery();
cmd.CommandText = @"
CREATE NONCLUSTERED INDEX [IX_Error_App_Time_Seq] ON [ELMAH_Error]
(
[Application] ASC,
[TimeUtc] DESC,
[Sequence] DESC
)";
cmd.ExecuteNonQuery();
}
transaction.Commit(CommitMode.Immediate);
}
catch (SqlCeException)
{
transaction.Rollback();
throw;
}
}
}
}
public static void dropReferentialConstraint(string table, string referencedTable)
{
string findConstraintSQL = @"
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_TABLE_NAME = @Table AND UNIQUE_CONSTRAINT_TABLE_NAME = @ReferencedTable";
SqlCeCommand cmd = new SqlCeCommand(findConstraintSQL);
cmd.Parameters.Add("@Table", SqlDbType.NVarChar).Value = table;
cmd.Parameters.Add("@ReferencedTable", SqlDbType.NVarChar).Value = referencedTable;
string errMsg = "Greska prilikom citanja podataka iz baze.";
SqlCeDataReader rdr = SqlCeUtilities.executeReader(cmd, errMsg);
if (!rdr.Read())
{
throw new Exception("Constraint does not exist.");
}
string constraintName = (string)rdr["CONSTRAINT_NAME"];
// NOTE: Izgleda da dodavanje parametara (pomocu @parameterName) radi samo kada je parametar sa desne
// strane znaka jednakosti (kao u findConstraintSQL). Zato ovde koristim spajanje stringova.
string dropConstraintSQL = "ALTER TABLE " + table + " DROP CONSTRAINT " + constraintName;
SqlCeCommand cmd2 = new SqlCeCommand(dropConstraintSQL);
SqlCeConnection conn = new SqlCeConnection(ConfigurationParameters.ConnectionString);
errMsg = "Neuspesna promena baze.";
SqlCeTransaction tr = null;
try
{
conn.Open();
tr = conn.BeginTransaction();
cmd2.Connection = conn;
cmd2.Transaction = tr;
int result = cmd2.ExecuteNonQuery();
tr.Commit();
}
catch (SqlCeException e)
{
// in Open()
if (tr != null)
{
tr.Rollback(); // TODO: this can throw Exception and InvalidOperationException
}
throw new InfrastructureException(errMsg, e);
}
catch (InvalidOperationException e)
{
// in ExecuteNonQuery(), ExecureScalar()
if (tr != null)
{
tr.Rollback();
}
throw new InfrastructureException(errMsg, e);
}
// za svaki slucaj
catch (Exception)
{
if (tr != null)
{
tr.Rollback();
}
throw;
}
finally
{
conn.Close();
}
}
private void WriteToServer(ISqlCeBulkCopyInsertAdapter adapter)
{
CheckDestination();
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
List <KeyValuePair <int, int> > mappings = null;
if (Mappings.Count > 0)
{
//mapping are set, and should be validated
mappings = Mappings.ValidateCollection(conn, adapter, options, destination);
}
else
{
//create default column mappings
mappings = SqlCeBulkCopyColumnMappingCollection.Create(conn, adapter, options, destination);
}
SqlCeTransaction localTrans = trans ?? conn.BeginTransaction();
using (SqlCeCommand cmd = new SqlCeCommand(destination, conn, localTrans))
{
cmd.CommandType = CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
{
int idOrdinal = SqlCeBulkCopyTableHelpers.IdentityOrdinal(conn, options, destination);
SqlCeUpdatableRecord rec = rs.CreateRecord();
int rowCounter = 0;
int totalRows = 0;
IdInsertOn();
//Converting to an array removed the perf issue of a list and foreach statement.
var cm = mappings.ToArray();
while (adapter.Read())
{
if (adapter.SkipRow())
{
continue;
}
for (int i = 0; i < cm.Length; i++)
{
//caching the values this way do not cause a perf issue.
var sourceIndex = cm[i].Key;
var destIndex = cm[i].Value;
// Let the destination assign identity values
if (!keepIdentity && destIndex == idOrdinal)
{
continue;
}
//determine if we should ever allow this in the map.
if (sourceIndex < 0)
{
continue;
}
var value = sourceIndex > -1 ? adapter.Get(sourceIndex) : null;
if (value != null && value.GetType() != DbNullType)
{
rec.SetValue(destIndex, value);
}
else
{
//we can't write to an auto number column so continue
if (keepNulls && destIndex == idOrdinal)
{
continue;
}
if (keepNulls)
{
rec.SetValue(destIndex, DBNull.Value);
}
else
{
rec.SetDefault(destIndex);
}
}
// Fire event if needed
if (notifyAfter > 0 && rowCounter == notifyAfter)
{
FireRowsCopiedEvent(totalRows);
rowCounter = 0;
}
}
rowCounter++;
totalRows++;
rs.Insert(rec);
}
IdInsertOff();
}
}
//if we have our own transaction, we will commit it
if (trans == null)
{
localTrans.Commit(CommitMode.Immediate);
}
}
protected override void BeforeEachExample()
{
dbConnection = new SqlCeConnection(TestDbConnectionString);
dbConnection.Open();
transaction = dbConnection.BeginTransaction();
}
public bool insert_payuserinfo(object[] obj)
{
string sql =
"INSERT INTO paymentconditions ([NAME], [BALANCE], [HANDLING_BALANCE], [PHONE_NUMBER], [POINT_X], [POINT_Y], [ORDER]) " +
"VALUES (@NAME, @BALANCE, @HANDLING_BALANCE, @PHONE_NUMBER, @POINT_X, @POINT_Y, @ORDER)";
//string sql =
// "INSERT INTO phone ([SERIAL_NUMBER], [NUMBER]) " +
//"VALUES (@SERIAL_NUMBER, @NUMBER)";
SqlCeConnection conn = new SqlCeConnection(connString);
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
conn.Open();
SqlCeTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd.Parameters.Add("@NAME", obj[0].ToString());
cmd.Parameters.AddWithValue("@BALANCE", obj[1]);
cmd.Parameters.AddWithValue("@HANDLING_BALANCE", obj[2]);
cmd.Parameters.Add("@PHONE_NUMBER", obj[3].ToString());
cmd.Parameters.AddWithValue("@POINT_X", 0);
cmd.Parameters.AddWithValue("@POINT_Y", 0);
cmd.Parameters.AddWithValue("@ORDER", 0);
//cmd.Parameters.Add("@NUMBER_CHANGE_COUNT", (Int32)1);
//cmd.Parameters.Add("@PAYMENT_COUNT", (Int32)1);
//cmd.Parameters.Add("@LEFT_CHARGE", (Int32)1);
//cmd.Parameters.Add("@ORDER", (Int32)1);
try
{
cmd.ExecuteNonQuery();
tran.Commit();
}
finally
{
conn.Close();
}
return true;
}
public static void addColumn(string table, string column, SqlDbType type,
Nullable <int> size, object columnValue)
{
if (SqlCeUtilities.columnExists(table, column))
{
return;
}
string addColumnSql = "ALTER TABLE [" + table + "] ADD COLUMN [" + column + "] " +
getSqlType(type, size);
string updateColumnSql = "UPDATE [" + table + "] SET [" + column + "] = @" +
column;
SqlCeCommand addColumnCommand = new SqlCeCommand(addColumnSql);
SqlCeCommand updateColumnCommand = new SqlCeCommand(updateColumnSql);
if (size != null)
{
updateColumnCommand.Parameters.Add("@" + column, type, size.Value).Value = columnValue;
}
else
{
updateColumnCommand.Parameters.Add("@" + column, type).Value = columnValue;
}
SqlCeConnection conn = new SqlCeConnection(ConfigurationParameters.ConnectionString);
string errMsg = "Neuspesna promena baze.";
SqlCeTransaction tr = null;
try
{
conn.Open();
tr = conn.BeginTransaction();
addColumnCommand.Connection = conn;
addColumnCommand.Transaction = tr;
addColumnCommand.ExecuteNonQuery();
updateColumnCommand.Connection = conn;
updateColumnCommand.Transaction = tr;
updateColumnCommand.ExecuteNonQuery();
tr.Commit();
}
catch (SqlCeException e)
{
// in Open()
if (tr != null)
{
tr.Rollback(); // TODO: this can throw Exception and InvalidOperationException
}
throw new InfrastructureException(errMsg, e);
}
catch (InvalidOperationException e)
{
// in ExecuteNonQuery(), ExecureScalar()
if (tr != null)
{
tr.Rollback();
}
throw new InfrastructureException(errMsg, e);
}
// za svaki slucaj
catch (Exception)
{
if (tr != null)
{
tr.Rollback();
}
throw;
}
finally
{
conn.Close();
}
}
public void test()
{
string dir = Path.Combine(Environment.CurrentDirectory, "DB.sdf");
string connectionString = @"Data Source=|DataDirectory|\DB.sdf";
//string connectionString = @dir;
SqlCeConnection con = new SqlCeConnection(connectionString);
con.Open();
// 데이터베이스 커맨드 생성
SqlCeCommand cmd = new SqlCeCommand();
// 커맨드에 커넥션을 연결
cmd.Connection = con;
// 트랜잭션 생성
SqlCeTransaction tran = con.BeginTransaction();
cmd.Transaction = tran;
// 쿼리 생성 : Insert 쿼리
cmd.CommandText = "INSERT INTO Test VALUES('소녀시대')";
// 쿼리 실행
cmd.ExecuteNonQuery();
// 반복으로 몇개 더 넣어보겠습니다.
cmd.CommandText = "INSERT INTO Test VALUES('원더걸스')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES('카라')";
cmd.ExecuteNonQuery();
// 커밋
tran.Commit();
// SELECT 쿼리로 변경
cmd.CommandText = "SELECT * FROM Test";
// DataReader에 쿼리 결과값 저장
SqlCeDataReader reader = cmd.ExecuteReader();
// 결과값 출력
while (reader.Read())
{
Console.WriteLine(reader["Name"]);
}
con.Close();
}
/// <summary>
/// Function to Delete a CustomerEntity from database.
/// </summary>
/// <param name="customer">CustomerEntity to delete</param>
/// <param name="scope">Internal structure to avoid circular reference locks. Must provide an instance while calling from other data access object.</param>
/// <exception cref="ArgumentNullException">
/// If <paramref name="customer"/> is not a <c>CustomerEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// If an DbException occurs in the try block while accessing the database.
/// </exception>
public void Delete(CustomerEntity customer, Dictionary <string, IEntity> scope)
{
if (customer == null)
{
throw new ArgumentException("The argument can't be null");
}
try
{
// Open connection and initialize a transaction if needed
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
// Reload the entity to ensure deletion of older data
customer = this.Load(customer.Id, true);
if (customer == null)
{
throw new UtnEmallDataAccessException("Error retrieving data while trying to delete.");
}
// Create a command for delete
string cmdText = "DELETE FROM [Customer] WHERE idCustomer = @idCustomer";
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(cmdText, dbConnection, dbTransaction);
// Add values to parameters
SqlCeParameter parameterID = dataAccess.GetNewDataParameter("@idCustomer", DbType.Int32);
parameterID.Value = customer.Id;
sqlCommand.Parameters.Add(parameterID);
// Execute the command
sqlCommand.ExecuteNonQuery();
// Delete related objects
if (customer.Preferences != null)
{
this.DeletePreferenceCollection(new PreferenceDataAccess(), customer.Preferences, scope);
}
if (customer.DeviceProfile != null)
{
this.DeleteDeviceProfileCollection(new DeviceProfileDataAccess(), customer.DeviceProfile, scope);
}
// Commit transaction if is mine
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Remove entity from loaded objects
inMemoryEntities.Remove(customer.Id);
// Remove entity from current internal scope
if (scope != null)
{
string scopeKey = customer.Id.ToString(NumberFormatInfo.InvariantInfo) + "Customer";
scope.Remove(scopeKey);
}
}
catch (DbException dbException)
{
// Rollback transaction
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Rethrow as custom exception
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Close connection if it was initiated by this instance
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
public override void CreateSyncRepository()
{
string connectionString = "Data Source=\"" + _DbPath + "\";Max Database Size=128;Default Lock Escalation=100;";
IDbConnection conn = new SqlCeConnection(connectionString);
conn.Open();
IDbTransaction t = conn.BeginTransaction();
IDbCommand com = conn.CreateCommand();
com.Transaction = t;
StringBuilder createSyncItem = new StringBuilder();
createSyncItem.Append("CREATE TABLE SyncItem").
Append("(SyncID INT PRIMARY KEY IDENTITY, SyncFK INT, ClassID nvarchar(255), HashCode nvarchar(32), ").
Append("SyncGuid UNIQUEIDENTIFIER, RowGuid UNIQUEIDENTIFIER)");
com.CommandText = createSyncItem.ToString();
com.ExecuteNonQuery();
StringBuilder createFieldState = new StringBuilder();
createFieldState.Append("CREATE TABLE FieldState").
Append("(SyncFK INT, FieldName nvarchar(255), HashCode nvarchar(32), ").
Append("RowGuid UNIQUEIDENTIFIER, PRIMARY KEY (SyncFK, FieldName))");
com.CommandText = createFieldState.ToString();
com.ExecuteNonQuery();
t.Commit();
conn.Close();
conn.Dispose();
}
private void WriteToServer(ISqlCeBulkCopyInsertAdapter adapter)
{
CheckDestination();
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
GetAndDropConstraints();
List <KeyValuePair <int, int> > map;
int totalRows = 0;
SqlCeTransaction localTrans = _trans ?? _conn.BeginTransaction();
if (ColumnMappings.Count > 0)
{
//mapping are set, and should be validated
map = ColumnMappings.ValidateCollection(_conn, localTrans, adapter, _keepNulls, _destination);
}
else
{
//create default column mappings
map = SqlCeBulkCopyColumnMappingCollection.Create(_conn, localTrans, adapter, _keepNulls, _destination);
}
using (var cmd = new SqlCeCommand(_destination, _conn, localTrans))
{
cmd.CommandType = CommandType.TableDirect;
using (var rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
{
var idOrdinal = SqlCeBulkCopyTableHelpers.IdentityOrdinal(_conn, localTrans, _destination);
var rec = rs.CreateRecord();
var rowCounter = 0;
IdInsertOn(localTrans, idOrdinal);
//Converting to an array removed the perf issue of a list and foreach statement.
var cm = map.ToArray();
while (adapter.Read())
{
if (adapter.SkipRow())
{
continue;
}
for (var i = 0; i < cm.Length; i++)
{
//caching the values this way do not cause a perf issue.
var sourceIndex = cm[i].Key;
var destIndex = cm[i].Value;
// Let the destination assign identity values
if (!_keepIdentity && destIndex == idOrdinal)
{
continue;
}
//determine if we should ever allow this in the map.
if (sourceIndex < 0)
{
continue;
}
var value = sourceIndex > -1 ? adapter.Get(sourceIndex) : null;
if (value != null && value.GetType() != DbNullType)
{
rec.SetValue(destIndex, value);
}
else
{
//we can't write to an auto number column so continue
if (_keepNulls && destIndex == idOrdinal)
{
continue;
}
if (_keepNulls)
{
rec.SetValue(destIndex, DBNull.Value);
}
else
{
rec.SetDefault(destIndex);
}
}
}
rowCounter++;
totalRows++;
try
{
rs.Insert(rec);
}
catch (SqlCeException ex)
{
if (ex.NativeError == 25016 && _ignoreDuplicateErrors) //A duplicate value cannot be inserted into a unique index.
{
System.Diagnostics.Trace.TraceWarning("SqlCeBulkCopy: Duplicate value error was ignored");
continue;
}
else
{
throw;
}
}
// Fire event if needed
if (RowsCopied != null && _notifyAfter > 0 && rowCounter == _notifyAfter)
{
FireRowsCopiedEvent(totalRows);
rowCounter = 0;
}
}
IdInsertOff(localTrans, idOrdinal);
if (RowsCopied != null)
{
FireRowsCopiedEvent(totalRows);
}
}
}
//if we have our own transaction, we will commit it
if (_trans == null)
{
localTrans.Commit(CommitMode.Immediate);
localTrans.Dispose();
}
ResetSeed(totalRows);
RestoreConstraints();
}
//
// MembershipProvider.CreateUser
//
public override MembershipUser CreateUser(string username,
string password,
string email,
string passwordQuestion,
string passwordAnswer,
bool isApproved,
object providerUserKey,
out MembershipCreateStatus status)
{
ValidatePasswordEventArgs args =
new ValidatePasswordEventArgs(username, password, true);
OnValidatingPassword(args);
if (args.Cancel)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
if (RequiresUniqueEmail && !string.IsNullOrWhiteSpace(GetUserNameByEmail(email)))
{
status = MembershipCreateStatus.DuplicateEmail;
return null;
}
MembershipUser u = GetUser(username, false);
if (u == null)
{
DateTime createDate = DateTime.UtcNow;
if (providerUserKey == null)
{
providerUserKey = Guid.NewGuid();
}
else
{
if (!(providerUserKey is Guid))
{
status = MembershipCreateStatus.InvalidProviderUserKey;
return null;
}
}
passwordQuestion = string.IsNullOrWhiteSpace(passwordQuestion) ? string.Empty : passwordQuestion;
passwordAnswer = string.IsNullOrWhiteSpace(passwordAnswer) ? string.Empty : passwordAnswer;
string salt = GenerateSalt();
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand(
@"INSERT INTO [aspnet_Membership]
([ApplicationId]
,[UserId]
,[Password]
,[PasswordFormat]
,[PasswordSalt]
,[Email]
,[LoweredEmail]
,[PasswordQuestion]
,[PasswordAnswer]
,[IsApproved]
,[IsLockedOut]
,[CreateDate]
,[LastLoginDate]
,[LastPasswordChangedDate]
,[LastLockoutDate]
,[FailedPasswordAttemptCount]
,[FailedPasswordAttemptWindowStart]
,[FailedPasswordAnswerAttemptCount]
,[FailedPasswordAnswerAttemptWindowStart]
,[Comment])
VALUES
(@ApplicationId
,@UserId
,@Password
,@PasswordFormat
,@PasswordSalt
,@Email
,@LoweredEmail
,@PasswordQuestion
,@PasswordAnswer
,@IsApproved
,@IsLockedOut
,@CreateDate
,@LastLoginDate
,@LastPasswordChangedDate
,@LastLockoutDate
,@FailedPasswordAttemptCount
,@FailedPasswordAttemptWindowStart
,@FailedPasswordAnswerAttemptCount
,@FailedPasswordAnswerAttemptWindowStart
,@Comment)", conn))
{
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;
cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = (Guid)providerUserKey;
cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 128).Value = EncodePassword(password, salt);
cmd.Parameters.Add("@PasswordFormat", SqlDbType.Int).Value = PasswordFormat.GetHashCode();
cmd.Parameters.Add("@PasswordSalt", SqlDbType.NVarChar, 128).Value = salt;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = email;
cmd.Parameters.Add("@LoweredEmail", SqlDbType.NVarChar, 256).Value = email.ToLowerInvariant();
cmd.Parameters.Add("@PasswordQuestion", SqlDbType.NVarChar, 256).Value = passwordQuestion;
cmd.Parameters.Add("@PasswordAnswer", SqlDbType.NVarChar, 128).Value = EncodePassword(passwordAnswer, salt);
cmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = isApproved;
cmd.Parameters.Add("@IsLockedOut", SqlDbType.Bit).Value = false;
cmd.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = createDate;
cmd.Parameters.Add("@LastLoginDate", SqlDbType.DateTime).Value = createDate;
cmd.Parameters.Add("@LastPasswordChangedDate", SqlDbType.DateTime).Value = createDate;
cmd.Parameters.Add("@LastLockoutDate", SqlDbType.DateTime).Value = SqlDateTime.MinValue;
cmd.Parameters.Add("@FailedPasswordAttemptCount", SqlDbType.Int).Value = 0;
cmd.Parameters.Add("@FailedPasswordAttemptWindowStart", SqlDbType.DateTime).Value = SqlDateTime.MinValue;
cmd.Parameters.Add("@FailedPasswordAnswerAttemptCount", SqlDbType.Int).Value = 0;
cmd.Parameters.Add("@FailedPasswordAnswerAttemptWindowStart", SqlDbType.DateTime).Value = SqlDateTime.MinValue;
cmd.Parameters.Add("@Comment", SqlDbType.NText).Value = "";
using (SqlCeCommand cmd2 = new SqlCeCommand(
@"INSERT INTO [aspnet_Users]
([ApplicationId]
,[UserId]
,[UserName]
,[LoweredUserName]
,[IsAnonymous]
,[LastActivityDate])
VALUES
(@ApplicationId
,@UserId
,@UserName
,@LoweredUserName
,@IsAnonymous
,@LastActivityDate)
", conn))
{
cmd2.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;
cmd2.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = (Guid)providerUserKey;
cmd2.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;
cmd2.Parameters.Add("@LoweredUserName", SqlDbType.NVarChar, 256).Value = username.ToLowerInvariant();
cmd2.Parameters.Add("@IsAnonymous", SqlDbType.Bit).Value = false;
cmd2.Parameters.Add("@LastActivityDate", SqlDbType.DateTime).Value = createDate;
SqlCeTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd2.Transaction = tran;
int recAdded = cmd2.ExecuteNonQuery();
int recAdded2 = cmd.ExecuteNonQuery();
if (recAdded > 0 && recAdded2 > 0)
{
status = MembershipCreateStatus.Success;
tran.Commit();
}
else
{
status = MembershipCreateStatus.UserRejected;
tran.Rollback();
}
}
catch (SqlCeException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "CreateUser");
}
status = MembershipCreateStatus.ProviderError;
}
catch (Exception)
{
if (tran != null)
tran.Rollback();
status = MembershipCreateStatus.ProviderError;
}
}
}
}
return GetUser(username, false);
}
else
{
status = MembershipCreateStatus.DuplicateUserName;
}
return null;
}
private void InitializeDatabase()
{
string connectionString = ConnectionString;
Debug.AssertStringNotEmpty(connectionString);
string dbFilePath = ConnectionStringHelper.GetDataSourceFilePath(connectionString);
if (File.Exists(dbFilePath))
return;
using (SqlCeEngine engine = new SqlCeEngine(ConnectionString))
{
engine.CreateDatabase();
}
using (SqlCeConnection conn = new SqlCeConnection(ConnectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand())
{
conn.Open();
SqlCeTransaction transaction = conn.BeginTransaction();
try
{
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.CommandText = @"
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'ELMAH_Error'";
object obj = cmd.ExecuteScalar();
if (obj == null)
{
cmd.CommandText = @"
CREATE TABLE ELMAH_Error (
[ErrorId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT newid(),
[Application] NVARCHAR(60) NOT NULL,
[Host] NVARCHAR(50) NOT NULL,
[Type] NVARCHAR(100) NOT NULL,
[Source] NVARCHAR(60) NOT NULL,
[Message] NVARCHAR(500) NOT NULL,
[User] NVARCHAR(50) NOT NULL,
[StatusCode] INT NOT NULL,
[TimeUtc] DATETIME NOT NULL,
[Sequence] INT IDENTITY (1, 1) NOT NULL,
[AllXml] NTEXT NOT NULL
)";
cmd.ExecuteNonQuery();
cmd.CommandText = @"
CREATE NONCLUSTERED INDEX [IX_Error_App_Time_Seq] ON [ELMAH_Error]
(
[Application] ASC,
[TimeUtc] DESC,
[Sequence] DESC
)";
cmd.ExecuteNonQuery();
}
transaction.Commit(CommitMode.Immediate);
}
catch (SqlCeException)
{
transaction.Rollback();
throw;
}
}
}
}
public bool editNumberCheckProduct(string _id,string _dcode,string _pcode,int numberedit)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" update DocCheckProductDetails ");
sb.Append(" set NumProduct ="+numberedit+"");
sb.Append(" where DCode ='"+_dcode+"'");
sb.Append(" and PCode='"+_pcode+"'");
sb.Append(" and ID='" + _id + "'");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
//public static bool Insert(string sql)
//{
// var ret = false;
// using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
// {
// conn.Open();
// SqlCeCommand command = new SqlCeCommand(sql, conn);
// ret = command.ExecuteNonQuery() > 0;
// conn.Close();
// }
// return ret;
//}
/// <summary>
/// InsertItemMaster
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public static bool InsertItemMaster(ItemMaster item)
{
var sql = @"INSERT INTO ItemMaster(ItemId,ItemName,ItemSize,ItemType,StockCount,StockPrice,Price,Id,UpdateTime,Color)
VALUES (@ItemId,@ItemName,@ItemSize,@ItemType,@StockCount,@StockPrice,@Price,@Id,@UpdateTime,@Color)";
var ret = false;
using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
{
SqlCeTransaction tx = null;
try
{
conn.Open();
tx = conn.BeginTransaction();
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("ItemId", SqlDbType.NVarChar, 100)
{
Value = item.ItemId
},
new SqlCeParameter("ItemName", SqlDbType.NVarChar, 250)
{
Value = item.ItemName
},
new SqlCeParameter("ItemSize", SqlDbType.Int)
{
Value = item.ItemSize
},
new SqlCeParameter("ItemType", SqlDbType.Int)
{
Value = item.ItemType
},
new SqlCeParameter("StockCount", SqlDbType.Int)
{
Value = item.StockCount
},
new SqlCeParameter("StockPrice", SqlDbType.Decimal)
{
Value = item.StockPrice
},
new SqlCeParameter("Price", SqlDbType.Decimal)
{
Value = item.Price
},
new SqlCeParameter("Id", SqlDbType.NVarChar, 50)
{
Value = item.Id
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = item.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss")
},
new SqlCeParameter("Color", SqlDbType.NVarChar, 50)
{
Value = item.Color ?? ""
}
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
ret = command.ExecuteNonQuery() > 0;
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
}
finally
{
conn.Close();
}
}
return(ret);
}
public bool saveDocCheckProduct(DocCheckProduct docCheckProduct)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("INSERT INTO DocCheckProducts(DCode,Warehouse,Location,CreateDate)");
sb.Append(" VALUES (@DCode,@Warehouse,@Location,@CreateDate)");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@DCode", SqlDbType.NVarChar).Value = docCheckProduct.Code;
com.Parameters.Add("@Warehouse", SqlDbType.NVarChar).Value = docCheckProduct.Warehouse;
com.Parameters.Add("@Location", SqlDbType.NVarChar).Value = docCheckProduct.Location;
com.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = DateTime.Now;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public static bool TransferData(string sourceDatabasePath, string targetDatabasePath)
{
string sourceConnectionString = "Data Source = " + sourceDatabasePath + "; Password = "******"Data Source = " + targetDatabasePath + "; Password = "******"FirmDetails", "UnitOfMeasurement", "Items", "Customers" };
sourceConnection.Open();
targetConnection.Open();
foreach (string tableName in tableNames)
{
transaction = targetConnection.BeginTransaction();
transferTableData(sourceConnection, targetConnection, transaction, tableName);
transaction.Commit(CommitMode.Immediate);
transaction.Dispose();
}
transaction = targetConnection.BeginTransaction();
transferCustomerBalance(sourceConnection, targetConnection, transaction);
transaction.Commit();
}
}
catch (Exception ex)
{
if (transaction != null)
{
try
{
transaction.Rollback();
}
catch (Exception exception)
{
ErrorLogger.LogError(exception);
}
}
string message = "An error occurred in transferring data from the previous financial year to the new " +
"financial year.\n The error text is as follows:\n" + Global.getExceptionText(ex);
System.Media.SystemSounds.Hand.Play();
System.Windows.Forms.MessageBox.Show(message, "Error Occurred", System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Error);
ErrorLogger.LogError(ex);
return(false);
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
if (targetConnection != null)
{
targetConnection.Close();
targetConnection.Dispose();
}
}
return(true);
}
public bool saveProduct(Product product)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("INSERT INTO Products(Name,Code,BarCode,Unit,Price1,Price3,Price4,CreateDate)");
sb.Append(" VALUES (@Name,@Code,@BarCode,@Unit,@Price1,@Price3,@Price4,@CreateDate)");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@Name", SqlDbType.NVarChar).Value = product.Name;
com.Parameters.Add("@Code", SqlDbType.NVarChar).Value = product.Code;
com.Parameters.Add("@BarCode", SqlDbType.NVarChar).Value = product.BarCode;
com.Parameters.Add("@Unit", SqlDbType.NVarChar).Value = product.Unit;
com.Parameters.Add("@Price1", SqlDbType.NVarChar).Value = product.Price1;
com.Parameters.Add("@Price3", SqlDbType.NVarChar).Value = product.Price3;
com.Parameters.Add("@Price4", SqlDbType.NVarChar).Value = product.Price4;
com.Parameters.Add("@CreateDate", SqlDbType.NVarChar).Value = product.CreateDate;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
/// <summary>
/// Function to Save a CustomerEntity in the database.
/// </summary>
/// <param name="customer">CustomerEntity to save</param>
/// <param name="scope">Interna structure to avoid circular reference locks. Provide an instance when calling from other data access object.</param>
/// <exception cref="ArgumentNullException">
/// If <paramref name="customer"/> is not a <c>CustomerEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// If an DbException occurs in the try block while accessing the database.
/// </exception>
public void Save(CustomerEntity customer, Dictionary <string, IEntity> scope)
{
if (customer == null)
{
throw new ArgumentException("The argument can't be null");
}
// Create a unique key to identify the object in the internal scope
string scopeKey = customer.Id.ToString(NumberFormatInfo.InvariantInfo) + "Customer";
if (scope != null)
{
// If it's on the scope return it, don't save again
if (scope.ContainsKey(scopeKey))
{
return;
}
}
else
{
// Create a new scope if it's not provided
scope = new Dictionary <string, IEntity>();
}
try
{
// Open a DbConnection and a new transaction if it isn't on a higher level one
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
string commandName = "";
bool isUpdate = false;
// Check if it is an insert or update command
if (customer.IsNew || !DataAccessConnection.ExistsEntity(customer.Id, "Customer", "idCustomer", dbConnection, dbTransaction))
{
commandName = "INSERT INTO [Customer] (idCustomer, NAME, SURNAME, ADDRESS, PHONENUMBER, USERNAME, PASSWORD, BIRTHDAY, HOWMANYCHILDREN, GENDER, CIVILSTATE, [TIMESTAMP] ) VALUES( @idCustomer, @name,@surname,@address,@phoneNumber,@userName,@password,@birthday,@howManyChildren,@gender,@civilState, GETDATE()); ";
}
else
{
isUpdate = true;
commandName = "UPDATE [Customer] SET name = @name, surname = @surname, address = @address, phoneNumber = @phoneNumber, userName = @userName, password = @password, birthday = @birthday, howManyChildren = @howManyChildren, gender = @gender, civilState = @civilState , timestamp=GETDATE() WHERE idCustomer = @idCustomer";
}
// Create a db command
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(commandName, dbConnection, dbTransaction);
// Add parameters values to current command
SqlCeParameter parameter;
if (!isUpdate && customer.Id == 0)
{
customer.Id = DataAccessConnection.GetNextId("idCustomer", "Customer", dbConnection, dbTransaction);
}
parameter = dataAccess.GetNewDataParameter("@idCustomer", DbType.Int32);
parameter.Value = customer.Id;
sqlCommand.Parameters.Add(parameter);
FillSaveParameters(customer, sqlCommand);
// Execute the command
sqlCommand.ExecuteNonQuery();
scopeKey = customer.Id.ToString(NumberFormatInfo.InvariantInfo) + "Customer";
// Add entity to current internal scope
scope.Add(scopeKey, customer);
// Save collections of related objects to current entity
if (customer.Preferences != null)
{
this.SavePreferenceCollection(new PreferenceDataAccess(), customer, customer.Preferences, customer.IsNew, scope);
}
if (customer.DeviceProfile != null)
{
this.SaveDeviceProfileCollection(new DeviceProfileDataAccess(), customer, customer.DeviceProfile, customer.IsNew, scope);
}
// Save objects related to current entity
// Update
// Close transaction if initiated by me
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Update new and changed flags
customer.IsNew = false;
customer.Changed = false;
}
catch (DbException dbException)
{
// Rollback transaction
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Rethrow as custom exception
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Close connection if initiated by me
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
private bool saveData(out int billID)
{
string errorText;
SqlCeConnection connection = Global.getDatabaseConnection(out errorText);
SqlCeTransaction transaction = null;
try
{
using (SqlCeCommand command = connection.CreateCommand())
{
if (billToEdit.HasValue)
{
configureBillMasterUpdateCommand(command);
}
else
{
configureBillMasterInsertCommand(command);
}
transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.ExecuteNonQuery(); //save bill master data
if (!billToEdit.HasValue)
{
command.CommandText = "SELECT @@IDENTITY AS BillID";
command.Parameters.Clear();
decimal ID = (decimal)(command.ExecuteScalar());
billID = decimal.ToInt32(ID);
}
else
{
//delete bill details for the existing bill
command.CommandText = "DELETE FROM BILLDETAILS WHERE BILLID = " + billToEdit.Value;
command.ExecuteNonQuery();
billID = billToEdit.Value;
}
saveBillDetails(command, decimal.ToInt32(billID));
transaction.Commit();
}
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
string message = "An error occurred in saving the bill data in the database." +
"\nThe error text is as follows:\n" + Global.getExceptionText(ex);
SystemSounds.Hand.Play();
Cursor.Current = Cursors.Default;
MessageBox.Show(message, "Error in Saving Data", MessageBoxButtons.OK,
MessageBoxIcon.Hand);
billID = 0;
ErrorLogger.LogError(ex);
return(false);
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
}
return(true);
}
private void Loadbaza()
{
this.label1.Text = "PRZYGOTOWANIE DO KONWERSJI";
label1.Refresh();
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
string delimeter = ";";
string filename = dfile;
StreamReader sr = new StreamReader(filename);
string allData = sr.ReadToEnd();
string[] rows = allData.Split("\r\n".ToCharArray());
allData = "empty";
sr.DiscardBufferedData();
sr.Close();
cn.Open();
//if (firstimport == 1)
//{
SqlCeCommand delete = cn.CreateCommand();
delete.CommandText = "DROP TABLE dane";
delete.Prepare();
delete.ExecuteNonQuery();
SqlCeCommand cmd4 = new SqlCeCommand("CREATE TABLE dane (typ nvarchar (7), kod nvarchar (15) not null, nazwa nvarchar(40), stan nvarchar(10), cenazk nvarchar(10), cenasp nvarchar(10), vat nvarchar(5), devstat nvarchar(10), bad_cena bit, bad_stan bit, cenapolka numeric(6,3), zliczono numeric(6,3), datazmian DateTime, cenahurt nvarchar(10), cenaoryg nvarchar(10))", cn);
cmd4.ExecuteNonQuery();
cmd4 = new SqlCeCommand("CREATE INDEX kod ON dane (kod)", cn);
cmd4.ExecuteNonQuery();
//}
// SqlCeCommand cmdtest = cn.CreateCommand();
// cmdtest.CommandText = "SELECT count(*) FROM dane WHERE kod = ?";
// cmdtest.Parameters.Add("@k", SqlDbType.NVarChar, 15);
// cmdtest.Prepare();
// SqlCeCommand cmd = cn.CreateCommand();
//cmd.CommandText = "UPDATE dane SET nazwa = ?, stan = ?, cenazk = ?, cenasp = ?, vat = ?, datazmian = ?, cenahurt = ?, cenaoryg = ? WHERE kod = ?";
// cmd.CommandText = "UPDATE dane SET nazwa = ?, stan = ?, cenazk = ?, cenasp = ?, vat = ? WHERE kod = ?";
// cmd.Parameters.Add("@n", SqlDbType.NVarChar, 40);
// cmd.Parameters.Add("@s", SqlDbType.NVarChar, 20);
// cmd.Parameters.Add("@cz", SqlDbType.NVarChar, 20);
// cmd.Parameters.Add("@cs", SqlDbType.NVarChar, 20);
// cmd.Parameters.Add("@v", SqlDbType.NVarChar, 20);
//cmd.Parameters.Add("@d", SqlDbType.DateTime);
//cmd.Parameters.Add("@ch", SqlDbType.NVarChar, 20);
//cmd.Parameters.Add("@co", SqlDbType.NVarChar, 20);
// cmd.Parameters.Add("@k1", SqlDbType.NVarChar, 20);
// cmd.Prepare();
SqlCeCommand cmd1 = cn.CreateCommand();
SqlCeTransaction transaction;
transaction = cn.BeginTransaction();
cmd1.Connection = cn;
cmd1.Transaction = transaction;
try
{
//cmd1.CommandText = "INSERT INTO dane (typ, kod, nazwa, stan, cenazk, cenasp, vat, bad_cena, bad_stan, cenapolka, zliczono, datazmian, cenahurt, cenaoryg) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
cmd1.CommandText = "INSERT INTO dane (typ, kod, nazwa, stan, cenazk, cenasp, vat, bad_cena, bad_stan, cenapolka, zliczono) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
cmd1.Parameters.Add("@t", SqlDbType.NVarChar, 7);
cmd1.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmd1.Parameters.Add("@n", SqlDbType.NVarChar, 40);
cmd1.Parameters.Add("@s", SqlDbType.NVarChar, 10);
cmd1.Parameters.Add("@cz", SqlDbType.NVarChar, 10);
cmd1.Parameters.Add("@cs", SqlDbType.NVarChar, 10);
cmd1.Parameters.Add("@v", SqlDbType.NVarChar, 5);
cmd1.Parameters.Add("@bc", SqlDbType.Bit);
cmd1.Parameters.Add("@bs", SqlDbType.Bit);
cmd1.Parameters.Add("@cp", SqlDbType.Decimal, 10);
cmd1.Parameters["@cp"].Precision = 10;
cmd1.Parameters["@cp"].Scale = 3;
cmd1.Parameters.Add("@zl", SqlDbType.Decimal, 10);
cmd1.Parameters["@zl"].Precision = 10;
cmd1.Parameters["@zl"].Scale = 3;
//cmd1.Parameters.Add("@d", SqlDbType.DateTime);
// cmd1.Parameters.Add("@ch", SqlDbType.NVarChar, 10);
// cmd1.Parameters.Add("@co", SqlDbType.NVarChar, 10);
cmd1.Prepare();
/*SqlCeCommand cmd3 = cn.CreateCommand();
* cmd3.CommandText = "DELETE FROM dane WHERE kod = ?";
* cmd3.Parameters.Add("@t", SqlDbType.NVarChar, 20);
*/
label1.Text = "KONWERTOWANIE DANYCH";
label1.Refresh();
progressBar1.Maximum = (rows.Length / 2);
progressBar1.Minimum = 0;
bool val;
string input;
input = bool.FalseString;
val = bool.Parse(input);
int i = 0;
//if (firstimport == 1)
//{
foreach (string r in rows)
{
string[] items = r.Split(delimeter.ToCharArray());
if (items[0] != "")
{
/* if (items[7] == "Update")
* {
* i += 1;
* progressBar1.Value = i;
* progressBar1.Update();
* cmd.Parameters["@t"].Value = items[0];
* cmd.Parameters["@k"].Value = items[1];
* cmd.Parameters["@n"].Value = items[2];
* cmd.Parameters["@s"].Value = items[3];
* cmd.Parameters["@cz"].Value = items[4];
* cmd.Parameters["@cs"].Value = items[5];
* cmd.Parameters["@v"].Value = items[6];
* cmd.Parameters["@k1"].Value = items[1];
* cmd.ExecuteNonQuery();
* }
*/
//else if (items[7] == "New")
//{
i += 1;
progressBar1.Value = i;
progressBar1.Update();
cmd1.Parameters["@t"].Value = items[0];
cmd1.Parameters["@k"].Value = items[1];
cmd1.Parameters["@n"].Value = items[2];
cmd1.Parameters["@s"].Value = items[3];
cmd1.Parameters["@cz"].Value = items[4];
cmd1.Parameters["@cs"].Value = items[5];
cmd1.Parameters["@v"].Value = items[6];
cmd1.Parameters["@bc"].Value = val;
cmd1.Parameters["@bs"].Value = val;
cmd1.Parameters["@cp"].Value = "0";
cmd1.Parameters["@zl"].Value = "0";
//cmd1.Parameters["@d"].Value = Convert.ToDateTime(items[7]);
//cmd1.Parameters["@ch"].Value = items[8];
//cmd1.Parameters["@co"].Value = items[9];
cmd1.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (Exception ex)
{
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
}
}
//}
// else
//{
/* foreach (string r in rows)
* {
*
* string[] items = r.Split(delimeter.ToCharArray());
* if (items[0] != "")
* {
* i += 1;
* progressBar1.Value = i;
* progressBar1.Update();
*
*
* int count = 0;
* cmdtest.Parameters["@k"].Value = items[1];
*
* SqlCeDataReader dr = cmdtest.ExecuteReader();
*
* while (dr.Read())
* {
* count = dr.GetInt32(0);
* }
* dr.Close();
* if (count > 0)
* {
*
* cmd.Parameters["@n"].Value = items[2];
* cmd.Parameters["@s"].Value = items[3];
* cmd.Parameters["@cz"].Value = items[4];
* cmd.Parameters["@cs"].Value = items[5];
* cmd.Parameters["@v"].Value = items[6];
* cmd.Parameters["@d"].Value = Convert.ToDateTime(items[7]);
* cmd.Parameters["@ch"].Value = items[8];
* cmd.Parameters["@co"].Value = items[9];
* cmd.Parameters["@k1"].Value = items[1];
* cmd.ExecuteNonQuery();
* }
* else
* {
*
*
*
*
* //cmd3.Parameters["@k"].Value = items[1];
* //cmd3.ExecuteNonQuery();
*
*
* cmd1.Parameters["@t"].Value = items[0];
* cmd1.Parameters["@k"].Value = items[1];
* cmd1.Parameters["@n"].Value = items[2];
* cmd1.Parameters["@s"].Value = items[3];
* cmd1.Parameters["@cz"].Value = items[4];
* cmd1.Parameters["@cs"].Value = items[5];
* cmd1.Parameters["@v"].Value = items[6];
* cmd1.Parameters["@bc"].Value = val;
* cmd1.Parameters["@bs"].Value = val;
* cmd1.Parameters["@cp"].Value = "0";
* cmd1.Parameters["@zl"].Value = "0";
* cmd1.Parameters["@d"].Value = Convert.ToDateTime(items[7]);
* cmd1.Parameters["@ch"].Value = items[8];
* cmd1.Parameters["@co"].Value = items[9];
* cmd1.ExecuteNonQuery();
*
*
* }
* }
* }
*
*
* } //}
* /* else if (items[7] == "Err")
* {
* i += 1;
* progressBar1.Value = i;
* progressBar1.Update();
*
* cmd3.Parameters["@k"].Value = items[1];
* cmd3.ExecuteNonQuery();
*
* progressBar1.Value = i;
* progressBar1.Update();
* cmd1.Parameters["@t"].Value = items[0];
* cmd1.Parameters["@k"].Value = items[1];
* cmd1.Parameters["@n"].Value = items[2];
* cmd1.Parameters["@s"].Value = items[3];
* cmd1.Parameters["@cz"].Value = items[4];
* cmd1.Parameters["@cs"].Value = items[5];
* cmd1.Parameters["@v"].Value = items[6];
* cmd1.ExecuteNonQuery();
* }
*/
/*SqlCeCommand cmd6 = new SqlCeCommand("UPDATE opcje SET devstat = 'Ok')", cn);
* cmd.Prepare();
*
* cmd6.ExecuteNonQuery();
*/
cn.Close();
//catch
//{
// SqlCeCommand cmd6 = new SqlCeCommand("UPDATE opcje SET devstat = 'Err')", cn);
// cmd6.Prepare();
//
// cmd6.ExecuteNonQuery();
// cn.Close();
//
//}
}
/// <summary>
/// UpdateCustomer
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public static bool UpdateCustomer(Customer item)
{
var sql = @"UPDATE Customer
SET Name = @Name
,Id = @Id
,Phone = @Phone
,IM = @IM
,Remark = @Remark
,Discount = @Discount
,UpdateTime = @UpdateTime
WHERE Id = @Id";
var ret = false;
using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
{
SqlCeTransaction tx = null;
try
{
conn.Open();
tx = conn.BeginTransaction();
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("Name", SqlDbType.NVarChar, 100)
{
Value = item.Name
},
new SqlCeParameter("Id", SqlDbType.NVarChar, 50)
{
Value = item.Id
},
new SqlCeParameter("Phone", SqlDbType.NVarChar, 100)
{
Value = item.Phone
},
new SqlCeParameter("IM", SqlDbType.NVarChar, 150)
{
Value = item.IM
},
//new SqlCeParameter("Deposit", SqlDbType.Decimal) { Value = item.Deposit }, 注意去掉了 sql 的 Deposit
new SqlCeParameter("Remark", SqlDbType.NVarChar, 300)
{
Value = item.Remark
},
new SqlCeParameter("Discount", SqlDbType.Int)
{
Value = item.Discount
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = item.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss")
}
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
ret = command.ExecuteNonQuery() > 0;
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
}
finally
{
conn.Close();
}
}
return(ret);
}
public static bool TransactionSQLsWithConn(string ConnectionString, List <string> SQLs, params string[] parameters)
{
bool flag = false;
try
{
List <string[]> list = new List <string[]>();
foreach (string SQL in SQLs)
{
list.Add(SQL.Split(SplitChars, StringSplitOptions.RemoveEmptyEntries));
}
List <List <string> > list2 = new List <List <string> >();
foreach (string[] item in list)
{
List <string> list3 = new List <string>();
string[] array = item;
foreach (string text in array)
{
if (!text.Contains("@@") && text.Contains("@"))
{
list3.Add(text.Substring(text.IndexOf("@"), text.Length - text.IndexOf("@")));
}
}
list2.Add(list3);
}
using (SqlCeConnection sqlCeConnection = new SqlCeConnection(ConnectionString))
{
sqlCeConnection.Open();
SqlCeTransaction sqlCeTransaction = sqlCeConnection.BeginTransaction();
int num = 0;
string value = "";
try
{
for (int j = 0; j <= SQLs.Count - 1; j++)
{
SqlCeCommand sqlCeCommand = new SqlCeCommand(SQLs[j], sqlCeConnection);
sqlCeCommand.Transaction = sqlCeTransaction;
sqlCeCommand.Parameters.Clear();
List <string> list4 = list2[j];
for (int k = 0; k <= list4.Count - 1; k++)
{
if (j != 0 && k == 0)
{
sqlCeCommand.Parameters.AddWithValue(list4[k], value);
}
else
{
sqlCeCommand.Parameters.AddWithValue(list4[k], parameters[num]);
}
num++;
}
if (SQLs[j].Contains("@@"))
{
DataTable dataTable = new DataTable();
using (SqlCeDataAdapter sqlCeDataAdapter = new SqlCeDataAdapter(sqlCeCommand))
{
sqlCeDataAdapter.Fill(dataTable);
}
if (dataTable.Rows.Count > 0)
{
value = dataTable.Rows[0][0].ToString();
}
}
else
{
sqlCeCommand.ExecuteNonQuery();
}
}
sqlCeTransaction.Commit();
return(true);
}
catch
{
sqlCeTransaction.Rollback();
flag = false;
throw;
}
}
}
catch
{
flag = false;
throw;
}
}
//多SQL交易
public static bool TransactionSQLsWithConn(string ConnectionString, List <string> SQLs, params string[] parameters)
{
bool finished = false;
try
{
List <string[]> wordsInSQLs = new List <string[]>();
foreach (string sql in SQLs)
{
wordsInSQLs.Add(sql.Split(SplitChars, StringSplitOptions.RemoveEmptyEntries));
}
List <List <string> > paramInSQLs = new List <List <string> >();
foreach (var wordsInSQL in wordsInSQLs)
{
List <string> paramInSQL = new List <string>();
foreach (var word in wordsInSQL)
{
if (word.Contains("@@"))
{
continue;
}
if (word.Contains("@"))
{
paramInSQL.Add(word.Substring(word.IndexOf("@"), word.Length - word.IndexOf("@")));
}
}
paramInSQLs.Add(paramInSQL);
}
using (SqlCeConnection sc = new SqlCeConnection(ConnectionString))
{
sc.Open();
SqlCeTransaction trans = sc.BeginTransaction();
int parametersIndex = 0;
string identity = "";
try
{
for (int i = 0; i <= SQLs.Count - 1; i++)
{
SqlCeCommand cmd = new SqlCeCommand(SQLs[i], sc);
cmd.Transaction = trans;
//CommandTimeout 重設為30秒
//cmd.ResetCommandTimeout();
//怕下列指令執行較長,將他延長設為120秒
//cmd.CommandTimeout = 120;
cmd.Parameters.Clear();
List <string> TakeOutParamInSQL = paramInSQLs[i];
for (int j = 0; j <= TakeOutParamInSQL.Count - 1; j++)
{
if (i != 0 && j == 0)
{
cmd.Parameters.AddWithValue(TakeOutParamInSQL[j], identity);
}
else
{
cmd.Parameters.AddWithValue(TakeOutParamInSQL[j], parameters[parametersIndex]);
}
parametersIndex++;
}
if (SQLs[i].Contains("@@"))
{
DataTable dt = new DataTable();
using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
{
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
identity = dt.Rows[0][0].ToString();
}
}
else
{
cmd.ExecuteNonQuery();
}
}
trans.Commit();
finished = true;
}
catch
{
trans.Rollback();
finished = false;
throw;
}
}
}
catch
{
finished = false;
throw;
}
return(finished);
}
/// <summary>
/// InsertItemSales
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static bool InsertItemSales(List <ItemSales> list)
{
var sql = @"INSERT INTO ItemSales(ItemSalesId,ItemMasterId,ItemName,CustomerId,SalesType,SalesCount,SoldPirce,UpdateTime)
VALUES (@ItemSalesId,@ItemMasterId,@ItemName,@CustomerId,@SalesType,@SalesCount,@SoldPirce,@UpdateTime)";
var ret = false;
using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
{
SqlCeTransaction tx = null;
try
{
conn.Open();
tx = conn.BeginTransaction();
//var ItemSalesId = Guid.NewGuid().ToString();
var ItemSalesId = DateTime.Now.ToString("yyyyMMddHHmmssfff");
list.ForEach(s =>
{
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("ItemSalesId", SqlDbType.NVarChar, 50)
{
Value = ItemSalesId
},
new SqlCeParameter("ItemMasterId", SqlDbType.NVarChar, 50)
{
Value = s.Id
},
new SqlCeParameter("ItemName", SqlDbType.NVarChar, 250)
{
Value = s.ItemName
},
new SqlCeParameter("CustomerId", SqlDbType.NVarChar, 50)
{
Value = s.CustomerId ?? ""
},
new SqlCeParameter("SalesType", SqlDbType.Int)
{
Value = s.SalesType
},
new SqlCeParameter("SalesCount", SqlDbType.Int)
{
Value = s.SalesCount
},
new SqlCeParameter("SoldPirce", SqlDbType.Decimal)
{
Value = s.SoldPirce
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
},
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
command.ExecuteNonQuery();
});
var cusid = list.FirstOrDefault().CustomerId;
if (!string.IsNullOrEmpty(cusid))
{
sql = "UPDATE Customer SET Deposit = @Deposit, UpdateTime = @UpdateTime WHERE Id = @Id";
var deposit = list.FirstOrDefault().DepositForUpdate - list.Sum(s => s.SoldPirce);
if (deposit < 0)
{
deposit = 0;
}
;
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("Deposit", SqlDbType.Decimal)
{
Value = deposit
},
new SqlCeParameter("Id", SqlDbType.NVarChar, 50)
{
Value = cusid
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
},
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
command.ExecuteNonQuery();
sql = @"INSERT INTO DepositDetail (CustomerId,Detail,ItemSalesId,UpdateTime)
VALUES (@CustomerId,@Detail,@ItemSalesId,@UpdateTime)";
command = conn.CreateCommand();
parameters = new[]
{
new SqlCeParameter("CustomerId", SqlDbType.NVarChar, 50)
{
Value = cusid
},
new SqlCeParameter("Detail", SqlDbType.NVarChar, 200)
{
Value = "消费---消费前还有 " + list.FirstOrDefault().DepositForUpdate + " 元，消费了 " + list.Sum(s => s.SoldPirce) + " 元，还剩 " + deposit + " 元"
},
new SqlCeParameter("ItemSalesId", SqlDbType.NVarChar, 50)
{
Value = ItemSalesId
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
command.ExecuteNonQuery();
}
sql = @"UPDATE ItemMaster SET StockCount = (StockCount - @SalesCount), UpdateTime = @UpdateTime WHERE Id = @Id";
list.ForEach(s =>
{
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("SalesCount", SqlDbType.Int)
{
Value = s.SalesCount
},
new SqlCeParameter("Id", SqlDbType.NVarChar, 50)
{
Value = s.Id
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
},
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
command.ExecuteNonQuery();
});
tx.Commit();
ret = true;
}
catch (Exception ex)
{
tx.Rollback();
}
finally
{
conn.Close();
}
}
return(ret);
}