Loading and saving text to a database field as BLOB.

Hi,

I have found an example how to save and load an image to a database field as BLOB.
But I want text to be saved not an image. So I have put a textBox1 on my form.
Who can help me to make my programm load and save text to a database field
I have put the example in the code-section.

Who can help me?

Peter
public partial class Form1 : Form
    {
        private string DBName = Application.StartupPath + @"\NoordWind.mdb";
        private string TheFile = Application.StartupPath + @"\Brandon.BMP";

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBName + ";");
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From MyImages", con);
            OleDbCommandBuilder MyCB = new OleDbCommandBuilder(da);
            DataSet ds = new DataSet("MyImages");

            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            FileStream fs = new FileStream(TheFile, FileMode.OpenOrCreate, FileAccess.Read);

            byte[] MyData = new byte[fs.Length];
            fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));

            fs.Close();

            da.Fill(ds, "MyImages");

            DataRow myRow;
            myRow = ds.Tables["MyImages"].NewRow();

            myRow["Description"] = "This would be description text";
            myRow["imgField"] = MyData;
            ds.Tables["MyImages"].Rows.Add(myRow);
            da.Update(ds, "MyImages");

            con.Close();

        }

        private void button2_Click(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBName + ";");
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From MyImages", con);
            OleDbCommandBuilder MyCB = new OleDbCommandBuilder(da);
            DataSet ds = new DataSet("MyImages");

            byte[] MyData = new byte[0];

            da.Fill(ds, "MyImages");
            DataRow myRow;
            myRow = ds.Tables["MyImages"].Rows[0];

            MyData = (byte[])myRow["imgField"];
            int ArraySize = new int();
            ArraySize = MyData.GetUpperBound(0);

            FileStream fs = new FileStream(TheFile, FileMode.OpenOrCreate, FileAccess.Write);
            fs.Write(MyData, 0, ArraySize);
            fs.Close();
        }

Open in new window

LVL 1
peterkiersAsked:
Who is Participating?
 
darkyin87Commented:
private void button1_Click(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBName + ";");
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From MyImages", con);
            OleDbCommandBuilder MyCB = new OleDbCommandBuilder(da);
            DataSet ds = new DataSet("MyImages");

            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            FileStream fs = new FileStream(TheFile, FileMode.OpenOrCreate, FileAccess.Read);

            String MyData = new String();
            fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));

            fs.Close();

            da.Fill(ds, "MyImages");

            DataRow myRow;
            myRow = ds.Tables["MyImages"].NewRow();

            myRow["Description"] = "This would be description text";
            myRow["imgField"] = MyData;
            ds.Tables["MyImages"].Rows.Add(myRow);
            da.Update(ds, "MyImages");

            con.Close();

        }


private void button2_Click(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBName + ";");
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From MyImages", con);
            OleDbCommandBuilder MyCB = new OleDbCommandBuilder(da);
            DataSet ds = new DataSet("MyImages");

            byte[] MyData = new byte[0];

            da.Fill(ds, "MyImages");
            DataRow myRow;
            myRow = ds.Tables["MyImages"].Rows[0];

            MyData = (byte[])myRow["imgField"];
String s = System.Text.Encoding.UTF8.GetString(MyData );
            int ArraySize = new int();
            ArraySize = MyData.GetUpperBound(0);

            FileStream fs = new FileStream(TheFile, FileMode.OpenOrCreate, FileAccess.Write);
            fs.Write(s, 0, ArraySize);
            fs.Close();
        }
0
 
LordWabbitCommented:
Simply convert the text to a byte array and back again
To convert a string to a byte array use
System.Text.ASCIIEncoding.ASCII.GetBytes("mystring")
and to get a string from a byte array
System.Text.ASCIIEncoding.ASCII.GetString(byteme[])
0
 
