// Return DataTable
private DataTable ExecuteDataTable(string commandText)
{
try
{
//Access the database and get the NorthWind
SqlCeConnection conn = new SqlCeConnection();
if (conn.ServerVersion.StartsWith("3.5"))
{
conn.ConnectionString = "Data Source = " + ResolveApplicationDataPath("NorthwindIO_3.5.sdf", "Data");
}
else
{
conn.ConnectionString = "Data Source = " + ResolveApplicationDataPath("NorthwindIO.sdf", "Data");
}
conn.Open();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(commandText, conn);
DataSet set = new DataSet();
adapter.Fill(set);
adapter.Dispose();
conn.Close();
DataTable table = new DataTable();
table = set.Tables[0];
return(table);
}
finally
{
}
}
public void SelectUser(int x)
{
try
{
byte count = 0;
SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Users", cKoneksi.Con);
SqlCeDataReader dr;
if (cKoneksi.Con.State == ConnectionState.Closed) { cKoneksi.Con.Open(); }
dr = cmd.ExecuteReader();
if (dr.Read()) { count = 1; } else { count = 0; }
dr.Close(); cmd.Dispose(); if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
if (count != 0)
{
DataSet ds = new DataSet();
SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM Users", cKoneksi.Con);
da.Fill(ds, "Users");
textBoxUser.Text = ds.Tables["Users"].Rows[x][0].ToString();
textBoxPass.Text = ds.Tables["Users"].Rows[x][1].ToString();
checkBoxTP.Checked = Convert.ToBoolean(ds.Tables["Users"].Rows[x][2]);
checkBoxTPK.Checked = Convert.ToBoolean(ds.Tables["Users"].Rows[x][3]);
ds.Dispose();
da.Dispose();
}
else
{
MessageBox.Show("Data User Kosong", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
buttonClose.Focus();
}
}
catch (SqlCeException ex)
{
MessageBox.Show(cError.ComposeSqlErrorMessage(ex), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
}
}
public void syncTime()
{
string cmd = "select GetDATE()";
sqldbCon.Open();
DateTime dts = System.DateTime.Now;
SqlDataAdapter da = new SqlDataAdapter(cmd, sqldbCon);
DataSet ds = new DataSet();
da.Fill(ds);
DateTime dt = Convert.ToDateTime(ds.Tables[0].Rows[0].ItemArray[0]);
UpdateCEMobileLocalTime.classSyncTime.SetSystemDateTime(dt);
sqldbCon.Close();
classLog.writeLog("Message @:Time Sync Completed");
//classLog.TaskSyncLog("Time Sync Completed", fname);
string cmds = "select ReaderId from ReaderConfig ";
SqlCeDataAdapter das = new SqlCeDataAdapter(cmds, localdbCon);
DataSet dss = new DataSet();
das.Fill(dss);
ReaderId = dss.Tables[0].Rows[0].ItemArray[0].ToString();
dss.Dispose();
das.Dispose();
VerssionAlert();
TruckMasterSyncValidation();
}
public DataTable Load(string sql)
{
UtilLocalDB objSqlCeServerDAL = GetInstance(connString);
objSqlCeServerDAL.Open();
SqlCeDataAdapter dAd = new SqlCeDataAdapter(sql, objSqlCeServerDAL.objSqlCeConnection);
dAd.SelectCommand.CommandType = CommandType.Text;
DataSet dSet = new DataSet();
try
{
dAd.Fill(dSet, "Tabela");
return(dSet.Tables["Tabela"]);
}
catch
{
throw;
}
finally
{
dSet.Dispose();
dAd.Dispose();
objSqlCeServerDAL.Dispose();
}
}
private void frmDetail_Load(object sender, EventArgs e)
{
myConnection = default(SqlCeConnection);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [ID],[Job],[ItemId],[Qty],[Unit],[CheckedDateTime],[CheckedBy],[Checked] FROM ["
+ storagePath.getStoreTable() + "] WHERE ID ='"
+ bm + "' and Job='" + jb + "' and ItemId = '" + item + "' and Checked='true'";
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(ds);
Adapter.Dispose();
if (ds.Tables[0].Rows.Count > 0)
{
//isCheck = true;
this.txtItem.Text = ds.Tables[0].Rows[0]["ItemId"].ToString();
this.txtCheckedDateTime.Text = ds.Tables[0].Rows[0]["CheckedDateTime"].ToString();
this.txtCheckedBy.Text = ds.Tables[0].Rows[0]["CheckedBy"].ToString();
}
else
{
// isCheck = false;
}
myCommand.Dispose();
dt = null;
myConnection.Close();
}
private DataTable GetQuestionsAndAnswersTable()
{
var answersDT = new DataTable();
using (SqlCeConnection conn = new SqlCeConnection(_connectionString))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT [Questions].[Type], [Questions].[Order], [Questions].[NumAnswers],
[Interview].[Interviewer_Id], [Interview].[StartTime], [Interview].[EndTime],
[Answers].[OpenEnded], [Answers].[CloseEnded], [Answers].[Question_Id]
FROM [Questions]
INNER JOIN [Answers]
ON [Answers].[Question_Id] = [Questions].[Id]
INNER JOIN [Interview]
ON [Answers].[Interview_Id] = [Interview].[Id]
WHERE [Interview_Id] = @InterviewId";
cmd.Parameters.Add(new SqlCeParameter("@InterviewId", _interviewId));
cmd.Prepare();
var da = new SqlCeDataAdapter(cmd);
da.Fill(answersDT);
da.Dispose();
}
return answersDT;
}
//==================BUSCAR INFORMAÇÔES NA BASE DE DADOS=======================
public DataTable EXE_READER(string query, List <SQLparametro> parametros = null)
{
//efetuar pesquisa no textbox 31º
//só o SELECT vai ser usado
DataTable dados = new DataTable();
adptador = new SqlCeDataAdapter(query, stringconexao);
adptador.SelectCommand.Parameters.Clear();//limpa os parametros que existe dentro do adptador
//executar a querry
try
{
//insere os parametros na query(caso exista para adicionar)
if (parametros != null)
{
foreach (SQLparametro Par in parametros)
{
adptador.SelectCommand.Parameters.AddWithValue(Par.parametro, Par.valor);
}
}
adptador.Fill(dados);
}
catch (Exception ex)//Exception apresenta um erro que ocorre durante a aplicação
//ex é a variavel aonde vai ser mostrado
{
MessageBox.Show("ERRO: " + ex.Message);//para mostrar o erro encontrado pelo Exception
//lembrando não coloque isso no código oficial pois o erro que aparece é apenas para quem esta programando ver e corrigir !!
}
adptador.Dispose();
return(dados);
}
public bool checkUsers(string user_nick, string user_password)
{
string str = "SELECT * FROM users WHERE user_nick = @user_nick AND user_password = @user_password";
try
{
conn.openDB();
sql.CommandText = str;
data = new SqlCeDataAdapter(str, conn.objConnection);
data.SelectCommand.Parameters.AddWithValue("@user_nick", user_nick);
data.SelectCommand.Parameters.AddWithValue("@user_password", user_password);
data.Fill(users);
data.Dispose();
conn.closeDB();
if (users.Rows.Count == 0)
{
return(getHas());
}
else
{
setHas(true);
}
}
catch (Exception e)
{
throw e;
}
return(getHas());
}
void Form1_Load(object sender, EventArgs e)
{
DataTable table = new DataTable();
//Access the database and get the NorthWind
#if !NETCORE
SqlCeConnection conn = new SqlCeConnection();
if (conn.ServerVersion.StartsWith("3.5"))
{
conn.ConnectionString = "Data Source = " + (@"..\..\Common\Data\" + "NorthwindIO_3.5.sdf");
}
else
{
conn.ConnectionString = "Data Source = " + (@"..\..\Common\Data\" + "NorthwindIO.sdf");
}
conn.Open();
SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT CompanyName, ContactName, City, Country, CustomerID FROM Customers ORDER BY CompanyName ", conn);
adapter.Fill(table);
//Bind the Data Table with the MultiColumnBoundCombobox DataSource
this.comboBoxAdvSampleControl1.multiColumnBoundCombo.DataSource = table;
this.comboBoxAdvSampleControl1.propertyGrid1.SelectedObject = this.comboBoxAdvSampleControl1.multiColumnBoundCombo;
adapter.Dispose();
conn.Close();
#endif
}
private void InitializeData()
{
try
{
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
sqlCeConnection = new SqlCeConnection();
if (sqlCeConnection.ServerVersion.StartsWith("3.5"))
{
sqlCeConnection.ConnectionString = "Data Source = " + ResolveApplicationDataPath("NorthwindIO_3.5.sdf");
}
else
{
sqlCeConnection.ConnectionString = "Data Source = " + ResolveApplicationDataPath("NorthwindIO.sdf");
}
sqlCeAdapter = new SqlCeDataAdapter("select OrderID from SyncOrders Order By OrderID", sqlCeConnection);
DataSet ds = new DataSet();
sqlCeAdapter.Fill(ds);
DataTable dt = ds.Tables[0];
ArrayList list = new ArrayList();
foreach (DataRow row in dt.Rows)
{
list.Add(row[0].ToString());
}
ViewData.Add("id", new SelectList(list));
sqlCeAdapter.Dispose();
sqlCeConnection.Close();
}
catch (Exception Ex)
{
// Shows the Message box with Exception message, if an exception is thrown.
this.Response.Write(Ex.Message);
}
}
private DataTable GetTestOrderTotals(int TestOrderId)
{
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
DataTable table = new DataTable();
SqlCeConnection conn = new SqlCeConnection();
if (conn.ServerVersion.StartsWith("3.5"))
{
conn.ConnectionString = "Data Source = " + dataPath + "..\\NorthwindIO_3.5.sdf";
}
else
{
conn.ConnectionString = "Data Source = " + dataPath + "..\\NorthwindIO.sdf";
}
conn.Open();
SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT * FROM SyncOrderTotals WHERE OrderId = " + TestOrderId, conn);
adapter.Fill(table);
adapter.Dispose();
conn.Close();
table.TableName = "OrderTotals";
return(table);
}
public Result ExecuteReaderQuery(string query, string connectionString)
{
var result = new Result();
if (!string.IsNullOrWhiteSpace(connectionString))
{
var connection = new SqlCeConnection(connectionString);
var adapter = new SqlCeDataAdapter(query, connection);
try
{
adapter.Fill(result.DataTable);
}
catch (Exception e)
{
result.Status = false;
result.Exception = e;
LogExtension.LogError("Exception on ExecuteReaderQuery", e, MethodBase.GetCurrentMethod(), " Query - " + query + " ConnectionString - " + connection);
}
finally
{
adapter.Dispose();
connection.Close();
}
}
else
{
var exception = new Exception("Invalid Connection string");
result.Status = false;
result.Exception = exception;
throw exception;
}
result.Status = true;
return(result);
}
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 bool checkNick(string user_nick)
{
bool exist = false;
string str = "SELECT * FROM users WHERE user_nick = @user_nick";
try
{
conn.openDB();
sql.CommandText = str;
data = new SqlCeDataAdapter(str, conn.objConnection);
data.SelectCommand.Parameters.AddWithValue("@user_nick", user_nick);
data.Fill(users);
data.Dispose();
conn.closeDB();
if (users.Rows.Count == 0)
{
return(exist);
}
else
{
exist = true;
}
}
catch (Exception e)
{
throw e;
}
return(exist);
}
public bool checkISBN(string v)
{
bool exists = false;
try
{
conn.openDB();
string query = "SELECT * FROM books_stoke WHERE book_isbn = '" + v + "'";
SqlCeCommand command = new SqlCeCommand();
command.CommandText = query;
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(query, conn.objConnection);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
dataAdapter.Dispose();
conn.closeDB();
if (dataTable.Rows.Count == 0)
{
exists = false;
}
else
{
exists = true;
}
}
catch (Exception e)
{
MessageBox.Show("Error has ocurred" + e);
}
return(exists);
}
public DataSet GetComuniFromSDF_V2(int indexRegione, int indexProvincia)
{
string query =
"SELECT[id_progressivo],[denominazione_in_italiano],[codice_catastale_del_comune], [sigla_automobilistica] FROM[comuni_2018] " +
" WHERE codice_provincia = " + indexProvincia.ToString() +
" ORDER BY denominazione_in_italiano ASC ";
SqlCeConnection conn = new SqlCeConnection(m_ConnectionString);
SqlCeCommand cmd = new SqlCeCommand(query, conn);
conn.Open();
DataSet ds = new DataSet();
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
da.Fill(ds);
conn.Close();
da.Dispose();
return(ds);
}
public DataSet GetProvinceFromSDF_V2(int indexRegione)
{
string query =
"SELECT DISTINCT [codice_regione],[codice_provincia], LTRIM(REPLACE([denominazione_citta_metropolitana] +[denominazione_provincia], '-', ' ')) as provincia FROM[comuni_2018] " +
" where codice_regione =" + indexRegione.ToString() + " ORDER BY provincia ASC";
SqlCeConnection conn = new SqlCeConnection(m_ConnectionString);
SqlCeCommand cmd = new SqlCeCommand(query, conn);
conn.Open();
DataSet ds = new DataSet();
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
da.Fill(ds);
conn.Close();
da.Dispose();
return(ds);
}
public Boolean checkStoredata()
{
bm = this.txtBom.Text.ToUpper();
Boolean isCheck = false;
myConnection = default(SqlCeConnection);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [ID] FROM [" + storagePath.getBomTable() + "] WHERE ID ='"
+ bm + "' ";
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(dt);
Adapter.Dispose();
if (dt.Rows.Count > 0)
{
isCheck = true;
}
else
{
isCheck = false;
}
myCommand.Dispose();
dt = null;
myConnection.Close();
return isCheck;
}
public void Close()
{
daSignal.Dispose();
daSession.Dispose();
daBitmap.Dispose();
connection.Close();
}
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);
}
private DataTable ExecuteDataTable(string commandText)
{
// Return DataTable
try
{
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
DataTable table = new DataTable();
SqlCeConnection conn = new SqlCeConnection();
if (conn.ServerVersion.StartsWith("3.5"))
{
conn.ConnectionString = "Data Source = " + dataPath + "..\\NorthwindIO_3.5.sdf";
}
else
{
conn.ConnectionString = "Data Source = " + dataPath + "..\\NorthwindIO.sdf";
}
conn.Open();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(commandText, conn);
adapter.Fill(table);
adapter.Dispose();
conn.Close();
return(table);
}
finally
{
}
}
/// <summary>
/// Close the Database Manager and Dispose all resources
/// </summary>
public void CloseAndDispose()
{
sqlConnection.Close();
sqlAdapter.Dispose();
sqlCommand.Dispose();
sqlConnection.Dispose();
instance = null;
}
private void FillTable(SqlCeConnection conn, string command, out DataTable table)
{
conn.Open();
table = new DataTable();
SqlCeDataAdapter da = new SqlCeDataAdapter(command, conn);
da.Fill(table);
da.Dispose();
}
public static void getDataSet()
{
DataSet ds = new System.Data.DataSet();
SqlCeConnection con = new SqlCeConnection(StringConexao);
SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM TBUsuarioLocal", con);
da.Fill(ds);
con.Close();
da.Dispose();
}
private void billrp_Load(object sender, EventArgs e)
{
try
{
string s = "#BILLNO-" + PaymentMain.com;
String nam = BillsMain.nam;
MessageBox.Show(s);
string q1 = "SELECT * FROM bill_detail where Ref='" + s + "'";
String q2 = "SELECT * from Bill_Tax where ref='" + s + "'";
String q3 = "SELECT * from vendor where [Shop_name]='" + nam + "'";
SqlCeConnection con = new SqlCeConnection(Properties.Settings.Default.conne);
SqlCeCommand cmd1 = new SqlCeCommand();
SqlCeDataAdapter sad = new SqlCeDataAdapter();
DataSet dt = new DataSet();
con.Open();
cmd1.Connection = con;
cmd1.CommandText = q1;
sad.SelectCommand = cmd1;
sad.Fill(dt, "Table[0]");
cmd1.CommandText = q2;
sad.SelectCommand = cmd1;
sad.Fill(dt, "Table[1]");
cmd1.CommandText = q3;
sad.SelectCommand = cmd1;
sad.Fill(dt, "Table[2]");
sad.Dispose();
cmd1.Dispose();
billrpt b = new billrpt();
// billrpt1 b = new billrpt1();
b.Database.Tables["bill_detail"].SetDataSource(dt.Tables[0]);
b.Database.Tables["Bill_Tax"].SetDataSource(dt.Tables[1]);
b.Database.Tables["vendor"].SetDataSource(dt.Tables[2]);
crystalReportViewer1.ReportSource = null;
crystalReportViewer1.ReportSource = b;
//ReportDocument crystal = new ReportDocument();
//string path = System.IO.Path.GetDirectoryName(Application.ExecutablePath);
//path = System.IO.Directory.GetParent(System.IO.Directory.GetParent(path).ToString()).ToString();
//path = System.IO.Path.Combine(path, "billrpt.rpt");
//crystal.Load("C:\\VensarkBilling\\rest\\billrpt.rpt");
//crystal.SetDataSource(dt);
//crystalReportViewer1.ReportSource = crystal;
//crystalReportViewer1.Refresh();
}
catch (Exception exc1)
{
MessageBox.Show("" + exc1);
}
}
private void GetDataFromSLQCE()
{
// Create a datatable if we don't have one yet
if (zadatakDataTable == null)
{
zadatakDataTable = new DataTable();
}
// Create a dataadapter to get the current data
SqlCeDataAdapter zadatakDataAdapter = new SqlCeDataAdapter("Select vrijeme, naziv, posao from raspored order by vrijeme", new SqlCeConnection(sqlCEConnectionString));
zadatakDataTable.Clear();
zadatakDataAdapter.Fill(zadatakDataTable);
zadatakDataAdapter.Dispose();
}
/// <summary>
/// Executes the dataset from a populated Command object.
/// </summary>
/// <param name="cmd">Fully populated SqlCeCommand</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataset(SqlCeCommand cmd)
{
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
var ds = new DataSet();
var da = new SqlCeDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Connection.Close();
cmd.Dispose();
return(ds);
}
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 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 DataSet Execute(string tableName, string sql, Dictionary <string, object> AParams)
{
// ClassLibrary.WaitCursor.Show(true);
DataSet _Result = new DataSet();
try
{
SqlCeDataAdapter _Adapter = null;
if (null == AParams)
{
_Adapter = GetAdapter(sql);
}
else
{
SqlCeCommand _Cmd = GetCommand();
_Cmd.CommandText = sql;
AddCommandParameters(_Cmd, AParams);
_Adapter = GetAdapter(_Cmd);
}
/*------------------------------------------------------------------------------------------
* TODO : Check here if next statement can be used, somehow without indexes the fill method
* consumes too much time.
* SqlCeResultSet _ResultSet = _Cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
* ------------------------------------------------------------------------------------------*/
_Adapter.Fill(_Result, tableName);
_Adapter.Dispose();
_Adapter = null;
}
catch (Exception ex)
{
//if (!this.IntegrityCheck)
//{
// ClassLibrary.Global.HandleError(null, ex);
//}
throw ex;
}
// ClassLibrary.WaitCursor.Show(false);
return(_Result);
}
public static DataTable FillDataTable(string Query, DataTable Table)
{
OpenConnection();
try
{
objDataAdapter = new SqlCeDataAdapter(Query, objConnection);
objDataAdapter.Fill(Table);
objDataAdapter.Dispose();
CloseConnection();
return(Table);
}
catch
{
return(null);
}
}
//===================EXEMPLO PARA SALVAR VARIOS DADOS==========================
//usado pelo programador
//38º
//2º metodo para atualizar a base de dados com os dados novos
public void GRAVAR_DATATABLE(DataTable dados)
{
//atualiza a informação na base de dados
SqlCeCommandBuilder CMD = new SqlCeCommandBuilder(adaptador_temp);
adaptador_temp.UpdateCommand = CMD.GetUpdateCommand();
try
{
adaptador_temp.Update(dados);
}
catch (Exception ex)
{
MessageBox.Show("ERRO: " + ex.Message);
}
adaptador_temp.Dispose();
}
public DataTable Get(int questionId)
{
using (SqlCeConnection conn = new SqlCeConnection(_connectionString))
{
conn.Open();
DataTable table = new DataTable();
var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Answers WHERE Interview_Id = @InterviewId AND Question_Id = @QuestionId";
cmd.Parameters.Add(new SqlCeParameter("@InterviewId", _interviewId));
cmd.Parameters.Add(new SqlCeParameter("@QuestionId", questionId));
cmd.Prepare();
var da = new SqlCeDataAdapter(cmd);
da.Fill(table);
da.Dispose();
return table;
}
}
public System.Data.DataSet ExecuteQuery(string Query, string[] Parameter, object[] Value)
{
SqlCeCommand cmd = new SqlCeCommand();
SqlCeDataAdapter adp = new SqlCeDataAdapter();
System.Data.DataSet ds = new System.Data.DataSet();
try
{
if (this.conn == null)
{
this.Connect();
}
while (this.conn.State != System.Data.ConnectionState.Open)
{
this.conn.Open();
}
cmd.Connection = this.conn;
cmd.CommandText = Query;
cmd.CommandTimeout = 0;
cmd.CommandType = System.Data.CommandType.Text;
this.Parameterize(cmd, Parameter, Value);
adp.SelectCommand = cmd;
adp.Fill(ds);
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
adp.Dispose();
cmd = null;
adp = null;
this.conn.Close();
}
}
public Boolean checkStoredata(string _item)
{
Boolean isCheck = false;
myConnection = default(SqlCeConnection);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [ID] FROM [" + storagePath.getStoreTable() + "] WHERE ID ='"
+ bm+"' and Job='"+jb+"' and ItemId = '"+_item+"' and Checked='true'" ;
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(dt);
Adapter.Dispose();
if (dt.Rows.Count > 0)
{
isCheck = true;
}
else
{
isCheck = false;
}
myCommand.Dispose();
dt = null;
myConnection.Close();
return isCheck;
}
private void PopulateTheSchemaTables(string schemaQuery, DataTable[] tables)
{
string sqlQuery;
using (SqlCeConnection connection = new SqlCeConnection(m_ConnectionString))
{
connection.Open();
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
sqlQuery =
Utility.GetResource(Assembly.GetExecutingAssembly(),
"SumDataTierGenerator.SchemaExtractor.EmbeddedResources.SqlCeExtractDatabaseTables.sql");
DataTable tblTables = new DataTable("Tables");
adapter.SelectCommand = new SqlCeCommand(sqlQuery, connection);
adapter.Fill(tables[0]);
sqlQuery =
Utility.GetResource(Assembly.GetExecutingAssembly(),
"SumDataTierGenerator.SchemaExtractor.EmbeddedResources.SqlCeExtractDatabaseTableColumns.sql");
DataTable tblColumns = new DataTable("Columns");
adapter.SelectCommand.CommandText = sqlQuery;
adapter.Fill(tables[1]);
sqlQuery =
Utility.GetResource(Assembly.GetExecutingAssembly(),
"SumDataTierGenerator.SchemaExtractor.EmbeddedResources.SqlCeExtractDatabasePrimaryKeys.sql");
DataTable tblKeys = new DataTable("Keys");
adapter.SelectCommand.CommandText = sqlQuery;
adapter.Fill(tables[2]);
adapter.Dispose();
connection.Close();
}
}
/// <summary>
/// Private helper method that execute a SqlCeCommand (that returns a resultset) against the specified SqlCeTransaction and SqlCeConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// FillDataset(conn, trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new SqlCeParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid SqlCeConnection</param>
/// <param name="transaction">A valid SqlCeTransaction</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
private static void FillDataset(SqlCeConnection connection, SqlCeTransaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SqlCeParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( dataSet == null ) throw new ArgumentNullException( "dataSet" );
// Create a command and prepare it for execution
SqlCeCommand command = new SqlCeCommand();
bool mustCloseConnection = false;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
// Create the DataAdapter & DataSet
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(command);
try
{
// Add the table mappings specified by the user
if (tableNames != null && tableNames.Length > 0)
{
string tableName = "Table";
for (int index=0; index < tableNames.Length; index++)
{
if( tableNames[index] == null || tableNames[index].Length == 0 ) throw new ArgumentException( "The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames" );
dataAdapter.TableMappings.Add(tableName, tableNames[index]);
tableName += (index + 1).ToString();
}
}
// Fill the DataSet using default values for DataTable names, etc
dataAdapter.Fill(dataSet);
// Detach the SqlCeParameters from the command object, so they can be used again
command.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
}
finally
{
dataAdapter.Dispose();
}
}
/// <summary>
/// Execute a SqlCeCommand (that returns a resultset) against the specified SqlCeConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// string r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", new SqlCeParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid SqlCeConnection</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>An string containing the resultset generated by the command</returns>
public static string ExecuteXml(SqlCeConnection connection, CommandType commandType, string commandText, params SqlCeParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
bool mustCloseConnection = false;
// Create a command and prepare it for execution
SqlCeCommand cmd = new SqlCeCommand();
try
{
PrepareCommand(cmd, connection, (SqlCeTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Create the DataAdapter & DataSet
SqlCeDataAdapter obj_Adapter =new SqlCeDataAdapter (cmd);
DataSet ds=new DataSet();
ds.Locale =CultureInfo.InvariantCulture;
obj_Adapter.Fill(ds);
// Detach the SqlCeParameters from the command object, so they can be used again
cmd.Parameters.Clear();
string retval= ds.GetXml();
ds.Clear();
obj_Adapter.Dispose ();
return retval;
}
catch
{
if( mustCloseConnection )
connection.Close();
throw;
}
}
public void RunQueryTable(string query, ref DataTable myDataTable)
{
SqlCeDataAdapter DBAdapter = null;
try
{
using (SqlCeCommand command = Connection.CreateCommand())
{
command.CommandText = query;
DBAdapter = new SqlCeDataAdapter(query, Connection);
DBAdapter.Fill(myDataTable);
DBAdapter.Dispose();
}
}
catch (Exception e)
{
string test = e.Message;
}
}
private void FormMasterUser_Load(object sender, EventArgs e)
{
statusBar1.Text = "User : "******" | Id Hand Held : " + ClassUser.HandheldID;
try
{
DataSet ds = new DataSet();
string strSQL = "SELECT * FROM Users";
SqlCeDataAdapter da = new SqlCeDataAdapter(strSQL, cKoneksi.Con);
da.Fill(ds, "Users");
dataGridMasterUser.DataSource = ds.Tables[0];
DataGridTableStyle ts = new DataGridTableStyle();
dataGridMasterUser.TableStyles.Clear();
ts.MappingName = "Users";
DataGridTextBoxColumn col1 = new DataGridTextBoxColumn();
col1.HeaderText = "Username";
col1.MappingName = "Userid";
col1.Width = 70;
ts.GridColumnStyles.Add(col1);
DataGridTextBoxColumn col2 = new DataGridTextBoxColumn();
col2.HeaderText = "Password";
col2.MappingName = "Password";
col2.Width = 60;
ts.GridColumnStyles.Add(col2);
DataGridTextBoxColumn col3 = new DataGridTextBoxColumn();
col3.HeaderText = "TP";
col3.MappingName = "TP";
col3.Width = 38;
ts.GridColumnStyles.Add(col3);
DataGridTextBoxColumn col4 = new DataGridTextBoxColumn();
col4.HeaderText = "TPK";
col4.MappingName = "TPK";
col4.Width = 38;
ts.GridColumnStyles.Add(col4);
dataGridMasterUser.TableStyles.Add(ts);
dataGridMasterUser.Refresh();
ds.Tables.Clear();
da.Dispose();
ds.Dispose();
dataGridMasterUser.Enabled = true;
textBoxUser.Text = "";
textBoxUser.Enabled = false;
textBoxPass.Text = "";
textBoxPass.Enabled = false;
checkBoxTP.Enabled = false;
checkBoxTPK.Enabled = false;
buttonCancel.Enabled = false;
buttonSave.Enabled = false;
buttonInsert.Enabled = true;
buttonUpdate.Enabled = true;
buttonDelete.Enabled = true;
SelectUser(0);
}
catch (SqlCeException ex)
{
MessageBox.Show(cError.ComposeSqlErrorMessage(ex), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
}
}
public static bool GetConfigFromSqlCe(string filename, string password)
{
try
{
string strDataSource = @"Data Source=" + Path.Combine(Directory.GetCurrentDirectory(), filename) + ";Encrypt Database=True;Password="******";" +
@"File Mode=shared read;Persist Security Info = False;";
SqlCeConnection conn = new SqlCeConnection();
conn.ConnectionString = strDataSource;
SqlCeCommand selectCmd = conn.CreateCommand();
selectCmd.CommandText = "SELECT * FROM Config";
SqlCeDataAdapter adp = new SqlCeDataAdapter(selectCmd);
DataTable dt = new DataTable();
adp.Fill(dt);
adp.Dispose();
conn.Close();
Param.SqlCeConfig = new Hashtable();
for (int i = 0; i < dt.Rows.Count; i++)
{
Param.SqlCeConfig.Add(dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString());
}
return true;
}
catch (Exception ex)
{
MessageBox.Show("รหัสผ่านไม่ถูกต้อง กรุณาลองใหม่อีกครั้ง\n" + ex.Message, "มีข้อผิดพลาดเกิดขึ้น", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
public static DataTable SqlCeQuery(string sql)
{
string connStr = "Data Source=" + Param.SqlCeFile;
Param.SqlCeConnection = new SqlCeConnection(connStr);
if (Param.SqlCeConnection.State == ConnectionState.Closed)
{
Param.SqlCeConnection = new SqlCeConnection(connStr);
Param.SqlCeConnection.Open();
}
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
DataTable dt = new DataTable();
try
{
SqlCeDataAdapter adapter = new SqlCeDataAdapter(sql, Param.SqlCeConnection);
adapter.Fill(dt);
adapter.Dispose();
}
catch (Exception ex)
{
WriteErrorLog(ex.Message);
WriteErrorLog(ex.StackTrace);
}
finally
{
Param.SqlCeConnection.Close();
}
return dt;
}
public DataSet getData(string _item)
{
myConnection = default(SqlCeConnection);
DataSet dsNew = new DataSet();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [id],[item],[qty],[unit] FROM ["
+ storagePath.getBomTable() + "] WHERE id ='"
+ bm + "' and item='"+_item+"' ";
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(dsNew);
Adapter.Dispose();
myCommand.Dispose();
//dt = null;
myConnection.Close();
return dsNew;
}
public void BindDataGrid()
{
bm = bm.ToUpper();
jb = jb.ToUpper();
//MessageBox.Show("xx : "+strBom);
//string tot = "B1106488";
myConnection = default(SqlCeConnection);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [id], [item], [qty],[unit] FROM [" + tableName + "] WHERE id ='"
+ bm + "' and unit !='SEC' and item != 'ROYALTY STANDARD'";
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(ds);
Adapter.Dispose();
myConnection.Close();
DataTable myTable = new DataTable();
//Create new Column in DataTable
myTable.Columns.Add(new DataColumn("OK", typeof(System.String)));
myTable.Columns.Add(new DataColumn("Item", typeof(System.String)));
// myTable.Columns.Add(new DataColumn("Qty", typeof(System.String)));
// myTable.Columns.Add(new DataColumn("Unit", typeof(System.String)));
this.dgvData.TableStyles.Clear();
DataGridTableStyle tableStyle = new DataGridTableStyle();
tableStyle.MappingName = myTable.TableName;
foreach (DataColumn item in myTable.Columns)
{
DataGridTextBoxColumn tbcName = new DataGridTextBoxColumn();
if (item.ColumnName == "OK")
{
tbcName.Width = 20;
}
else
{
tbcName.Width = 175;
}
tbcName.MappingName = item.ColumnName;
tbcName.HeaderText = item.ColumnName;
tableStyle.GridColumnStyles.Add(tbcName);
}
this.dgvData.TableStyles.Add(tableStyle);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = myTable.NewRow();
itemCode = ds.Tables[0].Rows[i]["item"].ToString();
// MessageBox.Show(itemCode);
if (this.checkStoredata(itemCode) == true)
{
dr["OK"] = " / ";
}
else
{
dr["OK"] = " ";
}
dr["Item"] = ds.Tables[0].Rows[i]["item"];
// dr["Qty"] = ds.Tables[0].Rows[i]["qty"];
// dr["Unit"] = ds.Tables[0].Rows[i]["unit"];
// if (ds.Tables["Showdata"].Rows[i]["Price"].ToString() != "")
// {
// totalX += Convert.ToInt32(ds.Tables["Showdata"].Rows[i]["Price"]);
// }
myTable.Rows.Add(dr);
itemCode = "";
}
this.dgvData.DataSource = myTable;
}
/// <summary>
/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
/// </summary>
/// <remarks>
/// e.g.:
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
/// </remarks>
/// <param name="insertCommand">A valid transact-SQL statement to insert new records into the data source</param>
/// <param name="deleteCommand">A valid transact-SQL statement to delete records from the data source</param>
/// <param name="updateCommand">A valid transact-SQL statement used to update records in the data source</param>
/// <param name="dataSet">The DataSet used to update the data source</param>
/// <param name="tableName">The DataTable used to update the data source.</param>
public static void UpdateDataset(SqlCeCommand insertCommand, SqlCeCommand deleteCommand, SqlCeCommand updateCommand, DataSet dataSet, string tableName)
{
if( insertCommand == null ) throw new ArgumentNullException( "insertCommand" );
if( deleteCommand == null ) throw new ArgumentNullException( "deleteCommand" );
if( updateCommand == null ) throw new ArgumentNullException( "updateCommand" );
if( tableName == null || tableName.Length == 0 ) throw new ArgumentNullException( "tableName" );
// Create a SqlDataAdapter, and dispose of it after we are done
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter();
try
{
// Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data source
dataAdapter.Update (dataSet,tableName);
// Commit all the changes made to the DataSet
dataSet.AcceptChanges();
}
catch (SqlCeException E)
{string strError=E.Message;}
finally{dataAdapter.Dispose();}
}
// For SuperNode Buddy RefreshTick.
List<string> fncGetBuddyStatus(string uName)
{
string ClientConnectionString = @"Data Source=" + System.IO.Path.GetDirectoryName(appPath) + "\\SuperNodeBuddyInfo.sdf";
SqlCeConnection conn = new SqlCeConnection(ClientConnectionString);
conn.Open();
List<string> lstOnlineBuddies = new List<string>();
try
{
string str = "SELECT * FROM User_BuddyList Where UserName ='******'";
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(str, conn);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
lstOnlineBuddies.Add(dataTable.Rows[i]["BuddyName"].ToString() + "-" + dataTable.Rows[i]["BuddyStatus"].ToString());
}
dataAdapter.Dispose();
conn.Close();
conn.Dispose();
return lstOnlineBuddies;
}
catch (Exception exp)
{
VMuktiHelper.ExceptionHandler(exp, "fncGetBuddyStatus()", "Controls\\BuddyExplorer\\CtlBuddies.xaml.cs");
conn.Close();
conn.Dispose();
return lstOnlineBuddies;
}
}
/// <summary>
/// Execute a SqlCeCommand (that returns a resultset) against the specified SqlCeTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", new SqlCeParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid SqlCeTransaction</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>An XmlReader containing the resultset generated by the command</returns>
public static string ExecuteXml(SqlCeTransaction transaction, CommandType commandType, string commandText, params SqlCeParameter[] commandParameters)
{
if( transaction == null ) throw new ArgumentNullException( "transaction" );
if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
// // Create a command and prepare it for execution
SqlCeCommand cmd = new SqlCeCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, (SqlCeConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
SqlCeDataAdapter obj_Adapter =new SqlCeDataAdapter (cmd);
DataSet ds=new DataSet();
ds.Locale =CultureInfo.InvariantCulture;
obj_Adapter.Fill(ds);
// Detach the SqlCeParameters from the command object, so they can be used again
cmd.Parameters.Clear();
string retval= ds.GetXml();
ds.Clear();
obj_Adapter.Dispose ();
return retval;
}