Click here to Skip to main content
15,886,362 members
Articles / Programming Languages / Visual Basic

SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Rate me:
Please Sign up or sign in to vote.
4.50/5 (46 votes)
7 Dec 2002BSD4 min read 380.4K   6.1K   142  
This a small tool that will generate static methods in a class that acts as wrapper for SQL stored procedures. It either outputs a source file or a compiled assembly. Also supports automatic DataSet generation.
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

namespace SPTestApp
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{
      private System.Windows.Forms.CheckedListBox checkedListBox1;
      private System.Windows.Forms.TextBox textBox1;
      private System.Windows.Forms.Button button1;
      private System.Data.SqlClient.SqlConnection sqlConnection1;

      DBHelper.SP spvb;
      DBHelper.SP spcs;
      //DBHelper.SP spjs;

      private System.Windows.Forms.TextBox textBox2;
      private System.Windows.Forms.TextBox textBox3;
      private System.Windows.Forms.CheckBox checkBox1;
      private System.Windows.Forms.CheckBox checkBox2;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public Form1()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

         spvb = new DBHelper.SP(sqlConnection1,new Microsoft.VisualBasic.VBCodeProvider());
         spcs = new DBHelper.SP(sqlConnection1,new Microsoft.CSharp.CSharpCodeProvider());
         //spjs = new DBHelper.SP(sqlConnection1,new Microsoft.JScript.JScriptCodeProvider());
         checkedListBox1.DataSource = spvb.Names;
			//
			// TODO: Add any constructor code after InitializeComponent call
			//
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
         this.checkedListBox1 = new System.Windows.Forms.CheckedListBox();
         this.textBox1 = new System.Windows.Forms.TextBox();
         this.button1 = new System.Windows.Forms.Button();
         this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
         this.textBox2 = new System.Windows.Forms.TextBox();
         this.textBox3 = new System.Windows.Forms.TextBox();
         this.checkBox1 = new System.Windows.Forms.CheckBox();
         this.checkBox2 = new System.Windows.Forms.CheckBox();
         this.SuspendLayout();
         // 
         // checkedListBox1
         // 
         this.checkedListBox1.Anchor = (((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) 
            | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right);
         this.checkedListBox1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
         this.checkedListBox1.CheckOnClick = true;
         this.checkedListBox1.Name = "checkedListBox1";
         this.checkedListBox1.Size = new System.Drawing.Size(704, 272);
         this.checkedListBox1.TabIndex = 0;
         // 
         // textBox1
         // 
         this.textBox1.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.textBox1.Location = new System.Drawing.Point(8, 288);
         this.textBox1.Name = "textBox1";
         this.textBox1.Size = new System.Drawing.Size(88, 20);
         this.textBox1.TabIndex = 1;
         this.textBox1.Text = "Filename";
         // 
         // button1
         // 
         this.button1.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right);
         this.button1.Location = new System.Drawing.Point(624, 288);
         this.button1.Name = "button1";
         this.button1.TabIndex = 2;
         this.button1.Text = "Generate";
         this.button1.Click += new System.EventHandler(this.button1_Click);
         // 
         // sqlConnection1
         // 
         this.sqlConnection1.ConnectionString = "data source=LEPPIE\\VSdotNET;initial catalog=Northwind;integrated security=SSPI;pe" +
            "rsist security info=False;workstation id=LEPPIE;packet size=4096";
         // 
         // textBox2
         // 
         this.textBox2.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.textBox2.Location = new System.Drawing.Point(104, 288);
         this.textBox2.Name = "textBox2";
         this.textBox2.Size = new System.Drawing.Size(88, 20);
         this.textBox2.TabIndex = 4;
         this.textBox2.Text = "NameSpace";
         // 
         // textBox3
         // 
         this.textBox3.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.textBox3.Location = new System.Drawing.Point(200, 288);
         this.textBox3.Name = "textBox3";
         this.textBox3.Size = new System.Drawing.Size(88, 20);
         this.textBox3.TabIndex = 5;
         this.textBox3.Text = "ClassName";
         // 
         // checkBox1
         // 
         this.checkBox1.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.checkBox1.Checked = true;
         this.checkBox1.CheckState = System.Windows.Forms.CheckState.Checked;
         this.checkBox1.Location = new System.Drawing.Point(288, 288);
         this.checkBox1.Name = "checkBox1";
         this.checkBox1.Size = new System.Drawing.Size(40, 24);
         this.checkBox1.TabIndex = 6;
         this.checkBox1.Text = "VB";
         // 
         // checkBox2
         // 
         this.checkBox2.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.checkBox2.Checked = true;
         this.checkBox2.CheckState = System.Windows.Forms.CheckState.Checked;
         this.checkBox2.Location = new System.Drawing.Point(328, 288);
         this.checkBox2.Name = "checkBox2";
         this.checkBox2.Size = new System.Drawing.Size(40, 24);
         this.checkBox2.TabIndex = 7;
         this.checkBox2.Text = "C#";
         // 
         // Form1
         // 
         this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
         this.ClientSize = new System.Drawing.Size(704, 317);
         this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                                      this.checkBox2,
                                                                      this.checkBox1,
                                                                      this.textBox3,
                                                                      this.textBox2,
                                                                      this.button1,
                                                                      this.textBox1,
                                                                      this.checkedListBox1});
         this.Name = "Form1";
         this.Text = "Form1";
         this.ResumeLayout(false);

      }
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}

      private void button1_Click(object sender, System.EventArgs e)
      {
         string[] sps = new string[checkedListBox1.CheckedItems.Count];
         for (int i = 0; i < sps.Length; i++) 
            sps[i] = checkedListBox1.CheckedItems[i].ToString();
         if (checkBox1.Checked) 
         {
            spvb.CreateFile(sps, textBox2.Text, textBox3.Text, textBox1.Text + ".vb");
            System.CodeDom.Compiler.CompilerResults results = 
               spvb.Compile(sps, textBox2.Text, textBox3.Text, textBox1.Text + ".vb.dll");

            string msg = "";
            if (results.Errors.Count == 0)MessageBox.Show(this, "Done VB");
            else 
            {
               foreach (System.CodeDom.Compiler.CompilerError err in results.Errors)
               {
                  msg += err.ToString() + "\n";
               }
               MessageBox.Show(this, msg);
            }
         }
         if (checkBox2.Checked)
         {
            spcs.CreateFile(sps, textBox2.Text, textBox3.Text, textBox1.Text + ".cs");
            System.CodeDom.Compiler.CompilerResults results = 
               spcs.Compile(sps, textBox2.Text, textBox3.Text, textBox1.Text + ".cs.dll");
            string msg = "";
            if (results.Errors.Count == 0)MessageBox.Show(this, "Done C#");
            else 
            {
               foreach (System.CodeDom.Compiler.CompilerError err in results.Errors)
               {
                  msg += err.ToString() + "\n";
               }
               MessageBox.Show(this, msg);
            }
         }
#if(WHENIHAVELEARNTJSCRIPT)
         if (checkBox3.Checked)
         {
            spjs.CreateFile(sps, textBox2.Text, textBox3.Text, textBox1.Text + ".js");
            System.CodeDom.Compiler.CompilerResults results = 
               spjs.Compile(sps, textBox2.Text, textBox3.Text, textBox1.Text + ".js.dll");
            string msg = "";
            if (results.Errors.Count == 0)MessageBox.Show(this, "Done JScript");
            else 
            {
               foreach (System.CodeDom.Compiler.CompilerError err in results.Errors)
               {
                  msg += err.ToString() + "\n";
               }
               MessageBox.Show(this, msg);
            }
         }
#endif

      }
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The BSD License


Written By
Software Developer
South Africa South Africa
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions