How To Completely Uninstall/Remove All Versions of SQL Server and related tools/docs

  1. Download get-installedapp.ps1
  2. Load PowerShell ISE and execute the following:
    .\get-installedapp -appname "*SQL*SERVER*" -matchall |  select-object -expandproperty AppID |   foreach-object { "msiexec /x ""$_""" } > uninstall.bat
  3. Run the generated uninstall.bat

 

To see what applications the above command would remove, run this first:

.\get-installedapp -appname "*SQL*SERVER*" -matchall  | select-object AppName,AppID

References:

 

https://social.technet.microsoft.com/Forums/scriptcenter/en-US/0a01e7e1-babf-44d0-9b3a-6d0916a5e6b7/uninstall-sql-2008-r2-with-powershell?forum=ITCG

http://windowsitpro.com/scripting/auditing-32-bit-and-64-bit-applications-powershell

http://sqlblog.com/blogs/rob_farley/archive/2011/05/24/powershell-script-to-help-uninstall-sql-server-2008-r2-evaluation-edition.aspx

How To Copy Schema And Data From One SQL Server Instance To Another

Within Microsoft SQL Server Management Studio 2008:

  1. Right click the database
  2. Select Tasks -> Generate Scripts
  3. (Click next if you get the intro screen)
  4. Select “Select specific database objects”
  5. Pick the objects to generate scripts for (tables, stored procedures, etc…)
  6. Click Next, then specify the output filename
  7. Click Finish to generate the script

This will generate the schemas only. If you want to do data generating scripts as well, in step 6) click the Advanced button and scroll down to the “Types of data to script” and change it from “Schema only” to “Data only” or “Schema and data”

Export DataSet or DataTable to Excel

Function to get the results in datatable

private DataTable GetData(SqlCommand cmd)
{
    DataTable dt = new DataTable();
    String strConnString = System.Configuration.ConfigurationManager.
         ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlDataAdapter sda = new SqlDataAdapter();
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    try
    {
        con.Open();
        sda.SelectCommand = cmd;
        sda.Fill(dt);
        return dt;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
        sda.Dispose();
        con.Dispose();
    }
}

Export to Excel

protected void ExportToExcel(object sender, EventArgs e)
{
    //Get the data from database into datatable
    string strQuery = "select CustomerID, ContactName, City, PostalCode" +
        " from customers";
    SqlCommand cmd = new SqlCommand(strQuery);
    DataTable dt = GetData(cmd);
 
    //Create a dummy GridView
    GridView GridView1 = new GridView();
    GridView1.AllowPaging = false;
    GridView1.DataSource = dt;
    GridView1.DataBind();
 
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition",
     "attachment;filename=DataTable.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
 
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        //Apply text style to each Row
        GridView1.Rows[i].Attributes.Add("class", "textmode");
    }
    GridView1.RenderControl(hw);
 
    //style to format numbers to string
    string style = @"<style> .textmode { mso-number-format:\@; } </style>";
    Response.Write(style);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();
}

Source from ASP Snippets

SqlCommand and AddWithValue – The Proper Way Of Adding Parameters To SQL Queries

When adding parameters to SQL queries in code behind ASP.NET pages, the ideal syntax is to use a SQL parameter to help build your query string, rather than creating a text string on the fly.

Example (Bad usage of inline substitution)

mySqlCommand = new SqlCommand("SELECT * FROM [PollAnswers] WHERE ([PollID] = '" + PollID + "') ORDER BY [SortOrder]", mySqlConnection);

Instead use Parameters.AddWithValue and substitute the values in place.

 mySqlCommand = new SqlCommand("SELECT * FROM [PollAnswers] WHERE ([PollID] = @PollID) ORDER BY [SortOrder]", mySqlConnection);

mySqlCommand.Parameters.AddWithValue("@PollID", PollID);

Using SqlCommand and AddWithValue Parameters To Execute SQL Insert

