· 7 years ago · Nov 28, 2018, 07:32 AM
1protected void UploadFile_Click(object sender, EventArgs e)
2 {
3 conStr = "workstation id=" + ServerName + ";packet size=4096;user id=xx;password=" yyyyy ";data source=" blahblah ";persist security info=False;initial catalog=";
4 conStr = conStr + DB;
5
6 string filepath = "D:\Work\Sample01.csv";
7 StreamReader sr = new StreamReader(filepath);
8 int NrLines = 0;
9 string[,] mline;
10 mline = new string[NrLines, 50];
11 int cntra = 0;
12 int counter = 0;
13
14 using (StreamReader cr = new StreamReader(filepath))
15 {
16 while ((cr.ReadLine()) != null)
17 {
18 NrLines++;
19 }
20 cr.Close();
21 }
22
23 mline = new string[NrLines, 25];
24
25 for (int lcounter = 1; (lcounter <= NrLines); lcounter++)
26 {
27
28 string[] sline = sr.ReadLine().Split(',');
29 //strElem = strElem.Append("");
30 if (sline != null)
31 {
32 for (int c = 0; c < sline.Length; c++)
33 mline[cntra, c] = sline[c];
34 cntra++;
35 }
36 }
37 sr.Close();
38
39 for (counter = 1; counter < NrLines; counter++)
40 {
41 string Date = mline[counter, 0].ToString();
42 string SiteUD = mline[counter, 1].ToString();
43 string SiteName = mline[counter, 2].ToString();
44 string ModelNo = mline[counter, 3].ToString();
45 string MachID = mline[counter, 4].ToString();
46 string Manufacture = mline[counter, 5].ToString();
47 string TotalCashIn = mline[counter, 6].ToString();
48 string TotalCashOut = mline[counter, 7].ToString();
49 string NotesIN = mline[counter, 8].ToString();
50 string CoinsIn = mline[counter, 9].ToString();
51 string CoinsOut = mline[counter, 10].ToString();
52 string CoinstoDrop = mline[counter, 11].ToString();
53 string RemoteCashIn = mline[counter, 12].ToString();
54 string RemoteCashOut = mline[counter, 13].ToString();
55 string TotalWin = mline[counter, 14].ToString();
56 string TotalBet = mline[counter, 15].ToString();
57 string GGR = mline[counter, 16].ToString();
58 string GamesPlayed = mline[counter, 17].ToString();
59 string HandPays = mline[counter, 18].ToString();
60 string HopperRefill = mline[counter, 19].ToString();
61
62 SQL = "INSERT INTO ztrewVNLCemsImport " +
63 "([Date], [SiteUD], [SiteName], [ModelNo.], [MachID], " +
64 "[Manufacture], [TotalCashIn], [TotalCashOut], [NotesIN], [CoinsIn], " +
65 "[CoinsOut], [CoinstoDrop], [RemoteCashIn], [RemoteCashOut], [TotalWin], " +
66 "[TotalBet], [GGR], [GamesPlayed], [HandPays], [HopperRefill] ) " +
67 "VALUES " +
68 "('" + Date + "', '" + SiteUD + "', '" + SiteName + "', '" + ModelNo + "', '" + MachID + "', " +
69 "'" + Manufacture + "', '" + TotalCashIn + "', '" + TotalCashOut + "', '" + NotesIN + "', '" + CoinsIn + "', " +
70 "'" + CoinsOut + "', '" + CoinstoDrop + "', '" + RemoteCashIn + "', '" + RemoteCashOut + "', '" + TotalWin + "', " +
71 "'" + TotalBet + "', '" + GGR + "', '" + GamesPlayed + "', '" + HandPays + "', '" + HopperRefill + "') ";
72 SQL = SQL.Replace('t', ' ');
73
74using System;
75using System.Collections.Generic;
76using System.Linq;
77using System.Text;
78using System.Threading.Tasks;
79using System.Data.SqlClient;
80using System.Data;
81using System.IO;
82
83namespace ImportCSVToSQL
84{
85 class Program
86 {
87 static void Main(string[] args)
88 {
89 try
90 {
91 using (SqlConnection conn = new SqlConnection(@"Data Source=**ServerName**; User ID = **; Password = **; Integrated Security=false"))
92 {
93 conn.Open();
94
95 #region create table
96
97 using (SqlCommand createTable = new SqlCommand(@"
98 IF EXISTS(SELECT TOP 1 *
99 FROM [DatabaseName].[dbo].[TableName])
100 DROP TABLE [DatabaseName].[dbo].[TableName]
101 IF NOT EXISTS
102 (
103 SELECT *
104 FROM
105 sys.schemas s
106 INNER JOIN sys.tables t ON
107 t.[schema_id] = s.[schema_id]
108 WHERE
109 s.name = 'dbo' AND
110 t.name = 'TableName'
111 )
112 CREATE TABLE [DatabaseName].[dbo].[TableName]
113 (
114 createddate varchar(500),
115 fid0 varchar(500),
116 fid1 varchar(500),
117 fid2 varchar(500),
118 fid3 varchar(500),
119 fpip varchar(500),
120 pid varchar(500),
121 isloggedin varchar(500),
122 sessionid varchar(500),
123 source varchar(500),
124 useragent varchar(500),
125 jpnumber varchar(500)
126 )
127
128 ;
129 ", conn))
130 {
131 createTable.ExecuteNonQuery();
132 }
133
134 #endregion
135
136 using (var reader = new StreamReader(@"CSV FILE PATH"))
137 {
138 Console.WriteLine("******* Uploading Data ...... ***********************");
139 while (!reader.EndOfStream)
140 {
141 var line = reader.ReadLine();
142 var data = line.Split(',');
143 // data[0] data[1]
144
145 string query = @"Insert into [DatabaseName].[dbo].[TableName]" + " values ('" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "','" + data[7] + "','" + data[8] + "','" + data[9] + "','" + data[10] + "','" + data[11] + "')";
146
147
148
149 SqlCommand cmd = new SqlCommand();
150 cmd.Connection = conn;
151 cmd.CommandText = query;
152 cmd.CommandType = CommandType.Text;
153 cmd.ExecuteNonQuery();
154 }
155 Console.WriteLine("******* Uploading Data Completed ***********************");
156 }
157
158 conn.Close();
159 }
160 }
161 catch(Exception ex)
162 {
163 Console.WriteLine(" n**************************** Error - " + ex.Message);
164 }
165
166 Console.ReadLine();
167 }
168 }
169}