首页 > 代码库 > .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>
        &nbsp;&nbsp;
        <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>
        &nbsp;
        <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>
        &nbsp;
        <asp:TextBox ID="sqlloadcontent" runat="server" Height="200px" TextMode="MultiLine"
            Width="300px"></asp:TextBox>
        &nbsp;&nbsp;
        <asp:Button ID="Button3" runat="server" Text="读取" Height="27px" Width="57px" OnClick="btnRead_Click" />
        &nbsp;&nbsp;
        <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>
        &nbsp;<asp:FileUpload ID="GetUploadFileContent" runat="server" />
        &nbsp; &nbsp;
        <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" />
        &nbsp;&nbsp;
        <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>
        &nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="loadlogcontent" runat="server" Height="240px" TextMode="MultiLine"
            Width="660px"></asp:TextBox>
        &nbsp;&nbsp;<asp:Button ID="Button7" runat="server" Text="查看" Style="margin: 20px 0px 0px 20px"
            Height="27px" Width="57px" OnClick="btnCheck_Click" />
        &nbsp;&nbsp;&nbsp;
        </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操作