public bool PollCastVote(int PollAnswerID)
    {

        bool returnValue = false;

        Guid userID = Guid.NewGuid();
        DateTime dateTimeStamp = DateTime.Now;

        // connection string
        string connectionString = ConfigurationManager.ConnectionStrings["PollConnectionString"].ConnectionString;

        // Sql connection object initialized with connection string used to connect
        using (SqlConnection mySqlConnection = new SqlConnection(connectionString))
        {
            try
            {
                // open the Sql connection
                mySqlConnection.Open();

                // Sql Command object initialized with SQL INSERT
                SqlCommand mySqlCommand = new SqlCommand("INSERT INTO PollUserResponses (UserID, PollAnswerID, DateTimeStamp) VALUES (@UserID, @PollAnswerID, @DateTimeStamp)", mySqlConnection);
                mySqlCommand.Parameters.AddWithValue("@UserID", userID);
                mySqlCommand.Parameters.AddWithValue("@PollAnswerID", PollAnswerID);
                mySqlCommand.Parameters.AddWithValue("@DateTimeStamp", dateTimeStamp);

                // Execute
                mySqlCommand.ExecuteNonQuery();

                returnValue = true;

            }
            catch (Exception ex)
            {

            }
            finally
            {
                // close the Sql Connection
                mySqlConnection.Close();
            }

            return returnValue;

        }
    }

Populate RadioButtonList From SQL Database

<%@ Control Language="C#" ClassName="PollControlAsync" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System.Web.UI" %>
<%@ Import Namespace="System.Web.UI.WebControls" %>
<%@ Import Namespace="System.Text" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>

<script runat="server">
    protected override void OnLoad(EventArgs e)
    {
        base.OnLoad(e);

        BindRadioButtonListData(1);
    }

    
    private void BindRadioButtonListData(int PollID)
    {
        // connection string
        string connectionString = ConfigurationManager.ConnectionStrings["PollConnectionString"].ConnectionString;

        // Sql connection object initialized with connection string used to connect        
        using (SqlConnection mySqlConnection = new SqlConnection(connectionString))
        {
            try
            {
                // open the Sql connection
                mySqlConnection.Open();

                // Sql Command object initialized with SQL query to retrieve the categories
                SqlCommand mySqlCommand = new SqlCommand("SELECT * FROM [PollAnswers] WHERE ([PollID] = @PollID) ORDER BY [SortOrder]", mySqlConnection);   
                mySqlCommand.Parameters.AddWithValue("@PollID", PollID);             
                
                
                // Sql Data Adapter object initialized by passing the Sql Command object
                SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(mySqlCommand);


                // DataSet object to store the retrieved SQL data items
                DataSet myDataSet = new DataSet();

                // fill the DataSet
                mySqlDataAdapter.Fill(myDataSet);

                // Set DataSet object as DataSource for the RadioButtonList
                rblPollAnswers.DataSource = myDataSet;

                // Specify the Field Name that you want to display as
                // text label for Radio Button list item
                rblPollAnswers.DataTextField = "DisplayText";

                // Specify the Field Name that you want to use as
                // value for each list item
                rblPollAnswers.DataValueField = "PollAnswerID";

                // Finalize the DataBinding
                rblPollAnswers.DataBind();

            }
            catch (Exception ex)
            {
                Label1.Text = ex.Message;
            }
            finally
            {
                // close the Sql Connection
                mySqlConnection.Close();
            }
        }



    }

    protected override void OnPreRender(EventArgs e)
    {
        base.OnPreRender(e);
    }

    /// <summary>
    /// Gets or sets the poll ID.
    /// </summary>
    /// <value>The poll ID.</value>
    public int PollID
    {
        get
        {
            if (ViewState["PollID"] == null)
                return -1;
            else
                return (int)ViewState["PollID"];
        }
        set { ViewState["PollID"] = value; }
    }
  

</script>
<asp:RadioButtonList ID="rblPollAnswers" runat="server" />

Simplest ConnectionString For Local SQL Server Express

Configured nothing in SQL Server or the database (DRRSandBox). Just added new one and went. As it uses SSPI (Integrated Security), it authenticates using local user account which by default has access to database.

<configuration>
  <connectionStrings>
    <add name="PollConnectionString"
 connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=DRRSandBox;Integrated Security=SSPI"
       providerName="System.Data.SqlClient" />

  </connectionStrings>
...
..
.
</configuration>