C# OleDbCommand.ExecuteScalar方法代码示例

        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 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 = '" + password + "'";
        else
            com.CommandText = "select UID, pass from UserInfo where UserName = '" + username + "' and Pass ='" + password + "'";

        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;
    }

  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 = '{1}' and Password = '{{0}}' 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());
        }
    }

        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='"+ TextBox2.Text+"';";
                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());
            }
        }

 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;
 }

    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? 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 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();
		}

  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 = '{1}' 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 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 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;
        }

        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 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 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;
        }