读取文本文件插入数据库

做了一个读取加密文件经过解密后插入数据库的功能,如果在数据库中没有该ID号(唯一)的记录则执行插入操作,如果该数据库中存在该ID的记录好么执行更新操作。本次选择文件采用的是 FileUpload控件,但这个控件在浏览器中如果设置不好通过FileUpload1.FileName或FileUpload1.PostedFile.FileName得到的只是文件名而不是全路径,而StreamReader sr = new StreamReader(url, Encoding.GetEncoding("GBK"))这个的url需要读取的是全路径,当时为了解决FileUpload1.PostedFile.FileName得到路径的方法找了一段时间,后面找到了需要对浏览器进行设置,但我觉得这样的方法并不实在,因为你总不能让浏览你这网站的人都去这么设置一下浏览品,因此我使用了把选取的文件放复制到指定的文件夹去,然后能过StreamReader sr = new StreamReader(url, Encoding.GetEncoding("GBK"))取的时候url只要取固定的路径就可以,这样就不会报找不到相关路径的错了。下面代码是功能实现的代码。

 

public partial class _Default : System.Web.UI.Page
    {
        string constr = "Data Source=198.98.98.101,20000;Initial Catalog=RparkingDB;Persist SecurityInfo=True;UserID=adminSHPD;Password=SHPDcadre;Min Pool Size=50;Max Pool Size=512;Load Balance Timeout=30";
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void bntInput_Click(object sender, EventArgs e)
        {
            deleteFile();//删指定文件夹的所有文件
            if (!FileUpload1.HasFile)//判断是否选择了文件
            {
                Response.Write("<script>alert(‘请选择文件!‘);</script>");
                return;
            }
            //限制选择的文件不得超过4M
            int length = FileUpload1.PostedFile.ContentLength;//字节长度4194304=4M
            if (length > 4194304)
            {
                Response.Write("<script>alert(‘文件过大,不支持超过4M的文件!‘);</script>");
                return;
            }
            //判断所选择的是不是bat或txt类型的
            FileInfo file = new FileInfo(FileUpload1.PostedFile.FileName);
            if (file.Extension != ".bat")
            {
                Response.Write("<script>alert(‘文件类型不对!‘);</script>");
                return;
            }
            //将选择的文件保存到指定文件夹中(防止使用 FileUpload1.FileName只能取到文件名而没法到到路径而报错
            string filePath = Server.MapPath(ResolveUrl("~/TxtFileStorage/"));
            string fileName = FileUpload1.FileName;
            HttpPostedFile File = FileUpload1.PostedFile;
            File.SaveAs(filePath + fileName);
            //获取文件的路径
            string url = filePath + fileName;
            //连接数据库
            SqlConnection conn = new SqlConnection(constr);
            StreamReader sr = new StreamReader(url, Encoding.GetEncoding("GBK"));
            try
            {
                conn.Open();
                 string str = sr.ReadToEnd();
                string value = DecryptDES(str, "Cadre159");
                string[] array = value.Split(‘;‘);
                for (int i = 0; i < array.Length; i++)
                {
                    string[] array1 = array[i].Split(‘,‘);
                    string id = array1[0];
                    if (checkRepeat(id) == 0)
                    {
                        string addTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
                        string sql = string.Format("insert into rp_sys_TradeRecordPDA select ‘{0}‘,‘{1}‘,‘{2}‘,‘{3}‘,‘{4}‘,‘{5}‘,‘{6}‘,‘{7}‘,‘{8}‘,‘{9}‘,‘{10}‘,‘{11}‘,‘{12}‘,‘{13}‘,‘{14}‘,‘{15}‘,‘{16}‘,‘{17}‘,‘{18}‘",
                                                    array1[0], array1[1], array1[2], array1[3], "0", array1[4], array1[5], array1[6], array1[7], array1[8], array1[9], array1[10], array1[11], array1[12], array1[14], array1[13], addTime, "", array1[15]);//执行插入 
                        SqlCommand cmd = new SqlCommand(sql, conn);
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        string addTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
                        string sql = string.Format("update rp_sys_TradeRecordPDA set Meter_Id= ‘{0}‘, Pay_Type = ‘{1}‘, Parking_Type = ‘{2}‘, Start_Time = ‘{3}‘, End_Time= ‘{4}‘, Parking_Time = ‘{5}‘, Fee=‘{6}‘,Card_Id=‘{7}‘,MomeyB=‘{8}‘, MomeyF=‘{9}‘,Car_No=‘{10}‘,Park_No=‘{11}‘,Pic=‘{12}‘,Insert_Time=‘{13}‘,IsGPRS_Upload=‘{14}‘, Add_Time=‘{15}‘ where TradeRecord_Id =‘{16}‘",
                                     array1[1], array1[2], array1[3], array1[4], array1[5], array1[6], array1[7], array1[8], array1[9], array1[10], array1[11], array1[12], array1[13], array1[14], array1[15], addTime, array1[0]);
                        SqlCommand cmd = new SqlCommand(sql, conn);
                        cmd.ExecuteNonQuery();
                    }
                }

                Response.Write("<script>alert(‘导入成功!‘);</script>");
            }
            catch
            {
                Response.Write("<script>alert(‘文件内容格式不正确!‘);</script>");
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn = null;
                }
                if(sr != null)
                {
                    sr.Close();
                    sr = null;
                }
            }
        }
        /// <summary>
        /// 判断数据库中是否存在该ID的记录,如果存在则返回1,否则返回0
        /// </summary>
        /// <param name="testId"></param>
        /// <returns></returns>
        private int checkRepeat(string id)
        {
            int count;
            string sql = "select 1 from rp_sys_TradeRecordPDA where TradeRecord_Id =‘" + id + "‘";
            using(SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                    count = 1;
                else
                    count = 0;
            }
            return count;
        }
        /// <summary>
        /// 删除指定文件夹中的所有文件
        /// </summary>
        private void deleteFile()
        {
            DirectoryInfo dir = new DirectoryInfo("WebTestInput/TxtFileStorage");
            foreach (FileInfo fi in dir.GetFiles())
            {
                //if (fi.CreationTime > DateTime.Today)
                //if (fi.CreationTime < Convert.ToDateTime("2014-07-28 15:53:00"))
                fi.Delete();
            }
        }
        private static byte[] Keys = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };
        /// <summary>
        /// DES解密字符串
        /// </summary>
        /// <param name="decryptString">待解密的字符串</param>
        /// <param name="decryptKey">解密密钥,要求为8位,和加密密钥相同(Cadre159)</param>
        /// <returns>解密成功返回解密后的字符串,失败返源串</returns>
        public static string DecryptDES(string decryptString, string decryptKey)
        {
            try
            {
                byte[] rgbKey = Encoding.UTF8.GetBytes(decryptKey);
                byte[] rgbIV = Keys;
                byte[] inputByteArray = Convert.FromBase64String(decryptString);
                DESCryptoServiceProvider DCSP = new DESCryptoServiceProvider();
                MemoryStream mStream = new MemoryStream();
                CryptoStream cStream = new CryptoStream(mStream, DCSP.CreateDecryptor(rgbKey, rgbIV), CryptoStreamMode.Write);
                cStream.Write(inputByteArray, 0, inputByteArray.Length);
                cStream.FlushFinalBlock();
                return Encoding.UTF8.GetString(mStream.ToArray());
            }
            catch
            {
                return decryptString;
            }
        }

 

 

 

