MySQL數據技術應用

一、  MySQL數據庫保存圖片

物理數據表欄位類型：MediumBlob,LongBlob

保存圖片處理步驟：

<1>打開圖片文件，將文件讀入一個文件流中（FileStream）

<2>聲明一個Byte類型的數組(Byte[]),將文件流中的數據讀入該數組中

<3>將保存SQL語句的圖片參數類型定義為MySqlDbType.LongBlob或MySqlDbType.MediumBlob類型

<4>將Byte[]數組賦給SQL參數對象的Value,並調用Prepare()方法

<5>執行保存SQL語句

 

示例代碼：

private void btnSave_Click(object sender, EventArgs e)

        {

            string FileName;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)

            {

                FileName = openFileDialog1.FileName;

                FileStream ms = new FileStream(FileName, FileMode.Open, FileAccess.Read);

                byte[] byteBlobData = new byte[ms.Length];

                ms.Position = 0;

                ms.Read(byteBlobData, 0, Convert.ToInt32(ms.Length));

                SaveImage(byteBlobData);

                ms.Close();

            }

        }

 

        private void SaveImage(byte[] Image)

        {

            MySqlCommand Comm=null;

            try

            {

                string SqlStr = "Update PUBPhotos Set Photo=?Photo Where ID=1 ";

                Comm = new MySqlCommand(SqlStr, (MySqlConnection) Conn );

 

                MySqlParameter Param = new MySqlParameter("?Photo", MySqlDbType.LongBlob);

                Param.Value = Image;

 

                Comm.Parameters.Add(Param);

                Comm.Prepare();    //關鍵語句

                Comm.ExecuteNonQuery();

                Comm.Dispose();

            }

            catch (Exception E)

            {

                Comm.Dispose();

                MessageBox.Show(E.Message);

            }

        }

 

讀取圖片處理步驟：

<1>讀取圖片數據，並將其強制轉換成Byte[]類型

<2>根據讀取的圖片數據(Byte[]類型)構造一個內存流對象(MemoryStream)

<3>根據內存流對象構造一個BitMap對象

<4>將BitMap對象賦給PictureBox控件的Image屬性

 

示例代碼：

  private void btnLoad_Click(object sender, EventArgs e)

        {

            byte[] image = GetImage();

            MemoryStream ms = new MemoryStream(image);

            Bitmap bmp = new Bitmap(ms);

            this.Photo.Image = bmp;

        }

 

        private byte[] GetImage()

        {

            MySqlDataReader Dr ;

            byte[] ImageData = null;

            string SqlStr = "Select Photo From  PUBPhotos Where ID=?Id";

            MySqlCommand Comm = new MySqlCommand(SqlStr, (MySqlConnection) Conn );

 

            MySqlParameter Param = new MySqlParameter("?Id", MySqlDbType.Int32);

            Param.Value = 1;

 

            Comm.Parameters.Add(Param);

            Comm.Prepare();    //關鍵語句

            Dr = Comm.ExecuteReader();

           

            if (Dr.Read())

                ImageData =  (byte[])Dr["Photo"];

 

            Dr.Close();

            Comm.Dispose();

            return ImageData;

        }

    }

 

二、  遍歷數據庫中所有表的所有數據列，將相應類型列更改為另一種類型

<1>用Mysql的Show Tables命令顯示數據庫中所有表

<2>取出表中所有數據列

<3>將特定類型的數據列類型改為另一種類型

 

代碼示例：

        private void button5_Click(object sender, EventArgs e)

        {

            Conn = new MySqlConnection("user id=developer;password=dev;initial catalog=SenLinDB;Server=10.168.0.13;Connect Timeout=30");

            Conn.Open();

            DataProvider Dp = new DataProvider( Conn );

            DataTable Tables = Dp.GetDataTable("show tables");

            this.progressBar1.Maximum = Tables.Rows.Count;

            foreach (DataRow Row in Tables.Rows)

            {

                string TableName = Row[0].ToString();

                string SelectSql = string.Format("Select * From {0}",TableName);

                DataTable Dt = Dp.GetDataTable(SelectSql);

                int i = 1;

                foreach ( DataColumn Col in Dt.Columns)

                {

                    if (Col.DataType == typeof(System.Decimal))

                    {

                        string update = string.Format("alter table {0} change {1} {1} varchar(50)",TableName,Col.ToString());

                        MySqlCommand Comm = new MySqlCommand(update, Conn );

                        Comm.ExecuteNonQuery();

                    }

                }

                this.progressBar1.Value = i;

                i++;

            }

            MessageBox.Show("更新完成！");

        }

 

 

說明：

<1>遠程對象屬性不能聲明為BitMap類型，否則在訪問時提示異常

<2>圖片要顯示在水晶報表中時，隻需要將對象屬性定義為Byte[]類型（不能聲明為BitMap類型，否則會過濾掉屬性），然後將該屬性拖放至水晶報表設計頁面即可