Full-house

背景：

公司生产系统数据库中的一个功能，显示了来自很多字段的值，以“/” 分隔，产生的问题是，如果某几个字符没有内容的话，那显示出来的效果就可能会出现多个“/” 相连。如“////////////////” 我们需要将这么多“/” 变成一个”/”。

解决方案：

使用SQL 自定义的函数，循环地将所有的“//” 变成”/” 即可。

Create function [dbo].[ReplaceFG](@string varchar(1000),@old varchar(50),@new varchar(50))
RETURNS varchar(1000)
AS
begin
declare @temp varchar(1000)
while CHARINDEX(@old,@string) > 0
begin
set @string = Replace(@string,@old,@new)
set @temp = @string
end
return @temp
end

示例代码如下：

select dbo.ReplaceFG(’sa/////////////00000////////dg///////sdf’,'//’,'/’) value

value
———————-
sa/00000/dg/sdf

(1 row(s) affected)

单个修改：

EXEC sp_changeobjectowner ‘要改的表名’,'dbo’

批量修改：

EXEC sp_MSforeachtable ‘exec sp_changeobjectowner ”?”,”dbo” ‘

参考网址：http://www.cnblogs.com/insus/articles/1433141.html

这两天一直为一件事苦恼，怎么使用日期选择器来选取几个不连续的日期，今天终于找到了。

http://nogray.com/calendar.php 【JavaScript Calendar Component】

当然，如果你有其它需要，这里有30个很Cool的Calendar Date Picker

http://www.edesignerz.net/tips-a-tricks/16700-show-time-30-jquery-calendar-date-picker-plugins

试一下！

private static void DemonstrateMergeTable()
{
DataTable table1 = new DataTable(“Items”);

// Add columns
DataColumn column1 = new DataColumn(“id”, typeof(System.Int32));
DataColumn column2 = new DataColumn(“item”, typeof(System.Int32));
table1.Columns.Add(column1);
table1.Columns.Add(column2);

// Set the primary key column.
table1.PrimaryKey = new DataColumn[] { column1 };

// Add RowChanged event handler for the table.
table1.RowChanged +=
new System.Data.DataRowChangeEventHandler(Row_Changed);

// Add some rows.
DataRow row;
for (int i = 0; i <= 3; i++)
{
row = table1.NewRow();
row["id"] = i;
row["item"] = i;
table1.Rows.Add(row);
}

// Accept changes.
table1.AcceptChanges();
PrintValues(table1, “Original values”);

// Create a second DataTable identical to the first.
DataTable table2 = table1.Clone();

// Add three rows. Note that the id column can’t be the
// same as existing rows in the original table.
row = table2.NewRow();
row["id"] = 14;
row["item"] = 774;
table2.Rows.Add(row);

row = table2.NewRow();
row["id"] = 12;
row["item"] = 555;
table2.Rows.Add(row);

row = table2.NewRow();
row["id"] = 13;
row["item"] = 665;
table2.Rows.Add(row);

// Merge table2 into the table1.
Console.WriteLine(“Merging”);
table1.Merge(table2);
PrintValues(table1, “Merged With table1″);

}

private static void Row_Changed(object sender,
DataRowChangeEventArgs e)
{
Console.WriteLine(“Row changed {0}\t{1}”,
e.Action, e.Row.ItemArray[0]);
}

private static void PrintValues(DataTable table, string label)
{
// Display the values in the supplied DataTable:
Console.WriteLine(label);
foreach (DataRow row in table.Rows)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(“\t ” + row[col].ToString());
}
Console.WriteLine();
}
}

我是这样使用的，请看：

void bind()
{
BLL.Ts_Planning bll = new BLL.Ts_Planning();
Capsugel.BLL.Users bll2 = new BLL.Users();
DataTable dt = bll2.GetTsList().Tables[0];
DataSet ds = new DataSet();
DataTable dt2;
dt2 = bll.GetListByUid_All_Planning_Count(dt.Rows[0]["user id"].ToString(), TextBox2.Text.ToString(), TextBox3.Text.ToString()).Tables[0];
for (int i = 1; i < dt.Rows.Count – 1; i++)
{
dt2.Merge(bll.GetListByUid_All_Planning_Count(dt.Rows[i]["user id"].ToString(), TextBox2.Text.ToString(), TextBox3.Text.ToString()).Tables[0]);
}
GridView1.DataSource = dt2;
GridView1.DataBind();
}

