Category Archives: DAO

List all SQL Instances on a local machine in C#


This is just another SQL C# articles which let you learn how to make a list of names of SQL instance available on your Microsoft SQL Server using C# code.

This is made possible using Registry entries.

     private IEnumerable<string> ListLocalSqlInstances(RegistryKey hive)
        {
            const string keyName = @"Software\Microsoft\Microsoft SQL Server";
            const string valueName = "InstalledInstances";
            const string defaultName = "MSSQLSERVER";

            using (var key = hive.OpenSubKey(keyName, false))
            {
                if (key == null) return Enumerable.Empty<string>();

                var value = key.GetValue(valueName) as string[];
                if (value == null) return Enumerable.Empty<string>();

                for (int index = 0; index < value.Length; index++)
                {
                    if (string.Equals(value[index], defaultName, StringComparison.OrdinalIgnoreCase))
                    {
                        value[index] = ".";
                    }
                    else
                    {
                        value[index] = @".\" + value[index];
                    }
                }

                return value;
            }
        }


 public IEnumerable<string> ListLocalSqlInstances()
        {
            if (Environment.Is64BitOperatingSystem)
            {
                using (var hive = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry64))
                {
                    foreach (string item in ListLocalSqlInstances(hive))
                    {
                        yield return item;
                    }
                }

                using (var hive = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry32))
                {
                    foreach (string item in ListLocalSqlInstances(hive))
                    {
                        yield return item;
                    }
                }
            }
            else
            {
                foreach (string item in ListLocalSqlInstances(Registry.LocalMachine))
                {
                    yield return item;
                }
            }
        }

How to use the function

The function has two part, one with argument which dig for registry element another will extract the information.

 txt_instance.ItemsSource = SqlHelper.ListLocalSqlInstances();

The a above line will serve the list of string ( instance names ) and serve as itemsource for a combo box control.

How to Check for a database on SQL Server using C#


This is just another SQL C# articles which let you learn how to check for a database existence on SQL Server.

All you have to do query names in sys.databases table and make sure it is available to use. You can create a function for the purpose.

public static bool IsDBExist(string server, string db)
        {
            List<string> list = new List<string>();

            // Open connection to the database
            string conString = null; ;
            
                conString = "server=" + server + ";uid=sa;";
           
            bool has = false;
            try
            {
                using (SqlConnection con = new SqlConnection(conString))
                {
                    if (con.State == ConnectionState.Open) con.Close();
                    con.Open();


                    // Set up a command with the given query and associate
                    // this with the current connection.
                    using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases where name='" + db + "'", con))
                    {
                        using (IDataReader dr = cmd.ExecuteReader())
                        {

                            while (dr.Read())
                            {
                                list.Add(dr[0].ToString());
                            }
                            if (list.Count > 0) has = true;
                        }
                    }
                    con.Close();
                }
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message.ToString());
            }
            return has;

        }

Create Table at run time using DAO connection in Visual Basic 6.0


Using VB6 database connection (DAO) connection we can not only manipulate data from the existing database but also dynamically create new table according to the necessity of the program. This can be achieved using CreateTableDef method of record set object.

Make sure your are included the appropriate references from References window.

Create a new connection 

Dim MedicalShop as Database
Set MedicalShop=opendatabse(GetAppPath()& “shop.mdb”)

Create the Sub for create table

The the sub will utilizes the Tabledef and Fields objects and by using the CreateTabledef command it will create the new table.

Lets add the body of the procedure as follows

Public Sub CreateTable(tbl As String, fl() As String)
Dim tdf As TableDef
Dim fld As Field
Dim f, I
I = 0
Set tdf = MedicalShop.CreateTableDef(tbl)
For Each f In fl
 If f Empty Then
  Set fld = tdf.CreateField(f, dbText)
  tdf.Fields.Append fld
  I = I + 1
 End If
Next
MedicalShop.TableDefs.Append tdf
MedicalShop.Recordsets.Refresh
End Sub

By default the field type is DbText, you can specify each  with different type by passing an addition argument list or a two dimensional array.

Call the CreateTable method

Dim f(3) as String
f(0)=”Entryno”
f(1)=”Name”
f(2)=”amount”
f(3)=”Narration”

Now call the procedure

Call CreateTable(“salesentry”,f)

Will create a new table with fields and having Text field type.

How to add new fields to Access table in VB6


Let me show how to add a new field into an existing access database Table using Visual Basic 6.0.Using the tableDef and Filed object you can create new fields from within the code . 

Firstly, you need to create Database and Recordset object, make sure the DAO access object library is enabled/added to the project.

Create the UpdateTableField Sub

Sub UpdateTableField(tb As String, new_fld As String)Dim CHECK As Boolean
CHECK = False
Dim tbl As TableDef
Dim fld As Field
Dim strName As String
Set tbl = DB.TableDefs(tb)
  For Each fld In tbl.Fields
    If fld.Name = new_fld Then
      CHECK = True
    End If
  Next
If CHECK = False Then
   Set RS = Nothing
   With tbl
   Set fld = .CreateField(new_fld, dbText)
   fld.DefaultValue = “”
   .Fields.Append fld
  MsgBox “new Field Created !, check table”
  End With
Else
  MsgBox “Field already exist !”End If

The ‘TableDefs’ method will fetch the table schema from the database which hold the field information and then fire the checking for table field. dbText hold the default data type. 

Use the sub

Dim DB As Database
Dim RS As Recordset
Private Sub Command1_Click()
Call UpdateTableField(“Table1”, “RoomID2”)
End Sub
Private Sub Form_Initialize()
Set DB = OpenDatabase(“F:\Manoj\Code\DB-AUTO-FIELD-ACCES\db3.mdb”)
End Sub

A new field “RoomID2” will be created in the ‘Table1‘ Table.
Download the Source Code