sql.cs

ajax 三层软件开发实例,三层软件开发实例

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using IDAL;
using Model;

namespace DAL
{
    class Sql : Iguestbook 
    {
        private string connectionString = System.Configuration.ConfigurationSettings.AppSettings["sqlConnectionString"] + System.Web.HttpContext.Current.Server.MapPath("../App_Data/myPage.mdf");


        /// <summary>
        /// 读取数据库中所有留言
        /// </summary>
        /// <returns>返回guestbool数组</returns>
        public guestbook[] getListGuestBook()
        {
            using (SqlConnection Conn = new SqlConnection(connectionString))
            {
                SqlCommand Cmd = new SqlCommand("select * from guestbook order by id desc",Conn);
                Conn.Open();

                ArrayList guest = new ArrayList();
                SqlDataReader Ddr = Cmd.ExecuteReader();
                while (Ddr.Read())
                {
                    guestbook gb = new guestbook();
                    gb.Id = Ddr["id"].ToString();
                    gb.Name =Ddr["name"].ToString();
                    gb.Time = DateTime.Parse(Ddr["times"].ToString());
                    gb.Ip = Ddr["ip"].ToString();
                    gb.Content = Ddr["content"].ToString();
                    gb.Recontent =Ddr["recontent"].ToString();
                    gb.Pic = Ddr["pic"].ToString();
                    gb.Email = Ddr["email"].ToString();
                    guest.Add(gb);
                }
                Ddr.Close();

                return (guestbook[])guest.ToArray(typeof(guestbook));
            }

        }

        /// <summary>
        /// 读取一条留言内容
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public guestbook[] getOnly(string id)
        {
            using (SqlConnection Conn = new SqlConnection(connectionString))
            {
                SqlCommand Cmd = new SqlCommand("select * from guestbook where id=" + id, Conn);
                Conn.Open();

                ArrayList guest = new ArrayList();
                SqlDataReader Ddr = Cmd.ExecuteReader();
                while (Ddr.Read())
                {
                    guestbook gb = new guestbook();
                    gb.Id = Ddr["id"].ToString();
                    gb.Name = Ddr["name"].ToString();
                    gb.Time = DateTime.Parse(Ddr["times"].ToString());
                    gb.Ip = Ddr["ip"].ToString();
                    gb.Content = Ddr["content"].ToString();
                    gb.Recontent = Ddr["recontent"].ToString();
                    gb.Pic = Ddr["pic"].ToString();
                    guest.Add(gb);
                }
                Ddr.Close();

                return (guestbook[])guest.ToArray(typeof(guestbook));
            }
        }

        /// <summary>
        /// 添加留言内容
        /// </summary>
        /// <param name="gb">留言实体对像</param>
        public void setGuestBook(guestbook gb)
        {
            using (SqlConnection Con = new SqlConnection(this.connectionString))
            {
                string sql = "insert into guestbook (name,times,ip,content,pic,email) values ( @name , @times , @ip , @content , @pic ,@email)";
                SqlParameter oleParam0 = new SqlParameter("@name", gb.Name);
                SqlParameter oleParam1 = new SqlParameter("@times", gb.Time);
                SqlParameter oleParam2 = new SqlParameter("@ip", gb.Ip);
                SqlParameter oleParam3 = new SqlParameter("@content", gb.Content);
                SqlParameter oleParam4 = new SqlParameter("@pic", gb.Pic);
                SqlParameter oleParam5 = new SqlParameter("@email", gb.Email );
                SqlCommand Cmd = new SqlCommand();
                Cmd.CommandText = sql;

                Cmd.Parameters.Add(oleParam0);
                Cmd.Parameters.Add(oleParam1);
                Cmd.Parameters.Add(oleParam2);
                Cmd.Parameters.Add(oleParam3);
                Cmd.Parameters.Add(oleParam4);
                Cmd.Parameters.Add(oleParam5);
                Con.Open();
                Cmd.Connection = Con;
                Cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 回复留言
        /// </summary>
        /// <param name="Regb"></param>
        public void setReGuestBook(guestbook Regb)
        {
            using (SqlConnection Con = new SqlConnection(this.connectionString))
            {
                string sql = "update guestbook set  recontent=@recontent where id=" + Regb.Id;
                SqlParameter Param0 = new SqlParameter("@recontent", Regb.Recontent);
                SqlCommand Cmd = new SqlCommand();
                Cmd.CommandText = sql;
                Cmd.Parameters.Add(Param0);
                Con.Open();
                Cmd.Connection = Con;
                Cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 删除留言
        /// </summary>
        /// <param name="id">留言id号</param>
        /// <returns></returns>
        public bool delGuestBook(string id)
        {
            using (SqlConnection Con = new SqlConnection(this.connectionString))
            {
                string sql = "delete from [guestbook] where id=@id";
                SqlParameter Param0 = new SqlParameter("@id", id);
                SqlCommand Cmd = new SqlCommand();
                Cmd.CommandText = sql;
                Cmd.Parameters.Add(Param0);
                Con.Open();
                Cmd.Connection = Con;
                try
                {
                    Cmd.ExecuteNonQuery();
                    return true;
                }
                catch
                {
                    return false;
                }
            }

        }
    }
}