browseprice.aspx.cs

简单的Web平台。能够读取Excel文件

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

public partial class BrowsePrice : System.Web.UI.Page
{
    #region 事件处理
    protected void Page_Load(object sender, EventArgs e)
    {
        if ( !Page.IsPostBack )
        {
            string strVersion = Request.QueryString["version"];

            if (null == strVersion)
            {
                return;
            }

            hidVersion.Value = strVersion;

            string strSQL_Level = "SELECT 级别层次,级别名称 FROM level";
            string strSQL_PriceInfo = "SELECT * FROM priceInfo as p " +
                                      "INNER JOIN level as l ON p.价格层次=l.级别层次 " +
                                      "WHERE 价格版本号 LIKE '" + strVersion + "'";
            
            DBConnector dbConn = new DBConnector();
            DataSet dsLevelInfo = dbConn.Query(strSQL_Level);
            ddlPriceLevel.DataSource = dsLevelInfo;
            ddlPriceLevel.DataBind();

            DataSet dsPriceInfo = dbConn.Query(strSQL_PriceInfo);

            txtVersion.Text = dsPriceInfo.Tables[0].Rows[0]["价格名称"].ToString();
            ddlPriceLevel.SelectedValue = dsPriceInfo.Tables[0].Rows[0]["级别层次"].ToString();
            int iStatus = Convert.ToInt32(dsPriceInfo.Tables[0].Rows[0]["状态"].ToString());
            if (1 == iStatus)
                txtStatus.Text = "制作中";
            else if (2 == iStatus)
                txtStatus.Text = "已完成";
            else if (3 == iStatus)
                txtStatus.Text = "已发布";
            else
                txtStatus.Text = "已废弃";

            txtPrcUpper.Text = dsPriceInfo.Tables[0].Rows[0]["波动上限"].ToString();
            txtPrcLower.Text = dsPriceInfo.Tables[0].Rows[0]["波动下限"].ToString();
            txtImpUpper.Text = dsPriceInfo.Tables[0].Rows[0]["导入上限"].ToString();
            txtImpLower.Text = dsPriceInfo.Tables[0].Rows[0]["导入下限"].ToString();
            txtRemark.Text = dsPriceInfo.Tables[0].Rows[0]["备注"].ToString();

            hidTableName.Value = dsPriceInfo.Tables[0].Rows[0]["数据表名称"].ToString();
        }        
    }

    /// <summary>
    /// 点击修改按钮
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnModify_Click(object sender, EventArgs e)
    {
        EnabelControls();

        btnModify.Visible = false;
        btnSave.Visible = true;
    }

    /// <summary>
    /// 点击保存按钮
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnSave_Click(object sender, EventArgs e)
    {
        DBConnector dbConn = new DBConnector();
        string strSQL_CheckName = "SELECT * FROM priceInfo WHERE 价格名称 LIKE '" + txtVersion + "' AND 价格版本号 <> '" + hidVersion + "'";
        DataSet dsChkNameRes = dbConn.Query( strSQL_CheckName );

        if ( dsChkNameRes.Tables[0].Rows.Count > 0 )
        {
            MessageBox.Show(this, "此名称已存在");
            txtVersion.Focus();
            return;
        }

        string strSQL_Update = "UPDATE priceInfo SET 价格名称=@Name,价格层次=@Level,导入上限=@ImpUpper,导入下限=@ImpLower,波动上限=@PrcUpper,波动下限=@PrcLower,备注=@Remark " + 
                               "WHERE 价格版本号 LIKE '" + hidVersion.Value + "'";

        SqlCommand sqlComm = new SqlCommand( strSQL_Update );
        sqlComm.Parameters.Add("@Name", SqlDbType.VarChar, 40);
        sqlComm.Parameters.Add("@Level", SqlDbType.Int);
        sqlComm.Parameters.Add("@ImpUpper", SqlDbType.Float);
        sqlComm.Parameters.Add("@ImpLower", SqlDbType.Float);        
        sqlComm.Parameters.Add("@PrcUpper", SqlDbType.Float);
        sqlComm.Parameters.Add("@PrcLower", SqlDbType.Float);
        sqlComm.Parameters.Add("@Remark", SqlDbType.VarChar, 200);

        sqlComm.Parameters["@Name"].Value = txtVersion.Text;
        sqlComm.Parameters["@Level"].Value = ddlPriceLevel.SelectedValue;
        sqlComm.Parameters["@ImpUpper"].Value = txtImpUpper.Text;
        sqlComm.Parameters["@ImpLower"].Value = txtImpLower.Text;
        sqlComm.Parameters["@PrcUpper"].Value = txtPrcUpper.Text;
        sqlComm.Parameters["@PrcLower"].Value = txtPrcLower.Text;
        sqlComm.Parameters["@Remark"].Value = txtRemark.Text;

        int iRow = dbConn.Execute( sqlComm );

        if ( iRow >= 1 )
        {
            MessageBox.Show(this, "更新成功");
        }

        DisableControls();
        btnModify.Visible = true;
        btnSave.Visible = false;        
    }

