asp.net、mvc、ajax、js、jquery、sql、EF、linq、netadvantage第三方控件知识点笔记

很简单，如下：

父页面:（弹出提示框）

function newwindow(obj)

{

var rtn = window.showModalDialog('NewPage.htm','','status=no,scrollbars=no,top=20,left=110,width=420,height=165');

var div = document.getElementById("div1");

div.innerHTML=rtn;

}

子页面：

function pageinit()

{

var lab = document.getElementById("infoimage");

window.returnValue=lab.value;

window.close();

}

---------------------------父窗体

function Show()

{

var bb=2;

var a=window.showModalDialog("弹出2.aspx","","dialogwidth=500px;dialogheight=500px;");

document.getElementById("Text1").value=a;

}

-----------------------------子窗体

function Button1_onclick() {

var a=document.getElementById("TextBox1");

window.returnValue=a.value;

window.close();

------------------window.dialogArguments

}

-------------------------------------------------后台调用前台js函数；

ClientScript.RegisterStartupScript(ClientScript.GetType(), null, "<script>guo('fffff');</script>");

--------------------------sd可以使name,或者是Id；

document.all("sd").style["height"]=h;//设置样式,js控制样式

-------------------------调节透明度

#guo {

filter:alpha(opacity=50);

}

</style>

--------------------------------调用客户端事件：

ScriptManager.RegisterStartupScript(UltraWebGrid1, typeof(UltraWebGrid), "null", "<script>window.parent.gb('T');</script>", false);

---------------调用frame框架外的函数：

------------------iframe获取父页面的元素

window.parent.document.getElementById("Text1").value="hahhahhaha";

父亲获取儿子

JS版：document.getElementById("IframeID").contentWindow.document.getElementById("divID")

--------------------------------js星期：

var day=["日","一","二","三","四","五","六",];

document.write("星期"+day[new Date().getDay()]);

-----------------------------------

Infragistics.WebUI.UltraWebListbar.UltraWebListbar bar = new Infragistics.WebUI.UltraWebListbar.UltraWebListbar();

bar.ID = "menu";

bar.SkinID = "MenuBar";

bar.ApplyStyleSheetSkin(Page);

//bar.Width=

//bar.Height = "100%";

bar.Groups.Clear();

YongHuModel userModel= Session["LoginUser"] as YongHuModel;

int roleId = userModel.RoleId.Id;

IList<RoleLines> roleLines = RoleLinesManager.GetSYSMenu(roleId);

foreach (RoleLines roleItem in roleLines)

