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;
}
private void Modify_SELL_Load(object sender, EventArgs e)
{
SqlCeCommand cmd = Form1.con.CreateCommand();
cmd.CommandText = "SELECT ID, Marka, Tipus, Rendszam FROM Autok";
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
//fill Dataset with datas
DataSet carRes = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(carRes, "Autok");
int row = carRes.Tables["Autok"].Rows.Count - 1;
Dictionary<int, String> cars = new Dictionary<int, String>();
//fill "cars" Dictionary with cars datas from from Dataset
//Dictionary data will be displayed in the combobox
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);
}
//set combobox properties
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);
}
//fill second combobox with datas from Dictionary, set combobox properties
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_sale = new DataSet();
cmd.CommandText = "SELECT Eladasi_datum, Fizetett FROM Eladas WHERE Auto_ID = @carid and Ugyfel_ID = @clientid";
cmd.Parameters.AddWithValue("@carid", carID);
cmd.Parameters.AddWithValue("@clientid", clientID);
adapter.SelectCommand = cmd;
adapter.Fill(current_sale, "Eladas");
textBox1.Text = current_sale.Tables["Eladas"].Rows[0].ItemArray[1].ToString();
}
private void Form2_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'angajatiDataSet1.info' table. You can move, or remove it, as needed.
this.infoTableAdapter.Fill(this.angajatiDataSet1.info);
var connString = (@"Data Source=" + System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)) + @"\Angajati.sdf");
using (var conn = new SqlCeConnection(connString))
{
try
{
conn.Open();
var query = "SELECT * FROM info WHERE Prenume='" + prenume + "' AND Nume='" + nume + "'";
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);
dataAdapter.Fill(dataTable);
byte[] ap = (byte[])(ds.Tables[0].Rows[0]["Poza"]);
MemoryStream ms = new MemoryStream(ap);
pictureBox1.Image = Image.FromStream(ms);
pictureBox1.SizeMode = PictureBoxSizeMode.CenterImage;
label1.Text = dataTable.Rows[0][1].ToString();
label2.Text = dataTable.Rows[0][2].ToString();
label3.Text = dataTable.Rows[0][3].ToString();
label4.Text = dataTable.Rows[0][4].ToString();
label5.Text = dataTable.Rows[0][5].ToString();
label14.Text = (dataTable.Rows[0][7].ToString());
label21.Text = (dataTable.Rows[0][8].ToString());
textBox1.Text = (dataTable.Rows[0][9].ToString());
textBox2.Text = (dataTable.Rows[0][10].ToString());
textBox4.Text = (dataTable.Rows[0][11].ToString());
textBox5.Text = (dataTable.Rows[0][12].ToString());
textBox6.Text = (dataTable.Rows[0][13].ToString());
textBox7.Text = (dataTable.Rows[0][14].ToString());
textBox8.Text = (dataTable.Rows[0][15].ToString());
double baza = label21.Text == "" ? 0 : double.Parse(label21.Text);
textBox10.Text = ((baza *75)/1000).ToString();
textBox11.Text = ((baza * 100) / 1000).ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
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();
}
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 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;
}
}
private void frmEditReceipt_Load(object sender, EventArgs e)
{
SqlCeConnection myConnection = default(SqlCeConnection);
myConnection = new SqlCeConnection("Data source="
+ (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
+ "\\GodDB.sdf;"));
myConnection.Open();
SqlCeCommand myCommand = myConnection.CreateCommand();
myCommand.CommandText = "Select [id],[itemid],[batch],[qty],[zone],[channel],[receiptdate],[receiptby] from [receipt] " +
" where id = '" + strID + "'";
myCommand.CommandType = CommandType.Text;
DataTable dt = new DataTable();
SqlCeDataAdapter Adapter = default(SqlCeDataAdapter);
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(dt);
myConnection.Close();
if (dt.Rows.Count > 0)
{
this.txtItemId.Text = dt.Rows[0]["itemid"].ToString();
this.txtBatch.Text = dt.Rows[0]["batch"].ToString();
this.txtQty.Text = dt.Rows[0]["qty"].ToString();
this.txtZone.Text = dt.Rows[0]["zone"].ToString();
this.txtChannel.Text = dt.Rows[0]["channel"].ToString();
this.txtReceiptDate.Text = dt.Rows[0]["receiptdate"].ToString();
this.txtReceiptBy.Text = dt.Rows[0]["receiptby"].ToString();
}
dt = null;
}
public static List<SimpleRecommendationObject> GetAllSimpleRecsAsList()
{
List<SimpleRecommendationObject> simpleRecommendationObjects = new List<SimpleRecommendationObject>();
DataTable advancedRexTable = new DataTable();
SqlCeConnection conn = BackEndUtils.GetSqlConnection();
SqlCeCommand getAllRecsCommand = new SqlCeCommand(Simple_Recommendation_SQL.commandGetAllSimpleRecommendations, conn);
DataTable dataTable = new DataTable();
try {
conn.Open();
using (SqlCeDataAdapter adapter = new SqlCeDataAdapter(Simple_Recommendation_SQL.commandGetAllSimpleRecommendations, conn)) {
adapter.Fill(dataTable);
}
for (int i = 0; i < dataTable.Rows.Count; i++) {
// DataTable capturePointsTable = GetRespectiveCapturePoints(dataTable.Rows[i].ItemArray[0], conn);
object[] itemArray = dataTable.Rows[i].ItemArray;
SimpleRecommendationObject simpleRec = new SimpleRecommendationObject();
simpleRec.optionName = itemArray[1] as string;
simpleRec.isRegex = string.Equals((itemArray[2] as string), "True");
simpleRec.description = itemArray[3] as string;
simpleRec.pattern = itemArray[4] as string;
simpleRec.replacement = itemArray[5] as string;
simpleRec.fileName = itemArray[6] as string;
simpleRecommendationObjects.Add(simpleRec);
}
} finally {
conn.Close();
}
return simpleRecommendationObjects;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//Prikaži u GV-u životinje
string connectionString = WebConfigurationManager.ConnectionStrings["ZivotinjeCS"].ConnectionString;
//objekt za povezivanje
SqlCeConnection connection = new SqlCeConnection(connectionString);
//Command za dohvat podataka
SqlCeCommand command = new SqlCeCommand("SELECT * FROM zivotinje",connection);
// Adapter koji će dohvatiti podatke
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
adapter.SelectCommand = command;
//Novi data set koji će imati kopiju baze
DataSet dataSet = new DataSet();
//napuni dataset
adapter.Fill(dataSet);
//Poveži podatke
gv_zivotinje.DataSource = dataSet;
//Sadaaa
gv_zivotinje.DataBind();
}
}
public DataTable GetData(string selectCommand)
{
SqlCeConnection connection = new SqlCeConnection("Data Source=C:/Users/Administrateur/Desktop/CSharp-master/Mercure/mercure.sdf");
try
{
//string connection = "Data Source=C:/Users/Larry/Documents/Visual Studio 2013/Projects/CSharp/Mercure/mercure.sdf";
connection.Open();
string query = selectCommand;
SqlCeCommand command = new SqlCeCommand(query, connection);
DataTable table = new DataTable();
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(command);
dataAdapter.Fill(table);
return table;
}
catch (Exception e) { Console.WriteLine(e); return null; }
finally
{
connection.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");
}
}
public bool BuscarRegistros(string consulta)
{
try
{
SqlCeConnection conexion = CrearConexion();
adaptador = new SqlCeDataAdapter(consulta, conexion);
tablas = new DataSet("tablas1");
adaptador.Fill(tablas, "tramites");
conexion.Close();
return true;
// System.IO.Directory.CreateDirectory("C:\XML");
//tring url = "C:\XML\informeTramite.xml";
// tablas.WriteXml(url, XmlWriteMode.WriteSchema);
}
catch (Exception Ex)
{
Exception ExcepcionManejada = new Exception("Error al recuperar información para crear INFORME", Ex);
throw ExcepcionManejada;
return false;
}
finally
{
}
}
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());
}
}
}
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;
}
public static DataTable Select(string query, SqlCeConnection conn)
{
DataTable dt = new DataTable();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(query, conn);
adapter.Fill(dt);
return dt;
}
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 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;
}
}
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();
}
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;
}
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];
//}
}
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 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());
}
}
}
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());
}
}
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 static void Clean(string tableName)
{
if (!Program.Settings.General.UseCachedResults)
return;
string getMaxIDSql = string.Format("select max(ID) from {0};", tableName);
long? maxID = null;
using (SqlCeCommand cmd = new SqlCeCommand(getMaxIDSql, Program.GetOpenCacheConnection()))
using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count == 1)
{
if (!DBNull.Value.Equals(ds.Tables[0].Rows[0][0]))
maxID = Convert.ToInt64(ds.Tables[0].Rows[0][0]);
}
}
if (maxID != null)
{
maxID -= Program.Settings.General.CacheSize;
if (maxID > 0)
{
string deleteSql = string.Format("delete from {0} where ID <= {1};", tableName, maxID);
using (SqlCeCommand cmd = new SqlCeCommand(deleteSql, Program.GetOpenCacheConnection()))
{
cmd.ExecuteNonQuery();
}
}
}
}
public Appointment getAppointment()
{
Appointment model = new Appointment();
SqlCeCommand cmd = new SqlCeCommand("Select * from Appointment " +
"inner join RoomInformation on Appointment.RoomID = RoomInformation.RoomID " +
"inner join UserInformation on Appointment.UserID = UserInformation.User_ID where Appointment.AppointmentID = @AppointmentID", conn);
cmd.Parameters.AddWithValue("@AppointmentID", this._appointment.AppointmentID);
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
adapter.SelectCommand = cmd;
DataSet setdata = new DataSet();
adapter.Fill(setdata, "Appointment");
model.AppointmentID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[0].ToString());
model.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString());
model.UserID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[2].ToString());
model.AppointmentDate = DateTime.Parse(setdata.Tables[0].Rows[0].ItemArray[3].ToString());
model.Status = setdata.Tables[0].Rows[0].ItemArray[4].ToString();
model.Respond = setdata.Tables[0].Rows[0].ItemArray[5].ToString();
model.RoomAcc.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString());
model.RoomAcc.ApartmentName = setdata.Tables[0].Rows[0].ItemArray[7].ToString();
model.RoomAcc.RoomForSale = bool.Parse (setdata.Tables[0].Rows[0].ItemArray[8].ToString());
model.RoomAcc.RoomForRent = bool.Parse (setdata.Tables[0].Rows[0].ItemArray[9].ToString());
model.RoomAcc.RoomPrice = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[12].ToString());
model.RoomAcc.Username = setdata.Tables[0].Rows[0].ItemArray [49].ToString ();
Account AccountModel = new Account();
AccountModel.ID = model.UserID;
AccountRepository _accountRepository = new AccountRepository(AccountModel);
model.Appointee = _accountRepository.getDataByID().username;
return model;
}
public 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 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;
}
//Загрузка из таблицы гаусса
private void barButtonItem13_ItemClick(object sender, ItemClickEventArgs e)
{
string[] gauss = { "ТА", "КП", "СОС", "ДС", "ДЗ", "ВА", "ЗЗ", "ПК", "СК", "ФР", "ДП", "Оср", "ПР.ТА", "ПР.ВА",
"ВР", "Вал.пр.", "КрУр", "Пр.прод", "Проч.д", "Проч.р", "ЧП", "RСовК", "Пр.до_нал.", "Себ.прод.", "НерПр",
"d1", "d2", "d3", "d4", "d5", "d6", "d7", "d8", "d9", "d10" };
try
{
SqlCeConnection conn;
conn = new SqlCeConnection(@"Data Source = |DataDirectory|\FANZ.sdf");
conn.Open();
SqlCeDataAdapter adapter3;
adapter3 = new SqlCeDataAdapter("SELECT x1, x2, x3, x4, x5, x6, x7, x8, x9, x10, x11, x12, x13, x14, x15, x16, x17, x18, x19, x20, x21, x22, x23, x24, x25, X26, X27, X28, X29, X30, X31, X32, X33, X34, X35 FROM Gauss", conn);
DataTable dt3 = new DataTable();
adapter3.Fill(dt3);
gridControl3.DataSource = dt3;
conn.Close();
#region оформляем грид
for (int r = 0; r < gauss.Count(); r++)
{
gridView3.Columns[r].Caption = gauss[r];
}
for (int t = 0; t < gridView3.Columns.Count; t++)
{
gridView3.Columns[t].AppearanceHeader.Options.UseTextOptions = true;
gridView3.Columns[t].AppearanceHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridView3.Columns[t].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridView3.Columns[t].BestFit();
}
foreach (DevExpress.XtraGrid.Columns.GridColumn column in gridView3.Columns)
{
column.OptionsColumn.AllowSort = DefaultBoolean.False;
}
#endregion
}
catch (Exception q)
{
gridControl3.DataSource = null;
MessageBox.Show(q.Message, "ФАНЗ", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
/// <summary>
/// Ask the user if they would like to add a missing value to the table
/// </summary>
/// <param name="tbl">The table to check if a value exists</param>
/// <param name="text">The value to insert to the table</param>
public static void InsertRecord(DataTable tbl, string text)
{
string tableName = tbl.TableName.ToString();
string columnName = tbl.Columns[0].ColumnName.ToString();
string sql = "SELECT * FROM " + tableName + " ORDER BY " + columnName;
if (tbl.Select(columnName + " = '" + text.Replace("'", "''") + "'").Length == 0)
{
DialogResult dr = MessageBox.Show("Would you like to add '" + text + "' to the list?", "Add New Value", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
switch (dr)
{
case DialogResult.Yes:
tbl.Rows.Add(new Object[] { text });
SqlCeConnection cn = new SqlCeConnection(Data.Connection());
SqlCeCommandBuilder scb = default(SqlCeCommandBuilder);
SqlCeDataAdapter sda = new SqlCeDataAdapter(sql, cn);
sda.TableMappings.Add("Table", tableName);
scb = new SqlCeCommandBuilder(sda);
sda.Update(tbl);
dynamic dcFormatString = new DataColumn(columnName, typeof(string));
tbl.Rows.Clear();
DataColumnCollection columns = tbl.Columns;
if (columns.Contains(columnName) == false)
{
tbl.Columns.Add(dcFormatString);
}
using (var da = new SqlCeDataAdapter(sql, Connection()))
{
da.Fill(tbl);
}
tbl.DefaultView.Sort = columnName + " asc";
break;
case DialogResult.No:
break;
}
}
}
private void txtId_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyData == Keys.Enter)
{
e.SuppressKeyPress = true;
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");
}
}
}
public static DataSet ReturnLogRows(int intStartID = 0, int intNumRows = 0, string strLevels = "all", int intServerID = -1)
{
DataSet ds = new DataSet();
SqlCeCommand command = connLocal.CreateCommand();
//We need to limit the number of rows or requests take an age and crash browsers
if (intNumRows == 0)
{
intNumRows = 1000;
}
//Build our SQL
StringBuilder strSQL = new StringBuilder();
strSQL.Append("SELECT ");
if (intNumRows > 0)
{
strSQL.Append("TOP(" + intNumRows.ToString() + ") ");
}
strSQL.Append("* FROM Log ");
strSQL.Append("WHERE 1=1 ");
if (intStartID > 0)
{
strSQL.Append("AND LogID > " + intStartID.ToString() + " ");
}
if (strLevels != "all")
{
strSQL.Append("AND LogLevel = '" + strLevels + "' ");
}
if (intServerID > -1)
{
strSQL.Append("AND ServerID = " + intServerID.ToString() + " ");
}
strSQL.Append("ORDER BY LogDateTime DESC, LogID ASC");
command.CommandText = strSQL.ToString();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(command);
adapter.Fill(ds);
return(ds);
}
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();
}
}
private void RefreshResultsToGrid()
{
try
{
string sql = "SELECT NBR_VALUE, COLOR_ID, RESULT_ID FROM GraphDataResults";
System.Data.DataTable dt = new System.Data.DataTable();
using (var da = new SqlCeDataAdapter(sql, Scripts.Data.Connection()))
{
da.Fill(dt);
}
dt.DefaultView.Sort = "[RESULT_ID] DESC";
dgvGraphDataResults.DataSource = dt.DefaultView;
dgvGraphDataResults.AutoGenerateColumns = false;
dgvGraphDataResults.Columns.Clear();
dgvGraphDataResults.AllowUserToAddRows = false;
DataGridViewTextBoxColumn txtResultColor = new DataGridViewTextBoxColumn();
txtResultColor.Width = 0;
txtResultColor.DataPropertyName = "COLOR_ID";
txtResultColor.Name = "COLOR_ID";
txtResultColor.Visible = false;
dgvGraphDataResults.Columns.Add(txtResultColor);
DataGridViewTextBoxColumn txtResultNumber = new DataGridViewTextBoxColumn();
txtResultNumber.Width = 100;
txtResultNumber.DataPropertyName = "NBR_VALUE";
txtResultNumber.Name = "NBR_VALUE";
txtResultNumber.HeaderText = "Results";
txtResultNumber.Visible = true;
txtResultNumber.ReadOnly = true;
dgvGraphDataResults.Columns.Add(txtResultNumber);
dgvGraphDataResults.Columns[1].DefaultCellStyle.ForeColor = System.Drawing.Color.White;
dgvGraphDataResults.CellFormatting += dgvGraphDataResults_CellFormatting;
dgvGraphDataResults.CellEndEdit += dgvGraphDataResults_CellEndEdit;
}
catch (Exception ex)
{
ErrorHandler.DisplayMessage(ex);
}
}
private void GetAllData(int currentPageNumber, int rowPerPage, string KHID)
{
DataTable dtMain = new DataTable();
int skipRecord = currentPageNumber - 1;
if (skipRecord != 0)
{
skipRecord = skipRecord * rowPerPage;
}
if (dgvOrder.Rows.Count > 0)
{
dgvOrder.DataSource = null;
}
string query = "Select DonhangID [Mã Đơn Hàng], NgayGD [Ngày], Customer.HoTen [Tên Khách Hàng], Product.Kyhieu [Ký Hiệu],Soluong [Số Lượng], Ghichu [Ghi Chú] from CustomerOrder join Product on CustomerOrder.Barcode = Product.Barcode join Customer on CustomerOrder.KHID=Customer.KHID where CustomerOrder.KHID = '" + KHID + "' order by CustomerOrder.Ngaysua DESC" + " OFFSET " + skipRecord.ToString() + " ROWS FETCH NEXT " + rowPerPage.ToString() + " ROWS ONLY; ";
using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
{
using (SqlCeCommand command = new SqlCeCommand(query, connection))
{
SqlCeDataAdapter sda = new SqlCeDataAdapter(command);
DataTable dt = new DataTable();
sda.Fill(dt);
dtMain.Merge(dt);
dgvOrder.DataSource = dtMain;
dgvOrder.Columns[1].Visible = false;
dgvOrder.Columns[2].Width = 130;
dgvOrder.Columns[2].DefaultCellStyle.Format = "dd/MM/yyyy HH:mm:ss";
dgvOrder.Columns[3].ReadOnly = true;
dgvOrder.Columns["Ký Hiệu"].Width = 60;
dgvOrder.Columns["Ký Hiệu"].ReadOnly = true;
dgvOrder.Columns["Số Lượng"].Width = 80;
this.dgvOrder.Columns["Số Lượng"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvOrder.Columns["Số Lượng"].ReadOnly = true;
dgvOrder.Columns[5].ReadOnly = true;
dgvOrder.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
}
if (rowCount > 0)
{
}
}
/// <summary>
/// Required Method for Grid Settings
/// </summary>
void GridSettings()
{
#region Datasource
String commandstring = "select * from Products";
SqlCeDataAdapter da = new SqlCeDataAdapter(commandstring, connString);
DataSet ds = new DataSet();
da.Fill(ds, "Customers");
gridGroupingControl1.DataSource = ds.Tables[0];
#endregion
this.gridGroupingControl1.TableControl.DpiAware = true;
this.gridGroupingControl1.GridVisualStyles = Syncfusion.Windows.Forms.GridVisualStyles.Metro;
this.gridGroupingControl1.TopLevelGroupOptions.ShowCaption = false;
this.gridGroupingControl1.TableOptions.ListBoxSelectionMode = SelectionMode.MultiExtended;
this.gridGroupingControl1.TopLevelGroupOptions.ShowAddNewRecordBeforeDetails = false;
//Change the header text for column
#region HeaderText
foreach (GridColumnDescriptor col in this.gridGroupingControl1.TableDescriptor.Columns)
{
Regex rex = new Regex(@"\p{Lu}");
int index = rex.Match(col.MappingName.Substring(1)).Index;
string name = "";
while (index > 0)
{
name += col.MappingName.Substring(0, index + 1) + " ";
string secondName = col.MappingName.Substring(index + 1);
index = rex.Match(secondName.Substring(1)).Index;
while (index > 0)
{
name += secondName.Substring(0, index + 1) + " ";
index = rex.Match(col.MappingName.Substring(name.Replace(" ", "").Length).Substring(1)).Index;
}
}
name += col.MappingName.Substring(name.Replace(" ", "").Length);
col.HeaderText = name;
}
#endregion
}
public DataTable dajTabeluSaArtiklima()
{
try
{
using (SqlCeConnection cn = new SqlCeConnection(konekcija))
{
SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Artikli", cn);
SqlCeDataAdapter da = new SqlCeDataAdapter();
da.SelectCommand = cmd;
DataTable dataTable = new DataTable();
da.Fill(dataTable);
return(dataTable);
}
}
catch (Exception ex)
{
Console.WriteLine("nema tabele!!! " + ex.Message);
return(new DataTable());
}
}
public DataTable getBloodGroupOfStudent(string group)
{
try
{
DataTable dt = new DataTable();
con.ConnectionString = constring.getConnection();
if (ConnectionState.Closed == con.State)
{
con.Open();
}
SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT Name,StudentMobileNo,PresentAddress FROM StudentProfile WHERE BloodGroup = '" + group + "'", con);
a.Fill(dt);
con.Close();
return(dt);
}
catch
{
throw;
}
}
public DataSet SelectQuestion(string table, string column, string param)
{
con = new SqlCeConnection(source);
dataSet = new DataSet();
if (param == "*")
{
var f = @"Select * From " + table;
cmd = new SqlCeCommand(f, con);
}
else
{
var f = @"Select * From " + table + " Where [" + column + "] = '" + param + "'";
cmd = new SqlCeCommand(f, con);
}
adapter = new SqlCeDataAdapter(cmd);
adapter.Fill(dataSet);
return(dataSet);
}
/* SE OBTIENE LA VIDA DEL JUGADOR DADO SU ID (NO IMPLEMENTADO) */
public int GetVidaJugadorId(int idJugador)
{
int vida;
try
{
SqlCeConnection Conexion = GetSqlConexion();
Conexion.Open();
SqlCeDataAdapter CMD = new SqlCeDataAdapter("SELECT * FROM \"Jugadores\" WHERE Id_Jugador = " + idJugador, Conexion);
DataSet DS = new DataSet();
CMD.Fill(DS, "Table");
DataTable tablaPersona = DS.Tables[0];
vida = (int)tablaPersona.Rows[0]["Vida_Jugador"];
Conexion.Close();
}
catch
{
vida = -1;
}
return(vida);
}
/* DADO EL ID DEL USUARIO OBTIENE SU NOMBRE, EN CASO DE QUE OCURRA UNA EXCEPCION RETORNARA ~ */
public String GetNombreUsuario(int idUsuario)
{
String res;
try
{
SqlCeConnection Conexion = GetSqlConexion();
Conexion.Open();
SqlCeDataAdapter CMD = new SqlCeDataAdapter("SELECT * FROM \"Usuarios\" WHERE Id_Usuario = " + idUsuario, Conexion);
DataSet DS = new DataSet();
CMD.Fill(DS, "Table");
DataTable tablaPersona = DS.Tables[0];
res = (String)tablaPersona.Rows[0]["Nombre_Usuario"];
Conexion.Close();
}
catch
{
res = "~";
}
return(res);
}
// alter table Testbeds alter column ID identity (0,1)
// There must be one entry in the Testbeds table or the ID will start over at 0.
public int GetNextID()
{
DataTable queryResultTable = new DataTable();
var adapter = new SqlCeDataAdapter("select max(ID) as maxID from Testbeds", ConnectionManager.connection);
try {
adapter.Fill(queryResultTable);
} catch (Exception ex) {
DebugLog.DebugLog.Log("Testbeds.GetNextID failed: " + ex);
}
foreach (DataRow row in queryResultTable.Rows)
{
if (row["maxID"] is DBNull)
{
ResetIDSeed();
return(0);
}
return((int)row["maxID"] + 1);
}
return(-1);
}
private DataTable GetEmployeeTable()
{
if (!LayoutControl.IsInDesignMode)
{
DataSet ds = new DataSet();
var connectionstring = string.Format(@"Data Source = {0}", LayoutControl.FindFile("AdventureWorksExt.sdf"));
using (SqlCeConnection connection = new SqlCeConnection(connectionstring))
{
connection.Open();
SqlCeDataAdapter sda = new SqlCeDataAdapter("Select * from HumanResources_Employee", connection);
sda.Fill(ds, "Employee");
}
ds.Relations.Add(new DataRelation("Employee_Relation", ds.Tables["Employee"].Columns["EmployeeID"], ds.Tables["Employee"].Columns["ManagerID"], false));
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
}
return(null);
}
public DataSet GetRegionsFromSDF_V2()
{
string query = "SELECT DISTINCT [codice_regione],[denominazione_regione] FROM [comuni_2018] ORDER BY [denominazione_regione] 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);
}
private void Expensemain_Load(object sender, EventArgs e)
{
showit();
SqlCeConnection con5 = new SqlCeConnection(Properties.Settings.Default.conne);
SqlCeDataAdapter ad19 = new SqlCeDataAdapter("SELECT Shop_name from [vendor]", con5);
DataSet ds19 = new DataSet();
ad19.Fill(ds19);
salution.DataSource = ds19.Tables[0];
salution.DisplayMember = "Shop_name";
salution.ValueMember = "Shop_name";
SqlCeConnection con7 = new SqlCeConnection(Properties.Settings.Default.conne);
SqlCeDataAdapter ad10 = new SqlCeDataAdapter("SELECT Expense_acc from [expenses]", con7);
DataSet ds10 = new DataSet();
ad10.Fill(ds10);
comboBox2.DataSource = ds10.Tables[0];
comboBox2.DisplayMember = "Expense_acc";
comboBox2.ValueMember = "Expense_acc";
}
protected void Page_Load(object sender, EventArgs e)
{ //Kreiraj novu konekciju sa podacima iz web.config-a
SqlCeConnection conn = new SqlCeConnection(WebConfigurationManager.ConnectionStrings["faks"].ConnectionString);
//Novi command objekt koji će pročitati sve studente
SqlCeCommand comm = new SqlCeCommand("SELECT * from student");
comm.Connection = conn;
//Kreiraj data adapter koji ima zadatak napuniti dataset kao izvor podataka
SqlCeDataAdapter da = new SqlCeDataAdapter(comm);
//Dataset kao komponenta koja će služiti kao izvor za GView
DataSet ds = new DataSet();
//Napuni podatke Data seta sa adapterom
//Fill metoda će u pozadini izvesti izvršavanje SELECT-a i dobivene rez proslijediti u dataset
//conn.Open(); Adapter će automatski otvoriti i zatvoti kon
da.Fill(ds);
//postavi za izvor podataka naš set
gv_studenti.DataSource = ds;
//refreshaj podatke u gridu
gv_studenti.DataBind();
}
private void PopulateTeamComboBox()
{
SqlCeConnection Connection = DataBaseConnection.Instance.Connection;
TeamViewComboBox.Items.Clear();
SqlCeCommand cmd = Connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM Team";
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
TeamViewComboBox.Items.Add(dr["Name"].ToString());
}
TeamViewComboBox.SelectedIndex = 0;
}
public void bindgirdview()
{
using (var connection = new SqlCeConnection(@"Data Source = |DataDirectory|\WalletDB.sdf"))
{
if (txtConsumerNo.Text != "")
{
string consumerno = txtConsumerNo.Text;
string query = string.Format("Select ConsumerNo,BillNo,Amount,Date,Status from BillPaid where ConsumerNo = {0}", consumerno);
SqlCeDataAdapter das = new SqlCeDataAdapter(query, connection);
DataTable dt = new DataTable();
das.Fill(dt);
dataGridBillPaid.DataSource = dt;
}
else
{
string message = "Please enter consumer no";
MessageBox.Show(message);
}
}
}
private void Edit_Appointment_Load(object sender, EventArgs e)
{
string cmdstr = "SELECT AP_ID FROM APPOINTMENT";
try
{
SqlCeConnection sqlcon = new SqlCeConnection(constr);
SqlCeCommand sqlcmd = new SqlCeCommand(cmdstr, sqlcon);
SqlCeDataAdapter da = new SqlCeDataAdapter(sqlcmd);
DataTable dt = new DataTable();
sqlcon.Open();
da.Fill(dt);
comboBox1.DataSource = dt;
comboBox1.DisplayMember = "AP_ID";
sqlcon.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public DataTable read()
{
#region conect to db sentax sql read db and view to gridview
//اتصال به بانک اطلاعاتی
SqlCeConnection myConnection = new SqlCeConnection();
myConnection.ConnectionString = @"Data Source=C:\Users\YAS\Desktop\Construction_workers\DBconstructionworkers.sdf;Password=!123admin;Persist Security Info=True";
//insert into tabelname() valu('',''.'');
SqlCeCommand myComment = new SqlCeCommand();
myComment.Connection = myConnection;
myComment.CommandText = "select * from [Employees]";
DataTable myDataTable = new DataTable();
SqlCeDataAdapter myDataAdapter = new SqlCeDataAdapter();
myDataAdapter.SelectCommand = myComment;
myDataAdapter.Fill(myDataTable);
return(myDataTable);
#endregion
}
/* SE OBTIENE EL NOMBRE DE UN JUGADOR DADO SU ID
* RETORNA ~ SI OCURRE UNA EXCEPCION
*/
public String GetNombreJugador(int idJugador)
{
String ret;
try
{
SqlCeConnection Conexion = GetSqlConexion();
Conexion.Open();
SqlCeDataAdapter CMD = new SqlCeDataAdapter("SELECT * FROM \"Jugadores\" WHERE Id_Jugador = " + idJugador, Conexion);
DataSet DS = new DataSet();
CMD.Fill(DS, "Table");
DataTable tablaPersona = DS.Tables[0];
ret = (String)tablaPersona.Rows[0]["Nombre_Jugador"];
Conexion.Close();
}
catch
{
ret = "~";
}
return(ret);
}
public DataTable rptActiveAgent_GetHistoryDataOfDates()
{
// strClientConnectionString = AppDomain.CurrentDomain.BaseDirectory + "\\VMukti.sdf";
// strClientConnectionString = @"Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "\\VMukti.sdf";
// System.Reflection.Assembly asm = System.Reflection.Assembly.GetExecutingAssembly();
strClientConnectionString = @"Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "\\VMukti.sdf";
SqlCeConnection ce = new SqlCeConnection(strClientConnectionString);
ce.Open();
DataTable dt4ActiveRecord = new DataTable();
// string strSelectCommand = "Select UserName as AgentName , CampaignID as Campaign , GroupID as Group , RoleID as Role , Starttime as SessionStartTime From AgentStatusInfo";
string strSelectCommand = "Select UserName as AgentName,CampaignName as Campaign,GroupName as [Group],RoleName as Role, starttime as SessionStart From AgentStatusInfo";
SqlCeDataAdapter da4ActiveAgent = new SqlCeDataAdapter(strSelectCommand, ce);
da4ActiveAgent.Fill(dt4ActiveRecord);
return(dt4ActiveRecord);
// return ExecuteDataSet("spDialTable", CommandType.StoredProcedure, CreateParameter("@dtStart", SqlDbType.DateTime, dtStartDate), CreateParameter("@dtEnd", SqlDbType.DateTime, dtEndDate));
}
/* SE OBTIENE EL MEJOR PUNTAJE DEL JUGADOR DADO SU NOMBRE */
public int GetPuntajeJugadorNombre(String nombreJugador)
{
int puntaje;
try
{
SqlCeConnection Conexion = GetSqlConexion();
Conexion.Open();
SqlCeDataAdapter CMD = new SqlCeDataAdapter("SELECT * FROM \"Jugadores\" WHERE Nombre_Jugador = '" + nombreJugador + "'", Conexion);
DataSet DS = new DataSet();
CMD.Fill(DS, "Table");
DataTable tablaPersona = DS.Tables[0];
puntaje = (int)tablaPersona.Rows[0]["Mejor_Puntaje_Jugador"];
Conexion.Close();
}
catch
{
puntaje = -1;
}
return(puntaje);
}
public void load_list_view()
{
str = "select eid,ename,eaddress,edesig,ephoto from emp";
cmd = new SqlCeCommand(str, con);
da = new SqlCeDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "emp");
listView1.Items.Clear();
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
string[] s = new string[5];
s[0] = ds.Tables[0].Rows[i][0].ToString();
s[1] = ds.Tables[0].Rows[i][1].ToString();
s[2] = ds.Tables[0].Rows[i][2].ToString();
s[3] = ds.Tables[0].Rows[i][3].ToString();
s[4] = ds.Tables[0].Rows[i][4].ToString();
ListViewItem lv = new ListViewItem(s);
this.listView1.Items.Add(lv);
}
}
internal static clsGround getGround(string groundName)
{
clsGround ground;
SqlCeCommand com = new SqlCeCommand("SELECT * FROM tblStadium WHERE stadiumName=@p1", connection.CON);
com.Parameters.AddWithValue("@p1", groundName);
SqlCeDataAdapter ad = new SqlCeDataAdapter(com);
DataSet ds = new DataSet("tblStadium");
ad.Fill(ds, "tblStadium");
DataRow r = ds.Tables["tblStadium"].Rows[0];
List <clsEnds> ends;
ends = clsEnds.getEndsByGroundId(Convert.ToInt32(r[0].ToString()));
ground = new clsGround(Convert.ToInt32(r[0].ToString()), r[1].ToString(), Convert.ToInt32(r[2]), Convert.ToInt32(r[3]), ends[0], ends[1]);
return(ground);
}
public DataTable getEduBackOfAStudent(string id)
{
try
{
DataTable dt = new DataTable();
con.ConnectionString = constring.getConnection();
if (ConnectionState.Closed == con.State)
{
con.Open();
}
SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT * FROM EducationalBackground WHERE ID = '" + id + "'", con);
a.Fill(dt);
con.Close();
return(dt);
}
catch
{
throw;
}
}
private void Edit_Category_Load(object sender, EventArgs e)
{
string cmdstr = "SELECT CC_ID FROM COUNSELOR_CATEGORIES";
try
{
SqlCeConnection sqlcon = new SqlCeConnection(constr);
SqlCeCommand sqlcmd = new SqlCeCommand(cmdstr, sqlcon);
SqlCeDataAdapter da = new SqlCeDataAdapter(sqlcmd);
DataTable dt = new DataTable();
sqlcon.Open();
da.Fill(dt);
comboBox1.DataSource = dt;
comboBox1.DisplayMember = "CC_ID";
sqlcon.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//showing the book info in the right panel
private void rBookComboBox_DropDownClosed(object sender, EventArgs e)
{
if (rBookComboBox.Text != "")
{
string query = "SELECT [RBook ID],[Book Name],Author,[Book ISBN],Genre,Price,Audience,Cover FROM RBooks WHERE [Book Name] = '" + rBookComboBox.Text + "'";
SqlCeDataAdapter adapt = new SqlCeDataAdapter(query, databaseConnection);
DataTable books = new DataTable();
databaseConnection.Open();
adapt.Fill(books);
bookIDBox.Text = books.Rows[0]["RBook ID"].ToString();
bookNameBox.Text = books.Rows[0]["Book Name"].ToString();
authorBox.Text = books.Rows[0]["Author"].ToString();
ISBNBox.Text = books.Rows[0]["Book ISBN"].ToString();
genreBox.Text = books.Rows[0]["Genre"].ToString();
priceBox.Text = books.Rows[0]["Price"].ToString();
audienceBox.Text = books.Rows[0]["Audience"].ToString();
bookCoverImg.Source = new BitmapImage(new Uri(books.Rows[0]["Cover"].ToString()));
databaseConnection.Close();
}
finalPriceBox.Text = priceBox.Text;
}
