首页 > 代码库 > 读取文本文件插入数据库
读取文本文件插入数据库
做了一个读取加密文件经过解密后插入数据库的功能，如果在数据库中没有该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 = http://www.mamicode.com/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 = http://www.mamicode.com/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盘生成一个目录用于存放导入的文件，每次导入都存一个文件到该文件夹，那么多次导入后生成太多的文件垃圾文件，因些我在每次导入的时候就对该目录进行一个文件的清空