C# Question

Read .csv file, store in SQL database?

For a programming asignment, I am to program a prototype.
Right now I'm only looking to code 1 method.
The method is supposed to read a locale .csv file, and save it as a BLOB (Binary Large Object) in a database on an external server.
However, I'm quite new at C#, and I am used to Java. I'm not exactly sure what a BLOB is, some sort of Byte Array or something?

So far my program can


  • Connect to the SQL server.

  • Read the .csv file.



The database table is called tblUsers.

The field I'm trying to insert it into is BlobFile, which is of datatype varbinary(8000).

I don't even know if the datatype is correct.

All I want is the .csv file saved into a table on a server.

"String or binary data would be truncated. The statement has been terminated."
Is what I get unfortunately, which I can figure out means that my .csv file somewhat doesn't match with my datatype in the table.
I don't know how to link you to the .csv but it looks like:

4.012 3.012 1.312 3.321 4.232


and so on.
This is the C# code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;


namespace WindowsFormsApplication8
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}


private void button1_Click(object sender, EventArgs e)
{
string filePath = "C:/Users/Soeren/Desktop/epilepsi - semester/EpilepsiEKG/Patient1_Reciprocal_HFpower_x1.csv";
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
string line = fs.ReadLine();
string[] value = line.Split(',');
BinaryReader reader = new BinaryReader(fs);


byte[] BlobValue = reader.ReadBytes((int)fs.Length);
fs.Close();
reader.Close();
//FILE READ!

SqlConnection con = new SqlConnection(@"Data Source=webhotel10.iha.dk;Initial Catalog=F13ST2ITS2201270867;Persist Security Info=True;User ID=F13ST2ITS2201270867;Password=F13ST2ITS2201270867");
SqlCommand com = new SqlCommand("insert into tblUsers(BlobFilename,BlobFile) values(@BlobFilename,@Blobfile)", con);
SqlParameter BlobFileNameParam = new SqlParameter("@BlobFileName", SqlDbType.NChar);
SqlParameter BlobFileParam = new SqlParameter("@BlobFile", SqlDbType.Binary);
com.Parameters.Add(BlobFileNameParam);
com.Parameters.Add(BlobFileParam);

BlobFileNameParam.Value = filePath.Substring(filePath.LastIndexOf("/") + 1);

BlobFileParam.Value = BlobValue;

try
{

com.Connection.Open();
com.ExecuteNonQuery();
MessageBox.Show(BlobFileNameParam.Value.ToString() + " saved to database.", "BLOB Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);

}


catch (Exception ex)
{

MessageBox.Show(ex.Message, "Save Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);

}


finally
{

com.Connection.Close();

}
}

}


}

Answer

The way the BlobFileParam Parameter is declared, it is a fixed length binary Parameter with size 0 (as the size is not specified). You try to insert data with a length greater than the specified size (0). Try setting the size property of the parameter BlobFileParam to the correct length; I also think the SqlDbType.VarBinary might be a better choice for the type of the parameter.