public static bool CreateAdmin(Admin info)
{
string sql = @"insert into sys_admin (username, openid, realname, create_time, last_login_time,
last_login_ip, roles_name, role_list, role_name_list, isvalid)
values (@username, @openid, @realname, @create_time, @last_login_time,
@last_login_ip, @roles_name, @role_list, @role_name_list, @isvalid)";
DbParameter[] parms =
{
PostgreHelper.MakeInParam("@username", NpgsqlDbType.Varchar, 50, info.username),
PostgreHelper.MakeInParam("@openid", NpgsqlDbType.Varchar, 50, info.openid),
PostgreHelper.MakeInParam("@realname", NpgsqlDbType.Varchar, 20, info.realname),
PostgreHelper.MakeInParam("@create_time", NpgsqlDbType.TimestampTZ, -1, info.create_time),
PostgreHelper.MakeInParam("@last_login_time", NpgsqlDbType.TimestampTZ, -1, info.last_login_time),
PostgreHelper.MakeInParam("@last_login_ip", NpgsqlDbType.Varchar, 15, info.last_login_ip),
PostgreHelper.MakeInParam("@roles_name", NpgsqlDbType.Varchar, 200, info.roles_name),
PostgreHelper.MakeInParam("@role_list", NpgsqlDbType.Array | NpgsqlDbType.Integer, 200, info.role_list),
PostgreHelper.MakeInParam("@role_name_list", NpgsqlDbType.Array | NpgsqlDbType.Varchar, 200, info.role_name_list),
PostgreHelper.MakeInParam("@isvalid", NpgsqlDbType.Boolean, -1, info.isvalid),
};
int rlt = PostgreHelper.ExecuteNonQuery(PostGreSqlConnectionString, CommandType.Text, sql, parms);
return(rlt > 0);
}
public static bool Createcomment(Comment info)
{
string sql = @"insert into article_comment (article_id, comm_content, comm_username, comm_time, ip, auditor,
audit_time, is_valid, shield_words, forum_topicid, forum_threadid,
last_modifier, modify_time)
values (@article_id, @comm_content, @comm_username, @comm_time, @ip, @auditor,
@audit_time, @is_valid, @shield_words, @forum_topicid, @forum_threadid,
@last_modifier, @modify_time)";
DbParameter[] parms =
{
PostgreHelper.MakeInParam("@article_id", NpgsqlDbType.Bigint, -1, info.article_id),
PostgreHelper.MakeInParam("@comm_content", NpgsqlDbType.Text, -1, info.comm_content),
PostgreHelper.MakeInParam("@comm_username", NpgsqlDbType.Varchar, 50, info.comm_username),
PostgreHelper.MakeInParam("@comm_time", NpgsqlDbType.TimestampTZ, -1, info.comm_time),
PostgreHelper.MakeInParam("@ip", NpgsqlDbType.Varchar, 15, info.ip),
PostgreHelper.MakeInParam("@auditor", NpgsqlDbType.Varchar, 50, info.auditor),
PostgreHelper.MakeInParam("@audit_time", NpgsqlDbType.TimestampTZ, -1, info.audit_time),
PostgreHelper.MakeInParam("@is_valid", NpgsqlDbType.Boolean, -1, info.is_valid),
PostgreHelper.MakeInParam("@shield_words", NpgsqlDbType.Varchar, 200, info.shield_words),
PostgreHelper.MakeInParam("@forum_topicid", NpgsqlDbType.Integer, -1, info.forum_topicid),
PostgreHelper.MakeInParam("@forum_threadid", NpgsqlDbType.Integer, -1, info.forum_threadid),
PostgreHelper.MakeInParam("@last_modifier", NpgsqlDbType.Varchar, 50, info.last_modifier),
PostgreHelper.MakeInParam("@modify_time", NpgsqlDbType.TimestampTZ, -1, info.modify_time),
};
int rlt = PostgreHelper.ExecuteNonQuery(PostGreSqlConnectionString, CommandType.Text, sql, parms);
return(rlt > 0);
}
public static bool CreateAticleImage(long id, string image)
{
string sql = @"insert into article_picture (article_id, image_url)
values (@id,@image_url)
";
DbParameter[] parms = {
PostgreHelper.MakeInParam("@id", NpgsqlDbType.Bigint, -1, id),
PostgreHelper.MakeInParam("@image_url", NpgsqlDbType.Varchar, -1, image),
};
int rlt = PostgreHelper.ExecuteNonQuery(PostGreSqlConnectionString, CommandType.Text, sql, parms);
return rlt > 0;
}
public static bool CreateAticleTag(long articleId, string tag, int type)
{
if (tag.Length > 16)
{
return true;
}
string sql = @"insert into article_tag (article_id,tag_name,tag_type,tag_property_id)
values (@article_id,@tag_name,@tag_type,@tag_property_id)
";
DbParameter[] parms = {
PostgreHelper.MakeInParam("@article_id", NpgsqlDbType.Bigint, -1, articleId),
PostgreHelper.MakeInParam("@tag_name", NpgsqlDbType.Varchar, 20, tag),
PostgreHelper.MakeInParam("@tag_type", NpgsqlDbType.Integer, -1, type),
PostgreHelper.MakeInParam("@tag_property_id", NpgsqlDbType.Integer, -1, 0),
};
int rlt = PostgreHelper.ExecuteNonQuery(PostGreSqlConnectionString, CommandType.Text, sql, parms);
return rlt > 0;
}
public static bool CreateArticle(Article info, List<Comment> comment)
{
int rlt = 0;
DataTable dt = new DataTable();
NpgsqlConnection cnn = new NpgsqlConnection(PostGreSqlConnectionString);
NpgsqlCommand cm = new NpgsqlCommand();
cm.Connection = cnn;
cnn.Open();
DbTransaction trans = cnn.BeginTransaction();
try
{
string sql = @"insert into article (id, title, content, publish_time,
syn_forum, syn_weixin, forum_id, media_id, create_time, creater_id,
creater, hits, unit_id, status, last_modifier, modify_time,
industry_id, industry_name, summary, is_publish, has_picture,
comments, image_url,tags,search_tags,images,url)
values (@id,@title,@content,@publish_time,
@syn_forum, @syn_weixin, @forum_id, @media_id, @create_time, @creater_id,
@creater, @hits, @unit_id, @status, @last_modifier, @modify_time,
@industry_id, @industry_name, @summary, @is_publish, @has_picture,
@comments, @image_url,@tags,@search_tags,@images,@url )";
//tags, source_cate_id @tags, @source_cate_id
DbParameter[] parms = {
PostgreHelper.MakeInParam("@id", NpgsqlDbType.Bigint, -1, info.Id),
//PostgreHelper.MakeInParam("@source_art_id", NpgsqlDbType.Integer, -1, info.Source_Art_Id),
//PostgreHelper.MakeInParam("@source_name", NpgsqlDbType.Varchar, 50, info.Source_Name),
//PostgreHelper.MakeInParam("@source_url", NpgsqlDbType.Varchar, 500, info.Source_Url),
PostgreHelper.MakeInParam("@title", NpgsqlDbType.Varchar, 255, info.Title),
PostgreHelper.MakeInParam("@content", NpgsqlDbType.Text, -1, info.Content),
PostgreHelper.MakeInParam("@publish_time", NpgsqlDbType.TimestampTZ, -1, info.Publish_Time),
PostgreHelper.MakeInParam("@syn_forum", NpgsqlDbType.Boolean, -1, info.Syn_Forum),
PostgreHelper.MakeInParam("@syn_weixin", NpgsqlDbType.Boolean, -1, info.Syn_Weixin),
PostgreHelper.MakeInParam("@forum_id", NpgsqlDbType.Integer, -1, info.Forum_Id),
PostgreHelper.MakeInParam("@media_id", NpgsqlDbType.Varchar, 80, info.Media_Id),
PostgreHelper.MakeInParam("@create_time", NpgsqlDbType.TimestampTZ, -1, info.Create_Time),
PostgreHelper.MakeInParam("@creater_id", NpgsqlDbType.Integer, -1, info.Creater_Id),
PostgreHelper.MakeInParam("@creater", NpgsqlDbType.Varchar, 50, info.Creater),
PostgreHelper.MakeInParam("@hits", NpgsqlDbType.Integer, -1, info.Hits),
PostgreHelper.MakeInParam("@unit_id", NpgsqlDbType.Integer, -1, info.Unit_Id),
PostgreHelper.MakeInParam("@status", NpgsqlDbType.Integer, -1, info.Status),
PostgreHelper.MakeInParam("@last_modifier", NpgsqlDbType.Varchar, 50, info.Last_Modifier),
PostgreHelper.MakeInParam("@modify_time", NpgsqlDbType.TimestampTZ, -1, info.Modify_Time),
//PostgreHelper.MakeInParam("@is_del", NpgsqlDbType.Boolean, -1, info.Is_Del),
//PostgreHelper.MakeInParam("@del_time", NpgsqlDbType.TimestampTZ, -1, info.Del_Time),
PostgreHelper.MakeInParam("@industry_id", NpgsqlDbType.Integer, -1, info.Industry_Id),
PostgreHelper.MakeInParam("@industry_name", NpgsqlDbType.Varchar, 20, info.Industry_Name),
PostgreHelper.MakeInParam("@summary", NpgsqlDbType.Varchar, 500, info.Summary),
PostgreHelper.MakeInParam("@is_publish", NpgsqlDbType.Boolean, -1, info.Is_Publish),
PostgreHelper.MakeInParam("@has_picture", NpgsqlDbType.Boolean, -1, info.Has_Picture),
PostgreHelper.MakeInParam("@comments", NpgsqlDbType.Integer, -1, info.Comments),
PostgreHelper.MakeInParam("@image_url", NpgsqlDbType.Varchar, 500, info.Image_Url),
PostgreHelper.MakeInParam("@tags", NpgsqlDbType.Array | NpgsqlDbType.Varchar, 20, info.Tags),
PostgreHelper.MakeInParam("@search_tags", NpgsqlDbType.Array | NpgsqlDbType.Varchar , 20, info.Search_Tags),
PostgreHelper.MakeInParam("@images", NpgsqlDbType.Array | NpgsqlDbType.Varchar , 500, info.Images),
PostgreHelper.MakeInParam("@url", NpgsqlDbType.Varchar, 200, info.Url),
//PostgreHelper.MakeInParam("@source_cate_id", NpgsqlDbType.Integer, -1,info.Source_cate_id),
};
rlt = PostgreHelper.ExecuteNonQuery(trans, CommandType.Text, sql, parms);
// return rlt > 0;
//PostgreHelper.ExecuteNonQuery(PostGreSqlConnectionString, CommandType.Text, sql, parms);
foreach (var info_comm in comment)
{
sql = @"insert into article_comment (article_id, comm_content, comm_username, comm_time, ip, auditor,
audit_time, is_valid, shield_words, forum_topicid, forum_threadid,
last_modifier, modify_time)
values (@article_id, @comm_content, @comm_username, @comm_time, @ip, @auditor,
@audit_time, @is_valid, @shield_words, @forum_topicid, @forum_threadid,
@last_modifier, @modify_time)";
DbParameter[] parms1 = {
PostgreHelper.MakeInParam("@article_id", NpgsqlDbType.Bigint, -1, info_comm.article_id),
PostgreHelper.MakeInParam("@comm_content", NpgsqlDbType.Varchar, 200, info_comm.comm_content),
PostgreHelper.MakeInParam("@comm_username", NpgsqlDbType.Varchar, 50, info_comm.comm_username),
PostgreHelper.MakeInParam("@comm_time", NpgsqlDbType.TimestampTZ, -1, info_comm.comm_time),
PostgreHelper.MakeInParam("@ip", NpgsqlDbType.Varchar, 15, info_comm.ip),
PostgreHelper.MakeInParam("@auditor", NpgsqlDbType.Varchar, 50, info_comm.auditor),
PostgreHelper.MakeInParam("@audit_time", NpgsqlDbType.TimestampTZ, -1, info_comm.audit_time),
PostgreHelper.MakeInParam("@is_valid", NpgsqlDbType.Boolean, -1, info_comm.is_valid),
PostgreHelper.MakeInParam("@shield_words", NpgsqlDbType.Varchar, 200, info_comm.shield_words),
PostgreHelper.MakeInParam("@forum_topicid", NpgsqlDbType.Integer, -1, info_comm.forum_topicid),
PostgreHelper.MakeInParam("@forum_threadid", NpgsqlDbType.Integer, -1, info_comm.forum_threadid),
PostgreHelper.MakeInParam("@last_modifier", NpgsqlDbType.Varchar, 50, info_comm.last_modifier),
PostgreHelper.MakeInParam("@modify_time", NpgsqlDbType.TimestampTZ, -1, info_comm.modify_time),
};
rlt = PostgreHelper.ExecuteNonQuery(trans, CommandType.Text, sql, parms1);
}
trans.Commit();
}
catch(Exception ex)
{
throw;
rlt = 0;
trans.Rollback();
}
finally
{
cnn.Close();
trans.Dispose();
cnn.Dispose();
}
return rlt > 0;
}
public static bool CreateUnit(Unit unit)
{
/**
* 1.检测loginName+"@博客"单元是否存在
* 存在:获取单元id
* 不存在:执行2
*
* 2.根据运营者名称查询单元
* 无:将博客单元的运营者指定为 loginName //username=loginName
* 有:将博客单元的协助管理员指定为 loginName //asmanagers = [loginName]
*
* 创建博客单元
* */
string sql = @"insert into unit ( id,unit_name, forum_id, create_time, isvalid, creater,
industry_id, unit_type, unit_logo, status,
article_count, auditor, audit_time, last_publish_time, tags,
username, address, longitude, latitude, phone, linkman, wechat,
website, asmanagers, order_number,unit_domain)
values ( @id,@unit_name, @forum_id, @create_time, @isvalid, @creater,
@industry_id, @unit_type, @unit_logo, @status,
@article_count, @auditor, @audit_time, @last_publish_time, @tags,
@username, @address, @longitude, @latitude, @phone, @linkman, @wechat,
@website, @asmanagers, @order_number,@unit_domain)";
DbParameter[] parms =
{
PostgreHelper.MakeInParam("@id", NpgsqlDbType.Integer, -1, unit.id),
PostgreHelper.MakeInParam("@unit_name", NpgsqlDbType.Varchar, 20, unit.unit_name),
//PostgreHelper.MakeInParam("@unit_remark", NpgsqlDbType.Varchar, 500, unit.unit_remark),
PostgreHelper.MakeInParam("@forum_id", NpgsqlDbType.Integer, -1, unit.forum_id),
PostgreHelper.MakeInParam("@create_time", NpgsqlDbType.TimestampTZ, -1, unit.create_time),
PostgreHelper.MakeInParam("@isvalid", NpgsqlDbType.Boolean, -1, unit.isvalid),
PostgreHelper.MakeInParam("@creater", NpgsqlDbType.Varchar, 50, unit.creater),
//PostgreHelper.MakeInParam("@modifier", NpgsqlDbType.Varchar, 50, unit.modifier),
//PostgreHelper.MakeInParam("@update_time", NpgsqlDbType.TimestampTZ, -1, unit.update_time),
PostgreHelper.MakeInParam("@industry_id", NpgsqlDbType.Integer, -1, unit.industry_id),
PostgreHelper.MakeInParam("@unit_type", NpgsqlDbType.Integer, -1, unit.unit_type),
PostgreHelper.MakeInParam("@unit_logo", NpgsqlDbType.Varchar, 200, unit.unit_logo),
PostgreHelper.MakeInParam("@status", NpgsqlDbType.Integer, -1, unit.status),
PostgreHelper.MakeInParam("@article_count", NpgsqlDbType.Integer, -1, unit.article_count),
PostgreHelper.MakeInParam("@auditor", NpgsqlDbType.Varchar, 50, unit.auditor),
PostgreHelper.MakeInParam("@audit_time", NpgsqlDbType.TimestampTZ, -1, unit.audit_time),
PostgreHelper.MakeInParam("@last_publish_time", NpgsqlDbType.TimestampTZ, -1, unit.last_publish_time),
PostgreHelper.MakeInParam("@tags", NpgsqlDbType.Array | NpgsqlDbType.Varchar, -1, unit.tags),
PostgreHelper.MakeInParam("@username", NpgsqlDbType.Varchar, 50, unit.username),
PostgreHelper.MakeInParam("@address", NpgsqlDbType.Varchar, 200, unit.address),
PostgreHelper.MakeInParam("@longitude", NpgsqlDbType.Double, -1, unit.longitude),
PostgreHelper.MakeInParam("@latitude", NpgsqlDbType.Double, -1, unit.latitude),
PostgreHelper.MakeInParam("@phone", NpgsqlDbType.Varchar, 50, unit.phone),
PostgreHelper.MakeInParam("@linkman", NpgsqlDbType.Varchar, 50, unit.linkman),
PostgreHelper.MakeInParam("@wechat", NpgsqlDbType.Varchar, 200, unit.wechat),
PostgreHelper.MakeInParam("@website", NpgsqlDbType.Varchar, 200, unit.website),
PostgreHelper.MakeInParam("@asmanagers", NpgsqlDbType.Array | NpgsqlDbType.Varchar, 50, unit.asmanagers),
PostgreHelper.MakeInParam("@order_number", NpgsqlDbType.Integer, -1, unit.order_number),
PostgreHelper.MakeInParam("@unit_domain", NpgsqlDbType.Varchar, 50, unit.unit_domain),
};
int rlt = PostgreHelper.ExecuteNonQuery(PostGreSqlConnectionString, CommandType.Text, sql, parms);
return(rlt > 0);
}