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

A .NET Windows Application To Generate SQL Server Scripts

, 1 Oct 2009
Generates SQL Server Stored Procedure and Table Defination Scripts
generate_script.zip
Generate Script
GenerateScripts
bin
Debug
GenerateScripts.exe
GenerateScripts.pdb
GenerateScripts.vshost.exe
GenerateScripts.vshost.exe.manifest
Release
GenerateScripts.exe
GenerateScripts.pdb
GenerateScripts.vshost.exe
GenerateScripts.vshost.exe.manifest
obj
Debug
GenerateScripts.csproj.GenerateResource.Cache
GenerateScripts.exe
GenerateScripts.Form1.resources
GenerateScripts.pdb
GenerateScripts.Properties.Resources.resources
Refactor
TempPE
Release
GenerateScripts.csproj.GenerateResource.Cache
GenerateScripts.exe
GenerateScripts.Form1.resources
GenerateScripts.pdb
GenerateScripts.Properties.Resources.resources
TempPE
Properties
Settings.settings
generate_scripts.zip
Generate Scripts
GenerateScripts
bin
Debug
GenerateScripts.exe
GenerateScripts.pdb
GenerateScripts.vshost.exe
GenerateScripts.vshost.exe.manifest
Release
GenerateScripts.exe
GenerateScripts.pdb
GenerateScripts.vshost.exe
GenerateScripts.vshost.exe.manifest
obj
Debug
GenerateScripts.csproj.GenerateResource.Cache
GenerateScripts.exe
GenerateScripts.Form1.resources
GenerateScripts.pdb
GenerateScripts.Properties.Resources.resources
Refactor
TempPE
Release
GenerateScripts.csproj.GenerateResource.Cache
GenerateScripts.exe
GenerateScripts.Form1.resources
GenerateScripts.pdb
GenerateScripts.Properties.Resources.resources
TempPE
Properties
Settings.settings
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

namespace GenerateScripts
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void btnGenerateScript_Click(object sender, EventArgs e)
        {
            if (txtConnString.Text.Trim() == "")
            {
                MessageBox.Show("Enter SQL Connection String", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return;
            }
            if (txtScriptsName.Text.Trim() == "")
            {
                MessageBox.Show("Enter Script Name","Alert", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return;
            }
            else
            {
                string ScriptName = txtScriptsName.Text.Trim();
                char[] a = { '\n' };
                string[] Finalstr = ScriptName.Split(a);
                if (rdbStoredProc.Checked)
                {
                    string Extn = "";
                    foreach (string s in Finalstr)
                    {
                        string ScriptString = s.TrimEnd('\r');
                        Extn = Path.GetExtension(ScriptString);
                        if (Extn.ToUpper() == ".SQL")
                        {
                            GenerateStoredProc(ScriptString.Replace(".sql",""));
                        }
                        else
                        {
                            MessageBox.Show("Invalid File Extension. Enter Script Name With .sql Extension", "Alert");
                        }
                    }
                }
                else
                {
                    foreach(string s in Finalstr)
                    {
                        GenerateTableScripts(s);
                    }
                }
            }
            MessageBox.Show(@"Scripts Generated Successfully");
        }

        private void GenerateStoredProc(string ProcName)
        { 
            if (System.IO.Directory.Exists(txtPath.Text.Trim()) == false)
            {
                 System.IO.Directory.CreateDirectory(txtPath.Text.Trim());
            }
            SqlConnection con = new SqlConnection(txtConnString.Text.Trim());

            SqlCommand cmd = new SqlCommand("SP_HELPTEXT " + ProcName, con);
            cmd.CommandType = CommandType.Text;
            cmd.Connection.Open();

            SqlDataReader dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
            string path = txtPath.Text.Trim() + ProcName + ".sql";

            FileStream fs = new FileStream(txtPath.Text.Trim() + ProcName + ".sql", FileMode.OpenOrCreate, FileAccess.ReadWrite);
            StreamWriter sw = new StreamWriter(fs, Encoding.Default);
            
            while (dr.Read())
            {
                sw.WriteLine(dr[0].ToString());
            }
            sw.Close();
            fs.Close();     
        }

        private void GenerateTableScripts(string TableName)
        {
            if (System.IO.Directory.Exists(txtPath.Text.Trim()) == false)
            {
                System.IO.Directory.CreateDirectory(txtPath.Text.Trim());
            }
            SqlConnection con = new SqlConnection(txtConnString.Text.Trim());

            SqlCommand cmd = new SqlCommand("EXEC sp_ScriptTable " + TableName, con);
            cmd.CommandType = CommandType.Text;
            cmd.Connection.Open();

            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            string path = txtPath.Text.Trim() + TableName + ".sql";

            FileStream fs = new FileStream(txtPath.Text.Trim() + TableName + ".sql", FileMode.OpenOrCreate, FileAccess.ReadWrite);
            StreamWriter sw = new StreamWriter(fs, Encoding.Default);

            while (dr.Read())
            {
                sw.WriteLine(dr[0].ToString());
            }
            sw.Close();
            fs.Close();
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            txtScriptsName.Text = "";
            txtConnString.Text = "";
            txtPath.Text = "";
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.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 Code Project Open License (CPOL)

About the Author

Deepankar Malik
Software Developer
India India
Working as a Software Engineer in a Reputed Company.Knowledge on C#.NET, ASP.NET, VB.NET & SQL Server

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 1 Oct 2009
Article Copyright 2009 by Deepankar Malik
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid