createprice.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.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class CreatePrice : System.Web.UI.Page
{
    #region 事件处理
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if ( !Page.IsPostBack )
        {
            string strSQL_LevelInfo = "SELECT * FROM level";

            DBConnector dbConn = new DBConnector();

            // 价格层次信息
            DataSet dsLevelInfo = dbConn.Query(strSQL_LevelInfo);
            ddlLevel.DataTextField = "级别名称";
            ddlLevel.DataValueField = "级别层次";
            ddlLevel.DataSource = dsLevelInfo;
            ddlLevel.DataBind();

            txtName.Text = ddlLevel.SelectedItem.Text;
        }
    }

    /// <summary>
    /// 显示日期控件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnCalShow_Click(object sender, EventArgs e)
    {
        calValidDate.Visible = !calValidDate.Visible;

        if (calValidDate.Visible)
            btnCalShow.Text = "隐藏";
        else
            btnCalShow.Text = "选择";
        
    }

    /// <summary>
    /// 选定日期
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void calValidDate_SelectionChanged(object sender, EventArgs e)
    {
        txtValDate.Text = calValidDate.SelectedDate.ToShortDateString();
        calValidDate.Visible = false;
        btnCalShow.Text = "选择";
    }

    /// <summary>
    /// 创建价格按钮点击事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnCreate_Click(object sender, EventArgs e)
    {
        /////////////////////////////////////////////////////////////////////////////////////
        // 检查价格名称
        string strPriceName = txtName.Text;
        strPriceName = strPriceName.Trim();

        if (string.Empty == strPriceName)
        {
            MessageBox.Show(this, "名称不能为空");
            return;
        }

        if (string.Empty == strPriceName)
        {
            MessageBox.Show(this, "价格名称不能为空");
            return;
        }

        if (CheckName(strPriceName))
        {
            MessageBox.Show(this, "价格名称可以使用");
        }
        else
        {
            MessageBox.Show(this, "此价格名称已存在,请更换名称");
        }
        /////////////////////////////////////////////////////////////////////////////////////        

        DBConnector dbConn = new DBConnector();

        string strDate = DateTime.Today.ToString("yyyy-MM-dd");
        strDate = strDate.Replace("-", "");

        // 取得当日价格索引值
        string strSQL_QueryTodayIndex = "SELECT count(*) FROM priceInfo WHERE 价格版本号 LIKE '" + strDate + "%'";
        int iPriceItemIndex = dbConn.QueryIntValue( strSQL_QueryTodayIndex ) + 1;
        string strIndex = string.Format("{0:000}", iPriceItemIndex);
        string strPriceIndex = strDate += strIndex;    // 要添加的价格版本号                        

        string strSQL_InsertItem = "INSERT INTO priceInfo (价格版本号,价格名称,状态,价格层次,数据表名称,波动下限,波动上限,导入下限,导入上限,有效期限,制作时间,制作人,备注) " +
                                   "VALUES (@Version, @Name, @Status, @Level, @TableName, @PrcLower, @PrcUpper, @ImpLower, @ImpUpper, @ValidDate, @MakeDate, @Author,  @Remark)";        

        string strTableName = "Part_" + strPriceIndex;

        SqlCommand sqlComm = new SqlCommand( strSQL_InsertItem );
        sqlComm.Parameters.Add(new SqlParameter("@Version", SqlDbType.VarChar, 15));
        sqlComm.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 40));
        sqlComm.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));        
        sqlComm.Parameters.Add(new SqlParameter("@Level", SqlDbType.Int));
        sqlComm.Parameters.Add(new SqlParameter("@TableName", SqlDbType.VarChar, 50));
        sqlComm.Parameters.Add(new SqlParameter("@PrcLower", SqlDbType.Float));
        sqlComm.Parameters.Add(new SqlParameter("@PrcUpper", SqlDbType.Float));
        sqlComm.Parameters.Add(new SqlParameter("@ImpLower", SqlDbType.Float));
        sqlComm.Parameters.Add(new SqlParameter("@ImpUpper", SqlDbType.Float));
        sqlComm.Parameters.Add(new SqlParameter("@ValidDate", SqlDbType.DateTime));
        sqlComm.Parameters.Add(new SqlParameter("@MakeDate", SqlDbType.DateTime));        
        sqlComm.Parameters.Add(new SqlParameter("@Author", SqlDbType.VarChar, 20));        
        sqlComm.Parameters.Add(new SqlParameter("@Remark", SqlDbType.VarChar, 200));

        string strUserID = null;
        try
        {
            strUserID = Session["UserID"].ToString();   
        }
        catch(NullReferenceException ex)
        {
        
        }

        sqlComm.Parameters["@Version"].Value    = strPriceIndex;            // 价格版本
        sqlComm.Parameters["@Name"].Value       = txtName.Text;             // 价格名称
        sqlComm.Parameters["@Status"].Value     = 1;                        // 价格状态（制作中）
        sqlComm.Parameters["@Level"].Value      = ddlLevel.SelectedValue;   // 价格因子层次
        sqlComm.Parameters["@TableName"].Value  = strTableName;             // 数据表名称
        sqlComm.Parameters["@PrcLower"].Value   = txtPrcLower.Text;         // 波动率下限
        sqlComm.Parameters["@PrcUpper"].Value   = txtPrcUpper.Text;         // 波动率上限
        sqlComm.Parameters["@ImpLower"].Value   = txtImpLower.Text;         // 导入下限
        sqlComm.Parameters["@ImpUpper"].Value   = txtImpUpper.Text;         // 导入上限
        sqlComm.Parameters["@ValidDate"].Value  = txtValDate.Text;          // 有效日期
        sqlComm.Parameters["@MakeDate"].Value   = DateTime.Today.ToShortDateString();   // 制作日期

        if (null != strUserID)
            sqlComm.Parameters["@Author"].Value = strUserID;         // 制作人       
        else
            sqlComm.Parameters["@Author"].Value = "ME";

        sqlComm.Parameters["@Remark"].Value     = txtRemark.Text;           // 备注

        int iRows = dbConn.Execute(sqlComm);

        string strSQL_InsTable = "SELECT * INTO " + strTableName + " FROM Template_Price WHERE 1<>1";
        dbConn.Execute(strSQL_InsTable);
        string strScript = "<script language='javascript'>" +
                           "window.alert('价格创建成功');" +
                           "parent.frames['Main_Bottom_MenuFrame'].location = 'PriceMenu.aspx';" +
                           "parent.frames['Main_Bottom_MainFrame'].location = 'BrowsePrice.aspx';" +
                           "</script>";
        ClientScript.RegisterClientScriptBlock(this.GetType(), "clientScript", strScript);
    }

    protected void ddlLevel_SelectedIndexChanged(object sender, EventArgs e)
    {
        txtName.Text = ddlLevel.SelectedItem.Text;
    }

    #endregion

    /// <summary>
    /// 检查名称
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnCheckName_Click(object sender, EventArgs e)
    {
        string strPriceName = txtName.Text;
        strPriceName = strPriceName.Trim();

        if (string.Empty == strPriceName)
        {
            MessageBox.Show(this, "名称不能为空");
            return;
        }

        if (string.Empty == strPriceName)
        {
            MessageBox.Show(this, "价格名称不能为空");
            return;
        }

        if (CheckName(strPriceName))
        {
            MessageBox.Show(this, "价格名称可以使用");
        }
        else 
        {
            MessageBox.Show(this, "此价格名称已存在,请更换名称");
        }

        ///////////////////////////////////////////////////////////////////////
        // 验证控件
        reqPrcUpper.Enabled = true;
        reqPrcLower.Enabled = true;
        reqImpUpper.Enabled = true;
        reqImpLower.Enabled = true;

        rngPrcUpper.Enabled = true;
        rngPrcLower.Enabled = true;
        rngImpUpper.Enabled = true;
        rngImpLower.Enabled = true;

        cmpPrc.Enabled = true;
        cmpImp.Enabled = true;

        reqValDate.Enabled = true;
        ///////////////////////////////////////////////////////////////////////
    }

    /// <summary>
    /// 检查价格名称是否已经使用
    /// </summary>
    /// <param name="strName"></param>
    /// <returns></returns>
    private bool CheckName(string strName)
    {
        string strSQL_CheckName = "SELECT * FROM priceInfo WHERE 价格名称 LIKE '" + strName + "'";
        DBConnector dbConn = new DBConnector();

        DataSet dsResult = dbConn.Query(strSQL_CheckName);

        if (dsResult.Tables[0].Rows.Count > 0)
        {            
            txtName.Focus();
            return false;
        }
        else
        {            
            txtPrcUpper.Focus();
            return true;
        }
    }
}