下面是存储过程，有没有高人来指点一下，虽然问题已解决，谢谢DataTable的Merge :-)，但是好像很烦：

ALTER PROCEDURE [dbo].[Ts_Planning_Count]
@Uid varchar(20),
@date1 smalldatetime,
@date2 smalldatetime

AS

Select U.Name,AAAAA.* From
[Users] U,
(Select * From
(Select count(Pid) as [Explor/Penetra] from Ts_Planning
where
Purpose = ‘Explor/Penetra’ and UID=@Uid and (Date between @date1 and @date2)) A,

(Select count(Pid) as [Relation buliding] from Ts_Planning
where
Purpose = ‘Relation buliding’ and UID=@Uid and (Date between @date1 and @date2)) B,

(Select count(Pid) as [Prevent service] from Ts_Planning
where
Purpose = ‘Prevent service’ and UID=@Uid and (Date between @date1 and @date2)) C,

(Select count(Pid) as [Complaint handing] from Ts_Planning
where
Purpose = ‘Complaint handing’ and UID=@Uid and (Date between @date1 and @date2)) D,

(Select count(Pid) as [Reactive service] from Ts_Planning
where
Purpose = ‘Reactive service’ and UID=@Uid and (Date between @date1 and @date2)) E,

(Select count(Pid) as [Trial run] from Ts_Planning
where
Purpose = ‘Trial run’ and UID=@Uid and (Date between @date1 and @date2)) F,

(Select count(Pid) as [Key] from Ts_Planning
where
Class = ‘Key’ and UID=@Uid and (Date between @date1 and @date2)) AA,

(Select count(Pid) as [Core] from Ts_Planning
where
Class = ‘Core’ and UID=@Uid and (Date between @date1 and @date2)) BB,

(Select count(Pid) as [Target] from Ts_Planning
where
Class = ‘Target’ and UID=@Uid and (Date between @date1 and @date2)) CC,

(Select count(Pid) as [Potential] from Ts_Planning
where
Class = ‘Potential’ and UID=@Uid and (Date between @date1 and @date2)) DD,

(Select count(Pid) as [WM] from Ts_Planning
where
Segment = ‘WM’ and UID=@Uid and (Date between @date1 and @date2)) AAA,

(Select count(Pid) as [TCM] from Ts_Planning
where
Segment = ‘TCM’ and UID=@Uid and (Date between @date1 and @date2)) BBB,

(Select count(Pid) as [DS] from Ts_Planning
where
Segment = ‘DS’ and UID=@Uid and (Date between @date1 and @date2)) CCC,

(Select count(Pid) as Visit from Ts_Planning
where
Activity = ‘Visit’ and UID=@Uid and (Date between @date1 and @date2)) AAAA,

(Select count(Pid) as Travel from Ts_Planning
where
Activity = ‘Travel’ and UID=@Uid and (Date between @date1 and @date2)) BBBB,

(Select count(Pid) as [O/HO] from Ts_Planning
where
Activity = ‘O/HO’ and UID=@Uid and (Date between @date1 and @date2)) CCCC,

(Select count(Pid) as [Meeting/Training] from Ts_Planning
where
Activity = ‘Meeting/Training’ and UID=@Uid and (Date between @date1 and @date2)) DDDD,

(Select count(Pid) as [CFM&Expo] from Ts_Planning
where
Activity = ‘CFM&Expo’ and UID=@Uid and (Date between @date1 and @date2)) EEEE,

(Select count(Pid) as [H&V] from Ts_Planning
where
Activity = ‘H&V’ and UID=@Uid and (Date between @date1 and @date2)) FFFF,

(Select SUM(ActivityTimes) as [ActivityTimes] from Ts_Planning
where
UID=@Uid and (Date between @date1 and @date2)) AAAAA
) AAAAA
where U.Uid = @Uid

