none
程序能够根据xml配置文件灵活生成执行SQL,不能将字段名写死在代码中 RRS feed

  • 问题

  • 根据附件给定的xml配置文件中DB_CNANME属性的设定(datesetno属性值代表excel中对应列号),
     写出c#程序读取附件给定excel中的数据列内容,插入SQLserver数据库表中,表名自定(数据库表手工建立即可)
     如不具备实际sqlserver环境,采用msgbox输出可执行sql序列即可
      
     要求:
     
     1、程序能够根据xml配置文件灵活生成执行SQL,不能将字段名写死在代码中 


    <?xml version="1.0" encoding="utf-8" standalone="yes" ?>
    <ypf_ebaby>
      <columns>
        <column0 dateset_no ="0" DB_CNANME ="ebaby_no" grid_name ="序号" type="text"></column0>
        <column1 dateset_no ="1" DB_CNANME ="ebaby_name" grid_name ="名称" type="text"></column1>
        <column2 dateset_no ="2" DB_CNANME ="ebaby_Category" grid_name ="所属分类名" type="text"></column2>
        <column3 dateset_no ="3" DB_CNANME ="ebaby_Concern" grid_name="关注度" type="text"></column3>
        <column4 dateset_no ="4" DB_CNANME ="ebaby_Browse" grid_name="浏览量" type="text"></column4>
        <column5 dateset_no ="5" DB_CNANME ="ebaby_Visitors" grid_name="访客数" type="text"></column5>
        <column6 dateset_no ="6" DB_CNANME ="ebaby_Visittime" grid_name="平均访问时间" type="text"></column6>
        <column7 dateset_no ="7" DB_CNANME ="ebaby_CheckIn" grid_name="入店人次" type="text"></column7>
        <column8 dateset_no ="8" DB_CNANME ="ebaby_CheckOut" grid_name="出店人次" type="text"></column8>
      </columns>
    </ypf_ebaby>


    这个是我写代码
    using System;
    using System.Configuration;
    using System.Data;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Data.SqlClient;
    using System.Data.OleDb;
    using System.IO;
    using ChangeHope;
    using System.Xml;

    public partial class _Default : System.Web.UI.Page
    {
        YX_sql Exsql = new YX_sql();
        string m_strValue = "";
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        //把EXCEL文件上传到服务器并返回文件路径
        private String typename(FileUpload fileloads)
        {
            string pathurl = "";
            if (FileUpload1.PostedFile != null)
            {
                string filename;//文件名字
                string hz;
                string newname;
                filename = FileUpload1.FileName;
                //取后最
                int pos = filename.IndexOf(".");
                hz = filename.Substring((pos + 1)).ToLower();

                if (hz != "xls")
                {
                    Response.Write("<script>alert('导入文件格式不对!---xls');</script>");
                    Response.End();
                }
                newname = sjname() + "." + hz;
                //保存图片
                pathurl = Server.MapPath("~") + "\\YX_UpFile\\Excel\\" + newname;
                FileUpload1.PostedFile.SaveAs(pathurl);
            }
            return pathurl;
        }
        /// <summary>
        /// 产生个随即名称
        /// </summary>
        /// <returns></returns>
        public string sjname()
        {
            string sj = null;
            sj = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.TimeOfDay.Hours.ToString() + DateTime.Now.TimeOfDay.Minutes.ToString() + DateTime.Now.TimeOfDay.Milliseconds.ToString();
            return sj;
        }
        //把excel数据读入dataset返回l数据集

        private DataSet xsldata(string filepath)
        {
            DataSet ds = new DataSet();
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'";
            //string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES;HDR=No;'";
            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
            string strCom = "SELECT * FROM [Sheet1$]";
            try
            {
                Conn.Open();
                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
                myCommand.Fill(ds, "[Sheet1$]");
            }
            catch (Exception)
            {
                ChangeHope_fc.Show_Msg("!请您检查Excel中表名是否与数据库中Sheet1表名中相同,如果不相同请你把表名改成Sheet1。", "ImportIDInfoPage.aspx");
            }
            finally
            {
                Conn.Close();
            }
            return ds;
        }
        protected void btnOk_Click(object sender, EventArgs e)
        {
            try
            {
                Exsql.Open();
                string fileurl = typename(this.FileUpload1);//调用typename方法取得excel文件路径
                DataSet ds = new DataSet();//取得数据集
                ds = xsldata(fileurl);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    this.dg1.DataSource = ds;
                    this.dg1.DataBind();
                }
                else
                {
                    ChangeHope_fc.Show_Msg("没有数据!!!!");
                    Response.End();
                }
                XmlDataDocument xmlDoc = new XmlDataDocument();
                string strPath = Server.MapPath("~/App_Data/TableColumns.xml");
                xmlDoc.Load(strPath);
                XmlNode xmlNodes = xmlDoc.SelectSingleNode("ypf_ebaby");

                foreach (XmlNode node in xmlNodes.ChildNodes)
                {
                    if (node.Name == "columns")
                    {
                        foreach (XmlNode nodes in node.ChildNodes)
                        {
                            string strTmp = "";
                            strTmp = nodes.Attributes["grid_name"].Value;
                            m_strValue += strTmp + ",";
                        }
                    }
                }
                m_strValue = m_strValue.Substring(0, m_strValue.LastIndexOf(','));
                string[] strName = m_strValue.Split(',');
                int errorcount = 0;//记录错误信息条数
                int insertcount = 0;//记录插入成功条数
                int updatecount = 0;//记录更新信息条数
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string strebaby_no = ds.Tables[0].Rows[i][0].ToString();
                    string strebaby_name = ds.Tables[0].Rows[i][1].ToString();
                    string strebaby_Category = ds.Tables[0].Rows[i][2].ToString();
                    string strebaby_Concern = ds.Tables[0].Rows[i][3].ToString();
                    string strebaby_Browse = ds.Tables[0].Rows[i][4].ToString();
                    string strebaby_Visitors = ds.Tables[0].Rows[i][5].ToString();
                    string strebaby_Visittime = ds.Tables[0].Rows[i][6].ToString();
                    string strebaby_CheckIn = ds.Tables[0].Rows[i][7].ToString();
                    string strebaby_CheckOut = ds.Tables[0].Rows[i][8].ToString();

                    if (strebaby_no != "" && strebaby_name != "" && strebaby_Category != "" && strebaby_Concern != "" && strebaby_Browse != "" && strebaby_Visitors != "" && strebaby_Visittime != "" && strebaby_CheckIn != "" && strebaby_CheckOut != "")
                    {
                        string strsql = string.Format("select count(*) from Userinfo  where {0}='{1}'and {2}='{3}'",
                            strName[0], strebaby_no, strName[1], strebaby_name);

                        SqlCommand selectcmd = new SqlCommand(strsql, Exsql.con);
                        int nCount = Convert.ToInt32(selectcmd.ExecuteScalar());
                        if (nCount > 0)
                        {
                            string strSQL = string.Format("update Userinfo set {0}='{1}',{2}='{3}',{4}='{5}',{6}='{7}',{8}='{9}',{10}='{11}',{12}='{13}',{14}='{15}',{16}='{17}' where {18}='{19}'and {20}='{21}'",
                             strName[0], strebaby_no, strName[1], strebaby_name, strName[2], strebaby_Category, strName[3], strebaby_Concern,
                              strName[4], strebaby_Browse, strName[5], strebaby_Visitors, strName[6], strebaby_Visittime,
                              strName[7], strebaby_CheckIn, strName[8], strebaby_CheckOut, strName[0], strebaby_no, strName[1], strebaby_name);

                            SqlCommand updatecmd = new SqlCommand(strSQL, Exsql.con);
                            updatecmd.ExecuteNonQuery();
                            updatecount++;
                        }
                        else
                        {

                            string strSql = string.Format("insert into Userinfo ({0},{1},{2},{3},{4},{5},{6},{7},{8}) values('{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}')",
                                strName[0], strName[1], strName[2], strName[3], strName[4], strName[5], strName[6], strName[7], strName[8],
                                strebaby_no, strebaby_name, strebaby_Category, strebaby_Concern, strebaby_Browse, strebaby_Visitors,
                                strebaby_Visittime, strebaby_CheckIn, strebaby_CheckOut);

                            SqlCommand insertcmd = new SqlCommand(strSql, Exsql.con);
                            insertcmd.ExecuteNonQuery();
                            insertcount++;
                        }
                    }
                    else
                    {
                        errorcount++;
                        ChangeHope_fc.Show_Msg("导入失败!请检查是否与数据库中字段相同还有字段的个数是否相同呢!!!");
                        Response.End();
                    }
                }
                Response.Write("<script language='javascript'>alert('" + insertcount + "条数据导入成功!" + updatecount + "条数据更新成功!" + errorcount + "条数据部分信息为空没有导入!');</script>");
            }
            catch
            {
            }
            finally
            {
                Exsql.Close();
            }
            string sql = "";
            sql = "select * from Userinfo order by ID desc";
            ChangHope_DB Chp = new ChangHope_DB();
            DataSet dss = Chp.Db_ds(sql, "Userinfo");
            this.dg1.DataSource = dss;
            this.dg1.DataBind();
        }
    }
    我怎么样些才能够根据xml配置文件灵活生成执行SQL


    当我自己逆境的时候,我认为我够!因为我勤奋、节俭、有毅力,我肯求知及肯建立一个信誉。 对人诚恳,做事
    2009年11月23日 3:14

