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();
}
}
public void connectToDatabase()
{
mySqlConnection = new SqlCeConnection(@"Data Source=C:\University\Adv Software Engineering\Bug Tracker\BugTracker\BugTracker\BugDatabase.mdf");
String selcmd = "SELECT BugID, LineStart, LineEnd, ProgrammerName, ClassName, MethodName, TimeSubmitted, ProjectName, Description FROM dbo ORDER BY TimeSubmitted";
SqlCeCommand mySqlCommand = new SqlCeCommand(selcmd, mySqlConnection);
}
public static DataTable Select(string query, SqlCeConnection conn)
{
DataTable dt = new DataTable();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(query, conn);
adapter.Fill(dt);
return dt;
}
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();
}
}
public static DataTable ExecuteSqlQuery(string query, params SqlCeParameter[] sqlParams)
{
var dt = new DataTable();
using (var conn = new SqlCeConnection(connStr))
using (var cmd = new SqlCeCommand(query, conn))
{
try
{
SqlCeEngine engine = new SqlCeEngine(conn.ConnectionString);
engine.Upgrade(conn.ConnectionString);
}
catch
{
}
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(sqlParams);
conn.Open();
dt.Load(cmd.ExecuteReader());
}
return dt;
}
public void conectaBD()
{
SqlCeConnection PathBD = new SqlCeConnection("Data Source=C:\\Facturacion\\Facturacion\\BaseDeDatos.sdf;Persist Security Info=False;");
//abre la conexion
try
{
da = new SqlCeDataAdapter("SELECT * FROM USUARIOS ORDER BY ID_USUARIO", PathBD);
// Crear los comandos de insertar, actualizar y eliminar
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
// Asignar los comandos al DataAdapter
// (se supone que lo hace automáticamente, pero...)
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
dt = new DataTable();
// Llenar la tabla con los datos indicados
da.Fill(dt);
PathBD.Open();
}
catch (Exception w)
{
MessageBox.Show(w.ToString());
return;
}
}
public static IDbConnection GetConnection()
{
var connectionString = ConfigProvider.GetConnectionString();
var connection = new SqlCeConnection(connectionString);
connection.Open();
return connection;
}
public void ReadTo(Action<SqlCeDataReader> readerAction)
{
try
{
using (var connection = new SqlCeConnection(ConnectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var command = connection.CreateCommand())
{
command.CommandText = _commandText;
SetParametersToCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
readerAction.Invoke(reader);
}
}
}
}
}
}
catch (SqlCeLockTimeoutException ex)
{
Thread.Sleep(TimeSpan.FromMilliseconds(500));
ReadTo(readerAction);
}
catch (Exception ex)
{
LogOrSendMailToAdmin(ex);
throw;
}
}
private void TestConnection(bool showMessage)
{
try
{
if (createDb)
{
if (!System.IO.File.Exists(builder.DataSource))
{
using (var eng = new SqlCeEngine(builder.ConnectionString))
{
eng.CreateDatabase();
}
}
}
using (var conn = new SqlCeConnection(builder.ConnectionString))
{
conn.Open();
this.ConnectionString = builder.ConnectionString;
if (showMessage)
{
MessageBox.Show("Test succeeded!");
}
else
{
this.DialogResult = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex));
}
}
public void CreateDatabase(string fileName, string password)
{
string connectionString;
if (System.IO.File.Exists(fileName))
return;
//System.IO.File.Delete(fileName);
// The DataSource must be surrounded with double quotes. The Password, on the other hand, must be surrounded
// with single quotes
connectionString = string.Format(
"DataSource=\"{0}\"; Password='******'", fileName, password);
// we'll use the SqlServerCe connection object to get the database file path
using (SqlCeConnection localConnection = new SqlCeConnection(connectionString))
{
// The SqlCeConnection.Database property contains the file parth portion
// of the database from the full connectionstring
if (!System.IO.File.Exists(localConnection.Database))
{
using (SqlCeEngine sqlCeEngine = new SqlCeEngine(connectionString))
{
sqlCeEngine.CreateDatabase();
CreateInitialDatabaseObjects(connectionString);
}
}
}
}
public override void Test()
{
string testString = ToTestString();
// Create a connection object
SqlCeConnection connection = new SqlCeConnection();
// Try to open it
try
{
connection.ConnectionString = ToFullString();
connection.Open();
}
catch (SqlCeException e)
{
// Customize the error message for upgrade required
if (e.Number == m_intDatabaseFileNeedsUpgrading)
{
throw new InvalidOperationException(Resources.SqlCeConnectionProperties_FileNeedsUpgrading);
}
throw;
}
finally
{
connection.Dispose();
}
}
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");
}
}
private void button2_Click(object sender, EventArgs e)
{
DataConnectionDialog dcd = new DataConnectionDialog();
DataConnectionConfiguration dcs = new DataConnectionConfiguration(null);
dcs.LoadConfiguration(dcd);
if (DataConnectionDialog.Show(dcd) == DialogResult.OK)
{
textBox2.Text = dcd.ConnectionString;
connectionString = dcd.ConnectionString;
comboBox1.Enabled = true;
using (SqlCeConnection con = new SqlCeConnection(connectionString))
{
comboBox1.Items.Clear();
con.Open();
using (SqlCeCommand command = new SqlCeCommand("SELECT table_name FROM INFORMATION_SCHEMA.Tables", con))
{
SqlCeDataReader reader = command.ExecuteReader();
while (reader.Read())
{
comboBox1.Items.Add(reader.GetString(0));
}
}
}
//textBox1.Text = dcd.SelectedDataSource.DisplayName;
}
dcs.SaveConfiguration(dcd);
}
public ModuleMainForm()
{
InitializeComponent();
try
{
// make data folder for this module
Common.MakeAllSubFolders(Static.DataFolderPath);
// check sdf database file, and copy new if dont exists
Static.CheckDB_SDF();
conn = new SqlCeConnection(Static.ConnectionString);
command = new SqlCeCommand("", conn);
dgwPanel.Columns.Add("tag", "Tag");
dgwPanel.Columns.Add("info", "Info");
DataGridViewButtonColumn btnColl1 = new DataGridViewButtonColumn();
btnColl1.HeaderText = "Edit";
btnColl1.Name = "Edit";
dgwPanel.Columns.Add(btnColl1);
keyEventsArgs = new KeyEventArgs(Keys.Oemtilde | Keys.Control);
HookManager.KeyDown += new KeyEventHandler(HookManager_KeyDown);
}
catch (Exception exc)
{
Log.Write(exc, this.Name, "ModuleMainForm", Log.LogType.ERROR);
}
}
internal void InsertCmd(Cmd cmd)
{
SqlCeConnection con = new SqlCeConnection(connectionString);
try
{
con.Open();
//SqlCeDataAdapter adapter = new SqlCeDataAdapter(
// "insert into cmd (name, description, path, arg) values (" +
// "'" + cmd.name + "'," +
// "'" + cmd.description + "'," +
// "'" + cmd.path + "'," +
// "'" + cmd.arg + "'" +
// ")", con);
//SqlCeCommand sqlCmd = adapter.InsertCommand;
//int count = sqlCmd.ExecuteNonQuery();
SqlCeCommand sqlCmd = con.CreateCommand();
sqlCmd.CommandText = "insert into cmd (name, description, path, arg) values (" +
"'" + cmd.name + "'," +
"'" + cmd.description + "'," +
"'" + cmd.path + "'," +
"'" + cmd.arg + "'" +
")";
int count = sqlCmd.ExecuteNonQuery();
}
finally
{
if (con != null)
{
con.Close();
}
}
}
private void refresh()
{
odData = odBox.Text;
doData = doBox.Text;
try
{
conn = new SqlCeConnection(connectionString);
SqlCeDataAdapter dataadapter = new SqlCeDataAdapter("Select * from Seriale WHERE Data_Premiery BETWEEN '" + odData + "' AND '"
+ doData + "'", conn);
DataSet ds = new DataSet();
conn.Open();
dataadapter.Fill(ds, "Seriale");
conn.Close();
tabela.DataSource = ds;
tabela.DataMember = "Seriale";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (conn != null) conn.Close();
}
}
public void Commit()
{
var connectionString = repository.GetConnectionString();
using (var conn = new SqlCeConnection(connectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
foreach (var command in commands)
{
try
{
SQLCeHelper.ResetParameterNullValue(command);
command.Transaction = trans;
command.Connection = conn;
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new KoobooException(e.Message + "SQL:" + command.CommandText, e);
}
}
trans.Commit();
}
}
//Execute post content events
foreach (var action in postActions)
{
action();
}
Clear();
}
/*
public List<String> getControls(Object PtID, Object EqID)
{
List<String> controls = new List<String>();
controls.Add("RateButton");
controls.Add("Lever");
return controls;
}
*/
public List<String> getControls(Object PatientID, Object EqID, SqlCeConnection thisConnection)
{
int PatID = 0, EquipmentID = 0;
List<String> controls = new List<String>();
try
{
SqlCeCommand cmd = thisConnection.CreateCommand();
cmd.CommandText = "SELECT ID FROM MarkerInventory WHERE MarkerID=\'" + PatientID.ToString() + "\'";
SqlCeDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
PatID = (int)rdr["ID"];
}
cmd.CommandText = "SELECT ID FROM MarkerInventory WHERE MarkerID=\'" + EqID.ToString() + "\'";
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
EquipmentID = (int)rdr["ID"];
}
cmd.CommandText = "SELECT Control FROM Patient WHERE PtID=" + PatID + " and EquipID=" + EquipmentID;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
controls.Add((String)rdr["Control"]);
}
rdr.Close();
cmd.Dispose();
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
return controls;
}
public override void CreateSyncRepository()
{
string connectionString = "Data Source=\"" + _DbPath + "\";Max Database Size=128;Default Lock Escalation=100;";
IDbConnection conn = new SqlCeConnection(connectionString);
conn.Open();
IDbTransaction t = conn.BeginTransaction();
IDbCommand com = conn.CreateCommand();
com.Transaction = t;
StringBuilder createSyncItem = new StringBuilder();
createSyncItem.Append("CREATE TABLE SyncItem").
Append("(SyncID INT PRIMARY KEY IDENTITY, SyncFK INT, ClassID nvarchar(255), HashCode nvarchar(32), ").
Append("SyncGuid UNIQUEIDENTIFIER, RowGuid UNIQUEIDENTIFIER)");
com.CommandText = createSyncItem.ToString();
com.ExecuteNonQuery();
StringBuilder createFieldState = new StringBuilder();
createFieldState.Append("CREATE TABLE FieldState").
Append("(SyncFK INT, FieldName nvarchar(255), HashCode nvarchar(32), ").
Append("RowGuid UNIQUEIDENTIFIER, PRIMARY KEY (SyncFK, FieldName))");
com.CommandText = createFieldState.ToString();
com.ExecuteNonQuery();
t.Commit();
conn.Close();
conn.Dispose();
}
public static TPunto GetTPunto(int id, SqlCeConnection conn)
{
TPunto p = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
string sql = @"SELECT p.puntoId, p.nombre, p.edificioId, p.tag, p.cota, p.cubiculo, p.observaciones,
e.nombre AS enombre, e.grupoId, g.nombre AS gnombre, p.csnmax, p.csnmargen, p.lastcontrol
FROM puntos AS p
LEFT OUTER JOIN edificios AS e ON e.edificioId = p.edificioId
LEFT OUTER JOIN grupos AS g ON g.grupoId = e.grupoId
WHERE p.puntoId= {0}";
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = String.Format(sql, id);
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
p = GetPuntoFromDr(dr);
}
if (!dr.IsClosed)
dr.Close();
}
}
return p;
}
public void AlimentaSaida(ref SqlCeConnection conexao, string lista, string nrmapa)
{
string DsList = lista;
const string dscabec = "NRGTIN;DSPRODUT;QTPRODUT;NRLOTE;DTVALID";
char[] DsSepLin = { '#' };
char[] DsSepVal = { ';' };
string[] lines;
string[] values;
DsList = DsList.Replace("\r\n", "#");
DsList = DsList.Replace("##", "#");
lines = DsList.Split(DsSepLin);
if (dscabec.ToUpper() != lines[0].ToUpper())
{
throw new Exception(String.Format("Lista de Saida não conforme: {0}", lines[0]));
}
Clear(ref conexao);
for (int idx = 1; idx < lines.Length; idx++)
{
if (lines[idx].Trim() != string.Empty)
{
values = lines[idx].Split(DsSepVal);
Insert(ref conexao, values, nrmapa, idx);
}
}
}
public static void SetBase(bool Dummy,string path)
{
string constr = "Data Source = \"{0}\"; Password =\"pdabase\"";
try
{
SqlCeConnection con = new SqlCeConnection(string.Format(constr, path));
con.Open();
//SqlCeCommand q=con.CreateCommand();
SqlCeCommand q;
if (Dummy)
{
q = new SqlCeCommand(string.Format(sql, 0), con);
q.ExecuteNonQuery();
}
else
{
q = new SqlCeCommand(string.Format(sql, 1), con);
q.ExecuteNonQuery();
}
con.Close();
}
catch (SqlCeException e)
{
Consoler(e.Message);
Log(e.Message,logpath);
}
}
//add issuance
public bool addIssuance(DateTime issuance_date, string license_no,
int violation_id, decimal price, int officer_id,
int issuance_status)
{
string sql_stmt;
bool result = false;
/**
sql_stmt = "CREATE TABLE issuances " +
"( " +
" issuance_date datetime," +
" license_no nvarchar(20)," +
" violation_id int," +
" price money," +
" officer_id int," +
" issuance_status int" +
") ";
**/
sql_stmt = "INSERT INTO issuances " +
" (issuance_date,license_no,violation_id,price,officer_id,issuance_status) " +
"VALUES " +
" (@id, @violation, @desc, @price, @last_mod)";
SqlCeConnection conn = new SqlCeConnection(this.conn_string);
conn.Open();
try
{
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = sql_stmt;
cmd.Parameters["issuance_date"].SqlDbType = System.Data.SqlDbType.DateTime;
cmd.Parameters["issuance_date"].Value = issuance_date;
cmd.Parameters["license_no"].SqlDbType = System.Data.SqlDbType.NVarChar;
cmd.Parameters["license_no"].Value = license_no;
cmd.Parameters["violation_id"].SqlDbType = System.Data.SqlDbType.Int;
cmd.Parameters["violation_id"].Value = violation_id;
cmd.Parameters["price"].SqlDbType = System.Data.SqlDbType.Money;
cmd.Parameters["price"].Value = price;
//cmd.Parameters["officer_id"].SqlDbType = System.Data.SqlDbType.Int;
//cmd.Parameters["officer_id"].Value = last_mod;
cmd.Parameters["issuance_status"].SqlDbType = System.Data.SqlDbType.Int;
cmd.Parameters["issuance_status"].Value = issuance_status;
result = (cmd.ExecuteNonQuery() > 0);
}
catch (Exception err)
{
string msg = err.Message;
result = false;
}
conn.Close();
return result;
}
protected override DbConnection CreateConnection(Type t, string connectionString)
{
SqlCeConnection connection = null;
try
{
connection = new SqlCeConnection(connectionString);
connection.Open();
}
catch (System.Data.SqlServerCe.SqlCeInvalidDatabaseFormatException)
{
try
{
var engine = new SqlCeEngine(connectionString);
engine.Upgrade();
try
{
connection = new SqlCeConnection(connectionString);
connection.Open();
}
catch (System.Exception){}
}
catch (System.Exception ex)
{
Console.WriteLine("Attempt on Upgrading SQL CE Database Failed (Reason = \"" + ex.Message + "\")");
}
}
catch (Exception ex) { Console.WriteLine("Unexpected Error Occurred ! Error Details : " + ex.Message); }
return connection;
}
public static Guid GetApplicationId(string connectionString, string applicationName)
{
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand("SELECT ApplicationId FROM [aspnet_Applications] " +
"WHERE ApplicationName = @ApplicationName", conn))
{
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName;
conn.Open();
var applicationId = cmd.ExecuteScalar();
if (applicationId == null)
{
cmd.Parameters.Clear();
cmd.CommandText = "INSERT INTO [aspnet_Applications] (ApplicationId, ApplicationName, LoweredApplicationName, Description) VALUES (@ApplicationId, @ApplicationName, @LoweredApplicationName, @Description)";
applicationId = Guid.NewGuid();
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = applicationId;
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName;
cmd.Parameters.Add("@LoweredApplicationName", SqlDbType.NVarChar, 256).Value = applicationName.ToLowerInvariant();
cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 256).Value = String.Empty;
cmd.ExecuteNonQuery();
}
return (Guid)applicationId;
}
}
}
public List<ErrorLog> GetLogs()
{
var result = new List<ErrorLog>();
IEnumerable<string> logs;
using (IDbConnection connection = new SqlCeConnection(Connection))
{
connection.Open();
var query = _settingsManager.GetMaxNumberOfLogs() > -1 ?
string.Format("SELECT TOP {0} [AllXml] FROM [ELMAH_Error] ORDER BY [Sequence] DESC;", _settingsManager.GetMaxNumberOfLogs()) :
"SELECT [AllXml] FROM [ELMAH_Error] ORDER BY [Sequence] DESC";
logs = connection.Query<string>(query);
}
foreach (var log in logs)
{
var errorLog = _parser.Parse(log);
if (errorLog == null)
{
_log.Error(string.Format("Failed to parse file: {0}", log));
continue;
}
result.Add(errorLog);
}
return result;
}
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 void InitTestSchema()
{
var connStr = String.Format("Data Source = '{0}';", _testDb);
using (var conn = new SqlCeConnection(connStr))
{
conn.Open();
var command = new SqlCeCommand();
command.Connection = conn;
command.CommandText =
@"CREATE TABLE accel_data (
id INT IDENTITY NOT NULL PRIMARY KEY,
date DATETIME,
Ax Float,Ay Float
)";
command.ExecuteNonQuery();
command.CommandText = @"CREATE TABLE accel_params (
id INT IDENTITY NOT NULL PRIMARY KEY,
date DATETIME,
sensorNumber smallint,
offsetX Float,offsetY Float,
gravityX Float,gravityY Float
)";
command.ExecuteNonQuery();
command.CommandText = @"CREATE TABLE calibr_result (
id INT IDENTITY NOT NULL PRIMARY KEY,
accelDataId INT,
accelParamsId INT
)";
command.ExecuteNonQuery();
}
}
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;
}
}
private void setprods()
{
//max
DataTable dtp = new DataTable();
int f = 0;
string SqlString = "Select max([Product Serial Number]) From Logs_prod";
if (Main.Amatrix.mgt == "")
{
using (SqlCeConnection conn = new SqlCeConnection(logs_prodTableAdapter.Connection.ConnectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
SqlCeDataReader reader = cmd.ExecuteReader();
using (reader)
{
dtp.Load(reader);
}
conn.Close();
}
}
}
else
{
using (SqlConnection conn = new SqlConnection(Main.Amatrix.mgt))
{
using (SqlCommand cmd = new SqlCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
using (reader)
{
dtp.Load(reader);
}
conn.Close();
}
}
}
try
{
f = Convert.ToInt32(dtp.Rows[0].ItemArray[0].ToString());
}
catch (Exception erty) { f = 0; }
DataRow dgvrow;
DataTable dtp_sve = new DataTable();
foreach (DataGridViewRow dgvr in dataGridView2.SelectedRows)
{
try
{
f = f + 1;
dgvrow = logs_prod2.Logs_prod.NewRow();
dgvrow[0] = f;
dgvrow[1] = dgvr.Cells[1].Value.ToString();
dgvrow[2] = ID;
dgvrow[3] = dataGridView1[0, dataGridView1.CurrentRow.Index].Value.ToString();
dgvrow[4] = dataGridView1[1, dataGridView1.CurrentRow.Index].Value.ToString();
logs_prod2.Logs_prod.Rows.Add(dgvrow);
dataGridView3.DataSource = logs_prod2;
if (Main.Amatrix.mgt == "")
{
logs_prodTableAdapter.Update(logs_prod2);
}
else
{
asql.Save(logs_prod2.Logs_prod, "Logs_prod", Main.Amatrix.mgt);
}
}
catch (Exception erty) { break; Am_err ner = new Am_err(); ner.tx("You May not Choose a Mainstream Product Please Pick a Bulk Item or create one For Your Inventory in the Inventory Information tab within Product Managment."); }
}
}
/// <summary>
/// Enumerates execute expando objects in this collection.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>
/// An enumerator that allows foreach to be used to process execute expando objects in this collection.
/// </returns>
public static IEnumerable <dynamic> ExecuteExpandoObjects(this SqlCeConnection @this, string cmdText, SqlCeTransaction transaction)
{
return(@this.ExecuteExpandoObjects(cmdText, null, CommandType.Text, transaction));
}
public Sqlmanager(string path)
{
_connection = new SqlCeConnection(string.Format(Resources.SQLConnect, path));
}
/// <summary>
/// Función que guarda un PreferenceEntity en la base de datos.
/// </summary>
/// <param name="preference">PreferenceEntity a guardar</param>
/// <param name="scope">Estructura interna para evitar problemas con referencias circulares</param>
/// <exception cref="ArgumentNullException">
/// Si <paramref name="preference"/> no es un <c>PreferenceEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// Si una DbException ocurre cuando se accede a la base de datos
/// </exception>
public void Save(PreferenceEntity preference, Dictionary <string, IEntity> scope)
{
if (preference == null)
{
throw new ArgumentException("The argument can't be null");
}
// Crear una clave unica para identificar el objeto dentro del scope interno
string scopeKey = preference.Id.ToString(NumberFormatInfo.InvariantInfo) + "Preference";
if (scope != null)
{
// Si se encuentra dentro del scope lo retornamos
if (scope.ContainsKey(scopeKey))
{
return;
}
}
else
{
// Crea un nuevo scope si este no fue enviado
scope = new Dictionary <string, IEntity>();
}
try
{
// Crea una nueva conexion y una nueva transaccion si no hay una a nivel superior
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
string commandName = "";
bool isUpdate = false;
// Verifica si se debe hacer una actualización o una inserción
if (preference.IsNew || !DataAccessConnection.ExistsEntity(preference.Id, "Preference", "idPreference", dbConnection, dbTransaction))
{
commandName = "INSERT INTO [Preference] (idPreference, ACTIVE, LEVEL, IDCUSTOMER, IDCATEGORY, [TIMESTAMP] ) VALUES( @idPreference, @active,@level,@idCustomer,@idCategory, GETDATE()); ";
}
else
{
isUpdate = true;
commandName = "UPDATE [Preference] SET active = @active, level = @level, idCustomer = @idCustomer, idCategory = @idCategory , timestamp=GETDATE() WHERE idPreference = @idPreference";
}
// Se crea un command
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(commandName, dbConnection, dbTransaction);
// Agregar los parametros del command .
SqlCeParameter parameter;
if (!isUpdate && preference.Id == 0)
{
preference.Id = DataAccessConnection.GetNextId("idPreference", "Preference", dbConnection, dbTransaction);
}
parameter = dataAccess.GetNewDataParameter("@idPreference", DbType.Int32);
parameter.Value = preference.Id;
sqlCommand.Parameters.Add(parameter);
FillSaveParameters(preference, sqlCommand);
// Ejecutar el command
sqlCommand.ExecuteNonQuery();
scopeKey = preference.Id.ToString(NumberFormatInfo.InvariantInfo) + "Preference";
// Agregar la entidad al scope actual
scope.Add(scopeKey, preference);
// Guarda las colecciones de objetos relacionados.
// Guardar objetos relacionados con la entidad actual
if (preference.Category != null)
{
CategoryDataAccess categoryDataAccess = new CategoryDataAccess();
categoryDataAccess.SetConnectionObjects(dbConnection, dbTransaction);
categoryDataAccess.Save(preference.Category, scope);
}
// Actualizar
Update(preference);
// Cierra la conexión si fue abierta en la función
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Actualizar los campos new y changed
preference.IsNew = false;
preference.Changed = false;
}
catch (DbException dbException)
{
// Anula la transaccion
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Relanza una excepcion personalizada
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Cierra la conexión si fue inicializada
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
public string checkScanItem(string itemNum, string batchNum, string itemActivity, SqlCeConnection con)
{
string strMasterAction = string.Empty;
try
{
SqlCeDataReader dr1 = default(SqlCeDataReader);
string strcmd1 = "select ItemNum, BatchNum from ItemMaster where ItemNum = '" + itemNum + "' and BatchNum = '" + batchNum + "'";
SqlCeCommand cmd1 = new SqlCeCommand(strcmd1, con);
dr1 = cmd1.ExecuteReader();
if (dr1.Read())
{
strMasterAction = "N";
}
else
{
string strcmd2 = "select ItemNum from ItemMaster where ItemNum = '" + itemNum + "'";
SqlCeCommand cmd2 = new SqlCeCommand(strcmd2, con);
SqlCeDataReader dr2 = default(SqlCeDataReader);
dr2 = cmd2.ExecuteReader();
if (dr2.Read())
{
if (itemActivity == "S")
{
strMasterAction = "A";
}
else
{
strMasterAction = "E";
}
}
else
{
strMasterAction = "E";
}
}
}
catch (Exception ex)
{
throw ex;
}
return(strMasterAction);
}
/// <summary>
/// Enumerates execute expando objects in this collection.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <returns>
/// An enumerator that allows foreach to be used to process execute expando objects in this collection.
/// </returns>
public static IEnumerable <dynamic> ExecuteExpandoObjects(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType)
{
return(@this.ExecuteExpandoObjects(cmdText, parameters, commandType, null));
}
} // Save
public static void SaveTo(this RecordTask task, SqlCeConnection cn)
{
var saveCmd = GetDbSaveCommand(task);
DbServices.Save(cn, saveCmd, "@XmlData", task);
} // SaveTo
public void DoSearch()
{
var sign = txtSearch.Text.Substring(0, 4).ToUpper();
var number = Int32.Parse(txtSearch.Text.Substring(4, 6));
var check = txtSearch.Text.Substring(10, 1);
DataRow row;
using (var connection = new SqlCeConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))
using (var command = new SqlCeCommand("SELECT Sign, Number, Checkdigit, Type FROM Containers WHERE Sign=@Sign AND Number=@Number AND Checkdigit=@Checkdigit", connection))
{
command.Parameters.AddWithValue("Sign", sign);
command.Parameters.AddWithValue("Number", number);
command.Parameters.AddWithValue("Checkdigit", check);
connection.Open();
using (var reader = command.ExecuteReader())
{
var table = new DataTable();
table.Load(reader);
row = table.Rows.Count == 0 ? null : table.Rows[0];
}
}
var container = row == null ? null : new Container(row["Sign"].ToString(),
Int32.Parse(row["Number"].ToString()), row["Checkdigit"].ToString(), containerDescriptions[row["Type"].ToString()],
Path.Combine(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Images"), row["Type"] + ".gif"));
if (container != null)
{
containerDetails.DataContext = container;
containerDetails.Visibility = Visibility.Visible;
}
else
{
containerDetails.DataContext = null;
containerDetails.Visibility = Visibility.Collapsed;
}
DataTable table1;
using (var connection = new SqlCeConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))
using (var command = new SqlCeCommand("SELECT s.Type, s.Seal, s.Weight, s.ToPort, s.FromPort, s.Ship, s.Forwarder, s.BoardingDate, s.LandingDate FROM Shippings AS s INNER JOIN Containers AS c ON s.ContainerId = c.ContainerId WHERE c.Sign=@Sign AND c.Number=@Number AND c.Checkdigit=@Checkdigit", connection))
{
command.Parameters.AddWithValue("Sign", sign);
command.Parameters.AddWithValue("Number", number);
command.Parameters.AddWithValue("Checkdigit", check);
connection.Open();
using (var reader = command.ExecuteReader())
{
table1 = new DataTable();
table1.Load(reader);
}
}
var shippings = new List <Shipping>();
foreach (DataRow row1 in table1.Rows)
{
var boardingDate = (Convert.IsDBNull(row1["BoardingDate"])) ? null : (DateTime?)Convert.ToDateTime(row1["BoardingDate"]);
var landingDate = (Convert.IsDBNull(row1["LandingDate"])) ? null : (DateTime?)Convert.ToDateTime(row1["LandingDate"]);
var port = row1["Type"].ToString() == "E" ? row1["ToPort"].ToString() : row1["FromPort"].ToString();
var date = row1["Type"].ToString() == "E" ? boardingDate.GetValueOrDefault() : landingDate.GetValueOrDefault();
var s = new Shipping(shippingDescriptions[row1["Type"].ToString()],
Int32.Parse(row1["Seal"].ToString()),
Int32.Parse(row1["Weight"].ToString()),
port, row1["Ship"].ToString(),
date, row1["Forwarder"].ToString());
shippings.Add(s);
}
if (shippings.Count > 0)
{
lstShippings.ItemsSource = shippings;
shippingsList.Visibility = Visibility.Visible;
}
else
{
lstShippings.DataContext = null;
shippingsList.Visibility = Visibility.Collapsed;
}
}
private async Task <IEnumerable <Entry> > GetEntriesAsync(SqlCeConnection connection)
{
return(await connection.QueryAsync <Entry>("SELECT * FROM [Entry]"));
}
//public string checkScanItemNum(string itemNum)
//{
// string strMasterAction = string.Empty;
// try
// {
// using (SqlCeConnection con = new SqlCeConnection(strConn))
// {
// con.Open();
// string strcmd = "select ItemNum from ItemMaster where ItemNum = '" + itemNum + "'";
// SqlCeCommand cmd = new SqlCeCommand(strcmd, con);
// SqlCeDataReader dr = default(SqlCeDataReader);
// dr = cmd.ExecuteReader();
// if (dr.Read())
// {
// if (dr[0] == DBNull.Value)
// strMasterAction = "E";
// else
// strMasterAction = "A";
// }
// con.Close();
// }
// }
// catch (Exception ex)
// {
// throw ex;
// }
// return strMasterAction;
//}
public string insertScanItem(string itemNum, string batchNum, string custName, string locationName, string itemActivity, int itemQuantity, string scannerID, string userID, string masterAction, SqlCeConnection con)
{
string strItemID = string.Empty;
try
{
string strcmd1
= "insert into HScanItems(Activity, Quantity, CustomerName, LocationName, ScannerID, UserID, ScanDate, ItemNum, BatchNum,MasterAction) values ('" + itemActivity + "','" + itemQuantity + "','" + custName + "','" + locationName + "','" + scannerID + "','" + userID + "','" + System.DateTime.Now + "','" + itemNum + "','" + batchNum + "','" + masterAction + "')";
SqlCeCommand cmd1 = new SqlCeCommand(strcmd1, con);
cmd1.ExecuteNonQuery();
//if (currentItemID == "")
// strItemID = "1";
//else
//{
// strItemID = (Int32.Parse(currentItemID) + 1).ToString();
//}
string strcmd2 = "SELECT @@IDENTITY";
SqlCeCommand cmd2 = new SqlCeCommand(strcmd2, con);
strItemID = cmd2.ExecuteScalar().ToString();
}
catch (Exception ex)
{
strItemID = string.Empty;
throw ex;
}
return(strItemID);
}
private void btsaque_Click(object sender, EventArgs e)
{
try
{
SqlCeConnection conexao = new SqlCeConnection(@"Data Source = C:\Users\thale\Desktop\Curso C#\Databases\Banco Digital.sdf" + "; Password = '******'");
conexao.Open();
string query = "SELECT * FROM Cliente";
SqlCeDataAdapter adaptador = new SqlCeDataAdapter(query, conexao);
DataTable dados = new DataTable();
adaptador.Fill(dados);
SqlCeCommand comando = new SqlCeCommand();
comando.Connection = conexao;
foreach (DataRow linha in dados.Rows)
{
string num_conta = linha["num_conta"].ToString();
string senha = linha["senha"].ToString();
if ((num_conta == tbnum_conta.Text) && (senha == tbsenha.Text))
{
float valor = float.Parse(tbvalor.Text);
float saldo = float.Parse(linha["saldo"].ToString());
string tipo_conta = linha["tipo_conta"].ToString();
if (tipo_conta == "Especial")
{
float saldo_atual = saldo - valor;
//parametros
comando.Parameters.AddWithValue("@saldo", saldo_atual);
comando.Parameters.AddWithValue("@num_conta", num_conta);
comando.Parameters.AddWithValue("@senha", senha);
//texto da query
comando.CommandText = "UPDATE Cliente SET saldo = @saldo WHERE num_conta = @num_conta AND senha = @senha";
comando.ExecuteNonQuery();
comando.Dispose();
conexao.Dispose();
tbnum_conta.Text = "";
tbvalor.Text = "";
tbsenha.Text = "";
tbnum_conta.Focus();
MessageBox.Show("Saque realizado com Sucesso.", "Sucesso", MessageBoxButtons.OK);
}
else if (saldo >= valor)
{
float saldo_atual = saldo - valor;
//parametros
comando.Parameters.AddWithValue("@saldo", saldo_atual);
comando.Parameters.AddWithValue("@num_conta", num_conta);
comando.Parameters.AddWithValue("@senha", senha);
//texto da query
comando.CommandText = "UPDATE Cliente SET saldo = @saldo WHERE num_conta = @num_conta AND senha = @senha";
comando.ExecuteNonQuery();
comando.Dispose();
conexao.Dispose();
tbnum_conta.Text = "";
tbvalor.Text = "";
tbsenha.Text = "";
tbnum_conta.Focus();
MessageBox.Show("Saque realizado com Sucesso.", "Sucesso", MessageBoxButtons.OK);
}
else
{
MessageBox.Show("Saldo insulficiente.", "Erro", MessageBoxButtons.OK);
return;
}
}
}
}
catch
{
MessageBox.Show("Aconteceu um erro na conexão com o Banco de Dados", "Erro", MessageBoxButtons.OK);
}
}
} // Load
public static RecordTask LoadFromDatabase(SqlCeConnection cn, Guid taskId)
{
return(DbServices.Load <RecordTask>(cn, GetDbLoadCommand(taskId), "XmlData"));
} // Load
/// <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();
}