需要注意的是这样的是:

  //将选择的文件保存到指定文件夹中(防止使用 FileUpload1.FileName只能取到文件名而没法到到路径而报错
            string filePath = Server.MapPath(ResolveUrl("~/TxtFileStorage/"));
            string fileName = FileUpload1.FileName;
            HttpPostedFile File = FileUpload1.PostedFile;
            File.SaveAs(filePath + fileName);
            //获取文件的路径
            string url = filePath + fileName;

 

 

这里的路径如果服务器经过IIS发布其他客户端浏览也是会报错的,以下是我解决这个错误的另一种实现写法:

 public void FolderCreate(string Path)
    {
        // 判断目标目录是否存在如果不存在则新建之  
        if (!Directory.Exists(Path))
            Directory.CreateDirectory(Path);
    } 
    protected void bntInput_Click(object sender, EventArgs e)
    {
        if (!FileUpload1.HasFile)//判断是否选择了文件
        {
            Response.Write("<script>alert(‘请选择文件!‘);</script>");
            return;
        }
        //限制选择的文件不得超过4M
        int length = FileUpload1.PostedFile.ContentLength;//字节长度4194304=4M
        if (length > 4194304)
        {
            Response.Write("<script>alert(‘文件过大,不支持超过4M的文件!‘);</script>");
            return;
        }
        //判断所选择的是不是bat或txt类型的
        FileInfo file = new FileInfo(FileUpload1.PostedFile.FileName);
        if (file.Extension != ".bat")
        {
            Response.Write("<script>alert(‘文件类型不对!‘);</script>");
            return;
        }
        //将选择的文件保存到指定文件夹中(防止使用 FileUpload1.FileName只能取到文件名而没法到到路径而报错
        string filePath = "C:/TxtFileStorage/";
        FolderCreate(filePath);
        deleteFile();//删指定文件夹的所有文件
        //string filePath = Server.MapPath(ResolveUrl("~/TxtFileStorage/"));
        string fileName = FileUpload1.FileName;
        HttpPostedFile File = FileUpload1.PostedFile;
        File.SaveAs(filePath + fileName);
        //获取文件的路径
        string url = filePath + fileName;
        //连接数据库
        StreamReader sr = new StreamReader(url, Encoding.GetEncoding("GBK"));
        try
        {
            string str = sr.ReadToEnd();
            string value = DecryptDES(str, "Cadre159");
            string[] array = value.Split(‘;‘);
            for (int i = 0; i < array.Length; i++)
            {
                string[] array1 = array[i].Split(‘,‘);
                string id = array1[0];
                if (checkRepeat(id) == 0)
                {
                    string addTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
                    string sql = string.Format("insert into rp_sys_TradeRecordPDA select ‘{0}‘,‘{1}‘,‘{2}‘,‘{3}‘,‘{4}‘,‘{5}‘,‘{6}‘,‘{7}‘,‘{8}‘,‘{9}‘,‘{10}‘,‘{11}‘,‘{12}‘,‘{13}‘,‘{14}‘,‘{15}‘,‘{16}‘,‘{17}‘,‘{18}‘",
                                                array1[0], array1[1], array1[2], array1[3], "0", array1[4], array1[5], array1[6], array1[7], array1[8], array1[9], array1[10], array1[11], array1[12], array1[14], array1[13], addTime, "", array1[15]);//执行插入 
                    RpSysTradeRecordManager.ExecuteCommand(sql);
                }
                else
                {
                    string addTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    string sql = string.Format("update rp_sys_TradeRecordPDA set Meter_Id= ‘{0}‘, Pay_Type = ‘{1}‘, Parking_Type = ‘{2}‘, Start_Time = ‘{3}‘, End_Time= ‘{4}‘, Parking_Time = ‘{5}‘, Fee=‘{6}‘,Card_Id=‘{7}‘,MomeyB=‘{8}‘, MomeyF=‘{9}‘,Car_No=‘{10}‘,Park_No=‘{11}‘,Pic=‘{12}‘,Insert_Time=‘{13}‘,IsGPRS_Upload=‘{14}‘, Add_Time=‘{15}‘ where TradeRecord_Id =‘{16}‘",
                                 array1[1], array1[2], array1[3], array1[4], array1[5], array1[6], array1[7], array1[8], array1[9], array1[10], array1[11], array1[12], array1[13], array1[14], array1[15], addTime, array1[0]);
                    RpSysTradeRecordManager.ExecuteCommand(sql);
                }
            }

            Response.Write("<script>alert(‘导入成功!‘);</script>");
        }
        catch
        {
            Response.Write("<script>alert(‘文件内容格式不正确!‘);</script>");
        }
        finally
        {
            if (sr != null)
            {
                sr.Close();
                sr = null;
            }
        }
    }
    /// <summary>
    /// 判断数据库中是否存在该ID的记录,如果存在则返回1,否则返回0
    /// </summary>
    /// <param name="testId"></param>
    /// <returns></returns>
    private int checkRepeat(string id)
    {
        int count;
        try
        {
            string sql = "select 1 from rp_sys_TradeRecordPDA where TradeRecord_Id =‘" + id + "‘";
            DataSet ds = RpSysTradeRecordManager.GetDataSet(sql);
            if (ds.Tables[0].Rows.Count > 0)
                count = 1;
            else
                count = 0;
            return count;
        }
        catch
        {
            return 0;
        }
    }
    /// <summary>
    /// 删除指定文件夹中的所有文件
    /// </summary>
    private void deleteFile()
    {
        DirectoryInfo dir = new DirectoryInfo("C:/TxtFileStorage");
        foreach (FileInfo fi in dir.GetFiles())
        {
            fi.Delete();
        }
    }
    private static byte[] Keys = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };
    /// <summary>
    /// DES解密字符串
    /// </summary>
    /// <param name="decryptString">待解密的字符串</param>
    /// <param name="decryptKey">解密密钥,要求为8位,和加密密钥相同(Cadre159)</param>
    /// <returns>解密成功返回解密后的字符串,失败返源串</returns>
    public static string DecryptDES(string decryptString, string decryptKey)
    {
        try
        {
            byte[] rgbKey = Encoding.UTF8.GetBytes(decryptKey);
            byte[] rgbIV = Keys;
            byte[] inputByteArray = Convert.FromBase64String(decryptString);
            DESCryptoServiceProvider DCSP = new DESCryptoServiceProvider();
            MemoryStream mStream = new MemoryStream();
            CryptoStream cStream = new CryptoStream(mStream, DCSP.CreateDecryptor(rgbKey, rgbIV), CryptoStreamMode.Write);
            cStream.Write(inputByteArray, 0, inputByteArray.Length);
            cStream.FlushFinalBlock();
            return Encoding.UTF8.GetString(mStream.ToArray());
        }
        catch
        {
            return decryptString;
        }
    }

 

 

这样的话会在IIS发布的服务器C盘生成一个目录用于存放导入的文件,每次导入都存一个文件到该文件夹,那么多次导入后生成太多的文件垃圾文件,因些我在每次导入的时候就对该目录进行一个文件的清空

读取文本文件插入数据库,布布扣,bubuko.com

读取文本文件插入数据库

上一篇:孤荷凌寒自学python第七十一天开始写Python的第一个爬虫


下一篇:【转载】SQL Server 2008 中新建用户登录并指定该用户的数据库