peterkiersAuthor Commented:
Could you PLEASE, PLEASE help me because I have no idea how
to implement your solution.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
darkyin87Commented:
1)  If your BLOB is stored as text, such as a SQL Server text field, you can pass the BLOB as a string parameter.
2) If the BLOB is stored in binary format, such as a SQL Server image field, you can pass an array of type byte as a binary parameter.

I hope this helps
0
 
peterkiersAuthor Commented:
Can you show me option 1
0
 
darkyin87Commented:
I don't think there is nothing much to change. Can you try this
String MyData = new String(); instead of this byte[] MyData = new byte[fs.Length];
I believe that should be enough.
0
 
peterkiersAuthor Commented:
I get an error:

Argument 1: cannot convert from 'string' to 'byte[]'      

P.
0
 
darkyin87Commented:
for getting the data something like this

byte[] byteBLOBData = (Byte[])(row["rowField"]);
String s = System.Text.Encoding.UTF8.GetString(byteBLOBData);

Hope that helps
0
 
peterkiersAuthor Commented:
Where do I have to put it in my example
0
 
peterkiersAuthor Commented:
I have errors:
'string' does not contain a constructor that takes 0 arguments      
The best overloaded method match for 'System.IO.Stream.Read(byte[], int, int)' has some invalid arguments      
Argument 1: cannot convert from 'string' to 'byte[]'

P.
0
 
darkyin87Commented:
I take back what I gave you before. Reading a text file as a byte or not should not matter. You can hav the same function that you had before and it should still work. Do you get any errors when try it that way. I mean the way you had for the images?
0
 
peterkiersAuthor Commented:
This is what I had at the beginning of my question:

private void button1_Click(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBName + ";");
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From MyImages", con);
            OleDbCommandBuilder MyCB = new OleDbCommandBuilder(da);
            DataSet ds = new DataSet("MyImages");

            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            FileStream fs = new FileStream(textBox1.Text, FileMode.OpenOrCreate, FileAccess.Read); <===


            byte[] MyData = new byte[fs.Length];
            fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));

            fs.Close();

            da.Fill(ds, "MyImages");

            DataRow myRow;
            myRow = ds.Tables["MyImages"].NewRow();

            myRow["Description"] = "This would be description text";
            myRow["imgField"] = MyData;
            ds.Tables["MyImages"].Rows.Add(myRow);
            da.Update(ds, "MyImages");

            con.Close();

        }


        private void button2_Click(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBName + ";");
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From MyImages", con);
            OleDbCommandBuilder MyCB = new OleDbCommandBuilder(da);
            DataSet ds = new DataSet("MyImages");

            byte[] MyData = new byte[0];

            da.Fill(ds, "MyImages");
            DataRow myRow;
            myRow = ds.Tables["MyImages"].Rows[0];

            MyData = (byte[])myRow["imgField"];
            int ArraySize = new int();
            ArraySize = MyData.GetUpperBound(0);

            FileStream fs = new FileStream(textBox1.Text, FileMode.OpenOrCreate, FileAccess.Write); <===
            fs.Write(MyData, 0, ArraySize);
            fs.Close();
        }

I get a logical error at the line where i put the arrow.

And that's because I change TheFile to textBox1.Text:

FileStream fs = new FileStream(TheFile, FileMode.OpenOrCreate, FileAccess.Read);  TO
FileStream fs = new FileStream(textBox1.Text, FileMode.OpenOrCreate, FileAccess.Read);

I get the error:
Empty path name is not legal.

P.
0
 
peterkiersAuthor Commented:
Maybe I need something else than the filestream command.

P.
0
 
peterkiersAuthor Commented:
f.e: i binary reader
0
 
peterkiersAuthor Commented:
I give up
0
 
darkyin87Commented:
oh ok.. I now understand what your problem is. You are trying to create a fielstream object on a textfield that is wrong.

This is what you need to do.
1) Read the text from file as byte Array and update the database
2) Read from the database as byteArray and the write the file.
3) All the other operations on the file can be just done as string itself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.