答案

  • 举个例子:
    string columns = ...;//想办法得到它,譬如说结果是"Field1, Field2"
    string tableName = ...;//想办法得到它,譬如说结果是"MyTable"
    string predicate = ...;//想办法得到它,譬如说结果是"Field4 = Field5"
    string sql=string.Format("SELECT {0} FROM {1} WHERE {2}", columns, tableName, predicate);

    然后columns啊tableName啊predicate啊就放在xml里面,我假设你知道怎么使用各种C#类读xml,如果不知道再说。
    2009年11月23日 8:26
  • 上面代码中columns,tableNames等字段就是从XML中读出来的
    2009年11月25日 8:31
    版主

全部回复

  • 举个例子:
    string columns = ...;//想办法得到它,譬如说结果是"Field1, Field2"
    string tableName = ...;//想办法得到它,譬如说结果是"MyTable"
    string predicate = ...;//想办法得到它,譬如说结果是"Field4 = Field5"
    string sql=string.Format("SELECT {0} FROM {1} WHERE {2}", columns, tableName, predicate);

    然后columns啊tableName啊predicate啊就放在xml里面,我假设你知道怎么使用各种C#类读xml,如果不知道再说。
    2009年11月23日 8:26
  • 你这个还是没有体现:程序能够根据xml配置文件灵活生成执行SQL。
    当我自己逆境的时候,我认为我够!因为我勤奋、节俭、有毅力,我肯求知及肯建立一个信誉。 对人诚恳,做事
    2009年11月24日 9:13
  • 上面代码中columns,tableNames等字段就是从XML中读出来的
    2009年11月25日 8:31
    版主