MS SQL Library

[citywest]

MSSQLRemote is a basic library which allows you to access a corporate MS SQL Server. Why would you want to do this?, well in my case it's for wireless hand held devices used in a warehouse environment.

Warning This is not currently in production and as it is my first library there are likely bugs and probably there are better ways of doing things. In particular the BeginTransaction and related functions are not fully tested.

Usage as follows:

LastError - Returns the last exception as string

New - Sets up the Connection and returns True or False
Open(ConnString) - Opens a database where ConnString is something like "Persist Security Info=False;Integrated Security=False;Server=CWS-MARK, 1433; initial catalog=ArrowDemo; user id=sa; password=pass;" Returns True or False depending on success.

Close - Closes the current connection

ExecuteNonQuery(QueryString) - Returns True or False. QueryString like "Update Invoices Set StatusFlag = 'X' Where Reference = '1234'"

ExecuteQuery(QueryString) - Returns True or False. QueryString like "Select * From Invoices"

After ExecuteQuery the following are valid:

• Data - Returns True or False. Step Through result set one row at at a time ie. (Do While Data - i = ReadRow - Loop)
  
• Read(FieldName) - Returns single field as string from current row
• ReadRow - Returns string array of current row

OR

• BuildResultSet - Will read entire result set and return True or False if data.
• GetResultSet - Returns entire result set as Array

Fields - Returns number of fields in current result set as integer
Rows - Return number of rows in current result set (only after BuildResultSet)

FieldInfo - Returns Array of field name and field type for current result set

BeginTransaction - Returns True or False
Commit - returns True or False
RollBack - returns True or False
TransactionDispose - returns True or False

Use BuildResultSet and GetResultSet with care as they use Array Redim. Large record sets may/will cause performance problems. Unfortunately CF and also basic4pcc do not appear to support multi dimensional arraylists? If any has any further information or ideas it would be most appreciated.

Requires System.Data.SqlClient.Dll. Tested on SQL Server 2005 should work on 2000 and Express.

Cheers

Mark S

[agraham]

Originally Posted by citywest

Unfortunately CF and also basic4pcc do not appear to support multi dimensional arraylists? If any has any further information or ideas it would be most appreciated.

The .NET Common Language Runtime does not support multi-dimensional arraylists. I've looked at your code and in fact you are doing the same as what an Arraylist does when it runs out of space. An Arraylist is based on an underlying fixed length array and, like you it,allocates a new longer array and copies the existing items into it. Unlike you it usually doubles the array length each time. The underlying .NET array copy mechanism appears to be a native code function call and is probably a memory block copy so your implementation is probably reasonably efficient. You could only get better if you knew how many rows the query returned but that's getting out of my depth as I don't do database work.

[citywest]

Thanks for the information agraham.

The sqldatareader is a forward reader only, so you have to read through the entire result set before you can determine the number of rows.

I guess a better way might be to allow the array size to be specified by the caller program.

Regards

Mark S

[UnknownArt]

I am a newbie for basic4ppc. Can you provide any basic4ppc sample code about using MSSQL.DLL?
Thanks,
Mike

[javcone]

Alguien tiene algun ejemplo de conectar a un sqlserver 2000 usando System.Data.SqlClient.dll. Gracias a todos

[citywest]

Been very busy lately so have only just seen this

A small code snippet that should get you going:

'Select All Records From Table CodMast and in Database ArrowDemo. Display the Fields named Record_Type and Description

msql1.New1
msql1.Open("Persist Security Info=False;Integrated Security=False;Server=CWS-MARK, 1433; initial catalog=ArrowDemo; user id=sa; password=1234;")

msql1.ExecuteQuery("Select * From CodMast Where Record_Type = 'LS' Order By Code")

Do While msql1.Data
Msgbox(msql1.Read("Record_Type") & " " & msql1.Read("Description"))
Loop

msql1.Close

'Update Table CodMast in Database ArrowDemo

msql2.New1
msql2.Open("Persist Security Info=False;Integrated Security=False;Server=CWS-MARK, 1433; initial catalog=ArrowDemo; user id=sa; password=1234;")

msql2.ExecuteNonQuery("Update CodMast Set Description = 'TEST DESCRIPTION' Where Record_Type = 'LS' And Code = 'EF')
msql2.Close

Cheers,

Mark S.

[soneinsa]

Hi,

I download the MSSQL Library and made a litter program to test it. What it does is create a new connection and try to open it, but before the form appear in the Device (HTC Touch, WM6 Professional) an error arise saying that it require a new .Net Compact Framework, it has installed the 2.0 CF, any way I install the 3.5 CF and the error continue.

What is wrong?

Rafael Liriano
SONEINSA

[Erel]

This library depends on another dll: System.Data.SqlClient.Dll

I've attached it.
You will need to copy it to the device together with MSSQL.dll.

[soneinsa]

Hi,

Thanks for the answer, now the application run after copy the System.Data.SqlClient.Dll file and the MSSQL.Dll.!!!

Now the my problem is that the following code does not open the connection to the SQL Server, I try SQL Server 2005 and SQL Server 2000, I try to connect wireless from a laptop to XPS server and the connection is Ok, but when try to do it from the Device (HTC Touch, WM6 Professional) to the same XPS Server then cnDatos.open return False.

When I click the button the .Open return False. What I'm doing wrong?, What I'm missing?. Please help me, because connecting to SQL Server is very necessary to my project. If I connect to the PC that has the XPS SQL Server via the file explorer from the Device I see the sharing folder from the PC, I mean that the wireless connection is ok with the PC.

Sub Globals
    'Declare the global variables here.

End Sub

Sub App_Start
    Form1.Show
    cnDatos.New1
End Sub

Sub Button1_Click
    If cnDatos.Open("Persist Security Info=False;Integrated Security=False;Server=XPS,1433; initial catalog=windip; user id=sa; password=12345;") Then
        Msgbox("Conexion exitosa!!!","My First Program",cMsgboxYesNo,cMsgboxHand)
    Else    
        Msgbox("Conexion ERRONEA!!!","My First Program",cMsgboxYesNo,cMsgboxHand)
    End If

End Sub

Thanks in advanced

Rafael Liriano
SONEINSA

[Erel]

Did you try to see the value of cnDatos.LastError?