C# SqlCeCommand.ExecuteScalar方法代码示例

        public int? Create(ConnectionString entity)
        {
            using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
            {
                connection.Open();
                SqlCeCommand command = new SqlCeCommand("INSERT INTO ConnectionStrings(Type, Name, Connection) VALUES (@Type, @Name, @Connection)", connection);
                command.Parameters.AddWithValue("@Type", (int)entity.Type);
                command.Parameters.AddWithValue("@Name", entity.Name);
                command.Parameters.AddWithValue("@Connection", entity.Connection);
                command.ExecuteNonQuery();

                SqlCeCommand getId = new SqlCeCommand("SELECT @@IDENTITY AS Id", connection);
                object id = getId.ExecuteScalar();
                return id == null ? (int?)null : Convert.ToInt32(getId.ExecuteScalar());
            }
        }

    private void postavi_labele()
    {
        //kreiraj novu praznu konekciju
        SqlCeConnection conn = new SqlCeConnection();
        //dohvati tekst za povezivanje na bazu iz web.config i postavi g ana konekciju
        string connStr = WebConfigurationManager.ConnectionStrings["studenti"].ConnectionString;
        conn.ConnectionString = connStr;

        //kreiraj novu naredbu i postavi SQL kao i konekciju
        SqlCeCommand cmd = new SqlCeCommand();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT COUNT(*) FROM student";
        cmd.CommandType = System.Data.CommandType.Text; //tip je SQL naredba (a ne tablica ili stor.proc)
        //otvori komunikaciju sa bazom
        conn.Open();
        int brojStud = (int)cmd.ExecuteScalar(); //izvrši vrati jednu vrijednost
        Label1.Text = "U bazi imamo " + brojStud.ToString() + " studenata!";

        cmd.CommandText = "SELECT * FROM student";

        //sada ih vrati kao datareader
        SqlCeDataReader dr = cmd.ExecuteReader();
        Label2.Text = "ID -  Ime  - Prezime" + "<br>";
        // na sql Expressu ima i dr.HasRows da vidimo je li prazan if(dr.HasRows))
        while (dr.Read())
        {
            //čitaj red po red dok ne dođeš do kraja
            Label2.Text += dr["stud_id"].ToString() + " - " + dr["ime"] + " - " + dr["prezime"] + " - " + dr["faks"] + "<br>";

        }

        conn.Close();
    }

        public static int InsertCapturePointsForTextConversion(int RecommendationId, List<CustomTreeNode> customNodesList)
        {
            int returnCode = -1;
            List<int> capturePointsIds = new List<int>();
            SqlCeConnection conn = BackEndUtils.GetSqlConnection();
            try {
                conn.Open();
                for (int i = 0; i < customNodesList.Count; i++) {
                    SqlCeCommand command = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandInsertCapturePointTextConv, conn);
                    //@pointText, @pointUsedAttributes, @pointParentNode, @pointUsedAttribValues, @pointRecId
                    command.Parameters.Add("@pointText", customNodesList[i].Text);
                    command.Parameters.Add("@pointUsedAttributes", BackEndUtils.GetUsedAttributes(customNodesList[i].customizedAttributeCollection));
                    command.Parameters.Add("@pointParentNode", (customNodesList[i].Parent == null ? "" : customNodesList[i].Parent.Text));
                    command.Parameters.Add("@pointUsedAttribValues", BackEndUtils.GetUsedAttributesValues(customNodesList[i].customizedAttributeCollection));
                    command.Parameters.Add("@pointRecId", RecommendationId);
                    command.Parameters.Add("@Level", customNodesList[i].Level);
                    command.Parameters.Add("@ItemIndex", customNodesList[i].Index);
                    command.Parameters.Add("@parentLevel", customNodesList[i].Parent == null ? -1 : customNodesList[i].Parent.Level);
                    command.Parameters.Add("@parentIndex", customNodesList[i].Parent == null ? -1 : customNodesList[i].Parent.Index);

                    returnCode = Convert.ToInt32(command.ExecuteNonQuery());
                    SqlCeCommand commandMaxId = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandMaxCapturePointIdTextConv, conn);
                    capturePointsIds.Add(Convert.ToInt32(commandMaxId.ExecuteScalar()));
                }
            } finally {
                conn.Close();
            }
            return returnCode;
        }

 public static long? ExecuteNonQuery(string query)
 {
     try
     {
         SqlCeConnection conn = CaseStudyDB.GetConnection();
         conn.Open();
         SqlCeCommand cmd = new SqlCeCommand(query, conn);
         cmd.ExecuteScalar();
         cmd = new SqlCeCommand("SELECT @@IDENTITY", conn);
         object queryReturn = cmd.ExecuteScalar();
         long value;
         long.TryParse(queryReturn.ToString(),out value);
         conn.Close();
         if(value != 0)
         {
             return value;
         }
         else
         {
             return null;
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(string.Format("Error exeuting query: {0}", ex.Message));
         return null;
     }
 }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(
            string connectionString,
            CommandType commandType,
            string commandText,
            params SqlCeParameter[] commandParameters
            )
        {
            object retVal;

            try
            {
                using (SqlCeConnection conn = SqlCeContextGuardian.Open(connectionString))
                {
                    using (SqlCeCommand cmd = new SqlCeCommand(commandText, conn))
                    {
                        AttachParameters(cmd, commandParameters);
                        Debug.WriteLine("---------------------------------SCALAR-------------------------------------");
                        Debug.WriteLine(commandText);
                        Debug.WriteLine("----------------------------------------------------------------------------");
                        retVal = cmd.ExecuteScalar();
                    }
                }

                return retVal;
            }
            catch (Exception ee)
            {
                throw new SqlCeProviderException("Error running Scalar: \nSQL Statement:\n" + commandText + "\n\nException:\n" + ee.ToString());
            }
        }

        public void login()
        {
            //checks fields are empty

            if (txt_user_name.Text != "" & txt_password.Text != "")
            {
                string queryText = @"SELECT Count(*) FROM users
                             WHERE user_name = @user_name AND password = @password";
                //makes connection to database, and writes a insert query
                using (SqlCeConnection cn = new SqlCeConnection(@"Data Source=C:\temp\Mydatabase.sdf "))
                using (SqlCeCommand cmd = new SqlCeCommand(queryText, cn))
                {
                    cn.Open();
                    //opens connection, sets fields with names like user_name
                    cmd.Parameters.AddWithValue("@user_name", txt_user_name.Text);
                    cmd.Parameters.AddWithValue("@password", txt_password.Text);
                    int result = (int)cmd.ExecuteScalar();
                    if (result > 0)
                    {
                        this.Hide();
                        Home frmchild = new Home();
                        frmchild.Show();
                        frmchild.WriteToText(txt_user_name.Text);
                    }
                    else
                    {
                        MessageBox.Show("User Not Found!");
                    }

                }
            }
        }

        public static Guid GetApplicationId(string connectionString, string applicationName)
        {
            using (SqlCeConnection conn = new SqlCeConnection(connectionString))
                    {
                        using (SqlCeCommand cmd = new SqlCeCommand("SELECT ApplicationId FROM [aspnet_Applications] " +
                                            "WHERE ApplicationName = @ApplicationName", conn))
                        {
                            cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName;

                            conn.Open();
                            var applicationId = cmd.ExecuteScalar();
                            if (applicationId == null)
                            {
                                cmd.Parameters.Clear();
                                cmd.CommandText = "INSERT INTO [aspnet_Applications] (ApplicationId, ApplicationName, LoweredApplicationName, Description) VALUES (@ApplicationId, @ApplicationName, @LoweredApplicationName, @Description)";

                                applicationId = Guid.NewGuid();
                                cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = applicationId;
                                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName;
                                cmd.Parameters.Add("@LoweredApplicationName", SqlDbType.NVarChar, 256).Value = applicationName.ToLowerInvariant();
                                cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 256).Value = String.Empty;

                                cmd.ExecuteNonQuery();
                            }
                            return (Guid)applicationId;

                        }
                    }
        }

        //changing current user's password
        private void button1_Click(object sender, EventArgs e)
        {
            //check if old password matches
            SqlCeCommand cm = new SqlCeCommand("SELECT COUNT(*) FROM Password WHERE [email protected] AND [email protected] ", Form1.con);
            cm.Parameters.AddWithValue("@user", label3.Text);
            cm.Parameters.AddWithValue("@pas", EncryptSHA512Managed(textBox1.Text));

            if ((int)cm.ExecuteScalar() == 1)
            {
                try
                {
                    //change password
                    SqlCeCommand cmd = Form1.con.CreateCommand();
                    cmd.CommandText = "UPDATE Password SET Password = @pass WHERE Username = @username";
                    cmd.Parameters.AddWithValue("@username", label3.Text);
                    cmd.Parameters.AddWithValue("@pass", EncryptSHA512Managed(textBox2.Text));
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                MessageBox.Show("Password modifyed!");
                textBox1.Text = "";
                textBox1.Text = "";
            }

            else
            {
                MessageBox.Show("Wrong Password Inserted!");
            }
        }

    protected void Page_Load(object sender, EventArgs e)
    {
        SqlCeConnection conn = new SqlCeConnection(connString); // SqlConnection
        conn.Open();
        SqlCeCommand command = new SqlCeCommand("SELECT * FROM student WHERE [email protected]", conn); // ... WHERE pbr=" + pbr.ToString() NE - SQL Injection
        command.Parameters.AddWithValue("pbr", 10000); // ovako rjestiti i labose sa usernmom i passwordom

        SqlCeDataReader dr = command.ExecuteReader();

        // za labose: dr.HasRows
        // HasRows ne radi na Sql Compact :(
        // workaround:
        // bool hasRow = dr.Read();
        // if (hasRow) {
        //   napravi nesto
        // }

        // ili: (tnx Svjetlana)
        // SqlCeResultSet rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
        // if (dr.HasRows)
        //     ima rezultata

        txtStudenti.Text = "";

        while (dr.Read())
        {
            txtStudenti.Text += dr[0] + " " +  dr["ime"] + " " + dr["prezime"] + " "+ dr["pbr"] + "\n";
        }

        dr.Close();
        command = new SqlCeCommand("SELECT COUNT(*) FROM student", conn);
        int broj = (int)command.ExecuteScalar();
        txtStudenti.Text += broj.ToString();
        conn.Close();
    }

        public static int CountUnsynchronizedBackroudWorker(SqlCeConnection databaseConnection)
        {
            if (databaseConnection == null) throw new ArgumentNullException("databaseConnection");

            SqlCeCommand com = new SqlCeCommand(("SELECT COUNT(*) FROM Scanari WHERE Sincronizat=0"), databaseConnection);
            return (Int32)com.ExecuteScalar();
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Length != 0)
            {

                var numePrenume = textBox1.Text.Trim().Split(' ');

               
                    if (numePrenume.Count() > 1)
                    {
                        var nume = numePrenume[0];
                        var prenume = numePrenume[1];
                        var connString = (@"Data Source=" + System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)) + @"\Angajati.sdf");
                        using (var conn = new SqlCeConnection(connString))
                        {
                            try
                            {

                                conn.Open();
                                var query = "SELECT COUNT(*) FROM info WHERE Nume='" + nume + "' AND Prenume='" + prenume + "'";
                                var command = new SqlCeCommand(query, conn);
                                var dataAdapter = new SqlCeDataAdapter(command);
                                var dataTable = new DataTable();
                                dataAdapter.Fill(dataTable);

                                //checks if there's the searched record is in the db.
                                int infoCount = (int)command.ExecuteScalar();
                                if (infoCount > 0)
                                {
                                    Info form = new Info(nume, prenume);
                                    form.Show();
                                }
                                else
                                {
                                    MessageBox.Show("Nu exista un angajat cu acest nume");
                                }


                            }
                            catch (Exception ex)
                            {
                                MessageBox.Show(ex.ToString());
                            }

                        }
                    }
                    else
                    {
                        MessageBox.Show("Nu ai introdus prenumele");

                    }
                }
                else
                {
                    MessageBox.Show("Nu ai introdus nici un nume!");
                }
            }

        private void btnLogin_Click(object sender, EventArgs e)
        {
            dbh.TestConnection();
            dbh.OpenConnectionToDB();

            bool exist = false;
            string username = txtUsername.Text;
            string password = txtPassword.Text;

            txtUsername.Text = "";
            txtPassword.Text = "";

            using (SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM [tblUsers] WHERE Username = @Username AND Password = @Password", dbh.GetCon()))
            {
                cmd.Parameters.AddWithValue("Username", username);
                cmd.Parameters.AddWithValue("Password", password);

                SqlCeDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    if (reader.GetString(1) == username && reader.GetString(2) == password)
                    {
                        userID = reader.GetInt32(0);
                        exist = true;
                        break;
                    }
                }
            }

            if (exist)
            {
                bool admin;
                using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(*) from [tblUsers] WHERE Username = @Username AND IsAdmin = 1", dbh.GetCon()))
                {
                    cmd.Parameters.AddWithValue("Username", username);
                    admin = (int)cmd.ExecuteScalar() > 0;
                }
                dbh.CloseConnectionToDB();

                if (admin)
                {
                    frmAdmin.Show();
                }
                else
                {
                    frmPlayer = new frmPlayer(frmRanking, username, this, userID);
                    frmPlayer.Show();
                    //frmPlayer.Show();
                }
            }
            else
            {
                dbh.CloseConnectionToDB();
                MessageHandler.ShowMessage("Wrong username and/or password.");
            }
        }

 private static int GetFoundRowsForExistingForm(string formName, SqlCeConnection conn)
 {
     int returnCode = -1;
     try {
         SqlCeCommand command = new SqlCeCommand(Form_Updated_SQL.commandGetFormCount, conn);
         command.Parameters.Add("@formName", formName);
         returnCode = Convert.ToInt32(command.ExecuteScalar());
     } catch (Exception ex) {
     }
     return returnCode;
 }

    //Block Memory Leak
 
  
    
  
    public static bool TableExists(SqlCeConnection connection, string tableName)
    {
        using (var command = new SqlCeCommand())
        {
            command.Connection = connection;
            var sql = string.Format("SELECT COUNT(*) FROM information_schema.tables WHERE table_name = '{0}'", tableName);
            command.CommandText = sql;
            var count = Convert.ToInt32(command.ExecuteScalar());
            return (count > 0);
        }
    }

 public static int InsertNewUserSession(SqlCeConnection conn)
 {
     int value = 0;
     SqlCeCommand command = new SqlCeCommand(User_Sessions_SQL.commandInsertNewUserSession, conn);
     command.Parameters.Add("@userName", MonitorObject.username);
     command.Parameters.Add("@loginTime", MonitorObject.loginTime);
     command.Parameters.Add("@logoutTime", MonitorObject.logoutTime);
     value = Convert.ToInt32(command.ExecuteNonQuery());
     SqlCeCommand commandMaxId = new SqlCeCommand(User_Sessions_SQL.commandMaxId, conn);
     value = Convert.ToInt32(commandMaxId.ExecuteScalar());
     return value;
 }