   /// <summary>
   /// 查询价格
   /// </summary>
   /// <param name="sender"></param>
   /// <param name="e"></param>
    protected void btnQuery_Click(object sender, EventArgs e)
    {
        PriceItemDataBind();
    }
    #endregion

    #region 用户定义函数

    /// <summary>
    /// 绑定价格项数据
    /// </summary>
    private void PriceItemDataBind()
    {
        string strSQL_QryPrice = "SELECT 零件编号,单价,MakeName,ModelName,备注,导入标记 FROM " + hidTableName.Value +
                                 " p INNER JOIN Make ON Make.MakeCode=p.厂牌编号 " +
                                 "INNER JOIN Model ON Model.ModelCode=p.所属车系 " +
                                 " WHERE ''=''";
        
        string strPartNum = txtPartNum.Text;
        strPartNum = strPartNum.Trim();
        if ( string.Empty != strPartNum )
        {
            if (ckbBlur.Checked)
            {
                strPartNum = "%" + strPartNum + "%";
            }
            strSQL_QryPrice += " AND 零件编号 LIKE '" + strPartNum + "'";
        }

        if ( "0" == ddlQRange.SelectedValue )   // 已导入
        {
            strSQL_QryPrice += " AND 导入标记=1";
        }
        else if ( "1" == ddlQRange.SelectedValue )
        {
            strSQL_QryPrice += " AND 导入标记=0";
        }

        DBConnector dbConn = new DBConnector();
        DataSet dsPriceItem = dbConn.Query( strSQL_QryPrice );
        gvPrice.DataSource = dsPriceItem;
        gvPrice.DataBind();
    }

    /// <summary>
    /// 使价格信息控件可用
    /// </summary>
    private void EnabelControls()
    {
        txtVersion.Enabled      = true;
        ddlPriceLevel.Enabled   = true;
        txtImpUpper.Enabled     = true;
        txtImpLower.Enabled     = true;
        txtPrcUpper.Enabled     = true;
        txtPrcLower.Enabled     = true;
        txtRemark.Enabled       = true;
    }

    /// <summary>
    /// 使价格信息控件不可用
    /// </summary>
    private void DisableControls()
    {
        txtVersion.Enabled      = false;
        ddlPriceLevel.Enabled   = false;
        txtImpUpper.Enabled     = false;
        txtImpLower.Enabled     = false;
        txtPrcUpper.Enabled     = false;
        txtPrcLower.Enabled     = false;
        txtRemark.Enabled       = false;
    }

    #endregion

    /// <summary>
    /// 翻页处理函数
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvPrice_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView theGrid = this.gvPrice;
        int newPageIndex = 0;
        if (-2 == e.NewPageIndex)
        {
            TextBox txtNewPageIndex = null;
            GridViewRow pagerRow = theGrid.BottomPagerRow;
            if (null != pagerRow)
            {
                txtNewPageIndex = pagerRow.FindControl("txtNewPageIndex") as TextBox;   // refer to the TextBox with the NewPageIndex value
            }

            if (null != txtNewPageIndex)
            {
                newPageIndex = int.Parse(txtNewPageIndex.Text) - 1; // get the NewPageIndex
            }
        }
        else
        {
            newPageIndex = e.NewPageIndex;
        }

        newPageIndex = newPageIndex < 0 ? 0 : newPageIndex;
        newPageIndex = newPageIndex >= theGrid.PageCount ? theGrid.PageCount - 1 : newPageIndex;

        theGrid.PageIndex = newPageIndex;

        PriceItemDataBind();
    }

    /// <summary>
    /// 行数据绑定事件(处理导入价格得背景颜色)
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvPrice_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            int iImportFlag = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "导入标记"));
            if (1 == iImportFlag)
            {
                e.Row.BackColor = Color.Yellow;
            }

            for (int i = 0; i < e.Row.Cells.Count; i++)
            {
                if ("-1" == e.Row.Cells[i].Text)
                {
                    e.Row.Cells[i].Text = "无";
                }
            }
        }
    }
}