public void MultiRSSqlCE()
{
if (File.Exists("Test.sdf"))
File.Delete("Test.sdf");
var cnnStr = "Data Source = Test.sdf;";
var engine = new SqlCeEngine(cnnStr);
engine.CreateDatabase();
using (var cnn = new SqlCeConnection(cnnStr))
{
cnn.Open();
cnn.Execute("create table Posts (ID int, Title nvarchar(50), Body nvarchar(50), AuthorID int)");
cnn.Execute("create table Authors (ID int, Name nvarchar(50))");
cnn.Execute("insert Posts values (1,'title','body',1)");
cnn.Execute("insert Posts values(2,'title2','body2',null)");
cnn.Execute("insert Authors values(1,'sam')");
var data = cnn.Query<PostCE, AuthorCE, PostCE>(@"select * from Posts p left join Authors a on a.ID = p.AuthorID", (post, author) => { post.Author = author; return post; }).ToList();
var firstPost = data.First();
firstPost.Title.IsEqualTo("title");
firstPost.Author.Name.IsEqualTo("sam");
data[1].Author.IsNull();
cnn.Close();
}
}
public LocalDatabase(string FilePath)
: base()
{
try
{
string fileName = Path.Combine(FilePath, "caselist.sdf");
connectionString = string.Format("Data Source=\"{0}\"", fileName);
SqlCeEngine en = new SqlCeEngine(connectionString);
if (!File.Exists(fileName))
{
en.CreateDatabase();
}
m_connection = new SqlCeConnection();
m_connection.ConnectionString = connectionString;
if (m_connection.State == System.Data.ConnectionState.Closed)
{
try
{
m_connection.Open();
}
catch (SqlCeInvalidDatabaseFormatException)
{
en.Upgrade();
m_connection.Open();
}
}
SqlCeCommand SelectTableCommand = new SqlCeCommand();
SelectTableCommand.CommandText = QuerySelectRowString;
SelectTableCommand.Connection = m_connection;
m_adapter = new SqlCeDataAdapter((SqlCeCommand)SelectTableCommand);
// Create the DbCommandBuilder.
m_builder = new SqlCeCommandBuilder();
m_builder.DataAdapter = m_adapter;
m_adapter.SelectCommand = SelectTableCommand;
}
catch
{
throw;
}
}
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();
}
public static void SetBase(bool Dummy,string path)
{
string constr = "Data Source = \"{0}\"; Password =\"pdabase\"";
try
{
SqlCeConnection con = new SqlCeConnection(string.Format(constr, path));
con.Open();
//SqlCeCommand q=con.CreateCommand();
SqlCeCommand q;
if (Dummy)
{
q = new SqlCeCommand(string.Format(sql, 0), con);
q.ExecuteNonQuery();
}
else
{
q = new SqlCeCommand(string.Format(sql, 1), con);
q.ExecuteNonQuery();
}
con.Close();
}
catch (SqlCeException e)
{
Consoler(e.Message);
Log(e.Message,logpath);
}
}
//add issuance
public bool addIssuance(DateTime issuance_date, string license_no,
int violation_id, decimal price, int officer_id,
int issuance_status)
{
string sql_stmt;
bool result = false;
/**
sql_stmt = "CREATE TABLE issuances " +
"( " +
" issuance_date datetime," +
" license_no nvarchar(20)," +
" violation_id int," +
" price money," +
" officer_id int," +
" issuance_status int" +
") ";
**/
sql_stmt = "INSERT INTO issuances " +
" (issuance_date,license_no,violation_id,price,officer_id,issuance_status) " +
"VALUES " +
" (@id, @violation, @desc, @price, @last_mod)";
SqlCeConnection conn = new SqlCeConnection(this.conn_string);
conn.Open();
try
{
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = sql_stmt;
cmd.Parameters["issuance_date"].SqlDbType = System.Data.SqlDbType.DateTime;
cmd.Parameters["issuance_date"].Value = issuance_date;
cmd.Parameters["license_no"].SqlDbType = System.Data.SqlDbType.NVarChar;
cmd.Parameters["license_no"].Value = license_no;
cmd.Parameters["violation_id"].SqlDbType = System.Data.SqlDbType.Int;
cmd.Parameters["violation_id"].Value = violation_id;
cmd.Parameters["price"].SqlDbType = System.Data.SqlDbType.Money;
cmd.Parameters["price"].Value = price;
//cmd.Parameters["officer_id"].SqlDbType = System.Data.SqlDbType.Int;
//cmd.Parameters["officer_id"].Value = last_mod;
cmd.Parameters["issuance_status"].SqlDbType = System.Data.SqlDbType.Int;
cmd.Parameters["issuance_status"].Value = issuance_status;
result = (cmd.ExecuteNonQuery() > 0);
}
catch (Exception err)
{
string msg = err.Message;
result = false;
}
conn.Close();
return result;
}
public static Guid GetApplicationId(string connectionString, string applicationName)
{
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand("SELECT ApplicationId FROM [aspnet_Applications] " +
"WHERE ApplicationName = @ApplicationName", conn))
{
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName;
conn.Open();
var applicationId = cmd.ExecuteScalar();
if (applicationId == null)
{
cmd.Parameters.Clear();
cmd.CommandText = "INSERT INTO [aspnet_Applications] (ApplicationId, ApplicationName, LoweredApplicationName, Description) VALUES (@ApplicationId, @ApplicationName, @LoweredApplicationName, @Description)";
applicationId = Guid.NewGuid();
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = applicationId;
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName;
cmd.Parameters.Add("@LoweredApplicationName", SqlDbType.NVarChar, 256).Value = applicationName.ToLowerInvariant();
cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 256).Value = String.Empty;
cmd.ExecuteNonQuery();
}
return (Guid)applicationId;
}
}
}
internal void InsertCmd(Cmd cmd)
{
SqlCeConnection con = new SqlCeConnection(connectionString);
try
{
con.Open();
//SqlCeDataAdapter adapter = new SqlCeDataAdapter(
// "insert into cmd (name, description, path, arg) values (" +
// "'" + cmd.name + "'," +
// "'" + cmd.description + "'," +
// "'" + cmd.path + "'," +
// "'" + cmd.arg + "'" +
// ")", con);
//SqlCeCommand sqlCmd = adapter.InsertCommand;
//int count = sqlCmd.ExecuteNonQuery();
SqlCeCommand sqlCmd = con.CreateCommand();
sqlCmd.CommandText = "insert into cmd (name, description, path, arg) values (" +
"'" + cmd.name + "'," +
"'" + cmd.description + "'," +
"'" + cmd.path + "'," +
"'" + cmd.arg + "'" +
")";
int count = sqlCmd.ExecuteNonQuery();
}
finally
{
if (con != null)
{
con.Close();
}
}
}
internal List<Cmd> GetDbList()
{
List<Cmd> list = new List<Cmd>();
SqlCeConnection con = new SqlCeConnection(connectionString);
try
{
con.Open();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(
"select * from cmd order by name", con);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Cmd");
DataTable table = dataSet.Tables["Cmd"];
foreach (DataRow row in table.Rows)
{
Cmd cmd = new Cmd();
cmd.name = (string)row["name"];
cmd.description = (string)row["description"];
cmd.path = (string)row["path"];
cmd.arg = (string)row["arg"];
list.Add(cmd);
}
}
finally
{
if (con != null)
{
con.Close();
}
}
return list;
}
public static User CreateUser(string username, string password)
{
SqlCeConnection con = new SqlCeConnection(CONNECTION_STRING);
try
{
con.Open();
SqlCeCommand comm = new SqlCeCommand("INSERT INTO users (username, password, salt, dateCreated) VALUES (@username, @password, @salt, @createdDate)", con);
comm.Parameters.Add(new SqlCeParameter("@username", username));
comm.Parameters.Add(new SqlCeParameter("@password", password));
comm.Parameters.Add(new SqlCeParameter("@salt", String.Empty));
comm.Parameters.Add(new SqlCeParameter("@createdDate", DateTime.UtcNow));
int numberOfRows = comm.ExecuteNonQuery();
if (numberOfRows > 0)
{
return GetUser(username);
}
}
catch (Exception ex)
{
Debug.Print("CreateUser Exception: " + ex);
}
finally
{
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
}
return null;
}
private ApplicationState()
{
// read the application state from db
SqlCeConnection _dataConn = null;
try
{
_dataConn = new SqlCeConnection("Data Source=FlightPlannerDB.sdf;Persist Security Info=False;");
_dataConn.Open();
SqlCeCommand selectCmd = new SqlCeCommand();
selectCmd.Connection = _dataConn;
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT cruiseSpeed,cruiseFuelFlow,minFuel,speed,unit,utcOffset,locationFormat,deckHoldFuel,registeredClientName FROM ApplicationState");
selectCmd.CommandText = selectQuery.ToString();
SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows)
{
results.ReadFirst();
cruiseSpeed = results.GetInt64(0);
cruiseFuelFlow = results.GetInt64(1);
minFuel = results.GetInt64(2);
speed = results.GetSqlString(3).ToString();
unit = results.GetSqlString(4).ToString();
utcOffset = results.GetSqlString(5).ToString();
locationFormat = results.GetSqlString(6).ToString();
deckHoldFuel = results.IsDBNull(7) ? 0 : results.GetInt64(7);
registeredClientName = results.IsDBNull(8) ? string.Empty : results.GetString(8);
}
}
finally
{
_dataConn.Close();
}
}
private void buttonImage1_Click(object sender, EventArgs e)
{
/*
* Verificar se existe coluna e inserir caso n exista
* garantir compatibilidade com bancos velhos
*/
//CONN
SqlCeConnection conn =
new SqlCeConnection("Data Source = " + Library.appDir + "\\db\\citeluz.sdf; Password=mfrn@0830$X-PRO;");
//coluna user
conn.Open();
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "ALTER TABLE trafo ADD [user] NVARCHAR(15) DEFAULT 'TESTE';";
try
{
command.ExecuteNonQuery();
MessageBox.Show("Tabela trafo atualizada com sucesso");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
public SqlServerCeProcessor(SqlCeConnection connection, IMigrationGenerator generator, IAnnouncer announcer, IMigrationProcessorOptions options)
: base(generator, announcer, options)
{
Connection = connection;
connection.Open();
BeginTransaction();
}
public bool conecta()
{
bool resultado = false;
try
{
con = new SqlCeConnection(strcon);
con.Open();
resultado = true;
}
catch (Exception ex)
{
try
{
con = new SqlCeConnection(strcon2);
con.Open();
resultado = true;
}
catch (Exception ex2)
{
Console.WriteLine("{0} Exception Caught.", ex2);
}
//Console.WriteLine("{0} Exception Caught.", ex);
}
return resultado;
}
public void Tt()
{
string connectionString = @"DataSource=db.sdf";
var conn = new SqlCeConnection(connectionString);
if(!File.Exists(conn.Database))
{
new SqlCeEngine(connectionString).CreateDatabase();
}
conn.Open();
//Creating a table
var cmdCreate = new SqlCeCommand("CREATE TABLE Products (Id int IDENTITY(1,1), Title nchar(50), PRIMARY KEY(Id))", conn);
cmdCreate.ExecuteNonQuery();
//Inserting some data...
var cmdInsert = new SqlCeCommand("INSERT INTO Products (Title) VALUES ('Some Product #1')", conn);
cmdInsert.ExecuteNonQuery();
//Making sure that our data was inserted by selecting it
var cmdSelect = new SqlCeCommand("SELECT Id, Title FROM Products", conn);
SqlCeDataReader reader = cmdSelect.ExecuteReader();
reader.Read();
Console.WriteLine("Id: {0} Title: {1}", reader["Id"], reader["Title"]);
reader.Close();
conn.Close();
}
private void frmDetail_Load(object sender, EventArgs e)
{
myConnection = default(SqlCeConnection);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [ID],[Job],[ItemId],[Qty],[Unit],[CheckedDateTime],[CheckedBy],[Checked] FROM ["
+ storagePath.getStoreTable() + "] WHERE ID ='"
+ bm + "' and Job='" + jb + "' and ItemId = '" + item + "' and Checked='true'";
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(ds);
Adapter.Dispose();
if (ds.Tables[0].Rows.Count > 0)
{
//isCheck = true;
this.txtItem.Text = ds.Tables[0].Rows[0]["ItemId"].ToString();
this.txtCheckedDateTime.Text = ds.Tables[0].Rows[0]["CheckedDateTime"].ToString();
this.txtCheckedBy.Text = ds.Tables[0].Rows[0]["CheckedBy"].ToString();
}
else
{
// isCheck = false;
}
myCommand.Dispose();
dt = null;
myConnection.Close();
}
private void btnDelete_Click(object sender, EventArgs e)
{
btnSave.Enabled = false;
grbDeviceIP.Enabled = false;
dataSourcePath = "Data Source = " + Application.StartupPath + @"\DeviceData.sdf";
SqlCeConnection sqlConnection1 = new SqlCeConnection();
sqlConnection1.ConnectionString = dataSourcePath;
SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
try
{
cmd.CommandText = "DELETE FROM DeviceData WHERE DEVICE_IP='" + Global.selMechIp + "'";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM DeviceURL WHERE DEV_IP='" + Global.selMechIp + "'";
cmd.ExecuteNonQuery();
}
catch { }
sqlConnection1.Dispose();
sqlConnection1.Close();
fnGetIpsFronTable();
btnDelete.Enabled = false;
btnEdit.Enabled = false;
txtDevIp.Text = "";
txtDevNo.Text = "";
txtDevPort.Text = "";
Application.DoEvents();
}
public List<ErrorLog> GetLogs()
{
var result = new List<ErrorLog>();
IEnumerable<string> logs;
using (IDbConnection connection = new SqlCeConnection(Connection))
{
connection.Open();
var query = _settingsManager.GetMaxNumberOfLogs() > -1 ?
string.Format("SELECT TOP {0} [AllXml] FROM [ELMAH_Error] ORDER BY [Sequence] DESC;", _settingsManager.GetMaxNumberOfLogs()) :
"SELECT [AllXml] FROM [ELMAH_Error] ORDER BY [Sequence] DESC";
logs = connection.Query<string>(query);
}
foreach (var log in logs)
{
var errorLog = _parser.Parse(log);
if (errorLog == null)
{
_log.Error(string.Format("Failed to parse file: {0}", log));
continue;
}
result.Add(errorLog);
}
return result;
}
private static void CreateInitialDatabaseObjects(string connString)
{
using (SqlCeConnection conn = new SqlCeConnection(connString))
{
string[] queries = Regex.Split(NetworkAssetManager.Properties.Resources.DBGenerateSql, "GO");
SqlCeCommand command = new SqlCeCommand();
command.Connection = conn;
conn.Open();
foreach (string query in queries)
{
string tempQuery = string.Empty;
tempQuery = query.Replace("\r\n", "");
if (tempQuery.StartsWith("--") == true)
{
/*Comments in script so ignore*/
continue;
}
_logger.Info("Executing query: " + tempQuery);
command.CommandText = tempQuery;
try
{
command.ExecuteNonQuery();
}
catch (System.Exception e)
{
_logger.Error(e.Message);
}
}
conn.Close();
}
}
public void InitTestSchema()
{
var connStr = String.Format("Data Source = '{0}';", _testDb);
using (var conn = new SqlCeConnection(connStr))
{
conn.Open();
var command = new SqlCeCommand();
command.Connection = conn;
command.CommandText =
@"CREATE TABLE accel_data (
id INT IDENTITY NOT NULL PRIMARY KEY,
date DATETIME,
Ax Float,Ay Float
)";
command.ExecuteNonQuery();
command.CommandText = @"CREATE TABLE accel_params (
id INT IDENTITY NOT NULL PRIMARY KEY,
date DATETIME,
sensorNumber smallint,
offsetX Float,offsetY Float,
gravityX Float,gravityY Float
)";
command.ExecuteNonQuery();
command.CommandText = @"CREATE TABLE calibr_result (
id INT IDENTITY NOT NULL PRIMARY KEY,
accelDataId INT,
accelParamsId INT
)";
command.ExecuteNonQuery();
}
}
public bool registriraj(string korisnickoIme, string lozinka, string address, string email)
{
SqlCeConnection conn = new SqlCeConnection(connString);
try
{
Random r = new Random(System.DateTime.Now.Millisecond);
string salt = r.Next().ToString();
string hashiranaLoznika = Util.SHA256(lozinka);
string hashiranaSlanaLoznika = Util.SHA256(salt + hashiranaLoznika);
conn.Open();
SqlCeCommand command = new SqlCeCommand
("INSERT INTO Kori(username,password,salt,address,email) VALUES (@username,@password,@salt,@address,@email)",conn);
command.Parameters.AddWithValue("username", korisnickoIme);
command.Parameters.AddWithValue("password", hashiranaSlanaLoznika);
command.Parameters.AddWithValue("salt", salt);
command.Parameters.AddWithValue("address", address);
command.Parameters.AddWithValue("email", email);
command.ExecuteNonQuery();
conn.Close();
return true;
}
catch (Exception ex)
{
return false;
}
}
public static IDbConnection GetConnection()
{
var connectionString = ConfigProvider.GetConnectionString();
var connection = new SqlCeConnection(connectionString);
connection.Open();
return connection;
}
private void TestConnection(bool showMessage)
{
try
{
if (createDb)
{
if (!System.IO.File.Exists(builder.DataSource))
{
using (var eng = new SqlCeEngine(builder.ConnectionString))
{
eng.CreateDatabase();
}
}
}
using (var conn = new SqlCeConnection(builder.ConnectionString))
{
conn.Open();
this.ConnectionString = builder.ConnectionString;
if (showMessage)
{
MessageBox.Show("Test succeeded!");
}
else
{
this.DialogResult = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex));
}
}
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;
}
}
public void conectaBD()
{
SqlCeConnection PathBD = new SqlCeConnection("Data Source=C:\\Facturacion\\Facturacion\\BaseDeDatos.sdf;Persist Security Info=False;");
//abre la conexion
try
{
da = new SqlCeDataAdapter("SELECT * FROM USUARIOS ORDER BY ID_USUARIO", PathBD);
// Crear los comandos de insertar, actualizar y eliminar
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
// Asignar los comandos al DataAdapter
// (se supone que lo hace automáticamente, pero...)
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
dt = new DataTable();
// Llenar la tabla con los datos indicados
da.Fill(dt);
PathBD.Open();
}
catch (Exception w)
{
MessageBox.Show(w.ToString());
return;
}
}
public ctlrptActiveCall()
{
try
{
InitializeComponent();
ConnectionString = VMuktiAPI.VMuktiInfo.MainConnectionString;
if (System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory.ToString() + "rptActiveCall.sdf"))
{
System.IO.File.Delete(AppDomain.CurrentDomain.BaseDirectory.ToString() + "rptActiveCall.sdf");
}
SqlCeEngine clientEngine = new SqlCeEngine(ClientConnectionString);
clientEngine.CreateDatabase();
LocalSQLConn = new SqlCeConnection();
LocalSQLConn.ConnectionString = ClientConnectionString;
LocalSQLConn.Open();
fncActiveCallTable();
LocalSQLConn.Close();
objRefreshReport = new delRefreshReport(fncRefreshReport);
NetPeerClient npcActiveCall = new NetPeerClient();
((NetP2PBootStrapActiveCallReportDelegates)objActiveCall).EntsvcJoinCall += new NetP2PBootStrapActiveCallReportDelegates.DelsvcJoinCall(ctlrptActiveCall_EntsvcJoinCall);
((NetP2PBootStrapActiveCallReportDelegates)objActiveCall).EntsvcGetCallInfo += new NetP2PBootStrapActiveCallReportDelegates.DelsvcGetCallInfo(ctlrptActiveCall_EntsvcGetCallInfo);
((NetP2PBootStrapActiveCallReportDelegates)objActiveCall).EntsvcActiveCalls += new NetP2PBootStrapActiveCallReportDelegates.DelsvcActiveCalls(ctlrptActiveCall_EntsvcActiveCalls);
((NetP2PBootStrapActiveCallReportDelegates)objActiveCall).EntsvcSetDuration += new NetP2PBootStrapActiveCallReportDelegates.DelsvcSetDuration(ctlrptActiveCall_EntsvcSetDuration);
((NetP2PBootStrapActiveCallReportDelegates)objActiveCall).EntsvcUnJoinCall += new NetP2PBootStrapActiveCallReportDelegates.DelsvcUnJoinCall(ctlrptActiveCall_EntsvcUnJoinCall);
channelNetTcpActiveCall = (INetP2PBootStrapReportChannel)npcActiveCall.OpenClient<INetP2PBootStrapReportChannel>("net.tcp://" + VMuktiAPI.VMuktiInfo.BootStrapIPs[0] + ":6000/NetP2PBootStrapActiveCallReport", "ActiveCallMesh", ref objActiveCall);
channelNetTcpActiveCall.svcJoinCall(VMuktiAPI.VMuktiInfo.CurrentPeer.DisplayName);
}
catch (Exception ex)
{
VMuktiAPI.VMuktiHelper.ExceptionHandler(ex, "ctlrptActiveCall", "ctlrptActiveCall.xaml.cs");
}
}
private void button2_Click(object sender, EventArgs e)
{
DataConnectionDialog dcd = new DataConnectionDialog();
DataConnectionConfiguration dcs = new DataConnectionConfiguration(null);
dcs.LoadConfiguration(dcd);
if (DataConnectionDialog.Show(dcd) == DialogResult.OK)
{
textBox2.Text = dcd.ConnectionString;
connectionString = dcd.ConnectionString;
comboBox1.Enabled = true;
using (SqlCeConnection con = new SqlCeConnection(connectionString))
{
comboBox1.Items.Clear();
con.Open();
using (SqlCeCommand command = new SqlCeCommand("SELECT table_name FROM INFORMATION_SCHEMA.Tables", con))
{
SqlCeDataReader reader = command.ExecuteReader();
while (reader.Read())
{
comboBox1.Items.Add(reader.GetString(0));
}
}
}
//textBox1.Text = dcd.SelectedDataSource.DisplayName;
}
dcs.SaveConfiguration(dcd);
}
public static DataTable ExecuteSqlQuery(string query, params SqlCeParameter[] sqlParams)
{
var dt = new DataTable();
using (var conn = new SqlCeConnection(connStr))
using (var cmd = new SqlCeCommand(query, conn))
{
try
{
SqlCeEngine engine = new SqlCeEngine(conn.ConnectionString);
engine.Upgrade(conn.ConnectionString);
}
catch
{
}
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(sqlParams);
conn.Open();
dt.Load(cmd.ExecuteReader());
}
return dt;
}
public override void Test()
{
string testString = ToTestString();
// Create a connection object
SqlCeConnection connection = new SqlCeConnection();
// Try to open it
try
{
connection.ConnectionString = ToFullString();
connection.Open();
}
catch (SqlCeException e)
{
// Customize the error message for upgrade required
if (e.Number == m_intDatabaseFileNeedsUpgrading)
{
throw new InvalidOperationException(Resources.SqlCeConnectionProperties_FileNeedsUpgrading);
}
throw;
}
finally
{
connection.Dispose();
}
}
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();
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
public virtual DatabaseModel Create(DbConnection connection, IEnumerable <string> tables, IEnumerable <string> schemas)
{
Check.NotNull(connection, nameof(connection));
Check.NotNull(tables, nameof(tables));
ResetState();
_connection = connection as SqlCeConnection;
var connectionStartedOpen = (_connection != null) && (_connection.State == ConnectionState.Open);
if (!connectionStartedOpen)
{
_connection?.Open();
}
try
{
_tableSelectionSet = new TableSelectionSet(tables, schemas);
string databaseName = null;
try
{
if (_connection != null)
{
databaseName = Path.GetFileNameWithoutExtension(_connection.DataSource);
}
}
catch (ArgumentException)
{
// graceful fallback
}
if (_connection != null)
{
_databaseModel.DatabaseName = !string.IsNullOrEmpty(databaseName) ? databaseName : _connection.DataSource;
}
GetTables();
GetColumns();
GetPrimaryKeys();
GetUniqueConstraints();
GetIndexes();
GetForeignKeys();
CheckSelectionsMatched(_tableSelectionSet);
return(_databaseModel);
}
finally
{
if (!connectionStartedOpen)
{
_connection?.Close();
}
}
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
public virtual DatabaseModel Create(DbConnection connection, TableSelectionSet tableSelectionSet)
{
ResetState();
_connection = connection as SqlCeConnection;
var connectionStartedOpen = (_connection != null) && (_connection.State == ConnectionState.Open);
if (!connectionStartedOpen)
{
_connection?.Open();
}
try
{
_tableSelectionSet = tableSelectionSet;
string databaseName = null;
try
{
if (_connection != null)
{
databaseName = Path.GetFileNameWithoutExtension(_connection.DataSource);
}
}
catch (ArgumentException)
{
// graceful fallback
}
if (_connection != null)
{
_databaseModel.DatabaseName = !string.IsNullOrEmpty(databaseName) ? databaseName : _connection.DataSource;
}
GetTables();
GetColumns();
GetIndexes();
GetForeignKeys();
return(_databaseModel);
}
finally
{
if (!connectionStartedOpen)
{
_connection?.Close();
}
}
}
public DatabaseAccess(string con_String)
{
SqlCeConnection con = new SqlCeConnection(@con_String);
con.Open();
}
//yes button guest
private void btnYesGuestCheckOut_Click(object sender, RoutedEventArgs e)
{
SqlCeConnection databaseConnection = new SqlCeConnection(@"Data Source=" + Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\TheLotusTempleManager\TheLotusTempleDB.sdf");
string query = "SELECT * FROM guestTab WHERE [Occupied Room]= '" + guestListUserControl.roomNameDataGrid + "'";
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
SqlCeDataAdapter data = new SqlCeDataAdapter(cmd);
databaseConnection.Close();
DataTable dt = new DataTable();
data.Fill(dt);
Guest obj = new Guest();
obj.firstNameGuest = dt.Rows[0]["First Name"].ToString();
obj.lastNameGuest = dt.Rows[0]["Family Name"].ToString();
obj.ageGuest = int.Parse(dt.Rows[0]["Age"].ToString());
obj.phoneNumberGuest = int.Parse(dt.Rows[0]["Phone Number"].ToString());
obj.identityGuest = int.Parse(dt.Rows[0]["Identity Number"].ToString());
obj.genderGuest = dt.Rows[0]["Gender"].ToString();
obj.occupiedRoomGuest = dt.Rows[0]["Occupied Room"].ToString();
obj.addressGuest = dt.Rows[0]["Address"].ToString();
query = "SELECT * FROM booking WHERE [Room Name] = '" + guestListUserControl.roomNameDataGrid + "'";
SqlCeCommand cmd2 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
SqlCeDataAdapter data2 = new SqlCeDataAdapter(cmd2);
databaseConnection.Close();
DataTable dt2 = new DataTable();
data2.Fill(dt2);
obj.checkInDate = DateTime.Parse(dt2.Rows[0]["CheckIn date"].ToString());
obj.checkOutDate = DateTime.Parse(dt2.Rows[0]["CheckOut date"].ToString());
query = "INSERT INTO archiveTable([First Name], [Family Name],[Age],[Phone Number],[Address], [Gender],[Occupied Room],[Identity Number],[CheckIn date],[CheckOut date])" +
" VALUES(@fname,@lname,@age,@phone,@address,@gender,@room,@id,@in,@out)";
SqlCeCommand cmd3 = new SqlCeCommand(query, databaseConnection);
cmd3.Parameters.AddWithValue("@fname", obj.firstNameGuest);
cmd3.Parameters.AddWithValue("@lname", obj.lastNameGuest);
cmd3.Parameters.AddWithValue("@age", obj.ageGuest);
cmd3.Parameters.AddWithValue("@phone", obj.phoneNumberGuest);
cmd3.Parameters.AddWithValue("@address", obj.addressGuest);
cmd3.Parameters.AddWithValue("@gender", obj.genderGuest);
cmd3.Parameters.AddWithValue("@room", obj.occupiedRoomGuest);
cmd3.Parameters.AddWithValue("@id", obj.identityGuest);
cmd3.Parameters.AddWithValue("@in", obj.checkInDate);
cmd3.Parameters.AddWithValue("@out", obj.checkOutDate);
databaseConnection.Open();
cmd3.ExecuteNonQuery();
databaseConnection.Close();
//Delete from the booking table
query = "DELETE FROM booking WHERE [Room Name] = '" + guestListUserControl.roomNameDataGrid + "'";
SqlCeCommand cmd4 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
cmd4.ExecuteNonQuery();
databaseConnection.Close();
//Delete from the guest table
query = "DELETE FROM guestTab WHERE [Occupied Room] = '" + guestListUserControl.roomNameDataGrid + "'";
SqlCeCommand cmd5 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
cmd5.ExecuteNonQuery();
databaseConnection.Close();
//updating the room status when deleting
query = "UPDATE roomsTab SET [Room Status] = 'Not occupied' WHERE [Room Name] ='" + guestListUserControl.roomNameDataGrid + "'";
SqlCeCommand cmd6 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
cmd6.ExecuteNonQuery();
databaseConnection.Close();
this.Close();
}
private static void CreateDatabase(SchemaType schemaType)
{
SqlCeEngine engine = new SqlCeEngine(connectionString);
if (!Directory.Exists("testdata"))
{
Directory.CreateDirectory("testdata");
}
if (System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}
engine.CreateDatabase();
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand())
{
cmd.Connection = conn;
switch (schemaType)
{
case SchemaType.NoConstraints:
cmd.CommandText = "CREATE TABLE [Shippers] ([ShipperID] int NOT NULL, [CompanyName] nvarchar(40) NULL);";
cmd.ExecuteNonQuery();
break;
case SchemaType.FullConstraints:
case SchemaType.FullConstraintsDuplicateRows:
cmd.CommandText = "CREATE TABLE [Shippers] ([ShipperID] int NOT NULL IDENTITY (1,1), [CompanyName] nvarchar(40) NULL DEFAULT N'ABC');";
cmd.ExecuteNonQuery();
cmd.CommandText = "ALTER TABLE [Shippers] ADD PRIMARY KEY ([ShipperID]);";
cmd.ExecuteNonQuery();
break;
case SchemaType.FullNoIdentity:
cmd.CommandText = "CREATE TABLE [Shippers] ([ShipperID] int NOT NULL IDENTITY (1,1), [CompanyName] nvarchar(40) NULL DEFAULT N'ABC');";
cmd.ExecuteNonQuery();
cmd.CommandText = "ALTER TABLE [Shippers] ADD PRIMARY KEY ([ShipperID]);";
cmd.ExecuteNonQuery();
break;
case SchemaType.DataReaderTest:
case SchemaType.DataReaderTestMapped:
cmd.CommandText = "CREATE TABLE [tblDoctor]([DoctorId] [int] NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [MiddleName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [FullName] [nvarchar](150) NOT NULL, [SpecialityId_FK] [int] NOT NULL, [Active] [bit] NOT NULL, [LastUpdated] [datetime] NOT NULL );";
cmd.ExecuteNonQuery();
cmd.CommandText = "ALTER TABLE [tblDoctor] ADD PRIMARY KEY ([DoctorId]);";
cmd.ExecuteNonQuery();
break;
case SchemaType.DataReaderTestMappedKeepOriginal:
case SchemaType.DataReaderTestMappedWithPrimaryKey:
case SchemaType.DataReaderTestMappedCollectionKeepOriginal:
cmd.CommandText = "CREATE TABLE [tblDoctor]([DoctorId] [int] NOT NULL IDENTITY (1,1), [FirstName] [nvarchar](50) NOT NULL, [MiddleName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [FullName] [nvarchar](150) NOT NULL, [SpecialityId_FK] [int] NOT NULL, [Active] [bit] NOT NULL, [LastUpdated] [datetime] NOT NULL );";
cmd.ExecuteNonQuery();
cmd.CommandText = "ALTER TABLE [tblDoctor] ADD PRIMARY KEY ([DoctorId]);";
cmd.ExecuteNonQuery();
break;
default:
break;
}
}
}
}
public void save1()
{
try
{
SqlCeConnection conn = new SqlCeConnection(Properties.Settings.Default.conne);
SqlCeCommand cmd = new SqlCeCommand();
// conn.ConnectionString = "Data Source=DELL-PC;Initial Catalog=GST;Integrated Security=True";
conn.Open();
cmd.Connection = conn;
foreach (ListViewItem li in listView.Items)
{
string it = li.SubItems[0].Text;
string hsn = li.SubItems[1].Text;
string gst = li.SubItems[2].Text;
string qt = li.SubItems[3].Text;
string mrp = li.SubItems[4].Text;
string rat = li.SubItems[5].Text;
string tamt = li.SubItems[6].Text;
string fiamt = li.SubItems[7].Text;
string q1 = "INSERT INTO bill_detail([Vendor_name],[Pur_ord],[Bill_date],[Due_date],[Items],[Qty],[Rate],[Total],[Ref],[GST],[Fin_amt],[HSN],[MRP],[Amt]) VALUES('" + salution.Text + "','" + textBox2.Text + "','" + dateTimePicker1.Text + "','" + dateTimePicker2.Text + "','" + it + "','" + qt + "','" + rat + "','" + textBox5.Text + "','" + textBox4.Text + "','" + gst + "','" + fiamt + "','" + hsn + "','" + mrp + "','" + tamt + "')";
SqlCeCommand cmd2 = new SqlCeCommand(q1, conn);
cmd2.ExecuteNonQuery();
}
Double val1 = Convert.ToDouble(textBox3.Text);
Double val2 = Convert.ToDouble(textBox5.Text);
Double val3 = val2 - val1;
cmd.CommandText = "insert into Bills([Date],[Vendor_name],[Due_date],[Amt],[Paid],[Bal]) VALUES ('" + dateTimePicker1.Text + "','" + salution.Text + "','" + dateTimePicker2.Text + "','" + textBox5.Text + "','" + textBox3.Text + "','" + val3 + "')";
cmd.ExecuteNonQuery();
string type = "Bills";
cmd.CommandText = " INSERT INTO Payment_made([Date],[Type],[Ref],[Vendor_name],[Mode],[Amt],[Desp]) VALUES('" + dateTimePicker1.Text + "','" + type + "','" + textBox4.Text + "','" + salution.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + comboBox1.Text + "')";
cmd.ExecuteNonQuery();
foreach (ListViewItem li in listView1.Items)
{
string ta = li.SubItems[0].Text;
string cgta = li.SubItems[1].Text;
string cgamt = li.SubItems[2].Text;
string sgta = li.SubItems[3].Text;
string sgamt = li.SubItems[4].Text;
string q1 = "INSERT INTO Bill_Tax ([Date_I],[B_ID],[Tax],[CTA],[CAmt],[STA],[SAmt],[CGST_t],[SGST_t],[ref]) VALUES('" + dateTimePicker1.Text + "','" + textBox2.Text + "','" + ta + "','" + cgta + "','" + cgamt + "','" + sgta + "','" + sgamt + "','" + textBox18.Text + "','" + textBox17.Text + "','" + textBox4.Text + "')";
SqlCeCommand cmd2 = new SqlCeCommand(q1, conn);
cmd2.ExecuteNonQuery();
}
//DataRow[] dr = ds18.Tables[0].Select("COMPLEATED_ON IS NOT NULL AND COMPLEATED_ON <> ''");
Int32 j1 = ds18.Tables[0].Rows.Count;
DataRow[] dr = ds18.Tables[0].Select();
//MessageBox.Show("Length Of Record " + j1);
for (int j = 0; j < j1; j++)
{
String s = Convert.ToString(dr[j]["Prod_name"]);
//MessageBox.Show("Prodcut name "+ s);
SqlCeConnection sqlConnection = new SqlCeConnection(Properties.Settings.Default.conne);
sqlConnection.Open();
SqlCeCommand check_User_Name = new SqlCeCommand("Select * FROM goods", sqlConnection);
SqlCeDataReader dr12 = check_User_Name.ExecuteReader();
String flag = "fasle";
while (dr12.Read())
{
//MessageBox.Show("Prodcut name in goods " + dr12[1].ToString());
if (dr12[1].ToString() == s)
{
flag = "true";
break;
}
else
{
flag = "false";
}
}
if (flag.Equals("true"))
{
//User Exists
// MessageBox.Show("Record have");
String qt = Convert.ToString(dr[j]["Qty"]);
cmd.CommandText = "UPDATE goods SET [Qty]='" + qt + "' where [Prod_name]='" + s + "'";
cmd.ExecuteNonQuery();
}
else
{
//User NOT Exists
//dr1["Prod_name"] = itde1.Text;
//dr1["Qty"] = qua1.Text;
//dr1["GST"] = comboBox4.Text;
//dr1["HSN"] = comboBox5.Text;
//dr1["MRP"] = comboBox3.Text;
//dr1["Salesp"] = rat1.Text;
String pro = Convert.ToString(dr[j]["Prod_name"]);
String qty = Convert.ToString(dr[j]["Qty"]);
String gst = Convert.ToString(dr[j]["GST"]);
String hsn = Convert.ToString(dr[j]["HSN"]);
String mrp = Convert.ToString(dr[j]["MRP"]);
String sal = "0";
cmd.CommandText = "INSERT INTO goods([Prod_name],[Qty],[Salesp],[GST],[HSN],[MRP]) VALUES('" + pro + "','" + qty + "','" + sal + "','" + gst + "','" + hsn + "','" + mrp + "')";
cmd.ExecuteNonQuery();
}
checkBox1.Checked = false;
sqlConnection.Close();
//MessageBox.Show(s);
//MessageBox.Show("" + st1);
}
MessageBox.Show("Inserted Successfully");
conn.Close();
here();
listView.Items.Clear();
cle();
panel1.Visible = false;
}
catch (Exception ab7)
{
MessageBox.Show("Error :" + ab7);
}
}
protected void searchReserves(string type24)
{
SqlCeConnection linksql = new SqlCeConnection(@"Data Source='C:\Users\Uoc\Documents\GitHub\Uoc_ASPNET\restaurantuoc.sdf';Password='******'");
linksql.Open();
SqlCeCommand sqlQuery = new SqlCeCommand();
if (type24 == "")
{
Timer1_Tick(this, EventArgs.Empty); // CRIDEM AL EVENT AL ENTRAR PERQUE VEGEM EL LINK DE 24h DESDE EL INICI. DESPRES COMENÇA A FER EL TIMER.
sqlQuery.CommandText = "SELECT * FROM reserves WHERE Data > GETDATE() order by Data";
if (FindControl("Link24") != null)
{
show24.Visible = true;
}
}
else
{
sqlQuery.CommandText = "SELECT * FROM reserves WHERE Data BETWEEN GETDATE() AND (DATEADD(day,1,GETDATE()))";
Timer1.Enabled = false; // PAREM LA CONSULTA DE 24h JA QUE JA ESTEM A LA PÀGINA DE 24h.
showList.Visible = true;
show24.Visible = false;
}
sqlQuery.Connection = linksql;
SqlCeDataReader resultSql = sqlQuery.ExecuteReader();
if (resultSql.Read())
{
resultSql.Close();
resultSql = sqlQuery.ExecuteReader(); //TORNEM A EXECUTAR EL READER JA QUE SI COMPROVAVEM IF READ() PERDIEM EL PRIMER REGISTRE. TANQUEM I TRONEM A OBRIR.
headTable.InnerHtml = "<table id=\"tableReservasA\"><tr bgcolor=\"#A4A4A4\"><td><strong>Nom</strong></td><td><strong>Cognoms</strong></td><td><strong>Telefon</strong></td><td><strong>Data</strong></td><td><strong>Comensals</strong></td></tr></table>";
while (resultSql.Read())
{
TableRow rowReservas = new TableRow();
rowReservas.ID = "Row" + resultSql["Id"].ToString();
rowReservas.CssClass = "treservas";
TableCell nomReservas = new TableCell();
nomReservas.Text = resultSql["Nom"].ToString();
TableCell cognomsReservas = new TableCell();
cognomsReservas.Text = resultSql["Cognoms"].ToString();
TableCell telfReservas = new TableCell();
telfReservas.Text = resultSql["Telefon"].ToString();
TableCell dataReservas = new TableCell();
dataReservas.Text = resultSql["Data"].ToString();
TableCell comenReservas = new TableCell();
comenReservas.Text = resultSql["Comensals"].ToString();
TableCell menueditReservas = new TableCell();
Button idform1 = new Button();
idform1.ID = "detailid" + resultSql["Id"].ToString();
idform1.CssClass = "detailres regform";
idform1.Text = "Detall";
idform1.Click += new EventHandler(this.detail_Click);
Button idform2 = new Button();
idform2.ID = "modid" + resultSql["Id"].ToString();
idform2.CssClass = "novamodif regform";
idform2.Text = "Modificar";
idform2.Click += new EventHandler(this.nova_Click);
Button idform3 = new Button();
idform3.ID = "delid" + resultSql["Id"].ToString();
idform3.CssClass = "delRes regform";
idform3.Text = "Eliminar";
idform3.Click += new EventHandler(this.del_Click);
menueditReservas.Controls.Add(idform1);
menueditReservas.Controls.Add(idform2);
menueditReservas.Controls.Add(idform3);
rowReservas.Controls.Add(nomReservas);
rowReservas.Controls.Add(cognomsReservas);
rowReservas.Controls.Add(telfReservas);
rowReservas.Controls.Add(dataReservas);
rowReservas.Controls.Add(comenReservas);
rowReservas.Controls.Add(menueditReservas);
tableReservas.Controls.Add(rowReservas);
}
}
else
{
updatableContent.InnerHtml = "<table id=\"tableReservasA\"><tr><td><strong>NO HI HA RESERVES PREVISTES.</strong></td></tr></table>";
}
linksql.Close();
}
/// <summary>
/// Initializes a new instance of the <see cref="DBRepository"/> class.
/// </summary>
/// <param name="connectionString">The connection string.</param>
public DBRepository(string connectionString)
#endif
{
_cn = new SqlCeConnection(connectionString);
_cn.Open();
}
public JsonResult GenerateDatabase(string data)
{
LogExtension.LogInfo("Generating database", MethodBase.GetCurrentMethod());
var i = 0;
var databaseCredentials = JsonConvert.DeserializeObject <DataBaseConfiguration>(data);
var isSql = databaseCredentials.ServerType.ToString();
object result;
if (String.Equals(isSql, "MSSQL", StringComparison.OrdinalIgnoreCase))
{
string connectionString;
if (!databaseCredentials.IsWindowsAuthentication)
{
connectionString =
"Data Source=" + databaseCredentials.ServerName + ";user id=" + databaseCredentials.UserName +
";password="******"Server=" + databaseCredentials.ServerName + "; Integrated Security=yes;";
}
var sqldbScript = new FileInfo(AppDomain.CurrentDomain.BaseDirectory +
WebConfigurationManager.AppSettings["SystemConfigurationPath"] +
ServerSetup.SqlTables);
var dbCreationScript = "USE [master]; CREATE DATABASE [" + databaseCredentials.DataBaseName + "];";
var isDatabaseExist = CheckDatabaseExists(connectionString, databaseCredentials.DataBaseName);
if (isDatabaseExist)
{
var failResult = new { key = false, value = "Database name is already exist" };
return(Json(new { Data = failResult }));
}
var connection = new SqlConnection(connectionString);
#region Create Database
var isDatabaseCreated = false;
try
{
LogExtension.LogInfo("Creating database in SQL server", MethodBase.GetCurrentMethod());
var command = new SqlCommand(dbCreationScript, connection);
connection.Open();
command.ExecuteNonQuery();
isDatabaseCreated = true;
}
catch (SqlException ex)
{
isDatabaseCreated = false;
LogExtension.LogInfo("Error in creating SQL Database", MethodBase.GetCurrentMethod());
LogExtension.LogError("Error in creating SQL Database", ex, MethodBase.GetCurrentMethod());
var failResult = new { key = false, value = ex.Message };
return(Json(new { Data = failResult }));
}
finally
{
connection.Close();
}
LogExtension.LogInfo("Is database created?" + isDatabaseCreated.ToString(), MethodBase.GetCurrentMethod());
#endregion
if (isDatabaseCreated)
{
var tabelCreationScript = "USE [" + databaseCredentials.DataBaseName + "]; " +
sqldbScript.OpenText().ReadToEnd();
try
{
LogExtension.LogInfo("Creating database tables in SQL server", MethodBase.GetCurrentMethod());
var command = new SqlCommand(tabelCreationScript, connection);
connection.Open();
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
LogExtension.LogInfo("Error in creating SQL Database tables", MethodBase.GetCurrentMethod());
LogExtension.LogError("Error in creating SQL Database", ex, MethodBase.GetCurrentMethod());
var failResult = new { key = false, value = ex.Message };
return(Json(new { Data = failResult }));
}
finally
{
connection.Close();
}
LogExtension.LogInfo("SQL database tables created successfully.", MethodBase.GetCurrentMethod());
if (!databaseCredentials.IsWindowsAuthentication)
{
connectionString =
"Data Source=" + databaseCredentials.ServerName + ";Initial Catalog=" +
databaseCredentials.DataBaseName + ";user id=" + databaseCredentials.UserName + ";password="******"Server=" + databaseCredentials.ServerName + ";Initial Catalog=" +
databaseCredentials.DataBaseName + "; Integrated Security=yes;";
}
}
result = new { key = true, value = _tokenCryptography.DoEncryption(connectionString) };
}
else
{
var sqlcedbScript = new FileInfo(AppDomain.CurrentDomain.BaseDirectory +
WebConfigurationManager.AppSettings["SystemConfigurationPath"] +
ServerSetup.SqlTables);
var appDataFolderPath = GlobalAppSettings.GetAppDataFolderPath();
if (Directory.Exists(appDataFolderPath) == false)
{
Directory.CreateDirectory(appDataFolderPath);
}
else
{
Array.ForEach(Directory.GetFiles(appDataFolderPath), System.IO.File.Delete);
}
var connStr = "Data Source = " + appDataFolderPath + "ReportServer.sdf; Password = reportserver";
using (var engine = new SqlCeEngine(connStr))
{
LogExtension.LogInfo("Creating SQLCE database", MethodBase.GetCurrentMethod());
engine.CreateDatabase();
}
var script = sqlcedbScript.OpenText().ReadToEnd();
SqlCeConnection conn = null;
try
{
conn = new SqlCeConnection(connStr);
conn.Open();
var cmd = conn.CreateCommand();
var splitter = new[] { ";" };
var commandTexts = script.Split(splitter, StringSplitOptions.RemoveEmptyEntries);
foreach (string commandText in commandTexts)
{
cmd.CommandText = commandText;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
LogExtension.LogInfo("Error in creating SQL CE Database", MethodBase.GetCurrentMethod());
LogExtension.LogError("Error in creating SQL CE Database", ex, MethodBase.GetCurrentMethod());
}
finally
{
if (conn != null)
{
conn.Close();
}
}
result =
new
{
key = true,
value = _tokenCryptography.DoEncryption(connStr)
};
}
return(Json(new { Data = result }));
}
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();
}
}
public void synctrucksfromSQLDBtoLocaldb()
{
string connectionString = sqlConnection;
SqlConnection cn = new SqlConnection(connectionString);
cn.Open();
string sql = "select count(*) from [dbo].[Tag_TruckAllocation] where RStatus='Active'";
SqlCommand cmd = new SqlCommand(sql, cn);
int flg;
flg = int.Parse(cmd.ExecuteScalar().ToString());
cn.Close();
if (flg == 0)
{
MessageBox.Show("No data in the SQLDB to sync.");
}
else
{
cn.Open();
string c = "Select min(Alltid) from [dbo].[Tag_TruckAllocation]";
SqlCommand c1 = new SqlCommand(c, cn);
Rid = int.Parse(c1.ExecuteScalar().ToString());
for (int i = 0; i < flg; i++)
{
string cmd1 = "select a.[Tkid],a.[Tagid],b.[TruckNo],c.[TagNo],a.[Alltid],a.[RStatus] from [dbo].[Tag_TruckAllocation] a, [dbo].[TruckMaster] b,[dbo].[TagMaster] c where a.[Alltid]=" + Rid + " and a.[Tkid]=b.[Tkid] and a.[Tagid]=c.[Tagid]";
SqlDataAdapter da = new SqlDataAdapter(cmd1, cn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
string Tkid = ds.Tables[0].Rows[0].ItemArray[0].ToString();
string Tagid = ds.Tables[0].Rows[0].ItemArray[1].ToString();
string truckno = ds.Tables[0].Rows[0].ItemArray[2].ToString();
string tagno = ds.Tables[0].Rows[0].ItemArray[3].ToString();
string id = ds.Tables[0].Rows[0].ItemArray[4].ToString();
//Connection to RFID SQLSERVER database
string CONN_STRING = localConnection;
SqlCeConnection dbCon = new SqlCeConnection(CONN_STRING);
try
{
dbCon.Open();
string Query = "Insert into TruckMaster ([ID],[Tkid],[TruckNo],[Tagid],[TagNo]) Values(" + id + ",'" + Tkid + "'," + "'" + truckno + "'" + ",'" + Tagid + "','" + tagno + "')";
SqlCeCommand cm = new SqlCeCommand(Query, dbCon);
cm.ExecuteNonQuery();
// updateTransctionstatusinTagRegTbl(tagno);
dbCon.Close();
//clearlocaldb();
}
catch
{
MessageBox.Show("Database Disconnected.");
}
//count--;
}
catch
{
MessageBox.Show("Details not found in the Database.");
}
cn.Close();
Rid++;
}
MessageBox.Show("Sync Complete.");
//MessageBox.Show("Successfully Saved..in Online.");
//_lbReadInfoReadWrite.Text = "Sync Complete";
}
}
public override bool ApplyOn()
{
csvCreation = true;
#region Version 1.0
var tradestudy = new ArrayList();
for (int i = 0; i < factors.Count; i++)
{
var options = new TS_Input_set("Data", factors[i].Name, "min", (double)startingValues[i], "max", (double)(startingValues[i] * noOfLevels[i]), "Increment", (double)noOfLevels[i]);
tradestudy.Add(options);
}
var treatmentTSInput = new Treatment_InOut_TS_Input(tradestudy);
#endregion Version 1.0
string directory = Path.GetDirectoryName(databaseFileName);
string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(databaseFileName);
string connectionString = $"DataSource=\"{databaseFileName}\"";
var connection = new SqlCeConnection(connectionString);
SqlCeCommand insertCmd = null;
string sql = "";
var filer = new CSVFiler(CsvPath);
try
{
#region Results Database Preparation
for (int i = 0; i < factors.Count; i++)
{
Result.MinValues.Add((double)(startingValues[i]));
if (stepSizes == null)
{
Result.MaxValues.Add((double)(arr[i][arr[i].Count() - 1]));
}
else
{
Result.MaxValues.Add((double)(startingValues[i] + (noOfLevels[i] - 1) * stepSizes[i]));
}
}
foreach (Data data in responses)
{
// Minimum and maximum values for result will be added later after execution of the workflow
responsesMinValues.Add(Double.PositiveInfinity);
responsesMaxValues.Add(Double.NegativeInfinity);
}
#endregion Results Database Preparation
#region Permutations Generation
var permutations = new List <List <decimal> >();
foreach (decimal init in arr[0])
{
var temp = new List <decimal> {
init
};
permutations.Add(temp);
}
for (int i = 1; i < arr.Length; ++i)
{
permutations = Permutation(permutations, arr[i]);
}
#endregion Permutations Generation
#region SDF File
if (sdfCreation)
{
#region Create tables
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
string createTableSQL = "create table " + fileNameWithoutExtension + " (ID int, ";
for (int i = 0; i < factors.Count(); i++)
{
string columnHeader = factors[i].Name;
createTableSQL += columnHeader + " ";
if (factors[i] is IntegerData)
{
createTableSQL += "int, ";
}
else if (factors[i] is DoubleData)
{
createTableSQL += "float, ";
}
}
for (int i = 0; i < responses.Count(); i++)
{
string columnHeader = responses[i].Name;
createTableSQL += columnHeader + " ";
if ((responses[i]) is IntegerData)
{
createTableSQL += "int, ";
}
else if (responses[i] is DoubleData)
{
createTableSQL += "float, ";
}
else if (responses[i] is DoubleVectorData)
{
createTableSQL += "nvarchar(2000), ";
}
else if (responses[i] is DoubleMatrixData)
{
createTableSQL += "nvarchar(4000), ";
}
}
if (factors.Count() + responses.Count() > 0)
{
createTableSQL = createTableSQL.Remove(createTableSQL.Length - 2);
}
createTableSQL += ")";
// Create SQL create table command for "SQL Server Compact Edition"
var createTableSQLCmd = new SqlCeCommand(createTableSQL, connection);
createTableSQLCmd.ExecuteNonQuery();
#endregion Create tables
#region Insert SQL Command
sql = "insert into " + fileNameWithoutExtension + " (ID, ";
string valuesString = "values (@ID, ";
for (int i = 0; i < factors.Count; i++)
{
sql += factors[i].Name + ", ";
valuesString += "@" + factors[i].Name + ", ";
}
for (int i = 0; i < responses.Count; i++)
{
sql += responses[i].Name + ", ";
valuesString += "@" + responses[i].Name + ", ";
}
if (factors.Count + responses.Count > 0)
{
sql = sql.Remove(sql.Length - 2);
valuesString = valuesString.Remove(valuesString.Length - 2);
}
sql += ")";
valuesString += ")";
sql += (" " + valuesString);
#endregion Insert SQL Command
}
#endregion SDF File
int tableID = 0;
int sz = factors.Count;
//int tot = (int)inf[1];
long tot = permutations.Count;
double[,] indices = new double[tot, sz];
long updatePeriod = Math.Max(tot / 100, 1);
foreach (List <decimal> list in permutations)
{
tableID++;
#region Parameter Value Assignment
for (int i = 0; i < list.Count; i++)
{
Data workflowInput = Component.ModelDataInputs.Find(delegate(Data d) { return(d.Name == factors[i].Name); });
if (workflowInput is IntegerData)
{
workflowInput.Value = (int)list[i];
}
if (workflowInput is DoubleData)
{
workflowInput.Value = (double)list[i];
}
}
#endregion Parameter Value Assignment
#region SDF Creation
if (sdfCreation)
{
insertCmd = new SqlCeCommand(sql, connection);
insertCmd.Parameters.AddWithValue("@ID", tableID);
for (int i = 0; i < list.Count; i++)
{
insertCmd.Parameters.AddWithValue("@" + factors[i].Name, list[i]);
}
}
#endregion SDF Creation
// Execute workflow
bool statusToCheck = Component.Execute();
for (int i = 0; i < responses.Count; i++)
{
// Store workflow data outputs as responses
Data workflowData = null;
workflowData = Component.ModelDataInputs.Find(delegate(Data d) { return(d.Name == responses[i].Name); });
if (workflowData == null)
{
workflowData = Component.ModelDataOutputs.Find(delegate(Data d) { return(d.Name == responses[i].Name); });
}
if (workflowData != null)
{
#region SDF Creation
if (sdfCreation)
{
if (workflowData is DoubleData)
{
responses[i].Value = Convert.ToDouble(workflowData.Value); //atif and xin 29042016
if (((double)(workflowData.Value)) < responsesMinValues[i])
{
responsesMinValues[i] = Convert.ToDouble(workflowData.Value);
}
if (((double)(workflowData.Value)) > responsesMaxValues[i])
{
responsesMaxValues[i] = Convert.ToDouble(workflowData.Value);
}
// Update database insert command
insertCmd.Parameters.AddWithValue("@" + responses[i].Name, (double)(responses[i].Value));
}
else if (workflowData is DoubleVectorData)
{
responses[i].Value = workflowData.Value;
// Update database insert command
string val = "";
foreach (double d in (double[])(responses[i].Value))
{
val += (d + ",");
}
val = val.TrimEnd(',');
insertCmd.Parameters.AddWithValue("@" + responses[i].Name, val);
}
else if (workflowData is DoubleMatrixData)
{
responses[i].Value = workflowData.Value;
// Update database insert command
double[,] data = (double[, ])(responses[i].Value);
string val = "";
for (int r = 0; r < data.GetLength(0); r++)
{
for (int c = 0; c < data.GetLength(1); c++)
{
val += (data[r, c] + ",");
}
val = val.TrimEnd(',');
val += ";";
}
val = val.TrimEnd(';');
insertCmd.Parameters.AddWithValue("@" + responses[i].Name, val);
}
else if (workflowData is IntegerData)
{
responses[i].Value = (int)(workflowData.Value);
if (((int)(workflowData.Value)) < responsesMinValues[i])
{
responsesMinValues[i] = (int)(workflowData.Value);
}
if (((int)(workflowData.Value)) > responsesMaxValues[i])
{
responsesMaxValues[i] = (int)(workflowData.Value);
}
// Update database insert command
insertCmd.Parameters.AddWithValue("@" + responses[i].Name, (int)(responses[i].Value));
}
else if (workflowData is IntegerVectorData)
{
responses[i].Value = workflowData.Value;
// Update database insert command
string val = "";
foreach (int d in (int[])(responses[i].Value))
{
val += (d + ",");
}
val = val.TrimEnd(',');
insertCmd.Parameters.AddWithValue("@" + responses[i].Name, val);
}
else
{
}
}
#endregion SDF Creation
}
}
// Execute database insert command
if (statusToCheck)
{
#region SDF Creation
if (sdfCreation)
{
insertCmd.ExecuteNonQuery();
}
#endregion SDF Creation
if (csvCreation)
{
filer.NewRow();
filer.AddToRow(tableID);
for (int i = 0; i < list.Count; i++)
{
filer.AddToRow(list[i]);
}
for (int i = 0; i < responses.Count; i++)
{
filer.AddToRow(responses[i]);
}
filer.WriteRow();
}
}
if (tableID % updatePeriod == 0)
{
ProgressReposter.ReportProgress(Convert.ToInt32(tableID * 100.0 / tot));
}
}
}
catch (SqlCeException sqlexception)
{
Console.WriteLine(sqlexception.Message, "Oh Crap.");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message, "Oh Crap.");
}
finally
{
connection.Close();
filer.Dispose();
ProgressReposter.ReportProgress(100);
}
// Results Min and Max values
for (int i = 0; i < responses.Count; i++)
{
Result.MinValues.Add(responsesMinValues[i]);
Result.MaxValues.Add(responsesMaxValues[i]);
}
return(true);
}
public void TableCheck()
{
//MessageBox.Show("Checking Table");
ChargeTable chargeTbl = new ChargeTable();
chargeTbl.ChargeList = new List <ChargeRecord>();
EmployeeTable employeeTbl = new EmployeeTable();
employeeTbl.EmployeeList = new List <EmployeeRecord>();
MeterFreeDateTable meterDateTbl = new MeterFreeDateTable();
meterDateTbl.MeterFreeDateList = new List <MeterFreeDateRecord>();
OfficerTable officerTbl = new OfficerTable();
officerTbl.OfficerList = new List <OfficerRecord>();
TagCategoryTable tagCatTbl = new TagCategoryTable();
tagCatTbl.TagCategoryList = new List <TagCategoryRecord>();
ParkingMeterTable parkMeterTbl = new ParkingMeterTable();
parkMeterTbl.ParkingMeterList = new List <ParkingMeter>();
DateTime LastUpdated = LastUpdateDate();
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://coaworks/api/publicsafety/parkingmetersync/table?"
+ "date=" + LastUpdated.ToShortDateString());
//HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://parking/api/syncapi/"
// + "?date=" + LastUpdated.ToShortDateString()
// + "&user="******"GET";
request.Timeout = 10000;
request.Credentials = CredentialCache.DefaultCredentials;
HttpWebResponse response;
Stream responseStream;
StreamReader reader;
try
{
response = request.GetResponse() as HttpWebResponse;
responseStream = response.GetResponseStream();
reader = new StreamReader(responseStream);
string responseText = reader.ReadToEnd();
JObject o = JObject.Parse(responseText);
#region Charges
JContainer chargesContainer = null;
if (o["charges"] != null && o["charges"].Type != JTokenType.Null)
{
chargesContainer = (JContainer)o["charges"];
List <ChargeRecord> chrgList = new List <ChargeRecord>();
for (int i = 0; i < chargesContainer.Count(); i++)
{
JToken chrg = chargesContainer[i];
ChargeRecord cr = new ChargeRecord();
if (chrg["Charge1"] != null && chrg["Charge1"].Type != JTokenType.Null)
{
cr.Charge = (string)chrg["Charge1"];
}
if (chrg["ChargeCode"] != null && chrg["ChargeCode"].Type != JTokenType.Null)
{
cr.ChargeCode = (string)chrg["ChargeCode"];
}
if (chrg["LocalOrd"] != null && chrg["LocalOrd"].Type != JTokenType.Null)
{
cr.LocalOrd = (string)chrg["LocalOrd"];
}
if (chrg["ViolationAmount"] != null && chrg["ViolationAmount"].Type != JTokenType.Null)
{
cr.ViolationAmount = (decimal)chrg["ViolationAmount"];
}
chrgList.Add(cr);
}
chargeTbl.ChargeList = chrgList;
}
else
{
chargesContainer = null;
}
#endregion
#region Employees
JContainer employeesContainer = null;
if (o["employees"] != null && o["employees"].Type != JTokenType.Null)
{
employeesContainer = (JContainer)o["employees"];
List <EmployeeRecord> empList = new List <EmployeeRecord>();
for (int i = 0; i < employeesContainer.Count(); i++)
{
JToken emp = employeesContainer[i];
EmployeeRecord er = new EmployeeRecord();
if (emp["EmployeeID"] != null && emp["EmployeeID"].Type != JTokenType.Null)
{
er.EmployeeID = (string)emp["EmployeeID"];
}
if (emp["EmployeeName"] != null && emp["EmployeeName"].Type != JTokenType.Null)
{
er.EmployeeName = (string)emp["EmployeeName"];
}
if (emp["UserName"] != null && emp["UserName"].Type != JTokenType.Null)
{
er.UserName = (string)emp["UserName"];
}
if (emp["Status"] != null && emp["Status"].Type != JTokenType.Null)
{
er.Status = (int)emp["Status"];
}
if (er.Status == 1)
{
empList.Add(er);
}
}
employeeTbl.EmployeeList = empList;
}
else
{
employeesContainer = null;
}
#endregion
#region MeterFreeDates
JContainer meterFreeDatesContainer = null;
if (o["meterFreeDates"] != null && o["meterFreeDates"].Type != JTokenType.Null)
{
meterFreeDatesContainer = (JContainer)o["meterFreeDates"];
List <MeterFreeDateRecord> mfDateList = new List <MeterFreeDateRecord>();
for (int i = 0; i < meterFreeDatesContainer.Count(); i++)
{
JToken freeDate = meterFreeDatesContainer[i];
MeterFreeDateRecord mfdr = new MeterFreeDateRecord();
if (freeDate["Date"] != null && freeDate["Date"].Type != JTokenType.Null)
{
mfdr.Date = (string)freeDate["Date"];
}
mfDateList.Add(mfdr);
}
meterDateTbl.MeterFreeDateList = mfDateList;
}
else
{
meterFreeDatesContainer = null;
}
#endregion
#region Officers
JContainer officersContainer = null;
if (o["officers"] != null && o["officers"].Type != JTokenType.Null)
{
officersContainer = (JContainer)o["officers"];
List <OfficerRecord> offRecList = new List <OfficerRecord>();
for (int i = 0; i < officersContainer.Count(); i++)
{
JToken officer = officersContainer[i];
OfficerRecord offR = new OfficerRecord();
if (officer["OfficerBadge"] != null && officer["OfficerBadge"].Type != JTokenType.Null)
{
offR.OfficerBadge = (string)officer["OfficerBadge"];
}
if (officer["EmployeeID"] != null && officer["EmployeeID"].Type != JTokenType.Null)
{
offR.EmployeeID = (string)officer["EmployeeID"];
}
if (officer["Rank"] != null && officer["Rank"].Type != JTokenType.Null)
{
offR.Rank = (string)officer["Rank"];
}
offRecList.Add(offR);
}
officerTbl.OfficerList = offRecList;
}
else
{
officersContainer = null;
}
#endregion
#region TagCategories
JContainer tagCategoriesContainer = null;
if (o["tagCategories"] != null && o["tagCategories"].Type != JTokenType.Null)
{
tagCategoriesContainer = (JContainer)o["tagCategories"];
List <TagCategoryRecord> tCatList = new List <TagCategoryRecord>();
for (int i = 0; i < tagCategoriesContainer.Count(); i++)
{
JToken tagCat = tagCategoriesContainer[i];
TagCategoryRecord tcr = new TagCategoryRecord();
if (tagCat["Description"] != null && tagCat["Description"].Type != JTokenType.Null)
{
tcr.Description = (string)tagCat["Description"];
}
if (tagCat["TagCategory1"] != null && tagCat["TagCategory1"].Type != JTokenType.Null)
{
tcr.TagCategory = (string)tagCat["TagCategory1"];
}
tCatList.Add(tcr);
}
tagCatTbl.TagCategoryList = tCatList;
}
else
{
tagCategoriesContainer = null;
}
#endregion
try
{
request.Abort();
response.Close();
responseStream.Close();
reader.Close();
}
catch (Exception ex) { }
}
catch (Exception ex)
{
request.Abort();
}
//This is away from the rest of the table downloading, mainly due to not wanting
//to interfere with the HttpWebResponse and HttpWebRequest
#region ParkingMeters
string connStr = "Data Source=atlas;Initial Catalog=Edits;";
using (SqlConnection sqlconn = new SqlConnection(connStr))
{
string queryStr = "SELECT * FROM Edits.dataloader.PARKINGMETERS_EVW WHERE LifecycleStatus = 'ACTIVE'";
SqlCommand comm = new SqlCommand(queryStr, sqlconn);
sqlconn.Open();
SqlDataReader sqlreader = comm.ExecuteReader();
try
{
while (sqlreader.Read())
{
string Location = sqlreader[2].ToString();
string facilityID = sqlreader[3].ToString();
parkMeterTbl.ParkingMeterList.Add(new ParkingMeter {
Location = Location, FacilityID = facilityID
});
}
}
finally
{
sqlreader.Close();
}
}
#endregion
SqlCeConnection cn = new SqlCeConnection(ConnectionString);
cn.Open();
if (cn.State == ConnectionState.Open)
{
try
{
bool NeedsUpdate = false;
#region Insert Charges
if (chargeTbl.ChargeList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM Charges ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (ChargeRecord CR in chargeTbl.ChargeList)
{
try
{
string cmdTxt2 = "Insert into Charges "
+ "(ChargeCode, Charge, LocalOrd, ViolationAmount)"
+ "values (@chargecode, @charge, @localord, @violationamount)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@chargecode", CR.ChargeCode);
cmd2.Parameters.AddWithValue("@charge", CR.Charge);
cmd2.Parameters.AddWithValue("@localord", CR.LocalOrd);
cmd2.Parameters.AddWithValue("@violationamount", CR.ViolationAmount);
cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
#endregion
#region Insert Employees
if (employeeTbl.EmployeeList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM Employees ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (EmployeeRecord ER in employeeTbl.EmployeeList)
{
try
{
string cmdTxt2 = "Insert into Employees "
+ "(EmployeeID, EmployeeName, UserName, Status)"
+ "values (@employeeid, @employeename, @username, @status)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@employeeid", ER.EmployeeID);
cmd2.Parameters.AddWithValue("@employeename", ER.EmployeeName);
cmd2.Parameters.AddWithValue("@username", ER.UserName);
cmd2.Parameters.AddWithValue("@status", ER.Status);
cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
#endregion
#region Insert MeterFreeDates
if (meterDateTbl.MeterFreeDateList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM MeterFreeDates ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (MeterFreeDateRecord MFDR in meterDateTbl.MeterFreeDateList)
{
try
{
string cmdTxt2 = "Insert into MeterFreeDates "
+ "(Date)"
+ "values (@date)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@date", MFDR.Date);
cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
#endregion
#region Insert Officers
if (officerTbl.OfficerList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM Officers ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (OfficerRecord OR in officerTbl.OfficerList)
{
try
{
string cmdTxt2 = "Insert into Officers "
+ "(OfficerBadge, EmployeeID, Rank)"
+ "values (@officerbadge, @employeeid, @rank)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@officerbadge", OR.OfficerBadge);
cmd2.Parameters.AddWithValue("@employeeid", OR.EmployeeID);
if (OR.Rank == null)
{
cmd2.Parameters.AddWithValue("@rank", DBNull.Value);
}
else
{
cmd2.Parameters.AddWithValue("@rank", OR.Rank);
}
cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
#endregion
#region Insert TagCategories
if (tagCatTbl.TagCategoryList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM TagCategory ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (TagCategoryRecord TCR in tagCatTbl.TagCategoryList)
{
string cmdTxt2 = "Insert into TagCategory "
+ "(TagCategory, Description)"
+ "values (@tagcategory, @description)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@tagcategory", TCR.TagCategory);
cmd2.Parameters.AddWithValue("@description", TCR.Description);
cmd2.ExecuteNonQuery();
}
}
#endregion
#region Insert Parking Meters
if (parkMeterTbl.ParkingMeterList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM ParkingMeters ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (ParkingMeter prkMeter in parkMeterTbl.ParkingMeterList)
{
string cmdTxt2 = "Insert into ParkingMeters "
+ "(Location, FacilityID)"
+ "values (@location, @facilityid)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@location", prkMeter.Location);
cmd2.Parameters.AddWithValue("@facilityid", prkMeter.FacilityID);
cmd2.ExecuteNonQuery();
}
}
#endregion
//MessageBox.Show("Needs Update is " + NeedsUpdate.ToString());
if (NeedsUpdate)
{
UpdateLastUpdateDate();
}
}
catch (Exception ex)
{
PopUpForm pf = new PopUpForm(ex.ToString());
pf.ShowDialog();
}
finally
{
cn.Close();
}
}
}
private void btnRequestSync_Click(object sender, EventArgs e)
{
signal();
if (strength < -5 && strength > -95)
{
txtSyncStatus.Visible = true;
try
{
classLog.writeLog("Message @: Sync Intialized...");
string CONNSTRING = sqlConnection;
if (cmbSyncData.Text != "----Select----" && cmbSyncData.Text != "" && cmbSyncData.Text != "System.Data.DataRowView")
{
cmbSyncData.Enabled = false; btnRequestSync.Enabled = false;
lblStatus.Text = "";
string sql = "";
if (cmbSyncData.Text == "User Master")
{
sql = " Select UId,Loginid,Password,LoginType,UserName from [dbo].[HHLoginMst] where [UStatus]='Active'";
}
else if (cmbSyncData.Text == "Loader Master")
{
sql = " Select LId,LoaderName,LoaderCode from [dbo].[LoaderMst] where [LStatus]='Active'";
}
else if (cmbSyncData.Text == "Event Master")
{
sql = "Select [EId],[EventName],[EventCode],[Status],[Remarks],[CreatedBy] from [dbo].[EventMaster] where [Status]='Active'";
//classLog.writeLog("Message @:"+ sql.ToString());
}
else if (cmbSyncData.Text == "Delegate Master")
{
sql = "SELECT [DMId],[DelegateName],[Category],[Company],[NoofPeople],[PAName],[PAMobileNo],[VehicleNo],[HostName],[HostMobileNo],[Remarks],[VPCategory],[Location],[TagNo] FROM [dbo].[view_DelegateDetails] order by [DMId] asc";
}
else if (cmbSyncData.Text == "Delegate Master-SQL Local")
{
string DMId = getMaxDMId(); if (DMId.Trim() == "0" || DMId.Trim() == "")
{
sql = "SELECT [DMId],[DelegateName],[Category],[Company],[NoofPeople],[PAName],[PAMobileNo],[VehicleNo],[HostName],[HostMobileNo],[Remarks],[VPCategory],[Location],[TagNo] FROM [dbo].[view_DelegateDetails] order by [DMId] asc";
}
else
{
sql = "SELECT [DMId],[DelegateName],[Category],[Company],[NoofPeople],[PAName],[PAMobileNo],[VehicleNo],[HostName],[HostMobileNo],[Remarks],[VPCategory],[Location],[TagNo] FROM [dbo].[view_DelegateDetails] where [DMId]>" + DMId + " order by [DMId] asc";
}
CONNSTRING = sqlConnection1;
}
//MessageBox.Show(CONNSTRING.ToString());
//MessageBox.Show(sql.ToString()); classLog.writeLog("Message @:" + sql.ToString());
SqlConnection dbCon = new SqlConnection(CONNSTRING);
//try
//{
dbCon.Open();
SqlDataAdapter da = new SqlDataAdapter(sql, dbCon); classLog.writeLog("Message @:" + da.ToString());
DataTable dt = new DataTable(); int i = 1;
da.Fill(dt); dbCon.Close();
if (cmbSyncData.Text == "User Master")
{
clearlocaldbUserMaster(); classLog.writeLog("Message @:User Master Cleared.");
}
else if (cmbSyncData.Text == "Loader Master")
{
clearlocaldbLoaderMaster();
}
else if (cmbSyncData.Text == "Event Master")
{
clearlocaldbEventMaster(); classLog.writeLog("Message @:Event Master Cleared.");
}
else if (cmbSyncData.Text == "Delegate Master")
{
clearlocaldbDelegateMaster(); classLog.writeLog("Message @:Delegate Master Cleared.");
}
//................................................
string datet = System.DateTime.Now.ToString("yyy-MM-dd HH:mm:ss");
string ndt = "{ts '" + datet + "'}";
lblStatus.Text = "Sync In Progress....";
string InsertQry = "";
try
{
classLog.writeLog("Message @:" + cmbSyncData.Text + " Syncing Records Count:" + dt.Rows.Count.ToString());
if (dt.Rows.Count > 0)
{
if (dt.Rows.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
txtSyncStatus.Text = cmbSyncData.Text + "Sync:" + dt.Rows.Count.ToString() + "-" + i.ToString();
if (dr != null)
{
lblStatus.Text = "Sync ....";
if (cmbSyncData.Text == "CargoGroup Master")
{
InsertQry = "INSERT INTO [CargoGroupMst] ([CargoGroupId],[CargoGroupName],[RStatus]) Values ('" + dr[0].ToString() + "','" + dr[1].ToString() + "','Active')";
}
else if (cmbSyncData.Text == "Commodity Master")
{
InsertQry = "INSERT INTO [CommodityMst] ([CommodityId],[CommodityName],[CargoGroupId],[RFIDCode],[RStatus]) Values ('" + dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() + "','" + dr[3].ToString() + "','Active')";
}
else if (cmbSyncData.Text == "User Master")
{
InsertQry = "INSERT INTO [UserMst] ([Id],[LoginId],[Password],[LoginType],[UserName],[Status]) Values ('" + dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() + "','" + dr[3].ToString() + "','" + dr[4].ToString() + "','Active')";
}
else if (cmbSyncData.Text == "Loader Master")
{
InsertQry = "INSERT INTO [LoaderMst] ([Id],[LoaderName],[LoaderCode],[Status]) Values ('" + dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() + "','Active')";
}
else if (cmbSyncData.Text == "Event Master")
{
InsertQry = "INSERT INTO [EventMaster] ([EId],[EventName],[EventCode],[Status],[Remarks],[CreatedBy],[CreatedTime]) Values ('" + dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() + "','" + dr[3].ToString() + "','" + dr[4].ToString() + "','" + dr[5].ToString() + "'," + ndt + ")";
}
else if (cmbSyncData.Text == "Delegate Master" || cmbSyncData.Text == "Delegate Master-SQL Local")
{
InsertQry = "INSERT INTO [DelegateMaster] ([DMId],[DelegateName],[Category],[Company],[NoofPeople],[PAName],[PAMobileNo],[VehicleNo],[HostName],[HostMobileNo],[Remarks],[VPCategory],[EventLocationsAllowed],[CreatedBy],[CreatedTime],[TagNo]) Values (" + dr[0].ToString() + ",'" + dr[1].ToString() + "','" + dr[2].ToString() + "','" + dr[3].ToString() + "','" + dr[4].ToString() + "','" + dr[5].ToString() + "','" + dr[6].ToString() + "','" + dr[7].ToString() + "','" + dr[8].ToString() + "','" + dr[9].ToString() + "','" + dr[10].ToString() + "','" + dr[11].ToString() + "','" + dr[12].ToString() + "','" + classLogin.User + "'," + ndt + ",'" + dr[13].ToString() + "')";
}
string CONN_STRING = localConnection;
SqlCeConnection Con = new SqlCeConnection(CONN_STRING);
Con.Open();
SqlCeCommand cmd = new SqlCeCommand(InsertQry, Con);
cmd.ExecuteNonQuery();
Con.Close();
i++;
//lblStatus.Text = "Sync In Progress....";
//System.Threading.Thread.Sleep(5000);
}
}
}
else
{
lblStatus.Text = "No Data to Sync.";
}
}
else
{
throw new Exception("No row for insertion");
lblStatus.Text = "No Data to Sync.";
}
dt.Dispose();
lblStatus.Text = "Sync Complete.";
classLog.writeLog("Message @:" + cmbSyncData.Text + " Records Count:" + dt.Rows.Count.ToString() + "Sync Completed.");
}
catch (Exception ex)
{
dt.Dispose(); classLog.writeLog("Error @:Sync Master :" + ex.ToString());
//throw new Exception("Please attach file in Proper format.");
}
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.ToString());
//}
}
cmbSyncData.Enabled = true; btnRequestSync.Enabled = true;
}
catch (Exception ex)
{
lblStatus.Text = "Wifi Not Available.";
classLog.writeLog("Error @:Sync Master :" + ex.ToString());
} txtSyncStatus.Visible = false;
}
else
{
lblStatus.Text = "Wifi Not Available.";
}
}
/// <summary>
///
/// </summary>
/// <param name="connectionString"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
public static int ExecuteNonQuery(
string connectionString,
CommandType commandType,
string commandText,
params SqlCeParameter[] commandParameters
)
{
try
{
int rowsAffected;
using (SqlCeConnection conn = new SqlCeConnection())
{
// this is for multiple queries in the installer
if (commandText.Trim().StartsWith("!!!"))
{
commandText = commandText.Trim().Trim('!');
string[] commands = commandText.Split('|');
string currentCmd = String.Empty;
conn.ConnectionString = connectionString;
conn.Open();
foreach (string cmd in commands)
{
try
{
currentCmd = cmd;
if (!String.IsNullOrWhiteSpace(cmd))
{
SqlCeCommand c = new SqlCeCommand(cmd, conn);
c.ExecuteNonQuery();
}
}
catch (Exception e)
{
Debug.WriteLine("*******************************************************************");
Debug.WriteLine(currentCmd);
Debug.WriteLine(e);
Debug.WriteLine("*******************************************************************");
}
}
return(1);
}
else
{
Debug.WriteLine("----------------------------------------------------------------------------");
Debug.WriteLine(commandText);
Debug.WriteLine("----------------------------------------------------------------------------");
conn.ConnectionString = connectionString;
conn.Open();
SqlCeCommand cmd = new SqlCeCommand(commandText, conn);
AttachParameters(cmd, commandParameters);
rowsAffected = cmd.ExecuteNonQuery();
}
}
return(rowsAffected);
}
catch (Exception ee)
{
throw new SqlCeProviderException("Error running NonQuery: \nSQL Statement:\n" + commandText + "\n\nException:\n" + ee.ToString());
}
}
static Database()
{
_Connection = new SqlCeConnection(string.Format("Data Source = {0}", Properties.Settings.Default.DatabaseFile));
_Connection.Open();
}
private void DoQuery()
{
string sQuery = "SELECT * FROM [DICOMServerEventLog]";
decimal nCount = numericUpDownLastLogs.Value;
if (checkBoxLastLogs.Checked)
{
sQuery = string.Format("SELECT TOP ({0}) * FROM [DICOMServerEventLog]", nCount);
}
bool bAnd = false;
if (checkBoxServerAeTitle.Checked)
{
string sSearchValue = textBoxServerAeTitle.Text.Trim();
sSearchValue = sSearchValue.Replace("*", string.Empty);
string sAppend = string.Format(" {0} ServerAETitle like '{1}%'", bAnd ? "AND" : "WHERE", sSearchValue);
sQuery = sQuery + sAppend;
bAnd = true;
}
if (checkBoxClientAeTitle.Checked)
{
string sSearchValue = textBoxClientAeTitle.Text.Trim();
sSearchValue = sSearchValue.Replace("*", string.Empty);
string sAppend = string.Format(" {0} ClientAETitle like '{1}%'", bAnd ? "AND" : "WHERE", sSearchValue);
sQuery = sQuery + sAppend;
bAnd = true;
}
sQuery = string.Concat(sQuery, " ORDER BY EventID");
listViewEventLog.Items.Clear();
string connectionString = GetConnectionString();
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
connection.Open();
using (SqlCeCommand com = new SqlCeCommand(sQuery, connection))
{
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
try
{
object id = reader["EventID"];
int nEventId = Convert.ToInt32(id);
string serverAeTitle = (string)reader["ServerAETitle"].ToString();
string serverIpAddress = (string)reader["ServerIPAddress"].ToString();
string serverPort = GetPortString(reader["ServerPort"]);
string clientAeTitle = (string)reader["ClientAETitle"].ToString();
string clientHostAddress = (string)reader["ClientHostAddress"].ToString();
string clientPort = GetPortString(reader["ClientPort"]);
string command = (string)reader["Command"].ToString();
string eventDateTime = (string)reader["EventDateTime"].ToString();
// string logType = (string)reader["Type"].ToString();
string messageDirection = (string)reader["MessageDirection"].ToString();
string datasetPath = (string)reader["DatasetPath"].ToString();
string description = (string)reader["Description"].ToString();
// CustomInformation
if (string.Compare(command, "Undefined", true) == 0)
{
command = string.Empty;
}
if (string.Compare(messageDirection, "None", true) == 0)
{
messageDirection = string.Empty;
}
ListViewItem item = listViewEventLog.Items.Add(serverAeTitle);
item.Tag = new EventLogItem(nEventId, datasetPath);
if (item != null)
{
item.SubItems.Add(serverIpAddress); // 1
item.SubItems.Add(serverPort); // 2
item.SubItems.Add(clientAeTitle); // 3
item.SubItems.Add(clientHostAddress); // 4
item.SubItems.Add(clientPort.ToString()); // 5
item.SubItems.Add(command); // 6
item.SubItems.Add(eventDateTime); // 7
// item.SubItems.Add(logType);
item.SubItems.Add(messageDirection); // 8
item.SubItems.Add(description); // 9
item.SubItems.Add(datasetPath); // 10
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, @"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}
public DBService()
{
_connection.Open();
}
private void WriteLine()
{
// try
// {
if (czydodac == 1)
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmdb = cn.CreateCommand();
cmdb.CommandText = "INSERT INTO fedibody (NrDok, Nazwa, kod, Vat, Jm, Asortyment, Ilosc, Cena, IleWOpak, CenaSp, ebid, Wymagane) VALUES (@a, @b, @c, @d, @e, @f, @g, @h, @i, @j, @k, @l)";
cmdb.Parameters.Add("@a", SqlDbType.NVarChar, 30);
cmdb.Parameters.Add("@b", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@c", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@d", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@e", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@f", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@g", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@h", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@i", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@j", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@k", SqlDbType.Int, 10);
cmdb.Parameters.Add("@l", SqlDbType.NVarChar, 10);
cmdb.Parameters["@a"].Value = indeks;
cmdb.Parameters["@b"].Value = nazwa_t.Text;
cmdb.Parameters["@c"].Value = kod_t.Text;
cmdb.Parameters["@d"].Value = vat_t.Text;
cmdb.Parameters["@e"].Value = jm_t.Text;
cmdb.Parameters["@f"].Value = asorcik1;
cmdb.Parameters["@g"].Value = ilosc_t.Text;
cmdb.Parameters["@h"].Value = cena_t.Text;
cmdb.Parameters["@i"].Value = wopak_t.Text;
cmdb.Parameters["@j"].Value = cenasp_t.Text;
cmdb.Parameters["@k"].Value = ebid;
cmdb.Parameters["@l"].Value = wymagane_t.Text;
cmdb.Prepare();
cmdb.ExecuteNonQuery();
SqlCeCommand cmd1 = cn.CreateCommand();
cmd1.CommandText = "SELECT kod, NrDok, Ilosc FROM fedibody WHERE kod = ? and NrDok = ?";
cmd1.Parameters.Add("@k", SqlDbType.NVarChar, 30);
cmd1.Parameters.Add("@d", SqlDbType.NVarChar, 30);
cmd1.Parameters["@k"].Value = kodzik;
cmd1.Parameters["@d"].Value = indeks;
cmd1.Prepare();
zliczono = "0";
SqlCeDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
zliczono = ((decimal.Parse(zliczono) + decimal.Parse(dr1.GetString(2))).ToString());
}
if (decimal.Parse(zliczono) >= decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'Ok', complete = 1 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Parameters["@a"].Value = ebid;
cmdc.Prepare();
cmdc.ExecuteNonQuery();
}
else if (decimal.Parse(zliczono) < decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'W trakcie', complete = 0 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Parameters["@a"].Value = ebid;
cmdc.Prepare();
cmdc.ExecuteNonQuery();
}
int toclose = 0;
SqlCeCommand cmd2 = cn.CreateCommand();
cmd2.CommandText = "SELECT count(id), complete FROM edibody WHERE NrDok = ? and complete = 0 GROUP BY complete";
cmd2.Parameters.Add("@d", SqlDbType.NVarChar, 30);
cmd2.Parameters["@d"].Value = indeks;
cmd2.Prepare();
SqlCeDataReader dr2 = cmd2.ExecuteReader();
while (dr2.Read())
{
toclose = dr2.GetInt32(0);
}
if (toclose == 0)
{
SqlCeCommand cmdf = cn.CreateCommand();
cmdf.CommandText = "UPDATE edihead SET status = 'OK', complete = 1 WHERE NrDok = ?";
cmdf.Parameters.Add("@a", SqlDbType.NVarChar, 30);
cmdf.Parameters["@a"].Value = indeks;
cmdf.Prepare();
cmdf.ExecuteNonQuery();
}
cn.Close();
form17.Loaddata();
this.Close();
}
else if (czydodac == 0)
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmdb = cn.CreateCommand();
cmdb.CommandText = "INSERT INTO edibody (NrDok, Nazwa, kod, Vat, Jm, Asortyment, Ilosc, Cena, IleWOpak, CenaSp, status, complete) VALUES (@a, @b, @c, @d, @e, @f, @g, @a0, @a2, @3, @4, @5)";
cmdb.Parameters.Add("@a", SqlDbType.NVarChar, 30);
cmdb.Parameters.Add("@b", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@c", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@d", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@e", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@f", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@g", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@a0", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@a2", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@a3", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@a4", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@a5", SqlDbType.Bit);
cmdb.Parameters["@a"].Value = Convert.ToString(indeks);
cmdb.Parameters["@b"].Value = nazwa_t.Text;
cmdb.Parameters["@c"].Value = kod_t.Text;
cmdb.Parameters["@d"].Value = vat_t.Text;
cmdb.Parameters["@e"].Value = jm_t.Text;
cmdb.Parameters["@f"].Value = asorcik1;
cmdb.Parameters["@g"].Value = ilosc_t.Text;
cmdb.Parameters["@a0"].Value = cena_t.Text;
cmdb.Parameters["@a2"].Value = wopak_t.Text;
cmdb.Parameters["@a3"].Value = cenasp_t.Text;
cmdb.Parameters["@a4"].Value = "Nowy";
cmdb.Parameters["@a5"].Value = byte.Parse("0");
cmdb.Prepare();
cmdb.ExecuteNonQuery();
cn.Close();
form17.Loaddata();
this.Close();
}
else if (czydodac == 2)
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmdb = cn.CreateCommand();
cmdb.CommandText = "Update edibody set Ilosc = ? WHERE id = ?";
cmdb.Parameters.Add("@a", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@b", SqlDbType.Int, 10);
cmdb.Prepare();
cmdb.Parameters["@a"].Value = ilosc_t.Text;
cmdb.Parameters["@b"].Value = ebid;
cmdb.ExecuteNonQuery();
SqlCeCommand cmd1 = cn.CreateCommand();
cmd1.CommandText = "SELECT kod, NrDok, Ilosc FROM fedibody WHERE kod = ? and NrDok = ?";
cmd1.Parameters.Add("@k", SqlDbType.NVarChar, 30);
cmd1.Parameters.Add("@d", SqlDbType.NVarChar, 30);
cmd1.Parameters["@k"].Value = kodzik;
cmd1.Parameters["@d"].Value = indeks;
cmd1.Prepare();
zliczono = "0";
SqlCeDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
zliczono = ((decimal.Parse(zliczono) + decimal.Parse(dr1.GetString(2))).ToString());
}
if (decimal.Parse(zliczono) >= decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'Ok', complete = 1 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Prepare();
cmdc.Parameters["@a"].Value = ebid;
cmdc.ExecuteNonQuery();
}
else if (decimal.Parse(zliczono) < decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'W trakcie', complete = 0 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Prepare();
cmdc.Parameters["@a"].Value = ebid;
cmdc.ExecuteNonQuery();
}
cn.Close();
form17.Loaddata();
this.Close();
}
else if (czydodac == 3)
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmdb = cn.CreateCommand();
cmdb.CommandText = "Update fedibody set Ilosc = ? WHERE id = ?";
cmdb.Parameters.Add("@a", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@b", SqlDbType.Int, 10);
cmdb.Prepare();
cmdb.Parameters["@a"].Value = ilosc_t.Text;
cmdb.Parameters["@b"].Value = ebid;
cmdb.ExecuteNonQuery();
SqlCeCommand cmd1 = cn.CreateCommand();
cmd1.CommandText = "SELECT kod, NrDok, Ilosc FROM fedibody WHERE kod = ? and NrDok = ?";
cmd1.Parameters.Add("@k", SqlDbType.NVarChar, 20);
cmd1.Parameters.Add("@d", SqlDbType.NVarChar, 20);
cmd1.Parameters["@k"].Value = kodzik;
cmd1.Parameters["@d"].Value = indeks;
cmd1.Prepare();
zliczono = "0";
SqlCeDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
zliczono = ((decimal.Parse(zliczono) + decimal.Parse(dr1.GetString(2))).ToString());
}
if (decimal.Parse(zliczono) >= decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'Ok', complete = 1 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Prepare();
cmdc.Parameters["@a"].Value = ebid;
cmdc.ExecuteNonQuery();
}
else if (decimal.Parse(zliczono) < decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'W trakcie', complete = 0 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Prepare();
cmdc.Parameters["@a"].Value = ebid;
cmdc.ExecuteNonQuery();
}
cn.Close();
form17.Loaddata();
this.Close();
}
}
public void AutoThreadingTest()
{
Microsoft.SqlServer.Dts.Runtime.Package package;
IDTSComponentMetaData100 multipleHash;
CManagedComponentWrapper multipleHashInstance;
String lineageString;
MainPipe dataFlowTask;
// Microsoft.SqlServer.Dts.Runtime.Application app;
StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask /*, out app */);
multipleHash.CustomPropertyCollection[Utility.MultipleThreadPropName].Value = MultipleHash.MultipleThread.Auto;
int outputID = multipleHash.OutputCollection[0].ID;
int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count;
// Add output column SHA1BinaryOutput (SHA1, Binary)
IDTSOutputColumn100 SHA1BinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA1BinaryOutput", "SHA1 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New();
SHA1BinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary;
SHA1BinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA1;
SHA1BinaryOutput.Name = "SHA1BinaryOutput";
SHA1BinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString;
Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA1, MultipleHash.OutputTypeEnumerator.Binary, SHA1BinaryOutput);
// Add output column SHA1HexOutput (SHA1, HexString)
IDTSOutputColumn100 SHA1HexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA1HexOutput", "SHA1 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New();
SHA1HexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString;
SHA1HexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA1;
SHA1HexOutput.Name = "SHA1HexOutput";
SHA1HexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString;
Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA1, MultipleHash.OutputTypeEnumerator.HexString, SHA1HexOutput);
// Add output column SHA1BaseOutput (SHA1, Base64String)
IDTSOutputColumn100 SHA1BaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA1BaseOutput", "SHA1 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New();
SHA1BaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String;
SHA1BaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA1;
SHA1BaseOutput.Name = "SHA1BaseOutput";
SHA1BaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString;
Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA1, MultipleHash.OutputTypeEnumerator.Base64String, SHA1BaseOutput);
// Add SQL CE Destination
// Add SQL CE Connection
ConnectionManager sqlCECM = null;
IDTSComponentMetaData100 sqlCETarget = null;
CManagedComponentWrapper sqlCEInstance = null;
StaticTestUtilities.CreateSQLCEComponent(package, dataFlowTask, sqlCEDatabaseName, sqlCEPassword, "TestRecords", out sqlCECM, out sqlCETarget, out sqlCEInstance);
StaticTestUtilities.CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance);
// Create a package events handler, to catch the output when running.
PackageEventHandler packageEvents = new PackageEventHandler();
// Execute the package
Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute(null, null, packageEvents as IDTSEvents, null, null);
foreach (String message in packageEvents.eventMessages)
{
Debug.WriteLine(message);
}
// Make sure the package worked.
Assert.AreEqual(Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, result, "Execution Failed");
// Connect to the SQLCE database
SqlCeConnection connection = new SqlCeConnection(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword));
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM [TestRecords] ORDER BY [StringData]", connection);
SqlCeDataReader sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default);
int rowCount = 0;
while (sqlData.Read())
{
rowCount++;
switch (rowCount)
{
case 1:
StaticTestUtilities.testValues20("SHA1", sqlData, "NullRow", null, "7404459d2254bfcd2d55cddf90fb29c751012310", "dARFnSJUv80tVc3fkPspx1EBIxA=");
break;
case 2:
StaticTestUtilities.testValues20("SHA1", sqlData, "StringData1", "MoreStringData1", "a007b9d8891448bf08ab2e87dc36cc4fadc7ec94", "oAe52IkUSL8Iqy6H3DbMT63H7JQ=");
break;
case 3:
StaticTestUtilities.testValues20("SHA1", sqlData, "StringData2", "MoreStringData2", "b7e5cdd69e366cabd2ecbabe23e2e270cf1d908e", "t+XN1p42bKvS7Lq+I+LicM8dkI4=");
break;
case 4:
StaticTestUtilities.testValues20("SHA1", sqlData, "StringData3", "MoreStringData3", "4b135b551bcb7383c6a3c3151db51ab5862a0a65", "SxNbVRvLc4PGo8MVHbUatYYqCmU=");
break;
case 5:
StaticTestUtilities.testValues20("SHA1", sqlData, "StringData4", "MoreStringData4", "053d62760fea89724505e4f15fbc7d299cf6423f", "BT1idg/qiXJFBeTxX7x9KZz2Qj8=");
break;
default:
Assert.Fail(string.Format("Account has to many records AccountCode {0}, AccountName {1}", sqlData.GetInt32(1), sqlData.GetString(2)));
break;
}
}
Assert.AreEqual(5, rowCount, "Rows in TestRecords");
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
}
/// <summary>
/// 执行插入或更新实体
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="entity">实体对象</param>
/// <param name="cmdtype">INSERT 或 UPDATE</param>
/// <returns>Boolean</returns>
public Boolean ExecuteCommand <T>(T entity, CmdType cmdtype)
{
if (cmdtype != CmdType.INSERT && cmdtype != CmdType.UPDATE)
{
throw new Exception("只支持INSERT 或 UPDATE命令.");
}
Boolean flag = false;
#region 转换为List对象
Type baseType = typeof(T);
Type makeType = baseType.IsGenericType ? baseType.GetGenericArguments()[0] : baseType;
Type makelist = typeof(List <>).MakeGenericType(makeType);
Object list;
if (baseType.IsGenericType)
{
list = entity;
//MethodInfo add = makelist.GetMethod("Add");
//MethodInfo _getenumerator = baseType.GetMethod("GetEnumerator");
//IEnumerator _items = _getenumerator.Invoke(entity, null) as IEnumerator;
//while (_items.MoveNext())
//{
// add.Invoke(list, new Object[] { _items.Current });
//}
}
else
{
list = Activator.CreateInstance(makelist);
/*--makelist.GetMethod("Add").Invoke(list, new Object[] { entity });*/
makelist.GetMethod("Add").FastInvoke(list, new Object[] { entity });
}
#endregion
String tableName = String.Empty;
TableAttribute[] _atttable = (TableAttribute[])makeType.GetCustomAttributes(typeof(TableAttribute), false);
if (_atttable != null && _atttable.Length > 0)
{
tableName = _atttable[0].TableName;
}
if (String.IsNullOrEmpty(tableName))
{
return(false);
}
PropertyInfo[] properties = makeType.GetProperties();
using (SqlCeConnection connection = new SqlCeConnection(this.ConnectionString))
{
connection.Open();
MethodInfo enumerator = makelist.GetMethod("GetEnumerator");
/*--IEnumerator items = enumerator.Invoke(list, null) as IEnumerator;*/
IEnumerator items = enumerator.FastInvoke(list, null) as IEnumerator;
while (items.MoveNext())
{
Object item = items.Current;
String sqlStr = String.Empty;
String sqlField = String.Empty;
String sqlWhere = String.Empty;
String sqlParam = String.Empty;
String sqlValue = String.Empty;
using (SqlCeCommand command = new SqlCeCommand())
{
#region 构造SQL语句
foreach (PropertyInfo field in properties)
{
if (!field.CanRead)
{
continue;
}
/*--Object value = field.GetValue(item, null);*/
Object value = field.FastGetValue(item);
if (value == null || value == DBNull.Value)
{
continue;
}
FieldAttribute[] attfield = (FieldAttribute[])field.GetCustomAttributes(typeof(FieldAttribute), false);
if (attfield != null && attfield.Length > 0)
{
FieldAttribute att = attfield[0];
if (att.IsSeed || att.IsVirtual || att.IsIgnore)
{
continue;
}
if (att.IsPrimaryKey)
{
if (cmdtype == CmdType.UPDATE)
{
sqlWhere = sqlWhere + "[" + att.Name + "]=@" + att.Name + " AND ";
command.Parameters.Add(new SqlCeParameter(att.Name, value));
}
}
else if (att.isAllowEdit)
{
if (cmdtype == CmdType.INSERT)
{
sqlParam = sqlParam + "[" + att.Name + "],";
sqlValue = sqlValue + "@" + att.Name + ",";
}
else if (cmdtype == CmdType.UPDATE)
{
sqlField = sqlField + "[" + att.Name + "]=@" + att.Name + ",";
}
command.Parameters.Add(new SqlCeParameter(att.Name, value));
}
}
}
if (cmdtype == CmdType.INSERT)
{
sqlStr = "INSERT INTO [" + tableName + "] (" + sqlParam.Substring(0, sqlParam.Length - 1) + ") VALUES (" + sqlValue.Substring(0, sqlValue.Length - 1) + ")";
}
else if (cmdtype == CmdType.UPDATE)
{
sqlStr = "UPDATE [" + tableName + "] SET " + sqlField.Substring(0, sqlField.Length - 1);
}
if (cmdtype == CmdType.UPDATE && sqlWhere != "")
{
sqlWhere = " WHERE " + sqlWhere.Substring(0, sqlWhere.Length - 4);
sqlStr = sqlStr + sqlWhere;
}
#endregion
command.CommandText = sqlStr;
command.Connection = connection;
using (SqlCeTransaction transaction = command.Connection.BeginTransaction())
{
try
{
command.Transaction = transaction;
int num = command.ExecuteNonQuery();
transaction.Commit();
flag = num >= 0;
if (!flag)
{
break;
}
}
catch (Exception ex)
{
transaction.Rollback();
throw new ApplicationException(ex.Message);
}
finally { }
}
}
}
}
return(flag);
}
private void button1_Click(object sender, System.EventArgs e)
{
// Get Template document and database path.
string dataPath = Application.StartupPath + @"\..\..\..\..\..\..\..\Common\Data\DocIO\";
try
{
//SDF and get the NorthWind
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
DataTable table = new DataTable();
SqlCeConnection conn = new SqlCeConnection();
if (conn.ServerVersion.StartsWith("3.5"))
{
conn.ConnectionString = "Data Source = " + dataPath + "..\\NorthwindIO_3.5.sdf";
}
else
{
conn.ConnectionString = "Data Source = " + dataPath + "..\\NorthwindIO.sdf";
}
conn.Open();
SqlCeDataAdapter adapter = new SqlCeDataAdapter("Select TOP(5) * from EmployeesReport", conn);
adapter.Fill(table);
adapter.Dispose();
conn.Close();
// Creating a new document.
WordDocument document = new WordDocument();
// Load template
document.Open(Path.Combine(dataPath, "EmployeesReportDemo.doc"), FormatType.Doc);
// Set table name as Employess for template mergefield reference.
table.TableName = "Employees";
// Execute Mail Merge with groups.
document.MailMerge.ExecuteGroup(table);
//Save as doc format
if (wordDocRadioBtn.Checked)
{
//Saving the document to disk.
document.Save("Sample.doc");
//Message box confirmation to view the created document.
if (MessageBoxAdv.Show("Do you want to view the generated Word document?", "Document has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
//Launching the MS Word file using the default Application.[MS Word Or Free WordViewer]
System.Diagnostics.Process.Start("Sample.doc");
//Exit
this.Close();
}
}
//Save as docx format
else if (wordDocxRadioBtn.Checked)
{
//Saving the document as .docx
document.Save("Sample.docx", FormatType.Docx);
//Message box confirmation to view the created document.
if (MessageBoxAdv.Show("Do you want to view the generated Word document?", "Document has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
try
{
//Launching the MS Word file using the default Application.[MS Word Or Free WordViewer]
System.Diagnostics.Process.Start("Sample.docx");
//Exit
this.Close();
}
catch (Win32Exception ex)
{
MessageBoxAdv.Show("Microsoft Word Viewer or Microsoft Word is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
}
//Save as pdf format
else if (pdfRadioBtn.Checked)
{
DocToPDFConverter converter = new DocToPDFConverter();
//Convert word document into PDF document
PdfDocument pdfDoc = converter.ConvertToPDF(document);
//Save the pdf file
pdfDoc.Save("Sample.pdf");
//Message box confirmation to view the created document.
if (MessageBoxAdv.Show("Do you want to view the generated PDF?", " Document has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
try
{
System.Diagnostics.Process.Start("Sample.pdf");
//Exit
this.Close();
}
catch (Exception ex)
{
MessageBoxAdv.Show("PDF Viewer is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
}
else
{
// Exit
this.Close();
}
}
catch (Exception Ex)
{
// Shows the Message box with Exception message, if an exception throws.
MessageBoxAdv.Show(Ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
}
}
public void SqlCeReadAfterUpdateTest()
{
SqlCeEngine LEngine = new SqlCeEngine(@"Data Source=TestDatabase.sdf");
if (!File.Exists("TestDatabase.sdf"))
{
LEngine.CreateDatabase();
}
using (SqlCeConnection LConnection = new SqlCeConnection("Data Source=TestDatabase.sdf"))
{
LConnection.Open();
using (SqlCeCommand LCommand = LConnection.CreateCommand())
{
LCommand.CommandText = "select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Test'";
if ((int)LCommand.ExecuteScalar() != 0)
{
LCommand.CommandText = "drop table Test";
LCommand.ExecuteNonQuery();
}
LCommand.CommandText = "create table Test ( ID int not null, Name nvarchar(20), constraint PK_Test primary key ( ID ) )";
LCommand.ExecuteNonQuery();
LCommand.CommandText = "insert into Test ( ID, Name ) values ( 1, 'Joe' )";
LCommand.ExecuteNonQuery();
}
using (SqlCeTransaction LTransaction = LConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
using (SqlCeCommand LCommand = LConnection.CreateCommand())
{
LCommand.CommandType = System.Data.CommandType.TableDirect;
LCommand.CommandText = "Test";
LCommand.IndexName = "PK_Test";
LCommand.SetRange(DbRangeOptions.Default, null, null);
using (SqlCeResultSet LResultSet = LCommand.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Sensitive | ResultSetOptions.Updatable))
{
if (!LResultSet.Read())
{
throw new Exception("Expected row");
}
if ((string)LResultSet[1] != "Joe")
{
throw new Exception("Expected Joe row");
}
LResultSet.SetValue(1, "Joes");
LResultSet.Update();
LResultSet.ReadFirst();
//if (!LResultSet.Read())
// throw new Exception("Expected row");
if ((string)LResultSet[1] != "Joes")
{
throw new Exception("Expected Joes row");
}
LResultSet.SetValue(1, "Joe");
LResultSet.Update();
}
}
LTransaction.Commit(CommitMode.Immediate);
}
catch
{
LTransaction.Rollback();
throw;
}
}
using (SqlCeTransaction LTransaction = LConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
}
}
}
//======================================================================
private void TratamentoDados(object sender, EventArgs e)
{
if (Edita_Adiciona_Porao.Local_Atualizado == true)
{
exibir_Local_Acesso();
Edita_Adiciona_Porao.Local_Atualizado = false;
}
// Remove os caracteres '\r' da variavel id+TagRFID
id_TagRFID = id_TagRFID.Replace("\r", "");
SqlCeConnection conexao = new SqlCeConnection("Data Source = " + Vars.base_dados);
conexao.Open();
string query = "SELECT * FROM RegistrosColaboradores WHERE id_TagRFID = " + "'" + id_TagRFID + "'";
SqlCeCommand comando = new SqlCeCommand(query, conexao);
SqlCeDataAdapter escravo = new SqlCeDataAdapter();
escravo.SelectCommand = comando;
//Autenticação verifica existencia
DataTable verifica_EX_Dados = new DataTable();
escravo.Fill(verifica_EX_Dados);
//Busca maior valor da primary key
comando.CommandText = "SELECT MAX(id_Historico) AS IndiceMAX FROM HistoricoAcesso";
escravo.SelectCommand = comando;
DataTable indiceMAX = new DataTable();
escravo.Fill(indiceMAX);
// Se diferente de 0 o valor da tag esta no banco de dados
if (verifica_EX_Dados.Rows.Count != 0)
{
// Busca o valor da tag no tabela Histórico de acesso
comando.CommandText = "SELECT * FROM HistoricoAcesso WHERE id_TagRFID =" + "'" + id_TagRFID + "'";
escravo.SelectCommand = comando;
// Tabela virtual de verificação de primeiro acesso
DataTable Verifica_Primeiro_Acesso = new DataTable();
escravo.Fill(Verifica_Primeiro_Acesso);
//Filtra se é o primero acesso do colaborador ao local
if (Verifica_Primeiro_Acesso.Rows.Count == 0)
{
// Calouro
arduino.Write("E");// Messagem de entrada na serial
bool bit = true;
string id_nP = verifica_EX_Dados.Rows[0]["id_NP"].ToString();
int id_Registros = int.Parse(verifica_EX_Dados.Rows[0]["id_Registros"].ToString()); // Busca a primary key de RegistrosColaboradores
comando.Parameters.AddWithValue("@id_Registros", id_Registros);
comando.Parameters.AddWithValue("@id_NP", id_nP);
comando.Parameters.AddWithValue("@id_TagRFID", id_TagRFID);
comando.Parameters.AddWithValue("@EntradaSaida", bit);
comando.Parameters.AddWithValue("@Horario", DateTime.Now);
comando.Parameters.AddWithValue("@Local", local_Acesso);
comando.CommandText = "INSERT INTO HistoricoAcesso (id_Registros, id_NP, id_TagRFID, EntradaSaida, Horario, Local )" +
"VALUES( @id_Registros, @id_NP, @id_TagRFID, @EntradaSaida, @Horario, @Local); ";
comando.ExecuteNonQuery();
Acessando();
CriaTabelaHistorico();
}
else
{
//Veterano
comando.CommandText = "SELECT * FROM HistoricoAcesso WHERE id_TagRFID =" + "'" + id_TagRFID + "'" + "ORDER BY Horario DESC";
escravo.SelectCommand = comando;
DataTable Verifica_Estado_Acesso = new DataTable();
escravo.Fill(Verifica_Estado_Acesso);
string nivel_logico = Verifica_Estado_Acesso.Rows[0]["EntradaSaida"].ToString();
bool Nivel_Logico_Acesso = bool.Parse(nivel_logico);
// Se o valor de EntradaSaida mais recente for true o colaborador esta saindo do local, se não esta entrando
if (Nivel_Logico_Acesso == true)
{
arduino.Write("S");// Mensagem de saida na serial
bool bit = false;
string id_nP = verifica_EX_Dados.Rows[0]["id_NP"].ToString();
int id_Registros = int.Parse(verifica_EX_Dados.Rows[0]["id_Registros"].ToString()); // Busca a primary key de RegistrosColaboradores
comando.Parameters.AddWithValue("@id_Registros", id_Registros);
comando.Parameters.AddWithValue("@id_NP", id_nP);
comando.Parameters.AddWithValue("@id_TagRFID", id_TagRFID);
comando.Parameters.AddWithValue("@EntradaSaida", bit);
comando.Parameters.AddWithValue("@Horario", DateTime.Now);
comando.Parameters.AddWithValue("@Local", local_Acesso);
comando.CommandText = "INSERT INTO HistoricoAcesso (id_Registros, id_NP, id_TagRFID, EntradaSaida, Horario, Local )" +
"VALUES( @id_Registros, @id_NP, @id_TagRFID, @EntradaSaida, @Horario, @Local); ";
comando.ExecuteNonQuery();
DescarteAcesso();
CriaTabelaHistorico();
}
else
{
arduino.Write("E");// Mensagem de entrada na serial
bool bit = true;
string id_nP = verifica_EX_Dados.Rows[0]["id_NP"].ToString();
int id_Registros = int.Parse(verifica_EX_Dados.Rows[0]["id_Registros"].ToString()); // Busca a primary key de RegistrosColaboradores
comando.Parameters.AddWithValue("@id_Registros", id_Registros);
comando.Parameters.AddWithValue("@id_NP", id_nP);
comando.Parameters.AddWithValue("@id_TagRFID", id_TagRFID);
comando.Parameters.AddWithValue("@EntradaSaida", bit);
comando.Parameters.AddWithValue("@Horario", DateTime.Now);
comando.Parameters.AddWithValue("@Local", local_Acesso);
comando.CommandText = "INSERT INTO HistoricoAcesso (id_Registros, id_NP, id_TagRFID, EntradaSaida, Horario, Local )" +
"VALUES( @id_Registros, @id_NP, @id_TagRFID, @EntradaSaida, @Horario, @Local); ";
comando.ExecuteNonQuery();
Acessando();
CriaTabelaHistorico();
}
}
id_TagRFID = null;
}
else
{
if (!frmEditar.valida_edicao_TAG)
{
// Imprime na serial (acesso negado)
arduino.Write("B");
if (!Verificador.verifica_Froms_Aberta)
{
id_TagRFID = null;
}
}
}
}
private void IntToGuidPK(string parentTable, Child[] childs, string parentPkConstraint)
{
var fkColumn = string.Format("{0}ID", parentTable);
var oldFkCol = string.Format("Old{0}", fkColumn);
var oldId = "OldId";
var Pk = string.Format("PK__{0}", parentTable);
var Id = "Id";
using (var conn = new SqlCeConnection(ConnectionString))
{
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
// copy old int Id
ExecuteNonQuery(cmd, "Alter TABLE {0} add column {1} integer", parentTable, oldId);
ExecuteNonQuery(cmd, "update {0} set {1} = {2}", parentTable, oldId, Id);
for (int i = 0; i < childs.Count(); i++)
{
var childTable = childs[i].table;
var Fk = childs[i].fk;
// copy and delete fkcolumn
ExecuteNonQuery(cmd, "Alter TABLE {0} add column {1} integer", childTable, oldFkCol);
ExecuteNonQuery(cmd, "update {0} set {1} = {2}", childTable, oldFkCol, fkColumn);
ExecuteNonQuery(cmd, "ALTER TABLE {0} DROP constraint {1}", childTable, Fk);
ExecuteNonQuery(cmd, "ALTER TABLE {0} DROP Column {1}", childTable, fkColumn);
}
// delete old int Id
ExecuteNonQuery(cmd, "ALTER TABLE {0} DROP constraint {1}", parentTable, parentPkConstraint);
ExecuteNonQuery(cmd, "ALTER TABLE {0} DROP Column {1}", parentTable, Id);
// create guid id
ExecuteNonQuery(cmd, "Alter TABLE {0} add column {1} uniqueidentifier NOT NULL DEFAULT NEWID() ", parentTable, Id);
ExecuteNonQuery(cmd, "Alter TABLE {0} add CONSTRAINT {1} PRIMARY KEY ({2})", parentTable, Pk, Id);
for (int i = 0; i < childs.Count(); i++)
{
var childTable = childs[i].table;
var Fk = childs[i].fk;
// create guid fk columns
ExecuteNonQuery(cmd, "Alter TABLE {0} add column {1} uniqueidentifier", childTable, fkColumn);
// update fk guid (no Set From in sqlce)
cmd.CommandText = string.Format("SELECT {0} FROM {1}", oldFkCol, childTable);
List <int> oldFkIds = new List <int>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
if (!Convert.IsDBNull(reader[0]))
{
oldFkIds.Add(Convert.ToInt32(reader[0]));
}
}
reader.Close();
foreach (var oldFkId in oldFkIds)
{
cmd.CommandText = string.Format("SELECT p.Id FROM {0} p inner join {1} c on p.{2}={3}", parentTable, childTable, oldId, oldFkId);
Guid newFkId = (Guid)cmd.ExecuteScalar();
ExecuteNonQuery(cmd, "UPDATE {0} SET {1}='{2}' WHERE {3}={4}", childTable, fkColumn, newFkId, oldFkCol, oldFkId);
}
// not null fk
if (childs[i].notnull)
{
ExecuteNonQuery(cmd, "Alter TABLE {0} alter column {1} uniqueidentifier NOT NULL", childTable, fkColumn);
}
// add fk CONSTRAINT
ExecuteNonQuery(cmd, "Alter TABLE {0} add CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3}({4})", childTable, Fk, fkColumn, parentTable, Id);
// drop old int columns
ExecuteNonQuery(cmd, "ALTER TABLE {0} DROP Column {1}", childTable, oldFkCol);
}
ExecuteNonQuery(cmd, "ALTER TABLE {0} DROP Column {1}", parentTable, oldId);
}
}
public DatabaseAccess()
{
con = new SqlCeConnection(@"Data Source=C:\Users\student\Desktop\Automated Routine Generator\Automated Routine Generator\Database1.sdf");
con.Open();
}
private void addSuppDocButton_Click(object sender, EventArgs e)
{
String newRowCount = "";
int newRowCountNum = 0;
String suppumentalPath, suppumentalFileName, suppumentalExt;
Byte[] suppumentalData;
long suppumentalNumBytes;
// Get Suppumental Filename and Path
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.InitialDirectory = "c:\\";
// openFileDialog1.Filter = "mdb files (*.mdb)|*.mdb|accdb files (*.accdb)|*.accdb";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
SqlCeConnection TecanSuppDocsDatabase = null;
TecanSuppDocsDatabase = new SqlCeConnection();
String dataPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
TecanSuppDocsDatabase.ConnectionString = "Data Source=|DataDirectory|\\TecanSuppDocs.sdf;Max Database Size=4000;Max Buffer Size=1024;Persist Security Info=False";
TecanSuppDocsDatabase.Open();
SqlCeCommand cmd = TecanSuppDocsDatabase.CreateCommand();
// Get the last DocID and add 1
cmd.CommandText = "SELECT DocID FROM SuppumentalDocs";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
newRowCount = reader[0].ToString();
}
reader.Dispose();
newRowCountNum = Convert.ToInt16(newRowCount);
newRowCountNum++;
// OPen File and get all fields required to add to Supp DB
suppumentalPath = openFileDialog1.FileName;
FileInfo suppumentalFileInfo = new FileInfo(suppumentalPath);
suppumentalNumBytes = suppumentalFileInfo.Length;
FileStream suppumentalContents = new FileStream(suppumentalPath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(suppumentalContents);
suppumentalData = br.ReadBytes((int)suppumentalNumBytes);
suppumentalContents.Close();
suppumentalFileName = suppumentalFileInfo.Name.ToLower();
suppumentalExt = suppumentalFileInfo.Extension.Replace(".", "").ToLower();
cmd.CommandText = "INSERT INTO SuppumentalDocs (DocID, DocExtension, Document, FileName)" +
" Values " +
"(@DocID, @DocExtension, @Document, @FileName)";
cmd.Parameters.AddWithValue("@DocId", newRowCountNum);
cmd.Parameters.AddWithValue("@DocExtension", suppumentalExt);
cmd.Parameters.AddWithValue("@Document", suppumentalData);
cmd.Parameters.AddWithValue("@FileName", suppumentalFileName);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Please correct the Supplemental Filename \n\n " + suppumentalFileName + "\n\n" + ex.Message + "\n\n" + ex.TargetSite);
}
cmd.Parameters.Clear();
suppumentalContents.Dispose();
// br.Dispose();
// Clear and rebuild the Supp Filelist Display
allSuppDocsDataGridView.Rows.Clear();
cmd.CommandText = "SELECT DocID, FileName FROM SuppumentalDocs ORDER BY FileName";
reader = cmd.ExecuteReader();
while (reader.Read())
{
allSuppDocsDataGridView.Rows.Add(reader[0].ToString(), reader[1].ToString());
}
reader.Dispose();
TecanSuppDocsDatabase.Close();
}
}
private void InitDatabase()
{
string DataBaseFileName = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + @"\aramis_wms_NS.sdf";
//System.IO.File.Delete(DataBaseFileName);
connString = String.Format("Data Source='{0}';", DataBaseFileName);
DBEngine = new SqlCeEngine(connString);
if (!File.Exists(DataBaseFileName))
{
#region Создание файла базы данных
DBEngine.CreateDatabase();
using (SqlCeConnection dBConnection = new SqlCeConnection(DBEngine.LocalConnectionString))
{
dBConnection.Open();
using (SqlCeCommand SQLCommand = dBConnection.CreateCommand())
{
// Справочник типов работ
// ПРИМЕЧАНИЕ.
// Поскольку в ранней версии вместо типа работы использовалась номенклатура,
// то везде в этом коде вместо WorkType используется Nomenclature
SQLCommand.CommandText = "CREATE TABLE Nomenclature (Id bigint CONSTRAINT pkID PRIMARY KEY,Descr nchar(50) NOT NULL)";
SQLCommand.ExecuteNonQuery();
// Справочник "Критерии оценивания"
SQLCommand.CommandText = "CREATE TABLE Criterions (Id bigint, Descr nchar(50), HaveSubCriterion bit, ParentCriterionId bigint, LineNumber int)";
SQLCommand.ExecuteNonQuery();
// Таблица соответствия, позволяет определить, какие критерии используются для каждой номенклатуры
SQLCommand.CommandText = "CREATE TABLE NomenclatureCriterions (Number int NOT NULL, NomenclatureId bigint NOT NULL, CriterionsId bigint NOT NULL)";
SQLCommand.ExecuteNonQuery();
// Таблица оценок
SQLCommand.CommandText = "CREATE TABLE Marks (NomenclatureId bigint NOT NULL, DepartmentId bigint NOT NULL, CriterionsId bigint NOT NULL, ParentCriterionId bigint NOT NULL, Mark int NOT NULL)";
SQLCommand.ExecuteNonQuery();
// Таблица со списком отсканированных паллет
SQLCommand.CommandText = "CREATE TABLE Pallets (NomenclatureId bigint NOT NULL, DepartmentId bigint NOT NULL, PalletNo nchar(50) CONSTRAINT pkPalletNo PRIMARY KEY)";
SQLCommand.ExecuteNonQuery();
}
}
#endregion
}
else
{
isEmptyCriterions = false;
#region Очистка результирующиих таблиц: Marks и Pallets
using (SqlCeConnection dBConnection = new SqlCeConnection(DBEngine.LocalConnectionString))
{
dBConnection.Open();
using (SqlCeCommand SQLCommand = dBConnection.CreateCommand())
{
// Очистка оценок
SQLCommand.CommandText = "DELETE FROM Marks";
SQLCommand.ExecuteNonQuery();
// Очистка отсканированных паллет
SQLCommand.CommandText = "DELETE FROM Pallets";
SQLCommand.ExecuteNonQuery();
}
}
#endregion
}
}
public bool IncluirEntrevistadoEndereco(TEntrevistadoEnderecoDOMINIO dadosEntrevistadoEndereco)
{
try
{
StringBuilder queryTabelaEndereco = new StringBuilder();
if (dadosEntrevistadoEndereco.Endereco.Length > 50)
{
dadosEntrevistadoEndereco.Endereco = dadosEntrevistadoEndereco.Endereco.Substring(0, 49);
}
if (dadosEntrevistadoEndereco.Bairro.Length > 50)
{
dadosEntrevistadoEndereco.Bairro = dadosEntrevistadoEndereco.Bairro.Substring(0, 49);
}
if (dadosEntrevistadoEndereco.Cidade.Length > 50)
{
dadosEntrevistadoEndereco.Cidade = dadosEntrevistadoEndereco.Cidade.Substring(0, 49);
}
if (dadosEntrevistadoEndereco.CEP.Length > 8)
{
dadosEntrevistadoEndereco.CEP = dadosEntrevistadoEndereco.CEP.Substring(0, 8);
}
queryTabelaEndereco.Append(@" INSERT INTO TEntrevistadoEndereco ");
queryTabelaEndereco.Append(@" ( CodigoEntrevista ");
queryTabelaEndereco.Append(@" , Endereco ");
queryTabelaEndereco.Append(@" , Numero ");
queryTabelaEndereco.Append(@" , Bairro ");
queryTabelaEndereco.Append(@" , Cidade ");
queryTabelaEndereco.Append(@" , UF ");
queryTabelaEndereco.Append(@" , CEP ");
queryTabelaEndereco.Append(@" , Complemento ");
queryTabelaEndereco.Append(@" , Email ) ");
queryTabelaEndereco.Append(@" VALUES ( " + dadosEntrevistadoEndereco.CodigoEntrevista + " ");
queryTabelaEndereco.Append(@" , '" + dadosEntrevistadoEndereco.Endereco + "' ");
queryTabelaEndereco.Append(@" , " + dadosEntrevistadoEndereco.Numero + " ");
queryTabelaEndereco.Append(@" , '" + dadosEntrevistadoEndereco.Bairro + "' ");
queryTabelaEndereco.Append(@" , '" + dadosEntrevistadoEndereco.Cidade + "' ");
queryTabelaEndereco.Append(@" , '" + dadosEntrevistadoEndereco.UF + "' ");
queryTabelaEndereco.Append(@" , '" + dadosEntrevistadoEndereco.CEP + "' ");
queryTabelaEndereco.Append(@" , '" + dadosEntrevistadoEndereco.Complemento + "' ");
queryTabelaEndereco.Append(@" , '" + dadosEntrevistadoEndereco.Email + "' ) ");
using (SqlCeConnection conn = new SqlCeConnection(ConnectionString))
{
conn.Open();
SqlCeCommand command = new SqlCeCommand(queryTabelaEndereco.ToString(), conn);
command.ExecuteNonQuery();
}
return(true);
}
catch (Exception ex)
{
Util.LogErro.GravaLog("Incluir registro TEntrevistadoEndereco", ex.Message);
return(false);
}
}
private void button1_Click(object sender, EventArgs e)
{
TFaceRecord fr = new TFaceRecord();
FSDK.SetFaceDetectionParameters(false, true, 384);
FSDK.SetFaceDetectionThreshold((int)FaceDetectionThreshold);
fr.ImageFileName = imgLoc;
fr.FacePosition = new FSDK.TFacePosition();
fr.FacialFeatures = new FSDK.TPoint[2];
fr.Template = new byte[FSDK.TemplateSize];
fr.image = new FSDK.CImage(imgLoc);
fr.FacePosition = fr.image.DetectFace();
if (0 == fr.FacePosition.w)
{
if (imgLoc.Length <= 1)
{
MessageBox.Show("No faces found. Try to lower the Minimal Face Quality parameter in the Options dialog box.", "Enrollment error");
}
else
{
}
}
else
{
fr.faceImage = fr.image.CopyRect((int)(fr.FacePosition.xc - Math.Round(fr.FacePosition.w * 0.5)), (int)(fr.FacePosition.yc - Math.Round(fr.FacePosition.w * 0.5)), (int)(fr.FacePosition.xc + Math.Round(fr.FacePosition.w * 0.5)), (int)(fr.FacePosition.yc + Math.Round(fr.FacePosition.w * 0.5)));
fr.FacialFeatures = fr.image.DetectEyesInRegion(ref fr.FacePosition);
fr.Template = fr.image.GetFaceTemplateInRegion(ref fr.FacePosition); // get template with higher precision
FaceList.Add(fr);
}
Image img = null;
Image img_face = null;
MemoryStream strm = new MemoryStream();
MemoryStream strm_face = new MemoryStream();
img = fr.image.ToCLRImage();
img_face = fr.faceImage.ToCLRImage();
img.Save(strm, System.Drawing.Imaging.ImageFormat.Jpeg);
img_face.Save(strm_face, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] img_array = new byte[strm.Length];
byte[] img_face_array = new byte[strm_face.Length];
strm.Position = 0;
strm.Read(img_array, 0, img_array.Length);
strm_face.Position = 0;
strm_face.Read(img_face_array, 0, img_face_array.Length);
conn = new SqlCeConnection(stringCon);
conn.Open();
var cmd = new SqlCeCommand("insert into FaceList (ImageFileName,SubjectName,FacePositionXc,FacePositionYc,FacePositionW,FacePositionAngle,Eye1X,Eye1Y,Eye2X,Eye2Y,Template,Image,FaceImage) values (@IFName,@SName,@FPXc,@FPYc,@FPW,@FPA,@Eye1X,@Eye1Y,@Eye2X,@Eye2Y,@Template,@Image,@FaceImage)", conn);
cmd.Parameters.Add(@"IFName", fr.ImageFileName);
cmd.Parameters.Add(@"SName", textBox1.Text.Trim());
cmd.Parameters.Add(@"FPXc", fr.FacePosition.xc);
cmd.Parameters.Add(@"FPYc", fr.FacePosition.yc);
cmd.Parameters.Add(@"FPW", fr.FacePosition.w);
cmd.Parameters.Add(@"FPA", fr.FacePosition.angle);
cmd.Parameters.Add(@"Eye1X", fr.FacialFeatures[0].x);
cmd.Parameters.Add(@"Eye1Y", fr.FacialFeatures[0].y);
cmd.Parameters.Add(@"Eye2X", fr.FacialFeatures[1].x);
cmd.Parameters.Add(@"Eye2Y", fr.FacialFeatures[1].y);
cmd.Parameters.Add(@"Template", fr.Template);
cmd.Parameters.Add(@"Image", img_array);
cmd.Parameters.Add(@"FaceImage", img_face_array);
int x = cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
cmd.Dispose();
MessageBox.Show(x.ToString() + "Image successfully added !!");
}
/// <summary>
/// Create the initial database
/// </summary>
private void CreateDB()
{
var connection = new SqlCeConnection(this.path);
try
{
var eng = new SqlCeEngine(this.path);
var cleanup = new System.Threading.Tasks.Task(eng.Dispose);
eng.CreateDatabase();
cleanup.Start();
}
catch (Exception e)
{
EventLogging.WriteError(e);
}
connection.Open();
var usersDB =
new SqlCeCommand(
"CREATE TABLE Users_DB("
+ "UserID int IDENTITY (100,1) NOT NULL UNIQUE, "
+ "UserName nvarchar(128) NOT NULL UNIQUE, "
+ "PassHash nvarchar(128) NOT NULL, "
+ "Friends varbinary(5000), "
+ "PRIMARY KEY (UserID));",
connection);
usersDB.ExecuteNonQuery();
usersDB.Dispose();
connection.Dispose();
connection.Close();
}