{

if (roleItem.LinkId.ParentId == 0)

{

//Infragistics.WebUI.UltraWebNavigator.Node root = new Node();

//root.Text = roleItem.LinkId.Name;

//root.DataKey = roleItem.LinkId.Id;

//root.Expanded = true;

//root.ImageUrl = "~/XtGlMk/images/base.gif";

TreeNode root = new TreeNode();

root.Text = roleItem.LinkId.Name;

root.Value = roleItem.LinkId.Id.ToString();

root.Expanded = true;

root.NavigateUrl = "";

Infragistics.WebUI.UltraWebListbar.Group group = new Infragistics.WebUI.UltraWebListbar.Group(root.Text);

MyTemplate tem = new MyTemplate();

tem.Lines = roleLines;

tem.Root = root;

group.Template = tem;

bar.Groups.Add(group);

}

bar.DataBind();

Panel1.Controls.Add(bar);

----------------------------------------------------treeNode节点添加：

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Collections.Generic;

using JC.BLL;

using JC.Model;

/// <summary>

/// MyTemplate 的摘要说明

/// </summary>

public class MyTemplate : ITemplate

{

private IList<RoleLines> lines;

public IList<RoleLines> Lines

{

set { lines = value; }

}

private TreeNode root;

public TreeNode Root

{

set { root = value; }

}

private System.Web.UI.Page skin;

public System.Web.UI.Page Skin

{

set { skin = value; }

}

public void InstantiateIn(Control container)

{

TreeView tree = new TreeView();

tree.Target = "I3";

//tree.Font.Size = FontUnit.Point(9);

//tree.ForeColor = System.Drawing.Color.Black;

//tree.ImageSet = TreeViewImageSet.BulletedList;

tree.NoExpandImageUrl = "../images/left.gif";

//tree.Font.Size = FontUnit.Point(9);

tree.CssClass = "STYLE3";

tree.Nodes.Add(root);

FillTemp(root, Convert.ToInt32(root.Value));

//test

//TreeNode cNode = new TreeNode();

//cNode.Text = "租赁";

//cNode.NavigateUrl = "~/XmGlMk/TenancyManager/Tenancy.aspx";

//cNode.Target = "I3";

//TreeNode gNode = new TreeNode();

//gNode.Text = "还租";

//gNode.NavigateUrl = "~/XmGlMk/TenancyManager/GiveBackManager.aspx";

//gNode.Target = "I3";

//tree.Nodes.Add(cNode);

//tree.Nodes.Add(gNode);

tree.DataBind();

container.Controls.Add(tree);

}

/// <summary>

/// 使用递归算法添加节点

/// </summary>

/// <param name="root"></param>

/// <param name="parentNodeId"></param>

private void FillTemp(TreeNode root, int parentNodeId)

{

foreach (RoleLines entity in lines)

{

if (entity.LinkId.ParentId == parentNodeId)

{

TreeNode node = new TreeNode();

node.Target = "I3";

node.Text = entity.LinkId.Name;

node.NavigateUrl = entity.LinkId.Url;

node.Value = entity.LinkId.Id.ToString();

string xtbh = HttpContext.Current.Session["XmBH"] as string;

if (xtbh != null)

{

int bh = Convert.ToInt32(xtbh);

XiangMuModel xm = XiangMuManager.getXiangMuByBH(bh);

if (!xm.JiJiaB)

{

if (entity.LinkId.Id == 30107)

{

node.NavigateUrl = "~/XmGlMk/YiDongJiJia.aspx";

}

DJConfiguration config = Commond.GetDjConfiguration(DJTYPE.RKD);

if (!config.GFSL)

{

if (node.Value == "30201" || node.Value == "30202" || node.Value == "30203" || node.Value == "30204")

{

continue;

}

FillTemp(node, entity.LinkId.Id);

root.ChildNodes.Add(node);

}

---------------------------------------listbar:

Infragistics.WebUI.UltraWebListbar.Group group = new Infragistics.WebUI.UltraWebListbar.Group("aa");

Infragistics.WebUI.UltraWebListbar.Item ite1 = new Infragistics.WebUI.UltraWebListbar.Item();

ite1.Text = "aaaa";

group.Items.Add(ite1);

this.UltraWebListbar1.Groups.Add(group);

----------------------------------------

ScriptManager.RegisterStartupScript(UpdatePanel1, typeof(UpdatePanel), "Button2",

"<script>alert('请选择要删除的行！');</script>", false);

------------------------------------------scriptmanage中使用控件netadvantage能够使用；webconfig文件

</httpHandlers>

---------------------------

<p style="padding-top:5px;">欢迎您</p> 若换成<span>就不好用了；

------------------------viewstate类似于static

-------------------------分页：

string sql2 = "select top " + page.PageSize + " * from GongSi where BH not in(select top " + page.Start + " BH from GongSi)";

---------------------------gridview中添加控件

<igtbl:TemplatedColumn Width="200px">

操作

</HeaderTemplate>

<asp:LinkButton ID="LinkButton1" runat="server" OnClick="LinkButton1_Click" Width="80px">下属用户</asp:LinkButton>

</CellTemplate>

</Header>

</Footer>

</igtbl:TemplatedColumn>

---------------------------------------------------------------------------------获取gridview的表格值，cell值：

Session["Id"] = UltraWebGrid1.DisplayLayout.SelectedCells[0].Row.Cells[0];

Session["Id"] = UltraWebGrid1.DisplayLayout.SelectedRows[0].Cells[0].ToString();

案例：

if (UltraWebGrid2.DisplayLayout.SelectedCells[0] != null)

{

if (UltraWebGrid2.DisplayLayout.SelectedCells[0].Row != null)

{

int id = Convert.ToInt32(UltraWebGrid2.DisplayLayout.SelectedCells[0].Row.Cells[0].Text);

Session["XgGsID"] = id;

Response.Redirect("XgGongSi.aspx");

}

else

{

ScriptManager.RegisterStartupScript(UpdatePanel1, typeof(UpdatePanel), "Button2",

"<script>alert('请选择要修改的行！');</script>", false);

}

else

{

if (UltraWebGrid2.DisplayLayout.SelectedRows[0] != null)

{

int id = Convert.ToInt32(UltraWebGrid2.DisplayLayout.SelectedRows[0].Cells[0].Text);

Session["XgGsID"] = id;

Response.Redirect("XgGongSi.aspx");

}

else

{

ScriptManager.RegisterStartupScript(UpdatePanel1, typeof(UpdatePanel), "Button2",

"<script>alert('请选择要修改的行！');</script>", false);

}

-------------------------------------------------------------------------------------------

----------------listbox添加项：

foreach (YongHuModel model in yhgl_models)

{

ListItem li = new ListItem();

li.Text = model.MC.ToString();

li.Value = model.BH.ToString();

cblJZXM.Items.Add(li);

}

------------------------------------------------listbox:添加移除列；

cblJZXM.Items.Add(li);

//移除

cblXM.Items.RemoveAt(i);

------------------------------------------------弹出框案例：

父页面案例：弹出提示框

function Button1_onclick() {

window.showModalDialog("子.aspx",this,"dialogHeight:450px;dialogWidth:800px;status=no");

}

function show(str)

{

document.getElementById("Text1").value=str;

}

</script>

//子页面案例：后台---

ScriptManager.RegisterStartupScript(UpdatePanel1, typeof(UpdatePanel), "null",

"<script>window.dialogArguments.addgs('" + GYSBH + "','" + MC + "');window.close();</script>", false);

必须在head内添加：

-----------------------------------后台获取前台session值：

string dataBaseName=System.Web.HttpContext.Current.Session["XmName"].ToString();

//--------------------------------------查询某数据库某个表：

SELECT * FROM CC ..AA

------------------------------------------

cmd.Parameters.AddRange(values);//循环添加sqlparameters

-----------------------------------------------------------------

using(SqlDataReader sdr=DBHelper.GetReader(sql1))，使用using的前提是对象类必须是实现：Idispose接口；

{

if (sdr.Read())

{

return 0;

}

--------------------------------------------------------------------------------------------------前台：

function IfGoDel(bh)

{

if(confirm("是否同时删除合格供应商名册与供应商复核里的数据？"))

{

document.getElementById("hidCommond").value="allDel";

document.getElementById("hidXtBH").value=bh;

}else

{

document.getElementById("hidCommond").value="Only";

document.getElementById("hidXtBH").value=bh;

}

// __doPostBack("UpdatePanel1","");

document.form1.submit();

}

--------后台:

string commond = Request.Form["hidCommon"];

if (commond == "Search")

{

Bind(1);

}

string commd = Request.Form["hidCommond"];

string xtbh = Request.Form["hidXtBH"];

if (commd == "allDel")

{

int key = Convert.ToInt32(xtbh);

bool bb = GysFpManager.dropGysFPByBH(key);

if (bb)

{

ScriptManager.RegisterStartupScript(UpdatePanel1, typeof(UpdatePanel), "Button2",

"<script>alert('删除成功！');</script>", false);

Bind(1);

}

else

{

ScriptManager.RegisterStartupScript(UpdatePanel1, typeof(UpdatePanel), "Button2",

"<script>alert('删除失败！');</script>", false);

}

if (commd == "Only")

{

return;

}

-----------------------------------数据库分页；

List<GysModel> GysS = new List<GysModel>();

List<SqlParameter> pars = new List<SqlParameter>();

string searchSql = CreatSql(searchModel, pars);

string selectCount = "select count(*) from Gys where (FHJG=1 or FHJG is null ) " + searchSql;

page.TotalRow = Convert.ToInt32(DBHelper.GetScalar(selectCount, pars.ToArray()));

StringBuilder sql = new StringBuilder("select * from (select row_number() OVER (ORDER BY BH desc) AS rowId,* from Gys where (FHJG=1 or FHJG is null )");

sql.Append(searchSql).Append(")as Temp where rowId between ").Append(page.Begin).Append(" and ").Append(page.End);

using (SqlDataReader dr = DBHelper.GetReader(sql.ToString(), pars.ToArray()))

{

while (dr.Read())

----------------------------------------添加事务：

begin transaction

insert into A(c1,c2) values(v1,v2);

insert into B(c3,c4) values(v3,v4);

insert into C(c5,c6) values(v5,v6);

if @@ERROR = 0

begin

commit transaction;

end

else

begin

rollback transaction;

end

---------------------------这条语句是正确的

cn = new SqlConnection("data source=DELL-PC\AA;uid=sa;pwd=sa");

cn.Open();

StringBuilder sb = new StringBuilder(" use CC update [User] set UName='666' where UId="+i+"");

-----------------------------这条语句也是正确的；

cn = new SqlConnection("data source=DELL-PC\AA;initial catalog=CC; uid=sa;pwd=sa");

cn.Open();

StringBuilder sb = new StringBuilder(" use CD update [User] set UName='666' where UId="+i+"");

---------------------------------同时对两个数据库进行操作

try

{

int i = 1;

cn = new SqlConnection("data source=DELL-PC\AA;initial catalog=CC; uid=sa;pwd=sa");

cn.Open();

StringBuilder sb = new StringBuilder("begin transaction ");

sb.Append("use CC update [User] set UName='xiao' where UId=1");

sb.Append("use CD update [User] set UName='xiao' where UId=1");

sb.Append("if @@ERROR = 0 begin commit transaction end else begin rollback transaction end");

SqlCommand cmd = new SqlCommand(sb.ToString(), cn);

cmd.ExecuteNonQuery();

}

------------------------------

cmd.Parameters.AddRange(values);

--------------------------------------

List<GysFhModel> GysFhS = new List<GysFhModel>();

List<SqlParameter> pars = new List<SqlParameter>();

string searchSql = CreatSql(searchModel, pars);

string selectCount = "select count(*) from GYSFH left join GYS on GYSFH.GYSBH=GYS.BH where 1=1 " + searchSql;

page.TotalRow = Convert.ToInt32(DBHelper.GetScalar(selectCount, pars.ToArray()));

StringBuilder sql = new StringBuilder("select * from (select row_number() OVER (ORDER BY GYSFH.BH desc) AS rowId,GYSFH.* ,GYS.MC from GYSFH left join GYS on GYSFH.GYSBH=GYS.BH where 1=1 ");

sql.Append(searchSql).Append(")as Temp where rowId between ").Append(page.Begin).Append(" and ").Append(page.End);

------------------------javascript:后台提交

document.getElementById("HiddenField1").value="1233434";

var b=document.getElementById("HiddenField2");

window.form1.submit();

接收 if (IsPostBack)

{

TextBox1.Text = HiddenField1.Value.ToString();

}

------------------------将数据库备份到指定位置；

backup database cc to disk='D:d3.bak'

-----------------------将备份还原到某个位置并附加数据库：格式不能变化；

restore database SJCL33 from disk='D:Company通用版JC.WebFileBakSourceTSSJCL.bak' with FILE = 1, move 'SJCL' to 'C:UsersdellDesktopsSJCL33.mdf',move 'SJCL_Log' to 'C:UsersdellDesktopsSJCL33.LDF',NOUNLOAD, REPLACE, STATS = 10

---------------------------

document.write("浏览器名称: " + navigator.appName + "<br>");

document.write("版本号: " + navigator.appVersion + "<br>");

document.write("代码名字: " + navigator.appCodeName + "<br>");

document.write("用户代理标识: " + navigator.userAgent);

---------------------------bit类型数据存储

插入时可以存：

INSERT INTO Users(Sex) VALUES('TRUE');

0为false,1为true;

或者直接插入0或1；插入时除了整形不带单引号其余都带；

显示时也为True或false;

-----------------------------存储过程整形作为条件

ALTER PROCEDURE dbo.selectuser

(

@UId int,

@result nvarchar(20) output

)

declare @s nvarchar(100)

set @s=@UId

declare @str nvarchar(1000)

set @str='select @result=UName from [User] where UId='''+@s+''''//一个或是三个单引号都行；若是整形，先赋值给字符型就行了

EXEC sp_executesql @str, N'@result nvarchar(20) OUTPUT', @result OUTPUT

--------------------------------数据库分页存储过程

"select UName from [User] where UName like '%"+ss+"%'"

ALTER PROCEDURE dbo.SelectPages

(

@name int,

@pageSize INT, --每页显示的索引号

@pageIndex INT, --每页显示的记录数

@count INT OUTPUT --总记录数

)

begin

declare @na nvarchar(20)

set @na=@name

declare @sqlstrc nvarchar(2000)

declare @sqlstrd nvarchar(2000)

set @sqlstrc='select @count=count(*) from Fen where Name>'''+@na+''''

exec sp_executesql @sqlstrc, N'@count int output',@count output

set @sqlstrd='select Name from (select Name,ROW_NUMBER() OVER (ORDER BY Id) AS SerialNumber from Fen where Name>'''+@na+''') as t'

set @sqlstrd=@sqlstrd+' WHERE t.SerialNumber > ' + CONVERT(NVARCHAR(100), (@pageIndex - 1) * @pageSize) +

' AND t.SerialNumber <= ' + CONVERT(NVARCHAR(100), @pageIndex * @PageSize)

exec (@sqlstrd)

end

----------------------------------数据库备份，断开连接知识

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using JC.Model;

namespace JC.DAL

{

public class DataBaseService

{

private const string SQL_SELECT_DATABASE_INFO = "select name,filename,fileid from sysfiles ORDER BY fileid";

private const string SQL_SELECT_SYSPROCESS = "select * from master..sysprocesses where dbid=db_id(@AimDB)";

private const string SQL_KILL_PROCESS_PRO = "killspid";

private const string SQL_RESTORE = "restore database {0} from disk=@SourceDbBakPath with FILE = 1, move @DBName to @DBPath,move @DBLogName to @DBLogPath,NOUNLOAD, REPLACE, STATS = 10";

private const string SQL_EXITS_PROC = "select * from sysobjects where id = object_id(@ProName) and OBJECTPROPERTY(id, N'IsProcedure') = 1";

/// <summary>

/// 获取mdf文件的基本信息

/// </summary>

/// <returns></returns>

public static List<DataBaseModel> GetDataBaseInfo()

{

List<DataBaseModel> list = new List<DataBaseModel>();

using (SqlDataReader reader = DBHelper.GetReader(SQL_SELECT_DATABASE_INFO))

{

while(reader.Read())

{

DataBaseModel model = new DataBaseModel();

model.Fileid = Convert.ToInt32(reader["fileid"]);

model.DataBaseName = reader["name"].ToString();

model.DataBasePath = reader["filename"].ToString();

list.Add(model);

}

reader.Close();

}

return list;

}

/// <summary>

/// 判断存储过程是否存在于master数据库

/// </summary>

/// <param name="proName">存储过程名称</param>

/// <returns></returns>

public static bool ExitsPro(string proName)

{

bool result = false;

using (SqlDataReader reader = DBHelper.GetReaderByConMaster(CommandType.Text, SQL_EXITS_PROC, new SqlParameter("@ProName", proName)))

{

if (reader.Read())

{

result = true;

}

reader.Close();

}

return result;

}

/// <summary>

/// 判断指定数据库是否有连接进程

/// </summary>

/// <param name="aimDbBase">数据库名称</param>

/// <returns></returns>

public static bool ExitsProcess(string aimDbBase)

{

bool result = false;

using (SqlDataReader reader = DBHelper.GetReader(SQL_SELECT_SYSPROCESS, new SqlParameter("@AimDB", aimDbBase)))

{

if (reader.Read())

{

result = true;

}

reader.Close();

}

return result;

}

/// <summary>

/// 判断某数据库是否有访问进程，有就关闭所有相关进程

/// </summary>

/// <param name="aimDbBase">数据库名称</param>

/// <returns></returns>

public static int ExitsKillProcess(string aimDbBase)

{

return DBHelper.ExecuteNonQueryByMaster(CommandType.StoredProcedure, SQL_KILL_PROCESS_PRO, new SqlParameter("@dbname", aimDbBase));

}

/// <summary>

/// 数据库备份

/// </summary>

/// <param name="sourceDbName">源数据库名称</param>

/// <param name="aimDbPath">备份文件目标路径</param>

/// <returns></returns>

public static int DbBackUp(string sourceDbName, string aimDbPath)

{

string sql = "BACKUP DATABASE " + sourceDbName + " TO DISK=@AimDbPath";

return DBHelper.ExecuteNonQueryByMaster(CommandType.Text, sql, new SqlParameter("@AimDbPath",aimDbPath));

}

///// <summary>

///// 数据库还原

///// </summary>

///// <param name="aimDbName">目标数据库名称</param>

///// <param name="sourceDbBakPath">数据库备份文件</param>

///// <returns></returns>

//public static int DbRestore(string aimDbName, string sourceDbBakPath)

//{

// string sql = "killspid @AimDbName restore database " + aimDbName + " from disk=@SourceDbBakPath with replace";

// SqlParameter [] pars=new SqlParameter[]{

// new SqlParameter("@AimDbName",aimDbName),

// new SqlParameter("@SourceDbBakPath",sourceDbBakPath)

// };

// return DBHelper.ExecuteNonQueryByMaster(CommandType.Text, sql, pars);

//}

/// <summary>

/// 数据库还原

/// </summary>

/// <param name="aimDbName">目标数据库名称</param>

/// <param name="sourceDbBakPath">备份文件物理路径</param>

/// <param name="DBName">备份文件数据库文件的逻辑名称</param>

/// <param name="DBPath">目标数据库文件的物理路径</param>

/// <param name="DBLogName">备份文件记录的源数据库的数据库日志文件逻辑名</param>

/// <param name="DBLogPath">目标数据库日志文件的物理路径</param>

/// <returns></returns>

public static int DbRestore(string aimDbName, string sourceDbBakPath,string DBName,string DBPath,string DBLogName,string DBLogPath)

{

string sql = string.Format(SQL_RESTORE, aimDbName);

SqlParameter[] pars = new SqlParameter[] {

new SqlParameter("@SourceDbBakPath",sourceDbBakPath),

new SqlParameter("@DBName",DBName),

new SqlParameter("@DBPath",DBPath),

new SqlParameter("@DBLogName",DBLogName),

new SqlParameter("@DBLogPath",DBLogPath)

};

DBHelper.ExecuteNonQueryByMaster(CommandType.Text, sql, pars);

return Convert.ToInt32(DBHelper.TestCommond("use " + aimDbName + " SELECT count(*) FROM sysobjects WHERE xtype = 'U'", null));

}

/// <summary>

/// 创建断开连接进程的存储过程

/// </summary>

/// <returns></returns>

public static int CreatKildPro()

{

string kill = " CREATE proc killspid (@dbname varchar(20))";

kill += " as ";

kill += " begin ";

kill += " declare @sql nvarchar(500) ";

kill += " declare @spid int ";

kill += " set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')'";

kill += " exec (@sql) ";

kill += " open getspid ";

kill += " fetch next from getspid into @spid ";

kill += " while @@fetch_status <> -1 ";

kill += " begin";

kill += " exec('kill '+@spid) ";

kill += " fetch next from getspid into @spid ";

kill += " end ";

kill += " close getspid ";

kill += " deallocate getspid ";

kill += " end";

return DBHelper.ExecuteNonQueryByMaster(CommandType.Text, kill, null);

}

---------------

using System.Configuration; string _connectionString = ConfigurationManager.AppSettings["ConnectionString"];

--------------------------------------------动软加密解密

using System;

using System.Security.Cryptography;

using System.Text;

namespace Maticsoft.DBUtility

{

/// <summary>

/// DES加密/解密类。

/// </summary>

public class DESEncrypt

{

public DESEncrypt()

{

}

#region ========加密========

/// <summary>

/// 加密

/// </summary>

/// <param name="Text"></param>

/// <returns></returns>

public static string Encrypt(string Text)

{

return Encrypt(Text, "litianping");

}

/// <summary>

/// 加密数据

/// </summary>

/// <param name="Text"></param>

/// <param name="sKey"></param>

/// <returns></returns>

public static string Encrypt(string Text,string sKey)

{

DESCryptoServiceProvider des = new DESCryptoServiceProvider();

byte[] inputByteArray;

inputByteArray=Encoding.Default.GetBytes(Text);

des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));

des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));

System.IO.MemoryStream ms=new System.IO.MemoryStream();

CryptoStream cs=new CryptoStream(ms,des.CreateEncryptor(),CryptoStreamMode.Write);

cs.Write(inputByteArray,0,inputByteArray.Length);

cs.FlushFinalBlock();

StringBuilder ret=new StringBuilder();

foreach( byte b in ms.ToArray())

{

ret.AppendFormat("{0:X2}",b);

}

return ret.ToString();

}

#endregion

#region ========解密========

/// <summary>

/// 解密

/// </summary>

/// <param name="Text"></param>

/// <returns></returns>

public static string Decrypt(string Text)

{

return Decrypt(Text, "litianping");

}

/// <summary>

/// 解密数据

/// </summary>

/// <param name="Text"></param>

/// <param name="sKey"></param>

/// <returns></returns>

public static string Decrypt(string Text,string sKey)

{

DESCryptoServiceProvider des = new DESCryptoServiceProvider();

int len;

len=Text.Length/2;

byte[] inputByteArray = new byte[len];

int x,i;

for(x=0;x<len;x++)

{

i = Convert.ToInt32(Text.Substring(x * 2, 2), 16);

inputByteArray[x]=(byte)i;

}

des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));

des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));

System.IO.MemoryStream ms=new System.IO.MemoryStream();

CryptoStream cs=new CryptoStream(ms,des.CreateDecryptor(),CryptoStreamMode.Write);

cs.Write(inputByteArray,0,inputByteArray.Length);

cs.FlushFinalBlock();

return Encoding.Default.GetString(ms.ToArray());

}

#endregion

}

-----------------------

using System;

using System.Collections;

using System.Collections.Specialized;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Data.Common;

using System.Collections.Generic;

namespace Maticsoft.DBUtility

{

/// <summary>

/// 数据访问抽象基础类

/// Copyright (C) Maticsoft

/// </summary>

public abstract class DbHelperSQL

{

//数据库连接字符串(web.config来配置)，多数据库可使用DbHelperSQLP来实现.

public static string connectionString = PubConstant.ConnectionString;

public DbHelperSQL()

{

}

#region 公用方法

/// <summary>

/// 判断是否存在某表的某个字段

/// </summary>

/// <param name="tableName">表名称</param>

/// <param name="columnName">列名称</param>

/// <returns>是否存在</returns>

public static bool ColumnExists(string tableName, string columnName)

{

string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";

object res = GetSingle(sql);

if (res == null)

{

return false;

}

return Convert.ToInt32(res) > 0;

}

public static int GetMaxID(string FieldName, string TableName)

{

string strsql = "select max(" + FieldName + ")+1 from " + TableName;

object obj = GetSingle(strsql);

if (obj == null)

{

return 1;

}

else

{

return int.Parse(obj.ToString());

}

public static bool Exists(string strSql)

{

object obj = GetSingle(strSql);

int cmdresult;

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

cmdresult = 0;

}

else

{

cmdresult = int.Parse(obj.ToString());

}

if (cmdresult == 0)

{

return false;

}

else

{

return true;

}

/// <summary>

/// 表是否存在

/// </summary>

/// <param name="TableName"></param>

/// <returns></returns>

public static bool TabExists(string TableName)

{

string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";

//string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";

object obj = GetSingle(strsql);

int cmdresult;

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

cmdresult = 0;

}

else

{

cmdresult = int.Parse(obj.ToString());

}

if (cmdresult == 0)

{

return false;

}

else

{

return true;

}

public static bool Exists(string strSql, params SqlParameter[] cmdParms)

{

object obj = GetSingle(strSql, cmdParms);

int cmdresult;

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

cmdresult = 0;

}

else

{

cmdresult = int.Parse(obj.ToString());

}

if (cmdresult == 0)

{

return false;

}

else

{

return true;

}

#endregion

#region 执行简单SQL语句

/// <summary>

/// 执行SQL语句，返回影响的记录数

/// </summary>

/// <param name="SQLString">SQL语句</param>

/// <returns>影响的记录数</returns>

public static int ExecuteSql(string SQLString)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand(SQLString, connection))

{

try

{

connection.Open();

int rows = cmd.ExecuteNonQuery();

return rows;

}

catch (System.Data.SqlClient.SqlException e)

{

connection.Close();

throw e;

}

public static int ExecuteSqlByTime(string SQLString, int Times)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand(SQLString, connection))

{

try

{

connection.Open();

cmd.CommandTimeout = Times;

int rows = cmd.ExecuteNonQuery();

return rows;

}

catch (System.Data.SqlClient.SqlException e)

{

connection.Close();

throw e;

}

/// <summary>

/// 执行Sql和Oracle滴混合事务

/// </summary>

/// <param name="list">SQL命令行列表</param>

/// <param name="oracleCmdSqlList">Oracle命令行列表</param>

/// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>

public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

SqlTransaction tx = conn.BeginTransaction();

cmd.Transaction = tx;

try

{

foreach (CommandInfo myDE in list)

{

string cmdText = myDE.CommandText;

SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;

PrepareCommand(cmd, conn, tx, cmdText, cmdParms);

if (myDE.EffentNextType == EffentNextType.SolicitationEvent)

{

if (myDE.CommandText.ToLower().IndexOf("count(") == -1)

{

tx.Rollback();

throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");

//return 0;

}

object obj = cmd.ExecuteScalar();

bool isHave = false;

if (obj == null && obj == DBNull.Value)

{

isHave = false;

}

isHave = Convert.ToInt32(obj) > 0;

if (isHave)

{

//引发事件

myDE.OnSolicitationEvent();

}

if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)

{

if (myDE.CommandText.ToLower().IndexOf("count(") == -1)

{

tx.Rollback();

throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");

//return 0;

}

object obj = cmd.ExecuteScalar();

bool isHave = false;

if (obj == null && obj == DBNull.Value)

{

isHave = false;

}

isHave = Convert.ToInt32(obj) > 0;

if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)

{

tx.Rollback();

throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");

//return 0;

}

if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)

{

tx.Rollback();

throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");

//return 0;

}

continue;

}

int val = cmd.ExecuteNonQuery();

if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)

{

tx.Rollback();

throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");

//return 0;

}

cmd.Parameters.Clear();

}

string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");

bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);

if (!res)

{

tx.Rollback();

throw new Exception("Oracle执行失败");

// return -1;

}

tx.Commit();

return 1;

}

catch (System.Data.SqlClient.SqlException e)

{

tx.Rollback();

throw e;

}

catch (Exception e)

{

tx.Rollback();

throw e;

}

/// <summary>

/// 执行多条SQL语句，实现数据库事务。

/// </summary>

/// <param name="SQLStringList">多条SQL语句</param>

public static int ExecuteSqlTran(List<String> SQLStringList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

SqlTransaction tx = conn.BeginTransaction();

cmd.Transaction = tx;

try

{

int count = 0;

for (int n = 0; n < SQLStringList.Count; n++)

{

string strsql = SQLStringList[n];

if (strsql.Trim().Length > 1)

{

cmd.CommandText = strsql;

count += cmd.ExecuteNonQuery();

}

tx.Commit();

return count;

}

catch

{

tx.Rollback();

return 0;

}

/// <summary>

/// 执行带一个存储过程参数的的SQL语句。

/// </summary>

/// <param name="SQLString">SQL语句</param>

/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>

/// <returns>影响的记录数</returns>

public static int ExecuteSql(string SQLString, string content)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand(SQLString, connection);

System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);

myParameter.Value = content;

cmd.Parameters.Add(myParameter);

try

{

connection.Open();

int rows = cmd.ExecuteNonQuery();

return rows;

}

catch (System.Data.SqlClient.SqlException e)

{

throw e;

}

finally

{

cmd.Dispose();

connection.Close();

}

/// <summary>

/// 执行带一个存储过程参数的的SQL语句。

/// </summary>

/// <param name="SQLString">SQL语句</param>

/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>

/// <returns>影响的记录数</returns>

public static object ExecuteSqlGet(string SQLString, string content)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand(SQLString, connection);

System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);

myParameter.Value = content;

cmd.Parameters.Add(myParameter);

try

{

connection.Open();

object obj = cmd.ExecuteScalar();

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

return null;

}

else

{

return obj;

}

catch (System.Data.SqlClient.SqlException e)

{

throw e;

}

finally

{

cmd.Dispose();

connection.Close();

}

/// <summary>

/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)

/// </summary>

/// <param name="strSQL">SQL语句</param>

/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>

/// <returns>影响的记录数</returns>

public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand(strSQL, connection);

System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);

myParameter.Value = fs;

cmd.Parameters.Add(myParameter);

try

{

connection.Open();

int rows = cmd.ExecuteNonQuery();

return rows;

}

catch (System.Data.SqlClient.SqlException e)

{

throw e;

}

finally

{

cmd.Dispose();

connection.Close();

}

/// <summary>

/// 执行一条计算查询结果语句，返回查询结果（object）。

/// </summary>

/// <param name="SQLString">计算查询结果语句</param>

/// <returns>查询结果（object）</returns>

public static object GetSingle(string SQLString)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand(SQLString, connection))

{

try

{

connection.Open();

object obj = cmd.ExecuteScalar();

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

return null;

}

else

{

return obj;

}

catch (System.Data.SqlClient.SqlException e)

{

connection.Close();

throw e;

}

public static object GetSingle(string SQLString, int Times)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand(SQLString, connection))

{

try

{

connection.Open();

cmd.CommandTimeout = Times;

object obj = cmd.ExecuteScalar();

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

return null;

}

else

{

return obj;

}

catch (System.Data.SqlClient.SqlException e)

{

connection.Close();

throw e;

}

/// <summary>

/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

/// </summary>

/// <param name="strSQL">查询语句</param>

/// <returns>SqlDataReader</returns>

public static SqlDataReader ExecuteReader(string strSQL)

{

SqlConnection connection = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(strSQL, connection);

try

{

connection.Open();

SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

return myReader;

}

catch (System.Data.SqlClient.SqlException e)

{

throw e;

}

/// <summary>

/// 执行查询语句，返回DataSet

/// </summary>

/// <param name="SQLString">查询语句</param>

/// <returns>DataSet</returns>

public static DataSet Query(string SQLString)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

DataSet ds = new DataSet();

try

{

connection.Open();

SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);

command.Fill(ds, "ds");

}

catch (System.Data.SqlClient.SqlException ex)

{

throw new Exception(ex.Message);

}

return ds;

}

public static DataSet Query(string SQLString, int Times)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

DataSet ds = new DataSet();

try

{

connection.Open();

SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);

command.SelectCommand.CommandTimeout = Times;

command.Fill(ds, "ds");

}

catch (System.Data.SqlClient.SqlException ex)

{

throw new Exception(ex.Message);

}

return ds;

}

#endregion

#region 执行带参数的SQL语句

/// <summary>

/// 执行SQL语句，返回影响的记录数

/// </summary>

/// <param name="SQLString">SQL语句</param>

/// <returns>影响的记录数</returns>

public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand())

{

try

{

PrepareCommand(cmd, connection, null, SQLString, cmdParms);

int rows = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return rows;

}

catch (System.Data.SqlClient.SqlException e)

{

throw e;

}

/// <summary>

/// 执行多条SQL语句，实现数据库事务。

/// </summary>

/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>

public static void ExecuteSqlTran(Hashtable SQLStringList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

using (SqlTransaction trans = conn.BeginTransaction())

{

SqlCommand cmd = new SqlCommand();

try

{

//循环

foreach (DictionaryEntry myDE in SQLStringList)

{

string cmdText = myDE.Key.ToString();

SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;

PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

}

trans.Commit();

}

catch

{

trans.Rollback();

throw;

}

/// <summary>

/// 执行多条SQL语句，实现数据库事务。

/// </summary>

/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>

public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

using (SqlTransaction trans = conn.BeginTransaction())

{

SqlCommand cmd = new SqlCommand();

try

{ int count = 0;

//循环

foreach (CommandInfo myDE in cmdList)

{

string cmdText = myDE.CommandText;

SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;

PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)

{

if (myDE.CommandText.ToLower().IndexOf("count(") == -1)

{

trans.Rollback();

return 0;

}

object obj = cmd.ExecuteScalar();

bool isHave = false;

if (obj == null && obj == DBNull.Value)

{

isHave = false;

}

isHave = Convert.ToInt32(obj) > 0;

if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)

{

trans.Rollback();

return 0;

}

if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)

{

trans.Rollback();

return 0;

}

continue;

}

int val = cmd.ExecuteNonQuery();

count += val;

if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)

{

trans.Rollback();

return 0;

}

cmd.Parameters.Clear();

}

trans.Commit();

return count;

}

catch

{

trans.Rollback();

throw;

}

/// <summary>

/// 执行多条SQL语句，实现数据库事务。

/// </summary>

/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>

public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

using (SqlTransaction trans = conn.BeginTransaction())

{

SqlCommand cmd = new SqlCommand();

try

{

int indentity = 0;

//循环

foreach (CommandInfo myDE in SQLStringList)

{

string cmdText = myDE.CommandText;

SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;

foreach (SqlParameter q in cmdParms)

{

if (q.Direction == ParameterDirection.InputOutput)

{

q.Value = indentity;

}

PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

int val = cmd.ExecuteNonQuery();

foreach (SqlParameter q in cmdParms)

{

if (q.Direction == ParameterDirection.Output)

{

indentity = Convert.ToInt32(q.Value);

}

cmd.Parameters.Clear();

}

trans.Commit();

}

catch

{

trans.Rollback();

throw;

}

/// <summary>

/// 执行多条SQL语句，实现数据库事务。

/// </summary>

/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>

public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

using (SqlTransaction trans = conn.BeginTransaction())

{

SqlCommand cmd = new SqlCommand();

try

{

int indentity = 0;

//循环

foreach (DictionaryEntry myDE in SQLStringList)

{

string cmdText = myDE.Key.ToString();

SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;

foreach (SqlParameter q in cmdParms)

{

if (q.Direction == ParameterDirection.InputOutput)

{

q.Value = indentity;

}

PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

int val = cmd.ExecuteNonQuery();

foreach (SqlParameter q in cmdParms)

{

if (q.Direction == ParameterDirection.Output)

{

indentity = Convert.ToInt32(q.Value);

}

cmd.Parameters.Clear();

}

trans.Commit();

}

catch

{

trans.Rollback();

throw;

}

/// <summary>

/// 执行一条计算查询结果语句，返回查询结果（object）。

/// </summary>

/// <param name="SQLString">计算查询结果语句</param>

/// <returns>查询结果（object）</returns>

public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand())

{

try

{

PrepareCommand(cmd, connection, null, SQLString, cmdParms);

object obj = cmd.ExecuteScalar();

cmd.Parameters.Clear();

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

return null;

}

else

{

return obj;

}

catch (System.Data.SqlClient.SqlException e)

{

throw e;

}

/// <summary>

/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

/// </summary>

/// <param name="strSQL">查询语句</param>

/// <returns>SqlDataReader</returns>

public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)

{

SqlConnection connection = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand();

try

{

PrepareCommand(cmd, connection, null, SQLString, cmdParms);

SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

cmd.Parameters.Clear();

return myReader;

}

catch (System.Data.SqlClient.SqlException e)

{

throw e;

}

// finally

// {

// cmd.Dispose();

// connection.Close();

// }

}

/// <summary>

/// 执行查询语句，返回DataSet

/// </summary>

/// <param name="SQLString">查询语句</param>

/// <returns>DataSet</returns>

public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand();

PrepareCommand(cmd, connection, null, SQLString, cmdParms);

using (SqlDataAdapter da = new SqlDataAdapter(cmd))

{

DataSet ds = new DataSet();

try

{

da.Fill(ds, "ds");

cmd.Parameters.Clear();

}

catch (System.Data.SqlClient.SqlException ex)

{

throw new Exception(ex.Message);

}

return ds;

}

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)

{

if (conn.State != ConnectionState.Open)

conn.Open();

cmd.Connection = conn;

cmd.CommandText = cmdText;

if (trans != null)

cmd.Transaction = trans;

cmd.CommandType = CommandType.Text;//cmdType;

if (cmdParms != null)

{

foreach (SqlParameter parameter in cmdParms)

{

if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

(parameter.Value == null))

{

parameter.Value = DBNull.Value;

}

cmd.Parameters.Add(parameter);

}

#endregion

#region 存储过程操作

/// <summary>

/// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

/// </summary>

/// <param name="storedProcName">存储过程名</param>

/// <param name="parameters">存储过程参数</param>

/// <returns>SqlDataReader</returns>

public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)

{

SqlConnection connection = new SqlConnection(connectionString);

SqlDataReader returnReader;

connection.Open();

SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

command.CommandType = CommandType.StoredProcedure;

returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);

return returnReader;

}

/// <summary>

/// 执行存储过程

/// </summary>

/// <param name="storedProcName">存储过程名</param>

/// <param name="parameters">存储过程参数</param>

/// <param name="tableName">DataSet结果中的表名</param>

/// <returns>DataSet</returns>

public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

DataSet dataSet = new DataSet();

connection.Open();

SqlDataAdapter sqlDA = new SqlDataAdapter();

sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

sqlDA.Fill(dataSet, tableName);

connection.Close();

return dataSet;

}

public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

DataSet dataSet = new DataSet();

connection.Open();

SqlDataAdapter sqlDA = new SqlDataAdapter();

sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

sqlDA.SelectCommand.CommandTimeout = Times;

sqlDA.Fill(dataSet, tableName);

connection.Close();

return dataSet;

}

/// <summary>

/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)

/// </summary>

/// <param name="connection">数据库连接</param>

/// <param name="storedProcName">存储过程名</param>

/// <param name="parameters">存储过程参数</param>

/// <returns>SqlCommand</returns>

private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)

{

SqlCommand command = new SqlCommand(storedProcName, connection);

command.CommandType = CommandType.StoredProcedure;

foreach (SqlParameter parameter in parameters)

{

if (parameter != null)

{

// 检查未分配值的输出参数,将其分配以DBNull.Value.

if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

(parameter.Value == null))

{

parameter.Value = DBNull.Value;

}

command.Parameters.Add(parameter);

}

return command;

}

/// <summary>

/// 执行存储过程，返回影响的行数

/// </summary>

/// <param name="storedProcName">存储过程名</param>

/// <param name="parameters">存储过程参数</param>

/// <param name="rowsAffected">影响的行数</param>

/// <returns></returns>

public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

int result;

connection.Open();

SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);

rowsAffected = command.ExecuteNonQuery();

result = (int)command.Parameters["ReturnValue"].Value;

//Connection.Close();

return result;

}

/// <summary>

/// 创建 SqlCommand 对象实例(用来返回一个整数值)

/// </summary>

/// <param name="storedProcName">存储过程名</param>

/// <param name="parameters">存储过程参数</param>

/// <returns>SqlCommand 对象实例</returns>

private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)

{

SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

command.Parameters.Add(new SqlParameter("ReturnValue",

SqlDbType.Int, 4, ParameterDirection.ReturnValue,

false, 0, 0, string.Empty, DataRowVersion.Default, null));

return command;

}

#endregion

}

-----------------边框；

----------------------------------------------------动软的缓存类

using System;

using System.Web;

namespace Maticsoft.Common

{

/// <summary>

/// 缓存相关的操作类

/// Copyright (C) Maticsoft

/// </summary>

public class DataCache

{

/// <summary>

/// 获取当前应用程序指定CacheKey的Cache值

/// </summary>

/// <param name="CacheKey"></param>

/// <returns></returns>

public static object GetCache(string CacheKey)

{

System.Web.Caching.Cache objCache = HttpRuntime.Cache;

return objCache[CacheKey];

}

/// <summary>

/// 设置当前应用程序指定CacheKey的Cache值

/// </summary>

/// <param name="CacheKey"></param>

/// <param name="objObject"></param>

public static void SetCache(string CacheKey, object objObject)

{

System.Web.Caching.Cache objCache = HttpRuntime.Cache;

objCache.Insert(CacheKey, objObject);

}

/// <summary>

/// 设置当前应用程序指定CacheKey的Cache值

/// </summary>

/// <param name="CacheKey"></param>

/// <param name="objObject"></param>

public static void SetCache(string CacheKey, object objObject, DateTime absoluteExpiration,TimeSpan slidingExpiration )

{

System.Web.Caching.Cache objCache = HttpRuntime.Cache;

objCache.Insert(CacheKey, objObject,null,absoluteExpiration,slidingExpiration);

}

-----------------------------------删除最后一个逗号

return str.Substring(0, str.LastIndexOf(","));

-----------------------生成查询条件案例

/// <summary>

/// 生成查询条件

/// </summary>

/// <param name="model"></param>

/// <param name="paramters"></param>

/// <returns></returns>

public static string CreateSqlStr(WorkersModel model,List<SqlParameter> paramters)

{

StringBuilder sql = new StringBuilder();

if (model != null)

{

if (!string.IsNullOrEmpty(model.Name))

{

sql.Append(" and Name like @Name");

paramters.Add(new SqlParameter("@Name","%"+model.Name+"%"));

}

if (!string.IsNullOrEmpty(model.IdCard))

{

sql.Append(" and IdCard like @IdCard");

paramters.Add(new SqlParameter("@IdCard", "%" + model.IdCard + "%"));

}

return sql.ToString();

}

--------------------------enabled=fase;不会保存视图状态；

document.getElementById("txtName").disabled=true;这种也不会保存视图状态，只能手动保存了，记住奥；

当然也可以用js控制hildfiled部分；

--------------------------------------弹出提示框进行选择！！！

function ChooseDele()

{

if(confirm("是否同时删除供应商复核与供应商复评与供应商调查里的数据？"))

{

document.getElementById("hidCommon").value="allDel";

document.getElementById("hidXtBH").value=bh;

}else

{

document.getElementById("hidCommon").value="Only";

document.getElementById("hidXtBH").value=bh;

}

// __doPostBack("UpdatePanel1","");

document.form1.submit();

}

-----------------------------------------数据库中判断字段是否为空

IF @d is null

-----------------------------------------------------引以为豪的sql拼接：最复杂的sql拼接：经典之作

public static DataTable GetStatistics(WorkRecordsModel model)

{

//由于无必要新创实体，返回datatable类型

List<SqlParameter> list = new List<SqlParameter>();

StringBuilder strSql = new StringBuilder();

strSql.Append("SELECT Workers.[Name],Workers.IdCard,sum(WR.WorkDays)as sumdays,sum(WR.Wages)as sumwages,");

strSql.Append("sum(PW1.AllWages)as sumallwages0,sum(PW2.AllWages) as sumallwages1 FROM Workers where 1=1");

if (model.Name != "" || model.Name != null)

{

strSql.Append(" and Name like @Name");

list.Add(new SqlParameter("@Name", "%" + model.Name + "%"));

}

strSql.Append(" LEFT join (select * from WorkRecords where [Date]>=@begindate and [Date]<=@enddate");

if (model.XMId != null || model.XMId != 0)

{

strSql.Append(" and XMId=@XMId");

list.Add(new SqlParameter("@XMId",model.XMId));

}

strSql.Append(" )as WR on Workers.IdCard=WR.IdCard");

strSql.Append(" LEFT JOIN (SELECT * FROM PayWages WHERE [Date]>=@begindate and [Date]<=@enddate AND Type=0) as PW1 ON Workers.IdCard=PW1.IdCard");

strSql.Append(" LEFT JOIN (SELECT * FROM PayWages WHERE [Date]>=@begindate and [Date]<=@enddate AND Type=1) as PW2 ON Workers.IdCard=PW2.IdCard");

strSql.Append(" GROUP BY Workers.[Name],Workers.IdCard");

list.Add(new SqlParameter("@begindate",model.BeginTime));

list.Add(new SqlParameter("@enddate",model.EndTime));

DataTable table=new DataTable();//ui只需判断rows.count便可

table= DBHelperXT.GetDataSet(strSql.ToString(),list.ToArray());

return table;

}

------------------------获取本月第一天，本周第一天；

namespace MyOffice.Framework

{

public class DateProvider

{

/// <summary>

/// 本周的第一天

/// </summary>

/// <returns></returns>

public static DateTime CurrentWeekStartDay()

{

int spanDays=0;

switch (DateTime.Now.DayOfWeek)

{

case DayOfWeek.Monday: spanDays = 0;

break;

case DayOfWeek.Tuesday: spanDays = 1;

break;

case DayOfWeek.Wednesday: spanDays = 2;

break;

case DayOfWeek.Thursday: spanDays = 3;

break;

case DayOfWeek.Friday: spanDays = 4;

break;

case DayOfWeek.Saturday: spanDays = 5;

break;

case DayOfWeek.Sunday: spanDays = 6;

break;

}

DateTime start = DateTime.Today.AddDays(-spanDays);

return start;

}

/// <summary>

/// 本月的第一天

/// </summary>

/// <returns></returns>

public static DateTime CurrentMonthStartDay()

{

DateTime start = DateTime.Today.AddDays(-DateTime.Today.Day + 1);

return start;

}

-------------------------经典sql拼接；

//由于无必要新创实体，返回datatable类型

List<SqlParameter> list = new List<SqlParameter>();

StringBuilder strSql = new StringBuilder();

page.TotalRow = GetStatisticsCount(model);

strSql.Append("select * from (SELECT Workers.[Name],Workers.IdCard,WR.sumdays,WR.sumwages,");

strSql.Append(" PW1.sumallwages0,PW2.sumallwages1,row_number() over(order by Workers.Name) as rowId FROM Workers");

strSql.Append(" LEFT join (select WR1.IdCard,sum(WR1.WorkDays) as sumdays,sum(WR1.Wages) as sumwages");

strSql.Append(" from (select * from WorkRecords where [Date]>=@begindate and [Date]<=@enddate");

if (model.XMId != 0)

{

strSql.Append(" and XMId=@XMId");

list.Add(new SqlParameter("@XMId", model.XMId));

}

strSql.Append(" ) as WR1 group by WR1.IdCard");

strSql.Append(" )as WR on Workers.IdCard=WR.IdCard");

strSql.Append(" LEFT JOIN (SELECT PW11.IdCard,sum(PW11.AllWages) as sumallwages0 FROM");

strSql.Append(" (select * from PayWages WHERE [Date]>=@begindate and [Date]<=@enddate AND Type=0) as PW11");

strSql.Append(" group by PW11.IdCard) as PW1 ON Workers.IdCard=PW1.IdCard");

strSql.Append(" LEFT JOIN (SELECT PW22.IdCard,sum(PW22.AllWages) as sumallwages1 FROM");

strSql.Append(" (select * from PayWages WHERE [Date]>=@begindate and [Date]<=@enddate AND Type=1) as PW22 ");

strSql.Append(" group by PW22.IdCard) as PW2 ON Workers.IdCard=PW2.IdCard");

if (model.Name != "" || model.Name != null)

{

strSql.Append(" where Workers.[Name] like @name");

list.Add(new SqlParameter("@Name", "%" + model.Name + "%"));

}

strSql.Append(" ) as T where T.rowId between ").Append(page.Begin).Append(" and ").Append(page.End);

list.Add(new SqlParameter("@begindate", model.BeginTime));

list.Add(new SqlParameter("@enddate", model.EndTime));

DataTable table = new DataTable();//ui只需判断rows.count便可

table = DBHelperXT.GetDataSet(strSql.ToString(), list.ToArray());

------------------------------------

txtBeginTime.Text = DateTime.Today.AddDays(-DateTime.Today.Day + 1).ToString("yyyy-MM-dd");

txtEndTime.Text = DateTime.Now.ToString("yyyy-MM-dd");

-----------------------------------------------------------------------cube grouping rollup的使用；

SELECT CASE WHEN grouping(Name)=1 THEN '合计' ELSE Name END as 姓名,

CASE WHEN grouping(Sex)=1 THEN '合计' ELSE sex END as 性别

,sum(Score) as 总成绩

from Score

GROUP BY name,Sex WITH CUBE

--当出现with rollup 根据by后面第一个字段分组进行汇总，聚合性别；

--若group by sex,Name WITH rollup则根据性别分组，聚合姓名；(男的有多少，女的有多少)

---若with cube,则以group by 后面的多列进行分组，多个维度分组；by后的第一个字段；(顺序为:根据性别聚合,总聚合,根据名称聚合)

------------count(1)和count（*）是等效的，都会返回本列的行数；

-------------------------------------------------------------------统计报表（合计、小计：cube rollup grouping的用法）

create table #t(a int,b int,c int,d int,e int)

insert into #t values(1,2,3,4,5)

insert into #t values(1,2,3,4,6)

insert into #t values(1,2,3,4,7)

insert into #t values(1,2,3,4,8)

insert into #t values(1,3,3,4,5)

insert into #t values(1,3,3,4,6)

insert into #t values(1,3,3,4,8)

insert into #t values(1,3,3,4,7)

insert into #t values(2,2,2,4,5)

insert into #t values(2,2,3,4,6)

insert into #t values(2,2,4,4,7)

insert into #t values(2,2,5,4,8)

insert into #t values(2,3,6,4,5)

insert into #t values(2,3,3,4,6)

insert into #t values(2,3,3,4,8)

insert into #t values(2,3,3,4,7)

情况一：只有一个分类汇总列时，只需要一个合计。只需要增加with rollup即可。（一个维度的）

select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,

sum(b),sum(c),sum(d),sum(e) from #t group by a with rollup

情况二:有多个分类汇总列，只需要一个合计.增加rollup之后，需要增加判断。

select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,

sum(c),sum(d),sum(e) from #t

group by a,b with rollup

having grouping(b)=0 or grouping(a)=1

select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,

sum(d),sum(e) from #t

group by a,b,c with rollup

having grouping(c)=0 or grouping(a)=1

情况三：有多个分类汇总列，需要全部的小计和合计

select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,

case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b,

case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,

sum(d),sum(e) from #t

group by a,b,c with rollup

另外一种显示小计的方式

select case when grouping(a)=1 then '合计'

when grouping(b)=1 then cast(a as varchar)+'小计'

else cast(a as varchar) end a,

case when grouping(b)=0 and grouping(c)=1

then cast(b as varchar)+'小计' else cast(b as varchar) end b,

case when grouping(c)=1 and grouping(b)=0

then '' else cast(c as varchar) end c,

sum(d),sum(e) from #t

group by a,b,c with rollup

情况四：有多个分类汇总列，需要部分的小计和合计

select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,

case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,

sum(d),sum(e) from #t

group by a,b,c with rollup

having grouping(a)=1 or grouping(b)=0

---

select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,

case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b,

sum(d),sum(e) from #t

group by a,b,c with rollup

having grouping(a)=1 or grouping(b)=1 or grouping(c)=0

select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,

case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,

sum(d),sum(e) from #t

group by a,b,c with rollup

having grouping(a)=1 or grouping(b)=0

--------------------------------------------递归遍历节点案例

/// <summary>

/// //根据施工部id遍历所有子节点id

/// </summary>

/// <param name="sgbId"></param>

/// <returns></returns>

public static string GetSgbIds(string sgbId)

{

StringBuilder strB = new StringBuilder();

StringBuilder sb = new StringBuilder();

sb.Append("select BH from ShiGongBuWei where SJ=@SJ");

SqlParameter[] paras ={

new SqlParameter("@SJ",sgbId)

};

DataTable ta = DBHelper.GetDataSet(sb.ToString(),paras);

strB.Append(sgbId).Append(",");

if (ta.Rows.Count != 0)

{

foreach (DataRow row in ta.Rows)

{

strB.Append(GetSgbIds(row["BH"].ToString()));

}

return strB.ToString();

}

---------------------------------------------对于存储过程中in(@str),这样是不好用的解决方案有两个

1.经语句赋值个字符串，再exec();:::in ('+@sgbids+')

2.利用逗号分割函数进行

--------------------------padding可以扩展元素的空间，比如《a> href>背景范围比较小，所以需要扩展一下；这样背景就变大了；

----------------页面布局时候：

行级元素通过padding来扩展空间：

块级元素通过 line-height:100px; padding:8px 12px;两者来扩充空间，因为块状不能自动调节行高，二行级可以。

----------------------------------16乘16小图标显示

8)收藏夹小图标例如：首先制作一个16x16的icon图标，命名为favicon.ico，放在根目录下。然后将下面的代码嵌入head区：

---------------------------搜索引擎设置(至于title下面)

为搜索引擎准备的内容

代码如下，替换成你自己站点的内容就可以：

? 允许搜索机器人搜索站内所有链接。如果你想某些页面不被搜索，推荐采用robots.txt方法

? 设置站点作者信息

? 设置站点版权信息

? 站点的简要介绍(推荐)

? 站点的关键词(推荐)

, xhtml, css, graphic, design, layout, usability, cces

sibility, w3c, w3, w3cn, ajie" name="keywords" /> 先介

绍这么多。补充说明，前面花了5节都是讲head区的代码，实际页面

内容还一字未提，呵呵，不要急，其实head区是非常重要的，看一个页面

的head的代码就可以知道设计师是否够专业。

-------------------------------------------------js获取身份证号年龄（根据身份证获取年龄）

if(idCard.length==15)

{

year="19" + idCard.substring(6,8);

document.getElementById("txtAge").value=dates.getFullYear()-parseInt(year);

}

else if(idCard.length==18)

{

year=idCard.substring(6,10);

document.getElementById("txtAge").value=dates.getFullYear()-parseInt(year);

}

-----------------------------------------------------

9:sql中所有的索引都从1开始；charindex,substring()等；

SET @a=charindex('o',@a)o在@a的位置从1开始；

SET @a=substring(@a,2,2)2：起始，2：长度；

js中s.substring();开始和结束索引

js中s.substr();索引+长度；

------------------------------------------------------------------正则表达式：

//郭泽峰总结：||备注：当 regu是字符串时应示例对象RegExp，否则的话 var emailReg =/在此加上正则/

//验证邮箱

function CheckEmail(strEmail) {

var emailReg = /^[w-]+(.[w-]+)*@[w-]+(.[w-]+)+$/;

if (emailReg.test(strEmail)) {

return true;

}

else {

return false;

}

};

//验证是不是数字

function IsNum(str) {

//前提是str不为空！

//可以是小数

if (isNaN(str)) {

return false;

}

else {

return true;

}

};

//验证是不是整数格式

function IsInteger(str) {

var regu = /^[-]{0,1}[0-9]{1,}$/;

return regu.test(str);

};

//验证是不是为空（包括空或者是空格！）

function IsNull(str) {

if (str == "") {

return true;

}

var regu = "^[ ]+$";

var re = new RegExp(regu);

return re.test(str);

};

//检查输入字符串是否只由英文字母和数字和下划线组成

function IsNumberOr_Letter(s) {

//判断是否是数字或字母

var regu = "^[0-9a-zA-Z\_]+$";

var re = new RegExp(regu);

if (re.test(s)) {

return true;

}

else {

return false;

}

};

//判断是否是汉字、字母、数字或下划线组成

function IsChinaOrNumbOrLett(s) {

//不必验证为空

var regu = "^[0-9a-zA-Zu4e00-u9fa5\_]+$";

var re = new RegExp(regu);

if (re.test(s)) {

return true;

}

else {

return false;

}

};

//去除字符串空格

function Trim(str){ //删除左右两端的空格

return str.replace(/(^s*)|(s*$)/g, "");

}

function Ltrim(str){ //删除左边的空格

return str.replace(/(^s*)/g,"");

}

function Rtrim(str){ //删除右边的空格

return str.replace(/(s*$)/g,"");

}

//验证身份证号

function IsIdCard(str) {

var reg = /(^d{15}$)|(^d{18}$)|(^d{17}(d|X|x)$)/;

return reg.test(str);

}

//验证日期（所有格式都可以）

function IsDateWithAllForm(str) {

var reg =/^((d{2}(([02468][048])|([13579][26]))[-/s]?((((0?[13578])|(1[02]))[-/s]((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[-/s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[-/s]?((0?[1-9])|([1-2][0-9])))))|(d{2}(([02468][1235679])|([13579][01345789]))[-/s]?((((0?[13578])|(1[02]))[-/s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[-/s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[-/s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))(s(((0?[1-9])|(1[0-2])):([0-5][0-9])((s)|(:([0-5][0-9])s))([AM|PM|am|pm]{2,2})))?$/;

return reg.test(str);

}

//验证日期+格式：格式必须为：2012-12-12这种的；

function IsDateWithOneForm(str) {

var reg = /^((((1[6-9]|[2-9]d)d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]d|3[01]))|(((1[6-9]|[2-9]d)d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]d|30))|(((1[6-9]|[2-9]d)d{2})-0?2-(0?[1-9]|1d|2[0-8]))|(((1[6-9]|[2-9]d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-))$/;

return reg.test(str);

}

//验证位数：/^d{3}$/：必须为三位，/^d{3,6}$/：为3-6位

function IsRight(str) {

var reg = /^d{3,6}$/;

return reg.test(str);

}

//------------------------------------------杂记

数据库的完整性=可靠性+准确性。

SELECT NEWID()（html中newid()）,相当于new guid（）,其类型为uniqueidentifier

数据库中双精度用real,单精度：float

-----黑客经常攻破IIS去为一些html页面加上iframe;

隐藏了但是有；

-----------

var father = window.parent;若三层窗口：var father = window.parent.parent;若：var father = window.Top;top指的是最顶层；

document.getElementById("result").innerHTML = str;//在页面显示文字。

-----------

if(typeof(father.accept)=="function")//判断是不是自定义函数类型

------new Date()获取新的日期；

--------------<%=DateTime.Now.ToLocalTime() %>

--------------get参数传递方式会在ie页面进行缓存，但是post不会。

----javascript是区分大小写的；

===========================================================================================================

ajax中503mistakes 指的是服务未提供，404错误指的是未找到；12007错误：跨域名错误提示

if(x.readyState==4&&x.status==200){}

else{alert(x.status)}//提示错误；

-------------///<reference name="MicrosoftAjax.js"/>

javascript脚本

///<reference name="MicrosoftAjax.js"/>

/// <reference path="JScript2.js" />

/// <reference path="jquery-1.4.1-vsdoc.js" />

window.onload= function () {

var s =String.format("hellow:{0}", "abc");

alert(s);

var i = addint(1,2);

alert(i);

$("#a").html(s); //应用jquery做法

$get("a").value = s;//这两行相当于document.getelementbyid();查找元素；

----------------------

}

--------------scriptmanage一个页面只能应用一次；若调用web服务应该命名为scriptmamageProxy(代理)

--------------ajaxcontroltookit官方下载网址：ajax.aspx.net;

----updatepanel笔记：

有个属性：updatemode,若选择conditional则被人不会影响我，否则（aways）所有panel回发；

2）若一个界面有两个panel则一个（不会发）控制另一个则设置childrenAStriggers为false；同时设updatemodles属性为conditionals(若为aways则矛盾)

3）fileupload不支持异步操作

-------------javascript

1)eval函数：用于计算字符串表达式的值；如3+3，var a=eval("3+4")->a=7;

2)isnan函数：用于验证参数是否为nan;

3)indexOf("a",0):查找字符串中存在a的位置，不存在返回-1；

4）toLowerCase();转化小写；

5）toUpperCase();转化成大写；

6)round(y)四舍五入取整;

7)sqrt(y)返回y的平方根；

8) var da = new Date();

// da.setFullYear(2014,1,14);//自定义日期

// da.setHours(13,25,32,900);

8)定时：settimeout:一次；setinterval；每隔几秒一次

9)javascript:var a=[1,2,3,4,5];声明一个数组（array）

------------------------------选择器：

1）：$("#id")

2):$("元素标签")

3）$(".css")//内填写.+样式名

//迭代：$("#id")返回的是一个数组（自动遍历）；var a=$("#id");if(a.length<=0)alert("没有此元素")

$(function () {

$("p").click(function () { $.each($(this).nextAll("p"), function () { $(this).css("background-color", "red"); } )});

})//阶级选择器

-------------------------------彩色照片变黑白照片：

{ filter:Gray;

}

</style>

function guo() {

// $(document.body).toggleClass("b");

// }

----------------------------------：图片注册事件

----------------------------上级目录嗲下级目录则~/;下级目录调用上级目录则：../;

---------------------------href="read.aspx?id={blogid}#coment实现自动定位：在第二个页面中加上《a name="coment"></a>

--------------再用repeater时，应删除selectitem块中的type="object"才能按id显示数据。

------------------javas获取却string【“id”】-- var blogid = '<%=Request["id"] %>';

------------------ $("#ab").toggle("slow");使div其实就开始隐藏

--------------------------

<%if (this.xianshi)//xianshi 是在后台中的属性

{ %>

<span class="permalink"><a href='<%# "~/Edite.aspx?blogid="+Eval("blogid") %>' runat="server">编辑</a>

<%} %>

}

-------------------new Guid(blogid):blogid 是guid的string("string")形式的，要想转化为新的guid 类型则new guid(blogdi)

----------------- <a href="#" df="<%# Eval("blogid") %>" onclick="shan(this.df)">删除</a></span>

------- window.location.reload();卸载jiavascript脚本中，重新加载（刷新）

------window.location.href="default.aspx"，重新加载到另一界面

--------------------$(".[tag='delete']").hide() ;取属性

----------------window.location.href("Default.aspx?uid='"+result.d+"'");

-----------------绑定日期

<%# Eval("Datetime"，"{0:d}")%>

Text='<%# Bind("OnDutyTime1","{0:hh:mm}") %>' />绑定时间部分12

Text='<%# Bind("OnDutyTime1","{0:H:mm}") %>' />24制

-----------------

a.[Order]当order为关键字，所以为此，不为[a.order]

一个表可以有多个列作为主键，担有且只有一个；

----------------

byte[] by=你读出来的的图片的字节

Response.ContentType = "image/bmp ";

Response.BinaryWrite(by);

----------------------

protected,类外不能调用，但是继承类可以；

-----------------------------------------------------数据库

isex char(4) check(isex='男' or isex='女'),

date datetime default '2011-6-6'

----------

drop trigger tr_sd

create trigger tr_sd

on score

after insert

select * from inserted

print '你插入了'

---------------

--(7)查询班级某科科目平均分数

select avg(math) as avg_sco from score

-----------------------

select * from (SELECT Id,Name,Price from Wuzi) as dd

------------------------

select t1.productid,t1.unitprice

from [order details] as t1 join [order details]

on t1.productid=t2.productid

group by ti.productid,t1.unitprice

having t1.unitprice<avg(t2.unitprice)

------------------------

--可将学生表中的数据导入老师表中

insert teacher

select id,name from student

--------------五种约束;

1.主键约束、外键约束、检查约束、默认约束、唯一约束

-sql是structured Query Language 结构化查询语句

---------T-Sql与sql

t_sql是sql的加强版

对功能进行了补充：变量说明、流程控制、功能函数

-----------

like 'c_'必须是2个字符

单引号表示字符串，与c#不同；

like 'c_%'至少两个，多了不限

lide 'as[1-3]'1-3的数字'%[1-4]' [1,2,3,4]任选

--------like可放在check中约束：通配符：%、—【】

----------------逻辑表达式：not 1=0；not(a='a')===<>

select 3%2 结果是1；

一张表只能有一个自增列

-------insert into biao(1,2)values(default,'12');

默认插入defalut，或不插

----------union合并数据

select 1 union select 2

-------------truncate table a 全都删除:自增重置为1；

delete from table a:一条一条删

-----------

select aa+'.'+bb from stu;aa bb是列名

-------------

123 as b getdate() as c都可以；

address is null ;放于where后面

条件不是空值：

where (not(address is null))

------select top 20 percent name from student;

语句可以出现表达式：如：（aa*30）as b或where （a*23）>30

------------------------------------

SELECT CASE WHEN number<5 THEN 'z' ELSE 'x' END as 'lllll'

from xin

---------------------------------union的用法，并在下面下面结果只有一列并且必须保证类型一致1,2必须是字符类型

USE xinwen

SELECT CASE WHEN number<5 THEN '1' ELSE '2' END as 'lllll'

from xin

UNION

SELECT title as cc from xin

--------------------order by后面可以跟表达式子

as 后面的名称不能出现在where后，但可以出现在order by后面

------------------字符串:

SELECT charindex('a','sda',1);返回3，第二个字符串从1开始，和c#不一样

select len('123')返回3；

select ltrim(' 123')//消除左侧空格。rtrim消除右侧空格

select left('1234',2)//取左侧两位

select right('1234',2)

select replace('abcdef','ab','00')

------------结果是00cdef

--------------------

select stuff('abcde',2,3,'11111'):从第2个字符截取3个再塞入最后的字符：a1111e

------------

select dateadd(yy,4,getdate())//加年限yy,改为mm,改为dd获取天数

SELECT datediff(mm,getdate(),'2012-9-2')

select datename(dw,getdate())//返回日期的周几切记返回的是字符串

sign(@A)若符号是-，返回-1，+返回1,0返回0

sqrt(16),平方根4；

round();近似值

power(2,4);2sup4;

floor(a):小于等于a的最小整数；

ceiling:大于或等与最大整数值；

60-100的随机数：rand()*40+60即可；

select convert(INT,'123') 或 select cast('123' as int)

select 1+'2'//返回3；

datalength();返回字节数，一个汉字两个字节，但是len()返回个数

system_user登陆名；

select isnull(mess,'哈哈') from xin：如果为空的话改为哈哈输出

set @str2=substring(@str1,0,@num)、

--------

min,max,avg,count,;

----------分组查询：多列分组；

group by id,iid,但二二对应，顺序不变

select distinct GTime from Grade

param[4].Value!=DBNull.Value

set @stuid=rtrim(@bubianid)+convert(char(10),@beginstuid)

----------内联：左右两个表都存在才显示

select identity(1,1) as t;表示列的行数；

SQL Server判断对象是否存在 (if exists (select * from sysobjects )(转)

1 判断数据库是否存在

Sql代码

if exists (select * from sys.databases where name = ’数据库名’)

drop database [数据库名] if exists (select * from sys.databases where name = ’数据库名’)

drop database [数据库名]

2 判断表是否存在

Sql代码

if exists (select * from sysobjects where id = object_id(N’[表名]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)

drop table [表名] if exists (select * from sysobjects where id = object_id(N’[表名]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)

drop table [表名]

3 判断存储过程是否存在

Sql代码

if exists (select * from sysobjects where id = object_id(N’[存储过程名]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)

drop procedure [存储过程名] if exists (select * from sysobjects where id = object_id(N’[存储过程名]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)

drop procedure [存储过程名]

4 判断临时表是否存在

Sql代码

if object_id(’tempdb..#临时表名’) is not null

drop table #临时表名 if object_id(’tempdb..#临时表名’) is not null

drop table #临时表名

5 判断视图是否存在

Sql代码

--SQL Server 2000

IF EXISTS (SELECT * FROM sysviews WHERE object_id = ’[dbo].[视图名]’

--SQL Server 2005

IF EXISTS (SELECT * FROM sys.views WHERE object_id = ’[dbo].[视图名]’ --SQL Server 2000

IF EXISTS (SELECT * FROM sysviews WHERE object_id = ’[dbo].[视图名]’

--SQL Server 2005

IF EXISTS (SELECT * FROM sys.views WHERE object_id = ’[dbo].[视图名]’

6 判断函数是否存在

Sql代码

-- 判断要创建的函数名是否存在

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[函数名]’) and xtype in (N’FN’, N’IF’, N’TF’))

drop function [dbo].[函数名] -- 判断要创建的函数名是否存在

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[函数名]’) and xtype in (N’FN’, N’IF’, N’TF’))

drop function [dbo].[函数名]

7 获取用户创建的对象信息

Sql代码

SELECT [name],[id],crdate FROM sysobjects where xtype=’U’

xtype 的表示参数类型，通常包括如下这些

C = CHECK 约束

D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束

L = 日志

FN = 标量函数

IF = 内嵌表函数

P = 存储过程

PK = PRIMARY KEY 约束（类型是 K）

RF = 复制筛选存储过程

S = 系统表

TF = 表函数

TR = 触发器

U = 用户表

UQ = UNIQUE 约束（类型是 K）

V = 视图

X = 扩展存储过程

*/ SELECT [name],[id],crdate FROM sysobjects where xtype=’U’

xtype 的表示参数类型，通常包括如下这些

C = CHECK 约束

D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束

L = 日志

FN = 标量函数

IF = 内嵌表函数

P = 存储过程

PK = PRIMARY KEY 约束（类型是 K）

RF = 复制筛选存储过程

S = 系统表

TF = 表函数

TR = 触发器

U = 用户表

UQ = UNIQUE 约束（类型是 K）

V = 视图

X = 扩展存储过程

8 判断列是否存在

Sql代码

if exists(select * from syscolumns where id=object_id(’表名’) and name=’列名’)

alter table 表名 drop column 列名 if exists(select * from syscolumns where id=object_id(’表名’) and name=’列名’)

alter table 表名 drop column 列名

9 判断列是否自增列

Sql代码

if columnproperty(object_id(’table’),’col’,’IsIdentity’)=1

print ’自增列’

else

print ’不是自增列’

SELECT * FROM sys.columns WHERE object_id=OBJECT_ID(’表名’)

AND is_identity=1 if columnproperty(object_id(’table’),’col’,’IsIdentity’)=1

print ’自增列’

else

print ’不是自增列’

SELECT * FROM sys.columns WHERE object_id=OBJECT_ID(’表名’)

AND is_identity=1

10 判断表中是否存在索引

Sql代码

if exists(select * from sysindexes where id=object_id(’表名’) and name=’索引名’)

print ’存在’

else

print ’不存在 if exists(select * from sysindexes where id=object_id(’表名’) and name=’索引名’)

print ’存在’

else

print ’不存在

11 查看数据库中对象

Sql代码

SELECT * FROM sys.sysobjects WHERE name=’对象名’ SELECT * FROM sys.sysobjects WHERE name=’对象名’

//sql语句优化：

1.尽量使用存储过程

2.使用存储过程建立表外链是最好所有的存储过程锁定表的顺序最好一样，否则会出现死锁现象；

3.避免类型转换；

4.在一个存储过程中使用的结果尽量保存到一个变量中去，这样减少运算；

5.尽量少使用count的函数，消耗内存；

6.存储过程可进行复杂运算减少网络传输；

--------------------------------------------like里面放入变量

SELECT * FROM Info

WHERE

Name

LIKE '%'+@a+'%'

---------------------------------------------多重选择！判断

！第一种用法;select CASE WHEN id=1 THEN 'sdfsdf' WHEN id=2 THEN 'rrsdfs' END as 'asdad'

FROM Info

!第二种用法：

------------------------------------------------------

三范式是一个人根据数学函数提出的：是一种具有最小冗余的表结构

1.第一范式：确保每列的原子行；每个列不可再分割，当然也不要分割的太死；

2.凡是多对多关系，都转为两个一对多关系，从而增添一个关联表；

3.第二范式要求每一个表只做一件事情；重要：表中除了主键列以外的其他列

都要依赖于主键列；（强调依赖关系）

4第三范式：在一个表中消除间接依赖；比如有用户ID就不需要有用用用户姓名了；我勒个去

如何保证表中不能插入相同数据行；（强调直接依赖关系；）

a 1 2 3

a 1 2 4

不能有a 1 2 3,a 1 3 不能再出现；

-----------------------------------------子查询时，当在多个结果中划等号可以用in

select ----where id in (select id from ----)

----------------

if(select Id from b where id>80)//是错误的，if只识别一个返回值，所以改为

if(select Max(id) from b where id>80)即可；

if exists()//可以判断返回多个返回值；

----------------事务

begin transaction

如果没问题的话提交事务；

commit transaction

回滚事务

rollback transaction;

-----------------------------------------------------------------------------------

===不只是数值相等,数据类型也要相同

虽然JS是弱类型,不用明确的定义数据类型,而且JS会根据情况自动转化类型.但是还是有数据类型的

isex char(4) check(isex='男' or isex='女'),

univer char(30) default '沈阳理工大学应用技术学院'

--计算时间差，运动会倒计时

--datediff(获取的时间部分，减得时间，被减的时间)

select datediff(day,getdate(),'2011-5-27')

select datediff(year,'1991-03-03',getdate())--后面的减去中间的就是生日

select id,upper(name) as lname from stu

select id,lower(name) as lname from stu

标量函数

create function fn_newRegion(@input nvarchar(30))

returns nvarchar(30)--指明返回值的数据类型returns记住要加s

begin

if @input is null

set @input='not know'

return @input

end

select * from authors

select count(distinct city) from authors

回滚事务

begin

raiserror('score is between0 and 100',16,1)

rollback transaction--回滚事物，取消操作

end

ALTER FUNCTION [dbo].[funGenerateBillCode](@name varchar(10),@time datetime,@time1 datetime)

RETURNS varCHAR(20)

BEGIN

declare @e varchar(20)

declare @a int

declare @b int

declare @c varchar(20)

SELECT @a=DATEPART(year,@time)

SELECT @b=DATEPART(month,@time1)

set @c=convert(varchar(20),@a*100+@b)

set @e=@c+@name

return @e

END

command.Parameters.Add(

new SqlParameter("ReturnValue", SqlDbType.Int, 4,

ParameterDirection.ReturnValue, false, 0, 0,

string.Empty, DataRowVersion.Default, null));

------存储过程“、

CREATE PROCEDURE [dbo].[uspCreateBillDispense]

(

@billType VARCHAR(50), --票据类型

@billStartCode VARCHAR(50), --票据开始时间

@billEndCode VARCHAR(50), --票据结束时间

@receiveBillperson VARCHAR(50), --领票人

@acceptStation VARCHAR(50), --接货点

@receiveBillTime DATETIME, --领票时间

@releaseperson VARCHAR(50), --分发人

@PKID INT OUTPUT --票据ID

)

CREATE FUNCTION [dbo].[funGenerateBillCode](@billType,@billCode,@receiveBillTime)

RETURNS CHAR(11)

BEGIN

DECLARE @code CHAR(11)

IF @billType='运货单'

SET @code=@code+'C'

ELSE

SET @code=@code+'R'

END

/*********************************************************************************

*过程名称 : uspCreateBillDispense

*功能描述 : 根据登录ID, 获取一条用户的信息

*输入参数 :

@billCode VARCHAR(50), ---票据开始号或票据结束号

@billType VARCHAR(50), ---票据类型

@receiveBillTime DATETIME ---领票时间

*输出参数 :

无

*返回值 :

0 - 不存在

1-存在

*作者 : 郭泽峰

*创建日期 : 2011-12-27

***********************************************************************************/

CREATE PROCEDURE [dbo].[uspExistBillDispense]

(

@billCode VARCHAR(50), ---票据开始号或票据结束号

@billType VARCHAR(50), ---票据类型

@receiveBillTime DATETIME ---领票时间

)

/* SET NOCOUNT ON */

RETURN

-----------------

CREATE PROCEDURE IsFocus @a INT, @b INTASBEGIN SET NOCOUNT ON; DECLARE @res bit IF EXISTS(SELECT * FROM dbo.foucs WHERE [user1]=@a AND [user2]=@b) SET @res=1 ELSE SET @res=0 SELECT @res AS res ENDGO

---------------------数据库分页效果

CREATE PROC sp_PageView

@tbname sysname, --要分页显示的表名

@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段

@PageCurrent int=1, --要显示的页码

@PageSize int=10, --每页的大小(记录数)

@FieldShow nvarchar(1000)= ' ', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder nvarchar(1000)= ' ', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC

用于指定排序顺序

@Where nvarchar(1000)= ' ', --查询条件

@PageCount int OUTPUT --总页数

SET NOCOUNT ON

--检查对象是否有效

IF OBJECT_ID(@tbname) IS NULL

BEGIN

RAISERROR(N '对象 "%s "不存在 ',1,16,@tbname)

RETURN

END

IF OBJECTPROPERTY(OBJECT_ID(@tbname),N 'IsTable ')=0

AND OBJECTPROPERTY(OBJECT_ID(@tbname),N 'IsView ')=0

AND OBJECTPROPERTY(OBJECT_ID(@tbname),N 'IsTableFunction ')=0

BEGIN

RAISERROR(N ' "%s "不是表、视图或者表值函数 ',1,16,@tbname)

RETURN

END

--分页字段检查

IF ISNULL(@FieldKey,N ' ')= ' '

BEGIN

RAISERROR(N '分页处理需要主键（或者惟一键） ',1,16)

RETURN

END

--其他参数检查及规范

IF ISNULL(@PageCurrent,0) <1 SET @PageCurrent=1

IF ISNULL(@PageSize,0) <1 SET @PageSize=10

IF ISNULL(@FieldShow,N ' ')=N ' ' SET @FieldShow=N '* '

IF ISNULL(@FieldOrder,N ' ')=N ' '

SET @FieldOrder=N ' '

ELSE

SET @FieldOrder=N 'ORDER BY '+LTRIM(@FieldOrder)

IF ISNULL(@Where,N ' ')=N ' '

SET @Where=N ' '

ELSE

SET @Where=N 'WHERE ( '+@Where+N ') '

--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)

IF @PageCount IS NULL

BEGIN

DECLARE @sql nvarchar(4000)

SET @sql=N 'SELECT @PageCount=COUNT(*) '

+N ' FROM '+@tbname

+N ' '+@Where

EXEC sp_executesql @sql,N '@PageCount int OUTPUT ',@PageCount OUTPUT

SET @PageCount=(@PageCount+@PageSize-1)/@PageSize

END

--计算分页显示的TOPN值

DECLARE @TopN varchar(20),@TopN1 varchar(20)

SELECT @TopN=@PageSize,

@TopN1=(@PageCurrent-1)*@PageSize

--第一页直接显示

IF @PageCurrent=1

EXEC(N 'SELECT TOP '+@TopN

+N ' '+@FieldShow

+N ' FROM '+@tbname

+N ' '+@Where

+N ' '+@FieldOrder)

ELSE

BEGIN

--处理别名

IF @FieldShow=N '* '

SET @FieldShow=N 'a.* '

--生成主键(惟一键)处理条件

DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),

@s nvarchar(1000),@Field sysname

SELECT @Where1=N ' ',@Where2=N ' ',@s=@FieldKey

WHILE CHARINDEX(N ', ',@s)> 0

SELECT @Field=LEFT(@s,CHARINDEX(N ', ',@s)-1),

@s=STUFF(@s,1,CHARINDEX(N ', ',@s),N ' '),

@Where1=@Where1+N ' AND a. '+@Field+N '=b. '+@Field,

@Where2=@Where2+N ' AND b. '+@Field+N ' IS NULL ',

@Where=REPLACE(@Where,@Field,N 'a. '+@Field),

@FieldOrder=REPLACE(@FieldOrder,@Field,N 'a. '+@Field),

@FieldShow=REPLACE(@FieldShow,@Field,N 'a. '+@Field)

SELECT @Where=REPLACE(@Where,@s,N 'a. '+@s),

@FieldOrder=REPLACE(@FieldOrder,@s,N 'a. '+@s),

@FieldShow=REPLACE(@FieldShow,@s,N 'a. '+@s),

@Where1=STUFF(@Where1+N ' AND a. '+@s+N '=b. '+@s,1,5,N ' '),

@Where2=CASE

WHEN @Where= ' ' THEN N 'WHERE ( '

ELSE @Where+N ' AND ( '

END+N 'b. '+@s+N ' IS NULL '+@Where2+N ') '

--执行查询

EXEC(N 'SELECT TOP '+@TopN

+N ' '+@FieldShow

+N ' FROM '+@tbname

+N ' a LEFT JOIN(SELECT TOP '+@TopN1

+N ' '+@FieldKey

+N ' FROM '+@tbname

+N ' a '+@Where

+N ' '+@FieldOrder

+N ')b ON '+@Where1

+N ' '+@Where2

+N ' '+@FieldOrder)

END

----------------------------------数据库时间函数的调用

1. 当前系统日期、时间

select getdate()

2. dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值

例如：向日期加上2天

select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000

3. datediff 返回跨两个指定日期的日期和时间边界数。

select datediff(day,'2004-09-01','2004-09-18') --返回：17

4. datepart 返回代表指定日期的指定日期部分的整数。

SELECT DATEPART(month, '2004-10-15') --返回 10

5. datename 返回代表指定日期的指定日期部分的字符串

SELECT datename(weekday, '2004-10-15') --返回：星期五

6. day(), month(),year() --可以与datepart对照一下

select 当前日期=convert(varchar(10),getdate(),120)

,当前时间=convert(varchar(8),getdate(),114)

select datename(dw,'2004-10-15')

select 本年第多少周=datename(week,'2004-10-15')

,今天是周几=datename(weekday,'2004-10-15')

函数参数/功能

GetDate( ) 返回系统目前的日期与时间

DateDiff (interval,date1,date2) 以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1

DateAdd (interval,number,date) 以interval指定的方式，加上number之后的日期

DatePart (interval,date) 返回日期date中，interval指定部分所对应的整数值

DateName (interval,date) 返回日期date中，interval指定部分所对应的字符串名称

参数 interval的设定值如下：

值缩写（Sql Server） Access 和 ASP 说明

Year Yy yyyy 年 1753 ~ 9999

Quarter Qq q 季 1 ~ 4

Month Mm m 月1 ~ 12

Day of year Dy y 一年的日数,一年中的第几日 1-366

Day Dd d 日，1-31

Weekday Dw w 一周的日数，一周中的第几日 1-7

Week Wk ww 周，一年中的第几周 0 ~ 51

Hour Hh h 时0 ~ 23

Minute Mi n 分钟0 ~ 59

Second Ss s 秒 0 ~ 59

Millisecond Ms - 毫秒 0 ~ 999

access 和 asp 中用date()和now()取得系统日期时间；其中DateDiff,DateAdd,DatePart也同是能用于Access和asp中，这些函数的用法也类似

举例：

1.GetDate() 用于sql server :select GetDate()

2.DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值为 514592 秒

DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值为 5 天

3.DatePart('w','2005-7-25 22:56:32')返回值为 2 即星期一(周日为1，周六为7)

DatePart('d','2005-7-25 22:56:32')返回值为 25即25号

DatePart('y','2005-7-25 22:56:32')返回值为 206即这一年中第206天

DatePart('yyyy','2005-7-25 22:56:32')返回值为 2005即2005年

SQL Server DATEPART() 函数返回 SQLServer datetime 字段的一部分。

SQL Server DATEPART() 函数的语法是：

DATEPART(portion, datetime)

其中 datetime 是 SQLServer datetime 字段和部分的名称是下列之一： Ms for Milliseconds

Yy for Year

Qq for Quarter of the Year

Mm for Month

Dy for the Day of the Year

Dd for Day of the Month

Wk for Week

Dw for the Day of the Week

Hh for Hour

Mi for Minute

Ss for Second

详细的说明:

通常，你需要获得当前日期和计算一些其他的日期，例如，你的程序可能需要判断一个月的第一天或者最后一天。你们大部分人大概都知道怎样把日期进行分割（年、月、日等），然后仅仅用分割出来的年、月、日等放在几个函数中计算出自己所需要的日期！在这篇文章里，我将告诉你如何使用DATEADD和DATEDIFF函数来计算出在你的程序中可能你要用到的一些不同日期。

在使用本文中的例子之前，你必须注意以下的问题。大部分可能不是所有例子在不同的机器上执行的结果可能不一样，这完全由哪一天是一个星期的第一天这个设置决定。第一天（DATEFIRST）设定决定了你的系统使用哪一天作为一周的第一天。所有以下的例子都是以星期天作为一周的第一天来建立，也就是第一天设置为7。假如你的第一天设置不一样，你可能需要调整这些例子，使它和不同的第一天设置相符合。你可以通过@@DATEFIRST函数来检查第一天设置。

为了理解这些例子，我们先复习一下DATEDIFF和DATEADD函数。DATEDIFF函数计算两个日期之间的小时、天、周、月、年等时间间隔总数。DATEADD函数计算一个日期通过给时间间隔加减来获得一个新的日期。要了解更多的DATEDIFF和DATEADD函数以及时间间隔可以阅读微软联机帮助。

使用DATEDIFF和DATEADD函数来计算日期，和本来从当前日期转换到你需要的日期的考虑方法有点不同。你必须从时间间隔这个方面来考虑。比如，从当前日期到你要得到的日期之间有多少时间间隔，或者，从今天到某一天（比如1900-1-1）之间有多少时间间隔，等等。理解怎样着眼于时间间隔有助于你轻松的理解我的不同的日期计算例子。

一个月的第一天

第一个例子，我将告诉你如何从当前日期去这个月的最后一天。请注意：这个例子以及这篇文章中的其他例子都将只使用DATEDIFF和DATEADD函数来计算我们想要的日期。每一个例子都将通过计算但前的时间间隔，然后进行加减来得到想要计算的日期。

这是计算一个月第一天的SQL 脚本：

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

我们把这个语句分开来看看它是如何工作的。最核心的函数是getdate()，大部分人都知道这个是返回当前的日期和时间的函数。下一个执行的函数DATEDIFF(mm,0,getdate())是计算当前日期和“1900-01-01 00:00:00.000”这个日期之间的月数。记住：时期和时间变量和毫秒一样是从“1900-01-01 00:00:00.000”开始计算的。这就是为什么你可以在DATEDIFF函数中指定第一个时间表达式为“0”。下一个函数是DATEADD，增加当前日期到“1900-01-01”的月数。通过增加预定义的日期“1900-01-01”和当前日期的月数，我们可以获得这个月的第一天。另外，计算出来的日期的时间部分将会是“00:00:00.000”。

这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数，然后把它加到“1900-01-01”上来获得特殊的日期，这个技巧可以用来计算很多不同的日期。下一个例子也是用这个技巧从当前日期来产生不同的日期。

本周的星期一

这里我是用周(wk)的时间间隔来计算哪一天是本周的星期一。

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

一年的第一天

现在用年(yy)的时间间隔来显示这一年的第一天。

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

季度的第一天

假如你要计算这个季度的第一天，这个例子告诉你该如何做。

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

当天的半夜

曾经需要通过getdate()函数为了返回时间值截掉时间部分，就会考虑到当前日期是不是在半夜。假如这样，这个例子使用DATEDIFF和DATEADD函数来获得半夜的时间点。

SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

深入DATEDIFF和DATEADD函数计算

你可以明白，通过使用简单的DATEDIFF和DATEADD函数计算，你可以发现很多不同的可能有意义的日期。

目前为止的所有例子只是仅仅计算当前的时间和“1900-01-01”之间的时间间隔数量，然后把它加到“1900-01-01”的时间间隔上来计算出日期。假定你修改时间间隔的数量，或者使用不同的时间间隔来调用DATEADD函数，或者减去时间间隔而不是增加，那么通过这些小的调整你可以发现和多不同的日期。

这里有四个例子使用另外一个DATEADD函数来计算最后一天来分别替换DATEADD函数前后两个时间间隔。

上个月的最后一天

这是一个计算上个月最后一天的例子。它通过从一个月的最后一天这个例子上减去3毫秒来获得。有一点要记住，在Sql Server中时间是精确到3毫秒。这就是为什么我需要减去3毫秒来获得我要的日期和时间。

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

计算出来的日期的时间部分包含了一个Sql Server可以记录的一天的最后时刻(“23:59:59:997”)的时间。

去年的最后一天

连接上面的例子，为了要得到去年的最后一天，你需要在今年的第一天上减去3毫秒。

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

本月的最后一天

现在，为了获得本月的最后一天，我需要稍微修改一下获得上个月的最后一天的语句。修改需要给用DATEDIFF比较当前日期和“1900-01-01”返回的时间间隔上加1。通过加1个月，我计算出下个月的第一天，然后减去3毫秒，这样就计算出了这个月的最后一天。这是计算本月最后一天的SQL脚本。

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

本年的最后一天

你现在应该掌握这个的做法，这是计算本年最后一天脚本

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

本月的第一个星期一

好了，现在是最后一个例子。这里我要计算这个月的第一个星期一。这是计算的脚本。

select DATEADD(wk, DATEDIFF(wk,0,

dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

-------------------------------------------------------------sql中根据字段不同sum统计

sum(case LeiBie when '阶段计划' then SL else 0 end) as 'JDSL',

------------------在某字段后面加字符是没有问题的

use cc

SELECT Name+'hhh',Sex,sum(Score) as 'ddd' from Score

GROUP BY Name,sex WITH ROLLUP

-----------------------------js依次打印文字；

it=0;

function typeit() {

var word = mytext.charAt(it, idstr);

if (it < mytext.length - 1) {

it++

t = setTimeout("typeit()", Math.random() * 10);

}

else {

clearTimeout(t);

it = 0;

i++;

start();

}

------------------------------js若在页面已加载就开始调用服务器端控件,对报关于对象的错！

解决方案，通过后台注册脚本方法间接调用前台方法；

-----js向标记中添加标记

累加$("#content").append("<img src='../img/bg2.jpg'/>");不累加：.html();

或者是js:.innerHTML=；累加：+=innerHTML;

----------------要让出链接之外的有北京颜色显示需要

$("li").mouseenter(function () {

$(this).attr("background-color", "#ccff66");

});

和

li:hover

{

/*background-image: url(img/menu2.gif);*/

cursor: pointer;

background-color:#ccff66;

}同时使用才有效果；

------------------------------frame去掉边框：

---------------------------------------------------------:

decimal(18,6);

-----------------------------虚拟表：#表名；

---timestamp:时间戳，二进制数据；

----WAITFOR delay '00:00:005'：数据库时间延迟

---SELECT @@rowcount;返回上一操作所影响的行数，如果大于0，可判断操作是否成功；

处理并发：悲观：（事务+锁）；乐观（时间戳）

悲观处理方式是采用SQLSERVER数据库中“事务+锁”！（隔离级别总共四种默认level1）会产生脏读；

　　先上伪代码再解释：

　　1.先解释2个要用到的锁

　　SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除（避免不可重复读），避免统一事务多次读取结果一致防止他人修改；

　　SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除

　 SELECT * FROM table WITH (updLOCK) 其他事务能读取表,但不能更新

　　PS：这2个是表级锁，要锁行加上SQL过滤条件即可。数据库的默认隔离级别是readcommit,(更改的事务未完成，其它不能读取)

也就是level1;，可读：可以查询+with(holdlock),若想不可读，with(tablockx)

2.乐观：事务+时间戳；

------------------如何避免死锁 1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；

2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；

3 优化程序，检查并避免死锁现象出现； 4 .对所有的脚本和SP都要仔细测试，在正是版本之前。

5 所有的SP都要有错误处理（通过@error） 6 一般不要修改SQL SERVER事务的默认级别。

不推荐强行加锁

------------------------------------时间戳：（微软建议用rowversion）

convert(bigint,time)，对应c#int64进行比较即可；

--------------------别面内存泄露的方法，在一个类中创建另一个类的实例时，当不用了，赋值null,这样就告诉系统，可以回收了；

------恢复数据库:

restore database guo from disk='路径备份' with recovery

------------------经典的dispose();垃圾回收gc（回收时将对象赋值为null）,告诉回收器，我已经不被引用了，随时清理；

class A:IDisposable

{

private bool isDisposed = false;

public void Dispose()

{

Dispose(true);

GC.SuppressFinalize(this);//告诉回收器清理已完成，无需调用析构函数；

}

protected virtual void Dispose(bool disposing)

{

if (!isDisposed)

{

if (disposing)

{

//释放托管资源

}

//释放非托管资源

}

isDisposed = true;

}

~A()//有回收期调用，不固定；

{

Dispose(false);

//主要清理非托管；

}

-------------------------------------文件流，数据库的连接，系统的窗口句柄，打印机资源等等网络连接，这些是非托管资源；

-----产生内存泄露的原因：

文件流，数据库的连接，系统的窗口句柄，打印机资源等等

------------

1.内存泄露的原因：

！使用静态引用；

！为退订的事件；

！为调用的Dispose方法；

！使用不彻底的Dispose方法；

等等；

内存泄露：Fabrice称，内存泄漏发生在一块内存不再被使用，但却依然被程序所引用时

使用静态引用

未退订的事件－作者认为这是最常见的内存泄漏原因

未退订的静态事件

未调用Dispose方法

使用不彻底的Dispose方法

在Windows Forms中对BindingSource的误用

未在WorkItem/CAB上调用Remove

------------

对象的创建者或拥有者负责销毁对象，而不是使用者

当不再需要一个事件订阅者时退订此事件，为确保安全可以在Dispose方法中退订

当对象不再触发事件时，应该将对象设为null来移除所有的事件订阅者

当模型和视图引用同一个对象时，推荐给视图传递一个此对象的克隆，以防止无法追踪谁在使用哪个对象

对系统资源的访问应该包装在using块中，这将在代码执行后强制执行Dispose

---------------------------

用上面的SQL语句还原备份数据库时，数据库的状态总是“正在还原”，使得不能对它进行其他操作。

解决方案：“RESTORE DATABASE " + dbName + " From disk='" + filePath + "' WITH REPLACE,RECOVERY ";

-----------一般还原数据库的话是 with replace;

----------------备份知识

1、SQL数据库恢复模型

----------------------------------------------------------------------

1）完全恢复模型

-----------------

（1）备份时要备份数据库的数据文件和日志文件

（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。

（3）能还原全部数据，并可以将数据库恢复到任意指定的时刻。

（4）为保证实现即时点恢复，对数据库的所有*作都将完整地记入日志，这样，日志占用空间较大，对性能也有所影响。

------------------

（2）大容量日志记录恢复模型

------------------

（1）备份时要备份数据库的数据文件和日志文件

（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。

（3）日志中不记录*作细节（如select into、create index等），而只记录*作的最终结果，因此占用日志空间小。

（4）只支持将数据库还原到事务日志备份的时刻，而不支持即时点恢复，因此可能产生数据丢失。

-------------------

（3）简单恢复模型

-------------------

（1）备份时只备份数据文件，还原时也用备份的数据文件恢复数据库。

（2）只能将数据恢复到数据文件备份的时刻，可能产生最多的数据丢失。

（3）不适于生产系统和大规模*作环境下选用。

-----------------------------------------

alter database d1 set recovery simple --设置数据库恢复模型

alter database d1 set recovery bulk_logged

alter database d1 set recovery full

----------------------------------------------------------------------

2、备份设备

----------------------------------------------------------------------

1）物理设备

---------------------------

disk：支持本地磁盘或者网络备份

tape：支持磁带机备份

name pipe：支持第三方备份软件

---------------------------

2）逻辑设备

---------------------------

永久备份文件：可以重复使用，应该在备份前创建。

临时备份文件：用于一次性备份，在备份时创建。

-------------------------------------------------

exec sp_addumpdevice 'disk','bak2','e:ack_deviceak2.bak' --创建永久磁盘备份设备

exec sp_addumpdevice 'disk','bak3','e:ack_deviceak3.bak'

----------------------------------------------------------------------

exec sp_addumpdevice 'disk','bak4','\sv2ackupak4.bak' --创建网络永久磁盘备份设备

exec sp_addumpdevice 'disk','bak5','\sv2ackupak5.bak'

----------------------------------------------------------------------

exec sp_dropdevice 'bak5' --删除备份设备

----------------------------------------------------------------------

backup database d3 to bak3 --将数据库备份到备份设备

backup database d4 to bak4

----------------------------------------------------------------------

restore headeronly from bak2 --查看备份设备中的内容

----------------------------------------------------------------------

backup database d3 to disk='e:ack_filed3.bak' --将数据库备份到临时备份文件

backup database d4 to disk='e:ack_filed4.bak'

----------------------------------------------------------------------

restore database d3 from bak3 --从备份设备还原数据库

restore database d4 from disk='e:ack_filed4.bak' --从备份文件还原数据库

----------------------------------------------------------------------

3、使用多个备份文件存储备份

----------------------------------------------------------------------

1）SQL可同时向多个备份文件进行写*作。如果把这些文件放到多个磁带机或磁盘中，则可提高备份速度。

2）这多个备份文件必须用同业型的媒体，并放到一个媒体集中。

3）媒体集中的文件必须同时使用，而不能单独使用。

4）可以通过format命令将媒体集重新划分，但原备份集中的数据不能再使用。

--------------------------------------------------------------------

backup database d4 to bak4,bak5,bak6 with medianame='bak456',format --备份D4并形成Media Set

backup database d3 to bak4 --失败，因Media set中文件必须同时使用

backup database d3 to bak4,bak5,bak6 --成功，将D3也备份到Media Set中

restore headeronly from bak4,bak5,bak6--查看Media Set中的备份内容

----------------------------------------------------------------------

backup database d4 to bak4 with medianame='bak4',format --重新划分Media Set

backup database d3 to bak5,bak6 with medianame='bak56',format

----------------------------------------------------------------------

backup database d1 to bak1 with init --with init重写备份设备中内容

backup database d2 to bak1 with noinit --with noinit将内容追加到备份设备中

restore headeronly from bak1

----------------------------------------------------------------------

4、备份的方法

----------------------------------------------------------------------

1）完全备份

-------------------------------------------

（1）是备份的基准。在做备份时第一次备份都建议使用完全备份。

（2）完全备份会备份数据库的所有数据文件、数据对象和数据。

（3）会备份事务日志中任何未提交的事务。因为已提交的事务已经写入数据文件中。

--------------------------------------------

backup database d1 to bak1 with init --完全备份

backup database d1 to bak1 with noinit

----------------------------------------------------------------------

2）差异备份

---------------------------------------------

（1）基于完全备份。

（2）备份自最近一次完全备份以来的所有数据库改变。

（3）恢复时，只应用最近一次完全备份和最新的差异备份。

-----------------------------------------------

backup database d2 to bak2 with init,name='d2_full' --差异备份，第一次备份时应做完全备份

create table b1(c1 int not null,c2 char(10) not null)

backup database d2 to bak2 with differential,name='d2_diff1'

insert b1 values(1,'a')

backup database d2 to bak2 with differential,name='d2_diff2'

insert b1 values(2,'b')

backup database d2 to bak2 with differential,name='d2_diff3'

insert b1 values(3,'c')

backup database d2 to bak2 with differential,name='d2_diff4'

restore headeronly from bak2

----------------------------------------------------------------------

3）事务日志备份

-------------------------------------------------------------

（1）基于完全备份。

（2）为递增备份，即备份从上一次备份以来到备份时所写的事务日志。

（3）允许恢复到故障时刻或者一个强制时间点。

（4）恢复时，需要应用完全备份和完全备份后的每次日志备份。

-------------------------------------------------------------

backup database d3 to bak3 with init,name='d3_full' --日志备份，第一次备份时应做完全备份

create table b1(c1 int not null,c2 char(10) not null)

backup log d3 to bak3 with

insert b1 values(1,'a')

backup log d3 to bak3 with

insert b1 values(2,'b')

backup log d3 to bak3 with

insert b1 values(3,'c')

backup log d3 to bak3 with

restore headeronly from bak3

----------------------------------------------------------------------

create table b1(c1 int not null,c2 char(10) not null) --Full+Log+Diff

backup log d4 to bak4 with

insert b1 values(1,'a')

backup log d4 to bak4 with

insert b1 values(2,'b')

backup database d4 to bak4 with differential,name='d4_diff1'

insert b1 values(3,'c')

backup log d4 to bak4 with

insert b1 values(4,'d')

backup log d4 to bak4 with

insert b1 values(5,'d')

backup database d4 to bak4 with differential,name='d4_diff2'

restore headeronly from bak4

----------------------------------------------------------------------

日志清除

-----------------------------------------

1）如果日志空间被填满，数据库将不能记录修改。

2）数据库在做完全备份时日志被截断。

3）如果将'Trans log on checkpoint'选项设为TRUE，则结果为不保存日志，即没有日志记录，不建议使用。

4）with truncate_only和with no_log设置日志满时清除日志

5）with no_truncate则可以完整保存日志，不清除，即使在数据文件已经损坏情况下。主要用于数据库出问题后在恢复前使用。可以将数据还原到出故障的那一时刻。

-------------------------------------------

exec sp_dboption d3

exec sp_dboption

sp_dboption 'd3','trunc. log on chkpt.','true' --设置自动清除数据库日志

sp_dboption 'd3','trunc. log on chkpt.','false' --将自动清除数据库日志的选项去除

----------------------------------------------------------------------

backup log d4 with truncate_only --设置D4日志满时清除日志，并做清除记录

----------------------------------------------------------------------

backup log d4 with no_log --设置D4日志满时清除日志，但不做清除记录

----------------------------------------------------------------------

backup log d4 to bak4 with no_truncate--在D4数据库损坏时马上备份当前数据库日志(DEMO)

--------

使用no_truncate

完全+修改1+差异+修改2+差异+修改3+停止SQL，删除数据库数据文件+重启SQL

backup log no_truncate

再还原，可还原到修改3

-----------------------------数据库恢复顺序；

恢复数据库中的数据，所以通常恢复的时间较长；先恢复最后一次全库备份，再恢复最后一次差异备份，

再顺序恢复最后一次差异备份以后进行的所有事务日志备份

------------------数据库四种备份；

1）完全数据库备份

完全数据库备份是备份数据库的所有表的数据及模式，以及对应的文件结构，包括对事务日志中的事务进行备份。它可以用作系统出现故障时恢复数据库的基础，也可用于在另外一台计算机上生成数据库的副本。

（2）差异数据库备份

差异数据库备份只记录自上次完全数据库备份后发生变化的数据。差异备份的数据量比完全备份的数据量小而且备份速度快，因此可以更经常地备份。

（3）事务日志备份

事务日志包含了自上次进行完全数据库备份、差异数据库备份或事务日志备份以来所完成的事务。可以使用事务日志备份将数据库恢复到特定的即时点或恢复到故障点。

（4）数据库文件和文件组备份

只备份特定的文件或文件组，常用于超大型数据库的备份。

---------------------------------------------------------数据并发，其多个用户修改其它用户正在使用的资源，并产生了一定的影响；

level0:(read uncommitted)；修改未提交时，便可以读取；---导致脏读；解决方案：SET tran isolation level read uncommitted

个事务读到另外一个事务还没有提交的数据，我们称之为脏读

level1(read committed):(sql默认，(修改的过程中不允许读和修改)修改时(修改的进程还没结束)，另一进程不能读取)read committed;---导致不可重复读：解决方案:读取的事务中设置SET tran isolation level repeatable read;

一个事务先后读取同一条记录，但两次读取的数据不同，我们称之为不可重复读。读取的过程中（5秒钟内），有其它事务修改了数据；

level2:（repeatable read）,{原理：进行了s锁，作用（小于with(holdlock),因为，le2不允许修改，所以不会出现不可重复读，但出现幻读，可以插入，而holdlock不允许，这就是区别）

}解决方案：set tran isolation level serializable;:串行化（个人认为是holdlock原理）：

一个事务先后读取一个范围的记录，但两次读取的纪录数不同，我们称之为幻象读；（幻读，读的时候有数据插入，不可重复读，读的时候有数据修改）

level3:(serializable)串行化:避免了幻读；

-------更改隔离类别；

BEGIN transaction

SET tran isolation level read uncommitted

update -----

IF @@error=0

COMMIT transaction

else

ROLLBACK transaction

-------------------------------默认的level1:在修改中不允许修改和查询，单允许插入；（插入不会引起不一致和完整性）

----------with(holdlock):查询时，不允许修改和插入操作，允许查询；

----------with(tablockx):查询时什么都不允许；

-------------------这两个锁只能应用在查询上；

总结

事务隔离级别是通过数据库的锁机制来控制的，在不同的应用场景需要应用不同的事务隔离级别，

SQL Server默认的事务隔离级别是READ COMMITTED，默认的隔离级别，已经可以满足我们大部分应用的需求。

--------------------------行级锁：锁的粒度小；不想表级锁，updlock,告诉大家，标记我整个事务将要更改，你们不要进行操作；

----------------------------------------------

--判断是否数据库存在某存储过程

use master

select * from sysobjects where id = object_id(N'[killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1

--判断是否存在某表

use cc

DECLARE

select * from sysobjects where id = object_id(N'[AA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1

--判断数据库是否存在

select * from sys.databases where name ='CC'

--判断是否存在临时表,若存在删除

CREATE table #a

(

id int,

name char(10)

)

SELECT * FROM #a

if object_id('tempdb..#a') is not null

drop table #a

--判断表中某列是否存在

USE CC

select * from syscolumns where id=object_id('AA') and name='A'

-------------------------------------------------

-----------------------结束某数据库的全部进程；（结束进程）游标，查询所有的数据库连接进程；

USE master

DECLARE @dbname varchar(20)

SET @dbname='CC'

declare @sql nvarchar(500)

declare @spid int

set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')'

EXEC(@sql)

open getspid

fetch next from getspid into @spid

while @@fetch_status =0

begin

exec('kill '+@spid)

fetch next from getspid into @spid

end

close getspid

deallocate getspid

--------------------------------------------获取表中的列名；

dr.GetName(1);sqldatareader;

---------------------------缓存：cache;

public static object GetCache(string CacheKey)

{

System.Web.Caching.Cache objCache = HttpRuntime.Cache;

return objCache[CacheKey];

}

/// <summary>

/// 设置当前应用程序指定CacheKey的Cache值

/// </summary>

/// <param name="CacheKey"></param>

/// <param name="objObject"></param>

public static void SetCache(string CacheKey, object objObject)

{

System.Web.Caching.Cache objCache = HttpRuntime.Cache;

objCache.Insert(CacheKey, objObject);

}

----------------------------将对象序列化；

User u = new User() { Age=1,Name="郭泽峰"};

var str = new System.Web.Script.Serialization.JavaScriptSerializer();

var ff=str.Serialize(u);//字符串形式的json对象；

TextBox1.Text = ff;

------------------------------ ScriptManager.RegisterStartupScript的命名空间：web.extension;中；

---------------------加入收藏夹；

window.external.addFavorite('http://zfsoftware.tk', '麦兜网')

----------设为首页；

-------------------------------------------------------ajax测试服务webjison

(1)调用web服务的user对象；提取方法guo千万不要加（）；success:guo;

$.ajax({

type: "Post",

url: "WebService.asmx/getuser",

contentType: "application/json; charset=utf-8",

dataType: "json",

success: function (obj) {

alert(obj.d.Name);

error: function (err) {

alert(err);

}

});

-----------自带的方法；（没有d），

WebService.getuser(function (result) {

alert(result.Name);

},null,null)

(2)--------------调用list<int>,数组

alert(obj.d[0]);

------------------

alert(result[1]);

(3)----------调用idictionary集合；

alert(obj.d[“a”].Name);

------------------

alert(result["a"].Name);

(4)调用list<user>对象；

alert(obj.d[0].Name);

for (var s in obj.d) {

$("#guo").append("<tr><td>"+obj.d[s].Name+"</td></tr>");

}

----------------------------------序列化；（json）

var str = new System.Web.Script.Serialization.JavaScriptSerializer();

TextBox1.Text =str.Serialize(u);

-------------js反序列化；

success: function (result) {

var json = JSON.decode(result.d);

alert(json.Name);

--------------------当webservice出现方法重载时；

[WebMethod( MessageName="guo")]在上面添加这个，一味着此方法的名字变为了guo,客户端只要调用guo就行了；

----------

------------------js调用后台方法；

function Button1_onclick() {

var a = " <% =getstr()%>";

alert(a);

}

-------------------------------sql事务现成的案例：

StringBuilder strSql = new StringBuilder();

strSql.Append("begin transaction");

strSql.Append(" DECLARE @inwages decimal SET @inwages=0.0 ");

strSql.Append(" DECLARE @outwages decimal set @outwages=0.0");

strSql.Append(" select @inwages=sum(Wages)from GroupWages where IdCard=@idcard");

strSql.Append(" SELECT @outwages=sum(AllWages) FROM PayWages WHERE IdCard=@idcard");

strSql.Append(" SELECT @inwages-@outwages");

strSql.Append(" IF @@error=0 commit transaction else rollback transaction ");

SqlParameter[] parameters ={

new SqlParameter("@idCard",idCard)

};

------------------------------返回本月的第一天

txtBeginTime.Text = DateTime.Today.AddDays(-DateTime.Today.Day + 1).ToString("yyyy-MM-dd");

-----------------用户自定义控件

用户控件调用父页面的方法:

//获得父页面

Page p = this.Parent.Page;

Type pageType = p.GetType();

//父页面的方法名

//System.Reflection:要添加此命名空间；

MethodInfo mi = pageType.GetMethod("Loading");

//执行

mi.Invoke(p, new object[] { "参数1","参数2" });

用户控件与用户控件之间调用:

//获得父页面

Page p = this.Parent.Page;

//获得父页面的子控件

UserControl uc = p.FindControl("tj_ReceiptList2") as UserControl;

Type pageType = uc.GetType();

//父类方法名

MethodInfo mi = pageType.GetMethod("Loading");

//参数

mi.Invoke(uc, new object[] { "参数1", "参数2" });

用户控件与用户控件之间设置属性:

//获得父页面

Page p = this.Parent.Page;

//获得父页面的子控件

UserControl uc = p.FindControl("aabb1") as UserControl;

Type pageType = uc.GetType();

//父类方法名

PropertyInfo mi = pageType.GetProperty("属性名称");

//mi.Invoke(uc, new object[] {});

mi.SetValue(uc,属性值,null);

--------------------------------------------身份验证登陆；（这两种形式都可以；）

System.Web.Security.FormsAuthentication.SetAuthCookie("a", false);

//System.Web.Security.FormsAuthentication.RedirectFromLoginPage("a",false);

System.Web.Security.FormsAuthentication.SignOut();

User.Identity.IsAuthenticated

--------------------------------------------mysql案例：

MySqlConnection cn = new MySqlConnection("data source=localhost;initial catalog=gzf;uid=root;pwd=sa;persist security info=True;");

// "server=localhost;user id=root;password=;database=guestbook"

//MySqlConnection cn = new MySqlConnection("server=localhost;user id=root;password=sa;database=gzf");

MySqlDataAdapter da = new MySqlDataAdapter(" select * from users", cn);

DataSet ds = new DataSet();

da.Fill(ds);

this.dataGridView1.DataSource =ds.Tables[0];

--------------------------------返回上一页

ViewState["preurl"]= Request.UrlReferrer.ToString();

Response.Redirect(ViewState["preurl"].ToString());

---------------------------------------------------------实体中的datetime类型的默认值不为空，而是datetime.minvalue;(时间)

-------------------------sqlin拼接：

存储过程中：1.转化，2，用exec进行拼接；在c#直接拼接不能用软绑定；

-----------------------路径转换

protected static string Transfer(string str)

{

string[] strs = str.Split('/');

if (str != "" && strs[strs.Length - 1].Length >= 7)

{

return strs[strs.Length - 1].Substring(0, 7);//获得路径下文件的名称

}

else

return strs[strs.Length - 1];

}

protected static string TransferPath(string str)

{

return ("." + str.Replace('~', '.'));//转换成下载路径

}

---------------------------复制表（）

DataTable dt2 = new DataTable();

dt2 = dt.Copy();

dt2.Rows.Clear();

dt2.ImportRow(dt.Rows[0]);//这是加入的是第一行

------------------------------------------------------------------序列化与反序列化

JavaScriptSerializer Serializer = new JavaScriptSerializer();

Dictionary<string,string> objs = Serializer.Deserialize<Dictionary<string,string>>("{a:'guo',b:'ze'}");

//List<aa> s = new List<aa>();

//s = JSONStringToList<aa>("[{name:'guo'},{name:'ze'}]");

//TextBox1.Text = s[1].name

}

public static List<T> JSONStringToList<T>(string JsonStr)

{

JavaScriptSerializer Serializer = new JavaScriptSerializer();

List<T> objs = Serializer.Deserialize<List<T>>(JsonStr);

return objs;

}

public static List<T> JSONStringToList<T>(string JsonStr)

{

JavaScriptSerializer Serializer = new JavaScriptSerializer();

List<T> objs = Serializer.Deserialize<List<T>>(JsonStr);

return objs;

}

--------------------缓存依赖

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class Default4 : System.Web.UI.Page

{

/// <summary>

/// 获取当前应用程序指定CacheKey的Cache对象值

/// </summary>

/// <param name="CacheKey">索引键值</param>

/// <returns>返回缓存对象</returns>

public static object GetCache(string CacheKey)

{

System.Web.Caching.Cache objCache = HttpRuntime.Cache;

return objCache[CacheKey];

}

/// <summary>

/// 设置以缓存依赖的方式缓存数据

/// </summary>

/// <param name="CacheKey">索引键值</param>

/// <param name="objObject">缓存对象</param>

/// <param name="cacheDepen">依赖对象</param>

public static void SetCache(string CacheKey, object objObject, System.Web.Caching.CacheDependency dep)

{

System.Web.Caching.Cache objCache = HttpRuntime.Cache;

objCache.Insert(

CacheKey,

objObject,

dep,

System.Web.Caching.Cache.NoAbsoluteExpiration, //从不过期

System.Web.Caching.Cache.NoSlidingExpiration, //禁用可调过期

System.Web.Caching.CacheItemPriority.Default,

null);

}

protected void Page_Load(object sender, EventArgs e)

{

string CacheKey = "cachetest";

object objModel = GetCache(CacheKey);

//从缓存中获取

SELECT * FROM QualityCheck WHERE len(replace(Ensure,' ',''))>0 if (objModel == null) //缓存里没有

{

objModel = DateTime.Now;//把当前时间进行缓存

if (objModel != null)

{

//依赖 C:\test.txt 文件的变化来更新缓存

System.Web.Caching.CacheDependency dep = new System.Web.Caching.CacheDependency("C:\test.txt");

SetCache(CacheKey, objModel, dep);//写入缓存

}

Label1.Text = objModel.ToString();

}

-----------------------判断字段是否有值且不为空；

SELECT * FROM QualityCheck WHERE len(replace(Ensure,' ',''))>0

---------------------------------------------------------------------------------------------------------js总结

1. ---------------

-var aa = new Array();

aa.push(1);

alert(aa[0]);

var aa=[];也表示一个数组；

--------------------------

var a = "sdfsfs";

if (typeof (a) == "string")

alert("shfidfodsf");

else if (typeof (a) == "undefined")

alert("未定义");

-----------------通过构造函数属性来判断类型；分割字符串

var gg = "gu,oze,ng";

var aa = gg.split(",");

if(aa.constructor==Array)

alert(aa[2]);

-------------------归并字符串；

var aa = new Array(1, 2, 3, 4);

var b = aa.join("-");

alert(b);

--------------------------

$(document.body).css("background-color","Red");

---------------------：切记没有分号，px后面，否则无效果；

$("li").each(function () {

$(this).css("background-color", "green");

$(this).css("font-size","20px");

});

--------------

$("li:eq(3)").css("background-color", "red");匹配第四个li;

------------

$("li:contains('b')").css("background-color",
"red");内容含有b的标签；

---------------

//$("ul").css("background-color","red");

//$("ul").attr("style", "background-color:red; font-size:20px;");

//$("ul").attr({ style: "background-color:red;",style: "background-color:red;"});

$("ul").attr("class", "lii");

$("ul").removeAttr("class");

-------------------------

$("p").addClass("selected highlight");

------------------获得索引：

.index();从0开始，返回一个数字；

-----------有的话，删除，没有的话添加；

$("ul").click(function () {

$(this).toggleClass("lii");

});

--------------not:除此之外的；

$("li").not($("li:last")).css("background-color", "red");

-------------2,3只选择第三个，2,4只选择第三和第四个，索引从0开始；当slice(2),表示从第三个到最后一个；

$("li").slice(2，3).css("background-color", "red");

---------------------lt:前四个；

$("li:gt(4)").css("background-color", "red");

-------所有的li;

$("ul").children().css("background-color", "red");

--------class为dd的类；

$("ul").children(".dd").css("background-color", "red");

-------------所有的同级元素；

$("#f").nextAll().css("background-color", "red");

-----------挨着的第一个同级元素；

$("#f").next().css("background-color", "red");

-----------同级直到table标记之间的同级标记，不包括table;

$("#f").nextUntil("table").css("background-color", "red");

-------------找到所有父级的标记；

$("#nei").parents().css("background-color","red");

-------------应该只有上一级；

$("#nei").parent().css("background-color","red");

-------------

siblings:除此之外的所同级元素；

---------相当于累加；

$("#nei").add("table").add("#f").css("background-color","red");

-----------并把自己也加上；

$("div").find("p").andSelf().addClass("border");

--------------将所有元素添加到b标签中；

$("#nei").contents().wrap("<b/>");

-------------在p标签的前面添加标记；append是后面；

$("p").prepend("<b>Hello</b>");

--------------.after,.before,insertafter,insertbefore;

------------将元素包含到另一元素中；

$("p").wrap(document.getElementById('content'));

------------

将所有匹配的元素用单个元素包裹起来时使用wrapall();

---------------------------:在p的内部添加b;(将b的内容用b抱起来)

$("p").wrapInner("<b></b>");

-----------变换标记；

$("p").click(function () {

$(this).replaceWith("<img src='../img/_8-1.png'></img>");

-----------

});

---------------------复制一份添加到标记中

$("b").clone().prependTo("p");

---------------------

将所有段落的字体颜色设为红色并且背景为蓝色。

jQuery 代码:

$("p").css({ color: "#ff0011", background: "blue" });描述:

如果属性名包含 "-"的话，必须使用引号:

jQuery 代码:

$("p").css({ "margin-left": "10px", "background-color": "blue" });

-----------------------不管怎么样：键值都加双引号；

$("#f").css({"color":"red","background":"blue"});

----------------------------------------------------------offset相对document的位置；

-----------设定移量；

$("#xuan").offset({top:200,left:400});

-------------得到相对位置

var a = $("#xuan").offset();

alert(a.left+"----"+a.top);

--------------------------------------------------------------position(),相对于父元素的偏移量；用法和offset一样；

------blur():失去焦点触发的事件；

-----change()事件:当内容改变且失去焦点时触发；

-------------滚动条滚动

$(window).scroll(function () {

setTimeout();

});

-----------序列化操作：

var a = $.parseJSON('{ "name": "guozeng" }');

alert(a.name);

---------------------

------------------------textbox下拉框案例

$(function () {

$("#Text1").focus(function () {

//$("#gg").attr("display","block");

$("#gg").css("display", "block");

$("#gg").offset({ top: $("#Text1").offset().top + $("#Text1").height() + 5, left: $("#Text1").offset().left });

});

$("li").click(function () {

$("#Text1").val($(this).text());

$("#gg").css("display", "none");

});

})

-------------------pannel案例：

$(function () {

$("img[alt='a']").click(function () {

if ($(this).attr("src") == "../img/_8-1.png") {

$(this).attr("src", "../img/_8-2.png")

$("#gg").slideUp(500);

}

else {

$(this).attr("src", "../img/_8-1.png")

$("#gg").slideDown(500);

}

});

})

------------------------------------------------------------------------------------------------------------------

1.new 父类方法：相当于复制了父类的方法，通过比对，不是重写，所以没有记录到方法表的重写表上。当父类引用指向子类实例时，调用该方法先去重写表上找，如果没找到，说明没被重写，所以继续调用父类的方法；

2.继承，实际上是：先将父类复制一份分配内存，在和子类比对，将重写的方法写在loader heap的方法表上，当父类指向子类实例，先检查方法表是否存在重写方法，没有的话，继续调用父

类的。

3,栈：从高位到低位，控件有限，堆，从低位到高位，回收时，无论栈还是堆，都从低位开始回收，所有就有的先进先出，先进后出之分；

4.进程栈，托管堆：gc堆，存储属性，loader heap:存储方法表、静态变量;生命周期：appdomain结束（应用程序域结束），当>85K时，存在loh上（large object heap）

5.类的静态放在loader heap上，先于实例创建。属性，无论引用还是值都创建在堆上。方法里的值创建在进程栈上。值类型中的引用对象的创建，还保持原来一样，引用在栈，创建在堆上。

-----------------------------------------------

1：不管是重写还是覆盖都不会影响父类自身的功能（废话，肯定的嘛，除非代码被改）。

2：当用子类创建父类的时候，如 C1 c3 = new C2()，重写会改变父类的功能，即调用子类的功能；而覆盖不会，仍然调用父类功能。

3：虚方法、实方法都可以被覆盖（new），抽象方法，接口不可以。

4：抽象方法，接口，标记为virtual的方法可以被重写（override），实方法不可以。

5：重写使用的频率比较高，实现多态；覆盖用的频率比较低，用于对以前无法修改的类进行继承的时候。

------------------------------------------soap序列化；

class Program

{

static void Main(string[] args)

{

//与二进制一样

Company com = new Company();

com.CompanyName = "软件开发公司";

com.CompanyID = "1234";

com.Office = "沈阳";

XmlTypeMapping map = (new SoapReflectionImporter().ImportTypeMapping(typeof(Company)));

XmlTextWriter writer = new XmlTextWriter(@"C:UsersdellDesktop123aa.soap",Encoding.UTF8);

writer.Formatting = Formatting.Indented;

XmlSerializer xs = new XmlSerializer(map);

writer.WriteStartElement("公司信息");

xs.Serialize(writer, com);

writer.WriteEndElement();

writer.Close();

XmlTextReader reader = new XmlTextReader(@"C:UsersdellDesktop123aa.soap");

reader.ReadStartElement();

Company ccom;

ccom =(Company)xs.Deserialize(reader);

reader.ReadEndElement();

reader.Close();

Console.WriteLine(ccom.ToString());

Console.Read();

}

------------------

using System.Xml.Serialization;

namespace Soap协议

{

public class Company//必须是共有的；

{

[SoapAttribute(AttributeName="名称")]

public string CompanyName;

[SoapElement]//默认名称

public string Office;

[SoapIgnore]

public string CompanyID;

public override string ToString()

{

string m;

m = "公司名称：" + CompanyName + " ";

m += "公司地址：" + Office + " ";

m += "公司编号：" + CompanyID;

return m;

}

------------------------------xml序列化

{

class Program

{

static void Main(string[] args)

{

//条件：对象必须执行一次不带残的构造函数且必须共有的

Meetion m = new Meetion("ATA");

Meetion mm;

m.roomNum = 505;

m.invitees = new string[] {"smith","john" };

XmlSerializer xs = new XmlSerializer(typeof(Meetion));

StreamWriter sw = new StreamWriter(@"C:UsersdellDesktop123xml.txt");

xs.Serialize(sw,m);

sw.Close();

StreamReader sr = new StreamReader(@"C:UsersdellDesktop123xml.txt");

mm=(Meetion)xs.Deserialize(sr);

Console.WriteLine(mm.roomNum);

}

[XmlRoot(ElementName="会议")]

public class Meetion

{

private string title;

[XmlAttribute(AttributeName="房间号")]

public int roomNum;

[XmlIgnore]

public string[] invitees;

public Meetion()

{

}

public Meetion(string t)

{

title = t;

}

---------------------------二进制序列化：

using System.Runtime.Serialization.Formatters.Binary;//二进制序列化命名空间

using System.Collections;

using System.IO;

namespace 二进制序列化

{

class Program

{

static void Serialize()

{

Hashtable t = new Hashtable();

//反序列化进行自动排序int升序，字符串降序；

t.Add(1, "沈阳");

t.Add(4, "抚顺");

t.Add(3, "北京");

BinaryFormatter f = new BinaryFormatter();

FileStream fs = new FileStream(@"C:UsersdellDesktop123as.txt", FileMode.Create);

f.Serialize(fs, t);

fs.Close();

}

/// <summary>

/// 反序列化

/// </summary>

static void DeSerialize()

{

Hashtable ht = null;

BinaryFormatter f = new BinaryFormatter();

FileStream fs = new FileStream(@"C:UsersdellDesktop123as.txt", FileMode.Open);

ht=(Hashtable)f.Deserialize(fs);

foreach( DictionaryEntry item in ht)

{

Console.WriteLine( item.Key.ToString()+item.Value);

}

static void Main(string[] args)

{

Serialize();

DeSerialize();

Console.Read();

}

---------------------------------------------打印杨辉三角

//打印杨辉三角

static void print(int n)

{

int mm = 0;

Queue<int> q = new Queue<int>();

Console.Write(space(n));//输出空格

for (int i = 1; i <= n; i++)

{

q.Enqueue(1);

for (int j = 1; j < i; j++)

{

mm = q.Dequeue();

Console.Write(mm + "-");//依次输出每行数字

q.Enqueue(mm + q.Peek());

}

Console.Write(q.Peek());//输出每行末尾数字 1

Console.WriteLine();//开始打印下一行

Console.Write(space(n - i));//输出下一行空格

}

Console.Read();

}

---------------------------------------------线程：

using System.Threading;

namespace Thread类

{

class Program

{

//static void Main(string[] args)

//{

// //帮助器委托不带参数

// ThreadStart ts = new ThreadStart(DoWork)

// for (int i = 1; i <= 3; i++)

// {

// Thread t = new Thread(ts);

// t.Name = "线程"+i.ToString() ;

// t.Start();

// }

// Console.Read();

//}

//static void DoWork()

//{

// for (int i = 0; i <= 9; i++)

// {

// //不给名字的话，默认为空；

// Console.WriteLine(Thread.CurrentThread.Name+"--"+i.ToString());

// Thread.Sleep(10);

// }

//}

//带参数

static void Main(string[] args)

{

//帮助器委托带参数

ParameterizedThreadStart ts = new ParameterizedThreadStart(DoWork);

for (int i = 1; i <= 3; i++)

{

Thread t = new Thread(ts);

t.Name = "线程" + i.ToString();

t.Start(5);//在这里指定参数（隐士转换）

}

Console.Read();

}

static void DoWork(object obj)

{

for (int i = 1; i <=(int)obj; i++)

{

//不给名字的话，默认为空；

Console.WriteLine(Thread.CurrentThread.Name + "--" + i.ToString());

Thread.Sleep(10);

}

-------------------------------异步：

class Program

{

public delegate int MathDelegate(int x, int y);

static void Main(string[] args)

{

//IAsyncResult

//异步编程一定要创建委托

int a = Convert.ToInt32(Console.ReadLine());

int b = Convert.ToInt32(Console.ReadLine());

Class1 c1 = new Class1();

MathDelegate md=new MathDelegate(c1.add);

IAsyncResult r = md.BeginInvoke(a, b, null, null);

while (!r.IsCompleted)

{

Console.WriteLine("我正在等结果，正在异步操作");

Console.ReadLine();

}

int sum = md.EndInvoke(r);

Console.WriteLine(su:m);

Console.ReadLine();

}

class Class1

{

public int add(int x, int y)

{

Thread.Sleep(5000);

return x+y;

}

--------------------------------回调函数：

delegate int FDeletate(int n);

static FDeletate f;

static void Main(string[] args)

{

f = new FDeletate(Fibonacci);

AsyncCallback callback = new AsyncCallback(Display);//回调委托

int n = Convert.ToInt32(Console.ReadLine());

f.BeginInvoke(n, callback,"ggds");

Console.ReadLine();

}

static int Fibonacci(int n)

{

if (n <= 1)

{

return 0;

}

else if (n == 1 || n == 2)

{

return n;

}

else

return Fibonacci(n - 1) + Fibonacci(n - 2);

}

static void Display(IAsyncResult r)

{

int result = f.EndInvoke(r);

Console.WriteLine("第"+r.AsyncState+"项值是"+result);

Console.Read();

}

----------------

Thread类有几个至关重要的方法，描述如下：

Start()：启动线程；

Sleep(int)：静态方法，暂停当前线程指定的毫秒数；

Abort()：通常使用该方法来终止一个线程；

Suspend()：该方法并不终止未完成的线程，它仅仅挂起线程，以后还可恢复；

Resume()：恢复被Suspend()方法挂起的线程的执行；

------只要是聚合函数就可以用case when;group by 后面可以加t-sql函数；

SELECT count(CASE WHEN CContent>4 THEN NULL ELSE 1 END) FROM DailyCheckRecord GROUP BY datepart(year,CDate)

--------------------------

select count(0):查询结果为1；

----------------------------------------(1):

[ServiceContract(Name = "name_IUser", Namespace ="http://localhost:3232/wpf/aa")]

-------------------------------------------(2)

[OperationContract(Name = "name_GetName", Action = "http://localhost:3232/wpf/aa/ServiceContract/GetName")]

--------------------------------(3)

using System.Runtime.Serialization;//datacontract；由此而来；需要添加引用的

--------------------李氏替换；不会剖出异常；

//[KnownType(typeof(DataItem.SonDataUser))]；(体现的是一种多态行处理)

------------------------（4）

//isrequired:是否必须初始化；order；序列化时顺序；

[DataMember(Name="name_Name",IsRequired=false,Order=0)]

----------------------------------------------------------

object.Equals("0001/1/1 0:00:00", DateTime.MinValue.ToString()

-----------------------------------------------------------------------

property与attribute的区别：

property:有getset;button按钮的宽度和高度；

attribute:比如：[serializable]

---------------------------------------------------------调用web服务生成成一步操作：必须这样：（异步调用）

protected void Button1_Click(object sender, EventArgs e)

{

ServiceReference1.WebServiceSoapClient a = new ServiceReference1.WebServiceSoapClient();

a.HellowAsync(new ServiceReference1.HellowRequest(new ServiceReference1.HellowRequestBody(1)));

a.HellowCompleted += new EventHandler<ServiceReference1.HellowCompletedEventArgs>(a_HellowCompleted);

}

public void a_HellowCompleted(object sender, ServiceReference1.HellowCompletedEventArgs e)

{

TextBox1.Text ="sff";

}

------------------同步调用：

ServiceReference1.WebServiceSoapClient a = new ServiceReference1.WebServiceSoapClient();

//ServiceReference1.HellowResponse aa = a.Hellow(new ServiceReference1.HellowRequest(new ServiceReference1.HellowRequestBody(2)));

//MessageBox.Show(aa.Body.HellowResult.ToString());

------------------------------------------------------------------------

if ($("#RadioButton1").attr("checked") ==true) {

alert("郭泽峰呢个");

}

------------------------jquery获取dropdownlist的值；

alert($("#dropProperty").find('option').filter(':selected').text());

var chec kText=$("#select_id").find("option:selected").text(); //获取Select选择的Text

-----------

if ($("#RadioButton1").attr("checked") == true) {

isBid = "是";

}

------------------------js截取字符串；

var param = hrefStr.substring(hrefStr.indexOf('?') + 1, hrefStr.length);

-------------

var aa = $("a[href='/AjaxPaging/UpdataModel/" + hdnId + "']").parent().parent().find("td");

$(aa).slice(0, 1).text(name);

------------------------进程的Process的命名空间（using System.Diagnostics;）意思是诊断的意思；

var ff = new Function("p1", "alert(p1)");

ff(2);

---------------------------

for(var a in arr)

{

alert(a);

}

--------------w3c标准事件；（Ie不支持，其它支持）(注意两个地方不一样；)

document.addEventListener("click", f1);

-------------ie;

document.attachEvent("onclick",f1);

------------

window.event.clientX,clientY;鼠标在文档的位置；

window.event.screenX,screenY;鼠标在浏览器的位置；

window.event.offsetX,offsetY;相对于事件源的位置；

window.event.returnValue=false;取消默认行为；如提交按钮不会提交；

----------------取消事件冒泡：

window.event.cancelBubble=true;

-----------动态创建：

window.onload = function () {

var btn = document.createElement("input");

btn.name = "guo";

btn.type = "button";

btn.onclick = function () {

alert("我去你大爷");

};

btn.value = "动态创建";

var dv = document.getElementById("mm");

dv.appendChild(btn);

};

------------document.getelementbyid("").style.background="";

------------

onload与onready的区别：

onload,等documnet和css,js外部文件加载完之后才执行；只能注册一次；

ready:只等document加载完毕之后才执行；可注册多此；可以提高响应速度；

----------

map:对数组遍历；

each:对字典的遍历；

---------------------

// $.each(person, function (key, value) {

// alert(key+"--"+value);

> // }, null);

$.each(person, function () {alert(this) });//this，指的是值，因为我们更对值感兴趣；

=--------------.next("div"):紧邻的第一个；如果没挨着；放回空；

-----。end（）,方法返回上一次被破坏的包装集，比如：$("#id").nextall().end(),指的是$("#id")

$("#id").next().andSelf()//报错自己和挨着的；

------去掉第一行：

$("#id tr:not(:first)");去掉第一行；

$("#id tr:not(:first):not(:last):even");各行设置；

-----------------全选；

$("#checkall").click(function () {

$(":checkbox[id*=checkbox]").attr("checked", $(this).attr("checked"));

});

---------------------------05594054 0371

---动态创建子元素；

var a=$("<a href='sdf'></a>")

a.css();

a.click();

$().append(a);

----------------remove：会移除对象的事件，但不会移除对象的样式；

appendto();就相当于剪切粘贴；

----------$("#id").unbind();移除所有的事件；

.one("click",function(){});注册一次性事件，用完后随即unbind();如果没有参数，就卸载所有的事件；

--------------------------------------------

$("#Button1").bind("click", function () {

alert(1);

});

$("#Button1").unbind("click");

-----------------------------------------------切换事件；第一次点击显示1，第二次显示2；

$("#Button1").toggle(function () { alert(1)}, function () { alert(2) });

--------------事件参数可以防止事件冒泡；

$("#id").click(function(e){

alert("郭");

e.stopPropagetion();

});

这样在id之外的标签事件就不会触发；

$("#id").click(function(e){

alert("郭");

e.preventDefault();//取消控件的默认行为；与return false;不同的是，后续代码继续执行；比如取消连接的跳转功能；

alert("则");

});

-------------------

$(document).mousemove(function(e){

$("#di").text(e.pageX+"=="+e.pageY);//获取文档的坐标；

});

----------

$(document).mousemove(function(e){

$("#di").css({"left":e.pageX,"top":e.pageY});//获取文档的坐标；

});

-----------------扩展方法：

$.extend({ "max": function (a, b) {

if (a > b)

return a;

}, "min": function () {

if (a < b)

return a;

}

------------------扩展实例方法：

$.fn.extend({"checked":$(this).attr("checked","true")});

$(function () {

alert($.max(55,34));

})

------调用： $("").checked();

----------------------------------

$(window).width();

----------------------cookie:(需要引入cookie.js)

$.cookie("name",value,{expires=10})

----------------------------------frame;

window.parent.afterEdit();

var childWindow = $("#frameEdit")[0].contentWindow; //怎么来拿frame标签的一个window对象

childWindow.submitEditFrm();

---------------------数据绑定；gridview;

DataBinder.Eval(Container.DataItem,"Id")

--------------------------------------------------引用样式表；css嵌套引用另一个css文件；

@import url(cs1.css);

-------------------------

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<asp:PostBackTrigger ControlID="btnExport" />

</Triggers>

------------------------------------------必须这样：

if (!object.Equals(reader["BidDate"], null) && !object.Equals(reader["BidDate"],DBNull.Value))

或

if (!object.Equals(reader["BidDate"], null) &&reader["BidDate"].tostring()!="")

-------------------------------------------------------------------

类的。

3,栈：从高位到低位，控件有限，堆，从低位到高位，回收时，无论栈还是堆，都从低位开始回收，所有就有的先进先出，先进后出之分；

4.进程栈，托管堆：gc堆，存储属性，loader heap:存储方法表、静态变量;生命周期：appdomain结束（应用程序域结束），当>85K时，存在loh上（large object heap）

---------------

----------------导入excel:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using Excel;

namespace PM.Frame

{

public class DataFromExcel

{

public static System.Data.DataTable DataTableforExcel(string strExcelFileName, string strSheetName)

{

System.Data.DataTable dt = new System.Data.DataTable();

for (int i = 0; i < 19; i++)

{

dt.Columns.Add(new DataColumn());

}

Excel.Application excel = new Excel.Application();

Excel.Workbook m_objBook = (Excel.Workbook)(excel.Workbooks.Add(strExcelFileName));

Worksheet sheet = null;

foreach (Worksheet wsheet in m_objBook.Worksheets)

{

if (wsheet.Name == strSheetName)

{

sheet = wsheet;

break;

}

if (sheet != null)

{

int row = 2;

while (true)

{

Range rName = sheet.Cells[row, 1] as Range;

if (rName.Text.ToString().Trim().Length == 0)

{

break;

}

DataRow dr = dt.NewRow();

for (int i = 0; i < 19; i++)

{

Range rContent = sheet.Cells[row, i + 1] as Range;

dr[i] = rContent.Text;

}

dt.Rows.Add(dr);

row += 1;

}

excel.Quit();

return dt;

}

//---======================================显示网页图表icon

-------------------------------------------------------------------------------------

//是否选中

if ($("#CheckBox1").attr("checked"))

{

}

---------------------------------------------------------------pm导出excel:

//导出报表

protected void btnExport_Click(object sender, EventArgs e)

{

//查询条件获得数据集合；

System.Data.DataTable ta = qm.GetAllTable(ViewState["queryModel"] as PCardBorrow);

if (ta == null || ta.Rows.Count== 0)

{

Messabox.ShowError(this, "无记录导出");

return;

}

string name = "~/File/ExcelFile/证件借阅台账" + Session.SessionID + ".xls";

string savePath = Server.MapPath(name);

bool f = ExportExcel(name,ta, savePath);

FileInfo DownloadFile = new FileInfo(savePath); //设置要下载的文件

Response.Clear(); //清除缓冲区流中的所有内容输出

Response.ClearHeaders(); //清除缓冲区流中的所有头

Response.Buffer = false; //设置缓冲输出为false

//设置输出流的 HTTP MIME 类型为application/octet-stream

Response.ContentType = "application/octet-stream";//将 HTTP 头添加到输出流

Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("证件借阅台账.xls", System.Text.Encoding.UTF8));

//Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());//将指定的文件直接写入 HTTP 内容输出流。

//Response.WriteFile(DownloadFile.FullName);

Response.WriteFile(savePath);

Response.Flush(); //向客户端发送当前所有缓冲的输出

File.Delete(savePath);//删除文件

Response.End(); //将当前所有缓冲的输出发送到客户端

}

//导出报表

public bool ExportExcel(string reportName,System.Data.DataTable ta, string saveFileName)

{

if (ta == null)

{

return false;

}

bool fileSaved = false;

Excel.Application m_objExcel = new Excel.Application();

if (m_objExcel == null)

{

return false;

}

Excel.Workbooks m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

Excel.Workbook m_objBook = (Excel.Workbook)(m_objBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet));

Excel.Sheets m_objSheets = (Excel.Sheets)m_objBook.Worksheets;

Excel._Worksheet m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

m_objSheet.Name = "Sheet1";

Excel.Range range = null;

long totalCount =ta.Rows.Count+ 2;

long colnum = 11;//有12列；

((Excel.Range)m_objSheet.Cells).NumberFormatLocal = "@";//修改excel为文本形式

m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[totalCount, colnum]).Borders.LineStyle = XlLineStyle.xlContinuous;//设置所有表格线；

////第一行设置

m_objExcel.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, colnum]).MergeCells = true;//合并单元格

m_objExcel.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, colnum]).HorizontalAlignment = XlHAlign.xlHAlignCenter;//水平对齐方式

m_objExcel.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, colnum]).VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直对齐方式

m_objExcel.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, colnum]).Font.Size = 14;

m_objSheet.Cells[1, 1] = "证件借阅台账";

m_objExcel.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[totalCount, colnum]).ColumnWidth = 20;//设置列的宽度；

m_objExcel.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[2, colnum]).HorizontalAlignment = XlHAlign.xlHAlignCenter;//水平对齐方式

m_objExcel.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[2, colnum]).VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直对齐方式

//设置格式

for (int i = 1; i <= colnum; i++)

{

if (i <= 4 || i >= 10)

{

m_objExcel.get_Range(m_objSheet.Cells[2, i], m_objSheet.Cells[3, i]).MergeCells = true;//合并单元格

}

m_objExcel.get_Range(m_objSheet.Cells[2, 5], m_objSheet.Cells[2, 7]).MergeCells = true;//合并单元格

m_objExcel.get_Range(m_objSheet.Cells[2, 8], m_objSheet.Cells[2, 9]).MergeCells = true;//合并单元格

//设置表头：

m_objSheet.Cells[2, 1] = "姓名";

m_objSheet.Cells[2, 2] = "性别";

m_objSheet.Cells[2, 3] = "证件类型";

m_objSheet.Cells[2, 4] = "证件名称";

m_objSheet.Cells[2, 5] = "借阅形式";

m_objSheet.Cells[2, 6] = "借阅部门或工程";

m_objSheet.Cells[2, 7] = "借阅人";

m_objSheet.Cells[2, 8] = "联系方式";

m_objSheet.Cells[2, 9] = "借阅日期";

m_objSheet.Cells[2, 10] = "是否归还";

m_objSheet.Cells[2, 11] = "归还日期";

//保存文件

if (saveFileName != "")

{

try

{

m_objBook.Saved = true;

m_objBook.SaveCopyAs(saveFileName);

fileSaved = true;

}

catch (Exception ex)

{

fileSaved = false;

}

else

{

fileSaved = false;

}

//释放Excel对应的对象

if (range != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(range);

range = null;

}

if (m_objSheet != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);

m_objSheet = null;

}

if (m_objBook != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);

m_objBook = null;

}

if (m_objBooks != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);

m_objBooks = null;

}

m_objExcel.Quit();

if (m_objExcel != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);

m_objExcel = null;

}

GC.Collect();

return fileSaved;

}

--------------------------------存储过程sql拼接

-- ================================================

-- Template generated from Template Explorer using:

-- Create Procedure (New Menu).SQL

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

-- This block of comments will not be included in

-- the definition of the procedure.

-- ================================================

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[PWageBT]

(

@PName varchar(100),

@HBName varchar(50),

@begintime datetime,

@OverTime datetime

)

BEGIN

----查询补贴金额技术

declare @workw money

declare @xxw money

declare @valw money

declare @xgdw money

--加班-工作日

select @workw=View_PWageBT.Wages from View_PWageBT join PBuTieType on View_PWageBT.WageName=PBuTieType.Id

where PBuTieType.HBName='工作日'

--加班-休息日

select @xxw=View_PWageBT.Wages from View_PWageBT join PBuTieType on View_PWageBT.WageName=PBuTieType.Id

where PBuTieType.HBName='休息日'

--加班-节假日

select @valw=View_PWageBT.Wages from View_PWageBT join PBuTieType on View_PWageBT.WageName=PBuTieType.Id

where PBuTieType.HBName='节假日'

--下工地

select @xgdw=PWageSet.Wages from PWageSet join PBuTieType on convert(int,PWageSet.WageName)=PBuTieType.Id

where PBuTieType.HBName='下工地' and PWageSet.WageType='补贴'

--统计加班数据

declare @str nvarchar(1000)

set @str=

'select *,case when HBName=''加班'' then WorkDays*@workw+RestDays*@xxw+ValDays*@valw else when HBName=''下工地'' then WorkDays*@xgdw end as AllMoney from View_PBTChild where 1=1 '

if(@PName<>'')

set @str=@str+' and PName LIKE ''%'' + REPLACE(''' + @PName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@HBName<>'')

set @str=@str+' and HBName LIKE ''%'' + REPLACE(''' + @HBName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@begintime<>'')

set @str=@str+' and BDate>='''+@begintime+''''

if(@OverTime<>'')

set @str=@str+' and BDate<='''+@OverTime+''''

END

--------------------------------经典存储过程（注意将变量用三个单引号或一个单引号都行，字符的话两个单引号，若变量不为字符串，一定要转为字符串在比较）比如；

set @str=

'select *,case when HBName=''加班'' then WorkDays*'+convert(varchar(10),@workw)+'+RestDays*'+convert(varchar(10),@xxw)+'+ValDays*'+convert(varchar(10),@valw)+' when HBName=''下工地'' then WorkDays*'+convert(varchar(10),@xgdw)+' end as AllMoney from View_PBTChild where 1=1 '

if(@PName<>'')

set @str=@str+' and PName LIKE ''%'' + REPLACE(''' + @PName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@HBName<>'')

set @str=@str+' and HBName LIKE ''%'' + REPLACE(''' + @HBName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@begintime<>'')

set @str=@str+' and BDate>='''+convert(varchar(100),@begintime)+''''

if(@OverTime<>'')

set @str=@str+' and BDate<='''+convert(varchar(100),@OverTime)+''''

EXEC (@str)

END

-------------------------------------

---------------------sql顺序：

select PerId,sum(WorkDays) as xdats from View_PBTChild group by PerId with rollup having 1=1 order by PerId

--------------------汇总案例

USE [SJCL1]

/****** 对象: StoredProcedure [dbo].[SZCHuiZong] 脚本日期: 07/07/2013 15:46:25 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[SZCHuiZong]

@ZhongZhiRiQi VARCHAR(20),

@QiShiRiQi VARCHAR(20)

set nocount on

CREATE TABLE #ShouZhiCunMingXi(CLBH [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,SL [float] NULL,JE [float] NULL,Lie [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL )

CREATE TABLE #T(CLBH [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,SL [float] NULL,JE [float] NULL )

/*上月结存*/

/*初始化的*/

INSERT INTO #T(CLBH,SL,JE) SELECT XTBH, SUM(KCSL) AS SJSL, SUM(KCJE) AS SJJE FROM ZhangWuChuShiHua GROUP BY XTBH

/*入库的*/

INSERT INTO #T(CLBH,SL,JE) SELECT RKD_JH.CLXTBH, SUM(RKD_JH.SL) AS SJSL, SUM(RKD_JH.JE) AS SJJE

FROM RKD_DJ inner join RKD_JH ON RKD_DJ.DJXTBH=RKD_JH.DJXTBH WHERE RKD_DJ.RQ <@QiShiRiQi GROUP BY RKD_JH.CLXTBH

/* 入库退货的*/

INSERT INTO #T(CLBH,SL,JE) SELECT RKTHD_JH.CLXTBH, -SUM(RKTHD_JH.SL) AS SJSL, -SUM(RKTHD_JH.JE) AS SJJE

FROM RKTHD_DJ inner join RKTHD_JH ON RKTHD_DJ.DJXTBH=RKTHD_JH.DJXTBH WHERE RKTHD_DJ.RQ <@QiShiRiQi GROUP BY RKTHD_JH.CLXTBH

/*调拨入库的*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBRKD_JH.CLXTBH, SUM(DBRKD_JH.SL) AS SJSL, SUM(DBRKD_JH.JE) AS SJJE

FROM DBRKD_DJ inner join DBRKD_JH ON DBRKD_DJ.DJXTBH=DBRKD_JH.DJXTBH WHERE DBRKD_DJ.RQ <@QiShiRiQi GROUP BY DBRKD_JH.CLXTBH

/*调拨入库退货单*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBRKTHD_JH.CLXTBH, -SUM(DBRKTHD_JH.SL) AS SJSL, -SUM(DBRKTHD_JH.JE) AS SJJE

FROM DBRKTHD_DJ inner join DBRKTHD_JH ON DBRKTHD_DJ.DJXTBH=DBRKTHD_JH.DJXTBH WHERE DBRKTHD_DJ.RQ <@QiShiRiQi

GROUP BY DBRKTHD_JH.CLXTBH

/*调拨出库的*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBCKD_JH.CLXTBH, -SUM(DBCKD_JH.SL) AS SJSL, -SUM(DBCKD_JH.JE) AS SJJE

FROM DBCKD_DJ inner join DBCKD_JH ON DBCKD_DJ.DJXTBH=DBCKD_JH.DJXTBH WHERE DBCKD_DJ.RQ <@QiShiRiQi GROUP BY DBCKD_JH.CLXTBH

/*调拨出库退货单*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBCKTHD_JH.CLXTBH, SUM(DBCKTHD_JH.SL) AS SJSL, SUM(DBCKTHD_JH.JE) AS SJJE

FROM DBCKTHD_DJ inner join DBCKTHD_JH ON DBCKTHD_DJ.DJXTBH=DBCKTHD_JH.DJXTBH WHERE DBCKTHD_DJ.RQ <@QiShiRiQi

GROUP BY DBCKTHD_JH.CLXTBH

/*发料的*/

INSERT INTO #T(CLBH,SL,JE) SELECT CKD_JH.CLXTBH, -SUM(CKD_JH.SL) AS SJSL, -SUM(CKD_JH.JE) AS SJJE FROM CKD_DJ

inner join CKD_JH ON CKD_DJ.DJXTBH=CKD_JH.CKD_DJ_DJXTBH WHERE CKD_DJ.RQ <@QiShiRiQi GROUP BY CKD_JH.CLXTBH

/*发料退回的*/

INSERT INTO #T(CLBH,SL,JE) SELECT CKTHD_JH.CLXTBH, SUM(CKTHD_JH.SL) AS SJSL, SUM(CKTHD_JH.JE) AS SJJE

FROM CKTHD_DJ inner join CKTHD_JH ON CKTHD_DJ.DJXTBH=CKTHD_JH.DJXTBH WHERE CKTHD_DJ.RQ <@QiShiRiQi GROUP BY CKTHD_JH.CLXTBH

/*盘点的量*/

INSERT INTO #T(CLBH,SL,JE) SELECT CLXTBH, SUM(CheckCount) AS SJSL, SUM(CheckJE) AS SJJE

FROM StorageCheck WHERE CheckDate <@QiShiRiQi GROUP BY CLXTBH

INSERT INTO #ShouZhiCunMingXi(CLBH,SL,JE,Lie) SELECT CLBH,SUM(SL) AS SL,SUM(JE) AS JE,'上月' AS LB FROM #T GROUP BY CLBH

DELETE FROM #T

/*本月月结存*/

/*初始化的*/

INSERT INTO #T(CLBH,SL,JE) SELECT XTBH, SUM(KCSL) AS SJSL, SUM(KCJE) AS SJJE FROM ZhangWuChuShiHua GROUP BY XTBH

/*入库的*/

INSERT INTO #T(CLBH,SL,JE) SELECT RKD_JH.CLXTBH, SUM(RKD_JH.SL) AS SJSL, SUM(RKD_JH.JE) AS SJJE

FROM RKD_DJ inner join RKD_JH ON RKD_DJ.DJXTBH=RKD_JH.DJXTBH WHERE RKD_DJ.RQ <= @ZhongZhiRiQi GROUP BY RKD_JH.CLXTBH

/* 入库退货的*/

INSERT INTO #T(CLBH,SL,JE) SELECT RKTHD_JH.CLXTBH, -SUM(RKTHD_JH.SL) AS SJSL, -SUM(RKTHD_JH.JE) AS SJJE

FROM RKTHD_DJ inner join RKTHD_JH ON RKTHD_DJ.DJXTBH=RKTHD_JH.DJXTBH WHERE RKTHD_DJ.RQ <= @ZhongZhiRiQi GROUP BY RKTHD_JH.CLXTBH

/*调拨入库的*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBRKD_JH.CLXTBH, SUM(DBRKD_JH.SL) AS SJSL, SUM(DBRKD_JH.JE) AS SJJE

FROM DBRKD_DJ inner join DBRKD_JH ON DBRKD_DJ.DJXTBH=DBRKD_JH.DJXTBH WHERE DBRKD_DJ.RQ <= @ZhongZhiRiQi

GROUP BY DBRKD_JH.CLXTBH

/*调拨入库退货单*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBRKTHD_JH.CLXTBH, -SUM(DBRKTHD_JH.SL) AS SJSL, -SUM(DBRKTHD_JH.JE) AS SJJE

FROM DBRKTHD_DJ inner join DBRKTHD_JH ON DBRKTHD_DJ.DJXTBH=DBRKTHD_JH.DJXTBH WHERE DBRKTHD_DJ.RQ <= @ZhongZhiRiQi

GROUP BY DBRKTHD_JH.CLXTBH

/*调拨出库的*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBCKD_JH.CLXTBH, -SUM(DBCKD_JH.SL) AS SJSL, -SUM(DBCKD_JH.JE) AS SJJE

FROM DBCKD_DJ inner join DBCKD_JH ON DBCKD_DJ.DJXTBH=DBCKD_JH.DJXTBH WHERE DBCKD_DJ.RQ <= @ZhongZhiRiQi

GROUP BY DBCKD_JH.CLXTBH

/*调拨出库退货单*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBCKTHD_JH.CLXTBH, SUM(DBCKTHD_JH.SL) AS SJSL, SUM(DBCKTHD_JH.JE) AS SJJE

FROM DBCKTHD_DJ inner join DBCKTHD_JH ON DBCKTHD_DJ.DJXTBH=DBCKTHD_JH.DJXTBH WHERE DBCKTHD_DJ.RQ <= @ZhongZhiRiQi

GROUP BY DBCKTHD_JH.CLXTBH

/*发料的*/

INSERT INTO #T(CLBH,SL,JE) SELECT CKD_JH.CLXTBH, -SUM(CKD_JH.SL) AS SJSL, -SUM(CKD_JH.JE) AS SJJE

FROM CKD_DJ inner join CKD_JH ON CKD_DJ.DJXTBH=CKD_JH.CKD_DJ_DJXTBH WHERE CKD_DJ.RQ <= @ZhongZhiRiQi

GROUP BY CKD_JH.CLXTBH

/*发料退回的*/

INSERT INTO #T(CLBH,SL,JE) SELECT CKTHD_JH.CLXTBH, SUM(CKTHD_JH.SL) AS SJSL, SUM(CKTHD_JH.JE) AS SJJE

FROM CKTHD_DJ inner join CKTHD_JH ON CKTHD_DJ.DJXTBH=CKTHD_JH.DJXTBH WHERE CKTHD_DJ.RQ <= @ZhongZhiRiQi

GROUP BY CKTHD_JH.CLXTBH

/*盘点的量*/

INSERT INTO #T(CLBH,SL,JE) SELECT CLXTBH, SUM(CheckCount) AS SJSL, SUM(CheckJE) AS SJJE FROM StorageCheck

WHERE CheckDate <=@ZhongZhiRiQi GROUP BY CLXTBH

INSERT INTO #ShouZhiCunMingXi(CLBH,SL,JE,Lie) SELECT CLBH,SUM(SL) AS SL,SUM(JE) AS JE,'本月' AS LB FROM #T GROUP BY CLBH

DELETE FROM #T

/*本月入库*/

/*入库的*/

INSERT INTO #T(CLBH,SL,JE) SELECT RKD_JH.CLXTBH, SUM(RKD_JH.SL) AS SJSL, SUM(RKD_JH.JE) AS SJJE

FROM RKD_DJ inner join RKD_JH ON RKD_DJ.DJXTBH=RKD_JH.DJXTBH WHERE RKD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi

GROUP BY RKD_JH.CLXTBH

/* 入库退货的*/

INSERT INTO #T(CLBH,SL,JE) SELECT RKTHD_JH.CLXTBH, -SUM(RKTHD_JH.SL) AS SJSL, -SUM(RKTHD_JH.JE) AS SJJE

FROM RKTHD_DJ inner join RKTHD_JH ON RKTHD_DJ.DJXTBH=RKTHD_JH.DJXTBH WHERE RKTHD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi

GROUP BY RKTHD_JH.CLXTBH

/*调拨入库的*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBRKD_JH.CLXTBH, SUM(DBRKD_JH.SL) AS SJSL, SUM(DBRKD_JH.JE) AS SJJE

FROM DBRKD_DJ inner join DBRKD_JH ON DBRKD_DJ.DJXTBH=DBRKD_JH.DJXTBH

WHERE DBRKD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY DBRKD_JH.CLXTBH

/*调拨入库退货单*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBRKTHD_JH.CLXTBH, -SUM(DBRKTHD_JH.SL) AS SJSL, -SUM(DBRKTHD_JH.JE) AS SJJE

FROM DBRKTHD_DJ inner join DBRKTHD_JH ON DBRKTHD_DJ.DJXTBH=DBRKTHD_JH.DJXTBH WHERE DBRKTHD_DJ.RQ

BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY DBRKTHD_JH.CLXTBH

INSERT INTO #ShouZhiCunMingXi(CLBH,SL,JE,Lie) SELECT CLBH,SUM(SL) AS SL,SUM(JE) AS JE,'入库' AS LB FROM #T GROUP BY CLBH

DELETE FROM #T

/*本月出库的*/

/*发料的*/

INSERT INTO #T(CLBH,SL,JE) SELECT CKD_JH.CLXTBH, SUM(CKD_JH.SL) AS SJSL, SUM(CKD_JH.JE) AS SJJE

FROM CKD_DJ inner join CKD_JH ON CKD_DJ.DJXTBH=CKD_JH.CKD_DJ_DJXTBH

WHERE CKD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY CKD_JH.CLXTBH

/*发料退回的*/

INSERT INTO #T(CLBH,SL,JE) SELECT CKTHD_JH.CLXTBH, -SUM(CKTHD_JH.SL) AS SJSL, -SUM(CKTHD_JH.JE) AS SJJE

FROM CKTHD_DJ inner join CKTHD_JH ON CKTHD_DJ.DJXTBH=CKTHD_JH.DJXTBH

WHERE CKTHD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY CKTHD_JH.CLXTBH

/*调拨出库的*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBCKD_JH.CLXTBH, SUM(DBCKD_JH.SL) AS SJSL, SUM(DBCKD_JH.JE) AS SJJE

FROM DBCKD_DJ inner join DBCKD_JH ON DBCKD_DJ.DJXTBH=DBCKD_JH.DJXTBH

WHERE DBCKD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY DBCKD_JH.CLXTBH

/*调拨出库退货单*/

INSERT INTO #T(CLBH,SL,JE) SELECT DBCKTHD_JH.CLXTBH, -SUM(DBCKTHD_JH.SL) AS SJSL, -SUM(DBCKTHD_JH.JE) AS SJJE

FROM DBCKTHD_DJ inner join DBCKTHD_JH ON DBCKTHD_DJ.DJXTBH=DBCKTHD_JH.DJXTBH

WHERE DBCKTHD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY DBCKTHD_JH.CLXTBH

INSERT INTO #ShouZhiCunMingXi(CLBH,SL,JE,Lie) SELECT CLBH,SUM(SL) AS SL,SUM(JE) AS JE,'出库' AS LB FROM #T GROUP BY CLBH

DELETE FROM #T

--第一步：根据系统编号、列、单价分组求和

select CLBH,sum(SL) as SL,sum(JE) as JE,Lie

into #TempSZCMX

from #ShouZhiCunMingXi group by Lie, CLBH order by Lie,CLBH

--第二步：列转行,分组求和

SELECT YJ,EJ,

MoJi=(CASE WHEN grouping(YJ) = 1 THEN '0'

WHEN grouping(EJ) = 1 THEN [YJ]

WHEN grouping(MoJi) = 1 THEN [EJ] ELSE MoJi END),

sum(SYSL) SYSL,SUM(SYJE) SYJE,SUM(BYSL) BYSL,SUM(BYJE) BYJE,

SUM(CKSL) CKSL,SUM(CKJE) CKJE,SUM(RKSL) RKSL,SUM(RKJE) RKJE

into SysCl.##TempLastResults

from

(

select CaiLiaoXinxi_1.SJ AS YJ,CaiLiaoXinxi_1.BH AS EJ,CaiLiaoXinxi.BH AS MoJi,

sum(case Lie when '上月' then SL else 0 end) as 'SYSL',

sum(case Lie when '上月' then JE else 0 end) as 'SYJE',

sum(case Lie when '本月' then SL else 0 end) as 'BYSL',

sum(case Lie when '本月' then JE else 0 end) as 'BYJE',

sum(case Lie when '出库' then SL else 0 end) as 'CKSL',

sum(case Lie when '出库' then JE else 0 end) as 'CKJE',

sum(case Lie when '入库' then SL else 0 end) as 'RKSL',

sum(case Lie when '入库' then JE else 0 end) as 'RKJE'

from #TempSZCMX inner join CaiLiaoXinxi ON #TempSZCMX.CLBH =CaiLiaoXinxi.XTBH

INNER JOIN CaiLiaoXinxi CaiLiaoXinxi_1 ON CaiLiaoXinxi.SJ=CaiLiaoXinxi_1.BH

GROUP BY CaiLiaoXinxi.BH ,CaiLiaoXinxi_1.BH,CaiLiaoXinxi_1.SJ ) as MingXi

group by YJ,EJ,MoJi with rollup

set nocount off

----------------------------汇总案例（材料）

ALTER PROCEDURE [dbo].[TeShuJiJia]

@ZhongZhiRiQi VARCHAR(20),

@QiShiRiQi VARCHAR(20),

@LeiBie VARCHAR(100),

@IsYD bit

set nocount on

CREATE TABLE #ShouZhiCunMingXi(CLBH [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,DJ [float] NULL,

SL [float] NULL,JE [float] NULL,Lie [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL )

CREATE TABLE #T(CLBH [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,DJ [float] NULL,SL [float] NULL,JE [float] NULL )

create table #lastResult(CLBH [varchar] (50) collate Chinese_PRC_CI_AS null,

SYSL [float] null,

SYJE [float] null,

BYSL [float] null,

BYJE [float] null,

CKSL [float] null,

CKJE [float] null,

RKSL [float] null,

RKJE [float] null)

create table #TempLastResults(YJ [varchar](50),EJ [varchar](50),MoJi [varchar](50),DJ [FLOAT] DEFAULT 0,

SYSL [FLOAT] DEFAULT 0,SYJE [FLOAT] DEFAULT 0,BYSL [FLOAT] DEFAULT 0,BYJE [FLOAT] DEFAULT 0,

CKSL [FLOAT] DEFAULT 0,CKJE [FLOAT] DEFAULT 0,RKSL [FLOAT] DEFAULT 0,RKJE [FLOAT] DEFAULT 0)

/*上月结存*/

if(@IsYD='true')

begin

/*含预点的入库单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT YDRKD_JH.CLXTBH, YDRKD_JH.DJ, SUM(YDRKD_JH.SL) AS SJSL, SUM(YDRKD_JH.JE) AS SJJE

FROM YDRKD_DJ inner join YDRKD_JH ON YDRKD_DJ.DJXTBH=YDRKD_JH.DJXTBH

WHERE YDRKD_DJ.RQ <@QiShiRiQi GROUP BY YDRKD_JH.CLXTBH, YDRKD_JH.DJ

/*含预点的发料单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT YDCKD_JH.CLXTBH, YDCKD_JH.KCDJ, -SUM(YDCKD_JH.SL) AS SJSL,

-SUM(YDCKD_JH.JE) AS SJJE FROM YDCKD_DJ inner join YDCKD_JH ON YDCKD_DJ.DJXTBH=YDCKD_JH.CKD_DJ_DJXTBH

WHERE YDCKD_DJ.RQ <@QiShiRiQi GROUP BY YDCKD_JH.CLXTBH, YDCKD_JH.KCDJ

end

/*初始化的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT XTBH, KCDJ, SUM(KCSL) AS SJSL, SUM(KCJE) AS SJJE FROM ZhangWuChuShiHua

GROUP BY XTBH, KCDJ

/*入库的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT RKD_JH.CLXTBH, RKD_JH.DJ, SUM(RKD_JH.SL) AS SJSL, SUM(RKD_JH.JE) AS SJJE

FROM RKD_DJ inner join RKD_JH ON RKD_DJ.DJXTBH=RKD_JH.DJXTBH WHERE RKD_DJ.RQ <@QiShiRiQi GROUP BY RKD_JH.CLXTBH, RKD_JH.DJ

/* 入库退货的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT RKTHD_JH.CLXTBH, RKTHD_JH.DJ, -SUM(RKTHD_JH.SL) AS SJSL, -SUM(RKTHD_JH.JE) AS SJJE

FROM RKTHD_DJ inner join RKTHD_JH ON RKTHD_DJ.DJXTBH=RKTHD_JH.DJXTBH WHERE RKTHD_DJ.RQ <@QiShiRiQi

GROUP BY RKTHD_JH.CLXTBH, RKTHD_JH.DJ

/*调拨入库的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBRKD_JH.CLXTBH, DBRKD_JH.DJ, SUM(DBRKD_JH.SL) AS SJSL,

SUM(DBRKD_JH.JE) AS SJJE FROM DBRKD_DJ inner join DBRKD_JH ON DBRKD_DJ.DJXTBH=DBRKD_JH.DJXTBH WHERE DBRKD_DJ.RQ <@QiShiRiQi

GROUP BY DBRKD_JH.CLXTBH, DBRKD_JH.DJ

/*调拨入库退货单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBRKTHD_JH.CLXTBH, DBRKTHD_JH.DJ, -SUM(DBRKTHD_JH.SL) AS SJSL,

-SUM(DBRKTHD_JH.JE) AS SJJE FROM DBRKTHD_DJ inner join DBRKTHD_JH ON DBRKTHD_DJ.DJXTBH=DBRKTHD_JH.DJXTBH

WHERE DBRKTHD_DJ.RQ <@QiShiRiQi GROUP BY DBRKTHD_JH.CLXTBH, DBRKTHD_JH.DJ

/*调拨出库的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBCKD_JH.CLXTBH, DBCKD_JH.KCDJ, -SUM(DBCKD_JH.SL) AS SJSL,

-SUM(DBCKD_JH.JE) AS SJJE FROM DBCKD_DJ inner join DBCKD_JH ON DBCKD_DJ.DJXTBH=DBCKD_JH.DJXTBH

WHERE DBCKD_DJ.RQ <@QiShiRiQi GROUP BY DBCKD_JH.CLXTBH, DBCKD_JH.KCDJ

/*调拨出库退货单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBCKTHD_JH.CLXTBH, DBCKTHD_JH.KCDJ, SUM(DBCKTHD_JH.SL) AS SJSL,

SUM(DBCKTHD_JH.JE) AS SJJE FROM DBCKTHD_DJ inner join DBCKTHD_JH ON DBCKTHD_DJ.DJXTBH=DBCKTHD_JH.DJXTBH

WHERE DBCKTHD_DJ.RQ <@QiShiRiQi GROUP BY DBCKTHD_JH.CLXTBH, DBCKTHD_JH.KCDJ

/*发料的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT CKD_JH.CLXTBH, CKD_JH.KCDJ, -SUM(CKD_JH.SL) AS SJSL,

-SUM(CKD_JH.JE) AS SJJE FROM CKD_DJ inner join CKD_JH ON CKD_DJ.DJXTBH=CKD_JH.CKD_DJ_DJXTBH

WHERE CKD_DJ.RQ <@QiShiRiQi GROUP BY CKD_JH.CLXTBH, CKD_JH.KCDJ

/*发料退回的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT CKTHD_JH.CLXTBH, CKTHD_JH.KCDJ, SUM(CKTHD_JH.SL) AS SJSL,

SUM(CKTHD_JH.JE) AS SJJE FROM CKTHD_DJ inner join CKTHD_JH ON CKTHD_DJ.DJXTBH=CKTHD_JH.DJXTBH

WHERE CKTHD_DJ.RQ <@QiShiRiQi GROUP BY CKTHD_JH.CLXTBH, CKTHD_JH.KCDJ

/*盘点的量*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT CLXTBH, KCDJ, SUM(DifferenceCount) AS SJSL, SUM(DifferenceCount*KCDJ) AS SJJE

FROM StorageCheck WHERE CheckDate <@QiShiRiQi GROUP BY CLXTBH, KCDJ

INSERT INTO #ShouZhiCunMingXi(CLBH,DJ,SL,JE,Lie) SELECT CLBH,DJ,SUM(SL) AS SL,SUM(JE) AS JE,'上月' AS LB

FROM #T GROUP BY CLBH,DJ

DELETE FROM #T

/*本月月结存*/

if(@IsYD='true')

begin

/*含预点的入库单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT YDRKD_JH.CLXTBH, YDRKD_JH.DJ, SUM(YDRKD_JH.SL) AS SJSL, SUM(YDRKD_JH.JE) AS SJJE

FROM YDRKD_DJ inner join YDRKD_JH ON YDRKD_DJ.DJXTBH=YDRKD_JH.DJXTBH

WHERE YDRKD_DJ.RQ <= @ZhongZhiRiQi GROUP BY YDRKD_JH.CLXTBH, YDRKD_JH.DJ

/*含预点的发料单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT YDCKD_JH.CLXTBH, YDCKD_JH.KCDJ, -SUM(YDCKD_JH.SL) AS SJSL,

-SUM(YDCKD_JH.JE) AS SJJE FROM YDCKD_DJ inner join YDCKD_JH ON YDCKD_DJ.DJXTBH=YDCKD_JH.CKD_DJ_DJXTBH

WHERE YDCKD_DJ.RQ <= @ZhongZhiRiQi GROUP BY YDCKD_JH.CLXTBH, YDCKD_JH.KCDJ

end

/*初始化的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT XTBH, KCDJ, SUM(KCSL) AS SJSL, SUM(KCJE) AS SJJE

FROM ZhangWuChuShiHua GROUP BY XTBH, KCDJ

/*入库的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT RKD_JH.CLXTBH, RKD_JH.DJ, SUM(RKD_JH.SL) AS SJSL,

SUM(RKD_JH.JE) AS SJJE FROM RKD_DJ inner join RKD_JH ON RKD_DJ.DJXTBH=RKD_JH.DJXTBH

WHERE RKD_DJ.RQ <= @ZhongZhiRiQi GROUP BY RKD_JH.CLXTBH, RKD_JH.DJ

/* 入库退货的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT RKTHD_JH.CLXTBH, RKTHD_JH.DJ, -SUM(RKTHD_JH.SL) AS SJSL,

-SUM(RKTHD_JH.JE) AS SJJE FROM RKTHD_DJ inner join RKTHD_JH ON RKTHD_DJ.DJXTBH=RKTHD_JH.DJXTBH

WHERE RKTHD_DJ.RQ <= @ZhongZhiRiQi GROUP BY RKTHD_JH.CLXTBH, RKTHD_JH.DJ

/*调拨入库的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBRKD_JH.CLXTBH, DBRKD_JH.DJ, SUM(DBRKD_JH.SL) AS SJSL,

SUM(DBRKD_JH.JE) AS SJJE FROM DBRKD_DJ inner join DBRKD_JH ON DBRKD_DJ.DJXTBH=DBRKD_JH.DJXTBH

WHERE DBRKD_DJ.RQ <= @ZhongZhiRiQi GROUP BY DBRKD_JH.CLXTBH, DBRKD_JH.DJ

/*调拨入库退货单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBRKTHD_JH.CLXTBH, DBRKTHD_JH.DJ, -SUM(DBRKTHD_JH.SL) AS SJSL,

-SUM(DBRKTHD_JH.JE) AS SJJE FROM DBRKTHD_DJ inner join DBRKTHD_JH ON DBRKTHD_DJ.DJXTBH=DBRKTHD_JH.DJXTBH

WHERE DBRKTHD_DJ.RQ <= @ZhongZhiRiQi GROUP BY DBRKTHD_JH.CLXTBH, DBRKTHD_JH.DJ

/*调拨出库的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBCKD_JH.CLXTBH, DBCKD_JH.KCDJ, -SUM(DBCKD_JH.SL) AS SJSL,

-SUM(DBCKD_JH.JE) AS SJJE FROM DBCKD_DJ inner join DBCKD_JH ON DBCKD_DJ.DJXTBH=DBCKD_JH.DJXTBH

WHERE DBCKD_DJ.RQ <= @ZhongZhiRiQi GROUP BY DBCKD_JH.CLXTBH, DBCKD_JH.KCDJ

/*调拨出库退货单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBCKTHD_JH.CLXTBH, DBCKTHD_JH.KCDJ, SUM(DBCKTHD_JH.SL) AS SJSL,

SUM(DBCKTHD_JH.JE) AS SJJE FROM DBCKTHD_DJ inner join DBCKTHD_JH ON DBCKTHD_DJ.DJXTBH=DBCKTHD_JH.DJXTBH

WHERE DBCKTHD_DJ.RQ <= @ZhongZhiRiQi GROUP BY DBCKTHD_JH.CLXTBH, DBCKTHD_JH.KCDJ

/*发料的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT CKD_JH.CLXTBH, CKD_JH.KCDJ, -SUM(CKD_JH.SL) AS SJSL,

-SUM(CKD_JH.JE) AS SJJE FROM CKD_DJ inner join CKD_JH ON CKD_DJ.DJXTBH=CKD_JH.CKD_DJ_DJXTBH

WHERE CKD_DJ.RQ <= @ZhongZhiRiQi GROUP BY CKD_JH.CLXTBH, CKD_JH.KCDJ

/*发料退回的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT CKTHD_JH.CLXTBH, CKTHD_JH.KCDJ, SUM(CKTHD_JH.SL) AS SJSL,

SUM(CKTHD_JH.JE) AS SJJE FROM CKTHD_DJ inner join CKTHD_JH ON CKTHD_DJ.DJXTBH=CKTHD_JH.DJXTBH

WHERE CKTHD_DJ.RQ <= @ZhongZhiRiQi GROUP BY CKTHD_JH.CLXTBH, CKTHD_JH.KCDJ

/*盘点的量*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT CLXTBH, KCDJ, SUM(DifferenceCount) AS SJSL, SUM(DifferenceCount*KCDJ) AS SJJE

FROM StorageCheck WHERE CheckDate <=@ZhongZhiRiQi GROUP BY CLXTBH, KCDJ

INSERT INTO #ShouZhiCunMingXi(CLBH,DJ,SL,JE,Lie) SELECT CLBH,DJ,SUM(SL) AS SL,SUM(JE) AS JE,'本月' AS LB

FROM #T GROUP BY CLBH,DJ

DELETE FROM #T

/*本月入库*/

if(@IsYD='true')

begin

/*含预点的入库单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT YDRKD_JH.CLXTBH, YDRKD_JH.DJ, SUM(YDRKD_JH.SL) AS SJSL, SUM(YDRKD_JH.JE) AS SJJE

FROM YDRKD_DJ inner join YDRKD_JH ON YDRKD_DJ.DJXTBH=YDRKD_JH.DJXTBH

WHERE YDRKD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY YDRKD_JH.CLXTBH, YDRKD_JH.DJ

/*直进直出入库*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT YDZJZC_JH.CLXTBH,YDZJZC_JH.CGDJ, sum(YDZJZC_JH.SL) as SJSL,sum(YDZJZC_JH.CGJE) as SJJE

FROM YDRKD_DJ INNER JOIN YDZJZC_JH ON YDRKD_DJ.DJXTBH = YDZJZC_JH.DJXTBH WHERE YDRKD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY YDZJZC_JH.CLXTBH,YDZJZC_JH.CGDJ

end

/*入库的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT RKD_JH.CLXTBH, RKD_JH.DJ, SUM(RKD_JH.SL) AS SJSL,

SUM(RKD_JH.JE) AS SJJE FROM RKD_DJ inner join RKD_JH ON RKD_DJ.DJXTBH=RKD_JH.DJXTBH

WHERE RKD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY RKD_JH.CLXTBH, RKD_JH.DJ

/* 入库退货的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT RKTHD_JH.CLXTBH, RKTHD_JH.DJ, -SUM(RKTHD_JH.SL) AS SJSL,

-SUM(RKTHD_JH.JE) AS SJJE FROM RKTHD_DJ inner join RKTHD_JH ON RKTHD_DJ.DJXTBH=RKTHD_JH.DJXTBH

WHERE RKTHD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY RKTHD_JH.CLXTBH, RKTHD_JH.DJ

/*调拨入库的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBRKD_JH.CLXTBH, DBRKD_JH.DJ, SUM(DBRKD_JH.SL) AS SJSL,

SUM(DBRKD_JH.JE) AS SJJE FROM DBRKD_DJ inner join DBRKD_JH ON DBRKD_DJ.DJXTBH=DBRKD_JH.DJXTBH

WHERE DBRKD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY DBRKD_JH.CLXTBH, DBRKD_JH.DJ

/*调拨入库退货单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBRKTHD_JH.CLXTBH, DBRKTHD_JH.DJ, -SUM(DBRKTHD_JH.SL) AS SJSL,

-SUM(DBRKTHD_JH.JE) AS SJJE FROM DBRKTHD_DJ inner join DBRKTHD_JH ON DBRKTHD_DJ.DJXTBH=DBRKTHD_JH.DJXTBH

WHERE DBRKTHD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY DBRKTHD_JH.CLXTBH, DBRKTHD_JH.DJ

/*直进直出入库*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT ZJZC_JH.CLXTBH,ZJZC_JH.CGDJ, sum(ZJZC_JH.SL) as SJSL,sum(ZJZC_JH.CGJE) as SJJE

FROM ZJZC_DJ INNER JOIN ZJZC_JH ON ZJZC_DJ.DJXTBH = ZJZC_JH.DJXTBH WHERE ZJZC_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY ZJZC_JH.CLXTBH,ZJZC_JH.CGDJ

/*直进直出入库退货*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT ZJZCTH_JH.CLXTBH, ZJZCTH_JH.CGTHDJ,-sum(ZJZCTH_JH.SL) as SJSL,-sum(ZJZCTH_JH.CGTHJE) as SJJE

FROM ZJZCTH_DJ INNER JOIN ZJZCTH_JH ON ZJZCTH_DJ.DJXTBH = ZJZCTH_JH.DJXTBH WHERE ZJZCTH_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY ZJZCTH_JH.CLXTBH,ZJZCTH_JH.CGTHDJ

INSERT INTO #ShouZhiCunMingXi(CLBH,DJ,SL,JE,Lie) SELECT CLBH,DJ,SUM(SL) AS SL,SUM(JE) AS JE,'入库' AS LB

FROM #T GROUP BY CLBH,DJ

DELETE FROM #T

/*本月出库的*/

if(@IsYD='true')

begin

/*含预点的发料单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT YDCKD_JH.CLXTBH, YDCKD_JH.KCDJ, SUM(YDCKD_JH.SL) AS SJSL,

SUM(YDCKD_JH.JE) AS SJJE FROM YDCKD_DJ inner join YDCKD_JH ON YDCKD_DJ.DJXTBH=YDCKD_JH.CKD_DJ_DJXTBH

WHERE YDCKD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY YDCKD_JH.CLXTBH, YDCKD_JH.KCDJ

/*直进直出出库*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT YDZJZC_JH.CLXTBH, YDZJZC_JH.CGDJ,sum(YDZJZC_JH.SL) as SJSL,sum(YDZJZC_JH.CKJE) as SJJE

FROM YDZJZC_DJ INNER JOIN YDZJZC_JH ON YDZJZC_DJ.DJXTBH = YDZJZC_JH.DJXTBH WHERE YDZJZC_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY YDZJZC_JH.CLXTBH,YDZJZC_JH.CGDJ

end

/*发料的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT CKD_JH.CLXTBH, CKD_JH.KCDJ, SUM(CKD_JH.SL) AS SJSL,

SUM(CKD_JH.JE) AS SJJE FROM CKD_DJ inner join CKD_JH ON CKD_DJ.DJXTBH=CKD_JH.CKD_DJ_DJXTBH

WHERE CKD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY CKD_JH.CLXTBH, CKD_JH.KCDJ

/*发料退回的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT CKTHD_JH.CLXTBH, CKTHD_JH.KCDJ, -SUM(CKTHD_JH.SL) AS SJSL,

-SUM(CKTHD_JH.JE) AS SJJE FROM CKTHD_DJ inner join CKTHD_JH ON CKTHD_DJ.DJXTBH=CKTHD_JH.DJXTBH

WHERE CKTHD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY CKTHD_JH.CLXTBH, CKTHD_JH.KCDJ

/*调拨出库的*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBCKD_JH.CLXTBH, DBCKD_JH.KCDJ, SUM(DBCKD_JH.SL) AS SJSL,

SUM(DBCKD_JH.JE) AS SJJE FROM DBCKD_DJ inner join DBCKD_JH ON DBCKD_DJ.DJXTBH=DBCKD_JH.DJXTBH

WHERE DBCKD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY DBCKD_JH.CLXTBH, DBCKD_JH.KCDJ

/*调拨出库退货单*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT DBCKTHD_JH.CLXTBH, DBCKTHD_JH.KCDJ, -SUM(DBCKTHD_JH.SL)

AS SJSL, -SUM(DBCKTHD_JH.JE) AS SJJE FROM DBCKTHD_DJ inner join DBCKTHD_JH ON DBCKTHD_DJ.DJXTBH=DBCKTHD_JH.DJXTBH

WHERE DBCKTHD_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY DBCKTHD_JH.CLXTBH, DBCKTHD_JH.KCDJ

/*直进直出出库*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT ZJZC_JH.CLXTBH, ZJZC_JH.CGDJ,sum(ZJZC_JH.SL) as SJSL,sum(ZJZC_JH.CKJE) as SJJE

FROM ZJZC_DJ INNER JOIN ZJZC_JH ON ZJZC_DJ.DJXTBH = ZJZC_JH.DJXTBH WHERE ZJZC_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY ZJZC_JH.CLXTBH,ZJZC_JH.CGDJ

/*直进直出chu库退货*/

INSERT INTO #T(CLBH,DJ,SL,JE) SELECT ZJZCTH_JH.CLXTBH,ZJZCTH_JH.CGTHDJ, -sum(ZJZCTH_JH.SL) as SJSL,-sum(ZJZCTH_JH.CKTHJE) as SJJE

FROM ZJZCTH_DJ INNER JOIN ZJZCTH_JH ON ZJZCTH_DJ.DJXTBH = ZJZCTH_JH.DJXTBH WHERE ZJZCTH_DJ.RQ BETWEEN @QiShiRiQi AND @ZhongZhiRiQi GROUP BY ZJZCTH_JH.CLXTBH,ZJZCTH_JH.CGTHDJ

INSERT INTO #ShouZhiCunMingXi(CLBH,DJ,SL,JE,Lie) SELECT CLBH,DJ,SUM(SL) AS SL,SUM(JE) AS JE,'出库' AS LB

FROM #T GROUP BY CLBH,DJ

DELETE FROM #T

--第一步：根据系统编号、列、单价分组求和

select CLBH,DJ,sum(SL) as SL,sum(JE) as JE,Lie

into #TempSZCMX

from #ShouZhiCunMingXi group by Lie, CLBH , DJ order by Lie,CLBH

--第二步：列转行,分组求和

insert into #TempLastResults

SELECT YJ,EJ,

MoJi=(CASE WHEN grouping(YJ) = 1 THEN '0'

WHEN grouping(EJ) = 1 THEN [YJ]

when grouping(MoJi)=1 then [EJ] else MoJi END),

DJ,

sum(SYSL) SYSL,SUM(SYJE) SYJE,SUM(BYSL) BYSL,SUM(BYJE) BYJE,

SUM(CKSL) CKSL,SUM(CKJE) CKJE,SUM(RKSL) RKSL,SUM(RKJE) RKJE

--into #TempLastResults

from

(

select CaiLiaoXinxi_1.SJ AS YJ,CaiLiaoXinxi_1.BH AS EJ,CaiLiaoXinxi.BH AS MoJi,DJ,

sum(case Lie when '上月' then SL else 0 end) as 'SYSL',

sum(case Lie when '上月' then JE else 0 end) as 'SYJE',

sum(case Lie when '本月' then SL else 0 end) as 'BYSL',

sum(case Lie when '本月' then JE else 0 end) as 'BYJE',

sum(case Lie when '出库' then SL else 0 end) as 'CKSL',

sum(case Lie when '出库' then JE else 0 end) as 'CKJE',

sum(case Lie when '入库' then SL else 0 end) as 'RKSL',

sum(case Lie when '入库' then JE else 0 end) as 'RKJE'

from #TempSZCMX inner join CaiLiaoXinxi ON #TempSZCMX.CLBH =CaiLiaoXinxi.XTBH

INNER JOIN CaiLiaoXinxi CaiLiaoXinxi_1 ON CaiLiaoXinxi.SJ=CaiLiaoXinxi_1.BH

GROUP BY CaiLiaoXinxi.BH ,CaiLiaoXinxi_1.BH,CaiLiaoXinxi_1.SJ,DJ

) as MingXi

group by YJ,EJ,MoJi,DJ with rollup having grouping(MoJi) =1 union all

select CaiLiaoXinxi_1.SJ AS YJ,CaiLiaoXinxi_1.BH AS EJ,CaiLiaoXinxi.BH AS MoJi,DJ,

sum(case Lie when '上月' then SL else 0 end) as 'SYSL',

sum(case Lie when '上月' then JE else 0 end) as 'SYJE',

sum(case Lie when '本月' then SL else 0 end) as 'BYSL',

sum(case Lie when '本月' then JE else 0 end) as 'BYJE',

sum(case Lie when '出库' then SL else 0 end) as 'CKSL',

sum(case Lie when '出库' then JE else 0 end) as 'CKJE',

sum(case Lie when '入库' then SL else 0 end) as 'RKSL',

sum(case Lie when '入库' then JE else 0 end) as 'RKJE'

--into #TempLastResults

from #TempSZCMX inner join CaiLiaoXinxi ON #TempSZCMX.CLBH =CaiLiaoXinxi.XTBH

INNER JOIN CaiLiaoXinxi CaiLiaoXinxi_1 ON CaiLiaoXinxi.SJ=CaiLiaoXinxi_1.BH

GROUP BY CaiLiaoXinxi.BH ,CaiLiaoXinxi_1.BH,CaiLiaoXinxi_1.SJ,DJ

--第三步：求最终结果

select MC,GG,DW,DJ,SYSL,SYJE,RKSL,RKJE,CKSL,CKJE,BYSL,BYJE

from #TempLastResults inner join

(

SELECT '0' AS BH,'全部材料总计' AS MC,'' AS GG,'' AS DW

UNION ALL

SELECT BH,(MC+' 合计') AS MC,GG,DW FROM CaiLiaoXinxi WHERE SJ='0' and MJ=0

UNION ALL SELECT BH,(MC+' 小计') AS MC,GG,DW FROM CaiLiaoXinxi WHERE SJ<>'0' AND MJ=0

UNION ALL SELECT BH, MC,GG,DW FROM CaiLiaoXinxi WHERE MJ=1

) AS B on B.BH=#TempLastResults.MoJi and B.BH like @LeiBie order by B.BH

-------------------------------------------------------------------------------------------

在SQLServer中real是16位的浮点型，float是32位的，这点和C#的不一样

SQLServer中的real和float分别对应C#中的float和double

------------------------完整的sql存储过程案例，分页；

USE [PM]

/****** 对象: StoredProcedure [dbo].[PItemPerMoney] 脚本日期: 07/11/2013 15:01:44 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[PItemPerMoney]

(

@PName varchar(100),

@DName varchar(50),

@begintime datetime,

@OverTime datetime,

@PerName varchar(50),

@bePage int,

@edpage int,

@num int output

)

BEGIN

----查询利率

declare @YL varchar(10)

declare @SY varchar(10)

declare @GJJ varchar(10)

declare @YB varchar(10)

declare @XMGTS varchar(10)

declare @MonCount varchar(10)

declare @str nvarchar(4000)

--给利率赋值

select @YL=cast(YL AS varchar(10)),@SY=cast(SY AS varchar(10)),@GJJ=cast(GJJ AS varchar(10)),@YB=cast(YB AS varchar(10)),@XMGTS=cast(XMGTS AS varchar(10)) from PTaxRatioSet where Id=1

--查询工作月份的月数

select @MonCount=CAST(datediff(mm,@begintime,@OverTime) as varchar(10))

IF @MonCount='0' or @MonCount is null

set @MonCount='1'

----查询总条数

set @str=

'select @num=count(1) from View_PItemPer where 1=1 '

if(@PName<>'')

set @str=@str+' and PName LIKE ''%'' + REPLACE(''' + @PName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@PerName<>'')

set @str=@str+' and PerName LIKE ''%'' + REPLACE(''' + @PerName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@DName<>'')

set @str=@str+' and DName LIKE ''%'' + REPLACE(''' + @DName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

EXEC sp_executesql @str, N'@num INT OUTPUT', @num OUTPUT

----统计

set @str=

' select tt.* from (select row_number() over(order by t.Id) as rowid,t.*,t.Gang-t.Yl-t.Sy-t.Gjj-t.Yb as shuiqian,t.Gang-t.Xmgts as shifa from (select *,PMoney*'+@MonCount+' as Gang,PMoney*'+@MonCount+'*'+@YL+' as Yl, PMoney*'+@MonCount+'*'+@SY+' as Sy, PMoney*'+@MonCount+'*'+@GJJ+' as Gjj, PMoney*'+@MonCount+'*'+@YB+' as Yb, PMoney*'+@MonCount+'*'+@XMGTS+' as Xmgts from View_PItemPer where 1=1 '

if(@PName<>'')

set @str=@str+' and PName LIKE ''%'' + REPLACE(''' + @PName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@PerName<>'')

set @str=@str+' and PerName LIKE ''%'' + REPLACE(''' + @PerName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@DName<>'')

set @str=@str+' and DName LIKE ''%'' + REPLACE(''' + @DName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

SET @str=@str+') as t) as tt where tt.rowid between '+cast(@bePage as varchar(10))+' and '+cast(@edpage as varchar(10))

EXEC (@str)

END

--------------------------存储过程需要注意：

设置拼接字符串变量@str必须是nvarchar类型，不然会报错；

返回数量变量必须是：

EXEC sp_executesql @str, N'@num INT OUTPUT', @num OUTPUT

----------------

-----------------

-------------------根据身份证号判断出生年月和工龄

/// <summary>

/// 出生日期

/// </summary>

public DateTime? Birth

{

get

{

if (_idcard.Length == 18)

{

string birth = _idcard.Substring(6, 8);

string birth1 = birth.Substring(0, 4) + "-" + birth.Substring(4, 2) + "-" + birth.Substring(6, 2);

return Convert.ToDateTime(birth1);

}

else if (_idcard.Length == 15)

{

string birth = _idcard.Substring(6, 6);

string birth1 ="19"+birth.Substring(0, 2) + "-" + birth.Substring(2, 2) + "-" + birth.Substring(4, 2);

return Convert.ToDateTime(birth1);

}

else

{

return null;

}

/// <summary>

/// 年龄

/// </summary>

public int Age

{

get

{

if (_idcard.Length == 18)

{

string birth = _idcard.Substring(6, 8);

return DateTime.Now.Year-Convert.ToInt32(birth.Substring(0,4));

}

else if (_idcard.Length == 15)

{

string birth = _idcard.Substring(6, 6);

return DateTime.Now.Year - Convert.ToInt32("19" + birth.Substring(0, 2));

}

else

{

return 0;

}

---------------------------------------------存储过程判断某一变量是否为空

if(@PName<>'')

当将语句定义为字符串时，应将所有变量转化为varchar类型，要不然会报错；

-------

------------------------------比如：

if(@begintime<>'')

set @str=@str+' and ApplyDate>='''+convert(varchar(100),@begintime)+''''

if(@OverTime<>'')

set @str=@str+' and ApplyDate<='''+convert(varchar(100),@OverTime)+''''

---------------------------当有输出参数执行字符串时；

EXEC sp_executesql @str, N'@num INT OUTPUT', @num OUTPUT

-------------行列转换

SUM(CASE WHEN T.ParentName=''休假'' THEN T.alldays ELSE 0 END)

--------------------------js根据出生日期获取年龄；

$(function () {

$("#txtAge").click(function () {

var dateUser = new Date();

var nowYear = dateUser.getFullYear();

var dateUser1 = new Date($("#txtBirthDate").val());

var birthYear = dateUser1.getFullYear();

$("#txtAge").val(nowYear - birthYear);

if ($("#txtAge").val() > 18) {

alert("年龄超过18岁，禁止填写");

}

});

---------------------汇总模板：

------汇总数据

select a.id,a.PerId,a.allworkm,a.allrestm,a.allvalm,a.workdays,a.restdays,a.valmdays,a.alldays,a.allmoney,

b.Sex,b.IdCard,b.WorkDateQ,b.RestDateQ,b.ValDateQ,

case when ptag='2' then '所有人(汇总)' when ptag='1' then '个人(汇总)' else b.PerName end as PerName

from(

select id,PerId,sum(workm) as allworkm,sum(restm) as allrestm,sum(valm) as allvalm,

sum(workdays) as workdays,sum(restdays) as restdays,sum(valmdays) as valmdays,

sum(alldays) as alldays,sum(workm+restm+valm) as allmoney,

case when grouping(PerId)=1 and grouping(id)=1 then '2' when grouping(id)=1 and grouping(PerId)=0 then '1' else '' end as Ptag

from #T group by PerId,Id with rollup) as a left join View_PBTChild as b

on a.id=b.Id

END

------------------------------存储过程模板

USE [PM]

/****** 对象: StoredProcedure [dbo].[PvalStastics] 脚本日期: 07/15/2013 10:27:40 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[PvalStastics]

(

@PName varchar(100),

@begintime datetime,

@OverTime datetime,

@PerName varchar(50),

@bePage int,

@edpage int,

@num int output

)

BEGIN

declare @str nvarchar(4000)

set @str=

'select @num=count(distinct PerId) from View_PvalApply where 1=1 '

if(@PName<>'')

set @str=@str+' and PName LIKE ''%'' + REPLACE(''' + @PName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@PerName<>'')

set @str=@str+' and PerName LIKE ''%'' + REPLACE(''' + @PerName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@begintime<>'')

set @str=@str+' and ApplyDate>='''+convert(varchar(100),@begintime)+''''

if(@OverTime<>'')

set @str=@str+' and ApplyDate<='''+convert(varchar(100),@OverTime)+''''

EXEC sp_executesql @str, N'@num INT OUTPUT', @num OUTPUT

----统计

set @str=

' SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY TT.PerId) as rowid,TT.*,TT.qing+TT.xiu as allvaldays FROM (

SELECT T.PerId,T.PerName,T.PName,SUM(CASE WHEN T.ParentName=''请假'' THEN T.alldays ELSE 0 END) as qing,

SUM(CASE WHEN T.ParentName=''休假'' THEN T.alldays ELSE 0 END) as xiu FROM(SELECT PerId,PerName,PName,ParentName,SUM(ValDays) as alldays FROM View_PvalApply GROUP BY PerId,PerName,PName,ParentName

HAVING 1=1 '

if(@PName<>'')

set @str=@str+' and PName LIKE ''%'' + REPLACE(''' + @PName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@PerName<>'')

set @str=@str+' and PerName LIKE ''%'' + REPLACE(''' + @PerName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''

if(@begintime<>'')

set @str=@str+' and ApplyDate>='''+convert(varchar(100),@begintime)+''''

if(@OverTime<>'')

set @str=@str+' and ApplyDate<='''+convert(varchar(100),@OverTime)+''''

SET @str=@str+') as T GROUP BY T.PerId,T.PerName,T.PName) as TT) as TTT WHERE TTT.rowid BETWEEN '+cast(@bePage as varchar(10))+' and '+cast(@edpage as varchar(10))

EXEC (@str)

END

--------------------------------查询年月季度的起始日期和结束日期的方法；

DateTime starttime = wp.BeginTime;

DateTime endtime = wp.OverTime;

wp.OverTime = wp.BeginTime;

while (wp.OverTime <=endtime)

{

switch(tag)

{

//1:年2：季度3：月

case 1: wp.OverTime =DateProvider.LastDateOfYear(wp.BeginTime);break;

case 2: wp.OverTime =DateProvider.LastDateOfQuarter(wp.BeginTime);break;

case 3: wp.OverTime = DateProvider.LastDateOfMonth(wp.BeginTime);break;

}

//查询每个时间间隔的数据

SafePdLevel sp = GetData(wp);

sp.dateId = wp.OverTime;

list.Add(sp);

wp.BeginTime = wp.OverTime.AddDays(1);

}

----------------------------excel导入全部代码：

public static System.Data.DataTable DataTableforExcel(string strExcelFileName, string strSheetName,int columnsCount)

{

System.Data.DataTable dt = new System.Data.DataTable();

for (int i = 0; i <columnsCount; i++)

{

dt.Columns.Add(new DataColumn());

}

Excel.Application excel = new Excel.Application();

Excel.Workbook m_objBook = (Excel.Workbook)(excel.Workbooks.Add(strExcelFileName));

Worksheet sheet = null;

foreach (Worksheet wsheet in m_objBook.Worksheets)

{

if (wsheet.Name == strSheetName)

{

sheet = wsheet;

break;

}

if (sheet != null)

{

int row = 2;

while (true)

{

Range rName = sheet.Cells[row, 1] as Range;

if (rName.Text.ToString().Trim().Length == 0)

{

break;

}

DataRow dr = dt.NewRow();

for (int i = 0; i <columnsCount; i++)

{

Range rContent = sheet.Cells[row, i + 1] as Range;

dr[i] = rContent.Text;

}

dt.Rows.Add(dr);

row += 1;

}

excel.Quit();

return dt;

}

---开始导入

public partial class Personnel_PPersonImport: System.Web.UI.Page

{

PPersonMessaManager pmm = new PPersonMessaManager();

PPartManager ppm = new PPartManager();

PDutyManager pdm = new PDutyManager();

protected void btnadd_Click(object sender, EventArgs e)

{

if (this.FileUpload1.HasFile)

{

//上传文件

string path = Server.MapPath("../File/PersonImport/");

FileUpload1.PostedFile.SaveAs(path + FileUpload1.FileName);

//生成datatable

string path1 = Server.MapPath("../File/PersonImport/"+FileUpload1.FileName);

DataTable table = PM.Frame.DataFromExcel.DataTableforExcel(path1, "Sheet1",20);

if (table != null && table.Rows.Count !=0)

{

Improt(table);

Messabox.ShowError(this,"已成功导入");

}

else

{

Messabox.ShowError(this, "没有数据要导入");

}

else

{

Messabox.ShowError(this, "请选择上传的人员文件");

}

protected void btnback_Click(object sender, EventArgs e)

{

}

protected bool Improt(DataTable table)

{

for (int i = 0; i < table.Rows.Count; i++)

{

PPersonMessa pm = new PPersonMessa();

pm.Id = pmm.GetMaxId();

//部门

PPart pp = new PPart();

pp.PName = table.Rows[i][1].ToString().Trim();

pp.SJ = -1;

List<PPart> list = ppm.GetList(pp);

if (list != null && list.Count != 0)

{

pm.PartId = new PPart {Id=list[0].Id};

}

//职位

PDuty pd = new PDuty();

pd.DName= table.Rows[i][9].ToString().Trim();

List<PDuty> list1 = pdm.GetList(pd);

if (list1 != null && list1.Count != 0)

{

pm.GangName = new PDuty { Id = list1[0].Id};

}

//其他

pm.PerName = table.Rows[i][2].ToString().Trim();

pm.Sex = table.Rows[i][3].ToString().Trim();

pm.Nation = table.Rows[i][4].ToString().Trim();

pm.Address = table.Rows[i][5].ToString().Trim();

pm.BirAddress = table.Rows[i][6].ToString().Trim();

pm.Face= table.Rows[i][7].ToString().Trim();

pm.DangTime= table.Rows[i][8].ToString().Trim();

pm.IdCard = table.Rows[i][10].ToString().Trim();

try

{

pm.WorkTime =Convert.ToDateTime(table.Rows[i][11].ToString().Trim());

}

catch { }

pm.Education= table.Rows[i][12].ToString().Trim();

pm.EducationTime = table.Rows[i][13].ToString().Trim();

pm.School = table.Rows[i][14].ToString().Trim();

pm.Major = table.Rows[i][15].ToString().Trim();

pm.ZhiCheng = table.Rows[i][16].ToString().Trim();

pm.ZiGe = table.Rows[i][17].ToString().Trim();

pm.Phone= table.Rows[i][18].ToString().Trim();

pm.PerState = table.Rows[i][19].ToString().Trim();

//插入

pmm.Add(pm);

}

return true;

}

---------------------------------查询是否存在某列

USE EF

SELECT COUNT(1) FROM syscolumns WHERE id=object_id('Orders') AND [name]='Name'

------

public static bool Exists(SqlDataReader reader, string field)

{

bool b = false;

for (int i = 0; i < reader.FieldCount; i++)

{

if (reader.GetName(i).Equals(field))

{

if (reader[field] != System.DBNull.Value)

{

b = true; break;

}

return b;

}