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