In this topic I will try to put up a way to deal with SQL Server using MASM32, step by step I'll try making the procedures and functions in order to facilitate the work
And just for clarification, I use
Microsoft® SQL Server® 2012 Express (https://www.microsoft.com/en-us/download/details.aspx?id=29062), and
Microsoft .NET Framework 4 (https://www.microsoft.com/en-us/download/details.aspx?id=17718),
RadASM IDE, on
Windows 8.1 x64, means that the code is not tested on another system.
To deal with databases we need
System.Data library, and to get them we can use the tool
Masm32Ref (https://sourceforge.net/projects/masm32ref/files/Masm32Ref.exe/download)Open the tool and then choose :"
List Registred .Net Assembly", Then choose :"
System.Data, Version=x.x.x.x" , Then choose then output folder and OK.
Go back to the IDE, open a new project name SqlExp.
so
First we must make sure the work of the .Net using the function
__InitCLRQuote
__UNICODE__ equ 1
include System.Data.INC
include Masm32RefMacro.INC
include masm32rt.inc
.code
start:
.if rv(__InitCLR)
;
.else
invoke crt_wprintf,BSTR$(13,10," unable to Initialise CLR")
.endif
inkey
exit
end start
Note the necessary files should be loaded :
Masm32Ref.INC (https://sourceforge.net/projects/masm32ref/files/Masm32Ref.INC/download) to "\Masm32\include"
Masm32Ref.lib (https://sourceforge.net/projects/masm32ref/files/Masm32Ref%20LIB/Masm32Ref.lib/download) to "\Masm32\lib"
Masm32RefMacro.INC (https://sourceforge.net/projects/masm32ref/files/Masm32RefMacro.INC/download) to "\Masm32\include"
Check if exist a database
Method:
ExistSqlDataBase Arg:
dbName : the name of database
Return :
eax == TRUE if database exist
eax == FALSE if database not exist
eax == 2 if Eror
Quote
ExistSqlDataBase PROC dbName
LOCAL l_SqlConnection
LOCAL l_SqlCommand
LOCAL l_SqlDataReader
LOCAL l_Bool
LOCAL create_Str [255]:word
LOCAL create_StrB
__New_Null l_SqlConnection
__New_Null l_SqlCommand
__New_Null l_SqlDataReader
New_System_Data_SqlClient_SqlConnection_2 l_SqlConnection
.if eax
System_Data_SqlClient_SqlConnection_CreateCommand l_SqlConnection,l_SqlCommand
System_Data_SqlClient_SqlConnection_ConnectionString_Set l_SqlConnection,__String(" Data Source=.\SQLEXPRESS;Integrated Security=True;")
System_Data_SqlClient_SqlConnection_Open l_SqlConnection
.if eax
invoke RtlZeroMemory ,addr create_Str,255*2
invoke lstrcat,addr create_Str,BSTR$(" IF EXISTS",28h,"SELECT * FROM sys.sysdatabases where name= ",27h)
invoke lstrcat,addr create_Str,dbName
invoke lstrcat,addr create_Str,BSTR$(27h,29h,13,10," SELECT ",27h,"OK",27h,"; ")
invoke SysAllocString,addr create_Str
mov create_StrB,eax
System_Data_SqlClient_SqlCommand_CommandText_Set l_SqlCommand,__String( create_StrB)
__New_Null l_Bool
System_Data_SqlClient_SqlCommand_ExecuteReader l_SqlCommand,l_SqlDataReader
System_Data_SqlClient_SqlDataReader_Read l_SqlDataReader,l_Bool
System_Data_SqlClient_SqlDataReader_HasRows_Get l_SqlDataReader,l_Bool
System_Data_SqlClient_SqlConnection_Close l_SqlConnection
invoke SysFreeString,create_StrB
mov ecx,l_Bool
movzx eax,[ecx].VARIANT.boolVal
.else
invoke crt_wprintf,BSTR$(13,10," unable to Open SqlConnection")
mov eax ,2
.endif
.else
invoke crt_wprintf,BSTR$(13,10," unable to create new SqlConnection")
mov eax ,2
.endif
ret
ExistSqlDataBase endp
Create new database
Method:
CreateSQLDataBaseArg:
dbName : the name of database
Return:
eax==True : if the database created
eax==False :error
Quote
CreateSQLDataBase PROC dbName
LOCAL l_SqlConnection
LOCAL l_SqlCommand
LOCAL create_Str [255]:word
LOCAL create_StrB
__New_Null l_SqlConnection
__New_Null l_SqlCommand
New_System_Data_SqlClient_SqlConnection_2 l_SqlConnection
.if eax
System_Data_SqlClient_SqlConnection_CreateCommand l_SqlConnection,l_SqlCommand
System_Data_SqlClient_SqlConnection_ConnectionString_Set l_SqlConnection,__String(" Data Source=.\SQLEXPRESS;Integrated Security=True;")
System_Data_SqlClient_SqlConnection_Open l_SqlConnection
.if eax
invoke RtlZeroMemory ,addr create_Str,255*2
invoke lstrcat,addr create_Str,BSTR$(" CREATE DATABASE ")
invoke lstrcat,addr create_Str,dbName
invoke SysAllocString,addr create_Str
mov create_StrB,eax
System_Data_SqlClient_SqlCommand_CommandText_Set l_SqlCommand,__String( create_StrB)
System_Data_SqlClient_SqlCommand_ExecuteNonQuery l_SqlCommand,0
push eax
invoke SysFreeString,create_StrB
System_Data_SqlClient_SqlConnection_Close l_SqlConnection
pop eax
.endif
.endif
ret
CreateSQLDataBase endp
Example:
Quote
__UNICODE__ equ 1
include System.Data.INC
include Masm32RefMacro.INC
include masm32rt.inc
include SqlExp.Inc
.data
.code
start:
.if rv(__InitCLR)
.if _rv( ExistSqlDataBase,BSTR$("DBTest")) != 2
.if eax
invoke crt_wprintf,BSTR$(13,10," Database exist",13,10)
.else
invoke crt_wprintf,BSTR$(13,10," Database not Found",13,10)
invoke CreateSQLDataBase,BSTR$("DBTest")
.endif
.endif
.else
invoke crt_wprintf,BSTR$(13,10," unable to Initialise CLR",13,10)
.endif
inkey
exit
end start
Drop DataBase
Method:
DropSQLDataBaseArg:
dbName : the name of database
Return:
eax==True : if OK
eax==False :error
Quote DropSQLDataBase PROC dbName
LOCAL l_SqlConnection
LOCAL l_SqlCommand
LOCAL create_Str [255]:word
LOCAL create_StrB
__New_Null l_SqlConnection
__New_Null l_SqlCommand
New_System_Data_SqlClient_SqlConnection_2 l_SqlConnection
.if eax
System_Data_SqlClient_SqlConnection_CreateCommand l_SqlConnection,l_SqlCommand
System_Data_SqlClient_SqlConnection_ConnectionString_Set l_SqlConnection,__String(" Data Source=.\SQLEXPRESS;Integrated Security=True;")
System_Data_SqlClient_SqlConnection_Open l_SqlConnection
.if eax
invoke RtlZeroMemory ,addr create_Str,255*2
invoke lstrcat,addr create_Str,BSTR$(" Drop DATABASE ")
invoke lstrcat,addr create_Str,dbName
invoke SysAllocString,addr create_Str
mov create_StrB,eax
System_Data_SqlClient_SqlCommand_CommandText_Set l_SqlCommand,__String( create_StrB)
System_Data_SqlClient_SqlCommand_ExecuteNonQuery l_SqlCommand,0
push eax
invoke SysFreeString,create_StrB
System_Data_SqlClient_SqlConnection_Close l_SqlConnection
pop eax
.endif
.endif
ret
DropSQLDataBase endp
Create Table
Method:
CreateSQLTableArg:
dbName: the name of database
tableName: the name of table
Primary_Key:
True :create primary key identity,
False: nothing
Return:
eax==True : if OK
eax==False :error
Quote
CreateSQLTable PROC dbName,tableName,Primary_Key
LOCAL l_SqlConnection
LOCAL l_SqlCommand
LOCAL create_Str [512]:word
LOCAL create_StrB
__New_Null l_SqlConnection
__New_Null l_SqlCommand
New_System_Data_SqlClient_SqlConnection_2 l_SqlConnection
.if eax
System_Data_SqlClient_SqlConnection_CreateCommand l_SqlConnection,l_SqlCommand
invoke RtlZeroMemory ,addr create_Str,255*2
invoke lstrcat,addr create_Str,BSTR$(" Data Source=.\SQLEXPRESS; Initial Catalog=")
invoke lstrcat,addr create_Str,dbName
invoke lstrcat,addr create_Str,BSTR$("; Integrated Security=True;")
invoke SysAllocString,addr create_Str
mov create_StrB,eax
System_Data_SqlClient_SqlConnection_ConnectionString_Set l_SqlConnection,__String(create_StrB)
invoke SysFreeString,create_StrB
System_Data_SqlClient_SqlConnection_Open l_SqlConnection
.if eax
invoke RtlZeroMemory ,addr create_Str,512*2
invoke lstrcat,addr create_Str,BSTR$(" CREATE TABLE ")
invoke lstrcat,addr create_Str,tableName
.if Primary_Key
invoke lstrcat,addr create_Str,BSTR$(" ",28h,"Id INT PRIMARY KEY IDENTITY",29h)
.endif
invoke SysAllocString,addr create_Str
mov create_StrB,eax
System_Data_SqlClient_SqlCommand_CommandText_Set l_SqlCommand,__String(create_StrB)
System_Data_SqlClient_SqlCommand_ExecuteNonQuery l_SqlCommand,0
push eax
invoke SysFreeString,create_StrB
System_Data_SqlClient_SqlConnection_Close l_SqlConnection
pop eax
.endif
.endif
ret
CreateSQLTable endp
Example:
Quote
__UNICODE__ equ 1
include System.Data.INC
include Masm32RefMacro.INC
include masm32rt.inc
include SqlExp.Inc
.code
start:
.if rv(__InitCLR)
.if _rv( ExistSqlDataBase,chr$("Masm32_DataBase")) != 2
.if eax
invoke crt_wprintf,cfm$("\n Database exist \n")
.else
.if _rv(CreateSQLDataBase,chr$("Masm32_DataBase"))
invoke CreateSQLTable,chr$("Masm32_DataBase"),chr$("Masm32_Table1") ,TRUE
invoke CreateSQLTable,chr$("Masm32_DataBase"),chr$("Masm32_Table2") ,TRUE
invoke CreateSQLTable,chr$("Masm32_DataBase"),chr$("Masm32_Table3") ,TRUE
invoke crt_wprintf,cfm$("\n Operation Complet \n")
.else
invoke crt_wprintf,cfm$("\n unable to create new database \n")
.endif
.endif
.endif
.else
invoke crt_wprintf,cfm$("\n unable to Initialise CLR \n")
.endif
inkey
exit
end start
(http://gdurl.com/7ChF)
MABDELOUAHAB,
This is a terrific concept,...I have never learned how to program SQL in any variation. I will study this intensely,...thanks for posting all the example code.
next step: hibernate + ObjAsm32 :biggrin:
Hi Zen,qWord, I thank you for reply
Quote from: qWord on April 29, 2016, 06:08:09 AM
next step: hibernate + ObjAsm32 :biggrin:
There are a lot :biggrin:, I expected that no one cares for the topic
Add Column To Table
Method:
AddColumnToSQLTableArg:
dbName: the name of database
tableName: the name of table
columnName: the name of table
SqlDbTypeStr: Data Type [,length][,Default Value]
Data Type:
BigInt, Binary, Bit, Char, DateTime, Decimal, Float, Image, Int, Money,
NChar, NText, NVarChar, Real, UniqueIdentifier, SmallDateTime, SmallInt, SmallMoney, Text, Timestamp,
TinyInt, VarBinary, VarChar, Variant, Xml, Udt, Structured, Date, Time, DateTime2, DateTimeOffsetReturn:
eax==True : if OK
eax==False :error
Quote
AddColumnToSQLTable PROC dbName,tableName,columnName,__SqlDbTypeStr
LOCAL l_SqlConnection
LOCAL l_SqlCommand
LOCAL create_Str [512]:word
LOCAL create_StrB
__New_Null l_SqlConnection
__New_Null l_SqlCommand
New_System_Data_SqlClient_SqlConnection_2 l_SqlConnection
.if eax
System_Data_SqlClient_SqlConnection_CreateCommand l_SqlConnection,l_SqlCommand
invoke RtlZeroMemory ,addr create_Str,255*2
invoke lstrcat,addr create_Str,BSTR$(" Data Source=.\SQLEXPRESS; Initial Catalog=")
invoke lstrcat,addr create_Str,dbName
invoke lstrcat,addr create_Str,BSTR$("; Integrated Security=True;")
invoke SysAllocString,addr create_Str
mov create_StrB,eax
System_Data_SqlClient_SqlConnection_ConnectionString_Set l_SqlConnection,__String(create_StrB)
invoke SysFreeString,create_StrB
System_Data_SqlClient_SqlConnection_Open l_SqlConnection
.if eax
invoke RtlZeroMemory ,addr create_Str,512*2
invoke lstrcat,addr create_Str,BSTR$(" ALTER TABLE ")
invoke lstrcat,addr create_Str,tableName
invoke lstrcat,addr create_Str,BSTR$(" ADD ")
invoke lstrcat,addr create_Str,columnName
invoke lstrcat,addr create_Str,BSTR$(" ")
invoke lstrcat,addr create_Str,__SqlDbTypeStr
invoke SysAllocString,addr create_Str
mov create_StrB,eax
System_Data_SqlClient_SqlCommand_CommandText_Set l_SqlCommand,__String(create_StrB)
System_Data_SqlClient_SqlCommand_ExecuteNonQuery l_SqlCommand,0
push eax
invoke SysFreeString,create_StrB
System_Data_SqlClient_SqlConnection_Close l_SqlConnection
pop eax
.endif
.endif
ret
AddColumnToSQLTable endp
Examples:
Quote
invoke AddColumnToSQLTable ,chr$("Masm32_DataBase"),
chr$("Masm32_Table2"),
chr$("Masm32_Colomn1"),
chr$(" Int ")
invoke AddColumnToSQLTable ,chr$("Masm32_DataBase"),
chr$("Masm32_Table2"),
chr$("Masm32_Colomn2"),
chr$(" NVarChar(20) Not Null DEFAULT 'Abc' ")
invoke AddColumnToSQLTable ,chr$("Masm32_DataBase"),
chr$("Masm32_Table2"),
chr$("Masm32_Colomn3"),
chr$(" Int Not Null DEFAULT 19 ")
(http://gdurl.com/ae2B)
The second part: Use a database
I. Execute Non Query
UPDATE, INSERT, DELETE
I think we will need many Methods :biggrin:
Methods -------------------------------------------------------------------------------------------------------------------------------------
CreateSqlConnection:
Return:
eax== pointer to System.Data.SqlClient.SqlConnection Object (PTR to VARIANT )
eax==False :error
-------------------------------------------------------------------------------------------------------------------------------------
OpenSqlConnection:
Arg:
pSqlConnection: System.Data.SqlClient.SqlConnection Object (from CreateSqlConnection)
pConnectionString: Connection String (
ConnectionString Property (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx) /
SQL Server 2012 connection strings (https://www.connectionstrings.com/sql-server-2012/))
Return:
eax==True : if OK
eax==False :error
-------------------------------------------------------------------------------------------------------------------------------------
CreateSqlCommand:
Arg:
pSqlConnection: System.Data.SqlClient.SqlConnection Object (from CreateSqlConnection)
Return:
eax==pointer to System.Data.SqlClient.SqlCommand Object (PTR to VARIANT )
eax==False :error
-------------------------------------------------------------------------------------------------------------------------------------
SetCommandText:
Arg:
pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand)
queryString: String=Transact-SQL statement (
Transact-SQL Reference (https://msdn.microsoft.com/en-us/library/bb510741.aspx) /
TSQL Tutorial (http://www.tsql.info))
Return:
eax==True :OK
eax==False :error
-------------------------------------------------------------------------------------------------------------------------------------
ExecuteNonQuery: Executes a Transact-SQL statement
Arg:
pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand)
Return:
eax== The number of rows affected.
eax==False :error
Quote
;-------------------------------------------------------------------------------------------------------------------------------------
CreateSqlConnection Proc
LOCAL lSqlConnection
mov lSqlConnection,_rv(HeapAlloc,_rv(GetProcessHeap),0,16)
New_System_Data_SqlClient_SqlConnection_2 lSqlConnection
.if eax
mov eax,lSqlConnection
.endif
ret
CreateSqlConnection endp
;-------------------------------------------------------------------------------------------------------------------------------------
OpenSqlConnection Proc pSqlConnection,pConnectionString
LOCAL LString
mov LString,_rv(SysAllocString,pConnectionString)
System_Data_SqlClient_SqlConnection_ConnectionString_Set pSqlConnection,__String(LString)
invoke SysFreeString,LString
System_Data_SqlClient_SqlConnection_Open pSqlConnection
ret
OpenSqlConnection endp
;-------------------------------------------------------------------------------------------------------------------------------------
CreateSqlCommand Proc pSqlConnection
LOCAL lSqlCommand
mov lSqlCommand,_rv(HeapAlloc,_rv(GetProcessHeap),0,16)
System_Data_SqlClient_SqlConnection_CreateCommand pSqlConnection,lSqlCommand
.if eax
mov eax,lSqlCommand
.endif
ret
CreateSqlCommand endp
;-------------------------------------------------------------------------------------------------------------------------------------
SetCommandText Proc pSqlCommand,queryString
LOCAL LString
mov LString,_rv(SysAllocString,queryString)
System_Data_SqlClient_SqlCommand_CommandText_Set pSqlCommand,__String(LString)
PUSH EAX
invoke SysFreeString,LString
POP EAX
ret
SetCommandText endp
;-------------------------------------------------------------------------------------------------------------------------------------
ExecuteNonQuery proc pSqlCommand
LOCAL nbrows
__New_Null nbrows
System_Data_SqlClient_SqlCommand_ExecuteNonQuery pSqlCommand,nbrows
.if eax
mov eax,nbrows
mov eax,[eax].VARIANT.lVal
.endif
ret
ExecuteNonQuery endp
Example:Quote
.data
_SqlConnection dd 0
_SqlCommand dd 0
__ConnectionString dd BSTR$(" Data Source=.\SQLEXPRESS; Integrated Security=True; Initial Catalog=Masm32_DataBase")
.code
mov _SqlConnection,_rv( CreateSqlConnection)
.if eax
.if _rv(OpenSqlConnection,_SqlConnection,__ConnectionString)
mov _SqlCommand,_rv(CreateSqlCommand,_SqlConnection)
invoke SetCommandText,_SqlCommand,chr$(" INSERT INTO Masm32_Table2 (Masm32_Colomn1)VALUES (1889); ")
invoke ExecuteNonQuery,_SqlCommand
invoke crt_wprintf,cfm$("\n The number of rows affected=%d \n"),eax
invoke SetCommandText,_SqlCommand,chr$(" INSERT INTO Masm32_Table2 (Masm32_Colomn2)VALUES ('Example A');",13,10," INSERT INTO Masm32_Table2 (Masm32_Colomn1)VALUES (1889);")
invoke ExecuteNonQuery,_SqlCommand
invoke crt_wprintf,cfm$("\n The number of rows affected=%d \n"),eax
.endif
.endif
Quote
The number of rows affected=1
The number of rows affected=2
Add SQL Parameters
Methods -------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: BigInt: AddLongParameter:
Arg:
pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
_parameterName : ptr to parameter Name
_parameterValue : the Value DWORD
-------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: Bit: AddBoolParameter:
Arg:
pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
_parameterName : ptr to parameter Name
_parameterValue : TRUE or FALSE
-------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: Char, Text, VarChar, NChar, NText, NVarChar, Xml: AddStringParameter:
Arg:
pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
_parameterName : ptr to parameter Name
_parameterValue : ptr to text value
Quote AddParameter PROC pSqlCommand,_parameterName,_parameterValue
LOCAL l_Parameter , LString , VString
LOCAL VarValue:VARIANT
mov LString,_rv(SysAllocString,_parameterName)
__New_Null l_Parameter
System_Data_SqlClient_SqlCommand_Parameters_Get pSqlCommand,l_Parameter
System_Data_SqlClient_SqlParameterCollection_AddWithValue l_Parameter,__String(LString),_parameterValue,0
invoke SysFreeString,LString
ret
AddParameter endp
;----------------------------------------------------------------------------
; long: SqlDbType.BigInt:
AddLongParameter PROC pSqlCommand,_parameterName,_parameterValue
LOCAL VarValue:VARIANT
mov VarValue.vt,VT_I4
mov eax,_parameterValue
mov VarValue.lVal,eax
invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
ret
AddLongParameter endp
;----------------------------------------------------------------------------
; bool: SqlDbType.Bit:
AddBoolParameter PROC pSqlCommand,_parameterName,_parameterValue:word
LOCAL VarValue:VARIANT
mov VarValue.vt,VT_BOOL
mov ax,_parameterValue
mov VarValue.boolVal,ax
invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
ret
AddBoolParameter endp
;----------------------------------------------------------------------------
; string: SqlDbType.Char,SqlDbType.Text,SqlDbType.VarChar,SqlDbType.NChar,SqlDbType.NText,SqlDbType.NVarChar,SqlDbType.Xml
AddStringParameter PROC pSqlCommand,_parameterName,_parameterValue
LOCAL VString
LOCAL VarValue:VARIANT
mov VString,_rv(SysAllocString,_parameterValue)
mov VarValue.vt,VT_BSTR
mov eax,VString
mov VarValue.bstrVal,eax
invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
invoke SysFreeString,VString
ret
AddStringParameter endp
Example :
Quote
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("ClmBigInt"),
chr$(" BigInt ")
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("ClmBit"),
chr$(" Bit ")
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("ClmNText"),
chr$(" NText ")
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("ClmNVarChar"),
chr$(" NVarChar(30) ")
....
invoke SetCommandText,_SqlCommand,chr$(" INSERT INTO MyTable (ClmBigInt,ClmBit,ClmNVarChar,ClmNText)VALUES (@v01,@v02,@v03,@v04); ")
invoke AddLongParameter,_SqlCommand,chr$("@v01"),1477
invoke AddBoolParameter,_SqlCommand,chr$("@v02"),TRUE
invoke AddStringParameter,_SqlCommand,chr$("@v03"),chr$("strint example ")
invoke AddStringParameter,_SqlCommand,chr$("@v04"),chr$("masm32 forum")
invoke ExecuteNonQuery,_SqlCommand
(http://gdurl.com/IRyW)
Hi, MABDELOUAHAB,
Sorry, I've had other stuff to do lately, and didn't get to your SQL Project. But, I visited Sourceforge: Masm32Ref, Brought to You By: MABDELOUAHAB (https://sourceforge.net/projects/masm32ref/).
I didn't realize that you had posted a thread about: Masm32Ref, January 14, 2016 (http://masm32.com/board/index.php?topic=5028.msg55550). I was totally unaware that this program even existed.
THIS IS EXTREMELY IMPRESSIVE !!!
...I'm afraid to ask you how much sleep you lost coding this tool,...:bgrin:
Quote from: Zen on May 12, 2016, 07:56:49 AM
THIS IS EXTREMELY IMPRESSIVE !!!
I fully agree :t
thank you
jj2007,
ZenQuote from: Zen on May 12, 2016, 07:56:49 AM
...I'm afraid to ask you how much sleep you lost coding this tool,...:bgrin:
two years :biggrin:
I do not forget that your topic
Really Bizarre Question (http://masm32.com/board/index.php?topic=2106.msg21988) has helped me, and so the topic deleted by MSDN
.NET Type Internals (http://msdn.microsoft.com/en-us/magazine/cc163791.aspx), but there is a copy in
.NET Framework Internals to See How the CLR Creates Runtime Objects (http://www.cnblogs.com/sunkang/archive/2011/04/28/2038821.html)
Add SQL Parameters
Methods -------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: Real: AddFloatParameter:
Arg:
pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
_parameterName : ptr to parameter Name
_parameterValue : the Value REAL4
-------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: Float: AddDoubleParameter:
Arg:
pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
_parameterName : ptr to parameter Name
_parameterValue : the Value REAL8
-------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: Int: AddIntParameter:
Arg:
pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
_parameterName : ptr to parameter Name
_parameterValue : dword
-------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: SmallInt: AddShortParameter:
Arg:
pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
_parameterName : ptr to parameter Name
_parameterValue : word value
Quote
;float: SqlDbType.Real
AddFloatParameter PROC pSqlCommand,_parameterName,_parameterValue:REAL4
LOCAL VarValue:VARIANT
mov VarValue.vt,VT_R4
lea ecx,VarValue.fltVal
fld _parameterValue
fstp REAL4 PTR [ecx]
invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
ret
AddFloatParameter endp
;----------------------------------------------------------------------------
;double: SqlDbType.Float:
AddDoubleParameter PROC pSqlCommand,_parameterName,_parameterValue:REAL8
LOCAL VarValue:VARIANT
mov VarValue.vt,VT_R8
lea ecx,VarValue.dblVal
fld _parameterValue
fstp REAL8 PTR [ecx]
invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
ret
AddDoubleParameter endp
;----------------------------------------------------------------------------
;int: sqlDbType.Int:
AddIntParameter PROC pSqlCommand,_parameterName,_parameterValue
LOCAL VarValue:VARIANT
mov VarValue.vt,VT_INT
mov eax,_parameterValue
mov VarValue.lVal,eax
invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
ret
AddIntParameter endp
;----------------------------------------------------------------------------
;short; SqlDbType.SmallInt:
AddShortParameter PROC pSqlCommand,_parameterName,_parameterValue:word
LOCAL VarValue:VARIANT
mov VarValue.vt,VT_I2
mov ax,_parameterValue
mov VarValue.iVal,ax
invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
ret
AddShortParameter endp
Example :
Quote
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("ClmRealt"),
chr$(" Real")
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("ClmFloat"),
chr$(" Float ")
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("ClmInt"),
chr$(" Int")
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("ClmSmallInt"),
chr$(" SmallInt ")
....
.data
prmPIr4 REAL4 3.14159265358979323846264338327
prmPIr8 REAL8 3.14159265358979323846264338327
.code
invoke SetCommandText,_SqlCommand,chr$(" INSERT INTO MyTable (ClmReal,ClmFloat,ClmInt,ClmSmallInt)VALUES (@v01,@v02,@v03,@v04); ")
invoke AddFloatParameter,_SqlCommand,chr$("@v01"),prmPIr4
invoke AddDoubleParameter,_SqlCommand,chr$("@v02"),prmPIr8
invoke AddIntParameter,_SqlCommand,chr$("@v03")1012
invoke AddShortParameter,_SqlCommand,chr$("@v04")511
invoke ExecuteNonQuery,_SqlCommand
(http://gdurl.com/frBZ)
Add SQL Parameters
Methods -------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: Binary, Image, Timestamp, VarBinary: AddArrayOfByteParameter:
Arg:
pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
_parameterName : ptr to parameter Name
_nByte: The number of bytes to be passed
_pByte: Ptr to the first byte
Quote
;byte[]: SqlDbType.Binary, SqlDbType.Image, SqlDbType.Timestamp, SqlDbType.VarBinary
AddArrayOfByteParameter PROC pSqlCommand,_parameterName,_nByte,_pByte
LOCAL VarValue:VARIANT
.data
__byteArray SAFEARRAY <>
.code
lea ecx,__byteArray
mov [ecx].SAFEARRAY.cDims ,1
mov [ecx].SAFEARRAY.fFeatures ,0
mov [ecx].SAFEARRAY.cbElements ,1
mov [ecx].SAFEARRAY.cLocks ,0
mov eax,_pByte
mov [ecx].SAFEARRAY.pvData ,eax
mov eax,_nByte
mov [ecx].SAFEARRAY.rgsabound.cElements ,EAX
mov [ecx].SAFEARRAY.rgsabound.lLbound ,0
mov VarValue.vt,VT_ARRAY OR VT_UI1
mov VarValue.parray,ecx
invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
ret
AddArrayOfByteParameter endp
Example :
Quote
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("column01"),
chr$(" Binary(3) ")
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("column02"),
chr$(" Image ")
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("column03"),
chr$(" Timestamp ")
invoke AddColumnToSQLTable ,chr$("MyDataBase"),
chr$("MyTable"),
chr$("column04"),
chr$(" VarBinary(10) ")
....
.data
arrByte db 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
.code
invoke SetCommandText,_SqlCommand,chr$(" INSERT INTO MyTable (column01,column02,column04)VALUES (@p1,@A2,@param); ")
invoke AddArrayOfByteParameter,_SqlCommand,chr$("@p1"),3,addr arrByte
invoke AddArrayOfByteParameter,_SqlCommand,chr$("@param"),10,addr arrByte
invoke AddArrayOfByteParameter,_SqlCommand,chr$("@A2"),15,addr arrByte
invoke ExecuteNonQuery,_SqlCommand
Quote
timestamp (Transact-SQL)
Is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.
Remarks
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value and, therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index
From:https://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx (https://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx)