C# SqlServerUtility.AddParameter方法代码示例

        public Core.Business.RoleOperation Select(RoleOperationKey id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@OperationId", SqlDbType.Int, id.OperationId);
            sql.AddParameter("@RoleId", SqlDbType.Int, id.RoleId);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectRoleOperation);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.RoleOperation roleOperation = new Core.Business.RoleOperation();

                roleOperation.Id = new Core.Business.RoleOperationKey();
                if (!reader.IsDBNull(0)) roleOperation.Id.OperationId = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) roleOperation.Id.RoleId = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) roleOperation.Status = reader.GetInt32(2);
                if (!reader.IsDBNull(3)) roleOperation.AddDate = reader.GetDateTime(3);

                reader.Close();
                return roleOperation;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public IList<CY.CSTS.Core.Business.INSTRATTACHMENT> GetInstrattachmentDynamic(string whereCondition, string orderByExpression)
        {
            IList<Core.Business.INSTRATTACHMENT> iNSTRATTACHMENTlist = new List<Core.Business.INSTRATTACHMENT>();
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@WhereCondition", SqlDbType.NVarChar, whereCondition);
            sql.AddParameter("@OrderByExpression", SqlDbType.NVarChar, orderByExpression);
            SqlDataReader reader = sql.ExecuteSPReader("usp_SelectINSTRATTACHMENTsDynamic");

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.INSTRATTACHMENT iNSTRATTACHMENT = new Core.Business.INSTRATTACHMENT();

                    if (!reader.IsDBNull(0)) iNSTRATTACHMENT.Id = reader.GetGuid(0);
                    if (!reader.IsDBNull(1)) iNSTRATTACHMENT.InstruId = reader.GetGuid(1);
                    if (!reader.IsDBNull(2)) iNSTRATTACHMENT.ATTACHMENTNAME = reader.GetString(2);
                    if (!reader.IsDBNull(3)) iNSTRATTACHMENT.ATTACHMENTAMOUNT = reader.GetInt32(3);
                    if (!reader.IsDBNull(4)) iNSTRATTACHMENT.ATTACHMENTSPEC = reader.GetString(4);
                    if (!reader.IsDBNull(5)) iNSTRATTACHMENT.ATTACHMENTPRICE = reader.GetDecimal(5);
                    if (!reader.IsDBNull(6)) iNSTRATTACHMENT.PURPOSE = reader.GetString(6);
                    if (!reader.IsDBNull(7)) iNSTRATTACHMENT.MANUFACTURER = reader.GetString(7);
                    if (!reader.IsDBNull(8)) iNSTRATTACHMENT.REMARK = reader.GetString(8);
                    if (!reader.IsDBNull(9)) iNSTRATTACHMENT.WRITEDATE = reader.GetDateTime(9);
                    if (!reader.IsDBNull(10)) iNSTRATTACHMENT.UPDATEDATE = reader.GetDateTime(10);

                    iNSTRATTACHMENT.MarkOld();
                    iNSTRATTACHMENTlist.Add(iNSTRATTACHMENT);
                }
                reader.Close();
            }
            return iNSTRATTACHMENTlist;
        }

        public int GetAvailableNum(DateTime ArriveTime, DateTime LeftTime, string RoomCate)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@ArriveTime", SqlDbType.DateTime, ArriveTime);
            db.AddParameter("@LeftTime", SqlDbType.DateTime, LeftTime);
            db.AddParameter("@RoomCate", SqlDbType.VarChar, RoomCate);

            SqlDataReader rdr = db.ExecuteSqlReader(SQLGetRoomNum);
            if (rdr != null)
            {
                if (!rdr.IsClosed)
                {
                    int Num = 0;
                    if (rdr.Read())
                    {
                        if (!rdr.IsDBNull(0))
                            Num = rdr.GetInt32(0);
                        rdr.Close();
                        return Num;
                    }
                }
            }
            return 0;
        }

        public void Add(CY.HotelBooking.Core.Business.Web_News n)
        {
            SqlServerUtility sqlhelper = new SqlServerUtility();
            sqlhelper.AddParameter("@News_Title", SqlDbType.VarChar, n.News_Title, 100);
            sqlhelper.AddParameter("@News_Content", SqlDbType.Text, n.News_Content);
            sqlhelper.AddParameter("@News_PubDate", SqlDbType.DateTime, n.News_PubDate);
            sqlhelper.AddParameter("@Module_Code", SqlDbType.VarChar, n.Module_Code, 10);
            sqlhelper.AddParameter("@Manager_Code", SqlDbType.VarChar, n.Manager_Code, 10);

            SqlDataReader reader = sqlhelper.ExecuteSqlReader(strSQLInsert);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0))
                {
                    int id;
                    if (int.TryParse(reader.GetValue(0).ToString(), out id))
                    {
                        n.Id = id;
                    }
                }

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public IList<Question> GetSqlWhere(string sqlwhere)
        {
            IList<Core.Business.Question> questionlist = new List<Core.Business.Question>();
            SqlServerUtility sql = new SqlServerUtility();

            StringBuilder sbOrderExpression = new StringBuilder();

            sbOrderExpression.Append(string.Format("CreateDate desc"));
            sql.AddParameter("@WhereCondition", SqlDbType.NVarChar, sqlwhere);

            sql.AddParameter("@OrderByExpression", SqlDbType.NVarChar, sbOrderExpression.ToString());
            SqlDataReader reader = sql.ExecuteSPReader("usp_SelectQuestionsDynamic");

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Question question = new Core.Business.Question();

                    if (!reader.IsDBNull(0)) question.Id = reader.GetGuid(0);
                    if (!reader.IsDBNull(1)) question.Title = reader.GetString(1);
                    if (!reader.IsDBNull(2)) question.Content = reader.GetString(2);
                    if (!reader.IsDBNull(3)) question.UserID = reader.GetGuid(3);
                    if (!reader.IsDBNull(4)) question.CreateDate = reader.GetDateTime(4);
                    if (!reader.IsDBNull(5)) question.IsHomePage = reader.GetInt32(5);

                    question.MarkOld();
                    questionlist.Add(question);
                }
                reader.Close();
            }
            return questionlist;
        }

        public void Insert(Core.Business.JointReview jointReview)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@InstrumentName", SqlDbType.NVarChar, jointReview.InstrumentName);
            sql.AddParameter("@ApplicationUint", SqlDbType.NVarChar, jointReview.ApplicationUint);
            sql.AddParameter("@ApplicationUintID", SqlDbType.UniqueIdentifier, jointReview.ApplicationUintID);
            sql.AddParameter("@Corporate", SqlDbType.NVarChar, jointReview.Corporate);
            sql.AddParameter("@ApplicaitionPerson", SqlDbType.UniqueIdentifier, jointReview.ApplicaitionPerson);
            sql.AddParameter("@ApplicationTime", SqlDbType.DateTime, jointReview.ApplicationTime);
            sql.AddParameter("@CreateTime", SqlDbType.DateTime, jointReview.CreateTime);
            sql.AddParameter("@Status", SqlDbType.Int, jointReview.Status);
            sql.AddParameter("@ApplicationDes", SqlDbType.NVarChar, jointReview.ApplicationDes);
            sql.AddParameter("@AuditDes", SqlDbType.NVarChar, jointReview.AuditDes);
            sql.AddOutputParameter("@Id", SqlDbType.UniqueIdentifier);
            SqlDataReader reader = sql.ExecuteSPReader("usp_InsertJointReview");

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) jointReview.Id = reader.GetGuid(0);

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public void Insert(Core.Business.SubCenterSetting subCenterSetting)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@UintID", SqlDbType.UniqueIdentifier, subCenterSetting.UintID);
            sql.AddParameter("@Name", SqlDbType.NVarChar, subCenterSetting.Name);
            sql.AddParameter("@URI", SqlDbType.NVarChar, subCenterSetting.URI);
            sql.AddParameter("@Themes", SqlDbType.NVarChar, subCenterSetting.Themes);
            sql.AddParameter("@User", SqlDbType.UniqueIdentifier, subCenterSetting.User);
            sql.AddParameter("@Contact", SqlDbType.NVarChar, subCenterSetting.Contact);
            sql.AddParameter("@Des", SqlDbType.NVarChar, subCenterSetting.Des);
            sql.AddOutputParameter("@Id", SqlDbType.UniqueIdentifier);
            SqlDataReader reader =  sql.ExecuteSPReader("usp_InsertSubCenterSetting");

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) subCenterSetting.Id = reader.GetGuid(0);

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public IList<Core.Business.College> GetCollegeByUniIdAndName(int uniId, string name)
        {
            IList<Core.Business.College> collegelist = new List<Core.Business.College>();
            SqlServerUtility sql = new SqlServerUtility(SqlConnectionString);
            sql.AddParameter("@UniversityId", SqlDbType.Int, uniId);
            sql.AddParameter("@Name", SqlDbType.Int, name);
            SqlDataReader reader = sql.ExecuteSPReader("USP_College_Select_By_UniId_And_Name");

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.College college = new Core.Business.College();

                    if (!reader.IsDBNull(0)) college.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) college.UniversityID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) college.DateCreated = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3)) college.Name = reader.GetString(3);
                    if (!reader.IsDBNull(4)) college.Des = reader.GetString(4);

                    college.MarkOld();
                    collegelist.Add(college);
                }
                reader.Close();
            }
            return collegelist;
        }

        public IList<Core.Business.Major> GetMajorByColIdAndName(int colId, string name)
        {
            IList<Core.Business.Major> majorlist = new List<Core.Business.Major>();
            SqlServerUtility sql = new SqlServerUtility(SqlConnectionString);
            sql.AddParameter("@CollegeId", SqlDbType.Int, colId);
            sql.AddParameter("@Name", SqlDbType.NVarChar, name);
            SqlDataReader reader = sql.ExecuteSPReader("USP_Major_Select_By_ColId_And_Name");

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Major major = new Core.Business.Major();

                    if (!reader.IsDBNull(0)) major.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) major.CollegeID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) major.Name = reader.GetString(2);
                    if (!reader.IsDBNull(3)) major.Des = reader.GetString(3);

                    major.MarkOld();
                    majorlist.Add(major);
                }
                reader.Close();
            }
            return majorlist;
        }

        /// <summary>
        /// ���ݻID��������ID����������ID�ж�ij�����Ƿ��Ѿ���ij�����
        /// </summary>
        /// <param name="AccountID"></param>
        /// <param name="InviteID"></param>
        /// <param name="ActiveitesID"></param>
        /// <returns></returns>
        public IList<ActivitiesInvite> Get(long AccountID, long InviteID, int ActiveitesID)
        {
            IList<Core.Business.ActivitiesInvite> activitiesInvitelist = new List<Core.Business.ActivitiesInvite>();
            SqlServerUtility sql = new SqlServerUtility(connectionString);
            string sqlwhere = "AccountID=" + AccountID + " and InviteID=" + InviteID + " and ActiveitesID=" + ActiveitesID + "";
            sql.AddParameter("@WhereCondition", SqlDbType.NVarChar, sqlwhere);
            sql.AddParameter("@OrderByExpression",SqlDbType.NVarChar,"InviteaTime desc");
            SqlDataReader reader = sql.ExecuteSPReader("usp_SelectActivitiesInvitesDynamic");

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.ActivitiesInvite activitiesInvite = new Core.Business.ActivitiesInvite();

                    if (!reader.IsDBNull(0)) activitiesInvite.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) activitiesInvite.AccountID = reader.GetInt64(1);
                    if (!reader.IsDBNull(2)) activitiesInvite.InviteID = reader.GetInt64(2);
                    if (!reader.IsDBNull(3)) activitiesInvite.InviteaTime = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4)) activitiesInvite.ActiveitesID = reader.GetInt32(4);

                    activitiesInvite.MarkOld();
                    activitiesInvitelist.Add(activitiesInvite);
                }
                reader.Close();
            }
            return activitiesInvitelist;
        }

        public IList<Core.Business.AccountGroup> GetAccountGroupByAccountIdOrGroupId(long accountId, int groupId)
        {
            IList<Core.Business.AccountGroup> accountGroupList = new List<Core.Business.AccountGroup>();

            SqlServerUtility sql = new SqlServerUtility(SqlConnection);

            sql.AddParameter("@AccountId", SqlDbType.BigInt, accountId);
            sql.AddParameter("@GroupId", SqlDbType.Int, groupId);

            SqlDataReader reader = sql.ExecuteSPReader("USP_AccountGroup_Select_By_AccountId_And_GroupId");

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.AccountGroup accountGroup = new AccountGroup();

                    if (!reader.IsDBNull(0)) accountGroup.Id = reader.GetInt64(0);
                    if (!reader.IsDBNull(1)) accountGroup.AccountId = reader.GetInt64(1);
                    if (!reader.IsDBNull(2)) accountGroup.GroupId = reader.GetInt32(2);
                    if (!reader.IsDBNull(3)) accountGroup.DateCreated = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4)) accountGroup.Role = reader.GetInt32(4);
                    if (!reader.IsDBNull(5)) accountGroup.LastVisitDate = reader.GetDateTime(5);
                    if (!reader.IsDBNull(6)) accountGroup.Name = reader.GetString(6);

                    accountGroup.MarkOld();
                    accountGroupList.Add(accountGroup);
                }
                reader.Close();
            }

            return accountGroupList;
        }

        public Account FindByName(string loginName, string password)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@LoginName", SqlDbType.NVarChar, loginName);
            sql.AddParameter("@Password", SqlDbType.NVarChar, password);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlFindBy);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.Account account = new Core.Business.Account();

                if (!reader.IsDBNull(0)) account.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) account.Status = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) account.Birthday = reader.GetDateTime(2);
                if (!reader.IsDBNull(3)) account.Gender = reader.GetDecimal(3);
                if (!reader.IsDBNull(4)) account.Name = reader.GetString(4);
                if (!reader.IsDBNull(5)) account.Telephone = reader.GetString(5);
                if (!reader.IsDBNull(6)) account.Address = reader.GetString(6);
                if (!reader.IsDBNull(7)) account.LoginName = reader.GetString(7);
                if (!reader.IsDBNull(8)) account.Password = reader.GetString(8);
                if (!reader.IsDBNull(9)) account.AvatarPath = reader.GetString(9);

                reader.Close();
                return account;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public void Insert(Core.Business.CommissionedCompletionRecord commissionedCompletionRecord)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@ApplicationID", SqlDbType.UniqueIdentifier, commissionedCompletionRecord.ApplicationID);
            sql.AddParameter("@FillPerson", SqlDbType.UniqueIdentifier, commissionedCompletionRecord.FillPerson);
            sql.AddParameter("@AchieveTime", SqlDbType.DateTime, commissionedCompletionRecord.AchieveTime);
            sql.AddParameter("@SamplesNub", SqlDbType.Float, commissionedCompletionRecord.SamplesNub);
            sql.AddParameter("@MachineTime", SqlDbType.Float, commissionedCompletionRecord.MachineTime);
            sql.AddParameter("@Charges", SqlDbType.NVarChar, commissionedCompletionRecord.Charges);
            sql.AddParameter("@FeesReceivable", SqlDbType.NVarChar, commissionedCompletionRecord.FeesReceivable);
            sql.AddParameter("@ActualCharges", SqlDbType.NVarChar, commissionedCompletionRecord.ActualCharges);
            sql.AddParameter("@Units", SqlDbType.UniqueIdentifier, commissionedCompletionRecord.Units);
            sql.AddParameter("@FillTime", SqlDbType.DateTime, commissionedCompletionRecord.FillTime);
            sql.AddOutputParameter("@Id", SqlDbType.UniqueIdentifier);
            SqlDataReader reader = sql.ExecuteSPReader("usp_InsertCommissionedCompletionRecord");

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) commissionedCompletionRecord.Id = reader.GetGuid(0);

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public IList<CY.UME.Core.Business.WishingExtend> GetWishesByActivityIdAndAccountId(long? accountId, string activityId, int count)
        {
            IList<Core.Business.WishingExtend> wishingExtendlist = new List<Core.Business.WishingExtend>();
            SqlServerUtility sql = new SqlServerUtility(SqlConnection);

            if (accountId == null)
            {
                accountId = 0;
            }
            //sql.AddParameter("@AccountId", SqlDbType.BigInt, accountId);
            sql.AddParameter("@ActivityId", SqlDbType.NVarChar, activityId);
            sql.AddParameter("@Count", SqlDbType.Int, count);
            SqlDataReader reader = sql.ExecuteSPReader("USP_WishingExtend_Select_By_ActivityId_And_AccountId");

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.WishingExtend wishingExtend = new Core.Business.WishingExtend();
                    long id = 0;
                    if (!reader.IsDBNull(0)) id = reader.GetInt64(0);
                    wishingExtend = CY.UME.Core.Business.WishingExtend.Load(id);

                    wishingExtend.MarkOld();
                    wishingExtendlist.Add(wishingExtend);
                }
                reader.Close();
            }
            return wishingExtendlist;
        }

        public int GetTopicesNumByInstanceAndType(string strType, string strInstanceId,int level)
        {
            int count = 0;

            SqlServerUtility sql = new SqlServerUtility(connectionString);

            sql.AddParameter("@Type", SqlDbType.NVarChar, strType);
            sql.AddParameter("@InstanceId", SqlDbType.NVarChar, strInstanceId);
            if (level != -1)
            {
                sql.AddParameter("@Level", SqlDbType.NVarChar, level);
            }

            SqlDataReader reader = sql.ExecuteSPReader("USP_TopicExtend_GetTopicCount_By_InstanceId_And_Type");

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.TopicExtend topicExtend = new Core.Business.TopicExtend();

                if (!reader.IsDBNull(0)) count = reader.GetInt32(0);

                reader.Close();
                return count;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return 0;
            }
        }