webservice.cs

visual c# 网络编程技术与实践实例包括几个源码

        }

        [WebMethod]
        //删除帖子
        public int DelNote(int id)
        {
            SqlConnection conn = new SqlConnection(DBconn);
            SqlCommand SqlCmd = conn.CreateCommand();

            //使用pd_DelNote存储过程
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "pd_DelNote";

            SqlParameter parInput1 = SqlCmd.Parameters.Add("@id", SqlDbType.Int);
            parInput1.Direction = ParameterDirection.Input;
            parInput1.Value = id;

            SqlCommand SqlCmd1 = conn.CreateCommand();

            //使用DelRes存储过程
            SqlCmd1.CommandType = CommandType.StoredProcedure;
            SqlCmd1.CommandText = "pd_DelRes";

            SqlParameter parInput2 = SqlCmd1.Parameters.Add("@id", SqlDbType.Int);
            parInput2.Direction = ParameterDirection.Input;
            parInput2.Value = id;

            //打开数据库连接
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

            //执行插入新贴命令
            SqlCmd.ExecuteNonQuery();
            SqlCmd1.ExecuteNonQuery();

            //关闭数据库
            if (conn.State == ConnectionState.Open)
                conn.Close();
            return 0;
        }

        [WebMethod]
        //根据参数传入的主题名，检查该投票是否存在
        public bool CheckSubject(string subject)
        {
            SqlConnection conn = new SqlConnection(DBconn);
            SqlCommand SqlCmd = conn.CreateCommand();

            //使用pd_checkSubject存储过程
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "pd_checkSubject";

            SqlParameter parInput1 = SqlCmd.Parameters.Add("@Subject", SqlDbType.VarChar, 50);
            parInput1.Direction = ParameterDirection.Input;
            parInput1.Value = subject;


            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                //如果已有投票项目返回false
                if (Convert.ToInt16(SqlCmd.ExecuteScalar()) > 0)
                {
                    return false;
                }
                //否则返回true
                else
                {
                    return true;
                }
            }
            finally
            {
                //函数结束时关闭连接
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        [WebMethod]
        //新增投票主题
        public void NewSubject(string subject)
        {
            SqlConnection conn = new SqlConnection(DBconn);
            SqlCommand SqlCmd = conn.CreateCommand();

            //使用pd_addSubject存储过程
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "pd_addSubject";

            SqlParameter parInput1 = SqlCmd.Parameters.Add("@subject", SqlDbType.VarChar, 50);
            parInput1.Direction = ParameterDirection.Input;
            parInput1.Value = subject;

            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                //添加项目
                SqlCmd.ExecuteNonQuery();
            }
            finally
            {
                //函数结束时关闭连接
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        [WebMethod]
        //根据投票主题名，获得该投票的ID
        public int GetSubjectID(string subject)
        {
            SqlConnection conn = new SqlConnection(DBconn);
            SqlCommand SqlCmd = conn.CreateCommand();

            //使用pd_GetSubject存储过程
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "pd_GetSubjectId";

            //将投票项目名传入
            SqlParameter parInput1 = SqlCmd.Parameters.Add("@subject", SqlDbType.VarChar, 50);
            parInput1.Direction = ParameterDirection.Input;
            parInput1.Value = subject;

            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                //返回投票项ID
                return Convert.ToInt16(SqlCmd.ExecuteScalar());
            }
            finally
            {
                //函数结束时关闭连接
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        [WebMethod]
        //删除投票
        public void DeleteSubject(int subjectId)
        {
            SqlConnection conn = new SqlConnection(DBconn);
            SqlCommand SqlCmd = conn.CreateCommand();

            //使用pd_DelSubject存储过程通过传入的Id来删除项目
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "pd_DelSubject";

            SqlParameter parInput1 = SqlCmd.Parameters.Add("@id", SqlDbType.Int);
            parInput1.Direction = ParameterDirection.Input;
            parInput1.Value = subjectId;

            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlCmd.ExecuteNonQuery();
            }
            finally
            {
                //函数结束时关闭连接
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        [WebMethod]
        //显示当前存在的所有投票主题
        public DataSet ShowSubjects()
        {
            SqlConnection conn = new SqlConnection(DBconn);
            SqlCommand SqlCmd = conn.CreateCommand();
            //使用pd_ShowSubject存储过程
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "pd_ShowSubject";
            //打开数据库连接
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlDataAdapter myDataAdapter = new SqlDataAdapter(SqlCmd);
                DataSet myDataSet = new DataSet();
                //将Subject表填入myDataSet中
                myDataAdapter.Fill(myDataSet, "Subject");
                //将myDataSet返回
                return myDataSet;
            }
            finally
            {
                //函数结束时关闭连接
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        [WebMethod]
        //获得当前投票结果
        public DataSet GetBallotRes(int id)
        {
            SqlConnection conn = new SqlConnection(DBconn);
            SqlCommand SqlCmd = conn.CreateCommand();

            //使用pd_GetSubject存储过程
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "pd_GetSubject";

            //传入需要结果的投票ID
            SqlParameter parInput1 = SqlCmd.Parameters.Add("@id", SqlDbType.Int);
            parInput1.Direction = ParameterDirection.Input;
            parInput1.Value = id;

            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlDataAdapter myDataAdapter = new SqlDataAdapter(SqlCmd);
                DataSet myDataSet = new DataSet();

                //将投票结果填充到myDataSet中并返回
                myDataAdapter.Fill(myDataSet, "Ballot");

                return myDataSet;
            }
            finally
            {
                //函数结束时关闭连接
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        [WebMethod]
        //更新投票项
        public void UpdateBallot(int OptionId, int SubjectId)
        {
            SqlConnection conn = new SqlConnection(DBconn);
            SqlCommand SqlCmd = conn.CreateCommand();

            //使用pd_UpdateBallot存储过程
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "pd_UpdateBallot";

            //将投票项目与投票ID作为参数传入
            SqlParameter parInput1 = SqlCmd.Parameters.Add("@option", SqlDbType.Int);
            parInput1.Direction = ParameterDirection.Input;
            parInput1.Value = OptionId;

            SqlParameter parInput2 = SqlCmd.Parameters.Add("@subject", SqlDbType.Int);
            parInput2.Direction = ParameterDirection.Input;
            parInput2.Value = SubjectId;

            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                //执行SQL
                SqlCmd.ExecuteNonQuery();
            }
            finally
            {
                //函数结束时关闭连接
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        [WebMethod]
        //增加投票项
        public void AddBallotItem(int SubjectId, string OptionName)
        {
            SqlConnection conn = new SqlConnection(DBconn);
            SqlCommand SqlCmd = conn.CreateCommand();

            //使用pd_AddBallot存储过程
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "pd_AddBallot";

            //传入投票项名与Id
            SqlParameter parInput1 = SqlCmd.Parameters.Add("@Name", SqlDbType.VarChar, 50);
            parInput1.Direction = ParameterDirection.Input;
            parInput1.Value = OptionName;

            SqlParameter parInput2 = SqlCmd.Parameters.Add("@Id", SqlDbType.Int);
            parInput2.Direction = ParameterDirection.Input;
            parInput2.Value = SubjectId;

            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlCmd.ExecuteNonQuery();
            }
            finally
            {
                //函数结束时关闭连接
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
    }    
}