C# SqlCeCommand.ExecuteResultSet方法代码示例

        public bool Exist(string word, Encoding wordEncoding)
        {
            if (string.IsNullOrEmpty(word))
                return true;

            if (wordEncoding != Encoding.Unicode)
                word = UnicodeConverter.Decode(word, wordEncoding);

            using (var conn = new SqlCeConnection(NlpHelper.DatabaseConnectionString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                try
                {
                    var sqlCeCommand = new SqlCeCommand(DatabaseQuery.SelectIdFromFormsWhereForm, conn)
                                           {CommandType = CommandType.Text};
                    sqlCeCommand.Parameters.AddWithValue("@form", word);

                    SqlCeResultSet resultSet =
                        sqlCeCommand.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Insensitive);
                    return resultSet.HasRows;
                }
                catch (SqlCeException sqlexception)
                {
                    Console.WriteLine("Error form: {0}", sqlexception.Message);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error form: {0}", ex.Message);
                }
            }

            return false;
        }

 public static SqlCeResultSet viewdata(string komenda)
 {
     SqlCeCommand cmd = new SqlCeCommand();
     cmd.Connection = polaczenie;
     cmd.CommandText = komenda;
     return cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
 }

        /// <summary>
        /// Загрузка данных о игроках и проверка пароля, чтобы можно было 
        /// загрузить данные о нем. 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            var con = new SqlCeConnection("DataSource=|DataDirectory|\\Database.sdf");
            var sqlCommand = new SqlCeCommand("select * from users", con);
            con.Open();
            var set = sqlCommand.ExecuteResultSet(ResultSetOptions.None);

            while (set.Read()) {
                var o = set["UserId"];
                var o1 = set["Login"];
                var o2 = set["Password"];

                if (o1.ToString() == textBox1.Text
                    && o2.ToString() == textBox2.Text) {
                    con.Close();
                    ok = true;
                    form1.User = new Users {
                        Login = o1.ToString(),
                        Password = o2.ToString()
                    };
                    Close();

                    return;
                }
            }

            con.Close();

            label3.Text = "Пароль или имя не верны";
        }

        private ApplicationState()
        {
            // read the application state from db
            SqlCeConnection _dataConn = null;
            try
            {
                _dataConn = new SqlCeConnection("Data Source=FlightPlannerDB.sdf;Persist Security Info=False;");
                _dataConn.Open();
                SqlCeCommand selectCmd = new SqlCeCommand();
                selectCmd.Connection = _dataConn;
                StringBuilder selectQuery = new StringBuilder();
                selectQuery.Append("SELECT cruiseSpeed,cruiseFuelFlow,minFuel,speed,unit,utcOffset,locationFormat,deckHoldFuel,registeredClientName FROM ApplicationState");
                selectCmd.CommandText = selectQuery.ToString();
                SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                if (results.HasRows)
                {
                    results.ReadFirst();
                    cruiseSpeed = results.GetInt64(0);
                    cruiseFuelFlow = results.GetInt64(1);
                    minFuel = results.GetInt64(2);
                    speed = results.GetSqlString(3).ToString();
                    unit = results.GetSqlString(4).ToString();
                    utcOffset = results.GetSqlString(5).ToString();
                    locationFormat = results.GetSqlString(6).ToString();
                    deckHoldFuel = results.IsDBNull(7) ? 0 : results.GetInt64(7);
                    registeredClientName = results.IsDBNull(8) ? string.Empty : results.GetString(8);
                }

            }

            finally
            {
                _dataConn.Close();
            }
        }

        int GetVehicleCatgeoryID(string vehicleCategory)
        {
           

                SqlCeConnection con = ((IDBManager)DBConnectionManager.GetInstance()).GetConnection();
                string sqlQuery;

                if (con.State == ConnectionState.Closed) { con.Open(); }
                SqlCeCommand command;

                sqlQuery = "SELECT Vehicle_category_ID FROM VSD_VEHICLE_CATEGORY WHERE (Category_Name = @vehCat)";
                command = new SqlCeCommand(sqlQuery, con);

                command.Parameters.Add("@vehCat", SqlDbType.NChar, 100).Value = vehicleCategory;

                SqlCeResultSet rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);

                int rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;

                int id = -1;
                if (rs.Read())
                {
                    id = (int)rs.GetInt32(0);
                }

                rs.Close();
                con.Close();
                return id;
           
        }

        public Book[] GetAllBooks()
        {
            var books = new List<Book>();

            using (SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Book", Connection))
            {
                using (var results = cmd.ExecuteResultSet(ResultSetOptions.Insensitive))
                {
                    while (results.Read())
                    {
                        if (m_bookOrdinals.Count == 0)
                        {
                            for (int i = 0; i < results.FieldCount; i++)
                            {
                                m_bookOrdinals.Add(results.GetName(i), i);
                            }
                        }

                        books.Add(new Book
                        {
                            BookID = results.GetInt32(m_bookOrdinals["BookID"]),
                            AuthorID = results.GetInt32(m_bookOrdinals["AuthorID"]),
                            Title = results.GetString(m_bookOrdinals["Title"])
                        });
                    }
                }
            }

            return books.ToArray();
        }

        bool IDBManager.AuthenticateUser(string userName, string userPass)
        {
            IDBManager iDBManager = (IDBManager)DBConnectionManager.GetInstance();
            // byte[] pass = ((IEncryptionDecryptionManager)new EncryptionManager()).Encrypt(userPass, AppProperties.encryptionKey, AppProperties.iVector);

            string sql = "select * from VSD_USER_ACCOUNT where User_Name like @empUserName";//and Password like @empPassword

            SqlCeConnection con = iDBManager.GetConnection();

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            SqlCeCommand command = new SqlCeCommand(sql, iDBManager.GetConnection());
            command.Parameters.Add("@empUserName", SqlDbType.NChar, 100).Value = userName;
            //command.Parameters.Add("@empPassword", SqlDbType.VarBinary, 20).Value = empPassword;
            try
            {

                SqlCeResultSet rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
                int rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;

                 byte[] encryptedBytes;
                string decryptedPassword;
                if (rs.HasRows)
                {
                    //return false;
                    rs.ReadFirst();
                     encryptedBytes = (byte[])rs.GetValue(2);
                      decryptedPassword = ((IEncryptionDecryptionManager)new DecryptionManager()).Decrypt(encryptedBytes, AppProperties.encryptionKey, AppProperties.iVector);
                     if (userPass == decryptedPassword)
                    {
                      return true;
                    }

                    while (rs.NextResult())
                    {
                         encryptedBytes = (byte[])rs.GetValue(2);
                         decryptedPassword = ((IEncryptionDecryptionManager)new DecryptionManager()).Decrypt(encryptedBytes, AppProperties.encryptionKey, AppProperties.iVector);
                        if (userPass == decryptedPassword)
                        {
                        return true;
                        }
                    }

                }


            }
            catch (Exception ex)
            {
                // System.Windows.Forms.MessageBox.Show(ex.Message)
                ; return false;
            }

            return false;
        }

        public Author GetAuthorById(int id)
        {
            Author author = null;
            List<Book> books = new List<Book>();

            using (SqlCeCommand cmd = new SqlCeCommand("SELECT AuthorID, Name, BookID, Title, BookType FROM Author INNER JOIN Book ON Author.AuthorID = Book.AuthorID WHERE Author.AuthorID = @id",
                Connection))
            {
                cmd.Parameters.Add(new SqlCeParameter("@id", id));

                using (var results = cmd.ExecuteResultSet(ResultSetOptions.Insensitive))
                {
                    while (results.Read())
                    {
                        if (author == null)
                        {
                            author = new Author
                            {
                                AuthorID = (int)results["AuthorID"],
                                Name = (string)results["Name"]
                            };
                        }
                        else if (author.AuthorID != results.GetInt32(m_authorOrdinals["AuthorID"]))
                        {
                            // we're on a new author , so we're done
                            // (shoudln't happen unless we have more than 1 author with the same name)
                            break;
                        }

                        books.Add(new Book
                        {
                            BookID = (int)results["BookID"],
                            Title = (string)results["Title"],
                            BookType = (BookType)results["BookType"],
                            AuthorID = author.AuthorID
                        });
                    }
                }
            }

            author.Books = books.ToArray();

            return author;
        }

        string GetVehicleCatgeoryCode(string vehicleCategory)
        {
            try
            {

           
            SqlCeConnection con = ((IDBManager)DBConnectionManager.GetInstance()).GetConnection();
            string sqlQuery;

            if (con.State == ConnectionState.Closed) { con.Open(); }
            SqlCeCommand command;


            sqlQuery = "SELECT category_Code FROM VSD_VEHICLE_CATEGORY WHERE (Category_Name = @vehCat)";
            command = new SqlCeCommand(sqlQuery, con);

            command.Parameters.Add("@vehCat", SqlDbType.NChar, 100).Value = vehicleCategory;


            SqlCeResultSet rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);

            int rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;

            string code = "";
            if (rs.Read())
            {
                code = rs.GetString(0);
            }

            rs.Close();
            con.Close();
            return code;
            }
            catch (Exception ex)
            {
                CommonUtils.WriteLog(ex.StackTrace);
                return null;
               
            }
        }

        static WordStreammer()
        {
            Forms = new List<string>();

            using (SqlCeConnection conn = new SqlCeConnection(NlpHelper.DatabaseConnectionString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                try
                {
                    var sqlCeCommand = new SqlCeCommand(DatabaseQuery.SelectFormFromForms, conn) { CommandType = CommandType.Text };

                    SqlCeResultSet resultSet =
                        sqlCeCommand.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Insensitive);

                    if (resultSet.HasRows)
                    {
                        int ordForm = resultSet.GetOrdinal("Form");
                        resultSet.ReadFirst();
                        Forms.Add(resultSet.GetString(ordForm));

                        while (resultSet.Read())
                        {
                            string form = resultSet.GetString(ordForm);
            //                            if (!Forms.Contains(form))
                                Forms.Add(form);
                        }
                    }
                }
                catch (SqlCeException sqlexception)
                {
                    Console.WriteLine("Error form: {0}", sqlexception.Message);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error form: {0}", ex.Message);
                }
            }
        }

        public void Synchronize_vehicleCatDefectSeverity(handHeldService.SynchronizeConfigDataResponseItem test)
        {
            try
            {
                SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
                string sqlQuery;
                if (con.State == ConnectionState.Closed) { con.Open(); }
                SqlCeCommand command;
                SqlCeResultSet rs;
                int rowCount;
                int temp = -1;
                ///////////////////////////
                //check for Vehicle Category Defect updates
                if (test.vehicleCatDefectSeverity != null)
                {
                    handHeldService.VehicleCategoryDefectSeverity[] WebServiceDefects = new VSDApp.handHeldService.VehicleCategoryDefectSeverity[test.vehicleCatDefectSeverity.Length];
                    WebServiceDefects = test.vehicleCatDefectSeverity;
                    foreach (handHeldService.VehicleCategoryDefectSeverity x in WebServiceDefects)
                    {
                        if (con.State == ConnectionState.Closed) { con.Open(); }
                        /*
                         * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
                            UPDATE Table1 SET (...) WHERE Column1='SomeValue'
                            ELSE
                            INSERT INTO Table1 VALUES (...)
                         */
                        sqlQuery = "SELECT COUNT(*) AS Expr1"
                                    + " FROM VSD_Veh_Cat_Defect"
                                    + " WHERE (Veh_Cat_Defect_id = @VehCatDefectID)";
                        command = new SqlCeCommand(sqlQuery, con);
                        command.Parameters.Add("@VehCatDefectID", SqlDbType.Int).Value = x.id;
                        rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
                        rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;



                        //rountine for Defect Table
                        if (rowCount > 0)
                        {
                            rs.ReadFirst();
                            temp = rs.GetInt32(0);
                        }
                        if (temp == 0)
                        {
                            sqlQuery = " INSERT INTO VSD_Veh_Cat_Defect (Veh_Cat_Defect_ID,Defect_ID,Severity_Level_ID,Vehicle_category_ID,IsDisabled) VALUES (" + x.id + "," + (x.defectId != null ? "" + x.defectId + "" : "NULL") + "," + (x.severityLevelId != null ? "" + x.severityLevelId + "" : "NULL") + "," + (x.vehicleCategoryId != null ? "" + x.vehicleCategoryId + "" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ")";
                        }
                        else
                        {
                            sqlQuery = " UPDATE VSD_Veh_Cat_Defect SET Veh_Cat_Defect_ID = " + x.id + ",Defect_ID = " + (x.defectId != null ? "" + x.defectId + "" : "NULL") + ",Severity_Level_ID = " + (x.severityLevelId != null ? "" + x.severityLevelId + "" : "NULL") + ",Vehicle_category_ID = " + (x.vehicleCategoryId != null ? "" + x.vehicleCategoryId + "" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Veh_Cat_Defect_ID = " + x.id;
                        }
                        command = new SqlCeCommand(sqlQuery, con);

                        command.Parameters.Add("@Veh_Cat_Defect_ID", SqlDbType.Int).Value = x.id;
                        command.Parameters.Add("@Defect_ID", SqlDbType.Int).Value = x.defectId;
                        command.Parameters.Add("@Severity_Level_ID", SqlDbType.Int).Value = x.severityLevelId;
                        command.Parameters.Add("@Vehicle_category_ID", SqlDbType.Int).Value = x.vehicleCategoryId;
                        command.Parameters.Add("@IsDisabled", SqlDbType.NChar, 1).Value = (x.isDisabled.ToString())[0];
                        rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
                        rs.Close();
                        con.Close();
                    }
                }

            }
            catch (Exception ex)
            {
                CommonUtils.WriteLog(ex.StackTrace);
            }
        }

        public void saveToDB()
        {
            // read the application state from db
            SqlCeConnection _dataConn = null;
            try
            {
                _dataConn = new SqlCeConnection("Data Source=FlightPlannerDB.sdf;Persist Security Info=False;");
                _dataConn.Open();
                SqlCeCommand selectCmd = new SqlCeCommand();
                selectCmd.Connection = _dataConn;
                StringBuilder selectQuery = new StringBuilder();
                selectQuery.Append("SELECT cruiseSpeed,cruiseFuelFlow,minFuel,deckHoldFuel,speed,unit,utcOffset,locationFormat FROM ApplicationState");
                selectCmd.CommandText = selectQuery.ToString();
                SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                if (results.HasRows)
                {
                    // update query
                    SqlCeCommand updCmd = new SqlCeCommand();
                    updCmd.Connection = _dataConn;
                    StringBuilder updQuery = new StringBuilder();
                    updQuery.Append("UPDATE ApplicationState set ");
                    updQuery.Append("cruiseSpeed = "+cruiseSpeed +",");
                    updQuery.Append("cruiseFuelFlow = " + cruiseFuelFlow + ",");
                    updQuery.Append("minFuel = " + minFuel + ",");
                    updQuery.Append("deckHoldFuel = " + deckHoldFuel + ",");
                    updQuery.Append("speed = '" + speed + "',");
                    updQuery.Append("unit = '" + unit + "',");
                    updQuery.Append("utcOffset = '" + utcOffset + "'");
                    //updQuery.Append("locationFormat = '" + locationFormat + "'");
                    updCmd.CommandText = updQuery.ToString();
                    updCmd.ExecuteNonQuery();

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error while saving application state to the database. Please try again!");
            }
            finally
            {
                _dataConn.Close();
            }
        }

        /// <summary>
        /// manual migration check for SQL Server Compact Edition (CE)
        /// </summary>
        private void CheckCE()
        {
            // connect to db using connection string from parent app
            using (SqlCeConnection cn = new SqlCeConnection(_cnConfig.ToString()))
            {

                // try to open db file but if can't locate then create and try again
                try
                {
                    cn.Open();
                }
                catch (Exception ex)
                {
                    if (ex.Message.Contains("The database file cannot be found."))
                    {
                        using (SqlCeEngine ce = new SqlCeEngine(_cnConfig.ToString()))
                        {
                            ce.CreateDatabase();
                        }
                        cn.Open();
                    }
                    else
                    {
                        throw;
                    }
                }

                // check if schema exists - if so there should always be a migration history table
                if (!CETableExists("__MigrationHistory", cn) && !CETableExists("MigrationHistory", cn)) NewInstall = true;

                // Rename __MigrationHistory table to MigrationHistory (prevents entity framework version check)
                if (CETableExists("__MigrationHistory", cn))
                {
                    ExecCENativeSQL(Properties.Resources.RenameMigrationHistory, cn);
                }

                // if no schema then create entire schema then populate bitmasks
                if (NewInstall)
                {
                    // create main schema
                    _sql = Properties.Resources.CreateEntireSchema;
                    if (_cnConfig.ProviderName.Equals("System.Data.?SqlClient"))        // should never be true - lifted logic as-is from "broken" migration
                    {                                                                   // so exactly matches latest db structure that theoretically should
                        _sql += Properties.Resources._201306050753190_ProgressiveLoad;  // not have the sprocs that ProgressiveLoad introduces.
                    }
                    ExecCENativeSQL(_sql, cn);

                    // populate bitmasks
                    EnsureBitmasksPopulated();
                }
                else
                {
                    // schema exists - build list of already executed migration steps
                    _sql = "SELECT MigrationId AS MigrationTitle FROM [MigrationHistory] WITH (NOLOCK) ORDER BY MigrationId";
                    using (SqlCeCommand cmd = new SqlCeCommand(_sql, cn))
                    {
                        using (SqlCeDataReader rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)) //cmd.ExecuteReader())
                        {
                            if (rs.HasRows)
                            {
                                while (rs.Read())
                                {
                                    string[] titleParts = rs["MigrationTitle"].ToString().Split('_'); // 0 will be key, 1 will be value (description).
                                    if (!_migrated.ContainsKey(titleParts[0]))
                                    {
                                        _migrated.Add(titleParts[0], titleParts[1]);
                                    }
                                }
                            }
                        }
                    }

                    // see if any migration stepa are missing - if so build sql in correct order which should also note step completed in db
                    _sql = BuildAnyMissingMigrationStepsSQL(_migrated);

                    // if any steps were missing
                    if (_sql != "")
                    {
                        // execute sql for any missing steps
                        ExecCENativeSQL(_sql, cn);

                        // ensure bitmasks are populated/repopulated
                        EnsureBitmasksPopulated();
                    }
                }

                cn.Close();
            }
        }

        public List<string> ReadDirsToWatch(int inType)
        {
            List<string> retList = new List<string>();

            SqlCeConnection cn = new SqlCeConnection(m_connectionString);
            if (cn.State == ConnectionState.Closed)
            { cn.Open(); }

            string sql = "select * from DirList";

            try
            {
                SqlCeCommand cmd = new SqlCeCommand(sql, cn);
                cmd.CommandType = CommandType.Text;

                SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                if (rs.HasRows)
                {
                    rs.Read();

                    int dirNameId = rs.GetOrdinal("Directory");
                    string dir = rs.GetString(dirNameId);
                    retList.Add(dir);

                    while (rs.Read())
                    {
                        retList.Add(rs.GetString(dirNameId));
                    }
                }
            }
            catch (Exception /*ex*/)
            {
            }
            finally
            {
                cn.Close();
            }
            return retList;
        }

        public static void BulkInsertFromCSV(string file_path, string table_name, string connection_string, Dictionary<string, Type> data_types, BackgroundWorker backgroundworker)  //, BackgroundWorker backgroundworker
        {
            string line;
            List<string> column_names = new List<string>();
            using (StreamReader reader = new StreamReader(file_path))
            {
                line = reader.ReadLine();
                string[] texts = line.Split(' ');
                foreach (string txt in texts)
                {
                    //MessageBox.Show(txt);
                    column_names.Add(txt);
                }

                using (SqlCeConnection conn = new SqlCeConnection(connection_string))
                {
                    SqlCeCommand cmd = new SqlCeCommand();
                    SqlCeResultSet rs;
                    SqlCeUpdatableRecord rec;
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandText = table_name;
                    cmd.CommandType = CommandType.TableDirect;

                    rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
                   // (sender as BackgroundWorker).ReportProgress(progressPercentage, i);
                   
                    while ((line = reader.ReadLine()) != null)
                    {
                        texts = line.Split(' '); // '/t'

                        rec = rs.CreateRecord();

                        for (int j = 0; j < column_names.Count; ++j)
                        {
                            string columnName = column_names[j];

                            int ordinal = rec.GetOrdinal(columnName);
                            string param_value = "";
                            if (j < texts.Length)
                            {
                                param_value = texts[j];
                            }

                            Type data_type = data_types[columnName];
                            if (data_type == typeof(Int32))
                            {
                                Int32 value = 0;
                                int.TryParse(param_value, out value);
                                rec.SetInt32(ordinal, value);
                            }
                            else if (data_type == typeof(Int64))
                            {
                                Int64 value = 0;
                                Int64.TryParse(param_value, out value);
                                rec.SetInt64(ordinal, value);
                            }
                            else if (data_type == typeof(Int16))
                            {
                                Int16 value = 0;
                                Int16.TryParse(param_value, out value);
                                rec.SetInt16(ordinal, value);
                            }
                            else if (data_type == typeof(string))
                            {
                                rec.SetString(ordinal, param_value);
                            }
                            else if (data_type == typeof(double))
                            {
                                double value = 0;
                                double.TryParse(param_value, out value);
                                rec.SetDouble(ordinal, value);
                            }
                            else if (data_type == typeof(float))
                            {
                                float value = 0;
                                float.TryParse(param_value, out value);
                                rec.SetFloat(ordinal, value);
                            }
                            else if (data_type == typeof(DateTime))
                            {
                                DateTime value;
                                if (DateTime.TryParse(param_value, out value))
                                    rec.SetDateTime(ordinal, value);
                            }
                            else if (data_type == typeof(decimal))
                            {
                                decimal value;
                                decimal.TryParse(param_value, out value);
                                rec.SetDecimal(ordinal, value);
                            }
                            else if (data_type == typeof(Byte))
                            {
                                int temp;
                                int.TryParse(param_value, out temp);
                                Byte[] value = BitConverter.GetBytes(temp);
                                //Byte[].TryParse(param_value, out value);
                                //System.Buffer.BlockCopy(param_value.ToCharArray(), 0, value, 0, 4);
                                //value = GetBytes(param_value);
                               // MessageBox.Show(Convert.ToString(value[0], 16).PadLeft(2, '0'));
//.........这里部分代码省略.........