When we write the program, especially when the database application is, it will often encounter such situations: For a given table, write this table corresponding to the class (with a fashionable saying is a business entity class), Class data members are all fields, and the class contains the addition modification deletion such as the table. Also, for a given stored procedure, you must complete access data or other database operations based on stored procedures. The following code is that we usually have to complete:
Table business entity
Private int IID;
Public Int ID
{
get
{
Return IID;
}
set
{
IID = value;
}
}
PRIVATE STRING STRNAME;
Public String Name
{
get
{
Return Strname;
}
set
{
Strname = value;
}
}
PRIVATE STRING STRCODE;
Public String Code
{
get
{
Return Strcode;
}
set
{
Strcode = value;
}
}
PRIVATE STRING STRDESCRIPTION;
Public String Description
{
get
{
Return strdescription;
}
set
{
STRDESCRIPTION = VALUE;
}
}
Private int ifatherid;
Public int fitherid
{
get
{
Return ifatherId;
}
set
{
ifatherid = value;
}
}
Private int iness;
Public int Type
{
get
{
Return ITYPE;
}
set
{
ITYPE = VALUE;
}
}
Private int userid;
Public int userid
{
get
{
Return iUserid;
}
set
{
iuserid = value;
}
}
Ublic bool add ()
{
SqlConnection conn = sqlconn.instance (). Connection;
String strsql = "INSERT INTO BOOK (ID, Name, Code, Description, FatherID, Type, UserID"
"VALUES (@ID, @name, @code, @Description, @fatherid, @Type, @Userid);
Sqlcommand command = new SQLCOMMAND (strsql, conn);
Command.Parameters.Add ("@ id", sqldbtype.int);
Command.Parameters ["@ id"]. value = iid;
Command.Parameters.add ("@ name", sqldbtype.nvarchar, 50);
IF (strname! = null)
Command.Parameters ["@ name"]. value = strname;
Else
Command.Parameters ["@ Name"]. Value = dbnull.value;
Command.Parameters.Add ("@ code", sqldbtype.nvarchar, 255); if (strcode! = null)
Command.Parameters ["@ code"]. value = strcode;
Else
Command.Parameters ["@ code"]. Value = dbnull.value;
Command.Parameters.Add ("@ description", sqldbtype.nvarchar, 255);
IF (strdescription! = null)
Command.Parameters ["@ Description"]. Value = strdescription;
Else
Command.Parameters ["@ Description"]. Value = dbnull.value;
Command.Parameters.Add ("@ FatherID", SqldbType.InT);
Command.Parameters ["@ FatherID"]. value = ifatherid;
Command.Parameters.Add ("@ type", sqldbtype.int);
Command.Parameters ["@ type"]. value = iodes;
Command.Parameters.Add ("@ userid", sqldbtype.int);
Command.parameters ["@ userid"]. value = iuserid;
Try
{
Cn.open ();
Command.executenonQuery ();
Return True;
}
Catch (Exception E)
{
"New Exception"; " ERROR IN THE DATABASE");
}
Finally
{
CONN.CLOSE ();
}
}
Public bool modify ()
{
SqlConnection conn = sqlconn.instance (). Connection;
String strsql = "Update book set id = @ID, name = @name, code = @code, description = @description, fatherid = @fatherid, type = @Type, userid = @userid
"where id = @ id";
Sqlcommand command = new SQLCOMMAND (strsql, conn);
Command.Parameters.Add ("@ id", sqldbtype.int);
Command.Parameters ["@ id"]. value = iid;
Command.Parameters.add ("@ name", sqldbtype.nvarchar, 50);
Command.Parameters ["@ name"]. value = strname;
Command.Parameters.Add ("@ code", sqldbtype.nvarchar, 255); command.parameters ["@ code"]. value = strcode;
Command.Parameters.Add ("@ description", sqldbtype.nvarchar, 255);
Command.Parameters ["@ Description"]. Value = strdescription;
Command.Parameters.Add ("@ FatherID", SqldbType.InT);
Command.Parameters ["@ FatherID"]. value = ifatherid;
Command.Parameters.Add ("@ type", sqldbtype.int);
Command.Parameters ["@ type"]. value = iodes;
Command.Parameters.Add ("@ userid", sqldbtype.int);
Command.parameters ["@ userid"]. value = iuserid;
Try
{
Cn.open ();
Command.executenonQuery ();
Return True;
}
Catch (Exception E)
{
"New Exception"; " ERROR IN THE DATABASE");
}
Finally
{
CONN.CLOSE ();
}
}
Take a look at the stored procedure:
Public Bool EXESP_DDMS_MODIFY_TRX (
INT AIPRSN_TRX_NO,
INT aiult_incid_no,
INT AIPRSN_TRX_STATUS_CD,
DateTime adttrx_cmpl_dt,
String astreme_addr)
{
SqlConnection conn = sqlconn.instance (). Connection;
String strspname = "ddms_modify_trx";
Sqlcommand command = new SQLCOMMAND (STRSPNAME, CONN);
Command.commandtype = commandtype.storedProcedure;
Command.Parameters.Add ("@ PRSN_TRX_NO", SQLDBTYPE.SMALLINT);
Command.Parameters ["@ PRSN_TRX_NO"]. Value = Aiprsn_TRX_NO;
Command.Parameters.Add ("@ Ult_incid_no", SqldbType.It);
Command.Parameters ["@ ULT_INCID_NO"]. Value = aiult_incid_no;
Command.Parameters.Add ("@ pRSN_TRX_STATUS_CD", SQLDBTYPE.INT);
Command.Parameters ["@ PRSN_TRX_STATUS_CD"]. Value = AIPRSN_TRX_STATUS_CD;
Command.Parameters.Add ("@ TRX_CMPL_DT", SqldbType.Datetime); if (adttrx_cmpl_dt! = datetime.minvalue)
Command.Parameters ["@ TRX_CMPL_DT"]. Value = adttrx_cmpl_dt;
Else
Command.Parameters ["@ TRX_CMPL_DT"]. Value = dbnull.value;
Command.Parameters.Add ("@ email_addr", sqldbtype.varchar, 70);
IF (ASTREMAIL_ADDR! = NULL)
Command.Parameters ["@ email_addr"]. value = astreme_addr;
Else
Command.Parameters ["@ email_addr"]. Value = dbnull.value;
Try
{
Cn.open ();
Command.executenonQuery ();
Return True;
}
Catch (Exception E)
{
"New Exception"; " ERROR IN THE DATABASE");
}
Finally
{
CONN.CLOSE ();
}
}
The code for processing the table is also very long, but that is just 7 fields. If a table contains two thirty fields, I am afraid the code is longer, and the same, check whether the code you write is wrong is also a painful process.
However, there is no difficulty of writing these code itself, and many work are repeated, which can't do not inspire us to complete these cumbersome errors.
If let's design a program to complete the code above, what should we do?
To complete the above work, we should first find the table or stored procedure we have to handle.
We can also implement:
List all database servers
Public Static ArrayList GetServerList ()
{
Arraylist alservers = new arraylist ();
SqldMo.application sqlapp = new sqldmo.applicationclass ();
Try
{
SqldMo.nameList ServerList = SQLAPP.ListavailableSqlServers ();
For (int i = 1; i <= serverList.count; i )
{
Alservers.Add (ServerList.Item (i));
}
}
Catch (Exception E)
{
Throw ("Note the database server list error:" E.MESSAGE));
}
Finally
{
SQLAPP.QUIT ();
}
Return alservers;
}
In this way, all database server labyrinth condenses exist in ArrayList, we can use a drop-down list to list all the servers, allowing users to choose one of him.
2. Get all database lists of selected servers
To complete this job, we need users to enter the username and password of this database server, the code is as follows:
Public static arraylist getdblist (String struserName, string strusername, string strpwd) {
ArrayList aldbs = new arraylist ();
SqldMo.application sqlapp = new sqldmo.applicationclass ();
Sqldmo.sqlserver svr = new sqldmo.sqlserverclass ();
Try
{
Svr.connect (struserName, strusername, strpwd);
Foreach (SqldMo.database DB in Svr.Databases)
{
IF (DB.NAME! = NULL)
Aldbs.Add (db.name);
}
}
Catch (Exception E)
{
Throw (New Exception: " E.MESSAGE));
}
Finally
{
Svr.disconnect ();
SQLAPP.QUIT ();
}
Return ALDBS;
}
This gives a list of databases, you can also allow users to choose a database.
3. List all user tables and stored procedures for selected databases
In this step, we don't have to use SQLDMO. We do this work by reading the record of the selected database, of course, the completion of this job requires the following information: the database server name, user name, password, database name, etc.
Public Static ArrayList GetTableList (String Strservername, String Strusername, String Strpwd)
{
String strconstr = "data source =" strserName "; initial catalog =" strdbname
"; PASSWORD =" strPwd "; persist security info = true; user id =" strusername;
SqlConnection conn = new SqlConnection (STRCONSTR);
ArrayList altbs = new arraylist ();
String strsql = "select * from sysobjects where (xtype = 'u' or xtype = 'p') And category <> 2 Order by name";
SQLCommand Comm = New Sqlcommand (strsql, conn);
SqlDataReader SR = NULL;
Try
{
Cn.open ();
SR = comm.executeReader ();
While (sr.read ())
{
String strname = sr ["name"]. TOSTRING ();
Bool BLTABLE = SR ["Xtype"]. TOSTRING (). Trim (). Toupper () == "U"? True: false;
TableInfo TB = New TableInfo (STRNAME, BLTABLE);
Altbs.Add (TB);
}
Catch (Exception Err)
{
Throw (New Exception "indicates a list error:" Err.Message));
}
Finally
{
IF (SR! = null)
{
sr.close ();
SR = NULL;
}
CONN.CLOSE ();
}
Return altbs;
}
In this way, we get all the user tables and stored procedures, pay attention to this sentence:
Select * from sysobjects where (xtype = 'u' or xtype = 'p') and category <> 2 ORDER BY NAME, this is a critical SQL statement, where xtype = 'u' represents the user table, Xtype = 'P' The stored procedure, category <> 2 is not a system stored procedure.
The next step is to obtain all the parameters of all fields or stored procedures in the selected table.
We get what we want from the table syscolumns:
STRTABLENAME = tbinfo.name;
String strsql = "SELECT * from syscolumns where id = ("
"Select ID from sysobjects where name = '" strtablename ")";
SqlDataAdapter Sa = New SqldataAdapter (strsql, conn);
DataSet DS = New Dataset ();
Sa.fill (DS);
This way we put all the fields of a table (or stored procedure) in the result set DS.
There is a field xtype in syscolumns, which represents the data type of the column, defines two functions, depending on the value of Xtype, the data type and SQLDB data type in the corresponding DOTNET:
Private string gettype (int ney)
{
String strresult = "";
Switch (iTYPE)
{
Case 34:
Strresult = "byte []";
Break;
Case 35:
Case 99:
Case 167:
Case 175:
Case 231:
Case 239:
Strresult = "string";
Break;
Case 48:
Case 52:
Case 56:
Case 127:
Strresult = "int";
Break;
Case 58:
Case 61:
Strresult = "datetime";
Break;
Case 59:
Strresult = "single";
Break;
Case 60:
Case 106:
Case 108:
Case 122:
Strresult = "Decimal";
Break;
Case 62:
Strresult = "double";
Break;
Case 104:
Strresult = "bool";
Break;
DEFAULT:
Strresult = "none"; // "unknow"; Break;
}
Return Strresult ""
}
Private string getsqldbtype (int ney)
{
String strresult = "";
Switch (iTYPE)
{
Case 34:
Strresult = "sqldbtype.image";
Break;
Case 35:
Strresult = "sqldbtype.text";
Break;
Case 48:
Strresult = "sqldbtype.tinyint";
Break;
Case 52:
Strresult = "sqldbtype.smallint";
Break;
Case 56:
Strresult = "sqldbtype.int";
Break;
Case 58:
Strresult = "sqldbtype.smalldatetime";
Break;
Case 59:
Strresult = "sqldbtype.real";
Break;
Case 60:
Strresult = "sqldbtype.money";
Break;
Case 61:
Strresult = "sqldbtype.datetime";
Break;
Case 62:
Strresult = "sqldbtype.float";
Break;
Case 99:
Strresult = "sqldbtype.ntext";
Break;
Case 104:
Strresult = "sqldbtype.bit";
Break;
Case 106:
Strresult = "sqldbtype.deecimal";
Break;
Case 108:
Strresult = "sqldbtype.deecimal";
Break;
Case 122:
Strresult = "sqldbtype.smallmoney";
Break;
Case 127:
Strresult = "sqldbtype.bigint";
Break;
Case 165:
Strresult = "sqldbtype.varbinary";
Break;
Case 167:
Strresult = "sqldbtype.varchar";
Break;
Case 173:
Strresult = "sqldbtype.binary";
Break;
Case 175:
Strresult = "sqldbtype.char";
Break;
Case 189:
Strresult = "sqldbtype.timestamp";
Break;
Case 231:
Strresult = "sqldbtype.nvarchar";
Break;
Case 239:
Strresult = "sqldbtype.nchar";
Break;
DEFAULT:
Strresult = "none"; // "unknow"; Break;
}
Return Strresult ""
}
Of course, there is a function first introduced it, there is a specific usage of it:
Private string gettypeshort (int iness)
{
String strresult = "";
Switch (ITYPE) .trim ())
{
Case "Byte []":
Strresult = "img";
Break;
Case "String":
Strresult = "STR";
Break;
Case "int":
Strresult = "i";
Break;
Case "datetime":
Strresult = "dt";
Break;
Case "Single":
Strresult = "sig";
Break;
Case "Decimal":
Strresult = "dec";
Break;
Case "double":
Strresult = "db";
Break;
Case "BOOL":
Strresult = "BL";
Break;
DEFAULT:
Strresult = "unknow";
Break;
}
Return Strresult;
}
Now has the above introduction and function foundation, we can gradually implement our goals:
1. Generate data members of the data entity class
Let's take a look, for a field ID, shaping, we define this:
Private int IID;
Public Int ID
{
get
{
Return IID;
}
set
{
IID = value;
}
}
Where IID indicates the data type field name, where the first letter of the field name is capitalized, the code is implemented as follows:
Private String Buildmember (DataSet DS)
{
StringBuilder SB = New StringBuilder ();
Foreach (DataRow Dr in Ds.Tables [0] .ROWS)
{
Int itype = (byte) DR ["xtype"];
String straType = gettype (iTYPE);
String strname = DR ["name"]. TOSTRING ();
Strname = Upfirstchar (STRNAME);
String strshorttype = gettypeshort (iTYPE);
String strprivateeline = "private" strtype strshorttype strname "; / r / n";
Sb.append (strprivate);
String strpublicLine = "public" strtype strname "/ r / n";
sb.append (strpublicLine);
sb.append ("{/ r / n");
sb.append ("/ tget / r / n");
Sb.append ("/ t {/ r / n");
Sb.append ("/ t / treturn" strshorttype strname "; / r / n");
Sb.append ("/ t} / r / n");
Sb.append ("/ tset / r / n");
Sb.append ("/ t {/ r / n");
Sb.append ("/ t / t" strshorttype strname "= value; / r / n");
Sb.append ("/ t} / r / n");
sb.append ("} / r / n");
sb.append ("/ r / n");
sb.append ("/ r / n");
}
Return sb.toString ();
}
The DS in the parameter dataset DS is obtained by the SA.FILL (DS) of the previous page, DR [""] represents a field name, DR ["xtype"] is a field type.
The Upfirstchar function is responsible for putting the first letter of the string:
Private string Upfirstchar (String Strvalue)
{
Return strval.substring (0,1) .toupper () strvalue.substring (1, strvalue.length - 1);
}
This generates data members of the data entity.
2. Generate add method
The Add method is a bit difficult, which can refer to the program of the first page, and we are through the method of parameters to implement add. One thing to note is that the parameter value is empty when assigning parameters. The code is implemented as follows:
Private string buildaddfunction (DataSet DS)
{
StringBuilder SB = New StringBuilder ();
SB.Append ("Public Bool Add () / R / N");
sb.append ("{/ r / n");
Sb.append ("/ tsqlconnection conn = sqlconn.instance (). Connection; / r / n");
sb.append ("/ r / n");
String strinsert = "/" INSERT INTO " STRTABLENAME " (";
String strfiledlist = "";
String strparamlist = "";
Foreach (DataRow Dr in Ds.Tables [0] .ROWS)
{
String strname = DR ["name"]. TOSTRING ();
StrfiledList = StrfiledList Strname ",";
Strparamlist = strparamlist "@" strname ",";
}
StrfiledList = StrfiledList.trim (). Trimend (','); strparamlist = strparamlist.trim (). Trimend (',');
Sb.append ("/ tstring strsql =" strinsert strfiledlist ") /" / r / n ");
Sb.append ("/ t /" VALUES / "; / r / n");
sb.append ("/ r / n");
Sb.append ("/ tsqlcommand command = new sqlcommand (strsql, conn); / r / n");
sb.append ("/ r / n");
Foreach (DataRow Dr in Ds.Tables [0] .ROWS)
{
String strname = DR ["name"]. TOSTRING ();
String strsqltype = getsqldbtype ((byte) DR ["xtype"]);
String strshorttype = gettypeshort ((byte) DR ["xtype"]);
String stritalueename = strshorttype upfirstchar (strname);
INT Ilen = (INT16) DR ["prec"];
String strlen = "";
IF (strshorttype == "str")
Strlen = Strlen "," Ilen;
String strcommandname = "/tcommand.parameters.add (/"@" strname "/", " strsqltype strlen "); / r / n ";
String strcommandvalue = "/tcommand.parameters[/"@" strname "/"].value =" strValuenge "; / r / n";
String strcommandnullvalue = "/tcommand.Parameters[/"@" strname "/"].value = dbnull.value; / r / n";
Sb.append (strcommandname);
IF (strshorttype == "dt")
Sb.append ("/ TIF (" Strvaluename "! = DATETIME.MINVALUE) / R / N");
Else IF (strshorttype == "str")
Sb.append ("/ TIF (" StrvalueName "! = null) / r / n");
ELSE IF (strshorttype == "img") sb.append ("/ tif (" strValueename "! = null) / r / n");
Else
sb.append ("");
sb.append (strcommandvalue);
IF (strshorttype == "dt" || strshorttype == "str" || strshorttype == "img")
{
sb.append ("/ telse / r / n");
sb.append (strcommandnullvalue);
}
sb.append ("/ r / n");
}
SB.Append (addcatchString ());
sb.append ("} / r / n");
Return sb.toString ();
}
3. EDIT, DELETE implementation method Similar to add, it is not detailed, the EDIT method code is as follows:
Private String BuildModifyFunction (DataSet DS)
{
StringBuilder SB = New StringBuilder ();
SB.Append ("Public Bool Modify () / R / N");
sb.append ("{/ r / n");
Sb.append ("/ tsqlconnection conn = sqlconn.instance (). Connection; / r / n");
String strModify = "string strsql = /" update " strtablename " set ";
String strparamlist = "";
Foreach (DataRow Dr in Ds.Tables [0] .ROWS)
{
String strname = DR ["name"]. TOSTRING ();
Strparamlist = strparamlist strname "@" strname ",";
}
Strparamlist = strparamlist.trim (). Trimend (',');
sb.append ("/ t" strmodify strparamlist "/" / r / n ");
Sb.append ("/ t /" where id = @ ID / "; / r / n");
Sb.append ("/ tsqlcommand command = new sqlcommand (strsql, conn); / r / n");
Foreach (DataRow Dr in Ds.Tables [0] .ROWS)
{
String strname = DR ["name"]. TOSTRING ();
String strsqltype = getsqldbtype ((byte) DR ["xtype"]);
String strshorttype = getTypeshort (Byte) DR ["xtype"]); string string stronguename = strshorttype upfirstchar (Strname);
INT Ilen = (INT16) DR ["prec"];
String strlen = "";
IF (strshorttype == "str")
Strlen = Strlen "," Ilen;
String strcommandname = "/tcommand.parameters.add (/"@" strname "/", " strsqltype strlen "); / r / n ";
String strcommandvalue = "/tcommand.parameters[/"@" strname "/"].value =" strValuenge "; / r / n";
Sb.append (strcommandname);
sb.append (strcommandvalue);
sb.append ("/ r / n");
}
SB.Append (addcatchString ());
sb.append ("} / r / n");
Return sb.toString ();
}
4. Generate the execution method of the stored procedure and the return result method. The parameters of the stored procedure are also detailed in the syscolumns table, which is also recorded for each parameter, but I don't consider the direction of the parameters in this function. Of course, I have to consider going in. It is not very troublesome.
Private String Buildspexec (Dataset DS)
{
StringBuilder SB = New StringBuilder ();
String strfuncparam = ""
Foreach (DataRow Dr in Ds.Tables [0] .ROWS)
{
Int itype = (byte) DR ["xtype"];
String strname = Upfirstchar (DR ["name"]. TOSTRING (). Trimstart ('@'));
String straType = gettype (iTYPE);
String strsqltype = getsqldbtype (iTYPE);
String strshorttype = gettypeshort (iTYPE);
Strfuncparam = strfuncparam "/ r / n / t / t" strtype "a" strshorttype strname ","
}
Strfuncparam = strfuncparam.trimend (',');
String strdef = "public bool eXesp_" strtablename "(" strfuncparam ") / r / n";
Sb.append (strdef); sb.append ("{/ r / n");
Sb.append ("/ tsqlconnection conn = sqlconn.instance (). Connection; / r / n");
sb.append ("/ r / n");
Sb.append ("/ tstring strspname = /" " stratablename " / "; / r / n");
Sb.append ("/ tsqlcommand command = new sqlcommand (strspname, conn); / r / n");
Sb.append ("/ tcommand.commandtype = commandtype.storedprocedure; / r / n");
sb.append ("/ r / n");
Foreach (DataRow Dr in Ds.Tables [0] .ROWS)
{
Int itype = (byte) DR ["xtype"];
String strparaname = DR ["name"]. TOSTRING ();
String strname = Upfirstchar (StrParaname.trimstart ('@'));
String strsqltype = getsqldbtype (iTYPE);
String strshorttype = gettypeshort (iTYPE);
String strign = "a" strshorttype strname;
INT Ilen = (INT16) DR ["prec"];
String strlen = "";
IF (strshorttype == "str")
Strlen = Strlen "," Ilen;
String strcommandadd = "/tcommand.Parameters.add(/" " strparaname " / "," strsqltype strlen "); / r / n";
String strcommandvalue = "/TCommand.Parameters[/" " strparaname " /"].value= " strval "; / r / n ";
String strcommandnull = "/tcommand.parameters[/" " strparaname " /"].value= dbnull.value; / r / n ";
Sb.append (strcommandadd);
IF (strshorttype == "dt")
Sb.append ("/ TIF (" Strvalue "! = datetime.minvalue) / r / n / t");
Else IF (strshorttype == "str")
Sb.append ("/ TIF (" Strvalue "! = NULL) / R / N / T"); Else IF (strshorttype == "img")
Sb.append ("/ TIF (" Strvalue "! = NULL) / R / N / T");
Else
sb.append ("");
sb.append (strcommandvalue);
IF (strshorttype == "dt" || strshorttype == "str" || strshorttype == "img")
{
Sb.append ("/ telse / r / n / t");
Sb.append (strcommandnull);
}
sb.append ("/ r / n");
}
SB.Append (addcatchString ());
sb.append ("} / r / n");
Return sb.toString ();
}
The functions of the resulting result set for the stored procedure are as follows:
Private String Buildspgetdata (DataSet DS)
{
StringBuilder SB = New StringBuilder ();
String strfuncparam = ""
Foreach (DataRow Dr in Ds.Tables [0] .ROWS)
{
Int itype = (byte) DR ["xtype"];
String strname = Upfirstchar (DR ["name"]. TOSTRING (). Trimstart ('@'));
String straType = gettype (iTYPE);
String strsqltype = getsqldbtype (iTYPE);
String strshorttype = gettypeshort (iTYPE);
Strfuncparam = strfuncparam "/ r / n / t / t" strtype "a" strshorttype strname ","
}
Strfuncparam = strfuncparam.trimend (',');
String strdef = "public dataset querysp_" strtablename "(" strfuncparam ") / r / n";
Sb.append (strdef);
sb.append ("{/ r / n");
Sb.append ("/ tsqlconnection conn = sqlconn.instance (). Connection; / r / n");
sb.append ("/ r / n");
Sb.append ("/ tstring strspname = /" " stratablename " / "; / r / n");
Sb.append ("/ tsqlcommand command = new sqlcommand (strspname, conn); / r / n"); sb.append ("/ tcommand.commandtype = commandtype.storedProcedure; / r / n");
sb.append ("/ r / n");
Foreach (DataRow Dr in Ds.Tables [0] .ROWS)
{
Int itype = (byte) DR ["xtype"];
String strparaname = DR ["name"]. TOSTRING ();
String strname = Upfirstchar (StrParaname.trimstart ('@'));
String strsqltype = getsqldbtype (iTYPE);
String strshorttype = gettypeshort (iTYPE);
String strign = "a" strshorttype strname;
INT Ilen = (INT16) DR ["prec"];
String strlen = "";
IF (strshorttype == "str")
Strlen = Strlen "," Ilen;
String strcommandadd = "/tcommand.Parameters.add(/" " strparaname " / "," strsqltype strlen "); / r / n";
String strcommandvalue = "/TCommand.Parameters[/" " strparaname " /"].value= " strval "; / r / n ";
String strcommandnull = "/tcommand.parameters[/" " strparaname " /"].value= dbnull.value; / r / n ";
Sb.append (strcommandadd);
IF (strshorttype == "dt")
Sb.append ("/ TIF (" Strvalue "! = datetime.minvalue) / r / n / t");
Else IF (strshorttype == "str")
Sb.append ("/ TIF (" Strvalue "! = NULL) / R / N / T");
Else IF (strshorttype == "img")
Sb.append ("/ TIF (" Strvalue "! = NULL) / R / N / T");
Else
sb.append ("");
sb.append (strcommandvalue);
IF (strshorttype == "dt" || strshorttype == "str" || strshorttype == "img") {
Sb.append ("/ telse / r / n / t");
Sb.append (strcommandnull);
}
sb.append ("/ r / n");
}
Sb.append ("/ tsqldataadapter sdaresisult = new sqldataadapter (command); / r / n");
Sb.append ("/ tdatanet ds = new dataset (); / r / n");
sb.append (addcatchQueryString ());
sb.append ("} / r / n");
Return sb.toString ();
}
Below is the result of the result set:
Public Dataset QuerySP_DDMS_GETBANK
INT aiprsn_id)
{
SqlConnection conn = sqlconn.instance (). Connection;
String strspname = "ddms_getbank";
Sqlcommand command = new SQLCOMMAND (STRSPNAME, CONN);
Command.commandtype = commandtype.storedProcedure;
Command.Parameters.Add ("@ PRSN_ID", SQLDBTYPE.INT);
Command.Parameters ["@ PRSN_ID"]. Value = aiprSN_ID;
SqlDataAdapter SDARESULT = New SqlDataAdapter (Command);
DataSet DS = New Dataset ();
Try
{
SDARESULT.FILL (DS);
}
Catch (Exception E)
{
"New Exception"; " ERROR IN THE DATABASE");
}
Finally
{
SDareSult.dispose ();
}
Return DS;
}
There are also two functions used in the program, as shown below:
Private string addcatchstring ()
{
StringBuilder SB = New StringBuilder ();
sb.append ("/ tTRY / R / N");
Sb.append ("/ t {/ r / n");
sb.append ("/ t / tconn.open (); / r / n");
Sb.append ("/ t / tcommand.executenonury (); / r / n");
Sb.append ("/ t / treturn true; / r / n");
Sb.append ("/ t} / r / n");
Sb.append ("/ Tcatch (Exception E) / R / N");
Sb.append ("/ t {/ r / n");
Sb.append ("/ T / TTHROW (NEW EXCEPTION); / R / N"); / r / n ");
Sb.append ("/ t} / r / n"); sb.append ("/ tfinalLY / R / N");
Sb.append ("/ t {/ r / n");
sb.append ("/ t / tconn.close (); / r / n");
Sb.append ("/ t} / r / n");
Return sb.toString ();
}
Private string addcatchQueryString ()
{
StringBuilder SB = New StringBuilder ();
sb.append ("/ tTRY / R / N");
Sb.append ("/ t {/ r / n");
Sb.append ("/ t / tsdaresult.fill (ds); / r / n");
Sb.append ("/ t} / r / n");
Sb.append ("/ Tcatch (Exception E) / R / N");
Sb.append ("/ t {/ r / n");
Sb.append ("/ T / TTHROW (NEW EXCEPTION); / R / N"); / r / n ");
Sb.append ("/ t} / r / n");
Sb.append ("/ tfinalLy / r / n");
Sb.append ("/ t {/ r / n");
sb.append ("/ t / tsdaresisult.dispose (); / r / n");
Sb.append ("/ t} / r / n");
Sb.append ("/ Treturn DS; / R / N");
Return sb.toString ();
}
By the way, a piece of code started with this program is to be generated by this program. Of course, this approach has to be improved, I hope everyone will correct.
