sqlcollections.cs

企业内部的短信交流管理平台。 设计详细

using System;

namespace Com.SMS.Dal
{
	/// <summary>
	/// SqlCollections 的摘要说明。
	/// </summary>
	public class SqlCollections
	{
		/// <summary>
		/// 添加地址本联系人
		/// </summary>
		public const string SQL_AADDRESSBOOKDETAIL_INSERT="insert into AddressBookDetail values(@AddressBookId,@userId)"; 

		/// <summary>
		/// 删除地址本联系人
		/// </summary>
		public const string SQL_AADDRESSBOOKDETAIL_DELETE="delete AddressBookDetail where AddressBookDetailId=@AddressBookDetailId";

		/// <summary>
		/// 删除某个用户在地址本联系人中的记录
		/// </summary>
		public const string SQL_AADDRESSBOOKDETAIL_DELETEBYUSER="delete AddressBookDetail where AddressBookId in(select AddressBookId from AddressBooks where userId=@userId) or userId=@usersId";

		/// <summary>
		/// 修改地址本联系人
		/// </summary>
		public const string SQL_AADDRESSBOOKDETAIL_UPDATE="update AddressBookDetail set AddressBookId=@AddressBookId,userId=@userId where AddressBookDetailId=@AddressBookDetailId";

		/// <summary>
		/// 删除某个地址本下的联系人
		/// </summary>
		public const string SQL_AADDRESSBOOKDETAIL_DELETEBYBOOK="delete AddressBookDetail where AddressBookId=@AddressBookId";

		/// <summary>
		/// 按地址本编号查询联系人
		/// </summary>
		public const string SQL_AADDRESSBOOKDETAIL_SELECTBYBOOKID="SELECT dbo.AddressBookDetail.*, dbo.AddressBooks.AddressBookName, dbo.Users.* FROM dbo.AddressBookDetail INNER JOIN dbo.AddressBooks ON dbo.AddressBookDetail.AddressBookId = dbo.AddressBooks.AddressBookId INNER JOIN dbo.Users ON dbo.AddressBookDetail.userId = dbo.Users.userId where dbo.AddressBookDetail.AddressBookId=@AddressBookId";

		/// <summary>
		/// 创建地址本
		/// </summary>
		public const string SQL_ADDRESSBOOK_INSERT="insert into AddressBooks values(@userId,@AddressBookName,@bz)";

		/// <summary>
		/// 删除地址本
		/// </summary>
		public const string SQL_ADDRESSBOOK_DELETE="delete AddressBooks where AddressBookId=@AddressBookId";

		/// <summary>
		/// 删除某个用户的地址本
		/// </summary>
		public const string SQL_ADDRESSBOOK_DELETEBYUSER="delete AddressBooks where userId=@userId";

		/// <summary>
		/// 修改地址本
		/// </summary>
		public const string SQL_ADDRESSBOOK_UPDATE="update AddressBooks set userId=@userId,AddressBookName=@AddressBookName,bz=@bz where AddressBookId=@AddressBookId";

		/// <summary>
		/// 查询某个用户的地址本
		/// </summary>
		public const string SQL_ADDRESSBOOK_SELECTBYUSER="SELECT dbo.AddressBooks.*, dbo.Users.userName FROM dbo.AddressBooks INNER JOIN dbo.Users ON dbo.AddressBooks.userId = dbo.Users.userId where AddressBooks.userId=@userId";

		/// <summary>
		/// 查询所有的地址本
		/// </summary>
		public const string SQL_ADDRESSBOOK_SELECTALL="SELECT dbo.AddressBooks.*, dbo.Users userName FROM dbo.AddressBooks INNER JOIN dbo.Users ON dbo.AddressBooks.userId = dbo.Users.userId";

		/// <summary>
		/// 按编号查询地址本
		/// </summary>
		public const string SQL_ADDRESSBOOK_SELECTBYID="SELECT dbo.AddressBooks.*, dbo.Users.userName AS Expr1 FROM dbo.AddressBooks INNER JOIN dbo.Users ON dbo.AddressBooks.userId = dbo.Users.userId  where dbo.AddressBooks.AddressBookId=@AddressBookId";


		/// <summary>
		/// 添加相册
		/// </summary>
		public const string SQL_ALBUM_INSERT="insert into Album values(@alName,@typeId,@userId,@alFace)";

		/// <summary>
		/// 删除相册
		/// </summary>
		public const string SQL_ALBUM_DELETE="delete Album where abId=@abId";

		/// <summary>
		/// 删除某个用户的相册
		/// </summary>
		public const string SQL_ALBUM_DELETEBYUSER="delete Album where userId=@userId";

		/// <summary>
		/// 修改相册
		/// </summary>
		public const string SQL_ALBUM_UPDATE="update Album set alName=@alName,typeId=@typeId,userId=@userId,alFace=@alFace where abId=@abId";

