IPCALLServicePro/HNWD.Pregrant.DataAccess/clsDataBaseRW.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Runtime.InteropServices;
using System.Data.OleDb;
using System.IO;
using System.Web;
using System.Data;

namespace HNWD.Pregrant.DataAccess
{
    public class clsDataBaseRW
    {

        private String DHCPINIFIlE = "";//'保存DHCP服务器的INI文件路径
        private String strDataBaseServerName = "";//'数据库的IP地址，若为空表示本机IP
        private String strDataBaseServerUserName = "";//'数据库连接用户名
        private String strDataBaseServerPassword = ""; //数据库连接密码

        //    ''' 声明从INI配置文件中获取类型为String的配置项的值的系统函数
        [DllImport("kernel32.dll",SetLastError = true, EntryPoint = "GetPrivateProfileString")]
　　    private extern static Int32 GetPrivateProfileString(string lpAppName ,string  lpKeyName ,String nDefault , string lpFileName);

        ///    ''' 声明从INI配置文件中获取类型为string的配置项的值的系统函数
      [DllImport("kernel32.dll", SetLastError = true, EntryPoint = "GetPrivateProfileString")]
        private extern static Int32 GetPrivateProfileString(string lpAppName, string lpKeyName, string lpDefault, string lpReturnedString, int nSize, string lpFileName);

        ///    '''声明向INI配置文件中写入类型为string的配置项的值的系统函数
      [DllImport("kernel32.dll", SetLastError = true, EntryPoint = "WritePrivateProfileString")]
      private extern static Int32 WritePrivateProfileString(string lpAppName, string lpKeyName, string lpString, string lpFileName);

        //'''<summary>
        //   '''INI文件路径名称
        // '''</summary>
        private string  strIniFile = ""; //              'InI文件的路径名称
        private OleDbConnection DBconn ;
        private bool InitFlag = false;

        private Int32 GetStringFromINI(string sectionName, string keyName, String defaultValue, string iniPath)
        {
            return GetPrivateProfileString(sectionName, keyName, defaultValue, iniPath);
        }

        private string GetStrFromINI(string sectionName, string keyName, string defaultValue, string initPath)
        {

            string buffer = " ".PadLeft(256);
            int rc = GetPrivateProfileString(sectionName, keyName, defaultValue, buffer, buffer.Length, initPath);

            return  buffer.Substring(0, buffer.IndexOf("") - 1);
  
        }

        private Int32 WriteStrINI(string sectionName, string keyName, string setValue, string iniPath)
        {
            int rc = WritePrivateProfileString(sectionName, keyName, setValue, iniPath);
            if(rc > 0)
            {
                rc = 1;
            }
            return rc;
        }

 
        //'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
        //'返回DHCP服务器中MAC地址对应的IP地址

        public string DHCP_ReadIPAddressByMAC(string strMAC)
        {
            string Res = "";
             strMAC = strMAC.Replace(":", "-");

             Res = GetStrFromINI(strMAC.Trim(), "IPADDR", "", DHCPINIFIlE);

            return Res;
        }
 
        //'设置MACf地址对应的分配IP地址

        public void DHCP_WriteIPAddressByMAC(string strMAC , string IPAddress)
        {
             DHCP_LockFileReadOnly(false);
             strMAC = strMAC.Replace(":", "-");

            WriteStrINI(strMAC.Trim(), "IPADDR", IPAddress, DHCPINIFIlE);
                    DHCP_LockFileReadOnly(true);
        }
 
    //'设置DHCP模块相关的参数

        public void DHCP_WriteDHCPSevCFG(string IPMASK , string GetWay , string DNS , string StartIP , string EndIP)
        {
            DHCP_LockFileReadOnly(false);

             WriteStrINI("General", "SUBNETMASK", IPMASK.Trim(), DHCPINIFIlE);
            WriteStrINI("General", "ROUTER_1", GetWay.Trim(), DHCPINIFIlE);
            WriteStrINI("General", "DNS_1", DNS.Trim(), DHCPINIFIlE);
            WriteStrINI("Settings", "IPPOOL_1", StartIP.Trim() + "-" + EndIP.Split(new char[]{'.'})[3], DHCPINIFIlE);
            DHCP_LockFileReadOnly(true);

        }

