数据库编程

开发平台：
Visual C++

DataBase.cs：源码内容
							﻿using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.IO;
namespace ABP
{
    class DataBase
    {
        public DataBase()
        {
            objcommand.Connection = objConnection;
            objcommand.Connection = objConnection;
        }
        #region defining components
        /******************  defining components and global variables**********************/
        SqlConnection objConnection = new SqlConnection("Password=0000;Persist Security Info=True;User ID=far;Initial Catalog=mytest;Data Source=FARKISH");
        SqlCommand objcommand = new SqlCommand();
        // public and private string temp;
        private SqlDataReader DataReaderInstance;
        private bool Connection_Fail_Flag = false;
        private string[] Main_Tbl_Fields = { "Device_Address", "Device_Name", "Device_Picture", "Auto_Manual", 
                                             "Overal_Trip", "Delay_Shutdown", "Relay_Output", "Reset_Alarm", 
                                             "Signal_Source", "Sensor_Scale", "Velo_Acc", "Adj_Change", 
                                             "RMS_Value", "Date_Time" };
        private string[] Main_Tbl_Fields_Types = { "tinyint", "nchar(30)", "image", "bit", 
                                                   "int", "int", "bit", "bit", 
                                                   "bit", "int", "bit", "bit", 
                                                   "smallint", "smalldatetime" };
        #endregion
        #region properties & methods
        /******************  defining properties and methods**********************/
        public bool ConnectionOpen()                        //open the connection to the DataBase
        {
            try
            {
                objConnection.Open();
                return true;
            }
            catch { return false; }
        }
        public bool ConnectionClose()                       //close the connection to the DataBase
        {
            try
            {
                objConnection.Close();
                return true;
            }
            catch { return false; }
        }
        public bool Connection_Fail
        {
            get { return Connection_Fail_Flag; }
            set
            {
                if (value == false)
                    Connection_Fail_Flag = value;
            }
        }
        #endregion
        #region functions
        /*******************************  functions  ********************************/
      
        /// <summary>
        /// if the tables exist the function return false
        /// if the tables was not exist , the function create talbes and return true
        /// if the tables was not exist , and the function cant creat them , return true and connection_fail_Flag=true
        /// if can not connect to data base return false and connection_fail_Flag=true
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool Create_Table(string name)
        {
            try
            {
                objConnection.Open();
                try                         //use for cheaking the tables have been bielt or no
                {
                    objcommand.CommandText = "SELECT * FROM Main_Table";
                    DataReaderInstance = objcommand.ExecuteReader();
                    return false;
                }
                catch
                {
                    try
                    {
                        objcommand.CommandText = "create table" + Main_Tbl_Fields[0] + " " + Main_Tbl_Fields_Types[0] + " , " + Main_Tbl_Fields[1] + " " + Main_Tbl_Fields_Types[1] + " , "
                                                 + Main_Tbl_Fields[2] + " " + Main_Tbl_Fields_Types[2] + " , " + Main_Tbl_Fields[3] + " " + Main_Tbl_Fields_Types[3] + " , "
                                                 + Main_Tbl_Fields[4] + " " + Main_Tbl_Fields_Types[4] + " , " + Main_Tbl_Fields[5] + " " + Main_Tbl_Fields_Types[5] + " , "
                                                 + Main_Tbl_Fields[6] + " " + Main_Tbl_Fields_Types[6] + " , " + Main_Tbl_Fields[7] + " " + Main_Tbl_Fields_Types[7] + " , "
                                                 + Main_Tbl_Fields[8] + " " + Main_Tbl_Fields_Types[8] + " , " + Main_Tbl_Fields[9] + " " + Main_Tbl_Fields_Types[9] + " , "
                                                 + Main_Tbl_Fields[10] + " " + Main_Tbl_Fields_Types[10] + " , " + Main_Tbl_Fields[11] + " " + Main_Tbl_Fields_Types[11] + " , "
                                                 + Main_Tbl_Fields[12] + " " + Main_Tbl_Fields_Types[12] + " , " + Main_Tbl_Fields[13] + " " + Main_Tbl_Fields_Types[13];
                        objcommand.ExecuteNonQuery();
                        for (byte i = 2; i <= 255; i++)
                        {
                            objcommand.CommandText = "create table T" + i.ToString() + " Date_Time SmallDateTim  , RMS_Value smallint";
                            objcommand.ExecuteNonQuery();
                        }
                    }
                    catch
                    {
                        Connection_Fail_Flag = true;
                    }
                }
                objConnection.Close();
                return true;
            }
            catch
            {
                try { objConnection.Close(); }
                catch { }
                Connection_Fail_Flag = true;
                return false;
            }
        }
      
