public DataTable GetAllData(string path)
{
SQLiteServer server = new SQLiteServer(path);
string sql = "select * from Dat_Info order by typeId";
return(server.ExecuteQuery(sql));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataTable GetAllType()
{
StringBuilder sql = new StringBuilder();
sql.Append("select TypeId,Name Type ");
sql.Append(" FROM Dat_Type order by Name");
SQLiteParameter[] parameters = {};
SQLiteServer server = new SQLiteServer();
return(server.ExecuteQuery(sql.ToString(), parameters));
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(MInfo model)
{
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO Dat_Info(");
sql.Append("Id,IsCtrl,IsAlt,IsShift,Key,Title,Content,UserId,CreateDateTime,TypeId)");
sql.Append(" values (");
sql.Append("@Id,@IsCtrl,@IsAlt,@IsShift,@Key,@Title,@Content,@UserId,@CreateDateTime,@TypeId)");
SQLiteServer server = new SQLiteServer();
try
{
SQLiteParameter[] parameters =
{
new SQLiteParameter("@Id", DbType.Int32, 4),
new SQLiteParameter("@IsCtrl", DbType.Boolean),
new SQLiteParameter("@IsAlt", DbType.Boolean),
new SQLiteParameter("@IsShift", DbType.Boolean),
new SQLiteParameter("@Key", DbType.String, 500),
new SQLiteParameter("@Title", DbType.String, 500),
new SQLiteParameter("@Content", DbType.String, 30000),
new SQLiteParameter("@UserId", DbType.Int16, 100),
new SQLiteParameter("@CreateDateTime", DbType.DateTime),
new SQLiteParameter("@TypeId", DbType.Int16, 100)
};
parameters[0].Value = int.Parse(server.ExecuteQuery("select ifnull(max(id),0) from Dat_Info").Rows[0][0].ToString()) + 1;
parameters[1].Value = model.IsCtrl;
parameters[2].Value = model.IsAlt;
parameters[3].Value = model.IsShift;
parameters[4].Value = model.Key;
parameters[5].Value = model.Title;
parameters[6].Value = model.Content;
parameters[7].Value = model.UserId;
parameters[8].Value = DateTime.Now.ToString();
parameters[9].Value = model.TypeId;
server.ExecuteNonQuery(sql.ToString(), parameters);
}
catch (Exception ex)
{
Log.WriteErrLog(ex);
return(false);
}
finally
{
}
return(true);
}
public bool IsExistName(string title)
{
StringBuilder sql = new StringBuilder();
sql.Append("select Title,Content");
sql.Append(" FROM Dat_Info where Title = @Title");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@Title", DbType.String, 500)
};
parameters[0].Value = title;
SQLiteServer server = new SQLiteServer();
return(server.ExecuteQuery(sql.ToString(), parameters).Rows.Count > 0);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataTable GetData(string title, int userId)
{
StringBuilder sql = new StringBuilder();
sql.Append("select Title,Content");
sql.Append(" FROM Dat_Info where Title like '%" + title + "%' and UserId= @UserId");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@UserId", DbType.Int16, 100)
};
parameters[0].Value = userId;
SQLiteServer server = new SQLiteServer();
return(server.ExecuteQuery(sql.ToString(), parameters));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataTable GetAll(string title, int typeId)
{
StringBuilder sql = new StringBuilder();
sql.Append("select Id,d.rowid as 序号,t.Name 类型 , Title 标题, Content 内容 FROM Dat_Info d left join Dat_Type t on t.typeId = d.typeId where Title like '%" + title + "%' ");
if (typeId != -1)
{
sql.Append(" and d.typeId =" + typeId);
}
sql.Append(" order by t.typeId");
SQLiteServer server = new SQLiteServer();
SQLiteParameter[] parameters = { };
return(server.ExecuteQuery(sql.ToString(), parameters));
}
/// <summary>
/// 获得数据列表
/// </summary>
public MInfo GetModel(int id)
{
StringBuilder sql = new StringBuilder();
sql.Append("select Id, Title ,Content ,typeId FROM Dat_Info where id = @id ");
SQLiteServer server = new SQLiteServer();
SQLiteParameter[] parameters =
{
new SQLiteParameter("@id", DbType.Int16, 100)
};
parameters[0].Value = id;
MInfo model = new MInfo();
DataTable dt = server.ExecuteQuery(sql.ToString(), parameters);
if (dt.Rows.Count > 0)
{
model.Title = dt.Rows[0][1].ToString();
model.Id = int.Parse(dt.Rows[0][0].ToString());
model.Content = dt.Rows[0][2].ToString();
model.TypeId = int.Parse(dt.Rows[0][3].ToString());
}
return(model);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(MType model)
{
int id = -1;
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO Dat_Type(");
sql.Append("TypeId,Name,Remark)");
sql.Append(" values (");
sql.Append("@TypeId,@Name,@Remark)");
SQLiteServer server = new SQLiteServer();
try
{
SQLiteParameter[] parameters =
{
new SQLiteParameter("@TypeId", DbType.Int32, 4),
new SQLiteParameter("@Name", DbType.String, 100),
new SQLiteParameter("@Remark", DbType.String, 200),
};
id = int.Parse(server.ExecuteQuery("select ifnull(max(TypeId),0) from Dat_Type").Rows[0][0].ToString()) + 1;
parameters[0].Value = id;
parameters[1].Value = model.Name;
parameters[2].Value = model.Name;
server.ExecuteNonQuery(sql.ToString(), parameters);
}
catch (Exception ex)
{
Log.WriteErrLog(ex);
return(id);
}
finally
{
}
return(id);
}