int inc = 0; // uses to increment or to change current row number
private void Form1_Load(object sender, EventArgs e)
{ // instantiating SqlCeConnection object
con = new System.Data.SqlServerCe.SqlCeConnection();
// holds location of the database
con.ConnectionString = "Data Source=C:\\Users\\131311399\\Documents\\databases\\Employees.sdf";
// opens connection
con.Open();
// holds SQL query
String sql = "SELECT * from tbl_employees";
// used to fill the DataSet with records from the database
da = new System.Data.SqlServerCe.SqlCeDataAdapter(sql, con);
MessageBox.Show("Connection open.");
// fills DataSet ds1 with table named "Workers"
da.Fill(ds1, "Workers");
// calls NavigateRecords() method
NavigateRecords();
// counts number of rows in DataSet ds1
MaxRows = ds1.Tables["Workers"].Rows.Count;
// calls countRecord() to display record number currently displayed
countRecord();
// closes connection
con.Close();
}
public override DbConnection OpenConnection()
{
var connection = new System.Data.SqlServerCe.SqlCeConnection(this.ConnectionString);
connection.Open();
return(connection);
}
public TblUpdate GetClientLastUpdate()
{
DateTime min = DateTime.Parse("2000/01/01");
TblUpdate novalue = new TblUpdate()
{
LastActivityLogUpdate = min,
LastClientUpdate = min,
LastConsutantUpdate = min,
LastInfoUpdate = min,
LastUserPermissionUpdate = min,
LocalActivityLogUpdate = min,
LocalClientUpdate = min,
LocalConsutantUpdate = min,
LocalInfoUpdate = min,
LocalUserUpdate = min
};
List<TblUpdate> u = new List<TblUpdate>();
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var q =
from a in db.TblUpdate
select a;
u = q.ToList();
}
if (u.FirstOrDefault() != null)
{
if (u.FirstOrDefault().LastClientUpdate != null)
return u.FirstOrDefault();
else
return novalue;
}
else
return novalue;
}
public void CopyTestWithDb()
{
string sourceFileName = @"c:\tmp\System.IO.Transactions.TxF\TestFiles\TestFile.source";
string destFileName = @"c:\tmp\System.IO.Transactions.TxF\TestFiles\TestFile.dest";
System.Data.SqlServerCe.SqlCeConnection cnDb = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=C:\tmp\System.IO.Transactions.TxF\System.IO.Transactions.TxF.Test\DatabaseTest.sdf");
System.Data.SqlServerCe.SqlCeCommand cmDb = new System.Data.SqlServerCe.SqlCeCommand("INSERT INTO TestTbl (Col_1) VALUES (GETDATE());", cnDb);
bool overwrite = true;
using (cnDb)
{
cnDb.Open();
using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope())
{
try
{
File.Copy(sourceFileName, destFileName, overwrite);
cmDb.ExecuteNonQuery();
ts.Complete();
}
catch (Exception)
{
throw;
}
finally
{ }
}
}
}
public void Dispose()
{
lock(cmdInsert)
{
if(cmdInsert != null)
{
cmdInsert.Dispose();
cmdInsert = null;
}
if(cnSet != null)
{
cnSet.Dispose();
cnSet = null;
}
}
lock(cmdFetch)
{
if(cmdFetch != null)
{
cmdFetch.Dispose();
cmdFetch = null;
}
if(cnGet != null)
{
cnGet.Dispose();
cnGet = null;
}
}
Initialized = false;
}
/// <summary>
/// Set up the SQL connections for the table adapters when creating/opening
/// a database.
/// </summary>
private void CreateConnections()
{
localDB = new DPDatabaseImportV1();
localTAManager = new DPDatabaseImportV1TableAdapters.TableAdapterManager();
localTAManager.ChatLogTableAdapter = new DPDatabaseImportV1TableAdapters.ChatLogTableAdapter();
// DVS/DirectParse uses SQLCE 3.1, so we can always do an upgrade here.
System.Data.SqlServerCe.SqlCeEngine sqlCeEngine = new System.Data.SqlServerCe.SqlCeEngine(databaseConnectionString);
// Creates a 0-byte file. We need the name, but don't want the file to exist.
tempDatabaseName = Path.GetTempFileName();
File.Delete(tempDatabaseName);
string tempConnectionString = string.Format("Data Source={0}", tempDatabaseName);
sqlCeEngine.Upgrade(tempConnectionString);
Properties.Settings.Default.Properties["DvsParse_SaveConnectionString"].DefaultValue = tempConnectionString;
System.Data.SqlServerCe.SqlCeConnection sqlConn =
new System.Data.SqlServerCe.SqlCeConnection(tempConnectionString);
localTAManager.Connection = sqlConn;
localTAManager.ChatLogTableAdapter.Connection = sqlConn;
localTAManager.ChatLogTableAdapter.Fill(localDB.ChatLog);
}
public void Dispose()
{
lock (cmdInsert)
{
if (cmdInsert != null)
{
cmdInsert.Dispose();
cmdInsert = null;
}
if (cnSet != null)
{
cnSet.Dispose();
cnSet = null;
}
}
lock (cmdFetch)
{
if (cmdFetch != null)
{
cmdFetch.Dispose();
cmdFetch = null;
}
if (cnGet != null)
{
cnGet.Dispose();
cnGet = null;
}
}
Initialized = false;
}
/// <summary>
/// Set up the SQL connections for the table adapters when creating/opening
/// a database.
/// </summary>
private void CreateConnections()
{
localDB = new KPDatabaseReadOnly();
localTAManager = new KPDatabaseReadOnlyTableAdapters.TableAdapterManager();
localTAManager.RecordLogTableAdapter = new KPDatabaseReadOnlyTableAdapters.RecordLogTableAdapter();
localTAManager.VersionTableAdapter = new KPDatabaseReadOnlyTableAdapters.VersionTableAdapter();
localTAManager.CombatantsTableAdapter = new KPDatabaseReadOnlyTableAdapters.CombatantsTableAdapter();
System.Data.SqlServerCe.SqlCeConnection sqlConn =
new System.Data.SqlServerCe.SqlCeConnection(databaseConnectionString);
localTAManager.Connection = sqlConn;
localTAManager.RecordLogTableAdapter.Connection = sqlConn;
localTAManager.VersionTableAdapter.Connection = sqlConn;
localTAManager.CombatantsTableAdapter.Connection = sqlConn;
// If opening an existing database, need to check version info before filling data
localTAManager.RecordLogTableAdapter.Fill(localDB.RecordLog);
localTAManager.VersionTableAdapter.Fill(localDB.Version);
localTAManager.CombatantsTableAdapter.Fill(localDB.Combatants);
}
public void CleanProducts()
{
using (System.Data.SqlServerCe.SqlCeConnection conn =
new System.Data.SqlServerCe.SqlCeConnection(
Program.Default.ProductsConnectionString))
{
conn.Open();
using (System.Data.SqlServerCe.SqlCeCommand cmd
= new System.Data.SqlServerCe.SqlCeCommand())
{
cmd.Connection = conn;
cmd.CommandText = "delete from productsBinTbl";
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from productsTbl";
cmd.ExecuteNonQuery();
}
this.ProductsTbl.Clear();
this.ProductsBinTbl.Clear();
this.AcceptChanges();
}
using (System.Data.SqlServerCe.SqlCeEngine engine
= new System.Data.SqlServerCe.SqlCeEngine(Program.Default.ProductsConnectionString))
{
engine.Shrink();
}
}
/// <summary>
/// 根据配置创建数据库连接
/// </summary>
/// <returns></returns>
public IDbConnection CreateSqlConnection()
{
IDbConnection connection = null;
switch (_dbType)
{
case DBType.SqlServer:
connection = new System.Data.SqlClient.SqlConnection(_conStr);
break;
case DBType.MySql:
connection = new MySql.Data.MySqlClient.MySqlConnection(_conStr);
break;
case DBType.Oracle:
connection = new Oracle.DataAccess.Client.OracleConnection(_conStr);
break;
case DBType.SqlCe:
connection = new System.Data.SqlServerCe.SqlCeConnection(_conStr);
break;
case DBType.SQLite:
connection = new System.Data.SQLite.SQLiteConnection(_conStr);
break;
}
return(connection);
}
/// <summary>
/// 工厂模式 根据配置创建数据库连接
/// </summary>
/// <returns></returns>
public static IDbConnection CreateSqlCon()
{
IDbConnection connection = null;
string conStr = PubConstant.ConnectionString;
switch (PubConstant.DbType.ToLower())
{
case "mssql":
connection = new System.Data.SqlClient.SqlConnection(conStr);
break;
case "mysql":
connection = new MySql.Data.MySqlClient.MySqlConnection(conStr);
break;
case "oracle":
connection = new System.Data.OracleClient.OracleConnection(conStr);
break;
case "sqlce":
connection = new System.Data.SqlServerCe.SqlCeConnection(conStr);
break;
case "sqlite":
connection = new System.Data.SQLite.SQLiteConnection(conStr);
break;
}
return(connection);
}
/// <summary>
/// Creates the ADO.NET connection.
/// </summary>
/// <param name="parameter">The connection parameter.</param>
/// <returns>An open ADO connection.</returns>
internal System.Data.SqlServerCe.SqlCeConnection CreateAdoConnection(DbConnectionParameter parameter)
{
string connectionString = GetConnectionString(parameter);
Trace.WriteLineIf(parameter.VerbositySwitch.TraceVerbose, "Connecting to SQL CE database using following connection string:\r\n" + connectionString);
System.Data.SqlServerCe.SqlCeConnection connection = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
return(connection);
}
public TblClient InsertOrUpdate(TblClient data)
{
Exception cex = null;
TblClient res = null;
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var matchedObj = (from c in db.TblClient
where c.ClientID == data.ClientID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblClient> TblObj = db.TblClient;
data.UpdateDate = DateTime.Now;
data.IsDeleted = 0;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res = data;
}
catch (Exception ex)
{
cex = ex;
}
}
else
{
try
{
matchedObj.ClientCode = data.ClientCode;
matchedObj.ClientName = data.ClientName;
matchedObj.Descriptions = data.Descriptions;
matchedObj.LocationCode = data.LocationCode;
matchedObj.LocationName = data.LocationName;
matchedObj.UpdateDate = DateTime.Now;
matchedObj.IsDeleted = data.IsDeleted;
db.SubmitChanges();
res = matchedObj;
}
catch (Exception ex)
{
cex = ex;
}
}
}
if (cex != null)
throw cex;
else
return res;
}
/// <summary>
/// 應用程式的主要進入點。
/// </summary>
static void Main()
{
System.Data.SqlServerCe.SqlCeConnection cn = new System.Data.SqlServerCe.SqlCeConnection(LedClientService.Properties.Settings.Default.DbConnectionString);
ServiceBase[] ServicesToRun;
ServicesToRun = new ServiceBase[]
{
new Service1()
};
ServiceBase.Run(ServicesToRun);
}
public IEnumerable<TblConsultantLog> GetAll()
{
List<TblConsultantLog> u = new List<TblConsultantLog>();
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var q =
from a in db.TblConsultantLog
select a;
u = q.ToList();
}
return u;
}
static public bool CheckDatabase()
{
try
{
System.Data.SqlServerCe.SqlCeConnection connection = new System.Data.SqlServerCe.SqlCeConnection(classSQL.connectionString);
connection.Open();
return(true);
}
catch (Exception)
{
return(false);
}
}
/// <summary>
/// Set up the SQL connections for the table adapters when creating/opening
/// a database.
/// </summary>
private void CreateConnections()
{
localDB = new KPDatabaseDataSet();
localTAManager = new TableAdapterManager();
localTAManager.CombatantsTableAdapter = new CombatantsTableAdapter();
localTAManager.BattlesTableAdapter = new BattlesTableAdapter();
localTAManager.InteractionsTableAdapter = new InteractionsTableAdapter();
localTAManager.ActionsTableAdapter = new ActionsTableAdapter();
localTAManager.LootTableAdapter = new LootTableAdapter();
localTAManager.ItemsTableAdapter = new ItemsTableAdapter();
localTAManager.RecordLogTableAdapter = new RecordLogTableAdapter();
localTAManager.VersionTableAdapter = new VersionTableAdapter();
localTAManager.ChatMessagesTableAdapter = new ChatMessagesTableAdapter();
localTAManager.ChatSpeakersTableAdapter = new ChatSpeakersTableAdapter();
System.Data.SqlServerCe.SqlCeConnection sqlConn =
new System.Data.SqlServerCe.SqlCeConnection(databaseConnectionString);
localTAManager.Connection = sqlConn;
localTAManager.CombatantsTableAdapter.Connection = sqlConn;
localTAManager.BattlesTableAdapter.Connection = sqlConn;
localTAManager.InteractionsTableAdapter.Connection = sqlConn;
localTAManager.ActionsTableAdapter.Connection = sqlConn;
localTAManager.LootTableAdapter.Connection = sqlConn;
localTAManager.ItemsTableAdapter.Connection = sqlConn;
localTAManager.ChatMessagesTableAdapter.Connection = sqlConn;
localTAManager.ChatSpeakersTableAdapter.Connection = sqlConn;
localTAManager.RecordLogTableAdapter.Connection = sqlConn;
localTAManager.VersionTableAdapter.Connection = sqlConn;
// If opening an existing database, need to check version info before filling data
localTAManager.CombatantsTableAdapter.Fill(localDB.Combatants);
localTAManager.BattlesTableAdapter.Fill(localDB.Battles);
localTAManager.InteractionsTableAdapter.Fill(localDB.Interactions);
localTAManager.ActionsTableAdapter.Fill(localDB.Actions);
localTAManager.LootTableAdapter.Fill(localDB.Loot);
localTAManager.ItemsTableAdapter.Fill(localDB.Items);
localTAManager.ChatMessagesTableAdapter.Fill(localDB.ChatMessages);
localTAManager.ChatSpeakersTableAdapter.Fill(localDB.ChatSpeakers);
localTAManager.RecordLogTableAdapter.Fill(localDB.RecordLog);
localTAManager.VersionTableAdapter.Fill(localDB.Version);
}
/* This method perform connection to the database
*
* and pull records from the database into a DataSet object
*/
private System.Data.DataSet MyDataSet()
{
System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection(strCon);
con.Open();
da_1 = new System.Data.SqlServerCe.SqlCeDataAdapter(sql_string, con);
dat_set = new System.Data.DataSet();
da_1.Fill(dat_set, "Default Table");
con.Close();
return(dat_set);
}
static void Spike2(string[] args)
{
using (var db = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=..\..\..\Nespe\App_Data\ncom.sdf"))
{
db.Open();
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "select 1 ";
var v = cmd.ExecuteScalar();
Console.WriteLine(v);
//using (var dr = cmd.ExecuteReader()) {
//}
}
}
}
static public void ReConnect()
{
if (sqlConnection == null)
{
sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
sqlConnection.Open();
}
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
else if (sqlConnection.State == ConnectionState.Broken)
{
sqlConnection.Close();
sqlConnection.Open();
}
}
private void testButton_Click(object sender, EventArgs e)
{
try
{
using (System.Data.SqlServerCe.SqlCeConnection connection = new System.Data.SqlServerCe.SqlCeConnection(ConnectionString))
{
connection.Open();
MessageBox.Show(this, "Connection Test Was Successful.",
"Connection Test", MessageBoxButtons.OK, MessageBoxIcon.Information);
connection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(this, "Connection Test Failed." + Environment.NewLine + Environment.NewLine + ex.GetBaseException().Message,
"Connection Test", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
static void Spike3(string[] args)
{
using (var cnn = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=..\..\..\Nespe\App_Data\nespe-ncom.sdf"))
{
using (var db = new Nespe.Context.NespeDbContext(cnn, true)) {
db.Database.CreateIfNotExists();
db.Database.Initialize(true);
var drc = db.DepartmentSet;
drc.Add(new Models.Department { Name="IT", Description="Information Technologie"});
drc.Add(new Models.Department { Name = "IT-Dev", Description = "Information Technologie Development" });
drc.Add(new Models.Department { Name = "IT-Infra", Description = "Information Technologie Infrastructures" });
db.SaveChanges();
var q = (from t in drc select t);
foreach (var dr in q) {
Console.WriteLine("{0}", dr.Name);
}
}
}
}
private void ReadEncryptedMountains()
{
System.Data.SqlServerCe.SqlCeConnection connection
= new System.Data.SqlServerCe.SqlCeConnection(string.Format("Data Source = {0}; max database size = 4091", sdfFileLocation.Text));
System.Data.SqlServerCe.SqlCeCommand command =
new System.Data.SqlServerCe.SqlCeCommand();
command.Connection = connection;
command.CommandText = "SELECT * FROM IRIMountain1";
connection.Open();
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start();
var reader = command.ExecuteReader();
List <MountainStructure> results = new List <MountainStructure>();
List <Microsoft.SqlServer.Types.SqlGeometry> geometries = new List <Microsoft.SqlServer.Types.SqlGeometry>();
while (reader.Read())
{
int id = (int)reader[0];
byte[] geometry = (byte[])reader[1];
MountainStructure temp = IRI.Ket.IO.BinaryStream.ByteArrayToStructure <MountainStructure>((byte[])reader[2]);
geometries.Add(Microsoft.SqlServer.Types.SqlGeometry.STGeomFromWKB(
new System.Data.SqlTypes.SqlBytes(geometry), 0));
results.Add(temp);
}
connection.Close();
watch.Stop();
var t = watch.ElapsedMilliseconds;
}
public bool Delete(TblInfor data)
{
Exception cex = null;
bool res = false;
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var matchedObj = (from c in db.TblInfor
where c.ID == data.ID
select c).SingleOrDefault();
if (matchedObj != null)
{
try
{
// does not exist
db.TblInfor.DeleteOnSubmit(matchedObj);
db.SubmitChanges();
res = true;
}
catch (Exception ex)
{
cex = ex;
res = false;
}
}
else
{
res = true;
}
}
if (cex != null)
throw cex;
else
return res;
}
private void PopulateDataSets()
{
System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection();
System.Data.SqlServerCe.SqlCeDataAdapter daHangmanDB;
DataRow drow;
DataSet dsWordlist = new DataSet();
con.ConnectionString = "Data Source=HangmanDB.sdf";
con.Open();
daHangmanDB = new System.Data.SqlServerCe.SqlCeDataAdapter("SELECT * From Wordlist", con);
daHangmanDB.Fill(dsWordlist, "Wordlist");
con.Close();
for (int i = 0; i < dsWordlist.Tables["Wordlist"].Rows.Count; ++i)
{
drow = dsWordlist.Tables["Wordlist"].Rows[i];
wordlist.Add(new WordInfo { ID = System.Convert.ToInt32(drow[0]), Cat = drow[1].ToString(), Words = drow[2].ToString() });
}
HashSet<String> categories = new HashSet<String>(); //unique list of categories
foreach (WordInfo wordinf in wordlist) categories.Add(wordinf.Cat);
comboBoxCategories.DataSource = categories.ToList();
return;
}
public override DbConnection OpenConnection()
{
var connection = new System.Data.SqlServerCe.SqlCeConnection(this.ConnectionString);
connection.Open();
return connection;
}
private void InitConnection()
{
this._connection = new System.Data.SqlServerCe.SqlCeConnection();
this._connection.ConnectionString = ("Data Source ="
+ (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\VoiceNotes.sdf;"));
}
/// <summary>
/// inits connection to server
/// </summary>
/// <returns></returns>
public bool Initialize()
{
if (!Initialized)
{
#region prepare mssql & cache table
try
{
// precrete dir
if (!Directory.Exists(gtileCache))
{
Directory.CreateDirectory(gtileCache);
}
string connectionString = string.Format("Data Source={0}Data.sdf", gtileCache);
if (!File.Exists(gtileCache + "Data.sdf"))
{
using (System.Data.SqlServerCe.SqlCeEngine engine = new System.Data.SqlServerCe.SqlCeEngine(connectionString))
{
engine.CreateDatabase();
}
try
{
using (SqlConnection c = new SqlConnection(connectionString))
{
c.Open();
using (SqlCommand cmd = new SqlCommand(
"CREATE TABLE [GMapNETcache] ( \n"
+ " [Type] [int] NOT NULL, \n"
+ " [Zoom] [int] NOT NULL, \n"
+ " [X] [int] NOT NULL, \n"
+ " [Y] [int] NOT NULL, \n"
+ " [Tile] [image] NOT NULL, \n"
+ " CONSTRAINT [PK_GMapNETcache] PRIMARY KEY (Type, Zoom, X, Y) \n"
+ ")", c))
{
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
try
{
File.Delete(gtileCache + "Data.sdf");
}
catch
{
}
throw ex;
}
}
// different connections so the multi-thread inserts and selects don't collide on open readers.
this.cnGet = new SqlConnection(connectionString);
this.cnGet.Open();
this.cnSet = new SqlConnection(connectionString);
this.cnSet.Open();
this.cmdFetch = new SqlCommand("SELECT [Tile] FROM [GMapNETcache] WITH (NOLOCK) WHERE [X]=@x AND [Y]=@y AND [Zoom]=@zoom AND [Type]=@type", cnGet);
this.cmdFetch.Parameters.Add("@x", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@y", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@zoom", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@type", System.Data.SqlDbType.Int);
this.cmdFetch.Prepare();
this.cmdInsert = new SqlCommand("INSERT INTO [GMapNETcache] ( [X], [Y], [Zoom], [Type], [Tile] ) VALUES ( @x, @y, @zoom, @type, @tile )", cnSet);
this.cmdInsert.Parameters.Add("@x", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@y", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@zoom", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@type", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@tile", System.Data.SqlDbType.Image); //, calcmaximgsize);
//can't prepare insert because of the IMAGE field having a variable size. Could set it to some 'maximum' size?
Initialized = true;
}
catch (Exception ex)
{
Initialized = false;
}
#endregion
}
return(Initialized);
}
/// <summary>
/// inits connection to server
/// </summary>
/// <returns></returns>
public bool Initialize()
{
if(!Initialized)
{
#region prepare mssql & cache table
try
{
// precrete dir
if(!Directory.Exists(gtileCache))
{
Directory.CreateDirectory(gtileCache);
}
string connectionString = string.Format("Data Source={0}Data.sdf", gtileCache);
if(!File.Exists(gtileCache + "Data.sdf"))
{
using(System.Data.SqlServerCe.SqlCeEngine engine = new System.Data.SqlServerCe.SqlCeEngine(connectionString))
{
engine.CreateDatabase();
}
try
{
using(SqlConnection c = new SqlConnection(connectionString))
{
c.Open();
using(SqlCommand cmd = new SqlCommand(
"CREATE TABLE [GMapNETcache] ( \n"
+ " [Type] [int] NOT NULL, \n"
+ " [Zoom] [int] NOT NULL, \n"
+ " [X] [int] NOT NULL, \n"
+ " [Y] [int] NOT NULL, \n"
+ " [Tile] [image] NOT NULL, \n"
+ " CONSTRAINT [PK_GMapNETcache] PRIMARY KEY (Type, Zoom, X, Y) \n"
+ ")", c))
{
cmd.ExecuteNonQuery();
}
}
}
catch(Exception ex)
{
try
{
File.Delete(gtileCache + "Data.sdf");
}
catch
{
}
throw ex;
}
}
// different connections so the multi-thread inserts and selects don't collide on open readers.
this.cnGet = new SqlConnection(connectionString);
this.cnGet.Open();
this.cnSet = new SqlConnection(connectionString);
this.cnSet.Open();
this.cmdFetch = new SqlCommand("SELECT [Tile] FROM [GMapNETcache] WITH (NOLOCK) WHERE [X]=@x AND [Y]=@y AND [Zoom]=@zoom AND [Type]=@type", cnGet);
this.cmdFetch.Parameters.Add("@x", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@y", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@zoom", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@type", System.Data.SqlDbType.Int);
this.cmdFetch.Prepare();
this.cmdInsert = new SqlCommand("INSERT INTO [GMapNETcache] ( [X], [Y], [Zoom], [Type], [Tile] ) VALUES ( @x, @y, @zoom, @type, @tile )", cnSet);
this.cmdInsert.Parameters.Add("@x", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@y", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@zoom", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@type", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@tile", System.Data.SqlDbType.Image); //, calcmaximgsize);
//can't prepare insert because of the IMAGE field having a variable size. Could set it to some 'maximum' size?
Initialized = true;
}
catch(Exception ex)
{
Initialized = false;
Debug.WriteLine(ex.Message);
}
#endregion
}
return Initialized;
}
static void Spike4(string[] args)
{
using (var cnn = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=C:\Users\RDAirasMa\Desktop\Nespe\Nespe\Nespe\App_Data\ncom.sdf"))
{
//cnn.Open();
var db = new NespeDbContext(cnn, true);
db.Database.CreateIfNotExists();
var tc = db.RequestSet;
var dr = tc.Create();
dr.NameNC = "MArtin";
dr.SurnameNC = "Airas";
db.SaveChanges();
}
}
static void Spike4(string[] args)
{
using (var cnn = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=..\..\..\Nespe\App_Data\nespe-ncom.sdf"))
{
using (var db = new Nespe.Context.NespeDbContext(cnn, true))
{
db.Database.CreateIfNotExists();
db.Database.Initialize(true);
var q = (from t in db.RequestSet select t);
foreach (var dr in q)
{
Console.WriteLine("{0}", dr.Person.FirstName);
}
}
}
}
public TblInfor InsertOrUpdate(TblInfor data)
{
Exception cex = null;
TblInfor res = null;
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var matchedObj = (from c in db.TblInfor
where c.ID == data.ID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblInfor> TblObj = db.TblInfor;
data.UpdateDate = DateTime.Now;
data.IsDeleted = 0;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res = data;
}
catch (Exception ex)
{
cex = ex;
}
}
else
{
try
{
matchedObj.BeginDate = data.BeginDate;
matchedObj.CreateDate = data.CreateDate;
matchedObj.CreateUserID = data.CreateUserID;
matchedObj.ExprieDate = data.ExprieDate;
matchedObj.InfoContentHtml = data.InfoContentHtml;
matchedObj.InfoContent = data.InfoContent;
matchedObj.InfoTag = data.InfoTag;
matchedObj.InfoTitle = data.InfoTitle;
matchedObj.InfoType = data.InfoType;
matchedObj.Location = data.Location;
matchedObj.Category = data.Category;
matchedObj.Status = data.Status;
matchedObj.UpdateDate = DateTime.Now;
matchedObj.IsDeleted = data.IsDeleted;
db.SubmitChanges();
res = matchedObj;
}
catch (Exception ex)
{
cex = ex;
}
}
}
if (cex != null)
throw cex;
else
return res;
}
static void Spike7(string[] args)
{
//WorkflowInvoker.Invoke(new Workflow1());
using (var cnn = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=C:\Users\RDAirasMa\Desktop\Nespe\Nespe\Nespe\App_Data\ncom.sdf"))
{
//cnn.Open();
var db = new NespeDbContext(cnn, true);
//var db = new NespeDbContext();
db.Database.CreateIfNotExists();
var tc = db.RequestInfoSet;
var dr = tc.Create();
dr.Name = "Demande IT";
dr.Description = "Demade par le formulaire";
db.SaveChanges();
}
}
static void Spike_NespeDataContext_ExternalConnection_Owned(string[] args)
{
var cs = System.Configuration.ConfigurationManager.ConnectionStrings["NespeDataContext"];
using (var cnn = new System.Data.SqlServerCe.SqlCeConnection(cs.ConnectionString))
{
using (var ctx = new Nespe.Data.Context.NespeDataContext(cnn, true))
{
ctx.Database.CreateIfNotExists();
foreach (var dp in ctx.DepartmentSet)
{
Console.WriteLine(dp);
};
}
}
}
public static void ReConnect()
{
if (sqlConnection == null)
{
sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
sqlConnection.Open();
}
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
else if (sqlConnection.State == ConnectionState.Broken)
{
sqlConnection.Close();
sqlConnection.Open();
}
}
private static void Spike_SqlCeConnection_With_ConnectionString(string[] args)
{
var cs = System.Configuration.ConfigurationManager.ConnectionStrings["NespeDataContext"];
using (var cnn = new System.Data.SqlServerCe.SqlCeConnection(cs.ConnectionString))
{
using (var cmd = cnn.CreateCommand())
{
cmd.CommandText = "select 1+1";
cnn.Open();
var v = cmd.ExecuteScalar();
Console.WriteLine(v);
}
}
}
static void Spike_SqlCeConnection(string[] args)
{
using (var cnn = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=D:\workspace\_NESPE\Nespe\data\db_nespe.sdf"))
{
using (var cmd = cnn.CreateCommand())
{
cmd.CommandText = "select 1+1";
cnn.Open();
var v=cmd.ExecuteScalar();
Console.WriteLine(v);
}
}
}
public TblConsultantLog InsertOrUpdate(TblConsultantLog data)
{
Exception cex = null;
TblConsultantLog res = null;
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var matchedObj = (from c in db.TblConsultantLog
where c.ID == data.ID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblConsultantLog> TblObj = db.TblConsultantLog;
data.UpdateDate = DateTime.Now;
data.SubmitDate = DateTime.Now;
data.IsDeleted = 0;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res = data;
}
catch (Exception ex)
{
cex = ex;
}
}
else
{
try
{
matchedObj.AnswerRef = data.AnswerRef;
matchedObj.CreateDate = data.CreateDate;
matchedObj.ClientCode = data.ClientCode;
matchedObj.ClientID = data.ClientID;
matchedObj.ClientName = data.ClientName;
matchedObj.ConsType = data.ConsType;
matchedObj.CreateDate = data.CreateDate;
matchedObj.Descriptions = data.Descriptions;
matchedObj.Status = data.Status;
matchedObj.FinishDate = data.FinishDate;
matchedObj.FinishType = data.FinishType;
matchedObj.IsDeleted = data.IsDeleted;
matchedObj.Question = data.Question;
matchedObj.SubmitDate = data.SubmitDate;
matchedObj.TargetCount = data.TargetCount;
matchedObj.SubmitDate = data.SubmitDate;
matchedObj.TargetCountry = data.TargetCountry;
matchedObj.TargetEmail = data.TargetEmail;
matchedObj.TargetName = data.TargetName;
matchedObj.TargetPhoneNumber = data.TargetPhoneNumber;
matchedObj.TargetType = data.TargetType;
matchedObj.UserID = data.UserID;
matchedObj.UserName = data.UserName;
matchedObj.UpdateDate = DateTime.Now;
db.SubmitChanges();
res = matchedObj;
}
catch (Exception ex)
{
cex = ex;
}
}
}
if (cex != null)
throw cex;
else
return res;
}
static void Main()
{
using (System.Data.SqlServerCe.SqlCeConnection conn =
new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ProductsConnectionString))
{
conn.Open();
using (System.Data.SqlServerCe.SqlCeCommand cmd =
new System.Data.SqlServerCe.SqlCeCommand("select * from productsbintbl", conn))
{
using (System.Data.SqlServerCe.SqlCeCommand cmdUpd =
new System.Data.SqlServerCe.SqlCeCommand(@"UPDATE ProductsBinTbl SET ActionCode = @ac
, Shablon = @sc
, SoundCode = @sndc
WHERE (ProductsBinTbl.Barcode = @b)", conn))
{
cmdUpd.Parameters.Add("@ac", typeof(byte));
cmdUpd.Parameters.Add("@sc", typeof(int));
cmdUpd.Parameters.Add("@sndc", typeof(int));
cmdUpd.Parameters.Add("@b", typeof(Int64));
System.Random r = new Random();
Array vals = Enum.GetValues(typeof(TSDUtils.ActionCode));
Array vals1 = Enum.GetValues(typeof(TSDUtils.ShablonCode));
using (System.Data.SqlServerCe.SqlCeDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Int64 bc = (Int64)rdr[0];
cmdUpd.Parameters[3].Value = bc;
byte c = 0;
for (int k=0;k<4;k++)
{
int b = 0;
Double d = Math.Round(r.NextDouble());
b = (byte)((byte)vals.GetValue(k) * ((byte)d));
c = (byte)(b|c);
}
uint sum = 0;
for (byte k = 0; k < 8; k++)
{
byte d1 = (byte)Math.Round(r.NextDouble() * 8);
byte b1 = (byte)(1 << k);//Math.Pow(2, k);
byte b = (byte)(c & b1);
if (b != 0)
{
uint b2 = (uint)(d1 << (3 * k));
sum += b2;
}
/*Double d = Math.Round(r.NextDouble());
b = (byte)((byte)vals1.GetValue(k) * ((byte)d));*/
//c = (byte)(b*Math.Pow( | c);
}
cmdUpd.Parameters[0].Value = c;
cmdUpd.Parameters[1].Value = sum;
cmdUpd.Parameters[2].Value = sum;
cmdUpd.ExecuteNonQuery();
}
}
}
}
}
return;
//TSDUtils.ActionCode a = TSDUtils.ActionCode.Remove | TSDUtils.ActionCode.Reprice;
//TSDUtils.ActionCode b = TSDUtils.ActionCode.Remove | TSDUtils.ActionCode.Returns;
//TSDUtils.ActionCode c = TSDUtils.ActionCode.Remove | TSDUtils.ActionCode.Returns | TSDUtils.ActionCode.Reprice;
/*Array e = Enum.GetValues(typeof(TSDUtils.ActionCode));
int counter = 0;
byte[] bArray = new byte[e.Length];
string s = string.Empty;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
foreach (TSDUtils.ActionCode i in e)
{
byte b = (byte)i;
bArray[counter++] = b;
sb.AppendFormat("{0} = {1} \n", i, b);
}
TSDUtils.ActionCode tmp = TSDUtils.ActionCode.NoAction;
for (int i = 0; i < bArray.Length; i++)
{
tmp = TSDUtils.ActionCode.NoAction;
for (int j = 0; j < bArray.Length; j++)
{
if (bArray[i] == bArray[j])
continue;
tmp = tmp |(TSDUtils.ActionCode) bArray[j];
byte b = (byte)tmp;
sb.AppendFormat("{0} = {1} \n", tmp, b);
}
}
*/
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
mainForm = new Form1();
//проверка на наличие второй запущеной копии программы
//если Null значит уже запущена другая копия
if (mainForm.mutex != null)
{
//другая копия программы не запущена
//инициализируем IPC сервер, который может принимать сообщения
//(в данном случае нужно для получения сообщения от второй копии показать главное окно
IpcChannel serverChannel =
new IpcChannel("localhost:9090");
System.Runtime.Remoting.Channels.ChannelServices.RegisterChannel(
serverChannel,false);
System.Runtime.Remoting.WellKnownServiceTypeEntry WKSTE =
new System.Runtime.Remoting.WellKnownServiceTypeEntry(
typeof(RemoteObject), "RemoteObject.rem", System.Runtime.Remoting.WellKnownObjectMode.Singleton);
System.Runtime.Remoting.RemotingConfiguration.RegisterWellKnownServiceType(WKSTE);
Application.Run(mainForm);//запуск главного экранного потока
}
else
{
//есть уже запущенная копия программы
IpcChannel channel = new IpcChannel();
System.Runtime.Remoting.Channels.ChannelServices.RegisterChannel(channel,false);
//получаем адрес сервера программы
RemoteObject service = (RemoteObject)Activator.GetObject(
typeof(RemoteObject), "ipc://localhost:9090/RemoteObject.rem");
//отправляем сообщение показать главное окно
service.Show();
//mainForm.Activate();
//выходим из программы
}
}
private void m_TransferButton_Click(object sender, EventArgs e)
{
//get Bacnet selection
if (m_list.SelectedItems.Count <= 0)
{
MessageBox.Show(this, "Please select a device", "No device selected", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
KeyValuePair <BacnetAddress, uint> device = (KeyValuePair <BacnetAddress, uint>)m_list.SelectedItems[0].Tag;
//open database connection
System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=..\..\SampleDatabase.sdf");
con.Open();
//retrieve list of 'properties'
IList <BacnetValue> value_list;
bacnet_client.ReadPropertyRequest(device.Key, new BacnetObjectId(BacnetObjectTypes.OBJECT_DEVICE, device.Value), BacnetPropertyIds.PROP_OBJECT_LIST, out value_list);
LinkedList <BacnetObjectId> object_list = new LinkedList <BacnetObjectId>();
foreach (BacnetValue value in value_list)
{
if (Enum.IsDefined(typeof(BacnetObjectTypes), ((BacnetObjectId)value.Value).Type))
{
object_list.AddLast((BacnetObjectId)value.Value);
}
}
//go through all 'properties' and store their 'present data' into a SQL database
foreach (BacnetObjectId object_id in object_list)
{
//read all properties
IList <BacnetValue> values = null;
try
{
if (!bacnet_client.ReadPropertyRequest(device.Key, object_id, BacnetPropertyIds.PROP_PRESENT_VALUE, out values))
{
MessageBox.Show(this, "Couldn't fetch 'present value' for object: " + object_id.ToString());
continue;
}
}
catch (Exception)
{
//perhaps the 'present value' is non existing - ignore
continue;
}
//store in DB
using (System.Data.SqlServerCe.SqlCeCommand com = new System.Data.SqlServerCe.SqlCeCommand("INSERT INTO SampleTable VALUES(@ObjectName,@PropertyId,@Value)", con))
{
com.Parameters.AddWithValue("@ObjectName", object_id.ToString());
com.Parameters.AddWithValue("@PropertyId", values[0].Tag.ToString());
com.Parameters.AddWithValue("@Value", values[0].Value.ToString());
com.ExecuteNonQuery();
}
}
//close DB
con.Close();
//done
MessageBox.Show(this, "Done!", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
