Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » Utilities » Downloads
 
Add your own
alternative version

SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

, 7 Dec 2002 BSD
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.
dbhelper-1.0.zip
DBHelper
CVS
Base
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
SP
CVS
Base
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
SPTestApp
App.ico
CVS
Base
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.1.zip
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.2.zip
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.3.zip
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.4.zip
Baserev
Entries
Repository
Root
dbh.gif
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.5.demo.zip
SPTestApp.exe
DBHelper.dll
Interop.MSDASC.dll
dbhelper-1.5.src.zip
Baserev
Entries
Repository
Root
dbh.gif
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.6.demo.zip
SPTestApp.exe
DBHelper.dll
Interop.MSDASC.dll
dbhelper-1.6.src.zip
Baserev
Entries
Repository
Root
dbh.gif
DBHelper.suo
DBHelper.vssscc
Baserev
Diff
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-16demo.zip
SPTestApp.exe
DBHelper.dll
Interop.MSDASC.dll
dbhelper-16src.zip
Baserev
Entries
Repository
Root
dbh.gif
Baserev
Entries
Repository
Root
DBHelper.csproj.user
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
#define VSNET

using System;
using System.Text;
using System.Reflection;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
#if (VSNET)
//using Microsoft.VSDesigner.CodeGenerator;
#endif
using System.IO;
using System.Xml.Serialization;
using System.Xml;
using System.CodeDom.Compiler;
using System.CodeDom;
using System.Runtime.Serialization.Formatters.Binary;
using DBHelper;

