// default constructor
public AppPartDataAdapter(int appID, string baseURI)
{
this._appID = appID;
this._baseURI = baseURI;
this._dbHelper = new ClayUIDatabaseHelper(this._appID, this._baseURI);
this._db = new SqlCeDataAdapter();
}
public Reservation getReservation()
{
Reservation model = new Reservation();
SqlCeCommand cmd = new SqlCeCommand("Select * from Reservation " +
"inner join RoomInformation on Reservation.RoomID = RoomInformation.RoomID " +
"inner join UserInformation on Reservation.UserID = UserInformation.User_ID where Reservation.ReservationID = @ReservationID", conn);
cmd.Parameters.AddWithValue("@ReservationID", this._reservation.ReservationID);
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
adapter.SelectCommand = cmd;
DataSet setdata = new DataSet();
adapter.Fill(setdata, "Reservation");
model.AppointmentID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[0].ToString());
model.UserID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString());
model.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[2].ToString());
model.ReservationDate = DateTime.Parse(setdata.Tables[0].Rows[0].ItemArray[3].ToString());
model.ReservationAmount = setdata.Tables[0].Rows[0].ItemArray[4].ToString();
model.ValidTill = DateTime.Parse(setdata.Tables[0].Rows[0].ItemArray[5].ToString());
model.ReservationType = setdata.Tables[0].Rows[0].ItemArray[6].ToString();
model.RoomAccount.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[2].ToString());
model.RoomAccount.ApartmentName = setdata.Tables[0].Rows[0].ItemArray[8].ToString();
model.RoomAccount .FullAddress= setdata.Tables[0].Rows[0].ItemArray[19].ToString();
model.RoomAccount.RoomType = setdata.Tables[0].Rows[0].ItemArray[12].ToString();
model.RoomAccount.RoomForRent = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[11].ToString());
model.RoomAccount.RoomForSale = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[10].ToString());
model.RoomAccount.RoomPrice = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[14].ToString());
model.RoomAccount .RoomStatus= setdata.Tables[0].Rows[0].ItemArray[23].ToString();
model.RoomAccount.Username = setdata.Tables[0].Rows[0].ItemArray[51].ToString();
Account AccountModel = new Account();
AccountModel.ID = model.UserID;
AccountRepository _accountRepository = new AccountRepository(AccountModel);
model.Reserver = _accountRepository.getDataByID().username;
return model;
}
public void conectaBD()
{
SqlCeConnection PathBD = new SqlCeConnection("Data Source=C:\\Facturacion\\Facturacion\\BaseDeDatos.sdf;Persist Security Info=False;");
//abre la conexion
try
{
da = new SqlCeDataAdapter("SELECT * FROM USUARIOS ORDER BY ID_USUARIO", PathBD);
// Crear los comandos de insertar, actualizar y eliminar
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
// Asignar los comandos al DataAdapter
// (se supone que lo hace automáticamente, pero...)
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
dt = new DataTable();
// Llenar la tabla con los datos indicados
da.Fill(dt);
PathBD.Open();
}
catch (Exception w)
{
MessageBox.Show(w.ToString());
return;
}
}
public static DataTable Select(string query, SqlCeConnection conn)
{
DataTable dt = new DataTable();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(query, conn);
adapter.Fill(dt);
return dt;
}
private void modifica_copii_Load(object sender, EventArgs e)
{
var connString = (@"Data Source=" + System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)) + @"\Grupe.sdf");
using (var conn = new SqlCeConnection(connString))
{
try
{
conn.Open();
var query = "SELECT * FROM copii WHERE Nume='" + nume2 + "'";
MessageBox.Show(query);
var command = new SqlCeCommand(query, conn);
SqlCeDataAdapter da = new SqlCeDataAdapter(command);
SqlCeCommandBuilder cbd = new SqlCeCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
var dataAdapter = new SqlCeDataAdapter(command);
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
label5.Text = dataTable.Rows[0][1].ToString();
textBox2.Text = dataTable.Rows[0][2].ToString();
textBox3.Text = dataTable.Rows[0][3].ToString();
textBox4.Text = dataTable.Rows[0][4].ToString();
textBox5.Text = dataTable.Rows[0][5].ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
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);
}
}
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 void openCheck(string _tableName,string _bom,string _job)
{
myConnection = default(SqlCeConnection);
DataTable dt = new DataTable();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [id] FROM [" + _tableName + "] WHERE id ='" + txtBom.Text + "' ";
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(dt);
myConnection.Close();
if (dt.Rows.Count > 0)
{
frmCheck frmCheck = new frmCheck(this.txtJob.Text.ToUpper(),this.txtBom.Text.ToUpper(),"");
// frmCheck.BindDataGrid();
frmCheck.setBom(dt.Rows[0]["id"].ToString());
frmCheck.setData(_bom, _job);
frmCheck.Show();
// frmCheck.setBom(this.txtBom.Text);
//frmCheck.setJob(this.txtJob.Text);
dt = null;
MessageBox.Show("Bom : " + dt.Rows[0]["id"].ToString());
}
else
{
MessageBox.Show("Bom : " + txtBom.Text + " does not exited");
}
}
/* private void GoFullscreen(bool fullscreen)
{
if (fullscreen)
{
this.WindowState = FormWindowState.Normal;
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None;
this.Bounds = Screen.PrimaryScreen.Bounds;
}
else
{
this.WindowState = FormWindowState.Maximized;
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Sizable;
}
}*/
private void Grupa_Load(object sender, EventArgs e)
{
string startPath = Application.StartupPath;
var filepath = startPath + "\\" + "Grupe.sdf";
var connString = (@"Data Source=" + filepath + "");
using (var conn = new SqlCeConnection(connString))
{
try
{
conn.Open();
var query = "SELECT * FROM grupe WHERE Nume='" + nume + "'";
var command = new SqlCeCommand(query, conn);
var dataAdapter = new SqlCeDataAdapter(command);
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
label1.Text = dataTable.Rows[0][0].ToString();
label2.Text = dataTable.Rows[0][1].ToString();
label3.Text = dataTable.Rows[0][2].ToString();
label4.Text = dataTable.Rows[0][3].ToString();
//label21.Text = dataTable.Rows[0][0].ToString();
textBox7.Text = nume;
refresh();
sume();
this.dataGridView1.Sort(this.dataGridView1.Columns["Nume"], ListSortDirection.Ascending);
colorRows();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
internal List<Cmd> GetDbList()
{
List<Cmd> list = new List<Cmd>();
SqlCeConnection con = new SqlCeConnection(connectionString);
try
{
con.Open();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(
"select * from cmd order by name", con);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Cmd");
DataTable table = dataSet.Tables["Cmd"];
foreach (DataRow row in table.Rows)
{
Cmd cmd = new Cmd();
cmd.name = (string)row["name"];
cmd.description = (string)row["description"];
cmd.path = (string)row["path"];
cmd.arg = (string)row["arg"];
list.Add(cmd);
}
}
finally
{
if (con != null)
{
con.Close();
}
}
return list;
}
private void Form1_Load(object sender, EventArgs e)
{
string cs = GetConnectionString();
//need to upgrade to sql engine v4.0?
if (!IsV40Installed())
{
SqlCeEngine engine = new SqlCeEngine(cs);
engine.Upgrade();
}
//open connection
SqlCeConnection sc = new SqlCeConnection(cs);
//query customers
string sql = "SELECT * FROM Customers";
SqlCeCommand cmd = new SqlCeCommand(sql, sc);
//create grid
SqlCeDataAdapter sda = new SqlCeDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
//close connection
sc.Close();
}
public VerTodosLosProductos(DataTable dt, SqlCeDataAdapter da)
{
InitializeComponent();
this.dt = dt;
this.da = da;
this.BuscarAlgoenTabla("SELECT * FROM Productos ORDER BY Clave");
}
private void button2_Click(object sender, EventArgs e)
{
try{
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
String searchText = textBox1.Text;
String query = "SELECT * FROM owner_master WHERE OwnerName LIKE '%" + searchText + "%'";
SqlCeDataAdapter adapter = new SqlCeDataAdapter(query, conn);
SqlCeCommandBuilder commnder = new SqlCeCommandBuilder(adapter);
DataTable dt = new DataTable();
adapter.Fill(dt);
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
dataGridView1.Rows.Add(dt.Rows[i][0], dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3]);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public ListaFactura(DataTable dt,SqlCeDataAdapter da, int procedimiento)
{
InitializeComponent();
this.dt = dt;
this.da = da;
proc = procedimiento;
}
public static void DeleteFolderFromDB(string folderPath, string dbFilePath)
{
using (SqlCeConnection con = CreateConnection(dbFilePath))
{
con.Open();
SqlCeDataAdapter da = new SqlCeDataAdapter("Select * FROM Folders", con);
da.DeleteCommand = new SqlCeCommand(
"DELETE FROM Folders WHERE id = @original_id " +
"and name = @original_name");
da.DeleteCommand.Parameters.Add("@original_id", SqlDbType.Int, 0, "id");
da.DeleteCommand.Parameters.Add("@original_name", SqlDbType.NVarChar, 255, "name");
da.DeleteCommand.Connection = con;
DataSet ds = new DataSet("Folder");
DataTable dt = new DataTable("Folders");
dt.Columns.Add(new DataColumn("id", typeof(int)));
dt.Columns.Add(new DataColumn("name", typeof(string)));
ds.Tables.Add(dt);
da.Fill(ds, "Folders");
int ind = -1;
for (int i = 0; i < folderList.Count; i++)
{
if (folderList[i] == folderPath.Replace("'", "`"))
{
ind = i;
break;
}
}
string folderid = ds.Tables["Folders"].Rows[ind]["id"].ToString();
dt.Rows[ind].Delete();
da.Update(ds, "Folders");
string sql = "DELETE FROM Songs WHERE folder_id = " + folderid;
SqlCeCommand com = new SqlCeCommand(sql, con);
com.ExecuteNonQuery();
}
}
public Order GetEverything(int id)
{
// Old-fashioned (but trusty and simple) ADO.NET
var connectionString = GetConnectionString();
var connection = new SqlCeConnection(connectionString);
// setup dataset
var ds = new DataSet();
ds.Tables.Add("Orders"); // matches our model or could use dbtable attribute to specify
ds.Tables.Add("OrderLineItems"); // matches a collection property on our model or could use dbtable attribute to specify
// because sql compact does not support multi-select queries in a single call we need to do them one at a time
var sql = "SELECT * FROM Orders WHERE OrderId = @id;"; // this is inline sql, but could also be stored procedure or dynamic
var cmd = new SqlCeCommand(sql, connection);
cmd.Parameters.AddWithValue("@id", id);
var da = new SqlCeDataAdapter(cmd);
da.Fill(ds.Tables["Orders"]);
// make second sql call for child line items
sql = "SELECT * FROM OrderLineItems WHERE OrderId = @id"; // additional query for child details (line items)
cmd = new SqlCeCommand(sql, connection);
cmd.Parameters.AddWithValue("@id", id);
da = new SqlCeDataAdapter(cmd);
da.Fill(ds.Tables["OrderLineItems"]);
// Map to object - this is the only pertinent part of the example
// **************************************************************
var order = Map<Order>.MapSingle(ds);
// **************************************************************
return order;
}
public List<ClientInfoModel> GetClientInfoModels()
{
try
{
List<ClientInfoModel> oResult = new List<ClientInfoModel>();
DataTable oData = new DataTable();
SqlCeDataAdapter oDataAdapter = new SqlCeDataAdapter("SELECT * FROM Client", Connection);
oDataAdapter.Fill(oData);
if (oData.Rows.Count > 0)
{
foreach (DataRow oRow in oData.Rows)
{
ClientInfoModel oClient = new ClientInfoModel();
oClient.macAddress = oRow["Client_MacAdresse"].ToString();
oClient.admin = Convert.ToBoolean(oRow["Client_Administrator"]);
oClient.group = Convert.ToInt32(oRow["Client_Gruppe"]);
oClient.ID = Convert.ToInt32(oRow["Client_ID"]);
oClient.arc = Convert.ToString(oRow["Client_Arc"]);
oClient.pcName = oRow["Client_PCName"].ToString();
oResult.Add(oClient);
}
}
return oResult;
}
catch (Exception ex)
{
Diagnostics.WriteToEventLog(ex.Message, System.Diagnostics.EventLogEntryType.Error, 3011);
return null;
}
}
public void conectaBD()
{
//busca la path de la aplicacion y le agrega la base de datos en string
string path = "C:\\EM Software - Control_De_Invitados\\EM Software - Control_De_Invitados\\BD\\Control_de_invitados_BD.sdf;Persist Security Info=False;";
// C:\EM Software - Control_De_Invitados\EM Software - Control_De_Invitados\BD
SqlCeConnection PathBD = new SqlCeConnection("Data source="+path);
//abre la conexion
try
{
string SeleccionaTodosLosDatos = "SELECT * FROM Invitados ORDER BY Num_invitado";
da = new SqlCeDataAdapter(SeleccionaTodosLosDatos, PathBD);
// Crear los coma;ndos de insertar, actualizar y eliminar
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
// Asignar los comandos al DataAdapter
// (se supone que lo hace automáticamente, pero...)
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
dt = new DataTable();
// Llenar la tabla con los datos indicados
da.Fill(dt);
PathBD.Open();
}
catch (Exception w)
{
MessageBox.Show(w.ToString());
}
}
private void refresh()
{
odData = odBox.Text;
doData = doBox.Text;
try
{
conn = new SqlCeConnection(connectionString);
SqlCeDataAdapter dataadapter = new SqlCeDataAdapter("Select * from Seriale WHERE Data_Premiery BETWEEN '" + odData + "' AND '"
+ doData + "'", conn);
DataSet ds = new DataSet();
conn.Open();
dataadapter.Fill(ds, "Seriale");
conn.Close();
tabela.DataSource = ds;
tabela.DataMember = "Seriale";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (conn != null) conn.Close();
}
}
private static void Clean(string tableName)
{
if (!Program.Settings.General.UseCachedResults)
return;
string getMaxIDSql = string.Format("select max(ID) from {0};", tableName);
long? maxID = null;
using (SqlCeCommand cmd = new SqlCeCommand(getMaxIDSql, Program.GetOpenCacheConnection()))
using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count == 1)
{
if (!DBNull.Value.Equals(ds.Tables[0].Rows[0][0]))
maxID = Convert.ToInt64(ds.Tables[0].Rows[0][0]);
}
}
if (maxID != null)
{
maxID -= Program.Settings.General.CacheSize;
if (maxID > 0)
{
string deleteSql = string.Format("delete from {0} where ID <= {1};", tableName, maxID);
using (SqlCeCommand cmd = new SqlCeCommand(deleteSql, Program.GetOpenCacheConnection()))
{
cmd.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// tipoTabela 1 = Endereço
/// tipoTabela 2 = Locador
/// tipoTabela 3 = Locatário
/// tipoTabela 4 = Recibos Principais
/// tipoTabela 5 = Recibos Locadores
/// </summary>
/// <param name="dsDados"></param>
/// <param name="sdaDados"></param>
/// <param name="tipoTabela"></param>
private static void PreencheDataset(out DataSet dsDados, out SqlCeDataAdapter sdaDados, int tipoTabela)
{
sdaDados = new SqlCeDataAdapter();
SqlCeCommand cmd = retornaConexao().CreateCommand();
if (tipoTabela == 1)
{
cmd.CommandText = "select * from Imoveis where Ativo = 1";
}
else if (tipoTabela == 2)
{
cmd.CommandText = "select * from Locadores where Ativo = 1";
}
else if (tipoTabela == 3)
{
cmd.CommandText = "select * from Locatarios where Ativo = 1";
}
else if (tipoTabela == 4)
{
cmd.CommandText = "select * from RecibosPrincipais";
}
else if (tipoTabela == 5)
{
cmd.CommandText = "select * from RecibosLocadores";
}
sdaDados.SelectCommand = cmd;
dsDados = new DataSet();
sdaDados.Fill(dsDados);
}
public void Save(FileIndex index)
{
SqlCeConnection connection = new SqlCeConnection(_connString);
SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from Indexes", connection);
adapter.InsertCommand = new SqlCeCommand("Insert Into Indexes (Timestamp, Machine, Profile, RelPath, Size, Hash) Values (@Timestamp, @Machine, @Profile, @RelPath, @Size, @Hash)", connection);
adapter.InsertCommand.Parameters.Add("@Timestamp", SqlDbType.DateTime);
adapter.InsertCommand.Parameters.Add("@Machine", SqlDbType.NVarChar);
adapter.InsertCommand.Parameters.Add("@Profile", SqlDbType.NVarChar);
adapter.InsertCommand.Parameters.Add("@RelPath", SqlDbType.NVarChar);
adapter.InsertCommand.Parameters.Add("@Size", SqlDbType.BigInt);
adapter.InsertCommand.Parameters.Add("@Hash", SqlDbType.NVarChar);
// A static timestamp to share among all records.
DateTime indextime = DateTime.Now;
connection.Open();
foreach (FileHeader file in index.Files)
{
adapter.InsertCommand.Parameters["@Timestamp"] = new SqlCeParameter("@Timestamp", indextime);
adapter.InsertCommand.Parameters["@Machine"] = new SqlCeParameter("@Machine", _localSettings.MachineName);
adapter.InsertCommand.Parameters["@Profile"] = new SqlCeParameter("@Profile", _options.ProfileName);
adapter.InsertCommand.Parameters["@RelPath"] = new SqlCeParameter("@RelPath", Path.Combine(file.RelativePath, file.FileName));
adapter.InsertCommand.Parameters["@Size"] = new SqlCeParameter("@Size", file.FileSize);
adapter.InsertCommand.Parameters["@Hash"] = new SqlCeParameter("@Hash", file.ContentsHash);
adapter.InsertCommand.ExecuteNonQuery();
}
// TODO: Find any indexes older than the last two. Remove them.
adapter.DeleteCommand = new SqlCeCommand("Delete From Indexes Where Timestamp Not In (Select Top 2 Timestamp From Indexes Where Machine = @Machine And Profile = @Profile) And Machine = @Machine And Profile = @Profile", connection);
adapter.DeleteCommand.Parameters.Add("@Machine", SqlDbType.NVarChar);
adapter.DeleteCommand.Parameters.Add("@Profile", SqlDbType.NVarChar);
adapter.DeleteCommand.Parameters["@Machine"] = new SqlCeParameter("@Machine", _localSettings.MachineName);
adapter.DeleteCommand.Parameters["@Profile"] = new SqlCeParameter("@Profile", _options.ProfileName);
adapter.DeleteCommand.ExecuteNonQuery();
connection.Close();
}
public List<Appointment> getAppointmentList()
{
List<Appointment> AppointmentList = new List<Appointment>();
SqlCeCommand cmd = new SqlCeCommand("select * from Appointment " +
"inner join RoomInformation on Appointment.RoomID = RoomInformation.RoomID " +
"inner join UserInformation on Appointment.UserID = UserInformation.User_ID " +
"inner join UserInformation as RoomAccount on RoomInformation.UserID = RoomAccount.User_ID " +
"where Appointment.RoomID = @RoomID and Appointment.UserID = @UserID", conn);
cmd.Parameters.AddWithValue("@UserID", this._appointment.UserID);
cmd.Parameters.AddWithValue("@RoomID", this._appointment.RoomID);
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
adapter.SelectCommand = cmd;
DataSet setdata = new DataSet();
adapter.Fill(setdata, "Appointment");
for (int i = 0; i < setdata.Tables[0].Rows.Count; i++)
{
Appointment model = new Appointment();
model.AppointmentID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[0].ToString());
model.RoomID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[1].ToString());
model.UserID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[2].ToString());
model.AppointmentDate = DateTime.Parse(setdata.Tables[0].Rows[i].ItemArray[3].ToString());
model.Status = setdata.Tables[0].Rows[i].ItemArray[4].ToString();
model.Respond = setdata.Tables[0].Rows[i].ItemArray[5].ToString();
model.RoomAcc.RoomID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[1].ToString());
model.RoomAcc.ApartmentName = setdata.Tables[0].Rows[i].ItemArray[7].ToString();
model.RoomAcc.RoomForSale = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[8].ToString());
model.RoomAcc.RoomForRent = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[9].ToString());
model.Appointee = setdata.Tables[0].Rows[i].ItemArray[49].ToString();
model.RoomAcc.Username = setdata.Tables[0].Rows[i].ItemArray[62].ToString();
AppointmentList.Add(model);
}
return AppointmentList;
}
public static DataTable GetContentsOf(string tableName)
{
using (var dbContext = new ProductsDbContext())
using (var connection = new SqlCeConnection(dbContext.Database.Connection.ConnectionString))
using (var command = connection.CreateCommand())
{
var sql = "SELECT* FROM " + tableName;
command.CommandText = sql;
var adapter = new SqlCeDataAdapter(command);
var dataSet = new DataSet();
adapter.Fill(dataSet);
return dataSet.Tables[0];
}
//var connection = new SqlConnection(ConnectionString);
//using (connection)
//{
// connection.Open();
// var command = new SqlCommand("SELECT * FROM " + tableName, connection);
// var adapter = new SqlDataAdapter(command);
// var dataSet = new DataSet();
// adapter.Fill(dataSet);
// return dataSet.Tables[0];
//}
}
public Appointment getAppointment()
{
Appointment model = new Appointment();
SqlCeCommand cmd = new SqlCeCommand("Select * from Appointment " +
"inner join RoomInformation on Appointment.RoomID = RoomInformation.RoomID " +
"inner join UserInformation on Appointment.UserID = UserInformation.User_ID where Appointment.AppointmentID = @AppointmentID", conn);
cmd.Parameters.AddWithValue("@AppointmentID", this._appointment.AppointmentID);
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
adapter.SelectCommand = cmd;
DataSet setdata = new DataSet();
adapter.Fill(setdata, "Appointment");
model.AppointmentID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[0].ToString());
model.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString());
model.UserID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[2].ToString());
model.AppointmentDate = DateTime.Parse(setdata.Tables[0].Rows[0].ItemArray[3].ToString());
model.Status = setdata.Tables[0].Rows[0].ItemArray[4].ToString();
model.Respond = setdata.Tables[0].Rows[0].ItemArray[5].ToString();
model.RoomAcc.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString());
model.RoomAcc.ApartmentName = setdata.Tables[0].Rows[0].ItemArray[7].ToString();
model.RoomAcc.RoomForSale = bool.Parse (setdata.Tables[0].Rows[0].ItemArray[8].ToString());
model.RoomAcc.RoomForRent = bool.Parse (setdata.Tables[0].Rows[0].ItemArray[9].ToString());
model.RoomAcc.RoomPrice = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[12].ToString());
model.RoomAcc.Username = setdata.Tables[0].Rows[0].ItemArray [49].ToString ();
Account AccountModel = new Account();
AccountModel.ID = model.UserID;
AccountRepository _accountRepository = new AccountRepository(AccountModel);
model.Appointee = _accountRepository.getDataByID().username;
return model;
}
private void GetData(string selectCommand)
{
try
{
// Specify a connection string. Replace the given value with a
// valid connection string for a Northwind SQL Server sample
// database accessible to your system.
String connectionString = "Data Source=|DataDirectory|\\MyDatabase4.sdf";
// Create a new data adapter based on the specified query.
dataAdapter = new SqlCeDataAdapter(selectCommand, connectionString);
// Create a command builder to generate SQL update, insert, and
// delete commands based on selectCommand. These are used to
// update the database.
SqlCeCommandBuilder commandBuilder = new SqlCeCommandBuilder(dataAdapter);
// Populate a new data table and bind it to the BindingSource.
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
// Resize the DataGridView columns to fit the newly loaded content.
dataGridView1.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
catch (SqlCeException)
{
MessageBox.Show("To run this example, replace the value of the " +
"connectionString variable with a connection string that is " +
"valid for your system.");
}
}
private void Modify_RENT_Load(object sender, EventArgs e)
{
//get every car's name, type and number. This datas will appear as combobox items
DataSet carRes = new DataSet();
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
SqlCeCommand cmd = Form1.con.CreateCommand();
//fill Dataset with datas
cmd.CommandText = "SELECT ID, Marka, Tipus, Rendszam FROM Autok";
adapter.SelectCommand = cmd;
adapter.Fill(carRes, "Autok");
//fill "cars" Dictionary with cars datas from from Dataset
//Dictionary data will be displayed in the combobox
int row = carRes.Tables["Autok"].Rows.Count - 1;
Dictionary<int, String> cars = new Dictionary<int, String>();
for (int r = 0; r <= row; r++)
{
String display_info = String.Concat(carRes.Tables["Autok"].Rows[r].ItemArray[1].ToString(), " ",
carRes.Tables["Autok"].Rows[r].ItemArray[2].ToString(), " ", carRes.Tables["Autok"].Rows[r].ItemArray[3].ToString());
cars.Add((int)carRes.Tables["Autok"].Rows[r].ItemArray[0], display_info);
}
comboBox1.DataSource = new BindingSource(cars,null);
comboBox1.DisplayMember = "Value";
comboBox1.ValueMember = "Key";
comboBox1.SelectedValue = carID;
//get Clients data
Dictionary<String, String> clients = new Dictionary<String, String>();
DataSet clientRes = new DataSet();
cmd.CommandText = "SELECT ID, Kereszt_nev, Vezetek_nev FROM Ugyfelek";
adapter.SelectCommand = cmd;
adapter.Fill(clientRes, "Ugyfelek");
row = clientRes.Tables["Ugyfelek"].Rows.Count - 1;
//fill Clients dictionary with information about clients
for (int r = 0; r <= row; r++)
{
String display_info = String.Concat(clientRes.Tables["Ugyfelek"].Rows[r].ItemArray[1].ToString(), " ",
clientRes.Tables["Ugyfelek"].Rows[r].ItemArray[2].ToString());
clients.Add(clientRes.Tables["Ugyfelek"].Rows[r].ItemArray[0].ToString(), display_info);
}
comboBox2.DataSource = new BindingSource(clients, null);
comboBox2.DisplayMember = "Value";
comboBox2.ValueMember = "Key";
comboBox2.SelectedValue = clientID;
//get information about selected sale and set controls values
DataSet current_rent = new DataSet();
cmd.CommandText = "SELECT Kezdeti_ido, Veg_ido FROM Berles WHERE Auto_ID = @carid AND Ugyfel_ID = @clientid";
cmd.Parameters.AddWithValue("@carid", carID);
cmd.Parameters.AddWithValue("@clientid", clientID);
adapter.SelectCommand = cmd;
adapter.Fill(current_rent, "Berles");
DateTime current_date = Convert.ToDateTime(current_rent.Tables["Berles"].Rows[0].ItemArray[0].ToString());
dateTimePicker1.Value = current_date;
current_date = Convert.ToDateTime(current_rent.Tables["Berles"].Rows[0].ItemArray[1].ToString());
dateTimePicker2.Value = current_date;
}
public IEnumerable<string> GetRecentFiles()
{
if (!General.IsRecentFilesSaved)
{
_recentFiles = null;
return new List<string>();
}
if (_recentFiles == null)
{
_recentFiles = new List<string>();
using (SqlCeCommand cmd = new SqlCeCommand("select Path from RecentFile order by ID desc", Program.GetOpenSettingsConnection()))
using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
_recentFiles.Add(Convert.ToString(dr[0]));
if (_recentFiles.Count >= 4)
break;
}
}
}
return _recentFiles;
}
/// <summary>
/// </summary>
/// <param name="dsDados"></param>
/// <param name="sdaDados"></param>
/// <param name="tipoTabela"></param>
/// <param name="idLocatario"></param>
private static void PreencheDataset(out DataSet dsDados, out SqlCeDataAdapter sdaDados, int tipoTabela, string idBusca)
{
sdaDados = new SqlCeDataAdapter();
SqlCeCommand cmd = Auxiliar.retornaConexao().CreateCommand();
if (tipoTabela == 1)
{
cmd.CommandText = "select rl.Aluguel, rl.PorcentagemMulta, rl.Multa, rl.PorcentagemComissao, rl.Comissao, rl.Complemento, rl.DescricaoComplemento,"+
" rl.Complemento2, rl.DescricaoComplemento2, rl.Complemento3, rl.DescricaoComplemento3, rl.Total, im.fkIdLocador1, im.fkIdLocador2" +
" from RecibosLocadores rl" +
" inner join RecibosPrincipais rp on rl.fkIdRecibo = rp.Id" +
" inner join Locatarios lct on rp.fkIdLocatario = lct.Id" +
" inner join Imoveis im on lct.fkIdImovel = im.Id" +
" where rl.Id = " + idBusca + "";
}
else if (tipoTabela == 2)
{
cmd.CommandText = "select Id, Locador, Cpf, Cnpj from Locadores where id in (" + idBusca + ") and Ativo = 1 order by Locador";
}
sdaDados.SelectCommand = cmd;
dsDados = new DataSet();
sdaDados.Fill(dsDados);
}
/// <summary>
/// Выполняет запрос к базе данных.
/// </summary>
/// <param name="query">SQL-запрос.</param>
/// <returns>Возвращает ответ базы данных.</returns>
public static List<List<string>> Query(string query)
{
var ret = new List<List<string>>();
var command = new SqlCeCommand();
command.Connection = Connect;
command.CommandType = CommandType.Text;
command.CommandText = query;
Open();
switch(query.Split(' ')[0].ToLower())
{
case "select":
var adapter = new SqlCeDataAdapter(command);
ret = Select(adapter);
return ret;
case "insert":
if (Insert(command))
{
ret.Add(new List<string>());
ret[0].Add("1");
}
else
{
ret.Add(new List<string>());
ret[0].Add("0");
}
return ret;
default:
ret.Add(new List<string>());
ret[0].Add("0");
return ret;
}
}
/// <summary>
/// Current cell tracing events and set the dataset to the GridGroupingControl
/// </summary>
private void SampleCustomization()
{
GridEngine eng = this.gridGroupingControl1.Engine;
eventTracer = new EngineEventTracer(eng, this.Output);
this.eventTracer.HookCurrentCellEvents();
String commandstring1 = "select * from Categories";
String commandstring2 = "select * from Products";
SqlCeDataAdapter da1 = new SqlCeDataAdapter(commandstring1, connString);
SqlCeDataAdapter da2 = new SqlCeDataAdapter(commandstring2, connString);
try
{
da1.Fill(dataset11, "Categories");
da2.Fill(dataset11, "Products");
}
catch (SqlException ex)
{
ShowErrorMessage(ex);
}
}
public DataSet getActivityInfo()
{
DataSet dsActivity = new DataSet();
try
{
using (SqlCeConnection con = new SqlCeConnection(strConn))
{
con.Open();
string strcmd = "select * from Activity order by ActivityDesc";
SqlCeCommand cmd = new SqlCeCommand(strcmd, con);
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
da.Fill(dsActivity);
con.Close();
}
}
catch (Exception ex)
{
dsActivity = null;
throw ex;
}
return(dsActivity);
}
public void Loaddata()
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
DataGridTableStyle ts = new DataGridTableStyle();
SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM dok", cn);
DataTable table = new DataTable();
da.Fill(table);
dataGrid1.DataSource = table.DefaultView;
dataGrid1.TableStyles.Add(ts);
dataGrid1.TableStyles[0].GridColumnStyles[0].Width = 0;
dataGrid1.TableStyles[0].GridColumnStyles[1].Width = 80;
dataGrid1.TableStyles[0].GridColumnStyles[2].Width = 30;
dataGrid1.TableStyles[0].GridColumnStyles[3].Width = 70;
cn.Close();
}
// This is used only to execute the Dynamic Query API
static private void LoadDataTableFromDynamicQuery(esDataRequest request, esDataResponse response, SqlCeCommand cmd)
{
try
{
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
SqlCeDataAdapter da = new SqlCeDataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataTable);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception ex)
{
CleanupCommand(cmd);
throw ex;
}
finally
{
}
}
/// <summary>
///
/// </summary>
/// <param name="sTableName"></param>
/// <param name="nRecordNo"></param>
/// <param name="sColumnName"></param>
/// <returns></returns>
// SQL Query to Select particular Record
// "SELECT * FROM ProductDetail WHERE RecordNo = 4;"
public DataRow SelectParticularRecord(string sTableName, int nRecordNo, string sColumnName)
{
string query = string.Format("SELECT * FROM {0} WHERE {1} = {2}", sTableName, sColumnName, nRecordNo);
using (DataTable dt = new DataTable())
{
DataRow row = null;
try
{
if (m_SqlConnection == null)
{
m_SqlConnection = new SqlCeConnection(GetConnectionString(sTableName));
}
if (m_SqlConnection.State == ConnectionState.Broken || m_SqlConnection.State == ConnectionState.Closed)
{
m_SqlConnection.Open();
}
using (SqlCeDataAdapter adpater = new SqlCeDataAdapter(query, m_SqlConnection))
{
adpater.Fill(dt);
}
if (dt.Rows.Count > 0)
{
row = dt.Rows[0];
}
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
#endif
}
return(row);
}
}
public DataSet Execute(string outTableName)
{
var ds = new DataSet();
try
{
using (var cn = Connection)
{
var db2Conn = (SqlCeConnection)cn.Open();
var cmd = new SqlCeCommand
{
Connection = db2Conn,
CommandText = Text
};
using (var da = new SqlCeDataAdapter(cmd.CommandText, db2Conn))
{
da.Fill(ds);
ds.Tables[0].TableName = outTableName;
DataSet = ds;
TableName = outTableName;
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
ds.Dispose();
}
return(ApplyBusinessLogic());
}
private void btbnBuscar_Click(object sender, EventArgs e)
{
try
{
int num;
bool isNum = int.TryParse(txtId.Text.Trim(), out num);
if (!isNum)
{
MessageBox.Show("El id proporcionado no tiene un formato valido, por favor proporcione un numero entero", "Para continuar");
return;
}
using (SqlCeConnection cnx = new SqlCeConnection(ConfigurationManager.ConnectionStrings["cnxString"].ToString()))
{
const string sqlGetById = "SELECT * FROM Producto WHERE Id = @id";
using (SqlCeCommand cmd = new SqlCeCommand(sqlGetById, cnx))
{
cmd.Parameters.AddWithValue("@id", txtId.Text);
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
dgvDatos.AutoGenerateColumns = false;
dgvDatos.DataSource = dt;
dgvDatos.Columns["columnId"].DataPropertyName = "Id";
dgvDatos.Columns["columnDescripcion"].DataPropertyName = "Descripcion";
dgvDatos.Columns["columnMarca"].DataPropertyName = "Marca";
dgvDatos.Columns["columnPrecio"].DataPropertyName = "Precio";
}
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Error: {0}", ex.Message), "Error inesperado");
}
}
/// <summary>
/// GridGrouping control Grouping Events customization.
/// </summary>
private void GridSettings()
{
String commandstring1 = "select * from Employees";
String commandstring2 = "select * from Orders";
SqlCeDataAdapter da1 = new SqlCeDataAdapter(commandstring1, connString);
SqlCeDataAdapter da2 = new SqlCeDataAdapter(commandstring2, connString);
try
{
da1.Fill(dataset11, "Employees");
da2.Fill(dataset11, "Orders");
}
catch (SqlException ex)
{
ShowErrorMessage(ex);
}
//Setting Grid properties
this.gridGroupingControl1.TableControl.DpiAware = true;
this.gridGroupingControl1.TopLevelGroupOptions.ShowAddNewRecordBeforeDetails = false;
this.gridGroupingControl1.NestedTableGroupOptions.ShowAddNewRecordBeforeDetails = false;
this.gridGroupingControl1.TableOptions.GridLineBorder = new GridBorder(GridBorderStyle.Solid, System.Drawing.Color.FromArgb(208, 215, 229), GridBorderWeight.Thin);
this.BackColor = Color.White;
this.gridGroupingControl1.GridVisualStyles = GridVisualStyles.Metro;
this.gridGroupingControl1.GridOfficeScrollBars = OfficeScrollBars.Metro;
FieldChooser fc = new FieldChooser(this.gridGroupingControl1);
//used to set multiextended selection mode in gridgrouping control.
this.gridGroupingControl1.TableOptions.ListBoxSelectionMode = SelectionMode.MultiExtended;
this.gridGroupingControl1.Table.DefaultColumnHeaderRowHeight = (int)DpiAware.LogicalToDeviceUnits(30.0f);
//this.gridGroupingControl1.Table.DefaultRecordRowHeight = (int)DpiAware.LogicalToDeviceUnits(22.0f);
this.gridGroupingControl1.Table.DefaultRecordRowHeight = this.gridGroupingControl1.GetTable("Orders").DefaultRecordRowHeight = (int)DpiAware.LogicalToDeviceUnits(22.0f);
//Navigate to other control using tabkey navigation
this.gridGroupingControl1.WantTabKey = false;
}
private void lerBancoDados()
{
//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);
nAmostras = dados.Rows.Count;
for (var i = 0; i < nAmostras; i++)
{
for (var j = 1; j < 13; j++)
{
if (j <= 6)
{
dTrain[i, j - 1] = Convert.ToDouble(dados.Rows[i][j]);
}
else
{
xTrainAux[i, j - 7] = Convert.ToDouble(dados.Rows[i][j]);
}
}
}
//Desligando todas as ligações
adaptador.Dispose();
ligacao.Dispose();
//Retorno
// return vetEntrada;
}
private void PopUp_F_Vendedor_Load(object sender, EventArgs e)
{
try
{
// Me conecto
SqlCeConnection conexion = new SqlCeConnection(MDIParentPadre.st_conexion);
DataSet sgdbDataSet = new DataSet();
SqlCeCommand cmd = new SqlCeCommand();
string sql = "SELECT [id_usuario],[nombre],[apellido] FROM data_usuarios WHERE usertype LIKE '%Vendedor%' ORDER BY id_usuario DESC";
SqlCeDataAdapter da = new SqlCeDataAdapter(sql, conexion);
da.Fill(sgdbDataSet, "data_usuarios");
BindingSource bindingSource = new BindingSource(sgdbDataSet.Tables[0], null);
dataGridView1.AutoGenerateColumns = false;
dataGridView1.Columns.Clear(); //Borro todas las columnas de dataGridView1
dataGridView1.Columns.Add("VendedorNumero", "Vendedor N°"); //(nombre de la columna,Titulo de la columna)
dataGridView1.Columns.Add("Nombre", "Nombre");
dataGridView1.Columns.Add("Apellido", "Apellido");
//dataGridView1.Columns[0].Width = 110; // ancho de la columna
//dataGridView1.Columns[1].Width = 300;
dataGridView1.Columns[0].DataPropertyName = "id_usuario"; //son los campos de la base de datos
dataGridView1.Columns[1].DataPropertyName = "nombre";
dataGridView1.Columns[2].DataPropertyName = "apellido";
dataGridView1.DataSource = bindingSource;
//bindingNavigator1.BindingSource = bindingSource;
conexion.Close();
}
catch (Exception ex)
{
em.sendmail(ex, "Error: F-VE1-001");
}
}
private static void SearchText(string searchText)
{
string connStr = "Data Source=Northwind40.sdf;Persist Security Info=False;";
DataTable dt = new DataTable();
try
{
string sql = "SELECT c.TABLE_NAME, c.COLUMN_NAME ";
sql += "FROM INFORMATION_SCHEMA.COLUMNS AS c ";
sql += "INNER JOIN INFORMATION_SCHEMA.Tables AS t ON t.TABLE_NAME = c.TABLE_NAME ";
sql += "WHERE (c.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')) AND (t.TABLE_TYPE = 'TABLE') ";
SqlCeDataAdapter da = new SqlCeDataAdapter(sql, connStr);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
string dynSQL = "SELECT [" + dr["COLUMN_NAME"] + "]";
dynSQL += " FROM [" + dr["TABLE_NAME"] + "]";
dynSQL += " WHERE [" + dr["COLUMN_NAME"] + "] LIKE '%" + searchText + "%'";
DataTable result = new DataTable();
da = new SqlCeDataAdapter(dynSQL, connStr);
da.Fill(result);
foreach (DataRow r in result.Rows)
{
Console.WriteLine("Table Name: " + dr["TABLE_NAME"]);
Console.WriteLine("Column Name: " + dr["COLUMN_NAME"]);
Console.WriteLine("Value: " + r[0]);
}
}
}
catch (Exception e)
{
Console.Write(e.Message);
}
}
public ActionResult ShowFactsRows(string baseName, string tableName)
{
List <FactJson> facts = new List <FactJson>();
DataBase db = olapDB.DataBases.Single(d => d.Name == baseName);
SqlCeConnection myConnection = new SqlCeConnection();
DataSet ds;
String selColumnsSQL = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" + tableName + "'";
string dataSource = Path.Combine(Server.MapPath("~/Files"), db.FileName);
myConnection.ConnectionString = @"Data Source=" + dataSource + ";Persist Security Info=False;";
myConnection.Open();
using (SqlCeCommand comm = new SqlCeCommand(selColumnsSQL, myConnection))
{
using (SqlCeDataAdapter da = new SqlCeDataAdapter(comm))
{
ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
}
finally
{
myConnection.Close();
}
}
}
addFactsRows(facts, ds);
return(Json(facts));
}
public DataTable SaldoEstoque(ModelSaldoEst filtro)
{
string P0 = ""; string P1 = "";
if (filtro.CodLocal > 0)
{
P0 = " AND codLocalEst = " + filtro.CodLocal;
}
if (filtro.CodPro > 0)
{
P1 = " AND codProEst = " + filtro.CodPro;
}
string sql = String.Format("SELECT * FROM SALDOESTOQUE S " +
"INNER JOIN ESTOQUE E on E.EstoqueID = S.codLocalEst " +
"WHERE idSaldoEst > 0 {0}{1}", P0, P1);
try
{
conexao = new SqlCeConnection(Funcoes.Busca_Conexao());
cmd = new SqlCeCommand(sql, conexao);
daDados = new SqlCeDataAdapter(cmd);
dtDados = new DataTable();
daDados.Fill(dtDados);
return(dtDados);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conexao.Close();
}
}
//===========================================================================
public DataTable read(string query, List <parametros> parametro = null)
{
// nova tabela
DataTable dados = new DataTable();
//abre ligacoes
ligacao = new SqlCeConnection(conecao);
adaptador = new SqlCeDataAdapter(query, ligacao);
adaptador.SelectCommand.Parameters.Clear();
//tenta preenceher a tabela
try
{
//add os parametros
if (parametro != null)
{
foreach (parametros p in parametro)
{
adaptador.SelectCommand.Parameters.AddWithValue(p.parametro, p.valor);
}
}
//preenche a tabela
adaptador.Fill(dados);
}
catch (Exception erro)
{
MessageBox.Show(erro.Message);
}
//Fecha ligacoes
adaptador.Dispose();
ligacao.Dispose();
//envia resultados
return(dados);
}
public void Loaddata()
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
DataGridTableStyle ts = new DataGridTableStyle();
SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM dok", cn);
DataTable table = new DataTable();
da.Fill(table);
index = table.Rows[rownum][0].ToString();
SqlCeDataAdapter db = new SqlCeDataAdapter("SELECT * FROM bufor", cn);
DataTable table2 = new DataTable();
db.SelectCommand = new SqlCeCommand("SELECT * FROM bufor WHERE dokid = ?", cn);
db.SelectCommand.Parameters.Add("@k", SqlDbType.Int);
db.SelectCommand.Parameters["@k"].Value = int.Parse(index);
db.SelectCommand.ExecuteNonQuery();
db.Fill(table2);
dataGrid1.DataSource = table2.DefaultView;
dataGrid1.TableStyles.Add(ts);
dataGrid1.TableStyles[0].GridColumnStyles[0].Width = 0;
dataGrid1.TableStyles[0].GridColumnStyles[1].Width = 0;
dataGrid1.TableStyles[0].GridColumnStyles[2].Width = 70;
dataGrid1.TableStyles[0].GridColumnStyles[3].Width = 85;
dataGrid1.TableStyles[0].GridColumnStyles[4].Width = 0;
dataGrid1.TableStyles[0].GridColumnStyles[5].Width = 25;
dataGrid1.TableStyles[0].GridColumnStyles[6].Width = 0;
cn.Close();
}
public List <TraceItem> selectDataByUeID(string ueid)
{
List <TraceItem> outputList = new List <TraceItem>();
TraceItem traceItem = new TraceItem();
try
{
con = new SqlCeConnection("Data Source = " + _mainDir + "/Database1.sdf; Max Database Size = 4090");
SqlCeDataAdapter sda = new SqlCeDataAdapter();
SqlCeCommand cmd = con.CreateCommand();
con.Open();
cmd.CommandText = String.Format("Select * FROM TraceDB WHERE ueId = '" + ueid + "';");
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
foreach (DataRow r in dt.Rows)
{
traceItem.rrcMessage = (string)r[0];
traceItem.ueid = (string)r[1];
traceItem.cellID = (string)r[2];
traceItem.time = (DateTime)r[3];
outputList.Add(traceItem);
}
con.Close();
}
catch (Exception ex)
{
errorMessage = ex.Message;
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return(outputList);
}
private void loginToolStripMenuItem_Click(object sender, EventArgs e)
{
SqlCeConnection conexao = new SqlCeConnection("Data Source = " + Vars.base_dados);
conexao.Open();
SqlCeCommand comando = new SqlCeCommand();
comando.Connection = conexao;
comando.CommandText = "SELECT * FROM Admin";
DataTable verifica_Existencia_Admin = new DataTable();
SqlCeDataAdapter escravo = new SqlCeDataAdapter();
escravo.SelectCommand = comando;
escravo.Fill(verifica_Existencia_Admin);
// verifica se admin já foi adicionado
if (verifica_Existencia_Admin.Rows.Count != 0)
{
// deixa visivel as opções de administrador
if (!status_Login)
{
AutenticaçãoAdmin abrir = new AutenticaçãoAdmin();
abrir.ShowDialog();
}
if (status_Login)
{
adicionarAdminToolStripMenuItem.Visible = true;
sairToolStripMenuItem.Visible = true;
localDoAcessoToolStripMenuItem.Visible = true;
toolStripSeparator1.Visible = true;
loginToolStripMenuItem.Text = AutenticaçãoAdmin.nome_admin;
loginToolStripMenuItem.Image = Properties.Resources.admin30x30;
}
}
}
private void GetTotalRow(string query)
{
string queryAll = "SELECT COUNT(*) AS Total FROM (select * from (select CONVERT(NVARCHAR(10), [order].ngayxong, 103) AS [Ngày Nhập/ Xuất], product.kyhieu[Ký Hiệu], product.masp[Mã Sản Phẩm], "
+ "SUM(CASE WHEN LoaiID = 0000001 AND[order].NhacciD is not null Then Soluong else 0 end)[Nhập BTP Chưa in], "
+ "SUM(CASE WHEN LoaiID = 0000002 AND[order].NhacciD is not null Then Soluong ELSE 0 END)[Nhập BTP Đã in], "
+ "SUM(CASE WHEN LoaiID = 0000003 AND[order].NhacciD is not null Then Soluong ELSE 0 END)[Nhập Thành Phẩm], "
+ "SUM(CASE WHEN LoaiID = 000004 AND[order].NhacciD is not null Then Soluong ELSE 0 END)[Nhập Sản phẩm lỗi], "
+ "SUM(CASE WHEN LoaiID = 0000001 AND[order].KHID is not null Then Soluong else 0 end)[Xuất BTP Chưa in], "
+ "SUM(CASE WHEN LoaiID = 0000002 AND[order].KHID is not null Then Soluong ELSE 0 END)[Xuất BTP Đã in], "
+ "SUM(CASE WHEN LoaiID = 0000003 AND[order].KHID is not null Then Soluong ELSE 0 END)[Xuất Thành Phẩm], "
+ "SUM(CASE WHEN LoaiID = 000004 AND[order].KHID is not null Then Soluong ELSE 0 END)[Xuất Sản phẩm lỗi] "
+ "from orderdetail "
+ "join [order] on orderdetail.donhangid = [order].donhangid "
+ "join product on orderdetail.barcode = product.barcode "
+ "where [order].xong = 1 "
+ "GROUP BY CONVERT(NVARCHAR(10), [order].ngayxong, 103), product.kyhieu,product.masp) New ) NewAll " + query;
using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
{
using (SqlCeCommand command = new SqlCeCommand(queryAll, connection))
{
SqlCeDataAdapter sda = new SqlCeDataAdapter(command);
DataTable dt = new DataTable();
sda.Fill(dt);
rowCount = Convert.ToInt32(dt.Rows[0][0].ToString());
pageSize = rowCount / rowPerPage;
// if any row left after calculated pages, add one more page
if (rowCount % rowPerPage > 0)
{
pageSize += 1;
}
txtPaging.Text = currentPageNumber.ToString() + " /" + pageSize.ToString();
lblTotalPage.Text = "Tổng số:" + rowCount.ToString();
}
}
}
public List <Domain.Placa> LoadMany(string sql)
{
Dal objSqlCeServerDAL = Dal.GetInstance(connString);
objSqlCeServerDAL.Open();
SqlCeDataAdapter dAd = new SqlCeDataAdapter(sql, objSqlCeConnection);
dAd.SelectCommand.CommandType = CommandType.Text;
DataSet dSet = new DataSet();
try
{
dAd.Fill(dSet, "Placa");
var placas = dSet.Tables["Placa"].AsEnumerable().Select(x => new Domain.Placa
{
Id = x.Field <int>("Id"),
DataHora = x.Field <DateTime>("DataHora"),
qtdCaixaSaida = x.Field <int>("qtdCaixaSaida"),
Observacao = x.Field <string>("Observacao"),
placa = x.Field <string>("placa"),
qtdCaixaRetorno = x.Field <Nullable <int> >("qtdCaixaRetorno"),
}).ToList();
return(placas);
}
catch (Exception e)
{
return(null);
}
finally
{
dSet.Dispose();
dAd.Dispose();
objSqlCeServerDAL.Dispose();
}
}
//取得DataSet
public static DataSet GetDataSetWithConn(string ConnectionString, string SQL, params string[] parameters)
{
DataSet ds = new DataSet();
try
{
List <string> paramInSQL = GetParameters(SQL);
using (SqlCeConnection sc = new SqlCeConnection(ConnectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand(SQL, sc))
{
//CommandTimeout 重設為30秒
//cmd.ResetCommandTimeout();
//怕下列指令執行較長,將他延長設為120秒
//cmd.CommandTimeout = 120;
for (int i = 0; i <= paramInSQL.Count - 1; i++)
{
cmd.Parameters.AddWithValue(paramInSQL[i], parameters[i]);
}
using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
{
sc.Open();
da.Fill(ds);
}
}
}
}
catch
{
throw;
}
return(ds);
}
/// <summary>
/// GridGrouping control getting started customization.
/// </summary>
private void GridSettings()
{
#region Data Source
String commandstring = "select * from Customers";
SqlCeDataAdapter da = new SqlCeDataAdapter(commandstring, connString);
try
{
da.Fill(dataset11, "Customers");
}
catch (SqlException ex)
{
ShowErrorMessage(ex);
}
#endregion
//used to set multiextended selection mode in gridgrouping control.
this.gridGroupingControl1.TableOptions.ListBoxSelectionMode = SelectionMode.MultiExtended;
this.gridGroupingControl1.TableControl.DpiAware = true;
//used to set GridCaptionRowHeight.
this.gridGroupingControl1.Table.DefaultCaptionRowHeight = (int)DpiAware.LogicalToDeviceUnits(25.0f);;
this.gridGroupingControl1.Table.DefaultColumnHeaderRowHeight = (int)DpiAware.LogicalToDeviceUnits(30.0f);;
this.gridGroupingControl1.Table.DefaultRecordRowHeight = (int)DpiAware.LogicalToDeviceUnits(22.0f);;
this.gridGroupingControl1.GridVisualStyles = GridVisualStyles.Metro;
this.gridGroupingControl1.TableDescriptor.Columns["CustomerID"].HeaderText = "Customer ID";
this.gridGroupingControl1.TableDescriptor.Columns["CompanyName"].HeaderText = "Company Name";
this.gridGroupingControl1.TableDescriptor.Columns["ContactTitle"].HeaderText = "Contact Title";
this.gridGroupingControl1.TableDescriptor.Columns["ContactName"].HeaderText = "Contact Name";
this.gridGroupingControl1.TableDescriptor.Columns["PostalCode"].HeaderText = "Postal Code";
//Navigate to other control using tabkey navigation
this.gridGroupingControl1.WantTabKey = false;
}
private void cmd_login_Click(object sender, EventArgs e)
{
if (text_Usuario.Text == "" || text_senha.Text == "Digite a Senha" || text_Usuario.Text == "Usuário" || text_senha.Text == "")
{
MessageBox.Show("Por favor preencha todos os campos.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
SqlCeConnection conexao = new SqlCeConnection("Data Source = " + Vars.base_dados);
conexao.Open();
SqlCeCommand comando = new SqlCeCommand();
comando.Parameters.AddWithValue("@Usuario", text_Usuario.Text);
comando.Parameters.AddWithValue("@Senha", text_senha.Text);
comando.CommandText = "SELECT * FROM Admin WHERE Usuario = @Usuario AND Senha = @Senha";
comando.Connection = conexao;
DataTable verifica_Admin = new DataTable();
SqlCeDataAdapter escravo = new SqlCeDataAdapter();
escravo.SelectCommand = comando;
escravo.Fill(verifica_Admin);
if (verifica_Admin.Rows.Count != 0)
{
nome_admin = verifica_Admin.Rows[0]["Usuario"].ToString();
frmMenu.status_Login = true;
Close();
}
else
{
MessageBox.Show("Usuario ou senha incorretos. Tente novamente", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
text_senha.Text = "Digite a Senha";
text_Usuario.Text = "Usuário";
}
}
static string myConnString = Program.CS;//ConfigurationManager.ConnectionStrings["connstrng"].ConnectionString;
public bool loginCheck(loginBLL l)
{
bool isSuccess = false;
SqlCeConnection con = new SqlCeConnection(myConnString);
try
{
string comm = "select * from tbl_users WHERE username=@username and password=@password and user_type=@user_type";
SqlCeCommand cmd = new SqlCeCommand(comm, con);
cmd.Parameters.AddWithValue("@username", l.username);
cmd.Parameters.AddWithValue("@password", l.password);
cmd.Parameters.AddWithValue("@user_type", l.user_type);
SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
new Mymessage(ex.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
/// <summary>
/// GridGrouping control getting started customization.
/// </summary>
private void GridSettings()
{
#region Data Source
String commandstring = "select * from Products";
SqlCeDataAdapter da = new SqlCeDataAdapter(commandstring, connString);
try
{
da.Fill(dataset11, "Products");
}
catch (SqlException ex)
{
ShowErrorMessage(ex);
}
#endregion
//used to set multiextended selection mode in gridgrouping control.
this.gridGroupingControl1.TableControl.DpiAware = true;
this.gridGroupingControl1.TableOptions.ListBoxSelectionMode = SelectionMode.MultiExtended;
this.gridGroupingControl1.AllowProportionalColumnSizing = true;
this.gridGroupingControl1.TableOptions.GridVisualStyles = GridVisualStyles.Metro;
this.gridGroupingControl1.TopLevelGroupOptions.ShowAddNewRecordBeforeDetails = false;
this.gridGroupingControl1.TopLevelGroupOptions.ShowCaption = false;
this.gridGroupingControl1.GridVisualStyles = GridVisualStyles.Metro;
this.gridGroupingControl1.GridOfficeScrollBars = OfficeScrollBars.Metro;
//used to set GridCaptionRowHeight.
this.gridGroupingControl1.Table.DefaultCaptionRowHeight = (int)DpiAware.LogicalToDeviceUnits(25);
this.gridGroupingControl1.Table.DefaultColumnHeaderRowHeight = (int)DpiAware.LogicalToDeviceUnits(30);
this.gridGroupingControl1.Table.DefaultRecordRowHeight = (int)DpiAware.LogicalToDeviceUnits(20);
this.MetroColor = System.Drawing.Color.Transparent;
//Navigate to other control using tabkey navigation
this.gridGroupingControl1.WantTabKey = false;
}
private void update_data_local_to_db()
{
string connection = ("Data Source=deploy\\localdata.sdf");
using (SqlCeConnection conn = new SqlCeConnection(connection))
{
SqlCeDataAdapter CountAdapter;
DataTable ds = new DataTable(); //
//string count_rows = "SELECT TOP (1) Part_Number, SRCT_Code, Dock_Code, Package, Error_Code, Chk_Type, LogUser, LogDate, ekb_order_no, Kanban_ID FROM LogHendheld ORDER BY id DESC";
string count_rows = "SELECT Part_Number, SRCT_Code, Dock_Code, Package, Error_Code, Chk_Type, LogUser, LogDate, ekb_order_no, Kanban_ID FROM LogHendheld ";
CountAdapter = new SqlCeDataAdapter(count_rows, conn);
CountAdapter.Fill(ds);
conn.Close();
rows_update = ds.Rows.Count;
//MessageBox.Show("rows_update : " + rows_update);
if (ds.Rows.Count > 0)
{
// MessageBox.Show("rows_update_in : " + rows_update);
for (int i = 0; i < ds.Rows.Count; i++)
{
part_local = (string)ds.Rows[i]["Part_Number"];
srct_local = (string)ds.Rows[i]["SRCT_Code"];
dock_local = (string)ds.Rows[i]["Dock_Code"];
pack_local = (int)ds.Rows[i]["Package"];
error_local = (string)ds.Rows[i]["Error_Code"];
chk_local = (string)ds.Rows[i]["Chk_Type"];
// match_local = (Boolean)ds.Rows[i]["IsMatch"];
user_local = (string)ds.Rows[i]["LogUser"];
date_local = (DateTime)ds.Rows[i]["LogDate"];
//pds_local = (string)ds.Rows[i]["PDS_number"];
ekb_local = (string)ds.Rows[i]["ekb_order_no"];
kanban_local = (string)ds.Rows[i]["Kanban_ID"];
update_db_from_local();
}
}
}
}
private List <string> LoadReportNames()
{
List <string> names = new List <string>();
try
{
this.con.Open();
SqlCeDataAdapter da = new SqlCeDataAdapter("Select * from ReportsTable", this.con);
DataSet dSet = new DataSet();
da.Fill(dSet);
DataTable table = dSet.Tables[0];
foreach (DataRow row in table.Rows)
{
names.Add(row.ItemArray[0] as string);
}
this.con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error in read from DB");
}
return(names);
}
private void verifica_Admin_Adicionado() // verifica ja foi adicionado um admin
{
SqlCeConnection conexao = new SqlCeConnection("Data Source = " + Vars.base_dados);
conexao.Open();
SqlCeCommand comando = new SqlCeCommand();
comando.Connection = conexao;
comando.CommandText = "SELECT * FROM Admin";
DataTable verifica_Existencia_Admin = new DataTable();
SqlCeDataAdapter escravo = new SqlCeDataAdapter();
escravo.SelectCommand = comando;
escravo.Fill(verifica_Existencia_Admin);
// admin já foi adicionado oculta a opção adicionar admin
if (verifica_Existencia_Admin.Rows.Count != 0)
{
adicionarAdminToolStripMenuItem.Visible = false;
adicionarAdminToolStripMenuItem.Enabled = false;
}
}
private void stocksearchtxtbox_TextChanged(object sender, EventArgs e)
{
if (stocksearchtb.Text != null)
{
dboperation operation = new dboperation();
SqlCeConnection conn = null;
string selectquery = "SELECT sub_catagory.ItemId, sub_catagory.ItemName, sub_catagory.stock_Quantity, sub_catagory.purchase_price, sub_catagory.selling_price, catagory.MeasuringUnit, catagory.Catagory FROM sub_catagory INNER JOIN catagory ON catagory.CatId=sub_catagory.undercatagory WHERE sub_catagory.ItemName LIKE @itemname";
conn = operation.dbConnection(Settings.Default.DatabasePath);
try
{
SqlCeDataAdapter adapter = new SqlCeDataAdapter(selectquery, conn);
adapter.SelectCommand.Parameters.AddWithValue("@itemname", "%" + stocksearchtb.Text + "%");
DataTable dt = new DataTable();
itemdetails.AutoGenerateColumns = false;
adapter.Fill(dt);
itemdetails.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show("" + ex);
}
}
}
private void GetTotalRowFilter(string query)
{
string queryAll = "Select Count (*) AS Total FROM (Select New.DonhangID [Mã Đơn Hàng], Customer.HoTen [Tên Khách Hàng],[Order].Xong [Xuất Kho],[Order].Ngaynhap [Ngày Tạo Phiếu], [Order].Ngayxong [Ngày Xuất Kho], Product.Kyhieu [Ký Hiệu], New.[BTP Chưa in], New.[BTP Đã in], New.[Thành Phẩm], New.[Sản phẩm lỗi] from (SELECT DonhangID, Barcode, SUM(CASE WHEN LoaiID = 0000001 Then Soluong ELSE 0 END)[BTP Chưa in], SUM(CASE WHEN LoaiID = 0000002 Then Soluong ELSE 0 END)[BTP Đã in], SUM(CASE WHEN LoaiID = 0000003 Then Soluong ELSE 0 END)[Thành Phẩm], SUM(CASE WHEN LoaiID = 000004 Then Soluong ELSE 0 END)[Sản phẩm lỗi] FROM OrderDetail group by DonhangID, Barcode, Ngaysua) New join Product on New.Barcode = Product.Barcode join[Order] on[Order].DonhangID = New.DonhangID join Customer on[Order].KHID = Customer.KHID) total " + query;
using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
{
using (SqlCeCommand command = new SqlCeCommand(queryAll, connection))
{
SqlCeDataAdapter sda = new SqlCeDataAdapter(command);
DataTable dt = new DataTable();
sda.Fill(dt);
rowCountFilter = Convert.ToInt32(dt.Rows[0][0].ToString());
pageSizeFilter = rowCountFilter / rowPerPageFilter;
// if any row left after calculated pages, add one more page
if (rowCountFilter % rowPerPageFilter > 0)
{
pageSizeFilter += 1;
}
txtPageSizeFilter.Text = currentPageNumberFilter.ToString() + " /" + pageSizeFilter.ToString();
lblTotalPageFilter.Text = "Tổng số:" + rowCountFilter.ToString();
}
}
}
/// <summary>
/// Setting GridGrouping control Properties.
/// </summary>
private void GridSettings()
{
String commandstring1 = "select * from Orders";
String commandstring2 = "select * from [Order Details]";
SqlCeDataAdapter da1 = new SqlCeDataAdapter(commandstring1, connString);
SqlCeDataAdapter da2 = new SqlCeDataAdapter(commandstring2, connString);
try
{
da1.Fill(dataSet11, "Orders");
da2.Fill(dataSet11, "Order Details");
}
catch (SqlException ex)
{
ShowErrorMessage(ex);
}
//Setting Grid properties
this.gridGroupingControl1.TopLevelGroupOptions.ShowCaption = false;
//used to set multiextended selection mode in gridgrouping control.
this.gridGroupingControl1.TableOptions.ListBoxSelectionMode = SelectionMode.MultiExtended;
}
