在X64系统中开发的Windows服务，如何连接数据库？

 

在使用ODBC连接数据库时，会出现如下问题：

1. 如果为Any CPU或者X64平台，则出现：The specified DSN contains an architecture mismatch between the Driver and Application
查阅资料，使用Wow64下的ODBCAD32.exe来添加系统DSN可解决问题，但实践下来无任何效果

2.如果为x86平台，则会出现：找不到数据源或者驱动程序

在使用JET OLEDB连接数据库时，出现问题如何解决：

1. 连接带有密码的ACCESS数据库时出现“无法启动应用程序。工作组信息文件丢失，或是已被其它用户以独占方式打开”的解决方法：

此问题是由数据库的连接串引起的，可用下面的串连接即可
"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=true;Data Source=E:\data1.mdb;Jet OLEDB:Database Password=1111 "

2. The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine 
未在本地计算机上注册“Microsoft.Jet.OLEDB.4.0”提供程序。

将系统编译目标改为X86即可，原因是未安装64bit的JET OLEDB驱动程序

在应用程序中安装、卸载、启动和停止系统服务

using System;
using System.Collections;
using System.Linq;
using System.Text;
using System.ServiceProcess;

namespace TncsConfig
{
    class ServiceManager
    {
        private String serviceName;
        private String filepath;

        public ServiceManager(string serviceName,string filepath){
            this.serviceName = serviceName;
            this.filepath = filepath;
        }
       
