/// <summary>
/// Closes the SqlCeBulkCopy instance.
/// </summary>
public void Close()
{
if (_ownsConnection && _conn != null)
{
_conn.Dispose();
}
}
private void llLogout_Click(object sender, EventArgs e)
{
try
{
UserID = string.Empty;
UserName = string.Empty;
CustName = string.Empty;
LocationName = string.Empty;
ItemActivity = string.Empty;
if (MyReader != null)
{
MyReader.Dispose();
}
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
this.Close();
}
catch (Exception ex)
{
Helper.LogError(ex, "llLogout_Click");
}
}
public void Close()
{
if (ownsConnection && conn != null)
{
conn.Dispose();
}
}
//=============================================================
private void btn_reset_Click(object sender, EventArgs e)
{
//deleta todos os registros feito pelo usuario na base de dados
//caso o usuario pressione NÂO, não acontece nada e volta para a janela de inicio
if (MessageBox.Show("ATENÇÃO: Deseja eliminar todos os contatos da base de dados?",
"ATENÇÃO", MessageBoxButtons.YesNo,
MessageBoxIcon.Warning) == DialogResult.No)
{
return;
}
//caso o usuario aperte sim executa o codigo abaixo e deleta todos os registros
SqlCeConnection deletar = new SqlCeConnection("Data Source = " + cl_static.base_dados);
deletar.Open();
SqlCeCommand coman = new SqlCeCommand("DELETE FROM contatos", deletar);
coman.ExecuteNonQuery();
deletar.Dispose();
//aparece a mensagem que todos os contatos foram eliminados
MessageBox.Show("Dados eliminados com sucesso.");
}
public void Dispose()
{
if (sqlConnection != null)
{
sqlConnection.Dispose();
}
}
//=======================================
//criar um metodo ára criar o DB
public static void CriarBaseDados()
{
//nosso motor
SqlCeEngine motor = new SqlCeEngine("Data source = " + base_dados); //fonte DB = local da pasta \+dados.sdf
motor.CreateDatabase(); //entao cria uma stancia desse objeto que é o motor
//criar extrutura da base de dados
SqlCeConnection ligacao = new SqlCeConnection(); //essa conextionstring poderia ser digitada diretamente aqui
ligacao.ConnectionString = "Data source = " + base_dados;
//abrir
ligacao.Open();
//comando
SqlCeCommand comando = new SqlCeCommand(); //commandtext poderia ser digitado diretamente aqui//comando de criação tabela
comando.CommandText =
"CREATE TABLE contatos(" +
"id_contato int not null primary key, "+
"nome nvarchar(50), "+
"telefone nvarchar(20), "+
"atualizacao datetime)";
//conexao
comando.Connection = ligacao;
comando.ExecuteNonQuery();
//uma query que nao devolve resultado apenas vai executar comando dentro da DB
comando.Dispose();
ligacao.Dispose(); //eliminar lixo de memoria restado
}
/// <summary>
/// Releases the resources.
/// </summary>
public void Dispose()
{
if (oConn != null)
{
oConn.Dispose();
}
}
public void UpdatedRecordIsSavedWhenTransactionCommitted()
{
string commandString = "Update Items Set Price = 5000 where ItemID = 2";
SqlCeCommand cmd = (SqlCeCommand)db.GetSqlStringCommand(commandString);
SqlCeConnection conn = (SqlCeConnection)db.CreateConnection();
conn.Open();
SqlCeTransaction trans = conn.BeginTransaction();
DataSet dsActualResult = db.ExecuteDataSet(cmd, trans);
commandString = "Select ItemDescription, Price from Items order by ItemID";
cmd = (SqlCeCommand)db.GetSqlStringCommand(commandString);
dsActualResult = db.ExecuteDataSet(cmd, trans);
trans.Commit();
conn.Close();
trans.Dispose();
conn.Dispose();
Assert.AreEqual<int>(3, dsActualResult.Tables[0].Rows.Count, "Mismatch in number of rows in the returned dataset. Problem with the test data or with the execute dataset.");
Assert.AreEqual("Digital Image Pro", dsActualResult.Tables[0].Rows[0][0].ToString().Trim());
Assert.AreEqual("38.95", dsActualResult.Tables[0].Rows[0][1].ToString().Trim());
Assert.AreEqual("Excel 2003", dsActualResult.Tables[0].Rows[1][0].ToString().Trim());
Assert.AreEqual("5000", dsActualResult.Tables[0].Rows[1][1].ToString().Trim());
Assert.AreEqual("Infopath", dsActualResult.Tables[0].Rows[2][0].ToString().Trim());
Assert.AreEqual("89", dsActualResult.Tables[0].Rows[2][1].ToString().Trim());
}
public static void AddParametro(string chave, string valor)
{
//string ConnectionString = ConfigurationManager.ConnectionStrings["prjbase.Properties.Settings.dbintegracaoConnectionString"].ConnectionString;
string ConnectionString = ConfigurationManager.ConnectionStrings["prjbase.Properties.Settings.ConnectionString"].ConnectionString;
string retorno = string.Empty;
//MySqlConnection con = new MySqlConnection(ConnectionString);
SqlCeConnection con = new SqlCeConnection(ConnectionString);
try
{
con.Open();
//MySqlCommand cmd = new MySqlCommand("insert into parametro (chave,valor) values(?chave,?valor)", con);
SqlCeCommand cmd = new SqlCeCommand("insert into parametro (chave,valor) values(@chave,@valor)", con);
cmd.Parameters.AddWithValue("@chave", chave);
cmd.Parameters.AddWithValue("@valor", valor);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
//=============================================================
public static void CriarBaseDados()
{
//criação da base de dados "Data source" é a connection string !!!
SqlCeEngine motor = new SqlCeEngine("Data source = " + base_dados); //sempre coloque "Data source" se não, não da certo
motor.CreateDatabase(); // dentro desse () /\ é uma string de conexao para criar o banco de dados
//conectar a base de dados
SqlCeConnection conectar = new SqlCeConnection();
conectar.ConnectionString = "Data source = " + base_dados;
conectar.Open();
//criar o operario para criar as estruturas(colunas) de dentro da base de dados como nome,telefone,endereço etc
SqlCeCommand operario = new SqlCeCommand();
operario.CommandText =
"CREATE TABLE contatos(" +
"id_contato int not null primary key, " +
"nome nvarchar(50), " +
"telefone nvarchar(20), " +
"atualizacao DateTime)";
//coloquei assim acima para organização, mas pode ser feito em uma linha apenas(mas não é indicado)
operario.Connection = conectar;
operario.ExecuteNonQuery(); //não retorna resultados
operario.Dispose(); //sempre colocar
conectar.Dispose(); //esses dois comandos não deixa o app na memoria, ele apaga então ao abrir de novo ele vai carregar todo denovo
}
private static void criarBaseDadosAcessos()
{
SqlCeEngine criador = new SqlCeEngine(@"Data Source = " + base_dados);
criador.CreateDatabase();
SqlCeConnection ligacao = new SqlCeConnection();
ligacao.ConnectionString = @"Data Source = " + base_dados;
ligacao.Open();
SqlCeCommand comando = new SqlCeCommand();
comando.CommandText =
"CREATE TABLE TabelaUsuarios (" +
"Id int not null primary key identity(1,1)," +
"Usuario nvarchar(50) not null ," +
"Senha nvarchar(50) not null," +
"NivelDeAcesso nvarchar(50) not null)";
comando.Connection = ligacao;
comando.ExecuteNonQuery();
comando.Dispose();
ligacao.Dispose();
}
private static void criarUsuarioPrincipal()
{
//Criar a ligação com a base de dados
SqlCeConnection ligacao = new SqlCeConnection();
ligacao.ConnectionString = @"Data Source = " + base_dados;
//Abrindo ligação com a base de dados
ligacao.Open();
//criar um comando
SqlCeCommand comando = new SqlCeCommand();
comando.Connection = ligacao;
//adicionando os parâmetros
comando.Parameters.AddWithValue(@"Usuario", "Adm");
comando.Parameters.AddWithValue(@"Senha", "Adm123");
comando.Parameters.AddWithValue(@"NivelDeAcesso", "All");
//inserir no banco de dados
comando.CommandText = "INSERT INTO TabelaUsuarios(Usuario,Senha,NivelDeAcesso) VALUES(@Usuario,@Senha,@NivelDeAcesso)";
comando.ExecuteNonQuery();
comando.Dispose();
ligacao.Dispose();
}
public static void criarTabelaHistoricoUsuario()
{
SqlCeConnection ligacao = new SqlCeConnection();
ligacao.ConnectionString = @"Data Source = " + base_dados;
ligacao.Open();
SqlCeCommand comando = new SqlCeCommand();
comando.CommandText =
"CREATE TABLE TabelaHistorico" + nome_usuario + " (" +
"Timer int not null," +
"DemandaProduto1 int not null," +
"DemandaProduto2 int not null," +
"DemandaProduto3 int not null," +
"QtdProduzidaProduto1 int not null," +
"QtdProduzidaProduto2 int not null," +
"QtdProduzidaProduto3 int not null)";
comando.Connection = ligacao;
comando.ExecuteNonQuery();
comando.Dispose();
ligacao.Dispose();
}
protected override void AfterEachExample()
{
transaction.Rollback();
transaction.Dispose();
dbConnection.Close();
dbConnection.Dispose();
}
public override void CreateSyncRepository()
{
string connectionString = "Data Source=\"" + _DbPath + "\";Max Database Size=128;Default Lock Escalation=100;";
IDbConnection conn = new SqlCeConnection(connectionString);
conn.Open();
IDbTransaction t = conn.BeginTransaction();
IDbCommand com = conn.CreateCommand();
com.Transaction = t;
StringBuilder createSyncItem = new StringBuilder();
createSyncItem.Append("CREATE TABLE SyncItem").
Append("(SyncID INT PRIMARY KEY IDENTITY, SyncFK INT, ClassID nvarchar(255), HashCode nvarchar(32), ").
Append("SyncGuid UNIQUEIDENTIFIER, RowGuid UNIQUEIDENTIFIER)");
com.CommandText = createSyncItem.ToString();
com.ExecuteNonQuery();
StringBuilder createFieldState = new StringBuilder();
createFieldState.Append("CREATE TABLE FieldState").
Append("(SyncFK INT, FieldName nvarchar(255), HashCode nvarchar(32), ").
Append("RowGuid UNIQUEIDENTIFIER, PRIMARY KEY (SyncFK, FieldName))");
com.CommandText = createFieldState.ToString();
com.ExecuteNonQuery();
t.Commit();
conn.Close();
conn.Dispose();
}
private void CadastrarUsuario()
{
//Criar a ligação com a base de dados
SqlCeConnection ligacao = new SqlCeConnection();
ligacao.ConnectionString = @"Data Source = " + Auxiliar.base_dados;
//Abrindo ligação com a base de dados
ligacao.Open();
//criar um comando
SqlCeCommand comando = new SqlCeCommand();
comando.Connection = ligacao;
//adicionando os parâmetros
comando.Parameters.AddWithValue(@"Usuario", caixaUsuario.Text);
comando.Parameters.AddWithValue(@"Senha", caixaSenha.Text);
comando.Parameters.AddWithValue(@"NivelDeAcesso", caixaNiveis.SelectedItem);
//inserir no banco de dados
comando.CommandText = "INSERT INTO TabelaUsuarios(Usuario,Senha,NivelDeAcesso) VALUES(@Usuario,@Senha,@NivelDeAcesso)";
comando.ExecuteNonQuery();
comando.Dispose();
ligacao.Dispose();
}
private object lerLinhaBancoDados(int id)
{
// Variáveis
double[] vetorEntrada = new double[qtdEntrada];
//Estabelecendo ligação
SqlCeConnection ligacao = new SqlCeConnection(@"Data Source = " + Auxiliar.base_dados);
ligacao.Open();
//Retirar dados da base de dados
SqlCeDataAdapter adaptador = new SqlCeDataAdapter("SELECT * FROM TabelaControle" + Auxiliar.nome_usuario, ligacao);
DataTable dados = new DataTable();
adaptador.Fill(dados);
for (var i = 0; i < qtdEntrada; i++)
{
vetorEntrada[i] = Convert.ToDouble(dados.Rows[0][i]);
}
Matrix <double> vetEntrada = Matrix <double> .Build.Dense(1, qtdEntrada, vetorEntrada);
//Desligando todas as ligações
adaptador.Dispose();
ligacao.Dispose();
//Retorno
return(vetEntrada);
}
public static void UpdateEndUserHHDReceiveDtl(string user, string usertype)
{
string localConnection = classServerDetails.SdfConnection;
SqlCeConnection dbCon = new SqlCeConnection(localConnection);
dbCon.Open();
string datet = System.DateTime.Now.ToString("yyy-MM-dd HH:mm:ss");
string ndt = "{ts '" + datet + "'}";
SqlCeCommand cmd = new SqlCeCommand("select Max(Id) from HHDTrack where AdminUserIssuedBy is NOT NULL and KiteUserReceivedBy is NOT NULL and EndUserReceivedBy is NULL and EndUserReturnedBy is NULL and KiteUserRetrievedBy is NULL and AdminUserReceivedBy is NULL ", dbCon);
string qry = "";
string idex = cmd.ExecuteScalar().ToString();
if (idex == "")
{
SqlCeCommand cm = new SqlCeCommand("select Max(Id) from HHDTrack where AdminUserIssuedBy is NOT NULL and KiteUserReceivedBy is NULL and EndUserReceivedBy is NULL and EndUserReturnedBy is NULL and KiteUserRetrievedBy is NULL and AdminUserReceivedBy is NULL ", dbCon);
idex = cm.ExecuteScalar().ToString();
}
if (idex != "")
{
qry = "Update HHDTrack set EndUserReceivedBy='" + user + "',EndUserReceivedTime=" + ndt + " where Id=" + idex + "";
SqlCeCommand cmd1 = new SqlCeCommand(qry, dbCon);
cmd1.ExecuteNonQuery();
cmd1.Dispose();
}
dbCon.Close();
dbCon.Dispose();
cmd.Dispose();
}
private bool verificarExistencia()
{
//Criar a ligação com a base de dados
SqlCeConnection ligacao = new SqlCeConnection();
ligacao.ConnectionString = @"Data Source = " + Auxiliar.base_dados;
//Abrindo ligação com a base de dados
ligacao.Open();
//criar um comando
SqlCeCommand comando = new SqlCeCommand();
comando.Connection = ligacao;
//adicionando os parâmetros
comando.Parameters.AddWithValue(@"Usuario", caixaUsuario.Text);
//inserir no banco de dados
comando.CommandText = "SELECT COUNT(1) FROM TabelaUsuarios WHERE Usuario = @Usuario";
Object retorno = comando.ExecuteScalar();
comando.Dispose();
ligacao.Dispose();
if (retorno.Equals(1))
{
return(true);
}
else
{
return(false);
}
}
public bool Add(string varekode, string kategori, decimal poeng, DateTime slutt, DateTime start)
{
if (CheckDuplicate(varekode, poeng, slutt, start))
return false; // Denne varekoden finnes allerede
try
{
var con = new SqlCeConnection(FormMain.SqlConStr);
con.Open();
using (SqlCeCommand cmd = new SqlCeCommand("insert into tblVinnprodukt(Avdeling, Varekode, Kategori, Poeng, DatoOpprettet, DatoExpire, DatoStart) values (@Val1, @val2, @val3, @val4, @val5, @val6, @val7)", con))
{
cmd.Parameters.AddWithValue("@Val1", main.appConfig.Avdeling);
cmd.Parameters.AddWithValue("@Val2", varekode);
cmd.Parameters.AddWithValue("@Val3", kategori);
cmd.Parameters.AddWithValue("@Val4", poeng);
cmd.Parameters.AddWithValue("@Val5", DateTime.Now);
cmd.Parameters.AddWithValue("@Val6", slutt);
cmd.Parameters.AddWithValue("@Val7", start);
cmd.CommandType = System.Data.CommandType.Text;
cmd.ExecuteNonQuery();
}
con.Close();
con.Dispose();
return true;
}
catch (Exception ex)
{
Log.Unhandled(ex);
}
return false;
}
public void Dispose()
{
if (conn != null)
{
conn.Dispose();
}
}
/// <summary>
///
/// </summary>
/// <param name="sdfPath"></param>
/// <param name="queryString"></param>
/// <param name="dataGridViewDestination"></param>
private static void runManicTimeSQLQuery(string sdfPath, string queryString, DataGridView dataGridViewDestination)
{
DataTable dtRecord = new DataTable();
dataGridViewDestination.DataSource = null;
dataGridViewDestination.Refresh();
string strConnection; //= @"C:\Users\Nick\AppData\Local\Finkit\ManicTime\ManicTime.sdf;";
strConnection = sdfPath;
SqlCeConnection con = new SqlCeConnection("Data Source = " + sdfPath + ";");
SqlCeCommand sqlCmd = new SqlCeCommand();
sqlCmd.Connection = con;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = queryString;
try
{
SqlCeDataAdapter sqlDataAdap = new SqlCeDataAdapter(sqlCmd);
sqlDataAdap.Fill(dtRecord);
dataGridViewDestination.DataSource = dtRecord;
//dataGridViewDestination.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader;
//int columncnt = dataGridViewDestination.Columns.Count;
//for (int i = 0; i < columncnt; i++)
//{
// dataGridViewDestination.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
//}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
dtRecord.Dispose();
sqlCmd.Dispose();
con.Close();
con.Dispose();
}
public void RecordIsUpdatedWhenUsingCommandTextAndCommandTypeIBeforeRollback()
{
string commandString = "Update Items Set Price = 5000 where ItemID = 2";
SqlCeConnection conn = (SqlCeConnection)db.CreateConnection();
conn.Open();
SqlCeTransaction trans = conn.BeginTransaction();
DataSet dsActualResult = db.ExecuteDataSet(trans, CommandType.Text, commandString);
commandString = "Select ItemDescription, Price from Items order by ItemID";
dsActualResult = db.ExecuteDataSet(trans, CommandType.Text, commandString);
Assert.AreEqual(3, dsActualResult.Tables[0].Rows.Count, "Mismatch in number of rows in the returned dataset. Problem with the test data or with the execute dataset.");
Assert.AreEqual("Digital Image Pro", dsActualResult.Tables[0].Rows[0][0].ToString().Trim());
Assert.AreEqual("38.95", dsActualResult.Tables[0].Rows[0][1].ToString().Trim());
Assert.AreEqual("Excel 2003", dsActualResult.Tables[0].Rows[1][0].ToString().Trim());
Assert.AreEqual("5000", dsActualResult.Tables[0].Rows[1][1].ToString().Trim());
Assert.AreEqual("Infopath", dsActualResult.Tables[0].Rows[2][0].ToString().Trim());
Assert.AreEqual("89", dsActualResult.Tables[0].Rows[2][1].ToString().Trim());
trans.Rollback();
conn.Close();
trans.Dispose();
conn.Dispose();
}
private void btnDelete_Click(object sender, EventArgs e)
{
btnSave.Enabled = false;
grbDeviceIP.Enabled = false;
dataSourcePath = "Data Source = " + Application.StartupPath + @"\DeviceData.sdf";
SqlCeConnection sqlConnection1 = new SqlCeConnection();
sqlConnection1.ConnectionString = dataSourcePath;
SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
try
{
cmd.CommandText = "DELETE FROM DeviceData WHERE DEVICE_IP='" + Global.selMechIp + "'";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM DeviceURL WHERE DEV_IP='" + Global.selMechIp + "'";
cmd.ExecuteNonQuery();
}
catch { }
sqlConnection1.Dispose();
sqlConnection1.Close();
fnGetIpsFronTable();
btnDelete.Enabled = false;
btnEdit.Enabled = false;
txtDevIp.Text = "";
txtDevNo.Text = "";
txtDevPort.Text = "";
Application.DoEvents();
}
public static void SetCurrentVal(string Sequence, long Value)
{
//string ConnectionString = ConfigurationManager.ConnectionStrings["prjbase.Properties.Settings.dbintegracaoConnectionString"].ConnectionString;
string ConnectionString = ConfigurationManager.ConnectionStrings["prjbase.Properties.Settings.ConnectionString"].ConnectionString;
//MySqlConnection con = new MySqlConnection(ConnectionString);
SqlCeConnection con = new SqlCeConnection(ConnectionString);
try
{
con.Open();
//MySqlCommand cmd = new MySqlCommand("UPDATE sequence_data SET sequence_cur_value = ?Value WHERE sequence_name = ?Sequence", con);
SqlCeCommand cmd = new SqlCeCommand("UPDATE sequence_data SET sequence_cur_value = @Value WHERE sequence_name = @Sequence", con);
cmd.Parameters.AddWithValue("@Sequence", Sequence);
cmd.Parameters.AddWithValue("@Value", Value);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
public async Task ApagarTimes(CommandContext ctx)
{
SqlCeConnection Conexao = new SqlCeConnection($"Data Source = {Utilidades.Utilidades.DB}");
Conexao.Open();
Console.WriteLine($"[Wall-E] [SQLCe] Apagando todos os dados no banco de dados...");
string query = "DELETE FROM TabelaTorneio";
SqlCeCommand Comandos = new SqlCeCommand(query, Conexao);
Comandos.ExecuteNonQuery();
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"[Wall-E] [SQLCe] Dados apagados com sucesso.");
Console.ResetColor();
var embed = new DiscordEmbedBuilder();
embed.WithColor(new DiscordColor(0x32363c))
.WithAuthor($"Os dados do banco de dados foram apagados com sucesso.", null, "https://cdn.discordapp.com/attachments/443159405991821323/471879195685814273/images.png");
await ctx.RespondAsync(embed : embed);
Conexao.Dispose();
Comandos.Dispose();
}
public async Task VerTimesTorneio(CommandContext ctx)
{
SqlCeConnection Conexao = new SqlCeConnection($"Data Source = {Utilidades.Utilidades.DB}");
Conexao.Open();
Console.WriteLine($"[Wall-E] [SQLCe] Pegando informações do banco de dados...");
string query = "SELECT * FROM TabelaTorneio";
SqlCeDataAdapter Adaptador = new SqlCeDataAdapter(query, Conexao);
DataTable Tabela = new DataTable();
Adaptador.Fill(Tabela);
Console.WriteLine($"[Wall-E] [SQLCe] Dados recolhidos com sucesso. Enviando para o Discord...");
Conexao.Dispose();
Adaptador.Dispose();
var embed = new DiscordEmbedBuilder();
foreach (DataRow dados in Tabela.Rows)
{
embed.WithColor(new DiscordColor(0x32363c))
.WithAuthor($"Time: {dados["NomeDoTime"].ToString()}", null, "https://cdn.discordapp.com/attachments/443159405991821323/471879195685814273/images.png")
.WithDescription($"**Jogadores:** {dados["Jogadores"].ToString()}");
await ctx.RespondAsync(embed : embed);
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"[Wall-E] [SQLCe] Dados recolhidos com sucesso e foram enviados para o Discord!");
Console.ResetColor();
}
public static void criarTabelaControleUsuario()
{
SqlCeConnection ligacao = new SqlCeConnection();
ligacao.ConnectionString = @"Data Source = " + base_dados;
ligacao.Open();
SqlCeCommand comando = new SqlCeCommand();
comando.CommandText =
"CREATE TABLE TabelaControle" + nome_usuario + " (" +
"Id int not null primary key identity(0,1)," +
"Bit1Produto1 int not null," +
"Bit2Produto1 int not null," +
"Bit1Produto2 int not null," +
"Bit2Produto2 int not null," +
"Bit1Produto3 int not null," +
"Bit2Produto3 int not null," +
"BitFuncBloco1 int not null," +
"BitFuncBloco2 int not null," +
"BitFuncBloco3 int not null," +
"DemandaProduto1 int not null," +
"DemandaProduto2 int not null," +
"DemandaProduto3 int not null," +
"QtdProduzidaProduto1 int not null," +
"QtdProduzidaProduto2 int not null," +
"QtdProduzidaProduto3 int not null)";
comando.Connection = ligacao;
comando.ExecuteNonQuery();
comando.Dispose();
ligacao.Dispose();
}
//===========================================================================
public void write(string query, List <parametros> parametros = null)
{
// cria ligacao com a base
ligacao = new SqlCeConnection(conecao);
comando = new SqlCeCommand();
ligacao.Open();
comando.Connection = ligacao;
comando.Parameters.Clear();
comando.CommandText = query;
try
{
// Realiza o add de parametros
if (parametros != null)
{
foreach (parametros p in parametros)
{
comando.Parameters.AddWithValue(p.parametro, p.valor);
}
}
//executa o comando
comando.ExecuteNonQuery();
}
catch (Exception erro)
{
MessageBox.Show(erro.Message);
}
//encerra a ligacao
comando.Dispose();
ligacao.Dispose();
}
//===========================================================================
public void write(List <string> tabela)
{
//Responsavel por criar a tabela
//-------------------------
//Tratamento da query
StringBuilder query = new StringBuilder();
foreach (string s in tabela)
{
query.Append(s);
}
// cria ligacao com a base
ligacao = new SqlCeConnection(conecao);
comando = new SqlCeCommand();
ligacao.Open();
comando.Connection = ligacao;
//envia a query
comando.CommandText = query.ToString();
comando.ExecuteNonQuery();
//encerra a ligacao
comando.Dispose();
ligacao.Dispose();
}
private void ConstroiLista()
{
try
{
SqlCeConnection conexao = new SqlCeConnection(@"Data Source = C:\Users\thale\Desktop\Curso C#\Databases\Banco Digital.sdf" + "; Password = '******'");
conexao.Open();
string query = "SELECT * FROM Cliente";
SqlCeDataAdapter adaptador = new SqlCeDataAdapter(query, conexao);
DataTable dados = new DataTable();
adaptador.Fill(dados);
lista_clientes.DataSource = dados;
//esconder colunas
lista_clientes.Columns["id_cliente"].Visible = false;
lista_clientes.Columns["senha"].Visible = false;
//controla a disponibilidade dos comandos
btapagar.Enabled = false;
conexao.Dispose();
}
catch
{
MessageBox.Show("Aconteceu um erro na conexão com o Banco de Dados", "Erro", MessageBoxButtons.OK);
}
}
//string dbfile = Server.MapPath("MyDatabase#1.sdf");
//
public static DataTable ExecuteTable(string constr, string sql, SqlCeParameter[] sp = null)
{
DataTable dt = new DataTable();
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
try
{
conn = GetConn(constr);
cmd = GetCommand(conn, sql, sp);
SqlCeDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
return(dt);
}
catch (Exception ex) { throw new Exception(ex.Message + ":" + sql); }
finally { if (conn != null)
{
conn.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
}
static void CreatePatchesTable()
{
if (!File.Exists(AppDataFolderHelper.GetDBFile("GkJournalDatabase.sdf")))
return;
var connection = new SqlCeConnection(ConnectionString);
var sqlCeCommand = new SqlCeCommand(
"CREATE TABLE Patches (Id nvarchar(20) not null)",
connection);
connection.Open();
sqlCeCommand.ExecuteNonQuery();
connection.Close();
connection.Dispose();
}
public static void AddPatchIndexToDB(string Index)
{
if (!File.Exists(AppDataFolderHelper.GetDBFile("GkJournalDatabase.sdf")))
return;
if (string.IsNullOrEmpty(Index))
return;
var connection = new SqlCeConnection(ConnectionString);
var sqlCeCommand = new SqlCeCommand("Insert Into Patches (Id) Values (@p1)", connection);
sqlCeCommand.Parameters.AddWithValue("@p1", (object)Index);
connection.Open();
sqlCeCommand.ExecuteNonQuery();
connection.Close();
connection.Dispose();
}
static bool IsPatchesTableExists()
{
if (!File.Exists(AppDataFolderHelper.GetDBFile("GkJournalDatabase.sdf")))
return false;
var connection = new SqlCeConnection(ConnectionString);
var sqlCeCommand = new SqlCeCommand(
"SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'Patches'",
connection);
connection.Open();
var reader = sqlCeCommand.ExecuteReader(CommandBehavior.CloseConnection);
bool result = reader.Read();
connection.Close();
connection.Dispose();
return result;
}
public PrekrsajnaForma()
{
InitializeComponent();
// Dohvat popisa prekršaja.
SqlCeConnection conn = new SqlCeConnection(Program.ConnString);
string sqlQry =
"SELECT " +
"ID, " +
"Naziv " +
"FROM " +
"Prekrsaj;";
try
{
DataTable dt = new DataTable("Prekrsaji");
SqlCeCommand cmd = new SqlCeCommand(sqlQry, conn);
SqlCeDataAdapter da = new SqlCeDataAdapter(sqlQry, conn);
conn.Open();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
lbxPrekrsaji.DataSource = dt;
//lbxPrekrsaji.Refresh();
}
else
{
throw new Exception("WTF! (What a Terrible Failure). Katalog prekršaja je prazan!");
}
da.Dispose();
cmd.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
}
}
public void ClearAll()
{
try
{
var con = new SqlCeConnection(FormMain.SqlConStr);
con.Open();
SqlCeCommand command = new SqlCeCommand("DELETE FROM tblVinnprodukt WHERE Avdeling = " + main.appConfig.Avdeling, con);
command.ExecuteNonQuery();
Log.n("Vinnprodukt listen er tømt.");
con.Close();
con.Dispose();
}
catch(Exception ex)
{
Log.Unhandled(ex);
}
}
static List<string> GetAllPatches()
{
if (!File.Exists(AppDataFolderHelper.GetDBFile("GkJournalDatabase.sdf")))
return null;
var connection = new SqlCeConnection(ConnectionString);
var sqlCeCommand = new SqlCeCommand("SELECT * FROM Patches", connection);
var patchIndexes = new List<string>();
connection.Open();
var reader = sqlCeCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
patchIndexes.Add(reader.GetValue(0).ToString());
}
connection.Close();
connection.Dispose();
return patchIndexes;
}
/// <summary>
/// Executes a non-query on the test database.
/// </summary>
/// <param name="query">The query.</param>
/// <remarks></remarks>
public static void ExecuteNonQuery(string query)
{
using (var conn = new SqlCeConnection(String.Format("Data Source = {0}", DbFileName)))
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.ExecuteNonQuery();
command.Dispose();
}
conn.Close();
conn.Dispose();
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
dataSourcePath = "Data Source = " + Application.StartupPath + @"\DeviceData.sdf";
if (Global.selUrlIp != "" && Global.selURL != "")
{
SqlCeConnection sqlConnection1 = new SqlCeConnection();
sqlConnection1.ConnectionString = dataSourcePath;
SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.CommandText = "DELETE FROM DeviceURL WHERE DEV_IP='" + Global.selUrlIp + "' AND DEV_URL='" + Global.selURL + "'";
cmd.ExecuteNonQuery();
sqlConnection1.Dispose();
sqlConnection1.Close();
frmDeviceURL_Load(null, null);
}
}
public void Init()
{
var engine = new SqlCeEngine(_connstr);
if (System.IO.File.Exists(_dbFilename)) {
System.IO.File.Delete(_dbFilename);
}
engine.CreateDatabase();
_conn = new SqlCeConnection(_connstr);
_conn.Open();
var cmd = new SqlCeCommand("create table test_table (id int identity primary key, str nvarchar(100));", _conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
if (_conn != null) {
_conn.Close();
_conn.Dispose();
}
_indepDalc = new SqlCeDalc(_connstr);
}
public bool Add(string nameEmail, string addressEmail, string typeEmail, bool quickEmail)
{
nameEmail = nameEmail.Trim();
if (!IsValidEmail(addressEmail))
{
Log.n("E-post adressen er i feil format.");
return false;
}
if (CheckDuplicate(addressEmail))
return false; // Denne adresse finnes allerede
try
{
var con = new SqlCeConnection(FormMain.SqlConStr);
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();
con.Dispose();
return true;
}
catch (Exception ex)
{
Log.Unhandled(ex);
return false;
}
}
private bool CheckDuplicate(string address)
{
var con = new SqlCeConnection(FormMain.SqlConStr);
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM tblEmail WHERE Address = '" + address + "'";
int result = ((int)cmd.ExecuteScalar());
con.Close();
con.Dispose();
if (result == 0)
return false;
return true;
}
private void EnsureWarmedUp(SqlCe4ConnectionInfo connectionInfo)
{
if(_warmupConnections.ContainsKey(connectionInfo.FilePath))
return;
var cn = new SqlCeConnection(connectionInfo.ServerConnectionString);
if(!_warmupConnections.TryAdd(connectionInfo.FilePath, cn))
{
cn.Close();
cn.Dispose();
}
}
public override void Test()
{
string testString = ToTestString();
// Create a connection object
SqlCeConnection connection = new SqlCeConnection();
// Try to open it
try
{
connection.ConnectionString = ToFullString();
connection.Open();
}
catch (SqlCeException e)
{
// Customize the error message for upgrade required
if (e.Number == m_intDatabaseFileNeedsUpgrading)
{
throw new InvalidOperationException(Resources.SqlCeConnectionProperties_FileNeedsUpgrading);
}
throw;
}
finally
{
connection.Dispose();
}
}
public bool Remove(string id)
{
try
{
int result = 0;
var con = new SqlCeConnection(FormMain.SqlConStr);
con.Open();
using (SqlCeCommand com = new SqlCeCommand("DELETE FROM tblEmail WHERE Id = " + id, con))
{
result = com.ExecuteNonQuery();
}
con.Close();
con.Dispose();
if (result > 0)
return true;
else
return false;
}
catch (Exception ex)
{
Log.Unhandled(ex);
return false;
}
}
public static void UpdateConcept(infoConcept updatedConcept)
{
SqlCeConnection sqlConnection = new SqlCeConnection(connectionString);
sqlConnection.Open();
//AccNum, fName, lastname, lastname2, grade, discount
SqlCeCommand updateCommand = new SqlCeCommand("UPDATE Conceptos SET Title = @conTitle, Base_Amount= @conAmount, Limit_Date = @conDate " +
"WHERE ID = @conID", sqlConnection);
updateCommand.Parameters.AddWithValue("@conTitle", updatedConcept.Name);
updateCommand.Parameters.AddWithValue("@conAmount", updatedConcept.Amount);
updateCommand.Parameters.AddWithValue("@conDate", updatedConcept.LimitDate.ToShortDateString());
updateCommand.Parameters.AddWithValue("@conID", updatedConcept.Value);
updateCommand.CommandType = System.Data.CommandType.Text;
try
{
updateCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
sqlConnection.Close();
sqlConnection.Dispose();
}
}
public static void newStudentRecord(infoStudent newStudent)
{
SqlCeConnection sqlConnection = new SqlCeConnection(connectionString);
sqlConnection.Open();
//AccNum, fName, lastname, lastname2, grade, discount
SqlCeCommand newStudentCommand = new SqlCeCommand("INSERT INTO Students VALUES (@accNum, @fistName, @lastName, @lastName2, @stdGroup, @stdLevel, @stdDiscount, @accType)", sqlConnection);
newStudentCommand.CommandType = System.Data.CommandType.Text;
newStudentCommand.Parameters.AddWithValue("@accNum", newStudent.studentID);
newStudentCommand.Parameters.AddWithValue("@fistName", newStudent.studentFistName);
newStudentCommand.Parameters.AddWithValue("@lastName", newStudent.studentLastName);
newStudentCommand.Parameters.AddWithValue("@lastName2", newStudent.studentLastName2);
newStudentCommand.Parameters.AddWithValue("@stdGroup", newStudent.studentGroup);
newStudentCommand.Parameters.AddWithValue("@stdLevel", newStudent.studentLevel);
newStudentCommand.Parameters.AddWithValue("@stdDiscount", newStudent.paymentDiscount);
newStudentCommand.Parameters.AddWithValue("@accType", newStudent.paymentType);
try
{
newStudentCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
sqlConnection.Close();
sqlConnection.Dispose();
}
}
private void btnEvidentiraj_Click(object sender, EventArgs e)
{
SqlCeConnection conn = new SqlCeConnection(Program.ConnString);
string sqlCmd =
"INSERT INTO Kazna (" +
"VlasnikID, " +
"PrekrsajID, " +
"DatKazne" +
") " +
"VALUES (" +
"@vozacID, " +
"@prekrsajID, " +
"@datKazne" +
");";
try
{
SqlCeParameter vozacID = new SqlCeParameter("vozacID", SqlDbType.Int);
vozacID.Value = intVozacID;
vozacID.Direction = ParameterDirection.Input;
SqlCeParameter prekrsajID = new SqlCeParameter("prekrsajID", SqlDbType.Int);
prekrsajID.Value = lbxPrekrsaji.SelectedValue;
prekrsajID.Direction = ParameterDirection.Input;
SqlCeParameter datKazne = new SqlCeParameter("datKazne", SqlDbType.DateTime);
datKazne.Value = DateTime.Now;
datKazne.Direction = ParameterDirection.Input;
SqlCeCommand cmd = new SqlCeCommand(sqlCmd, conn);
cmd.Parameters.Add(vozacID);
cmd.Parameters.Add(prekrsajID);
cmd.Parameters.Add(datKazne);
conn.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
MessageBox.Show("Prekršaj '" + lbxPrekrsaji.Text + "' je evidentiran.");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
}
}
public static void UpdatePayment(List<string> paymentInfo)
{
SqlCeConnection sqlConnection = new SqlCeConnection(connectionString);
sqlConnection.Open();
//AccNum, fName, lastname, lastname2, grade, discount
SqlCeCommand updateCommand = new SqlCeCommand("UPDATE Payment SET Folio = @pFolio, Amount = @pAmount, Date = @pDate, Completed = @pCompleted " +
"WHERE PID = @pID", sqlConnection);
updateCommand.Parameters.AddWithValue("@pFolio", paymentInfo[0]);
updateCommand.Parameters.AddWithValue("@pAmount", paymentInfo[1]);
updateCommand.Parameters.AddWithValue("@pDate", paymentInfo[2]);
updateCommand.Parameters.AddWithValue("@pCompleted", paymentInfo[3]);
updateCommand.Parameters.AddWithValue("@pID", paymentInfo[5]);
updateCommand.CommandType = System.Data.CommandType.Text;
try
{
updateCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
sqlConnection.Close();
sqlConnection.Dispose();
}
}
private void fnGetDBSettings()
{
string dataSourcePath = "Data Source = " + Application.StartupPath + @"\TADatabase.sdf";
SqlCeConnection sqlConnection1 = new SqlCeConnection();
sqlConnection1.ConnectionString = dataSourcePath;
SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM SQL_DATA_FIELD_SETTINGS WHERE SQL_TAB_COMP_ID="+Global.selUpdateId.ToString();
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
SqlCeDataReader dataRead;
dataRead = cmd.ExecuteReader();
while (dataRead.Read())
{
int i = 0;
foreach (string item in cboTable.Items)
{
if (item == dataRead[6].ToString())
{
cboTable.SelectedIndex = i;
break;
}
i++;
}
lbServer.Text = dataRead[1].ToString();
lbDatabase.Text = dataRead[2].ToString();
i = 0;
foreach (string emp in cmbEmpId.Items)
{
if (emp == dataRead[7].ToString())
{
cmbEmpId.SelectedIndex = i;
break;
}
i++;
}
i = 0;
foreach (string dev in cmbDevId.Items)
{
if (dev == dataRead[8].ToString())
{
cmbDevId.SelectedIndex = i;
break;
}
i++;
}
panel2.Enabled = true;//emp
panel9.Enabled = true;//device
if (dataRead[9].ToString() == "" || dataRead[9].ToString() == string.Empty || dataRead[9].ToString() == null)
{
if ((dataRead[10].ToString() == "" || dataRead[10].ToString() == string.Empty || dataRead[10].ToString() == null) && (dataRead[11].ToString() == "" || dataRead[11].ToString() == string.Empty || dataRead[11].ToString() == null))
{
i = 0;
foreach (string dev in cmbDate.Items)
{
if (dev == dataRead[12].ToString())
{
cmbDate.SelectedIndex = i;
break;
}
i++;
}
rdbnDateAndTime.Checked = true;
panel5.Enabled = true;//Date
if (dataRead[13].ToString() == "" || dataRead[13].ToString() == string.Empty || dataRead[13].ToString() == null)
{
i = 0;
foreach (string inti in cmbIn.Items)
{
if (inti == dataRead[14].ToString())
{
cmbIn.SelectedIndex = i;
break;
}
i++;
}
i = 0;
foreach (string inou in cmbOut.Items)
{
if (inou == dataRead[15].ToString())
{
cmbOut.SelectedIndex = i;
break;
}
i++;
}
rdbnInAndOut.Checked = true;
panel3.Enabled = false;//inout
panel4.Enabled = true;//in
panel7.Enabled = true;//out
}
else
{
i = 0;
foreach (string inout in cmbInOut.Items)
{
if (inout == dataRead[13].ToString())
{
cmbInOut.SelectedIndex = i;
break;
}
i++;
}
rdbnInOut.Checked = true;
panel3.Enabled = true;//inout
panel2.Enabled = true;//emp
panel9.Enabled = true;//device
}
}
else
{
i = 0;
foreach (string inti in cmbIn.Items)
{
if (inti == dataRead[10].ToString())
{
cmbIn.SelectedIndex = i;
break;
}
i++;
}
i = 0;
foreach (string tiou in cmbOut.Items)
{
if (tiou == dataRead[11].ToString())
{
cmbOut.SelectedIndex = i;
break;
}
i++;
}
rdbnDateTime.Checked = true;
rdbnInAndOut.Checked = true;
panel5.Enabled = false;//Date
panel3.Enabled = false;//inout
panel4.Enabled = true;//in
panel7.Enabled = true;//out
}
}
else
{
i = 0;
foreach (string tiou in cmbInOut.Items)
{
if (tiou == dataRead[9].ToString())
{
cmbInOut.SelectedIndex = i;
break;
}
i++;
}
rdbnDateTime.Checked = true;
rdbnInOut.Checked = true;
panel4.Enabled = false;//in
panel5.Enabled = false;//Date
panel7.Enabled = false;//out
panel3.Enabled = true;//inout
}
}
dataRead.Dispose();
dataRead.Close();
sqlConnection1.Dispose();
sqlConnection1.Close();
}
/// <summary>
/// Migrates the data in a membership database from ASP.NET 2.0 to ASP.NET 4.0 compatibility
/// by copying the data from the old database to a new database
/// </summary>
/// <param name="oldConnStr"></param>
/// <param name="newConnStr"></param>
public void MigrateMembershipDatabaseToAspNet40(string oldConnStr, string newConnStr)
{
if (String.IsNullOrWhiteSpace(oldConnStr))
throw new ArgumentException("Parameter cannot be null or empty", "oldConnStr");
if (String.IsNullOrWhiteSpace(newConnStr))
throw new ArgumentException("Parameter cannot be null or empty", "newConnStr");
SqlCeConnection oldConn = new SqlCeConnection(oldConnStr);
SqlCeConnection newConn = new SqlCeConnection(newConnStr);
SqlCeTransaction newTrans = null;
SqlCeCommand oldCmd = null;
SqlCeCommand newCmd = null;
System.Collections.Generic.Dictionary<String, Guid> apps = new System.Collections.Generic.Dictionary<String, Guid>();
System.Collections.Generic.Dictionary<String, Guid> roles = new System.Collections.Generic.Dictionary<String, Guid>();
try
{
oldConn.Open();
newConn.Open();
newTrans = newConn.BeginTransaction();
/**********************************************************************************
* aspnet_Applications
*********************************************************************************/
oldCmd = new SqlCeCommand("SELECT DISTINCT ApplicationName FROM aspnet_Users", oldConn);
using (SqlCeDataReader reader = oldCmd.ExecuteReader())
{
while (reader.Read())
{
string name = reader.GetString(0);
Guid id = Guid.NewGuid();
newCmd = new SqlCeCommand(@"
SELECT ApplicationId FROM aspnet_Applications
WHERE ApplicationName = @ApplicationName", newConn);
newCmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = name;
object value = newCmd.ExecuteScalar();
if (value != null && value != DBNull.Value)
{
apps.Add(name, (Guid)value);
}
else
{
apps.Add(name, id);
newCmd = new SqlCeCommand(
@"INSERT INTO aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName, Description)
VALUES (@ApplicationId, @ApplicationName, @LoweredApplicationName, NULL)", newConn, newTrans);
newCmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = id;
newCmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = name;
newCmd.Parameters.Add("@LoweredApplicationName", SqlDbType.NVarChar, 256).Value = name.ToLowerInvariant();
newCmd.ExecuteNonQuery();
}
}
}
/**********************************************************************************
* aspnet_Users
*********************************************************************************/
oldCmd = new SqlCeCommand(
@"SELECT PKID, ApplicationName, Username, LastActivityDate
FROM aspnet_Users", oldConn);
using (SqlCeDataReader reader = oldCmd.ExecuteReader())
{
while (reader.Read())
{
Guid userId = reader.GetGuid(0);
string applicationName = reader.GetString(1);
string userName = reader.GetString(2);
DateTime lastActivityDate = reader.GetDateTime(3);
newCmd = new SqlCeCommand(
@"INSERT INTO [aspnet_Users]
([ApplicationId]
,[UserId]
,[UserName]
,[LoweredUserName]
,[IsAnonymous]
,[LastActivityDate])
VALUES
(@ApplicationId
,@UserId
,@UserName
,@LoweredUserName
,@IsAnonymous
,@LastActivityDate)", newConn, newTrans);
newCmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = apps[applicationName];
newCmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = userId;
newCmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = userName;
newCmd.Parameters.Add("@LoweredUserName", SqlDbType.NVarChar, 256).Value = userName.ToLowerInvariant();
newCmd.Parameters.Add("@IsAnonymous", SqlDbType.Bit).Value = false;
newCmd.Parameters.Add("@LastActivityDate", SqlDbType.DateTime).Value = lastActivityDate;
newCmd.ExecuteNonQuery();
}
}
/**********************************************************************************
* aspnet_Membership
*********************************************************************************/
oldCmd = new SqlCeCommand(
@"SELECT ApplicationName, PKID, Password, Email, PasswordQuestion, PasswordAnswer,
IsApproved, IsLockedOut, CreationDate, LastLoginDate, LastPasswordChangedDate, LastLockedOutDate,
FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart, Comment
FROM aspnet_Users", oldConn);
using (SqlCeDataReader reader = oldCmd.ExecuteReader())
{
while (reader.Read())
{
string applicationName = reader.GetString(0);
Guid userId = reader.GetGuid(1);
string password = reader.GetString(2);
string email = reader.GetString(3);
string passwordQuestion = reader.GetString(4);
string passwordAnswer = reader.GetString(5);
bool isApproved = reader.GetBoolean(6);
bool isLockedOut = reader.GetBoolean(7);
DateTime creationDate = reader.GetDateTime(8);
DateTime lastLoginDate = reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.GetDateTime(10);
DateTime lastLockedOutDate = reader.GetDateTime(11);
int failedPasswordAttemptCount = reader.GetInt32(12);
DateTime failedPasswordAttemptWindowsStart = reader.GetDateTime(13);
int failedPasswordAnswerAttemptCount = reader.GetInt32(14);
DateTime failedPasswordAnswerAttemptWindowStart = reader.GetDateTime(15);
string comment = reader.GetString(16);
newCmd = new SqlCeCommand(
@"INSERT INTO [aspnet_Membership]
([ApplicationId]
,[UserId]
,[Password]
,[PasswordFormat]
,[PasswordSalt]
,[Email]
,[LoweredEmail]
,[PasswordQuestion]
,[PasswordAnswer]
,[IsApproved]
,[IsLockedOut]
,[CreateDate]
,[LastLoginDate]
,[LastPasswordChangedDate]
,[LastLockoutDate]
,[FailedPasswordAttemptCount]
,[FailedPasswordAttemptWindowStart]
,[FailedPasswordAnswerAttemptCount]
,[FailedPasswordAnswerAttemptWindowStart]
,[Comment])
VALUES
(@ApplicationId
,@UserId
,@Password
,@PasswordFormat
,@PasswordSalt
,@Email
,@LoweredEmail
,@PasswordQuestion
,@PasswordAnswer
,@IsApproved
,@IsLockedOut
,@CreateDate
,@LastLoginDate
,@LastPasswordChangedDate
,@LastLockoutDate
,@FailedPasswordAttemptCount
,@FailedPasswordAttemptWindowStart
,@FailedPasswordAnswerAttemptCount
,@FailedPasswordAnswerAttemptWindowStart
,@Comment)", newConn, newTrans);
newCmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = apps[applicationName];
newCmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = userId;
newCmd.Parameters.Add("@Password", SqlDbType.NVarChar, 128).Value = password;
newCmd.Parameters.Add("@PasswordFormat", SqlDbType.Int).Value = PasswordFormat.GetHashCode();
newCmd.Parameters.Add("@PasswordSalt", SqlDbType.NVarChar, 128).Value =
(PasswordFormat == MembershipPasswordFormat.Encrypted) ? String.Empty : encryptionKey;
newCmd.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = email;
newCmd.Parameters.Add("@LoweredEmail", SqlDbType.NVarChar, 256).Value = email.ToLowerInvariant();
newCmd.Parameters.Add("@PasswordQuestion", SqlDbType.NVarChar, 256).Value = passwordQuestion;
newCmd.Parameters.Add("@PasswordAnswer", SqlDbType.NVarChar, 128).Value = passwordAnswer;
newCmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = isApproved;
newCmd.Parameters.Add("@IsLockedOut", SqlDbType.Bit).Value = isLockedOut;
newCmd.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = creationDate;
newCmd.Parameters.Add("@LastLoginDate", SqlDbType.DateTime).Value = lastLoginDate;
newCmd.Parameters.Add("@LastPasswordChangedDate", SqlDbType.DateTime).Value = lastPasswordChangedDate;
newCmd.Parameters.Add("@LastLockoutDate", SqlDbType.DateTime).Value = lastLockedOutDate;
newCmd.Parameters.Add("@FailedPasswordAttemptCount", SqlDbType.Int).Value = failedPasswordAttemptCount;
newCmd.Parameters.Add("@FailedPasswordAttemptWindowStart", SqlDbType.DateTime).Value = failedPasswordAttemptWindowsStart;
newCmd.Parameters.Add("@FailedPasswordAnswerAttemptCount", SqlDbType.Int).Value = failedPasswordAnswerAttemptCount;
newCmd.Parameters.Add("@FailedPasswordAnswerAttemptWindowStart", SqlDbType.DateTime).Value = failedPasswordAnswerAttemptWindowStart;
newCmd.Parameters.Add("@Comment", SqlDbType.NText).Value = comment;
newCmd.ExecuteNonQuery();
}
}
/**********************************************************************************
* aspnet_Roles
*********************************************************************************/
oldCmd = new SqlCeCommand(@"SELECT Rolename, ApplicationName FROM aspnet_Roles", oldConn);
using (SqlCeDataReader reader = oldCmd.ExecuteReader())
{
while (reader.Read())
{
string roleName = reader.GetString(0);
string applicationName = reader.GetString(1);
Guid roleId = Guid.NewGuid();
roles.Add(applicationName + ":" + roleName, roleId);
newCmd = new SqlCeCommand(
@"INSERT INTO apsnet_Roles (ApplicationId, RoleId, RoleName, LoweredRoleName, Description)
VALUES (@ApplicationId, @RoleId @RoleName, @LoweredRoleName, NULL)", newConn, newTrans);
newCmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = apps[applicationName];
newCmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = roleId;
newCmd.Parameters.Add("@RoleName", SqlDbType.NVarChar, 256).Value = roleName;
newCmd.Parameters.Add("@LoweredRoleName", SqlDbType.NVarChar, 256).Value = roleName.ToLowerInvariant();
newCmd.ExecuteNonQuery();
}
}
/**********************************************************************************
* aspnet_UsersInRoles
*********************************************************************************/
oldCmd = new SqlCeCommand(
@"SELECT U.PKID, UR.Rolename, UR.ApplicationName
FROM aspnet_UsersInRoles UR
INNER JOIN aspnet_Users U ON UR.Username = U.UserName
AND UR.ApplicationName = U.ApplicationName", oldConn);
using (SqlCeDataReader reader = oldCmd.ExecuteReader())
{
while (reader.Read())
{
Guid userId = reader.GetGuid(0);
string roleName = reader.GetString(1);
string applicationName = reader.GetString(2);
newCmd = new SqlCeCommand(
@"INSERT INTO aspnet_UsersInRoles (UserId, RoleId)
VALUES (@UserId, @RoleId)", newConn, newTrans);
newCmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = userId;
newCmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = roles[applicationName + ":" + roleName];
newCmd.ExecuteNonQuery();
}
}
newTrans.Commit();
}
catch (Exception ex)
{
if (newTrans != null)
newTrans.Rollback();
throw ex;
}
finally
{
if (oldCmd != null)
oldCmd.Dispose();
oldConn.Dispose();
newConn.Dispose();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
btnNew.Enabled = false;
btnSave.Enabled = false;
lbError.Visible = false;
dataSourcePath = "Data Source = " + Application.StartupPath + @"\DeviceData.sdf";
SqlCeConnection sqlConnection1 = new SqlCeConnection();
sqlConnection1.ConnectionString = dataSourcePath;
if (txtDevIp.Text != "" && txtDevNo.Text != "" && txtDevPort.Text != "")
{
lbError.Visible = true;
lbError.Text = "Please wait, Testing device Connection before saving";
Application.DoEvents();
if (axCZKEM1.Connect_Net(txtDevIp.Text.Trim(), Convert.ToInt32(txtDevPort.Text.Trim())))
{
//if (axCZKEM1.ReadGeneralLogData(Convert.ToInt32(txtDevNo.Text)))
//{
lbError.Visible = false;
lbError.Text = "";
Application.DoEvents();
SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
if (newOld != "edit")
{
SqlCeDataReader SQLRD;
cmd.CommandText = "SELECT * FROM DeviceData WHERE DEVICE_NUM=" + txtDevNo.Text.Trim() + " OR DEVICE_IP='" + txtDevIp.Text.Trim() + "'";
SQLRD = cmd.ExecuteReader();
int v = 0;
while (SQLRD.Read())
{
v++;
}
SQLRD.Close();
if (v == 0)
{
try
{
cmd.CommandText = "INSERT INTO DeviceData VALUES(" + txtDevNo.Text.Trim() + ",'" + txtDevIp.Text.Trim() + "','" + txtDevPort.Text.Trim() + "')";
cmd.ExecuteNonQuery();
}
catch
{ }
}
else
{
lbError.Visible = true;
lbError.Text = "Already entered the details for this Device IP or Mechine number";
}
}
else
{
string mech = Global.selMech.ToString();
if (mech != txtDevNo.Text || Global.selMechIp != txtDevIp.Text || Global.selMechPort != txtDevPort.Text)
{
SqlCeDataReader SQLRD1;
cmd.CommandText = "SELECT * FROM DeviceData WHERE DEVICE_NUM=" + txtDevNo.Text.Trim() + " AND DEVICE_IP='" + txtDevIp.Text.Trim() + "' AND DEVICE_PORT='" + txtDevPort.Text.Trim() + "'";
SQLRD1 = cmd.ExecuteReader();
int v = 0;
while (SQLRD1.Read())
{
v++;
}
SQLRD1.Close();
if (v == 0)
{
try
{
cmd.CommandText = "UPDATE DeviceData SET DEVICE_NUM=" + txtDevNo.Text.Trim() + ", DEVICE_IP='" + txtDevIp.Text.Trim() + "', DEVICE_PORT='" + txtDevPort.Text.Trim() + "' WHERE DEVICE_NUM=" + selectedMech;
cmd.ExecuteNonQuery();
lbError.Visible = true;
lbError.Text = "Details of Device IP : " + txtDevIp.Text + " Saved";
}
catch
{ }
}
else
{
lbError.Visible = true;
lbError.Text = "Already exist same details for this Device IP : " + txtDevIp.Text;
}
}
else
{
lbError.Visible = true;
lbError.Text = "Data not Changed ";
}
btnEdit.Enabled = false;
}
btnSave.Enabled = false;
//}
//else
//{
// lbError.Visible = true;
// lbError.Text = "Enter Correct Device Number";
// txtDevNo.SelectAll();
// txtDevNo.Focus();
//}
}
else
{
lbError.Visible = true;
//lbError.Text = "Enter Correct Device Ip and Port";
lbError.Text = "Unable to connect device, enter correct device IP";
txtDevIp.SelectAll();
return;
}
axCZKEM1.Disconnect();
}
else
{
if (newOld != "edit")
{
if (txtDevNo.Text == "")
{
lbError.Visible = true;
lbError.Text = "Enter Device Number";
txtDevNo.Focus();
}
else if (txtDevPort.Text == "")
{
lbError.Visible = true;
lbError.Text = "Enter Device Port Number";
txtDevPort.Focus();
}
else if (txtDevIp.Text == "")
{
lbError.Visible = true;
lbError.Text = "Enter Device IP";
txtDevIp.Focus();
}
}
else
{
lbError.Visible = true;
lbError.Text = "To Edit, First select details from the table";
btnSave.Enabled = false;
}
}
sqlConnection1.Dispose();
sqlConnection1.Close();
btnNew.Enabled = true;
btnSave.Enabled = true;
fnGetIpsFronTable();
}
public static string updateStudentRecord(infoStudent editedStudent, Int32 currentAccNum)
{
SqlCeConnection sqlConnection = new SqlCeConnection(connectionString);
sqlConnection.Open();
string response = "";
//AccNum, fName, lastname, lastname2, grade, discount
SqlCeCommand updateCommand = new SqlCeCommand("UPDATE Students SET Account_Num = @AccN, First_Name = @fName, Last_Name = @lName, Last_Name_2 = @lName2," +
"[Group] = @gNum, School_Level = @sLevel, Discount = @disc, Pay_Type = @payType WHERE Account_Num = @currentStu", sqlConnection);
updateCommand.Parameters.AddWithValue("@AccN", editedStudent.studentID);
updateCommand.Parameters.AddWithValue("@fName", editedStudent.studentFistName);
updateCommand.Parameters.AddWithValue("@lName", editedStudent.studentLastName);
updateCommand.Parameters.AddWithValue("@lName2", editedStudent.studentLastName2);
updateCommand.Parameters.AddWithValue("@gNum", editedStudent.studentGroup);
updateCommand.Parameters.AddWithValue("@sLevel", editedStudent.studentLevel);
updateCommand.Parameters.AddWithValue("@disc", editedStudent.paymentDiscount);
updateCommand.Parameters.AddWithValue("@payType", editedStudent.paymentType);
updateCommand.Parameters.AddWithValue("@currentStu", currentAccNum);
updateCommand.CommandType = System.Data.CommandType.Text;
try
{
updateCommand.ExecuteNonQuery();
response = "OK";
}
catch (Exception e)
{
throw e;
}
sqlConnection.Close();
sqlConnection.Dispose();
return response;
}
public override void CreateSyncRepository()
{
string connectionString = "Data Source=\"" + _DbPath + "\";Max Database Size=128;Default Lock Escalation=100;";
IDbConnection conn = new SqlCeConnection(connectionString);
conn.Open();
IDbTransaction t = conn.BeginTransaction();
IDbCommand com = conn.CreateCommand();
com.Transaction = t;
StringBuilder createSyncItem = new StringBuilder();
createSyncItem.Append("CREATE TABLE SyncItem").
Append("(SyncID INT PRIMARY KEY IDENTITY, SyncFK INT, ClassID nvarchar(255), HashCode nvarchar(32), ").
Append("SyncGuid UNIQUEIDENTIFIER, RowGuid UNIQUEIDENTIFIER)");
com.CommandText = createSyncItem.ToString();
com.ExecuteNonQuery();
StringBuilder createFieldState = new StringBuilder();
createFieldState.Append("CREATE TABLE FieldState").
Append("(SyncFK INT, FieldName nvarchar(255), HashCode nvarchar(32), ").
Append("RowGuid UNIQUEIDENTIFIER, PRIMARY KEY (SyncFK, FieldName))");
com.CommandText = createFieldState.ToString();
com.ExecuteNonQuery();
t.Commit();
conn.Close();
conn.Dispose();
}
private void btnSave_Click(object sender, EventArgs e)
{
lbError.Visible = false;
Application.DoEvents();
dataSourcePath = "Data Source = " + Application.StartupPath + @"\DeviceData.sdf";
SqlCeConnection sqlConnection1 = new SqlCeConnection();
sqlConnection1.ConnectionString = dataSourcePath;
if (cmbDeviceIp.SelectedIndex != 0 && txtDeviceURL.Text != "")
{
frmBMDeviceMain frm = new frmBMDeviceMain();
if (frm.fnGetDBValues(txtDeviceURL.Text.Trim()))
{
SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
string url = @txtDeviceURL.Text;
SqlCeDataReader SQLRD;
cmd.CommandText = "SELECT * FROM DeviceURL WHERE DEV_IP='" + cmbDeviceIp.SelectedItem.ToString() + "' AND DEV_URL='" + url + "'";
SQLRD = cmd.ExecuteReader();
int v = 0;
while (SQLRD.Read())
{
v++;
}
SQLRD.Close();
if (v == 0)
{
if (StrEdit != "edit")
{
try
{
cmd.CommandText = "INSERT INTO DeviceURL (DEV_IP, DEV_URL) VALUES('" + cmbDeviceIp.SelectedItem.ToString() + "', '" + url + "')";
cmd.ExecuteNonQuery();
lbError.Visible = true;
lbError.Text = "Device IP and URL Maped Successfully";
btnSave.Enabled = false;
}
catch
{ }
}
else if (StrEdit == "edit")
{
try
{
cmd.CommandText = "UPDATE DeviceURL SET DEV_IP='" + cmbDeviceIp.SelectedItem.ToString() + "',DEV_URL='" + url + "'";
cmd.ExecuteNonQuery();
lbError.Visible = true;
lbError.Text = "Device IP and URL Maping Updated Successfully";
btnSave.Enabled = false;
}
catch
{ }
}
}
else
{
lbError.Visible = true;
lbError.Text = "Already exists this details";
}
frmDeviceURL_Load(null, null);
}
else
{
lbError.Visible = true;
lbError.Text = "Enter Correct SPP Web URL";
txtDeviceURL.SelectAll();
txtDeviceURL.Focus();
}
}
else
{
cmbDeviceIp.Enabled = true;
txtDeviceURL.Enabled = true;
if (cmbDeviceIp.SelectedIndex == 0)
{
lbError.Visible = true;
lbError.Text = "Select Device IP";
cmbDeviceIp.Focus();
}
else if (txtDeviceURL.Text == "")
{
lbError.Visible = true;
lbError.Text = "Enter Web URL";
txtDeviceURL.Focus();
}
}
sqlConnection1.Dispose();
sqlConnection1.Close();
}
/// <summary>
/// Used to fill DataViewGrids
/// </summary>
/// <param name="query">The SQL query that will fill the DataViewGrid</param>
/// <returns>A DataTable to fill the grid</returns>
public static DataTable dtFill(string query)
{
SqlCeConnection sqlConnection = new SqlCeConnection(connectionString);
//Open connection and execute command
sqlConnection.Open();
SqlCeDataAdapter sqlAdapter = new SqlCeDataAdapter(query, sqlConnection);
//Create and fill DataTable
DataTable dtQuery = new DataTable();
sqlAdapter.Fill(dtQuery);
//Close connection
sqlConnection.Close();
sqlConnection.Dispose();
//Returns DataTable
return dtQuery;
}
/// <summary>
/// Creates new record that represents payment of a student
/// </summary>
/// <param name="nPayment"></param>
public static void InsertPayment(infoPayment nPayment)
{
SqlCeConnection sqlConnection = new SqlCeConnection(connectionString);
sqlConnection.Open();
SqlCeCommand newPaymentCommand = new SqlCeCommand("INSERT INTO Payment (Folio, Amount, Date, Completed, Student_ID, Concept_ID) " +
"VALUES (@pFolio, @pAmount, @pDate, @pComplete, @pStudentID, @pConceptID)", sqlConnection);
newPaymentCommand.Parameters.AddWithValue("@pFolio", nPayment.paymentFolio);
newPaymentCommand.Parameters.AddWithValue("@pAmount", nPayment.payedAmount);
newPaymentCommand.Parameters.AddWithValue("@pDate", nPayment.paymentDate);
newPaymentCommand.Parameters.AddWithValue("@pComplete", nPayment.paymentComplete);
newPaymentCommand.Parameters.AddWithValue("@pStudentID", nPayment.studentID);
newPaymentCommand.Parameters.AddWithValue("@pConceptID", nPayment.conceptID);
newPaymentCommand.CommandType = System.Data.CommandType.Text;
try
{
newPaymentCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
sqlConnection.Close();
sqlConnection.Dispose();
}
}
private void ValidateConnection()
{
var connectionString = string.Format("Data Source={0}", m_storePath);
if (m_firstConnection)
{
var connection = new SqlCeConnection(connectionString);
// see if we need a password
try
{
connection.Open();
}
catch (SqlCeException ex)
{
if (ex.NativeError == 25028)
{
// a password is required.
var dialog = new GetPasswordDialog();
if (dialog.ShowDialog() == DialogResult.OK)
{
connectionString += (";password=" + dialog.Password);
connection.ConnectionString = connectionString;
try
{
connection.Open();
}
catch (SqlCeException exi)
{
if (exi.NativeError == 25028)
{
throw new InvalidPasswordException();
}
throw;
}
}
else
{
throw new InvalidPasswordException();
}
}
else
{
throw;
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
m_connectionString = connectionString;
m_firstConnection = false;
}
}
/// <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();
}
