1: using System;
2: using System.Data;
3: using System.Data.SqlClient;
4: using System.Data.OleDb;
5: using System.IO;
6: using System.Text;
7:
8: namespace WebApplication1
9: {
10: public partial class CSVToSQLImporting : System.Web.UI.Page
11: {
12: private string GetConnectionString()
13: {
14: return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
15: }
16: private void CreateDatabaseTable(DataTable dt, string tableName)
17: {
18:
19: string sqlQuery = string.Empty;
20: string sqlDBType = string.Empty;
21: string dataType = string.Empty;
22: int maxLength = 0;
23: StringBuilder sb = new StringBuilder();
24:
25: sb.AppendFormat(string.Format("CREATE TABLE {0} (", tableName));
26:
27: for (int i = 0; i < dt.Columns.Count; i++)
28: {
29: dataType = dt.Columns[i].DataType.ToString();
30: if (dataType == "System.Int32")
31: {
32: sqlDBType = "INT";
33: }
34: else if (dataType == "System.String")
35: {
36: sqlDBType = "NVARCHAR";
37: maxLength = dt.Columns[i].MaxLength;
38: }
39:
40: if (maxLength > 0)
41: {
42: sb.AppendFormat(string.Format(" {0} {1} ({2}), ", dt.Columns[i].ColumnName, sqlDBType, maxLength));
43: }
44: else
45: {
46: sb.AppendFormat(string.Format(" {0} {1}, ", dt.Columns[i].ColumnName, sqlDBType));
47: }
48: }
49:
50: sqlQuery = sb.ToString();
51: sqlQuery = sqlQuery.Trim().TrimEnd(',');
52: sqlQuery = sqlQuery + " )";
53:
54: using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
55: {
56: sqlConn.Open();
57: SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn);
58: sqlCmd.ExecuteNonQuery();
59: sqlConn.Close();
60: }
61:
62: }
63: private void LoadDataToDatabase(string tableName, string fileFullPath, string delimeter)
64: {
65: string sqlQuery = string.Empty;
66: StringBuilder sb = new StringBuilder();
67:
68: sb.AppendFormat(string.Format("BULK INSERT {0} ", tableName));
69: sb.AppendFormat(string.Format(" FROM '{0}'", fileFullPath));
70: sb.AppendFormat(string.Format(" WITH ( FIELDTERMINATOR = '{0}' , ROWTERMINATOR = '\n' )", delimeter));
71:
72: sqlQuery = sb.ToString();
73:
74: using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
75: {
76: sqlConn.Open();
77: SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn);
78: sqlCmd.ExecuteNonQuery();
79: sqlConn.Close();
80: }
81: }
82: protected void Page_Load(object sender, EventArgs e)
83: {
84:
85: }
86: protected void BTNImport_Click(object sender, EventArgs e)
87: {
88: if (FileUpload1.HasFile)
89: {
90: FileInfo fileInfo = new FileInfo(FileUpload1.PostedFile.FileName);
91: if (fileInfo.Name.Contains(".csv"))
92: {
93:
94: string fileName = fileInfo.Name.Replace(".csv", "").ToString();
95: string csvFilePath = Server.MapPath("UploadedCSVFiles") + "\\" + fileInfo.Name;
96:
97: //Save the CSV file in the Server inside 'MyCSVFolder'
98: FileUpload1.SaveAs(csvFilePath);
99:
100: //Fetch the location of CSV file
101: string filePath = Server.MapPath("UploadedCSVFiles") + "\\";
102: string strSql = "SELECT * FROM [" + fileInfo.Name + "]";
103: string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='text;HDR=YES;'";
104:
105: // load the data from CSV to DataTable
106:
107: OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, strCSVConnString);
108: DataTable dtCSV = new DataTable();
109: DataTable dtSchema = new DataTable();
110:
111: adapter.FillSchema(dtCSV, SchemaType.Mapped);
112: adapter.Fill(dtCSV);
113:
114: if (dtCSV.Rows.Count > 0)
115: {
116: CreateDatabaseTable(dtCSV, fileName);
117: Label2.Text = string.Format("The table ({0}) has been successfully created to the database.", fileName);
118:
119: string fileFullPath = filePath + fileInfo.Name;
120: LoadDataToDatabase(fileName, fileFullPath, ",");
121:
122: Label1.Text = string.Format("({0}) records has been loaded to the table {1}.", dtCSV.Rows.Count, fileName);
123: }
124: else
125: {
126: LBLError.Text = "File is empty.";
127: }
128: }
129: else
130: {
131: LBLError.Text = "Unable to recognize file.";
132: }
133:
134: }
135: }
136: }
137: }