        private void DHCP_LockFileReadOnly(bool Action)
        {
            if(File.Exists(DHCPINIFIlE))
            {
                if(Action)
                {
                    File.SetAttributes(DHCPINIFIlE, File.GetAttributes(DHCPINIFIlE) | FileAttributes.ReadOnly);
                }
                else
                {
                    //File.SetAttributes(DHCPINIFIlE, File.GetAttributes(DHCPINIFIlE) && (^FileAttributes.ReadOnly));
                }
            }
        }

        public void  DHCP_CreateIPList()
        {
            string IPMASK = "";
            string GetWay = "";
            string DNS = "";
            string StartIP = "";
            string InstallAsService = "";
            UInt32 i = 0;
     
 
        IPMASK = GetStrFromINI("General", "SUBNETMASK", "255.255.255.0", DHCPINIFIlE);
        GetWay = GetStrFromINI("General", "ROUTER_1", "192.168.1.1", DHCPINIFIlE);
        DNS = GetStrFromINI("General", "DNS_1", "192.168.1.1", DHCPINIFIlE);
        StartIP = GetStrFromINI("Settings", "IPPOOL_1", "192.168.1.50 - 250", DHCPINIFIlE);
        InstallAsService = GetStrFromINI("Settings", "InstallAsService", "2", DHCPINIFIlE);
        //'================================================================================
        if(File.Exists(DHCPINIFIlE))
        {
            File.Copy(DHCPINIFIlE, DHCPINIFIlE + string.Format(System.DateTime.Now.ToString("yyyyMMDDHHmmss")));
            DHCP_LockFileReadOnly(false);
            File.Delete(DHCPINIFIlE);
       
        }
       
   
        WriteStrINI("General", "SUBNETMASK", IPMASK.Trim(), DHCPINIFIlE);
        WriteStrINI("General", "ROUTER_1", GetWay.Trim(), DHCPINIFIlE);
        WriteStrINI("General", "DNS_1", DNS.Trim(), DHCPINIFIlE);
        WriteStrINI("Settings", "IPPOOL_1", StartIP.Trim(), DHCPINIFIlE);
        WriteStrINI("Settings", "InstallAsService", InstallAsService.Trim(), DHCPINIFIlE);
        //'================================================================================
        DataBaseInit();
        try
        {
            OleDbCommand  SqlCmd = new OleDbCommand("", DBconn);

            SqlCmd.CommandText = "select [DEVICE_ETH_MAC],[DEVICE_ETH_IP] from [WD_DeviceInfo]";
           OleDbDataReader sdr = SqlCmd.ExecuteReader();

            while( sdr.Read())
            {
                if(sdr.GetValue(0) != "" && sdr.GetValue(1) != "")
                {
                    DHCP_WriteIPAddressByMAC(sdr.GetValue(0).ToString(), sdr.GetValue(1).ToString());
                }
            }
            sdr.Close();
        }catch(Exception ex)
        {

        }
 
        DHCP_LockFileReadOnly(true);
        //'================================================================================
        }

        public clsDataBaseRW(string webPath  , bool InitFlag =  false)
        {
            try{
                 this.DHCPINIFIlE = webPath + @"\DHCP\dhcpsrv.ini";
                this.strDataBaseServerName = GetStrFromINI("DataBaseServer", "IPAddress", "", webPath + @"\Bin\Config.ini");
                this.strDataBaseServerUserName = GetStrFromINI("DataBaseServer", "UserName", "", webPath + @"\Bin\Config.ini");
                this.strDataBaseServerPassword = GetStrFromINI("DataBaseServer", "Password", "", webPath + @"\Bin\Config.ini");
                if(strDataBaseServerName =="")
                {
                    WriteStrINI("DataBaseServer", "IPAddress", "127.0.0.1", webPath + @"\Bin\Config.ini");
                    strDataBaseServerName = "127.0.0.1";
                }
                if(strDataBaseServerUserName == "")
                {
                    WriteStrINI("DataBaseServer", "UserName", "sa", webPath + @"\Bin\Config.ini");
                    strDataBaseServerUserName = "sa";
                }
                if(strDataBaseServerPassword == "")
                {
                    WriteStrINI("DataBaseServer", "Password", "Wutonghai113", webPath + @"\Bin\Config.ini");
                    strDataBaseServerPassword = "Wutonghai113";
                }
            }
            catch(Exception ex)
            {

            }  

              if(strDataBaseServerName == "")
              {
                strDataBaseServerName = "127.0.0.1";
              }
            if(strDataBaseServerUserName == "")
            {
                strDataBaseServerUserName = "sa";
            }
            if(strDataBaseServerPassword == "")
            {
                                strDataBaseServerPassword = "Wutonghai113";
            }

            if(InitFlag)
            {
                DataBaseInit();
            } 
        }

         
    //'''＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    //''' <summary>
    //''' 
    //''' </summary>
    //''' <remarks></remarks>

