private static bool assureTables()
{
SqlCeConnection cn = new SqlCeConnection(ConnectString());
bool ok = true;
if (cn.State == ConnectionState.Closed) cn.Open();
SqlCeCommand scmd;
try
{
if (!tableExists(cn, "Bydloes"))
{
scmd = cn.CreateCommand();
scmd.CommandText = "CREATE TABLE [Bydloes] (" +
"[Id] int NOT NULL," +
"[Imie] nvarchar(4000) NOT NULL," +
"[Nazwisko] nvarchar(4000) NOT NULL," +
"[pesel] nvarchar(4000) NULL," +
"[dowod] nvarchar(4000) NULL," +
"[nick] nvarchar(4000) NULL," +
"[jest] bit NOT NULL," +
"[adres] nvarchar(4000) NULL" +
");";
scmd.ExecuteNonQuery();
scmd = cn.CreateCommand();
scmd.CommandText = "ALTER TABLE [Bydloes] ADD CONSTRAINT [PK_Bydloes] PRIMARY KEY ([Id] );";
}
if (!tableExists(cn, "IdiotFriendlies"))
{
scmd = cn.CreateCommand();
scmd.CommandText = "CREATE TABLE [IdiotFriendlies] (" +
"[Id] int NOT NULL," +
"[Operacja] nvarchar(4000) NOT NULL," +
"[poszla] bit NOT NULL" +
");";
scmd.ExecuteNonQuery();
scmd = cn.CreateCommand();
scmd.CommandText = "ALTER TABLE [IdiotFriendlies] ADD CONSTRAINT [PK_IdiotFriendlies] PRIMARY KEY ([Id] );";
}
}
catch (SqlCeException sqlexception)
{
MessageBox.Show(sqlexception.Message, "Oh Crap.", MessageBoxButtons.OK, MessageBoxIcon.Error);
ok = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Oh Shit.", MessageBoxButtons.OK, MessageBoxIcon.Error);
ok = false;
}
finally
{
cn.Close();
}
return ok;
}
private bool ReadSetup()
{
SqlCeConnection Myconnection = null;
SqlCeDataReader dbReader = null;
Myconnection = new SqlCeConnection( connStr );
Myconnection.Open();
SqlCeCommand cmd = Myconnection.CreateCommand();
cmd.CommandText = "SELECT * FROM Setup";
dbReader = cmd.ExecuteReader();
if (dbReader.Read())
{
if (dbReader.GetString(0) == "False")
ItemCheckbox.Checked = false;
else
ItemCheckbox.Checked = true;
BarcodePrinterComboBox.Text = dbReader.GetString(1);
BatteryPrinterComboBox.Text = dbReader.GetString(2);
Myconnection.Close();
return true;
}
Myconnection.Close();
return false;
}
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 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;
}
private void deleteBtn_Click(object sender, EventArgs e)
{
SqlCeConnection conn = new SqlCeConnection(@"Data Source=c:\users\poloan\documents\visual studio 2010\Projects\LocalDBTrial\LocalDBTrial\data.sdf");
conn.Open();
Boolean queryBool = true;
try
{
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "DELETE FROM dataTbl WHERE name = @name OR id = @id ";
command.Parameters.AddWithValue("@id", idTextbox.Text);
command.Parameters.AddWithValue("@name", nameTextbox.Text);
command.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
queryBool = false;
MessageBox.Show("There was an error on your query..\n" + ex.Message.ToString());
}
finally
{
if (queryBool)
{
MessageBox.Show("Delete success");
}
else
{
MessageBox.Show("Delete failed");
}
}
conn.Close();
}
public void ReadTo(Action<SqlCeDataReader> readerAction)
{
try
{
using (var connection = new SqlCeConnection(ConnectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var command = connection.CreateCommand())
{
command.CommandText = _commandText;
SetParametersToCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
readerAction.Invoke(reader);
}
}
}
}
}
}
catch (SqlCeLockTimeoutException ex)
{
Thread.Sleep(TimeSpan.FromMilliseconds(500));
ReadTo(readerAction);
}
catch (Exception ex)
{
LogOrSendMailToAdmin(ex);
throw;
}
}
private static void ConvertProfile(IUserProfile profile, SqlCeConnection conn)
{
Console.Write("profile [{0}]...", profile.Name);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO Profiles (Name, Description, DefaultVocabularyPath, Sleep, Beep) VALUES (@Name, @Description, @DefaultVocabularyPath, @Sleep, @Beep)";
cmd.Parameters.Add(new SqlCeParameter("Name", profile.Name));
cmd.Parameters.Add(new SqlCeParameter("Description", String.Empty));
cmd.Parameters.Add(new SqlCeParameter("DefaultVocabularyPath", profile.DefaultVocabulary));
cmd.Parameters.Add(new SqlCeParameter("Sleep", profile.SleepInterval));
cmd.Parameters.Add(new SqlCeParameter("Beep", profile.Beep));
cmd.Prepare();
cmd.ExecuteNonQuery();
}
Console.WriteLine("ok");
ConvertActions(profile, conn);
foreach (IStatistics statistics in profile as IEnumerable)
{
ConvertStatistics(statistics, conn);
}
}
public static SqlCeConnection CreateDBConnection()
{
if (!File.Exists(Path.Combine(Application.StartupPath, "db.sdf")))
{
using (SqlCeEngine eng = new SqlCeEngine(dbPath))
eng.CreateDatabase();
using (SqlCeConnection conn = new SqlCeConnection(dbPath))
{
conn.Open();
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "CREATE TABLE [Beatmaps] " +
"(" +
"[Hash] nvarchar(32) NOT NULL, " +
"[Filename] nvarchar(500) NOT NULL" +
")";
cmd.ExecuteNonQuery();
cmd.CommandText = "ALTER TABLE [Beatmaps] ADD CONSTRAINT [PK_Beatmaps] PRIMARY KEY ([Hash]) ";
cmd.ExecuteNonQuery();
}
}
}
return new SqlCeConnection(dbPath);
}
public static ArrayList GetEmailList()
{
ArrayList aEmailList = new ArrayList();
using (SqlCeConnection cn = new SqlCeConnection(Database.WaldenConnect))
{
//Email FirstName LastName Active Date DateStamp
cn.Open();
using (SqlCeCommand cm = cn.CreateCommand())
{
cm.CommandText = "select FirstName,LastName, Email,EmailID,Active"
+ " from EmailList "
+ " where Active = 'Y'"
+ " order by Email";
SqlCeDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
aEmailList.Add(
dr.GetString(0) + "~"
+ dr.GetString(1) + "~"
+ dr.GetString(2) + "~"
+ dr.GetInt32(3) + "~"
+ dr.GetString(4) + "~"
);
}
return aEmailList;
}
}
}
//public MarkerNode getEquipMarkerNode(Scene scene)
public List<MarkerNode> getEquipMarkerNode(Scene scene, SqlCeConnection thisConnection)
{
int[] ids = new int[1];
//ids[0] = 1;
//return new MarkerNode(scene.MarkerTracker, "EquipementMarkerConfig.txt", ids); //ids[1]);
try
{
SqlCeCommand cmd = thisConnection.CreateCommand();
cmd.CommandText = "SELECT ID, MarkerID FROM MarkerInventory WHERE Patient=\'False\'";
SqlCeDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ids[0] = (int)rdr["ID"];
equipment.Add(new MarkerNode(scene.MarkerTracker, (String)rdr["MarkerID"], ids));
}
rdr.Close();
cmd.Dispose();
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
return equipment;
}
private static void ConvertAntonyms(IMeaning meaning, SqlCeConnection conn, Guid meaning_id)
{
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO Relations (MeaningId, Relation, Position, WordId) VALUES (@MeaningId, @Relation, @Position, @WordId)";
cmd.Parameters.Add(new SqlCeParameter("MeaningId", meaning_id));
cmd.Parameters.Add(new SqlCeParameter("Relation", SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlCeParameter("Position", SqlDbType.SmallInt));
cmd.Parameters.Add(new SqlCeParameter("WordId", SqlDbType.UniqueIdentifier));
cmd.Prepare();
int position = 0;
foreach (IWord word in meaning.Antonyms)
{
Console.Write("synonym [{0}]...", word.Text);
cmd.Parameters["Relation"].Value = "A";
cmd.Parameters["Position"].Value = ++position;
cmd.Parameters["WordId"].Value = GetWordId(word, conn);
cmd.ExecuteNonQuery();
Console.WriteLine("ok");
}
}
}
public static TPunto GetTPunto(int id, SqlCeConnection conn)
{
TPunto p = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
string sql = @"SELECT p.puntoId, p.nombre, p.edificioId, p.tag, p.cota, p.cubiculo, p.observaciones,
e.nombre AS enombre, e.grupoId, g.nombre AS gnombre, p.csnmax, p.csnmargen, p.lastcontrol
FROM puntos AS p
LEFT OUTER JOIN edificios AS e ON e.edificioId = p.edificioId
LEFT OUTER JOIN grupos AS g ON g.grupoId = e.grupoId
WHERE p.puntoId= {0}";
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = String.Format(sql, id);
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
p = GetPuntoFromDr(dr);
}
if (!dr.IsClosed)
dr.Close();
}
}
return p;
}
public void Construct_ShouldRunGivenScriptsOnDatabase()
{
var createTable = "create table TheTable(id int primary key, name nvarchar(128));";
var insertData = "insert into TheTable(id, name) values (1, 'one');";
var selectData = "select name from TheTable where id = 1;";
using (var db = new TempDBSqlCe(new[] { createTable, insertData }))
{
//---------------Set up test pack-------------------
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
using (var conn = new SqlCeConnection(db.ConnectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = selectData;
using (var rdr = cmd.ExecuteReader())
{
Assert.IsTrue(rdr.Read());
Assert.AreEqual("one", rdr["name"].ToString());
}
}
}
//---------------Test Result -----------------------
}
}
/*
public List<String> getControls(Object PtID, Object EqID)
{
List<String> controls = new List<String>();
controls.Add("RateButton");
controls.Add("Lever");
return controls;
}
*/
public List<String> getControls(Object PatientID, Object EqID, SqlCeConnection thisConnection)
{
int PatID = 0, EquipmentID = 0;
List<String> controls = new List<String>();
try
{
SqlCeCommand cmd = thisConnection.CreateCommand();
cmd.CommandText = "SELECT ID FROM MarkerInventory WHERE MarkerID=\'" + PatientID.ToString() + "\'";
SqlCeDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
PatID = (int)rdr["ID"];
}
cmd.CommandText = "SELECT ID FROM MarkerInventory WHERE MarkerID=\'" + EqID.ToString() + "\'";
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
EquipmentID = (int)rdr["ID"];
}
cmd.CommandText = "SELECT Control FROM Patient WHERE PtID=" + PatID + " and EquipID=" + EquipmentID;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
controls.Add((String)rdr["Control"]);
}
rdr.Close();
cmd.Dispose();
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
return controls;
}
private void buttonImage1_Click(object sender, EventArgs e)
{
/*
* Verificar se existe coluna e inserir caso n exista
* garantir compatibilidade com bancos velhos
*/
//CONN
SqlCeConnection conn =
new SqlCeConnection("Data Source = " + Library.appDir + "\\db\\citeluz.sdf; Password=mfrn@0830$X-PRO;");
//coluna user
conn.Open();
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "ALTER TABLE trafo ADD [user] NVARCHAR(15) DEFAULT 'TESTE';";
try
{
command.ExecuteNonQuery();
MessageBox.Show("Tabela trafo atualizada com sucesso");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void addToDatabase(string _user,string _pwd)
{
myConnection = default(SqlCeConnection);
// DataTable dt = new DataTable();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "INSERT INTO ["+storagePath.getUserTable()+"] ([username],[password]) VALUES " +
" ('" + _user
+ "','" + _pwd
+ "' ) ";
myCommand.CommandType = CommandType.Text;
try
{
myCommand.ExecuteNonQuery();
myCommand.Dispose();
MessageBox.Show("Username : "******" and password : "******" added sucessfully");
}
catch (Exception ex)
{
//MessageBox.Show(_id + " : " + _item + " : " + _name);
myCommand.Dispose();
}
myConnection.Close();
}
private void insertBtn_Click(object sender, EventArgs e)
{
SqlCeConnection conn = new SqlCeConnection(@"Data Source=c:\users\poloan\documents\visual studio 2010\Projects\LocalDBTrial\LocalDBTrial\data.sdf");
conn.Open();
Boolean queryBool = true;
try
{
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "INSERT INTO dataTbl (name,email) VALUES ( @name , @email )";
command.Parameters.AddWithValue("@name", nameTextbox.Text);
command.Parameters.AddWithValue("@email", emailTextbox.Text);
command.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
queryBool = false;
MessageBox.Show("There was an error on your query..\n" + ex.Message.ToString());
}
finally
{
if (queryBool)
{
MessageBox.Show("Insert success");
}
else
{
MessageBox.Show("Insert failed");
}
}
conn.Close();
}
private IEnumerable<PhonebookEntry> GetAllEntriesInternal()
{
using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
{
connection.Open();
using (SqlCeCommand getAllEntries = connection.CreateCommand())
{
getAllEntries.CommandText = GetAllEntriesQuery;
using (SqlCeDataReader reader = getAllEntries.ExecuteReader())
{
while (reader.Read())
{
yield return new PhonebookEntry
{
FirstName = reader.GetString(0),
LastName = reader.GetString(1),
PhoneNumber = reader.GetString(2)
};
}
}
}
}
}
static void CreateDatabase(string dbFileName, string connectionString) {
var dbFile = new FileInfo(dbFileName);
if (dbFile.Exists)
dbFile.Delete();
dbFile.Create().Close();
var script = File.ReadAllText("PocoDbSqlSchema.sql");
var splitScripts = script.Split(new[] {"GO"}, StringSplitOptions.RemoveEmptyEntries)
.Select(s => s.Replace("\n", "").Replace("\r", "")).Where(s => !String.IsNullOrWhiteSpace(s)).ToList();
using (var connection = new SqlCeConnection(connectionString)) {
connection.Open();
using (var trans = connection.BeginTransaction(IsolationLevel.Serializable)) {
foreach (var commandText in splitScripts) {
using (var command = connection.CreateCommand()) {
command.CommandText = commandText;
if (command.ExecuteNonQuery() == 0)
throw new InvalidOperationException("Failed to build db");
}
}
trans.Commit();
}
}
}
public void TestFixtureSetup()
{
// Initialize the database.
if (File.Exists("Test.sdf")) {
File.Delete("Test.sdf");
}
using (var engine = new SqlCeEngine(ConfigurationManager.ConnectionStrings["Test"].ConnectionString)) {
engine.CreateDatabase();
}
using (var conn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString)) {
var cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = "create table Users (Id int identity, Name nvarchar(250))";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table ManualIdUser (Id int, Name nvarchar(250))";
cmd.ExecuteNonQuery();
cmd.CommandText =
"create table CompositeKeyUser (Id int not null, Id2 nvarchar(250) not null, Name nvarchar(250), primary key (Id, Id2)) ";
cmd.ExecuteNonQuery();
}
}
public bool IsExist()
{
try
{
using (var connection = new SqlCeConnection(ConnectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var command = connection.CreateCommand())
{
var statement = "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = @tableName";
command.CommandText = statement;
command.Parameters.AddWithValue("tableName", tableName);
var count = Convert.ToInt32(command.ExecuteScalar());
return count > 0;
}
}
}
}
catch (SqlCeLockTimeoutException ex)
{
Thread.Sleep(TimeSpan.FromMilliseconds(500));
return IsExist();
}
catch (Exception ex)
{
LogOrSendMailToAdmin(ex);
throw;
}
}
public static TRonda GetRondaFromTag(string tag, SqlCeConnection conn)
{
TRonda r = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
var sql = @"SELECT r.rondaId, r.nombre, r.tag, r.tagf,
rp.rondaPuntoId, rp.orden, rp.puntoId,
p.nombre AS pnombre, p.edificioId, p.tag AS ptag,
e.nombre AS enombre, e.grupoId, g.nombre AS gnombre, p.cota, p.cubiculo, r.mintime, r.maxtime, p.csnmax, p.csnmargen, p.lastcontrol
FROM rondas AS r
LEFT OUTER JOIN rondaspuntos AS rp ON rp.rondaId = r.rondaId
LEFT OUTER JOIN puntos AS p ON p.puntoId = rp.puntoId
LEFT OUTER JOIN edificios AS e ON e.edificioId = p.edificioId
LEFT OUTER JOIN grupos AS g ON g.grupoId = e.grupoId
WHERE r.tag = '{0}' ORDER BY rp.orden";
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = String.Format(sql, tag);
using (SqlCeDataReader dr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (dr.HasRows)
{
r = GetRondaFromDr(dr);
}
if (!dr.IsClosed)
dr.Close();
}
}
return r;
}
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 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 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];
//}
}
internal void InsertCmd(Cmd cmd)
{
SqlCeConnection con = new SqlCeConnection(connectionString);
try
{
con.Open();
//SqlCeDataAdapter adapter = new SqlCeDataAdapter(
// "insert into cmd (name, description, path, arg) values (" +
// "'" + cmd.name + "'," +
// "'" + cmd.description + "'," +
// "'" + cmd.path + "'," +
// "'" + cmd.arg + "'" +
// ")", con);
//SqlCeCommand sqlCmd = adapter.InsertCommand;
//int count = sqlCmd.ExecuteNonQuery();
SqlCeCommand sqlCmd = con.CreateCommand();
sqlCmd.CommandText = "insert into cmd (name, description, path, arg) values (" +
"'" + cmd.name + "'," +
"'" + cmd.description + "'," +
"'" + cmd.path + "'," +
"'" + cmd.arg + "'" +
")";
int count = sqlCmd.ExecuteNonQuery();
}
finally
{
if (con != null)
{
con.Close();
}
}
}
//add issuance
public bool addIssuance(DateTime issuance_date, string license_no,
int violation_id, decimal price, int officer_id,
int issuance_status)
{
string sql_stmt;
bool result = false;
/**
sql_stmt = "CREATE TABLE issuances " +
"( " +
" issuance_date datetime," +
" license_no nvarchar(20)," +
" violation_id int," +
" price money," +
" officer_id int," +
" issuance_status int" +
") ";
**/
sql_stmt = "INSERT INTO issuances " +
" (issuance_date,license_no,violation_id,price,officer_id,issuance_status) " +
"VALUES " +
" (@id, @violation, @desc, @price, @last_mod)";
SqlCeConnection conn = new SqlCeConnection(this.conn_string);
conn.Open();
try
{
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = sql_stmt;
cmd.Parameters["issuance_date"].SqlDbType = System.Data.SqlDbType.DateTime;
cmd.Parameters["issuance_date"].Value = issuance_date;
cmd.Parameters["license_no"].SqlDbType = System.Data.SqlDbType.NVarChar;
cmd.Parameters["license_no"].Value = license_no;
cmd.Parameters["violation_id"].SqlDbType = System.Data.SqlDbType.Int;
cmd.Parameters["violation_id"].Value = violation_id;
cmd.Parameters["price"].SqlDbType = System.Data.SqlDbType.Money;
cmd.Parameters["price"].Value = price;
//cmd.Parameters["officer_id"].SqlDbType = System.Data.SqlDbType.Int;
//cmd.Parameters["officer_id"].Value = last_mod;
cmd.Parameters["issuance_status"].SqlDbType = System.Data.SqlDbType.Int;
cmd.Parameters["issuance_status"].Value = issuance_status;
result = (cmd.ExecuteNonQuery() > 0);
}
catch (Exception err)
{
string msg = err.Message;
result = false;
}
conn.Close();
return result;
}
public Image_View_Window(string item)
{
InitializeComponent();
Databox.DataContext = this;
// Images_LibraryItems.ItemsSource = Names;
DragDrop.AddDropHandler(this, oncursordrop);
this.item = item;
label1.Content = item;
// TODO: Complete member initialization
List<string> photos = new List<string>();
SqlCeConnection conn = null;
string query = "SELECT Image_Database.Image_location FROM Image_Map_to_Tags INNER JOIN Image_Database ON Image_Map_to_Tags.Image_tag_name = Image_Database.Image_name AND Image_Map_to_Tags.image_tag LIKE '"+item+"'";
string filesPath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "NatureNetDataBase_Main.sdf");
string connectionString = string.Format("Data Source=" + filesPath);
conn = new SqlCeConnection(connectionString);
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = query;
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (File.Exists(reader.GetString(0)))
{
if (!Names.Contains(reader.GetString(0)))
{
Names.Add(reader.GetString(0));
}
}
}
conn.Close();
//ObservableCollection<string> items = new ObservableCollection<string>();
//foreach (string s in Course)
//{
// items.Add(s);
Images_LibraryItems.DataContext = this;
//}
foreach (string s in Names)
{
Images_LibraryItems.Items.Add(s);
}
// Images_LibraryItems.ItemsSource = Names;
if (Names.Count == 0)
{
surfaceButton5.Visibility = Visibility.Visible;
}
//using (SqlConnection cn = new SqlConnection(connectionString))
//{
// using (SqlCommand cm = new SqlCommand(query, cn))
// {
// cn.Open();
// SqlDataReader reader = cm.ExecuteReader();
// while (reader.Read())
// {
// Course.Add(reader.GetString(0));
// }
// }
//}
}
public void loadDefinedIndexSymbolFromDB()
{
this.definedIndexSymbolList_.Clear();
SqlCeConnection connection = new SqlCeConnection(this.connectionStr_);
try
{
SqlCeCommand command = connection.CreateCommand();
connection.Open();
command.CommandText = "SELECT * FROM INDEX_TICKER_INFO";
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(command.CommandText, connection);
DataTable tb = new DataTable();
dataAdapter.Fill(tb);
//string selectionStr = " DATE = '"++"' and CODE_A = '" + itemCode1 + "' and CODE_B = '" + itemCode2 + "'";
//DataRow[] results = tb.Select(selectionStr);
//tb.Select("
foreach (DataRow row in tb.Rows)
{
string name = row["NAME"].ToString();
string krcode = row["KRCODE"].ToString();
string yahoo_ticker = row["YAHOO_TICKER"].ToString();
string bloomberg_ticker = row["BLOOMBERG_TICKER"].ToString();
string kap_ticker = row["KAP_TICKER"].ToString();
string kis_ticker = row["KIS_TICKER"].ToString();
string datasource = row["DATASOURCE"].ToString();
string time_download = row["TIME_DOWNLOAD"].ToString();
string index_type = row["INDEX_TYPE"].ToString();
this.definedIndexSymbolList_.Add(new IndexTickerInfo(name, krcode, yahoo_ticker,
bloomberg_ticker,
kap_ticker,
kis_ticker,
datasource,
time_download,
index_type
));
}
connection.Close();
}
catch (SqlCeException )
{
connection.Close();
OutputLogViewModel.addResult("DataBase Connection Error : DB Load");
throw;
}
//this.definedIndexSymbolList_.Add("VGK");
//this.definedIndexSymbolList_.Add("^VIX");
//this.definedIndexSymbolList_.Add("005930.KS");
}
private static void ConvertStatistics(IStatistics statistics, SqlCeConnection conn)
{
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO History (VocabularyId, MeaningId, ActionId, SyncMod) VALUES (@VocabularyId, @MeaningId, @ActionId, @SyncMod)";
cmd.Parameters.Add(new SqlCeParameter("VocabularyId", new Guid("ad4c2b38-80fd-4920-a727-bd6bbd24cc28")));
cmd.Parameters.Add(new SqlCeParameter("MeaningId", SqlDbType.UniqueIdentifier));
cmd.Parameters.Add(new SqlCeParameter("ActionId", SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlCeParameter("SyncMod", SqlDbType.DateTime));
cmd.Prepare();
Console.Write("modified on [{0}]...", statistics.Modified);
DateTime mininum = new DateTime(2000, 1, 1);
cmd.Parameters["MeaningId"].Value = statistics.Guid;
cmd.Parameters["ActionId"].Value = "M";
cmd.Parameters["SyncMod"].Value = statistics.Modified > mininum ? statistics.Modified : mininum;
cmd.ExecuteNonQuery();
Console.WriteLine("ok");
foreach (DateTime date in statistics.EnumerateRightAnswers())
{
Console.Write("answer right on [{0}]...", date);
cmd.Parameters["MeaningId"].Value = statistics.Guid;
cmd.Parameters["ActionId"].Value = "R";
cmd.Parameters["SyncMod"].Value = date;
cmd.ExecuteNonQuery();
Console.WriteLine("ok");
}
foreach (DateTime date in statistics.EnumerateWrongAnswers())
{
Console.Write("answer wrong on [{0}]...", date);
cmd.Parameters["MeaningId"].Value = statistics.Guid;
cmd.Parameters["ActionId"].Value = "W";
cmd.Parameters["SyncMod"].Value = date;
cmd.ExecuteNonQuery();
Console.WriteLine("ok");
}
foreach (DateTime date in statistics.EnumeratePromptAnswers())
{
Console.Write("answer prompt on [{0}]...", date);
cmd.Parameters["MeaningId"].Value = statistics.Guid;
cmd.Parameters["ActionId"].Value = "P";
cmd.Parameters["SyncMod"].Value = date;
cmd.ExecuteNonQuery();
Console.WriteLine("ok");
}
}
}
public static TAdministrador GetLogin(string login, string password, SqlCeConnection conn)
{
TAdministrador administrador = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = String.Format("SELECT * FROM administradores WHERE login = '******' AND password = '******'", login, password);
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
administrador = GetAdministradorFromDr(dr);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
}
return(administrador);
}
public static void TSave(TPrograma tp, SqlCeConnection conn)
{
string sql = "";
// primero verificamos que si el elemento está
//TPrograma ttp = GetTPrograma(tp.ProgramaId, conn);
TPrograma ttp = null;
if (ttp == null)
{
sql = @"INSERT INTO Programa(programa_id, usuario_id, estado, comentarios, fecha_programada) VALUES({0},{1},'{2}','{3}','{4:yyyy-MM-dd}')";
}
else
{
sql = @"UPDATE Programa SET usuario_id={1},estado='{2}',comentarios='{3}', fecha_programada='{4:yyyy-MM-dd}' WHERE programa_id={0}";
}
sql = String.Format(sql, tp.ProgramaId, tp.Usuario.UsuarioId, tp.Estado, tp.Comentarios, tp.FechaProgramada);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
int nrec = cmd.ExecuteNonQuery();
}
}
public static TPrograma GetTPrograma(int id, SqlCeConnection conn)
{
TPrograma programa = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM Programa WHERE programa_id = {0}", id);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
programa = new TPrograma()
{
ProgramaId = dr.GetInt32(0),
FechaProgramada = dr.GetDateTime(1),
Usuario = GetTUsuario(dr.GetInt32(2), conn),
Estado = dr.GetString(3),
Comentarios = dr.GetString(4),
Abm = dr.GetByte(5)
};
}
if (!dr.IsClosed)
{
dr.Close();
}
//if (programa != null)
//{
// // Ahora buscamos las revisiones asociadas.
// cmd.CommandText = String.Format("SELECT * FROM Revision WHERE programa_id = {0}", programa.ProgramaId);
// dr = cmd.ExecuteReader();
// while (dr.Read())
// {
// TRevision tr = CntSciTerminal.GetTRevision(dr.GetInt32(0), conn);
// if (tr != null) programa.Revisiones.Add(tr);
// }
// if (!dr.IsClosed) dr.Close();
//}
}
return(programa);
}
public static SqlCeDataAdapter GetAdaperForProcessInfosViewForDate(PTDate date)
{
SqlCeDataAdapter adapter = null;
SqlCeConnection connection = null;
try
{
connection = Connection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT Name, ActiveTime FROM ProcessInfos WHERE DateIdx=@dateIdx";
command.Parameters.Add("@dateIdx", SqlDbType.Int).Value = date.index;
command.ExecuteNonQuery();
adapter = new SqlCeDataAdapter(command);
Console.WriteLine(adapter.ToString());
}
finally
{
connection.Close();
}
return(adapter);
}
public static TIncidencia GetIncidencia(int id, SqlCeConnection conn)
{
TIncidencia i = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = String.Format("SELECT * FROM incidencias WHERE incidenciaId = {0}", id);
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
i = GetIncidenciaFromDr(dr);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
}
return(i);
}
private bool populateInvoiceStatements(DateTime openingDate, AccountStatementDataSet dataset)
{
string sql = getInvoiceRetrievalSQL(openingDate);
string errorText;
SqlCeConnection connection = Global.getDatabaseConnection(out errorText);
try
{
using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandText = sql;
using (SqlCeDataReader reader = command.ExecuteReader())
{
AccountStatementDataSet.AccountStatementRow row;
while (reader.Read())
{
row = dataset.AccountStatement.NewAccountStatementRow();
row.TransactionDate = reader.GetDateTime(1);
row.Description = "Invoice# " + reader.GetInt32(0) + " issued";
row.DebitAmount = reader.GetDecimal(2);
dataset.AccountStatement.AddAccountStatementRow(row);
}
}
}
}
catch (Exception ex)
{
string message = "An error occurred in fetching the account details from " +
"the database. The error text is as follows:\n" +
Global.getExceptionText(ex);
Cursor.Current = Cursors.Default;
MessageBox.Show(message, "Error Occurred", MessageBoxButtons.OK,
MessageBoxIcon.Error);
ErrorLogger.LogError(ex);
return(false);
}
return(true);
}
public static TGrupo GetGrupo(int id, SqlCeConnection conn)
{
TGrupo g = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = String.Format("SELECT * FROM grupos WHERE grupoId = {0}", id);
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
g = GetGrupoFromDr(dr);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
}
return(g);
}
public static TTipoDispositivo GetTTipoDispositivo(string nombre, SqlCeConnection conn)
{
TTipoDispositivo ta = null;
string sql = String.Format("SELECT * FROM TipoDispositivo WHERE nombre='{0}'", nombre);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ta = new TTipoDispositivo();
ta.TipoDispositivoId = dr.GetInt32(0);
ta.Nombre = dr.GetString(1);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(ta);
}
public List <DTOCatecismo> GetCatecismo()
{
List <DTOCatecismo> cliente = new List <DTOCatecismo>();
SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\DB\Pascuita.sdf");
conn.Open();
//commands represent a query or a stored procedure
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM catecismo;";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
cliente.Add(new DTOCatecismo(
int.Parse(reader["id_catecismo"].ToString()),
reader["catecismo"].ToString()
));
}
return(cliente);
}
public Info()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmd2 = cn.CreateCommand();
cmd2.CommandText = "SELECT * FROM opcje WHERE id = 1";
cmd2.Prepare();
SqlCeDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
serial = dr.GetString(11);
}
serial_t.Text = serial;
licence = GetDeviceID().Substring(1, 10);
deviceid.Text = licence;
this.Height = Screen.PrimaryScreen.Bounds.Height;
this.Width = Screen.PrimaryScreen.Bounds.Width;
Update();
//
// TODO: Add any constructor code after InitializeComponent call
//
cn.Close();
}
public Form71(int licence)
{
//
// Required for Windows Form Designer support
//
//lic = licence;
string connectionString;
lic = licence;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmd2 = cn.CreateCommand();
cmd2.CommandText = "SELECT * FROM opcje WHERE id = 1";
cmd2.Prepare();
SqlCeDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
transfer = dr.GetString(1);
com = dr.GetString(2);
ip = dr.GetString(3);
ufile = dr.GetString(4);
dfile = dr.GetString(5);
bdll = dr.GetString(6);
bflag = dr.GetBoolean(7);
ipflag = dr.GetBoolean(8);
port = dr.GetInt32(9);
skaner = dr.GetString(10);
}
cn.Close();
InitializeComponent();
this.Height = Screen.PrimaryScreen.Bounds.Height;
this.Width = Screen.PrimaryScreen.Bounds.Width;
Update();
}
private void FetchReferencedBy()
{
foreach (var table in Tables)
{
using (var conn = new SqlCeConnection(ConnectionString))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = @"SELECT CONSTRAINT_NAME, CONSTRAINT_TABLE_NAME, UNIQUE_CONSTRAINT_TABLE_NAME, UNIQUE_CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE UNIQUE_CONSTRAINT_TABLE_NAME='" + table.DisplayName + "'";
var dataTable = new DataTable();
using (var adapter = new SqlCeDataAdapter(cmd))
adapter.Fill(dataTable);
if (dataTable.Rows.Count == 0)
{
continue;
}
table.References = new List <ForeignKeyConstraint>(dataTable.Rows.Count);
foreach (DataRow row in dataTable.Rows)
{
var foreignKeyConstraint = new ForeignKeyConstraint
{
Name = row.Field <string>("CONSTRAINT_NAME"),
ReferenceTable = Tables.FirstOrDefault(c => c.DisplayName == row.Field <string>("CONSTRAINT_TABLE_NAME")),
};
cmd.CommandText = @"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME='" + foreignKeyConstraint.Name + "'";
foreignKeyConstraint.Column = table.Columns.Values.FirstOrDefault(c => c.DisplayName == cmd.ExecuteScalar() as string);
foreignKeyConstraint.ReferenceColumn = foreignKeyConstraint.ReferenceTable.Columns.Values.FirstOrDefault(c => c.IsPrimaryKey);
table.ReferencedBy.Add(foreignKeyConstraint);
}
}
}
}
///МАНИПУЛЯЦИИ С ТАБЛИЦЕЙ НАЗНАЧЕНИЙ///
public static void add_assingment(ComboBox task_name, ComboBox employee_name)
{
if (employee_name.Text.Length != 0 && task_name.Text.Length != 0)
{
SqlCeConnection conn = null;
try
{
conn = new SqlCeConnection(DBHelper.ConnectionString);
conn.Open();
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "INSERT INTO Assingment(ID_Task, ID_Employee) "
+ "VALUES(@ID_Task, @ID_Employee)";
command.Parameters.Add("@ID_Task", SqlDbType.Int);
command.Parameters["@ID_Task"].Value = Convert.ToInt16(DBHelper.GetDataInt("select ID from Tasks where Name = '" + task_name.Text + "'", "ID")[0]);
command.Parameters.Add("@ID_Employee", SqlDbType.Int);
command.Parameters["@ID_Employee"].Value = Convert.ToInt16(DBHelper.GetDataInt("select ID from Employee where Name = '" + employee_name.Text + "'", "ID")[0]);
command.ExecuteScalar();
command.Parameters.Clear();
}
catch
{
MessageBox.Show("Данный объект уже существует!");
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
else
{
MessageBox.Show("Все поля должны быть заполнены!");
}
}
public static List <string> Select_timestamp_Matches(string endpoint)
{
string connStr = "Data Source = mydatabase.sdf;";
List <string> line = new List <string>();
SqlCeConnection conn = new SqlCeConnection(connStr);
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = string.Format("SELECT * FROM matches WHERE endpoint = '{0}'", endpoint);
SqlCeDataReader w = cmd.ExecuteReader();
int i = 0;
while (w.Read())
{
line.Add(w["timestamp"].ToString());
i++;
}
conn.Close();
return(line);
}
public List <Credito> GetCredito()
{
List <Credito> credito = new List <Credito>();
SqlCeConnection conn = new SqlCeConnection(@"Data Source=|DataDirectory|\DB\DB_local.sdf");
conn.Open();
//commands represent a query or a stored procedure
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM creditos;";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
credito.Add(new Credito(
int.Parse(reader["id_cliente"].ToString()),
float.Parse(reader["deuda"].ToString())));
}
return(credito);
}
public static void TSave(TResponsable tu, SqlCeConnection conn)
{
string sql = "";
// primero verificamos que si el elemento está
// TResponsable tusu = GetTResponsable(tu.ResponsableId, conn);
TResponsable tusu = null;
if (tusu == null)
{
sql = "INSERT INTO Responsable(responsable_id, nombre) VALUES({0},'{1}')";
}
else
{
sql = "UPDATE Responsable SET nombre='{1}' WHERE responsable_id={0}";
}
sql = String.Format(sql, tu.ResponsableId, tu.Nombre);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
int nrec = cmd.ExecuteNonQuery();
}
}
private void button2_Click(object sender, EventArgs e)
{
bool flag = true;
try
{
SqlCeConnection conn = new SqlCeConnection("Data Source=D:\\containerinfo.sdf;Persist Security Info=False;");
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "select count(position) from container where containerid='" + textBox1.Text + "'";
SqlCeDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
if ((int)rd[0] == 0)
{
MessageBox.Show("Container does not exist");
flag = false;
}
}
if (flag)
{
cmd.CommandText = "delete from container where containerid='" + textBox1.Text + "'";
cmd.ExecuteNonQuery();
label14.Text = "Container deleted successfully!";
}
cmd.CommandText = "select * from container where yardnum='" + yardid + "' and col='" + col + "' and rowid='" + row + "'";
cmd.ExecuteNonQuery();
SqlCeDataAdapter sd = new SqlCeDataAdapter(cmd);
DataTable dt = new DataTable();
sd.Fill(dt);
dataGridView1.DataSource = dt;
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static TRonda GetTRonda(int id, SqlCeConnection conn)
{
TRonda r = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = String.Format("SELECT * FROM rondas WHERE rondaId = {0}", id);
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
r = GetRondaFromDr(dr);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
}
return(r);
}
//повернути типи тестів для того щоб користувач вибрав тип тесту який створює
public string[] GetTestTypes()
{
List <string> result = new List <string>();
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
connection.Open();
using (SqlCeCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT DISTINCT TestType FROM Test WHERE OwnerId=@id OR OwnerId=@userId";
cmd.Parameters.AddWithValue("@userId", userId);
cmd.Parameters.AddWithValue("@id", constId);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string type = dr["TestType"].ToString();
result.Add(type);
}
}
}
return(result.ToArray());
}
/// <summary>
/// Check if SchemaVersions_TBL exists in Database.
/// </summary>
/// <param name="connString"></param>
/// <returns></returns>
public static bool ExistSchemaVersion(string connString)
{
int returnValue;
using (SqlCeConnection cn = new SqlCeConnection(connString))
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES ic WHERE ic.TABLE_NAME = 'SchemaVersions_TBL'");
using (SqlCeCommand cm = cn.CreateCommand())
{
cm.CommandType = CommandType.Text;
cm.CommandText = sql.ToString();
cn.Open();
returnValue = (int)cm.ExecuteScalar();
}
}
return(returnValue > 0);
}
public Usuarios GetUser(string nombre, string pass)
{
Usuarios usuario = new Usuarios();
SqlCeConnection conn = new SqlCeConnection(@"Data Source=|DataDirectory|\DB\DB_local.sdf");
conn.Open();
//commands represent a query or a stored procedure
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM usuario WHERE usuario='" + nombre + "' && contrasenia='" + pass + "';";
SqlCeDataReader rd = cmd.ExecuteReader();
usuario.id_usuario = rd.GetInt16(0);
usuario.id_tipo_usuario = rd.GetInt16(1);
usuario.usuario = rd.GetString(2);
usuario.contrasenia = rd.GetString(3);
return(usuario);
}
private void rezlicz_b_Click(object sender, System.EventArgs e)
{
DialogResult result = MessageBox.Show("Czy napewno chcesz zatwierdziæ", "Pytanie", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
if (result == DialogResult.Yes)
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'Ok', complete = 1 WHERE id = ?";
cmdc.Parameters.Add("@1", SqlDbType.Int, 10);
cmdc.Parameters["@1"].Value = ebid;
cmdc.Prepare();
cmdc.ExecuteNonQuery();
cn.Close();
}
this.Close();
}
public static TTipoAnomalia GetTTipoAnomalia(int id, SqlCeConnection conn)
{
TTipoAnomalia ta = null;
string sql = String.Format("SELECT * FROM TipoAnomalia WHERE tipo_anomalia_id={0}", id);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ta = new TTipoAnomalia();
ta.TipoAnomaliaId = dr.GetInt32(0);
ta.Nombre = dr.GetString(1);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(ta);
}
public List <DTOAsistencia> GetAsistencia()
{
List <DTOAsistencia> cliente = new List <DTOAsistencia>();
SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\DB\Pascuita.sdf");
conn.Open();
//commands represent a query or a stored procedure
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM asistencia;";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
cliente.Add(new DTOAsistencia(
reader["id"].ToString(),
reader["codigo_alumno"].ToString()
));
}
return(cliente);
}
public static IList <TIncidencia> GetIncidencias(SqlCeConnection conn)
{
IList <TIncidencia> li = new List <TIncidencia>();
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = String.Format("SELECT * FROM incidencias");
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
li.Add(GetIncidenciaFromDr(dr));
}
if (!dr.IsClosed)
{
dr.Close();
}
}
}
return(li);
}
private bool loadPaymentDetails(int paymentID)
{
string errorText;
SqlCeConnection connection = Global.getDatabaseConnection(out errorText);
if (errorText != null)
{
Global.DisplayConnectionErrorMessage();
return(false);
}
try
{
using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Payments Where ID = " +
paymentID;
using (SqlCeDataReader reader = command.ExecuteReader())
{
reader.Read();
populateControlsWithData(reader);
}
}
}
catch (Exception ex)
{
string message = "An error occurred in loading the payment details. " +
"\nThe error text is as follows:\n" + Global.getExceptionText(ex);
SystemSounds.Hand.Play();
Cursor.Current = Cursors.Default;
MessageBox.Show(message, "Error in Loading Data", MessageBoxButtons.OK,
MessageBoxIcon.Error);
ErrorLogger.LogError(ex);
return(false);
}
return(true);
}
private void PopulateGameTypeComboBox()
{
SqlCeConnection Connection = DataBaseConnection.Instance.Connection;
GameTypeViewComboBox.Items.Clear();
SqlCeCommand cmd = Connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM Game_type";
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
GameTypeViewComboBox.Items.Add(dr["Mark"].ToString());
}
GameTypeViewComboBox.SelectedIndex = 0;
}
/// <summary>
/// A SqlCeConnection extension method that executes the data set operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>A DataSet.</returns>
public static DataSet ExecuteDataSet(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType, SqlCeTransaction transaction)
{
using (SqlCeCommand command = @this.CreateCommand())
{
command.CommandText = cmdText;
command.CommandType = commandType;
command.Transaction = transaction;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
var ds = new DataSet();
using (var dataAdapter = new SqlCeDataAdapter(command))
{
dataAdapter.Fill(ds);
}
return(ds);
}
}
public static void update_employee(DataGrid dgr, TextBox employee_name)
{
if (employee_name.Text.Length != 0)
{
DataRowView drv = (DataRowView)dgr.SelectedItem;
SqlCeConnection conn = null;
try
{
conn = new SqlCeConnection(DBHelper.ConnectionString);
conn.Open();
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "Update Employee set Name = @Name where ID = @ID";
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = (drv[0]).ToString();
command.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
command.Parameters["@Name"].Value = employee_name.Text;
command.ExecuteScalar();
command.Parameters.Clear();
}
catch
{
MessageBox.Show("Данный объект уже существует!");
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
else
{
MessageBox.Show("Все поля должны быть заполнены!");
}
}
public static TAdministrador GetTAdministrador(int id, SqlCeConnection conn)
{
TAdministrador administrador = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = String.Format("SELECT * FROM Administradores WHERE administradorId = {0}", id);
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
administrador = GetAdministradorFromDr(dr);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
}
return(administrador);
}
