$TaskName = "20071029-AllRejectedDuring"
$SqlServer = "SQLDEV01";
$SqlCatalog = "MyData";

# Get the T-SQL Query from .SQL file
$SqlQuery = Get-Content (".\" + $TaskName + ".sql")

#Write-Host ($SqlQuery) -foregroundcolor "gray"

# Setup SQL Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 
          "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

# Setup SQL Command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

# Setup .NET SQLAdapter to execute and fill .NET Dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

#Execute and Get Row Count
$nRecs = $SqlAdapter.Fill($DataSet)

Write-Host ($nRecs.ToString() + " Records retrieved.") -foregroundcolor "Cyan"
$SqlConnection.Close();

if ($nRecs -gt 0)
{
  # Do Stuff
  $DataSet.Tables[0].Rows[0][0]  #Print first data element
}

Earlier, I wrote about getting data from MS-SQL with PowerShell, which is a very useful and powerful thing.  But just displaying data is very limited.  I have a need at work to create extract "reports" for business owners frequently into Excel.  So I have leverage this scripting technique to do the legwork for me.  After many iterations, I have completed the first version of a script that will take a SQL query in a text file and create an Excel file with the results.  All fully automated with time-stamping, archiving of the successful query, and the ability to export XML or CSV as well.

The script is unfortunately only listed as a page on this site because WordPress does not allow posting of non-media files.  You will have to copy the full content and paste it into a text file named QuerySQL.ps1.  One day, I will host this weblog on a real provider in order to have better control (I will accept donations!).  If many folks prefer, I will post it on my personal webspace with my ISP and provide links on request.  And maybe to CodeProject too.

Feel free to use it in anyway you wish.  Please learn from it if you can, or conversely, send me a note on how to improve it.  I would love to get the field-name bolding working among other things.

It is fair to say that I don't do a huge amount of real-time SQL.  As an application developer, the vast majority of SQL I write is in Stored Procedures and packages.  Very infrequently, I need to do a manual insert statement.  So why do I always forget to drop the VALUES keyword in a T-SQL statement when using a sub-query?  I mean every time!

So here it is.  A representative INSERT statement with sub-query:

insert into UserRoleMembership
(userIdx, roleName)
 
(
  select 488, roleName from Roles
  where appidx = 18
  and roleName not in ( select roleName from UserRoleMembership where userIdx = 488 )
)

Now I won't forget.

$TaskName = "20071029-AllRejectedDuring"
$SqlServer = "SQLDEV01";
$SqlCatalog = "MyData";

# Get the T-SQL Query from .SQL file
$SqlQuery = Get-Content (".\" + $TaskName + ".sql")

#Write-Host ($SqlQuery) -foregroundcolor "gray"

# Setup SQL Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

# Setup SQL Command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

# Setup .NET SQLAdapter to execute and fill .NET Dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

#Execute and Get Row Count
$nRecs = $SqlAdapter.Fill($DataSet)

Write-Host ($nRecs.ToString() + " Records retrieved.") -foregroundcolor "Cyan"
$SqlConnection.Close();

if ($nRecs -gt 0)
{
  # Do Stuff
  $DataSet.Tables[0].Rows[0][0]  #Print first data element
}

The connection is using Integrated Security for simplicity, it wouldn't be difficult to switch to UID/PWD instead.  Also, I put the SQL in a .sql file (flat text) to make life easier; you could also put the statement in the string declaration, if it is a simple query.  PowerShell's Get-Content mechanism makes reading a file very easy.  Also, clean up after yourself, I won't include that here.

UPDATE:  I have posted a full script to export to Excel or XML in a followup post.

This really should have been more obvious to me, so why did I have to look it up?  If you need to check for NULL in a returned field from SQL (or any other datasource), you should the .Equals method on System.DBNull.Value.  I usually prefer the "==" notation for conditionals, but that's just me.  This is more efficient.  The code follows:

if (!dsSpecQuery.Tables[0].Rows[0]["device_type_end"].Equals(System.DBNull.Value)) 
{
    _EndDate = Convert.ToDateTime(dsSpecQuery.Tables[0].Rows[0]["device_type_end"]);
}

Of course, dsSpecQuery is a DataSet, and the field in question is DateTime out of SQLServer (not that it matters for the conditional).

Sometimes its necessary to script out data manipulation for deployments or automation.  The easiest vehicle in Windows is Windows Script Host (WSH) using VBScript.  Below is a very basic framework for accessing a database (MSSql) and looping a RecordSet.

''===// Retrieve Data from Database //=====
Const DBSERVER = "SQLDEV01"
Const DATABASE = "Cellular"
Const DBUSERID = "app_user"
Const DBPASSWD = "app_pass"

'' Construct ConnectionString
szADOConn = "Provider=SQLOLEDB;Network Library=DBMSSOCN;Persist Security Info=True;" &_ 
            "Data Source=" & DBSERVER & ";" &_
            "DATABASE=" & DATABASE & ";" &_
            "User ID=" & DBUSERID & ";Password=" & DBPASSWD & ";" 

Set DBConn = CreateObject("ADODB.Connection")
    DBConn.open szADOConn        
    szSqlQuery = "SELECT PhoneID, Field2, etc  FROM Import_Name WHERE PhoneID <= 200"
Set cmd = CreateObject("ADOdb.Command")
    cmd.CommandType = &H0001  ''adCmdText
    cmd.ActiveConnection = DBConn
    cmd.CommandText = szSqlQuery
Set RecordSet = CreateObject("ADODB.Recordset")
Set RecordSet = cmd.Execute(szSQLlc)

Do While Not RecordSet.EOF
  WScript.Echo RecordSet("PhoneID")  
  RecordSet.movenext
Loop

Set RecordSet = Nothing
Set cmd = Nothing
    DBConn.close
Set DBConn = Nothing

Of Course, it's possible to distill this down further, but I didn't for clarity (Consts) and it's better to not use implicit command objects by opening the database directly with the Recordset object.

Below is the most simple database query for C# to populate a GridView object:

            int nItemID = 1;
            try
            {
                using (SqlConnection oSQLConn = 
                   new SqlConnection(ConfigurationManager.ConnectionStrings["UserInfo"].ConnectionString))
                {
                    oSQLConn.Open();
                    using (SqlCommand oSQLCmd = new SqlCommand())
                    {
                        oSQLCmd.Connection = oSQLConn;
                        oSQLCmd.CommandText = "SELECT DisplayName, EMail FROM Users WHERE UserID = @PARAM_ItemID";
                        oSQLCmd.Parameters.AddWithValue("@PARAM_ItemID", nItemID);
                        SqlDataReader oSQLReader = oSQLCmd.ExecuteReader();
                        GridView1.DataSource = oSQLReader;
                        GridView1.DataBind();
                    }
                }
            }
            catch (Exception ex)
            {
                Label_ErrorMsg.Text = ex.Message;
            }

There are actually many reasons to never use this, including performance, caching, safe SQL, code reuse, standards, proper exception handling, etc, etc.  But sometimes you need a quick and dirty, non-production, piece of code for testing and/or development.  This is it.

I am not a great T-SQL developer, so I frequently struggle with some of the more advanced queries.  I spent some time building the query below, so I want to post it for future reference and the hope it may help someone else.

Say you have a dependant table for lookup values of status.  The status code in the primary table may be 1, 2, 3, etc., so the lookup table will define them as Open, Closed, Pending, whatever.  Now say for reporting you want to know how many Open or Closed items you have in the table.  The SQL query below will return each Status name with a count of items using the codes in the primary table:

SELECT OS.ObjectStateID, OS.Code, OS.Name,

    (SELECT COUNT(RS.ProductRequestID)

       FROM ProductRequestState RS

      WHERE RS.IsCurrent = 1 AND RS.ObjectStateID = OS.ObjectStateID

    )

FROM ObjectState OS

Where OS.IsActive = 1

The output will look something like this:

  OID Code              Name                              Count  
----- ----------------- --------------------------------- -----   
    1 WaitSubmit        Created                             154 
    3 Rejected          Rejected                           4785 
    4 WaitTSTechRvw     Waiting TS Tech Review               68 
    6 WaitGroupApprv    Waiting Cost/Labor/TS Tech Apprv    228 
    8 WaitExecApprv     Waiting Manager/Executive Apprv       0 
   13 Complete          Complete                          14433 
   14 WaitCostApprv     Waiting Cost Accounting Apprv         0 
   15 WaitLaborApprv    Waiting Labor and Supply Apprv        0 
   16 WaitTSTechApprv   Waiting TS Technologist Apprv         0