Solved

Dynamic sql server selections at runtime with C# and MSSQL

Posted on 2004-11-01
391 Views
Last Modified: 2011-10-03
Hi, I can't seem to find any information on how to setup a runtime list of sql server instances for which the user can select which server and database they want to log into. My C# app will be used with a network environment where there could be 1-20 different sql servers and databases they might need to log into. We're using MSSQL and my app is a windows app. I want to have a form pop up when the app is ran which would have a datgrid or list of all the servers available on the network to choose from. It does not matter to me if all of this is stored in the config file or whatever as long as I can provide this. I'm using oledb connections. So they would click on a listbox item and then say press a button to connect to that SQL server and database.

Can someone please help me witht this or provide a link to a sample app that does this. I would really appreciate it. I'm new to C# and .net so could you please provide specific examples. I can use all the help I can get.

Thanks so much!
JAmes
0
Question by:jhredd
    4 Comments
     
    LVL 8

    Accepted Solution

    by:
    see this url: http://www.codeproject.com/cs/database/LocatingSql.asp

    Good Luck,
    VINHNL

    This my code:

          /// <summary>
          /// Wrapper class for all Win32 API calls and structures
          /// </summary>
          internal class Win32API
          {
                #region Win32 API Interfaces
                [DllImport( "netapi32.dll", EntryPoint = "NetApiBufferFree" )]
                internal static extern void NetApiBufferFree(IntPtr bufptr);

                [DllImport( "netapi32.dll", EntryPoint = "NetServerEnum" )]
                internal static extern uint NetServerEnum(
                      IntPtr ServerName,
                      uint level,
                      ref IntPtr siPtr,
                      uint prefmaxlen,
                      ref uint entriesread,
                      ref uint totalentries,
                      uint servertype,
                      [MarshalAs(UnmanagedType.LPWStr)] string domain,
                      IntPtr resumeHandle);

                /// <summary>
                /// Windows NT/2000/XP Only
                /// </summary>
                [DllImport( "netapi32.dll", EntryPoint = "NetServerGetInfo" )]
                internal static extern uint NetServerGetInfo(
                      [MarshalAs(UnmanagedType.LPWStr)] string ServerName,
                      int level,
                      ref IntPtr buffPtr);

                [StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto)]
                      internal struct SERVER_INFO_101
                {
                      public int dwPlatformID;
                      public IntPtr lpszServerName;
                      public int dwVersionMajor;
                      public int dwVersionMinor;
                      public int dwType;
                      public IntPtr lpszComment;
                }
                #endregion
          }
          /// <summary>
          /// The possible flag values for Server Type (see lmserver.h).
          /// </summary>
          [Flags]
          internal enum ServerType : long
          {
                /// <summary>
                /// Opposite of All.  No servers will be returned.
                /// </summary>
                None = 0x00000000,
                /// <summary>
                /// All workstations
                /// </summary>
                Workstation = 0x00000001,
                /// <summary>
                /// All servers
                /// </summary>
                Server = 0x00000002,
                /// <summary>
                /// Any server running with Microsoft SQL Server
                /// </summary>
                SQLServer = 0x00000004,
                /// <summary>
                /// Primary domain controller
                /// </summary>
                DomainController = 0x00000008,
                /// <summary>
                /// Backup domain controller
                /// </summary>
                DomainBackupController = 0x00000010,
                /// <summary>
                /// Server running the Timesource service
                /// </summary>
                TimeSource = 0x00000020,
                /// <summary>
                /// Apple File Protocol servers
                /// </summary>
                AFP = 0x00000040,
                /// <summary>
                /// Novell servers
                /// </summary>
                Novell = 0x00000080,
                /// <summary>
                /// LAN Manager 2.x domain member
                /// </summary>
                DomainMember = 0x00000100,
                /// <summary>
                /// Server sharing print queue
                /// </summary>
                PrintQueue = 0x00000200,
                /// <summary>
                /// Server running dial-in service
                /// </summary>
                Dialin = 0x00000400,
                /// <summary>
                /// Xenix server
                /// </summary>
                Xenix = 0x00000800,
                /// <summary>
                /// Unix servers?
                /// </summary>
                Unix = Xenix,
                /// <summary>
                /// Windows NT workstation or server
                /// </summary>
                NT = 0x00001000,
                /// <summary>
                /// Server running Windows for Workgroups
                /// </summary>
                WFW = 0x00002000,
                /// <summary>
                /// Microsoft File and Print for NetWare
                /// </summary>
                MFPN = 0x00004000,
                /// <summary>
                /// Server that is not a domain controller
                /// </summary>
                NTServer = 0x00008000,
                /// <summary>
                /// Server that can run the browser service
                /// </summary>
                PotentialBrowser = 0x00010000,
                /// <summary>
                /// Server running a browser service as backup
                /// </summary>
                BackupBrowser = 0x00020000,
                /// <summary>
                /// Server running the master browser service
                /// </summary>
                MasterBrowser = 0x00040000,
                /// <summary>
                /// Server running the domain master browser
                /// </summary>
                DomainMaster = 0x00080000,
                /// <summary>
                /// Not documented on MSDN? Help Microsoft!
                /// </summary>
                OSF = 0x00100000,
                /// <summary>
                /// Running VMS
                /// </summary>
                VMS = 0x00200000,
                /// <summary>
                /// Windows 95 or later
                /// </summary>
                Windows = 0x00400000,
                /// <summary>
                /// Distributed File System??
                /// </summary>
                DFS = 0x00800000,
                /// <summary>
                /// Not documented on MSDN? Help Microsoft!
                /// </summary>
                ClusterNT = 0x01000000,
                /// <summary>
                /// Terminal Server
                /// </summary>
                TerminalServer = 0x02000000,
                /// <summary>
                /// Not documented on MSDN? Help Microsoft!
                /// </summary>
                DCE = 0x10000000,
                /// <summary>
                /// Not documented on MSDN? Help Microsoft!
                /// </summary>
                AlternateXPort =0x20000000,
                /// <summary>
                /// Servers maintained by the browser
                /// </summary>
                ListOnly = 0x40000000,
                /// <summary>
                /// List Domains
                /// </summary>
                DomainEnum = 0x80000000,
                /// <summary>
                /// All servers
                /// </summary>
                All = 0xFFFFFFFF
          }

          /// <summary>
          /// Enumerates over a set of servers returning the server's name.
          /// </summary>
          internal class ServerEnumerator : IEnumerator
          {
                #region Instance Variables
                /// <summary>
                /// Memory buffer pointer returned by NetServerEnum
                /// </summary>
                protected IntPtr serverInfoPtr;

                /// <summary>
                /// The current item number
                /// </summary>
                protected int currentItem;

                /// <summary>
                /// Number of items in the collection
                /// </summary>
                protected uint itemCount;

                /// <summary>
                /// The name of the machine returned by Current
                /// </summary>
                protected string currentServerName;

                /// <summary>
                /// Save the size of the SERVER_INFO_101 structure.
                /// This allows us to only have a single time we need
                /// to use 'unsafe' code.
                /// </summary>
                protected static int SERVER_INFO_101_SIZE;
                #endregion

                static ServerEnumerator()
                {
                      SERVER_INFO_101_SIZE = Marshal.SizeOf(typeof(Win32API.SERVER_INFO_101));
                }

                /// <summary>
                /// Returns the current server/machine/domain name
                /// </summary>
                public object Current
                {
                      get { return currentServerName; }
                }

                /// <summary>
                /// Moves to the next server/machine/domain
                /// </summary>
                /// <returns></returns>
                public bool MoveNext()
                {
                      bool result = false;

                      if ( ++currentItem < itemCount )
                      {
                            int newOffset = serverInfoPtr.ToInt32() + SERVER_INFO_101_SIZE * currentItem;
                            Win32API.SERVER_INFO_101 si = (Win32API.SERVER_INFO_101) Marshal.PtrToStructure(new IntPtr(newOffset), typeof(Win32API.SERVER_INFO_101));
                            currentServerName = Marshal.PtrToStringAuto(si.lpszServerName);
                            result = true;
                      }
                      return result;
                }

                /// <summary>
                /// Resets the enumeration back to the beginning.
                /// </summary>
                public void Reset()
                {
                      currentItem = -1;
                      currentServerName = null;
                }

                /// <summary>
                ///
                /// </summary>
                /// <param name="serverType"></param>
                protected internal ServerEnumerator(ServerType serverType) : this(serverType, null)
                {
                }

                /// <summary>
                ///
                /// </summary>
                /// <param name="serverType"></param>
                /// <param name="domainName"></param>
                protected internal ServerEnumerator(ServerType serverType, string domainName)
                {
                      uint level = 101, prefmaxlen = 0xFFFFFFFF, entriesread = 0, totalentries = 0;

                      Reset();
                      serverInfoPtr = IntPtr.Zero;

                      uint nRes = Win32API.NetServerEnum(
                            IntPtr.Zero,            // Server Name: Reserved; must be NULL.
                            level,                        // Return server names, types, and associated software. The bufptr parameter points to an array of SERVER_INFO_101 structures.
                            ref serverInfoPtr,      // Pointer to the buffer that receives the data.
                            prefmaxlen,                  // Specifies the preferred maximum length of returned data, in bytes.
                            ref entriesread,      // count of elements actually enumerated.
                            ref totalentries,      // total number of visible servers and workstations on the network
                            (uint) serverType,      // value that filters the server entries to return from the enumeration
                            domainName,                  // Pointer to a constant string that specifies the name of the domain for which a list of servers is to be returned.
                            IntPtr.Zero);            // Reserved; must be set to zero.

                      itemCount = entriesread;
                }

                /// <summary>
                ///
                /// </summary>
                ~ServerEnumerator()
                {
                      if ( ! serverInfoPtr.Equals( IntPtr.Zero ) )
                      {
                            Win32API.NetApiBufferFree(serverInfoPtr);
                            serverInfoPtr = IntPtr.Zero;            
                      }
                }
          }


          /// <summary>
          /// Class that encapsulates the Win32 API call of NetServerEnum
          /// </summary>
          internal class Servers : IEnumerable
          {
                private ServerType serverType;
                private string domainName;

                #region Win32 API Interfaces
                #endregion

                #region Properties
                /// <summary>
                /// Gets/Sets the server type
                /// </summary>
                public ServerType Type
                {
                      get { return serverType; }
                      set { serverType = value; }
                }
                /// <summary>
                ///
                /// </summary>
                public string DomainName
                {
                      get { return domainName; }
                      set      { domainName = value; }
                }
                #endregion

                /// <summary>
                ///
                /// </summary>
                public Servers()
                {
                      Type = ServerType.None;
                }

                /// <summary>
                /// Specifies a value that filters the server entries to return from the enumeration
                /// </summary>
                /// <param name="aServerType"></param>
                public Servers(ServerType aServerType)
                {
                      Type = aServerType;
                }

                /// <summary>
                ///
                /// </summary>
                /// <returns>IEnumerator</returns>
                public IEnumerator GetEnumerator()
                {
                      return new ServerEnumerator(serverType, domainName);                
                }

                /// <summary>
                /// Returns the server type of the named server.
                /// </summary>
                /// <param name="serverName"></param>
                /// <returns></returns>
                public static ServerType GetServerType(string serverName)
                {
                      ServerType result = ServerType.None;

                      IntPtr serverInfoPtr = IntPtr.Zero;
                      uint rc = Win32API.NetServerGetInfo( serverName, 101, ref serverInfoPtr );
                      if ( rc != 0 )
                      {
                            Win32API.SERVER_INFO_101 si = (Win32API.SERVER_INFO_101) Marshal.PtrToStructure(serverInfoPtr, typeof(Win32API.SERVER_INFO_101));
                            result = (ServerType) si.dwType;

                            Win32API.NetApiBufferFree(serverInfoPtr);
                            serverInfoPtr = IntPtr.Zero;            
                      }
                      return result;
                }
          }


    Sample:

                private void LoadServerList()
                {
                      servers.Type = ServerType.SQLServer;
                      int dbcount = 0;
                      // dem so db
                      foreach (String name in servers)
                            dbcount ++;
                      if(dbcount > 0)
                      {
                            string[] dblist = new string[dbcount];
                            int i = 0;
                            foreach (String name in servers)
                            {
                                  dblist[i] = name;
                                  i++;
                            }
                            Console.Writeline(dblist);
                      }
                }

                private void LoadDatabaseList()
                {
                      // dung co dang load db
                      try
                      {
                            string ConnectionString = "server = myserver; database =  master; user id = sa; password = sa;";
                            SqlConnection Connection = new SqlConnection(ConnectionString);
                            Connection.Open();
                            DataSet dset = MTSDDatabase.ExecuteDataset(Connection,CommandType.Text,"SELECT name FROM sysdatabases;");
                            Connection.Close();
                            if(dset.Tables[0].Rows.Count >0)
                            {
                                  string[] dblist = new string[dset.Tables[0].Rows.Count];
                                  for(int i = 0;i<dset.Tables[0].Rows.Count;i++)
                                        dblist[i] = (string)dset.Tables[0].Rows[i]["name"];
                                  Console.AddDB(dblist);
                            }
                      }
                      catch{}
                      // bo co dang load db
                }
    0
     

    Author Comment

    by:jhredd
    I will try this and let you know if it's what I need.

    Thanks So Much,
    James
    0
     

    Author Comment

    by:jhredd
    Hi, this code is great but not what I am looking to do. I just want to populate a datagrid with manually entered server and db names like:

    Server1          DB
    Ultra1         mydb
    XTar           mydb2

    Be able to add multiple servers and have this form popup before the main app loads. The user would then select one of the server/DB's in the list, have the code convert the selection into a connection string and connect to that server/db. I'm using oledb connections.

    Can someone help me with this? Thanks in advance.

    James
    0
     

    Author Comment

    by:jhredd
    Thanks a lot. I'm sorry I did not get back here sooner but I have been buried with another project. I appreciate your help. I don't have time to implement this now but this should work.

    Many Thanks,
    JR
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Article by: Najam
    Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
    Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
    This video Micro Tutorial is the second in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles a…
    Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now