I have large excel csv file(about 2000 columns)I want to store these data into my Local service based data table. But local dB support only 1024 columns.so I have to divide that csv into several parts and stored in to the database. How can I divide and stored into multiple table? and I've to show the data together in db. How can I do all these things through c# codes?? please help me. my code is here and it doesn't work
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using System.IO;
using System.Data.SqlClient;
namespace table_divide
{
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
DataTable dt = new DataTable();
string file_path = @"D:\Crysis3_25x16_VeryHigh-TotalFile\Crysis3_25x16_VeryHigh_dcc_off_Perf\Crysis3_25x16_VeryHigh_dcc_off_Perf.csv";
int count = 0;
int count2=0;
int row = 0;
string[] table_data;
string sql = string.Empty;
string table_name;
private void Button_Click(object sender, RoutedEventArgs e)
{
StreamReader readfile = new StreamReader(file_path);
{
string line;
string tabname;
while((line=readfile.ReadLine())!=null&& count<10)
{
if (count2 == 100)
break;
table_data = line.Split("\r\n".ToCharArray(),StringSplitOptions.RemoveEmptyEntries );
if(count>0)
{
(from st in table_data.Skip(0) select dt.Rows.Add(st.Split(",".ToCharArray()))).ToList();
}
else if(count==0)
{
var col = from cl in table_data[0].Split(",".ToCharArray()) select new DataColumn(cl.Trim().ToUpper().Replace(" ", ""));
dt.Columns.AddRange(col.ToArray());
create_table(table_name);
}
count++;
count2++;
if (count == 10)
{
insert_data(table_name);
count = 1;
dt.Rows.Clear();
}
}
insert_data(table_name);
dt.Rows.Clear();
}
MessageBox.Show("inserted");
}
private void create_table(string table_name)
{
try
{
SqlCommand cmd = null;
using (SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\MyPractice\table_divide\table_divide\Database2.mdf;Integrated Security=True"))
{
con.Open();
if (count == 0)
{
sql = "CREATE TABLE " + System.IO.Path.GetFileNameWithoutExtension(file_path) + "1 ( DRAWID INTEGER PRIMARY KEY AUTOINCREMENT, ";
for (int i = 0; i < 1000; i++)
{
sql += dt.Columns[i].ToString() + " INTEGER,";
}
sql = sql.Substring(0, sql.Length - 1);
sql += ")";
cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
}
sql = null;
if (count == 0)
{
sql = "CREATE TABLE" + System.IO.Path.GetFileNameWithoutExtension(file_path) + "2 ( DRAWID INTEGER PRIMARY KEY AUTOINCREMENT, ";
for (int J = 0; J < 2000; J++)
{
sql += dt.Columns[J].ToString() + "INTEGER ,";
}
sql = sql.Substring(0, sql.Length - 1);
sql += ")";
cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
}
sql = null;
if (count == 0)
{
sql = "CREATE TABLE" + System.IO.Path.GetFileNameWithoutExtension(file_path) + "3 ( DRAWID INTEGER PRIMARY KEY AUTOINCREMENT, ";
for (int k = 0; k < 2000; k++)
{
sql += dt.Columns[k].ToString() + "INTEGER,";
}
sql = sql.Substring(0, sql.Length - 1);
sql += ")";
cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
}
count = 1;
con.Close();
}
}
catch(Exception e)
{
}
}
public void insert_data(string table_name)
{
SqlCommand cmd;
string sql = string.Empty;
using(SqlConnection con=new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\MyPractice\table_divide\table_divide\Database2.mdf;Integrated Security=True"))
{
con.Open();
foreach(DataRow dr in dt.Rows)
{
sql = "INSERT INTO" + System.IO.Path.GetFileNameWithoutExtension(file_path) + "1" + "VALUES(" + row + ",";
for(int i=0;i<1000;i++)
{
sql += dr[i] + ",";
}
sql = sql.Substring(0, sql.Length - 1);
sql += ")";
cmd = new SqlCommand(sql,con);
cmd.ExecuteNonQuery();
sql = "INSERT INTO" + System.IO.Path.GetFileNameWithoutExtension(file_path) + "2" + "VALUES(" + row + ",";
for (int i = 1000; i < 2000; i++)
{
sql += dr[i] + ",";
}
sql = sql.Substring(0, sql.Length - 1);
sql += ")";
cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
sql = "INSERT INTO" + System.IO.Path.GetFileNameWithoutExtension(file_path) + "3" + "VALUES(" + row + ",";
for (int i = 2000; i < 2710; i++)
{
sql += dr[i] + ",";
}
sql = sql.Substring(0, sql.Length - 1);
sql += ")";
cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
row++;
con.Close();
}
}
}
}
}