        public clsDataBaseRW(System.Web.UI.Page  Host , bool InitFlag = false)
        {
            try
            {
                if(Host != null)
                {
                     DHCPINIFIlE = Host.MapPath(HttpContext.Current.Request.ApplicationPath.ToString()) + @"\DHCP\dhcpsrv.ini";
                     strDataBaseServerName = GetStrFromINI("DataBaseServer", "IPAddress", "", Host.MapPath(HttpContext.Current.Request.ApplicationPath.ToString()) +@"\Bin\Config.ini");
                    strDataBaseServerUserName = GetStrFromINI("DataBaseServer", "UserName", "", Host.MapPath(HttpContext.Current.Request.ApplicationPath.ToString()) + @"\Bin\Config.ini");
                    strDataBaseServerPassword = GetStrFromINI("DataBaseServer", "Password", "", Host.MapPath(HttpContext.Current.Request.ApplicationPath.ToString()) + @"\Bin\Config.ini");
                    if(strDataBaseServerName == "")
                    {
                        WriteStrINI("DataBaseServer", "IPAddress", "127.0.0.1", Host.MapPath(HttpContext.Current.Request.ApplicationPath.ToString()) + @"\Bin\Config.ini");
                        strDataBaseServerName = "127.0.0.1";
                    }

                    if(strDataBaseServerUserName == "")
                    {
                        WriteStrINI("DataBaseServer", "UserName", "sa", Host.MapPath(HttpContext.Current.Request.ApplicationPath.ToString()) + @"\Bin\Config.ini");
                        strDataBaseServerUserName = "sa";
                    }
                    if(strDataBaseServerPassword == "")
                    {
                        WriteStrINI("DataBaseServer", "Password", "Wutonghai113", Host.MapPath(HttpContext.Current.Request.ApplicationPath.ToString()) + @"\Bin\Config.ini");
                        strDataBaseServerPassword = "Wutonghai113";
                    }
                 }
            }
            catch(Exception ex)
            {

            }

            if(strDataBaseServerName == "")
            {
                strDataBaseServerName = "127.0.0.1";
            }
            if(strDataBaseServerUserName == "")
            {
                strDataBaseServerUserName = "sa";
            }
            if(strDataBaseServerPassword == "")
            {
                strDataBaseServerPassword = "Wutonghai113";
            }

            if(InitFlag)
            {
                 DataBaseInit();
            } 

        }


    //'''＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    //''' <summary>
    //''' 获得网卡MAC
    //''' </summary>
    //''' <returns></returns>


        //public string GetNetMACAdd()
        //{
        //    string strMac = "";

        //    ManagementClass myMac = new ManagementClass("Win32_NetworkAdapterConfiguration");
        //    ManagementObjectCollection myMacConnection = new myMac.GetInstances();

        //    foreach( ManagementObject myObject in myMacConnection)
        //    {
        //        if(string.IsNullOrEmpty(myObject("MacAddress")))
        //        {
        //            ///'MAC不为空，取第一个MAC
        //        }
        //        else
        //        {
        //            strMac = myObject("MacAddress");

        //            break;
        //        }

        //    }

        //    return strMac;
        //}
    //Public Function GetNetMACAdd() As String
    //    Dim strMac As String = ""
    //    Dim myMac As ManagementClass = New ManagementClass("Win32_NetworkAdapterConfiguration")
    //    Dim myMacConnection As ManagementObjectCollection = myMac.GetInstances()

    //    For Each myObject As ManagementObject In myMacConnection
    //        If IsNothing(myObject("MacAddress")) = False Then 'MAC不为空，取第一个MAC
    //            strMac = myObject("MacAddress")
    //            Exit For
    //        End If
    //    Next
    //    Return strMac
    //End Function
    //'''＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    //'''<summary>  
    //'''打开登录数据库
    //'''</summary>  

