读取:
public List<CategoryInfo> GetAllData()
{
List<Model.CategoryInfo> list = new List<CategoryInfo>();
string sql = "select * from CategoryInfo order by CatId desc";
using (SQLiteDataReader reader=SQLiteHelper.ExecuteReader(sql))
{
while (reader.Read())
{
Model.CategoryInfo categoryInfo = new CategoryInfo();
categoryInfo.Catid = Convert.ToInt32(reader[0]);
categoryInfo.Catname = reader[1].ToString();
categoryInfo.Catnum = reader[2].ToString();
categoryInfo.Remark = reader[3].ToString();
categoryInfo.Delflag = Convert.ToInt32(reader[4]);
categoryInfo.Subtime = Convert.ToDateTime(reader[5]);
categoryInfo.Subby = Convert.ToInt32(reader[6]);
list.Add(categoryInfo);
}
}
return list;
}
插入数据
public int InsertData(Model.CategoryInfo categoryInfo)
{
List<string> listParaName = new List<string>();
List<SQLiteParameter> listPara = new List<SQLiteParameter>();
IniList(categoryInfo, listParaName, listPara);
string sql = "insert into CategoryInfo({0}) values({1});";
sql = string.Format(sql, string.Join(",", listParaName), "@" + string.Join(",@", listParaName));
return SQLiteHelper.ExecuteNonQuery(sql, listPara.ToArray());
}
其中插入后sql如下:
insert into CategoryInfo(Catname,Catnum,Delflag,Remark,Subby,Subtime) values(@Catname,@Catnum,@Delflag,@Remark,@Subby,@Subtime);
初始化
private void IniList(CategoryInfo categoryInfo, List<string> listParaName, List<SQLiteParameter> listPara)
{
if (categoryInfo.Catid!=null)
{
listParaName.Add("Catid");
listPara.Add(new SQLiteParameter("@Catid", categoryInfo.Catid));
}
if (categoryInfo.Catname != null)
{
listParaName.Add("Catname");
listPara.Add(new SQLiteParameter("@Catname", categoryInfo.Catname));
}
if (categoryInfo.Catnum != null)
{
listParaName.Add("Catnum");
listPara.Add(new SQLiteParameter("@Catnum", categoryInfo.Catnum));
}
if (categoryInfo.Delflag != null)
{
listParaName.Add("Delflag");
listPara.Add(new SQLiteParameter("@DelFlag", categoryInfo.Delflag));
}
if (categoryInfo.Remark != null)
{
listParaName.Add("Remark");
listPara.Add(new SQLiteParameter("@Remark", categoryInfo.Remark));
}
if (categoryInfo.Subby != null)
{
listParaName.Add("Subby");
listPara.Add(new SQLiteParameter("@Subby", categoryInfo.Subby));
}
if (categoryInfo.Subtime != null)
{
listParaName.Add("Subtime");
listPara.Add(new SQLiteParameter("@Subtime", categoryInfo.Subtime));
}
}