		/// <summary>
		/// 按条件查询相册
		/// </summary>
		public const string SQL_ALBUM_SELECTWHERE="s_Album_SelectWhere";

		/// <summary>
		/// 按编号查询相册
		/// </summary>
		public const string SQL_ALBUM_SELECTBYID="select * from Album where abId=@abId";

		/// <summary>
		/// 没有分页条件查询相册
		/// </summary>
		public const string SQL_ALBUM_SELECTWHERENOPAGE="s_Album_SelectAll";

		/// <summary>
		/// 查询某个分类下的相册
		/// </summary>
		public const string SQL_ALBUM_SELECTBYTYPE="select * from Album where typeId=@typeId";

		/// <summary>
		/// 查询某个用户的相册
		/// </summary>
		public const string SQL_ALBUM_SELECTBYUSER="select * from Album where userId=@userId";

		/// <summary>
		/// 添加相册分类
		/// </summary>
		public const string SQL_ALBUMTYPE_INSERT="s_AlbumType_Insert";

		/// <summary>
		/// 删除相册分类
		/// </summary>
		public const string SQL_ALBUMTYPE_DELETE="delete AlbumType where abtId=@abtId";

		/// <summary>
		/// 修改相册分类
		/// </summary>
		public const string SQL_ALBUMTYPE_UPDATE="s_AlbumType_Update";

		/// <summary>
		/// 条件分页查询相册分类
		/// </summary>
		public const string SQL_ALBUMTYPE_SELECTWHERE="s_AlbumType_SelectWhere";

		/// <summary>
		/// 不分页查询所有相册分类
		/// </summary>
		public const string SQL_ALBUMTYPE_SELECTNOPAGE="select * from AlbumType";

		/// <summary>
		/// 按编号查询相册分类
		/// </summary>
		public const string SQL_ALBUMTYPE_SELECTBYID="select * from AlbumType where abtId=@abtId";

		/// <summary>
		/// 添加消息内容
		/// </summary>
		public const string SQL_CONTENTS_INSERT="insert into Contents values(@Content)";

		/// <summary>
		/// 删除消息内容
		/// </summary>
		public const string SQL_CONTENTS_DELETE="delete Contents where conId=@conId";

		/// <summary>
		/// 查看发送消息表中是否有对该内容引用
		/// </summary>
		public const string SQL_CONTENTS_ISHAVEINSEND="select count(*) from SendBox where conId=@conId";

		/// <summary>
		/// 查看接收消息表中是否有对该内容引用
		/// </summary>
		public const string SQL_CONTENTS_ISHAVEINRECIVE="select count(*) from ReciveBox where conId=@conId";

		/// <summary>
		/// 添加聊天消息
		/// </summary>
		public const string SQL_MESSAGES_INSERT="insert into Messages values(@Content,@sender,@recive,@sendTime)";

		/// <summary>
		/// 删除聊天消息内容
		/// </summary>
		public const string SQL_MESSAGES_DELETE="delete Messages where msgId=@msgId";

		/// <summary>
		/// 查询所有聊天消息
		/// </summary>
		public const string SQL_MESSAGES_SELECTALL="select * from Messages order by msgId desc";

		/// <summary>
		/// 添加新闻
		/// </summary>
		public const string SQL_NEWS_INSERT="insert into News values(@newsTitle,@newsContent,@sendId,@sendTime)";

		/// <summary>
		/// 查询所有新闻
		/// </summary>
		public const string SQL_NEWS_SELECTALL="SELECT dbo.News.*, dbo.Users.userName FROM dbo.News inner JOIN dbo.Users on dbo.Users.userId=dbo.News.sendId";

		/// <summary>
		/// 按编号查询新闻
		/// </summary>
		public const string SQL_NEWS_SELECTBYID="SELECT dbo.News.*, dbo.Users.userName FROM dbo.News inner JOIN dbo.Users on dbo.Users.userId=dbo.News.sendId where newsId=@newsId";

		/// <summary>
		/// 删除新闻
		/// </summary>
		public const string SQL_NEWS_DELETE="delete News where newsId=@newsId";

		/// <summary>
		/// 发布公告
		/// </summary>
		public const string SQL_AFFICHES_INSERT="insert into Affiches values(@aTitle,@aContent,@sendId,@sendTime)";

		/// <summary>
		/// 删除公告
		/// </summary>
		public const string SQL_AFFICHES_DELETE="delete Affiches where aId=@aId";

		/// <summary>
		/// 查询所有公告
		/// </summary>
		public const string SQL_AFFICHES_SELECTALL="SELECT dbo.Affiches.*, dbo.Users.userName FROM dbo.Affiches inner JOIN dbo.Users on dbo.Users.userId=dbo.Affiches.sendId";