        public bool DataBaseInit()
        {
            if(this.InitFlag)
            {
                return true;
            }

            string DBConnectString = "Provider=SQLOLEDB;Data Source=" + strDataBaseServerName + ",1433;Initial Catalog=WD_IPCALL_PREGNANT;User ID=" + strDataBaseServerUserName + ";Password=" + strDataBaseServerPassword + ";MultipleActiveResultSets=true;";

                 try{
                     DBconn.ConnectionString = DBConnectString;
                     DBconn.Open();

                     if(DBconn.State == ConnectionState.Open)
                     {
                         this.InitFlag = true;
                     }
                 }
            catch(Exception ex)
                 {

            }

            return this.InitFlag;
        }
 
    //'''＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    //'''<summary>  
    //'''关闭注销数据库
    //'''</summary>  

        public bool DataBaseClose()
        {
            if(this.InitFlag)
            {
                 DBconn.Close();
                this.InitFlag = false;
            }

            return true;
        }
 
    //''' <summary>
    //''' 
    //''' </summary>
    //''' <param name="strSql"></param>
    //''' <param name="n"></param>
    //''' <returns></returns>
    //''' <remarks></remarks>

        public bool DataBaseCmd(String strSql ,   Int32 n )
        {

            int LIntCnt ;
            bool res = false;

            if(DataBaseInit())
            {
                try
                {
                    OleDbCommand SqlCmd = new OleDbCommand("",DBconn);

                     SqlCmd.CommandText = strSql; //' "delete from " + TableName + " where [ID]=" + RowID.ToString()

                         if(n>=0)
                         {
                              n = Convert.ToInt32(SqlCmd.ExecuteScalar());
                              LIntCnt = n;

                         }
                         else

                         {
                             LIntCnt = SqlCmd.ExecuteNonQuery();
                         }

                         if (LIntCnt > 0)
                    {
                        res = true;
                    }
                    
                }
                catch(Exception ex)
                {

                }

                
            }
            return res;
        }

         

    //'''＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    //'''<summary>
    //'''搜索数据库中的指定表格指定字段值的记录，并从其中读取指定某条记录
    //'''</summary>
    //'''<param name="strSql">SQL查找语句</param>
    //'''<param name="RdLine">读取结果中的第几条记录</param>
    //'''<param name="Result">读缓存</param>

            public bool  DataBaseRead(string strSql ,UInt32  RdLine , ref string[] Result , bool  FiterFlag = false, string SpitStr = ":"  )
            {
                Result = new string[1];
                bool res = false;

                if(DataBaseInit())
                {
                    try
                    {
                        OleDbCommand SqlCmd = new OleDbCommand("", DBconn);

                         SqlCmd.CommandText = strSql;
                          OleDbDataReader sdr = SqlCmd.ExecuteReader();
                        UInt32 CurLine = 1;

                        while(sdr.Read())
                        {
                            if(RdLine == CurLine )
                            {
                                for(int j = 0;j<sdr.FieldCount - 1;j++)
                                {
                                    if(!FiterFlag)
                                    {
                                        Result[j] = "\"" + sdr.GetName(j).ToString() + "\"" + SpitStr + "\"" + sdr.GetValue(j).ToString() + "\"";
                                    }
                                    else
                                    {
                                        Result[j] = sdr.GetName(j).ToString() + SpitStr + sdr.GetValue(j).ToString();
                                    }
                                }

                                res = true;
                                break;
                            }

                            CurLine ++;
                        }

                        sdr.Close(); 
                    }
                    catch(Exception ex)
                    {

                    }
                }
                return res;
            }
        //    Try
        //        Dim SqlCmd As OleDbCommand = New OleDbCommand("", DBconn)
        //        SqlCmd.CommandText = strSql
        //        Dim sdr As OleDbDataReader = SqlCmd.ExecuteReader()
        //        Dim CurLine As UString = 1

        //        While sdr.Read = True
        //            If CurLine = RdLine Then
        //                For i = 0 To sdr.FieldCount - 1
        //                    If FiterFlag = False Then
        //                        Result(i) = """" + sdr.GetName(i).ToString + """" + SpitStr + """" + sdr.GetValue(i).ToString() + """"
        //                    Else
        //                        Result(i) = sdr.GetName(i).ToString + SpitStr + sdr.GetValue(i).ToString()
        //                    End If
        //                Next
        //                res = True
        //                ReDim Preserve Result(sdr.FieldCount - 1)
        //                Exit While
        //            End If
        //            CurLine += 1
        //        End While
        //        sdr.Close()
        //    Catch ex As Exception
        //    End Try
        //End If
        //Return res

