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();
}
//public void suaraOK()
//{
// SoundPlayer playOK = new SoundPlayer(@"Backup\doorbell.wav");
// playOK.Play();
//}
//public void suaraError()
//{
// SoundPlayer playError = new SoundPlayer(@"Backup\sirentone.WAV");
// playError.Play();
//}
public void Simpan()
{
//string Tanggal = DateTime.Now.ToString("dd/MM/yyyy");
//string JamTanggal = DateTime.Now.ToString();
try
{
SqlCeDataReader dr;
SqlCeCommand cmd = new SqlCeCommand("Select * from AngkutTP where ScanBarcode ='" + textBoxBarcode.Text + "'", cKoneksi.Con);
if (cKoneksi.Con.State == ConnectionState.Closed) { cKoneksi.Con.Open(); }
dr = cmd.ExecuteReader();
if (dr.Read())
{
//cSound.suaraError();
MessageBox.Show("Duplicate Scan!!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
dr.Close();
cmd.Dispose();
if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
textBoxBarcode.Text = "";
textBoxBarcode.Focus();
}
else
{
dr.Close();
cmd.Dispose();
string strSQL = "INSERT INTO AngkutTP VALUES('" + textBoxNoPolisi.Text + "','" + textBoxNomorator.Text + "','" + textBoxNamaTP.Text + "','" + textBoxBarcode.Text + "',GetDate())";
cQuery.Execute(strSQL);
//cSound.suaraOK();
}
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
}
}
private void frmDetail_Load(object sender, EventArgs e)
{
myConnection = default(SqlCeConnection);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [ID],[Job],[ItemId],[Qty],[Unit],[CheckedDateTime],[CheckedBy],[Checked] FROM ["
+ storagePath.getStoreTable() + "] WHERE ID ='"
+ bm + "' and Job='" + jb + "' and ItemId = '" + item + "' and Checked='true'";
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(ds);
Adapter.Dispose();
if (ds.Tables[0].Rows.Count > 0)
{
//isCheck = true;
this.txtItem.Text = ds.Tables[0].Rows[0]["ItemId"].ToString();
this.txtCheckedDateTime.Text = ds.Tables[0].Rows[0]["CheckedDateTime"].ToString();
this.txtCheckedBy.Text = ds.Tables[0].Rows[0]["CheckedBy"].ToString();
}
else
{
// isCheck = false;
}
myCommand.Dispose();
dt = null;
myConnection.Close();
}
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;
}
/// <summary>
/// Returns the number of affected rows from the Non-Query (UPDATE-, INSERT- or DELETE-statement only)
/// </summary>
/// <param name="sqlString">The SQL</param>
/// <returns></returns>
public int nonQuery(string sqlString)
{
int numberOfRows = 0;
SqlCeCommand sqlCommand = null;
try
{
dbConnection.ConnectionString = global::Tekken5DarkRessurectionScoreKeeper.Properties.Settings.Default.TekkenScoreDBConnectionString;
sqlCommand = new SqlCeCommand(sqlString, dbConnection);
openConnection();
numberOfRows = sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Database error (NonQuery)");
}
finally
{
closeConnection();
sqlCommand.Dispose();
}
return numberOfRows;
}
public void SelectUser(int x)
{
try
{
byte count = 0;
SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Users", cKoneksi.Con);
SqlCeDataReader dr;
if (cKoneksi.Con.State == ConnectionState.Closed) { cKoneksi.Con.Open(); }
dr = cmd.ExecuteReader();
if (dr.Read()) { count = 1; } else { count = 0; }
dr.Close(); cmd.Dispose(); if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
if (count != 0)
{
DataSet ds = new DataSet();
SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM Users", cKoneksi.Con);
da.Fill(ds, "Users");
textBoxUser.Text = ds.Tables["Users"].Rows[x][0].ToString();
textBoxPass.Text = ds.Tables["Users"].Rows[x][1].ToString();
checkBoxTP.Checked = Convert.ToBoolean(ds.Tables["Users"].Rows[x][2]);
checkBoxTPK.Checked = Convert.ToBoolean(ds.Tables["Users"].Rows[x][3]);
ds.Dispose();
da.Dispose();
}
else
{
MessageBox.Show("Data User Kosong", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
buttonClose.Focus();
}
}
catch (SqlCeException ex)
{
MessageBox.Show(cError.ComposeSqlErrorMessage(ex), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
}
}
/**
*
* 执行sql，包括 ： 插入、更新、删除
*
* */
public bool excute(string sql)
{
SqlCeCommand command = null;
try
{
command = new SqlCeCommand();
command.Connection = conn;
command.CommandText = sql;
command.Transaction = conn.BeginTransaction();
if (-1 != command.ExecuteNonQuery())
{
command.Transaction.Commit();
return true;
}
else
return false;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return false;
}
finally
{
if (command != null)
command.Dispose();
}
}
public void Login()
{
try
{
SqlCeDataReader dr;
string sql = "SELECT * From Users cross join idhandheld where userid='" + textBoxuserid.Text + "' and password ='******'";
SqlCeCommand cmd = new SqlCeCommand(sql,cKoneksi.Con);
if (cKoneksi.Con.State == ConnectionState.Closed) { cKoneksi.Con.Open(); }
dr = cmd.ExecuteReader();
if (dr.Read())
{
UserID = dr["Userid"].ToString();
IDHH = dr["IDHH"].ToString();
ClassUser.UserID = UserID.Trim();
ClassUser.HandheldID = IDHH.Trim();
TP = dr["TP"].ToString();
TPK = dr["TPK"].ToString();
dr.Close();
cmd.Dispose();
if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
cekrole();
}
else
{
if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
dr.Close();
cmd.Dispose();
MessageBox.Show("Username / Password Salah", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2);
textBoxuserid.Text = "";
textBoxuserid.Focus();
textBoxPassword.Text = "";
}
textBoxuserid.Text = "";
textBoxuserid.Focus();
textBoxPassword.Text = "";
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
}
}
/// <summary>
/// Cleanup unsuccessfull test log entries from the database (tests which did fail anyway).
/// </summary>
/// <param name="testContext">The test context.</param>
/// <remarks>Documented by Dev03, 2008-08-01</remarks>
internal static void Cleanup()
{
using (SqlCeConnection connection = GetConnection())
{
connection.Open();
SqlCeCommand command = new SqlCeCommand("DELETE FROM \"TestRunData\" WHERE stoptime IS NULL AND run_id = @runid", connection);
command.Parameters.Add("@runid", SqlDbType.Int, 8);
command.Parameters["@runid"].Value = TestRunId;
command.ExecuteNonQuery();
command.Dispose();
}
}
static void Main(string[] args)
{
var connectionString = $"Data Source = \"{SdfFilePath}\"";
SqlCeConnection connection = null;
SqlCeCommand command = null;
SqlCeDataReader reader = null;
try
{
connection = new SqlCeConnection(connectionString);
connection.Open();
var query = "SELECT * FROM Petitioners";
command = new SqlCeCommand(query, connection);
reader = command.ExecuteReader();
var json = new List <ExpandoObject>();
while (reader.Read())
{
var item = new ExpandoObject();
Enumerable.Range(0, reader.FieldCount)
.Skip(1)
.Select(i => new { Field = reader.GetName(i), Value = reader[i] })
.ToList()
.ForEach(data => ((IDictionary <string, Object>)item)[data.Field] = data.Value);
json.Add(item);
}
using (var jsonWriter = new StreamWriter($"{JsonFilePath}", false, Encoding.Default))
{
jsonWriter.WriteLine(JsonConvert.SerializeObject(json, Formatting.Indented));
};
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
finally
{
reader?.Close();
command?.Dispose();
connection?.Close();
}
Console.WriteLine($"{Environment.NewLine}Press any key to exit.");
Console.ReadKey();
}
static void Main(string[] args)
{
var connectionString = $"Data Source = \"{SdfFilePath}\"";
SqlCeConnection connection = null;
SqlCeCommand command = null;
SqlCeDataReader reader = null;
StreamWriter csvWriter = null;
try
{
connection = new SqlCeConnection(connectionString);
connection.Open();
var query = "SELECT * FROM Petitioners";
command = new SqlCeCommand(query, connection);
reader = command.ExecuteReader();
csvWriter = new StreamWriter($"{CsvFilePath}", false, Encoding.Default);
csvWriter.WriteLine(Enumerable.Range(0, reader.FieldCount)
.Select(i => $"\"{reader.GetName(i)}\"")
.Aggregate((current, next) => $"{current},{next}"));
while (reader.Read())
{
csvWriter.WriteLine(Enumerable.Range(0, reader.FieldCount)
.Select(i => String.Format("\"{0}\"",
reader.GetValue(i)
.ToString()
.Replace(',', '_')
.Replace('\"', '_')))
.Aggregate((current, next) => $"{current},{next}"));
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
finally
{
csvWriter?.Close();
reader?.Close();
command?.Dispose();
connection?.Close();
}
Console.WriteLine($"{Environment.NewLine}Press any key to exit.");
Console.ReadKey();
}
/// <summary>
/// Hakee tietokannasta osallistujien ID:T ja paivittaa onkoTyhja
/// arvon osallistujien ID-listan pituudella. Arvoa tarvitaan
/// evaamaan tai sallimaan paasy tulosten kirjaukseen
/// </summary>
/// <param name="onkoTyhja"> arvo kertoo montako osallistujaa tietokannassa on </param>
/// <returns></returns>
public int checkOsallistujat(int onkoTyhja)
{
List<int> osallistujat = new List<int>();
SqlCeCommand cmd = null;
SqlCeDataReader rdr = null;
bool ok = false;
do
{
SqlCeConnection con = _connection;
try
{
if (con.State == ConnectionState.Closed)
con.Open();
string Sql = String.Format(" SELECT osallistujaID FROM Osallistuja");
cmd = new SqlCeCommand(Sql, con);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
osallistujat.Add(rdr.GetInt32(0));
}
onkoTyhja = osallistujat.Count();
Console.WriteLine("Osallistujien maara tietokannassa: " + onkoTyhja);
}
catch (SqlCeException ex)
{
//ShowErrors(ex);
Console.WriteLine("VIRHEILMOITUS checkOsallistujat");
Console.WriteLine(ex.Message);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
rdr.Close();
cmd.Dispose();
ok = true;
}
} while (!ok);
return onkoTyhja;
}
private void buttonAngkutTP_Click(object sender, EventArgs e)
{
DialogResult pesan = MessageBox.Show("Anda Yakin ingin mengconvert data ini?", "Konfirmasi", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
if (DialogResult.OK == pesan)
{
//convert
try
{
Cursor.Current = Cursors.WaitCursor;
byte count = 0;
SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM AngkutTP", cConnection.Con);
SqlCeDataReader dr;
if (cConnection.Con.State == ConnectionState.Closed) { cConnection.Con.Open(); }
dr = cmd.ExecuteReader();
if (dr.Read())
{
count = 1;
}
else
{
count = 0;
}
dr.Close();
cmd.Dispose();
if (cConnection.Con.State == ConnectionState.Open) { cConnection.Con.Close(); }
if (count != 0)
{
ConvertAngkutTP();
Cursor.Current = Cursors.Default;
}
else
{
Cursor.Current = Cursors.Default;
MessageBox.Show("Tidak ada data Angkut TP", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
Cursor.Current = Cursors.Default;
}
}
}
public PrekrsajnaForma()
{
InitializeComponent();
// Dohvat popisa prekršaja.
SqlCeConnection conn = new SqlCeConnection(Program.ConnString);
string sqlQry =
"SELECT " +
"ID, " +
"Naziv " +
"FROM " +
"Prekrsaj;";
try
{
DataTable dt = new DataTable("Prekrsaji");
SqlCeCommand cmd = new SqlCeCommand(sqlQry, conn);
SqlCeDataAdapter da = new SqlCeDataAdapter(sqlQry, conn);
conn.Open();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
lbxPrekrsaji.DataSource = dt;
//lbxPrekrsaji.Refresh();
}
else
{
throw new Exception("WTF! (What a Terrible Failure). Katalog prekršaja je prazan!");
}
da.Dispose();
cmd.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
}
}
public static void Exec(string query, SqlCeConnection conn, Dictionary<string, object> parameters = null)
{
SqlCeTransaction trans = conn.BeginTransaction();
using (SqlCeCommand com = new SqlCeCommand(query, conn))
{
com.Transaction = trans;
if (parameters != null)
{
foreach (var element in parameters)
{
com.Parameters.AddWithValue(element.Key, element.Value);
}
}
com.ExecuteNonQuery();
trans.Commit(CommitMode.Immediate);
com.Dispose();
}
}
public bool checkUser(String username)
{
try
{
conn = new SqlCeConnection(Settings.Default.AccountsConnectionString);
conn.Open();
cmd = new SqlCeCommand("SELECT * FROM [Users] WHERE Username = @Username;", conn);
cmd.Parameters.AddWithValue("@Username", username);
SqlCeDataReader reader = cmd.ExecuteReader();
cmd.Dispose();
return reader.HasRows;
}
catch (Exception) { }
return false;
}
//static DataModelTestDataContext dm = new DataModelTestDataContext();
public static List<ConfiguracionB> ObtenerConfiguraciones()
{
// metodo agregado
#region SQL compact connection
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
SqlCeDataReader rdr = null;
List<ConfiguracionB> result = new List<ConfiguracionB>();
try
{
conn = new SqlCeConnection("Data Source=" + System.IO.Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "HRNPCIData.sdf"));
conn.Open();
cmd = new SqlCeCommand("SELECT * From Configuracion", conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ConfiguracionB f = new ConfiguracionB();
if (!DBNull.Value.Equals(rdr[0])) f.iCodigoConfiguracion = rdr.GetInt32(0);
if (!DBNull.Value.Equals(rdr[1])) f.vParametro = rdr.GetString(1);
if (!DBNull.Value.Equals(rdr[2])) f.vRutaEstatica = rdr.GetString(2);
if (!DBNull.Value.Equals(rdr[3])) f.dtFechaCreacion = rdr.GetDateTime(3);
if (!DBNull.Value.Equals(rdr[4])) f.dtFechaActualizacion = rdr.GetDateTime(4);
result.Add(f);
}
rdr.Close();
cmd.Dispose();
}
catch (Exception)
{
return null;
}
finally
{
conn.Close();
}
return result;
#endregion
}
private void OnDelete(object sender, EventArgs e)
{
if (!menuDelete.Enabled)
return;
if (MessageBox.Show("Do you want to delete selected accounts?", "Delete Accounts", MessageBoxButtons.YesNo,
MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.Yes)
return;
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
try
{
conn = new SqlCeConnection(Settings.Default.UserAccountsConnectionString);
conn.Open();
int cnt = accountListView.SelectedItems.Count;
// Itera tutti gli utenti selezionati
for (int n = cnt - 1 ; n >= 0 ; n--)
{
cmd = new SqlCeCommand("DELETE FROM [UserAccounts] WHERE [Username] = @username;", conn);
cmd.Parameters.AddWithValue("@username", accountListView.SelectedItems[n].SubItems[colUsername].Text);
int cntAffected = cmd.ExecuteNonQuery();
cmd.Dispose();
if (cntAffected == 1)
accountListView.SelectedItems[n].Remove();
}
conn.Close();
}
catch (Exception ex)
{
if (cmd != null)
cmd.Dispose();
if (conn != null)
conn.Close();
MessageBox.Show(ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public int checkLogin(String username, String password)
{
try
{
if (checkUser(username))
return -1;
conn = new SqlCeConnection(Settings.Default.AccountsConnectionString);
conn.Open();
cmd = new SqlCeCommand("SELECT * FROM [Users] WHERE Username = @Username AND Password = @Password;", conn);
cmd.Parameters.AddWithValue("@Username", username);
cmd.Parameters.AddWithValue("@Password", password);
SqlCeDataReader reader = cmd.ExecuteReader();
cmd.Dispose();
return 1;
} catch(Exception) {}
return 0;
}
public DataTable BindGrid(string text)
{
try
{
con.Open();
cmd = new SqlCeCommand("SELECT * FROM SubMenuValue where SubMenuId='" + text + "'", con);
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
con.Close();
cmd.Dispose();
return (dt);
}
catch (Exception e)
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
throw (e);
}
}
public string del_submenu(string record_id)
{
try
{
con.Open();
cmd = new SqlCeCommand("delete from SubMenuValue where Id=@Id", con);
cmd.Parameters.AddWithValue("@Id", record_id);
result = cmd.ExecuteNonQuery().ToString();
cmd.Dispose();
con.Close();
return (result);
}
catch (Exception e)
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
throw (e);
}
}
public void Init()
{
var engine = new SqlCeEngine(_connstr);
if (System.IO.File.Exists(_dbFilename)) {
System.IO.File.Delete(_dbFilename);
}
engine.CreateDatabase();
_conn = new SqlCeConnection(_connstr);
_conn.Open();
var cmd = new SqlCeCommand("create table test_table (id int identity primary key, str nvarchar(100));", _conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
if (_conn != null) {
_conn.Close();
_conn.Dispose();
}
_indepDalc = new SqlCeDalc(_connstr);
}
/// <summary>
/// Starts the specified test context.
/// </summary>
/// <param name="testContext">The test context.</param>
/// <remarks>Documented by Dev03, 2008-08-01</remarks>
internal static void Start(TestContext testContext)
{
DateTime starttime = DateTime.Now;
using (SqlCeConnection connection = GetConnection())
{
connection.Open();
if (TestRunId < 0)
{
SqlCeCommand command = new SqlCeCommand("INSERT INTO \"TestRun\" (date, name) VALUES (GETDATE(), @testname);", connection);
command.Parameters.Add("@testname", SqlDbType.NVarChar, 100);
command.Parameters["@testname"].Value = TestName;
command.ExecuteNonQuery();
command.Dispose();
command = new SqlCeCommand("SELECT @@IDENTITY;", connection);
TestRunId = Convert.ToInt32(command.ExecuteScalar());
command.Dispose();
}
if (TestRunId > -1)
{
SqlCeCommand command = new SqlCeCommand("INSERT INTO \"TestRunData\" (run_id, cs_id, name, starttime, text) VALUES (@runid, @csid, @testname, @time, NULL)", connection);
command.Parameters.Add("@runid", SqlDbType.Int, 8);
command.Parameters.Add("@csid", SqlDbType.Int, 8);
command.Parameters.Add("@testname", SqlDbType.NVarChar, 100);
command.Parameters.Add("@time", SqlDbType.DateTime);
command.Parameters["@runid"].Value = TestRunId;
command.Parameters["@csid"].Value = (int)testContext.DataRow["ID"];
command.Parameters["@testname"].Value = testContext.TestName;
command.Parameters["@time"].Value = starttime;
command.ExecuteNonQuery();
command.Dispose();
command = new SqlCeCommand("SELECT @@IDENTITY;", connection);
starttimes.Add(Convert.ToInt32(command.ExecuteScalar()), starttime);
command.Dispose();
}
}
}
public static void AddLog(string strMessage, string strSource = "app", string strLevel = "info", bool bolSendToAdmin = false, int intServerID = 0)
{
if (strMessage == null) strMessage = "Null message received";
string sqlIns = "INSERT INTO Log (LogSource, LogMessage, LogLevel, ServerID) VALUES (@source, @msg, @level, @serverid)";
try
{
if (strMessage.Length > 255) strMessage = strMessage.Substring(0, 255);
SqlCeCommand cmdIns = new SqlCeCommand(sqlIns, connLocal);
cmdIns.Parameters.Add("@source", strSource);
cmdIns.Parameters.Add("@msg", Util.Left(strMessage, 255));
cmdIns.Parameters.Add("@level", strLevel);
cmdIns.Parameters.Add("@serverid", intServerID);
cmdIns.ExecuteNonQuery();
cmdIns.Dispose();
cmdIns = null;
}
catch (Exception ex)
{
throw new Exception(ex.ToString(), ex);
}
}
public static void AddLog(DateTime datTimeStamp, string strMessage, string strSource = "app", string strLevel = "info", bool bolSendToAdmin = false, int intServerID = 0)
{
if (strMessage == null) strMessage = "Null message received";
string sqlIns = "INSERT INTO Log (LogSource, LogMessage, LogLevel, ServerID, LogDateTime) VALUES (@source, @msg, @level, @serverid, @timestamp)";
try
{
SqlCeCommand cmdIns = new SqlCeCommand(sqlIns, connLocal);
cmdIns.Parameters.Add("@source", strSource);
cmdIns.Parameters.Add("@msg", strMessage);
cmdIns.Parameters.Add("@level", strLevel);
cmdIns.Parameters.Add("@serverid", intServerID);
cmdIns.Parameters.Add("@timestamp", datTimeStamp);
cmdIns.ExecuteNonQuery();
cmdIns.Dispose();
cmdIns = null;
}
catch (Exception ex)
{
throw new Exception(ex.ToString(), ex);
}
}
public static void setImagem(Int64 Id, string tabela, string campoId, Image imagem)
{
//string ConnectionString = ConfigurationManager.ConnectionStrings["prjbase.Properties.Settings.dbintegracaoConnectionString"].ConnectionString;
string ConnectionString = ConfigurationManager.ConnectionStrings["prjbase.Properties.Settings.ConnectionString"].ConnectionString;
long? Id_Img = null;
//MySqlConnection con = new MySqlConnection(ConnectionString);
SqlCeConnection con = new SqlCeConnection(ConnectionString);
try
{
if (!string.IsNullOrEmpty(tabela) & !string.IsNullOrEmpty(campoId))
{
byte[] pic_arr = ConverterFotoParaByteArray((Image)imagem.Clone());
con.Open();
string sql = "SELECT Id from " + tabela + " where " + campoId + " = @Id";
//MySqlCommand cmd = new MySqlCommand(sql, con);
SqlCeCommand cmd = new SqlCeCommand(sql, con);
cmd.Parameters.AddWithValue("@Id", Id);
SqlCeDataReader dr = cmd.ExecuteReader();
bool HasRows = dr.Read();
if (HasRows)
{
Id_Img = Convert.ToInt64(dr["Id"]);
}
dr.Close();
dr.Dispose();
cmd.Dispose();
sql = string.Empty;
if (Id_Img != null)
{
sql = @"update " + tabela + " set imagem = @imagem where Id = @Id";
SqlCeCommand cmdUpdate = new SqlCeCommand(sql, con);
cmdUpdate.Parameters.AddWithValue("@imagem", pic_arr);
cmdUpdate.Parameters.AddWithValue("@Id", Id_Img);
cmdUpdate.ExecuteNonQuery();
cmdUpdate.Dispose();
}
else
{
sql = @"insert into " + tabela + " (" + campoId + ", imagem) values(@campoId,@imagem)";
SqlCeCommand cmdUpdate = new SqlCeCommand(sql, con);
cmdUpdate.Parameters.AddWithValue("@imagem", pic_arr);
cmdUpdate.Parameters.AddWithValue("@campoId", Id);
cmdUpdate.ExecuteNonQuery();
cmdUpdate.Dispose();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
static void ClientThread(Object StateInfo)
{
TcpClient Client = (TcpClient)StateInfo;
var stream = Client.GetStream();
StreamReader reader = new StreamReader(stream);
StreamWriter writer = new StreamWriter(stream);
writer.AutoFlush = true;
SqlCeCommand command = connection.CreateCommand();
bool isFirst = Boolean.Parse(reader.ReadLine());
bool bad = false;
string NickName = reader.ReadLine();
string Password = reader.ReadLine();
if (isFirst)
{
command.CommandText = "INSERT INTO Users (Login,Password,LastConnection,RegistrationDate) VALUES (@NN,@pwd,@lc,@rd)";
command.Parameters.AddWithValue("@NN", NickName);
command.Parameters.AddWithValue("@pwd", Password);
command.Parameters.AddWithValue("@lc", DateTime.Now.ToString());
command.Parameters.AddWithValue("@rd", DateTime.Now.ToString());
try
{
int count = command.ExecuteNonQuery();
}
catch (Exception)
{
writer.WriteLine("THIS NICKNAME IS ALREADY USED");
writer.WriteLine("ERROR\t");
// Thread.Sleep(10);
bad = true;
}
}
else
{
command.CommandText = "SELECT COUNT(*) FROM Users WHERE Login = @nn AND Password = @pwd";
command.Parameters.AddWithValue("@NN", NickName);
command.Parameters.AddWithValue("@pwd", Password);
if ((int)command.ExecuteScalar() == 1)
{
command.CommandText = "UPDATE Users set LastConnection = @lc WHERE Login = @lg";
command.Parameters.AddWithValue("@lc", DateTime.Now.ToString());
command.Parameters.AddWithValue("@lg", NickName);
int count = command.ExecuteNonQuery();
}
else
{
writer.WriteLine("ERROR\t");
//Thread.Sleep(10);
bad = true;
}
}
command.Dispose();
if (!bad)
{
SendMessages("\"" + NickName + "\"" + " CONNECTED!");
Writers.Add(writer);
while (Client.Connected)
{
try
{
Message = reader.ReadLine();
if (Message == null)
{
Message = "\"" + NickName + "\"" + " DISCONNECTED!";
}
SendMessages(Message);
}
catch (Exception)
{ }
}
}
}
public void syncUserLoginLogfromlocaldbtoSQLdb()
{
dbCon = new SqlCeConnection(localConnection);
dbCon.Open();
string sql = "select count(*) from UserLoginLog";
SqlCeCommand cmd = new SqlCeCommand(sql, dbCon);
int flg;
flg = int.Parse(cmd.ExecuteScalar().ToString());
if (flg == 0)
{
MessageBox.Show("No data in the localdb to sync.");
//classLog.DataSyncLog("No data in the localdb to sync.", fname);
}
else
{
//classLog.DataSyncLog("[TagNo],[TruckNo],[TaskCode],[SubTaskCode],[Location],[ReaderNo],[ReaderIP],[TStatus],[ReadTime],[CreatedBy],[CreatedDate],[ReaderOperation]", fname);
int i;
for (i = 0; i < flg; i++)
{
//txtCount.Text = "Sync Progress: " + (flg - i).ToString();
string c = "Select min(Id) from UserLoginLog ";
SqlCeCommand c1 = new SqlCeCommand(c, dbCon);
Rid = int.Parse(c1.ExecuteScalar().ToString());
c1.Dispose();
string cmd1 = "select Id,Loginid,UserType,LoginTime from UserLoginLog where Id=" + Rid + "";
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd1, dbCon);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
string id = ds.Tables[0].Rows[0].ItemArray[0].ToString();
string loginid = ds.Tables[0].Rows[0].ItemArray[1].ToString();
string usertype = ds.Tables[0].Rows[0].ItemArray[2].ToString();
string logintime = ds.Tables[0].Rows[0].ItemArray[9].ToString();
DateTime dt = Convert.ToDateTime(logintime);
string cdt = dt.ToString("yyy-MM-dd HH:mm:ss");
string ndt = "{ts '" + cdt + "'}";
string cmd01 = "select ReaderId from Config ";
SqlCeDataAdapter da1 = new SqlCeDataAdapter(cmd01, dbCon);
DataSet ds1 = new DataSet(); da1.Fill(ds1);
string readerid = ds1.Tables[0].Rows[0].ItemArray[0].ToString();
string TableName = "HHDUserLoginLog";
signal();
if (strength < -10 && strength > -95)
{
string CONN_STRING = sqlConnection;
SqlConnection dbCon1 = new SqlConnection(CONN_STRING);
try
{
dbCon1.Open();
string Query = "Insert into [dbo].[" + TableName + "] ([Readerid],[Loginid],[UserType],[LoginTime],[Synctime]) Values('" + readerid + "','" + loginid + "'," + usertype + "," + ndt + "',GETDATE())";
SqlCommand cm = new SqlCommand(Query, dbCon1);
cm.ExecuteNonQuery();
dbCon1.Close();
clearLoginlocaldb();
da.Dispose();
ds.Dispose();
// classLog.DataSyncLog(tagno + "," + truckno + "," + taskcode + "," + subtaskcode + "," + Location + "," + readerno + "," + readerip + "," + status + "," + ndt + "," + createdby + "," + ndt + "," + readeroperation, fname);
}
catch
{
MessageBox.Show("Database Disconnected.");
//classLog.DataSyncLog("Database Disconnected", fname);
}
// count--;
}
else
{
MessageBox.Show("Wifi Disconnected.");
//classLog.DataSyncLog("Wifi Disconnected", fname);
i = 100000;
}
}
catch
{
MessageBox.Show("Details not found in the Database.");
}
}
if (i == flg)
{
//_lbReadInfoReadWrite.Text = "Sync Complete";
classLog.writeLog("Sync Complete.");
// classLog.DataSyncLog("Sync Complete", fname);
}
else
{
//_lbReadInfoReadWrite.Text = "DB Connection Failed.";
MessageBox.Show("DB Connection Failed.");
}
}
dbCon.Close();
}
//=============================================================
private void btn_gravar_Click(object sender, EventArgs e)
{
SqlCeConnection conexao = new SqlCeConnection("Data source = " + cl_static.base_dados);
conexao.Open();
//grava novo registro ou edita registro existente
#region verificações...(para ver se o contato esta sem preencher ou contato duplicados)
//verifica se os campos estão preenchidos
if (textBox_nome.Text == "" || textBox_telefone.Text == "")
{
MessageBox.Show("Falta preencher todos os campos.");
return;
}
#endregion
//=============================================================
#region Novo contato
if (!editar) //se for igual a falso cria um novo contato
{
//buscar o id_contato disponivel (para isso criei um objeto logo acima como "conexao"
SqlCeDataAdapter adptador = new SqlCeDataAdapter("SELECT MAX(id_contato) AS maxid FROM contatos", conexao);
DataTable dados = new DataTable();
adptador.Fill(dados);
//verifica se o valor é nulo(null) \/
if (DBNull.Value.Equals(dados.Rows[0][0]))
{
id_contato = 0;
}
//se sim o valor for nulo ele vai parar o codigo acima/\ e vai aparecer a Messagebox criada nas verificações
//se nao for nulo ele continua o código abaixo \/
//nulo que digo é a pessoa não preencher os campos, se ela preencher não vai ser nulo
else
{
id_contato = Convert.ToInt16(dados.Rows[0][0]) + 1;
//se o valor nao for nulo ele vai pegar o valor acima e vai acrescentar mais uma unidade
}
//DBNull = data base null(nulo) usados para ver valores nulos duma base de dados
//"!" = diferente
// -------------------------------------------------------------------------------
//gravar o novo contato na base de dados
SqlCeCommand comando = new SqlCeCommand();
comando.Connection = conexao;
//modo seguro de realizar o sql sem correr o risco de SQLINJETION \/
//parametros(enviar para o meu comando cada um dos dados que quero gravar na base de dados e cada um dos dados vai ser um parametro)
comando.Parameters.AddWithValue("@id_contato", id_contato);
comando.Parameters.AddWithValue("@nome", textBox_nome.Text);
comando.Parameters.AddWithValue("@telefone", textBox_telefone.Text);
comando.Parameters.AddWithValue("@atualizacao", DateTime.Now);
//verifica se ja existe um contato com o mesmo nome e telefone
//veja como nao precisei criar um novo objeto eu simplesmente peguei o criado ali em cima (abaixo de if(editar))
//peguei os mesmos nomes e abri o objeto
adptador = new SqlCeDataAdapter();
dados = new DataTable();
comando.CommandText = "SELECT * FROM contatos WHERE nome = @nome AND telefone = @telefone"; //preciso definir o "comando" dessa forma pois se nao implica logo abaixo com o "texto da query"
adptador.SelectCommand = comando; //eu falo que o adptador é igual comando para não ter que criar esse codigo igual acima "comando.Parameters.AddWithValue("@atualizacao", DateTime.Now);" se nao teria que por todos
adptador.Fill(dados);
if (dados.Rows.Count != 0) //se a contagem de linha for diferente de 0 (ou seja se ja existir um contato em outra linha)
{
//ja existe um registro com o mesmo nome e telefone
//MessageBox.Show("Já existe um registro com o mesmo nome e telefone !");
//return; === esse exemplo aqui é para mostar ao usuario que ja existe nome e telefone e impedir ele de prosseguir.
// ja o exemplo abaixo não, mesmo se tiver contatos iguais ele tem a chance de prosseguir
//se ainda sim quer que o contato seja salvo
if (MessageBox.Show("Já existe um registro com o mesmo nome e telefone." + Environment.NewLine +
"Deseja salvar o contato mesmo assim ?", "ATENÇÃO", MessageBoxButtons.YesNo,
MessageBoxIcon.Question) == DialogResult.No)
{
return;
}
//Environment.NewLine = para colocar a frase em uma nova linha (paragrafo)(ou mudar de linha)
//se ele apertar "No" o procedimento para, mas se ele aperta "Yes" o procedimento continua nos códigos abaixo.
}
//texto da query
comando.CommandText = "INSERT INTO contatos VALUES(" +
"@id_contato,@nome,@telefone,@atualizacao)";
MessageBox.Show("Contato adicionado!");
//limpa o textbox apos adicionar o contato
textBox_nome.Text = "";
textBox_telefone.Text = "";
textBox_nome.Focus();
comando.ExecuteNonQuery(); // não executa informação, mas vai guardar informação na base de dados
comando.Dispose();
conexao.Dispose();
//modo não seguro de realizar o sql com risco de SQLINJETION \/
//--------------------------------------------------(primeiro eu fiz assim aqui em cima /\)
/* SqlCeCommand comando = new SqlCeCommand();
* comando.Connection = conexao;
* comando.CommandText = //todo esse código criado aqui não é seguro
* "INSERT INTO contatos VALUES(" + //pode ser facilmente estragado por SQLINJETION
* "'" + id_contato + ", " + //NAO REALIZAR ELE DESSA FORMA QUE ESTA AQUI
* "'" +textBox_nome.Text + "', " +
* textBox_telefone.Text + "', " +
* DateTime.Now + ")"; */
/* /\ estou fazendo isso acima
* INSERT INTO contatos VALUES
* id_contato,
* nome,
* telefone,
* atualização
*/
//-------------------------------------
//OBSERVAÇÃO MUITO IMPORTANTE, O CÓDIGO ACIMA NÃO ESTA SEGURO
//CASO ALGUEM COLOQUE NO NOME "DELETE FROM ALL" ELE VAI INSERIR UMA QUERY E VAI EXECUTAR
//OU SE A PESSOA COLOCAR UM CODIGO HACKER ELA VAI TER ACESSO AO BANCO DE DADOS
//É O QUE CHAMAN DE SQLINJECTION !!!
}
#endregion
//=============================================================
#region Editar contato
else
{
//se for verdadeiro edita o contato na base de dados
SqlCeCommand comando = new SqlCeCommand();
comando.Connection = conexao;
//modo seguro de realizar o sql sem correr o risco de SQLINJETION \/
//criando parametros no sql
comando.Parameters.AddWithValue("@id_contato", id_contato);
comando.Parameters.AddWithValue("@nome", textBox_nome.Text);
comando.Parameters.AddWithValue("@telefone", textBox_telefone.Text);
comando.Parameters.AddWithValue("@atualizacao", DateTime.Now);
//verifica se existe um registro com o mesmo nome
DataTable verifica = new DataTable();
comando.CommandText = "SELECT * FROM contatos WHERE nome = @nome AND id_contato <> @id_contato";
SqlCeDataAdapter adaptadorzinho = new SqlCeDataAdapter();
adaptadorzinho.SelectCommand = comando;
adaptadorzinho.Fill(verifica);
if (verifica.Rows.Count != 0)
{
//foi encontrado um contato com o mesmo nome, pergunta se o usuario quer editar ou salvar igual o mesmo contato
if (MessageBox.Show("Já existe um registro com o mesmo nome." + Environment.NewLine +
"Deseja salvar o contato mesmo assim ?", "ATENÇÃO", MessageBoxButtons.YesNo,
MessageBoxIcon.Question) == DialogResult.No)
{
return;
}
}
//editar o contato selecionado
comando.CommandText = "UPDATE contatos SET " +
"nome = @nome, " +
"telefone = @telefone, " +
"atualizacao = @atualizacao " +
"WHERE id_contato = @id_contato";
comando.ExecuteNonQuery();
//fecha o quadro e volta para a tela da tabela
this.Close();
}
#endregion
}
public void Dispose()
{
connection.Close();
connection.Dispose();
command.Dispose();
}
public static List <clsMatch> getAllMatches()
{
List <clsMatch> matchList = new List <clsMatch>();
SqlCeCommand com = new SqlCeCommand("SELECT * FROM tblMatch", connection.CON);
DataSet ds = new DataSet("tblMatch");
SqlCeDataAdapter ad = new SqlCeDataAdapter(com);
ad.Fill(ds, "tblMatch");
foreach (DataRow r in ds.Tables["tblMatch"].Rows)
{
string savePath = r[9].ToString();
List <clsTeams> squard = new List <clsTeams>();
clsTeams team1 = new clsTeams();
clsTeams team2 = new clsTeams();
int team1CountryId = Convert.ToInt32(r[5].ToString());
int team2CountryId = Convert.ToInt32(r[6].ToString());
team1.country = clsCountry.getCountry(clsCountry.getCountryName(team1CountryId));
team2.country = clsCountry.getCountry(clsCountry.getCountryName(team2CountryId));
SqlCeConnection con_temp = connection.getConnection(savePath);
if (con_temp != null)
{
SqlCeCommand com1 = new SqlCeCommand("SELECT playerId, countryId FROM tblTeam", con_temp);
DataSet ds1 = new DataSet("tblTeam");
SqlCeDataAdapter ad1 = new SqlCeDataAdapter(com1);
ad1.Fill(ds1, "tblTeam");
//Get the two teams // Inner loop
foreach (DataRow r1 in ds1.Tables["tblTeam"].Rows)
{
//If it is team 1 (Country 1)
if (Convert.ToInt32(r1[1].ToString()) == team1CountryId)
{
//clsPlayer pp=clsPlayer.getPlayer(Convert.ToInt32(r1[0].ToString()));
team1.players.Add(clsPlayer.getPlayer(Convert.ToInt32(r1[0].ToString())));
}
else
{
team2.players.Add(clsPlayer.getPlayer(Convert.ToInt32(r1[0].ToString())));
}
}
squard.Add(team1);
squard.Add(team2);
clsMatch mt = new clsMatch(Convert.ToInt32(r[0].ToString()), r[1].ToString(), clsMatchTypes.getMatchType(Convert.ToInt32(r[7].ToString())), Convert.ToDateTime(r[2].ToString()), r[9].ToString(), clsCountry.getCountry(clsCountry.getCountryName(Convert.ToInt32(r[3].ToString()))), clsGround.getGround(Convert.ToInt32(r[4].ToString())), squard, "toss won", "First bat");
matchList.Add(mt);
}
}
com.Dispose();
return(matchList);
}
private void searchTB_TextChanged(object sender, EventArgs e)
{
String sql;
SqlCeCommand comm = new SqlCeCommand();
SqlCeDataReader reader;
comm.Connection = _29thStreet_Cafe_Sales_Inventory_System.Connector.db.getConnection();
if (comboBox1.Text == "Lastname")
{
this.userGV.Rows.Clear();
sql = "SELECT * from Users where lname LIKE '" + searchTB.Text + "%'";
comm.CommandText = sql;
}
else if (comboBox1.Text == "User ID")
{
this.userGV.Rows.Clear();
sql = "SELECT * from Users where userID LIKE " + Convert.ToInt32(searchTB.Text);
comm.CommandText = sql;
}
else if (comboBox1.Text == "Firstname")
{
this.userGV.Rows.Clear();
sql = "SELECT * from Users where fname LIKE '" + searchTB.Text + "%'";
comm.CommandText = sql;
}
else if (comboBox1.Text == "Type")
{
this.userGV.Rows.Clear();
sql = "SELECT * from Users where type LIKE '" + searchTB.Text + "%'";
comm.CommandText = sql;
}
else if (comboBox1.Text == "Username")
{
this.userGV.Rows.Clear();
sql = "SELECT * from Users where username LIKE '" + searchTB.Text + "%'";
comm.CommandText = sql;
}
else if (comboBox1.Text == "Password")
{
this.userGV.Rows.Clear();
sql = "SELECT * from Users where password LIKE '" + searchTB.Text + "%'";
comm.CommandText = sql;
}
else
{
this.userGV.Rows.Clear();
displayUser();
}
reader = comm.ExecuteReader();
while (reader.Read())
{
this.userGV.Rows.Add(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5));
}
reader.Close();
comm.Dispose();
}
private void INV_REF_Load(object sender, EventArgs e)
{
try {
String no = "#REN-" + Payments.com;
String nam = Invoice.nam;
MessageBox.Show("Inoice No:-" + no + "\n Name:-" + nam);
String q1 = "SELECT * from Invoice_Dei where So_no='" + no + "'";
String q2 = "SELECT * from Inv_Tax where Inv_Id='" + no + "'";
String q3 = "SELECT * from customer where [First_name]='" + nam + "'";
// String q1 = "SELECT Est_Details.Date,Estimates.Cust_name, Est_Details.Expire_date, Est_Details.Items, Est_Details.Qty, Est_Details.Rate, Est_Details.Amt, Est_Details.Sub_Total, Est_Details.Discount,Est_Details.Adjustment, Est_Details.Total, Estimates.Cust_name, Est_Details.Ref FROM Est_Details INNER JOIN Estimates ON Est_Details.Total = Estimates.Amount where Est_Details.[Ref]=" + no + "";
SqlCeConnection con = new SqlCeConnection(Properties.Settings.Default.conne);
SqlCeCommand cmd = new SqlCeCommand();
SqlCeDataAdapter sad = new SqlCeDataAdapter();
DataSet dt = new DataSet();
con.Open();
sad.SelectCommand = cmd;
cmd.Connection = con;
cmd.CommandText = q1;
sad.Fill(dt, "Table[0]");
cmd.CommandText = q2;
sad.Fill(dt, "Table[1]");
cmd.CommandText = q3;
sad.Fill(dt, "Table[2]");
sad.Dispose();
cmd.Dispose();
inv i = new inv();
//inv1b2 i = new inv1b2();
i.Database.Tables["Invoice_Dei"].SetDataSource(dt.Tables[0]);
i.Database.Tables["Inv_Tax"].SetDataSource(dt.Tables[1]);
i.Database.Tables["customer"].SetDataSource(dt.Tables[2]);
//ReportDocument crystal = new ReportDocument();
rptviewer.ReportSource = null;
rptviewer.ReportSource = i;
// rptviewer.Refresh();
//dt.Tables[0].Merge(dt.Tables[1]);
// dt.Tables[0].Merge(dt.Tables[1]);
// string path = System.IO.Path.GetDirectoryName(Application.ExecutablePath);
// MessageBox.Show(path);
//string path = Application.StartupPath;
// string path = System.Windows.Forms.Application.StartupPath.Substring(0, System.Windows.Forms.Application.StartupPath.Substring(0, System.Windows.Forms.Application.StartupPath.LastIndexOf("\\")).LastIndexOf("\\"));
//path += @"\inv.rpt";
//MessageBox.Show(path);
// path = System.IO.Directory.GetParent(System.IO.Directory.GetParent(path).ToString()).ToString();
//MessageBox.Show(path);
//path=@"C:\VensarkBill\";
// path = System.IO.Path.Combine(path, "inv.rpt");
//MessageBox.Show(path);
// crystal.Load("C:\\VensarkBilling\\rest\\inv.rpt");
//crystal.Load(path);
//crystal.Database.Tables[0].SetDataSource(dt.Tables[0]);
//crystal.Database.Tables[1].SetDataSource(dt.Tables[1]);
// crystal.SetDataSource(dt.Tables[0]);
}
catch (Exception o) {
MessageBox.Show("Erorr " + o);
}
}
private void UpdateDatabase()
{
SqlCeCommand command = new SqlCeCommand("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'settings'", Connection);
SqlCeDataReader reader = command.ExecuteReader();
if (!reader.Read()) //database is not built
{
command = new SqlCeCommand("CREATE TABLE settings (version INT NOT NULL, emulatorId1 INT, emulatorId2 INT, emulatorId3 INT, emulatorId4 INT, screenshotDir NVARCHAR(255), mapDir NVARCHAR(255))", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("INSERT INTO settings (version) VALUES (0)", Connection);
command.ExecuteNonQuery();
}
int version = 0;
while (version != 5)
{
try
{
command = new SqlCeCommand("SELECT version FROM settings", Connection);
reader = command.ExecuteReader();
reader.Read();
version = (int)reader["version"];
reader.Dispose();
switch (version)
{
case 0:
command = new SqlCeCommand("CREATE TABLE accounts (id INTEGER PRIMARY KEY IDENTITY, name NVARCHAR(20) NOT NULL, username NVARCHAR(30) NOT NULL, email NVARCHAR(60) NOT NULL, password NVARCHAR(30) NOT NULL, priority INT DEFAULT 0, foodNegativeAmt INT DEFAULT 0, lastLogin DATETIME DEFAULT 0, lastLogout DATETIME DEFAULT 0)", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("CREATE TABLE log (id INTEGER PRIMARY KEY IDENTITY, type INT NOT NULL, description NVARCHAR(100), detail NVARCHAR(255), data IMAGE, timestamp DATETIME DEFAULT GETDATE() NOT NULL)", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("CREATE TABLE schedules (id INTEGER PRIMARY KEY IDENTITY, accountId INT NOT NULL, type INT NOT NULL, interval INT NOT NULL, amount INT NOT NULL, count INT DEFAULT 1, x INT, y INT, lastAction DATETIME DEFAULT 0)", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("CREATE UNIQUE INDEX NameIdx ON accounts (name)", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("CREATE TABLE emulators (id INTEGER PRIMARY KEY IDENTITY, type INT NOT NULL, command NVARCHAR(100), windowName NVARCHAR(60), lastKnownAccountId INT NOT NULL)", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("UPDATE settings SET version = 1, emulatorId1 = 0, emulatorId2 = 0, emulatorId3 = 0, emulatorId4 = 0, screenshotDir = 'output\\ss', mapDir = 'output\\map'", Connection);
command.ExecuteNonQuery();
version = 1;
break;
case 1:
command = new SqlCeCommand("ALTER TABLE schedules ADD backupX INTEGER DEFAULT 0, backupY INTEGER DEFAULT 0", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("UPDATE settings SET version = 2", Connection);
command.ExecuteNonQuery();
version = 2;
break;
case 2:
command = new SqlCeCommand("ALTER TABLE settings ADD slackURL NVARCHAR(100) NOT NULL DEFAULT '', pushoverAPIKey NVARCHAR(30) NOT NULL DEFAULT '', pushoverUserKey NVARCHAR(30) NOT NULL DEFAULT ''", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("UPDATE settings SET version = 3", Connection);
command.ExecuteNonQuery();
version = 3;
break;
case 3:
command = new SqlCeCommand("CREATE TABLE apps (id INTEGER PRIMARY KEY IDENTITY, name NVARCHAR(30), shortName NVARCHAR(6))", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("INSERT INTO apps (name, shortName) VALUES ('Mobile Strike', 'MS')", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("INSERT INTO apps (name, shortName) VALUES ('Final Fantasy XV', 'FFXV')", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("ALTER TABLE schedules ADD appId INTEGER NOT NULL DEFAULT 0", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("UPDATE schedules SET appId = 1", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("DROP INDEX accounts.NameIdx", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("ALTER TABLE accounts ADD appId INTEGER NOT NULL DEFAULT 0", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("UPDATE accounts SET appId = 1", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("CREATE UNIQUE INDEX AppAccIdx ON accounts (appId, name)", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("ALTER TABLE emulators ADD appId INTEGER NOT NULL DEFAULT 0", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("UPDATE emulators SET appId = 1", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("UPDATE settings SET version = 4", Connection);
command.ExecuteNonQuery();
version = 4;
break;
case 4:
command = new SqlCeCommand("ALTER TABLE accounts ADD pinCode INTEGER NOT NULL DEFAULT 0", Connection);
command.ExecuteNonQuery();
command = new SqlCeCommand("UPDATE settings SET version = 5", Connection);
command.ExecuteNonQuery();
version = 5;
break;
}
}
catch (SqlCeException e)
{
/*if (e.ErrorCode == SQLiteErrorCode.Constraint_Check)
* {
* //name already in use
* }*/
version = 99;
}
command.Dispose();
}
}
private void FindIndex()
{
kodbuf = kod_t.Text;
int wagaflag = 0;
string czywag = kodbuf.Substring(0, 2);
string waga = "";
string kodwag = "";
string kodwag2 = "";
if (czywag == "27" || czywag == "28" || czywag == "29")
{
if (kodbuf.Length == 13)
{
waga = kodbuf.Substring(kodbuf.Length - 6, 5);
kodwag = kodbuf.Substring(0, 6);
kodwag2 = kodbuf.Substring(2, 4);
wagaflag = 1;
}
}
//int rowqty = 0;
kod_t.Text = "SZUKAM TOWARU W BAZIE";
kod_t.Refresh();
//SqlCeCommand cmd2 = cn.CreateCommand();
//cmd2.CommandText = "SELECT kod, COUNT(nazwa) FROM dane WHERE kod = ? GROUP BY kod";
//cmd2.Parameters.Add("@k", SqlDbType.NVarChar, 20);
//cmd2.Parameters["@k"].Value = kodbuf;
//cmd2.Prepare();
//SqlCeDataReader dr1 = cmd2.ExecuteReader();
//while (dr1.Read())
//{
// rowqty = dr1.GetInt32(1);
//}
//if (rowqty > 0)
//{
if (wagaflag == 0)
{
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT kod, nazwa, stan, cenazk, cenasp, vat FROM dane WHERE kod = ?";
cmd.Parameters.Add("@k", SqlDbType.NVarChar, 20);
cmd.Parameters["@k"].Value = kodbuf;
cmd.Prepare();
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
nazwa_t.Text = dr.GetString(1);
stan_t.Text = dr.GetString(2);
cena_t.Text = dr.GetString(3);
cenasp_t.Text = dr.GetString(4);
vat_t.Text = dr.GetString(5);
}
cmd.Dispose();
dr.Dispose();
// string zliczono = "0";
// cmd = cn.CreateCommand();
// cmd.CommandText = "SELECT kod, dokid, ilosc FROM bufor WHERE kod = ? and dokid = ?";
// cmd.Parameters.Add("@k", SqlDbType.NVarChar, 15);
// cmd.Parameters.Add("@d", SqlDbType.Int, 10);
// cmd.Parameters["@k"].Value = kodbuf;
// cmd.Parameters["@d"].Value = int.Parse(index);
// cmd.Prepare();
// dr = cmd.ExecuteReader();
// while (dr.Read())
// {
// zliczono = ((decimal.Parse(zliczono) + dr.GetSqlDecimal(2)).ToString());;
// }
// zliczono_t.Text = zliczono;
kod_t.Text = kodbuf;
ilosc_t.Text = "1";
ilosc_t.Focus();
ilosc_t.SelectAll();
}
else if (wagaflag == 1)
{
string like = "kod LIKE '" + kodwag + ".......'";
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT kod, nazwa, stan, cenazk, cenasp, vat FROM dane WHERE " + like;
cmd.Prepare();
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
nazwa_t.Text = dr.GetString(1);
stan_t.Text = dr.GetString(2);
cena_t.Text = dr.GetString(3);
cenasp_t.Text = dr.GetString(4);
vat_t.Text = dr.GetString(5);
ilosc_t.Text = (int.Parse(waga.Substring(0, 2))).ToString() + "." + waga.Substring(2, 3);
}
cmd.Dispose();
dr.Dispose();
string zliczono = "0";
cmd = cn.CreateCommand();
cmd.CommandText = "SELECT kod, dokid, ilosc FROM bufor WHERE dokid = ? and " + like;
cmd.Parameters.Add("@d", SqlDbType.Int, 10);
cmd.Parameters["@d"].Value = int.Parse(index);
cmd.Prepare();
dr = cmd.ExecuteReader();
while (dr.Read())
{
zliczono = ((decimal.Parse(zliczono) + dr.GetSqlDecimal(2)).ToString());;
}
zliczono_t.Text = zliczono;
kod_t.Text = kodwag;
ilosc_t.Focus();
ilosc_t.SelectAll();
}
if (nazwa_t.Text == null || nazwa_t.Text == "")
{
DialogResult dialog = MessageBox.Show("Nie znaleziono kodu towaru czy? dodaæ - Tak, dodaæ bez nazwy - Anuluj, Nie dodawaæ - Nie", "Brak towaru", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
if (dialog == DialogResult.Yes)
{
kod_t.Text = kodbuf;
nazwa_t.ReadOnly = false;
cena_t.ReadOnly = false;
nazwa_t.Focus();
cena_t.Text = "0";
cenasp_t.Text = "0";
stan_t.Text = "0";
vat_t.Text = "0";
if (wagaflag == 1)
{
ilosc_t.Text = waga.Substring(0, 2) + "." + waga.Substring(2, 3);
}
}
else if (dialog == DialogResult.No)
{
kod_t.Text = null;
kod_t.Focus();
}
else if (dialog == DialogResult.Cancel)
{
nazwa_t.ReadOnly = true;
cena_t.ReadOnly = true;
kod_t.Text = kodbuf;
if (wagaflag == 1)
{
ilosc_t.Text = waga.Substring(0, 2) + "." + waga.Substring(2, 3);
}
ilosc_t.Text = "1";
ilosc_t.Focus();
ilosc_t.SelectAll();
cena_t.Text = "0";
cenasp_t.Text = "0";
stan_t.Text = "0";
vat_t.Text = "0";
}
}
}
private void FindIndex()
{
string kodbuf = kod_t.Text;
int wagaflag = 0;
string czywag = kodbuf.Substring(0, 2);
string waga = "";
string kodwag = "";
string kodwag2 = "";
if (czywag == "27" || czywag == "28" || czywag == "29")
{
if (kodbuf.Length == 13)
{
waga = kodbuf.Substring(kodbuf.Length - 6, 5);
kodwag = kodbuf.Substring(0, 6);
kodwag2 = kodbuf.Substring(2, 4);
wagaflag = 1;
}
}
//int rowqty = 0;
kod_t.Text = "SZUKAM TOWARU W BAZIE";
kod_t.Refresh();
//SqlCeCommand cmd2 = cn.CreateCommand();
//cmd2.CommandText = "SELECT kod, COUNT(nazwa) FROM dane WHERE kod = ? GROUP BY kod";
//cmd2.Parameters.Add("@k", SqlDbType.NVarChar, 20);
//cmd2.Parameters["@k"].Value = kodbuf;
//cmd2.Prepare();
//SqlCeDataReader dr1 = cmd2.ExecuteReader();
//while (dr1.Read())
//{
// rowqty = dr1.GetInt32(1);
//}
//if (rowqty > 0)
//{
if (wagaflag == 0)
{
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT kod, nazwa, stan, cenazk, cenasp, vat, bad_cena, bad_stan, zliczono, cenapolka FROM dane WHERE kod = ?";
cmd.Parameters.Add("@k", SqlDbType.NVarChar, 20);
cmd.Parameters["@k"].Value = kodbuf;
cmd.Prepare();
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
nazwa_t.Text = dr.GetString(1);
stan_t.Text = dr.GetString(2);
cenazk_t.Text = dr.GetString(3);
cenasp_t.Text = dr.GetString(4);
vat_t.Text = dr.GetString(5);
bad_cena_c.Checked = dr.GetBoolean(6);
bad_stan_c.Checked = dr.GetBoolean(7);
ilosc_t.Text = Convert.ToString(dr.GetSqlDecimal(8));
cena_t.Text = Convert.ToString(dr.GetSqlDecimal(9));
}
cmd.Dispose();
dr.Dispose();
kod_t.Text = kodbuf;
// ilosc_t.Focus();
}
else if (wagaflag == 1)
{
string like = "kod LIKE '" + kodwag + ".......'";
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT kod, nazwa, stan, cenazk, cenasp, vat, bad_cena, bad_stan, zliczono, cenapolka FROM dane WHERE " + like;
cmd.Prepare();
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
nazwa_t.Text = dr.GetString(1);
stan_t.Text = dr.GetString(2);
cena_t.Text = dr.GetString(3);
cenasp_t.Text = dr.GetString(4);
vat_t.Text = dr.GetString(5);
bad_cena_c.Checked = dr.GetBoolean(6);
bad_stan_c.Checked = dr.GetBoolean(7);
ilosc_t.Text = Convert.ToString(dr.GetSqlDecimal(8));
cena_t.Text = Convert.ToString(dr.GetSqlDecimal(9));
}
cmd.Dispose();
dr.Dispose();
kod_t.Text = kodwag;
// ilosc_t.Focus();
}
if (nazwa_t.Text == null || nazwa_t.Text == "")
{
DialogResult dialog = MessageBox.Show("Nie znaleziono kodu towaru");
kod_t.Focus();
}
}
private bool SaveDetailTbl()
{
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = App.con;
string sql = "insert into " + _dtablename + "(";
for (int i = 0; i < _loopindex; i++)
{
if (_fields[i].FieldType == "Npk" || _fields[i].FieldType == "Cpk")
{
sql += _fields[i].FieldName + ",";
}
}
for (int i = _loopindex + 1; i < _fields.Length; i++)
{
sql += _fields[i].FieldName + ",";
}
sql = sql.Substring(0, sql.Length - 1);
sql += ") ";
int looplength = _fields.Length - _loopindex - 1;
for (int i = 0; i < _loopnum; i++)
{
string sqlValue = "values (";
for (int j = 0; j < _loopindex; j++)
{
switch (_fields[j].FieldType)
{
case "Npk":
sqlValue += (_content[j] == "") ? "null," : _content[j] + ",";
break;
case "Cpk":
sqlValue += "'" + _content[j] + "',";
break;
case "Dpk":
sqlValue += (_content[j] == "") ? "null," : dateString(_content[j]) + ",";
break;
default:
break;
}
}
for (int j = _loopindex + 1; j < _fields.Length; j++)
{
switch (_fields[j].FieldType)
{
case "C":
sqlValue += "'" + _content[j + i * looplength] + "',";
break;
case "N":
sqlValue += (_content[j + i * looplength] == "") ? "null," : _content[j + i * looplength] + ",";
break;
case "D":
sqlValue += (_content[j + i * looplength] == "") ? "null," : dateString(_content[j + i * looplength]) + ",";
break;
default:
break;
}
}
if (_fields.Length > _loopindex + 1)
{
sqlValue = sqlValue.Substring(0, sqlValue.Length - 1);
}
sqlValue += ")";
cmd.CommandText = sql + sqlValue;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
App.WriteLog("sql=" + cmd.CommandText);
App.WriteLog("Error:" + ex.Message);
}
}
cmd.Dispose();
return(true);
}
private bool SaveMainTbl()
{
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = App.con;
string sql = "insert into " + _mtablename + "(";
int fldCnt = (_loopindex >= 0)? _loopindex : _fields.Length;
for (int i = 0; i < fldCnt; i++)
{
if (_fields[i].FieldName != "oper")
{
sql += _fields[i].FieldName + ",";
}
}
sql = sql.Substring(0, sql.Length - 1);
sql += ") values (";
for (int j = 0; j < fldCnt; j++)
{
switch (_fields[j].FieldType)
{
case "C":
case "Cpk":
sql += "'" + _content[j] + "',";
break;
case "N":
case "Npk":
sql += (_content[j] == "") ? "null," : _content[j] + ",";
break;
case "D":
case "Dpk":
sql += (_content[j] == "") ? "null," : dateString(_content[j]) + ",";
break;
default:
break;
}
}
if (_fields.Length > 0)
{
sql = sql.Substring(0, sql.Length - 1);
}
sql += ")";
cmd.CommandText = sql;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
App.WriteLog("sql=" + sql);
App.WriteLog("Error:" + ex.Message);
}
cmd.Dispose();
return(true);
}
//=====================METODO CRIAR A BASE DE DADOS===========================
public void CriarBaseDados(string base_dados, List <string> instrucoes, bool verificar_ficheiro = false)//criado exclusivamente para criar uma base de dados nova 20º
{
//============================================================================
#region Verificação da existencia de ficheiro da base de dados 21º
if (verificar_ficheiro)//21º
{
//executa a verificação se for true
//se for false vai pular esse código
//base_dados = tem o nome com o caminho completo até a database !!
if (File.Exists(base_dados))
{
if (MessageBox.Show("Existe uma base de dados com o mesmo nome." + //texto que vai aparecer na mensagem
Environment.NewLine + // paragrafo
"Deseja subistuir a base de dados existente ?", //texto que vai aparecer na mensagem
"ATENÇÃO", //titulo da box
MessageBoxButtons.YesNo, //botao de sim ou nao
MessageBoxIcon.Warning) == DialogResult.No) //icone de cuidado e se o resultado for NAO
{
return; // nao retorna qualquer valor e permanece na apicação
}
else
{
//se o usuario apertar Sim, apaga o ficheiro existente e cria um novo 29º
try
{
File.Delete(base_dados);
}
catch
{
MessageBox.Show("Erro, é necessario fechar os processos que estão abertos."); //se o programa tive aberto não tem como deletar o arquivo
return;
}
}
}
}
#endregion
//Construção da stringconexao 23º
#region Construção da stringconexao (Data Source) 23º
StringBuilder str = new StringBuilder();
str.Append("Data source = ");
#endregion
//nome da base de dados 24º
#region nome da base de dados (base_dados)25º
str.Append(base_dados);
#endregion
//verifica se tem password 25º
#region verifica se tem password (bd_password) 26º
if (bd_password != "")
{
str.Append("; Password = "******"C:\source\repos\loja.sdf", true);
//MessageBox.Show("Base de dados criada");
#endregion
//============================================================================
//criação das tabelas dentro da base de dados 30º
#region criação das tabelas dentro da base de dados 30º
//======================EXEMPLO================================================
//1º CREATE TABLE e o nome da tabela
//2º id_contato int not null primary key
//3º nome nvarchar(50)
//4º telefone nvarchar(50)
//5º data datetime
//6º etc...
//END -> ao colocar END ele faz o seguinte quando ele termina de ler a tabela acima /\
//ao chegar no END ele finaliza a tabela e vai para a proxima abaixo \/
//então da para criar duas tabelas ou mais seguidas
//exemplo tabelas de clientes
//end
//tabelas de encomendas ou compras
//end
//etc...
//1º CREATE TABLE e o nome da tabela
//2º id_contato int not null primary key
//3º nome nvarchar(50)
//4º telefone nvarchar(50)
//5º data datetime
//6º etc...
//============================================================================
conectar = new SqlCeConnection(str.ToString()); //comando para ligar a base de dados
conectar.Open();
comando = new SqlCeCommand(); //comando para injetar instruções a base de dados
comando.Connection = conectar;
//instruções (repare que eu coloquei aqui list<string> instrucoes "public void CriarBaseDados(string base_dados, "List<string> instrucoes" ,bool verificar_ficheiro = false)
//instrucoes que eu pretendo ser executadas para criar a tabela
str = null; // null porque criar sempre um novo conjunto de instruções
foreach (string item in instrucoes)
{
if (item.StartsWith("CREATE TABLE"))//startWith = começãr por "create table"
{
//inicia a construção da querry
str = new StringBuilder();// se começa com CREATE TABLE ele coloca o "item"
str.Append(item);
}
else if (item == "END") //END para terminar a tabela acima e executar o código abaixo \/
{
//fechar a criação da query e executa-la
comando.CommandText = str.ToString();
comando.ExecuteNonQuery();//aperte f9 aqui para testar se a base de dados ta correta
}
else//adiciona tudo que esta após ao "CREATE TABLE" ou seja as instruçõse "id_Contato, nome etc.. até chegar no END.
{
//adicionar instrução ao stringbuilder
str.Append(item);
}
}
//fecha o comando e a ligação
comando.Dispose();
conectar.Dispose();
#endregion
}
//static DataModelTestDataContext dm = new DataModelTestDataContext();
public static ObservableCollection <ReporteBean> ListarReportesPaciente()
{
#region linq to class
//ObservableCollection<ReporteBean> ocltnReporteBeans = new ObservableCollection<ReporteBean>();
//try
//{
// //RemoteModelDataContext dm = new RemoteModelDataContext();
// var collection = dm.SP_ListarReportesPacientes();
// foreach (var item in collection)
// {
// ReporteBean reporteBean = new ReporteBean();
// reporteBean.iCodigoDetalleReporte = (int)item.iCodigoDetalleReporte;
// reporteBean.iCodigoPaciente = (int)item.iCodigoPaciente;
// reporteBean.iCodigoReporte = item.iCodigoReporte;
// reporteBean.strApellidosPaciente = (string)item.vApellidosPaciente;
// reporteBean.strFecReportePaciente = (Convert.ToDateTime((DateTime)item.dtFecReportePaciente)).Date.ToString();
// reporteBean.iCodigoTipoReporte = (int)item.iCodigoTipoReporte;
// reporteBean.strNombreTipoReporte = (string)item.vNombreTipoReporte;
// reporteBean.strNombresPaciente = (string)item.vNombresPaciente;
// ReporteDetalle rd = ObtenerResultadosCodigos((int)item.iCodigoDetalleReporte);
// Resultado resultadoUno = ResultadoDL.ObtenerResultadoCodigo((int)rd.iCodigoResultadoUno);
// Resultado resultadoDos = ResultadoDL.ObtenerResultadoCodigo((int)rd.iCodigoResultadoDos);
// reporteBean.strListaAngulosUno = resultadoUno.vListaAngulos.ToString();
// reporteBean.strListaAngulosDos = resultadoDos.vListaAngulos.ToString();
// reporteBean.strUnidadPaciente = "Rodilla";
// int iCodigoResultadoUno = (int)ObtenerResultadosCodigos((int)item.iCodigoDetalleReporte).iCodigoResultadoUno;
// int iCodigoLateralidad = (int)(ResultadoDL.ObtenerResultadoCodigo(iCodigoResultadoUno).iCodigoLateralidad);
// Lateralidad lat = dm.Lateralidads.Where(l => l.iCodigoLateralidad == iCodigoLateralidad).First();
// reporteBean.strLateralidadPaciente = lat.vNombre;
// ocltnReporteBeans.Add(reporteBean);
// }
//}
//catch (Exception)
//{
// return null;
//}
//return ocltnReporteBeans;
#endregion
#region sql compact edition
ObservableCollection <ReporteBean> ocltnReporteBeans = new ObservableCollection <ReporteBean>();
try
{
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
SqlCeDataReader rdr = null;
conn = new SqlCeConnection("Data Source=" + System.IO.Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "HRNPCIData.sdf"));
conn.Open();
cmd = new SqlCeCommand("SELECT r.iCodigoReporte, r.iCodigoPaciente, r.iCodigoDetalleReporte , tr.iCodigoTipoReporte , tr.vNombreTipoReporte, r.dtFecReportePaciente, p.vNombresPaciente,p.vApellidosPaciente " +
"FROM Reporte r " +
"JOIN Paciente p " +
"ON r.iCodigoPaciente = p.iCodigoPaciente " +
"JOIN TipoReporte tr " +
"on r.iCodigoTipoReporte = tr.iCodigoTipoReporte " +
"ORDER by r.dtFecReportePaciente DESC", conn);
rdr = cmd.ExecuteReader();
int i = 0;
while (rdr.Read())
{
ReporteBean reporteBean = new ReporteBean();
if (!DBNull.Value.Equals(rdr[0]))
{
reporteBean.iCodigoReporte = (int)rdr.GetInt64(0);
}
if (!DBNull.Value.Equals(rdr[1]))
{
reporteBean.iCodigoPaciente = rdr.GetInt32(1);
}
if (!DBNull.Value.Equals(rdr[2]))
{
reporteBean.iCodigoDetalleReporte = (int)rdr.GetInt64(2);
}
if (!DBNull.Value.Equals(rdr[3]))
{
reporteBean.iCodigoTipoReporte = rdr.GetInt32(3);
}
if (!DBNull.Value.Equals(rdr[4]))
{
reporteBean.strNombreTipoReporte = rdr.GetString(4);
}
if (!DBNull.Value.Equals(rdr[5]))
{
reporteBean.strFecReportePaciente = rdr.GetDateTime(5).ToString();
}
if (!DBNull.Value.Equals(rdr[6]))
{
reporteBean.strNombresPaciente = rdr.GetString(6);
}
if (!DBNull.Value.Equals(rdr[7]))
{
reporteBean.strApellidosPaciente = rdr.GetString(7);
}
ReporteDetalleB rd = ObtenerResultadosCodigosDetalle(reporteBean.iCodigoTipoReporte);
ResultadoB resultadoUno = ResultadoDL.ObtenerResultadoCodigo((int)rd.iCodigoResultadoUno);
ResultadoB resultadoDos = ResultadoDL.ObtenerResultadoCodigo((int)rd.iCodigoResultadoDos);
//add the two dates from each resultados 1 y 2
reporteBean.strFecResultadoUno = (resultadoUno.dtFecAnalisisPaciente).ToString();
reporteBean.strFecResultadoDos = (resultadoDos.dtFecAnalisisPaciente).ToString();
reporteBean.strListaAngulosUno = resultadoUno.vListaAngulos.ToString();
reporteBean.strListaAngulosDos = resultadoDos.vListaAngulos.ToString();
reporteBean.strUnidadPaciente = "Rodilla";
int iCodigoResultadoUno = (int)ObtenerResultadosCodigosDetalle(reporteBean.iCodigoDetalleReporte).iCodigoResultadoUno;
int iCodigoLateralidad = (int)(ResultadoDL.ObtenerResultadoCodigo(iCodigoResultadoUno).iCodigoLateralidad);
LateralidadB lat = FisioterapeutaDL.ObtenerListaLateralidades().Where(l => l.iCodigoLateralidad == iCodigoLateralidad).First();
reporteBean.strLateralidadPaciente = lat.vNombre;
ocltnReporteBeans.Add(reporteBean);
}
rdr.Close();
cmd.Dispose();
}
catch (Exception)
{
return(null);
}
return(ocltnReporteBeans);
#endregion
}
public SymbolInfo BrowseSymbol()
{
Cursor.Current = Cursors.WaitCursor;
try
{
this.tableAdapter.CategoryCommand.Connection.Open();
SqlCeCommand cmd = this.tableAdapter.CategoryCommand.Connection.CreateCommand();
cmd.CommandText = "SELECT count(*) from Category";
this.categoryForm.TotalSymbols = (int)cmd.ExecuteScalar();
cmd.Dispose();
this.categoryForm.ResultSet = this.tableAdapter.CategoryCommand.ExecuteResultSet(ResultSetOptions.Insensitive | ResultSetOptions.Scrollable);
Cursor.Current = Cursors.Default;
if (this.categoryForm.ShowDialog() == DialogResult.OK && this.categoryForm.SelectedSymbol != null)
{
Cursor.Current = Cursors.WaitCursor;
if (this.categoryForm.ResultSet != null)
{
this.categoryForm.ResultSet.Close();
this.categoryForm.ResultSet = null;
}
this.tableAdapter.CategoryCommand.Connection.Close();
this.tableAdapter.SymbolCommand.Parameters[1].Value = this.categoryForm.SelectedSymbol.Id;
this.tableAdapter.SymbolCommand.Connection.Open();
SqlCeCommand cmd2 = this.tableAdapter.SymbolCommand.Connection.CreateCommand();
cmd2.CommandText = "SELECT count(*) from Symbol WHERE CategoryId=" + this.categoryForm.SelectedSymbol.Id;
this.symbolForm.TotalSymbols = (int)cmd2.ExecuteScalar();
cmd2.Dispose();
this.symbolForm.ResultSet = this.tableAdapter.SymbolCommand.ExecuteResultSet(ResultSetOptions.Insensitive | ResultSetOptions.Scrollable);
this.symbolForm.ShowTextOnButtons = this.categoryForm.ShowTextOnButtons;
Cursor.Current = Cursors.Default;
if (this.symbolForm.ShowDialog() == DialogResult.OK)
{
this.symbolForm.SelectedSymbol.Category = this.selectedCategory;
return(this.symbolForm.SelectedSymbol);
}
}
}
catch (Exception e)
{
Debug.WriteLine(e);
}
finally
{
if (this.categoryForm.ResultSet != null)
{
this.categoryForm.ResultSet.Close();
this.categoryForm.ResultSet = null;
}
this.tableAdapter.CategoryCommand.Connection.Close();
if (this.symbolForm.ResultSet != null)
{
this.symbolForm.ResultSet.Close();
this.symbolForm.ResultSet = null;
}
this.tableAdapter.SymbolCommand.Connection.Close();
if (Cursor.Current != Cursors.Default)
{
Cursor.Current = Cursors.Default;
}
}
return(null);
}
//Corregir
public static ObservableCollection <ReporteBean> BusquedaReportes(int iCodigoPaciente, int iCodigoReporte, DateTime dtFecReportePacienteLimInf, DateTime dtFecReportePacienteLimSup)
{
#region linq to class
//ObservableCollection<ReporteBean> ocltnReporteBeans = new ObservableCollection<ReporteBean>();
//try
//{
// //RemoteModelDataContext dm = new RemoteModelDataContext();
// var collection = dm.SP_BuscarReportes((iCodigoPaciente == -1) ? (int?)null : iCodigoPaciente,
// (iCodigoReporte == -1) ? (int?)null :iCodigoReporte ,
// (dtFecReportePacienteLimInf.ToString().Equals("01/01/0001 12:00:00 a.m.")) ? (DateTime?)null : dtFecReportePacienteLimInf.Date,
// (dtFecReportePacienteLimSup.ToString().Equals("01/01/0001 12:00:00 a.m.")) ? (DateTime?)null : dtFecReportePacienteLimSup.Date);
// foreach (var item in collection)
// {
// ReporteBean reporteBean = new ReporteBean();
// reporteBean.iCodigoDetalleReporte = (int)item.iCodigoDetalleReporte;
// reporteBean.iCodigoPaciente = (int)item.iCodigoPaciente;
// reporteBean.iCodigoReporte = item.iCodigoReporte;
// reporteBean.strApellidosPaciente = (string)item.vApellidosPaciente;
// reporteBean.strFecReportePaciente = (Convert.ToDateTime((DateTime)item.dtFecReportePaciente)).Date.ToString();
// reporteBean.iCodigoTipoReporte = (int)item.iCodigoTipoReporte;
// reporteBean.strNombreTipoReporte = (string)item.vNombreTipoReporte;
// reporteBean.strNombresPaciente = (string)item.vNombresPaciente;
// ReporteDetalleB rd = ObtenerResultadosCodigosDetalle((int)item.iCodigoDetalleReporte);
// ResultadoB resultadoUno = ResultadoDL.ObtenerResultadoCodigo((int)rd.iCodigoResultadoUno);
// ResultadoB resultadoDos = ResultadoDL.ObtenerResultadoCodigo((int)rd.iCodigoResultadoDos);
// reporteBean.strListaAngulosUno = resultadoUno.vListaAngulos.ToString();
// reporteBean.strListaAngulosDos = resultadoDos.vListaAngulos.ToString();
// reporteBean.strUnidadPaciente = "Rodilla";
// int iCodigoResultadoUno = (int)ObtenerResultadosCodigosDetalle((int)item.iCodigoDetalleReporte).iCodigoResultadoUno;
// int iCodigoLateralidad = (int)(ResultadoDL.ObtenerResultadoCodigo(iCodigoResultadoUno).iCodigoLateralidad);
// Lateralidad lat = dm.Lateralidads.Where(l => l.iCodigoLateralidad == iCodigoLateralidad).First();
// reporteBean.strLateralidadPaciente = lat.vNombre;
// ocltnReporteBeans.Add(reporteBean);
// }
//}
//catch (Exception)
//{
// return null;
//}
//return ocltnReporteBeans;
#endregion
//to Fix it
#region SQL compact connection
ObservableCollection <ReporteBean> result = new ObservableCollection <ReporteBean>();
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
SqlCeDataReader rdr = null;
try
{
conn = new SqlCeConnection("Data Source=" + System.IO.Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "HRNPCIData.sdf"));
conn.Open();
cmd = new SqlCeCommand("SELECT r.iCodigoReporte, r.iCodigoPaciente, r.iCodigoDetalleReporte, tr.iCodigoTipoReporte, tr.vNombreTipoReporte, r.dtFecReportePaciente, p.vNombresPaciente, p.vApellidosPaciente FROM Reporte r JOIN Paciente p ON r.iCodigoPaciente = p.iCodigoPaciente JOIN TipoReporte tr ON r.iCodigoTipoReporte = tr.iCodigoTipoReporte WHERE (r.iCodigoPaciente = @iCodigoPaciente ) AND (r.iCodigoTipoReporte = @iCodigoTipoReporte ) AND (r.dtFecReportePaciente >= @dtLimitInf ) AND (r.dtFecReportePaciente <= @dtLimitSup ) ORDER by r.dtFecReportePaciente DESC", conn);
cmd.Parameters.AddWithValue("@iCodigoPaciente", (iCodigoPaciente == -1) ? (Int32?)null : iCodigoPaciente);
cmd.Parameters.AddWithValue("@iCodigoTipoReporte", (iCodigoReporte == -1) ? (Int32?)null : iCodigoReporte);
cmd.Parameters.AddWithValue("@dtLimitInf", (dtFecReportePacienteLimInf.ToString().Equals("01/01/0001 12:00:00 a.m.")) ? (DateTime?)null : dtFecReportePacienteLimInf.Date);
cmd.Parameters.AddWithValue("@dtLimitSup", (dtFecReportePacienteLimSup.ToString().Equals("01/01/0001 12:00:00 a.m.")) ? (DateTime?)null : dtFecReportePacienteLimSup.Date);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ReporteBean f = new ReporteBean();
if (!DBNull.Value.Equals(rdr[0]))
{
f.iCodigoReporte = (int)(rdr.GetInt64(0));
}
if (!DBNull.Value.Equals(rdr[1]))
{
f.iCodigoPaciente = rdr.GetInt32(1);
}
if (!DBNull.Value.Equals(rdr[2]))
{
f.iCodigoDetalleReporte = (int)rdr.GetInt64(2);
}
if (!DBNull.Value.Equals(rdr[3]))
{
f.iCodigoTipoReporte = rdr.GetInt32(3);
}
if (!DBNull.Value.Equals(rdr[4]))
{
f.strNombreTipoReporte = rdr.GetString(4);
}
if (!DBNull.Value.Equals(rdr[5]))
{
f.strFecReportePaciente = rdr.GetDateTime(5).ToString();
}
if (!DBNull.Value.Equals(rdr[6]))
{
f.strNombresPaciente = rdr.GetString(6);
}
if (!DBNull.Value.Equals(rdr[7]))
{
f.strApellidosPaciente = rdr.GetString(7);
}
result.Add(f);
}
rdr.Close();
cmd.Dispose();
}
catch (Exception)
{
return(result);
}
finally
{
conn.Close();
}
return(result);
#endregion
}
private static void sqlCommandTest()
{
SqlCeCommand test = new SqlCeCommand();
test.Dispose();
}
private void cb_Click(object sender, EventArgs e)
{
columns = "";
if (cbx1.Text.ToLower() == "internal data-base")
{
try
{
SqlCeConnection conn2 = new SqlCeConnection(Properties.Settings.Default.UCBConnectionString);
SqlCeCommand cmd2 = conn2.CreateCommand();
foreach (ListViewItem itm in listView1.Items)
{
columns = columns + "[" + itm.SubItems[1].Text + "]";
if (itm.SubItems[2].Text == "Numerical" && itm.SubItems[1].Text != "Serial Number")
{
columns = columns + " int";
}
else if (itm.SubItems[2].Text == "AplhaNumerical Values")
{
columns = columns + " nvarchar";
}
else if (itm.SubItems[2].Text == "Money Values")
{
columns = columns + " money";
}
else if (itm.SubItems[2].Text == "Date and Time Values")
{
columns = columns + " datetime";
}
if (itm.SubItems[1].Text == "Serial Number")
{
columns = columns + " int NOT NULL PRIMARY KEY, CONSTRAINT [sex] UNIQUE ([Serial Number]), ";
}
else
{
if (itm.Index != listView1.Items.Count - 1)
{
columns = columns + ", ";
}
}
}
cmd2.CommandText = "CREATE TABLE [" + tbx1nme.Text + "] (" + columns + ")";
conn2.Open();
cmd2.ExecuteNonQuery();
cmd2.Dispose();
conn2.Close();
}
catch (Exception erty) { Am_err ner = new Am_err(); ner.tx("An Error Occured While Your Custom Book Was In Creation. '" + erty.Message + "'."); }
}
else
{
connxt = "DataSource='" + cbx1.Text + "'";
if (checkBox2.Checked == true)
{
}
else
{
connxt = connxt + "; Password='******'";
}
try
{
SqlCeConnection conn = new SqlCeConnection(connxt);
SqlCeCommand cmd = conn.CreateCommand();
foreach (ListViewItem itm in listView1.Items)
{
columns = columns + "[" + itm.SubItems[1].Text + "]";
if (itm.SubItems[2].Text == "Numerical Values" && itm.SubItems[1].Text != "Serial Number")
{
columns = columns + " int";
}
else if (itm.SubItems[2].Text == "AlphaNumerical Values")
{
columns = columns + " nvarchar(4000)";
}
else if (itm.SubItems[2].Text == "Money Values")
{
columns = columns + " money";
}
else if (itm.SubItems[2].Text == "Date and Time Values")
{
columns = columns + " datetime";
}
if (itm.SubItems[1].Text == "Serial Number")
{
columns = columns + " int NOT NULL PRIMARY KEY, CONSTRAINT [sex] UNIQUE([Serial Number]),";
}
else
{
columns = columns + " NULL";
if (itm.Index != listView1.Items.Count - 1)
{
columns = columns + ", ";
}
}
}
cmd.CommandText = "CREATE TABLE [" + tbx1nme.Text + "](" + columns + ")";
conn.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
try
{
SqlCeConnection conn_tb = new SqlCeConnection(Properties.Settings.Default.Amdtbse_2ConnectionString);
SqlCeCommand cmd_tb = conn_tb.CreateCommand();
conn_tb.Open();
if (checkBox1.Checked == false)
{
cmd_tb.CommandText = "INSERT [DB_PATH_LOCAL] VALUES ('" + tbx2.Text + "', '" + cbx1.Text + "', '')";
}
else
{
cmd_tb.CommandText = "INSERT [DB_PATH_LOCAL] VALUES ('NOT AVAILABLE', '" + cbx1.Text + "', '')";
}
cmd_tb.ExecuteNonQuery();
conn_tb.Close();
}
catch (Exception ertyyy) { /*tabPage2.Select(); Am_err ner = new Am_err(); ner.tx(ertyyy.Message/*"The Name you have Entered already Exists.");*/ }
}
catch (Exception erty) { /*Am_err ner = new Am_err(); ner.tx(columns + erty.Message);*/ }
}
this.Close();
}
private void btsaque_Click(object sender, EventArgs e)
{
try
{
SqlCeConnection conexao = new SqlCeConnection(@"Data Source = C:\Users\thale\Desktop\Curso C#\Databases\Banco Digital.sdf" + "; Password = '******'");
conexao.Open();
string query = "SELECT * FROM Cliente";
SqlCeDataAdapter adaptador = new SqlCeDataAdapter(query, conexao);
DataTable dados = new DataTable();
adaptador.Fill(dados);
SqlCeCommand comando = new SqlCeCommand();
comando.Connection = conexao;
foreach (DataRow linha in dados.Rows)
{
string num_conta = linha["num_conta"].ToString();
string senha = linha["senha"].ToString();
if ((num_conta == tbnum_conta.Text) && (senha == tbsenha.Text))
{
float valor = float.Parse(tbvalor.Text);
float saldo = float.Parse(linha["saldo"].ToString());
string tipo_conta = linha["tipo_conta"].ToString();
if (tipo_conta == "Especial")
{
float saldo_atual = saldo - valor;
//parametros
comando.Parameters.AddWithValue("@saldo", saldo_atual);
comando.Parameters.AddWithValue("@num_conta", num_conta);
comando.Parameters.AddWithValue("@senha", senha);
//texto da query
comando.CommandText = "UPDATE Cliente SET saldo = @saldo WHERE num_conta = @num_conta AND senha = @senha";
comando.ExecuteNonQuery();
comando.Dispose();
conexao.Dispose();
tbnum_conta.Text = "";
tbvalor.Text = "";
tbsenha.Text = "";
tbnum_conta.Focus();
MessageBox.Show("Saque realizado com Sucesso.", "Sucesso", MessageBoxButtons.OK);
}
else if (saldo >= valor)
{
float saldo_atual = saldo - valor;
//parametros
comando.Parameters.AddWithValue("@saldo", saldo_atual);
comando.Parameters.AddWithValue("@num_conta", num_conta);
comando.Parameters.AddWithValue("@senha", senha);
//texto da query
comando.CommandText = "UPDATE Cliente SET saldo = @saldo WHERE num_conta = @num_conta AND senha = @senha";
comando.ExecuteNonQuery();
comando.Dispose();
conexao.Dispose();
tbnum_conta.Text = "";
tbvalor.Text = "";
tbsenha.Text = "";
tbnum_conta.Focus();
MessageBox.Show("Saque realizado com Sucesso.", "Sucesso", MessageBoxButtons.OK);
}
else
{
MessageBox.Show("Saldo insulficiente.", "Erro", MessageBoxButtons.OK);
return;
}
}
}
}
catch
{
MessageBox.Show("Aconteceu um erro na conexão com o Banco de Dados", "Erro", MessageBoxButtons.OK);
}
}
private void addToError()
{
//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.getErrorTable()
+ "] ([ID],[Job],[ItemId],[Qty],[Unit],[CheckedDateTime],[CheckedBy],[Checked]) VALUES " +
" ('" + bm
+ "','" + jb
+ "','" + this.txtItem.Text.ToUpper()
+ "','"
+ "','"
+ "','" + DateTime.Now
+ "','" + username
+ "','false"
+ "' ) ";
myCommand.CommandType = CommandType.Text;
try
{
myCommand.ExecuteNonQuery();
myCommand.Dispose();
// MessageBox.Show("Item : " + this.txtItem.Text.ToUpper() + " check successfully!", "Successfully", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button2);
}
catch (Exception ex)
{
//MessageBox.Show(_id + " : " + _item + " : " + _name);
myCommand.Dispose();
}
myConnection.Close();
}
public static int SetIDCommand(string text)
{
comm.CommandText = text;
comm.Connection = con;
int rowID = -1;
con.Open();
SqlCeCommand getID = new SqlCeCommand("SELECT @@IDENTITY", con);
comm.ExecuteNonQuery();
rowID = int.Parse(getID.ExecuteScalar().ToString());
con.Close();
getID.Dispose();
return rowID;
}
public DataSet getData(string _item)
{
myConnection = default(SqlCeConnection);
DataSet dsNew = new DataSet();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [id],[item],[qty],[unit] FROM ["
+ storagePath.getBomTable() + "] WHERE id ='"
+ bm + "' and item='"+_item+"' ";
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(dsNew);
Adapter.Dispose();
myCommand.Dispose();
//dt = null;
myConnection.Close();
return dsNew;
}
public Boolean checkStoredata(string _item)
{
Boolean isCheck = false;
myConnection = default(SqlCeConnection);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [ID] FROM [" + storagePath.getStoreTable() + "] WHERE ID ='"
+ bm+"' and Job='"+jb+"' and ItemId = '"+_item+"' and Checked='true'" ;
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(dt);
Adapter.Dispose();
if (dt.Rows.Count > 0)
{
isCheck = true;
}
else
{
isCheck = false;
}
myCommand.Dispose();
dt = null;
myConnection.Close();
return isCheck;
}
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 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();
}
}
/// <summary>
/// Create the initial database
/// </summary>
private void CreateDB()
{
var connection = new SqlCeConnection(this.path);
try
{
var eng = new SqlCeEngine(this.path);
var cleanup = new System.Threading.Tasks.Task(eng.Dispose);
eng.CreateDatabase();
cleanup.Start();
}
catch (Exception e)
{
EventLogging.WriteError(e);
}
connection.Open();
var usersDB =
new SqlCeCommand(
"CREATE TABLE Users_DB("
+ "UserID int IDENTITY (100,1) NOT NULL UNIQUE, "
+ "UserName nvarchar(128) NOT NULL UNIQUE, "
+ "PassHash nvarchar(128) NOT NULL, "
+ "Friends varbinary(5000), "
+ "PRIMARY KEY (UserID));",
connection);
usersDB.ExecuteNonQuery();
usersDB.Dispose();
connection.Dispose();
connection.Close();
}