protected void Login_Click(object sender, EventArgs e)
{
SqlCeConnection conn = new SqlCeConnection(connString); // SqlConnection
conn.Open();
//podlozno SQL injection napadu
//SqlCeCommand command = new SqlCeCommand("SELECT * FROM student WHERE ime = '" +
// txtKorisnickoIme.Text + "' AND lozinka = '" +
// txtLozinka.Text + "'", conn);
SqlCeCommand command = new SqlCeCommand("SELECT * FROM student WHERE ime = @ime AND lozinka = @lozinka", conn);
command.Parameters.AddWithValue("ime", txtKorisnickoIme.Text);
command.Parameters.AddWithValue("lozinka", txtLozinka.Text);
SqlCeDataReader dr = command.ExecuteReader();
if (dr.Read())
{
Session["korisnickoIme"] = dr["ime"].ToString();
dr.Close();
conn.Close();
Response.Redirect("Zasticena.aspx");
}
dr.Close();
conn.Close();
}
/// <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 = "Пароль или имя не верны";
}
///////////////////
//VILLE
public bool createCity(string name)
{
//Connexion a la bdd
SqlCeConnection ConnectionBdd = new SqlCeConnection(ConnectionString);
try
{
//Ouverture de la connexion
ConnectionBdd.Open();
string query = "INSERT INTO [VILLE] (VILLE_NOM) "
+ "VALUES (" +
"'" + name +
"')";
Console.WriteLine(query);
SqlCeCommand mySqlCommand = new SqlCeCommand(query, ConnectionBdd);
mySqlCommand.ExecuteReader();
Console.WriteLine("--> Nouvelle ville créée : " + name);
ConnectionBdd.Close();
return true;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
ConnectionBdd.Close();
return false;
}
}
public bool deleteDocCheckProduct(string _DCode)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" delete from DocCheckProducts where DCode ='"+_DCode+"'");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
sb = new StringBuilder();
sb.Append(" delete from DocCheckProductDetails where DCode ='" + _DCode + "'");
string sqldelete;
sqldelete = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqldelete;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
private void btnLogin_Click(object sender, EventArgs e)
{
string spath = @"Data Source=Application Data\PDADB.sdf";
string strid = this.txtUserID.Text.Trim();
string strpwd = this.txtPwd.Text.Trim();
if (strid == "")
{
MessageBox.Show("�������û����!", "����", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return;
}
if (strpwd == "")
{
MessageBox.Show("�������û�����!", "����", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return;
}
string sql = "select usersn,pwd,flag from tuser where userid ='" + strid + "'";
try
{
conn = new SqlCeConnection(spath);
conn.Open();
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
SqlCeDataReader read = cmd.ExecuteReader();
if (read.Read())
{
string tmpsn = read[0].ToString();
string tmppwd = read[1].ToString();
string tmpflag = read[2].ToString();
conn.Close();
if (tmpflag != "0")
{
MessageBox.Show("����Ч�û�!" , "����", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return;
}
if (tmppwd != strpwd)
{
MessageBox.Show("�û����벻��ȷ!" , "����", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return;
}
CurrentUser.USERSN = tmpsn;
frmMain fm = new frmMain();
fm.ShowDialog();
}
else
{
conn.Close();
MessageBox.Show("û�ҵ��û���Ϣ!" , "����", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return;
}
}
catch (Exception ex)
{
conn.Close();
MessageBox.Show("��½�쳣!"+ ex.Message, "����", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return;
}
}
public bool Execute(string query)
{
SqlCeConnection sqlcon = new SqlCeConnection(Database.Connection);
SqlCeCommand sqlcom = new SqlCeCommand(query, sqlcon);
if (sqlcon.State != ConnectionState.Closed)
{
sqlcon.Close();
}
sqlcon.Open();
sqlcom.ExecuteNonQuery();
sqlcon.Close();
return true;
}
private static void CreateInitialDatabaseObjects(string connString)
{
using (SqlCeConnection conn = new SqlCeConnection(connString))
{
string[] queries = Regex.Split(NetworkAssetManager.Properties.Resources.DBGenerateSql, "GO");
SqlCeCommand command = new SqlCeCommand();
command.Connection = conn;
conn.Open();
foreach (string query in queries)
{
string tempQuery = string.Empty;
tempQuery = query.Replace("\r\n", "");
if (tempQuery.StartsWith("--") == true)
{
/*Comments in script so ignore*/
continue;
}
_logger.Info("Executing query: " + tempQuery);
command.CommandText = tempQuery;
try
{
command.ExecuteNonQuery();
}
catch (System.Exception e)
{
_logger.Error(e.Message);
}
}
conn.Close();
}
}
private void buttonImage1_Click(object sender, EventArgs e)
{
/*
* Verificar se existe coluna e inserir caso n exista
* garantir compatibilidade com bancos velhos
*/
//CONN
SqlCeConnection conn =
new SqlCeConnection("Data Source = " + Library.appDir + "\\db\\citeluz.sdf; Password=mfrn@0830$X-PRO;");
//coluna user
conn.Open();
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "ALTER TABLE trafo ADD [user] NVARCHAR(15) DEFAULT 'TESTE';";
try
{
command.ExecuteNonQuery();
MessageBox.Show("Tabela trafo atualizada com sucesso");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
public void Tt()
{
string connectionString = @"DataSource=db.sdf";
var conn = new SqlCeConnection(connectionString);
if(!File.Exists(conn.Database))
{
new SqlCeEngine(connectionString).CreateDatabase();
}
conn.Open();
//Creating a table
var cmdCreate = new SqlCeCommand("CREATE TABLE Products (Id int IDENTITY(1,1), Title nchar(50), PRIMARY KEY(Id))", conn);
cmdCreate.ExecuteNonQuery();
//Inserting some data...
var cmdInsert = new SqlCeCommand("INSERT INTO Products (Title) VALUES ('Some Product #1')", conn);
cmdInsert.ExecuteNonQuery();
//Making sure that our data was inserted by selecting it
var cmdSelect = new SqlCeCommand("SELECT Id, Title FROM Products", conn);
SqlCeDataReader reader = cmdSelect.ExecuteReader();
reader.Read();
Console.WriteLine("Id: {0} Title: {1}", reader["Id"], reader["Title"]);
reader.Close();
conn.Close();
}
public static User CreateUser(string username, string password)
{
SqlCeConnection con = new SqlCeConnection(CONNECTION_STRING);
try
{
con.Open();
SqlCeCommand comm = new SqlCeCommand("INSERT INTO users (username, password, salt, dateCreated) VALUES (@username, @password, @salt, @createdDate)", con);
comm.Parameters.Add(new SqlCeParameter("@username", username));
comm.Parameters.Add(new SqlCeParameter("@password", password));
comm.Parameters.Add(new SqlCeParameter("@salt", String.Empty));
comm.Parameters.Add(new SqlCeParameter("@createdDate", DateTime.UtcNow));
int numberOfRows = comm.ExecuteNonQuery();
if (numberOfRows > 0)
{
return GetUser(username);
}
}
catch (Exception ex)
{
Debug.Print("CreateUser Exception: " + ex);
}
finally
{
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
}
return null;
}
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();
}
}
public void MultiRSSqlCE()
{
if (File.Exists("Test.sdf"))
File.Delete("Test.sdf");
var cnnStr = "Data Source = Test.sdf;";
var engine = new SqlCeEngine(cnnStr);
engine.CreateDatabase();
using (var cnn = new SqlCeConnection(cnnStr))
{
cnn.Open();
cnn.Execute("create table Posts (ID int, Title nvarchar(50), Body nvarchar(50), AuthorID int)");
cnn.Execute("create table Authors (ID int, Name nvarchar(50))");
cnn.Execute("insert Posts values (1,'title','body',1)");
cnn.Execute("insert Posts values(2,'title2','body2',null)");
cnn.Execute("insert Authors values(1,'sam')");
var data = cnn.Query<PostCE, AuthorCE, PostCE>(@"select * from Posts p left join Authors a on a.ID = p.AuthorID", (post, author) => { post.Author = author; return post; }).ToList();
var firstPost = data.First();
firstPost.Title.IsEqualTo("title");
firstPost.Author.Name.IsEqualTo("sam");
data[1].Author.IsNull();
cnn.Close();
}
}
internal List<Cmd> GetDbList()
{
List<Cmd> list = new List<Cmd>();
SqlCeConnection con = new SqlCeConnection(connectionString);
try
{
con.Open();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(
"select * from cmd order by name", con);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Cmd");
DataTable table = dataSet.Tables["Cmd"];
foreach (DataRow row in table.Rows)
{
Cmd cmd = new Cmd();
cmd.name = (string)row["name"];
cmd.description = (string)row["description"];
cmd.path = (string)row["path"];
cmd.arg = (string)row["arg"];
list.Add(cmd);
}
}
finally
{
if (con != null)
{
con.Close();
}
}
return list;
}
private void btnDelete_Click(object sender, EventArgs e)
{
btnSave.Enabled = false;
grbDeviceIP.Enabled = false;
dataSourcePath = "Data Source = " + Application.StartupPath + @"\DeviceData.sdf";
SqlCeConnection sqlConnection1 = new SqlCeConnection();
sqlConnection1.ConnectionString = dataSourcePath;
SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
try
{
cmd.CommandText = "DELETE FROM DeviceData WHERE DEVICE_IP='" + Global.selMechIp + "'";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM DeviceURL WHERE DEV_IP='" + Global.selMechIp + "'";
cmd.ExecuteNonQuery();
}
catch { }
sqlConnection1.Dispose();
sqlConnection1.Close();
fnGetIpsFronTable();
btnDelete.Enabled = false;
btnEdit.Enabled = false;
txtDevIp.Text = "";
txtDevNo.Text = "";
txtDevPort.Text = "";
Application.DoEvents();
}
public ctlrptActiveCall()
{
try
{
InitializeComponent();
ConnectionString = VMuktiAPI.VMuktiInfo.MainConnectionString;
if (System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory.ToString() + "rptActiveCall.sdf"))
{
System.IO.File.Delete(AppDomain.CurrentDomain.BaseDirectory.ToString() + "rptActiveCall.sdf");
}
SqlCeEngine clientEngine = new SqlCeEngine(ClientConnectionString);
clientEngine.CreateDatabase();
LocalSQLConn = new SqlCeConnection();
LocalSQLConn.ConnectionString = ClientConnectionString;
LocalSQLConn.Open();
fncActiveCallTable();
LocalSQLConn.Close();
objRefreshReport = new delRefreshReport(fncRefreshReport);
NetPeerClient npcActiveCall = new NetPeerClient();
((NetP2PBootStrapActiveCallReportDelegates)objActiveCall).EntsvcJoinCall += new NetP2PBootStrapActiveCallReportDelegates.DelsvcJoinCall(ctlrptActiveCall_EntsvcJoinCall);
((NetP2PBootStrapActiveCallReportDelegates)objActiveCall).EntsvcGetCallInfo += new NetP2PBootStrapActiveCallReportDelegates.DelsvcGetCallInfo(ctlrptActiveCall_EntsvcGetCallInfo);
((NetP2PBootStrapActiveCallReportDelegates)objActiveCall).EntsvcActiveCalls += new NetP2PBootStrapActiveCallReportDelegates.DelsvcActiveCalls(ctlrptActiveCall_EntsvcActiveCalls);
((NetP2PBootStrapActiveCallReportDelegates)objActiveCall).EntsvcSetDuration += new NetP2PBootStrapActiveCallReportDelegates.DelsvcSetDuration(ctlrptActiveCall_EntsvcSetDuration);
((NetP2PBootStrapActiveCallReportDelegates)objActiveCall).EntsvcUnJoinCall += new NetP2PBootStrapActiveCallReportDelegates.DelsvcUnJoinCall(ctlrptActiveCall_EntsvcUnJoinCall);
channelNetTcpActiveCall = (INetP2PBootStrapReportChannel)npcActiveCall.OpenClient<INetP2PBootStrapReportChannel>("net.tcp://" + VMuktiAPI.VMuktiInfo.BootStrapIPs[0] + ":6000/NetP2PBootStrapActiveCallReport", "ActiveCallMesh", ref objActiveCall);
channelNetTcpActiveCall.svcJoinCall(VMuktiAPI.VMuktiInfo.CurrentPeer.DisplayName);
}
catch (Exception ex)
{
VMuktiAPI.VMuktiHelper.ExceptionHandler(ex, "ctlrptActiveCall", "ctlrptActiveCall.xaml.cs");
}
}
public bool registriraj(string korisnickoIme, string lozinka, string address, string email)
{
SqlCeConnection conn = new SqlCeConnection(connString);
try
{
Random r = new Random(System.DateTime.Now.Millisecond);
string salt = r.Next().ToString();
string hashiranaLoznika = Util.SHA256(lozinka);
string hashiranaSlanaLoznika = Util.SHA256(salt + hashiranaLoznika);
conn.Open();
SqlCeCommand command = new SqlCeCommand
("INSERT INTO Kori(username,password,salt,address,email) VALUES (@username,@password,@salt,@address,@email)",conn);
command.Parameters.AddWithValue("username", korisnickoIme);
command.Parameters.AddWithValue("password", hashiranaSlanaLoznika);
command.Parameters.AddWithValue("salt", salt);
command.Parameters.AddWithValue("address", address);
command.Parameters.AddWithValue("email", email);
command.ExecuteNonQuery();
conn.Close();
return true;
}
catch (Exception ex)
{
return false;
}
}
public DataBase(string connStr)
{
StopWordsTrie = new Trie();
TriesByTopic = new Dictionary<string, Trie>();
conn = new SqlCeConnection(connStr);//new SqlConnection(connStr);
globalTrie = new TextPredict.Base.Trie();
//Utils.initTrieFromString(Resources.worms , globalTrie);
//Utils.initTrieFromString(Resources.stop_words , globalTrie);
//Utils.initTrieFromString(Resources.stopWordsConversation , globalTrie);
//Utils.initTrieFromString(Resources.bicycle , globalTrie);
//Utils.initTrieFromString(Resources.science , globalTrie);
//Utils.initTrieFromString(Resources.how_to_play_fotbool , globalTrie);
//Utils.initTrieFromString(Resources.how_to_play_basketball , globalTrie);
//Utils.initTrieFromString(Resources.Cardiovascular_disease , globalTrie);
//Utils.initTrieFromString(Resources.calories , globalTrie);
try
{
if (!load())
Initialize();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
if (conn.State == ConnectionState.Open) conn.Close();
}
}
private void frmDetail_Load(object sender, EventArgs e)
{
myConnection = default(SqlCeConnection);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [ID],[Job],[ItemId],[Qty],[Unit],[CheckedDateTime],[CheckedBy],[Checked] FROM ["
+ storagePath.getStoreTable() + "] WHERE ID ='"
+ bm + "' and Job='" + jb + "' and ItemId = '" + item + "' and Checked='true'";
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(ds);
Adapter.Dispose();
if (ds.Tables[0].Rows.Count > 0)
{
//isCheck = true;
this.txtItem.Text = ds.Tables[0].Rows[0]["ItemId"].ToString();
this.txtCheckedDateTime.Text = ds.Tables[0].Rows[0]["CheckedDateTime"].ToString();
this.txtCheckedBy.Text = ds.Tables[0].Rows[0]["CheckedBy"].ToString();
}
else
{
// isCheck = false;
}
myCommand.Dispose();
dt = null;
myConnection.Close();
}
private void postavi_labele()
{
//kreiraj novu praznu konekciju
SqlCeConnection conn = new SqlCeConnection();
//dohvati tekst za povezivanje na bazu iz web.config i postavi g ana konekciju
string connStr = WebConfigurationManager.ConnectionStrings["studenti"].ConnectionString;
conn.ConnectionString = connStr;
//kreiraj novu naredbu i postavi SQL kao i konekciju
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT COUNT(*) FROM student";
cmd.CommandType = System.Data.CommandType.Text; //tip je SQL naredba (a ne tablica ili stor.proc)
//otvori komunikaciju sa bazom
conn.Open();
int brojStud = (int)cmd.ExecuteScalar(); //izvrši vrati jednu vrijednost
Label1.Text = "U bazi imamo " + brojStud.ToString() + " studenata!";
cmd.CommandText = "SELECT * FROM student";
//sada ih vrati kao datareader
SqlCeDataReader dr = cmd.ExecuteReader();
Label2.Text = "ID - Ime - Prezime" + "<br>";
// na sql Expressu ima i dr.HasRows da vidimo je li prazan if(dr.HasRows))
while (dr.Read())
{
//čitaj red po red dok ne dođeš do kraja
Label2.Text += dr["stud_id"].ToString() + " - " + dr["ime"] + " - " + dr["prezime"] + " - " + dr["faks"] + "<br>";
}
conn.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
//procitaj lozinku
string lozinka = tb_lozinka.Text;
//generiraj salt
Random r = new Random(DateTime.Now.Millisecond);
string salt = r.Next().ToString();
//Enkriptiraj lozinku
string hashLozinka = hashiraj(lozinka);
//sad dodaj još i sol na već hashiranu i sve zajedno ponovo hashiraj
string saltHashLozinka = hashiraj(salt + hashLozinka);
//ADO.NET kreiraj konekciju i pročitaj ConnString iz web.config-a
SqlCeConnection conn = new SqlCeConnection(WebConfigurationManager.ConnectionStrings["faks"].ConnectionString);
//Kreiraj SQL objekt naredbe , daj tekst naredbe i poveži sa kreiranoj konekcijom
SqlCeCommand comm = new SqlCeCommand("INSERT INTO korisnik VALUES (@kime, @lozinka, @salt)", conn);
//Proslijedi tekst SQL-a prema bazi
comm.CommandType = System.Data.CommandType.Text;
//Dodaj vrijednosti parametara
comm.Parameters.AddWithValue("@kime", tb_kime.Text);
comm.Parameters.AddWithValue("@lozinka", saltHashLozinka);
comm.Parameters.AddWithValue("@salt", salt);
try
{
//otvori konekciju i izvrši SQL koji neće vratiti ništa
conn.Open();
comm.ExecuteNonQuery();
}
finally
{
//na kraju zatvori konekciju
conn.Close();
}
}
public void RunApplication(string[] args)
{
// example command line args:
// ClearCanvas.Dicom.DataStore.SetupApplication.Application "<TrunkPath>\Dicom\DataStore\AuxiliaryFiles\empty_viewer.sdf" "<TrunkPath>\Dicom\DataStore\AuxiliaryFiles\CreateTables.clearcanvas.dicom.datastore.ddl"
string databaseFile = args[0];
string scriptFile = args[1];
File.Delete(databaseFile);
string connectionString = String.Format("Data Source=\"{0}\"", databaseFile);
SqlCeEngine engine = new SqlCeEngine(connectionString);
engine.CreateDatabase();
engine.Dispose();
StreamReader reader = new StreamReader(scriptFile);
string scriptText = reader.ReadToEnd();
reader.Close();
SqlCeConnection connection = new SqlCeConnection(connectionString);
connection.Open();
SqlCeTransaction transaction = connection.BeginTransaction();
SqlCeCommand command = new SqlCeCommand();
command.Connection = connection;
command.CommandText = scriptText;
command.ExecuteNonQuery();
transaction.Commit();
connection.Close();
}
public bool Alterar(EProduto produto)
{
SqlCeConnection cnn = new SqlCeConnection();
cnn.ConnectionString = Conexao.Caminho;
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = cnn;
#region alteracao do associado
cmd.CommandText = @"UPDATE Produto SET
IdProduto = @IdProduto,
Descricao = @Descricao,
Categoria = @Categoria,
ValorUnitario = @ValorUnitario
QtdTotal = @QtdTotal
WHERE IdProduto = @Id ";
cmd.Parameters.Add("@IdProduto", produto.IdProduto);
cmd.Parameters.Add("@Descricao", produto.Descricao);
cmd.Parameters.Add("@Categoria", produto.Categoria);
cmd.Parameters.Add("@ValorUnitario", produto.ValorUnitario);
cmd.Parameters.Add("@QtdTotal", produto.QtdTotal);
//Executa o comando setado - UPDATE
cnn.Open();
cmd.ExecuteNonQuery();
#endregion alteracao do associado
//Fecha a conexão
cnn.Close();
return true;
}
public static int GetLastOldId()
{
try
{
using (var dataContext = new SqlCeConnection(ConnectionString))
{
var query = "SELECT MAX(OldId) FROM Journal";
var result = new SqlCeCommand(query, dataContext);
dataContext.Open();
var reader = result.ExecuteReader();
int? firstResult = null;
if (reader.Read())
firstResult = (int)reader[0];
dataContext.Close();
if (firstResult != null)
return (int)firstResult;
return -1;
}
}
catch (Exception e)
{
Logger.Error(e, "Исключение при вызове DatabaseHelper.GetLastOldId");
}
return -1;
}
public bool Add(string varekode, string kategori, decimal poeng, DateTime slutt, DateTime start)
{
if (CheckDuplicate(varekode, poeng, slutt, start))
return false; // Denne varekoden finnes allerede
try
{
var con = new SqlCeConnection(FormMain.SqlConStr);
con.Open();
using (SqlCeCommand cmd = new SqlCeCommand("insert into tblVinnprodukt(Avdeling, Varekode, Kategori, Poeng, DatoOpprettet, DatoExpire, DatoStart) values (@Val1, @val2, @val3, @val4, @val5, @val6, @val7)", con))
{
cmd.Parameters.AddWithValue("@Val1", main.appConfig.Avdeling);
cmd.Parameters.AddWithValue("@Val2", varekode);
cmd.Parameters.AddWithValue("@Val3", kategori);
cmd.Parameters.AddWithValue("@Val4", poeng);
cmd.Parameters.AddWithValue("@Val5", DateTime.Now);
cmd.Parameters.AddWithValue("@Val6", slutt);
cmd.Parameters.AddWithValue("@Val7", start);
cmd.CommandType = System.Data.CommandType.Text;
cmd.ExecuteNonQuery();
}
con.Close();
con.Dispose();
return true;
}
catch (Exception ex)
{
Log.Unhandled(ex);
}
return false;
}
public static IEnumerable<string> GetFileDatabaseTables(string databaseFile, string password = null)
{
if (string.IsNullOrEmpty(databaseFile) || !File.Exists(databaseFile))
{
throw new Exception("Database not exists");
}
IList<string> tableNames = new List<string>();
SqlCeConnectionStringBuilder connectionString = new SqlCeConnectionStringBuilder();
connectionString.DataSource = databaseFile;
if (!string.IsNullOrEmpty(password))
connectionString.Password = password;
using (SqlCeConnection sqlCon = new SqlCeConnection(connectionString.ToString()))
{
string sqlStmt = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES";
using (SqlCeCommand cmd = new SqlCeCommand(commandText: sqlStmt, connection: sqlCon))
{
sqlCon.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
tableNames.Add((string)reader["table_name"]);
}
sqlCon.Close();
}
}
return tableNames;
}
private void openCheck(string _tableName,string _bom,string _job)
{
myConnection = default(SqlCeConnection);
DataTable dt = new DataTable();
Adapter = default(SqlCeDataAdapter);
myConnection = new SqlCeConnection(storagePath.getDatabasePath());
myConnection.Open();
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT [id] FROM [" + _tableName + "] WHERE id ='" + txtBom.Text + "' ";
myCommand.CommandType = CommandType.Text;
Adapter = new SqlCeDataAdapter(myCommand);
Adapter.Fill(dt);
myConnection.Close();
if (dt.Rows.Count > 0)
{
frmCheck frmCheck = new frmCheck(this.txtJob.Text.ToUpper(),this.txtBom.Text.ToUpper(),"");
// frmCheck.BindDataGrid();
frmCheck.setBom(dt.Rows[0]["id"].ToString());
frmCheck.setData(_bom, _job);
frmCheck.Show();
// frmCheck.setBom(this.txtBom.Text);
//frmCheck.setJob(this.txtJob.Text);
dt = null;
MessageBox.Show("Bom : " + dt.Rows[0]["id"].ToString());
}
else
{
MessageBox.Show("Bom : " + txtBom.Text + " does not exited");
}
}
protected void Button1_Click(object sender, EventArgs e)
{
//Hash lozinke i salt
string hashLozinka = Util.hashHash(tb_lozinka.Text);
//Sol generiraj
string salt = (new Random(DateTime.Now.Millisecond))
.Next()
.ToString();
//hashiraj sve skupa
string konacniHash = Util.hashHash(hashLozinka + salt);
//sPOJI SE NA BAZU I spremi korisnika
string connString = WebConfigurationManager.ConnectionStrings["UsersConnectionString"].ConnectionString;
SqlCeConnection connection = new SqlCeConnection(connString);
//kreiraj insert komandu
SqlCeCommand command = new SqlCeCommand();
command.Connection = connection;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "INSERT INTO korisnik VALUES(@ime, @lozinka, @salt)";
//Pročitaj lozinku i ime
command.Parameters.AddWithValue("ime", tb_kime.Text);
// No No command.Parameters.AddWithValue("lozinka", tb_lozinka.Text);
command.Parameters.AddWithValue("lozinka", konacniHash);
command.Parameters.AddWithValue("salt", salt);
connection.Open();
int brojRedova = command.ExecuteNonQuery();
connection.Close();
if (brojRedova == 1)
{
//Logiraj korisnika
Session["korisnik"] = tb_kime.Text;
Response.Redirect("LogiraniKorisnici.aspx");
} //else labela greška, itd...
}
public bool Alterar(EMovimentacaoConta movementacaoConta)
{
SqlCeConnection cnn = new SqlCeConnection();
cnn.ConnectionString = Conexao.Caminho;
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = cnn;
#region alteracao do associado
cmd.CommandText = @"UPDATE MovimentacaoConta SET
dataHoraMovimentacao = @dataHoraMovimentacao,
valortotal = @valortotal,
Id_Associado = @Id_Associado,
Id_Movimentacao = @Id_Movimentacao
ListaItens = @ListaItens
WHERE Id_Movimentacao = @Id ";
cmd.Parameters.Add("@Nome", movementacaoConta.DataHoraMovimentacao);
cmd.Parameters.Add("@valorTotal", movementacaoConta.Valortotal);
cmd.Parameters.Add("@IdMovimentacao", movementacaoConta.IdAssociado);
cmd.Parameters.Add("@IdMovimentacao", movementacaoConta.IdMovimentacao);
cmd.Parameters.Add("@Listaitens", movementacaoConta.ListaItens);
//Executa o comando setado - UPDATE
cnn.Open();
cmd.ExecuteNonQuery();
#endregion alteracao do associado
//Fecha a conexão
cnn.Close();
return true;
}
private void deleteBtn_Click(object sender, EventArgs e)
{
SqlCeConnection conn = new SqlCeConnection(@"Data Source=c:\users\poloan\documents\visual studio 2010\Projects\LocalDBTrial\LocalDBTrial\data.sdf");
conn.Open();
Boolean queryBool = true;
try
{
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "DELETE FROM dataTbl WHERE name = @name OR id = @id ";
command.Parameters.AddWithValue("@id", idTextbox.Text);
command.Parameters.AddWithValue("@name", nameTextbox.Text);
command.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
queryBool = false;
MessageBox.Show("There was an error on your query..\n" + ex.Message.ToString());
}
finally
{
if (queryBool)
{
MessageBox.Show("Delete success");
}
else
{
MessageBox.Show("Delete failed");
}
}
conn.Close();
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
public virtual DatabaseModel Create(DbConnection connection, IEnumerable <string> tables, IEnumerable <string> schemas)
{
Check.NotNull(connection, nameof(connection));
Check.NotNull(tables, nameof(tables));
ResetState();
_connection = connection as SqlCeConnection;
var connectionStartedOpen = (_connection != null) && (_connection.State == ConnectionState.Open);
if (!connectionStartedOpen)
{
_connection?.Open();
}
try
{
_tableSelectionSet = new TableSelectionSet(tables, schemas);
string databaseName = null;
try
{
if (_connection != null)
{
databaseName = Path.GetFileNameWithoutExtension(_connection.DataSource);
}
}
catch (ArgumentException)
{
// graceful fallback
}
if (_connection != null)
{
_databaseModel.DatabaseName = !string.IsNullOrEmpty(databaseName) ? databaseName : _connection.DataSource;
}
GetTables();
GetColumns();
GetPrimaryKeys();
GetUniqueConstraints();
GetIndexes();
GetForeignKeys();
CheckSelectionsMatched(_tableSelectionSet);
return(_databaseModel);
}
finally
{
if (!connectionStartedOpen)
{
_connection?.Close();
}
}
}
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>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
public virtual DatabaseModel Create(DbConnection connection, TableSelectionSet tableSelectionSet)
{
ResetState();
_connection = connection as SqlCeConnection;
var connectionStartedOpen = (_connection != null) && (_connection.State == ConnectionState.Open);
if (!connectionStartedOpen)
{
_connection?.Open();
}
try
{
_tableSelectionSet = tableSelectionSet;
string databaseName = null;
try
{
if (_connection != null)
{
databaseName = Path.GetFileNameWithoutExtension(_connection.DataSource);
}
}
catch (ArgumentException)
{
// graceful fallback
}
if (_connection != null)
{
_databaseModel.DatabaseName = !string.IsNullOrEmpty(databaseName) ? databaseName : _connection.DataSource;
}
GetTables();
GetColumns();
GetIndexes();
GetForeignKeys();
return(_databaseModel);
}
finally
{
if (!connectionStartedOpen)
{
_connection?.Close();
}
}
}
public void ShowSnapshot(string path)
{
YFormatter = value => value.ToString("C");
SqlCeConnection conn = null;
try
{
var connStr = $"Data Source={path};Max Database Size=4091";
conn = new SqlCeConnection(connStr);
var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT* FROM[Meta];";
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.Read())
{
Title = reader.GetString(5);
}
cmd.CommandText = "SELECT* FROM[Values];";
reader = cmd.ExecuteReader();
Labels = new List <string>();
Values1 = new ChartValues <double>();
Values2 = new ChartValues <double>();
while (reader.Read())
{
Labels.Add(reader.GetDateTime(0).ToString("g"));
Values2.Add(reader.GetDouble(1));
Values1.Add(reader.GetDouble(2));
}
DataContext = this;
}
catch (Exception)
{
// ignored
}
finally
{
conn?.Close();
}
}
private void MineThroughText(string FilePath, string Cluster)
{
StreamReader Reader = new StreamReader(FilePath);
uint LineCounters = 0;
List <DistributionList> DistList = new List <DistributionList>();
if ("" == txtLinesToSample.Text.Trim())
{
txtLinesToSample.Text = "100";
}
while (!Reader.EndOfStream && LineCounters < Convert.ToInt32(txtLinesToSample.Text))
{
string pattern = "";
string LinePattern = "";
string strText = "";
string[] tokens;
// read line
strText = Reader.ReadLine();
LineCounters++;
rtbOpenFile.Text = rtbOpenFile.Text + "\n" + strText;
// string to token
tokens = strText.Split(new Char[] { ' ', ',', '\t' });
// Find pattern in each token
foreach (string token in tokens)
{
if (token != "")
{
string tempPattern = ConvertRegEx(token);
if (pattern != "Text")
{
pattern = tempPattern;
LinePattern = LinePattern + " " + pattern;
}
else if (tempPattern != "Text")
{
pattern = tempPattern;
LinePattern = LinePattern + " " + pattern;
}
}
}
rtbRegEx.Text = rtbRegEx.Text + "\n" + LinePattern;
DistributionList Line = DistList.Find(
delegate(DistributionList DL)
{
return(DL.Line == LinePattern);
}
);
if (Line != null)
{
Line.Frequency++;
}
else
{
DistributionList Item = new DistributionList();
Item.Line = LinePattern;
Item.Frequency = 1;
DistList.Add(Item);
}
}
Reader.Close();
rtbPattern.Text = rtbFrequency.Text = "";
//DistList.Sort();
foreach (DistributionList T in DistList)
{
rtbPattern.Text = rtbPattern.Text + "\n" + T.Line;// +"::" + T.Frequency;
rtbFrequency.Text = rtbFrequency.Text + "\n" + T.Line + "::" + T.Frequency;
}
string hash;
string fhash;
using (System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create())
{
hash = BitConverter.ToString(
md5.ComputeHash(Encoding.UTF8.GetBytes(rtbFrequency.Text))
).Replace("-", String.Empty);
}
using (System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create())
{
fhash = BitConverter.ToString(
md5.ComputeHash(Encoding.UTF8.GetBytes(rtbPattern.Text))
).Replace("-", String.Empty);
}
//Check DB
SqlCeConnection Conn = new SqlCeConnection(@"Data Source=C:\Users\achinbha\SkyDrive\Code-Project\Str2RegEx\Str2RegEx\Sampler.sdf");
Conn.Open();
SqlCeCommand Command = new SqlCeCommand("Select * from SampleDB WHERE Hash='" + hash + "'", Conn);
SqlCeDataReader DataReader = Command.ExecuteReader();
//If pattern exist in DB, update its frequency
// Else insert to DB
if (DataReader.Read())
{
lblCluster.Text = "Cluster: " + DataReader.GetString(0);
lblHash.Text = "Hash: " + DataReader.GetString(1);
lblFrequency.Text = "Frequency: " + DataReader[2].ToString();
SqlCeDataAdapter Adapter = new SqlCeDataAdapter(Command);
Adapter.UpdateCommand = new SqlCeCommand("UPDATE SampleDB SET Frequency=" + (Convert.ToInt32(DataReader[2].ToString()) + 1) + "WHERE Hash = '" + hash + "'", Conn);
Adapter.UpdateCommand.ExecuteNonQuery();
}
else
{
lblCluster.Text = "Cluster: " + Cluster;
lblHash.Text = "Hash: " + hash;
//
// TODO:
// Check with user for Cluster name if not provided
//
SqlCeDataAdapter Adapter = new SqlCeDataAdapter(Command);
Adapter.UpdateCommand = new SqlCeCommand("Insert into SampleDB (Cluster, Hash, Frequency, Stack) Values ('" + Cluster + "', '" + hash + "', 0, '')", Conn);
lblFrequency.Text = "Frequency: " + Adapter.UpdateCommand.ExecuteNonQuery();
}
dataGridView1.Refresh();
DataReader.Close();
Conn.Close();
bool flag = false;
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
if (dataGridView1.Rows[i].Cells[1].Value.ToString() == hash)
{
dataGridView1.ClearSelection();
flag = true;
int Frequency = Convert.ToInt32(dataGridView1.Rows[i].Cells[2].Value.ToString());
Frequency = Frequency + 1;
dataGridView1.Rows[i].Cells[2].Value = Frequency;
dataGridView1.Rows[i].Selected = true;
break;
}
}
if (flag == false)
{
//dataGridView1.Rows.Add(hash, 0, Cluster,"");
}
}
public void SaveSnapshot(string path)
{
SaveData dataToSnapshot = null;
if (Timespan.Contains("y") || Timespan.Contains("m"))
{
var myData = DownloadmyData();
dataToSnapshot = new SaveData
{
CompanyName = myData.meta.Company_Name,
Currency = myData.meta.currency,
Exchangename = myData.meta.Exchange_Name,
TickerName = myData.meta.ticker,
Uri = myData.meta.uri,
Title = myData.meta.Company_Name + " " + TimeNumToDateTime(myData.Date.min).ToString("d") + " - " + TimeNumToDateTime(myData.Date.max).ToString("d"),
Values = new Dictionary <DateTime, Value>()
};
foreach (var variable in myData.series)
{
dataToSnapshot.Values.Add(TimeNumToDateTime(variable.Date), new Value(variable.high, variable.low, variable.close, variable.open, variable.volume));
}
}
if (Timespan.Contains("d"))
{
var dData = DownloaddData();
dataToSnapshot = new SaveData
{
CompanyName = dData.meta.Company_Name,
Currency = dData.meta.currency,
Exchangename = dData.meta.Exchange_Name,
TickerName = dData.meta.ticker,
Uri = dData.meta.uri,
Title = dData.meta.Company_Name + " " + UnixTimeStampToDateTime(dData.Timestamp.min).ToString("t") + " - " + UnixTimeStampToDateTime(dData.Timestamp.max).ToString("t"),
Values = new Dictionary <DateTime, Value>()
};
foreach (var variable in dData.series)
{
dataToSnapshot.Values.Add(UnixTimeStampToDateTime(variable.Timestamp), new Value(variable.high, variable.low, variable.close, variable.open, variable.volume));
}
}
if (dataToSnapshot == null)
{
return;
}
{
if (File.Exists(path))
{
File.Delete(path);
}
var connStr = $"Data Source={path};Max Database Size=4091";
var engine = new SqlCeEngine(connStr);
engine.CreateDatabase();
SqlCeConnection conn = null;
try
{
conn = new SqlCeConnection(connStr);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE [Values] ([Id] datetime NOT NULL, [High] float NOT NULL, [Low] float NOT NULL, [Close] float NOT NULL, [Open] float NOT NULL, [Volume] float NOT NULL);";
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText = "ALTER TABLE [Values] ADD CONSTRAINT [PK_Values] PRIMARY KEY ([Id]);";
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE[Meta]([CompanyName] NVARCHAR(255) NOT NULL,[Currency] NVARCHAR(255) NOT NULL,[Exchangename] NVARCHAR(255) NOT NULL,[TickerName] NVARCHAR(255) NOT NULL,[Uri] NVARCHAR(255) NOT NULL,[Title] NVARCHAR(500));";
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText = "ALTER TABLE [Meta] ADD CONSTRAINT [PK_Values] PRIMARY KEY ([CompanyName]);";
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO [Meta]([CompanyName],[Currency],[Exchangename],[TickerName],[Uri],[Title])VALUES(@companyName,@currency,@exchangename,@tickerName,@uri,@title);";
cmd.Parameters.AddWithValue("@companyName", dataToSnapshot.CompanyName);
cmd.Parameters.AddWithValue("@currency", dataToSnapshot.Currency);
cmd.Parameters.AddWithValue("@exchangename", dataToSnapshot.Exchangename);
cmd.Parameters.AddWithValue("@tickerName", dataToSnapshot.TickerName);
cmd.Parameters.AddWithValue("@uri", dataToSnapshot.Uri);
cmd.Parameters.AddWithValue("@title", dataToSnapshot.Title);
cmd.ExecuteNonQuery();
foreach (var variable in dataToSnapshot.Values)
{
cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO [Values]([Id],[High],[Low],[Close],[Open],[Volume])VALUES(@id,@high,@low,@close,@open,@volume);";
cmd.Parameters.AddWithValue("@id", variable.Key);
cmd.Parameters.AddWithValue("@high", variable.Value.High);
cmd.Parameters.AddWithValue("@low", variable.Value.Low);
cmd.Parameters.AddWithValue("@close", variable.Value.Close);
cmd.Parameters.AddWithValue("@open", variable.Value.Open);
cmd.Parameters.AddWithValue("@volume", variable.Value.Volume);
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
// ignored
}
finally
{
conn?.Close();
}
}
}
private void test()
{
connection = "";
if (cbx1.Text != "Data-Base Address" || cbx1.Text != "")
{
if (cbx1.Text.ToLower() == "internal data-base")
{
connection = Properties.Settings.Default.UCBConnectionString;
}
else if (cbx1.Text.ToLower() != "internal data-base")
{
connection = "DataSource='" + cbx1.Text + "'";
}
if (checkBox2.Checked == true)
{
}
else
{
connection = connection + "; Password='******'";
}
//testing part1
SqlCeConnection conn = new SqlCeConnection(connection);
DataSet dbtDataSet = new DataSet();
DataTable dt;
try
{
conn.Open();
tst_1.Visible = true;
pic_1.Visible = true;
progressBar1.Increment(20);
lbl_testing.Text = "Connected";
pic_1.BackgroundImage = Properties.Resources.tick;
}
catch (Exception erty)
{
tst_1.Visible = true;
pic_1.Visible = true;
pic_1.BackgroundImage = Properties.Resources.ex; tst_1.Text = "Not Connected"; lbl_testing.Text = "Testing Stopped";
if (erty.Message.Contains("The specified password does not match the database password.") == true)
{
Am_err ner = new Am_err();
ner.tx("The Password Specified is Incorrect Please Input the Correct Password and check all inputted data Before Running this Test.");
}
}
try
{
lbl_testing.Text = "Testing Tables...";
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES", conn);
dataAdapter.Fill(dbtDataSet);
progressBar1.Increment(20); dataGridView1.DataSource = dbtDataSet.Tables[0];
tst_2.Visible = true;
pic_2.Visible = true;
tst_2.Text = "Found Tables";
pic_2.BackgroundImage = Properties.Resources.tick;
lbl_testing.Text = "Testing Completed";
progressBar1.Increment(60);
pic_4.Visible = true;
tst_3.Visible = true;
}
catch (Exception erty)
{
tst_2.Visible = true;
tst_2.Text = "Table Testing Unsuccessfull";
pic_2.Visible = true;
pic_2.BackgroundImage = Properties.Resources.ex;
} conn.Close(); //conn.Dispose();
}
else
{
tabControl1.SelectTab(0); Am_err ner = new Am_err(); ner.tx("A valid Data-Base File has not Been Selected");
}
}
/// <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();
}
}
private void txt_ctcode_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == Convert.ToChar(Keys.Enter))
{
string sdate = string.Empty;
if (DTPS.Value.Year > 2550)
{
sdate = (DTPS.Value.Year - 543).ToString() + "." + DTPS.Value.Month.ToString() + "." + DTPS.Value.Day.ToString();
}
else
{
sdate = DTPS.Value.Year.ToString() + "." + DTPS.Value.Month.ToString() + "." + DTPS.Value.Day.ToString();
}
using (SqlCeConnection con = new SqlCeConnection(strcon))
{
//if (NAME == null || NAME == "")
//{
// MessageBox.Show("ไม่มีสินทรัพย์นี้");
// txt_ascode.Text = string.Empty;
// txt_ctcode.Text = string.Empty;
// txt_ascode.Focus();
//}
//else
//{
// //progress insert
//}
int check = 0;
con.Open();
string sqlCheck = @"SELECT ID FROM REPORT WHERE assetid = '" + txt_ascode.Text + "' AND location = '" + DPCODE_ + "' AND workdate = '" + sdate + "'";
using (SqlCeCommand command = new SqlCeCommand(sqlCheck, con))
{
using (SqlCeDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
check = Convert.ToInt32(reader["ID"].ToString());
}
}
}
if (check == 0)
{
string sql = @"INSERT REPORT (workdate,location,assetid,ctcode,name,stcode) VALUES ('" + sdate + "','" + DPCODE_ + "','" + txt_ascode.Text + "','" + txt_ctcode.Text + "','" + NAME + "','" + STCODE_ + "')";
SqlCeCommand cm = new SqlCeCommand(sql, con);
cm.ExecuteNonQuery();
con.Close();
txt_ascode.Text = string.Empty;
txt_ctcode.Text = string.Empty;
txt_ascode.Focus();
loaddata();
}
else
{
con.Close();
MessageBox.Show("ยิงสินทรัพย์นี้ไปแล้ว");
txt_ascode.Text = string.Empty;
txt_ctcode.Text = string.Empty;
txt_ascode.Focus();
}
}
}
}
public void Cleanup()
{
DBConnectionObj?.Close();
}
public void save2()
{
SqlCeConnection conn = new SqlCeConnection(Properties.Settings.Default.conne);
SqlCeCommand cmd = new SqlCeCommand();
conn.Open();
cmd.Connection = conn;
try {
cmd.CommandText = "select count([Acc_id]) from [Trasaction] where [Acc_id]='" + comboBox4.Text + "'";
int t = (int)cmd.ExecuteScalar();
if (t == 1)
{
if (radioButton2.Checked)
{
cmd.CommandText = "select [Cure] from Trasaction where [Acc_id]='" + comboBox5.Text + "'";
SqlCeDataReader read = cmd.ExecuteReader();
String pr = null;
while (read.Read())
{
pr = (read["Cure"].ToString());
}
conn.Close();
conn.Open();
String s = "Credit";
Int32 cu = Convert.ToInt32(pr);
Int32 am = Convert.ToInt32(textBox14.Text);
Int32 ch = cu + am;
cmd.CommandText = "INSERT INTO Trasaction ([Type],[Party],[Amount],Cure,[Pay_mode],[Descp],[Purpose],[Acc_id],[Date_t]) VALUES('" + s + "','" + textBox13.Text + "','" + textBox14.Text + "','" + Convert.ToString(ch) + "','" + comboBox3.Text + "','" + textBox7.Text + "','" + textBox6.Text + "','" + comboBox5.Text + "','" + dateTimePicker2.Text + "')";
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Credit Trasaction Add Successfully");
reload1();
textBox14.Text = "";
textBox7.Text = "";
textBox6.Text = "";
textBox13.Text = "";
dateTimePicker2.Text = DateTime.Now.ToShortDateString();
textBox5.Text = "";
comboBox5.Text = "";
comboBox3.Text = "";
pr = "";
}
else
{
if (radioButton1.Checked)
{
cmd.CommandText = "select Cure from Trasaction where [Acc_id]='" + comboBox4.Text + "'";
SqlCeDataReader read = cmd.ExecuteReader();
String pr = null;
while (read.Read())
{
pr = (read["Cure"].ToString());
}
conn.Close();
conn.Open();
String s = "Debit";
Int32 cu = Convert.ToInt32(pr);
Int32 am = Convert.ToInt32(textBox3.Text);
Int32 ch = cu - am;
cmd.CommandText = "INSERT INTO Trasaction ([Type],[Party],[Amount],Cure,[Pay_mode],[Descp],[Purpose],[Acc_id],[Date_t]) VALUES('" + s + "','" + textBox4.Text + "','" + textBox3.Text + "','" + Convert.ToString(ch) + "','" + comboBox2.Text + "','" + textBox5.Text + "','" + textBox1.Text + "','" + comboBox4.Text + "','" + dateTimePicker1.Text + "')";
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Debit Trasaction Add Successfully");
reload1();
textBox9.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox10.Text = "";
dateTimePicker1.Text = DateTime.Now.ToShortDateString();
textBox5.Text = "";
comboBox2.Text = "";
comboBox4.Text = "";
textBox1.Text = "";
pr = "";
}
}
}
else
{
if (radioButton2.Checked)
{
cmd.CommandText = "SELECT top(1) Cure FROM [Trasaction] where [Acc_id] ='" + comboBox5.Text + "' ORDER BY [TransactionId] DESC";
SqlCeDataReader read = cmd.ExecuteReader();
String pr = null;
while (read.Read())
{
pr = (read["Cure"].ToString());
}
conn.Close();
conn.Open();
String s = "Credit";
Int32 cu = Convert.ToInt32(pr);
Int32 am = Convert.ToInt32(textBox14.Text);
Int32 ch = cu + am;
cmd.CommandText = "INSERT INTO Trasaction ([Type],[Party],[Amount],Cure,[Pay_mode],[Descp],[Purpose],[Acc_id],[Date_t]) VALUES('" + s + "','" + textBox13.Text + "','" + textBox14.Text + "','" + Convert.ToString(ch) + "','" + comboBox3.Text + "','" + textBox7.Text + "','" + textBox6.Text + "','" + comboBox5.Text + "','" + dateTimePicker2.Text + "')";
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Credit Trasaction Add Successfully");
reload1();
textBox14.Text = "";
textBox7.Text = "";
textBox6.Text = "";
textBox13.Text = "";
textBox5.Text = "";
dateTimePicker2.Text = DateTime.Now.ToShortDateString();
comboBox5.Text = "";
comboBox3.Text = "";
pr = "";
}
else
{
if (radioButton1.Checked)
{
cmd.CommandText = "SELECT top(1) Cure FROM [Trasaction] where [Acc_id] ='" + comboBox4.Text + "' ORDER BY [TransactionId] DESC";
SqlCeDataReader read = cmd.ExecuteReader();
String pr = null;
while (read.Read())
{
pr = (read["Cure"].ToString());
}
conn.Close();
conn.Open();
String s = "Debit";
Int32 cu = Convert.ToInt32(pr);
Int32 am = Convert.ToInt32(textBox3.Text);
Int32 ch = cu - am;
cmd.CommandText = "INSERT INTO Trasaction ([Type],[Party],[Amount],Cure,[Pay_mode],[Descp],[Purpose],[Acc_id],[Date_t]) VALUES('" + s + "','" + textBox4.Text + "','" + textBox3.Text + "','" + Convert.ToString(ch) + "','" + comboBox2.Text + "','" + textBox5.Text + "','" + textBox1.Text + "','" + comboBox4.Text + "','" + dateTimePicker1.Text + "')";
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Debit Trasaction Add Successfully");
reload1();
textBox9.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox10.Text = "";
textBox5.Text = "";
dateTimePicker1.Text = DateTime.Now.ToShortDateString();
comboBox2.Text = "";
comboBox4.Text = "";
textBox1.Text = "";
pr = "";
}
}
conn.Close();
}
}
catch (Exception o) {
MessageBox.Show("Error " + o);
}
}
public static void Generate()
{
/* get the Path */
String dirPath = Application.StartupPath + "\\App_Code\\";
var directoryName = System.IO.Path.GetDirectoryName(dirPath);
var fileName = System.IO.Path.Combine(directoryName, DbFile);
string connStr = @"Data Source = " + fileName;// +";Mode = Exclusive";
using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
String Query = String.Empty;
try
{
Query = "DELETE FROM [TaskHeader]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [TaskDetail]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [FinalExport]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [Final_Table]";
RunDDL(conn, Query);
} catch {}
#region [ AdiGlobal ]
try {
Query = "DELETE FROM [ADIProduct1]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [ADIProduct]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [ADIInventoryExport]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [ADIInventory]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [ADIChild]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [ADICategoryExport]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [ADICategory]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [ADIBrands]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [ADIBrands]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [ADIBrands]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [ADIBrands]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [ADIBrands]";
RunDDL(conn, Query);
} catch {}
#endregion
#region [ SecLock ]
try {
Query = "DELETE FROM [SecLockCategory]";
RunDDL(conn, Query);
} catch {}
try {
Query = "DELETE FROM [SecLockManufacturer]";
RunDDL(conn, Query);
} catch {}
try
{
Query = "DELETE FROM [SecLockManufacturerSeries]";
RunDDL(conn, Query);
}
catch { }
try {
Query = "DELETE FROM [SecLockProduct]";
RunDDL(conn, Query);
} catch {}
#endregion
conn.Close();
conn.Dispose();
}
/* check if exists */
if (File.Exists(fileName))
{
File.Delete(fileName);
}
/* create Database */
SqlCeEngine engine = new SqlCeEngine(connStr);
engine.CreateDatabase();
/* create table and columns */
using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
String Query = String.Empty;
#region [ Temp Tables ]
Query = "CREATE TABLE [TaskHeader] ([ScheduleID] bigint IDENTITY (1,1) NOT NULL, [TaskName] nvarchar(255) NOT NULL, [TaskDescription] nvarchar(4000) NOT NULL, [Site] nvarchar(255) NOT NULL, [ScheduleFrom] datetime NOT NULL, [TaskRepeat] bit NULL, [TaskRepeatInterval] int NOT NULL, [TaskRepeatUnit] nvarchar(20) NOT NULL, [Enabled] bit NULL, [LastRun] datetime NULL, [NextRun] datetime NULL, [CreatedDate] datetime NOT NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [TaskDetail] ([TaskID] bigint IDENTITY (1,1) NOT NULL, [TaskHeaderID] bigint NOT NULL, [TaskNameText] nvarchar(255) NOT NULL, [TaskNameValue] nvarchar(255) NOT NULL, [TaskStatusText] nvarchar(255) NOT NULL, [TaskStatus] int NULL, [DownloadImages] bit DEFAULT (0) NOT NULL, [IgnitoMode] bit DEFAULT (0) NOT NULL, [TaskType] nvarchar(255) NOT NULL, [TaskMode] nvarchar(255) NOT NULL, [TaskSite] nvarchar(255) NOT NULL, [CreatedOn] datetime NOT NULL, [UpdatedOn] datetime NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [FinalExport] ([ExportSite] nvarchar(255) NULL, [ExportType] nvarchar(255) NULL, [ExportValue] nvarchar(255) NOT NULL, [CreatedDate] datetime DEFAULT (GETDATE()) NOT NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [Final_Table] ([ID] bigint NULL, [UPC] nvarchar(255) NULL, [VDR_PART] nvarchar(255) NULL, [VDR_IT_DSC] nvarchar(255) NULL, [Image_Folder] nvarchar(255) NULL, [AID_SOURCE_ID] nvarchar(225) NULL, [AID_PART] nvarchar(255) NULL, [AID_COST] numeric(10,2) NULL, [AID_IMG1] nvarchar(255) NULL, [AID_IMG2] nvarchar(255) NULL, [AID_VENDOR] nvarchar(255) NULL, [AID_INV] nvarchar(255) NULL, [AID_LastUpdate] nvarchar(255) NULL)";
RunDDL(conn, Query);
#endregion
#region [ AdiGlobal ]
Query = "CREATE TABLE [ADIProduct1] ([ID] bigint IDENTITY (27468,1) NOT NULL, [AdiNumber] nvarchar(255) NULL, [VendorName] nvarchar(255) NULL, [VendorNumber] nvarchar(255) NULL, [VendorModel] nvarchar(255) NULL, [PartNumber] nvarchar(255) NULL, [Name] nvarchar(255) NULL, [Url] nvarchar(255) NULL, [AllowedToBuy] nvarchar(255) NULL, [DangerousGoodsMessage] nvarchar(255) NULL, [InventoryMessage] nvarchar(255) NULL, [MarketingMessage] nvarchar(255) NULL, [MinQty] numeric(10,2) NULL, [ModelNumber] nvarchar(255) NULL, [Price] numeric(10,2) NULL, [ProductDescription] nvarchar(255) NULL, [ProductImagePath] nvarchar(255) NULL, [RecycleFee] nvarchar(255) NULL, [SaleMessageIndicator] nvarchar(255) NULL, [SaleType] nvarchar(255) NULL, [ST] nvarchar(255) NULL, [SMI] nvarchar(255) NULL, [InventoryMessageCode] nvarchar(255) NULL, [CatagoryID] nvarchar(255) NULL, [SmallImage] nvarchar(255) NULL, [BigImage] nvarchar(255) NULL, [ClearanceZone] bit DEFAULT (0) NOT NULL, [SaleCenter] bit DEFAULT (0) NOT NULL, [OnlineSpecials] bit DEFAULT (0) NOT NULL, [HotDeals] bit DEFAULT (0) NOT NULL, [InStock] bit DEFAULT (0) NOT NULL, [IsUpdating] bit NULL, [UpdateInterval] int NULL, [PriorityProduct] BIT, [LeastCount] int DEFAULT 0, [LastUpdateDatetime] datetime NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [ADIProduct] ([ID] bigint NOT NULL, [VENDOR_TITLE_NAME] nvarchar(255) NULL, [PART_NUM] nvarchar(255) NULL, [Cost] float NULL, [CAT_CODE] nvarchar(255) NULL, [BRAND_VALUE] nvarchar(255) NULL, [VDR_PART] nvarchar(255) NULL, [VDR_IT_DSC] nvarchar(4000) NULL, [Image_Folder] nvarchar(255) NULL, [AID_IMG1] nvarchar(255) NULL, [AID_IMG2] nvarchar(255) NULL, [CR Cost] float NULL, [MKT_MSG] nvarchar(4000) NULL, [LastUpdate] datetime NULL, [IsUpdating] bit NULL, [UpdateInterval] int NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [ADIInventoryExport] ([PART_NUM] nvarchar(255) NULL, [TotalInventory] int NULL, [Dallas] int NULL, [DC_AtlantaHub] int NULL, [DC_Dallas_Hub] int NULL, [DC_Elk_Grove_Hub] int NULL, [DC_Feura_Bush] int NULL, [DC_Louisville_Hub] int NULL, [DC_Reno_Hub] int NULL, [DC_Richmond_Dist_Ctr] int NULL, [Oklahama] int NULL, [RemainingBranches] int NULL, [LastUpdate] datetime NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [ADIInventory] ([AdiNumber] nvarchar(255) NOT NULL, [id] nvarchar(255) NULL, [dc] nvarchar(255) NULL, [region] nvarchar(255) NULL, [storeName] nvarchar(255) NULL, [address1] nvarchar(255) NULL, [address2] nvarchar(255) NULL, [address3] nvarchar(255) NULL, [country] nvarchar(255) NULL, [city] nvarchar(255) NULL, [state] nvarchar(255) NULL, [stateName] nvarchar(255) NULL, [zip] nvarchar(255) NULL, [phone] nvarchar(255) NULL, [fax] nvarchar(255) NULL, [lat] real NULL, [lon] real NULL, [inventory] nvarchar(255) NULL, [manager] nvarchar(255) NULL, [responseCode] nvarchar(255) NULL, [responseMessage] nvarchar(255) NULL, [IsHub] bit NULL, [LastUpdate] datetime NOT NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [ADIChild] ([ID] bigint IDENTITY (1,1) NOT NULL, [PART_NUM] nvarchar(255) NULL, [PropertyName] nvarchar(255) NULL, [PropertyValue] nvarchar(4000) NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [ADICategoryExport] ([RootValue] nvarchar(255) NULL, [RootDisplayName] nvarchar(4000) NULL, [ParentValue] nvarchar(255) NULL, [ParentDisplayName] nvarchar(4000) NULL, [Value] nvarchar(255) NOT NULL, [DisplayName] nvarchar(4000) NULL, [CategoryUrl] nvarchar(4000) NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [ADICategory] ([ParentValue] nvarchar(255) NULL, [Value] nvarchar(255) NOT NULL, [DisplayName] nvarchar(4000) NULL, [CategoryUrl] nvarchar(4000) NULL, [ClearanceZone] bit DEFAULT (0) NOT NULL, [SaleCenter] bit DEFAULT (0) NOT NULL, [OnlineSpecials] bit DEFAULT (0) NOT NULL, [HotDeals] bit DEFAULT (0) NOT NULL, [InStock] bit DEFAULT (0) NOT NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [ADIBrands] ([Value] nvarchar(255) NOT NULL, [DisplayName] nvarchar(255) NULL, [ClearanceZone] bit DEFAULT (0) NOT NULL, [SaleCenter] bit DEFAULT (0) NOT NULL, [OnlineSpecials] bit DEFAULT (0) NOT NULL, [HotDeals] bit DEFAULT (0) NOT NULL, [InStock] bit DEFAULT (0) NOT NULL)";
RunDDL(conn, Query);
Query = "ALTER TABLE [TaskHeader] ADD CONSTRAINT [PK__TaskHeader__00000000000005B2] PRIMARY KEY ([ScheduleID])";
RunDDL(conn, Query);
Query = "ALTER TABLE [TaskDetail] ADD CONSTRAINT [PK__TaskDetail__00000000000002FD] PRIMARY KEY ([TaskID])";
RunDDL(conn, Query);
Query = "ALTER TABLE [ADIProduct1] ADD CONSTRAINT [PK__ADIProduct1__000000000000037D] PRIMARY KEY ([ID])";
RunDDL(conn, Query);
Query = "ALTER TABLE [ADIProduct] ADD CONSTRAINT [PK__ADIProduct__0000000000000063] PRIMARY KEY ([ID])";
RunDDL(conn, Query);
Query = "ALTER TABLE [ADIChild] ADD CONSTRAINT [PK__ADIChild__0000000000000228] PRIMARY KEY ([ID])";
RunDDL(conn, Query);
Query = "ALTER TABLE [ADIBrands] ADD CONSTRAINT [PK__ADIBrands__00000000000003B8] PRIMARY KEY ([Value])";
RunDDL(conn, Query);
#endregion
#region [ SecLock ]
Query = "CREATE TABLE [SecLockManufacturer] ([Code] nvarchar(100) NOT NULL, [Name] nvarchar(100) NOT NULL, [ImagePath] nvarchar(100) NOT NULL, [Url] nvarchar(100) NOT NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [SecLockManufacturerSeries] ([ID] bigint IDENTITY (1,1) NOT NULL, [ManufacturerCode] nvarchar(100) NOT NULL, [Name] nvarchar(100) NOT NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [SecLockCategory] ([Code] nvarchar(100) NOT NULL, [Name] nvarchar(100) NOT NULL)";
RunDDL(conn, Query);
Query = "CREATE TABLE [SecLockProduct] ([Code] nvarchar(100) NOT NULL, [Name] nvarchar(100) NOT NULL, [Url] nvarchar(100) NOT NULL, [ManufacturerCode] nvarchar(100) NULL, [ManufacturerName] nvarchar(100) NULL, [ManufacturerSeries] nvarchar(100) NULL, [CategoyCode] nvarchar(100) NULL, [CategoryName] nvarchar(100) NULL, [YourPrice] numeric(10,2) NULL, [ListPrice] numeric(10,2) NULL, [ImageUrl1] nvarchar(100) NULL, [ImageUrl2] nvarchar(100) NULL, [Stock] int NULL, [Description] nvarchar(4000) NULL, [TechDoc] nvarchar(4000) NULL)";
RunDDL(conn, Query);
Query = "ALTER TABLE [SecLockManufacturer] ADD CONSTRAINT [PK_SecLockManufacturer] PRIMARY KEY ([Code])";
RunDDL(conn, Query);
Query = "ALTER TABLE [SecLockManufacturerSeries] ADD CONSTRAINT [PK_SecLockManufacturerSeries] PRIMARY KEY ([ID])";
RunDDL(conn, Query);
Query = "ALTER TABLE [SecLockCategory] ADD CONSTRAINT [PK_SecLockCategory] PRIMARY KEY ([Code])";
RunDDL(conn, Query);
Query = "ALTER TABLE [SecLockProduct] ADD CONSTRAINT [PK_SecLockProduct] PRIMARY KEY ([Code])";
RunDDL(conn, Query);
#endregion
conn.Close();
conn.Dispose();
}
}
private void frmKonumAdres_Load(object sender, EventArgs e)
{
try
{
baglanti.Open();
SqlCeCommand Benzin = new SqlCeCommand("select UrunAd,UrunTur,DepoKonum from UrunlerGiris", baglanti);
SqlCeDataReader Benzinokut = Benzin.ExecuteReader();
while (Benzinokut.Read() == true)
{
if (Benzinokut[2].ToString() == "BENZİN ÜRÜNLERİ")
{
lstBenzin.Items.Add("Cinsi :" + Benzinokut[0].ToString() + " - " + "Adeti :" + Benzinokut[1].ToString());
lstBenzin.Items.Add("------------------");
}
}
SqlCeCommand Hayvanlar = new SqlCeCommand("select Cinsi,Adet,YasamAlani from Buyukbas", baglanti);
SqlCeDataReader okut = Hayvanlar.ExecuteReader();
while (okut.Read() == true)
{
if (okut[2].ToString() == "HAYVANLAR")
{
lstHayvanlar.Items.Add("Cinsi :" + okut[0].ToString() + " - " + "Adeti :" + okut[1].ToString());
lstHayvanlar.Items.Add("------------------");
}
}
SqlCeCommand Kucukbas = new SqlCeCommand("select Cinsi,Adet,YasamAlani from Kucukbas", baglanti);
SqlCeDataReader oku = Hayvanlar.ExecuteReader();
while (oku.Read() == true)
{
if (oku[2].ToString() == "HAYVANLAR")
{
lstHayvanlar.Items.Add("Cinsi :" + oku[0].ToString() + " - " + "Adeti :" + oku[1].ToString());
lstHayvanlar.Items.Add("------------------");
}
}
SqlCeCommand Ulasım = new SqlCeCommand("select Arac,Romork,DepoBenzin from Ulasım", baglanti);
SqlCeDataReader ulas = Ulasım.ExecuteReader();
while (ulas.Read() == true)
{
if (ulas[2].ToString() == "ULAŞIM")
{
lstUlasım.Items.Add("Araç :" + ulas[0].ToString() + " - " + "Römörk :" + ulas[1].ToString());
lstUlasım.Items.Add("------------------");
}
}
SqlCeCommand sut = new SqlCeCommand("select UrunAd,Miktar,DepoKonum from UrunlerGiris ", baglanti);
SqlCeDataReader urunGir = sut.ExecuteReader();
while (urunGir.Read() == true)
{
if (urunGir[2].ToString() == "SÜT ÜRÜNLERİ")
{
lstSutUrunleri.Items.Add("Ürün Ad :" + urunGir[0].ToString() + " - " + "Miktar :" + urunGir[1].ToString());
lstSutUrunleri.Items.Add("------------------");
}
}
SqlCeCommand Harman = new SqlCeCommand("select UrunAd,Miktar,DepoKonum from UrunlerGiris ", baglanti);
SqlCeDataReader urunGirHarman = Harman.ExecuteReader();
while (urunGirHarman.Read() == true)
{
if (urunGirHarman[2].ToString() == "HARMAN")
{
lstHarman.Items.Add("Ürün Ad :" + urunGirHarman[0].ToString() + " - " + "Miktar :" + urunGirHarman[1].ToString());
lstHarman.Items.Add("------------------");
}
}
SqlCeCommand Tarısmsal = new SqlCeCommand("select UrunAd,Miktar,DepoKonum from UrunlerGiris ", baglanti);
SqlCeDataReader urunGirTarımsal = Tarısmsal.ExecuteReader();
while (urunGirTarımsal.Read() == true)
{
if (urunGirTarımsal[2].ToString() == "TARIMSAL ÜRÜNLER")
{
lstAraziMahsulleri.Items.Add("Ürün Ad :" + urunGirTarımsal[0].ToString() + " - " + "Miktar :" + urunGirTarımsal[1].ToString());
lstAraziMahsulleri.Items.Add("------------------");
}
}
SqlCeCommand Et = new SqlCeCommand("select UrunAd,Miktar,DepoKonum from UrunlerGiris ", baglanti);
SqlCeDataReader urunGirEt = Et.ExecuteReader();
while (urunGirEt.Read() == true)
{
if (urunGirEt[2].ToString() == "ET-KASAP")
{
lstEtKasap.Items.Add("Ürün Ad :" + urunGirEt[0].ToString() + " - " + "Miktar :" + urunGirEt[1].ToString());
lstEtKasap.Items.Add("------------------");
}
}
SqlCeCommand Diger = new SqlCeCommand("select UrunAd,Miktar,DepoKonum from UrunlerGiris ", baglanti);
SqlCeDataReader urunGirDiger = Diger.ExecuteReader();
while (urunGirDiger.Read() == true)
{
if (urunGirDiger[2].ToString() != "ET-KASAP" && urunGirDiger[2].ToString() != "TARIMSAL ÜRÜNLER" && urunGirDiger[2].ToString() != "ULAŞIM" && urunGirDiger[2].ToString() != "HARMAN" && urunGirDiger[2].ToString() != "SÜT ÜRÜNLERİ" && urunGirDiger[2].ToString() != "HAYVANLAR" && urunGirDiger[2].ToString() != "BENZİN ÜRÜNLERİ")
{
lstDiger.Items.Add("Ürün Ad :" + urunGirDiger[0].ToString() + " - " + "Miktar :" + urunGirDiger[1].ToString());
lstDiger.Items.Add("------------------");
}
}
baglanti.Close();
}
catch (Exception mesaj)
{
MessageBox.Show(mesaj.Message.ToString(), "UYARI", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
void CreateTables()
{
CeConn.Open();
try
{
string CreateCariTableSql = " CREATE TABLE CARI ( " +
" CR_CARI_NO nvarchar (10), " +
" CR_CARI_ADI1 nvarchar (37), " +
" CR_ADRES1 nvarchar (38), " +
" CR_ADRES2 nvarchar (26), " +
" CR_TEL1 nvarchar (12), " +
" CR_VERGI_DA nvarchar (14), " +
" CR_VERGI_NO nvarchar (10), " +
" CR_RISK_LIM float , " +
" CR_TOPLAM_RISK float , " +
" CR_PLAS_HS nvarchar (10), " +
" rg_pt float, " +
" rg_sl float, " +
" rg_cr float, " +
" rg_pr float, " +
" rg_cm float, " +
" rg_ct float, " +
" borc_bakiye float," +
" ort_vade datetime ," +
" rg_pz float) ";
SqlCeCommand cmd = new SqlCeCommand(CreateCariTableSql, CeConn);
cmd.ExecuteNonQuery();
string CreateStokTabeString = " CREATE TABLE Stok (" +
" GRUP_KODU nvarchar (12), " +
" SICIL_KODU nvarchar (13), " +
" SICIL_ADI nvarchar (70), " +
" SF1 float," +
" SF2 float," +
" SF3 float," +
" SF4 float," +
" SF5 float," +
" AF1 float," +
" AF2 float," +
" AF3 float," +
" AF4 float," +
" AF5 float," +
" SATIS_KDVY float," +
" IND1 float," +
" IND2 float," +
" IND3 float," +
" IND4 float," +
" IND5 float," +
" ISK_ENGEL nvarchar (11)," +
" ISKOD3 nvarchar (7)," +
" BIRIM nvarchar (8)," +
" BIRIM1A nvarchar (8)," +
" BIRIM1C float) ";
cmd.CommandText = CreateStokTabeString;
cmd.ExecuteNonQuery();
string CreateCarDatTableString = "CREATE TABLE CarDat ( " +
" TARIH datetime, " +
" HS_NO nvarchar (10), " +
" BELGE_NO nvarchar (9)," +
" BORC float ," +
" ALAC float ," +
" OZKOD2 nvarchar (19)," +
" VADE datetime ," +
" CR_PLAS_HS nvarchar (10))";
cmd.CommandText = CreateCarDatTableString;
cmd.ExecuteNonQuery();
string CreateSBaslikTableString = "CREATE TABLE SiparisBaslik ( " +
" Musteri_Kodu nvarchar (15)," +
" Siparis_No int IDENTITY (1, 1)," +
" Musteri_Adi nvarchar (60) ," +
" Plasiyer_Kodu nvarchar (15)," +
" Siparis_Tarihi datetime, " +
" Teslim_Tarihi datetime, " +
" Vade_Gunu int, " +
" Odeme_Sekli nvarchar (1))";
cmd.CommandText = CreateSBaslikTableString;
cmd.ExecuteNonQuery();
string SiparisDetayTableString = "CREATE TABLE siparisDetay( " +
" Grup_Kodu nvarchar (15), " +
" Siparis_No int, " +
" SiparisDetay_No int IDENTITY (1, 1)," +
" Sicil_Kodu nvarchar (15), " +
" Plasiyer_Kodu nvarchar (15), " +
" Sicil_Adi nvarchar (70), " +
" Miktar float, " +
" Birim nvarchar (10), " +
" Birim_Fiyat float, " +
" Tutar float, " +
" iskonto1 float, " +
" iskonto2 float, " +
" iskonto3 float, " +
" iskonto4 float, " +
" iskonto5 float, " +
" kolimiktar float)";
cmd.CommandText = SiparisDetayTableString;
cmd.ExecuteNonQuery();
string TahsilatTableString = "CREATE TABLE TahsilatBaslik ( " +
" Cari_No nvarchar (15), " +
" Makbuz_No int IDENTITY (1,1) ," +
" Plasiyer_Kodu nvarchar (15), " +
" Tahsilat_Tarihi datetime )";
cmd.CommandText = TahsilatTableString;
cmd.ExecuteNonQuery();
string TahsilatDetayString = "CREATE TABLE TahsilatDetay ( " +
" Makbuz_No int ," +
" Tahsilat_Turu nvarchar(1)," +
" Tahsilat_Tutari float, " +
" Para_cinsi nvarchar (3), " +
" Vadesi datetime, " +
" Borclu nvarchar (60), " +
" Tanzim_Tarihi datetime, " +
" Tanzim_Yeri nvarchar (15), " +
" Banka nvarchar (15), " +
" Banka_Sube nvarchar (15), " +
" Belge_No int IDENTITY (1,1), " +
" KKart_No nvarchar (19)," +
" Plasiyer_Kodu nvarchar(15), " +
" Cari_No nvarchar(15)) ";
cmd.CommandText = TahsilatDetayString;
cmd.ExecuteNonQuery();
string ZiyaretTableString = "CREATE TABLE Ziyaret ( " +
" Cari_No nvarchar (15), " +
" Ziyaret_turu nvarchar (250)," +
" Kilometre float, " +
" Ziyaret_Tarih datetime," +
" Plasiyer_Kodu nvarchar (15))";
cmd.CommandText = ZiyaretTableString;
cmd.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
}
CeConn.Close();
}
public void close()
{
conn.Close();
}
public void Close()
{
sqlConnection.Close();
}
public MainWindow()
{
InitializeComponent();
loggedAs.Text = Receptionist.loggedAs;
//number of occupied rooms
SqlCeConnection databaseConnection = new SqlCeConnection(@"Data Source=" + Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\TheLotusTempleManager\TheLotusTempleDB.sdf");
string query = "SELECT COUNT(*) FROM roomsTab WHERE [Room Status] = 'Occupied'";
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
occupiedRoomsNum.Text = cmd.ExecuteScalar().ToString();
databaseConnection.Close();
//number of availbe rooms
query = "SELECT COUNT(*) FROM roomsTab WHERE [Room Status] = 'Not occupied'";
SqlCeCommand cmd2 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
avaibleRoomsNum.Text = cmd2.ExecuteScalar().ToString();
databaseConnection.Close();
//number of avaible A rooms
query = "SELECT COUNT(*) FROM roomsTab WHERE [Room Status] = 'Not occupied' AND [Room Type] = 'A'";
SqlCeCommand cmd3 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
avaibleANum.Text = cmd3.ExecuteScalar().ToString();
databaseConnection.Close();
//number of avaible B rooms
query = "SELECT COUNT(*) FROM roomsTab WHERE [Room Status] = 'Not occupied' AND [Room Type] = 'B'";
SqlCeCommand cmd4 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
avaibleBNum.Text = cmd4.ExecuteScalar().ToString();
databaseConnection.Close();
//number of avaible C rooms
query = "SELECT COUNT(*) FROM roomsTab WHERE [Room Status] = 'Not occupied' AND [Room Type] = 'C'";
SqlCeCommand cmd5 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
avaibleCNum.Text = cmd5.ExecuteScalar().ToString();
databaseConnection.Close();
//number of guests
query = "SELECT COUNT(*) FROM guestTab";
SqlCeCommand cmd6 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
guestsNum.Text = cmd6.ExecuteScalar().ToString();
databaseConnection.Close();
//number of male guests
query = "SELECT COUNT(*) FROM guestTab WHERE Gender = 'Male'";
SqlCeCommand cmd7 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
guestsMaleNum.Text = cmd7.ExecuteScalar().ToString();
databaseConnection.Close();
//number of female guests
query = "SELECT COUNT(*) FROM guestTab WHERE Gender = 'Female'";
SqlCeCommand cmd8 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
guestsFemaleNum.Text = cmd8.ExecuteScalar().ToString();
databaseConnection.Close();
//number of checkouts today
query = "SELECT COUNT(*) FROM booking WHERE [CheckOut date] =@checkout";
SqlCeCommand cmd9 = new SqlCeCommand(query, databaseConnection);
cmd9.Parameters.AddWithValue("checkout", DateTime.Today);
databaseConnection.Open();
checkoutTodayNum.Text = cmd9.ExecuteScalar().ToString();
databaseConnection.Close();
}
/// <summary>
/// Program glowny
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
Watek w1 = new Watek(); //obiekt z procedura watku
Thread t1 = null; //obiekt na watek. Na razie pusty!
Console.WriteLine("Info:");
Console.WriteLine("1-start pomiarow");
Console.WriteLine("2-stop pomiarow");
Console.WriteLine("3-wyswietlanie danych");
Console.WriteLine("4-koniec programu");
try
{
//pobiera sciezke programu exe
String strAppDir = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
//dodaje do sciezki programu exe nazwe pliku bazy danych
String strFullPathToMyFile = Path.Combine(strAppDir, "AppDatabase1.sdf");
string sdfpath = @"sciezka\AppDatabase1.sdf";
SqlCeConnection conn = new SqlCeConnection("Data Source = " + sdfpath);
conn.Open(); //proba polaczenia z bazą
SqlCeCommand cmd = conn.CreateCommand(); //obiekt polecenia do bazy
while (true)
{
string zn = Console.ReadLine(); //czyta klawisz
if (zn == "4") //koniec programu
{
if ((t1 != null))
{ //sprawdzenie, czy watek juz nie dziala
Console.WriteLine("Watek ciagle działa!");
continue; //wraca na poczatek while
}
Console.WriteLine("Koniec pracy");
break; //koniec
}
if (zn == "1") //wlacza watek
{
if ((t1 != null))
{ //sprawdzenie, czy watek juz nie dziala
Console.WriteLine("Watek juz działa!");
continue; //wraca na poczatek while
}
w1.isStop = false; //warunek dzialania watku
t1 = new Thread(w1.ProcWatek); //obiekt watku
t1.Start(); // uruchomienie w watku
Console.WriteLine("Start watek");
}
if (zn == "2")//wylacza watek
{
if ((t1 == null))
{ //sprawdzenie, czy watek dziala
Console.WriteLine("Watek juz nie działa!");
continue; //wraca na poczatek while
}
w1.isStop = true;
t1 = null;
Console.WriteLine("Stop watek");
}
if (zn == "3") //wyswietlanie danych
{
cmd.CommandText = "select * from Pomiary ";
SqlCeDataReader reader = cmd.ExecuteReader(); //obiekt do odczytu danych
try
{
while (true) //petla po rekordach tabeli
{
if (!reader.Read())
{
break; //EOF
}
string wart = reader["wartosc"].ToString(); //odczyt z pola wartosc
string wielk = reader["wielkosc"].ToString(); //odczyt z pola wielkosc
Console.WriteLine("rekord: " + wart + ", " + wielk); //wyswietlenie
}
}
catch (Exception ex) { Console.WriteLine("Błąd " + ex.Message); }
// w1.isStop = false; //warunek dzialania watku
// t1 = new Thread(w1.ProcWatek); //obiekt watku
// t1.Start(); // uruchomienie w watku
// Console.WriteLine("Start watek");
}
}
conn.Close();//zamknac polaczenie z baza
}
catch (Exception ex)
{
Console.WriteLine("Błąd " + ex.Message);
}
}
private void ViewPredmeti_Load(object sender, EventArgs e)
{
cn.Open();
this.predmetiTableAdapter.Fill(this.database1DataSet.Predmeti);
cn.Close();
}
private void updateLista()
{
listView.Items.Clear();
SqlCeConnection connection = new SqlCeConnection(Properties.Settings.Default.DataConnectionString);
connection.Open();
String selectString = "(nome Like @nome or celular Like @celular or cpf Like @cpf or rg Like @rg)";
SqlCeCommand cmd_count = new SqlCeCommand("SELECT COUNT(*) FROM Clientes WHERE " + selectString, connection);
cmd_count.Parameters.AddWithValue("@nome", "%" + busca_edit.Text.ToString() + "%");
cmd_count.Parameters.AddWithValue("@celular", "%" + busca_edit.Text.ToString() + "%");
cmd_count.Parameters.AddWithValue("@cpf", "%" + busca_edit.Text.ToString() + "%");
cmd_count.Parameters.AddWithValue("@rg", "%" + busca_edit.Text.ToString() + "%");
int length = (int)cmd_count.ExecuteScalar();
clienteIDs = new int [length];
error_panel.Visible = length <= 0;
if (length > 0)
{
int i = 0;
DataSet AVATARLINE = new DataSet();
SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Clientes WHERE " + selectString + " ORDER BY nome " + (sortOrder ? "ASC" : "DESC"), connection);
cmd.Parameters.AddWithValue("@nome", "%" + busca_edit.Text.ToString() + "%");
cmd.Parameters.AddWithValue("@celular", "%" + busca_edit.Text.ToString() + "%");
cmd.Parameters.AddWithValue("@cpf", "%" + busca_edit.Text.ToString() + "%");
cmd.Parameters.AddWithValue("@rg", "%" + busca_edit.Text.ToString() + "%");
SqlCeDataAdapter AVATARLINE_1 = new SqlCeDataAdapter(cmd);
//SqlCeDataAdapter AVATARLINE_1 = new SqlCeDataAdapter("SELECT * FROM clientes WHERE " + findParameters + " ORDER BY nome " + (sortOrder ? "ASC" : "DESC"), connection);
AVATARLINE_1.Fill(AVATARLINE);
foreach (DataRow row in AVATARLINE.Tables[0].Rows)
{
ListViewItem list = new ListViewItem(row [9].ToString());
/*if (!row [2].ToString().Equals("")) {
* list.SubItems.Add(Transform.unPackPhone(row [2].ToString()));
* } else*/
list.SubItems.Add(Transform.unPackPhone(row [3].ToString()));
listView.Items.Add(list);
clienteIDs [i] = int.Parse(row [0].ToString());
i++;
}
}
else
{
/*ListViewItem list;
* list = new ListViewItem("Nenhum cliente encontrado.");
* if(busca_edit.Text.Equals(""))
* list = new ListViewItem("");
* else
* list = new ListViewItem("");
* listView.Items.Add(list);*/
}
/*SqlCeCommand cmd_count = new SqlCeCommand("SELECT COUNT(*) FROM [clientes] codigo", connection);
* int length = (int) cmd_count.ExecuteScalar();
*
* for (int i = 0; i < 1; i++) {
* SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM clientes WITH(INDEX(" + i + "))", connection);
* SqlCeDataReader re = cmd.ExecuteReader();
*
* if (re.Read()) {
* ListViewItem list = new ListViewItem(re ["nome"].ToString());
* list.SubItems.Add(Transform.unPackPhone(re ["telefone"].ToString()));
* listView.Items.Add(list);
* } else {
* this.Close();
* MessageBox.Show("Please enter a valid item barcode");
* }
* re.Close();
* }*/
connection.Close();
/*for (int i = 0; i < itemListNome.Length; i++) {
* ListViewItem list = new ListViewItem(itemListNome [i]);
* list.SubItems.Add(Transform.unPackPhone(itemListTelefone [i]));
* listView.Items.Add(list);
* }*/
}
private void button1_Click_1(object sender, System.EventArgs e)
{
// Get Template document and database path.
string dataPath = Application.StartupPath + @"..\..\..\..\..\..\..\common\Data\";
try
{
//SDF the database and get the NorthWind
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
DataTable table = new DataTable();
SqlCeConnection conn = new SqlCeConnection();
if (conn.ServerVersion.StartsWith("3.5"))
{
conn.ConnectionString = "Data Source = " + dataPath + "NorthwindIO_3.5.sdf";
}
else
{
conn.ConnectionString = "Data Source = " + dataPath + "NorthwindIO.sdf";
}
conn.Open();
SqlCeDataAdapter adapter = new SqlCeDataAdapter("Select CustomerID,CompanyName,ContactName,Address,Country,Phone from Customers", conn);
adapter.Fill(table);
adapter.Dispose();
conn.Close();
// Creating a new document.
WordDocument document = new WordDocument();
// Adding a new section to the document.
IWSection section = document.AddSection();
IWParagraph paragraph = section.AddParagraph();
paragraph.ParagraphFormat.BeforeSpacing = 20f;
//Format the heading.
IWTextRange text = paragraph.AppendText("Northwind Report");
text.CharacterFormat.Bold = true;
text.CharacterFormat.FontName = "Cambria";
text.CharacterFormat.FontSize = 14.0f;
text.CharacterFormat.TextColor = Color.DarkBlue;
paragraph.ParagraphFormat.HorizontalAlignment = Syncfusion.DocIO.DLS.HorizontalAlignment.Center;
paragraph = section.AddParagraph();
paragraph.ParagraphFormat.BeforeSpacing = 18f;
//Create a new table
WTextBody textBody = section.Body;
IWTable docTable = textBody.AddTable();
//Set the format for rows
RowFormat format = new RowFormat();
format.Borders.BorderType = Syncfusion.DocIO.DLS.BorderStyle.Single;
format.Borders.LineWidth = 1.0F;
format.Borders.Color = Color.Black;
//Initialize number of rows and cloumns.
docTable.ResetCells(table.Rows.Count + 1, table.Columns.Count, format, 84);
//Repeat the header.
docTable.Rows[0].IsHeader = true;
string colName;
//Format the header rows
for (int c = 0; c <= table.Columns.Count - 1; c++)
{
string[] Cols = table.Columns[c].ColumnName.Split('|');
colName = Cols[Cols.Length - 1];
IWTextRange theadertext = docTable.Rows[0].Cells[c].AddParagraph().AppendText(colName);
theadertext.CharacterFormat.FontSize = 12f;
theadertext.CharacterFormat.Bold = true;
theadertext.CharacterFormat.TextColor = Color.White;
docTable.Rows[0].Cells[c].CellFormat.BackColor = Color.FromArgb(33, 67, 126);
docTable.Rows[0].Cells[c].CellFormat.Borders.Color = Color.Black;
docTable.Rows[0].Cells[c].CellFormat.Borders.BorderType = Syncfusion.DocIO.DLS.BorderStyle.Single;
docTable.Rows[0].Cells[c].CellFormat.Borders.LineWidth = 1.0f;
docTable.Rows[0].Cells[c].CellFormat.VerticalAlignment = Syncfusion.DocIO.DLS.VerticalAlignment.Middle;
}
//Format the table body rows
for (int r = 0; r <= table.Rows.Count - 1; r++)
{
for (int c = 0; c <= table.Columns.Count - 1; c++)
{
string Value = table.Rows[r][c].ToString();
IWTextRange theadertext = docTable.Rows[r + 1].Cells[c].AddParagraph().AppendText(Value);
theadertext.CharacterFormat.FontSize = 10;
docTable.Rows[r + 1].Cells[c].CellFormat.BackColor = ((r & 1) == 0) ? Color.FromArgb(237, 240, 246) : Color.FromArgb(192, 201, 219);
docTable.Rows[r + 1].Cells[c].CellFormat.Borders.Color = Color.Black;
docTable.Rows[r + 1].Cells[c].CellFormat.Borders.BorderType = Syncfusion.DocIO.DLS.BorderStyle.Single;
docTable.Rows[r + 1].Cells[c].CellFormat.Borders.LineWidth = 0.5f;
docTable.Rows[r + 1].Cells[c].CellFormat.VerticalAlignment = Syncfusion.DocIO.DLS.VerticalAlignment.Middle;
}
}
// Add a footer paragraph text to the document.
WParagraph footerPar = new WParagraph(document);
// Add text.
footerPar.AppendText("Copyright Syncfusion Inc. 2001-2021");
// Add page and Number of pages field to the document.
footerPar.AppendText(" Page ");
footerPar.AppendField("Page", Syncfusion.DocIO.FieldType.FieldPage);
section.HeadersFooters.Footer.Paragraphs.Add(footerPar);
//Save as doc format
if (wordDocRadioBtn.Checked)
{
//Saving the document to disk.
document.Save("Sample.doc");
//Message box confirmation to view the created document.
if (MessageBoxAdv.Show("Do you want to view the generated Word document?", "Document has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
//Launching the MS Word file using the default Application.[MS Word Or Free WordViewer]
System.Diagnostics.Process.Start("Sample.doc");
//Exit
this.Close();
}
}
//Save as docx format
else if (wordDocxRadioBtn.Checked)
{
//Saving the document as .docx
document.Save("Sample.docx", FormatType.Docx);
//Message box confirmation to view the created document.
if (MessageBoxAdv.Show("Do you want to view the generated Word document?", "Document has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
try
{
//Launching the MS Word file using the default Application.[MS Word Or Free WordViewer]
System.Diagnostics.Process.Start("Sample.docx");
//Exit
this.Close();
}
catch (Win32Exception ex)
{
MessageBoxAdv.Show("Microsoft Word Viewer or Microsoft Word is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
}
//Save as pdf format
else if (pdfRadioBtn.Checked)
{
DocToPDFConverter converter = new DocToPDFConverter();
//Convert word document into PDF document
PdfDocument pdfDoc = converter.ConvertToPDF(document);
//Save the pdf file
pdfDoc.Save("Sample.pdf");
//Message box confirmation to view the created document.
if (MessageBoxAdv.Show("Do you want to view the generated PDF?", " Document has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
try
{
System.Diagnostics.Process.Start("Sample.pdf");
//Exit
this.Close();
}
catch (Exception ex)
{
MessageBoxAdv.Show("PDF Viewer is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
}
else
{
// Exit
this.Close();
}
}
catch (Exception Ex)
{
// Shows the Message box with Exception message, if an exception throws.
MessageBoxAdv.Show(Ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
}
}
public void Close()
{
varSqlConnect.Close();
}
private void cikis_Click(object sender, System.EventArgs e)
{
Ceconn.Close();
this.Close();
}
public void loaddata()
{
string sdate = string.Empty;
if (DTPS.Value.Year > 2550)
{
sdate = (DTPS.Value.Year - 543).ToString() + "." + DTPS.Value.Month.ToString() + "." + DTPS.Value.Day.ToString();
}
else
{
sdate = DTPS.Value.Year.ToString() + "." + DTPS.Value.Month.ToString() + "." + DTPS.Value.Day.ToString();
}
SqlCeConnection con = new SqlCeConnection(strcon);
try
{
con.Open();
string sql = "select * from REPORT WHERE location = " + DPCODE_ + " AND workdate = '" + sdate + "' order by ID desc";
SqlCeDataAdapter da = new SqlCeDataAdapter(sql, con);
dataGridItem.DataSource = null;
DataSet ds = new DataSet();
da.Fill(ds, "tt");
DataTable dt = ds.Tables["tt"];
DataGridTableStyle tableStyle = new DataGridTableStyle();
tableStyle.MappingName = dt.TableName;
DataGridTextBoxColumn column = new DataGridTextBoxColumn();
column.MappingName = "ID";
column.HeaderText = "ID";
column.Width = 0;
tableStyle.GridColumnStyles.Add(column);
DataGridTextBoxColumn column2 = new DataGridTextBoxColumn();
column2.MappingName = "assetid";
column2.HeaderText = "รหัส";
column2.Width = 90;
tableStyle.GridColumnStyles.Add(column2);
DataGridTextBoxColumn column3 = new DataGridTextBoxColumn();
column3.MappingName = "name";
column3.HeaderText = "ชื่อทรัพย์สิน";
column3.Width = 170;
tableStyle.GridColumnStyles.Add(column3);
DataGridTextBoxColumn column4 = new DataGridTextBoxColumn();
column4.MappingName = "ctcode";
column4.HeaderText = "ถือครอง";
column4.Width = 100;
tableStyle.GridColumnStyles.Add(column4);
dataGridItem.DataSource = dt;
dataGridItem.TableStyles.Clear();
dataGridItem.TableStyles.Add(tableStyle);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
//throw;
}
finally
{
con.Close();
}
}
private void salvar_button_Click(object sender, EventArgs e)
{
SqlCeConnection connection = new SqlCeConnection(Properties.Settings.Default.DataConnectionString);
connection.Open();
// Confirm save rg and cpf
SqlCeCommand conf = new SqlCeCommand("SELECT * FROM clientes WHERE id = @id", connection);
conf.Parameters.AddWithValue("@id", currentID);
SqlCeDataReader re = conf.ExecuteReader();
if (re.Read())
{
//MessageBox.Show(re["cpf"].ToString());
//MessageBox.Show(re["rg"].ToString());
// Verificação do CPF
if ((re["cpf"].ToString().Length <= 0 && cpf_edit.Text.Length > 0))
{
SqlCeCommand count = new SqlCeCommand("SELECT COUNT(*) FROM [clientes] WHERE ([cpf] = @cpf)", connection);
count.Parameters.AddWithValue("@cpf", Transform.packCPF(cpf_edit.Text));
int exists = (int)count.ExecuteScalar();
if (exists > 0)
{
MessageBox.Show("Esse CPF já está cadastrado em outro cliente.", "CPF inválido", MessageBoxButtons.OK, MessageBoxIcon.Error);
connection.Close();
return;
}
DialogResult m = MessageBox.Show("O CPF que você digitou, não poderá ser modificado.\n'" + cpf_edit.Text + "', Continuar?", "Confirmar CPF", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (m == DialogResult.No)
{
connection.Close(); return;
}
}
// Verificação do RG
if ((re["rg"].ToString().Length <= 0 && rg_edit.Text.Length > 0))
{
SqlCeCommand count = new SqlCeCommand("SELECT COUNT(*) FROM [clientes] WHERE ([rg] = @rg)", connection);
count.Parameters.AddWithValue("@rg", Transform.packRG(rg_edit.Text));
int exists = (int)count.ExecuteScalar();
if (exists > 0)
{
MessageBox.Show("Esse RG já está cadastrado em outro cliente.", "RG inválido", MessageBoxButtons.OK, MessageBoxIcon.Error);
connection.Close();
return;
}
DialogResult m = MessageBox.Show("O RG que você digitou, não poderá ser modificado.\n'" + rg_edit.Text + "', Continuar?", "Confirmar RG", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (m == DialogResult.No)
{
connection.Close(); return;
}
}
// Update
SqlCeCommand cmd = new SqlCeCommand("UPDATE Clientes SET nome = @nome, celular = @celular, rua = @rua, numero = @numero, cidade = @cidade, bairro = @bairro, cpf = @cpf, rg = @rg, estado = @estado WHERE id = @id;", connection);
cmd.Parameters.AddWithValue("@nome", nome_edit.Text);
cmd.Parameters.AddWithValue("@celular", Transform.packPhone(celular_edit.Text));
cmd.Parameters.AddWithValue("@cpf", Transform.packCPF(cpf_edit.Text));
cmd.Parameters.AddWithValue("@rg", Transform.packRG(rg_edit.Text));
cmd.Parameters.AddWithValue("@rua", rua_edit.Text);
cmd.Parameters.AddWithValue("@numero", numero_edit.Text);
cmd.Parameters.AddWithValue("@cidade", cidade_edit.Text);
cmd.Parameters.AddWithValue("@bairro", bairro_edit.Text);
cmd.Parameters.AddWithValue("@estado", estado_comboBox.Text);
cmd.Parameters.AddWithValue("@id", currentID);
cmd.ExecuteNonQuery();
this.updateLista();
this.setBtnMode(0);
}
else
{
MessageBox.Show("Please enter a valid item barcode");
}
re.Close();
connection.Close();
}
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();
}
internal byte[] GetPicure(int id)
{
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
SqlCeDataReader reader = null;
byte[] buffer = null;
try
{
conn = new SqlCeConnection(getConnectionString());
conn.Open();
cmd = new SqlCeCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT id, picture FROM images WHERE id = @id";
//cmd.Parameters.Add(new SqlCeParameter("@id", SqlDbType.Int)); // doesn't work
cmd.Parameters.AddWithValue("@id", id);
//cmd.Parameters.Add("@id", SqlDbType.Int);
//cmd.Parameters[0].Value = id;
reader = cmd.ExecuteReader();
//reader.Read();
//SqlBinary binary;
//SqlBytes bytes;
// if (reader.HasRows) //Does not work for CE
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
id = reader.GetInt32(0);
}
if (!reader.IsDBNull(1))
{
//binary = reader.GetSqlBinary(1);
buffer = (byte[])reader["picture"];
//int maxSize = 200000;
//buffer = new byte[maxSize];
//reader.GetBytes(1, 0L, buffer, 0, maxSize);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
try
{
if (reader != null)
{
reader.Close();
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (conn != null)
{
conn = null;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(buffer);
}
//method that insert a book into the rent book database
public void insertRentBook()
{
if (RBookCover == null)
{
SqlCeConnection databaseConnection = new SqlCeConnection(GlobalVariables.databasePath);
string query = "INSERT INTO RBooks([Book Name],[Book ISBN],[Book Edition],[Number of Pages],[Author],[BookRating],[Audience],[Copyright Holder]," +
"[Editor],[Genre],[Price],[Language],[Illustrator],[About],[Status]) " +
"VALUES (@bookName, @ISBN, @bookEdition, @pageNbr, @author, @rating, @audience, @copyrightHolder, @editor, @genre, @price, @language, " +
"@illustrator, @about,@status)";
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
cmd.Parameters.AddWithValue("@bookName", this.RBookName);
cmd.Parameters.AddWithValue("@ISBN", this.RBookISBN);
cmd.Parameters.AddWithValue("@bookEdition", this.RBookEdition);
cmd.Parameters.AddWithValue("@pageNbr", this.RBookPageNum);
cmd.Parameters.AddWithValue("@author", this.RBookAuthor);
cmd.Parameters.AddWithValue("@rating", this.RBookRating);
cmd.Parameters.AddWithValue("@audience", this.RBookAudience);
cmd.Parameters.AddWithValue("@copyrightHolder", this.RBookCopyRightsHolder);
cmd.Parameters.AddWithValue("@editor", this.RBookEditor);
cmd.Parameters.AddWithValue("@genre", this.RbookGenre);
cmd.Parameters.AddWithValue("@price", this.RBookRentPrice);
cmd.Parameters.AddWithValue("@language", this.RBookLanguage);
cmd.Parameters.AddWithValue("@illustrator", this.RBookIllustrator);
cmd.Parameters.AddWithValue("@about", this.AboutBook);
cmd.Parameters.AddWithValue("@status", "Available");
databaseConnection.Open();
cmd.ExecuteNonQuery();
databaseConnection.Close();
}
else
{
SqlCeConnection databaseConnection = new SqlCeConnection(GlobalVariables.databasePath);
string query = "INSERT INTO RBooks([Book Name],[Book ISBN],[Book Edition],[Number of Pages],[Author],[BookRating],[Audience],[Copyright Holder]," +
"[Editor],[Genre],[Price],[Language],[Illustrator],[About],[Cover],[Status]) " +
"VALUES (@bookName, @ISBN, @bookEdition, @pageNbr, @author, @rating, @audience, @copyrightHolder, @editor, @genre, @price, @language, " +
"@illustrator, @about, @imgcover,@status)";
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
cmd.Parameters.AddWithValue("@bookName", this.RBookName);
cmd.Parameters.AddWithValue("@ISBN", this.RBookISBN);
cmd.Parameters.AddWithValue("@bookEdition", this.RBookEdition);
cmd.Parameters.AddWithValue("@pageNbr", this.RBookPageNum);
cmd.Parameters.AddWithValue("@author", this.RBookAuthor);
cmd.Parameters.AddWithValue("@rating", this.RBookRating);
cmd.Parameters.AddWithValue("@audience", this.RBookAudience);
cmd.Parameters.AddWithValue("@copyrightHolder", this.RBookCopyRightsHolder);
cmd.Parameters.AddWithValue("@editor", this.RBookEditor);
cmd.Parameters.AddWithValue("@genre", this.RbookGenre);
cmd.Parameters.AddWithValue("@price", this.RBookRentPrice);
cmd.Parameters.AddWithValue("@language", this.RBookLanguage);
cmd.Parameters.AddWithValue("@illustrator", this.RBookIllustrator);
cmd.Parameters.AddWithValue("@status", "Available");
cmd.Parameters.AddWithValue("@about", this.AboutBook);
cmd.Parameters.AddWithValue("imgcover", GlobalVariables.coverPath + @"\" + this.RBookName + ".png");
databaseConnection.Open();
cmd.ExecuteNonQuery();
databaseConnection.Close();
//filestream to save the browsed image to the coverpath in appdata
var encoder = new PngBitmapEncoder();
encoder.Frames.Add(BitmapFrame.Create((BitmapSource)this.RBookCover));
using (FileStream stream = new FileStream(GlobalVariables.coverPath + @"\" + this.RBookName + ".png", FileMode.Create))
encoder.Save(stream);
}
}
public void Connection_Close()
{
objConnection.Close();
}
/// <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();
}
