sqlite 项目实例

        private string url = "http://gw.api.taobao.com/router/rest";
        private string appkey ="12198044"; //"12299799";//"12130006";
        private string appsecret = "40f6ceced642c41186ad7ccba28fa227";// "afa50ecbf3c01f7073dc09ab99b6db4c";//"6e254a97e30b0ae04eb31b21c7a24b6b";

        //单击事件
        private void simpleButton1_Click(object sender, EventArgs e)
        {
            //GetItemsOnsale2();
            Service_Api_TaoBao_Itemcats();
        }

        private DataTable dtcatelog2; //绑定数据源
        private DataRow dtcatelog;
        private DataTable dtcatelogs;
        private DataTable dtcatelogs2;

        /// <summary>
        /// 获取商家标准类目 添加至 指定的事情
        /// </summary>
        private void Service_Api_TaoBao_Itemcats()
        {
            client = new DefaultTopClient(url, appkey, appsecret, "json");
            dtcatelogs = null;
            dtcatelogs2 = null;
            dtcatelog2 = null;
            dtcatelog = null;
            DirSearchDt("0");
            if (dtcatelogs2 != null)
            {
                this.vGridControl1.DataSource = dtcatelogs2;
                GetDirSearch(dtcatelogs2);
            }
        }

        /// <summary>
        /// API 获取商家标准类目
        /// </summary>
        /// <param name="parentid"></param>
        private void DirSearchDt(string parentid)
        {
            try
            {
                ItemcatsGetRequest req = new ItemcatsGetRequest();
                req.Fields = "cid,parent_cid,is_parent,status,sort_order,name";
                req.ParentCid = Convert.ToInt32(parentid);
                ItemcatsGetResponse props = client.Execute(req);
                if (props.IsError) return;

                if (dtcatelog != null && dtcatelogs!=null) dtcatelogs.Rows.Remove(dtcatelog);
                if (dtcatelog2 != null && dtcatelog2.Rows.Count>0)
                { 
                    foreach( DataRow deleteItem in dtcatelog2.Rows)
                    {
                        dtcatelogs.Rows.Remove(deleteItem);
                    }
                    dtcatelog2.Rows.Clear();
                }

                DataTable dt = ToDataSet(props.Body).Tables["item_cat"];

                if (dt != null)
                {
                    if (dtcatelogs == null)
                        dtcatelogs = dt.Clone();
                    if (dtcatelogs2 == null)
                        dtcatelogs2 = dt.Clone();
                    dtcatelogs.Merge(dt, true, MissingSchemaAction.Ignore);
                    dtcatelogs2.Merge(dt, true, MissingSchemaAction.Ignore);
                }


                DataRow itemData = dtcatelogs.Rows[0];
                if (Convert.ToBoolean(itemData["IS_PARENT"]))
                {
                    dtcatelog = itemData;
                    DirSearchDt(itemData["CID"].ToString());
                }
                else
                {
                    dtcatelogs.Rows.Remove(itemData);
                    for (int i = 1; i <= dtcatelogs.Rows.Count; i++)
                    {
                        if (Convert.ToBoolean(dtcatelogs.Rows[0]["IS_PARENT"]) && dtcatelogs.Rows[0]["CID"].ToString() != "0")
                        {
                            dtcatelog = dtcatelogs.Rows[0];
                            DirSearchDt(dtcatelogs.Rows[0]["CID"].ToString());
                        }
                        else
                        {
                            dtcatelogs.Rows.Remove(dtcatelogs.Rows[0]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                string error = ex.Message;
            }
               
        }

          
        /// <summary>
        /// 添加到 SQLite文件
        /// </summary>
        /// <param name="dt">商家标准类目 集合</param>
        private void GetDirSearch(DataTable dt)
        {
            bool suc = false;
            string sourceUrl="data source=D:\\DB\\CatelogDataBase.db";
            if (string.IsNullOrEmpty(sourceUrl))
            {
                //配置错误
            }
            else
            {
                string[] arrary = sourceUrl.Split('=');
                if (System.IO.File.Exists(arrary[1]))
                {
                    //文件不存在
                }
            }
            using (DbConnection conn = new SQLiteConnection(sourceUrl))
            {
                conn.Open();

                using (DbTransaction dbTrans = conn.BeginTransaction())
                {
                    using (DbCommand comm = conn.CreateCommand())
                    {
                        try
                        {

                            comm.CommandText = "select CID, PARENT_CID,IS_PARENT,STATUS,SORT_ORDER,NAME1 as NAME,OWNERID,MODIFIERID,IS_ACTIVE, TYPE,ID from tb_catelog";
                            comm.CommandType = CommandType.Text;
                            using (IDataReader reader = comm.ExecuteReader())
                            {
                                if (reader.Read()) //存在
                                {
                                    suc = true;
                                }
                            }

                            if (suc)
                            {
                                comm.CommandText = "DELETE FROM tb_catelog";
                                int icount = comm.ExecuteNonQuery();
                            }
                            //comm.CommandText = "insert into tb_catelog(CID, PARENT_CID,IS_PARENT,STATUS,SORT_ORDER,NAME1,OWNERID,MODIFIERID,IS_ACTIVE, TYPE) values(@CID, @PARENT_CID,@IS_PARENT,@STATUS,@SORT_ORDER,@NAME1,@OWNERID,@MODIFIERID,@IS_ACTIVE, @TYPE)";
                            comm.CommandText = "insert into tb_catelog(CID,IS_PARENT,NAME1, PARENT_CID,SORT_ORDER,STATUS,OWNERID,MODIFIERID,IS_ACTIVE, TYPE) values(?,?,?,?,?,?,?,?,?,?)";
                            DbParameter Field1 = null;
                            DataRow dr = null;
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                comm.Parameters.Clear();
                                dr = dt.Rows[i];
                                for (int j = 0; j < dt.Columns.Count; j++)
                                {
                                    Field1 = comm.CreateParameter();
                                    Field1.Value = dr[j];
                                    comm.Parameters.Add(Field1);
                                    //Field1 = null;
                                }
                                for (int k = 0; k <= 3; k++)
                                {
                                    Field1 = comm.CreateParameter();
                                    if (k == 2)
                                    {
                                        Field1.Value = true;
                                    }
                                    else
                                    {
                                        Field1.Value = "burgeon";
                                    }
                                    comm.Parameters.Add(Field1);
                                }
                                comm.ExecuteNonQuery();

                            }

                            dbTrans.Commit();
                        }
                        catch
                        {
                            dbTrans.Rollback();
                        }

                    }

                }

            }

        }