public bool DoLogin(string username, string password, bool IsAdmin)
{
OleDbCommand com = new OleDbCommand("", MySchool.SLConnection);
bool ReturnMe = false;
if (IsAdmin)
com.CommandText = "select UID, UPass from LoginInfo where UName = '" + username + "' and UPass = '******'";
else
com.CommandText = "select UID, pass from UserInfo where UserName = '******' and Pass ='******'";
com.Connection.Open();
if (com.ExecuteScalar() != null)
{
if (IsAdmin)
{
TheSessions.EmploeeID = com.ExecuteScalar().ToString();
TheSessions.EmploeeName = username;
TheSessions.IsAdmin = true;
}
else
{
TheSessions.StudentID = com.ExecuteScalar().ToString();
TheSessions.StudentName = username;
TheSessions.IsAdmin = false;
}
ReturnMe = true;
}
else
ReturnMe = false;
com.Connection.Close();
return ReturnMe;
}
internal static int GetApplicationID(OleDbConnection connection, string applicationName, bool createIfNeeded)
{
OleDbCommand lookupCommand = new OleDbCommand("SELECT ApplicationId FROM aspnet_Applications WHERE ApplicationName = @AppName", connection);
lookupCommand.Parameters.Add(new OleDbParameter("@AppName", applicationName));
object lookupResult = lookupCommand.ExecuteScalar();
if ((lookupResult != null) && (lookupResult is int)) {
return (int)lookupResult;
}
if (createIfNeeded) {
OleDbCommand createCommand = new OleDbCommand("INSERT INTO aspnet_Applications (ApplicationName) VALUES (@AppName)",
connection);
createCommand.Parameters.Add(new OleDbParameter("@AppName", applicationName));
if (createCommand.ExecuteNonQuery() != 0) {
lookupResult = lookupCommand.ExecuteScalar();
if ((lookupResult != null) && (lookupResult is int)) {
return (int)lookupResult;
}
}
}
return 0;
}
public static bool FormsAuthenticate(string userName, string password)
{
bool authenticated = false;
switch (Mode)
{
case AuthenticationMode.None:
authenticated = FormsAuthentication.Authenticate(userName, password);
break;
case AuthenticationMode.Database:
using (OleDbConnection connection = AppContext.GetDatabaseConnection())
{
string format = String.Format("select count(*) from {0}User where UserName = '******' and Password = '******' and Active = 1", AppSettings.ConfigurationTablePrefix, userName);
using (OleDbCommand command = new OleDbCommand(String.Format(format, password), connection))
{
authenticated = Convert.ToInt32(command.ExecuteScalar()) > 0;
if (!authenticated)
{
command.CommandText = String.Format(format, HashPassword(password));
authenticated = Convert.ToInt32(command.ExecuteScalar()) > 0;
}
}
}
break;
}
return authenticated;
}
protected void Page_Load(object sender, EventArgs e)
{
//TheSessions.StudentID = "494108";
DataTable DT = new DataTable("ReadedBooks");
OleDbConnection AccessCon = MySchool.LibraryCodeConnection;
OleDbCommand AccessCMD = new OleDbCommand("", AccessCon);
try
{
if (TheSessions.StudentID != null)
{
OleDbDataAdapter AccessDA = new OleDbDataAdapter("SELECT motaraded.[number], book.name_book, motaraded.[date], book.code_printl, '' As name_printl, book.code_fari, '' As name_fari, book.code_tahasos, '' As name_tahasos, book.code_alnasher, '' As name_alnasher, (SELECT TOP 1 fm1 + ' ' + lm1 AS msl FROM masol WHERE ([number] = book.[number]) AND (k1 = 'مؤلف')) As masol FROM (motaraded INNER JOIN book ON motaraded.[number] = book.[number]) WHERE (motaraded.code_name = " + TheSessions.StudentID + ")", MySchool.LibraryConnection);
AccessDA.Fill(DT);
}
AccessCon.Open();
foreach (DataRow DR in DT.Rows)
{
if (DR["code_printl"].ToString() != string.Empty)
{
AccessCMD.CommandText = "Select name_printl from printl Where code_printl = " + DR["code_printl"].ToString();
DR["name_printl"] = AccessCMD.ExecuteScalar();
}
else { DR["name_printl"] = string.Empty; }
if (DR["code_fari"].ToString() != string.Empty)
{
AccessCMD.CommandText = "Select name_fari from fari Where code_fari = " + DR["code_fari"].ToString();
DR["name_fari"] = AccessCMD.ExecuteScalar();
}
else
{ DR["name_fari"] = string.Empty; }
if (DR["code_tahasos"].ToString() != string.Empty)
{
AccessCMD.CommandText = "Select name_tahasos from tahasos Where code_tahasos = " + DR["code_tahasos"].ToString();
DR["name_tahasos"] = AccessCMD.ExecuteScalar();
}
else
{ DR["name_tahasos"] = string.Empty; }
if (DR["code_alnasher"].ToString() != string.Empty)
{
AccessCMD.CommandText = "Select name_alnasher from alnasher Where code_alnasher = " + DR["code_alnasher"].ToString();
DR["name_alnasher"] = AccessCMD.ExecuteScalar();
}
else
{ DR["name_alnasher"] = string.Empty; }
}
AccessCon.Close();
GridViewReadedBooks.DataSource = DT;
ViewState["ReadedBooksTable"] = DT;
GridViewReadedBooks.DataBind();
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
/// <summary>
/// Generic method for issuing simple Insert commands to the current VWA4 database,
/// based on provided connection.
/// </summary>
/// <param name="sql">SQL query string to execute.</param>
/// <param name="conn">OleDbConnection to desired database.</param>
/// <param name="trans"></param>
/// <returns>PK/ID of inserted record.</returns>
public static int Insert(string sql, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
{
// Connect up to the database
int id = -1;
try
{
// Tee up the command
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
//
// Issue the command
//
if (trans != null)
{
cmd.Transaction = trans;
}
int iSqlStatus = cmd.ExecuteNonQuery();
//Now check the status
if (iSqlStatus <= 0)
{
//DO your failed messaging here
MessageBox.Show(null, "VWA4.NET INSERT ERROR: Query Failed to insert with status: " + iSqlStatus, "Insert Error");
}
cmd.CommandText = "SELECT @@Identity";
id = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show(null, "VWA4.NET INSERT Exception: Error " + ex.Message + "!", "Insert Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(id);
}
/// <summary>取得Scalar物件</summary>
/// <param name="strSQL">SQL 指令</param>
/// <param name="OleDbConn">OleDbConnection 連線物件(不指定表使用預設連線)</param>
/// <returns>Object</returns>
/// <remarks></remarks>
public static object getOleDbScalar(string strSQL, System.Data.OleDb.OleDbConnection OleDbConn)
{
System.Data.OleDb.OleDbCommand cmd = null;
object objScalar = null;
try
{
if (OleDbConn == null)
{
OleDbConn = createOleDbConnection();
}
if (!(OleDbConn.State == ConnectionState.Open))
{
OleDbConn.Open();
}
cmd = new System.Data.OleDb.OleDbCommand(strSQL, OleDbConn);
objScalar = cmd.ExecuteScalar();
}
catch (Exception Ex)
{
//Message.alertMessage("C0001", null, Ex.Message.ToString(), null);
objScalar = null;
}
finally
{
if (!(OleDbConn.State == ConnectionState.Closed))
{
OleDbConn.Close();
}
}
return(objScalar);
}
public static int? isStudentRegistered(int? sessionID, int? year, int? term, int? stdID)
{
OleDbConnection connection = new OleDbConnection(global::ResultManagement.Properties.Settings.Default.ConnectionString);
int? Registered_Session_ID = null;
string cmdStr = @"SELECT Registered_Session.ID
FROM Registered_Session
WHERE
Std_ID = @Std_ID AND
Sess_ID = @Sess_ID AND
Reg_Year = @Reg_Year AND
Reg_Term = @Reg_Term";
OleDbCommand cmd = new OleDbCommand(cmdStr, connection);
cmd.Parameters.Add("@Std_ID", OleDbType.Integer).Value = stdID;
cmd.Parameters.Add("@Sess_ID", OleDbType.Integer).Value = sessionID;
cmd.Parameters.Add("@Reg_Year", OleDbType.Integer).Value = year;
cmd.Parameters.Add("@Reg_Term", OleDbType.Integer).Value = term;
try
{
connection.Open();
Registered_Session_ID = (int?)cmd.ExecuteScalar();
}
finally
{
connection.Close();
}
return Registered_Session_ID;
}
public string LoadPage()
{
LblError.Visible = false;
//Load data from file to RadEditor
string Path = string.Empty;
OleDbConnection Con = MySchool.QAConnection;
OleDbCommand CMD = new OleDbCommand("SELECT Data_Path FROM QAMenu Where QAID = " + QAMainMenu.SelectedValue.ToString(), Con);
try
{
Con.Open();
Path = CMD.ExecuteScalar().ToString();
}
catch { }
Con.Close();
if (Path == string.Empty)
{
//HtmlEditorQA.Html = string.Empty;
return string.Empty;
}
Path = MapPath(Path);
if (!File.Exists(Path))
{
return string.Empty;
}
else
{
TextReader TR = new StreamReader(Path);
return TR.ReadToEnd().ToString();
}
}
public int EnterToSite(UserDetails userDetails)//בודק על פי האימייל והסיסמא האם המשתשמש שמנסה להיכנס לאתר קיים או לא
{
OleDbCommand myCmd = new OleDbCommand("CheckIfUserExist", myConnection);
myCmd.CommandType = CommandType.StoredProcedure;
OleDbParameter objParam;
objParam = myCmd.Parameters.Add("@Email", OleDbType.BSTR);
objParam.Direction = ParameterDirection.Input;
objParam.Value = userDetails.email;
objParam = myCmd.Parameters.Add("@Password", OleDbType.BSTR);
objParam.Direction = ParameterDirection.Input;
objParam.Value = userDetails.password;
int x = 0;
try
{
myConnection.Open();
x = (int)myCmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
myConnection.Close();
}
return x;
}
//--function for returning a value queried by the user witht the help of an datareader--->
public string execute_scalar(string s)
{ this.connect();
OleDbCommand cmd = new OleDbCommand(s, this.con);
return_result = Convert.ToString(cmd.ExecuteScalar());
this.disconnect();
return return_result;
}
private static string GetRole(OleDbConnection connection)
{
IPrincipal user = HttpContext.Current.User;
string role = "public";
if (user.Identity != null && user.Identity.IsAuthenticated)
{
if (AppAuthentication.Mode == AuthenticationMode.None)
{
role = "admin";
}
else
{
string sql = String.Format("select Role from {0}User where UserName = '******' and Role is not null",
AppSettings.ConfigurationTablePrefix, user.Identity.Name);
OleDbCommand command = new OleDbCommand(sql, connection);
role = command.ExecuteScalar() as string;
if (String.IsNullOrEmpty(role))
{
role = "private";
}
}
}
return role;
}
public static int Add(SachDto sach)
{
OleDbConnection cn;
cn = DataProvider.ConnectionData();
string strSQL;
strSQL = "Insert into Sach(TenSach,LoaiSach,TacGia,TrangThai,SoTrang,NgonNgu) values (?,?,?,?,?,?)";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
cmd.Parameters.Add("@TenSach", OleDbType.WChar);
cmd.Parameters.Add("@LoaiSach", OleDbType.Integer);
cmd.Parameters.Add("@TacGia", OleDbType.WChar);
cmd.Parameters.Add("@TrangThai", OleDbType.Boolean);
cmd.Parameters.Add("@SoTrang", OleDbType.Integer);
cmd.Parameters.Add("@NgonNgu", OleDbType.Integer);
cmd.Parameters["@TenSach"].Value = sach.TenSach;
cmd.Parameters["@LoaiSach"].Value = sach.LoaiSach;
cmd.Parameters["@TacGia"].Value = sach.TacGia;
cmd.Parameters["@TrangThai"].Value = sach.TrangThai;
cmd.Parameters["@SoTrang"].Value = sach.SoTrang;
cmd.Parameters["@NgonNgu"].Value = sach.NgonNgu;
cmd.ExecuteNonQuery();
strSQL = "Select @@IDENTITY";
cmd = new OleDbCommand(strSQL, cn);
sach.MSach = (int)cmd.ExecuteScalar();
cn.Close();
return sach.MSach;
}
//返回一个单值，一般用select操作
public string ExecScalar(OleDbCommand myCmd)
{
string strSql;
try
{
if (myCmd.Connection.State != ConnectionState.Open)
{
myCmd.Connection.Open();
}
strSql=Convert.ToString(myCmd.ExecuteScalar());
return strSql ;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (myCmd.Connection.State == ConnectionState.Open)
{
myCmd.Connection.Close();
}
}
}
public static string fcnDecrypt(string _strTextToDecrypt)
{
string strRes = "";
string strPassphrase = "";
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
string strSQL = "SELECT strEncPassPhrase " +
"FROM tblCampDefaults";
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
strPassphrase = cmdDB.ExecuteScalar().ToString();
conDB.Close();
}
try { strRes = fcnDecrypt(_strTextToDecrypt, strPassphrase); }
catch { }
if (strRes == "") strRes = _strTextToDecrypt;
return strRes;
}
public string ExecuteScalar(string sql)
{
try
{
using (OleDbConnection connection = new OleDbConnection(_ConnectionString))
{
WriteLog(sql);
connection.Open();
var cmd = new OleDbCommand(sql, connection);
object o = cmd.ExecuteScalar();
string s = null;
if (o != null && Object.Equals(o, System.DBNull.Value) == false)
{
s = o.ToString();
}
WriteLog("查询结果：" + s);
return s;
}
}
catch (Exception ex)
{
if (AfterExceptionThrow != null) AfterExceptionThrow(ex);
else throw ex;
return null;
}
}
public string LoadContain()
{
if (Request.QueryString["CourseId"] == null)
{
return string.Empty;
}
string ThePath = string.Empty;
string RetData = string.Empty;
using (OleDbConnection Con = new OleDbConnection(constr))
{
OleDbCommand cmd = new OleDbCommand(String.Format("SELECT TOP 1 DataPath FROM CoursenotimeDataPath WHERE CourseId = {0}", Request.QueryString["CourseId"]), Con);
try
{
Con.Open();
ThePath = cmd.ExecuteScalar().ToString();
//if (ThePath != string.Empty)
// ThePath = MapPath(DB.CourseNoTimeFileDir + ThePath);
ThePath = DB.CourseNoTimeFileDir + ThePath;
TextReader TR = new StreamReader(ThePath);
RetData = TR.ReadToEnd();
TR.Close();
TR.Dispose();
}
catch (Exception ex)
{
RetData = ex.Message;
}
Con.Close();
}
return HttpUtility.HtmlDecode(RetData);
}
public BL.Domain.Ticket CreateTicket(BL.Domain.Ticket ticket)
{
string insertStatement = "INSERT INTO Ticket(AccountId, [Text],DateOpened, State, DeviceName) VALUES(@accountId, @text, @dateOpened, @state,@deviceName)";
using (var connection = this.GetConnection())
{
OleDbCommand command = new OleDbCommand(insertStatement, connection);
command.Parameters.AddWithValue("@accountId", ticket.AccountId);
command.Parameters.AddWithValue("@text", ticket.Text);
command.Parameters.AddWithValue("@dateOpened",
ticket.DateOpened.ToString("yyyy-MM-dd HH:mm:ss"));
command.Parameters.AddWithValue("@state", (byte)ticket.State);
if (ticket is HardwareTicket)
command.Parameters.AddWithValue("@deviceName",
((HardwareTicket)ticket).DeviceName);
else
command.Parameters.AddWithValue("@deviceName", DBNull.Value);
connection.Open();
command.ExecuteNonQuery();
string retrievalQuery = "Select @@Identity";
OleDbCommand retrievalOfNewNbrCommand = new OleDbCommand(retrievalQuery, connection);
ticket.TicketNumber = Convert.ToInt32(retrievalOfNewNbrCommand.ExecuteScalar());
connection.Close();
} return ticket;
}
public void AccessGuideJoinExcel(string Access, string AccTable, string Excel)
{
try
{
string tem_sql = "";//定义字符串
string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Access + ";Persist Security Info=True";//记录连接Access的语句
System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr);//连接Access数据库
System.Data.OleDb.OleDbCommand tem_comm;//定义OleDbCommand类
tem_conn.Open();//打开连接的Access数据库
tem_sql = "select Count(*) From " + AccTable;//设置SQL语句，获取记录个数
tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类
int RecordCount = (int)tem_comm.ExecuteScalar();//执行SQL语句，并返回结果
//每个Sheet只能最多保存65536条记录。
tem_sql = @"select top 65535 * into [Excel 8.0;database=" + Excel + @".xls].[Sheet2] from 帐目";//记录连接Excel的语句
tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类
tem_comm.ExecuteNonQuery();//执行SQL语句，将数据表的内容导入到Excel中
tem_conn.Close();//关闭连接
tem_conn.Dispose();//释放资源
tem_conn = null;
MessageBox.Show("导入完成");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message,"提示！");
}
}
public int getid()
{
int z=0;
OleDbCommand cmd = new OleDbCommand("Select max(t_id) from [transaction]",con);
try
{
con.Open();
object a = cmd.ExecuteScalar();
if (a == DBNull.Value)
{
z = 1;
}
else
{
z = (int)a + 1;
}
}
catch (Exception)
{
return -1;
}
finally
{
con.Close();
}
return z;
}
public static object GetSingle(string SQLString, params System.Data.OleDb.OleDbParameter[] cmdParms)
{
object result;
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
using (System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand())
{
try
{
DbHelperOleDb.PrepareCommand(oleDbCommand, oleDbConnection, null, SQLString, cmdParms);
object obj = oleDbCommand.ExecuteScalar();
oleDbCommand.Parameters.Clear();
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
result = null;
}
else
{
result = obj;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
}
}
return(result);
}
public static object GetSingle(string SQLString)
{
object result;
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
using (System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(SQLString, oleDbConnection))
{
try
{
oleDbConnection.Open();
object obj = oleDbCommand.ExecuteScalar();
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
result = null;
}
else
{
result = obj;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
oleDbConnection.Close();
throw new Exception(ex.Message);
}
}
}
return(result);
}
public string LoadContain()
{
if (QAMainMenu.SelectedValue == "")
{
return "";
}
string ThePath = string.Empty;
string RetData = string.Empty;
OleDbConnection Con = MySchool.QAConnection;
OleDbCommand CMD = new OleDbCommand("SELECT Data_Path FROM QAMenu WHERE (QAID = " + QAMainMenu.SelectedValue.ToString() + ")", Con);
try
{
Con.Open();
ThePath = CMD.ExecuteScalar().ToString();
if (ThePath != string.Empty)
{
ThePath = MapPath(ThePath);
}
if (File.Exists(ThePath))
{
TextReader TR = new StreamReader(ThePath);
RetData = TR.ReadToEnd();
}
}
catch (Exception ex)
{
RetData = ex.Message;
}
Con.Close();
return RetData;
}
public BL.Domain.TicketResponse CreateTicketResponse(BL.Domain.TicketResponse response)
{
if (response.Ticket != null)
{
string insertStatement = "INSERT INTO TicketResponse([Text], [Date], IsClientResponse"
+ ", Ticket_TicketNumber) VALUES (@text, @date"
+ ", @isClientResponse, @tickedNumber)";
using (var connection = this.GetConnection())
{
OleDbCommand command = new OleDbCommand(insertStatement, connection);
command.Parameters.AddWithValue("@text", response.Text);
command.Parameters.AddWithValue("@date", response.Date.ToString("yyyy-MM-dd HH:mm:ss"));
command.Parameters.AddWithValue("@isClientResponse", response.IsClientResponse);
command.Parameters.AddWithValue("@tickedNumber", response.Ticket.TicketNumber);
connection.Open();
command.ExecuteNonQuery();
// Retrieve primary key ‘Id' of inserted ticketresponse
//When retrieving this data from Access, we must create and execute 2 commands
string retrievalQuery = "Select @@Identity";
OleDbCommand retrievalOfNewNbrCommand = new OleDbCommand(retrievalQuery, connection);
response.Id = Convert.ToInt32(retrievalOfNewNbrCommand.ExecuteScalar());
connection.Close(); // good practice!
}
return response;
}
else
throw new ArgumentException("The ticketresponse has no ticket attached to it");
}
public string selectOne(string q)
{
OleDbCommand cmd = new OleDbCommand(q, connection);
object o = cmd.ExecuteScalar();
if (o == null) return "";
else return o.ToString();
}
protected void LinkButton2_Click(object sender, EventArgs e)
{
try
{
connection.Open();
string qry1 = "select Upload from FilesTable where Number=" + 2 + "";
OleDbCommand cmd = new OleDbCommand(qry1, connection);
cmd.ExecuteNonQuery();
string fileName = cmd.ExecuteScalar().ToString();
string fileExtension = ".txt/.jpg/.pdf/.docx/.xls";
// Set Response.ContentType
Response.ContentType = GetContentType(fileExtension);
// Append header
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
// Write the file to the Response
Response.TransmitFile(Server.MapPath("~/Files/" + fileName));
Response.End();
}
catch
{ }
finally
{
connection.Close();
}
}
public static long fcnGetIRRegCount()
{
//get current count of web registrations
string strSQL;
long lngRes = 0;
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
strSQL = "SELECT Count(lngRegistrationWebID) AS lngRegCount " +
"FROM tblWebIndRegistrations;";
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
{
try { lngRes = Convert.ToInt32(cmdDB.ExecuteScalar()); }
catch { lngRes = 0; }
}
conDB.Close();
}
return lngRes;
}
public void run()
{
OleDbConnection con = null;
OleDbTransaction txn;
Exception exp = null;
try
{
BeginCase("OleDbTransaction Rollback");
//
//prepare data
base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
string Result = "";
con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
txn = con.BeginTransaction();
OleDbCommand cmd = new OleDbCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn);
cmd.ExecuteNonQuery();
txn.Rollback();
//
//
cmd = new OleDbCommand("Select LastName From Employees Where EmployeeID = 100", con);
Result = cmd.ExecuteScalar().ToString();
Compare(Result,"Last100" );
this.Log(Result);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
public static int GetCount()
{
OleDbConnection dbConnection;
OleDbCommand dbCommand;
object obj;
string buff;
int result = 0;
try
{
dbConnection = new OleDbConnection(ConnectionStringsManager.GetConnectionString());
dbCommand = new OleDbCommand(_SELECT_COUNT, dbConnection);
dbConnection.Open();
try
{
obj = dbCommand.ExecuteScalar();
buff = obj.ToString();
Int32.TryParse(buff, out result);
}
finally
{
dbConnection.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return result;
}
public string LoadPage()
{
string Path = string.Empty;
OleDbConnection Con = new OleDbConnection(constr);
OleDbCommand CMD = new OleDbCommand("SELECT Data_Path FROM MenuItem Where ItemID = " + MainMenu.SelectedValue.ToString(), Con);
try
{
Con.Open();
Path = CMD.ExecuteScalar().ToString();
}
catch { }
Con.Close();
if (Path == string.Empty)
{
//HtmlEditorQA.Html = string.Empty;
return string.Empty;
}
Path = MapPath(Path);
if (!File.Exists(Path))
{
return string.Empty;
}
else
{
TextReader TR = new StreamReader(Path);
string tt = TR.ReadToEnd().ToString();
TR.Close();
TR.Dispose();
return tt;
}
}
public void AccessGuideJoinExcel(string Access, string AccTable, string Excel)
{
try
{
string tem_sql = ""; //定义字符串
string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Access + ";Persist Security Info=True"; //记录连接Access的语句
System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr); //连接Access数据库
System.Data.OleDb.OleDbCommand tem_comm; //定义OleDbCommand类
tem_conn.Open(); //打开连接的Access数据库
tem_sql = "select Count(*) From " + AccTable; //设置SQL语句，获取记录个数
tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn); //实例化OleDbCommand类
int RecordCount = (int)tem_comm.ExecuteScalar(); //执行SQL语句，并返回结果
//每个Sheet只能最多保存65536条记录。
tem_sql = @"select top 65535 * into [Excel 8.0;database=" + Excel + @".xls].[Sheet2] from 帐目"; //记录连接Excel的语句
tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn); //实例化OleDbCommand类
tem_comm.ExecuteNonQuery(); //执行SQL语句，将数据表的内容导入到Excel中
tem_conn.Close(); //关闭连接
tem_conn.Dispose(); //释放资源
tem_conn = null;
MessageBox.Show("导入完成");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示！");
}
}
public static object ExecuteScalar(string sql)
{
if (myOleDbConnection == null)
Connect();
OleDbCommand myOleDbCommand = new OleDbCommand(sql, myOleDbConnection);
return myOleDbCommand.ExecuteScalar();
}
/// <summary></summary>
/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
///
/// SELECT 语句
/// <returns></returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用
public object ExecuteScalar(string sql)
{
using (oleDbConnection = this.GetOleDbConnection())
{
if (oleDbConnection == null)
{
return(null);
}
try
{
if (oleDbConnection.State == System.Data.ConnectionState.Closed)
{
oleDbConnection.Open();
}
oleDbCommand = new OleDbCommand(sql, oleDbConnection);
return(oleDbCommand.ExecuteScalar());
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return(null);
}
}
}
public frmCollectRefundAmt(long _lngGGCCRegistrationWebID)
{
InitializeComponent();
string strSQL = "";
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
strSQL = "SELECT curDeposit " +
"FROM tblWebGGCCRegistrations " +
"WHERE lngGGCCRegistrationWebID=" + _lngGGCCRegistrationWebID.ToString();
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
{
decimal decDeposit = 0;
try { decDeposit = Convert.ToDecimal(cmdDB.ExecuteScalar()); }
catch { decDeposit = 0; }
lblDeposit.Text = decDeposit.ToString("C");
txtAmt.Text = decDeposit.ToString();
}
conDB.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;" +
@"Data source= C:\Users\Ahmed Kamel Taha\Documents\Visual Studio 2015\Projects\ASP Training\bns.accdb";
conn.Open();
string q = "select count(*) from users where name='" + TextBox1.Text + "' And password='******';";
OleDbCommand comm = new OleDbCommand(q, conn);
if (Convert.ToInt32(comm.ExecuteScalar().ToString()) == 1)
{
Session["user"] = TextBox1.Text;
Response.Redirect("index.aspx");
}
else
{
Response.Write("<script> alert(\"wrong pass or user\"); </script>");
}
conn.Close();
}catch(Exception ex)
{
Response.Write(ex.ToString());
}
}
protected void Page_Load(object sender, EventArgs e)
{
string query = this.Request.QueryString.ToString();
int index = query.IndexOf('=');
query = query.Substring(index + 1);
int FlatID = Convert.ToInt32(query);
string UserName;
OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["RealEstate"].ConnectionString);
OleDbCommand cmd = new OleDbCommand(String.Format("SELECT UserName FROM Flats WHERE FlatID = {0}", FlatID), conn);
cmd.Connection = conn;
conn.Open();
UserName = cmd.ExecuteScalar().ToString();
conn.Close();
if (this.User.Identity.Name == UserName || this.User.IsInRole("Manager") || this.User.IsInRole("Operator"))
{
this.ButtonDeleteRecord.Visible = true;
}
else
{
this.ButtonDeleteRecord.Visible = false;
}
}
public static int InsertIntoOringalTransaction(BankAccount bankAccount, Transaction transaction, string categoryName)
{
string cmdText = "INSERT INTO tblOrginalTransaction(";
cmdText += "Verified, TransactionID, TransactionDate, TransactionAmount, Merchant, BankMemo, BankAccountId, TransactionType";
if (!string.IsNullOrEmpty(transaction.CheckNumber))
cmdText += ", CheckNumber";
if (categoryName != "")
cmdText += ", CategoryName";
cmdText += ") Values(";
cmdText += "false";
cmdText += ", '" + transaction.TransactionID + "'";
cmdText += ", #" + Convert.ToString(transaction.TransactionDate) + "#";
cmdText += ", " + Convert.ToString(transaction.TransactionAmount);
cmdText += ", '" + transaction.MerchantName.Replace(@"'", "''") + "'";
cmdText += ", '" + transaction.BankMemo.Replace(@"'", "''") + "'";
cmdText += ", " + Convert.ToString(bankAccount.BankAccountID);
cmdText += ", '" + transaction.TransactionType + "'";
if (!string.IsNullOrEmpty(transaction.CheckNumber))
cmdText += ", '" + transaction.CheckNumber + "'";
if (categoryName != "")
cmdText += ", '" + categoryName + "'";
cmdText += ")";
string identity = " SELECT @@Identity";
int orginalTransactionID;
using (OleDbConnection myConnection = new OleDbConnection(
ConfigurationManager.ConnectionStrings["BeanCounterDB"].ToString()))
{
myConnection.Open();
using (OleDbCommand myCommand = new OleDbCommand(cmdText, myConnection))
myCommand.ExecuteNonQuery();
using (OleDbCommand myCommand = new OleDbCommand(identity, myConnection))
orginalTransactionID = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
}
return orginalTransactionID;
}
/// <summary>
/// This function will run an SQL sentence and returns the first column of the first row returned by running it.
/// </summary>
/// <param name="strSQL">The SQL sentence to run</param>
/// <returns>The first column of the first row returned by running it. Returned as object and should be converted to the appropriate type before running</returns>
public object RunScalarSQL(string strSQL)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"provider=microsoft.jet.oledb.4.0;data source=" + this.path;
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandText = strSQL;
cmd.Connection = conn;
conn.Open();
Object obj = cmd.ExecuteScalar();
conn.Close();
return(obj);
}
public string getASIN(string imageURL)
{
string asin;
OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["databaseString"].ConnectionString);
conn.Open();
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "Select ItemID From Item Where ImageURL = '" + imageURL + "'";
asin = (cmd.ExecuteScalar()).ToString();
conn.Close();
return(asin);
}
public void addItemToDatabase(string asin)
{
string description = getItemDescription(asin);
string price = getCurrentPrice(asin);
string lastStoredPrice = "0";
string url = getImageURL(asin);
DateTime date = DateTime.Now;
OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["databaseString"].ConnectionString);
conn.Open();
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "Insert Into User_Item (UserID, ItemID) values ('testuser', '" + asin + "')";
cmd.ExecuteNonQuery();
//only insert into price if price is different from current price
try
{
cmd.CommandText = "Select Price From Price Where ItemID = '" + asin + "' Order By PriceDate DESC";
lastStoredPrice = (cmd.ExecuteScalar()).ToString();
}
catch (Exception)
{
//do nothing if no record found in price
}
if (lastStoredPrice != price)
{
cmd.CommandText = "Insert Into Price(ItemID, Price, PriceDate) values ('" + asin + "', '" + price + "', '" + date + "')";
cmd.ExecuteNonQuery();
}
try
{
cmd.CommandText = "Insert Into Item (ItemID, Description, ImageURL, CurPrice, CurDate) values ('" + asin + "', '" + description + "', '" + url + "', '" + price + "', '" + date + "')";
cmd.ExecuteNonQuery();
}
catch (Exception)
{
//do nothing if duplicate
}
conn.Close();
}
public bool update_history(string dev)
{
string sheet = (dev.Replace('/', '-')).Replace(':', '@');
string src = "C:\\WoT Testbed\\" + sheet + ".xls";
string headers = "[RecDateTime] datetime, [reading] int, [state] int";
System.Data.OleDb.OleDbCommand cmd;
System.Data.OleDb.OleDbConnection ExcelCon_dev = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + src + "';Extended Properties=Excel 8.0;");
if (!File.Exists(src))
{
cmd = new System.Data.OleDb.OleDbCommand("create table [" + sheet + "] (" + headers + " ) ", ExcelCon_dev);
ExcelCon_dev.Open();
cmd.ExecuteNonQuery();
ExcelCon_dev.Close();
}
try
{
// check history size
ExcelCon_dev.Open();
cmd = new System.Data.OleDb.OleDbCommand("Select count([reading]) from [" + sheet + "] ", ExcelCon_dev);
int history_count = int.Parse(cmd.ExecuteScalar().ToString());
DateTime d = new DateTime();
d = DateTime.Parse(DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute);
// delete old rows
string val = monitor(dev);
if (history_count > 20)
{
cmd = new OleDbCommand(" update [" + sheet + "] set [RecDateTime]='" + d + "', [reading]=" + int.Parse(val) + " where [RecDateTime]=(select min([RecDateTime]) from [" + sheet + "]) ", ExcelCon_dev);
}
// insert new rows
else
{
cmd = new OleDbCommand(" insert into [" + sheet + "] ([RecDateTime], [reading]) values ('" + d + "','" + val + "')", ExcelCon_dev);
}
cmd.ExecuteNonQuery();
ExcelCon_dev.Close();
}
catch { ExcelCon_dev.Close(); }
return(true);
}
public static object ExecuteScalar(System.Data.OleDb.OleDbCommand sqlCmd)
{
try
{
sqlCmd.Connection = Connection;
if (sqlCmd.Connection.State == ConnectionState.Closed)
{
sqlCmd.Connection.Open();
}
return(sqlCmd.ExecuteScalar());
}
catch (Exception ex)
{
MsgBoxX.Show(ex.Message);
return(null);
}
finally
{
Cursor.Current = Cursors.Default;
sqlCmd.Connection.Close();
}
}
public int dbConnect()
{
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 = "SELECT * FROM [USER_TABLE] WHERE ( USER_ID = '" + USERNAME.Text + "' and USER_PSW = '" + PSW.Text + "')";
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(queryString, conn);
conn.Open();
string str = command.ExecuteScalar().ToString();
if (str == "")
{
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to connect to data source" + ex.ToString());
return(1);
}
finally
{
conn.Close();
}
}
private void button1_Click(object sender, System.EventArgs e)
{
#region 处方发药明细
string stext = this.Text.Trim();
//上传发药明细表的数据
//this.Cursor =Cursors.WaitCursor;
this.Text = "正在上传处方发药明细.....";
OleDbTransaction myTrans0;
myTrans0 = DB.sConnect.BeginTransaction();
try
{
int err_code = -1;
string err_text = "";
System.Data.OleDb.OleDbCommand mySelCmd = new System.Data.OleDb.OleDbCommand();
mySelCmd.Connection = DB.sConnect;
mySelCmd.Transaction = myTrans0;
OleDbParameter parm;
mySelCmd.Parameters.Add("@v_djrq", XcDate.ServerDateTime.ToShortDateString());
mySelCmd.Parameters.Add("@V_DJSJ", XcDate.ServerDateTime.ToLongTimeString());
mySelCmd.Parameters.Add("@V_DJY", _employeeID);
mySelCmd.Parameters.Add("@v_deptid", _deptID);
parm = mySelCmd.Parameters.Add("@err_code", OleDbType.Integer);
parm.Direction = ParameterDirection.Output;
parm = mySelCmd.Parameters.Add("@err_text", OleDbType.VarChar, 250);
parm.Direction = ParameterDirection.Output;
mySelCmd.CommandText = "SP_Yk_fymx_dj";
mySelCmd.CommandType = System.Data.CommandType.StoredProcedure;
mySelCmd.ExecuteScalar();
err_code = Convert.ToInt32(mySelCmd.Parameters["@err_code"].Value);
err_text = Convert.ToString(mySelCmd.Parameters["@err_text"].Value);
if (err_code != 0)
{
throw new System.Exception(err_text);
}
myTrans0.Commit();
this.Text = stext;
}
catch (System.Exception err)
{
myTrans0.Rollback();
MessageBox.Show("在上传发药明细时发生错误" + err.Message);
return;
}
#endregion
#region 系统对账
//系统对账
this.Text = "正在进行系统对账.....";
try
{
ParameterEx[] parameters = new ParameterEx[2];
parameters[0].Value = dtp1.Value.ToShortDateString();
parameters[1].Value = _deptID;
DataTable tb = DatabaseAccess.GetDataTable(DatabaseType.IbmDb2YP, "SP_Yk_XTDZ", parameters, 30);
tb.TableName = "myTb";
if (tb.Rows.Count > 0)
{
this.Close();
Frmxtdzmx f = new Frmxtdzmx(_employeeID, _deptID);
f.MdiParent = _mdiParent;
f.Show();
f.FillData(tb);
return;
}
if (_functionName.Trim() == "Fxc_yk_xtdz")
{
MessageBox.Show("系统对帐完成");
this.Text = stext;
this.Close();
return;
}
}
catch (System.Exception err)
{
MessageBox.Show("出错" + err.Message);
return;
}
#endregion
#region 取消上次月结
//取消上次月结
if (_functionName.Trim() == "Fxc_yk_unymjz" && MessageBox.Show("您确定要取消上次月结吗 ？", "询问窗", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
this.Text = "正在取消上次月结.....";
OleDbTransaction myTrans1;
myTrans1 = DB.sConnect.BeginTransaction();
try
{
int err_code = -1;
string err_text = "";
System.Data.OleDb.OleDbCommand mySelCmd = new System.Data.OleDb.OleDbCommand();
mySelCmd.Connection = DB.sConnect;
mySelCmd.Transaction = myTrans1;
OleDbParameter parm;
mySelCmd.Parameters.Add("@deptid", _deptID);
mySelCmd.Parameters.Add("@djsj", XcDate.ServerDateTime);
mySelCmd.Parameters.Add("@djy", _employeeID);
parm = mySelCmd.Parameters.Add("@err_code", OleDbType.Integer);
parm.Direction = ParameterDirection.Output;
parm = mySelCmd.Parameters.Add("@err_text", OleDbType.VarChar, 100);
parm.Direction = ParameterDirection.Output;
mySelCmd.CommandText = "sp_Yk_unymjc";
mySelCmd.CommandType = System.Data.CommandType.StoredProcedure;
mySelCmd.ExecuteScalar();
err_code = Convert.ToInt32(mySelCmd.Parameters["@err_code"].Value);
err_text = Convert.ToString(mySelCmd.Parameters["@err_text"].Value);
if (err_code != 0)
{
throw new System.Exception(err_text);
}
myTrans1.Commit();
this.Text = stext;
MessageBox.Show(err_text);
this.Close();
return;
}
catch (System.Exception err)
{
myTrans1.Rollback();
this.butok.Enabled = true;
MessageBox.Show(err.Message);
}
}
#endregion
#region 月未结账
if (_functionName.Trim() != "Fxc_yk_ymjz")
{
return;
}
//月未结帐
this.Text = "正在进行月未结帐.....";
OleDbTransaction myTrans;
myTrans = DB.sConnect.BeginTransaction();
try
{
int err_code = -1;
string err_text = "";
System.Data.OleDb.OleDbCommand mySelCmd = new System.Data.OleDb.OleDbCommand();
mySelCmd.Connection = DB.sConnect;
mySelCmd.Transaction = myTrans;
OleDbParameter parm;
mySelCmd.Parameters.Add("@year", _year);
mySelCmd.Parameters.Add("@month", _month);
mySelCmd.Parameters.Add("@deptid", _deptID);
mySelCmd.Parameters.Add("@ksrq", dtp1.Value.ToShortDateString());
mySelCmd.Parameters.Add("@jsrq", dtp2.Value.ToShortDateString());
mySelCmd.Parameters.Add("@djsj", XcDate.ServerDateTime);
mySelCmd.Parameters.Add("@djy", _employeeID);
parm = mySelCmd.Parameters.Add("@err_code", OleDbType.Integer);
parm.Direction = ParameterDirection.Output;
parm = mySelCmd.Parameters.Add("@err_text", OleDbType.VarChar, 100);
parm.Direction = ParameterDirection.Output;
mySelCmd.CommandText = "sp_Yk_ymjc";
mySelCmd.CommandType = System.Data.CommandType.StoredProcedure;
mySelCmd.ExecuteScalar();
err_code = Convert.ToInt32(mySelCmd.Parameters["@err_code"].Value);
err_text = Convert.ToString(mySelCmd.Parameters["@err_text"].Value);
if (err_code != 0)
{
throw new System.Exception(err_text);
}
myTrans.Commit();
this.Text = stext;
MessageBox.Show(err_text);
this.Close();
}
catch (System.Exception err)
{
myTrans.Rollback();
this.butok.Enabled = true;
MessageBox.Show(err.Message);
}
#endregion
}
public static bool checkForPriceChanges()
{
DateTime date = DateTime.Now;
bool changed = false;
List <string> items = new List <string>();
string lastStoredPrice;
string currentPrice;
// count = (Int32)cmd.ExecuteScalar();
OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conString);
//first read unique items into a list
try
{
conn.Open();
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "Select ItemID From User_Item Where UserID = 'testuser'";
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
items.Add(reader.GetString(0));
}
}
catch (Exception)
{
//do nothing if no items found
}
conn.Close();
//compare current price for each item to latest stored price
foreach (string itemNum in items)
{
conn.Open();
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = conn;
currentPrice = service.getCurrentPrice(itemNum);
//wait 1.5 seconds for service
System.Threading.Thread.Sleep(1500);
cmd.CommandText = "Select Price From Price Where ItemID = '" + itemNum + "' Order By PriceDate DESC";
lastStoredPrice = (cmd.ExecuteScalar()).ToString();
conn.Close();
//sometimes an actual price can't be retrieved because it's lower than a manufacturer's minimum advertised price. in this case "Too low to display" is returned.
try
{
if (Convert.ToDecimal(lastStoredPrice) != Convert.ToDecimal(currentPrice))
{
//update currentprice and currentdate in item table
conn.Open();
cmd.CommandText = "Update Item Set CurPrice = '" + Convert.ToDecimal(currentPrice) + "' Where ItemID = '" + itemNum + "'";
cmd.ExecuteNonQuery();
//write current date
cmd.CommandText = "Update Item Set CurDate = '" + date + "' Where ItemID = '" + itemNum + "'";
cmd.ExecuteNonQuery();
conn.Close();
//add price record to price table
conn.Open();
cmd.CommandText = "Insert Into Price (ItemID, Price, PriceDate) values ('" + itemNum + "', '" + Decimal.Parse(currentPrice) + "', '" + date + "')";
cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine(itemNum + " change from $" + lastStoredPrice + " to $" + currentPrice);
changed = true;
}
else
{
Console.WriteLine(itemNum + " no price change.");
}
}
catch
{
//do nothing if the price was too low to display
}
}
//no changes
return(changed);
}
protected void Button1_Click(object sender, EventArgs e)
{
string username = TextBox1.Text;
string password = TextBox2.Text;
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data source= C:\Users\OK\Documents\Ecafe.accdb";
conn.Open();
String my_querry1 = "Select* From Users where Username = '******'and Password = '******'";
System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand(my_querry1, conn);
var dr1 = cmd1.ExecuteReader();
if (dr1.Read())
{
string[] i = (string[])Session["items"];
string[] q = (string[])Session["quants"];
string[] p = (string[])Session["prices"];
string ptime = (string)Session["picktime"];
int total = 0;
for (int j = 0; j < p.Length; ++j)
{
Int32 tpr = Convert.ToInt32(p[j]);
total += tpr;
}
//MessageBox.Show(dr1[0].ToString());
String uid = dr1[0].ToString();
String up = dr1[7].ToString();
String ua = dr1[6].ToString();
var dateAndTime = DateTime.Now;
var current_date = dateAndTime.Date;
//cmd = new OleDbCommand("SELECT @@IDENTITY", con);
//id = cmd.ExecuteNonQuery();
String my_quer2 = "select * from Orders";
String mq = "SELECT COUNT(*) FROM Orders";
System.Data.OleDb.OleDbCommand cmd2 = new System.Data.OleDb.OleDbCommand(my_quer2, conn);
System.Data.OleDb.OleDbCommand cmd6 = new System.Data.OleDb.OleDbCommand(mq, conn);
var dr2 = cmd2.ExecuteReader();
int f = (int)cmd6.ExecuteScalar();
var oid = "a"; //order id
Int32 y = 0;
var g = 1;
//MessageBox.Show(f.ToString());
if (dr2.Read() == false)
{
oid = "0";
}
while (dr2.Read())
{
y = Convert.ToInt32(dr2[11]);
MessageBox.Show(y.ToString());
g++;
//if((g-1)==f)
//break;
}
// MessageBox.Show(f.ToString());
Int32 ooid = y + 1;
//Int32 ooid = Convert.ToInt32(oid);
//ooid += 1;
// MessageBox.Show(oid.ToString());
//MessageBox.Show(ooid.ToString());
DateTime currentTime = DateTime.Now;
DateTime x30MinsLater = currentTime.AddMinutes(30);
// string pt = ptime.ToString("hh:mm tt");
String dt = x30MinsLater.ToString("hh:mm tt");
// DateTime dtime = Convert.ToDateTime(x30MinsLater);
DateTime dtime = DateTime.Parse(dt, System.Globalization.CultureInfo.CurrentCulture);
for (int j = 0; j < i.Length; ++j)
{
String my_quer = "select ID from Items where Item = '" + i[j] + "'";
OleDbCommand cmd3 = new OleDbCommand(@"select ID from Items where Item = @x1 ", conn);
cmd3.Parameters.AddWithValue("@x1", i[j]);
var dr3 = cmd3.ExecuteReader();
var iid = "a"; //item id
while (dr3.Read())
{
iid = dr3[0].ToString();
}
// MessageBox.Show(ptime);
if (string.IsNullOrWhiteSpace(ptime))
{
// MessageBox.Show("hello");
String sql = "insert into Orders ([OrderID],[UserID],[OrderType],[Status],[ItemID],[OrderDate],[TotalBill],[Address],[CustomerNumber],[Quantity],[DTime]) values ('" + ooid + "','" + uid + "','Delivery','Incomplete','" + iid + "','" + current_date + "','" + total + "','" + ua + "','" + up + "','" + q[j] + "','" + dtime + "')";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
var dr4 = cmd.ExecuteNonQuery();
MessageBox.Show("Your Order Has been Placed and it will be delivered around " + dtime.ToString("hh:mm tt"));
}
else
{
DateTime pitime = DateTime.Parse(ptime, System.Globalization.CultureInfo.CurrentCulture);
DateTime PTime = DateTime.ParseExact(ptime, "HH:mm", CultureInfo.InvariantCulture);
//MessageBox.Show(ptime);
String sql = "insert into Orders ([OrderID],[UserID],[OrderType],[Status],[ItemID],[OrderDate],[TotalBill],[Address],[CustomerNumber],[Quantity],[PTime]) values ('" + ooid + "','" + uid + "','Pickup','Incomplete','" + iid + "','" + current_date + "','" + total + "','" + ua + "','" + up + "','" + q[j] + "','" + PTime + "')";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
var dr4 = cmd.ExecuteNonQuery();
MessageBox.Show("Your Order Has been Placed. Please pick it up around " + PTime.ToString("HH:mm "));
}
Server.Transfer("WebForm1.aspx");
}
// MessageBox.Show("Your Order Has been Placed and it will be delivered around "+ dtime.ToString("hh:mm tt"));
}
else
{
MessageBox.Show("Please Check Your Username and Password.");
Server.Transfer("Login.aspx");
}
}
public static void UpdateProfit() // Updates last record in DB. Calculates position profit, accumulated profit etc. Done in few queries
{
if (connect.State == System.Data.ConnectionState.Closed) // If no connection to DB
{
Console.WriteLine("No DB connection! Connecting");
DBConnect();
}
// Get the value of account_balance from the previous record using nested query
var command1 = new oledb.OleDbCommand("SELECT account_balance FROM [tfr_account_statement] WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement]) - 1");
command1.Connection = connect;
try // Run sql command
{
command1.ExecuteNonQuery();
Console.WriteLine("get previous record. " + Convert.ToDouble(command1.ExecuteScalar()));
}
catch (Exception err)
{ Console.WriteLine("Error while getting previous record " + err); }
// profit_per_stock, profit_per_quantity
var command2 = new oledb.OleDbCommand("UPDATE [tfr_account_statement] SET profit_per_stock = trade_close_price - trade_open_price, profit_per_quantity = (trade_close_price - trade_open_price) * stock_quantity WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement])");
command2.Connection = connect;
try // Run sql command
{
command2.ExecuteNonQuery();
Console.WriteLine("Update record in DB. Calculate frofit 1st query");
}
catch (Exception err)
{ Console.WriteLine("Error while updating record. Calculate profit 1st query " + err); }
// account_balance = Previous account_balance + profit_per_quantity
var command3 = new oledb.OleDbCommand("UPDATE [tfr_account_statement] SET account_balance = '" + Convert.ToDouble(command1.ExecuteScalar()) + "' + profit_per_quantity WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement])");
command3.Connection = connect;
try // Run sql command
{
command3.ExecuteNonQuery();
Console.WriteLine("Update record in DB. Calculate profit 2nd query");
}
catch (Exception err)
{ Console.WriteLine("Error while updating record. Calculate profit 2nd query " + err); }
// Update trade_profit_prcnt
var command4 = new oledb.OleDbCommand("UPDATE [tfr_account_statement] SET trade_profit_prcnt = 100 * profit_per_quantity / (stock_quantity * trade_open_price) WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement])");
command4.Connection = connect;
try // Run sql command
{
command4.ExecuteNonQuery();
Console.WriteLine("Update record in DB. Calculate profit 3rd query");
}
catch (Exception err)
{ Console.WriteLine("Error while updating record. Calculate profit 3rd query " + err); }
// Get the value of accumulated_sum_prcnt from the previous record then it will be used in a second query
var command5 = new oledb.OleDbCommand("SELECT accumulated_sum_prcnt FROM [tfr_account_statement] WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement]) - 1");
command5.Connection = connect;
try // Run sql command
{
command5.ExecuteNonQuery();
Console.WriteLine("Get the value of accumulated_sum_prcnt from the previous record. " + Convert.ToDouble(command5.ExecuteScalar()));
}
catch (Exception err)
{ Console.WriteLine("Error while getting the value of accumulated_sum_prcnt from the previous record " + err); }
// accumulated_sum_prcnt = Previous accumulated_sum_prcnt + trade_profit_prcnt
var command6 = new oledb.OleDbCommand("UPDATE [tfr_account_statement] SET accumulated_sum_prcnt = '" + Convert.ToDouble(command5.ExecuteScalar()) + "' + trade_profit_prcnt WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement])");
command6.Connection = connect;
try // Run sql command
{
command6.ExecuteNonQuery();
Console.WriteLine("Update record in DB. accumulated_sum_prcnt query");
}
catch (Exception err)
{ Console.WriteLine("Error while updating record. accumulated_sum_prcnt query " + err); }
}
protected void Page_Load(object sender, EventArgs e)
{
//Custom Client Names.
string clientName = ConfigurationManager.AppSettings["ClientName"];
Page.Title = clientName + " LOGIN PAGE";
messageContainer.Visible = false;
//PersistCookie.Checked = false;
//PersistCookie.Visible = false;
Session["INTERNAL_IP"] = false;
System.Data.OleDb.OleDbConnection IPDBConnection = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["_InternalIPDB"].ConnectionString);
//check session to prevent logs from being created twice [1st on page load, 2nd when user click login button]
if (Session["SID"] == null)
{
string sHost = "";
try
{
sHost = System.Net.Dns.GetHostEntry(Request.Params["REMOTE_ADDR"]).HostName.ToString().ToUpper();
}
catch (Exception ex)
{
}
finally
{
Session["sHOST"] = sHost.ToUpper();
}
System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["_UserDatabase"].ConnectionString);
string strCommand = string.Format("Insert into [LOG] (IP_ADDRESS,COMPUTER_NAME, TIME_START) values ('{0}','{1}','{2}')", Request.Params["REMOTE_ADDR"], sHost, DateTime.Now.ToString());
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(strCommand, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
System.Data.OleDb.OleDbCommand cmdGetidentity = new System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", myConnection);
long lngNewID = Convert.ToInt64(cmdGetidentity.ExecuteScalar());
Session["SID"] = lngNewID;
myCommand.Connection.Close();
}
if (Page.Request["NoByPass"] == "TRUE")
{
if ((Session["CurrentEditor"] != null))
//user has already authenticated through one of the ASP edit forms
{
FormsAuthentication.RedirectFromLoginPage(Session["CurrentEditor"].ToString(), false);
}
return;
}
if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["No_Internal_IP_L123_Bypass"]) && bool.Parse(ConfigurationManager.AppSettings["No_Internal_IP_L123_Bypass"]) && Request["Token"] == null)
{
return;
}
if ((Session["sHost"].ToString().IndexOf("GIS1") == 0 || Session["sHost"].ToString().IndexOf("PROD3") == 0) && Request["Token"] == null)
{
//logged in from WS PC
DeveloperBypass();
return;
}
else if (Request.UserHostAddress == "127.0.0.1" || Request.UserHostAddress == "::1")
{
//logged in from server using localhost in browser address
DeveloperBypass_InitiallyReadOnly("ADMIN", "L3 Bypass (Debug)"); //will be prompted for password if trying to edit
return;
}
else
{
//check ip address
DataSet myData = new DataSet();
//Level II bypass - GIS PC
string strGISPCQuery = string.Format("select * from [IP_List_GIS] where [PCNAME] like '{0}'", Session["sHost"].ToString());
OleDbDataAdapter dsGISCmd = new OleDbDataAdapter(strGISPCQuery, IPDBConnection);
dsGISCmd.Fill(myData, "GIS_PC");
DataTable pTableGIS = myData.Tables["GIS_PC"];
if (pTableGIS.Rows.Count > 0)
{
DeveloperBypass_InitiallyReadOnly(pTableGIS.Rows[0]["LOGIN"].ToString(), string.Format("L2 Bypass ({0})", pTableGIS.Rows[0]["LOGIN"].ToString())); //will be prompted for password if trying to edit
return;
}
//Level I bypass - Wendel Intranet
bool bIpOk = false;
string strIPQuery = string.Format("select * from [IP_List] where [IP] = '{0}'", Request.UserHostAddress);
OleDbDataAdapter dsCmd = new OleDbDataAdapter(strIPQuery, IPDBConnection);
dsCmd.Fill(myData, "IP_Exact");
DataTable pTable = myData.Tables["IP_Exact"];
if (pTable.Rows.Count > 0)
{
//Exact match to IP address
bIpOk = true;
//
}
else
{
strIPQuery = "select * from [IP_List] where instr(1,[IP],'*')";
dsCmd = new OleDbDataAdapter(strIPQuery, IPDBConnection);
dsCmd.Fill(myData, "IP_SubNet");
pTable = myData.Tables["IP_SubNet"];
System.Data.DataRow pRow = null;
foreach (DataRow pRow_loopVariable in pTable.Rows)
{
pRow = pRow_loopVariable;
Wildcard wildcard = new Wildcard(pRow["IP"].ToString(), RegexOptions.IgnoreCase);
if (wildcard.IsMatch(Request.UserHostAddress) || wildcard.IsMatch(Session["sHOST"].ToString()))
{
bIpOk = true;
break; // TODO: might not be correct. Was : Exit For
}
}
}
//'Level 1 DNS lookup bypass
//'disable DNS lookup. it's slow
//If Not bIpOk Then
// strIPQuery = "select * from [IP_List] where [DNSLookup] = true"
// dsCmd = New OleDbDataAdapter(strIPQuery, IPDBConnection)
// dsCmd.Fill(myData, "DNS_SubNet")
// pTable = myData.Tables("DNS_SubNet")
// Dim pRow As System.Data.DataRow
// For Each pRow In pTable.Rows
// Try
// If Request.UserHostAddress = System.Net.Dns.GetHostEntry(pRow.Item("IP").ToString).AddressList(0).ToString Then
// bIpOk = True
// Exit For
// End If
// Catch ex As Exception
// End Try
// Next
//End If
//token in URL
if (Request["Token"] != null)
{
String[] userInfo = AESUtil.DecryptString(Request["Token"]).Split(new String[] { "|" }, StringSplitOptions.None);
//if ip matched the token - allow regardless of ip address
if (userInfo[0].ToUpper() == "WARIT" && userInfo[1] == Request.UserHostAddress)
{
//token valid
DeveloperBypass();
return;
}
//developer bypass - allow if coming from any of the recognized ip and token contains any of the recognized ip
else if (bIpOk)
{
String strTokenIPQuery = String.Format("select * from [IP_List] where [IP] = '{0}'", userInfo[1]);
OleDbDataAdapter dsTokenIPCmd = new OleDbDataAdapter(strTokenIPQuery, IPDBConnection);
dsCmd.Fill(myData, "Token_IP_Exact");
DataTable pTokenIPTable = myData.Tables["Token_IP_Exact"];
if (pTable.Rows.Count > 0)
{
//token valid
DeveloperBypass();
return;
}
}
}
if (bIpOk)
{
Session["INTERNAL_IP"] = true;
if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["No_Internal_IP_Bypass"]) && bool.Parse(ConfigurationManager.AppSettings["No_Internal_IP_Bypass"]))
{
messageContainer.Visible = true;
lblResults.Text = "Internal IP bypass is disabled in this application. You need to login.";
lblResults.Font.Bold = true;
}
else
{
UpdateLogUser("ADMIN", "L1 Bypass");
//Level I bypass
//Internal Access - bypass login screen
Session["AllowEdit"] = false;
FormsAuthentication.RedirectFromLoginPage("ADMIN", false);
return;
}
}
}
//Restrict_IP_Address
if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["Restrict_IP_Address"]) && bool.Parse(ConfigurationManager.AppSettings["Restrict_IP_Address"]))
{
bool bUserIPOK = false;
OleDbConnection myUserIPConnection = new OleDbConnection(ConfigurationManager.ConnectionStrings["_UserDatabase"].ConnectionString);
string strUserIPQuery = "select * from [IP_Allow_List] where [IP] = '" + Request.UserHostAddress + "'";
OleDbDataAdapter dsUserIPCmd = new OleDbDataAdapter(strUserIPQuery, myUserIPConnection);
DataSet myUserIPData = new DataSet();
dsUserIPCmd.Fill(myUserIPData, "IP_Exact");
DataTable pUserIPTable = myUserIPData.Tables["IP_Exact"];
if (pUserIPTable.Rows.Count > 0)
{
//Exact match to IP address
bUserIPOK = true;
}
else
{
strUserIPQuery = "select * from [IP_Allow_List] where instr(1,[IP],'*')";
dsUserIPCmd = new OleDbDataAdapter(strUserIPQuery, myUserIPConnection);
dsUserIPCmd.Fill(myUserIPData, "IP_SubNet");
pUserIPTable = myUserIPData.Tables["IP_SubNet"];
System.Data.DataRow pRow = null;
foreach (DataRow pRow_loopVariable in pUserIPTable.Rows)
{
pRow = pRow_loopVariable;
if (Request.UserHostAddress.Length >= pRow["IP"].ToString().Length)
{
if (Request.UserHostAddress.Substring(0, pRow["IP"].ToString().Length - 1) == pRow["IP"].ToString().Substring(0, pRow["IP"].ToString().Length - 1))
{
bUserIPOK = true;
break; // TODO: might not be correct. Was : Exit For
}
}
}
}
if (!bUserIPOK)
{
UserName.Disabled = true;
UserPass.Disabled = true;
cmdLogin.Enabled = false;
messageContainer.Visible = true;
lblResults.Text = "Sorry, access is not permitted from your IP Address. Please contact Wendel GIS department for assistance.";
lblResults.Font.Bold = true;
}
}
}