1. Redo

After working, I found that life passed quickly. In a twinkling of an eye, it has been more than a week since I last posted a blog, and it has improved a lot this week. Integrate the successful code tested on your own computer into the corresponding modules of the project.

There are also many difficulties. First, the database connection method used in the project is different from that implemented by yourself,

It is also groping and realizing the transformation. It feels that the execution of database statements encapsulated in the framework is more convenient.

This is the previous database connection method:

strConnection = ConfigurationManager.ConnectionStrings["wzglConnectionString"].ConnectionString;
con = new SqlConnection(strConnection);
try
{
         con.Open();
         SqlCommand sqlCmd = new SqlCommand();
         sqlCmd.CommandText = sqlStr2;
         sqlCmd.Connection = con;
         SqlDataReader sqlDataReader = sqlCmd.ExecuteReader();
         sqlDataReader.Close();
}
catch (Exception ex)
{
         Response.Write("Update failed,Failure reason:" + ex.Message);
}
finally
{
         con.Close();
 }

In the framework, the data connection is reflected in the settings, and the execution mode of database statements only needs to call the methods encapsulated by the framework:

try
{
      bll.GetList1(sqlStrUpdate);
}
catch (Exception ex)
{
       Response.Write("Update failed,Failure reason:" + ex.Message);
}       

The second is the change of the database, which causes the SQL statement to be rewritten and retested. This is just work hard. But the first day of the test was very strange. The code that clearly had no problem could not be displayed on the page. It didn't solve after tossing all afternoon. Finally, I don't know what happened. It's too strange. The code hasn't been changed. Maybe the test code is acclimatized in the framework?

The subsequent problem is that more than 500 pieces of data are displayed in the GridView, so paging display is made. When inserting the database, the data read from the GridView can only be inserted into the displayed page. Then try the solution, establish separate functions for reading Excel files, and call them respectively in the part of displaying and importing the database, so that they can be directly imported into the database by reading the data in Excel. This part and Previous code Updated. Previous business requirements and processes can be seen in previous blogs.

The codes of the main updated parts are as follows:

//Get Excel data function 
public void getExcel()
        {
            string strCon;
            strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("excel1.xls") + "; Extended Properties=Excel 8.0;";
            OleDbConnection olecon = new OleDbConnection(strCon);
            OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strCon);
            myda.Fill(myds);
            GridView1.DataSource = myds;
        }

        
       //This part of the foreground page refers to the previous blog, click the button to display the data in Excel into the GridView
        protected void Button1_Click(object sender, EventArgs e)
        {
            getExcel();
            GridView1.DataBind();
           

        }

        ///Read data from Excel and insert data circularly into the database
        protected void Button2_Click(object sender, EventArgs e)
        {
            getExcel();
            for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
            {
                //Here, pay attention to the combination with debugging to see whether the SQL statement is correct
                string sqlStr2 = "insert into [wzglxt].[dbo].[jhmxb]([ID],[sj],[bh],[wlbm],[wlmc],[jldw],[ckdj],[xqslhj],[mtotal],[bz])values";
                sqlStr2 += "('" + (myds.Tables[0].Rows[i][0]).ToString().Trim() + "',";
                sqlStr2 += "'" + (myds.Tables[0].Rows[i][1]).ToString().Trim() + "',";
                sqlStr2 += "'" + (myds.Tables[0].Rows[i][2]).ToString().Trim() + "',";
                sqlStr2 += "'" + (myds.Tables[0].Rows[i][3]).ToString().Trim() + "',";
                sqlStr2 += "'" + (myds.Tables[0].Rows[i][4]).ToString().Trim() + "',";
                sqlStr2 += "'" + (myds.Tables[0].Rows[i][5]).ToString().Trim() + "',";
                sqlStr2 += "'" + (myds.Tables[0].Rows[i][6]).ToString().Trim() + "',";
                sqlStr2 += "'" + (myds.Tables[0].Rows[i][7]).ToString().Trim() + "',";
                sqlStr2 += "'" + (myds.Tables[0].Rows[i][8]).ToString().Trim() + "',";
                sqlStr2 += "'" + (myds.Tables[0].Rows[i][9]).ToString().Trim() + "'";
                sqlStr2 += ")";
               
                try
                {
                    //How SQL is executed in the framework
                    bll.GetList1(sqlStr2);
                }
                catch (Exception ex)
                {
                    Response.Write("Update failed,Failure reason:" + ex.Message);
                }
                
            }
            Response.Write("<script>alert('Import succeeded! Do not import again!');</script>");

        }
        public void bindGridView1()
        {
            
            try
            {
                getExcel();
                GridView1.DataBind();
                if (myds.Tables[0].Rows.Count != 0)
                {  
                    GridView1.DataBind();

                }
            }
            catch (Exception ex)
            {
                Response.Write("<script>alert('" + ex.ToString() + "')</script>");
            }
        }
        //Binding of paging display data
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                e.Row.Attributes.Add("onmouseover", "if(window.oldtr==null||window.oldtr!=this){col=this.style.backgroundColor;this.style.backgroundColor='#95B8FF'}");
                e.Row.Attributes.Add("onmouseout", "if(window.oldtr==null||window.oldtr!=this){this.style.backgroundColor=col}");
                e.Row.Attributes.Add("onclick", "if(window.oldtr!=null){window.oldtr.style.backgroundColor=oldc;}this.style.backgroundColor='#e6c5fc';window.oldtr=this;oldc=col;");
            }
            if (e.Row.RowIndex != -1)
            {
                int indexID = this.GridView1.PageIndex * this.GridView1.PageSize + e.Row.RowIndex + 1;
                e.Row.Cells[0].Text = indexID.ToString();
            }
        }

        protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
        {

        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            bindGridView1();
        }

        public override void VerifyRenderingInServerForm(Control control) { }
        protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
        {

        }

2. Insert into the database by column

Due to business requirements, it is necessary to read and update the Excel table by column to the database. This part of the idea is similar to insertion. It is also to read the column circularly and then update it to the corresponding row of the corresponding database. This part of the local test is more complex, the code is very redundant, and it has been thought for a long time. This part has been streamlined and updated. Dynamically query the identity corresponding to the column name from the database, and then use it in the update statement.

For the problem of SQL statement splicing, you can debug to see whether the statement is correct.

In addition, it is worth noting that when reading the query results of the select statement in the DataSet, pay attention to the reading method. Here, only the field name corresponding to the record is queried, so the usage is as follows.

//sqlGetDw is the field name statement to get the record
DataSet dw = bll.GetList1(sqlGetDw);
//The method by which the DataSet reads the field name
dw.Tables[0].Rows[0][0]).ToString()

The specific code is as follows.

 ///Update the corresponding column of demand data
        protected void Button2_Click(object sender, EventArgs e)
        {
            getExcel();
            //Get the company name, which has been written in the blog before getting the header column
            string singleDw = GridView1.HeaderRow.Cells[10].Text.ToString();
            //Query the corresponding code from [wzglxt].[dbo].[zzjg] in the database
            
            for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
            {
                string sqlGetDw = "select jgpxm from [wzglxt].[dbo].[zzjg] where jgmc = " + "'" + singleDw + "'";
                DataSet dw = bll.GetList1(sqlGetDw);
                string sqlStrUpdate = "update [wzglxt].[dbo].[jhmxb] set " + (dw.Tables[0].Rows[0][0]).ToString() + "=";
                sqlStrUpdate += "'" + (myds.Tables[0].Rows[i][10]).ToString().Trim() + "'";
                sqlStrUpdate += "where ID = " + "'" + (myds.Tables[0].Rows[i][0]).ToString().Trim() + "' and sj = " + "'" + (myds.Tables[0].Rows[i][1]).ToString().Trim() + "'";
                try
                {
                    bll.GetList1(sqlStrUpdate);
                }
                catch (Exception ex)
                {
                    Response.Write("Update failed,Failure reason:" + ex.Message);
                }                
            }
            Response.Write("<script>alert('Import succeeded! Do not import again!');</script>");
        }

I, Xiaobai, have just come into contact with ASP.net and many places I don't understand. Welcome the boss for advice and work hard together!