<system.web>
<httpHandlers>
<add verb="*POST" path="AjaxFileUploadHandler.axd" type="AjaxControlToolkit.AjaxFileUploadHandler, AjaxControlToolkit"/>
</httpHandlers>
<compilation debug="true" targetFramework="4.0" />
<httpRuntime
executionTimeout="90"
maxRequestLength="5242880"
useFullyQualifiedRedirectUrl="false"
minFreeThreads="8"
minLocalRequestFreeThreads="4"
appRequestQueueLimit="100"
enableVersionHeader="true"
/>
</system.web>
<connectionStrings>
<add name="DBConnectionString" connectionString="Data Source=.;Initial Catalog=Billing_2015;Integrated Security=True" providerName="SystemSqlclient"/>
</connectionStrings>
</configuration>
------------------------------CS----------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using AjaxControlToolkit;
using System.IO;
using System.Data;
using System.Data.SqlClient;
namespace WebApplication2
{
public partial class App1 : System.Web.UI.Page
{
string tablename;
public static string GetConnectionString()
{
return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
}
protected void Page_Load(object sender, EventArgs e)
{
}
protected void OnUploadComplete(object sender, AjaxFileUploadEventArgs e)
{
var dateString = DateTime.Now.ToString("yyyyMMdd");
tablename = "TCDR" + dateString;
string fileName = Path.GetFileName(e.FileName);
AjaxFileUpload11.SaveAs(Server.MapPath("~/UploadedCSVFiles/" + fileName));
DataSet ds = Convert(Server.MapPath("~/UploadedCSVFiles/" + fileName), "MyNewTable", ";");
if (ds.Tables[0].Rows.Count != 0)
{
DataTable dt = ds.Tables["MyNewTable"];
createsqltable(dt, tablename);
dt = null;
ds = null;
//GridView1.DataSource = ds;
//GridView1.DataBind();
}
}
//protected void btnupload_Click(object sender, EventArgs e)
//{
// var dateString = DateTime.Now.ToString("yyyyMMdd");
// tablename = "TCDR" + dateString;
// if (FileUpload1.HasFile)
// {
// HttpFileCollection hfc = Request.Files;
// for (int i = 0; i < hfc.Count; i++)
// {
// HttpPostedFile hpf = hfc[i];
// if (hpf.ContentLength > 0)
// {
// lblError.Text += String.Format("{0}<br />", hpf.FileName);
// hpf.SaveAs(Server.MapPath("tttt"));
// DataSet ds = Convert(Server.MapPath(hpf.FileName), "MyNewTable", ";");
// if (ds.Tables[0].Rows.Count != 0)
// {
// DataTable dt = ds.Tables["MyNewTable"];
// createsqltable(dt, tablename);
// dt = null;
// ds = null;
// //GridView1.DataSource = ds;
// //GridView1.DataBind();
// }
// }
// }
// lblError.Visible = true;
// //foreach (HttpPostedFile uploadedFile in FileUpload1.PostedFile)
// //{
// // // uploadedFile.SaveAs(System.IO.Path.Combine(Server.MapPath("~/Images/"),uploadedFile.FileName));
// // lblError.Text += String.Format("{0}<br />", uploadedFile.FileName);
// //}
// }
//}
public static DataSet Convert(string File, string TableName, string delimiter)
{
//The DataSet to Return
DataSet result = new DataSet();
//Open the file in a stream reader.
StreamReader sd = new StreamReader(File);
double mycount = 0;
string COl = "Column";
string myCOl = "";
//Split the first line into the columns
string[] columns = sd.ReadLine().Split(delimiter.ToCharArray());
//Add the new DataTable to the RecordSet
result.Tables.Add(TableName);
//Cycle the colums, adding those that don't exist yet
//and sequencing the one that do.
foreach (string col in columns)
{
bool added = false;
string next = "";
int i = 0;
while (!added)
{
////Build the column name and remove any unwanted characters.
string columnname = col + next;
//columnname = columnname.Replace(";", "");
//columnname = columnname.Replace("#", "");
//columnname = columnname.Replace("'", "");
//columnname = columnname.Replace("&", "");
//columnname = columnname.Replace(";;", "");
//See if the column already exists
if (!result.Tables[TableName].Columns.Contains(columnname))
{
mycount = mycount + 1;
myCOl = COl + mycount;
//if it doesn't then we add it here and mark it as added
result.Tables[TableName].Columns.Add(myCOl);
added = true;
}
else
{
//if it did exist then we increment the sequencer and try again.
i++;
next = "_" + i.ToString();
}
}
}
StreamReader sdk = new StreamReader(File);
//Read the rest of the data in the file.
string AllData = sdk.ReadToEnd();
//Split off each row at the Carriage Return/Line Feed
//Default line ending in most windows exports.
//You may have to edit this to match your particular file.
//This will work for Excel, Access, etc. default exports.
char[] delimiters = new char[] { '\n' };
string[] rows = AllData.Split(delimiters);
//Now add each row to the DataSet
foreach (string r in rows)
{
//Split the row at the delimiter.
string[] items = r.Split(delimiter.ToCharArray());
//Add the item
result.Tables[TableName].Rows.Add(items);
}
//remove empty row
for (int i = result.Tables[TableName].Rows.Count - 1; i >= 0; i--)
{
if (result.Tables[TableName].Rows[i][1] == DBNull.Value)
{
result.Tables[TableName].Rows[i].Delete();
}
}
result.Tables[TableName].AcceptChanges();
//Return the imported data.
return result;
}
public void createsqltable(DataTable dt, string tablename)
{
string table = "";
table += "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + tablename + "]') AND type in (N'U'))";
table += "BEGIN ";
table += "create table " + tablename + "";
table += "(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i != dt.Columns.Count - 1)
table += dt.Columns[i].ColumnName + " " + "varchar(max)" + ",";
else
table += dt.Columns[i].ColumnName + " " + "varchar(max)";
}
table += ") ";
table += "END";
InsertQuery(table);
CopyData(dt, tablename);
}
public void InsertQuery(string qry)
{
SqlConnection connection = new SqlConnection(GetConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = qry;
cmd.Connection = connection;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
public static void CopyData(DataTable dt, string tablename)
{
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(GetConnectionString(), SqlBulkCopyOptions.TableLock))
{
bulkCopy.DestinationTableName = tablename;
bulkCopy.WriteToServer(dt);
}
}
}
}
---------------------------aspx-------------------------
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="App1.aspx.cs" Inherits="WebApplication2.App1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
body
{
font-family: Arial;
font-size: 10pt;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:Label ID="lblError" runat="server" ForeColor="Red" BorderColor="AliceBlue"></asp:Label>
<asp:ToolkitScriptManager runat="server">
</asp:ToolkitScriptManager>
<asp:AjaxFileUpload ID="AjaxFileUpload11" runat="server" MaximumNumberOfFiles="300"
Width="800px" OnUploadComplete="OnUploadComplete" ForeColor="Coral" align="center" />
</form>
</body>
</html>
No comments :
Post a Comment