Execute SQL Server store procedure C # ADO. Net wrapper Code Generator

Namespace microshaoft
{
Using system;
Using system. IO;
Using system. Data;
Using system. Data. sqlclient;
Using system. codedom;
Using system. codedom. compiler;
Using Microsoft. CSHARP;
Using microshaoft. Data;
Public class class1
{
[Stathread]
Static void main (string [] ARGs)
{
String spname = "sp_test ";
// Spname = ARGs [0];
String _ connectionstring = @ "Application name = test; Integrated Security = sspi; persist Security info = false; initial catalog = vnet3center; Data Source =. \ sqlexpress ";
Sqlconnection SC = new sqlconnection (_ connectionstring );
String S = generatecode (spname, true, SC, "microshaoft", "class1 ");
// String S = "asdsad ";
System. Windows. Forms. clipboard. setdataobject (s );
Console. writeline (s );
}
Public static string generatecode
(
String spname
, Bool includereturnvalueparameter
, Sqlconnection connection
, String namespace
, String classname
)
{
Sqlparameter [] SPA = sqlhelperparametercache. getspparameterset (connection, spname );
Int length = spa. length;
 
 
 
Csharpcodeprovider provider = new csharpcodeprovider ();
// Icodegenerator generator = provider. creategenerator ();
 
Codecompileunit CCU = new codecompileunit ();
Codenamespace Cn = new codenamespace (namespace); // Add a namespace
CN. Imports. Add (New codenamespaceimport ("system "));
CN. Imports. Add (New codenamespaceimport ("system. Data "));
CN. Imports. Add (New codenamespaceimport ("system. Data. sqlclient "));
// CN. Imports. Add (New codenamespaceimport ("microshaoft. Data "));
Codetypedeclaration CTD = new codetypedeclaration (classname );
System. codedom. codememberfield CMF = new codememberfield (New codetypereference (typeof (string), "_ connectionstring ");
CMF. Attributes = memberattributes. Static | memberattributes. Public;
CMF. initexpression = new codesnippetexpression ("@ \" "+ connection. connectionstring +" \ ""); // new codeprimitiveexpression (connection. connectionstring );
 
CTD. members. Add (CMF );
// Int rows = spa. length;
Codemembermethod CMM = new codemembermethod ();
Codesnippetstatement CSS = NULL;
CSS = new codesnippetstatement ("sqlconnection connection = new sqlconnection (_ connectionstring );");
CMM. Statements. Add (CSS );
// Css = new codesnippetstatement ("sqlparameter [] parameters = new sqlparameter [" + (Length + 1). tostring () + "];");
// CMM. Statements. Add (CSS );
CSS = new codesnippetstatement (string. Format ("sqlcommand command = new sqlcommand (\" {0} \ ", connection);", spname ));
CMM. Statements. Add (CSS );
CSS = new codesnippetstatement ("command. commandtype = commandtype. storedprocedure ;");
CMM. Statements. Add (CSS );
CMM. returntype = new codetypereference (typeof (INT ));
CMM. Name = "execproc _" + spname;
CMM. Attributes = memberattributes. Public | memberattributes. Final | memberattributes. Static;
Int I = 1;
Codestatementcollection CSC = new codestatementcollection ();
Foreach (sqlparameter parameter in spa)
{
String paramname = "P _" + parameter. parametername. substring (1 );
String paramtypename = gettypename (parameter. sqldbtype );
Codetypereference paramtype = new codetypereference (paramtypename );
// Paramtype. tostring ();
Codeparameterdeclarationexpression cpvdf = new codeparameterdeclarationexpression (paramtype, paramname );
// Css = new codesnippetstatement ("parameters [" + I. tostring () + "] = new sqlparameter (\" "+ parameter. parametername + "\", sqldbtype. "+ parameter. sqldbtype + ");");
// Css = new codesnippetstatement ("sqlparameter parameter" + I. tostring () + "= new sqlparameter (\" "+ parameter. parametername + "\", sqldbtype. "+ parameter. sqldbtype + ");");
// CMM. Statements. Add (CSS );
If (paramtypename = "system. String ")
{
CSS = new codesnippetstatement (string. format ("sqlparameter parameter" + I. tostring () + "= command. parameters. add (\ "{0} \", sqldbtype. {1}, {2}); ", parameter. parametername, parameter. sqldbtype, parameter. size ));
CMM. Statements. Add (CSS );
}
Else if (paramtypename = "system. String ")
{
}
Else
{
CSS = new codesnippetstatement (string. format ("sqlparameter parameter" + I. tostring () + "= command. parameters. add (\ "{0} \", sqldbtype. {1}); ", parameter. parametername, parameter. sqldbtype ));
CMM. Statements. Add (CSS );
}
If (parameter. direction! = Parameterdirection. Input)
{
Cpvdf. Direction = fielddirection. out;
CSS = new codesnippetstatement ("parameter" + I. tostring () + ". Direction = parameterdirection. output ;");
CMM. Statements. Add (CSS );
CSS = new codesnippetstatement (paramname + "= NULL ;");
CSC. Add (CSS );
CSS = new codesnippetstatement ("If (parameter" + I. tostring () + ". value! = Dbnull. Value )");
CSC. Add (CSS );
CSS = new codesnippetstatement ("{");
CSC. Add (CSS );
Codesnippetexpression CSE = new codesnippetexpression (paramname );
Codecastexpression CCE = new codecastexpression (paramtypename, new codesnippetexpression ("parameter" + I. tostring () + ". Value "));
Codeassignstatement CAS = new codeassignstatement (CSE, CCE );
CSC. Add (CAS );
CSS = new codesnippetstatement ("}");
CSC. Add (CSS );
}
Else
{
String Ss = "";
If (paramtypename = "system. String ")
{
Ss = string. Format ("parameter {0}. value = ({1 }! = NULL? (Object) {1}: dbnull. Value); ", I, paramname );
}
Else
{
Ss = string. Format ("parameter {0}. value = {1};", I, paramname );
}
CSS = new codesnippetstatement (SS );
CMM. Statements. Add (CSS );
}
CMM. Parameters. Add (cpvdf );
I ++;
}
// Css = new codesnippetstatement ("sqlparameter parameterreturn = new sqlparameter (\" @ return_value \ ", sqldbtype. INT );");
CSS = new codesnippetstatement ("sqlparameter parameterreturn = command. Parameters. Add (\" @ return_value \ ", sqldbtype. INT );");
CMM. Statements. Add (CSS );
CSS = new codesnippetstatement ("parameterreturn. Direction = parameterdirection. returnvalue ;");
CMM. Statements. Add (CSS );
CSS = new codesnippetstatement ("connection. open ();");
CMM. Statements. Add (CSS );
CSS = new codesnippetstatement ("// sqldataadapter SDA = new sqldataadapter (command );");
CMM. Statements. Add (CSS );
CSS = new codesnippetstatement ("// dataset DS = new dataset ();");
CMM. Statements. Add (CSS );
CSS = new codesnippetstatement ("// SDA. Fill (DS );");
CMM. Statements. Add (CSS );
CSS = new codesnippetstatement ("command. executenonquery ();");
CMM. Statements. Add (CSS );
CSS = new codesnippetstatement ("connection. Close ();");
CMM. Statements. Add (CSS );
For (Int J = 0; j <CSC. Count; j ++)
{
CMM. Statements. Add (CSC [J]);
}
CSS = new codesnippetstatement ("// return Ds. Tables [0];");
CMM. Statements. Add (CSS );
If (includereturnvalueparameter)
{
// Codesnippetexpression CSE = new codesnippetexpression ("p_return_value ");
Codecastexpression CCE = new codecastexpression (typeof (INT), new codesnippetexpression ("parameterreturn. Value "));
// Codeassignstatement CAS = new codeassignstatement (CSE, CCE );
// CMM. Statements. Add (CAS );
CMM. Statements. Add (New codemethodreturnstatement (CCE ));
}
CTD. members. Add (CMM );
CMM = new codemembermethod ();
CMM. Name = "Main ";
CMM. Attributes = memberattributes. Public | memberattributes. Final | memberattributes. Static;
CTD. members. Add (CMM );
CN. types. Add (CTD );
CCU. namespaces. Add (CN );
Using (streamwriter Sw = new streamwriter (@ "test. cs", false ))
{
Provider. generatecodefromcompileunit (CCU, SW, new codegeneratoroptions ());
Sw. Close ();
}
String S = NULL;
Using (streamreader sr = new streamreader (@ "test. cs "))
{
S = Sr. readtoend ();
Sr. Close ();
}
Return S;
}
Public static string generatecodewithsqlhelper
(
String spname
, Bool includereturnvalueparameter
, Sqlconnection connection
, String namespace
, String classname
)
{
Sqlparameter [] SPA = sqlhelperparametercache. getspparameterset (connection, spname );
Int length = spa. length;
Csharpcodeprovider provider = new csharpcodeprovider ();
// Icodegenerator generator = provider. creategenerator ();
 
Codecompileunit CCU = new codecompileunit ();
Codenamespace Cn = new codenamespace (namespace); // Add a namespace
CN. Imports. Add (New codenamespaceimport ("system "));
CN. Imports. Add (New codenamespaceimport ("system. Data "));
CN. Imports. Add (New codenamespaceimport ("system. Data. sqlclient "));
CN. Imports. Add (New codenamespaceimport ("microshaoft. Data "));
Codetypedeclaration CTD = new codetypedeclaration (classname );
 
System. codedom. codememberfield CMF = new codememberfield (New codetypereference (typeof (string), "_ connectionstring ");
 
 
CMF. Attributes = memberattributes. Static | memberattributes. Public;
CMF. initexpression = new codesnippetexpression ("@ \" "+ connection. connectionstring +" \ ""); // new codeprimitiveexpression (connection. connectionstring );
 
CTD. members. Add (CMF );
// Int rows = spa. length;
Codemembermethod CMM = new codemembermethod ();
Codesnippetstatement CSS = NULL;
CSS = new codesnippetstatement ("sqlparameter [] parameters = new sqlparameter [" + (Length + 1). tostring () + "];");
CMM. Statements. Add (CSS );
CMM. returntype = new codetypereference (typeof (INT ));
CMM. Name = "execproc _" + spname;
CMM. Attributes = memberattributes. Public | memberattributes. Final | memberattributes. Static;
Int I = 0;
Codestatementcollection CSC = new codestatementcollection ();
Foreach (sqlparameter parameter in spa)
{
String paramname = "P _" + parameter. parametername. substring (1 );
String paramtypename = gettypename (parameter. sqldbtype );
Codetypereference paramtype = new codetypereference (paramtypename );
Paramtype. tostring ();
Codeparameterdeclarationexpression cpvdf = new codeparameterdeclarationexpression (paramtype, paramname );
CSS = new codesnippetstatement ("parameters [" + I. tostring () + "] = new sqlparameter (\" "+ parameter. parametername + "\", sqldbtype. "+ parameter. sqldbtype + ");");
CMM. Statements. Add (CSS );
If (parameter. direction! = Parameterdirection. Input)
{
Cpvdf. Direction = fielddirection. out;
If (paramtypename = "system. String ")
{
CSS = new codesnippetstatement ("parameters [" + I. tostring () + "]. size =" + parameter. Size + ";");
CMM. Statements. Add (CSS );
}
 
CSS = new codesnippetstatement ("parameters [" + I. tostring () + "]. Direction = parameterdirection. output ;");
CMM. Statements. Add (CSS );
 
CSS = new codesnippetstatement (paramname + "= NULL ;");
CSC. Add (CSS );
CSS = new codesnippetstatement ("If (parameters [" + I. tostring () + "]. value! = System. dbnull. Value )");
CSC. Add (CSS );
CSS = new codesnippetstatement ("{");
CSC. Add (CSS );
Codesnippetexpression CSE = new codesnippetexpression (paramname );
Codecastexpression CCE = new codecastexpression (paramtypename, new codesnippetexpression ("parameters [" + I. tostring () + "]. Value "));
Codeassignstatement CAS = new codeassignstatement (CSE, CCE );
CSC. Add (CAS );
CSS = new codesnippetstatement ("}");
CSC. Add (CSS );
}
Else
{
CSS = new codesnippetstatement ("parameters [" + I. tostring () + "]. value =" + paramname + ";");
CMM. Statements. Add (CSS );
}
CMM. Parameters. Add (cpvdf );
I ++;
 
}
 
 
CSS = new codesnippetstatement ("parameters [" + length. tostring () + "] = new sqlparameter (\" @ return_value \ ", sqldbtype. INT );");
CMM. Statements. Add (CSS );
 
CSS = new codesnippetstatement ("parameters [" + length. tostring () + "]. Direction = parameterdirection. returnvalue ;");
CMM. Statements. Add (CSS );
CSS = new codesnippetstatement ("sqlconnection connection = new sqlconnection (_ connectionstring );");
CMM. Statements. Add (CSS );
CSS = new codesnippetstatement ("sqlhelper. executenonquery (connection, commandtype. storedprocedure, \" "+ spname +" \ ", parameters );");
CMM. Statements. Add (CSS );
For (Int J = 0; j <CSC. Count; j ++)
{
CMM. Statements. Add (CSC [J]);
}
If (includereturnvalueparameter)
{
// Codesnippetexpression CSE = new codesnippetexpression ("p_return_value ");
Codecastexpression CCE = new codecastexpression (typeof (INT), new codesnippetexpression ("parameters [" + I. tostring () + "]. Value "));
// Codeassignstatement CAS = new codeassignstatement (CSE, CCE );
// CMM. Statements. Add (CAS );
CMM. Statements. Add (New codemethodreturnstatement (CCE ));
}
CTD. members. Add (CMM );
CMM = new codemembermethod ();
CMM.