        /// <summary>
        /// reads all fields in main table exept Image fild
        /// if cant read them connection_fail_Flag = true;
        /// </summary>
        /// <returns></returns>
        public string[,] Main_Table_Read()
        {
            byte Row_Nmb = 0;
            byte i = 0;
            string[,] OutPut_Array;
            try
            {
                objConnection.Open();
                objcommand.CommandText = "SELECT * FROM Main_Table";
                DataReaderInstance = objcommand.ExecuteReader();
                while (DataReaderInstance.Read())
                    Row_Nmb++;
                objcommand.CommandText = "SELECT * FROM Main_Table";
                DataReaderInstance = objcommand.ExecuteReader();
                OutPut_Array = new string[Row_Nmb, Main_Tbl_Fields.Length-1];
                while (DataReaderInstance.Read())
                {
                    for (byte j = 0; j < Main_Tbl_Fields.Length; j++)
                    {
                        if (j != (byte)(2))                                //because field 2 contain image
                            OutPut_Array[i, j] = Convert.ToString(DataReaderInstance[Main_Tbl_Fields[j]]);
                        else
                            OutPut_Array[i, 2] = "NotRead";
                    }
                    
                    i++;
                }
                return OutPut_Array;
            }
            catch
            {
                try { objConnection.Close(); }
                catch { }
                Connection_Fail_Flag = true;
                string[,] Error_OutPut_Array = null;
                return Error_OutPut_Array;
            }
        }
        /// <summary>
        /// writes data in hte specific tacles and update 
        /// value and date_time in the main table
        /// if can do this nothing happend
        /// else Connection_Fail_Flag will be true
        /// </summary>
        /// <param name="Input_Array"></param>
        public void Write_Data(string[,] Input_Array)
        {
            try
            {
                objConnection.Open();
                for (byte i = 0; i < Input_Array.GetLength(0); i++)
                {
                    objcommand.Parameters.AddWithValue("@RmsValue", Convert.ToInt16(Input_Array[i, 1]));
                    objcommand.Parameters.AddWithValue("@DateTime", Convert.ToDateTime(Input_Array[i, 2]));
                    objcommand.CommandText = "UPDATE Main_Table SET " + Main_Tbl_Fields[12] + " = @RmsValue , "
                                              + Main_Tbl_Fields[13] + " = @DateTime  WHERE "
                                              + Main_Tbl_Fields[0] + " = " + Input_Array[i, 0];
                    objcommand.ExecuteNonQuery();
                    objcommand.CommandText = "INSERT INTO " + Input_Array[i, 0] +
                                             " (" + Main_Tbl_Fields[13] + " , " + Main_Tbl_Fields[12] +
                                             ") VALUES(@DateTime, @RmsValue)";
                    objcommand.ExecuteNonQuery();
                    objcommand.Parameters.RemoveAt("@RmsValue");
                    objcommand.Parameters.RemoveAt("@DateTime");
                }
                objConnection.Close();
            }
            catch 
            {
                try { objConnection.Close(); }
                catch { }
                Connection_Fail_Flag = true;
            }
        }
        /// <summary>
        /// wite image on the specific address
        /// if can do this nothing happend
        /// else Connection_Fail_Flag will be true
        /// </summary>
        /// <param name="?"></param>
        public void Image_Write(string Address, Image Temp_Image)
        {
            try
            {
                MemoryStream Obj_MemoryStream = new MemoryStream();
                Temp_Image.Save(Obj_MemoryStream, Temp_Image.RawFormat);
                byte[] Array_Image = new byte[5000000];
                Array_Image = Obj_MemoryStream.GetBuffer();
                objConnection.Open();
                objcommand.Parameters.Add("@picture_file", SqlDbType.Image).Value = Array_Image;
                objcommand.Parameters.AddWithValue("@Address", Convert.ToByte(Address));
                objcommand.CommandText = "UPDATE Main_Table SET " + Main_Tbl_Fields[2] +
                                          " = @picture_file WHERE" + Main_Tbl_Fields[0] + " = @Address";
                objcommand.ExecuteNonQuery();
                objcommand.Parameters.RemoveAt("@picture_file");
                objcommand.Parameters.RemoveAt("@Address");
                objConnection.Close();
            }
            catch
            {
                try { objConnection.Close(); }
                catch { }
                Connection_Fail_Flag = true;
            }
            
        }
        /// <summary>
        /// read image from specific address
        /// if can do this nothing happend
        /// else Connection_Fail_Flag will be true
        /// </summary>
        /// <param name="?"></param>
        public Image Read_Image(string Address)
        {
            try
            {
                Image Temp_Image;
                objConnection.Open();
                objcommand.Parameters.AddWithValue("@Address", Convert.ToByte(Address));
                objcommand.CommandText = "SELECT " + Main_Tbl_Fields[2] + "FROM Main_Table"
                                         + " WHERE" + Main_Tbl_Fields[0] + " = @Address";
                DataReaderInstance = objcommand.ExecuteReader();
                DataReaderInstance.Read();
                objcommand.Parameters.RemoveAt("@Address");
                byte[] Array_Image = (byte[])(DataReaderInstance[Main_Tbl_Fields[2]]);
                MemoryStream Obj_MemoryStream = new MemoryStream(Array_Image);
                Temp_Image = Image.FromStream(Obj_MemoryStream);
                objConnection.Close();
                return Temp_Image;
            }
            catch
            {
                try { objConnection.Close(); }
                catch { }                
                Connection_Fail_Flag = true;
                Image Error_Image = null;
                return Error_Image;
            }
        }
        /// <summary>
        /// return rms and date_time between start and end
        /// if cant retrive data connection_fail_flag will be TRUE
        /// </summary>
        /// <param name="Address"></param>
        /// <param name="Start"></param>
        /// <param name="End"></param>
        /// <returns></returns>
        public string[,] Search_Data(string Address, DateTime Start, DateTime End)
        {
            string[,] Output_Array;
            int Data_Count = 0;
            int i = 0;
            try
            {
                objConnection.Open();
                objcommand.Parameters.AddWithValue("@Start", Start);
                objcommand.Parameters.AddWithValue("@End", End);
                objcommand.CommandText = "SELECT * FROM T" + Address + " WHERE DateAndTime between @Start and @End";
                DataReaderInstance = objcommand.ExecuteReader();
                while (DataReaderInstance.Read())
                    Data_Count++;
                Output_Array = new string[Data_Count, 2];
                DataReaderInstance = objcommand.ExecuteReader();
                while (DataReaderInstance.Read())
                {
                    Output_Array[i, 0] = Convert.ToString(DataReaderInstance["Date_Time"]);
                    Output_Array[i, 1] = Convert.ToString(DataReaderInstance["RMS_Value"]);
                    i++;
                }
                objcommand.Parameters.RemoveAt("@Start");
                objcommand.Parameters.RemoveAt("@End");
                objConnection.Close();
                return Output_Array;
            }
            catch
            {
                try { objConnection.Close(); }
                catch { }
                Connection_Fail_Flag = true;
                string[,] Error_Array =null;
                return Error_Array;
            }
        }
        /// <summary>
        /// write adjusts to database , if  adjust exist update them
        /// else create new line in Main_Table with Adjust values
        /// if cant do them , Conncetion_Fail_Flag will be true
        /// </summary>
        /// <param name="Adj_Values"></param>
        public void Write_Adjust(string[] Adj_Values)
        {
            try
            {
                objConnection.Open();
                objcommand.Parameters.AddWithValue("@Device_Address", Convert.ToByte(Adj_Values[0]));
                objcommand.Parameters.AddWithValue("@Device_Name", Convert.ToString(Adj_Values[1]));
                //adj_Values[2] not read because is image
                objcommand.Parameters.AddWithValue("@Auto_Manual", Convert.ToBoolean(Adj_Values[3]));
                objcommand.Parameters.AddWithValue("@Overal_Trip", Convert.ToInt32(Adj_Values[4]));
                objcommand.Parameters.AddWithValue("@Delay_Shutdown", Convert.ToInt32(Adj_Values[5]));
                objcommand.Parameters.AddWithValue("@Relay_Output", Convert.ToBoolean(Adj_Values[6]));
                objcommand.Parameters.AddWithValue("@Reset_Alarm", Convert.ToBoolean(Adj_Values[7]));
                objcommand.Parameters.AddWithValue("@Signal_Source", Convert.ToBoolean(Adj_Values[8]));
                objcommand.Parameters.AddWithValue("@Sensor_Scale", Convert.ToInt32(Adj_Values[9]));
                objcommand.Parameters.AddWithValue("@Velo_Acc", Convert.ToBoolean(Adj_Values[10]));
                objcommand.Parameters.AddWithValue("@Adj_Change", Convert.ToBoolean(Adj_Values[11]));
                objcommand.CommandText = "SELECT * FROM Main_Table WHERE " + Main_Tbl_Fields[0] + " = Device_Address";
                DataReaderInstance = objcommand.ExecuteReader();   //the the addres exist , it will be update , else it will be create
                if (DataReaderInstance.Read() == true)
                {
                    objcommand.CommandText = "UPDATE " + Adj_Values[0] + " SET " + Main_Tbl_Fields[0] + " = @Device_Address , " + Main_Tbl_Fields[1] + " = @Device_Name , "
                                             + Main_Tbl_Fields[3] + " = @Auto_Manual , " + Main_Tbl_Fields[4] + " = @Overal_Trip , " + Main_Tbl_Fields[5] + " = @Delay_Shutdown , "
                                             + Main_Tbl_Fields[6] + " = @Relay_Output , " + Main_Tbl_Fields[7] + " = @Reset_Alarm , " + Main_Tbl_Fields[8] + " = @Signal_Source , "
                                             + Main_Tbl_Fields[9] + " = @Sensor_Scale , " + Main_Tbl_Fields[10] + " = @Velo_Acc , " + Main_Tbl_Fields[11] + " = @Adj_Change , "
                                              + " WHERE " + Main_Tbl_Fields[0] + " = @Device_Address";
                    objcommand.ExecuteNonQuery();
                }
                else
                {
                    objcommand.CommandText = "INSERT INTO Main_Table ( " + Main_Tbl_Fields[0] + " , " + Main_Tbl_Fields[1] + " , " + Main_Tbl_Fields[3] + " , "
                                              + Main_Tbl_Fields[4] + " , " + Main_Tbl_Fields[5] + " , " + Main_Tbl_Fields[6] + " , " + Main_Tbl_Fields[7] + " , "
                                              + Main_Tbl_Fields[8] + " , " + Main_Tbl_Fields[9] + " , " + Main_Tbl_Fields[10] + " , " + Main_Tbl_Fields[11] + " )"
                                              + "VALUES @Device_Address, @Device_Name, @Auto_Manual, @Overal_Trip, @Delay_Shutdown, @Relay_Output, "
                                              + "@Reset_Alarm, @Signal_Source, @Sensor_Scale, @Velo_Acc, @Adj_Change";
                    objcommand.ExecuteNonQuery();
                }
                objcommand.Parameters.RemoveAt("@Device_Address");
                objcommand.Parameters.RemoveAt("@Device_Name");
                objcommand.Parameters.RemoveAt("@Auto_Manual");
                objcommand.Parameters.RemoveAt("@Overal_Trip");
                objcommand.Parameters.RemoveAt("@Delay_Shutdown");
                objcommand.Parameters.RemoveAt("@Relay_Output");
                objcommand.Parameters.RemoveAt("@Reset_Alarm");
                objcommand.Parameters.RemoveAt("@Signal_Source");
                objcommand.Parameters.RemoveAt("@Sensor_Scale");
                objcommand.Parameters.RemoveAt("@Velo_Acc");
                objcommand.Parameters.RemoveAt("@Adj_Change");
                objConnection.Close();
            }
            catch
            {
                try { objConnection.Close(); }
                catch { }
                Connection_Fail_Flag = true;
            }
        }
        /// <summary>
        /// it is just for test and will be delete finaly
        /// </summary>
        public void test()             
        {
            try
            {
                objConnection.Open();
                objcommand.Parameters.AddWithValue("@value", Convert.ToInt32(10));
                objcommand.CommandText = "SELECT * FROM arash1 WHERE value = @value";
                DataReaderInstance = objcommand.ExecuteReader();
                DataReaderInstance.Read();
                DataReaderInstance.Read();
                DataReaderInstance.Read();
                // if (DataReaderInstance.Read() == false)
                //   MessageBox.Show("AAAA");
              //  objcommand.Parameters.RemoveAt("@value");
                objConnection.Close();
            }
            catch 
            {
                objcommand.Parameters.AddWithValue("@value", Convert.ToInt32(12));
                objcommand.Parameters.AddWithValue("@value", Convert.ToInt32(13));
                objConnection.Close();
            }
        }
        #endregion
    }
}

						