MSSQL编程：以增添保藏夹功效为实例，剖析asp.net ...

使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性asp.net|计划
tdid="1"class="ControlPanelTabInactive"align="center"nowrap><ahref="<%=Globals.GetSiteUrls().MyFavorites%>"><%=ResourceManager.GetString("MyFavorites_Title")%></a></td>修正：<tdcolspan=11class="ControlPanelTabLine"><imgwidth="1"height=1alt=""></td>跨跃列数五.增添响应文件体现层1，保藏夹主视图在web/user/目次增添MyFavorites.aspx，终极用户页面在ControlsViews目次增添MyFavoritesView.cs,页面视图服务器控件（次要体现为页面处置逻辑）界面视图：在WebThemesdefaultkins中增添View-MyFavorites.ascx保藏夹视图（次要体现为页面UI）组件在Components目次增添Favorites.cs（相称于营业逻辑层，加s表营业处置），此例中未在子目次Components/Components中增添Favorite.cs(相称于营业实体层，未加s表实体)，因其实不必要，完全的Asp.netforums形式应当另有这一层。体现层2，用户点击保藏按钮后出现的UI（这个对照复杂）在web目次增添MyFavoritesAdd.aspx文件处置到场保藏时服务器控件,在Controls目次增添MyFavoritesAdd.cs（页面处置逻辑）在WebThemesdefaultkins中增添Skin-MyFavoritesAdd.ascx将主题到场保藏时的视图(UI)六．数据库增添表forums_FavoritesUserIDint40ThreadIDint40FavoriteDatedatetime80创立存储历程forums_Favorites_CreateDeleteCREATEprocedureforums_Favorites_CreateDelete(@UserIDint,@ThreadIDint,@Actionint)ASBEGINIF@Action=0BEGIN--Doestheuseralreadyhavetheabilitytoseethisthread?IFEXISTS(SELECTUserIDFROMforums_FavoritesWHEREUserID=@UserIDandThreadID=@ThreadID)returnINSERTINTOforums_FavoritesVALUES(@UserID,@ThreadID,getdate())RETURNENDIF@Action=2BEGINDELETEforums_FavoritesWHEREUserID=@UserIDANDThreadID=@ThreadIDRETURNENDENDGOSETQUOTED_IDENTIFIEROFFGOSETANSI_NULLSONGO七.数据处置1.ComponentsProviderForumsDataProvider.cs增添#region保藏夹publicabstractvoidCreateFavorites(ArrayListusers,intthreadID);publicabstractvoidDeleteFavorites(intuserID,ArrayListdeleteList);#endregion2.DataProvidersqlDataProviderqlDataProvider.cs增添完成办法#region####保藏夹####byvenjiang0912///<summary>///追加主题到保藏夹///</summary>///<paramname="userID">用户ID</param>///<paramname="threadID">主题ID</param>publicoverridevoidCreateFavorites(intuserID,intthreadID){using(SqlConnectionmyConnection=GetSqlConnection()){SqlCommandmyCommand=newSqlCommand(databaseOwner+".forums_Favorites_CreateDelete",myConnection);myCommand.CommandType=CommandType.StoredProcedure;myCommand.Parameters.Add("@Action",SqlDbType.Bit).Value=DataProviderAction.Create;myCommand.Parameters.Add("@UserID",SqlDbType.Int);myCommand.Parameters.Add("@ThreadID",SqlDbType.Int);myConnection.Open();myCommand.Parameters["@UserID"].Value=userID;myCommand.Parameters["@ThreadID"].Value=threadID;myCommand.ExecuteNonQuery();}}///<summary>///从保藏夹中删除主题///</summary>///<paramname="userID">用户ID</param>///<paramname="deleteList">删除列表</param>publicoverridevoidDeleteFavorites(intuserID,ArrayListdeleteList){//CreateInstanceofConnectionandCommandObjectusing(SqlConnectionmyConnection=GetSqlConnection()){SqlCommandmyCommand=newSqlCommand(databaseOwner+".forums_Favorites_CreateDelete",myConnection);myCommand.CommandType=CommandType.StoredProcedure;myCommand.Parameters.Add("@Action",SqlDbType.Int).Value=DataProviderAction.Delete;myCommand.Parameters.Add("@UserID",SqlDbType.Int).Value=userID;myCommand.Parameters.Add("@ThreadID",SqlDbType.Int);//OpentheconnectionmyConnection.Open();//Addmultipletimes//foreach(intthreadIDindeleteList){myCommand.Parameters["@ThreadID"].Value=threadID;myCommand.ExecuteNonQuery();}}}#endregion3.在DataProvidersqlDataProviderqlDataProvider.cs修正GetThreads办法,以撑持保藏功效#region####Threads####//增添贴子保藏byvenjiang0911publicoverrideThreadSetGetThreads(intforumID,intpageIndex,intpageSize,intuserID,DateTimethreadsNewerThan,SortThreadsBysortBy,SortOrdersortOrder,ThreadStatusthreadStatus,ThreadUsersFilteruserFilter,boolactiveTopics,boolunreadOnly,boolunansweredOnly,boolreturnRecordCount,//增添新参数，是不是仅显现保藏的主题boolfavoriteOnly){//CreateInstanceofConnectionandCommandObject//using(SqlConnectionconnection=GetSqlConnection()){SqlCommandcommand=newSqlCommand(databaseOwner+".forums_Threads_GetThreadSet",connection);command.CommandType=CommandType.StoredProcedure;ThreadSetthreadSet=newThreadSet();StringBuildersqlCountSelect=newStringBuilder("SELECTcount(T.ThreadID)");StringBuildersqlPopulateSelect=newStringBuilder("SELECTT.ThreadID,HasRead=");StringBuilderfromClause=newStringBuilder("FROM"+this.databaseOwner+".forums_ThreadsT");StringBuilderwhereClause=newStringBuilder("WHERE");StringBuilderorderClause=newStringBuilder("ORDERBY");//增添保藏判别byvenjiang0911if(favoriteOnly==true){fromClause.Append(","+this.databaseOwner+".forums_FavoritesFav");}//EnsureDateTimeisminvalueforSQL//threadsNewerThan=SqlDataProvider.GetSafeSqlDateTime(threadsNewerThan);//Constructtheclauses#regionConstrainForums//Contraintheselectivnesstoasetofspecifiedforums.TheForumIDisour//clusteredindexsowewantthistobefirstif(forumID>0){whereClause.Append("T.ForumID=");whereClause.Append(forumID);}elseif(forumID<0){whereClause.Append("(T.ForumID=");//Getalistofalltheforumstheuserhasaccessto//ArrayListforumList=Forums.GetForums(userID,false,true);for(inti=0;i<forumList.Count;i++){if(((Forum)forumList[i]).ForumID>0){if((i+1)<forumList.Count){whereClause.Append(((Forum)forumList[i]).ForumID+"ORT.ForumID=");}else{whereClause.Append(((Forum)forumList[i]).ForumID);whereClause.Append(")");}}}}else{whereClause.Append("T.ForumID=0ANDP.UserID=");whereClause.Append(userID);whereClause.Append("ANDP.ThreadID=T.ThreadID");fromClause.Append(","+this.databaseOwner+".forums_PrivateMessagesP");}#endregion#regionConstrainDatewhereClause.Append("ANDStickyDate>=");whereClause.Append(threadsNewerThan.ToString(System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.SortableDateTimePattern));whereClause.Append("");#endregion#regionConstainApprovalwhereClause.Append("ANDIsApproved=1");#endregion#regionConstrainRead/Unreadif(userID>0){sqlPopulateSelect.Append("(SELECT"+this.databaseOwner+".HasReadPost(");sqlPopulateSelect.Append(userID);sqlPopulateSelect.Append(",T.ThreadID,T.ForumID))");if(unreadOnly){whereClause.Append("AND"+this.databaseOwner+".HasReadPost(");whereClause.Append(userID);whereClause.Append(",T.ThreadID,T.ForumID)=0");}}else{sqlPopulateSelect.Append("0");}#endregion#regionUnansweredtopicsif(unansweredOnly){whereClause.Append("ANDTotalReplies=0ANDIsLocked=0");}#endregion#regionActivetopics//抢手贴子if(activeTopics){whereClause.Append("ANDTotalReplies>2ANDIsLocked=0ANDTotalViews>50");}#endregion#region保藏//尽显现保藏的主题if(favoriteOnly){whereClause.Append("ANDT.ThreadID=Fav.ThreadIDANDFav.UserID=");whereClause.Append(userID);}#endregion#regionUsersfilterif(userFilter!=ThreadUsersFilter.All){if((userFilter==ThreadUsersFilter.HideTopicsParticipatedIn)||(userFilter==ThreadUsersFilter.HideTopicsNotParticipatedIn)){whereClause.Append("AND");whereClause.Append(userID);if(userFilter==ThreadUsersFilter.HideTopicsNotParticipatedIn)whereClause.Append("NOT");whereClause.Append("IN(SELECTUserIDFROM"+this.databaseOwner+".forums_PostsPWHEREP.ThreadID=T.ThreadID)");}else{if(userFilter==ThreadUsersFilter.HideTopicsByNonAnonymousUsers)whereClause.Append("AND0NOT");elsewhereClause.Append("AND0");whereClause.Append("IN(SELECTUserIDFROM"+this.databaseOwner+".forums_PostsPWHEREThreadID=T.ThreadIDANDP.UserID=0)");}}#endregion#regionThreadStatusif(threadStatus!=ThreadStatus.NotSet){switch(threadStatus){caseThreadStatus.Open:whereClause.Append("ANDThreadStatus=0");break;caseThreadStatus.Closed:whereClause.Append("ANDThreadStatus=0");break;caseThreadStatus.Resolved:whereClause.Append("ANDThreadStatus=0");break;default:break;}}#endregion#regionOrderByswitch(sortBy){caseSortThreadsBy.LastPost:if(sortOrder==SortOrder.Ascending){if(activeTopics||unansweredOnly)orderClause.Append("ThreadDate");elseorderClause.Append("IsSticky,StickyDate");}else{if(activeTopics||unansweredOnly)orderClause.Append("ThreadDateDESC");elseorderClause.Append("IsStickyDESC,StickyDateDESC");}break;caseSortThreadsBy.TotalRatings:if(sortOrder==SortOrder.Ascending)orderClause.Append("TotalRatings");elseorderClause.Append("TotalRatingsDESC");break;caseSortThreadsBy.TotalReplies:if(sortOrder==SortOrder.Ascending)orderClause.Append("TotalReplies");elseorderClause.Append("TotalRepliesDESC");break;caseSortThreadsBy.ThreadAuthor:if(sortOrder==SortOrder.Ascending)orderClause.Append("PostAuthorDESC");elseorderClause.Append("PostAuthor");break;caseSortThreadsBy.TotalViews:if(sortOrder==SortOrder.Ascending)orderClause.Append("TotalViews");elseorderClause.Append("TotalViewsDESC");break;}#endregion//BuildtheSQLstatementssqlCountSelect.Append(fromClause.ToString());sqlCountSelect.Append(whereClause.ToString());sqlPopulateSelect.Append(fromClause.ToString());sqlPopulateSelect.Append(whereClause.ToString());sqlPopulateSelect.Append(orderClause.ToString());//AddParameterstoSPROC//command.Parameters.Add("@ForumID",SqlDbType.Int).Value=forumID;command.Parameters.Add("@PageIndex",SqlDbType.Int,4).Value=pageIndex;command.Parameters.Add("@PageSize",SqlDbType.Int,4).Value=pageSize;command.Parameters.Add("@sqlCount",SqlDbType.NVarChar,4000).Value=sqlCountSelect.ToString();command.Parameters.Add("@sqlPopulate",SqlDbType.NVarChar,4000).Value=sqlPopulateSelect.ToString();command.Parameters.Add("@UserID",SqlDbType.Int).Value=userID;command.Parameters.Add("@ReturnRecordCount",SqlDbType.Bit).Value=returnRecordCount;//Executethecommandconnection.Open();SqlDataReaderdr=command.ExecuteReader();//PopulatetheThreadSet//while(dr.Read()){//Addthreads//if(forumID==0)threadSet.Threads.Add(ForumsDataProvider.PopulatePrivateMessageFromIDataReader(dr));elsethreadSet.Threads.Add(ForumsDataProvider.PopulateThreadFromIDataReader(dr));}//Doweneedtoreturnrecordcount?//if(returnRecordCount){dr.NextResult();dr.Read();//Readthetotalrecords//threadSet.TotalRecords=(int)dr[0];}//Gettherecipientsifthisisarequestfor//theprivatemessagelistif((forumID==0)&&(dr.NextResult())){HashtablerecipientsLookupTable=newHashtable();while(dr.Read()){intthreadID=(int)dr["ThreadID"];if(recipientsLookupTable[threadID]==null){recipientsLookupTable[threadID]=newArrayList();}((ArrayList)recipientsLookupTable[threadID]).Add(ForumsDataProvider.PopulateUserFromIDataReader(dr));}//Maprecipientstothethreads//foreach(PrivateMessagethreadinthreadSet.Threads){thread.Recipients=(ArrayList)recipientsLookupTable[thread.ThreadID];}}dr.Close();connection.Close();returnthreadSet;}}#endregion八.增添新办法在ComponentsThreads.cs增添新的重载办法,以不用修正本来的办法挪用.//为了不影响之前的程序,独自加一个重载办法,以取得保藏夹主题publicstaticThreadSetGetThreads(intforumID,intpageIndex,intpageSize,intuserID,DateTimethreadsNewerThan,SortThreadsBysortBy,SortOrdersortOrder,ThreadStatusthreadStatus,ThreadUsersFilteruserFilter,boolactiveTopics,boolunreadOnly,boolunansweredOnly,boolreturnRecordCount,boolfavoriteOnly)//多了一个参数favoriteOnly{ForumContextforumContext=ForumContext.Current;stringanonymousKey="Thread-"+forumID+pageSize.ToString()+pageIndex.ToString()+threadsNewerThan.DayOfYear.ToString()+sortBy+sortOrder+activeTopics.ToString()+unansweredOnly.ToString()+favoriteOnly.ToString();ThreadSetthreadSet;//Iftheuserisanonymoustakesomeloadoffthedb//if(userID==0){if(forumContext.Context.Cache[anonymousKey]!=null)return(ThreadSet)forumContext.Context.Cache[anonymousKey];}//CreateInstanceoftheIDataProvider//ForumsDataProviderdp=ForumsDataProvider.Instance();//Getthethreads//threadSet=dp.GetThreads(forumID,pageIndex,pageSize,userID,threadsNewerThan,sortBy,sortOrder,threadStatus,userFilter,activeTopics,unreadOnly,unansweredOnly,returnRecordCount,favoriteOnly);if(userID==0)forumContext.Context.Cache.Insert(anonymousKey,threadSet,null,DateTime.Now.AddMinutes(2),TimeSpan.Zero,CacheItemPriority.Low,null);returnthreadSet;}九．营业逻辑层Components目次中增添Favorites.cs，完成主题的增添删除办法publicstaticvoidAddFavoritesPost(intuserID,intthreadID){ForumsDataProviderdp=ForumsDataProvider.Instance();dp.CreateFavorites(userID,threadID);}///<summary>///删除保藏///</summary>///<paramname="userID">用户ID</param>///<paramname="deleteList">删除列表</param>publicstaticvoidDeleteFavorites(intuserID,ArrayListdeleteList){//ForumsDataProviderdp=ForumsDataProvider.Instance();dp.DeleteFavorites(userID,deleteList);}十．体现层挪用1.保藏夹主视图加载保藏主题列表threadSet=Threads.GetThreads(forumID,pager.PageIndex,pager.PageSize,Users.GetUser().UserID,dateFilterValue,threadSortddl.SelectedValue,sortOrderddl.SelectedValue,ThreadStatus.NotSet,ThreadUsersFilter.All,false,hideReadPosts.SelectedValue,false,true,true);注重最初一个参数是true,即前往保藏夹的数据集。2.增添主题到保藏夹Favorites.AddFavoritesPost(user.UserID,post.ThreadID);HttpContext.Current.Response.Redirect(Globals.ApplicationPath+"/MyFavoritesAdd.aspx",true);3.删除保藏的主题Favorites.DeleteFavorites（…）
先说DDL的分类。有一类DDL，是不需要重建表的，比如加非聚簇索引。这类操作其实不会丢数据，也是在原表上直接操作，对于我们“以恢复数据为目的”的闪回，是可以先忽略的。另外一类，则是会影响到表数据的操作。

不过话说回来了，绝大多数的性能优化准则与对sqlserver存储的结构理解息息相关

呵呵，这就是偶想说的

不过话说回来了，绝大多数的性能优化准则与对sqlserver存储的结构理解息息相关

对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。

一个是把SQL语句写到客户端,可以使用DataSet进行加工;

总感觉自己还是不会SQL

然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情