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(); }