首页 > 代码库 > .net创建页面进行sqlload操作
.net创建页面进行sqlload操作
.net创建网站进行sqlload操作，导入数据，简化工作
主要流程：
1.判断临时表是否存在于数据库，不存在的话，建立临时表并确定导入数据的结构；
2.修改sqlload文件（主要修改表名，分隔符号和表结构）；
3.利用FileUpload空间打开文件，并将数据导入到指定文件；
4.执行cmd命令；
5.查看日志表，确定是否导入数据成功；
前台：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="index.aspx.cs" Inherits="index" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>数据导入</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h2 align="center">
数据导入</h2>
<br />
<asp:Label ID="Label1" runat="server" Text="临时表名称：" Style="margin: 10px 0px 0px 50px"></asp:Label>
<asp:TextBox ID="tablename" runat="server" Height="28px" Width="228px"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="确定" OnClick="btnEnsure_Click" Height="30px"
Width="65px" />
<br />
<br />
<asp:Label ID="Label2" runat="server" Text="临时表结构：" Style="margin: 10px 0px 0px 50px"></asp:Label>
<asp:TextBox ID="tablestructure" runat="server" Height="28px" Width="700px"></asp:TextBox>
<asp:Label ID="Label3" runat="server" Text="（字段名,字段类型）"></asp:Label>
<asp:Button ID="Button2" runat="server" Text="创建" OnClick="btnCreate_Click" Visible="false"
Height="30px" Width="65px" />
</div>
<br />
<hr style="border: 1 dashed #987cb9" width="100%" color="#987cb9" size="1">
<br />
<div>
<asp:Label ID="Label4" runat="server" Text="SQLLOAD：" Style="margin: 10px 0px 0px 50px"></asp:Label>
<asp:TextBox ID="sqlloadcontent" runat="server" Height="200px" TextMode="MultiLine"
Width="300px"></asp:TextBox>
<asp:Button ID="Button3" runat="server" Text="读取" Height="27px" Width="57px" OnClick="btnRead_Click" />
<asp:Button ID="Button4" runat="server" Text="修改" Height="27px" Width="57px" OnClick="btnUpdate_Click" />
</div>
<br />
<hr style="border: 1 dashed #987cb9" width="100%" color="#987cb9" size="1">
<div>
<asp:Label ID="Label5" runat="server" Text="读取数据到Data中：" Style="margin: 10px 0px 0px 50px"></asp:Label>
<asp:FileUpload ID="GetUploadFileContent" runat="server" />
<asp:Label ID="Msg" runat="server" Text=""></asp:Label>
<asp:Button ID="Button5" runat="server" Text="保存" Style="margin: 20px 0px 0px 20px"
Height="27px" Width="57px" OnClick="btnUpload_Click" />
<asp:Button ID="Button6" runat="server" Height="27px" Text="执行cmd命令"
OnClick="btnExecute_Click" Width="92px" />
<br />
<br />
<asp:Label ID="Label6" runat="server" Text="LOADLOG："
Style="margin: 10px 0px 0px 50px"></asp:Label>
<asp:TextBox ID="loadlogcontent" runat="server" Height="240px" TextMode="MultiLine"
Width="660px"></asp:TextBox>
<asp:Button ID="Button7" runat="server" Text="查看" Style="margin: 20px 0px 0px 20px"
Height="27px" Width="57px" OnClick="btnCheck_Click" />
</div>
<script language="javascript">
function chkstr1() {
if (tablename.value =http://www.mamicode.com/= ‘‘) {
alert(‘请重新输入临时表名‘);
return false;
}
}
function chkstr2() {
if (TextBox2.value =http://www.mamicode.com/= ‘‘) {
alert(‘请重新输入临时表结构‘);
return false;
}
}
</script>
</form>
</body>
</html>后台cs代码：
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OracleClient;
using System.IO;
using System.Text;
public partial class index : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Button1.Attributes.Add("OnClick", "javascript:return chkstr1();");
Button2.Attributes.Add("OnClick", "javascript:return chkstr2();");
}
//验证临时表是否存在
protected void btnEnsure_Click(object sender, EventArgs e)
{
string cnnStr1 = "server=.;Password=123456;Persist Security Info=True;User ID=allcom;Data Source=jsjods_dg";
OracleConnection conn1 = new OracleConnection(cnnStr1);
conn1.Open();
string sql = "SELECT count(*) FROM ALL_TABLES WHERE OWNER = UPPER(‘allcom‘)";
if (tablename.Text.Trim() != null && tablename.Text.Trim() != "")
{
sql = sql + " AND TABLE_NAME=UPPER(‘" + tablename.Text + "‘)";
}
OracleCommand cmd1 = new OracleCommand(sql, conn1);//sql是sql语句,con是连接数据库
int count = int.Parse(cmd1.ExecuteScalar().ToString());
if (count > 0)
{
string script1 = "<script> alert(‘临时表存在！‘) </script>";
Page.RegisterStartupScript("", script1);
}
else
{
string script1 = "<script> alert(‘临时表不存在！‘) </script>";
Page.RegisterStartupScript("", script1);
Button2.Visible = true;
}
conn1.Close();
}
//创建临时表
protected void btnCreate_Click(object sender, EventArgs e)
{
string cnnStr2 = "server=.;Password=123456;Persist Security Info=True;User ID=allcom;Data Source=jsjods_dg";
OracleConnection conn2 = new OracleConnection(cnnStr2);
conn2.Open();
string sql_create = "Create Table " + tablename.Text + " ( ";
if (tablestructure.Text.Trim() != null && tablestructure.Text.Trim() != "")
{
sql_create += "" + tablestructure.Text + ")";
}
OracleCommand cmd2 = new OracleCommand(sql_create, conn2);//sql是sql语句,con是连接数据库
cmd2.ExecuteNonQuery();
//string script2 = "<script> alert(‘临时表创建成功！‘) </script>";
// Page.RegisterStartupScript("", script2);
conn2.Close();
}
//获取sqlload内容
protected void btnRead_Click(object sender, EventArgs e)
{
StreamReader sr = File.OpenText("D:\\sqlload\\sqlload.txt");
string text = sr.ReadToEnd();//这个是把文件从头读到结尾
sr.Close();
sqlloadcontent.Text = text;
}
//修改sqlload内容并保存
protected void btnUpdate_Click(object sender, EventArgs e)
{
FileInfo myFile = new FileInfo(@"D:\\sqlload\\sqlload.txt");
StreamWriter sw = myFile.CreateText();
sw.WriteLine(sqlloadcontent.Text, false, Encoding.UTF8);
sw.Close();
sqlloadcontent.Text = "";
}
//将数据读取到data中
protected void btnUpload_Click(object sender, EventArgs e)
{
string FileName = GetUploadFileContent.FileName;//上传文件文件名
string FilePath = GetUploadFileContent.PostedFile.FileName;//上传文件完整路径+文件名
int FileLen = GetUploadFileContent.PostedFile.ContentLength;//获取上传文件的大小
byte[] input = new byte[FileLen];
System.IO.Stream UpLoadStream = GetUploadFileContent.PostedFile.InputStream;
UpLoadStream.Read(input, 0, FileLen);
UpLoadStream.Position = 0;
System.IO.StreamReader sr = new System.IO.StreamReader(UpLoadStream, System.Text.Encoding.Default);
string text=sr.ReadToEnd();
sr.Close();
FileInfo myFile = new FileInfo(@"D:\\sqlload\\date.txt");
StreamWriter sw = myFile.CreateText();
sw.WriteLine(text);
sw.Close();
}
//调用CMD命令
protected void btnExecute_Click(object sender, EventArgs e)
{
System.Diagnostics.ProcessStartInfo pro = new System.Diagnostics.ProcessStartInfo("cmd.exe");
pro.UseShellExecute = false;
pro.RedirectStandardOutput = true;
pro.RedirectStandardError = true;
pro.Arguments = "/K D:\\sqlload\\cmd.bat";
pro.WorkingDirectory = "D:\\sqlload\\";
System.Diagnostics.Process proc = System.Diagnostics.Process.Start(pro);
System.IO.StreamReader sOut = proc.StandardOutput;
proc.Close();
string results = sOut.ReadToEnd().Trim();
sOut.Close();
//string fmtStdOut = "<font face=courier size=0>{0}</font>";
//this.Response.Write(String.Format(fmtStdOut, results.Replace(System.Environment.NewLine, "<br>")));
// Response.Write("<script>alert(‘插入成功!‘); </script>");
//Response.Redirect("Success.html");
}
//获取loadlog内容
protected void btnCheck_Click(object sender, EventArgs e)
{
StreamReader sr = new StreamReader("D:\\sqlload\\loadlog.txt",System.Text.Encoding.Default);
//StreamReader sr = File.OpenText("D:\\sqlload\\loadlog.txt") 出现中文乱码
string text = sr.ReadToEnd();//这个是把文件从头读到结尾
sr.Close();
loadlogcontent.Text = text;
}
}
主要问题：
1.数据库连接（基本格式）：
string cnnStr= "server=.;Password=;Persist Security Info=True;User ID=;Data Source=";
OracleConnection conn = new OracleConnection(cnnStr);
conn.Open();
string sql = "";OracleCommand cmd2 = new OracleCommand(sql, conn);//sql是sql语句,con是连接数据库
cmd.ExecuteNonQuery();
2.利用StreamReader，StreamWirte对流的读取和输入，保证前后修改的编码不能变（ANSI不能变成UTF8）：
StreamReader与File.OpenText同与异
StreamReader sr = new StreamReader(FileName)
StreamReader sr = File.OpenText(FileName)
前者只是声明了一个StreamReader ，而后者在声明后进行了一系列检查操作，然后打开了这个流。
因为多做了很多工作，后者比起前者性能差一些，占用的内存页多一些
3.利用FileUpload控件，打开文件控制大小，否则出现连接已重置错误，解决方法：
在web.config中找到<system.web></system.web>,在其中加入<httpRuntime maxRequestLength="102400" executionTimeout="60" appRequestQueueLimit="100"/>（maxRequestLength为最大文件大小，executionTimeout为最大响应时间）
4.调用cmd命令（基本格式）
System.Diagnostics.ProcessStartInfo pro = new System.Diagnostics.ProcessStartInfo("cmd.exe");
pro.UseShellExecute = false;
pro.RedirectStandardOutput = true;
pro.RedirectStandardError = true;
pro.Arguments = "/K D:\\sqlload\\cmd.bat";//文件路径
pro.WorkingDirectory = "D:\\sqlload\\";//文件路径
System.Diagnostics.Process proc = System.Diagnostics.Process.Start(pro);
System.IO.StreamReader sOut = proc.StandardOutput;
proc.Close();
string results = sOut.ReadToEnd().Trim();
sOut.Close();
.net创建页面进行sqlload操作