1、 DropDownList 绑定数据后 设置另外的默认值
private void dropdown()
{
cm_serviceEntity model = new cm_serviceEntity();
DataSet ds = GeneralServiceProxy.SelectData(model, "activityid in('1')");
this.Dropcategory.DataSource = ds.Tables[0];
this.Dropcategory.DataTextField="serviceName";
this.Dropcategory.DataValueField = "serviceID";
this.Dropcategory.DataBind();
this.Dropcategory.Items.Insert(0, new ListItem("--请选择--", ""));
this.Dropcategory.SelectedItem.Selected = true;
}
2存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS `ovps`.`pro_post_get`$$
CREATE DEFINER=`root`@`%` PROCEDURE `pro_post_get`(
in pi_chv_xml_model VARCHAR(1000),
in pi_int_pageindex int,
in pi_int_pagesize int,
in pi_chv_str_order varchar(20),
in pi_int_order int,
inout po_int_recoadcount int
)
begin
declare v_sql_query VARCHAR(4000);
declare v_sql_where VARCHAR(2000);
declare v_sql_count VARCHAR(2000);
declare v_id int;
declare v_name varchar(50);
declare v_communityid int;
declare v_categoryid int;
declare v_serviceid int;
declare v_activityid int;
declare v_title varchar(50);
declare v_begintime varchar(50);
declare v_endtime varchar(50);
declare v_state varchar(10);
declare v_limitStart int;
declare v_limitEnd int;
declare v_categoryids varchar(10);
declare v_order_where varchar(180);
declare v_asc_desc_where varchar(10);
set v_id = ExtractValue(pi_chv_xml_model, '/root/item[1]/@id') ;
set v_name = ExtractValue(pi_chv_xml_model, '/root/item[1]/@name') ;
set v_communityid = ExtractValue(pi_chv_xml_model, '/root/item[1]/@communityid') ;
set v_categoryid = ExtractValue(pi_chv_xml_model, '/root/item[1]/@categoryid') ;
set v_serviceid = ExtractValue(pi_chv_xml_model, '/root/item[1]/@serviceid') ;
set v_activityid = ExtractValue(pi_chv_xml_model, '/root/item[1]/@activityid') ;
set v_title = ExtractValue(pi_chv_xml_model, '/root/item[1]/@title') ;
set v_begintime = ExtractValue(pi_chv_xml_model, '/root/item[1]/@begintime') ;
set v_endtime = ExtractValue(pi_chv_xml_model, '/root/item[1]/@endtime') ;
set v_state = ExtractValue(pi_chv_xml_model, '/root/item[1]/@state') ;
set v_categoryids = ExtractValue(pi_chv_xml_model,'/root/item[1]/@categoryids');
set v_sql_where = ' where 1 = 1 ';
set v_order_where='';
if v_id is not null and v_id > 0 then
set v_sql_where = CONCAT(v_sql_where,' and cm_post.id = ',v_id);
end if;
if v_name is not null and length(v_name) > 0 then
set v_sql_where = CONCAT(v_sql_where,' and cm_post.name = ',v_name);
end if;
if v_communityid is not null and v_communityid > 0 then
set v_sql_where = CONCAT(v_sql_where,' and cm_post.communityid = ',v_communityid);
end if;
if v_categoryid is not null and v_categoryid > 0 then
set v_sql_where = CONCAT(v_sql_where,' and cm_post.categoryid=',v_categoryid);
end if;
if v_title is not null and length(v_title) > 0 then
set v_sql_where = CONCAT(v_sql_where,' and cm_post.like=''',v_title,'''');
end if;
if v_begintime is not null and length(v_begintime) > 0 then
set v_sql_where = CONCAT(v_sql_where,' and cm_post.createtime >= ',v_begintime,' and cm_post.createtime <= ',v_endtime);
end if;
if v_state is not null and length(v_state) > 0 then
set v_sql_where = CONCAT(v_sql_where,' and cm_post.state >= ',v_state);
end if;
if v_categoryids is not null and length(v_categoryids) >0 then
set v_sql_where = CONCAT(v_sql_where,' and cm_post.categoryid in (',v_categoryids,')');
end if;
if pi_int_order is not null and pi_int_order < 0 then
set v_asc_desc_where = ' desc';
end if;
if pi_int_order is not null and pi_int_order > 0 then
set v_asc_desc_where = ' asc';
end if;
if pi_chv_str_order is not null and length(pi_chv_str_order) > 0 then
set v_order_where=CONCAT(v_order_where,' order by cm_post.IsTop desc , cm_post.',pi_chv_str_order,v_asc_desc_where);
end if;
if pi_int_pagesize < 0 then
set v_sql_query = CONCAT(
'select cm_post.*,t1.CategoryName,c.CommunityName,sm.Username,
se.ServiceName,ac.ActivityName
from cm_post
left join cm_contentcategory t1
on cm_post.categoryid = t1.id
left join co_community c
on cm_post.communityid = c.id
left join sm_admin sm
on cm_post.CreateUserID=sm.id
left join cm_service se
on cm_post.ServiceID = se.ServiceID
left join cm_activity ac
on se.ActivityID=ac.ActivityID '
,v_sql_where,v_order_where);
end if;
if pi_int_pagesize > 0 then
SET v_limitStart = (pi_int_pageindex - 1)*pi_int_pagesize;
SET v_limitEnd = pi_int_pageindex*pi_int_pagesize - 1;
set v_sql_query = CONCAT(
'select cm_post.*,t1.CategoryName,c.CommunityName,sm.Username,se.ServiceName,ac.ActivityName
from cm_post
left join cm_contentcategory t1
on cm_post.categoryid = t1.id
left join co_community c
on cm_post.communityid = c.id
left join sm_admin sm
on cm_post.CreateUserID=sm.id
left join cm_service se
on cm_post.ServiceID = se.ServiceID
left join cm_activity ac
on se.ActivityID=ac.ActivityID '
,v_sql_where,v_order_where
,' limit ',v_limitStart,',',v_limitEnd);
end if;
set @v_sql_query = v_sql_query;
-- select @v_sql_query ;
PREPARE s_cnt from @v_sql_query;
EXECUTE s_cnt;
DEALLOCATE PREPARE s_cnt;
if (po_int_recoadcount = 0) then
set v_sql_count = CONCAT('select count(1) into',po_int_recoadcount,' from cm_post
left join cm_contentcategory t1
on cm_post.categoryid = t1.id
left join co_community c
on cm_post.communityid = c.id ', v_sql_where);
set @v_sql_count = v_sql_count;
PREPARE s_cnt from @v_sql_count;
EXECUTE s_cnt;
DEALLOCATE PREPARE s_cnt;
end if;
end$$
DELIMITER ;
public DataSet PostInfoList(string model_xml, int page_index, int page_size, string columunorder, int order, ref int total_recoad_count)
{
DataSet ds = new DataSet();
MySqlParameter param1 = new MySqlParameter();
param1.ParameterName = "@pi_chv_xml_model";
param1.MySqlDbType = MySqlDbType.String;
param1.Value = model_xml;
MySqlParameter param2 = new MySqlParameter();
param2.ParameterName = "@pi_int_pageindex";
param2.MySqlDbType = MySqlDbType.Int32;
param2.Value = page_index;
MySqlParameter param3 = new MySqlParameter();
param3.ParameterName = "@pi_int_pagesize";
param3.MySqlDbType = MySqlDbType.Int32;
param3.Value = page_size;
MySqlParameter param4 = new MySqlParameter();
param4.ParameterName = "@po_int_recoadcount";
param4.MySqlDbType = MySqlDbType.Int32;
param4.Direction = ParameterDirection.InputOutput;
param4.Value = total_recoad_count;
MySqlParameter param5 = new MySqlParameter();
param5.ParameterName = "@pi_chv_str_order";
param5.MySqlDbType = MySqlDbType.VarChar;
param5.Value = columunorder;
MySqlParameter param6 = new MySqlParameter();
param6.ParameterName = "@pi_int_order";
param6.MySqlDbType = MySqlDbType.Int32;
param6.Value = order;
try
{
MySqlParameter[] param = new MySqlParameter[] { param1, param2, param3, param4,param5,param6 };
ds=SqlHelper.GetDataSet(CommandType.StoredProcedure,pro_post_get", param);
if (ds.Tables[1] != null)
{
total_recoad_count = int.Parse(ds.Tables[1].Rows[0][0].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
LogInfo("[end]");
return ds;
}
DataSet PostInfoList(string model_xml, int page_index, int page_size, string columunorder, int order, ref int total_recoad_count);
public static DataSet PostInfoList(string xml, int pageindex, int pagesize, string record, int order, ref int recoadcount)
{
return CreateClient().PostInfoList(xml, pageindex, pagesize, record, order, ref recoadcount);
}
定义一个绑定grideView的方法
private void getdate()
{
string xml = string.Format("<root><item categoryid=\"{0}\" /></root>", "邻里驿站");
DataSet ds = CMProxy.PostInfoList(xml,ctl_pages1.PageIndex, ctl_pages1.PageSize, "createtime", -1, ref total);
ctl_pages1.Count = total;
if (ds.Tables[0].Rows.Count == 0)
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
GridView1.DataSource = ds;
GridView1.DataBind();
int columnCount = GridView1.Rows[0].Cells.Count;
GridView1.Rows[0].Cells.Clear();
GridView1.Rows[0].Cells.Add(new TableCell());
GridView1.Rows[0].Cells[0].ColumnSpan = columnCount;
GridView1.Rows[0].Cells[0].Text="没有数据信息";
GridView1.RowStyle.HorizontalAlign=System.Web.UI.WebControls.HorizontalAlign.Center;
}
else
{
this.GridView1.DataSource = ds.Tables[0];
this.GridView1.DataBind();
}