It is often necessary to import data from and export data to a SQL Server database. There are multiple times at which this import and export may be useful. For example, when a form opens on a Windows device, it may be useful to query a data source for picklist items. A similar use case is through the use of the ServerContacted API in iOS/Android targeted forms. Many forms may also wish to export their data to a SQL Server as a customized data export.

The examples below (and attached) are designed to show the concept of how to connect to a SQL server for data import/export through .NET script code.

First, let's examine how to query data. In the attached example, a field is used as a filter to query from a single table as so:  

    Function ImportFromSQL() As Boolean
        Using myConnection As New System.Data.SqlClient.SqlConnection(DBCONNECTION_STRING)
            ' Open a DB connection
            Try
                If (myConnection Is Nothing) Then
                    _SQLStatus.Value = "Failed to open database."
                    Return False
                End If
                myConnection.Open()
            Catch ex As System.Exception
                _SQLStatus.Value = ex.Message
                Return False
            End Try

            ' Construct and run the query
            Try
                Dim strSQL As String = String.Format("SELECT EmployeeID, FirstName, LastName FROM Employees WHERE LastName LIKE '{0}%'", _FirstLetter.Value)
                Using myCmd As New System.Data.SqlClient.SqlCommand(strSQL, myConnection)
                    Using xreader As System.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()

                        ' Read just the first record -- simple example
                        If (Not xreader.HasRows) Then
                            _FirstName.Value = ""
                            _LastName.Value = ""
                            _EmployeeID.Value = ""
                            xreader.Close()
                            _SQLStatus.Value = "No records found"
                            Return False
                        End If
                        xreader.Read()

                        ' Populate Mi-Forms fields based on the query
                        _EmployeeID.Value = xreader(0).ToString()
                        _FirstName.Value = xreader(1).ToString()
                        _LastName.Value = xreader(2).ToString()
                        xreader.Close()

                        ' Indicate that we were successful
                        _SQLStatus.Value = "Success quering database"
                        Return True
                    End Using
                End Using
            Catch ex As System.Exception
                _SQLStatus.Value = ex.Message
                Return False
            Finally
                myConnection.Close()
            End Try
        End Using

    End Function

    Function ExportToSQL() As Boolean
        Using myConnection = New System.Data.SqlClient.SqlConnection(DBCONNECTION_STRING)
            ' Open a DB connection
            Try
                If (myConnection Is Nothing) Then
                    _SQLStatus.Value = "Failed to open database"
                    Return False
                End If
                myConnection.Open()
            Catch ex As System.Exception
                _SQLStatus.Value = ex.Message
                Return False
            End Try

            Dim addRecord As Boolean = True

            ' First query to see if this ID exists. If it does, then we'll update, otherwise add
            Try
                Dim strSQL As String = String.Format("SELECT EmployeeID FROM Employees WHERE EmployeeID = '{0}'", _EmployeeID.Value)
                Using myCmd As New System.Data.SqlClient.SqlCommand(strSQL, myConnection)
                    Using xreader As System.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()
                        If xreader.HasRows Then
                            addRecord = False
                        End If
                        xreader.Close()
                    End Using
                End Using
            Catch ex As Exception
                _SQLStatus.Value = ex.Message
                Return False
            End Try

            ' Construct and run the update
            Try
                Dim strSQL As String
                If addRecord Then
                    strSQL = String.Format("INSERT INTO Employees (FirstName, LastName, EmployeeID) VALUES ('{0}', '{1}', '{2}')", _FirstName.Value, _LastName.Value, _EmployeeID.Value)
                Else
                    strSQL = String.Format("UPDATE Employees SET FirstName='{0}', LastName='{1}' WHERE EmployeeID={2}", _FirstName.Value, _LastName.Value, _EmployeeID.Value)
                End If
                Using myCmd As New System.Data.SqlClient.SqlCommand(strSQL, myConnection)
                    Dim nRows As Integer = myCmd.ExecuteNonQuery()
                    If addRecord Then
                        _SQLStatus.Value = "Success inserting into database"
                    Else
                        _SQLStatus.Value = "Success updating database"
                    End If
                    Return True
                End Using
            Catch ex As System.Exception
                _SQLStatus.Value = ex.Message
                Return False
            Finally
                myConnection.Close()
            End Try
        End Using

    End Function