internal void getTrackingState(string SolutionName, string ProjectName, ref string LocalStoredPath, ref string RemoteStoredPath, ref bool bOverride)
{
try
{
SqlCeResultSet rsResult = null;
cmd.CommandText = "select LocalStoredPath, RemoteStoredPath, ManualNotTracked from [Projects] where [SolutionName] = " +
SolutionName + " and [ProjectName] = " + ProjectName;
rsResult = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rsResult == null)
{
MessageBox.Show("Failed to execute command to get tracking info?");
return;
}
if (rsResult.RecordsAffected != 1)
{
MessageBox.Show("Seems we have too many rows in our tracker for this Solution..");
return;
}
rsResult.ReadFirst();
LocalStoredPath = (string)rsResult.GetString(0);
RemoteStoredPath = (string)rsResult.GetString(1);
bOverride = (bool)rsResult.GetBoolean(2);
}
catch (Exception ex)
{
}
}
public DataTable GetGroups(string sqlText)
{
DataTable dt = new DataTable();
DataColumn dc = new DataColumn("ID_AssetGroup", Type.GetType("System.Int64"));
dt.Columns.Add(dc);
dc = new DataColumn("Name", Type.GetType("System.String"));
dt.Columns.Add(dc);
DataRow dr;
using (SqlCeCommand cmd = new SqlCeCommand(sqlText, _CEConnection))
{
cmd.CommandType = CommandType.Text;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
while (rs.Read())
{
dr = dt.NewRow();
dr["ID_AssetGroup"] = rs.GetInt64(rs.GetOrdinal("ID_AssetGroup"));
dr["Name"] = rs.GetString(rs.GetOrdinal("Name"));
dt.Rows.Add(dr);
}
}
}
dt.AcceptChanges();
return(dt);
}
public static Price GetPrice(bool summerTariff, string ticketType, string vehicleType)
{
if (ticketType == null || ticketType.Length == 0 || vehicleType == null || vehicleType.Length == 0)
{
return(null);
}
string sql = "select charge, summerTariff, ticketType, vehicleType from price where summerTariff=@summerTariff and ticketType=@ticketType and vehicleType=@vehicleType";
Price price = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@summerTariff", summerTariff);
cmd.Parameters.AddWithValue("@ticketType", ticketType);
cmd.Parameters.AddWithValue("@vehicleType", vehicleType);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordCharge = rs.GetOrdinal("charge");
int ordSummerTariff = rs.GetOrdinal("summerTariff");
int ordTicketType = rs.GetOrdinal("ticketType");
int ordVehicleType = rs.GetOrdinal("vehicleType");
rs.ReadFirst();
price = new Price();
price.Charge = rs.GetDecimal(ordCharge);
price.SummerTariff = rs.GetBoolean(ordSummerTariff);
price.TicketType = rs.GetString(ordTicketType);
price.VehicleType = rs.GetString(ordVehicleType);
}
return(price);
}
public static VehicleType GetVehicleType(String vehicleType)
{
if (vehicleType == null || vehicleType.Length == 0)
{
return(null);
}
string sql = "select type from vehicleType where type=@vehicleType";
VehicleType type = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@vehicleType", vehicleType);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordType = rs.GetOrdinal("type");
rs.ReadFirst();
type = new VehicleType();
type.Type = rs.GetString(ordType);
}
return(type);
}
public static List <VehicleType> GetAllVehicleTypes()
{
List <VehicleType> vehicleTypes = new List <VehicleType>();
string sql = "select type from vehicleType";
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordType = rs.GetOrdinal("type");
rs.ReadFirst();
VehicleType vehicleType = new VehicleType();
vehicleType.Type = rs.GetString(ordType);
vehicleTypes.Add(vehicleType);
while (rs.Read())
{
vehicleType = new VehicleType();
vehicleType.Type = rs.GetString(ordType);
vehicleTypes.Add(vehicleType);
}
}
return(vehicleTypes);
}
public static Vehicle GetVehicle(String licencePlates)
{
if (licencePlates == null || licencePlates.Length == 0)
{
return(null);
}
string sql = "select licencePlates, vehicleType, checkedIn, checkedInDate from vehicle where licencePlates=@licencePlates";
Vehicle vehicle = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@licencePlates", licencePlates);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordLicencePlates = rs.GetOrdinal("licencePlates");
int ordVehicleType = rs.GetOrdinal("vehicleType");
int ordCheckedIn = rs.GetOrdinal("checkedIn");
int ordCheckedInDate = rs.GetOrdinal("checkedInDate");
rs.ReadFirst();
vehicle = new Vehicle();
vehicle.LicencePlates = rs.GetString(ordLicencePlates);
vehicle.VehicleType = rs.GetString(ordVehicleType);
vehicle.CheckedIn = rs.GetBoolean(ordCheckedIn);
vehicle.CheckedInDate = rs.GetDateTime(ordCheckedInDate);
}
return(vehicle);
}
public bool Exist(string word, Encoding wordEncoding)
{
if (string.IsNullOrEmpty(word))
return true;
if (wordEncoding != Encoding.Unicode)
word = UnicodeConverter.Decode(word, wordEncoding);
using (var conn = new SqlCeConnection(NlpHelper.DatabaseConnectionString))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
try
{
var sqlCeCommand = new SqlCeCommand(DatabaseQuery.SelectIdFromFormsWhereForm, conn)
{CommandType = CommandType.Text};
sqlCeCommand.Parameters.AddWithValue("@form", word);
SqlCeResultSet resultSet =
sqlCeCommand.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Insensitive);
return resultSet.HasRows;
}
catch (SqlCeException sqlexception)
{
Console.WriteLine("Error form: {0}", sqlexception.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error form: {0}", ex.Message);
}
}
return false;
}
/// <summary>
/// Загрузка данных о игроках и проверка пароля, чтобы можно было
/// загрузить данные о нем.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
var con = new SqlCeConnection("DataSource=|DataDirectory|\\Database.sdf");
var sqlCommand = new SqlCeCommand("select * from users", con);
con.Open();
var set = sqlCommand.ExecuteResultSet(ResultSetOptions.None);
while (set.Read()) {
var o = set["UserId"];
var o1 = set["Login"];
var o2 = set["Password"];
if (o1.ToString() == textBox1.Text
&& o2.ToString() == textBox2.Text) {
con.Close();
ok = true;
form1.User = new Users {
Login = o1.ToString(),
Password = o2.ToString()
};
Close();
return;
}
}
con.Close();
label3.Text = "Пароль или имя не верны";
}
public static SqlCeResultSet viewdata(string komenda)
{
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = polaczenie;
cmd.CommandText = komenda;
return cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
}
public ForgotPassword(string userID)
{
InitializeComponent();
WindowStartupLocation = System.Windows.WindowStartupLocation.CenterScreen;
txtUsername.Text = userID;
if (!string.IsNullOrWhiteSpace(txtUsername.Text))
{
txtUsername.IsReadOnly = true;
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("Select * from Accounts where userID = @userID", conn))
{
cmd.Parameters.AddWithValue("@userID", userID);
SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
int ordinal = 0;
if (reader.Read())
{
ordinal = reader.GetOrdinal("securityQuestion");
question = reader.GetString(ordinal);
ordinal = reader.GetOrdinal("securityAnswer");
answer = reader.GetString(ordinal);
lblQuestion.Content = question;
lblQuestion.Visibility = Visibility.Visible;
txtAnswer.Visibility = Visibility.Visible;
txtAnswer.Focus();
}
reader.Close();
}
}
}
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);
}
static bool ExistProductInProductTable(string code)
{
bool existProduct;
using (SqlCeConnection connection = new SqlCeConnection(DataBase.ConStrDB))
{
connection.Open();
string expression = @"SELECT code
FROM Product
WHERE code = @code";
SqlCeCommand cmd = new SqlCeCommand(expression, connection);
cmd.Parameters.AddWithValue("code", code);
SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (!reader.HasRows) // если возвращается 0 - такого продукта нет в БД
{
existProduct = false;
}
else
{
existProduct = true;
}
reader.Close();
}
return(existProduct);
}
static int ExistSuchProductInPriceTable(out int quantityBD, string code, decimal priceDC, decimal pricePC, int catalogID, bool discont)
{
int productIndex;
using (SqlCeConnection connection = new SqlCeConnection(DataBase.ConStrDB))
{
connection.Open();
string expression = @"SELECT Price.id, Price.quantity
FROM Price
WHERE Price.code = @code AND
Price.priceDC = @priceDC AND Price.pricePC = @pricePC AND
Price.catalog = @catalog AND Price.discont = @discont";
SqlCeCommand cmd = new SqlCeCommand(expression, connection);
cmd.Parameters.AddWithValue("code", code);
cmd.Parameters.AddWithValue("priceDC", priceDC);
cmd.Parameters.AddWithValue("pricePC", pricePC);
cmd.Parameters.AddWithValue("catalog", catalogID);
cmd.Parameters.AddWithValue("discont", discont);
SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (!reader.HasRows) // если возвращается 0 - такого продукта нет в БД
{
quantityBD = 0;
return(0);
}
reader.Read();
productIndex = (int)reader[0];
quantityBD = (int)reader[1];
reader.Close();
}
return(productIndex);
}
public static User GetUser(string username)
{
string sql = "select Username, Password, FirstName, LastName, UserType, OIB from users where Username=@username";
User user = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@username", username);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordUsername = rs.GetOrdinal("Username");
int ordPassword = rs.GetOrdinal("Password");
int ordUserType = rs.GetOrdinal("UserType");
int ordLastName = rs.GetOrdinal("LastName");
int ordFirstname = rs.GetOrdinal("FirstName");
int ordOIB = rs.GetOrdinal("OIB");
rs.ReadFirst();
user = new User();
user.FirstName = rs.GetString(ordFirstname);
user.LastName = rs.GetString(ordLastName);
user.Username = rs.GetString(ordUsername);
user.Password = rs.GetString(ordPassword);
user.UserType = rs.GetString(ordUserType);
user.OIB = rs.GetString(ordOIB);
}
return(user);
}
private void studentList()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT studentNo, fullName from BorrowerList where groupID = @groupID and subject = @subject and expName = @expName and DATEDIFF(day, dateReq, @dateReq) = 0 and DATEDIFF(day, dateExp, @dateExp) = 0 and lockNo = @lockNo", conn))
{
cmd.Parameters.AddWithValue("@groupID", txtGrpID.Text);
cmd.Parameters.AddWithValue("@subject", txtSubj.Text);
cmd.Parameters.AddWithValue("@expName", txtExpName.Text);
cmd.Parameters.AddWithValue("@dateReq", txtDateReq.Text);
cmd.Parameters.AddWithValue("@dateExp", txtDateExp.Text);
cmd.Parameters.AddWithValue("@lockNo", txtLockNo.Text);
using (DbDataReader rd = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
while (rd.Read())
{
int studentNoIndex = rd.GetOrdinal("studentNo");
string studentNo = Convert.ToString(rd.GetValue(studentNoIndex));
int fullNameIndex = rd.GetOrdinal("fullName");
string fullName = Convert.ToString(rd.GetValue(fullNameIndex));
studInfo.Add(new StudentInfo
{
studName = fullName,
studNo = studentNo
});
}
}
}
foreach (var student in studInfo)
{
if (string.IsNullOrEmpty(txtStud1.Text))
{
txtStud1.Text = Convert.ToString(student.studNo);
txtName1.Text = student.studName;
}
else if (string.IsNullOrEmpty(txtStud2.Text))
{
txtStud2.Text = Convert.ToString(student.studNo);
txtName2.Text = student.studName;
}
else if (string.IsNullOrEmpty(txtStud3.Text))
{
txtStud3.Text = Convert.ToString(student.studNo);
txtName3.Text = student.studName;
}
else if (string.IsNullOrEmpty(txtStud4.Text))
{
txtStud4.Text = Convert.ToString(student.studNo);
txtName4.Text = student.studName;
}
else if (string.IsNullOrEmpty(txtStud5.Text))
{
txtStud5.Text = Convert.ToString(student.studNo);
txtName5.Text = student.studName;
}
}
}
// возвращает объект Информация об базе данных переучета которая была записана при формированиия БД
public DbInfo GetDbInfo()
{
DbInfo dbInfo = new DbInfo();
List <Ean> eans = new List <Ean>();
string commandText = @" SELECT pName, val FROM Info ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = connect.CreateCommand())
{
command.CommandText = commandText;
using (SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (res.HasRows)
{
while (res.Read())
{
switch (res.GetString(0).ToUpper())
{
case "APTEKAID":
dbInfo.AptekaID = res.GetString(1);
break;
case "APTEKANAME":
dbInfo.AptekaName = res.GetString(1);
break;
case "VERSION":
dbInfo.Version = res.GetString(1);
break;
case "PEREUCHETDATE":
long ticks = long.Parse(res.GetString(1));
var dt = new DateTime(ticks);
dbInfo.PereuchetDateText = dt.ToShortDateString();
dbInfo.PereuchetDate = dt;
break;
case "DBCREATEDTIME":
long CreatedDBTicks = long.Parse(res.GetString(1));
var CreatedDbDt = new DateTime(CreatedDBTicks);
dbInfo.CreatedDBDt = CreatedDbDt;
break;
case "SPREANROWSCOUNT":
dbInfo.SprEanRowCountText = res.GetString(1);
break;
case "PEREUCHETROWSCOUNT":
dbInfo.PereuchetRowCountText = res.GetString(1);
break;
}
}
}
}
}
}
return(dbInfo);
}
public Employee GetEmployee(string barcode)
{
Employee emp = null;
string selectCommand = @" SELECT CONVERT(INT,id_gamma) id_gamma, ename, case when barcode is not null then barcode else '' END barcode FROM Employee WHERE barcode IS NOT NULL AND barcode = CONVERT(NVARCHAR(12), @barcode) ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = new SqlCeCommand(selectCommand, connect))
{
var param = command.Parameters.Add("barcode", SqlDbType.NVarChar);
param.Value = barcode;
using (SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (res.ReadFirst())
{
emp = new Employee()
{
GammaID = (int)res.GetInt32(res.GetOrdinal("id_gamma")),
Barcode = (res.IsDBNull(res.GetOrdinal("barcode"))) ? "" : res.GetString(res.GetOrdinal("barcode")),
Name = res.GetString(res.GetOrdinal("ename"))
};
}
}
}
}
return(emp);
}
// возвращает сканированный товар и количество
public Scan GetScan(int artcode)
{
Scan scan = null;
string selectCommand = @" SELECT artcode, id_gamma, qty FROM scan WHERE artcode = @artcode and id_gamma = @id_gamma";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = connect.CreateCommand())
{
command.CommandText = selectCommand;
var param = command.Parameters.Add("artcode", SqlDbType.Int);
param.Value = artcode;
param = command.Parameters.Add("id_gamma", SqlDbType.Int);
param.Value = GlobalArea.CurrentEmployee.GammaID;
using (SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (!res.HasRows)
{
return(null);
}
if (!res.ReadFirst())
{
return(null);
}
scan = new Scan((int)res.GetInt32(res.GetOrdinal("artcode")),
(int)res.GetInt32(res.GetOrdinal("id_gamma")),
(int)res.GetInt32(res.GetOrdinal("qty")));
}
}
return(scan);
}
}
/// <summary>
/// Gets the primary key for a given table.
/// </summary>
/// <param name="connectionString">The connection string used to connect to the target database.</param>
/// <param name="table"></param>
/// <returns></returns>
public PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table)
{
// Erik Ejlskov - corrected SQL statement
string sql = string.Format("select KCU.[CONSTRAINT_NAME] as [Constraint], KCU.[COLUMN_NAME] as [ColumnName] from INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME where KCU.TABLE_NAME = '{0}' AND TC.TABLE_NAME = '{0}' AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' order by KCU.ORDINAL_POSITION", table.Name);
string name = String.Empty;
var cols = new List <string>();
using (SqlCeCommand cmd = GetCeCommand(connectionString, sql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
while (results.Read())
{
name = (string)results["Constraint"];
cols.Add((string)results["ColumnName"]);
}
}
}
if (name == String.Empty && cols.Count == 0)
{
return(null);
}
return(new PrimaryKeySchema(table, name, cols.ToArray()));
}
private void fillSubjects()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT subjName, subjCode from Subjects", conn))
{
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
lvList.Items.Clear();
while (reader.Read())
{
int subjCodeIndex = reader.GetOrdinal("subjCode");
string subjCode = Convert.ToString(reader.GetValue(subjCodeIndex));
int subjNameIndex = reader.GetOrdinal("subjName");
string subjName = Convert.ToString(reader.GetValue(subjNameIndex));
lvList.Items.Add(new LVSubject
{
subjCode = subjCode,
subjName = subjName
});
}
}
}
}
}
public Book[] GetAllBooks()
{
var books = new List<Book>();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Book", Connection))
{
using (var results = cmd.ExecuteResultSet(ResultSetOptions.Insensitive))
{
while (results.Read())
{
if (m_bookOrdinals.Count == 0)
{
for (int i = 0; i < results.FieldCount; i++)
{
m_bookOrdinals.Add(results.GetName(i), i);
}
}
books.Add(new Book
{
BookID = results.GetInt32(m_bookOrdinals["BookID"]),
AuthorID = results.GetInt32(m_bookOrdinals["AuthorID"]),
Title = results.GetString(m_bookOrdinals["Title"])
});
}
}
}
return books.ToArray();
}
public static List <Company> GetAllCompanies()
{
var list = new List <Company>();
SqlCeCommand sqlCeCommand = new SqlCeCommand("select * from company order by name", DatabaseConnector.DatabaseConnection);
sqlCeCommand.CommandType = CommandType.Text;
SqlCeResultSet sqlCeResultSet = sqlCeCommand.ExecuteResultSet(ResultSetOptions.Scrollable);
if (sqlCeResultSet.HasRows)
{
int ordinal0 = sqlCeResultSet.GetOrdinal("id");
int ordinal1 = sqlCeResultSet.GetOrdinal("name");
int ordinal2 = sqlCeResultSet.GetOrdinal("address");
int ordinal3 = sqlCeResultSet.GetOrdinal("OIB");
sqlCeResultSet.ReadFirst();
list.Add(new Company()
{
Id = sqlCeResultSet.GetInt32(ordinal0),
Name = sqlCeResultSet.GetString(ordinal1),
Address = sqlCeResultSet.GetString(ordinal2),
OIB = sqlCeResultSet.GetString(ordinal3)
});
while (sqlCeResultSet.Read())
{
list.Add(new Company()
{
Id = sqlCeResultSet.GetInt32(ordinal0),
Name = sqlCeResultSet.GetString(ordinal1),
Address = sqlCeResultSet.GetString(ordinal2),
OIB = sqlCeResultSet.GetString(ordinal3)
});
}
}
return(list);
}
int GetVehicleCatgeoryID(string vehicleCategory)
{
SqlCeConnection con = ((IDBManager)DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
sqlQuery = "SELECT Vehicle_category_ID FROM VSD_VEHICLE_CATEGORY WHERE (Category_Name = @vehCat)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@vehCat", SqlDbType.NChar, 100).Value = vehicleCategory;
SqlCeResultSet rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
int rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
int id = -1;
if (rs.Read())
{
id = (int)rs.GetInt32(0);
}
rs.Close();
con.Close();
return id;
}
public void Open(SqlCeConnection sqlConn)
{
_sqlCmd = sqlConn.CreateCommand();
_sqlCmd.CommandText = String.Concat("SELECT * FROM ", _tableName);
_resultSet = _sqlCmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
_record = _resultSet.CreateRecord();
}
public ReportSpecific(int studNo)
{
InitializeComponent();
this.studNo = studNo;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT LastName + ', ' + FirstName + ' ' + COALESCE(MiddleName, '') AS [Full Name], ResidenceStatus, CounterProbi FROM StudentInfo WHERE StudentNo = @studentNo", conn))
{
cmd.Parameters.AddWithValue("@studentNo", studNo);
using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.Read())
{
int fullNameIndex = reader.GetOrdinal("Full Name");
fullName = Convert.ToString(reader.GetValue(fullNameIndex));
int residenceIndex = reader.GetOrdinal("ResidenceStatus");
residence = Convert.ToString(reader.GetValue(residenceIndex));
int probiCountIndex = reader.GetOrdinal("CounterProbi");
probiCount = Convert.ToInt32(reader.GetValue(probiCountIndex));
}
}
}
txtStudNo.Text = studNo.ToString();
txtResidence.Text = residence;
txtFullName.Text = fullName;
txtProb.Text = AddOrdinal(probiCount);
updateListView();
}
protected override void DoFlush()
{
Stopwatch timer = new Stopwatch();
timer.Start();
using (var callsSet = m_callsCmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable))
{
FlushCalls(callsSet);
}
using (var samplesSet = m_samplesCmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable))
{
FlushSamples(samplesSet);
}
using (var timingsSet = m_timingsCmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable))
{
FlushTimings(timingsSet);
}
m_cachedSamples = 0;
//m_cachedTimings = 0;
timer.Stop();
Debug.WriteLine(string.Format("Database update took {0} milliseconds.", timer.ElapsedMilliseconds));
}
public Book[] GetAllBooks()
{
string sql = "SELECT BookID, Title, Author, Pages FROM Books";
List <Book> books = new List <Book>();
using (SqlCeCommand cmd = new SqlCeCommand(sql, Connection))
using (var resultset = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (resultset.HasRows)
{
while (resultset.Read())
{
books.Add(new Book
{
ID = resultset.GetInt32(0),
Title = resultset.GetString(1),
Author = resultset.GetString(2),
Pages = resultset.IsDBNull(3) ? null : (int?)resultset.GetInt32(3)
});
}
}
}
return(books.ToArray());
}
/*
* showDataBase will load the database and show it in a grid
* @tabela: table's name
* @grid: grid where the table will be showed
*/
private void showDataBase(string tabela, DataGridView grid)
{
SqlCeConnection cn = new SqlCeConnection(stringConexao());
if (cn.State == ConnectionState.Closed)
{
try
{
cn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
try
{
// define o command para usar a tabela e não a consulta
SqlCeCommand cmd = new SqlCeCommand(tabela, cn);
cmd.CommandType = CommandType.TableDirect;
// Pega a tabela
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
// carrega o resultado no grid
grid.DataSource = rs;
}
catch (SqlCeException sqlexception)
{
MessageBox.Show(sqlexception.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void txtName_TextChanged(object sender, TextChangedEventArgs e)
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT LastName, firstName from StudentInfo WHERE (LastName LIKE @lastName ) or (firstName LIKE @firstName)", conn))
{
cmd.Parameters.AddWithValue("@lastName", txtLastName.Text + "%");
cmd.Parameters.AddWithValue("@firstName", txtFirstName.Text + "%");
using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
lvListStudent.Items.Clear();
i = 1;
if (reader.HasRows)
{
while (reader.Read())
{
int lastNameIndex = reader.GetOrdinal("LastName");
string lastName = Convert.ToString(reader.GetValue(lastNameIndex));
int firstNameIndex = reader.GetOrdinal("firstName");
string firstName = Convert.ToString(reader.GetValue(firstNameIndex));
lvListStudent.Items.Add(new ListViewSearchStudent
{
i = this.i,
LastName = lastName,
FirstName = firstName
});
i++;
}
}
}
}
conn.Close();
}
private void Insert(IEnumerable <IRow> inserts, SqlCeConnection cn, string table)
{
var enumerated = inserts.ToArray();
if (enumerated.Length == 0)
{
return;
}
try {
using (var cmd = new SqlCeCommand(table, cn, null)) {
cmd.CommandType = CommandType.TableDirect;
using (var rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable)) {
foreach (var row in enumerated)
{
var rec = rs.CreateRecord();
for (var i = 0; i < _output.OutputFields.Length; i++)
{
var field = _output.OutputFields[i];
rec.SetValue(i, row[field]);
}
rs.Insert(rec);
}
}
}
_output.Entity.Inserts += Convert.ToUInt32(enumerated.Length);
} catch (Exception ex) {
_output.Error(ex.Message);
}
}
private ApplicationState()
{
// read the application state from db
SqlCeConnection _dataConn = null;
try
{
_dataConn = new SqlCeConnection("Data Source=FlightPlannerDB.sdf;Persist Security Info=False;");
_dataConn.Open();
SqlCeCommand selectCmd = new SqlCeCommand();
selectCmd.Connection = _dataConn;
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT cruiseSpeed,cruiseFuelFlow,minFuel,speed,unit,utcOffset,locationFormat,deckHoldFuel,registeredClientName FROM ApplicationState");
selectCmd.CommandText = selectQuery.ToString();
SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows)
{
results.ReadFirst();
cruiseSpeed = results.GetInt64(0);
cruiseFuelFlow = results.GetInt64(1);
minFuel = results.GetInt64(2);
speed = results.GetSqlString(3).ToString();
unit = results.GetSqlString(4).ToString();
utcOffset = results.GetSqlString(5).ToString();
locationFormat = results.GetSqlString(6).ToString();
deckHoldFuel = results.IsDBNull(7) ? 0 : results.GetInt64(7);
registeredClientName = results.IsDBNull(8) ? string.Empty : results.GetString(8);
}
}
finally
{
_dataConn.Close();
}
}
private void fillSize()
{
if (!string.IsNullOrEmpty(cmbInventName.Text))
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
cmbSize.Items.Clear();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT size from ApparatusInventory where name = @inventName and SIZE is NOT NULL", conn))
{
cmd.Parameters.AddWithValue("@inventName", cmbInventName.Text);
using (DbDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
int sizeIndex = reader.GetOrdinal("size");
string size = Convert.ToString(reader.GetValue(sizeIndex));
cmbSize.Items.Add(size);
}
}
}
}
}
}
private void txtUsername_KeyDown(object sender, KeyEventArgs e)
{
if (e.Key == Key.Enter)
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
userID = txtUsername.Text;
using (SqlCeCommand cmd = new SqlCeCommand("Select * from Accounts where userID = @userID", conn))
{
cmd.Parameters.AddWithValue("@userID", userID);
SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
int ordinal = 0;
if (reader.Read())
{
ordinal = reader.GetOrdinal("securityQuestion");
question = reader.GetString(ordinal);
ordinal = reader.GetOrdinal("securityAnswer");
answer = reader.GetString(ordinal);
lblQuestion.Content = question;
unlockFields();
txtAnswer.Focus();
}
else
{
MessageBox.Show("Account does not exist.");
return;
}
reader.Close();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="attributeColumn"></param>
/// <param name="whereClause"> forget the "WHERE", e.g. coulumn01 = someValue</param>
/// <returns></returns>
public override List <object> GetAttributes(string attributeColumn, string whereClause)
{
//SqlCeConnection connection = new SqlCeConnection(_connectionString);
SqlCeCommand command = new SqlCeCommand(string.Format(System.Globalization.CultureInfo.InvariantCulture, "SELECT {0} FROM {1} {2}", attributeColumn, _tableName, MakeWhereClause(whereClause)), _connection);
command.CommandType = System.Data.CommandType.Text;
//connection.Open();
List <object> result = new List <object>();
SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.Scrollable);
if (!resultSet.HasRows)
{
return(new List <object>());
}
int columnIndex = resultSet.GetOrdinal(attributeColumn);
while (resultSet.Read())
{
result.Add(resultSet.GetValue(columnIndex));
}
//connection.Close();
return(result.Cast <object>().ToList());
}
private SqlCeResultSet DoExecuteResultSet(SqlCeCommand command, ResultSetOptions options)
{
DateTime startTime = DateTime.Now;
SqlCeResultSet reader = command.ExecuteResultSet(options);
return(reader);
}
public static Company GetCompany(int id)
{
string sql = "select id, name, address, OIB from company where id=@id";
Company company = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@id", id);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordId = rs.GetOrdinal("id");
int ordName = rs.GetOrdinal("name");
int ordAddress = rs.GetOrdinal("address");
int ordOIB = rs.GetOrdinal("OIB");
rs.ReadFirst();
company = new Company();
company.Id = rs.GetInt32(ordId);
company.Name = rs.GetString(ordName);
company.Address = rs.GetString(ordAddress);
company.OIB = rs.GetString(ordOIB);
}
return(company);
}
bool IDBManager.AuthenticateUser(string userName, string userPass)
{
IDBManager iDBManager = (IDBManager)DBConnectionManager.GetInstance();
// byte[] pass = ((IEncryptionDecryptionManager)new EncryptionManager()).Encrypt(userPass, AppProperties.encryptionKey, AppProperties.iVector);
string sql = "select * from VSD_USER_ACCOUNT where User_Name like @empUserName";//and Password like @empPassword
SqlCeConnection con = iDBManager.GetConnection();
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCeCommand command = new SqlCeCommand(sql, iDBManager.GetConnection());
command.Parameters.Add("@empUserName", SqlDbType.NChar, 100).Value = userName;
//command.Parameters.Add("@empPassword", SqlDbType.VarBinary, 20).Value = empPassword;
try
{
SqlCeResultSet rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
int rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
byte[] encryptedBytes;
string decryptedPassword;
if (rs.HasRows)
{
//return false;
rs.ReadFirst();
encryptedBytes = (byte[])rs.GetValue(2);
decryptedPassword = ((IEncryptionDecryptionManager)new DecryptionManager()).Decrypt(encryptedBytes, AppProperties.encryptionKey, AppProperties.iVector);
if (userPass == decryptedPassword)
{
return true;
}
while (rs.NextResult())
{
encryptedBytes = (byte[])rs.GetValue(2);
decryptedPassword = ((IEncryptionDecryptionManager)new DecryptionManager()).Decrypt(encryptedBytes, AppProperties.encryptionKey, AppProperties.iVector);
if (userPass == decryptedPassword)
{
return true;
}
}
}
}
catch (Exception ex)
{
// System.Windows.Forms.MessageBox.Show(ex.Message)
; return false;
}
return false;
}
public Author GetAuthorById(int id)
{
Author author = null;
List<Book> books = new List<Book>();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT AuthorID, Name, BookID, Title, BookType FROM Author INNER JOIN Book ON Author.AuthorID = Book.AuthorID WHERE Author.AuthorID = @id",
Connection))
{
cmd.Parameters.Add(new SqlCeParameter("@id", id));
using (var results = cmd.ExecuteResultSet(ResultSetOptions.Insensitive))
{
while (results.Read())
{
if (author == null)
{
author = new Author
{
AuthorID = (int)results["AuthorID"],
Name = (string)results["Name"]
};
}
else if (author.AuthorID != results.GetInt32(m_authorOrdinals["AuthorID"]))
{
// we're on a new author , so we're done
// (shoudln't happen unless we have more than 1 author with the same name)
break;
}
books.Add(new Book
{
BookID = (int)results["BookID"],
Title = (string)results["Title"],
BookType = (BookType)results["BookType"],
AuthorID = author.AuthorID
});
}
}
}
author.Books = books.ToArray();
return author;
}
string GetVehicleCatgeoryCode(string vehicleCategory)
{
try
{
SqlCeConnection con = ((IDBManager)DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
sqlQuery = "SELECT category_Code FROM VSD_VEHICLE_CATEGORY WHERE (Category_Name = @vehCat)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@vehCat", SqlDbType.NChar, 100).Value = vehicleCategory;
SqlCeResultSet rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
int rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
string code = "";
if (rs.Read())
{
code = rs.GetString(0);
}
rs.Close();
con.Close();
return code;
}
catch (Exception ex)
{
CommonUtils.WriteLog(ex.StackTrace);
return null;
}
}
static WordStreammer()
{
Forms = new List<string>();
using (SqlCeConnection conn = new SqlCeConnection(NlpHelper.DatabaseConnectionString))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
try
{
var sqlCeCommand = new SqlCeCommand(DatabaseQuery.SelectFormFromForms, conn) { CommandType = CommandType.Text };
SqlCeResultSet resultSet =
sqlCeCommand.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Insensitive);
if (resultSet.HasRows)
{
int ordForm = resultSet.GetOrdinal("Form");
resultSet.ReadFirst();
Forms.Add(resultSet.GetString(ordForm));
while (resultSet.Read())
{
string form = resultSet.GetString(ordForm);
// if (!Forms.Contains(form))
Forms.Add(form);
}
}
}
catch (SqlCeException sqlexception)
{
Console.WriteLine("Error form: {0}", sqlexception.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error form: {0}", ex.Message);
}
}
}
public string ReadConfigValue(string inName)
{
string result = "";
SqlCeConnection cn = new SqlCeConnection(m_connectionString);
if (cn.State == ConnectionState.Closed)
{ cn.Open(); }
string sql = "select * from ConfigValues where Name='" + inName + "'";
try
{
SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
rs.Read();
int valueOrdinal = rs.GetOrdinal("Value");
result = rs.GetString(valueOrdinal);
}
}
catch (Exception ex)
{
int i = 0;
}
finally
{
cn.Close();
}
return result;
}
public List<string> getIdentsFromRoute(string routeIdent)
{
List<string> idents = new List<string>();
try
{
_dataConn = new SqlCeConnection("Data Source=FlightPlannerDB.sdf;Persist Security Info=False;");
_dataConn.Open();
// first delete the existing data
SqlCeCommand selectCmd = new SqlCeCommand();
selectCmd.Connection = _dataConn;
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append(string.Format("SELECT AirportIdent FROM RouteIdents where RouteId = '{0}' order by RouteSequenceNumber asc",routeIdent));
selectCmd.CommandText = selectQuery.ToString();
SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows)
{
results.ReadFirst();
while (true)
{
idents.Add(results.GetSqlString(0).ToString());
if (!results.Read())
break;
}
}
return idents;
}
catch (Exception ex)
{
return idents;
}
finally
{
if (_dataConn != null)
{
_dataConn.Close();
}
}
}
public void InitializeAirportdataFromDatabase()
{
try
{
_dataConn = new SqlCeConnection("Data Source=FlightPlannerDB.sdf;Persist Security Info=False;");
_dataConn.Open();
// first delete the existing data
SqlCeCommand selectCmd = new SqlCeCommand();
selectCmd.Connection = _dataConn;
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT airport_id, airport_ident, airport_type, airport_name, airport_latdeg, airport_longdeg, airport_elev_ft, airport_muncipality, airport_freqkhz, airport_gps_code,");
selectQuery.Append("airport_iata_code, airport_magnetic_deg, associated_airport FROM Airports");
selectCmd.CommandText = selectQuery.ToString();
SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows)
{
results.ReadFirst();
while (true)
{
Airport currAirport = new Airport();
currAirport.ID = results.GetSqlString(0).ToString();
currAirport.ident = results.GetSqlString(1).ToString();
currAirport.type = results.GetSqlString(2).ToString();
currAirport.name = results.GetSqlString(3).ToString();
currAirport.latitude_deg = results.GetSqlString(4).ToString();
currAirport.longitude_deg = results.GetSqlString(5).ToString();
currAirport.elev_ft = results.GetSqlString(6).ToString();
currAirport.municipality = results.GetSqlString(7).ToString();
currAirport.frequency_khz = results.GetSqlString(8).ToString();
currAirport.gps_code = results.GetSqlString(9).ToString();
currAirport.iata_code = results.GetSqlString(10).ToString();
currAirport.magnetic_variation_deg = results.GetSqlString(11).ToString();
currAirport.associated_airport = results.GetSqlString(12).ToString();
if (!listofAirports.ContainsKey(currAirport.ident))
listofAirports.Add(currAirport.ident, new List<Airport>());
listofAirports[currAirport.ident].Add(currAirport);
identList.Add(currAirport.ident);
if (!results.Read())
break;
}
}
}
catch (Exception ex)
{
}
finally
{
if (_dataConn != null)
{
_dataConn.Close();
}
}
}
protected override void Update(object item, bool cascadeUpdates, List<string> fieldNames, IDbConnection connection, IDbTransaction transaction)
{
var isDynamicEntity = item is DynamicEntity;
string entityName = null;
if (isDynamicEntity)
{
entityName = ((DynamicEntity)item).EntityName;
if (!m_entities.HasEntity(entityName)) throw new EntityNotFoundException(entityName);
}
else
{
entityName = m_entities.GetNameForType(item.GetType());
}
if (entityName == null)
{
throw new EntityNotFoundException(item.GetType());
}
var entity = m_entities[entityName];
if (entity.Fields.KeyField == null)
{
throw new PrimaryKeyRequiredException("A primary key is required on an Entity in order to perform Updates");
}
object keyValue;
Boolean bInheritedConnection = connection != null;
if (transaction == null && connection == null)
connection = GetConnection(false);
// TODO: Make multiple fieldnames possible!
string fieldName = null;
if (fieldNames != null && fieldNames.Count > 0) fieldName = fieldNames[0];
try
{
CheckPrimaryKeyIndex(entityName);
OnBeforeUpdate(item, cascadeUpdates, fieldName);
var start = DateTime.Now;
using (var command = new SqlCeCommand())
{
if (transaction == null)
{
command.Connection = connection as SqlCeConnection;
}
else
{
command.Transaction = transaction as SqlCeTransaction;
command.Connection = transaction.Connection as SqlCeConnection;
}
// TODO: Update doesn't support multiple Primary Keys. Need to be checked before we use TableDirect.
command.CommandText = entityName;
command.CommandType = CommandType.TableDirect;
command.IndexName = entity.PrimaryKeyIndexName;
using (var results = command.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
var indexes = new List<object>();
foreach (var field in entity.Fields.KeyFields)
{
if (isDynamicEntity)
{
keyValue = ((DynamicEntity)item)[field.FieldName];
}
else
{
keyValue = field.PropertyInfo.GetValue(item, null);
}
indexes.Add(keyValue);
}
var ordinals = GetOrdinals(entityName, results);
// seek on the PK
var found = results.Seek(DbSeekOptions.BeforeEqual, indexes.ToArray());
if (!found)
{
// TODO: the PK value has changed - we need to store the original value in the entity or diallow this kind of change
throw new RecordNotFoundException("Cannot locate a record with the provided primary key. You cannot update a primary key value through the Update method");
}
results.Read();
// update the values
foreach (var field in entity.Fields)
{
// do not update PK fields
if (field.IsPrimaryKey)
{
continue;
}
else if (fieldName != null && field.FieldName != fieldName)
{
continue; // if we pass in a field name, skip over any fields that don't match
}
else if (isDynamicEntity)
{
object value = null;
if (entity.Fields[field.FieldName].DataType == DbType.Object)
{
if (entity.Serializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
field.FieldName, entity.EntityName));
}
value = entity.Serializer.Invoke(item, field.FieldName);
}
else
{
value = ((DynamicEntity)item)[field.FieldName];
}
if (value == null)
{
results.SetValue(ordinals[field.FieldName], DBNull.Value);
}
else
{
results.SetValue(ordinals[field.FieldName], value);
}
}
else
{
if (field.DataType == DbType.Object)
{
if (entity.Serializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
field.FieldName, entityName));
}
var value = entity.Serializer.Invoke(item, field.FieldName);
results.SetValue(ordinals[field.FieldName], value);
}
else if (field.IsRowVersion)
{
// read-only, so do nothing
}
else if (field.PropertyInfo.PropertyType.UnderlyingTypeIs<TimeSpan>())
{
// SQL Compact doesn't support Time, so we're convert to ticks in both directions
var value = field.PropertyInfo.GetValue(item, null);
if (value == null)
{
results.SetValue(ordinals[field.FieldName], DBNull.Value);
}
else
{
var ticks = ((TimeSpan)value).Ticks;
results.SetValue(ordinals[field.FieldName], ticks);
}
}
else
{
var value = field.PropertyInfo.GetValue(item, null);
// TODO: should we update only if it's changed? Does it really matter at this point?
results.SetValue(ordinals[field.FieldName], value);
}
}
}
results.Update();
}
}
if (cascadeUpdates)
{
CascadeUpdates(item, fieldNames, null, entity, connection, transaction);
}
OnAfterUpdate(item, cascadeUpdates, fieldName, DateTime.Now.Subtract(start), "tableDirect");
}
finally
{
if (!bInheritedConnection) DoneWithConnection(connection, false);
}
}
bool IDBManager.SaveUserCredentials()
{
//throw new NotImplementedException();
try
{
//try
//{
// SqlCeEngine sqlEngine = new SqlCeEngine(AppProperties.connectionString);
// sqlEngine.CreateDatabase();
//}
//catch (SqlCeException ex)
//{
// //db exists
//}
SqlCeConnection cn = ((IDBManager)DBConnectionManager.GetInstance()).GetConnection();
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
//string sql = "create table login("
// + "UserName nvarchar (100), "
// + "Password VarBinary(20) ) ";
//SqlCeCommand command = new SqlCeCommand(sql, cn);
//try
//{
// command.ExecuteNonQuery();
//}
//catch (Exception ex)
//{
// System.Windows.Forms.MessageBox.Show("Table exists"+ex.Message);
// return true;
//}
byte[] pass = ((IEncryptionDecryptionManager)new EncryptionManager()).Encrypt(AppProperties.empPassword, AppProperties.encryptionKey, AppProperties.iVector);
string sql = "delete from VSD_USER_ACCOUNT";//and Password like @empPassword
SqlCeCommand deleteCommand = new SqlCeCommand(sql, cn);
SqlCeCommand insertCommand = new SqlCeCommand(sql, cn);
deleteCommand.Parameters.Add("@empUserName", SqlDbType.NChar, 100).Value = AppProperties.empUserName.ToLower();
SqlCeResultSet rs = deleteCommand.ExecuteResultSet(ResultSetOptions.Scrollable);
sql = "insert into VSD_USER_ACCOUNT (USER_EMP_ID,User_Account_ID,User_Name,User_Password,User_Status) values (@EmpID,'1',@empUserName,@empPassword,@USER_STATUS)";
if (cn.State == ConnectionState.Closed) cn.Open();
insertCommand = new SqlCeCommand(sql, cn);
// insertCommand.Parameters.Add("@User_ACCOUNT_ID",SqlDbType.Int,4).Value = "1";
insertCommand.Parameters.Add("@empUserName", SqlDbType.NChar, 100).Value = AppProperties.empUserName;
EncryptionManager Encryptor = new EncryptionManager();
IEncryptionDecryptionManager Iencryptor = (IEncryptionDecryptionManager)Encryptor;
insertCommand.Parameters.Add("@empPassword", SqlDbType.VarBinary, 512).Value = Iencryptor.Encrypt(AppProperties.empPassword, AppProperties.encryptionKey, AppProperties.iVector);
insertCommand.Parameters.Add("@USER_STATUS", SqlDbType.TinyInt, 1).Value = "1";
insertCommand.Parameters.Add("@EmpID", SqlDbType.NChar, 20).Value = (AppProperties.empID == null) ? "" : AppProperties.empID;
try
{
insertCommand.ExecuteNonQuery();
if (cn.State == ConnectionState.Open) cn.Close();
}
catch (Exception ex)
{
// CommonUtils.WriteLog(ex.StackTrace);
//System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
catch (SqlCeException sqlEx)
{
// CommonUtils.WriteLog(sqlEx.StackTrace);
// System.Windows.Forms.MessageBox.Show(sqlEx.Message);
}
return true;
}
public string GeraListagem(ref SqlCeConnection conexao, string nrmapa)
{
string vlresult = string.Empty;
SqlCeCommand cmd;
string nrgtin = string.Empty;
string nrlote = string.Empty;
string dtvalid = string.Empty;
string nrsscc = string.Empty;
string dtproduc = string.Empty;
string dtcoleta = string.Empty;
string stitem = string.Empty;
decimal qtprodut = 0;
string sql = "Select NRGTIN,NRLOTE,DTVALID,NRSSCC,DTPRODUC,DTCOLETA,QTPRODUT,STITEM,STCONSOL " +
"From Saida " +
"Where STITEM in ('C', 'D') ";
try
{
cmd = new SqlCeCommand(sql, conexao);
cmd.CommandType = System.Data.CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
vlresult = "NRMAPA;NRGTIN;NRLOTE;DTVALID;NRSSCC;DTPRODUC;DTCOLETA;QTPRODUT;STITEM\r\n";
rs.ReadFirst();
nrgtin = rs.GetString(0);
nrlote = rs.GetString(1).Replace(";", "/");
dtvalid = rs.GetString(2);
nrsscc = rs.GetString(3);
dtproduc = rs.GetString(4);
dtcoleta = rs.GetString(5);
qtprodut = rs.GetDecimal(6);
stitem = rs.GetString(7);
if (!rs.IsDBNull(8))
{
stitem = rs.GetString(8);
}
vlresult += nrmapa + ";" + nrgtin + ";" + nrlote + ";" + dtvalid + ";" + nrsscc + ";" +
dtproduc + ";" + dtcoleta + ";" + qtprodut.ToString() + ";" + stitem + "\r\n";
while (rs.Read())
{
nrgtin = rs.GetString(0);
nrlote = rs.GetString(1).Replace(";", "/");;
dtvalid = rs.GetString(2);
nrsscc = rs.GetString(3);
dtproduc = rs.GetString(4);
dtcoleta = rs.GetString(5);
qtprodut = rs.GetDecimal(6);
stitem = rs.GetString(7);
if (!rs.IsDBNull(8))
{
stitem = rs.GetString(8);
}
vlresult += nrmapa + ";" + nrgtin + ";" + nrlote + ";" + dtvalid + ";" + nrsscc + ";" +
dtproduc + ";" + dtcoleta + ";" + qtprodut.ToString() + ";" + stitem + "\r\n";
}
return vlresult;
}
}
catch (SqlCeException sqlexception)
{
Controller.ShowMessage("Erro durante geração da lista: " + sqlexception.Message);
}
return vlresult;
}
public bool FinalizarContagem(ref SqlCeConnection conexao)
{
bool dsresult = true;
try
{
SqlCeCommand cmd;
string sql = "Select nrgtin, nrlote, dsprodut, dtvalid, qtprodut from Saida where stitem = 'R'";
cmd = new SqlCeCommand(sql, conexao);
cmd.CommandType = System.Data.CommandType.Text;
SqlCeResultSet RsReferencia = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (RsReferencia.HasRows)
{
string nrgtin = string.Empty;
string nrlote = string.Empty;
string dsprodut = string.Empty;
string dtvalid = string.Empty;
decimal qtprodut = 0;
//Consolidando todos os itens não esperados
cmd.CommandText = "Update Saida set STITEM = 'C', STCONSOL = 'N' Where STITEM = 'X' ";
cmd.ExecuteNonQuery();
while (RsReferencia.Read())
{
nrgtin = RsReferencia.GetString(0);
nrlote = RsReferencia.GetString(1);
dsprodut = RsReferencia.GetString(2);
dtvalid = RsReferencia.GetString(3);
qtprodut = RsReferencia.GetDecimal(4);
cmd.CommandText = "Select Sum(QTPRODUT) " +
"from Saida " +
"where STITEM = @STITEM and " +
" NRGTIN = @NRGTIN and " +
" NRLOTE = @NRLOTE and " +
" DTVALID = @DTVALID ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@NRGTIN", nrgtin);
cmd.Parameters.AddWithValue("@NRLOTE", nrlote);
cmd.Parameters.AddWithValue("@DTVALID", dtvalid);
cmd.Parameters.AddWithValue("@STITEM", "E");
string qtcountaux = cmd.ExecuteScalar().ToString();
decimal qtcount = 0;
if (qtcountaux.Trim() != string.Empty)
{
qtcount = Decimal.Parse(qtcountaux);
}
if (qtprodut != qtcount)
{
if (!Controller.MessageDlg("Produto " + nrgtin + " : " + dsprodut + "\n" +
"Lote [ " + nrlote + " ] \n DIVERGENTE!!! \n" +
"Deseja continuar?"))
{
//Atualizando itens contados - Histórico
cmd.CommandText = "Update Saida set STITEM = @STITEMNEW" +
" Where STITEM = @STITEM and " +
" NRGTIN = @NRGTIN and " +
" NRLOTE = @NRLOTE and " +
" DTVALID = @DTVALID ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@NRGTIN", nrgtin);
cmd.Parameters.AddWithValue("@NRLOTE", nrlote);
cmd.Parameters.AddWithValue("@DTVALID", dtvalid);
cmd.Parameters.AddWithValue("@STITEM", "E");
cmd.Parameters.AddWithValue("@STITEMNEW", "H");
cmd.ExecuteNonQuery();
//Atualizando itens contados - Histórico (nao validados)
cmd.CommandText = "Update saida set STITEM = @STITEMNEW" +
" Where STITEM = @STITEM and " +
" NRGTIN = @NRGTIN ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@NRGTIN", nrgtin);
cmd.Parameters.AddWithValue("@STITEM", "E");
cmd.Parameters.AddWithValue("@STITEMNEW", "H");
cmd.ExecuteNonQuery();
//Atualizando referencia
cmd.CommandText = "Update Saida set NRCONATU = NRCONATU + 1 " +
" Where STITEM = @STITEM and " +
" NRGTIN = @NRGTIN and " +
" NRLOTE = @NRLOTE and " +
" DTVALID = @DTVALID ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@NRGTIN", nrgtin);
cmd.Parameters.AddWithValue("@NRLOTE", nrlote);
cmd.Parameters.AddWithValue("@DTVALID", dtvalid);
cmd.Parameters.AddWithValue("@STITEM", "R");
cmd.ExecuteNonQuery();
dsresult = false;
RsReferencia.ReadLast();
}
else
{
if (qtcount == 0)
{
InsertContagem(ref conexao, nrgtin, "", "", "", "", false, 0, false);
}
//Atualizando itens contados
cmd.CommandText = " Update Saida set STITEM = @STITEMNEW" +
" Where STITEM = @STITEM and " +
" NRGTIN = @NRGTIN and " +
" NRLOTE = @NRLOTE and " +
" DTVALID = @DTVALID ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@NRGTIN", nrgtin);
cmd.Parameters.AddWithValue("@NRLOTE", nrlote);
cmd.Parameters.AddWithValue("@DTVALID", dtvalid);
cmd.Parameters.AddWithValue("@STITEM", "E");
cmd.Parameters.AddWithValue("@STITEMNEW", "D");
cmd.ExecuteNonQuery();
//Atualizando itens contados e nao validados
cmd.CommandText = " Update saida set STITEM = @STITEMNEW" +
" Where STITEM = @STITEM and " +
" NRGTIN = @NRGTIN ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@NRGTIN", nrgtin);
cmd.Parameters.AddWithValue("@STITEM", "E");
cmd.Parameters.AddWithValue("@STITEMNEW", "D");
cmd.ExecuteNonQuery();
//Atualizando referencia
cmd.CommandText = "Update Saida set STITEM = @STITEMNEW" +
" Where STITEM = @STITEM and " +
" NRGTIN = @NRGTIN and " +
" NRLOTE = @NRLOTE and " +
" DTVALID = @DTVALID ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@NRGTIN", nrgtin);
cmd.Parameters.AddWithValue("@NRLOTE", nrlote);
cmd.Parameters.AddWithValue("@DTVALID", dtvalid);
cmd.Parameters.AddWithValue("@STITEM", "R");
cmd.Parameters.AddWithValue("@STITEMNEW", "V");
cmd.ExecuteNonQuery();
}
}
else
{
//Atualizando itens contados
cmd.CommandText = " Update Saida set STITEM = @STITEMNEW" +
" Where STITEM = @STITEM and " +
" NRGTIN = @NRGTIN and " +
" NRLOTE = @NRLOTE and " +
" DTVALID = @DTVALID ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@NRGTIN", nrgtin);
cmd.Parameters.AddWithValue("@NRLOTE", nrlote);
cmd.Parameters.AddWithValue("@DTVALID", dtvalid);
cmd.Parameters.AddWithValue("@STITEM", "E");
cmd.Parameters.AddWithValue("@STITEMNEW", "C");
cmd.ExecuteNonQuery();
//Atualizando itens contados e não Validados
//cmd.CommandText = " Update Saida set STITEM = @STITEMNEW" +
// " Where STITEM = @STITEM and " +
// " NRGTIN = @NRGTIN ";
//cmd.Parameters.Clear();
//cmd.Parameters.AddWithValue("@NRGTIN", nrgtin);
//cmd.Parameters.AddWithValue("@STITEM", "E");
//cmd.Parameters.AddWithValue("@STITEMNEW", "C");
//cmd.ExecuteNonQuery();
//Atualizando referencia
cmd.CommandText = "Update Saida set STITEM = @STITEMNEW" +
" Where STITEM = @STITEM and " +
" NRGTIN = @NRGTIN and " +
" NRLOTE = @NRLOTE and " +
" DTVALID = @DTVALID ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@NRGTIN", nrgtin);
cmd.Parameters.AddWithValue("@NRLOTE", nrlote);
cmd.Parameters.AddWithValue("@DTVALID", dtvalid);
cmd.Parameters.AddWithValue("@STITEM", "R");
cmd.Parameters.AddWithValue("@STITEMNEW", "F");
cmd.ExecuteNonQuery();
}
}
}
}
catch (SqlCeException sqlexception)
{
Controller.ShowMessage("Erro durante finalizacao: " + sqlexception.Message);
dsresult = false;
}
return dsresult;
}
public bool FindItem(ref SqlCeConnection conexao, string cdgtin, string nrlote, string dtvalid, string sqlcompl)
{
bool vlresult = false;
SqlCeCommand cmd;
string sql = string.Empty;
if ((nrlote.Trim() == string.Empty) && (dtvalid.Trim() == string.Empty))
{
sql = "Select * From Saida " +
"Where NRGTIN = @NRGTIN " +
sqlcompl;
}
else
{
sql = "Select * From Saida " +
"Where NRGTIN = @NRGTIN and " +
" NRLOTE = @NRLOTE and " +
" DTVALID = @DTVALID " +
sqlcompl;
}
cmd = new SqlCeCommand(sql, conexao);
if ((nrlote.Trim() == string.Empty) && (dtvalid.Trim() == string.Empty))
{
cmd.Parameters.AddWithValue("@NRGTIN", cdgtin);
}
else
{
cmd.Parameters.AddWithValue("@NRGTIN", cdgtin);
cmd.Parameters.AddWithValue("@NRLOTE", nrlote);
cmd.Parameters.AddWithValue("@DTVALID", dtvalid);
}
try
{
cmd.CommandType = System.Data.CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
vlresult = true;
}
else
{
vlresult = false;
}
}
catch (SqlCeException sqlexception)
{
Controller.ShowMessage("Erro durante busca de Item: " + sqlexception.Message);
}
return vlresult;
}
public bool FindItemExt(ref SqlCeConnection conexao, string cdgtin, string nrlote, string dtproduc,
string dtvalid, string nrsscc)
{
bool vlresult = false;
SqlCeCommand cmd;
string sql = "Select * From Saida " +
"Where NRGTIN = @NRGTIN and " +
" NRLOTE = @NRLOTE and " +
" DTPRODUC = @DTPRODUC and " +
" DTVALID = @DTVALID and " +
" NRSSCC = @NRSSCC and " +
" STITEM in ('C', 'E') ";
try
{
cmd = new SqlCeCommand(sql, conexao);
cmd.Parameters.AddWithValue("@NRGTIN", cdgtin);
cmd.Parameters.AddWithValue("@NRLOTE", nrlote);
cmd.Parameters.AddWithValue("@DTPRODUC", dtproduc);
cmd.Parameters.AddWithValue("@DTVALID", dtvalid);
cmd.Parameters.AddWithValue("@NRSSCC", nrsscc);
cmd.CommandType = System.Data.CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
vlresult = true;
}
else
{
vlresult = false;
}
}
catch (SqlCeException sqlexception)
{
Controller.ShowMessage("Erro durante comando busca extendida: " + sqlexception.Message);
}
return vlresult;
}
public static void BulkInsertFromCSV(string file_path, string table_name, string connection_string, Dictionary<string, Type> data_types, BackgroundWorker backgroundworker) //, BackgroundWorker backgroundworker
{
string line;
List<string> column_names = new List<string>();
using (StreamReader reader = new StreamReader(file_path))
{
line = reader.ReadLine();
string[] texts = line.Split(' ');
foreach (string txt in texts)
{
//MessageBox.Show(txt);
column_names.Add(txt);
}
using (SqlCeConnection conn = new SqlCeConnection(connection_string))
{
SqlCeCommand cmd = new SqlCeCommand();
SqlCeResultSet rs;
SqlCeUpdatableRecord rec;
conn.Open();
cmd.Connection = conn;
cmd.CommandText = table_name;
cmd.CommandType = CommandType.TableDirect;
rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
// (sender as BackgroundWorker).ReportProgress(progressPercentage, i);
while ((line = reader.ReadLine()) != null)
{
texts = line.Split(' '); // '/t'
rec = rs.CreateRecord();
for (int j = 0; j < column_names.Count; ++j)
{
string columnName = column_names[j];
int ordinal = rec.GetOrdinal(columnName);
string param_value = "";
if (j < texts.Length)
{
param_value = texts[j];
}
Type data_type = data_types[columnName];
if (data_type == typeof(Int32))
{
Int32 value = 0;
int.TryParse(param_value, out value);
rec.SetInt32(ordinal, value);
}
else if (data_type == typeof(Int64))
{
Int64 value = 0;
Int64.TryParse(param_value, out value);
rec.SetInt64(ordinal, value);
}
else if (data_type == typeof(Int16))
{
Int16 value = 0;
Int16.TryParse(param_value, out value);
rec.SetInt16(ordinal, value);
}
else if (data_type == typeof(string))
{
rec.SetString(ordinal, param_value);
}
else if (data_type == typeof(double))
{
double value = 0;
double.TryParse(param_value, out value);
rec.SetDouble(ordinal, value);
}
else if (data_type == typeof(float))
{
float value = 0;
float.TryParse(param_value, out value);
rec.SetFloat(ordinal, value);
}
else if (data_type == typeof(DateTime))
{
DateTime value;
if (DateTime.TryParse(param_value, out value))
rec.SetDateTime(ordinal, value);
}
else if (data_type == typeof(decimal))
{
decimal value;
decimal.TryParse(param_value, out value);
rec.SetDecimal(ordinal, value);
}
else if (data_type == typeof(Byte))
{
int temp;
int.TryParse(param_value, out temp);
Byte[] value = BitConverter.GetBytes(temp);
//Byte[].TryParse(param_value, out value);
//System.Buffer.BlockCopy(param_value.ToCharArray(), 0, value, 0, 4);
//value = GetBytes(param_value);
// MessageBox.Show(Convert.ToString(value[0], 16).PadLeft(2, '0'));
//value =BitConverter. param_value;
// rec.SetByte(ordinal, value[0]);
//rec.set
rec.SetBytes(ordinal,0, value,0,value.Length);
}
}
// MessageBox.Show( rec.ToString());
rs.Insert(rec);
}
rs.Close();
rs.Dispose();
cmd.Dispose();
}
}
}
public void Synchronize_vehicleCatDefectSeverity(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
///////////////////////////
//check for Vehicle Category Defect updates
if (test.vehicleCatDefectSeverity != null)
{
handHeldService.VehicleCategoryDefectSeverity[] WebServiceDefects = new VSDApp.handHeldService.VehicleCategoryDefectSeverity[test.vehicleCatDefectSeverity.Length];
WebServiceDefects = test.vehicleCatDefectSeverity;
foreach (handHeldService.VehicleCategoryDefectSeverity x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Veh_Cat_Defect"
+ " WHERE (Veh_Cat_Defect_id = @VehCatDefectID)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@VehCatDefectID", SqlDbType.Int).Value = x.id;
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_Veh_Cat_Defect (Veh_Cat_Defect_ID,Defect_ID,Severity_Level_ID,Vehicle_category_ID,IsDisabled) VALUES (" + x.id + "," + (x.defectId != null ? "" + x.defectId + "" : "NULL") + "," + (x.severityLevelId != null ? "" + x.severityLevelId + "" : "NULL") + "," + (x.vehicleCategoryId != null ? "" + x.vehicleCategoryId + "" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ")";
}
else
{
sqlQuery = " UPDATE VSD_Veh_Cat_Defect SET Veh_Cat_Defect_ID = " + x.id + ",Defect_ID = " + (x.defectId != null ? "" + x.defectId + "" : "NULL") + ",Severity_Level_ID = " + (x.severityLevelId != null ? "" + x.severityLevelId + "" : "NULL") + ",Vehicle_category_ID = " + (x.vehicleCategoryId != null ? "" + x.vehicleCategoryId + "" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Veh_Cat_Defect_ID = " + x.id;
}
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@Veh_Cat_Defect_ID", SqlDbType.Int).Value = x.id;
command.Parameters.Add("@Defect_ID", SqlDbType.Int).Value = x.defectId;
command.Parameters.Add("@Severity_Level_ID", SqlDbType.Int).Value = x.severityLevelId;
command.Parameters.Add("@Vehicle_category_ID", SqlDbType.Int).Value = x.vehicleCategoryId;
command.Parameters.Add("@IsDisabled", SqlDbType.NChar, 1).Value = (x.isDisabled.ToString())[0];
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rs.Close();
con.Close();
}
}
}
catch (Exception ex)
{
CommonUtils.WriteLog(ex.StackTrace);
}
}
public bool CheckIfSent(string MD5)
{
bool found = false;
SqlCeConnection cn = new SqlCeConnection(m_connectionString);
if (cn.State == ConnectionState.Closed)
{ cn.Open(); }
// Build the sql query. If this was real life,
// I’d use a parameter for the where bit
// to avoid SQL Injection attacks.
string sql = "select * from PixUploaded where MD5='" + MD5 + "'";
try
{
SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.CommandType = CommandType.Text;
// if you don’t set the result set to
// scrollable HasRows does not work
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
// If you need to be able to update the result set, instead use:
// SqlCeResultSet rs = cmd.ExecuteResultSet(
// ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
if (rs.HasRows)
{
// Use the get ordinal function so you don’t
// have to worry about remembering what
// order your SQL put the field names in.
//int ordLastName = rs.GetOrdinal("DateSent");
//string date = rs.GetString(ordLastName);
found = true;
//while (rs.Read())
//{
//output.AppendLine(rs.GetString(ordFirstname)+ ” “ + rs.GetString(ordLastName));
//}
}
}
catch (Exception /*ex*/)
{
}
finally
{
// Don’t need it anymore so we’ll be good and close it.
// in a ‘real life’ situation
// cn would likely be class level
cn.Close();
}
return found;
}
public void saveToDB()
{
// read the application state from db
SqlCeConnection _dataConn = null;
try
{
_dataConn = new SqlCeConnection("Data Source=FlightPlannerDB.sdf;Persist Security Info=False;");
_dataConn.Open();
SqlCeCommand selectCmd = new SqlCeCommand();
selectCmd.Connection = _dataConn;
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT cruiseSpeed,cruiseFuelFlow,minFuel,deckHoldFuel,speed,unit,utcOffset,locationFormat FROM ApplicationState");
selectCmd.CommandText = selectQuery.ToString();
SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows)
{
// update query
SqlCeCommand updCmd = new SqlCeCommand();
updCmd.Connection = _dataConn;
StringBuilder updQuery = new StringBuilder();
updQuery.Append("UPDATE ApplicationState set ");
updQuery.Append("cruiseSpeed = "+cruiseSpeed +",");
updQuery.Append("cruiseFuelFlow = " + cruiseFuelFlow + ",");
updQuery.Append("minFuel = " + minFuel + ",");
updQuery.Append("deckHoldFuel = " + deckHoldFuel + ",");
updQuery.Append("speed = '" + speed + "',");
updQuery.Append("unit = '" + unit + "',");
updQuery.Append("utcOffset = '" + utcOffset + "'");
//updQuery.Append("locationFormat = '" + locationFormat + "'");
updCmd.CommandText = updQuery.ToString();
updCmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show("Error while saving application state to the database. Please try again!");
}
finally
{
_dataConn.Close();
}
}
public List<string> GetAllQueuedPix()
{
Logger log = new Logger("PixDBInterface::GetAllQueuedPix");
List<string> result = new List<string>();
SqlCeConnection cn = new SqlCeConnection(m_connectionString);
if (cn.State == ConnectionState.Closed)
{ cn.Open(); }
string sql = "select * from PixQueue";
try
{
SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
while (rs.Read())
{
int valueOrdinal = rs.GetOrdinal("Filename");
result.Add(rs.GetString(valueOrdinal));
}
}
}
catch (Exception ex)
{
log.WriteMessage("tossed with message: " + ex.Message);
log.WriteMessage("stack: " + ex.StackTrace);
}
finally
{
cn.Close();
}
return result;
}
public List<string> ReadDirsToWatch(int inType)
{
List<string> retList = new List<string>();
SqlCeConnection cn = new SqlCeConnection(m_connectionString);
if (cn.State == ConnectionState.Closed)
{ cn.Open(); }
string sql = "select * from DirList";
try
{
SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
rs.Read();
int dirNameId = rs.GetOrdinal("Directory");
string dir = rs.GetString(dirNameId);
retList.Add(dir);
while (rs.Read())
{
retList.Add(rs.GetString(dirNameId));
}
}
}
catch (Exception /*ex*/)
{
}
finally
{
cn.Close();
}
return retList;
}
/// <summary>
/// manual migration check for SQL Server Compact Edition (CE)
/// </summary>
private void CheckCE()
{
// connect to db using connection string from parent app
using (SqlCeConnection cn = new SqlCeConnection(_cnConfig.ToString()))
{
// try to open db file but if can't locate then create and try again
try
{
cn.Open();
}
catch (Exception ex)
{
if (ex.Message.Contains("The database file cannot be found."))
{
using (SqlCeEngine ce = new SqlCeEngine(_cnConfig.ToString()))
{
ce.CreateDatabase();
}
cn.Open();
}
else
{
throw;
}
}
// check if schema exists - if so there should always be a migration history table
if (!CETableExists("__MigrationHistory", cn) && !CETableExists("MigrationHistory", cn)) NewInstall = true;
// Rename __MigrationHistory table to MigrationHistory (prevents entity framework version check)
if (CETableExists("__MigrationHistory", cn))
{
ExecCENativeSQL(Properties.Resources.RenameMigrationHistory, cn);
}
// if no schema then create entire schema then populate bitmasks
if (NewInstall)
{
// create main schema
_sql = Properties.Resources.CreateEntireSchema;
if (_cnConfig.ProviderName.Equals("System.Data.?SqlClient")) // should never be true - lifted logic as-is from "broken" migration
{ // so exactly matches latest db structure that theoretically should
_sql += Properties.Resources._201306050753190_ProgressiveLoad; // not have the sprocs that ProgressiveLoad introduces.
}
ExecCENativeSQL(_sql, cn);
// populate bitmasks
EnsureBitmasksPopulated();
}
else
{
// schema exists - build list of already executed migration steps
_sql = "SELECT MigrationId AS MigrationTitle FROM [MigrationHistory] WITH (NOLOCK) ORDER BY MigrationId";
using (SqlCeCommand cmd = new SqlCeCommand(_sql, cn))
{
using (SqlCeDataReader rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)) //cmd.ExecuteReader())
{
if (rs.HasRows)
{
while (rs.Read())
{
string[] titleParts = rs["MigrationTitle"].ToString().Split('_'); // 0 will be key, 1 will be value (description).
if (!_migrated.ContainsKey(titleParts[0]))
{
_migrated.Add(titleParts[0], titleParts[1]);
}
}
}
}
}
// see if any migration stepa are missing - if so build sql in correct order which should also note step completed in db
_sql = BuildAnyMissingMigrationStepsSQL(_migrated);
// if any steps were missing
if (_sql != "")
{
// execute sql for any missing steps
ExecCENativeSQL(_sql, cn);
// ensure bitmasks are populated/repopulated
EnsureBitmasksPopulated();
}
}
cn.Close();
}
}
public static void BulkInsertfromRecord(string table_name, string connection_string,DataTable datatable)
{
using (SqlCeConnection conn = new SqlCeConnection(connection_string))
{
SqlCeCommand cmd = new SqlCeCommand();
SqlCeResultSet rs;
SqlCeUpdatableRecord rec;
conn.Open();
cmd.Connection = conn;
cmd.CommandText = table_name;
cmd.CommandType = CommandType.TableDirect;
rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
for(int i=0;i<datatable.Rows.Count;i++)
{
rec = rs.CreateRecord();
rec.SetInt32(1, (Int32)datatable.Rows[i][0]);
rec.SetInt32(2, (Int32)datatable.Rows[i][1]);
rec.SetDecimal(3, (Decimal)datatable.Rows[i][2]);
/* rec.SetString(4, (String)datatable.Rows[i][3]);
rec.SetString(5, (String)datatable.Rows[i][4]);
rec.SetString(6, (String)datatable.Rows[i][5]);
rec.SetString(7, (String)datatable.Rows[i][6]); OLD VALUES IN BYTES
rec.SetString(8, (String)datatable.Rows[i][7]);
rec.SetString(9, (String)datatable.Rows[i][8]);*/
rs.Insert(rec);
//rec.SetValues((obj)datatable.Rows[i]);
}
datatable.Clear();
rs.Close();
rs.Dispose();
cmd.Dispose();
}
}
public bool CheckIfDbSetup()
{
bool done = false;
string sql = "select * from ConfigValues";
SqlCeConnection cn = null;
try
{
cn = new SqlCeConnection(m_connectionString);
if (cn.State == ConnectionState.Closed)
{ cn.Open(); }
SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.CommandType = CommandType.Text;
// if you don’t set the result set to
// scrollable HasRows does not work
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
// If you need to be able to update the result set, instead use:
// SqlCeResultSet rs = cmd.ExecuteResultSet(
// ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
if (rs.HasRows)
{
done = true;
}
}
catch (Exception)
{ }
finally
{
if (cn != null)
cn.Close();
}
return done;
}
public IEntity LoadEntityFromDatabase(string similarForm)
{
IEntity entity = new StreamingEntity();
using (var conn = new SqlCeConnection(NlpHelper.DatabaseConnectionString))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
try
{
var sqlCeCommand = new SqlCeCommand(DatabaseQuery.SelectIdFromFormsWhereForm, conn) { CommandType = CommandType.Text };
sqlCeCommand.Parameters.AddWithValue("@form", similarForm);
SqlCeResultSet resultSet = sqlCeCommand.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Insensitive);
List<int> ids = new List<int>();
if (resultSet.HasRows)
{
int ordId = resultSet.GetOrdinal("ID");
resultSet.ReadFirst();
ids.Add(resultSet.GetInt32(ordId));
while (resultSet.Read())
ids.Add(resultSet.GetInt32(ordId));
}
int id = ids[0];
// foreach (int id in ids)
{
sqlCeCommand = new SqlCeCommand(DatabaseQuery.SelectAllFromBaseWordWhereId, conn);
sqlCeCommand.Parameters.AddWithValue("@id", id);
resultSet =
sqlCeCommand.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Insensitive);
if (resultSet.HasRows)
{
int ordBaseForm = resultSet.GetOrdinal("BaseForm");
int ordLabel = resultSet.GetOrdinal("Label");
int ordPrefix= resultSet.GetOrdinal("Prefix");
resultSet.ReadFirst();
entity.BaseForm = resultSet.GetString(ordBaseForm);
entity.Label = resultSet.GetString(ordLabel);
entity.Prefix = resultSet.GetString(ordPrefix);
}
sqlCeCommand = new SqlCeCommand(DatabaseQuery.SelectFormFromFormsWhereId, conn);
sqlCeCommand.Parameters.AddWithValue("@id", id);
resultSet =
sqlCeCommand.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Insensitive);
if (resultSet.HasRows)
{
int ordForm = resultSet.GetOrdinal("Form");
resultSet.ReadFirst();
entity.Forms.Add(resultSet.GetString(ordForm));
while (resultSet.Read())
entity.Forms.Add(resultSet.GetString(ordForm));
}
}
return entity;
}
catch (SqlCeException sqlexception)
{
Console.WriteLine("Error form: {0}", sqlexception.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error form: {0}", ex.Message);
}
}
return null;
}
public bool CheckIfInPixQueue(string inFile)
{
bool found = false;
SqlCeConnection cn = new SqlCeConnection(m_connectionString);
if (cn.State == ConnectionState.Closed)
{ cn.Open(); }
try
{
string test = "select * from PixQueue where Filename='" + inFile + "'";
SqlCeCommand cmd = new SqlCeCommand(test, cn);
cmd.CommandType = CommandType.Text;
string foo = cmd.CommandText;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
rs.Read();
found = true;
}
}
catch (Exception /* e */)
{
}
finally
{
cn.Close();
}
return found;
}