        public void InstallService(IDictionary stateSaver)
        {
            try
            {
                System.ServiceProcess.ServiceController service = new System.ServiceProcess.ServiceController(serviceName);
                if (!ServiceIsExisted())
                {
                    //Install Service
                    string[] commandLineOptions = new string[1] { "/LogFile=TncsDataTransferService.log" };
                    System.Configuration.Install.AssemblyInstaller myAssemblyInstaller = new System.Configuration.Install.AssemblyInstaller(filepath,commandLineOptions);
                    myAssemblyInstaller.UseNewContext = true;
                    myAssemblyInstaller.Install(stateSaver);
                    myAssemblyInstaller.Commit(stateSaver);
                    myAssemblyInstaller.Dispose();
                    //--Start Service
                    service.Start();
                    service.WaitForStatus(ServiceControllerStatus.Running);
                }
                else
                {
                    if (service.Status != System.ServiceProcess.ServiceControllerStatus.Running && service.Status != System.ServiceProcess.ServiceControllerStatus.StartPending)
                    {
                        service.Start();
                        service.WaitForStatus(ServiceControllerStatus.Running);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Install Service Error: " + ex.Message);
            }
        }

        public void UnInstallService()
        {
            try
            {
                if (ServiceIsExisted())
                {
                    //UnInstall Service
                    System.Configuration.Install.AssemblyInstaller myAssemblyInstaller = new System.Configuration.Install.AssemblyInstaller();
                    myAssemblyInstaller.UseNewContext = true;
                    myAssemblyInstaller.Path = filepath;
                    myAssemblyInstaller.Uninstall(null);
                    myAssemblyInstaller.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Uninstall Service Error: " + ex.Message);
            }
        }

        public bool ServiceIsExisted()
        {
            ServiceController[] services = ServiceController.GetServices();
            foreach (ServiceController s in services)
            {
                if (s.ServiceName == serviceName)
                {
                    return true;
                }
            }
            return false;
        }

        public Boolean ServiceIsRunning(){
            if (ServiceIsExisted())
            {
                System.ServiceProcess.ServiceController service = new System.ServiceProcess.ServiceController(serviceName);
                if (service.Status == System.ServiceProcess.ServiceControllerStatus.Running)
                    return true;
                else
                    return false;
            }
            return false;
        }

        public void StartService()
        {
            if (ServiceIsExisted())
            {
                System.ServiceProcess.ServiceController service = new System.ServiceProcess.ServiceController(serviceName);
                if (service.Status != System.ServiceProcess.ServiceControllerStatus.Running && service.Status != System.ServiceProcess.ServiceControllerStatus.StartPending)
                {
                    service.Start();
                    service.WaitForStatus(ServiceControllerStatus.Running);
                }
            }
        }

        public void StopService()
        {
            if (ServiceIsExisted())
            {
                System.ServiceProcess.ServiceController service = new System.ServiceProcess.ServiceController(serviceName);
                if (service.Status == System.ServiceProcess.ServiceControllerStatus.Running)
                {
                    service.Stop();
                    service.WaitForStatus(ServiceControllerStatus.Stopped);
                }
            }
        }
    }
}

Win7 X64中使用控制面板中的设置数据源无法读到系统ODBC数据源

 

应该使用 C:\Windows\SysWOW64\odbcad32.exe 来配置ODBC数据源

在Win7 X64中使用C#获取系统和用户ODBC数据源

 

需要注意的是，系统ODBC数据源保存在 HKLM\Software\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources键下

using System;
using System.Collections.Generic;
using System.Text;

namespace TncsConfig
{
    /// <summary>
    /// The types of data sources that can be set.
    /// </summary>
    public enum DataSourceType { System, User }

    /// <summary>
    /// Provides methods and tools to manage the Odbc data sources on the machine.
    /// </summary>
    public class OdbcDataSourceManager
    {
        // Returns a list of data source names from the local machine.
        public System.Collections.SortedList GetAllDataSourceNames()
        {
            // Get the list of user DSN's first.
            System.Collections.SortedList dsnList = GetUserDataSourceNames();

            // Get list of System DSN's and add them to the first list.
            System.Collections.SortedList systemDsnList = GetSystemDataSourceNames();
            for (int i = 0; i < systemDsnList.Count; i++)
            {
                string sName = systemDsnList.GetKey(i) as string;
                DataSourceType type = (DataSourceType)systemDsnList.GetByIndex(i);
                try
                {
                    // This dsn to the master list
                    dsnList.Add(sName, type);
                }
                catch
                {
                    // An exception can be thrown if the key being added is a duplicate so
                    // we just catch it here and have to ignore it.
                }
            }

            return dsnList;
        }

        /// <summary>
        /// Gets all System data source names for the local machine.
        /// </summary>
        public System.Collections.SortedList GetSystemDataSourceNames()
        {
            System.Collections.SortedList dsnList = new System.Collections.SortedList();

            // get system dsn's
            Microsoft.Win32.RegistryKey reg = (Microsoft.Win32.Registry.LocalMachine).OpenSubKey("Software");
            if (reg != null)
            {
                reg = reg.OpenSubKey("ODBC");
                if (reg != null)
                {
                    reg = reg.OpenSubKey("ODBC.INI");
                    if (reg != null)
                    {
                        reg = reg.OpenSubKey("ODBC Data Sources");
                        if (reg != null)
                        {
                            // Get all DSN entries defined in DSN_LOC_IN_REGISTRY.
                            foreach (string sName in reg.GetValueNames())
                            {
                                dsnList.Add(sName, DataSourceType.System);
                            }
                        }
                        try
                        {
                            reg.Close();
                        }
                        catch { /* ignore this exception if we couldn't close */ }
                    }
                }
            }
            reg = (Microsoft.Win32.Registry.LocalMachine).OpenSubKey("Software");
            if(reg!=null){
                reg = reg.OpenSubKey("Wow6432Node");
                if(reg!=null){
                    reg = reg.OpenSubKey("ODBC");
                    if (reg != null)
                    {
                        reg = reg.OpenSubKey("ODBC.INI");
                        if (reg != null)
                        {
                            reg = reg.OpenSubKey("ODBC Data Sources");
                            if (reg != null)
                            {
                                // Get all DSN entries defined in DSN_LOC_IN_REGISTRY.
                                foreach (string sName in reg.GetValueNames())
                                {
                                    dsnList.Add(sName, DataSourceType.System);
                                }
                            }
                            try
                            {
                                reg.Close();
                            }
                            catch { /* ignore this exception if we couldn't close */ }
                        }
                    }
                }
            }

            return dsnList;
        }

        /// <summary>
        /// Gets all User data source names for the local machine.
        /// </summary>
        public System.Collections.SortedList GetUserDataSourceNames()
        {
            System.Collections.SortedList dsnList = new System.Collections.SortedList();

            // get user dsn's
            Microsoft.Win32.RegistryKey reg = (Microsoft.Win32.Registry.CurrentUser).OpenSubKey("Software");
            if (reg != null)
            {
                reg = reg.OpenSubKey("ODBC");
                if (reg != null)
                {
                    reg = reg.OpenSubKey("ODBC.INI");
                    if (reg != null)
                    {
                        reg = reg.OpenSubKey("ODBC Data Sources");
                        if (reg != null)
                        {
                            // Get all DSN entries defined in DSN_LOC_IN_REGISTRY.
                            foreach (string sName in reg.GetValueNames())
                            {
                                dsnList.Add(sName, DataSourceType.User);
                            }
                        }
                        try
                        {
                            reg.Close();
                        }
                        catch { /* ignore this exception if we couldn't close */ }
                    }
                }
            }

            return dsnList;
        }
    }
}

删除重复数据的存储过程

CREATE PROCEDURE deldup

AS
    declare @bid integer,@bbid integer,@bname varchar(200)
    declare name_cursor cursor for  SELECT distinct a.bname FROM BuyerTable a INNER JOIN BuyerTable b ON a.Bname = b.Bname AND a.BuyerID <> b.BuyerID
   
    open name_cursor

    FETCH   NEXT   FROM   name_cursor  INTO   @bname

    WHILE   @@FETCH_STATUS   =   0
    BEGIN
        select @bid=min(buyerid) from buyertable where bname=@bname
        declare id_cursor cursor for select buyerid from buyertable where bname=@bname
        open id_cursor
        fetch next from id_cursor into @bbid
        while @@fetch_status=0
        begin
            update ProductionSell set buyerid=@bid where buyerid=@bbid
            fetch next from id_cursor into @bbid
        end
        close id_cursor
        deallocate id_cursor

        update buyertable set buyerid=@bid where bname=@bname
       
        FETCH   NEXT   FROM   name_cursor  INTO   @bname
    end
    close name_cursor
    deallocate name_cursor
   
GO