//AñadirPago public void AñadirNuevo(PagoEntrante pagEntr) { //Crear Conexion y Abrirla SqlCeConnection Con = CrearConexion(); // Crear SQLCeCommand - Asignarle la conexion - Asignarle la instruccion SQL (consulta) SqlCeCommand Comando = new SqlCeCommand(); Comando.Connection = Con; Comando.CommandType = CommandType.Text; Comando.CommandText = "INSERT INTO [PagosEntrantes] ([idTramite], [dniCuilCliente], [fecha], [valor], [detalle]) VALUES (@IDTRAMITE, @DNICUILCLI, @FECHA, @VALOR, @DETALLE)"; Comando.Parameters.Add(new SqlCeParameter("@IDTRAMITE", SqlDbType.Int)); Comando.Parameters["@IDTRAMITE"].Value = pagEntr.IdTramite; Comando.Parameters.Add(new SqlCeParameter("@DNICUILCLI", SqlDbType.NVarChar)); Comando.Parameters["@DNICUILCLI"].Value = pagEntr.DniCuilCliente; Comando.Parameters.Add(new SqlCeParameter("@FECHA", SqlDbType.DateTime)); Comando.Parameters["@FECHA"].Value = pagEntr.Fecha; Comando.Parameters.Add(new SqlCeParameter("@VALOR", SqlDbType.Money)); Comando.Parameters["@VALOR"].Value = pagEntr.Valor; Comando.Parameters.Add(new SqlCeParameter("@DETALLE", SqlDbType.NVarChar)); Comando.Parameters["@DETALLE"].Value = pagEntr.Detalle; //Ejecuta el comando INSERT Comando.Connection.Open(); Comando.ExecuteNonQuery(); Comando.Connection.Close(); }
public static int InsertCapturePointsForTextConversion(int RecommendationId, List<CustomTreeNode> customNodesList) { int returnCode = -1; List<int> capturePointsIds = new List<int>(); SqlCeConnection conn = BackEndUtils.GetSqlConnection(); try { conn.Open(); for (int i = 0; i < customNodesList.Count; i++) { SqlCeCommand command = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandInsertCapturePointTextConv, conn); //@pointText, @pointUsedAttributes, @pointParentNode, @pointUsedAttribValues, @pointRecId command.Parameters.Add("@pointText", customNodesList[i].Text); command.Parameters.Add("@pointUsedAttributes", BackEndUtils.GetUsedAttributes(customNodesList[i].customizedAttributeCollection)); command.Parameters.Add("@pointParentNode", (customNodesList[i].Parent == null ? "" : customNodesList[i].Parent.Text)); command.Parameters.Add("@pointUsedAttribValues", BackEndUtils.GetUsedAttributesValues(customNodesList[i].customizedAttributeCollection)); command.Parameters.Add("@pointRecId", RecommendationId); command.Parameters.Add("@Level", customNodesList[i].Level); command.Parameters.Add("@ItemIndex", customNodesList[i].Index); command.Parameters.Add("@parentLevel", customNodesList[i].Parent == null ? -1 : customNodesList[i].Parent.Level); command.Parameters.Add("@parentIndex", customNodesList[i].Parent == null ? -1 : customNodesList[i].Parent.Index); returnCode = Convert.ToInt32(command.ExecuteNonQuery()); SqlCeCommand commandMaxId = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandMaxCapturePointIdTextConv, conn); capturePointsIds.Add(Convert.ToInt32(commandMaxId.ExecuteScalar())); } } finally { conn.Close(); } return returnCode; }
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(); } }
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; }
public static void DeleteAllFileNames(SqlCeTransaction transaction, SqlCeConnection conn) { SqlCeCommand command = new SqlCeCommand(Folder_Names_SQL.commandDeleteAllFolderNames, conn); command.Transaction = transaction; command.Connection = conn; command.ExecuteNonQuery(); }
//Search button clicked private void button1_Click(object sender, EventArgs e) { SqlCeCommand cm = new SqlCeCommand(); if (comboBox1.Text.Length < 1) { cm = new SqlCeCommand("SELECT ID, Marka as Brand, Tipus as Type, Kiadasi_ev as R_year, Berlesi_ar as Price FROM Autok WHERE Allapot='raktaron' AND Kiadasi_ev>@yearfrom AND Kiadasi_ev<@yearuntil AND Eladasi_ar>@pricefrom AND Eladasi_ar<@priceuntil", Form1.con); } else if (comboBox2.Text.Length < 1) { cm = new SqlCeCommand("SELECT ID, Marka as Brand, Tipus as Type, Kiadasi_ev as R_year, Berlesi_ar as Price FROM Autok WHERE Allapot='raktaron' AND Marka=@marka AND Kiadasi_ev>@yearfrom AND Kiadasi_ev<@yearuntil AND Eladasi_ar>@pricefrom AND Eladasi_ar<@priceuntil ", Form1.con); } else { cm = new SqlCeCommand("SELECT ID, Marka as Brand, Tipus as Type, Kiadasi_ev as R_yer, Berlesi_ar as Price FROM Autok WHERE Allapot='raktaron' AND Marka=@marka AND Tipus=@tipus AND Kiadasi_ev>@yearfrom AND Kiadasi_ev<@yearuntil AND Eladasi_ar>@pricefrom AND Eladasi_ar<@priceuntil ", Form1.con); } if (comboBox6.Text.Equals("100000+")) { cm.Parameters.AddWithValue("@priceuntil", int.MaxValue); } else { cm.Parameters.AddWithValue("@priceuntil", comboBox6.Text); } cm.Parameters.AddWithValue("@pricefrom", comboBox4.Text); cm.Parameters.AddWithValue("@yearuntil", comboBox5.Text); cm.Parameters.AddWithValue("@yearfrom", comboBox3.Text); cm.Parameters.AddWithValue("@tipus", comboBox2.Text); cm.Parameters.AddWithValue("@marka", comboBox1.Text); SqlCeDataAdapter a = new SqlCeDataAdapter(cm); DataTable dt = new DataTable(); a.Fill(dt); dataGridView2.DataSource = dt; this.dataGridView2.Height = setAutomaticHeight(this.dataGridView2.Columns[0].HeaderCell.Size.Height, this.dataGridView2.Rows[0].Height, this.dataGridView2.Rows.Count, 340); }
internal void Save() { const string updateSql = @"update Creating set ExcludeFilesOfType = @ExcludeFilesOfType, SortFiles = @SortFiles, SFV32Compatibility = @SFV32Compatibility, MD5SumCompatibility = @MD5SumCompatibility, PromptForFileName = @PromptForFileName, AutoCloseWhenDoneCreating = @AutoCloseWhenDoneCreating, CreateForEachSubDir = @CreateForEachSubDir "; using (SqlCeCommand cmd = new SqlCeCommand(updateSql, Program.GetOpenSettingsConnection())) { cmd.Parameters.AddWithValue("@ExcludeFilesOfType", ExcludeFilesOfType); cmd.Parameters.AddWithValue("@SortFiles", SortFiles); cmd.Parameters.AddWithValue("@SFV32Compatibility", SFV32Compatibility); cmd.Parameters.AddWithValue("@MD5SumCompatibility", MD5SumCompatibility); cmd.Parameters.AddWithValue("@PromptForFileName", PromptForFileName); cmd.Parameters.AddWithValue("@AutoCloseWhenDoneCreating", AutoCloseWhenDoneCreating); cmd.Parameters.AddWithValue("@CreateForEachSubDir", CreateForEachSubDir); cmd.ExecuteNonQuery(); } }
public static long? ExecuteNonQuery(string query) { try { SqlCeConnection conn = CaseStudyDB.GetConnection(); conn.Open(); SqlCeCommand cmd = new SqlCeCommand(query, conn); cmd.ExecuteScalar(); cmd = new SqlCeCommand("SELECT @@IDENTITY", conn); object queryReturn = cmd.ExecuteScalar(); long value; long.TryParse(queryReturn.ToString(),out value); conn.Close(); if(value != 0) { return value; } else { return null; } } catch (Exception ex) { MessageBox.Show(string.Format("Error exeuting query: {0}", ex.Message)); return null; } }
public void SaveUserFollower(TwitterAPPUser user, TwitterAPPUser followerUser) { var command = new SqlCeCommand("INSERT INTO SEGUIDORES_TWITTER (IDTWITTER, IDTWITTERSEGUIDOR) VALUES (@IDTWITTER, @IDTWITTERSEGUIDOR)"); command.Parameters.Add("IDTWITTER", user.id_str); command.Parameters.Add("IDTWITTERSEGUIDOR", followerUser.id_str); SQLOperation.ExecuteSQLCommand(command); }
SqlCeCommand NewCommand(SqlPreCommandSimple preCommand, SqlCeConnection overridenConnection) { SqlCeCommand cmd = new SqlCeCommand(); int? timeout = Connector.ScopeTimeout ?? CommandTimeout; if (timeout.HasValue) cmd.CommandTimeout = timeout.Value; if (overridenConnection != null) cmd.Connection = overridenConnection; else { cmd.Connection = (SqlCeConnection)Transaction.CurrentConnection; cmd.Transaction = (SqlCeTransaction)Transaction.CurrentTransaccion; } cmd.CommandText = preCommand.Sql; if (preCommand.Parameters != null) { foreach (SqlCeParameter param in preCommand.Parameters) { cmd.Parameters.Add(param); } } Log(preCommand); return cmd; }
//Chamar sempre apos carregar os parametros public void Carregar() { SqlCeDataReader reader=null; try { string sql = "select * from funcionario where id=" + Id; SqlCeCommand cmd = new SqlCeCommand(sql, D.Bd.Con); reader = cmd.ExecuteReader(); reader.Read(); Nome = Convert.ToString(reader["nome"]); DescontoMaximo = Convert.ToDouble(reader["desconto_maximo"]); AcrescimoMaximo = Convert.ToDouble(reader["acrescimo_maximo"]); } catch (Exception ex) { throw new Exception("Não consegui obter os dados do funcionário, configure e sincronize antes de utilizar o dispositivo " + ex.Message); } finally { try { reader.Close(); } catch { } } }
public static List<StudentModel> ReadAllStudents() { string cmdText = @"SELECT Id, ExpGraduation, Name, CanCode, CreditsLeft, Advisor FROM Student"; List<StudentModel> list = new List<StudentModel>(); using (SqlCeConnection conn = CreateConnection()) { using (SqlCeCommand cmd = new SqlCeCommand(cmdText, conn)) { using (SqlCeDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { StudentModel item = new StudentModel() { Id = (int) reader["Id"], ExpGraduation = (DateTime) reader["ExpGraduation"], Name = (string) reader["Name"], CanCode = (bool) reader["CanCode"], CreditsLeft = (int) reader["CreditsLeft"], Advisor = (string) reader["Advisor"] }; list.Add(item); } } } } return list; }
public Reservation getReservation() { Reservation model = new Reservation(); SqlCeCommand cmd = new SqlCeCommand("Select * from Reservation " + "inner join RoomInformation on Reservation.RoomID = RoomInformation.RoomID " + "inner join UserInformation on Reservation.UserID = UserInformation.User_ID where Reservation.ReservationID = @ReservationID", conn); cmd.Parameters.AddWithValue("@ReservationID", this._reservation.ReservationID); SqlCeDataAdapter adapter = new SqlCeDataAdapter(); adapter.SelectCommand = cmd; DataSet setdata = new DataSet(); adapter.Fill(setdata, "Reservation"); model.AppointmentID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[0].ToString()); model.UserID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString()); model.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[2].ToString()); model.ReservationDate = DateTime.Parse(setdata.Tables[0].Rows[0].ItemArray[3].ToString()); model.ReservationAmount = setdata.Tables[0].Rows[0].ItemArray[4].ToString(); model.ValidTill = DateTime.Parse(setdata.Tables[0].Rows[0].ItemArray[5].ToString()); model.ReservationType = setdata.Tables[0].Rows[0].ItemArray[6].ToString(); model.RoomAccount.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[2].ToString()); model.RoomAccount.ApartmentName = setdata.Tables[0].Rows[0].ItemArray[8].ToString(); model.RoomAccount .FullAddress= setdata.Tables[0].Rows[0].ItemArray[19].ToString(); model.RoomAccount.RoomType = setdata.Tables[0].Rows[0].ItemArray[12].ToString(); model.RoomAccount.RoomForRent = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[11].ToString()); model.RoomAccount.RoomForSale = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[10].ToString()); model.RoomAccount.RoomPrice = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[14].ToString()); model.RoomAccount .RoomStatus= setdata.Tables[0].Rows[0].ItemArray[23].ToString(); model.RoomAccount.Username = setdata.Tables[0].Rows[0].ItemArray[51].ToString(); Account AccountModel = new Account(); AccountModel.ID = model.UserID; AccountRepository _accountRepository = new AccountRepository(AccountModel); model.Reserver = _accountRepository.getDataByID().username; return model; }
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(); } }
private static void CreateApplications(SqlCeCommand cmd, string applicationName) { cmd.CommandText = @"CREATE TABLE [aspnet_Applications] ( [ApplicationName] nvarchar(256) NOT NULL , [LoweredApplicationName] nvarchar(256) NOT NULL , [ApplicationId] uniqueidentifier NOT NULL DEFAULT (newid()) PRIMARY KEY , [Description] nvarchar(256) NULL ); "; cmd.ExecuteNonQuery(); cmd.CommandText = @"CREATE INDEX [aspnet_Applications_Index] ON [aspnet_Applications] ([LoweredApplicationName] ASC);"; cmd.ExecuteNonQuery(); cmd.CommandText = @"CREATE UNIQUE INDEX [UQ__aspnet_A__3091033107020F21] ON [aspnet_Applications] ([ApplicationName] ASC);"; cmd.ExecuteNonQuery(); cmd.CommandText = @"INSERT INTO [aspnet_Applications] ([ApplicationName], [LoweredApplicationName] ,[ApplicationId]) VALUES (@ApplicationName ,@LoweredApplicationName ,@ApplicationId ); "; cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName; cmd.Parameters.Add("@LoweredApplicationName", SqlDbType.NVarChar, 256).Value = applicationName.ToLowerInvariant(); cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid(); cmd.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 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); } }
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<Appointment> getAppointmentList() { List<Appointment> AppointmentList = new List<Appointment>(); SqlCeCommand cmd = new SqlCeCommand("select * from Appointment " + "inner join RoomInformation on Appointment.RoomID = RoomInformation.RoomID " + "inner join UserInformation on Appointment.UserID = UserInformation.User_ID " + "inner join UserInformation as RoomAccount on RoomInformation.UserID = RoomAccount.User_ID " + "where Appointment.RoomID = @RoomID and Appointment.UserID = @UserID", conn); cmd.Parameters.AddWithValue("@UserID", this._appointment.UserID); cmd.Parameters.AddWithValue("@RoomID", this._appointment.RoomID); SqlCeDataAdapter adapter = new SqlCeDataAdapter(); adapter.SelectCommand = cmd; DataSet setdata = new DataSet(); adapter.Fill(setdata, "Appointment"); for (int i = 0; i < setdata.Tables[0].Rows.Count; i++) { Appointment model = new Appointment(); model.AppointmentID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[0].ToString()); model.RoomID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[1].ToString()); model.UserID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[2].ToString()); model.AppointmentDate = DateTime.Parse(setdata.Tables[0].Rows[i].ItemArray[3].ToString()); model.Status = setdata.Tables[0].Rows[i].ItemArray[4].ToString(); model.Respond = setdata.Tables[0].Rows[i].ItemArray[5].ToString(); model.RoomAcc.RoomID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[1].ToString()); model.RoomAcc.ApartmentName = setdata.Tables[0].Rows[i].ItemArray[7].ToString(); model.RoomAcc.RoomForSale = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[8].ToString()); model.RoomAcc.RoomForRent = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[9].ToString()); model.Appointee = setdata.Tables[0].Rows[i].ItemArray[49].ToString(); model.RoomAcc.Username = setdata.Tables[0].Rows[i].ItemArray[62].ToString(); AppointmentList.Add(model); } return AppointmentList; }
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); }
/// <summary> /// Vérifie si la BD existe et la mise à jours au besoin /// </summary> public void CheckDatabase() { if (IsAvailable) { if (!File.Exists(DBName)) CreateDatabase(); else { // 3.1.0 if (!TableExist("CivInfo")) { // Création de la table contenant la version de la BD CreateCIVInfoTable(); // Remplir les valeurs par défaut foreach (string user in GetUserTables()) { using (SqlCeCommand cmd = new SqlCeCommand(String.Format("ALTER TABLE {0} ADD period nChar(17)", user), DataBaseFactory.Instance.GetConnection())) { cmd.ExecuteNonQuery(); } DailyUsageDAO.Instance.FillPeriod(user); } } } } }
public Appointment getAppointment() { Appointment model = new Appointment(); SqlCeCommand cmd = new SqlCeCommand("Select * from Appointment " + "inner join RoomInformation on Appointment.RoomID = RoomInformation.RoomID " + "inner join UserInformation on Appointment.UserID = UserInformation.User_ID where Appointment.AppointmentID = @AppointmentID", conn); cmd.Parameters.AddWithValue("@AppointmentID", this._appointment.AppointmentID); SqlCeDataAdapter adapter = new SqlCeDataAdapter(); adapter.SelectCommand = cmd; DataSet setdata = new DataSet(); adapter.Fill(setdata, "Appointment"); model.AppointmentID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[0].ToString()); model.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString()); model.UserID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[2].ToString()); model.AppointmentDate = DateTime.Parse(setdata.Tables[0].Rows[0].ItemArray[3].ToString()); model.Status = setdata.Tables[0].Rows[0].ItemArray[4].ToString(); model.Respond = setdata.Tables[0].Rows[0].ItemArray[5].ToString(); model.RoomAcc.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString()); model.RoomAcc.ApartmentName = setdata.Tables[0].Rows[0].ItemArray[7].ToString(); model.RoomAcc.RoomForSale = bool.Parse (setdata.Tables[0].Rows[0].ItemArray[8].ToString()); model.RoomAcc.RoomForRent = bool.Parse (setdata.Tables[0].Rows[0].ItemArray[9].ToString()); model.RoomAcc.RoomPrice = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[12].ToString()); model.RoomAcc.Username = setdata.Tables[0].Rows[0].ItemArray [49].ToString (); Account AccountModel = new Account(); AccountModel.ID = model.UserID; AccountRepository _accountRepository = new AccountRepository(AccountModel); model.Appointee = _accountRepository.getDataByID().username; return model; }
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 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; }
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(); }
static void DropData(SqlCeConnection connection) { Console.Write("Dropping"); Console.CursorLeft = 0; SqlCeCommand command = new SqlCeCommand("delete from entity", connection); command.ExecuteNonQuery(); }
private static void Clean(string tableName) { if (!Program.Settings.General.UseCachedResults) return; string getMaxIDSql = string.Format("select max(ID) from {0};", tableName); long? maxID = null; using (SqlCeCommand cmd = new SqlCeCommand(getMaxIDSql, Program.GetOpenCacheConnection())) using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd)) { DataSet ds = new DataSet(); da.Fill(ds); if (ds.Tables[0].Rows.Count == 1) { if (!DBNull.Value.Equals(ds.Tables[0].Rows[0][0])) maxID = Convert.ToInt64(ds.Tables[0].Rows[0][0]); } } if (maxID != null) { maxID -= Program.Settings.General.CacheSize; if (maxID > 0) { string deleteSql = string.Format("delete from {0} where ID <= {1};", tableName, maxID); using (SqlCeCommand cmd = new SqlCeCommand(deleteSql, Program.GetOpenCacheConnection())) { cmd.ExecuteNonQuery(); } } } }
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 int getNextOrderNo() { // Get the next order number from the local database int orderNo = 0; try { conn.Open(); SqlCeCommand sqlCmd = new SqlCeCommand("SELECT OrderNo FROM Orders", conn); SqlCeDataReader rdr = sqlCmd.ExecuteReader(); while (rdr.Read()) { orderNo = rdr.GetInt32(0); } } catch (Exception ex) { dbError = ex.Message; return -1; } finally { conn.Close(); } return ++orderNo; }
public static void TSave(bool newRecord, TSustitucion ts, SqlCeConnection conn) { int usuario_id = 0; if (ts.TUsuario != null) { usuario_id = ts.TUsuario.UsuarioId; } int dispo_id = 0; if (ts.TDispositivoOriginal != null) { dispo_id = ts.TDispositivoOriginal.DispositivoId; } int dispos_id = 0; if (ts.TDispositivoSustituto != null) { dispos_id = ts.TDispositivoSustituto.DispositivoId; } int programa_id = 0; if (ts.TPrograma != null) { programa_id = ts.TPrograma.ProgramaId; } int revision_id = 0; if (ts.TRevision != null) { revision_id = ts.TRevision.RevisionId; if (ts.TRevision.TPrograma != null) { programa_id = ts.TRevision.TPrograma.ProgramaId; } } string fecha = "NULL"; if (!CntSciTerminal.FechaNula(ts.Fecha)) { fecha = String.Format("'{0}'", ts.Fecha); } //fecha_apertura = String.Format("'{0:dd/MM/yyyy}'", ts.Fecha); string comentarios = ""; string pos = ""; string poss = ""; if (ts.Comentarios != null) { comentarios = ts.Comentarios; } if (ts.Pos != null) { pos = ts.Pos; } if (ts.Poss != null) { poss = ts.Poss; } //manejo ss byte abm = ts.Abm; if (abm != 1) { abm = 3; } string sql = ""; if (newRecord) { sql = @"SELECT MAX(sustitucion_id) from sustituciones"; using (SqlCeCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; SqlCeDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if (dr[0] != DBNull.Value) { ts.SustitucionId = dr.GetInt32(0) + 1; } else { ts.SustitucionId = 1; } break; } if (!dr.IsClosed) { dr.Close(); } } sql = @"INSERT INTO Sustituciones(sustitucion_id, fecha, comentarios, estado, usuario_id, dispo_id, disps_id, revision_id, programa_id, abm, pos, poss) VALUES({0},{1},'{2}','{3}', {4},{5},{6},{7},{8},1, '{10}', '{11}')"; } else { sql = @"UPDATE Sustituciones SET fecha={1},comentarios='{2}',estado='{3}', usuario_id={4}, dispo_id={5}, disps_id={6}, revision_id={7}, programa_id={8}, abm = {9}, pos='{10}', poss='{11}' WHERE sustitucion_id={0}"; } sql = String.Format(sql, ts.SustitucionId, fecha, comentarios, ts.Estado, usuario_id, dispo_id, dispos_id, revision_id, programa_id, abm, pos, poss); using (SqlCeCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } //Ahora actualizamos el estado de los dispositivos string newpos = ts.TDispositivoOriginal.Posicion; if (pos != "") { newpos = pos; } sql = @"UPDATE Dispositivo SET estado='{1}', posicion = '{2}' WHERE dispositivo_id={0}"; sql = String.Format(sql, ts.TDispositivoOriginal.DispositivoId, ts.TDispositivoOriginal.Estado, newpos); using (SqlCeCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } if (ts.TDispositivoSustituto != null) { newpos = ts.TDispositivoSustituto.Posicion; if (poss != "") { newpos = poss; } sql = @"UPDATE Dispositivo SET estado='{1}', posicion = '{2}' WHERE dispositivo_id={0}"; sql = String.Format(sql, ts.TDispositivoSustituto.DispositivoId, ts.TDispositivoSustituto.Estado, newpos); using (SqlCeCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } }
public static TSustitucion GetTSustitucion(int id, SqlCeConnection conn) { TSustitucion tSustitucion = null; using (SqlCeCommand cmd = conn.CreateCommand()) { cmd.CommandText = String.Format("SELECT * FROM Sustituciones WHERE sustitucion_id = {0}", id); SqlCeDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { tSustitucion = new TSustitucion() { SustitucionId = id, Comentarios = dr.GetString(2), Estado = dr.GetString(3), Abm = dr.GetByte(9) }; if (dr[1] != DBNull.Value) { tSustitucion.Fecha = dr.GetDateTime(1); } if (dr[4] != DBNull.Value) { tSustitucion.TUsuario = GetTUsuario(dr.GetInt32(4), conn); } if (dr[5] != DBNull.Value) { tSustitucion.TDispositivoOriginal = GetTDispositivo(dr.GetInt32(5), conn); } if (dr[6] != DBNull.Value) { tSustitucion.TDispositivoSustituto = GetTDispositivo(dr.GetInt32(6), conn); } if (dr[7] != DBNull.Value) { tSustitucion.TRevision = GetTRevision(dr.GetInt32(7), conn); } if (dr[8] != DBNull.Value) { tSustitucion.TPrograma = GetTPrograma(dr.GetInt32(8), conn); } if (dr[10] != DBNull.Value) { tSustitucion.Pos = dr.GetString(10); } if (dr[11] != DBNull.Value) { tSustitucion.Pos = dr.GetString(11); } if (tSustitucion.TDispositivoOriginal != null) { tSustitucion.NInstalacion = tSustitucion.TDispositivoOriginal.Instalacion.Nombre; } } if (!dr.IsClosed) { dr.Close(); } } return(tSustitucion); }
public static IList <TSustitucion> GetTSustituciones(TDispositivo dispositivo, SqlCeConnection conn) { IList <TSustitucion> l = new List <TSustitucion>(); string sql = String.Format("SELECT * FROM Sustituciones WHERE dispo_id = {0} ORDER BY fecha ASC", dispositivo.DispositivoId); using (SqlCeCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; SqlCeDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { TSustitucion tSustitucion = new TSustitucion() { SustitucionId = dr.GetInt32(0), Comentarios = dr.GetString(2), Estado = dr.GetString(3), Abm = dr.GetByte(9) }; if (dr[1] != DBNull.Value) { tSustitucion.Fecha = dr.GetDateTime(1); } if (dr[4] != DBNull.Value) { tSustitucion.TUsuario = GetTUsuario(dr.GetInt32(4), conn); } if (dr[5] != DBNull.Value) { tSustitucion.TDispositivoOriginal = GetTDispositivo(dr.GetInt32(5), conn); } if (dr[6] != DBNull.Value) { tSustitucion.TDispositivoSustituto = GetTDispositivo(dr.GetInt32(6), conn); } if (dr[7] != DBNull.Value) { tSustitucion.TRevision = GetTRevision(dr.GetInt32(7), conn); } if (dr[8] != DBNull.Value) { tSustitucion.TPrograma = GetTPrograma(dr.GetInt32(8), conn); } if (dr[10] != DBNull.Value) { tSustitucion.Pos = dr.GetString(10); } if (dr[11] != DBNull.Value) { tSustitucion.Pos = dr.GetString(11); } if (tSustitucion.TDispositivoOriginal != null) { tSustitucion.NInstalacion = tSustitucion.TDispositivoOriginal.Instalacion.Nombre; } l.Add(tSustitucion); } if (!dr.IsClosed) { dr.Close(); } } return(l); }
public static OperationResult ImportSeriale() { var result = new OperationResult(); try { var connDest = new SqlCeConnection(Constants.ConnectionString); var connSource = new SqlCeConnection("Data Source = Desene.sdf;Max Database Size=4091;Persist Security Info=False"); connDest.Open(); connSource.Open(); const string insertString = @" INSERT INTO FileDetail ( ParentId, FileName, Year, Theme, Season, InsertedDate, LastChangeDate, Recommended, RecommendedLink, DescriptionLink, Poster, Notes) VALUES ( @ParentId, @FileName, @Year, @Theme, @Season, @InsertedDate, @LastChangeDate, @Recommended, @RecommendedLink, @DescriptionLink, @Poster, @Notes)"; try { var commandSource = new SqlCeCommand("select * from Seriale", connSource); using (var reader = commandSource.ExecuteReader()) { while (reader.Read()) { if (string.IsNullOrEmpty(reader["Titlu"].ToString())) { continue; } var cmd = new SqlCeCommand(insertString, connDest) { CommandType = CommandType.Text }; cmd.Parameters.AddWithValue("@ParentId", -1); cmd.Parameters.AddWithValue("@FileName", reader["Titlu"].ToString()); cmd.Parameters.AddWithValue("@Theme", string.Empty); cmd.Parameters.AddWithValue("@Year", string.Empty); cmd.Parameters.AddWithValue("@Season", string.Empty); if (!(reader["DataAdaugare"] is DBNull)) { cmd.Parameters.AddWithValue("@InsertedDate", (DateTime)reader["DataAdaugare"]); } else { cmd.Parameters.AddWithValue("@InsertedDate", DBNull.Value); } if (!(reader["DataUM"] is DBNull)) { cmd.Parameters.AddWithValue("@LastChangeDate", (DateTime)reader["DataUM"]); } else { cmd.Parameters.AddWithValue("@LastChangeDate", DBNull.Value); } cmd.Parameters.AddWithValue("@Recommended", reader["Recomandat"].ToString()); cmd.Parameters.AddWithValue("@RecommendedLink", reader["RecomandatLink"].ToString() != "0" ? reader["RecomandatLink"].ToString() : string.Empty); cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString()); cmd.Parameters.AddWithValue("@Notes", reader["Obs"].ToString()); if (!(reader["Cover"] is DBNull)) { cmd.Parameters.AddWithValue("@Poster", (byte[])reader["Cover"]); } else { cmd.Parameters.AddWithValue("@Poster", DBNull.Value); } cmd.ExecuteNonQuery(); cmd.CommandText = "Select @@Identity"; var seriesId = (int)(decimal)cmd.ExecuteScalar(); var commandSource2 = new SqlCeCommand("select * from Episoade where SerialId = " + reader["Id"], connSource); using (var readerEp = commandSource2.ExecuteReader()) { while (readerEp.Read()) { var cmdEp = new SqlCeCommand(insertString, connDest) { CommandType = CommandType.Text }; cmdEp.Parameters.AddWithValue("@ParentId", seriesId); cmdEp.Parameters.AddWithValue("@FileName", readerEp["Titlu"].ToString()); cmdEp.Parameters.AddWithValue("@Year", readerEp["An"].ToString()); cmdEp.Parameters.AddWithValue("@Theme", readerEp["Tematica"].ToString()); cmdEp.Parameters.AddWithValue("@Season", readerEp["Sezon"].ToString()); if (!(readerEp["DataAdaugare"] is DBNull)) { cmdEp.Parameters.AddWithValue("@InsertedDate", (DateTime)readerEp["DataAdaugare"]); } else { cmdEp.Parameters.AddWithValue("@InsertedDate", DBNull.Value); } if (!(readerEp["DataUM"] is DBNull)) { cmdEp.Parameters.AddWithValue("@LastChangeDate", (DateTime)readerEp["DataUM"]); } else { cmdEp.Parameters.AddWithValue("@LastChangeDate", DBNull.Value); } cmdEp.Parameters.AddWithValue("@Recommended", DBNull.Value); cmdEp.Parameters.AddWithValue("@RecommendedLink", DBNull.Value); cmdEp.Parameters.AddWithValue("@DescriptionLink", DBNull.Value); cmdEp.Parameters.AddWithValue("@Notes", DBNull.Value); cmdEp.Parameters.AddWithValue("@Poster", DBNull.Value); cmdEp.ExecuteNonQuery(); } } } } } finally { connDest.Close(); connSource.Close(); } } catch (Exception e) { result.FailWithMessage(e); } return(result); }
/* * public static OperationResult ImportFilmeHD() * { * var result = new OperationResult(); * * try * { * var connDest = new SqlCeConnection(Constants.ConnectionString); * var connSource = new SqlCeConnection("Data Source = Desene.sdf;Persist Security Info=False"); * connDest.Open(); * connSource.Open(); * * const string insertString = * @" * INSERT INTO MovieOrSeries ( * Recommended, * RecommendedLink, * DescriptionLink, * Poster, * IsSerie, * Notes) * VALUES ( * @Recommended, * @RecommendedLink, * @DescriptionLink, * @Poster, * @IsSerie, * @Notes)"; * * const string insertString2 = * @" * INSERT INTO FileDetail ( * MovieOrSeriesId, * FileName, * Year, * Theme, * InsertedDate, * LastChangeDate) * VALUES ( * @MovieOrSeriesId, * @FileName, * @Year, * @Theme, * @InsertedDate, * @LastChangeDate)"; * * //var sr = File.CreateText("googllinks.txt"); * var googleUrls = File.ReadAllLines(@"goo.gl to long url.csv") * .Select(ShortToLongUrl.FromCsv) * .ToList(); * * var urlSLErrors = ""; * * try * { * var commandSource = new SqlCeCommand("select * from Filme", connSource); * * using (var reader = commandSource.ExecuteReader()) * { * while (reader.Read()) * { * if (string.IsNullOrEmpty(reader["Titlu"].ToString())) continue; * * var cmd = new SqlCeCommand(insertString, connDest) { CommandType = CommandType.Text }; * * cmd.Parameters.AddWithValue("@Recommended", reader["Recomandat"].ToString()); * cmd.Parameters.AddWithValue("@RecommendedLink", reader["RecomandatLink"].ToString() != "0" ? reader["RecomandatLink"].ToString() : string.Empty); * cmd.Parameters.AddWithValue("@IsSerie", false); * cmd.Parameters.AddWithValue("@Notes", reader["Obs"].ToString()); * //cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString() != "0" ? reader["MoreInfo"].ToString() : string.Empty); * * * if (reader["MoreInfo"].ToString() == "0" || string.IsNullOrEmpty(reader["MoreInfo"].ToString())) * { * cmd.Parameters.AddWithValue("@DescriptionLink", string.Empty); * } * else * { * if (reader["MoreInfo"].ToString().Contains("goo.gl")) * { * var shortUrl = reader["MoreInfo"].ToString().Replace("#", ""); * var longUrlObj = googleUrls.FirstOrDefault(s => s.ShortUrl == shortUrl); * * if (longUrlObj == null) * { * cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString()); * urlSLErrors += reader["Id"].ToString() + ", "; * } * else * { * cmd.Parameters.AddWithValue("@DescriptionLink", longUrlObj.LongUrl); * } * * //sr.WriteLine(reader["MoreInfo"].ToString().Replace("#", "")); * } * else * { * cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString()); * } * } * * if (!(reader["Cover"] is DBNull)) * cmd.Parameters.AddWithValue("@Poster", (byte[])reader["Cover"]); * else * cmd.Parameters.AddWithValue("@Poster", DBNull.Value); * * * cmd.ExecuteNonQuery(); * * * cmd.CommandText = "Select @@Identity"; * var newId = (int)(decimal)cmd.ExecuteScalar(); * * cmd = new SqlCeCommand(insertString2, connDest) { CommandType = CommandType.Text }; * cmd.Parameters.AddWithValue("@MovieOrSeriesId", newId); * cmd.Parameters.AddWithValue("@FileName", reader["Titlu"].ToString()); * cmd.Parameters.AddWithValue("@Year", reader["An"].ToString()); * cmd.Parameters.AddWithValue("@Theme", reader["Tematica"].ToString()); * * if (!(reader["DataAdaugare"] is DBNull)) * cmd.Parameters.AddWithValue("@InsertedDate", (DateTime)reader["DataAdaugare"]); * else * cmd.Parameters.AddWithValue("@InsertedDate", DBNull.Value); * * if (!(reader["DataUM"] is DBNull)) * cmd.Parameters.AddWithValue("@LastChangeDate", (DateTime)reader["DataUM"]); * else * cmd.Parameters.AddWithValue("@LastChangeDate", DBNull.Value); * * cmd.ExecuteNonQuery(); * } * } * } * finally * { * connDest.Close(); * connSource.Close(); * * //sr.Close(); * } * } * catch (Exception e) * { * result.FailWithMessage(e); * } * * return result; * } * * public static OperationResult ImportSeriale() * { * var result = new OperationResult(); * * try * { * var connDest = new SqlCeConnection(Constants.ConnectionString); * var connSource = new SqlCeConnection("Data Source = Desene.sdf;Persist Security Info=False"); * * connDest.Open(); * connSource.Open(); * * const string insertString = * @" * INSERT INTO MovieOrSeries ( * Recommended, * RecommendedLink, * DescriptionLink, * Poster, * IsSerie, * Notes) * VALUES ( * @Recommended, * @RecommendedLink, * @DescriptionLink, * @Poster, * @IsSerie, * @Notes)"; * * const string insertString2 = * @" * INSERT INTO FileDetail ( * MovieOrSeriesId, * ParentId, * FileName, * Year, * Theme, * Season, * InsertedDate, * LastChangeDate) * VALUES ( * @MovieOrSeriesId, * @ParentId, * @FileName, * @Year, * @Theme, * @Season, * @InsertedDate, * @LastChangeDate)"; * * try * { * var commandSource = new SqlCeCommand("select * from Seriale", connSource); * * using (var reader = commandSource.ExecuteReader()) * { * while (reader.Read()) * { * if (string.IsNullOrEmpty(reader["Titlu"].ToString())) continue; * * var cmd = new SqlCeCommand(insertString, connDest) { CommandType = CommandType.Text }; * * cmd.Parameters.AddWithValue("@Recommended", reader["Recomandat"].ToString()); * cmd.Parameters.AddWithValue("@RecommendedLink", reader["RecomandatLink"].ToString() != "0" ? reader["RecomandatLink"].ToString() : string.Empty); * cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString()); * cmd.Parameters.AddWithValue("@IsSerie", true); * cmd.Parameters.AddWithValue("@Notes", reader["Obs"].ToString()); * * if (!(reader["Cover"] is DBNull)) * cmd.Parameters.AddWithValue("@Poster", (byte[])reader["Cover"]); * else * cmd.Parameters.AddWithValue("@Poster", DBNull.Value); * * * cmd.ExecuteNonQuery(); * * cmd.CommandText = "Select @@Identity"; * var newId = (int)(decimal)cmd.ExecuteScalar(); * * cmd = new SqlCeCommand(insertString2, connDest) { CommandType = CommandType.Text }; * cmd.Parameters.AddWithValue("@MovieOrSeriesId", newId); * cmd.Parameters.AddWithValue("@ParentId", -1); * cmd.Parameters.AddWithValue("@FileName", reader["Titlu"].ToString()); * cmd.Parameters.AddWithValue("@Theme", string.Empty); * cmd.Parameters.AddWithValue("@Year", string.Empty); * cmd.Parameters.AddWithValue("@Season", string.Empty); * * if (!(reader["DataAdaugare"] is DBNull)) * cmd.Parameters.AddWithValue("@InsertedDate", (DateTime)reader["DataAdaugare"]); * else * cmd.Parameters.AddWithValue("@InsertedDate", DBNull.Value); * * if (!(reader["DataUM"] is DBNull)) * cmd.Parameters.AddWithValue("@LastChangeDate", (DateTime)reader["DataUM"]); * else * cmd.Parameters.AddWithValue("@LastChangeDate", DBNull.Value); * * * cmd.ExecuteNonQuery(); * cmd.CommandText = "Select @@Identity"; * var newId2 = (int)(decimal)cmd.ExecuteScalar(); * * var commandSource2 = new SqlCeCommand("select * from Episoade where SerialId = " + reader["Id"], connSource); * * using (var readerEp = commandSource2.ExecuteReader()) * { * while (readerEp.Read()) * { * var cmdEp = new SqlCeCommand(insertString2, connDest) { CommandType = CommandType.Text }; * cmdEp.Parameters.AddWithValue("@MovieOrSeriesId", newId); * cmdEp.Parameters.AddWithValue("@ParentId", newId2); * cmdEp.Parameters.AddWithValue("@FileName", readerEp["Titlu"].ToString()); * cmdEp.Parameters.AddWithValue("@Year", readerEp["An"].ToString()); * cmdEp.Parameters.AddWithValue("@Theme", readerEp["Tematica"].ToString()); * cmdEp.Parameters.AddWithValue("@Season", readerEp["Sezon"].ToString()); * * if (!(readerEp["DataAdaugare"] is DBNull)) * cmdEp.Parameters.AddWithValue("@InsertedDate", (DateTime)readerEp["DataAdaugare"]); * else * cmdEp.Parameters.AddWithValue("@InsertedDate", DBNull.Value); * * if (!(readerEp["DataUM"] is DBNull)) * cmdEp.Parameters.AddWithValue("@LastChangeDate", (DateTime)readerEp["DataUM"]); * else * cmdEp.Parameters.AddWithValue("@LastChangeDate", DBNull.Value); * * * cmdEp.ExecuteNonQuery(); * } * } * } * } * } * finally * { * connDest.Close(); * connSource.Close(); * } * } * catch (Exception e) * { * result.FailWithMessage(e); * } * * return result; * } */ public static OperationResult ImportFilmeHD() { var result = new OperationResult(); try { var connDest = new SqlCeConnection(Constants.ConnectionString); var connSource = new SqlCeConnection("Data Source = Desene.sdf;Max Database Size=4091;Persist Security Info=False"); connDest.Open(); connSource.Open(); const string insertString = @" INSERT INTO FileDetail ( FileName, Year, Theme, InsertedDate, LastChangeDate, Recommended, RecommendedLink, DescriptionLink, Poster, Trailer, Notes, NlAudioSource) VALUES ( @FileName, @Year, @Theme, @InsertedDate, @LastChangeDate, @Recommended, @RecommendedLink, @DescriptionLink, @Poster, @Trailer, @Notes, @NlAudioSource)"; //var sr = File.CreateText("googllinks.txt"); var googleUrls = File.ReadAllLines(@"goo.gl to long url.csv") .Select(ShortToLongUrl.FromCsv) .ToList(); var urlSLErrors = ""; try { var commandSource = new SqlCeCommand("select * from Filme", connSource); using (var reader = commandSource.ExecuteReader()) { while (reader.Read()) { if (string.IsNullOrEmpty(reader["Titlu"].ToString())) { continue; } var cmd = new SqlCeCommand(insertString, connDest) { CommandType = CommandType.Text }; var titluCorectat = reader["Titlu"].ToString().Replace(": ", " - "); cmd.Parameters.AddWithValue("@FileName", titluCorectat); cmd.Parameters.AddWithValue("@Year", reader["An"].ToString()); cmd.Parameters.AddWithValue("@Theme", reader["Tematica"].ToString()); if (!(reader["DataAdaugare"] is DBNull)) { cmd.Parameters.AddWithValue("@InsertedDate", (DateTime)reader["DataAdaugare"]); } else { cmd.Parameters.AddWithValue("@InsertedDate", DBNull.Value); } if (!(reader["DataUM"] is DBNull)) { cmd.Parameters.AddWithValue("@LastChangeDate", (DateTime)reader["DataUM"]); } else { cmd.Parameters.AddWithValue("@LastChangeDate", DBNull.Value); } cmd.Parameters.AddWithValue("@Recommended", reader["Recomandat"].ToString()); cmd.Parameters.AddWithValue("@RecommendedLink", reader["RecomandatLink"].ToString() != "0" ? reader["RecomandatLink"].ToString() : string.Empty); var obsList = new List <string> { reader["Obs"].ToString().Trim(), reader["Obs2"].ToString().Trim(), reader["Obs3"].ToString().Trim() }; obsList.RemoveAll(n => n == string.Empty); var notes = string.Join(Environment.NewLine, obsList); cmd.Parameters.AddWithValue("@Notes", notes); //cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString() != "0" ? reader["MoreInfo"].ToString() : string.Empty); cmd.Parameters.AddWithValue("@Trailer", reader["Trailer"].ToString()); cmd.Parameters.AddWithValue("@NlAudioSource", reader["Nlsource"].ToString()); if (reader["MoreInfo"].ToString() == "0" || string.IsNullOrEmpty(reader["MoreInfo"].ToString())) { cmd.Parameters.AddWithValue("@DescriptionLink", string.Empty); } else { if (reader["MoreInfo"].ToString().Contains("goo.gl")) { var shortUrl = reader["MoreInfo"].ToString().Replace("#", ""); var longUrlObj = googleUrls.FirstOrDefault(s => s.ShortUrl == shortUrl); if (longUrlObj == null) { cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString()); urlSLErrors += reader["Id"].ToString() + ", "; } else { cmd.Parameters.AddWithValue("@DescriptionLink", longUrlObj.LongUrl); } //sr.WriteLine(reader["MoreInfo"].ToString().Replace("#", "")); } else { cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString()); } } if (!(reader["Cover"] is DBNull)) { cmd.Parameters.AddWithValue("@Poster", (byte[])reader["Cover"]); } else { cmd.Parameters.AddWithValue("@Poster", DBNull.Value); } cmd.ExecuteNonQuery(); } } } finally { connDest.Close(); connSource.Close(); //sr.Close(); } } catch (Exception e) { result.FailWithMessage(e); } return(result); }
private void btnConvert_Click(object sender, EventArgs e) { string cmd = ""; int count = 0; create_SQLCE_database(); // Create the SQL Server CE database file and a table within it string SQLconnectionString = "server=(local); database=PTHData; Trusted_Connection=True;"; // open PTHData.mdf string SQLCEconnectionString = "Data Source=" + Application.StartupPath + "\\pthData.sdf;Persist Security Info=False"; // open PTHDATA.sdf // open the input and output database SqlCeConnection SQLCEconnection = new SqlCeConnection(SQLCEconnectionString); try { SQLCEconnection.Open(); } catch (SqlCeException ex) { string errorMessages = "A SQLCE exception occurred on open.\n" + ex.Message; MessageBox.Show(errorMessages, "Convert"); return; } SqlConnection SQLconnection = new SqlConnection(SQLconnectionString); try { SQLconnection.Open(); } catch (SqlException ex) { string errorMessages = "A SQL exception occurred on open.\n" + ex.Message; MessageBox.Show( errorMessages, "Convert"); return; } //Databases are not open, time to convert SqlCommand cmdread = new SqlCommand(); cmdread.Connection = SQLconnection; cmdread.CommandText = "Select * from USTimeZones"; SqlDataReader drread = null; SqlCeCommand cmdwrite = new SqlCeCommand(); cmdwrite.Connection = SQLCEconnection; try { drread = cmdread.ExecuteReader(); while (drread.Read()) { drread["timezone"].ToString(); cmd = "Insert into USTimeZones values ('" + drread["state"].ToString() + "','" + drread["city"].ToString() + "','" + drread["county"].ToString() + "','" + drread["timezone"].ToString() + "','" + drread["timetype"].ToString() + "','" + drread["latitude"].ToString() + "','" + drread["longitude"].ToString() + "')"; cmdwrite.CommandText = cmd; try { cmdwrite.ExecuteNonQuery(); count++; } catch (SqlCeException ex) { string errorMessages = "An SQL exception occurred on writing the SQLCE record.\n" + ex.Message; MessageBox.Show(errorMessages, "Convert"); SQLCEconnection.Close(); SQLconnection.Close(); return; } } } catch (SqlException ex) { string errorMessages = "A SQL exception occurred reading records.\n" + ex.Message; MessageBox.Show(errorMessages, "Convert"); } catch (Exception ex) { string errorMessages = "A General exception occurred reading records.\n" + ex.Message; MessageBox.Show(errorMessages, "Convert"); } MessageBox.Show("Records written: " + count.ToString(), "Conversion complete"); drread.Close(); SQLconnection.Close(); SQLCEconnection.Close(); }
private void btnRequestSync_Click(object sender, EventArgs e) { try { if (cmbSyncData.Text != "----Select----" && cmbSyncData.Text != "" && cmbSyncData.Text != "System.Data.DataRowView") { cmbSyncData.Enabled = false; btnRequestSync.Enabled = false; lblStatus.Text = ""; string sql = ""; if (cmbSyncData.Text == "CargoGroup Master") { sql = "SELECT CargoGroupId,CargoGroupName FROM [dbo].[CargoGroupMst] where RStatus='Active'"; } else if (cmbSyncData.Text == "Commodity Master") { sql = "SELECT CommodityId,CommodityName,CargoGroupId,RFIDCode FROM [dbo].[CommodityMst] where RStatus='Active'"; } else 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 == "Truck Master") { //sql = "Select EqupmetName,EqupmentID from [dbo].[EquipmentMst] where [Status='Active']"; } string CONNSTRING = sqlConnection; SqlConnection dbCon = new SqlConnection(CONNSTRING); dbCon.Open(); SqlDataAdapter da = new SqlDataAdapter(sql, dbCon); DataTable dt = new DataTable(); da.Fill(dt); if (cmbSyncData.Text == "CargoGroup Master") { clearlocaldbCargoGroupMaster(); } else if (cmbSyncData.Text == "Commodity Master") { clearlocaldbCommodityMaster(); } else if (cmbSyncData.Text == "User Master") { clearlocaldbUserMaster(); } else if (cmbSyncData.Text == "Loader Master") { clearlocaldbLoaderMaster(); } else if (cmbSyncData.Text == "Truck Master") { clearlocaldbTruckMaster(); } //................................................ lblStatus.Text = "Sync In Progress...."; string InsertQry = ""; try { if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { 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 == "Truck Master") { InsertQry = "INSERT INTO [TruckMaster] ([Id],[Tkid],[TruckNo],[Tagid],[TagNo],[Status]) Values ('" + dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() + "','Active')"; } string CONN_STRING = localConnection; SqlCeConnection Con = new SqlCeConnection(CONN_STRING); Con.Open(); SqlCeCommand cmd = new SqlCeCommand(InsertQry, Con); cmd.ExecuteNonQuery(); Con.Close(); lblStatus.Text = "Sync In Progress...."; System.Threading.Thread.Sleep(5000); } } } else { throw new Exception("No row for insertion"); } dt.Dispose(); lblStatus.Text = "Sync Complete."; } catch (Exception ex) { dt.Dispose(); throw new Exception("Please attach file in Proper format."); } } cmbSyncData.Enabled = true; btnRequestSync.Enabled = true; } catch { lblStatus.Text = "Wifi Not Available."; } }
/// <summary> /// 根据最新期数获取同期不重复的红球 /// </summary> /// <param name="term">最新期数</param> /// <returns>同期不重复的红球列表</returns> public List <int> findSameForecastListOfTerm(string term) { con.Open(); List <int> list = new List <int>(); Queue queue = new Queue(); SqlCeCommand cmd = con.CreateCommand(); try { String temp = term.Substring(4, 3); for (int i = 0; i < numConfig.fullTerm.Count; i++) { //查询一期的红球数据 cmd = con.CreateCommand(); String dterm = numConfig.fullTerm[i] + temp; cmd.CommandText = "SELECT red_num1,red_num2,red_num3,red_num4,red_num5,red_num6 FROM ticket WHERE [term] = @Term"; cmd.Parameters.Add("@Term", SqlDbType.NVarChar); cmd.Parameters["@Term"].Value = dterm; rdr = cmd.ExecuteReader(); while (rdr.Read()) { if (!queue.Contains(rdr.GetInt16(0))) { queue.Enqueue(rdr.GetInt16(0)); list.Add(rdr.GetInt16(0)); } if (!queue.Contains(rdr.GetInt16(1))) { queue.Enqueue(rdr.GetInt16(1)); list.Add(rdr.GetInt16(1)); } if (!queue.Contains(rdr.GetInt16(2))) { queue.Enqueue(rdr.GetInt16(2)); list.Add(rdr.GetInt16(2)); } if (!queue.Contains(rdr.GetInt16(3))) { queue.Enqueue(rdr.GetInt16(3)); list.Add(rdr.GetInt16(3)); } if (!queue.Contains(rdr.GetInt16(4))) { queue.Enqueue(rdr.GetInt16(4)); list.Add(rdr.GetInt16(4)); } if (!queue.Contains(rdr.GetInt16(5))) { queue.Enqueue(rdr.GetInt16(5)); list.Add(rdr.GetInt16(5)); } } cmd.Dispose(); } } finally { cmd.Dispose(); con.Close(); } return(list); }
public static int NewServerWeb(List <KeyValuePair <string, string> > listServer, string strServerTitle, int intServerMemory = 1024) { SqlCeCommand cmd = new SqlCeCommand(); cmd.Connection = connLocal; //Create the server and get an ID cmd.CommandText = "INSERT INTO MCServers (ServerTitle, ServerWrapperMode, ServerAssignedMemory, ServerAutostart) VALUES (@title, 0, @mem, 0)"; cmd.Parameters.Add("@title", strServerTitle); cmd.Parameters.Add("@mem", intServerMemory); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText = "SELECT @@IDENTITY"; int intNewID = Convert.ToInt32(cmd.ExecuteScalar()); //Set up Files + Folders if (!Directory.Exists(Core.StoragePath + intNewID.ToString())) { Directory.CreateDirectory(Core.StoragePath + intNewID.ToString()); } //if (!Directory.Exists(Core.StoragePath + intNewID.ToString() + @"\config\")) Directory.CreateDirectory(Core.StoragePath + intNewID.ToString() + @"\config\"); if (!Directory.Exists(Core.StoragePath + intNewID.ToString() + @"\world\")) { Directory.CreateDirectory(Core.StoragePath + intNewID.ToString() + @"\world\"); } if (!Directory.Exists(Core.StoragePath + intNewID.ToString() + @"\backups\")) { Directory.CreateDirectory(Core.StoragePath + intNewID.ToString() + @"\backups\"); } if (!Directory.Exists(Core.StoragePath + intNewID.ToString() + @"\renders\")) { Directory.CreateDirectory(Core.StoragePath + intNewID.ToString() + @"\renders\"); } if (!Directory.Exists(Core.StoragePath + intNewID.ToString() + @"\renders\overviewer\")) { Directory.CreateDirectory(Core.StoragePath + intNewID.ToString() + @"\renders\overviewer\"); } if (!Directory.Exists(Core.StoragePath + intNewID.ToString() + @"\renders\overviewer\output\")) { Directory.CreateDirectory(Core.StoragePath + intNewID.ToString() + @"\renders\overviewer\output\"); } if (!File.Exists(Core.StoragePath + intNewID.ToString() + @"\banned-ips.txt")) { File.Create(Core.StoragePath + intNewID.ToString() + @"\banned-ips.txt"); } if (!File.Exists(Core.StoragePath + intNewID.ToString() + @"\banned-players.txt")) { File.Create(Core.StoragePath + intNewID.ToString() + @"\banned-players.txt"); } if (!File.Exists(Core.StoragePath + intNewID.ToString() + @"\ops.txt")) { File.Create(Core.StoragePath + intNewID.ToString() + @"\ops.txt"); } if (!File.Exists(Core.StoragePath + intNewID.ToString() + @"\white-list.txt")) { File.Create(Core.StoragePath + intNewID.ToString() + @"\white-list.txt"); } //Insert the settings into the DB for this server foreach (var element in listServer) { cmd.Parameters.Clear(); cmd.CommandText = "INSERT INTO MCSettings (ServerID, SettingName, SettingValue) VALUES (@id, @name, @value);"; cmd.Parameters.Add("@id", intNewID); cmd.Parameters.Add("@name", element.Key); cmd.Parameters.Add("@value", element.Value); cmd.ExecuteNonQuery(); } //Create default config files BuildServerProperties(intNewID); //Add the server to the collection MCServer myServer = new MCServer(intNewID); Core.Servers.Add(intNewID, myServer); return(intNewID); }
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 void ValidateTable(IEntityInfo entity) { var entityName = entity.GetNameInStore(); var connection = _datastore.GetConnection(); // first make sure the table exists if (!_datastore.TableExists(entityName)) { _datastore.CreateTable(connection, entity); return; } using (var command = new SqlCeCommand()) { command.Transaction = _datastore.CurrentTransaction as SqlCeTransaction; command.Connection = connection as SqlCeConnection; foreach (var field in entity.Fields) { // yes, I realize hard-coded ordinals are not a good practice, but the SQL isn't changing, it's method specific var sql = string.Format("SELECT column_name, " // 0 + "data_type, " // 1 + "character_maximum_length, " // 2 + "numeric_precision, " // 3 + "numeric_scale, " // 4 + "is_nullable " + "FROM information_schema.columns " + "WHERE (table_name = '{0}' AND column_name = '{1}')", entityName, field.FieldName); command.CommandText = sql; using (var reader = command.ExecuteReader()) { if (!reader.Read()) { // field doesn't exist - we must create it var alter = new StringBuilder(string.Format("ALTER TABLE [{0}] ", entity.GetNameInStore())); alter.Append(string.Format("ADD {0}", field.GetFieldDefinitionSqlQuery())); using (var altercmd = new SqlCeCommand(alter.ToString(), connection as SqlCeConnection)) { altercmd.ExecuteNonQuery(); } } } } _datastore.VerifiyPrimaryKey(entity.PrimaryKey); foreach (var foreignKey in entity.ForeignKeys) { _datastore.VerifyForeignKey(foreignKey); } foreach (var index in entity.Indexes) { _datastore.VerifyIndex(index); } } }
/// <summary> /// Return a table containing the AUDITED VALUES for the specified Audited Data Item category/name optionally /// returning just the data for a specific asset /// </summary> /// <param name="applicationID"></param> /// <returns></returns> public DataTable GetAuditedItemValues(Asset aAsset, string aCategory, string aName) { if (isDebugEnabled) { logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in"); } DataTable dataTable = new DataTable(TableNames.AUDITEDITEMS); if (compactDatabaseType) { try { using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection()) { string commandText = "SELECT _ASSETID ,_VALUE ,_DISPLAY_UNITS ,_DATATYPE FROM AUDITEDITEMS " + "WHERE _category = @cCategory AND _NAME = @cName"; if (aAsset != null) { commandText += " AND _ASSETID = @assetID"; } using (SqlCeCommand command = new SqlCeCommand(commandText, conn)) { if (aAsset != null) { command.Parameters.AddWithValue("@assetID", aAsset.AssetID); } command.Parameters.AddWithValue("@cCategory", aCategory); command.Parameters.AddWithValue("@cName", aName); new SqlCeDataAdapter(command).Fill(dataTable); } } } catch (SqlCeException ex) { Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine + "Please see the log file for further details."); logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex); } catch (Exception ex) { Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine + "Please see the log file for further details."); logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex); } } else { AuditWizardDataAccess lAuditWizardDataAccess = new AuditWizardDataAccess(); dataTable = lAuditWizardDataAccess.GetAuditedItemValues(aAsset, aCategory, aName); } if (isDebugEnabled) { logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out"); } return(dataTable); }
/// <summary> /// Deleted the selected profile. /// </summary> private void DeleteSelectedProfile() { if (cmbProfiles.Items.Count > 1) { bool changeDefault = false; string name = txtName.Text; try { DialogResult result = MessageBox.Show("Are you sure you want to delete " + name + "?", "Delete", MessageBoxButtons.YesNo); if (result == DialogResult.Yes) { using (SqlCeConnection conn = new SqlCeConnection(Properties.Settings.Default.switchexConnectionString)) { using (SqlCeCommand cmd = new SqlCeCommand("DELETE FROM Profiles WHERE ID=@id", conn)) { cmd.Parameters.AddWithValue("@id", selectedID); conn.Open(); // deletes the profile from the database cmd.ExecuteNonQuery(); // gets the profile from the list Profile delProfile = Globals.profiles.SingleOrDefault(item => item.ProfileID == selectedID); // if the profile was the default, reassign the default if (delProfile.ProfileDefault) { changeDefault = true; } // then remove it Globals.profiles.Remove(delProfile); if (changeDefault) { Globals.profiles[0].EditProfile(Globals.profiles[0].ProfileID, isDefault: true); } // if user selects to delete servers with their profile if (Properties.Settings.Default.deleteServersAfterProfile) { cmd.CommandText = "DELETE FROM Servers WHERE ServerProfile=@profile"; cmd.Parameters.AddWithValue("@profile", name); int moved = cmd.ExecuteNonQuery(); MessageBox.Show(name + " and " + moved + " servers deleted.", "Delete Profile"); } else { cmd.CommandText = "UPDATE Servers SET ServerProfile=@new WHERE ServerProfile=@profile"; cmd.Parameters.AddWithValue("@new", Globals.profiles.SingleOrDefault( item => item.ProfileDefault == true && item.ProfileName != name).ProfileName); cmd.Parameters.AddWithValue("@profile", name); int moved = cmd.ExecuteNonQuery(); if (moved == 0) { MessageBox.Show(name + " deleted. No servers were moved to another profile.", "Delete Profile"); } else { MessageBox.Show(name + " deleted. " + moved + (moved > 1 ? " servers" : " server") + " moved to profile \"" + Globals.profiles.SingleOrDefault(item => item.ProfileDefault == true).ProfileName + "\".", "Delete Profile"); } } } LoadAllProfiles(); } } } catch (Exception ex) { Globals.frmError.ShowDialog(ex); } } else { MessageBox.Show("You cannot delete your last profile.", "Delete Profile"); } }
// get supp doc from DB and save to temp folder then display. private void viewSuppDocButton_Click(object sender, EventArgs e) { // String selectedDocID; String selectedDocName; int rowIndex = 0; // Get the filename and docID Int32 selectedRowCount = this.allSuppDocsDataGridView.Rows.GetRowCount(DataGridViewElementStates.Selected); if (selectedRowCount > 0) { for (int i = 0; i < selectedRowCount; i++) { rowIndex = allSuppDocsDataGridView.SelectedRows[i].Index; } } DataGridViewRow row = this.allSuppDocsDataGridView.Rows[rowIndex]; // selectedDocID = row.Cells[0].Value.ToString(); selectedDocName = row.Cells[1].Value.ToString(); // Get the file contents from the database 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(); cmd.CommandText = "SELECT Document FROM SuppumentalDocs WHERE FileName = '" + selectedDocName + "'"; SqlCeDataReader reader = cmd.ExecuteReader(); reader = cmd.ExecuteReader(); Byte[] documentData = new Byte[0]; while (reader.Read()) { documentData = (byte[])reader[0]; } reader.Dispose(); TecanSuppDocsDatabase.Close(); // Create the new file in temp directory String tempFilePath = @AppDomain.CurrentDomain.BaseDirectory.ToString() + "temp"; System.IO.Directory.CreateDirectory(tempFilePath); // If temp directory current contains any files, delete them System.IO.DirectoryInfo tempFiles = new DirectoryInfo(tempFilePath); foreach (FileInfo file in tempFiles.GetFiles()) { try { file.Delete(); } catch { } } String fullFilePathName = @tempFilePath + "\\" + selectedDocName; try { System.IO.FileStream fs = System.IO.File.Create(fullFilePathName); fs.Close(); // Write file contents into file BinaryWriter Writer = null; try { // Create a new stream to write to the file Writer = new BinaryWriter(File.OpenWrite(fullFilePathName)); // Writer raw data Writer.Write(documentData); Writer.Flush(); Writer.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } Process.Start(fullFilePathName); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void tableMaintnance_Load(object sender, EventArgs e) { partsListTableAdapter.Connection.ConnectionString = mainForm.whichDb; salesTypeTableAdapter.Connection.ConnectionString = mainForm.whichDb; dBMembershipTableAdapter.Connection.ConnectionString = mainForm.whichDb; sSPCategoryTableAdapter.Connection.ConnectionString = mainForm.whichDb; subCategoryTableAdapter.Connection.ConnectionString = mainForm.whichDb; categoryTableAdapter.Connection.ConnectionString = mainForm.whichDb; instrumentTableAdapter.Connection.ConnectionString = mainForm.whichDb; //String str1 = salesTypeTableAdapter.Connection.ConnectionString.ToString(); //String str2 = dBMembershipTableAdapter.Connection.ConnectionString.ToString(); //String str3 = sSPCategoryTableAdapter.Connection.ConnectionString.ToString(); //String str4 = subCategoryTableAdapter.Connection.ConnectionString.ToString(); //String str5 = categoryTableAdapter.Connection.ConnectionString.ToString(); //String str6 = instrumentTableAdapter.Connection.ConnectionString.ToString(); //String str7 = partsListTableAdapter.Connection.ConnectionString.ToString(); //MessageBox.Show(str1 + "\n" + str2 + "\n" + str3 + "\n" + str4 + "\n" + str5 + "\n" + str6 + "\n" + str7); this.partsListTableAdapter.Fill(this.tecanPartsListDataSet.PartsList); this.suppumentalDocsTableAdapter.Fill(this.tecanPartsListDataSet.SuppumentalDocs); this.salesTypeTableAdapter.Fill(this.tecanPartsListDataSet.SalesType); this.dBMembershipTableAdapter.Fill(this.tecanPartsListDataSet.DBMembership); this.sSPCategoryTableAdapter.Fill(this.tecanPartsListDataSet.SSPCategory); this.subCategoryTableAdapter.Fill(this.tecanPartsListDataSet.SubCategory); this.categoryTableAdapter.Fill(this.tecanPartsListDataSet.Category); this.instrumentTableAdapter.Fill(this.tecanPartsListDataSet.Instrument); // Add All Supplemental Documents to List 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(); cmd.CommandText = "SELECT DocID, FileName FROM SuppumentalDocs ORDER BY FileName"; SqlCeDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { allSuppDocsDataGridView.Rows.Add(reader[0].ToString(), reader[1].ToString()); } reader.Dispose(); TecanSuppDocsDatabase.Close(); allSuppDocsDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect; suppumentalDocsDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect; // Add Compatibilities to List ArrayList theCompatibilities = new ArrayList(); SqlCeConnection TecanDatabase = null; TecanDatabase = new SqlCeConnection(); // String dataPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase); TecanDatabase.ConnectionString = "Data Source=|DataDirectory|\\TecanPartsList.sdf;Max Database Size=4000;Max Buffer Size=1024;Persist Security Info=False"; TecanDatabase.Open(); cmd = TecanDatabase.CreateCommand(); cmd.CommandText = "SELECT CompatibilityName, CompatibilityID FROM Compatibility ORDER BY CompatibilityName"; reader = cmd.ExecuteReader(); while (reader.Read()) { theCompatibilities.Add(new Compatibilities(reader[0].ToString(), reader[1].ToString())); } reader.Dispose(); TecanDatabase.Close(); compatibilityListBox.DataSource = theCompatibilities; compatibilityListBox.DisplayMember = "Name"; compatibilityListBox.ValueMember = "ID"; }
private void btnFinalizar_Click(object sender, EventArgs e) { limpia(); List <string> listaUpdate = new List <string>(); List <string> listaCont = new List <string>(); SqlCeDataReader rs = null; Stack st = new Stack(); int contador = 0; try { int cuenta = getIntSDF("select count(*)FROM detalleInvCong WHERE (Estatus = 1)"); if (cuenta > 0) { SqlCeCommand select = new SqlCeCommand("SELECT IDDetInvCong, Conteo1 FROM detalleInvCong WHERE (Estatus = 1)", conexion); conexion.Open(); rs = select.ExecuteReader(); while (rs.Read()) { listaUpdate.Add(rs.GetValue(0).ToString()); listaCont.Add(rs.GetValue(1).ToString()); } int registros = listaUpdate.Count; for (int z = 0; z < registros; z++) { int cosa = ws.inserta("UPDATE detalleInvCong SET Estatus = 1, Conteo1 = " + listaCont[z] + " WHERE IDDetInvCong = " + listaUpdate[z], "ConsolaAdmin"); if (cosa == 1) { contador += 1; } } conexion.Close(); if ((contador > 0)) { /////* if (ws.inserta("update InventarioCongelado SET estatus = 1 WHERE IDInv =" + IdInv, "ConsolaAdmin") == 1) { MessageBox.Show("Transferencia completa", "EXITO"); } /////* ///// solo pruebas MessageBox.Show("Transferencia completa-//Tabla InventarioCongelado omitida", "EXITO"); //this.Dispose(); this.Close(); return; } } else { MessageBox.Show("No se realizó ningun cambio", "Informacion"); frmMenu_Inventario fmi = new frmMenu_Inventario(usuario); fmi.Visible = true; } } catch (Exception ex) { MessageBox.Show(ex.Message, "ERROR"); } }
private void button1_Click(object sender, EventArgs e) { switch (comboBox2.SelectedItem.ToString()) { case "Matches": sql = "update cricket set matches =" + textBox1.Text + " where pname = " + comboBox1.Text + ""; using (SqlCeCommand command = new SqlCeCommand(sql, sqlCon)) { try { command.ExecuteNonQuery(); } catch (SqlCeException exc) { MessageBox.Show("error"); }; MessageBox.Show("Updated Successfully"); } break; case "Runs": sql = "update cricket set runs =" + textBox1.Text + " where pname = " + comboBox1.Text + ""; using (SqlCeCommand command = new SqlCeCommand(sql, sqlCon)) { try { command.ExecuteNonQuery(); } catch (SqlCeException exc) { MessageBox.Show("error"); }; MessageBox.Show("Updated Successfully"); } break; case "Batting Rating": sql = "update cricket set batrat =" + textBox1.Text + " where pname =" + comboBox1.Text + ""; using (SqlCeCommand command = new SqlCeCommand(sql, sqlCon)) { try { command.ExecuteNonQuery(); } catch (SqlCeException exc) { MessageBox.Show("error"); }; MessageBox.Show("Updated Successfully"); } break; case "Wickets": sql = "update cricket set wickets =" + textBox1.Text + " where pname = " + comboBox1.Text + ""; using (SqlCeCommand command = new SqlCeCommand(sql, sqlCon)) { try { command.ExecuteNonQuery(); } catch (SqlCeException exc) { MessageBox.Show("error"); } MessageBox.Show("Updated Successfully"); } break; case "Bowling Rating": sql = "update cricket set bowlrat =" + textBox1.Text + " where pname = " + comboBox1.Text + ""; using (SqlCeCommand command = new SqlCeCommand(sql, sqlCon)) { try { command.ExecuteNonQuery(); } catch (SqlCeException exc) { MessageBox.Show("error"); } MessageBox.Show("Updated Successfully"); } break; case "Overall Rating": sql = "update cricket set overallrat =" + textBox1.Text + " where pname = " + comboBox1.Text + ""; using (SqlCeCommand command = new SqlCeCommand(sql, sqlCon)) { try { command.ExecuteNonQuery(); } catch (SqlCeException exc) { MessageBox.Show("error"); }; MessageBox.Show("Updated Successfully"); } break; } }
// redisplay the affected listbox, grid after edit public void returnFromEdit() { switch (currentTable) { case "Instrument": this.instrumentTableAdapter.Fill(this.tecanPartsListDataSet.Instrument); break; case "Category": this.categoryTableAdapter.Fill(this.tecanPartsListDataSet.Category); break; case "SubCategory": this.subCategoryTableAdapter.Fill(this.tecanPartsListDataSet.SubCategory); break; case "SSPCategory": this.sSPCategoryTableAdapter.Fill(this.tecanPartsListDataSet.SSPCategory); break; case "DBMembership": this.dBMembershipTableAdapter.Fill(this.tecanPartsListDataSet.DBMembership); break; case "SalesType": this.salesTypeTableAdapter.Fill(this.tecanPartsListDataSet.SalesType); break; case "Compatibility": // Add Compatibilities to List ArrayList theCompatibilities = new ArrayList(); SqlCeConnection TecanDatabase = null; TecanDatabase = new SqlCeConnection(); // String dataPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase); TecanDatabase.ConnectionString = "Data Source=|DataDirectory|\\TecanPartsList.sdf;Max Database Size=4000;Max Buffer Size=1024;Persist Security Info=False"; TecanDatabase.Open(); SqlCeCommand cmd = TecanDatabase.CreateCommand(); cmd.CommandText = "SELECT CompatibilityName, CompatibilityID FROM Compatibility ORDER BY CompatibilityName"; SqlCeDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { theCompatibilities.Add(new Compatibilities(reader[0].ToString(), reader[1].ToString())); } reader.Dispose(); TecanDatabase.Close(); compatibilityListBox.DataSource = theCompatibilities; compatibilityListBox.DisplayMember = "Name"; compatibilityListBox.ValueMember = "ID"; break; case "SuppumentalDocs": // Clear and rebuild the Supp Filelist Display allSuppDocsDataGridView.Rows.Clear(); 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 cmd2 = TecanSuppDocsDatabase.CreateCommand(); cmd2.CommandText = "SELECT DocID, FileName FROM SuppumentalDocs ORDER BY FileName"; SqlCeDataReader reader2 = cmd2.ExecuteReader(); reader2 = cmd2.ExecuteReader(); while (reader2.Read()) { allSuppDocsDataGridView.Rows.Add(reader2[0].ToString(), reader2[1].ToString()); } reader2.Dispose(); TecanSuppDocsDatabase.Close(); this.suppumentalDocsTableAdapter.Fill(this.tecanPartsListDataSet.SuppumentalDocs); break; } }
private void suppumentalDocsListBox_DoubleClick(object sender, EventArgs e) { String selectedDocName = suppumentalDocsListBox.GetItemText(suppumentalDocsListBox.SelectedItem); // Get the file contents from the database 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(); cmd.CommandText = "SELECT Document FROM SuppumentalDocs WHERE FileName = '" + selectedDocName + "'"; SqlCeDataReader reader = cmd.ExecuteReader(); reader = cmd.ExecuteReader(); Byte[] documentData = new Byte[0]; while (reader.Read()) { documentData = (byte[])reader[0]; } reader.Dispose(); TecanSuppDocsDatabase.Close(); // Create the new file in temp directory String tempFilePath = @AppDomain.CurrentDomain.BaseDirectory.ToString() + "temp"; System.IO.Directory.CreateDirectory(tempFilePath); // If temp directory current contains any files, delete them System.IO.DirectoryInfo tempFiles = new DirectoryInfo(tempFilePath); foreach (FileInfo file in tempFiles.GetFiles()) { file.Delete(); } String fullFilePathName = @tempFilePath + "\\" + selectedDocName; System.IO.FileStream fs = System.IO.File.Create(fullFilePathName); fs.Close(); // Write file contents into file BinaryWriter Writer = null; try { // Create a new stream to write to the file Writer = new BinaryWriter(File.OpenWrite(fullFilePathName)); // Writer raw data Writer.Write(documentData); Writer.Flush(); Writer.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } Process.Start(fullFilePathName); }
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 content1() { List <int> bucket = new List <int>(); DateTime fdt, tdt; fdt = fromdtp.Value; tdt = todtp.Value; dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); string query1 = "SELECT InvoiceNo, totalSGST, totalCGST, totalDiscount, InvoiceTime, totalItemPrice FROM customerDetails WHERE InvoiceTime>=@start AND InvoiceTime<=@end"; // 0 1 2 3 4 5 string query2 = "SELECT ItemName, quantity, TotalSellingPrice, TotalPurchasedPrice, perItemSGST, perItemCGST, discountPerItemType FROM invoiceDetails WHERE InvoiceNo="; SqlCeCommand cmd = new SqlCeCommand(query1, conn); SqlCeDataReader reader = null; try { cmd.Parameters.AddWithValue("@start", fdt); cmd.Parameters.AddWithValue("@end", tdt); reader = cmd.ExecuteReader(); while (reader.Read()) { invoiceno = reader.GetInt64(0).ToString(); totaldiscount = reader.GetString(3); totalsgst = reader.GetString(1); totalcgst = reader.GetString(2); totalsp = reader.GetString(5); sheet.Cells[rowcounter, 1] = invoiceno; sheet.Cells[rowcounter, 2] = reader.GetDateTime(4).ToString("dd/MM/yyyy"); getinfoperinvoice(conn, query2 + invoiceno); string totalPP = purchashed_price.ToString(); // Insert data to excel; sheet.Cells[rowcounter, 2] = "Total"; sheet.Cells[rowcounter, 5] = totalPP; sheet.Cells[rowcounter, 6] = totalsp; sheet.Cells[rowcounter, 7] = totaldiscount; sheet.Cells[rowcounter, 8] = totalsgst; sheet.Cells[rowcounter, 9] = totalcgst; sheet.Cells[rowcounter, 9].EntireRow.Font.Bold = true; sheet.Cells[rowcounter, 10] = double.Parse(totalsp) - double.Parse(totalPP) - double.Parse(totaldiscount); bucket.Add(rowcounter); rowcounter++; } if (bucket.Count > 0) { string grandpp, grandsp, grandsgst, grandcgst, granddisc, grandprofit; grandpp = "=SUM("; grandsp = "=SUM("; grandsgst = "=SUM("; grandcgst = "=SUM("; granddisc = "=SUM("; grandprofit = "=SUM("; for (int i = 0; i < bucket.Count; i++) { // Function for total Sum if (grandpp == "=SUM(") { grandpp += " E" + bucket[i]; grandsp += " F" + bucket[i]; grandsgst += " H" + bucket[i]; grandcgst += " I" + bucket[i]; granddisc += " G" + bucket[i]; grandprofit += " J" + bucket[i]; } else { grandpp += " , E" + bucket[i]; grandsp += " , F" + bucket[i]; grandsgst += " , H" + bucket[i]; grandcgst += " , I" + bucket[i]; granddisc += " , G" + bucket[i]; grandprofit += " , J" + bucket[i]; } } grandpp += " )"; grandsp += " )"; grandsgst += " )"; grandcgst += " )"; granddisc += " )"; grandprofit += " )"; rowcounter++; sheet.Range["E" + rowcounter].Formula = grandpp; sheet.Range["F" + rowcounter].Formula = grandsp; sheet.Range["H" + rowcounter].Formula = grandsgst; sheet.Range["I" + rowcounter].Formula = grandcgst; sheet.Range["G" + rowcounter].Formula = granddisc; sheet.Range["J" + rowcounter].Formula = grandprofit; sheet.Cells[rowcounter, 2] = "GRAND TOTAL"; sheet.Cells[rowcounter, 3].EntireRow.Font.Bold = true; sheet.get_Range("A" + rowcounter, "J" + rowcounter).Cells.Font.Size = 14; } } catch (Exception ex) { MessageBox.Show("" + ex); } }
public DataTable GetAllClientes() { //Crear Conexion y Abrirla SqlCeConnection Con = CrearConexion(); // Crear SQLCeCommand - Asignarle la conexion - Asignarle la instruccion SQL (consulta) SqlCeCommand Comando = new SqlCeCommand("SELECT Clientes.dnicuil, Clientes.apellido + ', ' + Clientes.nombre AS [Apellido y Nombre], Clientes.email, Clientes.tel1, Clientes.tel2 FROM Clientes", Con); //Crea DataTable para ser Origen de la Grilla DataTable dataTableClientes = new DataTable(); //Creacion de Columnas DataColumn dnicuilCOL = new DataColumn("DNI - CUIL"); DataColumn nombreyapellidoCOL = new DataColumn("Apellido y Nombre"); DataColumn emailCOL = new DataColumn("Email"); DataColumn tel1COL = new DataColumn("Teléfono"); DataColumn tel2COL = new DataColumn("Teléfono Alt."); //Agrego columnas a DataTable de Clientes dataTableClientes.Columns.Add(dnicuilCOL); dataTableClientes.Columns.Add(nombreyapellidoCOL); dataTableClientes.Columns.Add(emailCOL); dataTableClientes.Columns.Add(tel1COL); dataTableClientes.Columns.Add(tel2COL); try { Comando.Connection.Open(); SqlCeDataReader drClientes = Comando.ExecuteReader(); while (drClientes.Read()) { DataRow rowCliente = dataTableClientes.NewRow(); rowCliente[dnicuilCOL] = (string)drClientes["dnicuil"]; rowCliente[nombreyapellidoCOL] = (string)drClientes["Apellido y Nombre"]; rowCliente[emailCOL] = (string)drClientes["email"]; rowCliente[tel1COL] = (string)drClientes["tel1"]; rowCliente[tel2COL] = (string)drClientes["tel2"]; dataTableClientes.Rows.Add(rowCliente); } drClientes.Close(); } catch (Exception Ex) { Exception ExcepcionManejada = new Exception("Error al recuperar información de Clientes", Ex); throw ExcepcionManejada; } finally { Comando.Connection.Close(); } return(dataTableClientes); }
protected void BuidFormReserva(int resID) { string[] mesosArray = { "Gener", "Febrer", "Març", "Abril", "Maig", "Juny", "Juliol", "Agost", "Setembre", "Octubre", "Novembre", "Decembre" }; if (resID != 0) { SqlCeConnection linksql = new SqlCeConnection(@"Data Source='C:\Users\Uoc\Documents\GitHub\Uoc_ASPNET\restaurantuoc.sdf';Password='******'"); linksql.Open(); SqlCeCommand sqlQuery = new SqlCeCommand("SELECT * FROM reserves WHERE id=" + resID, linksql); SqlCeDataReader resultSql = sqlQuery.ExecuteReader(); while (resultSql.Read()) { nomForm.Text = resultSql["Nom"].ToString(); cognomForm.Text = resultSql["Cognoms"].ToString(); telForm.Text = resultSql["Telefon"].ToString(); DateTime Data = Convert.ToDateTime(resultSql["Data"].ToString()); dies.Items.FindByValue(Data.Day.ToString()).Selected = true; anys.Items.FindByValue(Data.Year.ToString()).Selected = true; mesos.Items.FindByValue(mesosArray[Data.Month - 1]).Selected = true; if (Data.Hour < 10) { hores.Items.FindByValue("0" + Data.Hour.ToString()).Selected = true; } else { hores.Items.FindByValue(Data.Hour.ToString()).Selected = true; } if (Data.Minute < 10) { minuts.Items.FindByValue("0" + Data.Minute.ToString()).Selected = true; } else { minuts.Items.FindByValue(Data.Minute.ToString()).Selected = true; } comensalForm.Text = resultSql["Comensals"].ToString(); comentForm.Text = resultSql["Comentaris"].ToString(); } linksql.Close(); } else { DateTime Data = DateTime.Now; dies.Items.FindByValue((Data.AddDays(1).Day).ToString()).Selected = true; // Pre-Seleccionem un dia més per la nova reserva. mesos.Items.FindByValue(mesosArray[Data.Month - 1]).Selected = true; anys.Items.FindByValue(Data.Year.ToString()).Selected = true; if (Data.Hour < 10) { hores.Items.FindByValue("0" + Data.AddHours(1).Hour.ToString()).Selected = true; // Pre-Seleccionem una hora més per la nova reserva. } else { if (int.Parse(Data.AddHours(1).Hour.ToString()) == 0) { hores.Items.FindByValue("00").Selected = true; } else { hores.Items.FindByValue(Data.AddHours(1).Hour.ToString()).Selected = true; } } if (Data.Minute < 10) { minuts.Items.FindByValue("0" + Data.Minute.ToString()).Selected = true; } else { minuts.Items.FindByValue(Data.Minute.ToString()).Selected = true; } } }
public bool IncluirEntrevistado(TEntrevistadoDOMINIO dadosEntrevistado) { try { StringBuilder queryTabelaEntrevistado = new StringBuilder(); string informacao = dadosEntrevistado.Informacao ? "1" : "0"; string dataNascimento = dadosEntrevistado.DataNascimento.HasValue? "'" + dadosEntrevistado.DataNascimento.Value.ToString("s") + "'": "null"; string dataNascimentoConjuge = dadosEntrevistado.DataNascimentoConjuge.HasValue ? "'" + dadosEntrevistado.DataNascimentoConjuge.Value.ToString("s") + "'" : "null"; queryTabelaEntrevistado.Append(@" INSERT INTO TEntrevistado "); queryTabelaEntrevistado.Append(@" ( CodigoEntrevista "); queryTabelaEntrevistado.Append(@" , Nome "); queryTabelaEntrevistado.Append(@" , CPF "); queryTabelaEntrevistado.Append(@" , DataNascimento "); queryTabelaEntrevistado.Append(@" , EstadoCivil "); queryTabelaEntrevistado.Append(@" , IDProfissao "); queryTabelaEntrevistado.Append(@" , FaixaEtaria "); queryTabelaEntrevistado.Append(@" , CapitalLimitado "); queryTabelaEntrevistado.Append(@" , DDD "); queryTabelaEntrevistado.Append(@" , Telefone "); queryTabelaEntrevistado.Append(@" , DDDCelular "); queryTabelaEntrevistado.Append(@" , Celular "); queryTabelaEntrevistado.Append(@" , Sexo "); queryTabelaEntrevistado.Append(@" , Informacao "); queryTabelaEntrevistado.Append(@" , IDProfissaoConjuge "); queryTabelaEntrevistado.Append(@" , FaixaEtariaConjuge "); queryTabelaEntrevistado.Append(@" , CapitalLimitadoConjuge "); queryTabelaEntrevistado.Append(@" , DataNascimentoConjuge ) "); queryTabelaEntrevistado.Append(@" VALUES ( " + dadosEntrevistado.CodigoEntrevista + " "); queryTabelaEntrevistado.Append(@" , '" + dadosEntrevistado.Nome + "' "); queryTabelaEntrevistado.Append(@" , '" + dadosEntrevistado.CPF + "' "); queryTabelaEntrevistado.Append(@" , " + dataNascimento + " "); queryTabelaEntrevistado.Append(@" , " + dadosEntrevistado.EstadoCivil + " "); queryTabelaEntrevistado.Append(@" , " + dadosEntrevistado.IDProfissao + " "); queryTabelaEntrevistado.Append(@" , " + dadosEntrevistado.FaixaEtaria + " "); queryTabelaEntrevistado.Append(@" , '" + dadosEntrevistado.CapitalLimitado + "' "); queryTabelaEntrevistado.Append(@" , '" + dadosEntrevistado.DDD + "' "); queryTabelaEntrevistado.Append(@" , '" + dadosEntrevistado.Telefone + "' "); queryTabelaEntrevistado.Append(@" , '" + dadosEntrevistado.DDDCelular + "' "); queryTabelaEntrevistado.Append(@" , '" + dadosEntrevistado.Celular + "' "); queryTabelaEntrevistado.Append(@" , '" + dadosEntrevistado.Sexo + "' "); queryTabelaEntrevistado.Append(@" , " + informacao + " "); queryTabelaEntrevistado.Append(@" , " + dadosEntrevistado.IDProfissaoConjuge + " "); queryTabelaEntrevistado.Append(@" , " + dadosEntrevistado.FaixaEtariaConjuge + " "); queryTabelaEntrevistado.Append(@" , '" + dadosEntrevistado.CapitalLimitadoConjuge + "' "); queryTabelaEntrevistado.Append(@" , " + dataNascimentoConjuge + " ) "); using (SqlCeConnection conn = new SqlCeConnection(ConnectionString)) { conn.Open(); SqlCeCommand command = new SqlCeCommand(queryTabelaEntrevistado.ToString(), conn); command.ExecuteNonQuery(); } return(true); } catch (Exception ex) { Util.LogErro.GravaLog("Incluir registro TEntrevistado", ex.Message); return(false); } }
/// <summary> /// Execute the SQL statements in the specified stream. /// </summary> /// <param name="stream">A stream containing a series of SQL statements separated by the word GO.</param> /// <param name="connectionString">The connection string to use. This parameter can be set to null or <see cref="String.Empty" /> /// when calling this function from an initialized data provider, since in that case it will use the connection string passed to the /// data provider.</param> /// <remarks>This function is copied from the install wizard in the web project.</remarks> private static void ExecuteSqlInStream(Stream stream, string connectionString) { StreamReader sr = null; StringBuilder sb = new StringBuilder(); SqlCeTransaction tran = null; try { sr = new StreamReader(stream); using (SqlCeConnection cn = GetDbConnectionForGallery(connectionString)) { cn.Open(); tran = cn.BeginTransaction(); while (!sr.EndOfStream) { if (sb.Length > 0) { sb.Remove(0, sb.Length); // Clear out string builder } using (SqlCeCommand cmd = cn.CreateCommand()) { while (!sr.EndOfStream) { string s = sr.ReadLine(); if (s != null && s.Trim().Equals("GO", StringComparison.OrdinalIgnoreCase)) { break; } sb.AppendLine(s); } // Execute T-SQL against the target database cmd.CommandText = sb.ToString(); cmd.ExecuteNonQuery(); } } tran.Commit(); } } catch (Exception ex) { if (tran != null) { try { tran.Rollback(); } catch (Exception) { } } if (!ex.Data.Contains(ExceptionSqlId)) { ex.Data.Add(ExceptionSqlId, sb.ToString()); } throw; } finally { if (sr != null) { sr.Close(); } } }
protected static string GetComparisonStatement(StandardProviderParameters std, esDynamicQuerySerializable query, List <esComparison> items, string prefix) { string sql = String.Empty; string comma = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; //======================================= // WHERE //======================================= if (items != null) { sql += prefix; string compareTo = String.Empty; foreach (esComparison comparisonItem in items) { esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem; esDynamicQuerySerializable subQuery = null; bool requiresParam = true; bool needsStringParameter = false; std.needsIntegerParameter = false; if (comparisonData.IsParenthesis) { if (comparisonData.Parenthesis == esParenthesis.Open) { sql += "("; } else { sql += ")"; } continue; } if (comparisonData.IsConjunction) { switch (comparisonData.Conjunction) { case esConjunction.And: sql += " AND "; break; case esConjunction.Or: sql += " OR "; break; case esConjunction.AndNot: sql += " AND NOT "; break; case esConjunction.OrNot: sql += " OR NOT "; break; } continue; } Dictionary <string, SqlCeParameter> types = null; if (comparisonData.Column.Query != null) { IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)iLocalQuery.Columns); } if (comparisonData.IsLiteral) { if (comparisonData.Column.Name[0] == '<') { sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2); } else { sql += comparisonData.Column.Name; } continue; } if (comparisonData.ComparisonColumn.Name == null) { subQuery = comparisonData.Value as esDynamicQuerySerializable; if (subQuery == null) { if (comparisonData.Column.Name != null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString(); } else { compareTo = Delimiters.Param + "Expr" + (++std.pindex).ToString(); } } else { // It's a sub query compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ") "; requiresParam = false; } } else { compareTo = GetColumnName(comparisonData.ComparisonColumn); requiresParam = false; } switch (comparisonData.Operand) { case esComparisonOperand.Exists: sql += " EXISTS" + compareTo; break; case esComparisonOperand.NotExists: sql += " NOT EXISTS" + compareTo; break; //----------------------------------------------------------- // Comparison operators, left side vs right side //----------------------------------------------------------- case esComparisonOperand.Equal: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo; } else { sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.NotEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo; } else { sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.GreaterThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo; } else { sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.LessThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo; } else { sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.LessThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo; } else { sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.GreaterThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo; } else { sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.Like: string esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case esComparisonOperand.NotLike: esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case esComparisonOperand.IsNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL"; requiresParam = false; break; case esComparisonOperand.IsNotNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL"; requiresParam = false; break; case esComparisonOperand.In: case esComparisonOperand.NotIn: { if (subQuery != null) { // They used a subquery for In or Not sql += ApplyWhereSubOperations(std, query, comparisonData); sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN"; sql += compareTo; } else { comma = String.Empty; if (comparisonData.Operand == esComparisonOperand.In) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " IN ("; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT IN ("; } foreach (object oin in comparisonData.Values) { string str = oin as string; if (str != null) { // STRING sql += comma + "'" + str + "'"; comma = ","; } else if (null != oin as System.Collections.IEnumerable) { // LIST OR COLLECTION OF SOME SORT System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable; if (enumer != null) { System.Collections.IEnumerator iter = enumer.GetEnumerator(); while (iter.MoveNext()) { object o = iter.Current; string soin = o as string; if (soin != null) { sql += comma + "'" + soin + "'"; } else { sql += comma + Convert.ToString(o); } comma = ","; } } } else { // NON STRING OR LIST sql += comma + Convert.ToString(oin); comma = ","; } } sql += ") "; requiresParam = false; } } break; case esComparisonOperand.Between: SqlCeCommand sqlCommand = std.cmd as SqlCeCommand; sql += ApplyWhereSubOperations(std, query, comparisonData) + " BETWEEN "; sql += compareTo; if (comparisonData.ComparisonColumn.Name == null) { sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenBegin); } if (comparisonData.ComparisonColumn2.Name == null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString(); sql += " AND " + compareTo; sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd); } else { sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose; } requiresParam = false; break; } if (requiresParam) { SqlCeParameter p; if (comparisonData.Column.Name != null) { p = types[comparisonData.Column.Name]; p = Cache.CloneParameter(p); p.ParameterName = compareTo; p.Value = comparisonData.Value; if (needsStringParameter) { p.DbType = DbType.String; } else if (std.needsIntegerParameter) { p.DbType = DbType.Int32; } } else { p = new SqlCeParameter(compareTo, comparisonData.Value); } std.cmd.Parameters.Add(p); } } } return(sql); }
public static void TransferToServer() { int locationCheckProductID = 0; DataTable locationTable = new DataTable(); DataTable productTable = new DataTable(); List <ProductLocation> productOfLocation = new List <ProductLocation>(); using (SqlCeConnection con = new SqlCeConnection(SqlHelper.SqlCeConnectionString)) { con.Open(); using (SqlCeCommand cmd = new SqlCeCommand()) { cmd.Connection = con; cmd.Parameters.Clear(); cmd.CommandText = SqlHelper.GetSql(16); cmd.Parameters.AddWithValue("@CreatedBy", GlobalContext.UserCode); cmd.ExecuteNonQuery(); using (SqlCeDataAdapter adab = new SqlCeDataAdapter(cmd)) { adab.Fill(locationTable); } foreach (DataRow item in locationTable.Rows) { locationCheckProductID = (int)item["LocationCheckProductID"]; cmd.Parameters.Clear(); cmd.CommandText = SqlHelper.GetSql(17); cmd.Parameters.AddWithValue("@ID", locationCheckProductID); using (SqlCeDataAdapter adab = new SqlCeDataAdapter(cmd)) { adab.Fill(productTable); } foreach (DataRow row in productTable.Rows) { productOfLocation.Add(new ProductLocation { ProductBarcode = (string)row["ProductBarcode"], ShopPrice = (decimal)row["ShopPrice"], ShopPriceSpecified = true, DisplayOrder = (int)row["DisplayOrder"], DisplayOrderSpecified = true, CreateDateSpecified = true, OfficerID = (string)item["EmployeeCode"], WarehouseCode = (string)item["WarehouseCode"], UserID = (string)item["CreatedBy"], LocationCode = (string)item["LocationCode"], }); } //Transfer to SAP ServiceHelper.MobileServices.ProductLocationAddHandHeldLocation(productOfLocation.ToArray(), (string)item["BranchCode"]); productOfLocation.Clear(); //Delete if Transfer to SAP success. Delete(locationCheckProductID); } } } }
protected void boto_Click(object sender, EventArgs e) { var id = 0; if (Request.QueryString["id"] != null) { id = int.Parse(Request.QueryString["id"]); } //Validem com si fos requiredfield a cada camp que volem. Amb AjaxControlToolkit ja controlem el format de cada camp. var formFull = true; String errorInfoBoxText = ""; if (nomForm.Text == "") { nomForm.CssClass = "failMark"; errorInfoBoxText += "Falten dades al camp obligatori: NOM.</br>"; formFull = false; } else { nomForm.CssClass = "fieldsVal"; } if (cognomForm.Text == "") { cognomForm.CssClass = "failMark"; errorInfoBoxText += "Falten dades al camp obligatori: COGNOM.</br>"; formFull = false; } else { cognomForm.CssClass = "fieldsVal"; } if (telForm.Text == "") { telForm.CssClass = "failMark"; errorInfoBoxText += "Falten dades al camp obligatori: TELEFON.</br>"; formFull = false; } else { telForm.CssClass = "fieldsVal"; } DateTime Data = new DateTime(int.Parse(anys.SelectedValue), (mesos.SelectedIndex + 1), int.Parse(dies.SelectedValue), int.Parse(hores.SelectedValue), int.Parse(minuts.SelectedValue), 0); DateTime Datanow = DateTime.Now; var diftime = Data.Subtract(Datanow).TotalMilliseconds; int timeToDif = 0; if (id != 0) { timeToDif = 60 * 60 * 1000; // DEIXEM MODIFICAR FINS UNA HORA ABANS. } else { timeToDif = 24 * 60 * 60 * 1000; // AMB NOVA RESERVA NOMÉS PERMETEM FINS 1 DIA ABANS. } if (diftime < timeToDif) { if (id != 0) { errorInfoBoxText += "DATA incorrecte. No està permès modificar la reserva per abans de 1 hora. Procediu a eliminar.</br>"; } else { errorInfoBoxText += "DATA incorrecte. La hora de la reserva ha de ser posterior a 24h.</br>"; } formFull = false; dies.CssClass = "failMark"; mesos.CssClass = "failMark"; anys.CssClass = "failMark"; hores.CssClass = "failMark"; minuts.CssClass = "failMark"; } else { dies.CssClass = "timeClass"; mesos.CssClass = "timeClass"; anys.CssClass = "timeClass"; hores.CssClass = "timeClass"; minuts.CssClass = "timeClass"; } if (comensalForm.Text == "") { comensalForm.CssClass = "failMark"; errorInfoBoxText += "Falten dades al camp obligatori: COMENSALS.</br>"; formFull = false; } else { if (int.Parse(comensalForm.Text) > 10 || int.Parse(comensalForm.Text) < 1) { comensalForm.CssClass = "failMark"; errorInfoBoxText += "Només s' accepten reserves de 1 a 10 comensals.</br>"; formFull = false; } else { comensalForm.CssClass = "fieldsVal"; } } infoBox.Controls.Add(new LiteralControl(errorInfoBoxText)); if (formFull == true) { SqlCeConnection linksql = new SqlCeConnection(@"Data Source='C:\Users\Uoc\Documents\GitHub\Uoc_ASPNET\restaurantuoc.sdf';Password='******'"); linksql.Open(); SqlCeCommand sqlQuery = new SqlCeCommand(); if (id != 0) { sqlQuery.CommandText = "UPDATE reserves SET Nom=@elNom,Cognoms=@elCognom,Telefon=@elTelf,Data=@elData,Comensals=@elComensal,Comentaris=@elComentari WHERE Id=" + id; } else { sqlQuery.CommandText = "INSERT INTO reserves (Nom,Cognoms,Telefon,Data,Comensals,Comentaris)" + "VALUES (@elNom,@elCognom,@elTelf,@elData,@elComensal,@elComentari)"; } sqlQuery.Connection = linksql; sqlQuery.Parameters.AddWithValue("@elNom", nomForm.Text); sqlQuery.Parameters.AddWithValue("@elCognom", cognomForm.Text); sqlQuery.Parameters.AddWithValue("@elTelf", telForm.Text); sqlQuery.Parameters.AddWithValue("@elData", Data.ToString()); sqlQuery.Parameters.AddWithValue("@elComensal", int.Parse(comensalForm.Text)); sqlQuery.Parameters.AddWithValue("@elComentari", comentForm.Text); sqlQuery.ExecuteNonQuery(); linksql.Close(); if (id != 0) { Response.Redirect("LlistaReserves.aspx"); // DESPRÉS DE MODIFICAR LA RESERRVA TORNEM AL LLISTAT PRINCIPAL. } else { Response.Redirect("NovaReserva.aspx"); // DESPRÉS DE INSERTAR LA NOVA RESERVA PERMETEM INSERTAR NOVES RESERVES, INICIALITZANT EL FORMULARI. } } }
/// <summary> /// 根据期数获得最新几期红球和篮球 /// </summary> /// <param name="num">多少期数</param> /// <returns></returns> public Hashtable findBallListOfNum(int num) { con.Open(); Hashtable table = new Hashtable(); Queue redQueue = new Queue(); Queue blueQueue = new Queue(); List <int> redList = new List <int>(); List <int> blueList = new List <int>(); List <int> redFullList = new List <int>(); List <int> blueFullList = new List <int>(); SqlCeCommand cmd = con.CreateCommand(); try { cmd.CommandText = "SELECT TOP(@Num) red_num1,red_num2,red_num3,red_num4,red_num5,red_num6,blue_num FROM ticket ORDER BY opentime DESC"; cmd.Parameters.Add("@Num", SqlDbType.SmallInt); cmd.Parameters["@Num"].Value = num; rdr = cmd.ExecuteReader(); while (rdr.Read()) { //添加去重后的球 if (!redQueue.Contains(rdr.GetInt16(0))) { redQueue.Enqueue(rdr.GetInt16(0)); redList.Add(rdr.GetInt16(0)); } if (!redQueue.Contains(rdr.GetInt16(1))) { redQueue.Enqueue(rdr.GetInt16(1)); redList.Add(rdr.GetInt16(1)); } if (!redQueue.Contains(rdr.GetInt16(2))) { redQueue.Enqueue(rdr.GetInt16(2)); redList.Add(rdr.GetInt16(2)); } if (!redQueue.Contains(rdr.GetInt16(3))) { redQueue.Enqueue(rdr.GetInt16(3)); redList.Add(rdr.GetInt16(3)); } if (!redQueue.Contains(rdr.GetInt16(4))) { redQueue.Enqueue(rdr.GetInt16(4)); redList.Add(rdr.GetInt16(4)); } if (!redQueue.Contains(rdr.GetInt16(5))) { redQueue.Enqueue(rdr.GetInt16(5)); redList.Add(rdr.GetInt16(5)); } if (!blueQueue.Contains(rdr.GetInt16(6))) { blueQueue.Enqueue(rdr.GetInt16(6)); blueList.Add(rdr.GetInt16(6)); } //添加所有的球 redFullList.Add(rdr.GetInt16(0)); redFullList.Add(rdr.GetInt16(1)); redFullList.Add(rdr.GetInt16(2)); redFullList.Add(rdr.GetInt16(3)); redFullList.Add(rdr.GetInt16(4)); redFullList.Add(rdr.GetInt16(5)); blueFullList.Add(rdr.GetInt16(6)); } table.Add("redList", redList); table.Add("blueList", blueList); table.Add("redFullList", redFullList); table.Add("blueFullList", blueFullList); } finally { cmd.Dispose(); con.Close(); } return(table); }
private bool UpdateRules() { this.PerformQuery("ПолучитьОбновленияПоОцениваниюКачества"); if (ResultParameters == null) { return(false); } DataTable dt; using (SqlCeConnection dBConnection = new SqlCeConnection(DBEngine.LocalConnectionString)) { dBConnection.Open(); #region Загрузка справочника Номенклатура dt = ResultParameters[0] as DataTable; using (SqlCeCommand SQLCommand = dBConnection.CreateCommand()) { // Удаление старых записей номенклатуры SQLCommand.CommandText = "DELETE FROM Nomenclature"; SQLCommand.ExecuteNonQuery(); SQLCommand.CommandText = "INSERT INTO Nomenclature (id, descr) VALUES (@Id, @Descr)"; var idParam = SQLCommand.Parameters.Add("@Id", SqlDbType.BigInt); var descrParam = SQLCommand.Parameters.Add("@Descr", SqlDbType.NChar, 50); foreach (DataRow dr in dt.Rows) { idParam.Value = System.Convert.ToInt64(dr["Id"]); descrParam.Value = dr["Descr"]; SQLCommand.ExecuteNonQuery(); } } #endregion #region Загрузка справочника Критерии оценивания dt = ResultParameters[1] as DataTable; using (SqlCeCommand SQLCommand = dBConnection.CreateCommand()) { // Удаление старых записей критериев оценивания SQLCommand.CommandText = "DELETE FROM Criterions"; SQLCommand.ExecuteNonQuery(); SQLCommand.CommandText = "INSERT INTO Criterions (id, descr) VALUES (@Id, @Descr)"; var idParam = SQLCommand.Parameters.Add("@Id", SqlDbType.BigInt); var descrParam = SQLCommand.Parameters.Add("@Descr", SqlDbType.NChar, 50); foreach (DataRow dr in dt.Rows) { idParam.Value = System.Convert.ToInt64(dr["Id"]); descrParam.Value = dr["Descr"]; SQLCommand.ExecuteNonQuery(); } } #endregion #region Загрузка правил проверки dt = ResultParameters[2] as DataTable; using (SqlCeCommand SQLCommand = dBConnection.CreateCommand()) { // Удаление старых записей SQLCommand.CommandText = "DELETE FROM NomenclatureCriterions"; SQLCommand.ExecuteNonQuery(); SQLCommand.CommandText = "INSERT INTO NomenclatureCriterions (Number, NomenclatureId, CriterionsId) VALUES (@Number, @NomenclatureId, @CriterionsId)"; var numberParam = SQLCommand.Parameters.Add("@Number", SqlDbType.Int); var nomenclatureIdParam = SQLCommand.Parameters.Add("@NomenclatureId", SqlDbType.BigInt); var criterionsIdParam = SQLCommand.Parameters.Add("@CriterionsId", SqlDbType.BigInt); foreach (DataRow dr in dt.Rows) { numberParam.Value = System.Convert.ToInt32(dr["Number"]); nomenclatureIdParam.Value = System.Convert.ToInt64(dr["WorkTypeId"]); criterionsIdParam.Value = System.Convert.ToInt64(dr["CriterionId"]); SQLCommand.ExecuteNonQuery(); } } #endregion } return(true); }
/// <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(); }