        //    }


    //Public Function DataBaseRead(ByVal strSql As String, ByVal RdLine As UString, ByRef Result() As String, Optional FiterFlag As Boolean = False, Optional SpitStr As String = ":") As Boolean
    //    Dim res As Boolean = False
    //    Dim i As Stringeger
    //    '=======================
    //    If DataBaseInit() = True Then
    //        Try
    //            Dim SqlCmd As OleDbCommand = New OleDbCommand("", DBconn)
    //            SqlCmd.CommandText = strSql
    //            Dim sdr As OleDbDataReader = SqlCmd.ExecuteReader()
    //            Dim CurLine As UString = 1

    //            While sdr.Read = True
    //                If CurLine = RdLine Then
    //                    For i = 0 To sdr.FieldCount - 1
    //                        If FiterFlag = False Then
    //                            Result(i) = """" + sdr.GetName(i).ToString + """" + SpitStr + """" + sdr.GetValue(i).ToString() + """"
    //                        Else
    //                            Result(i) = sdr.GetName(i).ToString + SpitStr + sdr.GetValue(i).ToString()
    //                        End If
    //                    Next
    //                    res = True
    //                    ReDim Preserve Result(sdr.FieldCount - 1)
    //                    Exit While
    //                End If
    //                CurLine += 1
    //            End While
    //            sdr.Close()
    //        Catch ex As Exception
    //        End Try
    //    End If
    //    Return res
    //End Function


        public bool DataBaseLog(String LogType , string LogSource , string Logcontent )
        {
            bool Res = false;
 

            try
            {
                Res = DataBaseCmd("INSERT StringO WD_SysLogInfo([LOG_TYPE],[LOG_SOURCE],[LOG_CONTENT],[LOG_DATETIME]) VALUES ('" + LogType.ToString().Trim() + "','" + LogSource.Trim() + "','" + Logcontent.Trim() + "','" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "')",-1);
            }
            catch(Exception ex)
            {
                
            }
            return Res;

        }


    //    public bool DataBaseReadMul(String strSql , String[][] Result , bool FiterFlag = false , String SpitStr = ":")
    //    {
    //        bool Res = false;
    //        String i;
    //        UString j ;

    //        if(DataBaseInit())
    //        {
    //            try
    //            {
    //                OleDbCommand SqlCmd = new OleDbCommand("",DBconn);

    //                SqlCmd.CommandText = strSql;

    //                OleDbDataReader sdr = SqlCmd.ExecuteReader();

    //                i = 0;

    //                while(sdr.Read())
    //                {
    //                    if(i>)
    //                }
    //            }
    //        }
    //    }



    //Public Function DataBaseReadMul(ByVal strSql As String, ByRef Result(,) As String, Optional FiterFlag As Boolean = False, Optional SpitStr As String = ":") As Boolean
    //    Dim Res As Boolean = False
    //    Dim i As Stringeger
    //    Dim j As UString
    //    '=======================
    //    If DataBaseInit() = True Then
    //        Try
    //            Dim SqlCmd As OleDbCommand = New OleDbCommand("", DBconn)
    //            SqlCmd.CommandText = strSql
    //            Dim sdr As OleDbDataReader = SqlCmd.ExecuteReader()
    //            i = 0

    //            While sdr.Read = True
    //                If i > Result.GetUpperBound(1) Then ReDim Preserve Result(Result.GetUpperBound(0), i)
    //                For j = 0 To sdr.FieldCount - 1
    //                    If FiterFlag = False Then
    //                        Result(j, i) = """" + sdr.GetName(j).ToString + """" + SpitStr + """" + sdr.GetValue(j).ToString() + """"
    //                    Else
    //                        Result(j, i) = sdr.GetName(j).ToString + SpitStr + sdr.GetValue(j).ToString()
    //                    End If
    //                Next
    //                i += 1
    //            End While
    //            sdr.Close()
    //            Res = True
    //        Catch ex As Exception
    //        End Try
    //    End If
    //    Return Res
    //End Function


    }
}