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();
}
}
}
}