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
Peter KiersOperatorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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[])
Peter KiersOperatorAuthor Commented:
Could you PLEASE, PLEASE help me because I have no idea how
to implement your solution.
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
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Peter KiersOperatorAuthor Commented:
Can you show me option 1
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.
Peter KiersOperatorAuthor Commented:
I get an error:

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

P.
darkyin87Commented:
for getting the data something like this

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

Hope that helps
Peter KiersOperatorAuthor Commented:
Where do I have to put it in my example
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();
        }

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Peter KiersOperatorAuthor 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.
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?
Peter KiersOperatorAuthor 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.
Peter KiersOperatorAuthor Commented:
Maybe I need something else than the filestream command.

P.
Peter KiersOperatorAuthor Commented:
f.e: i binary reader
Peter KiersOperatorAuthor Commented:
I give up
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.