namespace SPTestApp
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{
      private System.Windows.Forms.Button button1;
      private System.Data.SqlClient.SqlConnection sqlConnection1;
     
      DBHelper.SP sp;
      System.CodeDom.Compiler.CodeDomProvider cp;
      private System.Windows.Forms.DataGrid dataGrid1;
      private System.Data.DataSet dataSet1;
      private System.Windows.Forms.Label label1;
      private System.Windows.Forms.Label label2;
      private System.Windows.Forms.Label label3;
      private System.Windows.Forms.Label label4;
      private System.Windows.Forms.Label label5;
      private System.Windows.Forms.Button button2;
      private System.Windows.Forms.TextBox assemblydll;
      private System.Windows.Forms.TextBox NameSpace;
      private System.Windows.Forms.TextBox ClassName;
      private System.Windows.Forms.TextBox dataset;
      private System.Windows.Forms.CheckBox chkxsd;
      private System.Windows.Forms.TextBox source;
      private System.Windows.Forms.CheckBox chkdll;
      private System.Windows.Forms.CheckBox chksrc;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

      private System.Windows.Forms.GroupBox groupBox1;
      private System.Windows.Forms.RadioButton chkcs;
      private System.Windows.Forms.RadioButton chkvb;
      private System.Windows.Forms.GroupBox groupBox2;
      private System.Windows.Forms.CheckBox chkdsplus;
      private System.Windows.Forms.Button button3;
      private System.Windows.Forms.Button button4;
      private System.Windows.Forms.Label label6;
      private System.Windows.Forms.TextBox completeass;

      private static Microsoft.VisualBasic.VBCodeProvider vbcp = 
         new Microsoft.VisualBasic.VBCodeProvider();
      private static Microsoft.CSharp.CSharpCodeProvider cscp =
         new Microsoft.CSharp.CSharpCodeProvider();


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

         cp = cscp;
         Connect();
         
         UpdateOptions();
      }

		/// <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.assemblydll = new System.Windows.Forms.TextBox();
         this.button1 = new System.Windows.Forms.Button();
         this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
         this.NameSpace = new System.Windows.Forms.TextBox();
         this.ClassName = new System.Windows.Forms.TextBox();
         this.dataGrid1 = new System.Windows.Forms.DataGrid();
         this.dataSet1 = new System.Data.DataSet();
         this.dataset = new System.Windows.Forms.TextBox();
         this.chkxsd = new System.Windows.Forms.CheckBox();
         this.label1 = new System.Windows.Forms.Label();
         this.label2 = new System.Windows.Forms.Label();
         this.label3 = new System.Windows.Forms.Label();
         this.label4 = new System.Windows.Forms.Label();
         this.source = new System.Windows.Forms.TextBox();
         this.label5 = new System.Windows.Forms.Label();
         this.chkdll = new System.Windows.Forms.CheckBox();
         this.chksrc = new System.Windows.Forms.CheckBox();
         this.button2 = new System.Windows.Forms.Button();
         this.chkcs = new System.Windows.Forms.RadioButton();
         this.chkvb = new System.Windows.Forms.RadioButton();
         this.groupBox1 = new System.Windows.Forms.GroupBox();
         this.groupBox2 = new System.Windows.Forms.GroupBox();
         this.chkdsplus = new System.Windows.Forms.CheckBox();
         this.button3 = new System.Windows.Forms.Button();
         this.button4 = new System.Windows.Forms.Button();
         this.label6 = new System.Windows.Forms.Label();
         this.completeass = new System.Windows.Forms.TextBox();
         ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
         ((System.ComponentModel.ISupportInitialize)(this.dataSet1)).BeginInit();
         this.groupBox1.SuspendLayout();
         this.groupBox2.SuspendLayout();
         this.SuspendLayout();
         // 
         // assemblydll
         // 
         this.assemblydll.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.assemblydll.BackColor = System.Drawing.Color.White;
         this.assemblydll.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
         this.assemblydll.Location = new System.Drawing.Point(97, 374);
         this.assemblydll.Name = "assemblydll";
         this.assemblydll.ReadOnly = true;
         this.assemblydll.Size = new System.Drawing.Size(144, 20);
         this.assemblydll.TabIndex = 1;
         this.assemblydll.Text = "Filename.dll";
         // 
         // button1
         // 
         this.button1.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.button1.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
         this.button1.Location = new System.Drawing.Point(365, 394);
         this.button1.Name = "button1";
         this.button1.Size = new System.Drawing.Size(67, 23);
         this.button1.TabIndex = 2;
         this.button1.Text = "Generate";
         this.button1.Click += new System.EventHandler(this.button1_Click);
         // 
         // NameSpace
         // 
         this.NameSpace.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.NameSpace.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
         this.NameSpace.Location = new System.Drawing.Point(97, 302);
         this.NameSpace.Name = "NameSpace";
         this.NameSpace.Size = new System.Drawing.Size(144, 20);
         this.NameSpace.TabIndex = 4;
         this.NameSpace.Text = "";
         this.NameSpace.TextChanged += new System.EventHandler(this.ClassName_TextChanged);
         // 
         // ClassName
         // 
         this.ClassName.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.ClassName.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
         this.ClassName.Location = new System.Drawing.Point(97, 326);
         this.ClassName.Name = "ClassName";
         this.ClassName.Size = new System.Drawing.Size(144, 20);
         this.ClassName.TabIndex = 5;
         this.ClassName.Text = "SP";
         this.ClassName.TextChanged += new System.EventHandler(this.ClassName_TextChanged);
         // 
         // dataGrid1
         // 
         this.dataGrid1.AlternatingBackColor = System.Drawing.Color.LightGray;
         this.dataGrid1.Anchor = (((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) 
            | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right);
         this.dataGrid1.BackColor = System.Drawing.Color.Gainsboro;
         this.dataGrid1.BackgroundColor = System.Drawing.Color.Silver;
         this.dataGrid1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
         this.dataGrid1.CaptionBackColor = System.Drawing.Color.LightSteelBlue;
         this.dataGrid1.CaptionForeColor = System.Drawing.Color.MidnightBlue;
         this.dataGrid1.CaptionText = "Stored Procedures";
         this.dataGrid1.DataMember = "";
         this.dataGrid1.FlatMode = true;
         this.dataGrid1.Font = new System.Drawing.Font("Tahoma", 8F);
         this.dataGrid1.ForeColor = System.Drawing.Color.Black;
         this.dataGrid1.GridLineColor = System.Drawing.Color.DimGray;
         this.dataGrid1.HeaderBackColor = System.Drawing.Color.MidnightBlue;
         this.dataGrid1.HeaderFont = new System.Drawing.Font("Tahoma", 8F, System.Drawing.FontStyle.Bold);
         this.dataGrid1.HeaderForeColor = System.Drawing.Color.White;
         this.dataGrid1.LinkColor = System.Drawing.Color.MidnightBlue;
         this.dataGrid1.Name = "dataGrid1";
         this.dataGrid1.ParentRowsBackColor = System.Drawing.Color.DarkGray;
         this.dataGrid1.ParentRowsForeColor = System.Drawing.Color.Black;
         this.dataGrid1.SelectionBackColor = System.Drawing.Color.CadetBlue;
         this.dataGrid1.SelectionForeColor = System.Drawing.Color.White;
         this.dataGrid1.Size = new System.Drawing.Size(648, 296);
         this.dataGrid1.TabIndex = 8;
         // 
         // dataSet1
         // 
         this.dataSet1.DataSetName = "NewDataSet";
         this.dataSet1.Locale = new System.Globalization.CultureInfo("");
         // 
         // dataset
         // 
         this.dataset.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.dataset.BackColor = System.Drawing.Color.White;
         this.dataset.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
         this.dataset.Location = new System.Drawing.Point(97, 398);
         this.dataset.Name = "dataset";
         this.dataset.ReadOnly = true;
         this.dataset.Size = new System.Drawing.Size(144, 20);
         this.dataset.TabIndex = 9;
         this.dataset.Text = "TypedDataSet.xsd";
         // 
         // chkxsd
         // 
         this.chkxsd.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
         this.chkxsd.Location = new System.Drawing.Point(8, 62);
         this.chkxsd.Name = "chkxsd";
         this.chkxsd.Size = new System.Drawing.Size(48, 24);
         this.chkxsd.TabIndex = 10;
         this.chkxsd.Text = "XSD";
         this.chkxsd.CheckedChanged += new System.EventHandler(this.chkcs_CheckedChanged);
         // 
         // label1
         // 
         this.label1.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.label1.Location = new System.Drawing.Point(1, 372);
         this.label1.Name = "label1";
         this.label1.Size = new System.Drawing.Size(96, 23);
         this.label1.TabIndex = 11;
         this.label1.Text = "Output assembly";
         this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
         // 
         // label2
         // 
         this.label2.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.label2.Location = new System.Drawing.Point(1, 302);
         this.label2.Name = "label2";
         this.label2.Size = new System.Drawing.Size(96, 23);
         this.label2.TabIndex = 12;
         this.label2.Text = "Namespace";
         this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
         // 
         // label3
         // 
         this.label3.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.label3.Location = new System.Drawing.Point(1, 326);
         this.label3.Name = "label3";
         this.label3.Size = new System.Drawing.Size(96, 23);
         this.label3.TabIndex = 13;
         this.label3.Text = "Class name";
         this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
         // 
         // label4
         // 
         this.label4.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.label4.Location = new System.Drawing.Point(1, 396);
         this.label4.Name = "label4";
         this.label4.Size = new System.Drawing.Size(96, 23);
         this.label4.TabIndex = 14;
         this.label4.Text = "DataSet";
         this.label4.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
         // 
         // source
         // 
         this.source.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.source.BackColor = System.Drawing.Color.White;
         this.source.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
         this.source.Location = new System.Drawing.Point(97, 350);
         this.source.Name = "source";
         this.source.ReadOnly = true;
         this.source.Size = new System.Drawing.Size(144, 20);
         this.source.TabIndex = 15;
         this.source.Text = "Filename.cs";
         // 
         // label5
         // 
         this.label5.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.label5.Location = new System.Drawing.Point(1, 348);
         this.label5.Name = "label5";
         this.label5.Size = new System.Drawing.Size(96, 23);
         this.label5.TabIndex = 16;
         this.label5.Text = "Output source";
         this.label5.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
         // 
         // chkdll
         // 
         this.chkdll.Checked = true;
         this.chkdll.CheckState = System.Windows.Forms.CheckState.Checked;
         this.chkdll.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
         this.chkdll.Location = new System.Drawing.Point(8, 37);
         this.chkdll.Name = "chkdll";
         this.chkdll.Size = new System.Drawing.Size(72, 24);
         this.chkdll.TabIndex = 18;
         this.chkdll.Text = "Assembly";
         this.chkdll.CheckedChanged += new System.EventHandler(this.chkcs_CheckedChanged);
         // 
         // chksrc
         // 
         this.chksrc.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
         this.chksrc.Location = new System.Drawing.Point(8, 13);
         this.chksrc.Name = "chksrc";
         this.chksrc.Size = new System.Drawing.Size(64, 24);
         this.chksrc.TabIndex = 17;
         this.chksrc.Text = "Source";
         this.chksrc.CheckedChanged += new System.EventHandler(this.chkcs_CheckedChanged);
         // 
         // button2
         // 
         this.button2.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.button2.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
         this.button2.Location = new System.Drawing.Point(247, 394);
         this.button2.Name = "button2";
         this.button2.Size = new System.Drawing.Size(112, 23);
         this.button2.TabIndex = 19;
         this.button2.Text = "Connect DB";
         this.button2.Click += new System.EventHandler(this.button2_Click);
         // 
         // chkcs
         // 
         this.chkcs.Checked = true;
         this.chkcs.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
         this.chkcs.Location = new System.Drawing.Point(8, 14);
         this.chkcs.Name = "chkcs";
         this.chkcs.Size = new System.Drawing.Size(56, 24);
         this.chkcs.TabIndex = 20;
         this.chkcs.TabStop = true;
         this.chkcs.Text = "C#";
         this.chkcs.CheckedChanged += new System.EventHandler(this.chkcs_CheckedChanged);
         // 
         // chkvb
         // 
         this.chkvb.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
         this.chkvb.Location = new System.Drawing.Point(8, 38);
         this.chkvb.Name = "chkvb";
         this.chkvb.Size = new System.Drawing.Size(64, 24);
         this.chkvb.TabIndex = 21;
         this.chkvb.Text = "VB.NET";
         this.chkvb.CheckedChanged += new System.EventHandler(this.chkcs_CheckedChanged);
         // 
         // groupBox1
         // 
         this.groupBox1.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.groupBox1.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                                                this.chkcs,
                                                                                this.chkvb,
                                                                                this.chkxsd});
         this.groupBox1.Location = new System.Drawing.Point(247, 302);
         this.groupBox1.Name = "groupBox1";
         this.groupBox1.Size = new System.Drawing.Size(114, 88);
         this.groupBox1.TabIndex = 22;
         this.groupBox1.TabStop = false;
         this.groupBox1.Text = "Language Options";
         // 
         // groupBox2
         // 
         this.groupBox2.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.groupBox2.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                                                this.chkdsplus,
                                                                                this.chksrc,
                                                                                this.chkdll});
         this.groupBox2.Location = new System.Drawing.Point(366, 302);
         this.groupBox2.Name = "groupBox2";
         this.groupBox2.Size = new System.Drawing.Size(144, 88);
         this.groupBox2.TabIndex = 23;
         this.groupBox2.TabStop = false;
         this.groupBox2.Text = "Generation Options";
         // 
         // chkdsplus
         // 
         this.chkdsplus.Enabled = false;
         this.chkdsplus.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
         this.chkdsplus.Location = new System.Drawing.Point(8, 61);
         this.chkdsplus.Name = "chkdsplus";
         this.chkdsplus.Size = new System.Drawing.Size(134, 24);
         this.chkdsplus.TabIndex = 19;
         this.chkdsplus.Text = "Assembly inc DataSet";
         this.chkdsplus.CheckedChanged += new System.EventHandler(this.chkcs_CheckedChanged);
         // 
         // button3
         // 
         this.button3.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.button3.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
         this.button3.Location = new System.Drawing.Point(441, 394);
         this.button3.Name = "button3";
         this.button3.Size = new System.Drawing.Size(67, 23);
         this.button3.TabIndex = 24;
         this.button3.Text = "View";
         this.button3.Click += new System.EventHandler(this.button3_Click);
         // 
         // button4
         // 
         this.button4.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.button4.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
         this.button4.Location = new System.Drawing.Point(512, 394);
         this.button4.Name = "button4";
         this.button4.Size = new System.Drawing.Size(128, 23);
         this.button4.TabIndex = 25;
         this.button4.Text = "Serialize CompileUnit";
         this.button4.Visible = false;
         this.button4.Click += new System.EventHandler(this.button4_Click);
         // 
         // label6
         // 
         this.label6.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.label6.Location = new System.Drawing.Point(512, 304);
         this.label6.Name = "label6";
         this.label6.Size = new System.Drawing.Size(104, 23);
         this.label6.TabIndex = 27;
         this.label6.Text = "Complete assembly";
         this.label6.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
         // 
         // completeass
         // 
         this.completeass.Anchor = (System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left);
         this.completeass.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
         this.completeass.Location = new System.Drawing.Point(512, 328);
         this.completeass.Name = "completeass";
         this.completeass.Size = new System.Drawing.Size(128, 20);
         this.completeass.TabIndex = 28;
         this.completeass.Text = "";
         // 
         // Form1
         // 
         this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
         this.BackColor = System.Drawing.Color.LightSteelBlue;
         this.ClientSize = new System.Drawing.Size(648, 421);
         this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                                      this.completeass,
                                                                      this.label6,
                                                                      this.button4,
                                                                      this.button3,
                                                                      this.groupBox2,
                                                                      this.groupBox1,
                                                                      this.button2,
                                                                      this.label5,
                                                                      this.source,
                                                                      this.label4,
                                                                      this.label3,
                                                                      this.label2,
                                                                      this.label1,
                                                                      this.dataset,
                                                                      this.dataGrid1,
                                                                      this.ClassName,
                                                                      this.NameSpace,
                                                                      this.button1,
                                                                      this.assemblydll});
         this.Name = "Form1";
         this.Text = "DBHelper";
         ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
         ((System.ComponentModel.ISupportInitialize)(this.dataSet1)).EndInit();
         this.groupBox1.ResumeLayout(false);
         this.groupBox2.ResumeLayout(false);
         this.ResumeLayout(false);

      }
		#endregion

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

      private static DataTable GetReturnSchema(
         SqlConnection con, string spname)
      {
         SqlCommand com = new SqlCommand(spname, con);
         com.CommandType = CommandType.StoredProcedure;
         SqlTransaction trans = null;
         DataTable schema = null;
         try 
         {
            con.Open();

            SqlCommandBuilder.DeriveParameters(com);
            foreach (SqlParameter par in com.Parameters)
            {
               if (par.ParameterName == "@RETURN_VALUE") continue;
               Type type = TypeMapping.Mapping[par.SqlDbType];
               if (type == typeof(string))
                  par.Value = "";
               else if (type == typeof(Guid))
                  par.Value = Guid.Empty;
               else if (type == typeof(DateTime))
                  par.Value = DateTime.Now;
               else if (type == typeof(byte[]))
                  par.Value = new byte[0];
               else par.Value = 0;
            }

            trans = con.BeginTransaction();
            com.Transaction = trans;

            try 
            {
               //AFAIK this should not effect the table??? But we make doubly sure
               SqlDataReader reader = com.ExecuteReader(CommandBehavior.SchemaOnly);
               schema = new DataTable(spname);
               for (int i = 0; (i < reader.FieldCount); i = i + 1
                  )
               {
                  System.Type type = reader.GetFieldType(i);
                  string name = reader.GetName(i);
                  try 
                  {
                     schema.Columns.Add(name, type);
                  } 
                  catch (Exception)
                  {
                     // some weird error like Duplicate Columns has occured
                     schema = null;
                     break;
                  }
               }
               //schema = reader.GetSchemaTable();
               reader.Close();
            }
            catch (SqlException){}
            finally 
            {
               trans.Rollback();
            }
         } 
         catch (SqlException){}
         finally
         {
            con.Close();
         }
         return schema;
      }

      private void button1_Click(object sender, System.EventArgs e)
      {
         if (sp == null) return;
         dataSet1.Tables.Clear();
         
         sp.Names = (DBHelper.SPCollection) dataGrid1.DataSource;
         
         if (chksrc.Checked)
         {
            sp.CreateFile(NameSpace.Text, ClassName.Text, source.Text);
            source.BackColor = Color.LightGreen;
         }
         if (chkdll.Checked | chkxsd.Checked){
            System.CodeDom.Compiler.CompilerResults results = 
               sp.Compile(NameSpace.Text, ClassName.Text, assemblydll.Text);
            string msg = "";
            if (results.Errors.Count == 0) {if (chkdll.Checked) assemblydll.BackColor = Color.LightGreen;}
            else 
            {
               foreach (System.CodeDom.Compiler.CompilerError err in results.Errors)
               {
                  msg += err.ToString() + "\n";
               }
               MessageBox.Show(this, msg);
            }
         }
         if (chkxsd.Checked)
         {   
           foreach (StoredProc sproc in sp.Names)
            {
               //we will try new way
               if (sproc.Create)
               {
                  DataTable schema = GetReturnSchema(sqlConnection1, sproc.Name);
                  if (schema != null && schema.Columns.Count > 0)
                  {
                     dataSet1.Tables.Add(schema);
                  }
               }
            }

            dataSet1.WriteXmlSchema(dataset.Text);
            dataset.BackColor = Color.LightGreen;
         }
         if (chkdsplus.Checked)
         {
            CodeCompileUnit fccu = new CodeCompileUnit();
            CodeNamespace xcns = new CodeNamespace(NameSpace.Text);
            TypedDataSetGenerator.Generate(dataSet1, xcns, sp.Provider.CreateGenerator(""));
            fccu.Namespaces.Add(xcns);
            fccu.Namespaces.Add( sp.Generate(NameSpace.Text, ClassName.Text));
 
            CompilerParameters cpar = new CompilerParameters();
            cpar.OutputAssembly = completeass.Text;
            cpar.ReferencedAssemblies.Add("System.dll");
            cpar.ReferencedAssemblies.Add("System.Data.dll");
            cpar.ReferencedAssemblies.Add("System.Xml.dll");
            
            cpar.CompilerOptions = "/t:library";
            cpar.WarningLevel = 4;
            CompilerResults results = sp.Provider.CreateCompiler().CompileAssemblyFromDom(cpar, 
               fccu);

            string msg = "";
            if (results.Errors.Count == 0)completeass.BackColor = Color.LightGreen;
            else 
            {
               foreach (System.CodeDom.Compiler.CompilerError err in results.Errors)
               {
                  msg += err.ToString() + "\n";
               }
               MessageBox.Show(this, msg);
            }
         }
      }

      private void button2_Click(object sender, System.EventArgs e)
      {
         Connect();
      }

      bool connected = false;

      private void Connect()
      {
         connected = false;

         MSDASC.DataLinks dl = new MSDASC.DataLinks();
         ADODB.Connection connection = dl.PromptNew() as ADODB.Connection;

         if( null != connection )
         {
            string constr = connection.ConnectionString;
            string[] settings = constr.Split(';');
            constr = "";
            foreach (string str in settings)
            {
               if (!str.StartsWith("Provider")) 
                  constr += str + ";";
            }
            sqlConnection1.ConnectionString = constr;
         
            if (sqlConnection1.Database == "") 
            {
               MessageBox.Show(this, "Please select a Database as well.");
               Connect();
            }
            else 
            {
               sp = new DBHelper.SP(sqlConnection1, cp);
               connected = true;
               NameSpace.Text = dataSet1.DataSetName = sqlConnection1.Database;
               dataset.Text = sqlConnection1.Database + ".xsd";

               dataSet1.Namespace = NameSpace.Text;
               dataGrid1.SetDataBinding(sp.Names, null);
               dataGrid1.CaptionText = "Stored Procedures for " + sqlConnection1.Database;
            }
         }
         else dataGrid1.CaptionText = "No database selected.";
      }

      private void UpdateOptions()
      {
         foreach (Control c in Controls)
         {
            TextBox t = c as TextBox;
            if (t != null) t.BackColor = SystemColors.Control;
         }
         NameSpace.BackColor = Color.White;
         ClassName.BackColor = Color.White;

         if (connected)
         {
            if (chkxsd.Checked) 
               chkdsplus.Enabled = true;
            else chkdsplus.Enabled = false;
            if (chkcs.Checked) sp.Provider = cscp;
            else sp.Provider = vbcp;

            source.Text = String.Format("{0}.{1}.{2}", NameSpace.Text, ClassName.Text, sp.Provider.FileExtension).TrimStart('.');
            assemblydll.Text = String.Format("{0}.{1}.{2}", NameSpace.Text, ClassName.Text, "dll").TrimStart('.');
            dataset.Text = sqlConnection1.Database + ".xsd";
            completeass.Text = sqlConnection1.Database + "SQL.dll";
            button1.Enabled = true;
         }
         else button1.Enabled = false;
      }

      private void ClassName_TextChanged(object sender, System.EventArgs e)
      {
         UpdateOptions();
      }

      private void chkcs_CheckedChanged(object sender, System.EventArgs e)
      {
         if (!chkxsd.Checked) chkdsplus.Checked = false;

         UpdateOptions();
      }

      private void button3_Click(object sender, System.EventArgs e)
      {
         sp.Names = (DBHelper.SPCollection) dataGrid1.DataSource;
         CodeDOMView cdv = new CodeDOMView(sp.Generate(NameSpace.Text, ClassName.Text), sp.Provider);
         cdv.ShowDialog(this);
      }

      private void button4_Click(object sender, System.EventArgs e)
      {
         CodeNamespace cns = sp.Generate(NameSpace.Text, ClassName.Text);
         BinaryFormatter bf = new BinaryFormatter();
         Stream stream = File.Create("cns.data");
         bf.Serialize(stream, cns);
         stream.Close();
      }
	}
}

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

Share

About the Author

leppie
Software Developer
South Africa South Africa
No Biography provided
Follow on   Twitter

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 8 Dec 2002
Article Copyright 2002 by leppie
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid