项目中的代码

修改编辑后台的功能前后用了三天时间，中间有些细节需要注意，存储过程的类型转换，动态SQL语句单引号的运用，将部分代码贴在这里。

存储过程

 1set ANSI_NULLS ON
 2set QUOTED_IDENTIFIER ON
 3go
 4
 5
 6-- =============================================
 7-- Author:        <bobby>
 8-- Create date: <2007-06-06>
 9-- Description:    <批量更新图书章节的编辑状态>
10-- =============================================
11ALTER PROCEDURE [dbo].[sp_Book_UpdateChapterAllEditorDo]
12    @BookID int,
13    @VolumeIDList nvarchar(2000),
14    @ChapterIDList nvarchar(2000),
15    @EditorDo char(1)
16AS
17declare @SQL nvarchar(2000)
18SET NOCOUNT ON
19
20set @SQL = 'UPDATE [dbo].[Book_Chapter] SET
21    EditorDo = '''+@EditorDo+'''
22WHERE
23    [BookID] = '+cast(@BookID as varchar)+' and VolumeID in ('+@VolumeIDList+') and ChapterID in ('+@ChapterIDList+')'
24--注意以上的数据类型转换和单引号
25print @SQL
26--注意这里加了括号，否则错误
27exec (@SQL)
28
29
30

数据访问层

  更新章节信息#region 更新章节信息
    public static bool UpdateChapter(int chapterID, string chapterName, string chapterContent, int wordCount, int bookID, int diffCount, string editorDo, string volumeIDListOne, string chapterIDList, string volumeIDList)
    {
        bool result = false;

        Database db = DatabaseFactory.CreateDatabase();
        using (DbConnection conn = db.CreateConnection())
        {
            conn.Open();
            DbTransaction transaction = conn.BeginTransaction();

            try
            {
                DbCommand updateChapter = db.GetStoredProcCommand("sp_Book_UpdateChapter");
                db.AddInParameter(updateChapter, "@ChapterID", DbType.Int32, chapterID);
                db.AddInParameter(updateChapter, "@ChapterName", DbType.String, chapterName);
                db.AddInParameter(updateChapter, "@ChapterContent", DbType.String, chapterContent);
                db.AddInParameter(updateChapter, "@WordCount", DbType.Int32, wordCount);
                db.ExecuteNonQuery(updateChapter);

                DbCommand updateBook = db.GetStoredProcCommand("sp_Book_UpdateBookChapter");
                db.AddInParameter(updateBook, "@BookID", DbType.Int32, bookID);
                db.AddInParameter(updateBook, "@LastChapterID", DbType.Int32, chapterID);
                db.AddInParameter(updateBook, "@LastChapterName", DbType.String, chapterName);
                db.AddInParameter(updateBook, "@WordCount", DbType.Int32, diffCount);
                db.ExecuteNonQuery(updateBook);


                //批量更新章节的编辑信息,当前选中的卷所在章节
                DbCommand sqlUpdateAll = db.GetStoredProcCommand("sp_Book_UpdateChapterAllEditorDo");
                db.AddInParameter(sqlUpdateAll, "@BookID", DbType.Int32, bookID);
                db.AddInParameter(sqlUpdateAll, "@VolumeIDList", DbType.String, volumeIDListOne);
                db.AddInParameter(sqlUpdateAll, "@ChapterIDList", DbType.String, chapterIDList);
                db.AddInParameter(sqlUpdateAll, "@EditorDo", DbType.String, editorDo);
                db.ExecuteNonQuery(sqlUpdateAll);

                //批量更新章节的编辑信息,当前选中的卷以后的章节
                DbCommand sqlUpdateAllVol = db.GetStoredProcCommand("sp_Book_UpdateVolumeAllEditorDo");
                db.AddInParameter(sqlUpdateAllVol, "@BookID", DbType.Int32, bookID);
                db.AddInParameter(sqlUpdateAllVol, "@VolumeIDList", DbType.String, volumeIDList);
                db.AddInParameter(sqlUpdateAllVol, "@EditorDo", DbType.String, editorDo);
                db.ExecuteNonQuery(sqlUpdateAllVol);

                transaction.Commit();

                result = true;
            }
            catch
            {
                transaction.Rollback();
            }

            conn.Close();
        }

        return result;
    }
    #endregion

表现层

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;

public partial class Writer_ModifyChapterInfo : WriterBasePage
{
    #region 窗体载入，定义两个公共变量传递卷和章的排序号
    public int volumeOrder;
    public int chapterOrder;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (WriterID == 0)
        { }
        if (!IsPostBack)
        {
            ddlBookList.DataSource = BookNameList;
            ddlBookList.DataTextField = "BookName";
            ddlBookList.DataValueField = "BookID";
            ddlBookList.DataBind();

            BindVolumeList();
            BindChapterList();
            BindChapterInfo();
            getOrder();
            showRbl();
        }
    }
   
    #endregion

    #region 更改选中的图书
    protected void ddlBookList_SelectedIndexChanged(object sender, EventArgs e)
    {
        BindVolumeList();
        BindChapterList();
        BindChapterInfo();
        btnSubmit.Enabled = true;
        showRbl();
    }
    #endregion
 

    #region 更改选中的卷
       protected void ddlVolumeList_SelectedIndexChanged(object sender, EventArgs e)
    {
       
        BindChapterList();
        BindChapterInfo();
        btnSubmit.Enabled = true;
       
        getOrder();
    }
    #endregion
 

    #region 更改选中的章节
    protected void ddlChapterList_SelectedIndexChanged(object sender, EventArgs e)
    {
        BindChapterInfo();
        btnSubmit.Enabled = true;

        getOrder();
    }

    #endregion

    #region 提交更新
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        int chapterID = Int32.Parse(ddlChapterList.SelectedValue);
        string chapterName = PageValidate.InputText(txtChapterName.Text, 20);
        string chapterContent = PageValidate.InputText(txtChapterContent.Text, 0);
        int wordCount = txtChapterContent.Text.Length;
        int bookID = Int32.Parse(ddlBookList.SelectedValue);
        int diffCount = wordCount - Int32.Parse(lblWordCount.Text);
        int volSelectID = Int32.Parse(ddlVolumeList.SelectedItem.Value);

        string chapterIDList = updateChapterEdit(); //得到章节的ID列表
        string volumeIDList = updateVolumeEdit();   //得到卷的ID列表
        //默认正常的显示在中文网
        if (rblShow.SelectedItem.Value != "1")
        {
            if (WriterService.UpdateChapter(chapterID, chapterName, chapterContent, wordCount, bookID, diffCount, "0", volSelectID.ToString(), chapterIDList, volumeIDList))
            {
                lblMessage.Text = "章节更新成功";
                btnSubmit.Enabled = false;
                BindChapterList();
                BindChapterInfo();
            }
            else
            {
                lblMessage.Text = "更新失败，请稍后重试";
            }

        }
        //编辑勾选了确认出版的选项 不在中文显示作品
        else if ((rblShow.Visible == false) && (rblShow.SelectedIndex == 1))
        {
            if (WriterService.UpdateChapter(chapterID, chapterName, chapterContent, wordCount, bookID, diffCount, "4", volSelectID.ToString(), chapterIDList, volumeIDList))
            {
                lblMessage.Text = "章节更新成功";
                btnSubmit.Enabled = false;
                BindChapterList();
                BindChapterInfo();
            }
            else
            {
                lblMessage.Text = "更新失败，请稍后重试";
            }
        }
        //作者勾选了不在中文网显示
        else
        {
            if (WriterService.UpdateChapter(chapterID, chapterName, chapterContent, wordCount, bookID, diffCount, "3", volSelectID.ToString(), chapterIDList, volumeIDList))
            {
                lblMessage.Text = "章节更新成功";
                btnSubmit.Enabled = false;
                BindChapterList();
                BindChapterInfo();
            }
            else
            {
                lblMessage.Text = "更新失败，请稍后重试";
            }
        }
    }
    #endregion

    #region 下拉框绑定卷名
    private void BindVolumeList()
    {
        ddlVolumeList.DataSource = WriterService.GetVolumeNameList(Int32.Parse(ddlBookList.SelectedValue));
        ddlVolumeList.DataTextField = "VolumeName";
        ddlVolumeList.DataValueField = "VolumeID";
        ddlVolumeList.DataBind();

        ddlVolumeOrder.DataSource = WriterService.GetVolumeNameList(Int32.Parse(ddlBookList.SelectedValue));
        ddlVolumeOrder.DataTextField = "VolumeOrder";
        ddlVolumeOrder.DataValueField = "VolumeID";
        ddlVolumeOrder.DataBind();

        if (string.IsNullOrEmpty(ddlVolumeList.SelectedValue))
        {
            Server.Transfer("~/Writer/NewChapter.aspx");
        }
    }

    #endregion

    #region 下拉框绑定章名
    private void BindChapterList()
    {
        ddlChapterList.DataSource = WriterService.GetChapterNameList(Int32.Parse(ddlVolumeList.SelectedValue));
        ddlChapterList.DataTextField = "ChapterName";
        ddlChapterList.DataValueField = "ChapterID";
        ddlChapterList.DataBind();

        ddlChapterOrder.DataSource = WriterService.GetChapterNameList(Int32.Parse(ddlVolumeList.SelectedValue));
        ddlChapterOrder.DataTextField = "ChapterOrder";
        ddlChapterOrder.DataValueField = "ChapterID";
        ddlChapterOrder.DataBind();

    }
    #endregion

    #region 绑定章节信息
    private void BindChapterInfo()
    {
        if (ddlChapterList.Items.Count > 0)
        {
            int chapterID = Int32.Parse(ddlChapterList.SelectedValue);
            DataSet ds = WriterService.GetChapterInfo(chapterID);

            txtChapterName.Text = ds.Tables[0].Rows[0]["ChapterName"].ToString();
            txtChapterContent.Text = ds.Tables[0].Rows[0]["ChapterContent"].ToString();
            lblWordCount.Text = ds.Tables[0].Rows[0]["WordCount"].ToString();
        }
    }
   
    #endregion

    #region 是否显示单选框控件
    private void showRbl()
    {
        int bookID = Int32.Parse(ddlBookList.SelectedItem.Value.ToString());
        int count = 0;
        EditorService.GetPublishFollowECount(bookID, out count);
        if (count != 0)
        {
            lblShow.Visible = true;
            rblShow.Visible = true;
        }
        else
        {
            lblShow.Visible = false;
            rblShow.Visible = false;
        }

        int fpCount = 0;
        EditorService.GetPublishF_PCount(bookID, out fpCount);
        if (fpCount != 0)
        {
            lblShow.Visible = false;
            rblShow.Visible = false;
            rblShow.SelectedIndex = 1;  //不显示                 
        }
    }

    #endregion

    #region 得到选中卷和章的排序号
    private void getOrder()
    {
        string bookID = ddlBookList.SelectedValue;
        string volumeID = ddlVolumeList.SelectedValue;
        string chapterID = ddlChapterList.SelectedValue;
        if (bookID.Length != 0 && volumeID.Length != 0 && chapterID.Length != 0)
        {
            volumeOrder = WriterService.GetVolumeOrder(Int32.Parse(ddlBookList.SelectedValue), Int32.Parse(ddlVolumeList.SelectedValue));
            chapterOrder = WriterService.GetChapterOrder(Int32.Parse(ddlBookList.SelectedValue), Int32.Parse(ddlVolumeList.SelectedValue), Int32.Parse(ddlChapterList.SelectedValue));
        }
        else
        {
            return;
        }
    }
    #endregion

    #region 选出章节的ID号列表
    private string updateChapterEdit()
    {
        //chapterOrder  选中的章的ORDER
        getOrder();
        int bookID = Int32.Parse(ddlBookList.SelectedValue);
        StringBuilder chaIDlist = new StringBuilder();

        int chaSelectID = Int32.Parse(ddlChapterList.SelectedItem.Value);   //确定选中章节的章编号   

        for (int j = 0; j < ddlChapterOrder.Items.Count; j++)
        {
            string chaOrder = ddlChapterOrder.Items[j].Text;    //章排序号
            string chaID = ddlChapterOrder.Items[j].Value;      //章号
            if (Int32.Parse(chaOrder) >= chapterOrder)        //比较选定的章编号，取出比其大的章编号
            {
                chaIDlist.Append(chaID).Append(",");
            }
        }
        string cCondition = chaIDlist.ToString();
        if ((cCondition.EndsWith(",")))
        {
            cCondition = cCondition.Substring(0, cCondition.Length - 1);
        }
        return cCondition;
    }

    #endregion

    #region 选出卷的ID号列表 
    private string updateVolumeEdit()
    {
        //volumeOrder   选中的卷的ORDER
        getOrder();
        int bookID = Int32.Parse(ddlBookList.SelectedValue);
        StringBuilder volIDlist = new StringBuilder();
        for (int i = 0; i < ddlVolumeOrder.Items.Count; i++)
        {
            string ddlvolOrder = ddlVolumeOrder.Items[i].Text;  //卷排序号
            string ddlvolBookID = ddlVolumeOrder.Items[i].Value;    //卷号

            if (volumeOrder < Int32.Parse(ddlvolOrder))   //比较选定的卷号，取出比其大的卷编号
            {
                volIDlist.Append(ddlvolBookID).Append(",");
            }           
        }

        string vCondition = volIDlist.ToString();

        if ((vCondition.EndsWith(",")))
        {
            vCondition = vCondition.Substring(0, vCondition.Length - 1);
        }
        return vCondition;
    }   
    #endregion  

  
}