这是我最近在做一个项目，我尽量全部手写代码。现在将CS文件贴分来分享一下，作一个备忘吧！

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

namespace Capsugel.Web
{
public partial class Customers : System.Web.UI.Page
{
//使用DataView,排序的时候使用到
DataView dv = new DataView();

protected void Page_Load(object sender, EventArgs e)
{
//第一次加载页面
if (!IsPostBack)
{
bind();
}
}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
//测试 Ajax
//System.Threading.Thread.Sleep(1000);
GridView1.PageIndex = e.NewPageIndex;
//重新绑定一次
bind();
}

///
/// 绑定GridView
///
void bind()
{
Capsugel.BLL.Customer bll = new BLL.Customer();
//根据TextBox中的关键词，显示数据
DataTable dt = bll.GetList_ByName(TextBox1.Text.Trim()).Tables[0];
if (ViewState["sortExpr"] != null)
{
dv = new DataView(dt);
dv.Sort = (string)ViewState["sortExpr"] + ” ” + ViewState["sortingOrder"].ToString();
}
else
{
dv = dt.DefaultView;
}
GridView1.DataSource = dv;
GridView1.DataBind();
}

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
int idx = 0;
string Uid = “”;
BLL.Customer bll = new BLL.Customer();

if (e.CommandName == “myedit”)
{
//得到当前行的索引号，确定要将哪一行开启编辑状态，
//如果是第二行的第一个，那应该是 0，所以才有了下面的公式
idx = Convert.ToInt32(e.CommandArgument)-GridView1.PageIndex * GridView1.PageSize;
GridView1.EditIndex = idx;
}
else if (e.CommandName == “myupdate”)
{
idx = Convert.ToInt32(e.CommandArgument) – GridView1.PageIndex * GridView1.PageSize;
Uid = GridView1.DataKeys[idx].Values[0].ToString();
//得到编辑框中的值
string uname = ((TextBox)GridView1.Rows[idx].FindControl(“TextBox1″)).Text;
string custid = ((TextBox)GridView1.Rows[idx].FindControl(“TextBox2″)).Text;
string userid = ((TextBox)GridView1.Rows[idx].FindControl(“TextBox3″)).Text;
bool check = ((CheckBox)GridView1.Rows[idx].FindControl(“CheckBox1″)).Checked;
//对像模型
Model.Customer m = new Model.Customer();
m.CID = Convert.ToInt32(Uid);
m.Checked = check;
m.Name = uname;
m.CustID = custid;
m.Uid = userid;
//更新
bll.Update(m);
//取消编辑状态
GridView1.EditIndex = -1;
}
else if (e.CommandName == “mydelete”)
{
idx = Convert.ToInt32(e.CommandArgument) – GridView1.PageIndex * GridView1.PageSize;
Uid = GridView1.DataKeys[idx].Values[0].ToString();
bll.Delete(Convert.ToInt32(Uid));
}
else if (e.CommandName == “mycancel”)
{
GridView1.EditIndex = -1;
}
//最后都要进行重新绑定一次
bind();
}

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
//判断是否是DataRow，以防止鼠标经过Header也有效果
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Attributes.Add(“onmouseover”, “e=this.style.backgroundColor;this.style.backgroundColor=’#BDF9AC’;”);
e.Row.Attributes.Add(“onmouseout”, “this.style.backgroundColor=e;”);
}
}

protected void Button3_Click(object sender, EventArgs e)
{
//POSTBACK
bind();
}

///
/// 处理排序的问题，定义的排序
///
///
///
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
//点一次时是desc,第二次是asc
if (ViewState["sortingOrder"] == null)
ViewState["sortingOrder"] = “desc”;
else if (Convert.ToString(ViewState["sortingOrder"]) == “asc”)
ViewState["sortingOrder"] = “desc”;
else if (Convert.ToString(ViewState["sortingOrder"]) == “desc”)
ViewState["sortingOrder"] = “asc”;

ViewState["sortExpr"] = e.SortExpression;
bind();
}

///
/// 显示所有的，只要清空一下TextBox中的值
///
///
///
protected void Button4_Click(object sender, EventArgs e)
{
TextBox1.Text = “”;
bind();
}
}
}