csharp sqlserverce develop log for sql

1.reference dll

System.Data.SqlServerCe

2.open microsoft sqlserverce

open  or create db if not exist,and special db max site

 

            string sdf = "test" + ".sdf"; //  + basedir

            // if (File.Exists(sdf)) File.Delete(sdf);

            if (!File.Exists(sdf))
            {
                SqlCeEngine engine = new SqlCeEngine("Data Source = " + sdf);
                engine.CreateDatabase();
            }

            dbcon_ = new SqlCeConnection("Data Source = " + sdf + ";SSCE:Max Database Size=2048;");
            dbcon_.Open();

3.create datatable and check it exist

private static bool _createDbTable(SqlCeCommand cmd,string sql)
        {
            try
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e)
            {
                if ((uint)e.ErrorCode != 0x80004005)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            catch (Exception e)
            {
                // if(e. 80004005)
                MessageBox.Show(e.Message);
                return false;
            }
            return true;
        }

how to used:

SqlCeCommand cmd = dbcon.CreateCommand();
_createDbTable(cmd, "CREATE TABLE " + tablename_normal_ + " (id INT IDENTITY(1,1), pid INT DEFAULT 0, parsed INT DEFAULT 0, title NTEXT, url NTEXT, content_id INT DEFAULT 0, w_content_id INT DEFAULT 0,w_submit_url NTEXT)");

4.copy table with some columns

SqlCeCommand cmd = dbcon.CreateCommand();
cmd.CommandText = "INSERT INTO newtablename (pid, parsed, title, url, content_id) SELECT pid, parsed, title, url, id FROM oldtablename";
cmd.ExecuteNonQuery();

5.update some rows columns data by conditions

SqlCeCommand cmd = dbcon.CreateCommand();
cmd.CommandText = "UPDATE tablea SET content_id=1 WHERE content_id=0 AND id IN ( SELECT ID FROM tableb)";
cmd.ExecuteNonQuery();

6.read all rows

SqlCeCommand cmd = Utils.Instance.dbcon.CreateCommand();
cmd.CommandText = "SELECT * FROM tablea";
            rdr = cmd.ExecuteReader();

            bool checkSameUrlItem = false;

            List<int> samedIds = new List<int>();
            job_db_result tmp;
            while (rdr.Read())
            {
                job_db_result item = new job_db_result();
                for (int j = 0, j2 = rdr.FieldCount; j != j2; ++j)
                {
                    string colname = rdr.GetName(j);
                    if (colname == "id")
                        item.id = rdr.GetInt32(j);
                    else if (colname == "pid")
                        item.pid = rdr.GetInt32(j);
                    else if (colname == "parsed")
                        item.parsed = rdr.GetInt32(j);
                    else if ((colname == "title") && !rdr.IsDBNull(j))
                        item.title = rdr.GetString(j);
                    else if ((colname == "url") && !rdr.IsDBNull(j))
                    {
                        item.url = rdr.GetString(j);
                        if (checkSameUrlItem && tryGetResultFromUrl(item.url, out tmp))
                            samedIds.Add(item.id);
                    }
                    else if ((colname == "content_id"))
                        item.content_id = rdr.GetInt32(j);
                    else if ((colname == "w_content_id"))
                        item.w_content_id = rdr.GetInt32(j);
                    else if ((colname == "w_submit_url") && !rdr.IsDBNull(j))
                        item.w_submit_url = rdr.GetString(j);
                }
                result_.TryAdd(item.id, item);
            }

7.insert data and get last insert row id

               SqlCeCommand cmd = Utils.Instance.dbcon.CreateCommand();

                cmd.CommandText = "INSERT INTO " + tablename_normal_ + " (pid, parsed, title, url, content_id, w_content_id, w_submit_url) VALUES (@pid, @parsed, @title, @url, @content_id, @w_content_id, @w_submit_url)";

                cmd.Parameters.AddWithValue("@pid", result.pid);
                cmd.Parameters.AddWithValue("@parsed", result.parsed);
                cmd.Parameters.AddWithValue("@title", result.title);
                cmd.Parameters.AddWithValue("@url", result.url);
                cmd.Parameters.AddWithValue("@content_id", result.content_id);
                cmd.Parameters.AddWithValue("@w_content_id", result.w_content_id);
                cmd.Parameters.AddWithValue("@w_submit_url", ((result.w_submit_url != null) ? result.w_submit_url : ""));
                cmd.ExecuteNonQuery();

                cmd = Utils.Instance.dbcon.CreateCommand();
                cmd.CommandText = "SELECT max(id) FROM " + tablename_normal_;

                newId = (int)cmd.ExecuteScalar();

8.insert row or update data if exists

                SqlCeCommand cmd = Utils.Instance.dbcon.CreateCommand();

                cmd.CommandText = "SELECT id FROM " + tablename_content_ + " WHERE id=@id";
                cmd.Parameters.AddWithValue("@id", item.id);

                if (null == cmd.ExecuteScalar())
                    cmd.CommandText = "INSERT INTO " + tablename_content_ + " (id, content) VALUES (@id, @content)";
                else
                    cmd.CommandText = "UPDATE " + tablename_content_ + " SET content=@content WHERE id=@id";

                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@id", item.id);
                cmd.Parameters.AddWithValue("@content", content);
                cmd.ExecuteNonQuery();

9.last

Thanks for your reading.