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 void connectToDatabase() { mySqlConnection = new SqlCeConnection(@"Data Source=C:\University\Adv Software Engineering\Bug Tracker\BugTracker\BugTracker\BugDatabase.mdf"); String selcmd = "SELECT BugID, LineStart, LineEnd, ProgrammerName, ClassName, MethodName, TimeSubmitted, ProjectName, Description FROM dbo ORDER BY TimeSubmitted"; SqlCeCommand mySqlCommand = new SqlCeCommand(selcmd, mySqlConnection); }
public static DataTable Select(string query, SqlCeConnection conn) { DataTable dt = new DataTable(); SqlCeDataAdapter adapter = new SqlCeDataAdapter(query, conn); adapter.Fill(dt); return dt; }
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 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 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 static IDbConnection GetConnection() { var connectionString = ConfigProvider.GetConnectionString(); var connection = new SqlCeConnection(connectionString); connection.Open(); return connection; }
public void ReadTo(Action<SqlCeDataReader> readerAction) { try { using (var connection = new SqlCeConnection(ConnectionString)) { connection.Open(); using (var transaction = connection.BeginTransaction()) { using (var command = connection.CreateCommand()) { command.CommandText = _commandText; SetParametersToCommand(command); using (var reader = command.ExecuteReader()) { while (reader.Read()) { readerAction.Invoke(reader); } } } } } } catch (SqlCeLockTimeoutException ex) { Thread.Sleep(TimeSpan.FromMilliseconds(500)); ReadTo(readerAction); } catch (Exception ex) { LogOrSendMailToAdmin(ex); throw; } }
private 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 CreateDatabase(string fileName, string password) { string connectionString; if (System.IO.File.Exists(fileName)) return; //System.IO.File.Delete(fileName); // The DataSource must be surrounded with double quotes. The Password, on the other hand, must be surrounded // with single quotes connectionString = string.Format( "DataSource=\"{0}\"; Password='******'", fileName, password); // we'll use the SqlServerCe connection object to get the database file path using (SqlCeConnection localConnection = new SqlCeConnection(connectionString)) { // The SqlCeConnection.Database property contains the file parth portion // of the database from the full connectionstring if (!System.IO.File.Exists(localConnection.Database)) { using (SqlCeEngine sqlCeEngine = new SqlCeEngine(connectionString)) { sqlCeEngine.CreateDatabase(); CreateInitialDatabaseObjects(connectionString); } } } }
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 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 ModuleMainForm() { InitializeComponent(); try { // make data folder for this module Common.MakeAllSubFolders(Static.DataFolderPath); // check sdf database file, and copy new if dont exists Static.CheckDB_SDF(); conn = new SqlCeConnection(Static.ConnectionString); command = new SqlCeCommand("", conn); dgwPanel.Columns.Add("tag", "Tag"); dgwPanel.Columns.Add("info", "Info"); DataGridViewButtonColumn btnColl1 = new DataGridViewButtonColumn(); btnColl1.HeaderText = "Edit"; btnColl1.Name = "Edit"; dgwPanel.Columns.Add(btnColl1); keyEventsArgs = new KeyEventArgs(Keys.Oemtilde | Keys.Control); HookManager.KeyDown += new KeyEventHandler(HookManager_KeyDown); } catch (Exception exc) { Log.Write(exc, this.Name, "ModuleMainForm", Log.LogType.ERROR); } }
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(); } } }
private void refresh() { odData = odBox.Text; doData = doBox.Text; try { conn = new SqlCeConnection(connectionString); SqlCeDataAdapter dataadapter = new SqlCeDataAdapter("Select * from Seriale WHERE Data_Premiery BETWEEN '" + odData + "' AND '" + doData + "'", conn); DataSet ds = new DataSet(); conn.Open(); dataadapter.Fill(ds, "Seriale"); conn.Close(); tabela.DataSource = ds; tabela.DataMember = "Seriale"; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (conn != null) conn.Close(); } }
public void Commit() { var connectionString = repository.GetConnectionString(); using (var conn = new SqlCeConnection(connectionString)) { conn.Open(); using (var trans = conn.BeginTransaction()) { foreach (var command in commands) { try { SQLCeHelper.ResetParameterNullValue(command); command.Transaction = trans; command.Connection = conn; command.ExecuteNonQuery(); } catch (Exception e) { throw new KoobooException(e.Message + "SQL:" + command.CommandText, e); } } trans.Commit(); } } //Execute post content events foreach (var action in postActions) { action(); } Clear(); }
/* public List<String> getControls(Object PtID, Object EqID) { List<String> controls = new List<String>(); controls.Add("RateButton"); controls.Add("Lever"); return controls; } */ public List<String> getControls(Object PatientID, Object EqID, SqlCeConnection thisConnection) { int PatID = 0, EquipmentID = 0; List<String> controls = new List<String>(); try { SqlCeCommand cmd = thisConnection.CreateCommand(); cmd.CommandText = "SELECT ID FROM MarkerInventory WHERE MarkerID=\'" + PatientID.ToString() + "\'"; SqlCeDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { PatID = (int)rdr["ID"]; } cmd.CommandText = "SELECT ID FROM MarkerInventory WHERE MarkerID=\'" + EqID.ToString() + "\'"; rdr = cmd.ExecuteReader(); while (rdr.Read()) { EquipmentID = (int)rdr["ID"]; } cmd.CommandText = "SELECT Control FROM Patient WHERE PtID=" + PatID + " and EquipID=" + EquipmentID; rdr = cmd.ExecuteReader(); while (rdr.Read()) { controls.Add((String)rdr["Control"]); } rdr.Close(); cmd.Dispose(); } catch (SqlException e) { Console.WriteLine(e.Message); } return controls; }
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 TPunto GetTPunto(int id, SqlCeConnection conn) { TPunto p = null; using (SqlCeCommand cmd = conn.CreateCommand()) { string sql = @"SELECT p.puntoId, p.nombre, p.edificioId, p.tag, p.cota, p.cubiculo, p.observaciones, e.nombre AS enombre, e.grupoId, g.nombre AS gnombre, p.csnmax, p.csnmargen, p.lastcontrol FROM puntos AS p LEFT OUTER JOIN edificios AS e ON e.edificioId = p.edificioId LEFT OUTER JOIN grupos AS g ON g.grupoId = e.grupoId WHERE p.puntoId= {0}"; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = String.Format(sql, id); using (SqlCeDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { p = GetPuntoFromDr(dr); } if (!dr.IsClosed) dr.Close(); } } return p; }
public void AlimentaSaida(ref SqlCeConnection conexao, string lista, string nrmapa) { string DsList = lista; const string dscabec = "NRGTIN;DSPRODUT;QTPRODUT;NRLOTE;DTVALID"; char[] DsSepLin = { '#' }; char[] DsSepVal = { ';' }; string[] lines; string[] values; DsList = DsList.Replace("\r\n", "#"); DsList = DsList.Replace("##", "#"); lines = DsList.Split(DsSepLin); if (dscabec.ToUpper() != lines[0].ToUpper()) { throw new Exception(String.Format("Lista de Saida não conforme: {0}", lines[0])); } Clear(ref conexao); for (int idx = 1; idx < lines.Length; idx++) { if (lines[idx].Trim() != string.Empty) { values = lines[idx].Split(DsSepVal); Insert(ref conexao, values, nrmapa, idx); } } }
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; }
protected override DbConnection CreateConnection(Type t, string connectionString) { SqlCeConnection connection = null; try { connection = new SqlCeConnection(connectionString); connection.Open(); } catch (System.Data.SqlServerCe.SqlCeInvalidDatabaseFormatException) { try { var engine = new SqlCeEngine(connectionString); engine.Upgrade(); try { connection = new SqlCeConnection(connectionString); connection.Open(); } catch (System.Exception){} } catch (System.Exception ex) { Console.WriteLine("Attempt on Upgrading SQL CE Database Failed (Reason = \"" + ex.Message + "\")"); } } catch (Exception ex) { Console.WriteLine("Unexpected Error Occurred ! Error Details : " + ex.Message); } return connection; }
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; } } }
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 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 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; } }
private void setprods() { //max DataTable dtp = new DataTable(); int f = 0; string SqlString = "Select max([Product Serial Number]) From Logs_prod"; if (Main.Amatrix.mgt == "") { using (SqlCeConnection conn = new SqlCeConnection(logs_prodTableAdapter.Connection.ConnectionString)) { using (SqlCeCommand cmd = new SqlCeCommand(SqlString, conn)) { cmd.CommandType = CommandType.Text; conn.Open(); SqlCeDataReader reader = cmd.ExecuteReader(); using (reader) { dtp.Load(reader); } conn.Close(); } } } else { using (SqlConnection conn = new SqlConnection(Main.Amatrix.mgt)) { using (SqlCommand cmd = new SqlCommand(SqlString, conn)) { cmd.CommandType = CommandType.Text; conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); using (reader) { dtp.Load(reader); } conn.Close(); } } } try { f = Convert.ToInt32(dtp.Rows[0].ItemArray[0].ToString()); } catch (Exception erty) { f = 0; } DataRow dgvrow; DataTable dtp_sve = new DataTable(); foreach (DataGridViewRow dgvr in dataGridView2.SelectedRows) { try { f = f + 1; dgvrow = logs_prod2.Logs_prod.NewRow(); dgvrow[0] = f; dgvrow[1] = dgvr.Cells[1].Value.ToString(); dgvrow[2] = ID; dgvrow[3] = dataGridView1[0, dataGridView1.CurrentRow.Index].Value.ToString(); dgvrow[4] = dataGridView1[1, dataGridView1.CurrentRow.Index].Value.ToString(); logs_prod2.Logs_prod.Rows.Add(dgvrow); dataGridView3.DataSource = logs_prod2; if (Main.Amatrix.mgt == "") { logs_prodTableAdapter.Update(logs_prod2); } else { asql.Save(logs_prod2.Logs_prod, "Logs_prod", Main.Amatrix.mgt); } } catch (Exception erty) { break; Am_err ner = new Am_err(); ner.tx("You May not Choose a Mainstream Product Please Pick a Bulk Item or create one For Your Inventory in the Inventory Information tab within Product Managment."); } } }
/// <summary> /// Enumerates execute expando objects in this collection. /// </summary> /// <param name="this">The @this to act on.</param> /// <param name="cmdText">The command text.</param> /// <param name="transaction">The transaction.</param> /// <returns> /// An enumerator that allows foreach to be used to process execute expando objects in this collection. /// </returns> public static IEnumerable <dynamic> ExecuteExpandoObjects(this SqlCeConnection @this, string cmdText, SqlCeTransaction transaction) { return(@this.ExecuteExpandoObjects(cmdText, null, CommandType.Text, transaction)); }
public Sqlmanager(string path) { _connection = new SqlCeConnection(string.Format(Resources.SQLConnect, path)); }
/// <summary> /// Función que guarda un PreferenceEntity en la base de datos. /// </summary> /// <param name="preference">PreferenceEntity a guardar</param> /// <param name="scope">Estructura interna para evitar problemas con referencias circulares</param> /// <exception cref="ArgumentNullException"> /// Si <paramref name="preference"/> no es un <c>PreferenceEntity</c>. /// </exception> /// <exception cref="UtnEmallDataAccessException"> /// Si una DbException ocurre cuando se accede a la base de datos /// </exception> public void Save(PreferenceEntity preference, Dictionary <string, IEntity> scope) { if (preference == null) { throw new ArgumentException("The argument can't be null"); } // Crear una clave unica para identificar el objeto dentro del scope interno string scopeKey = preference.Id.ToString(NumberFormatInfo.InvariantInfo) + "Preference"; if (scope != null) { // Si se encuentra dentro del scope lo retornamos if (scope.ContainsKey(scopeKey)) { return; } } else { // Crea un nuevo scope si este no fue enviado scope = new Dictionary <string, IEntity>(); } try { // Crea una nueva conexion y una nueva transaccion si no hay una a nivel superior if (!isGlobalTransaction) { dbConnection = dataAccess.GetNewConnection(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); } string commandName = ""; bool isUpdate = false; // Verifica si se debe hacer una actualización o una inserción if (preference.IsNew || !DataAccessConnection.ExistsEntity(preference.Id, "Preference", "idPreference", dbConnection, dbTransaction)) { commandName = "INSERT INTO [Preference] (idPreference, ACTIVE, LEVEL, IDCUSTOMER, IDCATEGORY, [TIMESTAMP] ) VALUES( @idPreference, @active,@level,@idCustomer,@idCategory, GETDATE()); "; } else { isUpdate = true; commandName = "UPDATE [Preference] SET active = @active, level = @level, idCustomer = @idCustomer, idCategory = @idCategory , timestamp=GETDATE() WHERE idPreference = @idPreference"; } // Se crea un command SqlCeCommand sqlCommand = dataAccess.GetNewCommand(commandName, dbConnection, dbTransaction); // Agregar los parametros del command . SqlCeParameter parameter; if (!isUpdate && preference.Id == 0) { preference.Id = DataAccessConnection.GetNextId("idPreference", "Preference", dbConnection, dbTransaction); } parameter = dataAccess.GetNewDataParameter("@idPreference", DbType.Int32); parameter.Value = preference.Id; sqlCommand.Parameters.Add(parameter); FillSaveParameters(preference, sqlCommand); // Ejecutar el command sqlCommand.ExecuteNonQuery(); scopeKey = preference.Id.ToString(NumberFormatInfo.InvariantInfo) + "Preference"; // Agregar la entidad al scope actual scope.Add(scopeKey, preference); // Guarda las colecciones de objetos relacionados. // Guardar objetos relacionados con la entidad actual if (preference.Category != null) { CategoryDataAccess categoryDataAccess = new CategoryDataAccess(); categoryDataAccess.SetConnectionObjects(dbConnection, dbTransaction); categoryDataAccess.Save(preference.Category, scope); } // Actualizar Update(preference); // Cierra la conexión si fue abierta en la función if (!isGlobalTransaction) { dbTransaction.Commit(); } // Actualizar los campos new y changed preference.IsNew = false; preference.Changed = false; } catch (DbException dbException) { // Anula la transaccion if (!isGlobalTransaction) { dbTransaction.Rollback(); } // Relanza una excepcion personalizada throw new UtnEmallDataAccessException(dbException.Message, dbException); } finally { // Cierra la conexión si fue inicializada if (!isGlobalTransaction) { dbConnection.Close(); dbConnection = null; dbTransaction = null; } } }
public string checkScanItem(string itemNum, string batchNum, string itemActivity, SqlCeConnection con) { string strMasterAction = string.Empty; try { SqlCeDataReader dr1 = default(SqlCeDataReader); string strcmd1 = "select ItemNum, BatchNum from ItemMaster where ItemNum = '" + itemNum + "' and BatchNum = '" + batchNum + "'"; SqlCeCommand cmd1 = new SqlCeCommand(strcmd1, con); dr1 = cmd1.ExecuteReader(); if (dr1.Read()) { strMasterAction = "N"; } else { string strcmd2 = "select ItemNum from ItemMaster where ItemNum = '" + itemNum + "'"; SqlCeCommand cmd2 = new SqlCeCommand(strcmd2, con); SqlCeDataReader dr2 = default(SqlCeDataReader); dr2 = cmd2.ExecuteReader(); if (dr2.Read()) { if (itemActivity == "S") { strMasterAction = "A"; } else { strMasterAction = "E"; } } else { strMasterAction = "E"; } } } catch (Exception ex) { throw ex; } return(strMasterAction); }
/// <summary> /// Enumerates execute expando objects in this collection. /// </summary> /// <param name="this">The @this to act on.</param> /// <param name="cmdText">The command text.</param> /// <param name="parameters">Options for controlling the operation.</param> /// <param name="commandType">Type of the command.</param> /// <returns> /// An enumerator that allows foreach to be used to process execute expando objects in this collection. /// </returns> public static IEnumerable <dynamic> ExecuteExpandoObjects(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType) { return(@this.ExecuteExpandoObjects(cmdText, parameters, commandType, null)); }
} // Save public static void SaveTo(this RecordTask task, SqlCeConnection cn) { var saveCmd = GetDbSaveCommand(task); DbServices.Save(cn, saveCmd, "@XmlData", task); } // SaveTo
public void DoSearch() { var sign = txtSearch.Text.Substring(0, 4).ToUpper(); var number = Int32.Parse(txtSearch.Text.Substring(4, 6)); var check = txtSearch.Text.Substring(10, 1); DataRow row; using (var connection = new SqlCeConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString)) using (var command = new SqlCeCommand("SELECT Sign, Number, Checkdigit, Type FROM Containers WHERE Sign=@Sign AND Number=@Number AND Checkdigit=@Checkdigit", connection)) { command.Parameters.AddWithValue("Sign", sign); command.Parameters.AddWithValue("Number", number); command.Parameters.AddWithValue("Checkdigit", check); connection.Open(); using (var reader = command.ExecuteReader()) { var table = new DataTable(); table.Load(reader); row = table.Rows.Count == 0 ? null : table.Rows[0]; } } var container = row == null ? null : new Container(row["Sign"].ToString(), Int32.Parse(row["Number"].ToString()), row["Checkdigit"].ToString(), containerDescriptions[row["Type"].ToString()], Path.Combine(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Images"), row["Type"] + ".gif")); if (container != null) { containerDetails.DataContext = container; containerDetails.Visibility = Visibility.Visible; } else { containerDetails.DataContext = null; containerDetails.Visibility = Visibility.Collapsed; } DataTable table1; using (var connection = new SqlCeConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString)) using (var command = new SqlCeCommand("SELECT s.Type, s.Seal, s.Weight, s.ToPort, s.FromPort, s.Ship, s.Forwarder, s.BoardingDate, s.LandingDate FROM Shippings AS s INNER JOIN Containers AS c ON s.ContainerId = c.ContainerId WHERE c.Sign=@Sign AND c.Number=@Number AND c.Checkdigit=@Checkdigit", connection)) { command.Parameters.AddWithValue("Sign", sign); command.Parameters.AddWithValue("Number", number); command.Parameters.AddWithValue("Checkdigit", check); connection.Open(); using (var reader = command.ExecuteReader()) { table1 = new DataTable(); table1.Load(reader); } } var shippings = new List <Shipping>(); foreach (DataRow row1 in table1.Rows) { var boardingDate = (Convert.IsDBNull(row1["BoardingDate"])) ? null : (DateTime?)Convert.ToDateTime(row1["BoardingDate"]); var landingDate = (Convert.IsDBNull(row1["LandingDate"])) ? null : (DateTime?)Convert.ToDateTime(row1["LandingDate"]); var port = row1["Type"].ToString() == "E" ? row1["ToPort"].ToString() : row1["FromPort"].ToString(); var date = row1["Type"].ToString() == "E" ? boardingDate.GetValueOrDefault() : landingDate.GetValueOrDefault(); var s = new Shipping(shippingDescriptions[row1["Type"].ToString()], Int32.Parse(row1["Seal"].ToString()), Int32.Parse(row1["Weight"].ToString()), port, row1["Ship"].ToString(), date, row1["Forwarder"].ToString()); shippings.Add(s); } if (shippings.Count > 0) { lstShippings.ItemsSource = shippings; shippingsList.Visibility = Visibility.Visible; } else { lstShippings.DataContext = null; shippingsList.Visibility = Visibility.Collapsed; } }
private async Task <IEnumerable <Entry> > GetEntriesAsync(SqlCeConnection connection) { return(await connection.QueryAsync <Entry>("SELECT * FROM [Entry]")); }
//public string checkScanItemNum(string itemNum) //{ // string strMasterAction = string.Empty; // try // { // using (SqlCeConnection con = new SqlCeConnection(strConn)) // { // con.Open(); // string strcmd = "select ItemNum from ItemMaster where ItemNum = '" + itemNum + "'"; // SqlCeCommand cmd = new SqlCeCommand(strcmd, con); // SqlCeDataReader dr = default(SqlCeDataReader); // dr = cmd.ExecuteReader(); // if (dr.Read()) // { // if (dr[0] == DBNull.Value) // strMasterAction = "E"; // else // strMasterAction = "A"; // } // con.Close(); // } // } // catch (Exception ex) // { // throw ex; // } // return strMasterAction; //} public string insertScanItem(string itemNum, string batchNum, string custName, string locationName, string itemActivity, int itemQuantity, string scannerID, string userID, string masterAction, SqlCeConnection con) { string strItemID = string.Empty; try { string strcmd1 = "insert into HScanItems(Activity, Quantity, CustomerName, LocationName, ScannerID, UserID, ScanDate, ItemNum, BatchNum,MasterAction) values ('" + itemActivity + "','" + itemQuantity + "','" + custName + "','" + locationName + "','" + scannerID + "','" + userID + "','" + System.DateTime.Now + "','" + itemNum + "','" + batchNum + "','" + masterAction + "')"; SqlCeCommand cmd1 = new SqlCeCommand(strcmd1, con); cmd1.ExecuteNonQuery(); //if (currentItemID == "") // strItemID = "1"; //else //{ // strItemID = (Int32.Parse(currentItemID) + 1).ToString(); //} string strcmd2 = "SELECT @@IDENTITY"; SqlCeCommand cmd2 = new SqlCeCommand(strcmd2, con); strItemID = cmd2.ExecuteScalar().ToString(); } catch (Exception ex) { strItemID = string.Empty; throw ex; } return(strItemID); }
private void btsaque_Click(object sender, EventArgs e) { try { SqlCeConnection conexao = new SqlCeConnection(@"Data Source = C:\Users\thale\Desktop\Curso C#\Databases\Banco Digital.sdf" + "; Password = '******'"); conexao.Open(); string query = "SELECT * FROM Cliente"; SqlCeDataAdapter adaptador = new SqlCeDataAdapter(query, conexao); DataTable dados = new DataTable(); adaptador.Fill(dados); SqlCeCommand comando = new SqlCeCommand(); comando.Connection = conexao; foreach (DataRow linha in dados.Rows) { string num_conta = linha["num_conta"].ToString(); string senha = linha["senha"].ToString(); if ((num_conta == tbnum_conta.Text) && (senha == tbsenha.Text)) { float valor = float.Parse(tbvalor.Text); float saldo = float.Parse(linha["saldo"].ToString()); string tipo_conta = linha["tipo_conta"].ToString(); if (tipo_conta == "Especial") { float saldo_atual = saldo - valor; //parametros comando.Parameters.AddWithValue("@saldo", saldo_atual); comando.Parameters.AddWithValue("@num_conta", num_conta); comando.Parameters.AddWithValue("@senha", senha); //texto da query comando.CommandText = "UPDATE Cliente SET saldo = @saldo WHERE num_conta = @num_conta AND senha = @senha"; comando.ExecuteNonQuery(); comando.Dispose(); conexao.Dispose(); tbnum_conta.Text = ""; tbvalor.Text = ""; tbsenha.Text = ""; tbnum_conta.Focus(); MessageBox.Show("Saque realizado com Sucesso.", "Sucesso", MessageBoxButtons.OK); } else if (saldo >= valor) { float saldo_atual = saldo - valor; //parametros comando.Parameters.AddWithValue("@saldo", saldo_atual); comando.Parameters.AddWithValue("@num_conta", num_conta); comando.Parameters.AddWithValue("@senha", senha); //texto da query comando.CommandText = "UPDATE Cliente SET saldo = @saldo WHERE num_conta = @num_conta AND senha = @senha"; comando.ExecuteNonQuery(); comando.Dispose(); conexao.Dispose(); tbnum_conta.Text = ""; tbvalor.Text = ""; tbsenha.Text = ""; tbnum_conta.Focus(); MessageBox.Show("Saque realizado com Sucesso.", "Sucesso", MessageBoxButtons.OK); } else { MessageBox.Show("Saldo insulficiente.", "Erro", MessageBoxButtons.OK); return; } } } } catch { MessageBox.Show("Aconteceu um erro na conexão com o Banco de Dados", "Erro", MessageBoxButtons.OK); } }
} // Load public static RecordTask LoadFromDatabase(SqlCeConnection cn, Guid taskId) { return(DbServices.Load <RecordTask>(cn, GetDbLoadCommand(taskId), "XmlData")); } // Load
/// <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(); }