Sunday, May 4, 2014

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.


 


 


 


 


 

 
 

1 comment:

Online Pharmacy said...

Do you mind if I quote a few of your posts as long as I provide credit and
sources back to your website? My website is in the very same area of interest as
yours and my users would really benefit from some of the information you provide here.
Please let me know if this okay with you. Thanks a lot!