|
Ah, I figured it out. I had used CodeSmith 2.0[^] to generate my stored procedures, and I overlooked a tiny error in the procedure code for adding an entry.
Thanks for the great project (and the slap in the right direction).
|
|
|
|
|
I have a stored procedure that takes an image as a parameter. I had used you tool to generate the class to execute this stored procedure. It was working fine if the image was less than 65,534 bytes. However when it went beyond that size I was getting an "Error: 17805, Severity: 20, State: 3 Invalid buffer received from client". It seems that your tool used the sql type of SqlDbType.VarBinary in the paremeters list instead of using SqlDbType.Image. Once I changed the type the code worked fine.
andy
|
|
|
|
|
Thanks for the input Will keep it in mind for an update. Havent touched a SQL server in months now
Hey leppie! Your "proof" seems brilliant and absurd at the same time. - Vikram Punathambekar 28 Apr '03
|
|
|
|
|
Since INFORMATION_SCHEMA.ROUTINES isn't supported in 7.0, I changed affected code to:
select [name] from sysobjects where xtype = 'P' order by [name]
and it works (at least I think it does!)
-Vorn
|
|
|
|
|
Dude, if you've not got money or a job - then you sure do seem to have alot of industry knowledge and some pretty damn cool coding skills!
Personally I wouldn't go near any SQL unless my job requires it, which now it does.
Thanks for your article, as always well written and understandable.
/**********************************
Paul Evans, Dorset, UK.
Personal Homepage "EnjoySoftware" @
http://www.enjoysoftware.co.uk/
**********************************/
|
|
|
|
|
Hello,
this is a very usefull tool, especially if output and return parameters (@RETURN_VALUE) are supported. I added this for C# to the source. I think the method SP::Generate is the only updated Source. The resulting methods are like this:
public static int AddCompany(
System.Data.SqlClient.SqlConnection connection,
System.Data.DataTable table,
ref int RETURN_VALUE_param,
ref int CompID_param,
string CompName_param,
string CompShortName_param,
string CompDesc_param
)
Output and return parametes has the modifier "ref". The parameter table can be "null". This is usefull for the most SP's.
Enjoy
Andreas
Because i can't post the complete Project as attachment here the method SP::Generate.
public CodeNamespace Generate(string ns, string classname)
{
CodeNamespace cns = new CodeNamespace(ns);
cns.Imports.Add( new CodeNamespaceImport("System"));
cns.Imports.Add( new CodeNamespaceImport("System.Data"));
cns.Imports.Add( new CodeNamespaceImport("System.Data.SqlClient"));
CodeTypeDeclaration cclass = new CodeTypeDeclaration(classname);
cclass.IsClass = true;
foreach(StoredProc sp in allsp)
{
if (sp.Create)
{
//SqlCommand com = sp.GetCommand();
//generate the code
// if (sp.Parameters.Contains("@RETURN_VALUE"))
// sp.Parameters.Remove(sp.Parameters["@RETURN_VALUE"]);
CodeMemberMethod method = new CodeMemberMethod();
method.Name = sp.Name/*.Replace(" ","_")*/; //gotta figure out how to handle spaces
method.ReturnType = new CodeTypeReference("System.Int32");
method.Attributes = MemberAttributes.Public | MemberAttributes.Static;
CodeParameterDeclarationExpression connpar = new CodeParameterDeclarationExpression(typeof(SqlConnection), "connection");
method.Parameters.Add( connpar);
method.Parameters.Add( new CodeParameterDeclarationExpression(typeof(DataTable), "table"));
//method.Parameters.Add( new CodeParameterDeclarationExpression(typeof(bool), "filltable"));
method.Statements.Add( new CodeVariableDeclarationStatement(
typeof(SqlCommand), "cmd", new CodeObjectCreateExpression(typeof(SqlCommand))));
method.Statements.Add( new CodeVariableDeclarationStatement(typeof(int), "result",
new CodePrimitiveExpression(0)));
CodeSnippetExpression cmdexp = new CodeSnippetExpression("cmd");
method.Statements.Add( new CodeAssignStatement(
new CodePropertyReferenceExpression( cmdexp, "Connection"),
new CodeVariableReferenceExpression("connection")
));
method.Statements.Add( new CodeSnippetExpression("cmd.CommandText = \"" + sp + "\""));
method.Statements.Add( new CodeSnippetExpression("cmd.CommandType = CommandType.StoredProcedure"));
foreach (SqlParameter par in sp.Parameters)
{
CodeParameterDeclarationExpression cpar = new CodeParameterDeclarationExpression(
/*(par.SqlParameter.IsNullable) ? typeof(object) : */
TypeMapping.Mapping[par.SqlDbType],
par.ParameterName.Replace("@","") + "_param");
// Ist so nicht OK
// if (par.Direction == ParameterDirection.InputOutput)
// cpar.Direction = FieldDirection.Ref;
// if (par.Direction == ParameterDirection.Output)
// cpar.Direction = FieldDirection.Out;
if (par.Direction == ParameterDirection.InputOutput ||
par.Direction == ParameterDirection.Output )
cpar.Direction = FieldDirection.Ref;
else if ( par.Direction == ParameterDirection.Input )
cpar.Direction = FieldDirection.In ;
else if ( par.Direction == ParameterDirection.ReturnValue )
//cpar.Direction = FieldDirection.Out; // out int xxx
cpar.Direction = FieldDirection.Ref; // ref int xxx
method.Parameters.Add(cpar);
method.Statements.Add(
//new CodeMethodInvokeExpression(
//new CodePropertyReferenceExpression( new CodeVariableReferenceExpression("cmd"),
//"Parameters"), "Add",
new CodeSnippetExpression(
String.Format("cmd.Parameters.Add(\"{0}\", SqlDbType.{1}).Value = {2}",
par.ParameterName, par.SqlDbType,
par.ParameterName.Replace("@","") + "_param")
));
if ( par.Direction == ParameterDirection.Output ||
par.Direction == ParameterDirection.InputOutput
)
{
method.Statements.Add(
new CodeSnippetExpression(
String.Format( "cmd.Parameters[\"{0}\"].Direction = ParameterDirection.Output",
(string)par.ParameterName
)
)
);
}
else if ( par.Direction == ParameterDirection.ReturnValue )
{
method.Statements.Add(
new CodeSnippetExpression(
String.Format( "cmd.Parameters[\"{0}\"].Direction = ParameterDirection.ReturnValue",
(string)par.ParameterName
)
)
);
}
}
method.Statements.Add( new CodeMethodInvokeExpression(
new CodeVariableReferenceExpression("connection"),
"Open"));
CodeTryCatchFinallyStatement trycatch = new CodeTryCatchFinallyStatement();
CodeConditionStatement choice = new CodeConditionStatement(
new CodeBinaryOperatorExpression(
new CodeVariableReferenceExpression("table"),
CodeBinaryOperatorType.IdentityInequality,
new CodePrimitiveExpression(null)
));
choice.TrueStatements.Add( new CodeVariableDeclarationStatement(typeof(SqlDataReader), "reader",
new CodeMethodInvokeExpression( new CodeVariableReferenceExpression("cmd"),
"ExecuteReader")
));
CodeConditionStatement tabletest = new CodeConditionStatement( new CodeBinaryOperatorExpression(
new CodeSnippetExpression("table.Columns.Count"), CodeBinaryOperatorType.ValueEquality,
new CodePrimitiveExpression(0)
));
tabletest.TrueStatements.Add( new CodeAssignStatement(
new CodeVariableReferenceExpression("table.TableName"),
new CodeSnippetExpression("\"" + sp + "\"")
));
CodeIterationStatement forfield = new CodeIterationStatement(
new CodeVariableDeclarationStatement(typeof(int), "i", new CodePrimitiveExpression(0)),
new CodeBinaryOperatorExpression(
new CodeVariableReferenceExpression("i"),
CodeBinaryOperatorType.LessThan,
new CodePropertyReferenceExpression(
new CodeVariableReferenceExpression("reader"),"FieldCount")),
new CodeSnippetStatement("i = i + 1"));
forfield.Statements.Add( new CodeVariableDeclarationStatement(
typeof(Type), "type",
new CodeMethodInvokeExpression( new CodeVariableReferenceExpression("reader"),
"GetFieldType", new CodeVariableReferenceExpression("i"))));
forfield.Statements.Add( new CodeVariableDeclarationStatement(
typeof(string), "name",
new CodeMethodInvokeExpression( new CodeVariableReferenceExpression("reader"),
"GetName", new CodeVariableReferenceExpression("i"))));
forfield.Statements.Add( new CodeMethodInvokeExpression(
new CodePropertyReferenceExpression( new CodeVariableReferenceExpression("table"),
"Columns"), "Add",
new CodeVariableReferenceExpression("name"),
new CodeVariableReferenceExpression("type")));
tabletest.TrueStatements.Add(forfield);
//tabletest.TrueStatements.Add( new CodeSnippetExpression("reader.Close()"));
//tabletest.TrueStatements.Add( new CodeMethodReturnStatement(
// new CodeVariableReferenceExpression("table.Columns.Count")
// ));
choice.TrueStatements.Add(tabletest);
choice.TrueStatements.Add( new CodeMethodInvokeExpression(
new CodeVariableReferenceExpression("table"), "Clear"));
CodeIterationStatement whileread = new CodeIterationStatement(
//not sure how to handle this in VB and JS
new CodeSnippetStatement(""),
new CodeMethodInvokeExpression( new CodeVariableReferenceExpression("reader"),
"Read"),
//not sure how to handle this in VB and JS
new CodeSnippetStatement("result = result + 1"));
whileread.Statements.Add( new CodeVariableDeclarationStatement(typeof(DataRow), "row",
new CodeMethodInvokeExpression(
new CodeVariableReferenceExpression("table"), "NewRow")));
whileread.Statements.Add( new CodeVariableDeclarationStatement(typeof(object[]), "rowdata",
new CodeArrayCreateExpression(typeof(object), new CodePropertyReferenceExpression(
new CodeVariableReferenceExpression("reader"), "FieldCount"))));
whileread.Statements.Add( new CodeMethodInvokeExpression(
new CodeVariableReferenceExpression("reader"), "GetValues",
new CodeVariableReferenceExpression("rowdata")));
whileread.Statements.Add( new CodeAssignStatement(
new CodePropertyReferenceExpression( new CodeVariableReferenceExpression("row"),"ItemArray"),
new CodeVariableReferenceExpression("rowdata")));
whileread.Statements.Add( new CodeMethodInvokeExpression(
new CodePropertyReferenceExpression( new CodeVariableReferenceExpression("table"),
"Rows"), "Add",
new CodeVariableReferenceExpression("row")));
choice.TrueStatements.Add(whileread);
foreach (SqlParameter par2 in sp.Parameters)
{
if ( par2.Direction == ParameterDirection.Output ||
par2.Direction == ParameterDirection.InputOutput ||
par2.Direction == ParameterDirection.ReturnValue
)
{
choice.TrueStatements.Add(
new CodeSnippetExpression(
String.Format( "{0} = ({1})cmd.Parameters[\"{2}\"].Value",
(par2.ParameterName.Replace("@","") + "_param"),
TypeMapping.Mapping[par2.SqlDbType].ToString(), // Mappt zu .net Framework Typ z.B. System.In32
(string)par2.ParameterName
)
)
);
}
}
choice.TrueStatements.Add( new CodeMethodInvokeExpression(
new CodeVariableReferenceExpression("reader"), "Close"));
choice.FalseStatements.Add( new CodeAssignStatement(
new CodeVariableReferenceExpression("result"),
new CodeMethodInvokeExpression( new CodeVariableReferenceExpression("cmd"),
"ExecuteNonQuery")
));
foreach (SqlParameter par2 in sp.Parameters)
{
if ( par2.Direction == ParameterDirection.Output ||
par2.Direction == ParameterDirection.InputOutput ||
par2.Direction == ParameterDirection.ReturnValue
)
{
choice.FalseStatements.Add(
new CodeSnippetExpression(
String.Format( "{0} = ({1})cmd.Parameters[\"{2}\"].Value",
(par2.ParameterName.Replace("@","") + "_param"),
TypeMapping.Mapping[par2.SqlDbType].ToString(), // Mappt zu .net Framework Typ z.B. System.In32
(string)par2.ParameterName
)
)
);
}
}
trycatch.TryStatements.Add(choice);
CodeCatchClause catchclause = new CodeCatchClause("ex", new CodeTypeReference("SqlException"));
catchclause.Statements.Add( new CodeThrowExceptionStatement(
new CodeVariableReferenceExpression("ex")));
trycatch.CatchClauses.Add( catchclause);
trycatch.FinallyStatements.Add( new CodeMethodInvokeExpression(
new CodeVariableReferenceExpression("connection"),
"Close"));
method.Statements.Add(trycatch);
method.Statements.Add( new CodeMethodReturnStatement(
new CodeVariableReferenceExpression("result")));
cclass.Members.Add(method);
}
}
cns.Types.Add(cclass);
return cns;
}
Andreas Gratz
|
|
|
|
|
i have a database with 97 stored procedures. all of them are included in the generated c#-file,
but only 6 of them (they seem to be randomly choosen) are in the xsd-file.
whats going wrong ?
is there a limitation i dont know ?
ciao,
uwe
|
|
|
|
|
There is a good reason for this. Only 6 of your stored procudures returns records, iow tables.
ATOH, there might be some mystery problem like spaces, nulls, etc. Let me know or send me a list of your stored procedures.
I rated this article 2 by mistake. It deserves more. I wanted to get to the second page... - vjedlicka 3:33 25 Nov '02
|
|
|
|
|
Hi
I'm quite new to sp and to vb.net. i want to try out the program i managed to generate the SP.Dll and the SP.Vb files but how to use them in my project?
How do i call the class... please provide me with a simple example i really want to give it a try and see how it works.
Thanks in advance for your help
|
|
|
|
|
I would like to see a VB example too. Looks great but still hoping to use it
|
|
|
|
|
Sorry just saw these posts now. Look at my reply to the other person. If anyone would like to send me some VB.NET code I will up it
MyDUMeter: a .NET DUMeter clone "Thats like saying "hahahaha he doesnt know the difference between a cyberneticradioactivenuclothermolopticdimswitch and a biocontainingspherogramotron", but with words you have really never heard of."
|
|
|
|
|
shobapond wrote:
How do i call the class... please provide me with a simple example i really want to give it a try and see how it works.
I dont know VB.NET. But you would just add the generated assembly as a reference to you project, then call a static (Shared function). There is no need the instantiate a class.
Sorry but thats about al I can give you.
MyDUMeter: a .NET DUMeter clone "Thats like saying "hahahaha he doesnt know the difference between a cyberneticradioactivenuclothermolopticdimswitch and a biocontainingspherogramotron", but with words you have really never heard of."
|
|
|
|
|
I´m creating a similar project... but it doesn´t create a class (it creates the code necessary to create the parameters) and it was created as an VS.NET Add-in. Don´t know if I should post it now...
BTW, nice article, very usefull ! Got my five !
Mauricio Ritter - Brazil
Sonorking now: 100.13560 MRitter
"Th@ langwagje is screwed! It has if's but no end if's!! Stupid php cant even do butuns on forms! VISHAUL BASICS ARE THE FUTSHURE!" - Simon Walton
|
|
|
|
|
Mauricio Ritter wrote:
´m creating a similar project... but it doesn´t create a class (it creates the code necessary to create the parameters) and it was created as an VS.NET Add-in. Don´t know if I should post it now...
I didnt realise it, but there was another article like this posted beginning November (SP/Invoke). Pretty much does the same (does have some extra features), but works thru a custom tool in VS.NET. Mine one the other hand are completely automatic (click click ). However, if yours does something different or better in your opinion, go ahead post it!
Mauricio Ritter wrote:
BTW, nice article, very usefull ! Got my five !
Thanks I must say it ended up being an extremely usefull tool (started as a small library only then added some cool features), especially for system SQL stored procedures.
CHeers
WebBoxes - Yet another collapsable control, but it relies on a "graphics server" for dynamic pretty rounded corners, cool arrows and unlimited font support.
|
|
|
|
|
Leppie,
This is very nice work, but there is room for improvement (isn't there always ). In particular, it would be nice if you UI would allow the user to specify which procedures were execute only types (not returning records, but only a records affected cout, meant to be 'Cmd.ExecuteNoRecords' targets), also the generated code is broken if one or more of the parameters are output parameters, Since yuuo build the command with all the parameters set to the default (ParameterDirection.Input)...
It might help to factor the gernerated code a bit:
For each SP, build a method to construct the command object: public static SqlCommand BuildSP(string ProcName){}
One for getting Data: public static int SP_GetData(SqlCommand cmd, SqlConnection connection, System.Data.DataTable table, SomeDataType p1Val,ref SomeValueType p2,...){ ... return results;} (buld the DataTable pretty much as you do...maybe some special handling for dbNulls, set the values for the parameters that were output types - pass them in as ref Valuetype so the proc can set the values)
and one for execute: public static int SP_Execute(SqlCommand cmd, SqlConnection connection,SomeDataType param1val,...){ ....return RecordsAffected;}
It might also be desirable to let the caller specify the CommandBehavior and perhaps Transaction.. many times you don't want to close the connection immediately, overloads on each of the methods could work here...
Also, either put the SqlDataReaders in a using{} block or a try-catch-finally (close the reader in finally...). Failing to close the reader because an exception took you out of scope creates some really ugly problems...
Keep up the Good work!
Rob
|
|
|
|
|
OldRob wrote:
This is very nice work, but there is room for improvement (isn't there always ).
Thanx
OldRob wrote:
In particular, it would be nice if you UI would allow the user to specify which procedures were execute only types (not returning records, but only a records affected cout, meant to be 'Cmd.ExecuteNoRecords' targets)
That has been done I forgot to mention it! When you pass null as the DataTable it will ExecuteNonQuery In fact I should included a bit better usage docs
OldRob wrote:
also the generated code is broken if one or more of the parameters are output parameters, Since yuuo build the command with all the parameters set to the default (ParameterDirection.Input)...
Hmmm, I though I have fixed that....will look
OldRob wrote:
It might help to factor the gernerated code a bit:
This "little" library has progressed way faster than I anticipated. I know I should but it works like it should (and it generated , so who cares about all the extra code?). It also makes it handy to tweak specific/problemamtic procedures. I think the CodeDom code needs a bit of refactoring! (what a mess, thank MS for intellisense).
OldRob wrote:
It might also be desirable to let the caller specify the CommandBehavior and perhaps Transaction.. many times you don't want to close the connection immediately, overloads on each of the methods could work here...
I'm a bit new in this SQL game, so all my SQL side stuff is pretty basic, not that I have needed any more...Can you please explain in a bit more detail?
OldRob wrote:
Also, either put the SqlDataReaders in a using{} block or a try-catch-finally (close the reader in finally...). Failing to close the reader because an exception took you out of scope creates some really ugly problems...
Keep up the Good work!
Yeah, lazy me! I write code to normally not to throw exceptions, but I should really have that in there! Once again thanks .
PS: Did you get the latest version? 1.5?
DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET popularity better now, thank you
|
|
|
|
|
OldRob wrote:
also the generated code is broken if one or more of the parameters are output parameters, Since yuuo build the command with all the parameters set to the default (ParameterDirection.Input)...
I checked it, and I cant see what you mean. Have you got the latest version? I basically force ref parameters for OUT and INOUT. default only matches if the above two cases dont match.
If have made done updates and will be making some more tomorrow, look for an update.
CHeers
DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET popularity better now, thank you
|
|
|
|
|
My bad. I had an older version, but did a lousy job of code inspection as well. The current version fixes all my issues. On DbNull handling I have come to the conclusion that the user of the generated code should do whatever he thinks is appropriate anyway, no way a libray can (or should) try to anticipate this. If what you provide is good as is for the needs, then your dll can do, if not, you provide the generated code as source, one can always specialize that as needed and recompile.
Darn users, give 'em a good tool and the next thing you know they want it to write all the code for them. If you somehow manage to do that, then they'll want it to think up killer apps for them to use the code in...
R
|
|
|
|
|
OldRob wrote:
My bad. I had an older version
Phew, I was getting worried there for a sec
OldRob wrote:
The current version fixes all my issues.
Not quite. There is the transaction issue (of which I have little/no knowledge), the issue with people naming SP with spaces (god knows why?) and the try catch block. This is what I have proposed for it, but I'm not sure if (and how) I should catch the reader...Maybe a another block? Heres an output sample:
public static int GetNewsItem(System.Data.SqlClient.SqlConnection connection, System.Data.DataTable table, int id_param)
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
int result = 0;
cmd.Connection = connection;
cmd.CommandText = "GetNewsItem";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id_param;
connection.Open();
try
{
if ((table != null))
{
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
if ((table.Columns.Count == 0))
{
table.TableName = "GetNewsItem";
for (int i = 0; (i < reader.FieldCount); i = i + 1
)
{
System.Type type = reader.GetFieldType(i);
string name = reader.GetName(i);
table.Columns.Add(name, type);
}
}
table.Clear();
for (
; reader.Read(); result = result + 1
)
{
System.Data.DataRow row = table.NewRow();
object[] rowdata = new object[reader.FieldCount];
reader.GetValues(rowdata);
row.ItemArray = rowdata;
table.Rows.Add(row);
}
reader.Close();
}
else
{
result = cmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return result;
}
Cheers
DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET popularity better now, thank you
|
|
|
|
|
I rated this article 2 by mistake. It deserves more. I wanted to get to the second page...
|
|
|
|
|
|
Nice job! - Before it becomes really usable you should add DBNulls to your application
|
|
|
|
|
Hi
I have a slight problem with DBNull
If I allow NULL's I need to change the methods parameter type to object, making it lose some of the "strongly-typed-ness". But I guess we can live with it . I was thinking of overloads, but that would require quite a bit of extra code generation.
Currently, I have further increased functionality by allowing you to compile a complete assembly (all SP methods plus generated typed dataset all in one!). I have tested the generation on the ASPNet forums DB and it seems to work ok, except for the fact that 5 "tables" being returned has duplicate column names....I'm not sure how kosher that is , as you cant add duplicate table columns in a DataTable.
Perhaps I will figure it out, or the ASPnetforums are just "wrong"!
CHeers
PS: I will update it probably later tonite. If you follow some design "rules" the program works pretty flawless.
"I dont have a life, I have a program." Also, I won't support any software without the LeppieRules variable.
|
|
|
|
|
Hi,
I'd suggest to keep the strongly typed nature, but to allow the users of your tool to define their own 'default' values for DBNull for all different types like:
int = -1;
string = "___empty___";
double = NotANumber;
DateTime = DateTime.Min;
etc.
How is that?
I am looking forward to your upcoming versions!!
Take care,
Marc
|
|
|
|
|
MarcHoeppner wrote:
DateTime = DateTime.Min;
That is already out of SQL datetime scope !
And again you will pretty much have to use those rules when desing your tables/queries. Returning a DBNull infact is not a problem, only passing it into the function is.
Cheers
"I dont have a life, I have a program." Also, I won't support any software without the LeppieRules variable.
|
|
|
|
|