Learn step by step network programming using C#.Net socket, remoting, WCF and SignalR technology from lots of source code and description. All source code download link available at end of the blog post.
These lines of code reading some
particular file from local drive and string its data in byte array
“clientData”. File data need to store in array with raw byte format to send
these to client. With data file name size string at initial of file data. This
is predefined between client and server and it needs to do, otherwise client
will not get file name which is sending by server.
For my case I am using first four
byte to represent file name length and form 5thbyte file name is storing. So all file
data will store after file name.
5) Server Action:Now file data is in byte array and it needs
to send to client. The same thing is happening by using below code with the
help of client socket (clientSock) object, which was created during client
request acceptance.
clientSock.Send(clientData);
Basically server application task
ends here for small file transfer. Remaining code has used for some decoration
and socket closing related things.
5) Client Action:Now again turn comes to client and it will
perform below tasks:
Here first two lines are just
creating byte array to store server data and path is used to decide where data
to be save. In my code, I am saving data in C: drive.
Last line is start receiving data
from server. Whenever client socket starts receiving server data then it
returns length of data which has captured in a integer variable.
Now I will discuss about, how we can transfer a large file (file size in GB) using Microsoft dot net socket programming using C# (C sharp) language. This code has written in Visual Studio 2005 (dot net 2).
To cover this article you need to read my few articles, these are –
1. Asynchronous Socket Server Programming for Beginner
2. Asynchronous Socket Client Programming for Beginner
3. File Transfer using C# Socket Programming
4. Split and Assemble large file (around 2GB) in C# dot net Programming
Steps
The basic code has covered in these article and that article basically comes by assembled these four. To transfer a large file from Client to Server I’ve followed these basic steps-
1)First read the file (slice wise) and store a slice data in a buffer (byte array). How a file can read slice wise, it has covered in my 4th article (Split and Assemble large file (around 2GB) in C#) in file split section. It’s required because TCP buffer is limited so if we try to send a large data then we will get TCP buffer overflow error.
2)Send these buffered data to server using TCP socket object. How it can be done, has covered in 3rd article (File Transfer using C# Socket programming). And for basic idea you can read article 1 & 2.
3)In server side when we starts to receive sliced data from client then we write these data in a file. How this can be done has covered in article 4 (Split and Assemble large file (around 2GB) in C#.net ), in file assemble section.
To receive file from Server to Client same process is applicable, just then server sends data by sliced and receive by Client in same way.
How to use DLL
In my code I have made these two functionality (server and client) in two separate class library (DLL file). By calling these two libraries (DLL file) any one can transfer data very easily. To do it need to follow these steps -
Step 1: Create Object
To use these dll to transfer data you need to create one client and one server object.
For client object -
SynchronusClient_ByteArr objClient = new SynchronusClient_ByteArr ("","localhost", @"C:\Documents and Settings\Administrator\UserData");
First argument for client id, if you keep it blank string then also ok, but when you use dll for multiple client (sender) to transfer multiple file to multiple client (receiver) then it’s mandatory. Otherwise file may goes to all user or different user. Next argument for server IP, and next for a temporary back up path, it’s require at file receive time from server.
Here and one more constructor with more argument there you can assign port no etc. Here default port is 8000 and 8001.
For Server object –
SyncSocketServerMulClient servObj = new SyncSocketServerMulClient();
If you use this code then server will load it’s default configuration, like port 8000 and 8001 and it’s default path to C: Other wise you can call overloaded method.
Step 2: Method & Properties
For server and client has some method which you need to call.
Client methods and properties are –
Picture 1 Picture 2
Picture 1 for Method and events of client object and Picture 2 for method and property of client class.
Hope from pic 1 you can understand everything very easily and from pic 2 also clear from it’s name. For ‘status’, it holds current status of client like, slicing data, sending data, receiving data etc and ‘progress’ it holds current % of data transfer currently done.
Server methods and properties are –
Picture 3 Picture 4
For pic 3 hope everything clear to you. But my comments here is, when you call start server then server starts and stays active as long as you call stop server. After call start server you can’t call it again before stop server call.
From pic 4 ‘bufferSize’ is for declare byte array length, you can define it as your self, ‘maxClientReceived’ for how many client can send or receive data at a time.
By using this code I’ve developed few large application, like text chat with smiles, file transfer, picture transfer, online drawing etc. Hope you will get a great experience by that code.
Download link
You can download source code from these two links-
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Net;
using System.Net.Sockets;
using System.IO;
/// That server code will run to access multiple client requrest and response. Here will not any
/// Un-zip and Decript technology. Server will save all data in Encripted-Zipped form which will
/// sent by Client.
public class SyncSocketServerMulClient
{
public static bool isServerRunning=false;
public static int receivePort,sendPort, maxClientReceived, bufferSize;
//public static int progress=0 ;
public string outPath ;
public static string status="",presentOperation = "";
public string currentStatus = "";
byte[] data = new byte[bufferSize];
//DATA FORMAT: [FILE SIZE LEN INFO[0-3]][FILE NAME LEN INFO[4-7]][FILE NAME DATA][FILE CONTENT]
//GET FILE NAME, SIZE ETC.
currentStatus =presentOperation = "Data Receiving";
int len = clientSock.Receive(data);
if (len == 0)
{
clientSock.Close();
return;
}
if (len == 3)
{
string clientData = Encoding.ASCII.GetString(data);
if (clientData.Substring(0, len) == "END")
return;
}
totalDataLen = BitConverter.ToInt32(data, 0);
fileNameLen = BitConverter.ToInt32(data, 4);
fileName = Encoding.ASCII.GetString(data, 8, fileNameLen);
fileContentStartIndex = 4 + 4 + fileNameLen;
receivedLen = len - fileContentStartIndex;
//READ DATA & STORE OF FIRST PACKET
//DELETE IF FILE ALREADY EXIST
if (File.Exists(outPath + fileName))
File.Delete(outPath + fileName);
bWriter = new BinaryWriter(File.Open(outPath + fileName, FileMode.Append));
bWriter.Write(data, 0, len);
while (true)
{
if (receivedLen < len =" clientSock.Receive(data);" currentstatus ="presentOperation" currentstatus =" presentOperation" clientinfodata =" new" clientinfodata =" Encoding.ASCII.GetBytes(" status = "Access Denied by server end." status = "Address Already In Use." status = "Address Not Available." status = "Connection aborted by server." status = "Connection refused by server." status = "Connection Reset." status = "Destination Address Required." status = "Disconnecting." status = "Target Host is Down." status = "Target Host Not Found." status = "Target Host Unreachable." status = "In Progress." status = "Interrupted." status = "Invalid Argument." status = "Network Down." status = "Network Reset." status = "Network Unreachable." status = "No Buffer Space Available." status = "No Data." status = "Not Connected." status = "Not Initialized." status = "Not Socket." status = "Operation Aborted." status = "Socket already Shutdown." status = "Too Many Open Sockets." status = "Too Many Open Sockets." status = "Have some unknown problem in Socket." isserverrunning =" true;" threadsendserver =" new" ipendsend =" new" sendsock =" new" isserverrunning =" true;" clientsock =" sendSock.Accept();" serobj =" new" newclient =" new" clientsocket="(Socket)clientObject;" filenamewithpath = "" breader =" null;" status = "" clientdata =" new" clientdatalen =" clientSocket.Receive(clientData);" clientdatalen ="=" clientdatalen ="=" clientdatastr =" Encoding.ASCII.GetString(clientData);" clientidlen =" BitConverter.ToInt32(clientData," filenamelen=" BitConverter.ToInt32(clientData," clientid =" Encoding.ASCII.GetString(clientData," filename =" Encoding.ASCII.GetString(clientData," copiedfilename =" clientId" filenamewithpath =" this.outPath;" filenamewithpath =" fileNameWithPath.Replace(" length ="=" currentstatus =" presentOperation" breader =" new" data =" new" sentdatasize =" (int)bReader.BaseStream.Length;" data =" new" sentdatasize =" bufferSize;" totalsentdataslot =" 1;" totaldatasize =" (int)bReader.BaseStream.Length;" progress =" (int)(((float)sentDataSize" progress =" (int)(((float)sentDataSize" enddata =" new" sentdatasize =" (int)bReader.BaseStream.Length;" progress =" (int)(((float)sentDataSize" currentstatus =" presentOperation" strfleerr = "ERROR" tempfleerrarr =" Encoding.ASCII.GetBytes(strFleErr);" currentstatus =" presentOperation" status = "Access Denied by server end." status = "Address Already In Use." status = "Address Not Available." status = "Connection aborted by server." status = "Connection refused by server." status = "Connection Reset." status = "Destination Address Required." status = "Disconnecting." status = "Target Host is Down." status = "Target Host Not Found." status = "Target Host Unreachable." status = "In Progress." status = "Interrupted." status = "Invalid Argument." status = "Network Down." status = "Network Reset." status = "Network Unreachable." status = "No Buffer Space Available." status = "No Data." status = "Not Connected." status = "Not Initialized." status = "Not Socket." status = "Operation Aborted." status = "Socket already Shutdown." status = "Too Many Open Sockets." status = "Too Many Open Sockets." status = "Have some unknown problem in Socket." status =" ex.Message;" isserverrunning =" false;" ipaddress =" Dns.GetHostAddresses(" ipend =" new" clientsock =" new" strdata = "END" clientdata =" new" clientdata =" Encoding.ASCII.GetBytes(strData);" serverdata =" new" len =" clientSock.Receive(serverData);" style="font-weight: bold; color: rgb(51, 51, 255);" size="4">Client Code is here:
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Threading;
using System.Net;
using System.Net.Sockets;
using System.Windows.Forms;
/// Client open a syncronous socket and send file to server, here asynchronous mode becomes by
/// Threading, and here uses Byte Array to transfer data.
public class SynchronusClient_ByteArr
{
public delegate void FileSendCompletedDelegate();
public event FileSendCompletedDelegate FileSendCompleted, FileReceiveCompleted;
public static int progress = 0, portSend, portReceive;
int bufferSize;
public static string status = "", presentOperation = "", ipAddress, outPathDefault = "";
private static string outPathUserSelected = "";
string clientId = "";
string fileNameWithPath;
public static bool isSaveToDefaultPath = false;
static int totalSentDataSlot;
//NEW FILE NAME IS NOW IN IT'S OWN EXTENSION NEED TO CHANGE .sjb FORM
//Only file name
int k = newFileName.Length - 1;
while (newFileName.Substring(k, 1) != ".")
{
k--;
}
newFileName = newFileName.Substring(0, k);
newFileName = newFileName + ".sjb";
this.fileNameWithPath = filePath + tempFileName;// this.fileNameWithPath;
presentOperation = "Data Sending";
IPAddress[] serverIp = Dns.GetHostAddresses(SynchronusClient_ByteArr.ipAddress);
IPEndPoint ipEnd = new IPEndPoint(serverIp[0], SynchronusClient_ByteArr.portSend);
server = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.IP);
server.Connect(ipEnd);
int sentDataSize;
byte[] data;// = new byte[bufferSize];
bReader = new BinaryReader(File.Open(fileNameWithPath, FileMode.Open));
if (bReader.BaseStream.Length <= bufferSize) { data = new byte[bReader.BaseStream.Length]; bReader.Read(data, 0, (int)bReader.BaseStream.Length); sentDataSize = (int)bReader.BaseStream.Length; } else { data = new byte[bufferSize]; bReader.Read(data, 0, bufferSize); sentDataSize = bufferSize; } string onlyFileName; fileNameWithPath = fileNameWithPath.Replace("\\", "/"); onlyFileName = fileNameWithPath; while (onlyFileName.IndexOf("/") != -1) onlyFileName = onlyFileName.Substring(onlyFileName.IndexOf("/") + 1); //DATA FORMAT: [FILE SIZE LEN INFO[0-3]][FILE NAME LEN INFO[4-7]][FILE NAME DATA][FILE CONTENT] byte[] dataLenBytes = BitConverter.GetBytes((int)bReader.BaseStream.Length); byte[] fileNameLenBytes = BitConverter.GetBytes(onlyFileName.Length); byte[] fileNameBytes = Encoding.ASCII.GetBytes(onlyFileName); byte[] allData = new byte[8 + fileNameBytes.Length + data.Length]; //MAKE FIRST PACKET TO SERVER WITH FILE SIZE & FILE NAME dataLenBytes.CopyTo(allData, 0); fileNameLenBytes.CopyTo(allData, 4); fileNameBytes.CopyTo(allData, 8); data.CopyTo(allData, 8 + fileNameBytes.Length); //SEND FIRST PACKET TO SERVER server.Send(allData); totalSentDataSlot = 1; int totalDataSize = (int)bReader.BaseStream.Length; progress = (int)(((float)sentDataSize / totalDataSize) * 100); while (sentDataSize < progress =" (int)(((float)sentDataSize" enddata =" new" sentdatasize =" (int)bReader.BaseStream.Length;" progress =" (int)(((float)sentDataSize" receivedata =" new" recvlen =" server.Receive(receiveData);" presentoperation = "Data Sending Completed" status = "Access Denied by server end." status = "Address Already In Use." status = "Address Not Available." status = "Connection aborted by server." status = "Connection refused by server." status = "Connection Reset." status = "Destination Address Required." status = "Disconnecting." status = "Target Host is Down." status = "Target Host Not Found." status = "Target Host Unreachable." status = "In Progress." status = "Interrupted." status = "Invalid Argument." status = "Network Down." status = "Network Reset." status = "Network Unreachable." status = "No Buffer Space Available." status = "No Data." status = "Not Connected." status = "Not Initialized." status = "Not Socket." status = "Operation Aborted." status = "Socket already Shutdown." status = "Too Many Open Sockets." status = "Too Many Open Sockets." status = "Have some unknown problem in Socket." status =" ex.Message;" fbd =" new" outpathuserselected =" fbd.SelectedPath;" startfilereceivethread =" new" outpathuserselected =" ReceivePath;" startfilereceivethread =" new" server =" null;" bwriter =" null;" rcvfilename = "" filename = "" status = "" onlyfilename =" onlyFileNameObj.ToString();" presentoperation = "Data Compressing" length ="=" clientid =" DateTime.Now.Ticks.ToString();" clientidlenbytes =" BitConverter.GetBytes(clientId.Length);" clientidbytes =" Encoding.ASCII.GetBytes(clientId);" filenamelenbytes =" BitConverter.GetBytes(onlyFileName.Length);" filenamebytes =" Encoding.ASCII.GetBytes(onlyFileName);" alldata =" new" presentoperation = "Data Sending" serverip =" Dns.GetHostAddresses(SynchronusClient_ByteArr.ipAddress);" ipend =" new" server =" new" status = "" progress =" 0;" presentoperation = "" server =" (Socket)clientObject;" flag =" true;" data =" new" presentoperation = "Data Receiving" len =" server.Receive(data);" len ="=" strtemp =" Encoding.ASCII.GetString(data," strtemp ="=" status = "File yet not sent by Sender" presentoperation = "Data Receiving Not Started" progress =" 0;" totaldatalen =" BitConverter.ToInt32(data," filenamelen =" BitConverter.ToInt32(data," filename =" Encoding.ASCII.GetString(data," filecontentstartindex =" 4" receivedlen =" len;//" outpathdefault =" outPathDefault.Replace(" outpathuserselected =" outPathUserSelected.Replace(" fbd =" new" bwriter =" new" bwriter =" new" bwriter =" new" progress =" (int)(((float)receivedLen" len =" server.Receive(data);" val =" (float)receivedLen" progress =" (int)(((float)receivedLen" presentoperation = "Data Decompressing" rcvfilename =" ZipUnzip.LZO_Algorithm.LZO.Decompress(outPathDefault" rcvfilename =" ZipUnzip.LZO_Algorithm.LZO.Decompress(outPathUserSelected" orgfilename =" rcvFileName=" orgfilename =" orgFileName.Substring(orgFileName.IndexOf(" isserverrunning =" false;" presentoperation = "Data Receiving Completed" progress =" 100;" status = "Access Denied by server end." status = "Address Already In Use." status = "Address Not Available." status = "Connection aborted by server." status = "Connection refused by server." status = "Connection Reset." status = "Destination Address Required." status = "Disconnecting." status = "Target Host is Down." status = "Target Host Not Found." status = "Target Host Unreachable." status = "In Progress." status = "Interrupted." status = "Invalid Argument." status = "Network Down." status = "Network Reset." status = "Network Unreachable." status = "No Buffer Space Available." status = "No Data." status = "Not Connected." status = "Not Initialized." status = "Not Socket." status = "Operation Aborted." status = "Socket already Shutdown." status = "Too Many Open Sockets." status = "Too Many Open Sockets." status = "Have some unknown problem in Socket." status =" ex.Message;" style="">
In my last article I found some limitation and this code also quite large so I’ve write it’s modified code to run stored procedure. It’s more powerful and comparatively very low code and use is also very easy.
When you run a stored procedure by that code you need to call one function to run specific stored procedure and for every argument need to call just a function. Also here I’ve handle arraylist internally which was need to use externally by you. Also you can use ADO.Net ’senum to provide table’s field data type and parameter direction also you can use ado.net enum.
Hove ever I’m giving one sample code which you need to write. It’s very easy, just create an object of that class, then invoke these functions. Codes are below:
NOTE: When I am tring to publish that post then getting some problem for HTML rendering so I replace two character. please replace these two character. HTML opening tag with * and HTML closing tag with $.
Hi, Now I’m far from socket now Im giving some code with .Net and SQL Server related code, that code will help any one to run stored procedure, very easyly with little code. Now follow these code.
Create a table to test the stored procedure, I’m giving one example:
CREATE TABLE client( id int IDENTITY(1,1) NOT NULL, Name varchar(64) NULL, jdate datetime NULL )
Then insert some data into that table.
INSERT INTO client(Name,jdate)VALUES ('Taniya',getdate()) INSERT INTO client(Name,jdate)VALUES ('Jhuma',getdate()) INSERT INTO client(Name,jdate)VALUES ('Ganesh',getdate())
Then create a stored procedure to test my class, here I’m creating a stored procedure (sp) to update some data based on client id. Here store procedure’s parameters are @nextName and @id. Code is giving here:
create procedure UpdateName ( @nextName varchar(256), @id int ) as update client set name=@nextName where id=@id
Now sql server data base related job is done. Now will to c# related code.
First giving the code by that my class will be accessed to run that stored procedure. Just note that code, only that code will be required to run any stored procedure.
public class RunSP { /// *summary$ /// Run Store Procedure. /// */summary$ /// *param name="cnnStr"$Connection String of SQL server*/param$ public static void RunSP(string cnnStr) { //Create a array list to store parameter(s) with //parameter name, value & data type. ArrayList arLst = new ArrayList(); //Now send array list name, value & varchar, persingle //call of that function will create one stored parameter. SP.spArgumentsCollection(arLst, "@nextName", "Suman Biswas", "varchar"); SP.spArgumentsCollection(arLst, "@id", "2", "int"); //Now run stored procedure. SP.RunStoredProcedure(cnnStr, "UpdateName", arLst); } }
Here the actual class to run stored procedure, you can make all dll file by that code and then easily use this. Codes are below:
using System; using System.Collections.Generic; using System.Text; using System.Collections; using System.Data; using System.Data.SqlClient;
//That code has written by Suman Biswas, that code is running fine at my testing, //if any one get any problem please inform me. My email is sumanbiswas@aol.in namespace StoredProcedureLib { /// *summary$ /// This class helps to built SP argument. It can use directly to any SP. Created by Suman Biswas on 23rd Frb 2007. /// */summary$
public class SP {
internal class SPArgBuild { internal string parameterName = ""; internal string parameterValue = ""; /// *summary$ /// Write full data type, such as SqlDBType.VarChar. /// */summary$ internal string pramValueType = "";
/// *summary$ /// This function built an Array List, which is collection of some SP parameter's Name, Value and Data type. /// */summary$ /// *param name="arrLst"$Array List which will store all argument.*/param$ /// *param name="spParmName"$SP Argument Parameter Name.*/param$ /// *param name="spParmValue"$SP Argument Parameter Value.*/param$ /// *param name="spPramValueType"$Parameter value type EXACTLY same as SqlDBType. E.g. 'SqlDbType.BigInt' will 'BigInt'. */param$ /// *returns$*/returns$ public static ArrayList spArgumentsCollection(ArrayList arrLst, string spParmName, string spParmValue, string spPramValueType) { SPArgBuild spArgBuiltObj = new SPArgBuild(spParmName, spParmValue, spPramValueType); arrLst.Add(spArgBuiltObj); return arrLst; }
/// *summary$ /// Run a stored procedure of Select SQL type. /// */summary$ /// *param name="dbConnStr"$Connection String to connect Sql Server*/param$ /// *param name="ds"$DataSet which will return after filling Data*/param$ /// *param name="spName"$Stored Procedure Name*/param$ /// *param name="spPramArrList"$Parameters in ArrayList*/param$ /// *returns$Return DataSet after filing data by SQL.*/returns$ public static DataSet RunStoredProcedure(string dbConnStr, DataSet ds, string spName, ArrayList spPramArrList) { SqlConnection conn = new SqlConnection(dbConnStr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn; cmd.CommandText = spName;
string spPramName = ""; string spPramValue = ""; string spPramDataType = ""; for (int i = 0; i * spPramArrList.Count; i++) { spPramName = ((SPArgBuild)spPramArrListi).parameterName; spPramValue = ((SPArgBuild)spPramArrListi).parameterValue; spPramDataType = ((SPArgBuild)spPramArrListi).pramValueType; SqlParameter pram = null; #region SQL DB TYPE AND VALUE ASSIGNMENT switch (spPramDataType.ToUpper()) { case "BIGINT": pram = cmd.Parameters.Add(spPramName, SqlDbType.BigInt); pram.Value = spPramValue; break;
case "BINARY": pram = cmd.Parameters.Add(spPramName, SqlDbType.Binary); pram.Value = spPramValue; break;
case "BIT": pram = cmd.Parameters.Add(spPramName, SqlDbType.Bit); pram.Value = spPramValue; break;
case "CHAR": pram = cmd.Parameters.Add(spPramName, SqlDbType.Char); pram.Value = spPramValue; break;
case "DATETIME": pram = cmd.Parameters.Add(spPramName, SqlDbType.DateTime); pram.Value = spPramValue; break;
case "DECIMAL": pram = cmd.Parameters.Add(spPramName, SqlDbType.Decimal); pram.Value = spPramValue; break;
case "FLOAT": pram = cmd.Parameters.Add(spPramName, SqlDbType.Float); pram.Value = spPramValue; break;
case "IMAGE": pram = cmd.Parameters.Add(spPramName, SqlDbType.Image); pram.Value = spPramValue; break;
case "INT": pram = cmd.Parameters.Add(spPramName, SqlDbType.Int); pram.Value = spPramValue; break;
case "MONEY": pram = cmd.Parameters.Add(spPramName, SqlDbType.Money); pram.Value = spPramValue; break;
case "NCHAR": pram = cmd.Parameters.Add(spPramName, SqlDbType.NChar); pram.Value = spPramValue; break;
case "NTEXT": pram = cmd.Parameters.Add(spPramName, SqlDbType.NText); pram.Value = spPramValue; break;
case "NVARCHAR": pram = cmd.Parameters.Add(spPramName, SqlDbType.NVarChar); pram.Value = spPramValue; break;
case "REAL": pram = cmd.Parameters.Add(spPramName, SqlDbType.Real); pram.Value = spPramValue; break;
case "SMALLDATETIME": pram = cmd.Parameters.Add(spPramName, SqlDbType.SmallDateTime); pram.Value = spPramValue; break;
case "SMALLINT": pram = cmd.Parameters.Add(spPramName, SqlDbType.SmallInt); pram.Value = spPramValue; break;
case "SMALLMONEY": pram = cmd.Parameters.Add(spPramName, SqlDbType.SmallMoney); pram.Value = spPramValue; break;
case "TEXT": pram = cmd.Parameters.Add(spPramName, SqlDbType.Text); pram.Value = spPramValue; break;
case "TIMESTAMP": pram = cmd.Parameters.Add(spPramName, SqlDbType.Timestamp); pram.Value = spPramValue; break;
case "TINYINT": pram = cmd.Parameters.Add(spPramName, SqlDbType.TinyInt); pram.Value = spPramValue; break;
case "UDT": pram = cmd.Parameters.Add(spPramName, SqlDbType.Udt); pram.Value = spPramValue; break;
case "UMIQUEIDENTIFIER": pram = cmd.Parameters.Add(spPramName, SqlDbType.UniqueIdentifier); pram.Value = spPramValue; break;
case "VARBINARY": pram = cmd.Parameters.Add(spPramName, SqlDbType.VarBinary); pram.Value = spPramValue; break;
case "VARCHAR": pram = cmd.Parameters.Add(spPramName, SqlDbType.VarChar); pram.Value = spPramValue; break;
case "VARIANT": pram = cmd.Parameters.Add(spPramName, SqlDbType.Variant); pram.Value = spPramValue; break;
