I have this project:
.txt fileThe text file is divided by tab in four fields so as the database.
I've done the first step using a rich text box and saving all the data on a string. My idea is to split the string every line and save it on an array but then: How do I split every row so I can save the fields properly? How do I connect a database on SQL Server to my project on C#?
Let's tackle this one step at a time...
Getting the data:
string readText = File.ReadAllText("path to my file.txt");
Now that we have it in a string we can parse it
List<string> listStrLineElements = List<string> listStrLineElements = line.Split(new string[] { Environment.NewLine }, StringSplitOptions.None).ToList();// You need using System.Linq at the top.
More options for splitting at a new line can be found here
Now, you need to split each element into its own row; you said you had it tab delimited which is good (something like comma seperated (CSV) is more common modernly, but tabs will work!)... For that we can do something like this:
List<string> rowList = listStrLineElements.SelectMany(s => s.Split('\t')).ToList();// The \t is an *escape character* meaning tab.
now, you need something like a loop to go through each one of these entries and insert it into the database, which means we now need our database connection...
Connecting to the database
Make sure you are using the SQL namespace...
using System.Data.SqlClient;
private void sqlCon(List<string> x)
{
//Replace with your server credentials/info
SqlConnection myConnection = new SqlConnection("user id=username;" +"password=password;server=serverurl;" +"Trusted_Connection=yes;" +"database=database; " + "connection timeout=30");
try
{
myConnection.Open();
for (int i = 0; i <= x.Count -4; i += 4)//Implement by 3...
{
//Replace table_name with your table name, and Column1 with your column names (replace for all).
SqlCommand myCommand = new SqlCommand("INSERT INTO table_name (Column1, Column2, Column3, Column4) " +
String.Format("Values ('{0}','{1}','{2}','{3}')", x[i], x[i + 1], x[i + 2], x[i + 3]), myConnection);
myCommand.ExecuteNonQuery();
}
}
catch (Exception e){Console.WriteLine(e.ToString());}
try{myConnection.Close();}
catch (Exception e){Console.WriteLine(e.ToString());}
}
Note, you may very likely have to change/edit my loop; the logic behind it is to implement i by 4 so you can read each of your columns, so the columns Count (number of entries) must be 4 less so you don't get an index out of bounds (typing this all in notepad I'm not sure if I got all the logic right, but again, if it's wrong that is something you will have to fix, we don't just write code for people).
PLEASE NOTE!
things like string.format() leave you open for an SQL injection, you really should lookup SQL parameters, but this will get the job done. Here are some posts that I consider a must read:
To connect to the database, you'll need a connection string with all the info inside the app.config file. Once you connect to the db, there should be no problem to add a new row or even update one from inside your code. Simply build a Query you where you enter the string data for each column of the new row !
There is a ton of documentation on how to build SQL queries and how to connect to a database on the Microsoft website, give it a look :)
You could try using BCP from command line. This makes a bunch of assumptions about what permissions you have, and how you access the database, but basically you can call this on the database server (its a hidden feature) or you can copy the file locally:
https://msdn.microsoft.com/en-us/library/ms162802.aspx
BCP.exe .. in -S -U -P -c -t
-c is "character format" -t is field terminator - leave blank for TAB
-U and -P can be replaced with -T if it is a trusted connection.
Some advice: These things are always better handled by importing to a staging table, making any changes you need, and then having a SQL function handle the transfer to the production table. This way you can make sure you don't blow away data by accident.
I would advise making use of Lists and datatable in this scenarios. The use of lists is clean when compared to array and the same applies to operations on the database using data tables. You could refer this post on stackoverflow to retrieve data from a text file into a data table. This post could also help
Hope this helps..
