2005年10月18日随笔档案 - Konimeter

2005年10月18日

GetPictures

获取图片列表（进入相册管理的时候）

GetPictures

public override ThreadSet GetPictures(GalleryThreadQuery query)

{

using( SqlConnection connection = GetSqlConnection() )

{

using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_gallery_Threads_GetThreadSet", connection))

{

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.SectionID;

command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;

command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = query.PageSize;

command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = SqlGenerator.BuildGalleryThreadQuery(query, databaseOwner);

command.Parameters.Add("@UserID", SqlDbType.Int).Value = query.UserID;

command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = query.IncludeCategories;

command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;

command.Parameters.Add(SettingsIDParameter());

ThreadSet ts = new ThreadSet();

// Execute the command

//If we want to display the list of categories for the post, we will need to

//use a dataset so we can join the child records

if(query.IncludeCategories)

{

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter(command);

//wait as long as possible to open the conn

connection.Open();

da.Fill(ds);

connection.Close();

//keep a referece to the relation since it is used in the GetChildRows look up anyway

DataRelation relation = new DataRelation("Categories",ds.Tables[0].Columns["PostID"],ds.Tables[1].Columns["PostID"],false);

ds.Relations.Add(relation);

DataRowCollection posts = ds.Tables[0].Rows;

foreach(DataRow dr in posts)

ts.Threads.Add(PopulatePictureFromIDataReader(dr, relation));

ds.Clear();

ds.Dispose();

ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;

}

else

{

//No categories needed, so we can use a datareader.

connection.Open();

using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))

{

while(dr.Read())

ts.Threads.Add(PopulatePictureFromIDataReader(dr));

dr.NextResult();

ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;

}

return ts;

}

posted @ 2005-10-18 16:18 Konimeter 阅读(101) 评论(0) 编辑

CreatePicture和CreatePictureData

先新增一条新增相片的记录

CreatePicture

public override void CreatePicture(Picture picture)

{

using( SqlConnection myConnection = GetSqlConnection() )

{

SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_CreateUpdate", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

// Add parameters

myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = picture.SectionID;

myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = picture.ParentID;

myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = true;

myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = 0;

myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = picture.Subject;

myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = picture.IsLocked;

myCommand.Parameters.Add("@PostType", SqlDbType.Int).Value = picture.PostType;

myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = picture.EmoticonID;

myCommand.Parameters.Add("@PostAuthor", SqlDbType.NVarChar, 64).Value = picture.Username;

myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = picture.AuthorID;

myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = picture.Body;

myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = picture.FormattedBody;

myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar, 32).Value = picture.UserHostAddress;

myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = picture.IsSticky;

myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = picture.StickyDate;

myCommand.Parameters.Add(this.SettingsIDParameter());

SerializerData data = picture.GetSerializerData();

myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;

myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

myCommand.Parameters.Add("@PostID", SqlDbType.Int).Direction = ParameterDirection.Output;

myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Direction = ParameterDirection.Output;

myConnection.Open();

myCommand.ExecuteNonQuery();

// LN 5/27/04: try/catch added to get rid of exceptions

try

{

picture.PostID = (int) myCommand.Parameters["@PostID"].Value;

picture.ThreadID = (int) myCommand.Parameters["@ThreadID"].Value;

}

catch {}

if (picture.PostID == -1)

{

myConnection.Close();

throw new CSException(CSExceptionType.PostDuplicate);

}

myConnection.Close();

}

然后再将相片文件存入DB

CreatePictureData

public override void CreatePictureData(Picture picture, PostAttachment pictureData)

{

using( SqlConnection connection = GetSqlConnection() )

{

SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment_Add", connection);

myCommand.CommandType = CommandType.StoredProcedure;

// Add parameters

myCommand.Parameters.Add("@AttachmentID", SqlDbType.UniqueIdentifier).Value = picture.PictureData.AttachmentID;

//guid //

myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = picture.PostID;

//21 //

myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = picture.AuthorID;

//2105 //

myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = picture.SectionID;

//15 //

myCommand.Parameters.Add("@Filename", SqlDbType.NVarChar, 256).Value = pictureData.FileName;

//xin_410902010922890994356.jpg //

myCommand.Parameters.Add("@RealFilename", SqlDbType.NVarChar, 256).Value = pictureData.RealFileName;

//1000.15.21 //1000.SectionID.PostID

myCommand.Parameters.Add("@Content", SqlDbType.Image).Value = pictureData.Content;

myCommand.Parameters.Add("@ContentType", SqlDbType.NVarChar, 50).Value = pictureData.ContentType;

//image/jpeg

myCommand.Parameters.Add("@ContentSize", SqlDbType.Int).Value = pictureData.Length;

//49790

myCommand.Parameters.Add(this.SettingsIDParameter());

connection.Open();

myCommand.ExecuteNonQuery();

connection.Close();

}

posted @ 2005-10-18 15:38 Konimeter 阅读(199) 评论(0) 编辑

GetPost

/// <summary>

/// Get basic information about a single post. This method returns an instance of the Post class,

/// which contains less information than the PostDeails class, which is what is returned by the

/// GetPostDetails method.

/// </summary>

/// <param name="PostID">The ID of the post whose information we are interested in.</param>

/// <returns>An instance of the Post class.</returns>

/// <remarks>If a PostID is passed in that is NOT found in the database, a PostNotFoundException

/// exception is thrown.</remarks>

public override ForumPost GetPost(int postID, int userID, bool trackViews)

{

// Create Instance of Connection and Command Object

using( SqlConnection myConnection = GetSqlConnection() )

{

SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Post", myConnection);

// Mark the Command as a SPROC

myCommand.CommandType = CommandType.StoredProcedure;

// Add Parameters to SPROC

myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;

myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;

myCommand.Parameters.Add("@TrackViews", SqlDbType.Bit).Value = trackViews;

myCommand.Parameters.Add(this.SettingsIDParameter());

// Execute the command

myConnection.Open();

ForumPost p = null;

using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))

{

if (!dr.Read())

{

dr.Close();

myConnection.Close();

// we did not get back a post

throw new CSException(CSExceptionType.PostNotFound, postID.ToString());

}

p = PopulatePostFromIDataReader(dr);

dr.Close();

}

myConnection.Close();

// we have a post to work with

return p;

}

posted @ 2005-10-18 14:36 Konimeter 阅读(114) 评论(0) 编辑

AddPost

/// <summary>

/// Adds a new Post. This method checks the allowDuplicatePosts settings to determine whether

/// or not to allow for duplicate posts. If allowDuplicatePosts is set to false and the user

/// attempts to enter a duplicate post, a PostDuplicateException exception is thrown.

/// </summary>

/// <param name="PostToAdd">A Post object containing the information needed to add a new

/// post. The essential fields of the Post class that must be set are: the Subject, the

/// Body, the Username, and a ForumID or a ParentID (depending on whether the post to add is

/// a new post or a reply to an existing post, respectively).</param>

/// <returns>A Post object with information on the newly inserted post. This returned Post

/// object includes the ID of the newly added Post (PostID) as well as if the Post is

/// Approved or not.</returns>

public override ForumPost AddPost (ForumPost post, int userID, bool autoApprove)

{

int postID = -1;

// Create Instance of Connection and Command Object

using( SqlConnection myConnection = GetSqlConnection() )

{

SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_CreateUpdate", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

// Add parameters

myCommand.Parameters.Add(this.SettingsIDParameter());

myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;

//3 //论坛板块

myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = post.ParentID;

//0 //

myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = sqlHelper.GetSiteSettings().EnableDuplicatePosts;

//false

myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = sqlHelper.GetSiteSettings().DuplicatePostIntervalInMinutes;

//15

myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = post.Subject;

//论坛跟踪，发表新贴

myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;

//false

myCommand.Parameters.Add("@IsTracked", SqlDbType.Bit).Value = post.IsTracked;

//false

myCommand.Parameters.Add("@PostType", SqlDbType.Int).Value = post.PostType;

//HTML

myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = post.EmoticonID;

//0

myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;

//2105

myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = post.Body;

//test

myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = post.FormattedBody;

//test

myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar, 32).Value = post.UserHostAddress;

//127.0.0.1

myCommand.Parameters.Add("@PostID", SqlDbType.Int).Direction = ParameterDirection.Output;

// //output参数

if( userID == 0 )

{

myCommand.Parameters.Add("@PostAuthor", SqlDbType.NVarChar, 128).Value = post.Username;

}

if (post is Thread)

{

myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = ((Thread) post).IsSticky;

//false

myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = ((Thread) post).StickyDate;

//10/18/1980

}

SerializerData data = post.GetSerializerData();

myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;

//null

myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

//null

// If autoApprove is true, mark it as approved

if(autoApprove)

myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = true;

myCommand.Parameters.Add("@PostConfiguration", SqlDbType.Int).Value = post.PostConfiguration;

//0

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

// LN 5/27/04: try/catch added to get rid of exceptions

try

{

postID = (int) myCommand.Parameters["@PostID"].Value;

}

catch {}

if (postID == -1)

{

throw new CSException(CSExceptionType.PostDuplicate);

}

// Return the newly inserted Post

return GetPost(postID, userID, false);

}

posted @ 2005-10-18 12:29 Konimeter 阅读(131) 评论(0) 编辑

UpdatePost

返回为BlogPostResults类型自定义枚举类型
参数为WeblogPost类型自定义对象变量

UpdatePost

public override BlogPostResults UpdatePost(WeblogPost post, int editedBy)

{

// Create Instance of Connection and Command Object

using( SqlConnection myConnection = GetSqlConnection() )

{

SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_weblog_Post_Update", myConnection);

// Mark the Command as a SPROC

myCommand.CommandType = CommandType.StoredProcedure;

myCommand.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

// Add Parameters to SPROC

myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;

myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = post.PostID;

myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = post.Subject;

myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = post.Body;

myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = post.FormattedBody;

myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = post.EmoticonID;

myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;

myCommand.Parameters.Add("@IsTracked", SqlDbType.Bit).Value = post.IsTracked;

myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = post.IsApproved;

myCommand.Parameters.Add("@EditedBy", SqlDbType.Int, 4).Value = editedBy;

myCommand.Parameters.Add("@PostDate",SqlDbType.DateTime,8).Value = post.PostDate;

myCommand.Parameters.Add("@BloggerTime",SqlDbType.DateTime,8).Value = post.BloggerTime;

SerializerData data = post.GetSerializerData();

myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;

myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

myCommand.Parameters.Add(SettingsIDParameter());

// Allow Thread to update sticky properties.

if (post is IThread)

{

IThread thread = (IThread) post;

myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = thread.IsSticky;

myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = thread.StickyDate;

}

myCommand.Parameters.Add("@Excerpt",SqlDbType.NVarChar,500).Value = DBValue(post.Excerpt);

myCommand.Parameters.Add("@PostName",SqlDbType.NVarChar,256).Value = DBValue(post.Name);

myCommand.Parameters.Add("@TitleUrl",SqlDbType.NVarChar,256).Value = DBValue(post.TitleUrl);

myCommand.Parameters.Add("@PostConfig",SqlDbType.Int,4).Value = post.PostConfig;

myCommand.Parameters.Add("@BlogPostType",SqlDbType.TinyInt).Value = post.BlogPostType;

if(post.BlogPostType == BlogPostType.Post)

{

myCommand.Parameters.Add("@CategoryType",SqlDbType.TinyInt).Value = CategoryType.BlogPost;

myCommand.Parameters.Add("@Categories",SqlDbType.NVarChar,4000).Value = ConvertCategoriesToXML(post.Categories);

}

else if(post.BlogPostType == BlogPostType.Article)

{

myCommand.Parameters.Add("@CategoryType",SqlDbType.TinyInt).Value = CategoryType.BlogArticle;

myCommand.Parameters.Add("@Categories",SqlDbType.NVarChar,4000).Value = ConvertCategoriesToXML(post.Categories);

}

// Execute the command

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

BlogPostResults result = (BlogPostResults)(int)myCommand.Parameters["RETURN_VALUE"].Value;

return result;

}

posted @ 2005-10-18 12:15 Konimeter 阅读(90) 评论(0) 编辑

GetWeblogs

/// <summary>

/// Returns all of the Weblogs for the current settingsID

/// </summary>

/// <returns></returns>

public override Hashtable GetWeblogs()

{

Hashtable weblogs = new Hashtable();

using( SqlConnection myConnection = GetSqlConnection() )

{

SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_weblog_Weblogs_Get", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

// Add Parameters to SPROC

myCommand.Parameters.Add(this.SettingsIDParameter());

// Execute the command

myConnection.Open();

SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

// Get the requested weblogs

Weblog w = null;

while (dr.Read())

{

w = new Weblog();

PopulateWeblogData(dr,w);

// add all weblogs into the Hashtable

weblogs.Add(w.ApplicationKey.ToLower(), w);

}

// Get the permissions

if (dr.NextResult())

{

while (dr.Read())

{

// Get the forum

string appKey = dr["ApplicationKey"] as string;

// Specific permissions

if (appKey != null) {

w = weblogs[appKey.ToLower()] as Weblog;

if(w != null) {

WeblogPermission bp = new WeblogPermission();

CommonDataProvider.PopulatePermissionFromIDataReader( bp, dr );

w.PermissionSet.Add(bp.Name, bp);

}

} else {

// Global permissions

WeblogPermission bp = new WeblogPermission();

CommonDataProvider.PopulatePermissionFromIDataReader( bp, dr );

foreach (Weblog wb in weblogs.Values) {

if(!wb.PermissionSet.ContainsKey(bp.Name))

wb.PermissionSet.Add(bp.Name,bp);

}

// Done with the reader and the connection

dr.Close();

myConnection.Close();

}

return weblogs;

}

posted @ 2005-10-18 11:55 Konimeter 阅读(103) 评论(0) 编辑

GetWeblogPost

返回WeblogPost类型的自定义对象变量

GetWeblogPost

/**//// <summary>

/// Returns an instance of WeblogPost.

/// </summary>

public override WeblogPost GetWeblogPost(int postID, bool includeCategories, bool isApproved)

{

WeblogPost entry = null;

using( SqlConnection connection = GetSqlConnection() )

{

using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_Post_Get", connection))

{

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;

//12

//Blog的记录ID

command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = includeCategories;

//false

//没见过True，都是False，不知道什么意思。

command.Parameters.Add("@IsApprovedFilter", SqlDbType.Bit).Value = isApproved;

//True

//是否审批，表示该Blog是否已经经过审核，经过审核的才能Show出来。

command.Parameters.Add(SettingsIDParameter());

//1000

//一般是1000或1001，不知道什么意思。。。

connection.Open();

using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))

{

if(reader.Read())

{

entry = PopulateWeblogEntryFromIDataReader(reader);

//将从数据库的读取的记录里的数据写入WeblogPost自定义对象变量

}

if(includeCategories && reader.NextResult())

{

ArrayList al = new ArrayList();

while(reader.Read())

{

al.Add(reader["Name"] as string);

}

entry.Categories = (string[])al.ToArray(typeof(string));

}

reader.Close();

}

return entry;

}

posted @ 2005-10-18 11:43 Konimeter 阅读(97) 评论(0) 编辑

GetPostsByMonths

获取每个月的Post数量，一般用于首页的“列表”

@SectionID=BlogID=14，氯胺酮的Blog，

结果样式如下：
2005年10月（10）
2005年9月（2）

GetPostsBtMonth

posted @ 2005-10-18 11:28 Konimeter 阅读(83) 评论(0) 编辑

GetPostsByMonth

获取一个月内所有的帖子

@SectionID=14，表示氯胺酮的Blog
@Year=2005，年份
@Month=10，月份

public override Hashtable GetPostsByMonth(int BlogID, DateTime month)

{

using( SqlConnection connection = GetSqlConnection() )

{

using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_PostsByMonth", connection))

{

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = BlogID;

//14

//个人blog的记录的ID

command.Parameters.Add("@Year", SqlDbType.Int, 4).Value = month.Year;

//2005

//年

command.Parameters.Add("@Month", SqlDbType.Int, 4).Value = month.Month;

//10

//月

connection.Open();

using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))

{

Hashtable items = new Hashtable();

ArchiveDataItem ad = null;

while(reader.Read())

{

ad = new ArchiveDataItem();

ad.Date = new DateTime((int)reader["Year"],(int)reader["Month"],(int)reader["Day"]);

ad.Count = (int)reader["Count"];

items.Add(ad.Date.Day,ad);

}

reader.Close();

connection.Close();

command.Dispose();

return items;

}

posted @ 2005-10-18 11:16 Konimeter 阅读(85) 评论(0) 编辑

Konimeter的Blog主站	Email：	koni@21cn.com
Konimeter@Solartimes	QQ：	670955
Konimeter@ShangXun	MSN：	konimeter@hotmail.com