private void buttonInfoMessage_Click(object sender, EventArgs e)
{
/**** Create this Store Procedure in the tempdb database first ****
Create Proc TestInfoMessage
as
Print 'Using a Print Statement'
RaisError('RaisError in Stored Procedures', 9, 1)
****************************************************/
//1. Make a Connection
System.Data.OleDb.OleDbConnection objOleCon = new System.Data.OleDb.OleDbConnection();
objOleCon.ConnectionString = strConnection;
objOleCon.Open();
objOleCon.InfoMessage += new System.Data.OleDb.OleDbInfoMessageEventHandler(objOleCon_InfoMessage);
//2. Issue a Command
System.Data.OleDb.OleDbCommand objCmd;
objCmd = new System.Data.OleDb.OleDbCommand("Raiserror('Typical Message Goes Here', 9, 1)", objOleCon);
objCmd.ExecuteNonQuery();
objCmd = new System.Data.OleDb.OleDbCommand("Exec TestInfoMessage", objOleCon); //This executes the stored procedure.
objCmd.ExecuteNonQuery();
//3. Process the Results
/** No Results at this time **/
//4. Clean up code
objOleCon.Close();
}
protected void btnGo_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
string sql = "";
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.OleDb.OleDbDataReader dr;
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
//http://www.c-sharpcorner.com/UploadFile/dchoksi/transaction02132007020042AM/transaction.aspx
//get this from connectionstrings.com/access
conn.ConnectionString = txtConnString.Text;
conn.Open();
//here I can talk to my db...
System.Data.OleDb.OleDbTransaction Trans;
Trans = conn.BeginTransaction(System.Data.IsolationLevel.Chaos);
try
{
comm.Connection = conn;
comm.Transaction = Trans;
// Trans.Begin();
//Console.WriteLine(conn.State);
sql = txtSQL.Text;
comm.CommandText = sql;
if (sql.ToLower().IndexOf("select") == 0)
{
dr = comm.ExecuteReader();
while (dr.Read())
{
txtresults.Text = dr.GetValue(0).ToString();
}
}
else
{
txtresults.Text = comm.ExecuteNonQuery().ToString();
}
Trans.Commit();
}
catch (Exception ex)
{
txtresults.Text = ex.Message;
Trans.Rollback();
}
finally
{
comm.Dispose();
conn.Close();
conn = null;
}
}
public int AddEvent(String eventName, String eventRoom,
String eventAttendees, String eventDate)
{
System.Data.OleDb.OleDbConnection oleDbConnection1;
System.Data.OleDb.OleDbDataAdapter daEvents;
DataSet ds;
oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\\Inetpub\\wwwroot\\PCSWebApp3\\PCSWebApp3.mdb";
String oleDbCommand = "INSERT INTO Events (Name, Room, AttendeeList," +
" EventDate) VALUES ('" + eventName + "', '" +
eventRoom + "', '" + eventAttendees + "', '" +
eventDate + "')";
System.Data.OleDb.OleDbCommand insertCommand =
new System.Data.OleDb.OleDbCommand(oleDbCommand,
oleDbConnection1);
oleDbConnection1.Open();
int queryResult = insertCommand.ExecuteNonQuery();
if (queryResult == 1)
{
daEvents = new System.Data.OleDb.OleDbDataAdapter(
"SELECT * FROM Events", oleDbConnection1);
ds = (DataSet)Application["ds"];
ds.Tables["Events"].Clear();
daEvents.Fill(ds, "Events");
Application.Lock();
Application["ds"] = ds;
Application.UnLock();
oleDbConnection1.Close();
}
return queryResult;
}
protected void btnInsert_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection();
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("app_data/products.mdb");
Conn.Open();
//Response.Write(Conn.State);
string strSQL = "insert into [users] ([username], firstname, lastname, email, [password]) values (?,?,?,?,?)";
object returnVal;
System.Data.OleDb.OleDbCommand Comm = new System.Data.OleDb.OleDbCommand();
Comm.Connection = Conn;
Comm.CommandText = "select [username] from [users] where [username] = ?";
Comm.Parameters.AddWithValue("[username]", txtUserName.Text);
returnVal = Comm.ExecuteScalar();
if (returnVal == null)
{
Comm.Parameters.Clear();
Comm.CommandText = strSQL;
Comm.Parameters.AddWithValue("username", txtUserName.Text);
Comm.Parameters.AddWithValue("firstname", txtFName.Text);
Comm.Parameters.AddWithValue("lastname", txtLName.Text);
Comm.Parameters.AddWithValue("email", txtEmail.Text);
Comm.Parameters.AddWithValue("password", txtPassword.Text);
Comm.ExecuteNonQuery();
}
else {
Response.Write("Username already exists.");
}
Conn.Close();
Conn = null;
}
public void insertImage(string wkImg)
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandText = "Insert into [" + TableName + "] (imageName) values('" + wkImg + "') ";
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
private void btnGo_Click(object sender, EventArgs e)
{
lblError.Text = "";
//open connection to database
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = txtConnString.Text;
conn.Open();
// MessageBox.Show(conn.State.ToString());
//create my command
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
comm.Connection = conn;
//send the sql through the command
comm.CommandText = txtSQL.Text;
//receive the result into a data container.
System.Data.OleDb.OleDbDataReader dr;
try
{
if (txtSQL.Text.ToUpper().StartsWith("SELECT"))
{
dr = comm.ExecuteReader();
System.Data.DataTable dt = new DataTable();
dt.Load(dr);
Grid1.AutoGenerateColumns = true;
//bind the result to the grid
Grid1.DataSource = dt;
}
else
{
MessageBox.Show(comm.ExecuteNonQuery().ToString());
}
}
catch (Exception ex)
{
lblError.Text = ex.Message;
}
finally
{
conn.Close();
}
}
static void Main(string[] args)
{
string connstring =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DropBox\My Dropbox\Devry\CIS407\SU10B\day5\NorthWind.mdb;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
string sql = "";
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.OleDb.OleDbDataReader dr;
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
try
{
//get this from connectionstrings.com/access
conn.ConnectionString = connstring;
conn.Open();
//here I can talk to my db...
comm.Connection = conn;
//Console.WriteLine(conn.State);
sql = Console.ReadLine();
comm.CommandText = sql;
if (sql.ToLower().IndexOf("select") == 0)
{
dr = comm.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(dr.GetString(0));
}
}
else
{
Console.WriteLine(comm.ExecuteNonQuery().ToString());
}
}
catch ( Exception ex)
{
Console.WriteLine(ex.Message);
Console.Read();
}
finally
{
Console.ReadLine();
comm.Dispose();
conn.Close();
conn = null;
}
}
protected void btnGo_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
string sql = "";
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.OleDb.OleDbDataReader dr;
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
try
{
//get this from connectionstrings.com/access
conn.ConnectionString = txtConnString.Text;
conn.Open();
//here I can talk to my db...
comm.Connection = conn;
//Console.WriteLine(conn.State);
sql = txtSQL.Text;
comm.CommandText = sql;
if (sql.ToLower().IndexOf("select") == 0)
{
dr = comm.ExecuteReader();
while (dr.Read())
{
txtresults.Text = dr.GetValue(0).ToString();
}
}
else
{
txtresults.Text = comm.ExecuteNonQuery().ToString();
}
}
catch (Exception ex)
{
txtresults.Text = ex.Message;
}
finally
{
comm.Dispose();
conn.Close();
conn = null;
}
}
public void Querry(string Que, string DataBasePath)
{
string conn = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataBasePath;
System.Data.OleDb.OleDbConnection connect = new System.Data.OleDb.OleDbConnection(conn);
connect.Open();
using (System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(Que, connect))
{
try
{
command.ExecuteNonQuery();
}
catch (System.Data.OleDb.OleDbException ex)
{
MessageBox.Show("Произошла ошибка при создании таблицы\n" + ex.Message);
}
}
connect.Close();
}
protected void ExcluirDados(string sTextoSql)
{
if (ValidaDados(false))
{
DialogResult res;
res = MessageBox.Show(this, "Certeza de excluir?",
frmPrincipal.fPrincipal.Text, MessageBoxButtons.YesNo,
MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (res == DialogResult.Yes)
{
Arquiva = frmPrincipal.fPrincipal.db.CreateCommand();
Arquiva.CommandText = sTextoSql;
Arquiva.ExecuteNonQuery();
Arquiva.Dispose();
btnLimpar_Click(this, null);
}
}
}
public static void cv(string a, string b)
{
try
{
con1.Open();
System.Data.OleDb.OleDbCommand top1 = new System.Data.OleDb.OleDbCommand("UPDATE SYMBOL_TABLE SET[ADDRESS] = ? where SYMBOLE = ?", con1);
top1.Parameters.AddWithValue("?", a);
top1.Parameters.AddWithValue("?", b);
top1.ExecuteNonQuery();
con1.Close();
Form2 obj = new Form2();
obj.grid2();
}
catch (Exception e)
{
// MessageBox.Show(e.Message);
}
}
public static void AddData(string Key, string Value, string TestCasesName)
{
string sql = null;
path = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location).Replace("\\bin\\Debug", "") + "\\Input Files\\TestData.xlsx";
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "'; Extended Properties='Excel 12.0; HDR = YES'");
MyConnection.Open();
myCommand.Connection = MyConnection;
sql = "update [COMET$] set " + Key + "=\"" + Value + "\" where TestCasesName =\"" + TestCasesName + "\"";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
MyConnection.Close();
}
public void Querry(string Que, string DataBasePath)
{
string conn = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataBasePath;
System.Data.OleDb.OleDbConnection connect = new System.Data.OleDb.OleDbConnection(conn);
connect.Open();
using (System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(Que, connect))
{
try
{
command.ExecuteNonQuery();
}
catch (System.Data.OleDb.OleDbException ex)
{
MessageBox.Show("Произошла ошибка при создании таблицы\n" + ex.Message);
}
}
connect.Close();
}
private static void CreateBase(Base b, String path)
{
String udl = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ path
+ ";Mode=ReadWrite;Persist Security Info=False";
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ path
);
b.conn = new System.Data.OleDb.OleDbConnection(udl);
b.conn.Open();
System.Data.OleDb.OleDbCommand com = b.conn.CreateCommand();
com.CommandText = "create table Common(Path VARCHAR(255))";
com.ExecuteNonQuery();
}
public void AddVacancy(string compName, string profession, string requirements, int salary)
{
string cmdString = "INSERT INTO `Vacancies` (`Company`, `Profession`, `Requirements`, `Salary`) VALUES ('"
+ compName + "', '" + profession + "', '" + requirements + "', '" + salary + "')";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
this.databaseMaster.connection.Open();
cmd.CommandText = cmdString;
cmd.Connection = this.databaseMaster.connection;
try
{
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("Error");
}
this.databaseMaster.connection.Close();
}
private void save(Report r, System.Data.OleDb.OleDbConnection con)
{
string cmd;
TextManager mng = new TextManager();
string[] text = new string[r.Items.Count];
cmd = "Insert into Diagn (FIO, Dat, Name) Values (";
cmd += "\"" + r.FIO + "\", ";
cmd += "\"" + r.Date.ToShortDateString() + "\", ";
cmd += "\"" + (string)r.Tag + "\");";
con.Open();
System.Data.OleDb.OleDbCommand c =
new System.Data.OleDb.OleDbCommand(cmd, con);
c.ExecuteNonQuery();
cmd = "Select max(ID) From Diagn;";
c = new System.Data.OleDb.OleDbCommand(cmd, con);
mng.Save(r, c.ExecuteScalar());
con.Close();
}
private void button1_Click(object sender, EventArgs e)
{
string psw = password1.Text,
psw0 = password2.Text;
//得到用户名
string username = Form1.strName;
if (psw != psw0)
{
MessageBox.Show("两次密码不一致，请重新输入");
}
//注册
System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection();
// TODO: Modify the connection string and include any
// additional required properties for your database.
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data source=" +
@"USER_TABLE1.mdb";
try
{
string queryString = "UPDATE USER_TABLE SET USER_PSW = '" + psw0 + "' WHERE USER_ID = '" + username + "'";
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(queryString, conn);
conn.Open();
int i = command.ExecuteNonQuery();
if (i == 1)
{
this.Close();
MessageBox.Show("修改成功~~");
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to connect to data source" + ex.ToString());
}
finally
{
conn.Close();
}
}
private bool ExecuteCommand(string sCommand)
{
string sMsg;
if (TestConnection(out sMsg))
{
try
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sCommand, connectionTest);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
connectionTest.Close();
throw (ex);
}
return(true);
}
throw (new Exception(sMsg));
}
private void button1_Click(object sender, EventArgs e)
{
try
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
MyConnection.Open();
myCommand.Connection = MyConnection;
sql = "Insert into [Sheet1$] (id,name) values('5','e')";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void InsertIntoExcel(string filename, string tablename, ArrayList Name, ArrayList Value)
{
System.Data.OleDb.OleDbConnection CN = new System.Data.OleDb.OleDbConnection();
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0";
CN.Open();
System.Data.OleDb.OleDbCommand CM = CN.CreateCommand();
string Command;
//Insert to Table
int i, length = 0;
length = Name.Count;
for (i = 0; i < length; i++)
{
Command = string.Format("INSERT INTO [{0}] VALUES ('{1}', '{2}')", tablename, Name[i].ToString(), Value[i].ToString());
CM.CommandText = Command;
CM.ExecuteNonQuery();
}
CN.Close();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
foreach (DataGridViewRow item in this.userdata.SelectedRows)
{
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\ASD\ase 2\user Database.accdb");
connection.Open();
string user = userdata.SelectedRows[0].Cells[1].Value.ToString();
String query2 = "DELETE FROM users WHERE [username]='" + user + "'";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query2, connection);
cmd.ExecuteNonQuery();
MessageBox.Show("deleted");
userdata.Rows.RemoveAt(item.Index);
}
}
catch (Exception ex)
{
MessageBox.Show("Failed due to " + ex.Message);
}
}
private void savebtn_Click(object sender, EventArgs e)
{
if (IsFormatted(amounttb.Text) &&
(companytb.Text.Length > 0 ||
firstNametb.Text.Length > 0 &&
lastnametb.Text.Length > 0))
{
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection();
con.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=acct.mdb;";
con.Open();
System.Data.OleDb.OleDbCommand com = new System.Data.OleDb.OleDbCommand();
com.Connection = con;
com.CommandText = "Update AcctAR set company='" + companytb.Text.Replace("'", "''") + "', firstName='" + firstNametb.Text.Replace("'", "''") + "', lastName='" + lastnametb.Text.Replace("'", "''") + "', [date]='" + datetb.Text + "', [time]='" + timetb.Text + "', amount=" + amounttb.Text + ", invoiceNo='" + invoiceNotb.Text.Replace("'", "''") + "', description='" + description.Text.Replace("'", "''") + "' where id=" + this.arId + " and accountid=" + this.accountid;
com.ExecuteNonQuery();
MessageBox.Show("Save successfully");
con.Close();
}
}
/// <summary>
/// method when the delete user button is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
try
{
foreach (DataGridViewRow item in this.userdata.SelectedRows) //foreach loop that gets all the selected rows in the datagrid
{
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\ASD\ase 2\user Database.accdb");
connection.Open(); //opens connection
string user = userdata.SelectedRows[0].Cells[1].Value.ToString();
String query2 = "DELETE FROM users WHERE [username]='" + user + "'"; //query that deletes selected rows
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query2, connection);
cmd.ExecuteNonQuery(); //executes query
MessageBox.Show("deleted"); //message box confirming the account has been deleted
userdata.Rows.RemoveAt(item.Index); //removes user in the actual database
}
}
catch (Exception ex)
{
MessageBox.Show("Failed due to " + ex.Message);
}
}
public static void AddManu(string file, string BSSID, string Manufacturer)
{
string connectionString = string.Format("Provider={0}; Data Source={1}; Jet OLEDB:Engine Type={2}",
"Microsoft.Jet.OLEDB.4.0",
file,
5);
using (var con = new System.Data.OleDb.OleDbConnection(connectionString))
{
con.Open(); // Open a connection to the database.
string query = "INSERT INTO Manufacturers ([BSSID],[Manufacturer]) VALUES (@BSSID,@Manufacturer);";
using (var command = new System.Data.OleDb.OleDbCommand(query, con))
{
command.Parameters.AddWithValue("@BSSID", BSSID);
command.Parameters.AddWithValue("@Manufacturer", Manufacturer);
command.ExecuteNonQuery();
}
}
}
static void Main(string[] args)
{
System.Data.OleDb.OleDbConnectionStringBuilder bldr = new System.Data.OleDb.OleDbConnectionStringBuilder();
bldr.DataSource = @"C:\Users\tekhe\Documents\Database2.mdb";
bldr.Provider = "Microsoft.Jet.OLEDB.4.0";
using (System.Data.OleDb.OleDbConnection cnxn = new System.Data.OleDb.OleDbConnection(bldr.ConnectionString))
{
cnxn.Open();
Console.WriteLine("open");
using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand())
{
cmd.Connection = cnxn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT INTO [Table1] ([Dob]) VALUES(#" + DateTime.Now.ToString() + "#)";
cmd.ExecuteNonQuery();
}
}
Console.ReadKey();
}
private void submitButton_Click(object sender, System.EventArgs e)
{
if (this.IsValid)
{
String attendees = "";
foreach (ListItem attendee in attendeeList.Items)
{
if (attendee.Selected)
{
attendees += attendee.Text + " (" + attendee.Value + "), ";
}
}
attendees += " and " + nameBox.Text;
String dateString = calendar.SelectedDate.Date.Date.ToShortDateString();
String oleDbCommand = "INSERT INTO Events (Name, Room, " +
"AttendeeList, EventDate) VALUES ('" +
eventBox.Text + "', '" +
roomList.SelectedItem.Value + "', '" +
attendees + "', '" + dateString + "')";
System.Data.OleDb.OleDbCommand insertCommand = new System.Data.OleDb.OleDbCommand(oleDbCommand, oleDbConnection1);
oleDbConnection1.Open();
int queryResult = insertCommand.ExecuteNonQuery();
if (queryResult == 1)
{
resultLabel.Text = "Event Added.";
daEvents = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Events", oleDbConnection1);
ds.Clear();
daEvents.Fill(ds, "Events");
eventTable = ds.Tables["Events"];
calendar.SelectedDate = getFreeDate(calendar.SelectedDate.AddDays(1));
eventDetails1.DataBind();
eventDetails2.DataBind();
}
else
{
resultLabel.Text = "Event not added due to DB access problem.";
}
oleDbConnection1.Close();
}
}
private void update_user_Click(object sender, EventArgs e)
{
try
{
foreach (DataGridViewRow item in this.userdata.SelectedRows)
{
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\ASD\ase 2\user Database.accdb");
connection.Open();
string user = userdata.SelectedRows[0].Cells[1].Value.ToString();
String query = "UPDATE users SET [username] = '" + update_name.Text + "', [password] = '" + update_password.Text + "' WHERE [username]='" + user + "'";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, connection);
cmd.ExecuteNonQuery();
MessageBox.Show("updated");
this.Refresh();
//userdata.Rows.Insert(item.Index);
}
}
catch (Exception ex)
{
MessageBox.Show("Failed due to " + ex.Message);
}
}
private void menuItem2_Click(object sender, System.EventArgs e)
{
string message = "你确定要清除数据表中的所有行吗? 是，将全部清除； 否，将取消此操作。";
string caption = "清空确认";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result;
result = MessageBox.Show(this, message, caption, buttons,
MessageBoxIcon.Question, MessageBoxDefaultButton.Button1
);
if (result == DialogResult.Yes)
{
string myConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\data.mdb;";
string myExecuteQuery = "DELETE * FROM table1";
System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(myConnectionString);
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(myExecuteQuery, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
}
private void m_axCalendar_DoDeleteRPattern(object sender, AxXtremeCalendarControl._DCalendarControlEvents_DoDeleteRPatternEvent e)
{
e.bResult = false;
if (!IsOpen())
{
return;
}
try
{
System.Data.OleDb.OleDbCommand objCommand = m_oleDbConnection.CreateCommand();
objCommand.CommandText = "DELETE FROM CalendarRecurrencePatterns WHERE RecurrencePatternID = " + Convert.ToString(e.pPattern.Id);
objCommand.ExecuteNonQuery();
e.bResult = true;
}
catch (System.Exception ex)
{
System.Diagnostics.Debug.WriteLine("EXCEPTION! providerSQLServer.m_axCalendar_DoCreateRPattern: " + ex.Message);
}
}
protected void SalvarDados(string sTextoSql, string sTextoUpdate,
string sTextoInsert, bool bLimparDados)
{
if (ValidaDados(true))
{
Arquiva = frmPrincipal.fPrincipal.db.CreateCommand();
if (RotinasGlobais.Rotinas.ConsultaDados(Arquiva, sTextoSql))
{
Arquiva.CommandText = sTextoUpdate;
}
else
{
Arquiva.CommandText = sTextoInsert;
}
Arquiva.ExecuteNonQuery();
Arquiva.Dispose();
if (bLimparDados)
{
btnLimpar_Click(this, null);
}
}
}
public void CancelIncome()
{
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection();
con.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=acct.mdb;";
con.Open();
string quryString = "INSERT INTO AcctAR(accountid, company, firstName, lastName, [date], amount, invoiceNo, description) SELECT accountid, company, firstName, lastName, [date], amount, invoiceNo, incomeNote FROM INCOME WHERE id=" + this.incomeId + " and accountid=" + this.accountid;
System.Data.OleDb.OleDbCommand c = new System.Data.OleDb.OleDbCommand();
c.CommandText = quryString;
c.Connection = con;
c.ExecuteNonQuery();
quryString = "DELETE FROM Income WHERE id=" + this.incomeId + " and accountid=" + this.accountid;
c = new System.Data.OleDb.OleDbCommand();
c.CommandText = quryString;
c.Connection = con;
c.ExecuteNonQuery();
MessageBox.Show("Cancel Successfully");
con.Close();
}
public static void writedata()
{
try
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\\Users\\hsanghvi\\Desktop\\Checkwrite.xls';Extended Properties=Excel 8.0;");
MyConnection.Open();
myCommand.Connection = MyConnection;
int strid = 23;
// string strdata = "check";
sql = "Insert into [Sheet1$] ('id') values(" + strid + ")";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
MyConnection.Close();
}
catch (Exception ex)
{
Report.Failure("Failuer in write" + ex.Message);
}
}
public void CreateNewAccount()
{
if (openlistbox.SelectedValue == null)
{
return;
}
List <string> _items = new List <string>();
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection();
con.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=acct.mdb;";
con.Open();
System.Data.OleDb.OleDbCommand c = new System.Data.OleDb.OleDbCommand();
string quryString = "update Accounts set [default]=0";
c.CommandText = quryString;
c.Connection = con;
c.ExecuteNonQuery();
quryString = "insert into Accounts (accountName, firstName, lastName, createdDate, company, address, city, state, country, phone, fax, email, [default]) values ('" + accountName.Text + "', '" + firstName.Text + "', '" + lastName.Text + "', Date(), '" + company.Text + "', '" + address.Text + "', '" + city.Text + "', '" + state.Text + "', '" + country.Text + "', '" + phone.Text + "', '" + fax.Text + "', '" + email.Text + "', 1)";
c = new System.Data.OleDb.OleDbCommand();
c.CommandText = quryString;
c.Connection = con;
c.ExecuteNonQuery();
quryString = "select max(id)+1 as accountid from Accounts";
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(quryString, con);
DataSet ds = new DataSet();
da.Fill(ds, "Accounts");
int row = ds.Tables["Accounts"].Rows.Count - 1;
for (int r = 0; r <= row; r++)
{
this.theaccountid = (int)Int64.Parse(ds.Tables["Accounts"].Rows[r].ItemArray[0].ToString());
}
con.Close();
}
/// <summary>
/// method when the update user button is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void update_user_Click(object sender, EventArgs e)
{
try
{
foreach (DataGridViewRow item in this.userdata.SelectedRows) //foreach loop that goes through each selected row
{
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\ASD\ase 2\user Database.accdb");
connection.Open(); //opens connection
string user = userdata.SelectedRows[0].Cells[1].Value.ToString(); //assigns user to the second array index on the table
String query = "UPDATE users SET [username] = '" + update_name.Text + "', [password] = '" + update_password.Text + "' WHERE [username] ='" + user + "'"; //query that updates the username or password values where user is at the selected index
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, connection);
cmd.ExecuteNonQuery(); //executes query
MessageBox.Show("updated"); //messagebox confirming the information has been updated
this.Refresh(); //refeshes page
//userdata.Rows.Insert(item.Index);
}
}
catch (Exception ex)
{
MessageBox.Show("Failed due to " + ex.Message);
}
}
//****************************************************************************
/************** Actualiza_Flag_Prescrip
* Metodo que actualiza el flag de envio de las prescripciones (para que no lo vuelva a enviar)
***************/
private bool Actualiza_Flag_Prescrip()
{
bool exito = false;
// RETAIL
using (System.Data.OleDb.OleDbConnection dbConn = new System.Data.OleDb.OleDbConnection(Conexion.Conn1))
{
dbConn.Open();
foreach (DataRow fila in dat_presccabRetail.Rows)
{
//string valor = fila["Prescrip"].ToString();
//string sql_upd = "UPDATE FVPRESP SET PRE_RECNO=1 WHERE PRE_TIEND='" + fila["Pre_tiend"] + "' AND PRE_ARTIC='" + fila["Pre_artic"] + "' AND PRE_CALID='" + fila["Pre_calid"] + "' AND PRE_ARTIC='2811304' AND PRE_TIEND='50522'";
string sql_upd = "UPDATE SCCCGUD SET CGUD_FTXTD='X' WHERE cgud_gudis='" + fila["cgud_gudis"] + "'";
System.Data.OleDb.OleDbCommand com_upd = new System.Data.OleDb.OleDbCommand(sql_upd, dbConn);
com_upd.ExecuteNonQuery();
LogHandle.Graba_Log("UPDATE SCCCGUD");
}
}
// NO RETAIL
using (System.Data.OleDb.OleDbConnection dbConn = new System.Data.OleDb.OleDbConnection(Conexion.Conn2))
{
dbConn.Open();
foreach (DataRow fila in dat_presccabNoRetail.Rows)
{
//string valor = fila["Prescrip"].ToString();
//string sql_upd = "UPDATE FVPRESP SET PRE_RECNO=1 WHERE PRE_TIEND='" + fila["Pre_tiend"] + "' AND PRE_ARTIC='" + fila["Pre_artic"] + "' AND PRE_CALID='" + fila["Pre_calid"] + "' AND PRE_ARTIC='2811304' AND PRE_TIEND='50522'";
string sql_upd = "UPDATE vmaoc SET oc_ftx='X' WHERE oc_nord='" + fila["oc_nord"] + "'";
System.Data.OleDb.OleDbCommand com_upd = new System.Data.OleDb.OleDbCommand(sql_upd, dbConn);
com_upd.ExecuteNonQuery();
LogHandle.Graba_Log("UPDATE vmaoc");
}
}
exito = true;
return(exito);
}
private void button1_Click(object sender, EventArgs e)
{
var cn = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0;"
+ "Data Source=C:\\C#2017\\SampleDB.accdb;");
//テストテーブルを削除する
try
{
cn.Open();
var cmd = new System.Data.OleDb.OleDbCommand("DROP TABLE テスト", cn);
cmd.ExecuteNonQuery();
MessageBox.Show("削除しました。", "通知");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "通知");
}
finally
{
cn.Close();
}
}
void Session_End(object sender, EventArgs e)
{
//// Code that runs when a session ends.
//// Note: The Session_End event is raised only when the sessionstate mode
//// is set to InProc in the Web.config file. If session mode is set to StateServer
//// or SQLServer, the event is not raised.
//// Close out session and quit application
Application["UserOnline"] = (int)Application["UserOnline"] - 1;
Application["User" + Session["SID"]] = null;
try
{
System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["_UserDatabase"].ConnectionString);
string strCommand = string.Format("UPDATE [LOG] SET [TIME_END]='{0}' WHERE [ID]={1}", DateTime.Now.ToString(), Session["SID"]);
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(strCommand, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
}
catch (Exception ex)
{
}
}
private void shrani_podatke_Click(object sender, System.EventArgs e)
{
con_2.Open();
oleDbInsertCommand1.Parameters["St_sektorjev"].Value = _2.st_sektorjev;
oleDbInsertCommand1.Parameters["St_mest_za_sektor"].Value = _2.st_vseh_mest_za_sektor;
oleDbInsertCommand1.Parameters["St_vseh_mest"].Value = _2.st_vseh_mest;
oleDbInsertCommand1.Parameters["St_zasedenih_mest"].Value = _2.st_z_mest;
oleDbInsertCommand1.Parameters["Doloceni_sektorji"].Value = _2.niz_sektorjev[0] + " " + _2.niz_sektorjev[1] + " " + _2.niz_sektorjev[2] + " " + _2.niz_sektorjev[3] + " " + _2.niz_sektorjev[4] + " " + _2.niz_sektorjev[5] + " " + _2.niz_sektorjev[6] + " " + _2.niz_sektorjev[7] + " " + _2.niz_sektorjev[8] + " " + _2.niz_sektorjev[9];
if (_2.sek_arena[0] != "")
{
oleDbInsertCommand1.Parameters["Sektorji_v_areni"].Value = _2.sek_arena[0] + " " + _2.sek_arena[1];
}
else
{
oleDbInsertCommand1.Parameters["Sektorji_v_areni"].Value = "Ni sektorjev v areni";
}
oleDbInsertCommand1.Parameters["Cena"].Value = _2.cena;
oleDbInsertCommand1.Parameters["Vsota"].Value = _2.vs;
oleDbInsertCommand1.Parameters["Datum"].Value = _2.datum;
oleDbInsertCommand1.ExecuteNonQuery();
con_2.Close();
shrani_podatke.Enabled = false;
}
private void btnsubmit_Click(object sender, EventArgs e)
{
try
{
System.Data.OleDb.OleDbConnection myconnection;
System.Data.OleDb.OleDbCommand mycommand = new System.Data.OleDb.OleDbCommand();
String sql = null;
myconnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source='D:\\virentask.xlsx'; Extended Properties = Excel 8.0 ");
myconnection.Open();
mycommand.Connection = myconnection;
sql = "Insert into [Sheet1$] (id,name,email,phone,address,college) values ('" + txtId.Text + "','" + txtname.Text + "','" + txtemail.Text + "','" + txtphone.Text + "','" + txtadd.Text + "','" + txtclg.Text + "') ";
mycommand.CommandText = sql;
mycommand.ExecuteNonQuery();
MessageBox.Show("Data Inserted");
myconnection.Close();
clear();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public bool SaveAs(string fileName, System.Data.DataTable dataTable)
{
if (dataTable == null || dataTable.Rows.Count == 0) return false;
if (xlApp == null) return false;
_workbook = xlApp.Workbooks.Add(Type.Missing);
_worksheet = (Worksheet)_workbook.Worksheets[1];
for (var i = 0; i < dataTable.Columns.Count; i++)
{
_worksheet.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
//range = (Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;
//range.Font.Bold = true;
}
_workbook.SaveAs(fileName,XlFileFormat.xlWorkbookNormal, "", "", false, false, XlSaveAsAccessMode.xlExclusive,XlPlatform.xlWindows, false, false, false, false);
_workbook.Close(true, fileName, false);
xlApp.Quit();
_connectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties=Excel 8.0;"; // строка подключения
using (var excelConn = new System.Data.OleDb.OleDbConnection(_connectionString)) // используем OleDb
{
var queryValues = String.Empty;
excelConn.Open();
for (var i = 0; i < dataTable.Rows.Count; i++)
{
for (var c = 0; c < dataTable.Columns.Count; c++)
{
queryValues += dataTable.Rows[i][c] + "','";
}
queryValues = queryValues.Substring(0, queryValues.Length - 3);
var writeCmd = new System.Data.OleDb.OleDbCommand("INSERT INTO [Лист1$] VALUES ('" + queryValues + "')", excelConn);
writeCmd.ExecuteNonQuery(); // вставляем данные в лист1 файла - filename
writeCmd.Dispose();
queryValues = String.Empty;
}
excelConn.Close();
}
return System.IO.File.Exists(fileName);
}
protected void cmdSubmit_Click(object sender, EventArgs e)
{
string connstring =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DropBox\My Dropbox\Devry\CIS407\SU10B\day7\dbreader\dbreader\App_Data\Shoes.mdb;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
string sql = "";
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
conn.ConnectionString = connstring;
conn.Open();
comm.Connection = conn;
//Console.WriteLine(conn.State);
sql = "Insert into customers (customerid, fname, lname, [password], email)";
sql += " values (?, ?, ?, ?, ?)";
//http://www.4guysfromrolla.com/webtech/092601-1.2.shtml
comm.CommandText = sql;
comm.Parameters.AddWithValue("customerid", txtID.Text);
comm.Parameters.AddWithValue("fname", txtFName.Text);
comm.Parameters.AddWithValue("lname", txtLName.Text);
comm.Parameters.AddWithValue("passwd", txtPassWD.Text);
comm.Parameters.AddWithValue("email", txtEmail.Text);
comm.ExecuteNonQuery();
}
public static bool insert_data_to_Excel(List<TaiSanHienThi> list, String file_path)
{
try
{
const String ID = "A";
const String NGAY_CT = "B";
const String SOHIEU_CT = "C";
const String TEN = "D";
const String LOAI = "E";
const String DONVITINH = "F";
const String NGAY_SD = "G";
const String NUOC_SX = "H";
const String SOLUONG = "I";
const String DONGIA = "J";
const String THANHTIEN = "K";
const String TINHTRANG = "L";
const String VITRI = "O";
const String PHONG = "N";
const String DONVI_QL = "M";
const String GHICHU = "P";
String currentPath = Directory.GetCurrentDirectory();
String path = Path.Combine(currentPath, "Excel");
//String path = "F:";
String file = "";
String fileName = "MauExport.xls";
if (Directory.Exists(path))
{
file = path + "//" + fileName;
if (System.IO.File.Exists(file))
{
File.Delete(file_path);
File.Copy(file, file_path);
System.Data.OleDb.OleDbConnection MyConnection;
MyConnection = new System.Data.OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=Excel 8.0;", file_path));
MyConnection.Open();
int i = 0;
foreach (TaiSanHienThi ts in list)
{
i++;
if (i % 100 == 0)
{
MyConnection.Close();
MyConnection = new System.Data.OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=Excel 8.0;", file_path));
MyConnection.Open();
}
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
myCommand.Connection = MyConnection;
sql = String.Format("Insert into [TaiSan$A2:P9999] ({0}) values(@id, @ngay_ct, @sohieu_ct, @ten, @loai, @donvitinh, @ngay_sd, @nuoc_sx, @soluong, @dongia, @thanhtien, @tinhtrang, @vitri, @phong, @donvi_ql, @ghichu)",
ID + "," + NGAY_CT + "," + SOHIEU_CT + "," + TEN + "," + LOAI + "," + DONVITINH + "," + NGAY_SD + "," + NUOC_SX + "," + SOLUONG + "," + DONGIA + "," + THANHTIEN + "," + TINHTRANG + "," + VITRI + "," + PHONG + "," + DONVI_QL + "," + GHICHU);
myCommand.CommandText = sql;
myCommand.Parameters.AddWithValue("@id", ts.id);
myCommand.Parameters.AddWithValue("@ngay_ct", ts.ngay_ct != null ? (Object)((DateTime)ts.ngay_ct).ToShortDateString() : DBNull.Value);
myCommand.Parameters.AddWithValue("@sohieu_ct", (Object)ts.sohieu_ct ?? DBNull.Value);
myCommand.Parameters.AddWithValue("@ten", ts.ten);
myCommand.Parameters.AddWithValue("@loai", ts.loaits);
myCommand.Parameters.AddWithValue("@donvitinh", ts.donvitinh);
myCommand.Parameters.AddWithValue("@ngay_sd", ts.ngay != null ? (Object)((DateTime)ts.ngay).ToShortDateString() : DBNull.Value);
myCommand.Parameters.AddWithValue("@nuoc_sx", (Object)ts.nuocsx ?? DBNull.Value);
myCommand.Parameters.AddWithValue("@soluong", ts.soluong);
myCommand.Parameters.AddWithValue("@dongia", ts.dongia);
myCommand.Parameters.AddWithValue("@thanhtien", ts.thanhtien);
myCommand.Parameters.AddWithValue("@tinhtrang", ts.tinhtrang);
myCommand.Parameters.AddWithValue("@vitri", (Object)ts.vitri ?? DBNull.Value);
myCommand.Parameters.AddWithValue("@phong", (Object)ts.phong ?? DBNull.Value);
myCommand.Parameters.AddWithValue("@donvi_ql", (Object)ts.dvquanly ?? DBNull.Value);
myCommand.Parameters.AddWithValue("@ghichu", (Object)ts.ghichu ?? DBNull.Value);
myCommand.ExecuteNonQuery();
if (i % 500 == 0)
{
}
}
MyConnection.Close();
return true;
}
else
return false;
}
else
return false;
}
catch(Exception ex)
{
Debug.WriteLine("ExcelDataBaseHelper->insert_data_to_Excel: " + ex.Message);
return false;
}
}
private static void WriteNSX(String fileName, String sheet, String stt, String text)
{
try
{
if (!stt.Equals(""))
{
//Ghi file Excel
using (System.Data.OleDb.OleDbConnection MyConnection = new System.Data.OleDb.OleDbConnection(GetConnectionString(fileName)))
{
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MyConnection.Open();
myCommand.Connection = MyConnection;
sql = String.Format("Update [{0}$] set NSX = @nsx where STT = @stt", sheet);
myCommand.CommandText = sql;
myCommand.Parameters.AddWithValue("@nsx", text);
myCommand.Parameters.AddWithValue("@stt", stt);
myCommand.ExecuteNonQuery();
MyConnection.Close();
}
}
}
catch (Exception ex)
{
Debug.WriteLine("ExcelDataBaseHelper->WriteFileWithRange: " + ex.Message);
}
}
///-------------------------------------------------
#region
//private void set_headers_rec_order()
//{
// int count_headers = 0; string condition = "";
// Lb_Headers.Items.Add("RecDateTime"); count_headers++;
// headers = new string[G_Variables.No_GWs_Availble];
// for (int i = 0; i < G_Variables.No_GWs_Availble; i++)
// {
// condition = "";
// if (Rb_All_Types.Checked)
// condition = "( Select title, id, root_id, pin_id from device where gateway_id=" + G_Variables.Gateways_ID[i] + " )";
// else //if ((Rb_DeviceType.Checked) && (!Ch_additionalSensors.Checked))
// condition = "( Select distinct title, id , root_id , pin_id ,type from device where gateway_id=" + G_Variables.Gateways_ID[i] + " and type='" + com_monitor_type.Text + "' )";
// if ((Rb_DeviceType.Checked) && (Ch_additionalSensors.Checked))
// {
// condition += " union (SELECT DISTINCT device_1.title, device_1.id , device_1.root_id, device_1.pin_id ,device_1.type " +
// " FROM dbo.device INNER JOIN " +
// " dbo.Dependencies ON dbo.device.id = dbo.Dependencies.device INNER JOIN " +
// " dbo.device AS device_1 ON dbo.Dependencies.dependon_device = device_1.id " +
// " WHERE (device_1.gateway_id =" + G_Variables.Gateways_ID[i] + ") AND (dbo.device.type = '" + com_monitor_type.Text + "'))";
// }
// condition += " order by type,pin_id ";
// con2db.Close();
// con2db.Open();
// SqlCommand cmd = new SqlCommand(condition, con2db);
// SqlDataAdapter adaptor = new SqlDataAdapter(cmd);
// DataSet DTt = new DataSet();
// adaptor.Fill(DTt);
// DataRowCollection drgoods = DTt.Tables[0].Rows;
// foreach (DataRow dtdep in drgoods)
// {
// string s = dtdep[0].ToString() + "(" + dtdep[1].ToString() + "/" + dtdep[2].ToString() + ":" + dtdep[3].ToString() + "/" + G_Variables.Gateways_ID[i] + ")";
// Lb_Headers.Items.Add(s); headers[i] += (s + ",");
// count_headers++;
// }
// }
// con2db.Close();
// if (Lb_Headers.Items.Count == 1)
// {
// Lb_Headers.Items.Clear(); count_headers = 0;
// MessageBox.Show("Ensure that Network was discovered and There were Devices, that already have been connected on them !", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
// }
// lab_headers.Text = "=" + count_headers;
//}
///------------------------------------------------
//private void set_headers()
//{
// // bool add_sensor=false;
// int count_headers = 0;
// string condition = " Select id, title, root_id,gateway_id,pin_id from device ";
// if ((Rb_Whole_Network.Checked) && (Rb_All_Types.Checked))
// { condition += ""; rec_state = "Network"; }
// else if ((Rb_Whole_Network.Checked) && (Rb_DeviceType.Checked))
// { condition += " where type='" + com_monitor_type.Text + "'"; rec_state = "Network"; }
// else if ((Rb_Gateway.Checked) && (Rb_All_Types.Checked))
// { condition += " where gateway_id= " + com_gateway.SelectedValue + ""; rec_state = com_gateway.SelectedValue.ToString(); }
// else
// {
// condition += " where gateway_id= " + com_gateway.SelectedValue + " and type='" + com_monitor_type.Text + "'"; rec_state = com_gateway.SelectedValue.ToString();
// // if((Ch_additionalSensors.Checked) && (!Rb_All_Types.Checked))
// // add_sensor = true;
// }
// condition += " order by type, id ,gateway_id";
// con2db.Close();
// con2db.Open();
// SqlCommand cmd = new SqlCommand(condition, con2db);
// SqlDataAdapter adaptor = new SqlDataAdapter(cmd);
// DataSet DTt = new DataSet();
// adaptor.Fill(DTt);
// DataRowCollection drgoods = DTt.Tables[0].Rows;
// count_headers = DTt.Tables[0].Rows.Count;// +1; //for time we add 1
// //lab_headers.Text = "=" + count_headers;
// Lb_Headers.Items.Add("RecDateTime");
// foreach (DataRow dtdep in drgoods)
// {
// string s = dtdep[1].ToString() + "(" + dtdep[0].ToString() + "/" + dtdep[2].ToString() + ":" + dtdep[4].ToString() + "/" + dtdep[3].ToString() + ")";
// Lb_Headers.Items.Add(s);
// }
// if ((Rb_Whole_Network.Checked)&&(Rb_DeviceType.Checked) && (Ch_additionalSensors.Checked))
// {
// condition = " SELECT DISTINCT device_1.id, device_1.title, device_1.root_id, device_1.gateway_id, dbo.device.type" +
// " FROM dbo.device INNER JOIN" +
// " dbo.Dependencies ON dbo.device.id = dbo.Dependencies.device INNER JOIN" +
// " dbo.device AS device_1 ON dbo.Dependencies.dependon_device = device_1.id" +
// " WHERE (dbo.device.type = '" + com_monitor_type.Text + "')";
// }
// else if ((Rb_Gateway.Checked) && (Rb_DeviceType.Checked) && (Ch_additionalSensors.Checked))
// {
// condition = " SELECT DISTINCT device_1.id, device_1.title, device_1.root_id, device_1.gateway_id, dbo.device.type" +
// " FROM dbo.device INNER JOIN" +
// " dbo.Dependencies ON dbo.device.id = dbo.Dependencies.device INNER JOIN" +
// " dbo.device AS device_1 ON dbo.Dependencies.dependon_device = device_1.id" +
// " WHERE (dbo.device.gateway_id = "+int.Parse(com_gateway.SelectedValue.ToString())+") AND (dbo.device.type = '"+com_monitor_type.Text+"')";
// }
// if ((Rb_DeviceType.Checked) && (Ch_additionalSensors.Checked))
// {
// cmd = new SqlCommand(condition, con2db);
// adaptor = new SqlDataAdapter(cmd);
// DTt = new DataSet();
// adaptor.Fill(DTt);
// drgoods = DTt.Tables[0].Rows;
// count_headers+=DTt.Tables[0].Rows.Count;
// // lab_headers.Text = "=" + count_headers;
// foreach (DataRow dtdep in drgoods)
// {
// string s = dtdep[1].ToString() + "(" + dtdep[0].ToString() + "/" + dtdep[2].ToString() + ":" + dtdep[3].ToString() + ")";
// Lb_Headers.Items.Add(s);
// }
// }
// con2db.Close();
// if (Lb_Headers.Items.Count == 1)
// Lb_Headers.Items.Clear();
// else
// lab_headers.Text = "=" + (++count_headers);
//}
#endregion
///-------------------------------------------------
private void create_datasetfile()
{
System.Data.OleDb.OleDbConnection MyConnection;
string s="";
string t = "_"+DateTime.Now.Year+"-"+DateTime.Now.Month+"-"+DateTime.Now.Day+"-h" + DateTime.Now.ToLocalTime().Hour.ToString();
txt_FileName.Text = G_Variables.Log_Title + t;
s = G_Variables.Log_Path + "\\" + G_Variables.Log_Title + t;
bool continue_work = true; bool append = false;
if(File.Exists(s + ".XLS") == true)
{
DialogResult dr = MessageBox.Show(" File was Exist at that path with the same title, because another file was created within the same hour with the same criteria \n Do you want to continue process replacing existing file with that new file ?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dr == DialogResult.Yes)
{ File.Delete(s + ".XLS"); continue_work = true; }
else
{
dr = MessageBox.Show(" Do You want to continue using the same file to append data on it?", "info.", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.No)
{
dr = MessageBox.Show(" Do You want to continue using an alternative title which will be (prev. titel plus minutes)?", "info.", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
s += "-m" + DateTime.Now.ToLocalTime().Minute.ToString();
txt_FileName.Text = G_Variables.Log_Title + t + "-m" + DateTime.Now.ToLocalTime().Minute.ToString(); ;
continue_work = true;
}
else
continue_work = false;
}
else append = true;
}
}
if(continue_work)
{
try
{
columns = Lb_Headers.Items.Count;
if (Lb_Rules.Items[1].ToString().Contains('#'))
Cur_Type = "Network";
else
Cur_Type = "Gateway";
Cur_Ds_File = s;
Cur_File_Sheet = G_Variables.Log_Title;
string header = "";
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Cur_Ds_File + "';Extended Properties='Excel 8.0;HDR=Yes'");
MyConnection.Open();
if( Lb_Headers.Items.Count>0)
header += "[" + Lb_Headers.Items[0] + "] datetime,";
for (int i = 1; i < Lb_Headers.Items.Count - 1; i++)
header += "[" + Lb_Headers.Items[i] + "] int,";
header += "[" + Lb_Headers.Items[Lb_Headers.Items.Count - 1] + "] int";
if (!append)
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("create table [" + Cur_File_Sheet + "] (" + header + " ) ", MyConnection);
cmd.ExecuteNonQuery();
}
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
private void AddToList(string msg)
{
int n;
DateTime st = System.DateTime.Now;
if (Bt_Record.Text == "Stop Rec.")
{
DGV_Statistics[3, 0].Value = (st.Ticks - LastReceiveTime.Ticks) / 1000000.0; // DGV_Statistics[3,0].Value = st.Subtract(LastReceiveTime);
if (int.Parse(txt_time.Text) <= 0) txt_time.Text = "1000";
textBox1.Text =Math.Truncate((((st.Ticks - LastReceiveTime.Ticks) / 10000.0) - double.Parse(txt_time.Text))).ToString();
textBox2.Text =Math.Truncate((double.Parse(textBox2.Text) + double.Parse(textBox1.Text))).ToString();
}
LastReceiveTime = st; //last receive time
if (msg.IndexOf("GW_ID=")>-1) // discovering gateways
{
int ind = msg.IndexOf("=");
int indx = (msg.IndexOf("\r")-ind-1);
cur_GW_ID += msg.Substring(ind+1,indx);
bool add_gw = false;
int y=int.Parse(msg.Substring(ind+1));
foreach(int i in G_Variables.Gateways_ID)
if (y == G_Variables.Gateways_ID[i]) add_gw = true;
if (!add_gw) //if it is new one so it will be added
{ G_Variables.Gateways_ID[G_Variables.No_GWs_Availble++] = int.Parse(msg.Substring(ind + 1)); txt_time.Text = (1000 * G_Variables.No_GWs_Availble)+""; }
for (int i = 0; i < (DGV_Gateways_Available.Rows.Count - 1); i++)
if (int.Parse(DGV_Gateways_Available.Rows[i].Cells[0].Value.ToString()) == y)
DGV_Gateways_Available.Rows[i].Cells[4].Value = Properties.Resources.on;
}
else if((msg.Contains("Connected"))||(msg.Contains("Disconnected")))
{
n = msg_listbox.Items.Add(msg);
msg_listbox.SelectedIndex = n;
msg_listbox.ClearSelected();
}
else
{
System.Data.OleDb.OleDbConnection MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Cur_Ds_File + "';Extended Properties='Excel 8.0;HDR=Yes'");
n = Lb_Sensors_Data.Items.Add(msg);
Lb_Sensors_Data.SelectedIndex = n; Lb_Sensors_Data.ClearSelected();
if (Bt_Record.Text == "Stop Rec.")
{
count_sensor_records++;
DGV_Statistics[0, 0].Value = count_sensor_records + ""; lab_SensorRecords.Text = "Recieved =" + count_sensor_records + " msg.(s)";
}
DateTime d = new DateTime();
d = DateTime.Parse(DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute);
if ((start_record) && (msg.Contains("@")))// && (rec_state != "Network"))
{
bool missing_headers_order = false;
try
{
int ind_under = (msg.IndexOf('_') - 1);
string eds = msg.Substring(2, ind_under - 1);
int gw = int.Parse(eds);
string s = msg.Substring(msg.LastIndexOf(":") + 1);
int x = s.LastIndexOf(',');
s = s.Substring(0, s.Length - 2);
s = s.Replace("@", "");
string gw_headers = "";
for (int i = 0; i < G_Variables.No_GWs_Availble; i++)
if (G_Variables.Gateways_ID[i] == gw)
{
if (Cur_Type == "Network")
gw_headers = headers[i];
else
gw_headers = headers[0];
}
x = gw_headers.LastIndexOf(',');
gw_headers = gw_headers.Substring(0, gw_headers.Length - 1);
var hlist = gw_headers.Split(',');
var vlist = s.Split(',');
// MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Cur_Ds_File + "';Extended Properties='Excel 8.0;HDR=Yes'");
MyConnection.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
// to update
string sets = ""; string sql = "";
int NumOfUpdates = 0;
if ((Cur_Type == "Network") && (G_Variables.No_GWs_Availble > 1))
{
for (int i = 0; i < hlist.Length - 1; i++)
{
try { sets += "[" + hlist[i] + "]=" + vlist[i] + ","; }
catch { missing_headers_order = true; }
}
sets += "[" + hlist[hlist.Length - 1] + "]=" + vlist[vlist.Length - 1];
sql = " update [" + Cur_File_Sheet + "] set " + sets + " where [RecDateTime] like '" + d + "' ";
cmd.CommandText = sql;
cmd.Connection = MyConnection;
NumOfUpdates = cmd.ExecuteNonQuery();
}
if (NumOfUpdates == 0)
{
sets = "";
for (int i = 0; i < hlist.Length - 1; i++)
sets += "[" + hlist[i] + "],";
sets += "[" + hlist[hlist.Length - 1] + "]";
sql = "insert into [" + Cur_File_Sheet + "] ([RecDateTime]," + sets + ") values ('" + d + "'," + s + ")";
cmd.CommandText = sql;
cmd.Connection = MyConnection;
cmd.ExecuteNonQuery();
Lab_FileRows.Text = "Recorded=" + (++count_file_rows) + " row(s)";
DGV_Statistics[1, 0].Value = count_file_rows + " row(s)";
//DGV_Statistics[2, 0].Value = System.DateTime.Now.Ticks - st.Ticks / 10000000.0;
// DGV_Statistics[4, 0].Value =""+ DateTime.Parse(DGV_Statistics[2, 0].Value.ToString()).Add( DateTime.Parse(DGV_Statistics[3, 0].Value.ToString());
}
MyConnection.Close();
}
catch { if (missing_headers_order) MessageBox.Show("Please Close Coordinator then press discover followed by reset rules and file creation, Press Yes for append message to continue"); }
DGV_Statistics[2, 0].Value = (System.DateTime.Now.Ticks - st.Ticks) / 1000000.0; //recording time
textBox3.Text = Math.Truncate(((System.DateTime.Now.Ticks - st.Ticks)/10000.0)).ToString();
textBox4.Text = Math.Truncate((double.Parse(textBox4.Text) + double.Parse(textBox3.Text))).ToString();
textBox5.Text = Math.Truncate((double.Parse(textBox2.Text) + double.Parse(textBox4.Text))).ToString();
textBox6.Text = (1-(double.Parse(textBox5.Text) / (int.Parse(DGV_Statistics[0, 0].Value.ToString()) * int.Parse(txt_time.Text)))).ToString();
}
else if ((start_record) && (msg.Contains("="))) //recieve arbitrary msgs according to changes from all devices at all available gateways
{
//try
//{
int ind_under = (msg.IndexOf('_') - 1);
string eds = msg.Substring(2, ind_under - 1);
int gw = int.Parse(eds);
string s = msg.Substring(msg.LastIndexOf(":") + 1);
int x = s.LastIndexOf(',');
s = s.Substring(0, s.Length - 2);
string gw_headers_insert = "";
string gw_inser_values = "";
string gw_headers_update = "";
var vlist = s.Split(',');
for (int i = 0; i < vlist.Length - 1; i++)
{
var pin_value = vlist[i].Split('=');
string sqlstr = "SELECT title, id, root_id FROM dbo.device WHERE (gateway_id = " + gw + ") AND (pin_id = " + pin_value[0] + ")";
con2db.Close();
con2db.Open();
SqlCommand cmdd = new SqlCommand(sqlstr, con2db);
SqlDataReader r = cmdd.ExecuteReader();
r.Read();
gw_headers_insert += "[" + r.GetString(0) + "(" + r.GetInt32(1) + "/" + r.GetInt32(2) + ":" + pin_value[0] + "/" + gw + ")]";
gw_inser_values += pin_value[1];
gw_headers_update += gw_headers_insert + "=" + pin_value[1];
gw_headers_insert += ",";
gw_inser_values += ",";
gw_headers_update += ",";
}
var lpin_value = vlist[vlist.Length - 1].Split('=');
string lsqlstr = "SELECT title, id, root_id FROM dbo.device WHERE (gateway_id = " + gw + ") AND (pin_id = " + lpin_value[0] + ")";
con2db.Close();
con2db.Open();
SqlCommand lcmd = new SqlCommand(lsqlstr, con2db);
SqlDataReader lr = lcmd.ExecuteReader();
lr.Read();
gw_headers_insert += "[" + lr.GetString(0) + "(" + lr.GetInt32(1) + "/" + lr.GetInt32(2) + ":" + lpin_value[0] + "/" + gw + ")]";
gw_inser_values += lpin_value[1];
gw_headers_update += gw_headers_insert + "=" + lpin_value[1];
int NumOfUpdates = 0;
MyConnection.Close();
MyConnection.Open();
System.Data.OleDb.OleDbCommand execlcmd = new System.Data.OleDb.OleDbCommand();
string excelstr = " update [" + Cur_File_Sheet + "] set " + gw_headers_update + " where [RecDateTime] like '" + d + "' ";
//cmd =(sql, MyConnection);
execlcmd.CommandText = excelstr;
execlcmd.Connection = MyConnection;
try
{
NumOfUpdates = execlcmd.ExecuteNonQuery();
}
catch { }
if (NumOfUpdates == 0)
{
excelstr = "insert into [" + Cur_File_Sheet + "] ([RecDateTime]," + gw_headers_insert + ") values ('" + d + "'," + gw_inser_values + ")";
execlcmd.CommandText = excelstr;
execlcmd.Connection = MyConnection;
execlcmd.ExecuteNonQuery();
Lab_FileRows.Text = "Recorded=" + (++count_file_rows) + " row(s)";
DGV_Statistics[1, 0].Value = count_file_rows + " row(s)";
}
MyConnection.Close();
//}
//catch { }
} //end else if changes
else { }
}
//DGV_Statistics[2, 0].Value = (System.DateTime.Now.Ticks - st.Ticks) / 1000000.0; //recording time
if (Bt_Record.Text == "Stop Rec.")
{
double tl = double.Parse(DGV_Statistics[2, 0].Value.ToString()) + double.Parse(DGV_Statistics[3, 0].Value.ToString());
totallatency += tl;
// MessageBox.Show(""+(totallatency / (1+count_msg)));
DGV_Statistics[4, 0].Value = totallatency;
// DGV_Statistics[5, 0].Value = totallatency / (1 + count_sensor_records);
DGV_Statistics[5, 0].Value =100-( 100*( (totallatency / (1 + count_sensor_records))/int.Parse(txt_time.Text)));
list_SynTA.Items.Add(DGV_Statistics[5, 0].Value);
list_SynTA.SelectedIndex=list_SynTA.Items.Count-1;
}
}
/// <summary>
/// 删除当前对象的所有记录（在数据库中）
/// </summary>
public void DeleteAllInDataBase()
{
string strSql = string.Empty;
string strTableName = this.m_DataSet.Tables[0].TableName;
string strKeyName = this.m_DataSet.Tables[0].PrimaryKey[0].ColumnName;
strSql = string.Format(" Delete From {0} Where {1} in ({2})", strTableName, strKeyName, GetPrimaryKeyIDs());
System.Data.OleDb.OleDbCommand objOleDbCommand = new System.Data.OleDb.OleDbCommand(strSql, this.m_DBManger.CurrentlyConnection);
objOleDbCommand.Transaction = this.m_DBManger.CurrentlyTransaction;
objOleDbCommand.ExecuteNonQuery();
}
public void UpdateTableDefs(System.Data.OleDb.OleDbConnection db)
{
if (db == null)
{
return ;
}
try
{
System.Data.OleDb.OleDbCommand SQLCom = new System.Data.OleDb.OleDbCommand("CREATE TABLE QuasiAddIn2_NETSample2 (Setup Integer, Averages Integer)", db);
SQLCom.ExecuteNonQuery();
}
catch (Exception)
{
//table already exists
//MsgBox("UpdateTableDefs " & ex.Message)
}
}
public void UpdateDateTime(string skey, DateTime StartDatetime, DateTime updatetime)
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
string Query = "Update [" + TableName + "] set submittedOn = '" + StartDatetime.ToString("dd/MM/yyyy hh:mm:ss") + "' ";
if (updatetime.Year > 1900)
{
Query += ", lastEdited = '" + updatetime.ToString("dd/MM/yyyy hh:mm:ss") + "'";
}
Query += " Where imagePath = '" + skey + "'";
cmd.CommandText = Query;
cmd.Connection = conn;
int x = cmd.ExecuteNonQuery();
}
private void btnRun_Click(object sender, EventArgs e)
{
string sDataSourceFox = txtDBName.Text.Trim();
string sCnnStrJet = "Provider=" + sProviderJet + "; Data Source=" + sDataSourceJet;
string sCnnStrFox = "Provider=" + sProviderFox + "; Data Source=" + sDataSourceFox;
// Fox
System.Data.OleDb.OleDbConnection cnnFox = null;
System.Data.OleDb.OleDbCommand cmdFox = null;
System.Data.OleDb.OleDbDataAdapter daFox = null;
System.Data.DataSet daSet = null;
//MDB
System.Data.OleDb.OleDbConnection cnnJet = null;
System.Data.OleDb.OleDbCommand cmdJet = null;
string sSqlFox = "select * from " + System.IO.Path.GetFileName(sDataSourceFox.Trim());
bool is99 = false;
string sPagina = null;
int iTotalImg = 0;
int iTotalExp = 0;
string INSUREDID = string.Empty;
string PROVIDERNPI = string.Empty;
string SERVICEDATE = string.Empty;
string NOMBREIMAGEN = string.Empty;
try
{
//fox
cnnFox = new System.Data.OleDb.OleDbConnection();
cmdFox = new System.Data.OleDb.OleDbCommand();
daFox = new System.Data.OleDb.OleDbDataAdapter();
daSet = new DataSet();
//mdb
cnnJet = new System.Data.OleDb.OleDbConnection();
cmdJet = new System.Data.OleDb.OleDbCommand();
if (cnnFox.State == ConnectionState.Closed)
{
cnnFox.ConnectionString = sCnnStrFox;
cnnFox.Open();
}
cmdFox.Connection = cnnFox;
cmdFox.CommandType = CommandType.Text;
cmdFox.CommandText = sSqlFox;
daFox.SelectCommand = cmdFox;
daFox.Fill(daSet);
if (cnnJet.State == ConnectionState.Closed)
{
cnnJet.ConnectionString = sCnnStrJet;
cnnJet.Open();
}
cmdJet.Connection = cnnJet;
cmdJet.CommandType = CommandType.Text;
cmdJet.CommandText = "delete from OHCINDEX";
cmdJet.ExecuteNonQuery();
foreach (DataTable T in daSet.Tables)
{
foreach (DataRow R in T.Rows)
{
iTotalImg++;
sPagina = R["V1PG"].ToString().Trim();
if (sPagina != "99")
{
is99 = false;
iTotalExp++;
}
else
{
is99 = true;
}
if (is99 == false)
{
INSUREDID = R["V1INSUREID"].ToString().Trim();
PROVIDERNPI = RB1500.Checked ? R["V133ANPI"].ToString().Trim() : R["V156NPI"].ToString().Trim();
SERVICEDATE = R["V1DATEFROM"].ToString().Trim();
NOMBREIMAGEN = "D:\\OHC\\" + R["V0IFNAME01"].ToString().Trim();
}
else
{
NOMBREIMAGEN = "D:\\OHC\\" + R["V0IFNAME01"].ToString().Trim();
}
/*
* InsuredId
ProviderNpi
ServiceDate
ImageFile
*/
string sSqlJet = "insert into OHCINDEX VALUES('" + INSUREDID + "','" +
PROVIDERNPI + "','" +
SERVICEDATE + "','" +
NOMBREIMAGEN + "')";
cmdJet.CommandText = sSqlJet.Trim();
cmdJet.ExecuteNonQuery();
}
}//end Loop
//Close
cnnFox.Close();
cnnJet.Close();
cnnJet.Dispose();
cnnFox.Dispose();
// Copy MDB .... //
Program.copyImg(this.txtOutPut.Text.Trim(), sMdbPath, true, "*.mdb");
MessageBox.Show("Total de Imagenes:\r" + iTotalImg.ToString(), "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
MessageBox.Show("Total de Record:\r" + iTotalExp.ToString(), "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
MessageBox.Show("Done .. \r:-) Lindo Dia ", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void UpdateEndScreen()
{
mouseState = Mouse.GetState();
if (mouseState.LeftButton == ButtonState.Pressed && PlayAgainButton.Contains(new Point(mouseState.X, mouseState.Y)))
{
Initialize();
state = GameState.menuScreen;
}
if (mouseState.LeftButton == ButtonState.Pressed && mouseState.X > 701 && mouseState.Y > 451)
{
state = GameState.highScoreScreen;
}
if (enemyHitTimes > 0)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = CONNECTION_STRING;
conn.Open();
var command = new System.Data.OleDb.OleDbCommand();
command.Connection = conn;
command.CommandText = "insert into DB ([Time]) Values (" + ((timer / 60) - (stallTime / 60)) + ")";
command.ExecuteNonQuery();
}
enemyHitTimes = 0;
}
/// <summary>
/// Run a transact SQL query against the database.
/// </summary>
/// <param name="sqlQuery">String with the SQL query (Insert, Update, Delete, etc).</param>
/// <returns>The number of rows affected or -1 if an exception occurs.</returns>
public int ExecuteQuery(string sqlQuery)
{
// Do not proceed if the database is not connected.
if (this.IsConnected == false)
{
this.hasException = true;
this.lastException = new System.Exception("You cannot query a database until you connect to it (ExecuteQuery(string). Connect first.");
if (this.ThrowExceptions == true) throw this.lastException;
return -1;
}
// Clear past exceptions
this.hasException = false;
/*
* Switch to the appropriate database client and execute the query
*
* Set the default output to -1 (which means that there was an error)
* Create a Command object
* Set the Command to use the current database's object
* Set the Command's timeout value (if exceeded, an exception will occur)
* Execute the SQL query, populating the output with the number of rows affected
* Dispose of the Command object
*/
int output = -1;
switch (this.DataClient)
{
case DatabaseClient.OleClient:
try
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = this.oleConn;
cmd.CommandText = sqlQuery;
cmd.CommandTimeout = this.CommandTimeout;
output = cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception exception)
{
hasException = true;
lastException = exception;
if (ThrowExceptions == true)
throw lastException;
}
break;
case DatabaseClient.SqlClient:
try
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = this.sqlConn;
cmd.CommandText = sqlQuery;
cmd.CommandTimeout = this.CommandTimeout;
output = cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception exception)
{
hasException = true;
lastException = exception;
if (ThrowExceptions == true) throw lastException;
}
break;
default:
this.isConnected = false;
this.hasException = true;
this.lastException = new SystemException("The database client type entered is invalid (DataClient=" + this.DataClient.ToString() + ").");
if (this.ThrowExceptions == true)
throw this.lastException;
break;
}
return output;
}
protected int ExecSQL(string SQL, System.Data.OleDb.OleDbTransaction odTrans, System.Data.OleDb.OleDbType[] ParaType, object[] ParaValue)
{
int re = -1;
ConnectionState OldConnState = odc.State;
try
{
if (odc.State == ConnectionState.Closed) odc.Open();
odCommExec = new System.Data.OleDb.OleDbCommand(SQL, odc);
odCommExec.CommandTimeout = 60 * 60 * 24;
odCommExec.Transaction = odTrans;
if (ParaType != null)
{
for (int i = 0; i < ParaType.Length; i++)
{
odCommExec.Parameters.Add("Para" + i, ParaType[i]).Value = ParaValue[i];
}
}
re = odCommExec.ExecuteNonQuery();
}
catch (Exception ex)
{
CCLog.WriteLog(ex);
throw ex;
}
finally
{
if (OldConnState == ConnectionState.Closed) odc.Close();
}
return re;
}
// create excel sheet for WoT hierarchical structure
public static void write_hierarchicals_excel(string dir, SqlConnection con2db)
{
string path=dir + "\\WoT";
string filename = "WoT_Structure.xls";
string sheet1="Building", sheet2="Device";
if (!Directory.Exists(path))
Directory.CreateDirectory(path);
if (File.Exists(path +"\\"+ filename))
{
File.Delete(path +"\\"+ filename);
}
string header1 = "[id]int,[title]string,[root_id]int,[type]string,[description]string,[image_url]string";
string header2 = "[id]int,[building_id]int,[title]string,[type]string,[model]string,[serial]string,[manufacturer]string,[description]string,[gateway_id]int,[pin_id]int,[ws_address]string,[img_on_url]string,[img_off_url]string";
System.Data.OleDb.OleDbConnection ExcelCon;
ExcelCon = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "\\" + filename + "';Extended Properties='Excel 8.0;HDR=Yes'");
ExcelCon.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("create table [" + sheet1 + "] (" + header1 + " ) ", ExcelCon);
cmd.ExecuteNonQuery();
cmd = new System.Data.OleDb.OleDbCommand("create table [" + sheet2 + "] (" + header2 + " ) ", ExcelCon);
cmd.ExecuteNonQuery();
ExcelCon.Close();
string sql1 = "Select id, title, root_id, type, description from building";
string sql2 = "Select id, root_id, title, type, model, serial, manufacturer, description, gateway_id, pin_id,ws_address from device";
con2db.Close();
con2db.Open();
ExcelCon.Close();
ExcelCon.Open();
SqlCommand sqlcmd = new SqlCommand(sql1, con2db);
SqlDataAdapter dt = new SqlDataAdapter(sqlcmd);
DataSet st = new DataSet();
dt.Fill(st);
DataRowCollection dtc = st.Tables[0].Rows;
foreach (DataRow dr in dtc)
{
try { int.Parse(dr[2] + ""); } catch{dr[2] = 0;}
//string txt = dr[4].ToString();
for (int i = 0; i < 5; i++)
{
if (dr[i].ToString().Contains("'")) dr[i] = dr[i].ToString().Replace("'s", "''s");
}
cmd.CommandText = " insert into [" + sheet1 + "] ([id],[title],[root_id],[type],[description]) values (" + dr[0] + ",'"+dr[1].ToString()+"',"+dr[2]+",'"+dr[3].ToString()+"','"+dr[4]+"')";
cmd.Connection = ExcelCon;
cmd.ExecuteNonQuery();
}
con2db.Close();
con2db.Open();
ExcelCon.Close();
ExcelCon.Open();
sqlcmd = new SqlCommand(sql2, con2db);
dt = new SqlDataAdapter(sqlcmd);
st = new DataSet();
dt.Fill(st);
dtc = st.Tables[0].Rows;
foreach (DataRow dr in dtc)
{
cmd.CommandText = " insert into [" + sheet2 + "] ([id],[building_id],[title],[type],[model],[serial],[manufacturer],[description],[gateway_id],[pin_id],[ws_address]) values (" + dr[0] + ","+dr[1]+",'"+dr[2]+"','"+dr[3]+"','"+dr[4]+"','"+dr[5]+"','"+dr[6]+"','"+dr[7]+"',"+dr[8]+","+dr[9]+",'"+dr[10]+"')"; cmd.Connection = ExcelCon;
cmd.ExecuteNonQuery();
}
con2db.Close();
ExcelCon.Close();
}
public override void RemoveRecord()
{
if (DBPtr == null)
{
return;
}
System.Data.OleDb.OleDbCommand sqlcom = new System.Data.OleDb.OleDbCommand("DELETE * FROM QuasiAddIn2_NetSample2 WHERE Setup=" + System.Convert.ToString(Setup), DBPtr);
try
{
DBPtr.Open();
sqlcom.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show("RemoveRecord " + e.Message);
}
finally
{
DBPtr.Close();
}
DBPtr = null;
}
public override void StoreParameters()
{
if (DBPtr == null)
{
return ;
}
try
{
System.Data.OleDb.OleDbCommand SQLCom = new System.Data.OleDb.OleDbCommand("DELETE * FROM QuasiAddIn2_NETSample2 WHERE Setup=" + Setup.ToString(), DBPtr);
DBPtr.Open();
SQLCom.ExecuteNonQuery();
SQLCom.CommandText = "INSERT INTO QuasiAddIn2_NETSample2 (Setup, Averages) VALUES (" + Setup.ToString() + "," + mvarAverages.ToString() + ")";
SQLCom.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("StoreParameters " + ex.Message);
}
finally
{
if (DBPtr.State != ConnectionState.Closed)
{
DBPtr.Close();
}
}
}
private void submitButton_Click(object sender, System.EventArgs e)
{
if (this.IsValid)
{
String attendees = "";
foreach (ListItem attendee in attendeeList.Items)
{
if (attendee.Selected)
{
attendees += attendee.Text + " (" + attendee.Value + "), ";
}
}
attendees += " and " + nameBox.Text;
String dateString = calendar.SelectedDate.Date.Date.ToShortDateString();
String oleDbCommand = "INSERT INTO Events (Name, Room, " +
"AttendeeList, EventDate) VALUES ('" +
eventBox.Text + "', '" +
roomList.SelectedItem.Value + "', '" +
attendees + "', '" + dateString + "')";
System.Data.OleDb.OleDbCommand insertCommand = new System.Data.OleDb.OleDbCommand(oleDbCommand, oleDbConnection1);
oleDbConnection1.Open();
int queryResult = insertCommand.ExecuteNonQuery();
if (queryResult == 1)
{
resultLabel.Text = "Event Added.";
daEvents = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Events", oleDbConnection1);
ds.Clear();
daEvents.Fill(ds, "Events");
eventTable = ds.Tables["Events"];
calendar.SelectedDate = getFreeDate(calendar.SelectedDate.AddDays(1));
eventDetails1.DataBind();
eventDetails2.DataBind();
}
else
{
resultLabel.Text = "Event not added due to DB access problem.";
}
oleDbConnection1.Close();
}
}
public override void RestoreParameters()
{
//testsetup was left for compatibility only; is not used otherwise
System.Data.OleDb.OleDbDataReader rsParameters = default(System.Data.OleDb.OleDbDataReader);
try
{
if (DBPtr == null)
{
return ;
}
DBPtr.Open();
System.Data.OleDb.OleDbCommand sqlCOM = new System.Data.OleDb.OleDbCommand("SELECT * FROM QuasiAddIn2_NetSample2 WHERE Setup=" + System.Convert.ToString(Setup)) {Connection = DBPtr};
sqlCOM.ExecuteScalar();
rsParameters = sqlCOM.ExecuteReader();
if (!rsParameters.Read())
{
//no records
rsParameters.Close();
System.Data.OleDb.OleDbCommand sqlWRCom = new System.Data.OleDb.OleDbCommand("INSERT INTO QuasiAddIn2_NetSample2 (Setup) VALUES (" + System.Convert.ToString(Setup) + ")", DBPtr);
sqlWRCom.ExecuteNonQuery();
sqlCOM.ExecuteNonQuery();
rsParameters = sqlCOM.ExecuteReader();
rsParameters.Read();
}
if (!(rsParameters == null))
{
mvarAverages = System.Convert.ToInt16(rsParameters["Averages"]);
}
base.RaiseRestoreParams();
}
catch (Exception ex)
{
MessageBox.Show("RestoreParameters " + ex.Message);
}
finally
{
if (DBPtr.State == ConnectionState.Open)
{
DBPtr.Close();
}
}
}
public bool deleteUserSQL(user record)
{
int C1;
string thisName;
string connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=|DataDirectory|\\OSLI_Database.mdb";
System.Data.OleDb.OleDbCommand
cmd = new System.Data.OleDb.
OleDbCommand();
System.Data.OleDb.OleDbCommand
cmd2 = new System.Data.OleDb.
OleDbCommand();
System.Data.OleDb.OleDbConnection
connection =
new System.Data.OleDb.
OleDbConnection(
connectionString);
try
{
if (findUserIndex(record))
{
C1 = record.index1;
thisName = (string)(OLSI_DS.
DST_Users.Rows[C1]
["Username"]);
cmd.CommandType =
System.Data.
CommandType.Text;
cmd.Connection = connection;
connection.Open();
cmd.CommandText =
"DELETE FROM DST_List.DST_Users WHERE Username = \'" +
thisName + "\'";
cmd.ExecuteNonQuery();
connection.Close();
connection.Open();
cmd.CommandText =
"DELETE FROM DST_List.Users_Details WHERE Username = \'" +
thisName + "\'";
cmd.ExecuteNonQuery();
connection.Close();
OLSI_UA.Fill(OLSI_DS.DST_Users);
OLSI_DA.Fill(
OLSI_DS.Users_Details);
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
Console.WriteLine(
"DeleteUserSQL: " +
ex.ToString());
return false;
}
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.Text.ToString() == "")
{
MessageBox.Show("Выбери год", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (comboBox2.Text.ToString() == "")
{
MessageBox.Show("Выбери месяц", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox1.Text == "")
{
MessageBox.Show("Выбери файл", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
string strConn;
//Check for Excel version
if (textBox1.Text.Substring(textBox1.Text.LastIndexOf('.')).ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 12.0;HDR=YES; IMEX=0\"";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=0\"";
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strConn);
con.Open();
DataSet ds = new DataSet();
DataTable shemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string sheet1 = (string)shemaTable.Rows[0].ItemArray[2];
string select = String.Format("SELECT * FROM [{0}]", sheet1);
System.Data.OleDb.OleDbDataAdapter ad = new System.Data.OleDb.OleDbDataAdapter(select, con);
ad.Fill(ds);
if (System.IO.File.Exists("your_base.mdb"))
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
ADOX.Catalog cat = new ADOX.Catalog();
string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5";
cat.Create(String.Format(connstr, "your_base.mdb"));
cat = null;
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
Querry("CREATE TABLE SURVEY(Anim varchar(255), weight int, height int, s_year int, s_month int);", "your_base.mdb");
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
//string comm = "Insert into SURVEY (Anim, weight, height) VALUES (hare, 10, 20)";
//System.Data.OleDb.OleDbDataAdapter dbAdp = new System.Data.OleDb.OleDbDataAdapter(comm, conStr);
//dbAdp.Update(ds.Tables[0]);
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
con.Close();
}
}
/// <summary>
/// InsertData
/// </summary>
/// <param name="fileName">模板文件在服务器端的完全路径</param>
/// <param name="theData"></param>
private bool InsertData(string fileName, DataTable theData)
{
// connection use oledb
DataTable dt = theData;
string strConn = string.Format(CONNECTION_STRING_WRITE, fileName);
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
System.Data.OleDb.OleDbCommand cmd = null;
bool bRet = false;
try
{
conn.Open();
//cmd = new System.Data.OleDb.OleDbCommand("Create table [sheet1]([name] Text, [age] int, [phone] Text)", conn);
//cmd.ExecuteNonQuery();
string strSQL = InsertSQL;
cmd = new System.Data.OleDb.OleDbCommand(strSQL, conn);
for (int i = 0; i < dt.Columns.Count; i++)
{
cmd.Parameters.Add(i.ToString(), System.Data.OleDb.OleDbType.VarChar);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
cmd.Parameters[j.ToString()].Value = dt.Rows[i][j].ToString();
}
cmd.ExecuteNonQuery();
}
bRet = true;
}
catch (Exception e2)
{
bRet = false;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
conn.Dispose();
}
return bRet;
}
