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();
}
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 void CreateTable()
{
using (SqlCeCommand cmd = new SqlCeCommand("CREATE TABLE CivInfo (name nvarchar(50) NOT NULL, value ntext NOT NULL)", DataBaseFactory.Instance.GetConnection()))
{
// Création de la table
cmd.ExecuteNonQuery();
// Création de l'index
cmd.CommandText = "CREATE INDEX idxInfoName ON CivInfo (name);";
cmd.ExecuteNonQuery();
}
}
public void clearTables()
{
SqlCeCommand deleteCommand = new SqlCeCommand("DELETE FROM Patient", this._conn);
deleteCommand.ExecuteNonQuery();
deleteCommand.CommandText = "DELETE FROM Visit";
deleteCommand.ExecuteNonQuery();
SqlCeCommand alterTableCommand = new SqlCeCommand("ALTER TABLE Patient alter column id IDENTITY(1,1)", this._conn);
alterTableCommand.ExecuteNonQuery();
alterTableCommand.CommandText = "ALTER TABLE Visit alter column id IDENTITY(1,1)";
alterTableCommand.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 void Deleteentry(string id)
{
if(_connection.State == ConnectionState.Closed)
_connection.Open();
var cmd = new SqlCeCommand(string.Format("DELETE FROM Contacts WHERE Id='{0}'", id), _connection);
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 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();
}
}
/// <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);
}
}
}
}
}
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();
}
}
}
}
public void Actualizar(Entidades.Cliente cli, string dniActual)
{
//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 = "UPDATE [Clientes] SET [dnicuil]=@DNINuevo,[nombre] = @NOMBRE, [apellido] = @APELLIDO, [email] = @EMAIL, [tel1]=@TEL1, [tel2]=@TEL2 WHERE (([dnicuil] = @DNICUILACTUAL))";
Comando.Parameters.Add(new SqlCeParameter("@DNICUILACTUAL", SqlDbType.NVarChar));
Comando.Parameters["@DNICUILACTUAL"].Value = dniActual;
Comando.Parameters.Add(new SqlCeParameter("@NOMBRE", SqlDbType.NVarChar));
Comando.Parameters["@NOMBRE"].Value = cli.Nombre;
Comando.Parameters.Add(new SqlCeParameter("@APELLIDO", SqlDbType.NVarChar));
Comando.Parameters["@APELLIDO"].Value = cli.Apellido;
Comando.Parameters.Add(new SqlCeParameter("@EMAIL", SqlDbType.NVarChar));
Comando.Parameters["@EMAIL"].Value = cli.Email;
Comando.Parameters.Add(new SqlCeParameter("@TEL1", SqlDbType.NVarChar));
Comando.Parameters["@TEL1"].Value = cli.Tel1;
Comando.Parameters.Add(new SqlCeParameter("@TEL2", SqlDbType.NVarChar));
Comando.Parameters["@TEL2"].Value = cli.Tel2;
Comando.Parameters.Add(new SqlCeParameter("@DNINuevo", SqlDbType.NVarChar));
Comando.Parameters["@DNINuevo"].Value = cli.DniCuil;
//Ejecuta el comando INSERT
Comando.Connection.Open();
Comando.ExecuteNonQuery();
Comando.Connection.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 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;
}
//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();
}
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 void DeleteAllFileNames(SqlCeTransaction transaction, SqlCeConnection conn)
{
SqlCeCommand command = new SqlCeCommand(Folder_Names_SQL.commandDeleteAllFolderNames, conn);
command.Transaction = transaction;
command.Connection = conn;
command.ExecuteNonQuery();
}
static void DropData(SqlCeConnection connection)
{
Console.Write("Dropping");
Console.CursorLeft = 0;
SqlCeCommand command = new SqlCeCommand("delete from entity", connection);
command.ExecuteNonQuery();
}
public void Clear()
{
conn.Open();
var cmd = new SqlCeCommand("DELETE FROM Words", conn);
cmd.ExecuteNonQuery();
conn.Close();
}
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 void Tt()
{
string connectionString = @"DataSource=db.sdf";
var conn = new SqlCeConnection(connectionString);
if(!File.Exists(conn.Database))
{
new SqlCeEngine(connectionString).CreateDatabase();
}
conn.Open();
//Creating a table
var cmdCreate = new SqlCeCommand("CREATE TABLE Products (Id int IDENTITY(1,1), Title nchar(50), PRIMARY KEY(Id))", conn);
cmdCreate.ExecuteNonQuery();
//Inserting some data...
var cmdInsert = new SqlCeCommand("INSERT INTO Products (Title) VALUES ('Some Product #1')", conn);
cmdInsert.ExecuteNonQuery();
//Making sure that our data was inserted by selecting it
var cmdSelect = new SqlCeCommand("SELECT Id, Title FROM Products", conn);
SqlCeDataReader reader = cmdSelect.ExecuteReader();
reader.Read();
Console.WriteLine("Id: {0} Title: {1}", reader["Id"], reader["Title"]);
reader.Close();
conn.Close();
}
public bool AddNewAdmin(string UserName, string Password)
{
string commandText = "INSERT INTO AdminInfo(AID, Name, UserName, Password, NICNumber)" +
"VALUES(@aID, @name, @userName, @password, @nicNumber)";
using (SqlCeConnection connection = new SqlCeConnection(connectionSting))
{
using (SqlCeCommand command = new SqlCeCommand(commandText, connection))
{
try
{
connection.Open();
command.Parameters.AddWithValue("@aID", Guid.NewGuid());
command.Parameters.AddWithValue("@name", "");
command.Parameters.AddWithValue("@userName", UserName);
command.Parameters.AddWithValue("@password", Password);
command.Parameters.AddWithValue("@nicNumber", "");
command.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
return false;
}
}
}
return true;
}
public bool Add(string nameEmail, string addressEmail, string typeEmail, bool quickEmail)
{
nameEmail = nameEmail.Trim();
if (CheckDuplicate(addressEmail))
return false; // Denne adresse finnes allerede
try
{
con.Open();
using (SqlCeCommand cmd = new SqlCeCommand("insert into tblEmail(Name, Address, Type, Quick) values (@Val1, @val2, @val3, @val4)", con))
{
cmd.Parameters.AddWithValue("@Val1", nameEmail);
cmd.Parameters.AddWithValue("@Val2", addressEmail);
cmd.Parameters.AddWithValue("@Val3", typeEmail);
cmd.Parameters.AddWithValue("@Val4", quickEmail);
cmd.CommandType = System.Data.CommandType.Text;
cmd.ExecuteNonQuery();
}
con.Close();
return true;
}
catch(Exception ex)
{
Log.Unhandled(ex);
return false;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
//Hash lozinke i salt
string hashLozinka = Util.hashHash(tb_lozinka.Text);
//Sol generiraj
string salt = (new Random(DateTime.Now.Millisecond))
.Next()
.ToString();
//hashiraj sve skupa
string konacniHash = Util.hashHash(hashLozinka + salt);
//sPOJI SE NA BAZU I spremi korisnika
string connString = WebConfigurationManager.ConnectionStrings["UsersConnectionString"].ConnectionString;
SqlCeConnection connection = new SqlCeConnection(connString);
//kreiraj insert komandu
SqlCeCommand command = new SqlCeCommand();
command.Connection = connection;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "INSERT INTO korisnik VALUES(@ime, @lozinka, @salt)";
//Pročitaj lozinku i ime
command.Parameters.AddWithValue("ime", tb_kime.Text);
// No No command.Parameters.AddWithValue("lozinka", tb_lozinka.Text);
command.Parameters.AddWithValue("lozinka", konacniHash);
command.Parameters.AddWithValue("salt", salt);
connection.Open();
int brojRedova = command.ExecuteNonQuery();
connection.Close();
if (brojRedova == 1)
{
//Logiraj korisnika
Session["korisnik"] = tb_kime.Text;
Response.Redirect("LogiraniKorisnici.aspx");
} //else labela greška, itd...
}
public void AddAddress(string city, string district, string street, int building, int? apartment)
{
try
{
using (SqlCeConnection connection = new SqlCeConnection(_connString))
{
connection.Open();
using (SqlCeCommand command = new SqlCeCommand())
{
string apt = "null";
if (apartment.HasValue)
{
apt = apartment.ToString();
}
string sql = string.Format("INSERT INTO Addresses (City, District, Street, Building, Apartment) " +
"VALUES ('{0}','{1}','{2}',{3},{4})",
city, district, street, building, apt);
command.CommandText = sql;
command.Connection = connection;
command.ExecuteNonQuery();
}
}
}
catch
{
throw;
}
}
private void button1_Click(object sender, EventArgs e)
{
if (checkBox1.Checked)
{
label5.Text = ("1");
}
if (checkBox2.Checked)
{
label5.Text = ("0");
}
var connString = (@"Data Source=" + System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)) + @"\Grupe.sdf");
using (var conn = new SqlCeConnection(connString))
{
try
{
conn.Open();
var query = "INSERT INTO copii(prezenta, Nume, Prenume, Program, Taxa) VALUES('"+ label5.Text +"', '" + textBox2.Text.Trim() + "', '" + textBox3.Text.Trim() + "', '" + textBox4.Text.Trim() + "', '" + textBox5.Text.Trim() + "')";
MessageBox.Show(query);
var command = new SqlCeCommand(query, conn);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
///<summary>
///INSERT INTO Scanari (SefID, CodMuncitor, HalaID, CelulaID, AmbalajID,DataScanare, Cantitate ,
///Greutate ,Sincronizat) VALUES ( 1, 12345678,@VarHalaID,@VarCelulaID,1, @VarDateTime ,1 ,1.00 , 0)
///</summary>
///<returns/>
public static void Insert(string sefID, string codMuncitorScanat, string halaID, string celulaID, int ambalajID, string greutate)
{
try
{
if (codMuncitorScanat.Length > 20) throw new ArgumentNullException("codMuncitorScanat");
if (String.IsNullOrEmpty(codMuncitorScanat)) throw new ArgumentNullException("codMuncitorScanat");
if (String.IsNullOrEmpty(sefID)) throw new ArgumentNullException("sefID");
if (String.IsNullOrEmpty(halaID)) throw new ArgumentNullException("halaID");
if (String.IsNullOrEmpty(celulaID)) throw new ArgumentNullException("celulaID");
if (String.IsNullOrEmpty(greutate)) throw new ArgumentNullException("greutate");
using (SqlCeCommand com = new SqlCeCommand("INSERT INTO Scanari (SefID, CodMuncitor, HalaID, CelulaID, AmbalajID,DataScanare, Cantitate , Greutate ,Sincronizat) VALUES ( @VarSefID, @VarCodMuncitor,@VarHalaID,@VarCelulaID,@VarAmbalajID, @VarDateTime ,1 ,@VarGreutate , 0)", Database.Get_Database_Connection()))
{ //"10#12345678#1#1#1#"+timeStamp+"#1#1.00";
com.Parameters.AddWithValue("@VarSefID", sefID);
com.Parameters.AddWithValue("@VarCodMuncitor", codMuncitorScanat);
com.Parameters.AddWithValue("@VarHalaID", halaID);
com.Parameters.AddWithValue("@VarCelulaID", celulaID);
com.Parameters.AddWithValue("@VarAmbalajID", ambalajID);
string timeStamp = String.Format("{0:s}", DateTime.Now); // "2008-03-09T16:05:07" SortableDateTime
com.Parameters.AddWithValue("@VarDateTime", timeStamp);
com.Parameters.AddWithValue("@VarGreutate", greutate);
com.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show("Format Scanare necorespunzator");
}
}
static void Listen()
{
//[ClientID],[pNumber],[msg],[move],[start board]
while (true)
{
string sql;
string rcvd = Console.ReadLine();
if (rcvd.Split('¬')[2].StartsWith("cht:"))
{
sql = "insert into history(ClientID,name,message,status) values (" + System.Diagnostics.Process.GetCurrentProcess().Id + ",'" + name + "','" + msg.Substring(4) + "','N')";
SqlCeCommand command = new SqlCeCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
}
if (rcvd.Split('¬')[2].StartsWith("lst:"))
{
sql = "SELECT * FROM Session where clientID2 is null";
SqlCeCommand command = new SqlCeCommand(sql, m_dbConnection);
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
SqlCeDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("¬" + reader["message"].ToString() + "¬");
}
}
}
}
public bool Alterar(EMovimentacaoConta movementacaoConta)
{
SqlCeConnection cnn = new SqlCeConnection();
cnn.ConnectionString = Conexao.Caminho;
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = cnn;
#region alteracao do associado
cmd.CommandText = @"UPDATE MovimentacaoConta SET
dataHoraMovimentacao = @dataHoraMovimentacao,
valortotal = @valortotal,
Id_Associado = @Id_Associado,
Id_Movimentacao = @Id_Movimentacao
ListaItens = @ListaItens
WHERE Id_Movimentacao = @Id ";
cmd.Parameters.Add("@Nome", movementacaoConta.DataHoraMovimentacao);
cmd.Parameters.Add("@valorTotal", movementacaoConta.Valortotal);
cmd.Parameters.Add("@IdMovimentacao", movementacaoConta.IdAssociado);
cmd.Parameters.Add("@IdMovimentacao", movementacaoConta.IdMovimentacao);
cmd.Parameters.Add("@Listaitens", movementacaoConta.ListaItens);
//Executa o comando setado - UPDATE
cnn.Open();
cmd.ExecuteNonQuery();
#endregion alteracao do associado
//Fecha a conexão
cnn.Close();
return true;
}
public void AñadirNuevo(Cliente cli)
{
//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 [Clientes] ([dnicuil], [nombre], [apellido], [email], [tel1], [tel2]) VALUES (@DNICUIL, @NOMBRE, @APELLIDO, @EMAIL, @TEL1, @TEL2)";
Comando.Parameters.Add(new SqlCeParameter("@DNICUIL", SqlDbType.NVarChar));
Comando.Parameters["@DNICUIL"].Value = cli.DniCuil;
Comando.Parameters.Add(new SqlCeParameter("@NOMBRE", SqlDbType.NVarChar));
Comando.Parameters["@NOMBRE"].Value = cli.Nombre;
Comando.Parameters.Add(new SqlCeParameter("@APELLIDO", SqlDbType.NVarChar));
Comando.Parameters["@APELLIDO"].Value = cli.Apellido;
Comando.Parameters.Add(new SqlCeParameter("@EMAIL", SqlDbType.NVarChar));
Comando.Parameters["@EMAIL"].Value = cli.Email;
Comando.Parameters.Add(new SqlCeParameter("@TEL1", SqlDbType.NVarChar));
Comando.Parameters["@TEL1"].Value = cli.Tel1;
Comando.Parameters.Add(new SqlCeParameter("@TEL2", SqlDbType.NVarChar));
Comando.Parameters["@TEL2"].Value = cli.Tel2;
//Ejecuta el comando INSERT
Comando.Connection.Open();
Comando.ExecuteNonQuery();
Comando.Connection.Close();
}
protected override void ValidateTable(IDbConnection connection, IEntityInfo entity)
{
// prevent caches reads of entitiy fields
m_lastEntity = null;
// first make sure the table exists
if (!TableExists(entity.EntityAttribute.NameInStore))
{
CreateTable(connection, entity);
return;
}
using (var command = new SqlCeCommand())
{
command.Transaction = CurrentTransaction as SqlCeTransaction;
command.Connection = connection as SqlCeConnection;
foreach (var field in entity.Fields)
{
if (ReservedWords.Contains(field.FieldName, StringComparer.InvariantCultureIgnoreCase))
{
throw new ReservedWordException(field.FieldName);
}
// 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}')",
entity.EntityAttribute.NameInStore, 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.EntityAttribute.NameInStore));
alter.Append(string.Format("ADD [{0}] {1} {2}",
field.FieldName,
GetFieldDataTypeString(entity.EntityName, field),
GetFieldCreationAttributes(entity.EntityAttribute, field)));
using (var altercmd = new SqlCeCommand(alter.ToString(), connection as SqlCeConnection))
{
altercmd.ExecuteNonQuery();
}
}
else
{
// TODO: verify field length, etc.
}
}
}
}
}
public void saveCofigdetailsinsdfdb()//saveing the configuration details in sdf db..
{
//Save Record in Handle sqlce database (sdf)
string connectionString = "Data Source=" +
(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)) +
"\\localdb.sdf;Persist Security info=False";
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
if (radioRegistration.Checked == true)
{
Optype = "Reg";
}
else if (radioUnRegistration.Checked == true)
{
Optype = "UnReg";
}
else if (radioVerification.Checked == true)
{
Optype = "Verify";
}
else if (radioYard.Checked == true)
{
Optype = "Yard";
}
else if (radioInternalOperation.Checked == true)
{
Optype = "Internal Ops";
}
string readerip = txtReaderIP.Text.ToString();
string readerno = txtReaderNo.Text.ToString();
string readername = txtReaderName.Text.ToString();
string macadd = txtMacAdd.Text.ToString();
string serverip = txtServerIP.Text.ToString();
string serverport = txtServerPort.Text.ToString();
string taskcode = txtTaskId.Text.ToString();
string process = txtProcess.Text.ToString();
string conntype = cmbConnectionType.Text.ToString();
if (ReaderId == "")
{
MessageBox.Show("Reader Id not found.");
classLog.writeLog("Error @: Configuration faild lack of Reader Id");
}
else
{
try
{
connection.Open();
string datet = System.DateTime.Now.ToString("yyy-MM-dd HH:mm:ss");
string ndt = "{ts '" + datet + "'}";
SqlCeCommand cmd = new SqlCeCommand("Insert into ReaderConfig (ReaderNo,ReaderName,ReaderIP,ReaderMacAdd,ServerIP,ServerPort,ConfigDateTime,Status,ReaderOperation,TaskCode,TaskConfigID,ProcessCode,Location,ConnectionType,ReaderId)" + " Values (" + "'" + readerno + "'" + "," + "'" + readername + "'" + "," + "'" + readerip + "'" + "," + "'" + macadd + "'" + "," + "'" + serverip + "'" + "," + "'" + serverport + "'" + "," + ndt + ",'A','" + Optype + "','" + taskcode + "','" + TaskConfID + "','" + process + "','" + cmbLocation.Text.ToString() + "','" + conntype + "','" + ReaderId + "')", connection);
cmd.ExecuteNonQuery();
connection.Close();
//saveCofigdetailsinsdfdb();
MessageBox.Show("Configuration Success...");
classLog.writeLog("Message @: Configuration Success");
}
catch
{
MessageBox.Show("Internaldb details notfound.");
MessageBox.Show("Configuration Faild...");
classLog.writeLog("Error @: Configuration faild due to Internal DB problem");
}
}
}
}
private void CreateTable(string tableName)
{
string sql = string.Format("CREATE TABLE [{0}] ", tableName);
// Jetzt alle Spalten der Tabelle ermitteln
DataTable dt = hdbConnection.GetSchema("Columns");
List <Column> columns = new List <Column>();
foreach (DataRow row in dt.Rows)
{
if (row["TABLE_NAME"].ToString() == tableName)
{
foreach (System.Data.DataColumn col in dt.Columns)
{
Debug.WriteLine(string.Format("{0} = {1}", col.ColumnName, row[col]));
}
Debug.WriteLine("============================");
SqlDbType sqlDbType;
string colType = "";
int dataType = (int)row["DATA_TYPE"];
long colFlags = (long)row["COLUMN_FLAGS"];
bool autoIncrement = false;
switch (dataType)
{
case 2:
colType = "int";
sqlDbType = SqlDbType.Int;
break;
case 3:
colType = "int";
sqlDbType = SqlDbType.Int;
if (colFlags == 90 && tableName != "LoanedCD")
{
autoIncrement = true;
}
break;
case 7:
colType = "datetime";
sqlDbType = SqlDbType.DateTime;
break;
case 11:
colType = "bit";
sqlDbType = SqlDbType.Bit;
break;
case 17:
colType = "smallint";
sqlDbType = SqlDbType.SmallInt;
break;
case 130:
if ((long)row["CHARACTER_MAXIMUM_LENGTH"] == 0)
{
colType = "ntext";
sqlDbType = SqlDbType.NText;
}
else
{
colType = string.Format("nvarchar({0})", row["CHARACTER_MAXIMUM_LENGTH"]);
sqlDbType = SqlDbType.NVarChar;
}
break;
default:
colType = "int";
sqlDbType = SqlDbType.Int;
break;
}
Column newColumn = new Column();
newColumn.ColumnName = row["COLUMN_NAME"].ToString();
newColumn.DataType = colType;
newColumn.OrdinalPosition = (long)row["ORDINAL_POSITION"];
newColumn.SqlDbType = sqlDbType;
newColumn.IsNullable = (bool)row["IS_NULLABLE"];
if (!(row["CHARACTER_MAXIMUM_LENGTH"] is DBNull))
{
newColumn.Length = (long)row["CHARACTER_MAXIMUM_LENGTH"];
}
if (autoIncrement)
{
newColumn.IsAutoIncrement = autoIncrement;
}
columns.Add(newColumn);
}
}
List <Index> indexes = new List <Index>();
FindIndexes(indexes, tableName);
columns.Sort(new Comparison <Column>(SortColumns));
string sqlColumns = "";
foreach (Column col in columns)
{
if (!string.IsNullOrEmpty(sqlColumns))
{
sqlColumns += ", ";
}
sqlColumns += string.Format("[{0}] {1}", col.ColumnName, col.DataType);
if (col.IsAutoIncrement)
{
sqlColumns += string.Format(" IDENTITY (1, 1)");
}
if (!col.IsNullable || IsInPrimaryKey(indexes, col.ColumnName))
{
sqlColumns += string.Format(" NOT NULL");
}
}
sql += "(" + sqlColumns + ")";
SqlCeCommand cmd = new SqlCeCommand(sql, sdfConnection);
cmd.ExecuteNonQuery();
// Contraints
foreach (Index index in indexes)
{
String sqlIndex;
if (index.PrimaryKey)
{
sqlIndex = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] PRIMARY KEY", tableName, index.Name);
}
else
{
sqlIndex = string.Format("CREATE {0} INDEX [{1}] ON [{2}]", index.Unique ? "UNIQUE" : "", index.Name, tableName);
}
sqlIndex += " (";
int count = 0;
foreach (IndexElement column in index.Columns)
{
if (count > 0)
{
sqlIndex += ", ";
}
sqlIndex += "[" + column.Column + "]";
count++;
}
sqlIndex += ")";
SqlCeCommand cmdIndex = new SqlCeCommand(sqlIndex, sdfConnection);
cmdIndex.ExecuteNonQuery();
}
//CopyData(tableName, columns);
}
//=============================================
private void btn_gravar_Click(object sender, EventArgs e)
{
//botao aberto na form tanto para add ou para editar
//precisa criar a conexao com sql para add ou editar os dados
SqlCeConnection ligacao = new SqlCeConnection("Data Source =" + vars.base_dados);
//essa conection string ja foi escrita la dentro do parentese
//ligacao.ConnectionString = "Data Source=" + vars.base_dados;
ligacao.Open(); //liga
//grava novo registro ou edita registro existente
//verificações....
//impedir que grave com campos vazios
#region verificacoes
//verificar se os campos estao preenchidos
if (txt_nome.Text == "" || txt_telefone.Text == "")
{
MessageBox.Show("falta preencher algum dos campos.");
return; //para que o codigo volte e continue a ser executado
}
#endregion
//------------------------------------------
//daqui pra baixo é parte de ADD ,seja para editar ou gravar um novo ,a parte de cima carrega apenas no form resultados quando clicaco em edita
//valor de editar aqui é falso ,==-1
//essa parte do codigo e apresentado quando no menu clica no add ,aqui edita ==false
//----------------------------------------
#region NOVO CONTATO
if (!editar) //(id contato =-1) se for diferente de verdadeiro(==falso) ele nao edita ele add..
{
//buscar id contato disponivel
//apos criar a conexao e abrir
SqlCeDataAdapter operarioAD = new SqlCeDataAdapter("SELECT MAX(id_contato) AS maxid FROM contatos", ligacao); //a stringconec.. ja escrita aqui dentro com comando do SQL
DataTable dados = new DataTable(); //uma tabela invisivel para controle para poder preencher //depois de colocar o codigo para selecionar o numero max, objeto criado para conexao
operarioAD.Fill(dados);
//operaioid manda comando para pegar o numero max de id do caminho da tab
//apos operario vai preencher essa tabela invisivel com id
//verificar se o valor devolvido é nulo ,,quer dizer que nao tem registro ainda
if (DBNull.Value.Equals(dados.Rows[0][0])) //linha 0 e coluna 0 linha do id
{ //se for nulo ...id contato == 0 pra poder começar inserir contato ao id 0
id_contato = 0;
//se nessa tabela invisivel nao tiver valor acrescenta valor 0 para add esse contato
//se ja tiver ele acrescenta mais +1 APOS PARA ALOCAR NOVOP CONTATO
}
else
{
//se nao for ele pega numero de linhas e acrescenta +1 para novo contato
id_contato = Convert.ToInt16(dados.Rows[0][0]) + 1;
}
//=========== =============== ============
//gravar o novo contato na base de dados
//usar o comando e conectar ele aos dados
//inserir ao comando.conexao= ligacao
SqlCeCommand comando = new SqlCeCommand();
comando.Connection = ligacao; //usa o objeto ligação para a conexao
//inserir ao comando.parametros com valores que representam cada coluna
//parametros
comando.Parameters.AddWithValue("@id_contato", id_contato); //define um nome para ele,e o valor que ele recebe
comando.Parameters.AddWithValue("@nome", txt_nome.Text);
comando.Parameters.AddWithValue("@telefone", txt_telefone.Text);
comando.Parameters.AddWithValue("@atualizacao", DateTime.Now);
//mas antes de adicionar precisa verificar se ja existe nom e tel
//-============ = =========== = =============== = ================ = =
//REINICIAR ADPT E TABELA e NOVO
//e acrescenta comando de selecao ao comando.comandText
//verificar se o nome e tel já é existente preenchendo o datatable invisvel
operarioAD = new SqlCeDataAdapter();
dados = new DataTable();
comando.CommandText = "SELECT * FROM contatos WHERE nome= @nome AND telefone = @telefone";
//comando carrega inform~ções de (.conection) e (parametro) e (.comandtext) que recebeu
//o adaptador tem varios comandos select ,delete,update..
operarioAD.SelectCommand = comando;
operarioAD.Fill(dados);
//se tiver qualquer quantidade de linha ele vai dizer que existe (commandtext)
if (dados.Rows.Count != 0)
{ //ja exeiste um contato com esses dados
MessageBox.Show("ja existe o registro");
return;
}
//============= =========== ============ =============
//só depois de verificar se ja existe ele pode executar o insert
//entao comando recebe um novo comando de texto para inserir usando (parametros dele)
//texto da query
comando.CommandText = "INSERT INTO contatos VALUES(" +
"@id_contato, @nome, @telefone, @atualizacao)";
comando.ExecuteNonQuery(); //ele nao vai buscar info ,apenas guardar entao nao precisa de retorno
comando.Dispose();
ligacao.Dispose();
MessageBox.Show("contato add com sucesso");
//limpar o campo ja escrito
txt_nome.Text = "";
txt_telefone.Text = "";
txt_nome.Focus();
}
#endregion
//----------------------------------------
#region EDITAR CONTATO
else
{
//gravar o novo contato na base de dados
//usar o comando e conectar ele aos dados
//inserir ao comando.conexao= ligacao
SqlCeCommand comando = new SqlCeCommand();
comando.Connection = ligacao; //usa o objeto ligação para a conexao
//inserir ao comando.parametros com valores que representam cada coluna
//parametros
comando.Parameters.AddWithValue("@id_contato", id_contato); //define um nome para ele,e o valor que ele recebe
comando.Parameters.AddWithValue("@nome", txt_nome.Text);
comando.Parameters.AddWithValue("@telefone", txt_telefone.Text);
comando.Parameters.AddWithValue("@atualizacao", DateTime.Now);
//verifica se ja existe um registro com mesmo nome mas um id diferente
DataTable tableainvisivel = new DataTable();
comando.CommandText = "SELECT * FROM contatos WHERE nome = @nome AND id_contato <> @id_contato";
SqlCeDataAdapter operario = new SqlCeDataAdapter(); //nao precisa passar aqui ,ja que ligacao ja esta associada ao comando ,a isntrução de selecionar tmb
operario.SelectCommand = comando;
operario.Fill(tableainvisivel);
if (tableainvisivel.Rows.Count != 0)
{
//foi encontrado um registro com mesmo nome
if (MessageBox.Show("existe um contao", "mudar", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
{
return;
}
}
//editar registro caso sim
comando.CommandText = "UPDATE contatos SET nome = @nome ,telefone = @telefone, atualizacao = @atualizacao WHERE id_contato = @id_contato";
comando.ExecuteNonQuery();
//fechar o quadro
this.Close();
}
#endregion
}
private void InitializeDatabase()
{
var connectionString = ConnectionString;
Debug.AssertStringNotEmpty(connectionString);
var dbFilePath = ConnectionStringHelper.GetDataSourceFilePath(connectionString);
if (File.Exists(dbFilePath))
{
return;
}
using (var engine = new SqlCeEngine(ConnectionString))
{
engine.CreateDatabase();
}
using (var conn = new SqlCeConnection(ConnectionString))
{
using (var cmd = new SqlCeCommand())
{
conn.Open();
var transaction = conn.BeginTransaction();
try
{
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.CommandText = @"
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'ELMAH_Error'";
var obj = cmd.ExecuteScalar();
if (obj == null)
{
cmd.CommandText = @"
CREATE TABLE ELMAH_Error (
[ErrorId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT newid(),
[Application] NVARCHAR(60) NOT NULL,
[Host] NVARCHAR(50) NOT NULL,
[Type] NVARCHAR(100) NOT NULL,
[Source] NVARCHAR(60) NOT NULL,
[Message] NVARCHAR(500) NOT NULL,
[User] NVARCHAR(50) NOT NULL,
[StatusCode] INT NOT NULL,
[TimeUtc] DATETIME NOT NULL,
[Sequence] INT IDENTITY (1, 1) NOT NULL,
[AllXml] NTEXT NOT NULL
)";
cmd.ExecuteNonQuery();
cmd.CommandText = @"
CREATE NONCLUSTERED INDEX [IX_Error_App_Time_Seq] ON [ELMAH_Error]
(
[Application] ASC,
[TimeUtc] DESC,
[Sequence] DESC
)";
cmd.ExecuteNonQuery();
}
transaction.Commit(CommitMode.Immediate);
}
catch (SqlCeException)
{
transaction.Rollback();
throw;
}
}
}
}
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 == "User Master")
//{
// sql = " Select UId,Loginid,Password,LoginType,UserName from [dbo].[HHLoginMst] where [UStatus]='Active'";
//}
DataTable dt = getDatafromWebservice(cmbSyncData.Text.ToString());
if (cmbSyncData.Text == "User Master")
{
clearlocaldbUserMaster();
}
//................................................
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 == "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')";
}
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.";
}
}
private void CheckDb()
{
if (!File.Exists("fiverr.sdf"))
{
SqlCeEngine engine = new SqlCeEngine(st);
engine.CreateDatabase();
SqlCeConnection cn = new SqlCeConnection(st);
if (cn.State == System.Data.ConnectionState.Closed)
{
cn.Open();
}
var sql = "create table users (id int primary key identity, username nvarchar(30) );";
var sql5 = "create table wishlist(id int primary key identity, isbn nvarchar(30), name nvarchar(50), price int, quantity int, userid int, foreign key(userid) references users(id)); ";
var sql8 = "create table storages(id int primary key identity, userid int, wishlistid int, foreign key(wishlistid) references wishlist(id), foreign key(userid) references users(id))";
var sql2 = "insert into users (username) values ('Alice');";
var sql3 = "insert into users (username) values ('Martin');";
var sql4 = "insert into users (username) values ('Bob');";
var sql6 = "insert into wishlist(isbn, name, price, quantity, userid) values (123, 'Song of Elves1', 42, 5, 1)";
var sql20 = "insert into wishlist(isbn, name, price, quantity, userid) values (124, 'Song of Elves2', 42, 5, 1)";
var sql18 = "insert into wishlist(isbn, name, price, quantity, userid) values (125, 'Song of Elves3', 42, 5, 1)";
var sql19 = "insert into wishlist(isbn, name, price, quantity, userid) values (126, 'Song of Elves4', 42, 5, 2)";
var sql21 = "insert into wishlist(isbn, name, price, quantity, userid) values (127, 'Song of Elves5', 42, 5, 2)";
var sql7 = "insert into wishlist(isbn, name, price, quantity, userid) values (12323, 'Elvish Pie6', 44, 6, 2)";
var sql22 = "insert into wishlist(isbn, name, price, quantity, userid) values (123823, 'Elvish Pie7', 44, 6, 3)";
var sql23 = "insert into wishlist(isbn, name, price, quantity, userid) values (12393, 'Elvish Pie8', 44, 6, 3)";
var sql24 = "insert into wishlist(isbn, name, price, quantity, userid) values (123203, 'Elvish Pie9', 44, 6, 3)";
var sql9 = "insert into storages(userid, wishlistid) values (1, 1);";
var sql10 = "insert into storages(userid, wishlistid) values (1, 2);";
var sql11 = "insert into storages(userid, wishlistid) values (1, 3);";
var sql12 = "insert into storages(userid, wishlistid) values (2, 4);";
var sql13 = "insert into storages(userid, wishlistid) values (2, 5);";
var sql14 = "insert into storages(userid, wishlistid) values (2, 6);";
var sql15 = "insert into storages(userid, wishlistid) values (3, 7);";
var sql16 = "insert into storages(userid, wishlistid) values (3, 8);";
var sql17 = "insert into storages(userid, wishlistid) values (3, 9);";
var cmd = new SqlCeCommand(sql, cn);
var cmd4 = new SqlCeCommand(sql5, cn);
var cmd7 = new SqlCeCommand(sql8, cn);
var cmd1 = new SqlCeCommand(sql2, cn);
var cmd2 = new SqlCeCommand(sql3, cn);
var cmd3 = new SqlCeCommand(sql4, cn);
var cmd5 = new SqlCeCommand(sql6, cn);
var cmd6 = new SqlCeCommand(sql7, cn);
var cmd8 = new SqlCeCommand(sql9, cn);
var cmd9 = new SqlCeCommand(sql10, cn);
var cmd10 = new SqlCeCommand(sql11, cn);
var cmd11 = new SqlCeCommand(sql12, cn);
var cmd12 = new SqlCeCommand(sql13, cn);
var cmd13 = new SqlCeCommand(sql14, cn);
var cmd14 = new SqlCeCommand(sql15, cn);
var cmd15 = new SqlCeCommand(sql16, cn);
var cmd16 = new SqlCeCommand(sql17, cn);
var cmd17 = new SqlCeCommand(sql18, cn);
var cmd18 = new SqlCeCommand(sql19, cn);
var cmd19 = new SqlCeCommand(sql20, cn);
var cmd20 = new SqlCeCommand(sql21, cn);
var cmd21 = new SqlCeCommand(sql22, cn);
var cmd22 = new SqlCeCommand(sql23, cn);
var cmd23 = new SqlCeCommand(sql24, cn);
try
{
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
cmd3.ExecuteNonQuery();
cmd4.ExecuteNonQuery();
cmd5.ExecuteNonQuery();
cmd6.ExecuteNonQuery();
cmd7.ExecuteNonQuery();
cmd8.ExecuteNonQuery();
cmd20.ExecuteNonQuery();
cmd19.ExecuteNonQuery();
cmd21.ExecuteNonQuery();
cmd9.ExecuteNonQuery();
cmd10.ExecuteNonQuery();
cmd11.ExecuteNonQuery();
cmd18.ExecuteNonQuery();
cmd13.ExecuteNonQuery();
cmd22.ExecuteNonQuery();
cmd23.ExecuteNonQuery();
cmd14.ExecuteNonQuery();
cmd12.ExecuteNonQuery();
cmd15.ExecuteNonQuery();
cmd17.ExecuteNonQuery();
cmd16.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + sql);
}
finally
{
cn.Close();
}
}
}
public override int Insert(string tableName, string[] columns, System.Collections.ArrayList value)
{
//产生数据的时间基本可以忽略
string allColumns = "";
string allDatas = "";
if (columns != null && columns.Length > 0)
{
columns.ToList().ForEach(col => allColumns = string.Format("{0}[{1}],", allColumns, col));
allColumns = allColumns.Substring(0, allColumns.Length - 1);
allColumns = string.Format(" ({0}) ", allColumns);
}
if (value != null && value.Count > 0)
{
for (int i = 0; i < value.Count; i++)
{
if (value[i] == null)
{
All.Class.Error.Add("Access插入空数据");
continue;
}
switch (System.Type.GetTypeCode(value[i].GetType()))
{
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
case TypeCode.Byte:
case TypeCode.Double:
case TypeCode.Decimal:
case TypeCode.Single:
case TypeCode.SByte:
allDatas = string.Format("{0},{1}", allDatas, value[i]);
break;
case TypeCode.DateTime:
allDatas = string.Format("{0},'{1:yyyy-MM-dd HH:mm:ss}'", allDatas, value[i]);
break;
case TypeCode.Boolean:
allDatas = string.Format("{0},{1}", allDatas, value[i].ToString().ToBool() ? 1 : 0);
break;
case TypeCode.String:
allDatas = string.Format("{0},'{1}'", allDatas, value[i]);
break;
}
}
if (allDatas.StartsWith(","))
{
allDatas = allDatas.Substring(1);
}
allDatas = string.Format(" ({0}) ", allDatas);
}
else
{
return(0);
}
string sql = string.Format("insert into {0} {1} values {2}", tableName, allColumns, allDatas);
lock (lockObject)
{
int result = 0;
try
{
if (!CheckConn())
{
return(0);
}
using (SqlCeCommand cmd = new SqlCeCommand(sql, conn))
{
result = cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
All.Class.Error.Add("故障语句", sql);
All.Class.Error.Add(e);
}
return(result);
}
}
private void regbtn_Click(object sender, EventArgs e)
{
if (flag)
{
updateInformation();
}
else
{
if (compnametb.Text == "")
{
MessageBox.Show("Company Name can't be empty");
}
else if (compaddrtb.Text == "")
{
MessageBox.Show("Company Address can't be empty");
}
else if (compmbltb.Text == "")
{
MessageBox.Show("Company Mobile number can't be empty");
}
else if (gstuintb.Text == "")
{
MessageBox.Show("GSTIN/UIN field can't be empty");
}
else if (regkeytb.Text == "")
{
MessageBox.Show("Please, specify a registration key or Contact [email protected]");
}
else if (prefix.Text == "")
{
MessageBox.Show("Please specify a prefix for invoice number");
}
else if (compemail.Text == "")
{
compemail.Text = "0";
}
else if (compvattb.Text == "")
{
compvattb.Text = "0";
}
else if (compcsttb.Text == "")
{
compcsttb.Text = "0";
}
else if (comppantb.Text == "")
{
comppantb.Text = "0";
}
else
{
dboperation operation = new dboperation();
if (!(operation.keyvalidation(regkeytb.Text, gstuintb.Text)))
{
MessageBox.Show("Your Key is not Valid");
this.Close();
}
if (Settings.Default.DatabasePath == "" || !File.Exists(Settings.Default.DatabasePath + "\\" + Global.ROOT_DATA_FOLDER + "\\" + Global.DB_NAME_PREFIX + ".sdf"))
{
FilePath setpath = new FilePath();
setpath.ShowDialog();
}
else
{
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
int flag;
byte[] photo_array = null;
MemoryStream ms = new MemoryStream();
if (complogopb.Image == null)
{
complogopb.Image = Resources.defaultlogo;
}
complogopb.Image.Save(ms, ImageFormat.Png);
photo_array = new byte[ms.Length];
ms.Position = 0;
ms.Read(photo_array, 0, photo_array.Length);
string insertquery = "INSERT INTO regdetails(Id,companyName, companyAddr, companyMobile, companyEmail, companygstin, companyVAT, companyCST, companyPAN, regkey, prefix, logo) VALUES(@compid,@cname, @caddr, @mbl, @email, @gstin, @vat, @cst, @pan,@key,@prefix,@img)";
try
{
conn = operation.dbConnection(Settings.Default.DatabasePath);
cmd = new SqlCeCommand(insertquery, conn);
cmd.Parameters.AddWithValue("@compid", "1");
cmd.Parameters.AddWithValue("@cname", compnametb.Text);
cmd.Parameters.AddWithValue("@caddr", compaddrtb.Text);
cmd.Parameters.AddWithValue("@mbl", compmbltb.Text);
cmd.Parameters.AddWithValue("@email", compemail.Text);
cmd.Parameters.AddWithValue("@gstin", gstuintb.Text);
cmd.Parameters.AddWithValue("@vat", compvattb.Text);
cmd.Parameters.AddWithValue("@cst", compcsttb.Text);
cmd.Parameters.AddWithValue("@pan", comppantb.Text);
cmd.Parameters.AddWithValue("@key", regkeytb.Text);
cmd.Parameters.AddWithValue("@prefix", prefix.Text);
cmd.Parameters.AddWithValue("@img", photo_array);
flag = cmd.ExecuteNonQuery();
if (flag < 0)
{
MessageBox.Show("Error User to Register");
this.Close();
}
else
{
this.Close();
thread = new Thread(openlogin);
thread.SetApartmentState(ApartmentState.STA);
thread.Start();
}
}
catch (Exception ex)
{
MessageBox.Show("An error occured to creating database" + ex);
}
}
}
}
}
private void updateInformation()
{
if (compnametb.Text == "")
{
MessageBox.Show("Company Name can't be empty");
}
else if (compaddrtb.Text == "")
{
MessageBox.Show("Company Address can't be empty");
}
else if (compmbltb.Text == "")
{
MessageBox.Show("Company Mobile number can't be empty");
}
else if (compemail.Text == "")
{
compemail.Text = "0";
}
else if (compvattb.Text == "")
{
compvattb.Text = "0";
}
else if (compcsttb.Text == "")
{
compcsttb.Text = "0";
}
else if (comppantb.Text == "")
{
comppantb.Text = "0";
}
else
{
dboperation operation = new dboperation();
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
int flag;
byte[] photo_array = null;
MemoryStream ms = new MemoryStream();
if (complogopb.Image != null)
{
complogopb.Image.Save(ms, ImageFormat.Png);
photo_array = new byte[ms.Length];
ms.Position = 0;
ms.Read(photo_array, 0, photo_array.Length);
}
string updatequery = "UPDATE regdetails SET companyName = @cname, companyAddr = @caddr, companyMobile = @mbl, companyEmail = @email, companyVAT = @vat, companyCST = @cst, companyPAN = @pan, logo = @img WHERE Id = @compid";
try
{
conn = operation.dbConnection(Settings.Default.DatabasePath);
cmd = new SqlCeCommand(updatequery, conn);
cmd.Parameters.AddWithValue("@compid", "1");
cmd.Parameters.AddWithValue("@cname", compnametb.Text);
cmd.Parameters.AddWithValue("@caddr", compaddrtb.Text);
cmd.Parameters.AddWithValue("@mbl", compmbltb.Text);
cmd.Parameters.AddWithValue("@email", compemail.Text);
// cmd.Parameters.AddWithValue("@gstin", gstuintb.Text);
cmd.Parameters.AddWithValue("@vat", compvattb.Text);
cmd.Parameters.AddWithValue("@cst", compcsttb.Text);
cmd.Parameters.AddWithValue("@pan", comppantb.Text);
// cmd.Parameters.AddWithValue("@key", regkeytb.Text);
cmd.Parameters.AddWithValue("@img", photo_array);
flag = cmd.ExecuteNonQuery();
if (flag < 0)
{
MessageBox.Show("Error User to Register");
this.Close();
}
else
{
MessageBox.Show("Information updated successfully");
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("An error occured to creating database" + ex);
}
}
}
public static bool ActualizarPaciente(PacienteB pPaciente)
{
#region linq to class
//try
//{
// //RemoteModelDataContext dm = new RemoteModelDataContext();
// Paciente paciente = dm.Pacientes.Where(i => i.iCodigoPaciente == pPaciente.iCodigoPaciente).First();
// paciente.vNombresApoderado = pPaciente.vNombresApoderado;
// paciente.vApellidoApoderado = pPaciente.vApellidoApoderado;
// paciente.vCelularApoderado = pPaciente.vCelularApoderado;
// paciente.vTelefonoApoderado = pPaciente.vTelefonoApoderado;
// paciente.vParentescoApoderado = pPaciente.vParentescoApoderado;
// dm.SubmitChanges();
// return true;
//}
//catch (Exception ex)
//{
// //throw (ex);
// return false;
//}
#endregion
#region SQL compact connection
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
try
{
conn = new SqlCeConnection("Data Source=" + System.IO.Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "HRNPCIData.sdf"));
conn.Open();
cmd = new SqlCeCommand("UPDATE Paciente SET " +
"vParentescoApoderado = @vParentescoApoderado," +
"vNombresApoderado = @vNombresApoderado," +
"vApellidoApoderado = @vApellidoApoderado, " +
"vCelularApoderado = @vCelularApoderado, " +
"vTelefonoApoderado = @vTelefonoApoderado " +
"WHERE iCodigoPaciente = @iCodigoPaciente", conn);
cmd.Parameters.AddWithValue("@iCodigoPaciente", (pPaciente.iCodigoPaciente as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vNombresApoderado", (pPaciente.vNombresApoderado.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vParentescoApoderado", (pPaciente.vParentescoApoderado.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vApellidoApoderado", (pPaciente.vApellidoApoderado.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vCelularApoderado", (pPaciente.vCelularApoderado.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vTelefonoApoderado", (pPaciente.vTelefonoApoderado.Trim() as object) ?? DBNull.Value);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
return(false);
}
finally
{
conn.Close();
}
return(true);
#endregion
}
private void Convert()
{
string sql;
formConvertProgress.MaximumValue = 15;
formConvertProgress.CurrentTable = "Master";
ConvertMasterTable();
#region CD
formConvertProgress.CurrentTable = "CD";
formConvertProgress.Value++;
List <FieldConvert> fieldsCD = new List <FieldConvert>();
fieldsCD.Add(new FieldConvert("IDCD", "CDID"));
fieldsCD.Add(new FieldConvert("dwGesamtlaenge", "Length"));
fieldsCD.Add(new FieldConvert("cAnzahlLieder", "NumberOfTracks"));
fieldsCD.Add(new FieldConvert("bCDSampler", "IsSampler"));
fieldsCD.Add(new FieldConvert("wNummerImSet", "SetNumber"));
fieldsCD.Add(new FieldConvert("IDCDSet", "SetID"));
fieldsCD.Add(new FieldConvert("IDArtist", "ArtistID"));
fieldsCD.Add(new FieldConvert("IDKategorie", "CategoryID"));
fieldsCD.Add(new FieldConvert("IDMedium", "MediumID"));
fieldsCD.Add(new FieldConvert("szTitel", "Title"));
fieldsCD.Add(new FieldConvert("szDatum", "Date"));
fieldsCD.Add(new FieldConvert("szArchivNummer", "ArchiveNumber"));
fieldsCD.Add(new FieldConvert("szPfadBitmap", "FrontCover"));
fieldsCD.Add(new FieldConvert("szKennzeichen", "Codes"));
fieldsCD.Add(new FieldConvert("szKommentar", "Comment"));
fieldsCD.Add(new FieldConvert("szFeld1", "User1"));
fieldsCD.Add(new FieldConvert("szFeld2", "User2"));
fieldsCD.Add(new FieldConvert("szFeld3", "User3"));
fieldsCD.Add(new FieldConvert("szFeld4", "User4"));
fieldsCD.Add(new FieldConvert("szFeld5", "User5"));
fieldsCD.Add(new FieldConvert("C_Type", "Type"));
fieldsCD.Add(new FieldConvert("C_YearRecorded", "YearRecorded"));
fieldsCD.Add(new FieldConvert("C_Copyright", "Copyright"));
fieldsCD.Add(new FieldConvert("C_BackCoverBitmap", "BackCover"));
fieldsCD.Add(new FieldConvert("C_CDLabelBitmap", "CDLabelCover"));
fieldsCD.Add(new FieldConvert("C_Rating", "Rating"));
fieldsCD.Add(new FieldConvert("C_Label", "Label"));
fieldsCD.Add(new FieldConvert("C_URL", "URL"));
fieldsCD.Add(new FieldConvert("C_Price", "Price"));
fieldsCD.Add(new FieldConvert("C_UPC", "UPC"));
fieldsCD.Add(new FieldConvert("C_Original", "IsOriginal"));
fieldsCD.Add(new FieldConvert("C_IDComposer", "ComposerID"));
fieldsCD.Add(new FieldConvert("C_Location", "Location"));
fieldsCD.Add(new FieldConvert("C_Language", "Language"));
CDTableAdapter cdAdap = new CDTableAdapter(sdfDatabase);
ConvertDataSet.CDDataTable cd = new ConvertDataSet.CDDataTable();
cdAdap.Fill(cd);
// Alle Identities einlesen
Dictionary <int, string> identites = ReadIdentities();
CopyData("CD", fieldsCD, cd, identites);
SqlCeCommand cmd = new SqlCeCommand("SET IDENTITY_INSERT CD ON", sdfDatabase.Connection);
cmd.ExecuteNonQuery();
int count = 0;
foreach (DataRow row in cd.Rows)
{
try
{
cdAdap.Update(row);
}
catch (Exception e)
{
FormUnhandledException formUnhandledException = new FormUnhandledException(e);
formUnhandledException.ShowDialog();
}
if ((count % 100) == 0)
{
formConvertProgress.CurrentTable = string.Format("{0} ({1} Einträge)", "Track", count);
formConvertProgress.Refresh();
Application.DoEvents();
}
count++;
}
cmd = new SqlCeCommand("SET IDENTITY_INSERT CD OFF", sdfDatabase.Connection);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("SELECT MAX(CDID) FROM CD", sdfDatabase.Connection);
int maxId = (int)cmd.ExecuteScalar() + 1;
cmd = new SqlCeCommand(string.Format("ALTER TABLE CD ALTER COLUMN CDID IDENTITY({0},1)", maxId), sdfDatabase.Connection);
cmd.ExecuteNonQuery();
#endregion
#region Track
formConvertProgress.CurrentTable = "Track";
formConvertProgress.Value++;
List <FieldConvert> fieldsTrack = new List <FieldConvert>();
fieldsTrack.Add(new FieldConvert("IDLied", "TrackID"));
fieldsTrack.Add(new FieldConvert("IDCD", "CDID"));
fieldsTrack.Add(new FieldConvert("IDArtist", "ArtistID"));
fieldsTrack.Add(new FieldConvert("wLiedNummer", "TrackNumber"));
fieldsTrack.Add(new FieldConvert("szTitel", "Title"));
fieldsTrack.Add(new FieldConvert("dwLaenge", "Length"));
fieldsTrack.Add(new FieldConvert("wBpm", "Bpm"));
fieldsTrack.Add(new FieldConvert("szNameRecDatei", "SoundFile"));
fieldsTrack.Add(new FieldConvert("szKennzeichen", "Codes"));
fieldsTrack.Add(new FieldConvert("szKommentar", "Comment"));
fieldsTrack.Add(new FieldConvert("szLiedtext", "Lyrics"));
fieldsTrack.Add(new FieldConvert("szFeld1", "User1"));
fieldsTrack.Add(new FieldConvert("szFeld2", "User2"));
fieldsTrack.Add(new FieldConvert("szFeld3", "User3"));
fieldsTrack.Add(new FieldConvert("szFeld4", "User4"));
fieldsTrack.Add(new FieldConvert("szFeld5", "User5"));
fieldsTrack.Add(new FieldConvert("L_TrackFormat", "TrackFormat"));
fieldsTrack.Add(new FieldConvert("L_BitRate", "BitRate"));
fieldsTrack.Add(new FieldConvert("L_SampleRate", "SampleRate"));
fieldsTrack.Add(new FieldConvert("L_Channels", "Channels"));
fieldsTrack.Add(new FieldConvert("L_YearRecorded", "YearRecorded"));
fieldsTrack.Add(new FieldConvert("L_Checksum", "Checksum"));
fieldsTrack.Add(new FieldConvert("L_Rating", "Rating"));
fieldsTrack.Add(new FieldConvert("L_IDCategory", "CategoryID"));
fieldsTrack.Add(new FieldConvert("L_IDComposer", "ComposerID"));
fieldsTrack.Add(new FieldConvert("L_Language", "Language"));
TrackTableAdapter trackAdap = new TrackTableAdapter(sdfDatabase);
ConvertDataSet.TrackDataTable track = new ConvertDataSet.TrackDataTable();
trackAdap.Fill(track);
CopyData("Lied", fieldsTrack, track);
sql = string.Format("SET IDENTITY_INSERT Track ON");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
count = 0;
foreach (DataRow row in track.Rows)
{
try
{
trackAdap.Update(row);
}
catch (Exception e)
{
FormUnhandledException formUnhandledException = new FormUnhandledException(e);
formUnhandledException.ShowDialog();
}
if ((count % 100) == 0)
{
formConvertProgress.CurrentTable = string.Format("{0} ({1} Einträge)", "Track", count);
formConvertProgress.Refresh();
Application.DoEvents();
}
count++;
}
sql = string.Format("SET IDENTITY_INSERT Track OFF");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("SELECT MAX(TrackID) FROM Track", sdfDatabase.Connection);
maxId = (int)cmd.ExecuteScalar() + 1;
cmd = new SqlCeCommand(string.Format("ALTER TABLE Track ALTER COLUMN TrackID IDENTITY({0},1)", maxId), sdfDatabase.Connection);
cmd.ExecuteNonQuery();
#endregion
#region Artist
formConvertProgress.CurrentTable = "Artist";
formConvertProgress.Value++;
List <FieldConvert> fieldsPersonGroup = new List <FieldConvert>();
fieldsPersonGroup.Add(new FieldConvert("IDArtist", "PersonGroupID"));
fieldsPersonGroup.Add(new FieldConvert("szArtistName", "Name"));
fieldsPersonGroup.Add(new FieldConvert("sSortKey", "SaveAs"));
fieldsPersonGroup.Add(new FieldConvert("nGroup", "Type"));
fieldsPersonGroup.Add(new FieldConvert("nSex", "Sex"));
fieldsPersonGroup.Add(new FieldConvert("sComment", "Comment"));
fieldsPersonGroup.Add(new FieldConvert("A_URL", "URL"));
fieldsPersonGroup.Add(new FieldConvert("A_Country", "Country"));
fieldsPersonGroup.Add(new FieldConvert("A_BirthDay", "BirthDay", true));
fieldsPersonGroup.Add(new FieldConvert("A_DayOfDeath", "DayOfDeath", true));
fieldsPersonGroup.Add(new FieldConvert("A_ImageFilename", "ImageFilename"));
PersonGroupTableAdapter personGroupAdap = new PersonGroupTableAdapter(sdfDatabase);
ConvertDataSet.PersonGroupDataTable personGroup = new ConvertDataSet.PersonGroupDataTable();
personGroupAdap.Fill(personGroup);
CopyData("Artist", fieldsPersonGroup, personGroup);
sql = string.Format("SET IDENTITY_INSERT PersonGroup ON");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
count = 0;
foreach (DataRow row in personGroup.Rows)
{
try
{
personGroupAdap.Update(row);
}
catch (Exception e)
{
FormUnhandledException formUnhandledException = new FormUnhandledException(e);
formUnhandledException.ShowDialog();
}
if ((count % 100) == 0)
{
formConvertProgress.CurrentTable = string.Format("{0} ({1} Einträge)", "Artist", count);
formConvertProgress.Refresh();
Application.DoEvents();
}
count++;
}
sql = string.Format("SET IDENTITY_INSERT PersonGroup OFF");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("SELECT MAX(PersonGroupID) FROM PersonGroup", sdfDatabase.Connection);
maxId = (int)cmd.ExecuteScalar() + 1;
cmd = new SqlCeCommand(string.Format("ALTER TABLE PersonGroup ALTER COLUMN PersonGroupID IDENTITY({0},1)", maxId), sdfDatabase.Connection);
cmd.ExecuteNonQuery();
#endregion
#region Category
formConvertProgress.CurrentTable = "Category";
formConvertProgress.Value++;
List <FieldConvert> fieldsCategory = new List <FieldConvert>();
fieldsCategory.Add(new FieldConvert("IDKategorie", "CategoryID"));
fieldsCategory.Add(new FieldConvert("szKategorieName", "Name"));
fieldsCategory.Add(new FieldConvert("wOrder", "Order"));
CategoryTableAdapter categoryAdap = new CategoryTableAdapter(sdfDatabase);
ConvertDataSet.CategoryDataTable categories = new ConvertDataSet.CategoryDataTable();
categoryAdap.Fill(categories);
CopyData("Kategorie", fieldsCategory, categories);
sql = string.Format("SET IDENTITY_INSERT Category ON");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
categoryAdap.Update(categories);
sql = string.Format("SET IDENTITY_INSERT Category OFF");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("SELECT MAX(CategoryID) FROM Category", sdfDatabase.Connection);
maxId = (int)cmd.ExecuteScalar() + 1;
cmd = new SqlCeCommand(string.Format("ALTER TABLE Category ALTER COLUMN CategoryID IDENTITY({0},1)", maxId), sdfDatabase.Connection);
cmd.ExecuteNonQuery();
#endregion
#region Medium
formConvertProgress.CurrentTable = "Medium";
formConvertProgress.Value++;
List <FieldConvert> fieldsMedium = new List <FieldConvert>();
fieldsMedium.Add(new FieldConvert("IDMedium", "MediumID"));
fieldsMedium.Add(new FieldConvert("szMedium", "Name"));
fieldsMedium.Add(new FieldConvert("wOrder", "Order"));
MediumTableAdapter mediumAdap = new MediumTableAdapter(sdfDatabase);
ConvertDataSet.MediumDataTable mediums = new ConvertDataSet.MediumDataTable();
mediumAdap.Fill(mediums);
CopyData("Medium", fieldsMedium, mediums);
sql = string.Format("SET IDENTITY_INSERT Medium ON");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
mediumAdap.Update(mediums);
sql = string.Format("SET IDENTITY_INSERT Medium OFF");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("SELECT MAX(MediumID) FROM Medium", sdfDatabase.Connection);
maxId = (int)cmd.ExecuteScalar() + 1;
cmd = new SqlCeCommand(string.Format("ALTER TABLE Medium ALTER COLUMN MediumID IDENTITY({0},1)", maxId), sdfDatabase.Connection);
cmd.ExecuteNonQuery();
#endregion
#region CDSet
formConvertProgress.CurrentTable = "CDSet";
formConvertProgress.Value++;
List <FieldConvert> fieldsSet = new List <FieldConvert>();
fieldsSet.Add(new FieldConvert("IDCDSet", "SetID"));
fieldsSet.Add(new FieldConvert("szCDSetName", "Name"));
SetTableAdapter setAdap = new SetTableAdapter(sdfDatabase);
ConvertDataSet.SetDataTable sets = new ConvertDataSet.SetDataTable();
setAdap.Fill(sets);
CopyData("CDSet", fieldsSet, sets);
sql = string.Format("SET IDENTITY_INSERT [Set] ON");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
setAdap.Update(sets);
sql = string.Format("SET IDENTITY_INSERT [Set] OFF");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("SELECT MAX(SetID) FROM [Set]", sdfDatabase.Connection);
object maxSetID = cmd.ExecuteScalar();
if (maxSetID is DBNull)
{
maxId = 1;
}
else
{
maxId = (int)maxSetID + 1;
}
cmd = new SqlCeCommand(string.Format("ALTER TABLE [Set] ALTER COLUMN SetID IDENTITY({0},1)", maxId), sdfDatabase.Connection);
cmd.ExecuteNonQuery();
#endregion
#region Program
formConvertProgress.CurrentTable = "Program";
formConvertProgress.Value++;
List <FieldConvert> fieldsProgram = new List <FieldConvert>();
fieldsProgram.Add(new FieldConvert("IDCD", "CDID"));
fieldsProgram.Add(new FieldConvert("szName", "Name"));
fieldsProgram.Add(new FieldConvert("szLieder", "Tracks"));
fieldsProgram.Add(new FieldConvert("bStandard", "IsStandard"));
ProgramTableAdapter programAdap = new ProgramTableAdapter(sdfDatabase);
ConvertDataSet.ProgramDataTable programs = new ConvertDataSet.ProgramDataTable();
programAdap.Fill(programs);
CopyData("Programme", fieldsProgram, programs);
programAdap.Update(programs);
#endregion
#region Index
formConvertProgress.CurrentTable = "Index";
formConvertProgress.Value++;
List <FieldConvert> fieldsIndex = new List <FieldConvert>();
fieldsIndex.Add(new FieldConvert("IDCD", "CDID"));
fieldsIndex.Add(new FieldConvert("szIndexName", "Name"));
fieldsIndex.Add(new FieldConvert("dwPosition", "Position"));
IndexTableAdapter indexAdap = new IndexTableAdapter(sdfDatabase);
ConvertDataSet.IndexDataTable indexes = new ConvertDataSet.IndexDataTable();
indexAdap.Fill(indexes);
CopyData("Index", fieldsIndex, indexes);
indexAdap.Update(indexes);
#endregion
#region Codes
formConvertProgress.CurrentTable = "Codes";
formConvertProgress.Value++;
List <FieldConvert> fieldsCode = new List <FieldConvert>();
fieldsCode.Add(new FieldConvert("cBuchstabe", "Letter"));
fieldsCode.Add(new FieldConvert("szBedeutung", "Description"));
CodeTableAdapter codeAdap = new CodeTableAdapter(sdfDatabase);
ConvertDataSet.CodeDataTable codes = new ConvertDataSet.CodeDataTable();
codeAdap.Fill(codes);
CopyData("Kennzeichen", fieldsCode, codes);
codeAdap.Update(codes);
#endregion
#region Queue
formConvertProgress.CurrentTable = "Queue";
formConvertProgress.Value++;
List <FieldConvert> fieldsQueue = new List <FieldConvert>();
fieldsQueue.Add(new FieldConvert("Q_lIDCD", "CDID"));
fieldsQueue.Add(new FieldConvert("Q_lAction", "Action"));
fieldsQueue.Add(new FieldConvert("Q_sIdentity", "Identity"));
fieldsQueue.Add(new FieldConvert("Q_sIdentityCDDB", "IdentityCDDB"));
QueueTableAdapter queueAdap = new QueueTableAdapter(sdfDatabase);
ConvertDataSet.QueueDataTable queues = new ConvertDataSet.QueueDataTable();
queueAdap.Fill(queues);
CopyData("Queue", fieldsQueue, queues);
queueAdap.Update(queues);
#endregion
#region LoanedCDs
formConvertProgress.CurrentTable = "LoanedCDs";
formConvertProgress.Value++;
List <FieldConvert> fieldsLoanedCD = new List <FieldConvert>();
fieldsLoanedCD.Add(new FieldConvert("IDCD", "CDID"));
fieldsLoanedCD.Add(new FieldConvert("Kommentar", "Comment"));
fieldsLoanedCD.Add(new FieldConvert("VerliehenAm", "LoanedDate"));
fieldsLoanedCD.Add(new FieldConvert("VerliehenAn", "LoanedTo"));
fieldsLoanedCD.Add(new FieldConvert("RueckgabeTermin", "ReturnDate"));
LoanedCDTableAdapter loanedCDAdap = new LoanedCDTableAdapter(sdfDatabase);
ConvertDataSet.LoanedCDDataTable loanedCDs = new ConvertDataSet.LoanedCDDataTable();
loanedCDAdap.Fill(loanedCDs);
CopyData("VerlieheneCDs", fieldsLoanedCD, loanedCDs);
loanedCDAdap.Update(loanedCDs);
#endregion
#region Dialog
formConvertProgress.CurrentTable = "Dialog";
formConvertProgress.Value++;
List <FieldConvert> fieldsDialog = new List <FieldConvert>();
fieldsDialog.Add(new FieldConvert("D_IDDialog", "DialogID"));
fieldsDialog.Add(new FieldConvert("D_IDCategory", "CategoryID"));
fieldsDialog.Add(new FieldConvert("D_DialogXML", "DialogXML"));
DialogTableAdapter dialogAdap = new DialogTableAdapter(sdfDatabase);
ConvertDataSet.DialogDataTable dialogs = new ConvertDataSet.DialogDataTable();
dialogAdap.Fill(dialogs);
CopyData("Dialog", fieldsDialog, dialogs);
sql = string.Format("SET IDENTITY_INSERT [Dialog] ON");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
dialogAdap.Update(dialogs);
sql = string.Format("SET IDENTITY_INSERT [Dialog] OFF");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("SELECT MAX(DialogID) FROM [Dialog]", sdfDatabase.Connection);
maxSetID = cmd.ExecuteScalar();
if (maxSetID is DBNull)
{
maxId = 1;
}
else
{
maxId = (int)maxSetID + 1;
}
cmd = new SqlCeCommand(string.Format("ALTER TABLE [Dialog] ALTER COLUMN DialogID IDENTITY({0},1)", maxId), sdfDatabase.Connection);
cmd.ExecuteNonQuery();
#endregion
#region Role
formConvertProgress.CurrentTable = "Role";
formConvertProgress.Value++;
List <FieldConvert> fieldsRole = new List <FieldConvert>();
fieldsRole.Add(new FieldConvert("R_ID", "RoleID"));
fieldsRole.Add(new FieldConvert("R_Role", "Name"));
RoleTableAdapter roleAdap = new RoleTableAdapter(sdfDatabase);
ConvertDataSet.RoleDataTable roles = new ConvertDataSet.RoleDataTable();
roleAdap.Fill(roles);
CopyData("Role", fieldsRole, roles);
sql = string.Format("SET IDENTITY_INSERT [Role] ON");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
roleAdap.Update(roles);
sql = string.Format("SET IDENTITY_INSERT [Role] OFF");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("SELECT MAX(RoleID) FROM [Role]", sdfDatabase.Connection);
maxSetID = cmd.ExecuteScalar();
if (maxSetID is DBNull)
{
maxId = 1;
}
else
{
maxId = (int)maxSetID + 1;
}
cmd = new SqlCeCommand(string.Format("ALTER TABLE [Role] ALTER COLUMN RoleID IDENTITY({0},1)", maxId), sdfDatabase.Connection);
cmd.ExecuteNonQuery();
#endregion
#region Participant
formConvertProgress.CurrentTable = "Participant";
formConvertProgress.Value++;
List <FieldConvert> fieldsParticipant = new List <FieldConvert>();
fieldsParticipant.Add(new FieldConvert("P_ID", "ParticipantID"));
fieldsParticipant.Add(new FieldConvert("P_IDArtist", "PersonGroupID"));
fieldsParticipant.Add(new FieldConvert("P_IDRole", "RoleID"));
fieldsParticipant.Add(new FieldConvert("P_IDCD", "CDID"));
fieldsParticipant.Add(new FieldConvert("P_TrackNumber", "TrackNumber"));
fieldsParticipant.Add(new FieldConvert("P_Comment", "Comment"));
ParticipantTableAdapter participantAdap = new ParticipantTableAdapter(sdfDatabase);
ConvertDataSet.ParticipantDataTable participants = new ConvertDataSet.ParticipantDataTable();
participantAdap.Fill(participants);
CopyData("Participant", fieldsParticipant, participants);
sql = string.Format("SET IDENTITY_INSERT [Participant] ON");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
participantAdap.Update(participants);
sql = string.Format("SET IDENTITY_INSERT [Participant] OFF");
cmd = new SqlCeCommand(sql, sdfDatabase.Connection);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("SELECT MAX(ParticipantID) FROM [Participant]", sdfDatabase.Connection);
maxSetID = cmd.ExecuteScalar();
if (maxSetID is DBNull)
{
maxId = 1;
}
else
{
maxId = (int)maxSetID + 1;
}
cmd = new SqlCeCommand(string.Format("ALTER TABLE [Participant] ALTER COLUMN ParticipantID IDENTITY({0},1)", maxId), sdfDatabase.Connection);
cmd.ExecuteNonQuery();
#endregion
}
void CreateTables()
{
CeConn.Open();
try
{
string CreateCariTableSql = " CREATE TABLE CARI ( " +
" CR_CARI_NO nvarchar (10), " +
" CR_CARI_ADI1 nvarchar (37), " +
" CR_ADRES1 nvarchar (38), " +
" CR_ADRES2 nvarchar (26), " +
" CR_TEL1 nvarchar (12), " +
" CR_VERGI_DA nvarchar (14), " +
" CR_VERGI_NO nvarchar (10), " +
" CR_RISK_LIM float , " +
" CR_TOPLAM_RISK float , " +
" CR_PLAS_HS nvarchar (10), " +
" rg_pt float, " +
" rg_sl float, " +
" rg_cr float, " +
" rg_pr float, " +
" rg_cm float, " +
" rg_ct float, " +
" borc_bakiye float," +
" ort_vade datetime ," +
" rg_pz float) ";
SqlCeCommand cmd = new SqlCeCommand(CreateCariTableSql, CeConn);
cmd.ExecuteNonQuery();
string CreateStokTabeString = " CREATE TABLE Stok (" +
" GRUP_KODU nvarchar (12), " +
" SICIL_KODU nvarchar (13), " +
" SICIL_ADI nvarchar (70), " +
" SF1 float," +
" SF2 float," +
" SF3 float," +
" SF4 float," +
" SF5 float," +
" AF1 float," +
" AF2 float," +
" AF3 float," +
" AF4 float," +
" AF5 float," +
" SATIS_KDVY float," +
" IND1 float," +
" IND2 float," +
" IND3 float," +
" IND4 float," +
" IND5 float," +
" ISK_ENGEL nvarchar (11)," +
" ISKOD3 nvarchar (7)," +
" BIRIM nvarchar (8)," +
" BIRIM1A nvarchar (8)," +
" BIRIM1C float) ";
cmd.CommandText = CreateStokTabeString;
cmd.ExecuteNonQuery();
string CreateCarDatTableString = "CREATE TABLE CarDat ( " +
" TARIH datetime, " +
" HS_NO nvarchar (10), " +
" BELGE_NO nvarchar (9)," +
" BORC float ," +
" ALAC float ," +
" OZKOD2 nvarchar (19)," +
" VADE datetime ," +
" CR_PLAS_HS nvarchar (10))";
cmd.CommandText = CreateCarDatTableString;
cmd.ExecuteNonQuery();
string CreateSBaslikTableString = "CREATE TABLE SiparisBaslik ( " +
" Musteri_Kodu nvarchar (15)," +
" Siparis_No int IDENTITY (1, 1)," +
" Musteri_Adi nvarchar (60) ," +
" Plasiyer_Kodu nvarchar (15)," +
" Siparis_Tarihi datetime, " +
" Teslim_Tarihi datetime, " +
" Vade_Gunu int, " +
" Odeme_Sekli nvarchar (1))";
cmd.CommandText = CreateSBaslikTableString;
cmd.ExecuteNonQuery();
string SiparisDetayTableString = "CREATE TABLE siparisDetay( " +
" Grup_Kodu nvarchar (15), " +
" Siparis_No int, " +
" SiparisDetay_No int IDENTITY (1, 1)," +
" Sicil_Kodu nvarchar (15), " +
" Plasiyer_Kodu nvarchar (15), " +
" Sicil_Adi nvarchar (70), " +
" Miktar float, " +
" Birim nvarchar (10), " +
" Birim_Fiyat float, " +
" Tutar float, " +
" iskonto1 float, " +
" iskonto2 float, " +
" iskonto3 float, " +
" iskonto4 float, " +
" iskonto5 float, " +
" kolimiktar float)";
cmd.CommandText = SiparisDetayTableString;
cmd.ExecuteNonQuery();
string TahsilatTableString = "CREATE TABLE TahsilatBaslik ( " +
" Cari_No nvarchar (15), " +
" Makbuz_No int IDENTITY (1,1) ," +
" Plasiyer_Kodu nvarchar (15), " +
" Tahsilat_Tarihi datetime )";
cmd.CommandText = TahsilatTableString;
cmd.ExecuteNonQuery();
string TahsilatDetayString = "CREATE TABLE TahsilatDetay ( " +
" Makbuz_No int ," +
" Tahsilat_Turu nvarchar(1)," +
" Tahsilat_Tutari float, " +
" Para_cinsi nvarchar (3), " +
" Vadesi datetime, " +
" Borclu nvarchar (60), " +
" Tanzim_Tarihi datetime, " +
" Tanzim_Yeri nvarchar (15), " +
" Banka nvarchar (15), " +
" Banka_Sube nvarchar (15), " +
" Belge_No int IDENTITY (1,1), " +
" KKart_No nvarchar (19)," +
" Plasiyer_Kodu nvarchar(15), " +
" Cari_No nvarchar(15)) ";
cmd.CommandText = TahsilatDetayString;
cmd.ExecuteNonQuery();
string ZiyaretTableString = "CREATE TABLE Ziyaret ( " +
" Cari_No nvarchar (15), " +
" Ziyaret_turu nvarchar (250)," +
" Kilometre float, " +
" Ziyaret_Tarih datetime," +
" Plasiyer_Kodu nvarchar (15))";
cmd.CommandText = ZiyaretTableString;
cmd.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
}
CeConn.Close();
}
public static bool AsignarFisioterapuetasPaciente(int piCodigoPaciente, int piCodigoFisioterapeuta01, int piCodigoFisioterapeuta02)
{
#region linq to class
//try
//{
// //RemoteModelDataContext dm = new RemoteModelDataContext();
// Paciente pacienteAsignarFisioterapeutas = dm.Pacientes.Where(i => i.iCodigoPaciente == piCodigoPaciente).First();
// if (piCodigoFisioterapeuta01 > 0)
// pacienteAsignarFisioterapeutas.iCodigoFisioterapeutaUno = piCodigoFisioterapeuta01;
// else
// pacienteAsignarFisioterapeutas.iCodigoFisioterapeutaUno = null;
// if (piCodigoFisioterapeuta02 > 0)
// pacienteAsignarFisioterapeutas.iCodigoFisioterapeutaDos = piCodigoFisioterapeuta02;
// else
// pacienteAsignarFisioterapeutas.iCodigoFisioterapeutaDos = null;
// if (piCodigoFisioterapeuta01 == -1 && piCodigoFisioterapeuta02 == -1)
// pacienteAsignarFisioterapeutas.iFlagFisioAsigPaciente = 0;
// else
// pacienteAsignarFisioterapeutas.iFlagFisioAsigPaciente = 1;// 1 => SI tiene almento un fisioterapeuta asignado
// // 0 => NO tiene almento un fisioterapeuta asignado
// dm.SubmitChanges();
// return true;
//}
//catch (Exception Ex)
//{
// return false;
//}
#endregion
#region sql compact connection
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
try
{
PacienteB pacienteAsignarFisioterapeutas = ObtenerPacientes().Where(i => i.iCodigoPaciente == piCodigoPaciente).First();
if (piCodigoFisioterapeuta01 > 0)
{
pacienteAsignarFisioterapeutas.iCodigoFisioterapeutaUno = piCodigoFisioterapeuta01;
}
else
{
pacienteAsignarFisioterapeutas.iCodigoFisioterapeutaUno = null;
}
if (piCodigoFisioterapeuta02 > 0)
{
pacienteAsignarFisioterapeutas.iCodigoFisioterapeutaDos = piCodigoFisioterapeuta02;
}
else
{
pacienteAsignarFisioterapeutas.iCodigoFisioterapeutaDos = null;
}
if (piCodigoFisioterapeuta01 == -1 && piCodigoFisioterapeuta02 == -1)
{
pacienteAsignarFisioterapeutas.iFlagFisioAsigPaciente = 0;
}
else
{
pacienteAsignarFisioterapeutas.iFlagFisioAsigPaciente = 1;// 1 => SI tiene almento un fisioterapeuta asignado
}
// 0 => NO tiene almento un fisioterapeuta asignado
conn = new SqlCeConnection("Data Source=" + System.IO.Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "HRNPCIData.sdf"));
conn.Open();
cmd = new SqlCeCommand("UPDATE Paciente SET " +
"iCodigoFisioterapeutaUno = @iCodigoFisioterapeutaUno, " +
"iCodigoFisioterapeutaDos = @iCodigoFisioterapeutaDos, " +
"iFlagFisioAsigPaciente = @iFlagFisioAsigPaciente " +
"WHERE iCodigoPaciente = @iCodigoPaciente", conn);
// (pPaciente.iCodigoFisioterapeutaUno as object) ?? DBNull.Value)
cmd.Parameters.AddWithValue("@iCodigoFisioterapeutaUno", (pacienteAsignarFisioterapeutas.iCodigoFisioterapeutaUno as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@iCodigoFisioterapeutaDos", (pacienteAsignarFisioterapeutas.iCodigoFisioterapeutaDos as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@iCodigoPaciente", piCodigoPaciente);
cmd.Parameters.AddWithValue("@iFlagFisioAsigPaciente", (pacienteAsignarFisioterapeutas.iFlagFisioAsigPaciente as object) ?? DBNull.Value);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
return(false);
}
finally
{
conn.Close();
}
return(true);
#endregion
}
public void getReaderDetails()
{
//string cmd = "select ReaderId from ReaderConfig ";
// SqlCeDataAdapter da = new SqlCeDataAdapter(cmd, localdbCon);
// DataSet ds = new DataSet();
// da.Fill(ds);
// ReaderId = ds.Tables[0].Rows[0].ItemArray[0].ToString();
if (ReaderId == "")
{
MessageBox.Show("Reader details Not found.");
classLog.writeLog("Error @:ReaderId Missing from Reader Configuration");
//classLog.TaskSyncLog("ReaderId Missing from Reader Configuration", fname);
}
else
{
txtTaskStatus.Visible = true;
string cmd1 = "select OperationType,LocationId,AllocationCode,SubTaskCode from [dbo].[Reader_Allocation_Dtl_Tbl] where [ReaderId]=" + ReaderId + " and [RAStatus]='Allocated' ";
SqlDataAdapter da1 = new SqlDataAdapter(cmd1, sqldbCon);
DataTable dt0 = new DataTable();
da1.Fill(dt0);
int f = 0;
if (dt0.Rows.Count > 0)
{
foreach (DataRow dr0 in dt0.Rows)
{
OperationType = dr0[0].ToString();
LocationId = dr0[1].ToString();
AllocationCode = dr0[2].ToString();
SubTaskCode = dr0[3].ToString();
//MessageBox.Show(SubTaskCode);
if (f == 0)
{
clearTaskDatainlocaldb();
f++;
clearTaskdtlinTruckmst();
}
//...............................................
string cmd11 = "select a.TaskCode,a.OperationType,a.RefCode,c.SubTaskCode,c.SubRefCode,c.Commodity,c.Source,c.Destination,a.TStatus from [dbo].[Task_Tbl] a,[dbo].[Fleet_Allocation_Tbl] b,[dbo].[SubTask_Tbl] c where b.[AllocationCode]=" + AllocationCode + " and a.[TaskCode]=b.[TaskCode] and b.[TaskCode]=c.[TaskCode] and a.[TStatus] in('M','S','A','R')";
SqlDataAdapter da11 = new SqlDataAdapter(cmd11, sqldbCon);
DataTable dt11 = new DataTable();
da11.Fill(dt11);
if (dt11.Rows.Count > 0)
{
foreach (DataRow dr in dt11.Rows)
{
if (dr != null)
{
//classLog.TaskSyncLog("TaskDetails", fname);
if (dr[8].ToString() == "M")//to update the Task Status as Synced if its Status is Mapped.
{
string co = "Update [dbo].[Task_Tbl] set TStatus='S',ModifiedBy='" + User + "',ModifiedDate=GETDATE() where TaskCode=" + dr[0].ToString() + " and TStatus='M'";
sqldbCon.Open();
SqlCommand c = new SqlCommand(co, sqldbCon);
c.ExecuteNonQuery();
sqldbCon.Close();
c.Dispose();
}
//classLog.TaskSyncLog("[TaskCode],[RefCode],[SubTaskCode],[SubRefCode],[Commodity],[Source],[Destination],[LocationId],[ReaderOperation],[ReaderId]", fname);
string InsertQuery = "Insert into TaskConfig ([TaskCode],[RefCode],[SubTaskCode],[SubRefCode],[Commodity],[Source],[Destination],[LocationId],[ReaderOperation],[ReaderId],[Status]) Values ('" + dr[0].ToString() + "','" + dr[2].ToString() + "','" + dr[3].ToString() + "','" + dr[4].ToString() + "','" + dr[5].ToString() + "','" + dr[6].ToString() + "','" + dr[7].ToString() + "','" + LocationId + "','" + OperationType + "','" + ReaderId + "','Active')";
string CON_STRING = localConnection;
SqlCeCommand cm = new SqlCeCommand(InsertQuery, localdbCon);
cm.ExecuteNonQuery();
cm.Dispose();
txtTaskNo.Text = dr[0].ToString();
txtOperationType.Text = dr[1].ToString();
txtRefCode.Text = dr[2].ToString();
txtSubTaskNo.Text = dr[3].ToString();
txtSubRefCode.Text = dr[4].ToString();
txtCommodity.Text = dr[5].ToString();
txtSource.Text = dr[6].ToString();
txtDestination.Text = dr[7].ToString();
txtTaskStatus.Text = "Task deatils Saved.";
//classLog.TaskSyncLog(dr[0].ToString() + "," + dr[2].ToString() + "," + dr[3].ToString() + "," + dr[4].ToString() + "," + dr[5].ToString() + "," + dr[6].ToString() + "," + dr[7].ToString() + "," + LocationId + "," + OperationType + ","+ ReaderId +"" + ":TaskDetails Saved", fname);
classLog.writeLog("Message @:" + txtTaskNo.Text + "Task details saved and fleat details updation in progress");
TaskSyncLogInfo();
}
}
//..............................................................................................................
if (AllocationCode == "")
{
MessageBox.Show("Fleet Allotment pending.");
}
else
{
string sql1 = "select a.[Tkid],a.[SubTaskCode],b.[TruckNo] from [dbo].[Fleet_Allocation_Dtl_Tbl] a,[dbo].[TruckMaster] b where a.[AllocationCode]=" + AllocationCode + "and a.[Tkid]=b.[Tkid] and a.[FAStatus]='Alloted'";
sqldbCon.Open();
SqlDataAdapter daa1 = new SqlDataAdapter(sql1, sqldbCon);
DataTable dt1 = new DataTable();
daa1.Fill(dt1);
sqldbCon.Close();
if (dt1.Rows.Count == 0)
{
syncstatus = "N";
txtTaskStatus.Text = "Fleet Details Not found.";
//classLog.TaskSyncLog("Task Sync Faild due to Lack of Fleet details", fname);
classLog.writeLog("Error @:" + txtTaskNo.Text + "Task Sync Faild due to Lack of Fleet details");
}
else
{
try
{
int i = 1;
//classLog.TaskSyncLog(dt1.Rows.Count.ToString() +":Trucks Allocated", fname);
//classLog.TaskSyncLog("[TaskCode],[SubTaskCode],[Tkid],[TruckNo]", fname);
foreach (DataRow drr in dt1.Rows)
{
txtTaskStatus.Text = "Task Truck Update:" + dt1.Rows.Count.ToString() + "-" + i.ToString();
string UpdateQry = "UPDATE [TruckMaster] set [TaskCode]='" + txtTaskNo.Text + "' ,[SubTaskCode]='" + drr[1].ToString() + "' where [Tkid]='" + drr[0].ToString() + "'";
//classLog.TaskSyncLog(txtTaskNo.Text + "," + drr[1].ToString() + "," + drr[0].ToString() + "," + drr[2].ToString() + ":Fleet details updated", fname);
SqlCeCommand cmd111 = new SqlCeCommand(UpdateQry, localdbCon);
cmd111.ExecuteNonQuery();
cmd111.Dispose();
i++;
}
syncstatus = "Y";
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
classLog.writeLog("Erro @:" + ex.ToString());
}
}
}
//...................................................................
}
else
{
syncstatus = "X";
MessageBox.Show("Task details not found.");
}
if (syncstatus == "N")
{
txtTaskNo.Text = "";
txtOperationType.Text = "";
txtRefCode.Text = "";
txtSubTaskNo.Text = "";
txtSubRefCode.Text = "";
txtCommodity.Text = "";
txtSource.Text = "";
txtDestination.Text = "";
MessageBox.Show("Task Sync Failed.");
classLog.writeLog("Error @:Task Sync Failed");
//classLog.TaskSyncLog("Task Sync Failed", fname);
}
else if (syncstatus == "Y")
{
MessageBox.Show("Task Sync Completed.");
classLog.writeLog("Messsage @:" + txtTaskNo.Text + "Task Sync Completed");
//classLog.TaskSyncLog("Task Sync Completed", fname);
syncstatus = "ZZ";
}
//...............................................
}
}
else
{
MessageBox.Show("Reader has no Task");
classLog.writeLog("Error @:Task Sync Failed due to Lack of Task");
//classLog.TaskSyncLog("Task Sync Failed due to Lack of Task", fname);
}
//da.Dispose();
da1.Dispose();
dt0.Dispose();
//ds.Dispose();
}
txtTaskStatus.Visible = false;
sqldbCon.Close();
}
public static bool GuardarPaciente(PacienteB pPaciente)
{
#region linq to class
//try
//{
// //RemoteModelDataContext dm = new RemoteModelDataContext();
// dm.Pacientes.InsertOnSubmit(pPaciente);
// dm.SubmitChanges();
// return true;
//}
//catch
//{
// return false;
//}
#endregion
#region SQL compact connection
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
try
{
conn = new SqlCeConnection("Data Source=" + System.IO.Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "HRNPCIData.sdf"));
conn.Open();
cmd = new SqlCeCommand("INSERT INTO Paciente (vNombresPaciente, vApellidosPaciente, cGeneroPaciente, vDiagnosticoMedicoPaciente, iNivelPaciente, iPorcentajeNivelPaciente, vEdadCronologicaPaciente, daFecNacPaciente, vUrlFotoPaciente,iFlagFisioAsigPaciente, iFlagBorrradoPaciente, vTelefonoApoderado, vCelularApoderado, vApellidoApoderado, vNombresApoderado, vParentescoApoderado, vCelMedNeuroReferencia, vNomApeMedNeuroReferencia, cDNIPaciente, iCodigoFisioterapeutaDos, iCodigoFisioterapeutaUno) " +
"VALUES (@vNombresPaciente, @vApellidosPaciente, @cGeneroPaciente, @vDiagnosticoMedicoPaciente, @iNivelPaciente, @iPorcentajeNivelPaciente, @vEdadCronologicaPaciente, @daFecNacPaciente, @vUrlFotoPaciente, @iFlagFisioAsigPaciente, @iFlagBorrradoPaciente, @vTelefonoApoderado, @vCelularApoderado, @vApellidoApoderado, @vNombresApoderado, @vParentescoApoderado, @vCelMedNeuroReferencia, @vNomApeMedNeuroReferencia, @cDNIPaciente, @iCodigoFisioterapeutaDos, @iCodigoFisioterapeutaUno)", conn);
cmd.Parameters.AddWithValue("@vNombresPaciente", (pPaciente.vNombresPaciente.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vApellidosPaciente", (pPaciente.vApellidosPaciente.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@cGeneroPaciente", (pPaciente.cGeneroPaciente as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vDiagnosticoMedicoPaciente", (pPaciente.vDiagnosticoMedicoPaciente.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@iNivelPaciente", (pPaciente.iNivelPaciente as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@iPorcentajeNivelPaciente", (pPaciente.iPorcentajeNivelPaciente as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vEdadCronologicaPaciente", (pPaciente.vEdadCronologicaPaciente.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@daFecNacPaciente", (pPaciente.daFecNacPaciente as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vUrlFotoPaciente", (pPaciente.vUrlFotoPaciente.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@iFlagFisioAsigPaciente", (pPaciente.iFlagFisioAsigPaciente as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@iFlagBorrradoPaciente", (pPaciente.iFlagBorrradoPaciente as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vTelefonoApoderado", (pPaciente.vTelefonoApoderado.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vCelularApoderado", (pPaciente.vCelularApoderado.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vApellidoApoderado", (pPaciente.vApellidoApoderado.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vNombresApoderado", (pPaciente.vNombresApoderado.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vParentescoApoderado", (pPaciente.vParentescoApoderado.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vCelMedNeuroReferencia", (pPaciente.vCelMedNeuroReferencia.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@vNomApeMedNeuroReferencia", (pPaciente.vNomApeMedNeuroReferencia.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@cDNIPaciente", (pPaciente.cDNIPaciente.Trim() as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@iCodigoFisioterapeutaUno", (pPaciente.iCodigoFisioterapeutaUno as object) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@iCodigoFisioterapeutaDos", (pPaciente.iCodigoFisioterapeutaDos as object) ?? DBNull.Value);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
return(false);
}
finally
{
conn.Close();
}
return(true);
#endregion
}
/// <summary>
/// Función que guarda un UserActionClientDataEntity en la base de datos.
/// </summary>
/// <param name="userActionClientData">UserActionClientDataEntity a guardar</param>
/// <param name="scope">Estructura interna para evitar problemas con referencias circulares</param>
/// <exception cref="ArgumentNullException">
/// Si <paramref name="userActionClientData"/> no es un <c>UserActionClientDataEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// Si una DbException ocurre cuando se accede a la base de datos
/// </exception>
public void Save(UserActionClientDataEntity userActionClientData, Dictionary <string, IEntity> scope)
{
if (userActionClientData == null)
{
throw new ArgumentException("The argument can't be null");
}
// Crear una clave unica para identificar el objeto dentro del scope interno
string scopeKey = userActionClientData.Id.ToString(NumberFormatInfo.InvariantInfo) + "UserActionClientData";
if (scope != null)
{
// Si se encuentra dentro del scope lo retornamos
if (scope.ContainsKey(scopeKey))
{
return;
}
}
else
{
// Crea un nuevo scope si este no fue enviado
scope = new Dictionary <string, IEntity>();
}
try
{
// Crea una nueva conexion y una nueva transaccion si no hay una a nivel superior
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
string commandName = "";
bool isUpdate = false;
// Verifica si se debe hacer una actualización o una inserción
if (userActionClientData.IsNew || !DataAccessConnection.ExistsEntity(userActionClientData.Id, "UserActionClientData", "idUserActionClientData", dbConnection, dbTransaction))
{
commandName = "INSERT INTO [UserActionClientData] (idUserActionClientData, ACTIONTYPE, START, STOP, IDTABLE, IDREGISTER, IDCOMPONENT, IDSERVICE, [TIMESTAMP] ) VALUES( @idUserActionClientData, @actionType,@start,@stop,@idTable,@idRegister,@idComponent,@idService, GETDATE()); ";
}
else
{
isUpdate = true;
commandName = "UPDATE [UserActionClientData] SET actionType = @actionType, start = @start, stop = @stop, idTable = @idTable, idRegister = @idRegister, idComponent = @idComponent, idService = @idService , timestamp=GETDATE() WHERE idUserActionClientData = @idUserActionClientData";
}
// Se crea un command
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(commandName, dbConnection, dbTransaction);
// Agregar los parametros del command .
SqlCeParameter parameter;
if (!isUpdate && userActionClientData.Id == 0)
{
userActionClientData.Id = DataAccessConnection.GetNextId("idUserActionClientData", "UserActionClientData", dbConnection, dbTransaction);
}
parameter = dataAccess.GetNewDataParameter("@idUserActionClientData", DbType.Int32);
parameter.Value = userActionClientData.Id;
sqlCommand.Parameters.Add(parameter);
FillSaveParameters(userActionClientData, sqlCommand);
// Ejecutar el command
sqlCommand.ExecuteNonQuery();
scopeKey = userActionClientData.Id.ToString(NumberFormatInfo.InvariantInfo) + "UserActionClientData";
// Agregar la entidad al scope actual
scope.Add(scopeKey, userActionClientData);
// Guarda las colecciones de objetos relacionados.
// Guardar objetos relacionados con la entidad actual
// Actualizar
// Cierra la conexión si fue abierta en la función
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Actualizar los campos new y changed
userActionClientData.IsNew = false;
userActionClientData.Changed = false;
}
catch (DbException dbException)
{
// Anula la transaccion
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Relanza una excepcion personalizada
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Cierra la conexión si fue inicializada
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
/// <summary>
/// Función que elimina un UserActionClientDataEntity de la base de datos.
/// </summary>
/// <param name="userActionClientData">UserActionClientDataEntity a eliminar</param>
/// <param name="scope">Estructura interna para evitar problemas de referencia circular.</param>
/// <exception cref="ArgumentNullException">
/// Si <paramref name="userActionClientData"/> no es un <c>UserActionClientDataEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// Si una DbException ocurre cuando se accede a la base de datos
/// </exception>
public void Delete(UserActionClientDataEntity userActionClientData, Dictionary <string, IEntity> scope)
{
if (userActionClientData == null)
{
throw new ArgumentException("The argument can't be null");
}
try
{
// Abrir una nueva conexión e inicializar una transacción si es necesario
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
// Carga la entidad para garantizar eliminar todos los datos antiguos.
userActionClientData = this.Load(userActionClientData.Id, true);
if (userActionClientData == null)
{
throw new UtnEmallDataAccessException("Error al recuperar datos al intentar eliminar.");
}
// Crea un nuevo command para eliminar
string cmdText = "DELETE FROM [UserActionClientData] WHERE idUserActionClientData = @idUserActionClientData";
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(cmdText, dbConnection, dbTransaction);
// Agrega los valores de los parametros
SqlCeParameter parameterID = dataAccess.GetNewDataParameter("@idUserActionClientData", DbType.Int32);
parameterID.Value = userActionClientData.Id;
sqlCommand.Parameters.Add(parameterID);
// Ejecuta el comando
sqlCommand.ExecuteNonQuery();
// Elimina los objetos relacionados
// Confirma la transacción si se inicio dentro de la función
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Eliminamos la entidad de la lista de entidades cargadas en memoria
inMemoryEntities.Remove(userActionClientData.Id);
// Eliminamos la entidad del scope
if (scope != null)
{
string scopeKey = userActionClientData.Id.ToString(NumberFormatInfo.InvariantInfo) + "UserActionClientData";
scope.Remove(scopeKey);
}
}
catch (DbException dbException)
{
// Anula la transaccion
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Relanza una excepcion personalizada
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Cierra la conexión si fue abierta dentro de la Función
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
public static bool createDB()
{
//File.Create(BaseData.DBPath);
BaseData.ConnectionString = "Data Source=" + BaseData.DBPath + ";Password=1396";
BaseData.DBPath = BaseData.DBPath;
SqlCeEngine engine = new SqlCeEngine(BaseData.ConnectionString);
engine.CreateDatabase();
try
{
BaseData.Connection = new SqlCeConnection(BaseData.ConnectionString);
BaseData.Connection.Open();
SqlCeCommand cmd = BaseData.Connection.CreateCommand();
cmd.CommandText = @"
CREATE TABLE [cars]
(
[id] INT NOT NULL IDENTITY (100,1),
[owner_user_id] INT,
[pelak] NVARCHAR(20) NULL,
[description] NVARCHAR(2000) NULL
);";
cmd.ExecuteNonQuery();
cmd = BaseData.Connection.CreateCommand();
cmd.CommandText = @"ALTER TABLE [cars] ADD CONSTRAINT [PK__cars__000000000000006F] PRIMARY KEY ([id]);";
cmd.ExecuteNonQuery();
cmd = BaseData.Connection.CreateCommand();
cmd.CommandText = @"CREATE TABLE [items]
(
[id] INT NOT NULL IDENTITY (100,1),
[name] NVARCHAR(100) NULL,
[description] NVARCHAR(2000) NULL
);
";
cmd.ExecuteNonQuery();
cmd = BaseData.Connection.CreateCommand();
cmd.CommandText = @"ALTER TABLE [items] ADD CONSTRAINT [PK__items__0000000000000061] PRIMARY KEY ([id]);";
cmd.ExecuteNonQuery();
cmd = BaseData.Connection.CreateCommand();
cmd.CommandText = @"CREATE TABLE [users]
(
[id] INT NOT NULL IDENTITY (100,1),
[username] NVARCHAR(100) NULL,
[password] NVARCHAR(100) NULL,
[create_date] NVARCHAR(20) NULL,
[name] NVARCHAR(50) NULL,
[family] NVARCHAR(50) NULL,
[address] NVARCHAR(2000) NULL,
[melli] NVARCHAR(20) NULL,
[location] NVARCHAR(50) NULL,
[car_pelak] NVARCHAR(20) NULL,
[user_type] INT NULL,
[description] NVARCHAR(2000) NULL
);
";
cmd.ExecuteNonQuery();
cmd = BaseData.Connection.CreateCommand();
cmd.CommandText = @"ALTER TABLE [users] ADD CONSTRAINT [PK__users__000000000000002E] PRIMARY KEY ([id]);";
cmd.ExecuteNonQuery();
cmd = BaseData.Connection.CreateCommand();
cmd.CommandText = @"insert into users(username,password,user_type) values('admin','123',1);";
cmd.ExecuteNonQuery();
cmd = BaseData.Connection.CreateCommand();
cmd.CommandText = @"CREATE TABLE [weight]
(
[id] INT NOT NULL IDENTITY (100,1),
[owner_user_id] INT NULL,
[driver_user_id] INT NULL,
[opperator_user_id] INT NULL,
[serial] NVARCHAR(20) NULL,
[insert_date] NVARCHAR(20) NULL,
[insert_time] NVARCHAR(20) NULL,
[weight] FLOAT NULL,
[weight2] FLOAT NULL,
[car_id] INT NULL,
[item_type] INT NULL,
[positive_decrease] FLOAT NULL,
[negative_decrease] FLOAT NULL,
[pest_percent] FLOAT NULL,
[description] NVARCHAR(2000) NULL
);";
cmd.ExecuteNonQuery();
cmd = BaseData.Connection.CreateCommand();
cmd.CommandText = @"ALTER TABLE [weight] ADD CONSTRAINT [PK__weight__0000000000000055] PRIMARY KEY ([id]);";
cmd.ExecuteNonQuery();
}
catch
{
return(false);
}
finally
{
BaseData.Connection.Close();
}
return(true);
}
private void Loadbaza()
{
this.label1.Text = "PRZYGOTOWANIE DO KONWERSJI";
label1.Refresh();
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
string delimeter = ";";
string filename = dfile;
StreamReader sr = new StreamReader(filename);
string allData = sr.ReadToEnd();
string[] rows = allData.Split("\r\n".ToCharArray());
allData = "empty";
sr.DiscardBufferedData();
sr.Close();
cn.Open();
SqlCeCommand cmdh = cn.CreateCommand();
cmdh.CommandText = "INSERT INTO edihead (FileName, TypPolskichLiter, TypDok, NrDok, Data, DataRealizacji, Magazyn, SposobPlatn, TerminPlatn, IndeksCentralny, NazwaWystawcy, AdresWystawcy, KodWystawcy, MiastoWystawcy, UlicaWystawcy, NIPWystawcy, BankWystawcy, KontoWystawcy, TelefonWystawcy, NrWystawcyWSieciSklepow, NazwaOdbiorcy, AdresOdbiorcy, KodOdbiorcy, MiastoOdbiorcy, UlicaOdbiorcy, NIPOdbiorcy, BankOdbiorcy, KontoOdbiorcy, TelefonOdbiorcy, NrOdbiorcyWSieciSklepow, DoZaplaty, status, complete) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
cmdh.Parameters.Add("@0", SqlDbType.NVarChar, 40);
cmdh.Parameters.Add("@1", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@2", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@3", SqlDbType.NVarChar, 30);
cmdh.Parameters.Add("@4", SqlDbType.NVarChar, 30);
cmdh.Parameters.Add("@5", SqlDbType.NVarChar, 30);
cmdh.Parameters.Add("@6", SqlDbType.NVarChar, 30);
cmdh.Parameters.Add("@7", SqlDbType.NVarChar, 10);
cmdh.Parameters.Add("@8", SqlDbType.NVarChar, 10);
cmdh.Parameters.Add("@9", SqlDbType.NVarChar, 10);
cmdh.Parameters.Add("@10", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@11", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@12", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@13", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@14", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@15", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@16", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@17", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@18", SqlDbType.NVarChar, 30);
cmdh.Parameters.Add("@19", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@20", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@21", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@22", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@23", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@24", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@25", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@26", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@27", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@28", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@29", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@30", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@31", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@32", SqlDbType.Bit);
cmdh.Prepare();
SqlCeCommand cmdb = cn.CreateCommand();
cmdb.CommandText = "INSERT INTO edibody (NrDok, Nazwa, kod, Vat, Jm, Asortyment, Sww, PKWiU, Ilosc, Cena, Wartosc, IleWOpak, CenaSp, status, complete) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
cmdb.Parameters.Add("@1", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@2", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@3", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@4", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@5", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@6", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@7", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@8", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@9", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@10", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@11", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@12", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@13", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@14", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@15", SqlDbType.Bit);
cmdb.Prepare();
SqlCeCommand cmde = cn.CreateCommand();
cmde.CommandText = "INSERT INTO ediend (NrDok, Vat, SumaNet, SumaVat, status, complete) VALUES (?, ?, ?, ?, ?, ?)";
cmde.Parameters.Add("@1", SqlDbType.NVarChar, 30);
cmde.Parameters.Add("@2", SqlDbType.NVarChar, 20);
cmde.Parameters.Add("@3", SqlDbType.NVarChar, 20);
cmde.Parameters.Add("@4", SqlDbType.NVarChar, 20);
cmde.Parameters.Add("@5", SqlDbType.NVarChar, 20);
cmde.Parameters.Add("@6", SqlDbType.Bit);
cmde.Prepare();
label1.Text = "KONWERTOWANIE DANYCH";
label1.Refresh();
zile.Text = rows.Length.ToString();
zile.Refresh();
int i = 0;
foreach (string r in rows)
{
string[] items = r.Split(delimeter.ToCharArray());
if (items[0] != "" && items[0] == "edihead")
{
i += 1;
ile.Text = i.ToString();
ile.Refresh();
cmdh.Parameters["@0"].Value = items[1];
cmdh.Parameters["@1"].Value = items[2];
cmdh.Parameters["@2"].Value = items[3];
cmdh.Parameters["@3"].Value = items[4];
cmdh.Parameters["@4"].Value = items[5];
cmdh.Parameters["@5"].Value = items[6];
cmdh.Parameters["@6"].Value = items[7];
cmdh.Parameters["@7"].Value = items[8];
cmdh.Parameters["@8"].Value = items[9];
cmdh.Parameters["@9"].Value = items[10];
cmdh.Parameters["@10"].Value = items[11];
cmdh.Parameters["@11"].Value = items[12];
cmdh.Parameters["@12"].Value = items[13];
cmdh.Parameters["@13"].Value = items[14];
cmdh.Parameters["@14"].Value = items[15];
cmdh.Parameters["@15"].Value = items[16];
cmdh.Parameters["@16"].Value = items[17];
cmdh.Parameters["@17"].Value = items[18];
cmdh.Parameters["@18"].Value = items[19];
cmdh.Parameters["@19"].Value = items[20];
cmdh.Parameters["@20"].Value = items[21];
cmdh.Parameters["@21"].Value = items[22];
cmdh.Parameters["@22"].Value = items[23];
cmdh.Parameters["@23"].Value = items[24];
cmdh.Parameters["@24"].Value = items[25];
cmdh.Parameters["@25"].Value = items[26];
cmdh.Parameters["@26"].Value = items[27];
cmdh.Parameters["@27"].Value = items[28];
cmdh.Parameters["@28"].Value = items[29];
cmdh.Parameters["@29"].Value = items[30];
cmdh.Parameters["@30"].Value = items[31];
cmdh.Parameters["@31"].Value = items[32];
cmdh.Parameters["@32"].Value = byte.Parse(items[33]);
cmdh.ExecuteNonQuery();
}
if (items[0] != "" && items[0] == "edibody")
{
i += 1;
ile.Text = i.ToString();
ile.Refresh();
cmdb.Parameters["@1"].Value = items[1];
cmdb.Parameters["@2"].Value = items[2];
cmdb.Parameters["@3"].Value = items[3];
cmdb.Parameters["@4"].Value = items[4];
cmdb.Parameters["@5"].Value = items[5];
cmdb.Parameters["@6"].Value = items[6];
cmdb.Parameters["@7"].Value = items[7];
cmdb.Parameters["@8"].Value = items[8];
cmdb.Parameters["@9"].Value = items[9];
cmdb.Parameters["@10"].Value = items[10];
cmdb.Parameters["@11"].Value = items[11];
cmdb.Parameters["@12"].Value = items[12];
cmdb.Parameters["@13"].Value = items[13];
cmdb.Parameters["@14"].Value = items[14];
cmdb.Parameters["@15"].Value = byte.Parse(items[15]);
cmdb.ExecuteNonQuery();
}
if (items[0] != "" && items[0] == "ediend")
{
i += 1;
ile.Text = i.ToString();
ile.Refresh();
cmde.Parameters["@1"].Value = items[1];
cmde.Parameters["@2"].Value = items[2];
cmde.Parameters["@3"].Value = items[3];
cmde.Parameters["@4"].Value = items[4];
cmde.Parameters["@5"].Value = items[5];
cmde.Parameters["@6"].Value = byte.Parse(items[6]);
cmde.ExecuteNonQuery();
}
}
cn.Close();
form15.Loaddata();
this.Close();
}
/// <summary>
/// Executes a SQL statement against a connection object.
/// </summary>
/// <returns>
/// The number of rows affected.
/// </returns>
/// <filterpriority>1</filterpriority>
public override int ExecuteNonQuery()
{
return(_innerCommand.ExecuteNonQuery());
}
/// <summary>
/// Función que guarda un StoreEntity en la base de datos.
/// </summary>
/// <param name="store">StoreEntity a guardar</param>
/// <param name="scope">Estructura interna para evitar problemas con referencias circulares</param>
/// <exception cref="ArgumentNullException">
/// Si <paramref name="store"/> no es un <c>StoreEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// Si una DbException ocurre cuando se accede a la base de datos
/// </exception>
public void Save(StoreEntity store, Dictionary <string, IEntity> scope)
{
if (store == null)
{
throw new ArgumentException("The argument can't be null");
}
// Crear una clave unica para identificar el objeto dentro del scope interno
string scopeKey = store.Id.ToString(NumberFormatInfo.InvariantInfo) + "Store";
if (scope != null)
{
// Si se encuentra dentro del scope lo retornamos
if (scope.ContainsKey(scopeKey))
{
return;
}
}
else
{
// Crea un nuevo scope si este no fue enviado
scope = new Dictionary <string, IEntity>();
}
try
{
// Crea una nueva conexion y una nueva transaccion si no hay una a nivel superior
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
string commandName = "";
bool isUpdate = false;
// Verifica si se debe hacer una actualización o una inserción
if (store.IsNew || !DataAccessConnection.ExistsEntity(store.Id, "Store", "idStore", dbConnection, dbTransaction))
{
commandName = "INSERT INTO [Store] (idStore, NAME, TELEPHONENUMBER, INTERNALPHONENUMBER, CONTACTNAME, OWNERNAME, EMAIL, WEBADDRESS, LOCALNUMBER, [TIMESTAMP] ) VALUES( @idStore, @name,@telephoneNumber,@internalPhoneNumber,@contactName,@ownerName,@email,@webAddress,@localNumber, GETDATE()); ";
}
else
{
isUpdate = true;
commandName = "UPDATE [Store] SET name = @name, telephoneNumber = @telephoneNumber, internalPhoneNumber = @internalPhoneNumber, contactName = @contactName, ownerName = @ownerName, email = @email, webAddress = @webAddress, localNumber = @localNumber , timestamp=GETDATE() WHERE idStore = @idStore";
}
// Se crea un command
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(commandName, dbConnection, dbTransaction);
// Agregar los parametros del command .
SqlCeParameter parameter;
if (!isUpdate && store.Id == 0)
{
store.Id = DataAccessConnection.GetNextId("idStore", "Store", dbConnection, dbTransaction);
}
parameter = dataAccess.GetNewDataParameter("@idStore", DbType.Int32);
parameter.Value = store.Id;
sqlCommand.Parameters.Add(parameter);
FillSaveParameters(store, sqlCommand);
// Ejecutar el command
sqlCommand.ExecuteNonQuery();
scopeKey = store.Id.ToString(NumberFormatInfo.InvariantInfo) + "Store";
// Agregar la entidad al scope actual
scope.Add(scopeKey, store);
// Guarda las colecciones de objetos relacionados.
if (store.StoreCategory != null)
{
this.SaveStoreCategoryCollection(new StoreCategoryDataAccess(), store, store.StoreCategory, store.IsNew, scope);
}
// Guardar objetos relacionados con la entidad actual
// Actualizar
// Cierra la conexión si fue abierta en la función
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Actualizar los campos new y changed
store.IsNew = false;
store.Changed = false;
}
catch (DbException dbException)
{
// Anula la transaccion
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Relanza una excepcion personalizada
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Cierra la conexión si fue inicializada
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
private void button2_Click(object sender, EventArgs e)
{
if (id == 1)
{
connect = new SqlCeConnection(database);
connect.Open();
cmd = new SqlCeCommand(@"SELECT * FROM Industry WHERE Name = @name", connect);
cmd.Parameters.AddWithValue("@name", textBox1.Text.Trim());
da = new SqlCeDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
cmd.ExecuteReader();
if (dt.Rows.Count != 0)
{
label4.Visible = true;
return;
}
if (textBox1.Text == "")
{
label4.Visible = true;
return;
}
else
{
label4.Visible = false;
}
if (!double.TryParse(textBox2.Text, out beta))
{
label5.Visible = true;
return;
}
else
{
label5.Visible = false;
}
if (!double.TryParse(textBox3.Text, out growth))
{
label6.Visible = true;
return;
}
else
{
label6.Visible = false;
}
growth = growth / 100;
connect = new SqlCeConnection(database);
connect.Open();
cmd = new SqlCeCommand(@"INSERT INTO Industry (Name, Beta, GrowthRate) VALUES (@name, @beta, @g)", connect);
cmd.Parameters.AddWithValue("@name", textBox1.Text.Trim());
cmd.Parameters.AddWithValue("@beta", beta);
cmd.Parameters.AddWithValue("@g", growth);
cmd.ExecuteNonQuery();
}
else if (id == 3)
{
if (!double.TryParse(textBox1.Text, out beta))
{
label4.Visible = true;
return;
}
else
{
label4.Visible = false;
}
if (!double.TryParse(textBox2.Text, out growth))
{
label5.Visible = true;
return;
}
else
{
label5.Visible = false;
}
growth = growth / 100;
connect = new SqlCeConnection(database);
connect.Open();
cmd = new SqlCeCommand(@"UPDATE Industry SET Beta = @beta, GrowthRate = @g WHERE Name = @name", connect);
cmd.Parameters.AddWithValue("@name", firm.Trim());
cmd.Parameters.AddWithValue("@beta", beta);
cmd.Parameters.AddWithValue("@g", growth);
cmd.ExecuteNonQuery();
}
else if (id == 5)
{
if (!double.TryParse(textBox1.Text, out market))
{
label4.Visible = true;
return;
}
else
{
label4.Visible = false;
}
if (!double.TryParse(textBox2.Text, out riskfree))
{
label5.Visible = true;
return;
}
else
{
label5.Visible = false;
}
int years;
DateTime localdate = DateTime.Now;
years = localdate.Year;
market = market / 100;
riskfree = riskfree / 100;
connect = new SqlCeConnection(database);
connect.Open();
cmd = new SqlCeCommand(@"UPDATE Market SET MarketRate = @market, Riskfree = @risk, Date = @date WHERE MarketRate = @m", connect);
cmd.Parameters.AddWithValue("@market", market);
cmd.Parameters.AddWithValue("@risk", riskfree);
cmd.Parameters.AddWithValue("@date", years);
cmd.Parameters.AddWithValue("@m", market_id);
cmd.ExecuteNonQuery();
}
}
public static bool TransactionSQLsWithConn(string ConnectionString, List <string> SQLs, params string[] parameters)
{
bool flag = false;
try
{
List <string[]> list = new List <string[]>();
foreach (string SQL in SQLs)
{
list.Add(SQL.Split(SplitChars, StringSplitOptions.RemoveEmptyEntries));
}
List <List <string> > list2 = new List <List <string> >();
foreach (string[] item in list)
{
List <string> list3 = new List <string>();
string[] array = item;
foreach (string text in array)
{
if (!text.Contains("@@") && text.Contains("@"))
{
list3.Add(text.Substring(text.IndexOf("@"), text.Length - text.IndexOf("@")));
}
}
list2.Add(list3);
}
using (SqlCeConnection sqlCeConnection = new SqlCeConnection(ConnectionString))
{
sqlCeConnection.Open();
SqlCeTransaction sqlCeTransaction = sqlCeConnection.BeginTransaction();
int num = 0;
string value = "";
try
{
for (int j = 0; j <= SQLs.Count - 1; j++)
{
SqlCeCommand sqlCeCommand = new SqlCeCommand(SQLs[j], sqlCeConnection);
sqlCeCommand.Transaction = sqlCeTransaction;
sqlCeCommand.Parameters.Clear();
List <string> list4 = list2[j];
for (int k = 0; k <= list4.Count - 1; k++)
{
if (j != 0 && k == 0)
{
sqlCeCommand.Parameters.AddWithValue(list4[k], value);
}
else
{
sqlCeCommand.Parameters.AddWithValue(list4[k], parameters[num]);
}
num++;
}
if (SQLs[j].Contains("@@"))
{
DataTable dataTable = new DataTable();
using (SqlCeDataAdapter sqlCeDataAdapter = new SqlCeDataAdapter(sqlCeCommand))
{
sqlCeDataAdapter.Fill(dataTable);
}
if (dataTable.Rows.Count > 0)
{
value = dataTable.Rows[0][0].ToString();
}
}
else
{
sqlCeCommand.ExecuteNonQuery();
}
}
sqlCeTransaction.Commit();
return(true);
}
catch
{
sqlCeTransaction.Rollback();
flag = false;
throw;
}
}
}
catch
{
flag = false;
throw;
}
}
private void AddEditTextTags()
{
StringBuilder sb = new StringBuilder();
int lastID = 0;
int result = 0;
object lastIDObject = null;
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (this.tags_texts_id == 0)
{
command.CommandText = "SELECT MAX(tags_texts_id) FROM tags_texts";
lastIDObject = command.ExecuteScalar();
int.TryParse(lastIDObject.ToString(), out lastID);
lastID++;
sb.Append("INSERT INTO tags_texts (tags_texts_id,text,info) VALUES (");
sb.Append(lastID);
sb.Append(",'");
sb.Append(txtText.Text.Trim());
sb.Append("','");
sb.Append(txtInfo.Text.Trim());
sb.Append("')");
Log.Write(sb.ToString(), this.GetType(), "btnAddEditText_Click", Log.LogType.DEBUG);
this.tags_texts_id = lastID;
command.CommandText = sb.ToString();
result = command.ExecuteNonQuery();
Log.Write("Rows afected " + result, this.GetType(), "btnAddEditText_Click", Log.LogType.INFO);
}
else
{
sb.Append("UPDATE tags_texts SET text = '");
sb.Append(txtText.Text.Trim());
sb.Append("', info = '");
sb.Append(txtInfo.Text.Trim());
sb.Append("' WHERE tags_texts_id = ");
sb.Append(this.tags_texts_id);
Log.Write(sb.ToString(), this.GetType(), "btnAddEditText_Click", Log.LogType.DEBUG);
command.CommandText = sb.ToString();
command.ExecuteNonQuery();
}
command.CommandText = "SELECT MAX(tags_links_id) FROM tags_links";
lastIDObject = command.ExecuteScalar();
int.TryParse(lastIDObject.ToString(), out lastID);
foreach (Tag tag in lbTextTags.Items)
{
/// Ako treba dodati tag flag je na false
if (!tag.Flag)
{
lastID++;
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tags_links (tags_links_id, tags_id, tags_texts_id) VALUES (");
sb.Append(lastID);
sb.Append(",");
sb.Append(tag.ID);
sb.Append(",");
sb.Append(this.tags_texts_id);
sb.Append(")");
Log.Write(sb.ToString(), this.GetType(), "btnAddEditText_Click", Log.LogType.DEBUG);
Log.Write("Linking tag: " + tag.Name, this.GetType(), "btnAddEditText_Click", Log.LogType.DEBUG);
command.CommandText = sb.ToString();
result = command.ExecuteNonQuery();
Log.Write("Rows afected " + result, this.GetType(), "btnAddEditText_Click", Log.LogType.INFO);
}
}
foreach (Tag tag in lbTags.Items)
{
/// Ako treba izbrisati tag, flag je true
if (tag.Flag)
{
lastID++;
sb.Remove(0, sb.Length);
sb.Append("DELETE FROM tags_links WHERE tags_id =");
sb.Append(tag.ID);
sb.Append(" AND tags_texts_id =");
sb.Append(this.tags_texts_id);
Log.Write(sb.ToString(), this.GetType(), "btnAddEditText_Click", Log.LogType.DEBUG);
command.CommandText = sb.ToString();
command.ExecuteNonQuery();
}
}
}
catch (Exception exc)
{
Log.Write(exc, this.GetType(), "btnAddEditText_Click", Log.LogType.ERROR);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
private void SaveData()
{
if (gvLocationProduct.DataSource != null)
{
try
{
Cursor.Current = Cursors.WaitCursor;
//LocationCheckProductManager.TransferToServer();
DataTable productMixedTable = new DataTable();
List <ProductLocation> productLocations = new List <ProductLocation>();
using (SqlCeConnection con = new SqlCeConnection(SqlHelper.SqlCeConnectionString))
{
con.Open();
using (SqlCeCommand com = new SqlCeCommand())
{
com.Connection = con;
foreach (Resco.Controls.SmartGrid.Row item in gvLocationProduct.Rows)
{
var locationCode = ((System.Data.DataRowView)(item.Data)).Row.ItemArray[0].ToString();
using (SqlCeDataAdapter adab = new SqlCeDataAdapter(com))
{
com.CommandText = SqlHelper.GetSql(33);
com.Parameters.Clear();
com.Parameters.AddWithValue("@LocationCode", locationCode);
com.Parameters.AddWithValue("@CreatedBy", GlobalContext.UserCode);
adab.Fill(productMixedTable);
var isWarehouse = GlobalContext.UseInPlaces == UseInPlaces.WAREHOUSE ? true : false;
foreach (DataRow row in productMixedTable.Rows)
{
var productLocation = ServiceHelper.MobileServices.ProductLocationGetByBarcode(row["Barcode"].ToString(),
locationCode,
GlobalContext.WarehouseCode,
GlobalContext.BranchCode,
isWarehouse);
productLocation.OfficerID = row["OfficerId"].ToString();
productLocations.Add(productLocation);
}
// transfer to server.
ServiceHelper.MobileServices.ProductLocationMixAdd(GlobalContext.BranchCode,
GlobalContext.WarehouseCode,
GlobalContext.UserCode,
productLocations.ToArray());
//delete
com.CommandText = SqlHelper.GetSql(30);
com.ExecuteNonQuery();
productMixedTable.Clear();
productLocations.Clear();
}
}
}
}
Cursor.Current = Cursors.Default;
GlobalMessageBox.ShowInfomation("บันทึกข้อมูลไปยัง SAP สำเร็จ");
this.Close();
}
catch (Exception ex)
{
Cursor.Current = Cursors.Default;
GlobalMessageBox.ShowError("บันทึกข้อมูลไม่สำเร็จ กรุณาลองใหม่อีกครั้ง " + ex.Message);
BindGrid();
}
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
try
{
SqlCeConnection con = new SqlCeConnection(Properties.Settings.Default.conne);
SqlCeConnection sqlConnection = new SqlCeConnection(Properties.Settings.Default.conne);
SqlCeCommand cmd1 = new SqlCeCommand();
cmd1.Connection = con;
con.Open();
foreach (DataRow d in ta.Rows)
{
i++;
String pn = d["Prod_name"].ToString();
String qt = d["Qty"].ToString();
String un = d["Unit"].ToString();
String sa = d["Salesp"].ToString();
if (sa.Equals(""))
{
sa = "0";
}
String mrp = d["MRP"].ToString();
String st = d["Stockl"].ToString();
String gst = d["GST"].ToString();
String de = d["Desp"].ToString();
String br = d["Barcode"].ToString();
String hsn = d["HSN"].ToString();
String bd = d["Bar_Desp"].ToString();
sqlConnection.Open();
SqlCeCommand check_User_Name = new SqlCeCommand("Select * FROM goods", sqlConnection);
SqlCeDataReader dr = check_User_Name.ExecuteReader();
String flag = "false";
while (dr.Read())
{
if (dr[1].ToString() == pn)
{
flag = "true";
break;
}
else
{
flag = "false";
}
}
if (flag.Equals("true"))
{
//User Exists
//MessageBox.Show("Record have");
cmd1.CommandText = "UPDATE goods SET [Qty]='" + qt + "',[Unit]='" + un + "',[Salesp]='" + sa + "',[Stockl]='" + st + "',[GST]='" + gst + "',[Desp]='" + de + "',[Barcode]='" + br + "',[HSN]='" + hsn + "',[Bar_desp]='" + bd + "',[MRP]='" + mrp + "' where [Prod_name]='" + pn + "'";
cmd1.ExecuteNonQuery();
}
else
{
//User NOT Exists
cmd1.CommandText = "INSERT INTO goods([Prod_name],[Qty],[Unit],[Salesp],[Stockl],[GST],[Desp],[Barcode],[HSN],[Bar_desp],[MRP]) VALUES('" + pn + "','" + qt + "','" + un + "','" + sa + "','" + st + "','" + gst + "','" + de + "','" + br + "','" + hsn + "','" + bd + "','" + mrp + "')";
cmd1.ExecuteNonQuery();
}
sqlConnection.Close();
MessageBox.Show("Your Product Will Upload SuccessFully");
//DoHeavyWork();
//backgroundWorker1.ReportProgress(i);
}
con.Close();
//MessageBox.Show("Your Excle Sheet Successfully Imported..");
}
catch (SqlCeException ex)
{
MessageBox.Show("Error :" + ex);
}
}
public static void SaveExecutedCommand(CommandExecutor executor)
{
using (var connection = DatabaseUtil.GetConnection())
{
using (var trans = connection.BeginTransaction())
{
const string insertCmdText =
@"INSERT INTO CommandUsages (Text, At, CommandId)
VALUES (@Text, @At, @CommandId)";
const string insertArgText =
@"INSERT INTO CommandUsageArguments
(CommandUsageId, FacetMonikerId, Ordinal, RangeStartIndex, RangeLength, Relevance, MatchedText)
VALUES
(@CommandUsageId, @FacetMonikerId, @Ordinal, @RangeStartIndex, @RangeLength, 0.0, @MatchedText)";
int commandUsageId;
using (var cmd = new SqlCeCommand(insertCmdText, connection, trans))
{
cmd.Parameters.AddWithValue("@CommandId", Loader.GetCommandInfo(executor.Command).DatabaseId);
cmd.Parameters.AddWithValue("@Text", executor.Input == null ? "" : executor.Input.Text);
cmd.Parameters.AddWithValue("@At", DateTime.Now);
cmd.ExecuteNonQuery();
commandUsageId = connection.GetLastInsertedId(trans);
}
using (var cmd = new SqlCeCommand(insertArgText, connection, trans))
{
cmd.Parameters.AddWithValue("@CommandUsageId", commandUsageId);
cmd.Parameters.AddWithValue("@FacetMonikerId", 0);
cmd.Parameters.AddWithValue("@Ordinal", 0);
cmd.Parameters.AddWithValue("@RangeStartIndex", 0);
cmd.Parameters.AddWithValue("@RangeLength", 0);
cmd.Parameters.AddWithValue("@MatchedText", "");
for (var ordinal = 0; ordinal < executor.Arguments.Count; ordinal++)
{
var arg = executor.Arguments[ordinal];
if (!arg.IsSpecified)
{
continue;
}
cmd.Parameters["@Ordinal"].Value = ordinal;
cmd.Parameters["@FacetMonikerId"].Value =
FacetIndex.SaveFacetMoniker(arg.FacetMoniker, connection: connection, trans: trans);
if (arg.ParseRange != null)
{
cmd.Parameters["@RangeStartIndex"].Value = arg.ParseRange.StartIndex;
cmd.Parameters["@RangeLength"].Value = arg.ParseRange.Length;
cmd.Parameters["@MatchedText"].Value = executor.Input == null ? "" : executor.Input.GetTextForRange(arg.ParseRange);
}
else
{
cmd.Parameters["@RangeStartIndex"].Value = -1;
cmd.Parameters["@RangeLength"].Value = -1;
cmd.Parameters["@MatchedText"].Value = "";
}
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
}
}
public static string UpdateRows(string id_skladiste, string kolicina, string sifra)
{
DataTable sql_kol_skl = classSQL.select("Select max(id_stavka) from promjena_cijene_komadno_stavke where sifra = '" + sifra + "' ", "kolicina na skladistu posebno").Tables[0];
if (classSQL.remoteConnectionString == "")
{
try
{
if (classSQL.connection.State.ToString() == "Closed")
{
classSQL.connection.Open();
}
SqlCeCommand UpdateCmd = classSQL.connection.CreateCommand();
UpdateCmd.CommandText = "UPDATE promjena_cijene_komadno_stavke " + "SET kolicina_ostatak = @kolicina " + "WHERE id_skladiste = @id_skladiste AND sifra=@sifra AND id_stavka = '" + sql_kol_skl.Rows[0][0].ToString() + "'";
UpdateCmd.Parameters.Add("@id_skladiste", SqlDbType.Int, 4, "id_skladiste");
UpdateCmd.Parameters.Add("@kolicina", SqlDbType.Decimal, 10, "kolicina");
UpdateCmd.Parameters.Add("@sifra", SqlDbType.NVarChar, 20, "sifra");
UpdateCmd.Parameters["@sifra"].Value = sifra;
UpdateCmd.Parameters["@id_skladiste"].Value = id_skladiste;
UpdateCmd.Parameters["@kolicina"].Value = kolicina;
UpdateCmd.ExecuteNonQuery();
classSQL.connection.Close();
return("");
}
catch (SqlCeException ex)
{
classSQL.connection.Close();
return(ex.ToString());
}
}
else
{
try
{
if (classSQL.remoteConnection.State.ToString() == "Closed")
{
classSQL.remoteConnection.Open();
}
NpgsqlCommand UpdateCmd = classSQL.remoteConnection.CreateCommand();
UpdateCmd.CommandText = "UPDATE promjena_cijene_komadno_stavke " + "SET kolicina_ostatak = @kolicina " + "WHERE id_skladiste = @id_skladiste AND sifra=@sifra AND id_stavka = '" + sql_kol_skl.Rows[0][0].ToString() + "'";
UpdateCmd.Parameters.Add("@id_skladiste", NpgsqlDbType.Integer, 4, "id_skladiste");
UpdateCmd.Parameters.Add("@kolicina", NpgsqlDbType.Numeric, 10, "kolicina");
UpdateCmd.Parameters.Add("@sifra", NpgsqlDbType.Varchar, 20, "sifra");
UpdateCmd.Parameters["@sifra"].Value = sifra;
UpdateCmd.Parameters["@id_skladiste"].Value = id_skladiste;
UpdateCmd.Parameters["@kolicina"].Value = kolicina;
UpdateCmd.ExecuteNonQuery();
classSQL.connection.Close();
return("");
}
catch (SqlCeException ex)
{
classSQL.connection.Close();
return(ex.ToString());
}
}
}
private void button2_Click_1(object sender, EventArgs e)
{
openFileDialog1.Filter = "Excel (*.xls)|*.xls|All files (*.*)|*.*";
DialogResult ofd = openFileDialog1.ShowDialog();
if (ofd == DialogResult.OK)
{
filename = openFileDialog1.FileName;
MessageBox.Show(" Your Excel File :- " + filename);
SqlCeConnection sqlconn = new SqlCeConnection(Properties.Settings.Default.conne);
//SqlCeCommand sqlcmd = new SqlCeCommand("DELETE from goods ", sqlconn);
//sqlconn.Open();
//sqlcmd.ExecuteNonQuery();
//SqlCeCommand sqlcmd1 = new SqlCeCommand("ALTER TABLE [goods] ALTER COLUMN [goodsID] IDENTITY(1,1)", sqlconn);
//sqlcmd1.ExecuteNonQuery();
//sqlconn.Close();
SqlCeConnection con = new SqlCeConnection(Properties.Settings.Default.conne);
SqlCeCommand cmd1 = new SqlCeCommand();
String connstr = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filename + "';Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'");
OleDbConnection conn = new OleDbConnection(connstr);
string strSQL = ("SELECT * FROM [Goods Sheet$]");
ta = new DataTable();
OleDbCommand cmd = new OleDbCommand(strSQL, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ta);
con.Open();
cmd1.Connection = con;
try
{
SqlCeConnection sqlConnection = new SqlCeConnection(Properties.Settings.Default.conne);
foreach (DataRow d in ta.Rows)
{
i++;
String pn = d["Prod_name"].ToString();
String qt = d["Qty"].ToString();
String un = d["Unit"].ToString();
String sa = d["Salesp"].ToString();
if (sa.Equals(""))
{
sa = "0";
}
String mrp = d["MRP"].ToString();
String st = d["Stockl"].ToString();
String gst = d["GST"].ToString();
String de = d["Desp"].ToString();
String br = d["Barcode"].ToString();
String hsn = d["HSN"].ToString();
String bd = d["Bar_Desp"].ToString();
sqlConnection.Open();
SqlCeCommand check_User_Name = new SqlCeCommand("Select * FROM goods", sqlConnection);
SqlCeDataReader dr = check_User_Name.ExecuteReader();
String flag = "false";
while (dr.Read())
{
if (dr[1].ToString() == pn)
{
flag = "true";
break;
}
else
{
flag = "false";
}
}
if (flag.Equals("true"))
{
//User Exists
//MessageBox.Show("Record have");
cmd1.CommandText = "UPDATE goods SET [Qty]='" + qt + "',[Unit]='" + un + "',[Salesp]='" + sa + "',[Stockl]='" + st + "',[GST]='" + gst + "',[Desp]='" + de + "',[Barcode]='" + br + "',[HSN]='" + hsn + "',[Bar_desp]='" + bd + "',[MRP]='" + mrp + "' where [Prod_name]='" + pn + "'";
cmd1.ExecuteNonQuery();
}
else
{
//User NOT Exists
cmd1.CommandText = "INSERT INTO goods([Prod_name],[Qty],[Unit],[Salesp],[Stockl],[GST],[Desp],[Barcode],[HSN],[Bar_desp],[MRP]) VALUES('" + pn + "','" + qt + "','" + un + "','" + sa + "','" + st + "','" + gst + "','" + de + "','" + br + "','" + hsn + "','" + bd + "','" + mrp + "')";
cmd1.ExecuteNonQuery();
}
sqlConnection.Close();
//DoHeavyWork();
//backgroundWorker1.ReportProgress(i);
}
con.Close();
//MessageBox.Show("Your Excle Sheet Successfully Imported..");
}
catch (SqlCeException ex)
{
MessageBox.Show("Error :" + ex);
}
}
}
/// <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();
}
