_automatching.aspx.cs

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

            {
                for (int k = 0; k < TreeRange.Nodes[j].ChildNodes.Count; k++ )
                {
                    if (TreeRange.Nodes[j].ChildNodes[k].Value == dsMatched.Tables[0].Rows[i][0].ToString())
                        TreeRange.Nodes[j].ChildNodes[k].Checked = true;
                }
            }
        }
    }

    private void PopulateSubMdlNodes(TreeNode tn)
    {
        string strValue = tn.Value;

        DBConnector dbConn = new DBConnector();
        dbConn.Connect();

        string strSQL = "SELECT * FROM SubModel WHERE ModelCode LIKE '" + strValue + "'";
        DataSet dsSubMdl = dbConn.Query( strSQL );

        for (int i = 0; i < dsSubMdl.Tables[0].Rows.Count; i++ )
        {
            TreeNode tnChild = new TreeNode(dsSubMdl.Tables[0].Rows[i]["SubMdlName"].ToString(), dsSubMdl.Tables[0].Rows[i]["SubMdlCode"].ToString());
            tn.ChildNodes.Add(tnChild);
        }
    }

    protected void btnFilter_Click(object sender, EventArgs e)
    {
        if (plFilter.Visible)
        {
            plFilter.Visible = false;
            btnFilter.Text = "选择";
        }
        else
        {
            plFilter.Visible = true;
            btnFilter.Text = "隐藏";
        }
    }

    #endregion

    protected void btnAutoMatching_Click(object sender, EventArgs e)
    {
        SaveToTempData(m_dsXls);

        DBConnector dbConn = new DBConnector();
        dbConn.Connect();

        // 查询此厂牌的数据是否建立
        string strQueryExist = "SELECT 价格版本号,数据表名称 FROM priceInfo WHERE 厂牌编号 LIKE '" + ddlMark.SelectedValue + "'";
        strQueryExist += "AND 价格层次=" + ddlPriceLevel.SelectedValue;
        DataSet dsExist = dbConn.Query( strQueryExist );
        string strTableName;
        string strDateIndex;
        if (dsExist.Tables[0].Rows.Count == 0)
        {
            // 查询当日添加的价格信息条数
            string strDate = DateTime.Today.ToShortDateString();
            strDate = strDate.Replace("-", "");

            string strQueryIndex = "SELECT count(*) FROM priceInfo WHERE 价格版本号 LIKE '" + strDate + "%'";


            int iTodayItemCount = dbConn.QueryIntValue(strQueryIndex);
            iTodayItemCount++;
            string strIndex = string.Format("{0:000}", iTodayItemCount);

            strDateIndex = strDate += strIndex;    // 要添加的价格版本号
            string strInsert = "INSERT INTO priceInfo (价格版本号, 状态, 厂牌编号, 波动下限, 波动上限, 有效期限, 价格层次, 制作时间, 制作人, 数据表名称, 备注) " +
                               "VALUES ( @Version, @Status, @Make, @Low, @Upper, @Range, @Level, @Date, @Author, @TableName, @Remark )";

            strTableName = "Part_" + ddlMark.SelectedValue + "_" + ddlPriceLevel.SelectedValue;
            SqlCommand sqlComm = new SqlCommand(strInsert);
            sqlComm.Parameters.Add(new SqlParameter("@Version", SqlDbType.VarChar, 15));
            sqlComm.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
            sqlComm.Parameters.Add(new SqlParameter("@Make", SqlDbType.VarChar, 10));
            sqlComm.Parameters.Add(new SqlParameter("@Low", SqlDbType.Float));
            sqlComm.Parameters.Add(new SqlParameter("@Upper", SqlDbType.Float));
            sqlComm.Parameters.Add(new SqlParameter("@Range", SqlDbType.DateTime));
            sqlComm.Parameters.Add(new SqlParameter("@Level", SqlDbType.Int));
            sqlComm.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));
            sqlComm.Parameters.Add(new SqlParameter("@Author", SqlDbType.VarChar, 20));
            sqlComm.Parameters.Add(new SqlParameter("@TableName", SqlDbType.VarChar, 50));
            sqlComm.Parameters.Add(new SqlParameter("@Remark", SqlDbType.VarChar, 200));

            sqlComm.Parameters["@Version"].Value = strDateIndex;
            sqlComm.Parameters["@Status"].Value = 1;
            sqlComm.Parameters["@Make"].Value = ddlMark.SelectedValue;
            sqlComm.Parameters["@Low"].Value = txtLowerLimit.Text.ToString();
            sqlComm.Parameters["@Upper"].Value = txtUpperLimit.Text.ToString();
            sqlComm.Parameters["@Range"].Value = DateTime.Parse(txtDataRange.Text);
            sqlComm.Parameters["@Level"].Value = ddlPriceLevel.SelectedValue;
            sqlComm.Parameters["@Date"].Value = DateTime.Now.ToShortDateString();
            sqlComm.Parameters["@TableName"].Value = strTableName;
            sqlComm.Parameters["@Author"].Value = "this";
            sqlComm.Parameters["@Remark"].Value = "无";

            int iRows = dbConn.Execute(sqlComm);

            string strSQLInsert = "SELECT * INTO " + strTableName + " FROM Template_Price WHERE 1<>1";
            dbConn.Execute(strSQLInsert);
        }
        else
        {
            strDateIndex = dsExist.Tables[0].Rows[0][0].ToString();
            strTableName = dsExist.Tables[0].Rows[0][1].ToString();
        }


        string strSQL_Match = "INSERT INTO " + strTableName + " (价格版本号,零件编号,单价,所属部位,零件类型,所属车系,所属车型,备注) " +
                              "SELECT '" + strDateIndex + "',零件编号,单价,所属部位,零件类型,所属车系,所属车系,备注 FROM tempPrice WHERE tempPrice.零件编号 IN " +
                              "(SELECT PartNum FROM Part)";
        int iRowsCount = dbConn.Execute( strSQL_Match );
    }

    /// <summary>
    /// 将被导入的价格保存到临时表
    /// </summary>
    /// <param name="dsXml"></param>
    private void SaveToTempData(DataSet dsXml)
    {
        // 默认过滤符号:空格,-
        const string DEFAULT_FILTER = " -";
        ///////////////////////////////////

        string strFilter = txtFilter.Text + DEFAULT_FILTER;  

        DBConnector dbConn = new DBConnector();
        /*dbConn.Connect();*/

        SqlConnection sqlConn = new SqlConnection( dbConn.GetConnectString() );
        sqlConn.Open();

        SqlDataAdapter sqlAdp = new SqlDataAdapter("SELECT * FROM tempPrice WHERE 1<>1", sqlConn);
        DataSet dsTemp = new DataSet();
        sqlAdp.Fill(dsTemp, "temp");

        //DataSet dsTemp = dbConn.Query("SELECT * FROM tempPrice WHERE 1<>1");

        /*dbConn.Close();*/

        for (int i = 0; i < dsXml.Tables[0].Rows.Count; i++)
        {
            DataRow drTemp = dsTemp.Tables[0].NewRow();

            switch (dsXml.Tables[0].Columns.Count)
            {
                case 9:
                    if ("舍弃" != ddlHeader9.SelectedValue)
                        drTemp[ddlHeader9.SelectedValue] = dsXml.Tables[0].Rows[i][8];
                    goto case 8;

                case 8:
                    if ("舍弃" != ddlHeader8.SelectedValue)
                        drTemp[ddlHeader8.SelectedValue] = dsXml.Tables[0].Rows[i][7];
                    goto case 7;

                case 7:
                    if ("舍弃" != ddlHeader7.SelectedValue)
                        drTemp[ddlHeader7.SelectedValue] = dsXml.Tables[0].Rows[i][6];
                    goto case 6;

                case 6:
                    if ("舍弃" != ddlHeader6.SelectedValue)
                        drTemp[ddlHeader6.SelectedValue] = dsXml.Tables[0].Rows[i][5];
                    goto case 5;

                case 5:
                    if ("舍弃" != ddlHeader5.SelectedValue)
                        drTemp[ddlHeader5.SelectedValue] = dsXml.Tables[0].Rows[i][4];
                    goto case 4;

                case 4:
                    if ("舍弃" != ddlHeader4.SelectedValue)
                        drTemp[ddlHeader4.SelectedValue] = dsXml.Tables[0].Rows[i][3];
                    goto case 3;

                case 3:
                    if ("舍弃" != ddlHeader3.SelectedValue)
                        drTemp[ddlHeader3.SelectedValue] = dsXml.Tables[0].Rows[i][2];
                    goto case 2;

                case 2:
                    if ("舍弃" != ddlHeader2.SelectedValue)
                        drTemp[ddlHeader2.SelectedValue] = dsXml.Tables[0].Rows[i][1];
                    goto case 1;

                case 1:
                    if ("舍弃" != ddlHeader1.SelectedValue)
                        drTemp[ddlHeader1.SelectedValue] = dsXml.Tables[0].Rows[i][0];
                    break;
            }
            
            dsTemp.Tables[0].Rows.Add( drTemp );            
        }
        SqlCommandBuilder custCB = new SqlCommandBuilder(sqlAdp);
        sqlAdp.Update(dsTemp, "temp");
    }
    static DataSet m_dsXls = new DataSet();
}