Introduction
With all the time developing applications using stored procedures, generating classes that used this stored procs, I got sick of having to go through the same process all the time; name of stored proc, parameters, parameter type, parameter direction,... oh God, really, it was sickening. So once, I decided I wouldn't do this anymore. That's why I did this little application and decided to share it with you.
At the time I developed it, May 2004, I didn't know about Microsoft Application Blocks, so I had to hard code it, let's say so. If I had known before about MS App Blocks, I would have done it with it, but it was done already, and it works sort of fast: some 40,000 lines of code in five seconds. Some friend said to me I could have used the CodeDom namespace to do so, but honestly I only used SqlClient class and one miserable StringBuilder, so it was pure appending strings.
This is a picture of the running application:
This is a little snippet of the code I use to get the parameters for each of the stored procs:
foreach (string str in commands)
{
SqlCommand myCommand = new SqlCommand("SELECT * " +
"FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = '"
+ str + "'", mycon);
SqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.HasRows)
{
string prms = "";
SqlCommand cmdParams = new SqlCommand("SELECT DATA_TYPE," +
" PARAMETER_NAME FROM INFORMATION_SCHEMA.PARAMETERS" +
" WHERE SPECIFIC_NAME = '"+str+"'", mycon2);
SqlDataReader ParamsReader = cmdParams.ExecuteReader();
int prm = 0;
if (ParamsReader.HasRows)
{
while(ParamsReader.Read())
{
string prmname = ParamsReader.GetString(0);
prms += ParamTypeToString(prmname) + " _" +
ParamsReader.GetString(1).Remove(0,1).ToLower()
+", ";
prm++;
}
}
prms = prms.Remove(prms.Length -1, 1);
strBuilder.Append("\n\n\tpublic void " + commands[i] + "(" +
prms.Remove(prms.Length -1, 1)+")");
strBuilder.Append("\n\t{");
string sqlcon = (this.tbConnection.Text == "")? "\"" +
this.connection+"\"" : this.tbConnection.Text;
strBuilder.Append("\n\t\tSqlConnection sqlCon =
new SqlConnection("+sqlcon+");");
strBuilder.Append("\n\t\tSqlCommand myCommand =
new SqlCommand(\""+commands[i]+"\", sqlCon);");
strBuilder.Append("\n\t\tmyCommand.CommandType =
CommandType.StoredProcedure;");
while(myReader.Read())
{
string _size = (myReader.IsDBNull(9)) ? "": ", " +
myReader.GetInt32(9).ToString();
string param_name_whole = myReader.GetString(7);
string param_name = param_name_whole.Remove(0,1);
As you can see, it was just some commands I run against the SQL Server. I didn't know about these commands but with the Help, I found them and it really worked.
Summary
Honestly, there are better ways to do this, but this was the first way I found. I didn't know about Application Blocks at the time and after I finished the application, I really wouldn't change the code because it really works and, for the use it has, it is pretty fine. It does go several times to the SQL Server for finding the stored procs, then parameters, etc., but I consider it runs real fast.
I expect that you can find some use out of it. Hope you enjoy it.
