Using Return Values From Stored Procedures in C#.NET

Ok, let's say you have a stored procedure that returns a value.






Now lets say you want to test it in SQL Server Management Studio...






This gives us a value of 1, as expected.



Now if we want to call this from C#, there is a little trick we have to play with Parameters.






Note the trick we had to play here (line 19) to access the return value after the procedure runs.  At first, I tried using:

i=myCommand.ExecuteNonQuery();

...but that always yielded a value of -1.

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Massive ReportViewer Headache Relieved

Ok I like organization.  Too much perhaps.  But when my project root folder gets full (Microsoft likes everything in there)  I get tired of fishing around for all the parts of everything.  Did I name that thing reportInventory, ir InventoryReport?  So I like to make folders for everything, like so.

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Using SQL Server Authentication on SQL Express

For most of my databases, I am content to use Windows Authentication for my database access.  In a development or small shop environment, it's usually acceptable to let Windows bear the burden of authentication.

But if you need to add just one database app that requires a separate login beyond the Windows login, here's how.



First a few definitions.

SERVER vs. DATABASE

SQL Server (in my case express 2008 R2)  is not a Database.  In the management studio, when you log into the management studio, you can see that .\SQLEXPRESS (the root node) is called a server.  It is important to read this article carefully when I talk about servers vs. databases, as it makes a difference.



LOGIN vs. USER

This is a little more blurry, but a login is not much more than a name, password, and a set of permissions to access the server.  A user exists in databases, and has specific roles and permissions in an individual database.



Getting Started

In the Mode

First, make sure your server is in the mode (mood?) to use both kinds of authentication.

Open SQL Server Management Studio and right-click the SERVER name.  Pick properties.

Then on the Security tab, make sure SQL Server and Windows Authentication mode is selected.

If it wasn't, then you'll need to save the change and restart SQL Server.  If it was already selected you can skip this section.

Restarting SQL Server

Return to the SQL Server Management Studio and right click the Server Name.

Select restart.



Create a Server Login

When we create logins, in general we want to create one login per user. First, log into management studio using windows authentication (or however you normally gain admin access).  Under the server tree (not a database tree) select Security- Logins and pick New Login.

Add your login... 

Make sure to add a password and select the default database.

 

On the User Mapping tab...

Select the checkbox by your database.

Enter dbo as the default schema

Check every permission that doesn't contain the word deny.

Note that there are some additional roles in my database that I had added previously.

 On the Status tab, make sure grant and enabled are selected.

Click OK.

Using witch-hazel and fairy dust, Management studio will now create your LOGIN to the SERVER, and your USER in the DATABASE.



Look in the Databases tree and find the user created by the wizard.

Open his properties.

The General tab should look much like this.



Testing

To test your new user/login, open a second copy of SQL Server Management Studio.

Change to SQL Server Authentication and enter your user login info.

If you forgot the password (as I did about 6 times while writing this article) you can go back to the login tree in your first SQL Server Management Studio window and change it there, then try again.



Now it's easy to use this login in your programs to access this database.  It's also easy to set database level permissions, roles etc.



in C# to create a connection string to access the database do this:

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

SQL Server 2008 R2 Express

Three Hours later...

What a bunch of USELESS CRAP!



For those who lack a life, the text in the box below is...

The following notes apply to this release of SQL Server only.



Microsoft Update



For information about how to use Microsoft Update to identify updates for SQL Server 2008 R2, see the Microsoft Update Web site at http://go.microsoft.com/fwlink/?LinkId=108409.



Samples



By default, sample databases and sample code are not installed as part of SQL Server Setup. To install sample databases and sample code for non-Express editions of SQL Server 2008 R2, see the CodePlex Web site at http://go.microsoft.com/fwlink/?LinkId=87843. To read about support for SQL Server sample databases and sample code for SQL Server Express, see Databases and Samples Overview on the CodePlex Web site at http://go.microsoft.com/fwlink/?LinkId=110391.



Release Notes



For more information about late-breaking changes in this release of SQL Server, see the latest readme file at http://go.microsoft.com/fwlink/?LinkId=141691.



Documentation and Links



To install the .NET Framework SDK, see “Installing the .NET Framework SDK” in SQL Server 2008 R2 Books Online at http://go.microsoft.com/fwlink/?LinkId=141693.



For information about SQL Server 2008 R2 Surface Area Configuration, see the following SQL Server 2008 R2 documentation topics:



In SQL Server 2008 R2 Books Online: “Understanding Surface Area Configuration.”



In SQL Server 2008 R2 Setup Help: “Minimize SQL Server 2008 R2 Surface Area.”



In SQL Server 2008 R2 Books Online on MSDN: Understanding Surface Area Configuration at http://go.microsoft.com/fwlink/?LinkId=141692.

And the log file it left says

Overall summary:

  Final result:                  Failed: see details below

  Exit code (Decimal):           -2068052398

  Exit facility code:            1212

  Exit error code:               1618

  Exit message:                  Failed: see details below

  Start time:                    2011-03-03 15:55:52

  End time:                      2011-03-03 16:52:14

  Requested action:              Upgrade

  Log with failure:              C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20110303_155335\sql_engine_core_inst_ctp6_Cpu32_1.log

  Exception help link:           http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1



Machine Properties:

  Machine name:                  WEB1

  Machine processor count:       2

  OS version:                    Windows Server 2003

  OS service pack:               Service Pack 2

  OS region:                     United States

  OS language:                   English (United States)

  OS architecture:               x86

  Process architecture:          32 Bit

  OS clustered:                  No



Product features discovered:

  Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered

  Sql Server 2008      SQLEXPRESS           MSSQL10.SQLEXPRESS             Database Engine Services                 1033                 Express Edition      10.1.2531.0     No       

  Sql Server 2008      SQLEXPRESS           MSSQL10.SQLEXPRESS             SQL Server Replication                   1033                 Express Edition      10.1.2531.0     No       

  Sql Server 2008                                                          Management Tools - Basic                 1033                 Express Edition      10.0.1600.22    No       



Package properties:

  Description:                   SQL Server Database Services 2008 R2

  ProductName:                   SQL Server 2008 R2

  Type:                          RTM

  Version:                       10

  SPLevel:                       0

  Installation location:         d:\bf7ec28684b1818f6c3151c67288a7c8\x86\setup\

  Installation edition:          EXPRESS_ADVANCED



User Input Settings:

  ACTION:                        Upgrade

  AGTDOMAINGROUP:                <empty>

  BROWSERSVCSTARTUPTYPE:         Automatic

  CONFIGURATIONFILE:            

  CUSOURCE:                     

  ENU:                           True

  ERRORREPORTING:                False

  FAILOVERCLUSTERROLLOWNERSHIP:  2

  FARMACCOUNT:                   <empty>

  FARMADMINPORT:                 0

  FARMPASSWORD:                  *****

  FTSVCACCOUNT:                  <empty>

  FTSVCPASSWORD:                 *****

  FTUPGRADEOPTION:               Import

  HELP:                          False

  IACCEPTSQLSERVERLICENSETERMS:  False

  INDICATEPROGRESS:              False

  INSTANCEID:                    SQLEXPRESS

  INSTANCENAME:                  SQLEXPRESS

  ISSVCACCOUNT:                  NT AUTHORITY\NetworkService

  ISSVCPASSWORD:                 *****

  ISSVCSTARTUPTYPE:              Automatic

  PASSPHRASE:                    *****

  PCUSOURCE:                    

  PID:                           *****

  QUIET:                         False

  QUIETSIMPLE:                   False

  RSCATALOGSERVERINSTANCENAME:   Unknown

  RSUPGRADEDATABASEACCOUNT:     

  RSUPGRADEPASSWORD:             *****

  SQLDOMAINGROUP:                <empty>

  SQMREPORTING:                  True

  UIMODE:                        AutoAdvance

  X86:                           False



  Configuration file:            C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20110303_155335\ConfigurationFile.ini



Detailed results:

  Feature:                       Database Engine Services

  Status:                        Passed

  MSI status:                    Passed

  Configuration status:          Passed



  Feature:                       SQL Client Connectivity

  Status:                        Passed

  MSI status:                    Passed

  Configuration status:          Passed



  Feature:                       SQL Client Connectivity SDK

  Status:                        Passed

  MSI status:                    Passed

  Configuration status:          Passed



  Feature:                       SQL Writer

  Status:                        Passed

  MSI status:                    Passed

  Configuration status:          Passed



  Feature:                       SQL Browser

  Status:                        Passed

  MSI status:                    Passed

  Configuration status:          Passed



  Feature:                       SQL Server Replication

  Status:                        Passed

  MSI status:                    Passed

  Configuration status:          Passed



  Feature:                       SQL Compact Edition Runtime

  Status:                        Passed

  MSI status:                    Passed

  Configuration status:          Passed



  Feature:                       Management Tools - Basic

  Status:                        Passed

  MSI status:                    Passed

  Configuration status:          Passed



Rules with failures:



Global rules:



Scenario specific rules:



Rules report file:               C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20110303_155335\SystemConfigurationCheck_Report.htm

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

SQL Server Money Type Conversion

How to convert a SQL Server money Type to a C# floating point number.

The easiest way yo do this is to use the C# decimal type.



decimal amount = (decimal)myReader["Amount"];



I had tried double and float, and they both gave me an explicit conversion error.



FYI: when initializing a decimal, use this:



decimal mynumber=0m;



Think of the "m" as "money".



In format strings, use {0:C} (currency format) to display.



string.format("{0:C}",amount);

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

General SQL Optimization Tips and Tricks

SQL and You

SQL can get complicated.  You are simple.  That's just the way it is. 
You like issuing commands like...

select * from orders

...when all you need is a few columns.  But the SQL Server will
dutifully fetch all the data you requested, no matter the cost to the
performance of the server, the network, and your fellow database users.



The above query might not be too bad, but when you see what an order
looks like...

...you figure out you need to join in some additional data to make a
meaningful report.

so you join to the customer table, and your query becomes:

select o.*, b.billto_firstname+' '+b.billto_lastname as CustName from orders o

join buyerdata b on b.OrderID = o.OrderID

...giving us a concatenated name...



Ok, time goes on and you add joins to categorize the orders by state,
product type, salesman, etc. etc. etc. until your SQL gets into a state
we like to call gnarly.  Another thing that happens is that your
SQL is written to make life easier for you, not for the database
server.  Soon, your query gives you the perfect data, and your
report/web site/whatever looks amazing - after minutes of crunching to
gather the data.



This article is written to help you find and correct some common things
that make queries easy for us, but difficult for your database server. 
This process in general, is called query optimization and
companies pay big bucks for people who are experts at this.  Imagine a
business with 800 users entering data on their desktops.  Some are on
the phone with vendors or clients, and system performance is mission
critical.  Suddenly you launch off the un-optimized query for your
weekly TPS report, and the entire database starts chugging like The Little Engine That Could.  This means hundreds of your business
clients all start hearing words like "Geez, hang on - this screen is
loading...".  You can see the business need here.



Even if your business is small, why tax your systems needlessly if you
can take a few steps to write better SQL that gives you the data you
need faster?  I promise - if your small business grows big, that query
that generates a report out of your 1MB database in 3 seconds now will
probably take 30 seconds when you reach 10MB and 5 minutes at 100MB. 



Tip 1:  Never use Select *

Select * is great for when you just want to get a feel for what data is
in a table.  But it's the least efficient thing you can do in a
production query.  Think about it.  First, the parser has to access the
data dictionary and fetch the names and types of all the columns in the
table (or multiple, joined tables).  Then your '*' is replaced with all
the names of the columns - which (by the way) are not guaranteed to be
in any particular order under ANSI SQL specs.  If you are writing a
query for production use, one that will potentially execute thousands of
times a day, take the time to tell it which columns to return.



Tip 2: Indexes

Indexes are designed to help queries. If you're summarizing sales by
agent, then adding a secondary index to the agent column in your sales
table will save you a lot of time.  Technically the time is being spent,
but it's being used in tiny slices whenever you change data in the
sales table.  That triggers the maintenance of the index, which can then
be searched much faster than the whole table.



Tip 3: Case Insensitive Searches

Consider:

Select fname, lname, email from Customers where lastname='McHenry'

This query might work, unless you're looking for all people who entered
any of these: McHenry, mchenry, Mchenry, MCHENRY, McHENRY.  ok - so you
want to just find the customers with a case insensitive search.  So you
do this:

Select fname, lname, email from Customers where lowercase(lastname)=lowercase(@searchname)

What happens now? The query is executed not against the field "lastname"
(and any indexes applied to it), it is instead executed against the
function lowercase(), which has no index.  There are two
common workable solutions to this issue.   Neither of the solutions is
to make sure all data is entered using strict capitalization
standards. No one wants to be the Capitalization Nazi!

1. Use a function index.  Oracle supports these. you can actually make
   an index on "lowercase(field)" or "getmonth(datefield)".  This is not a
   widespread feature, but is really useful in these cases.


















2. Add a lower or upper column in your data to use for joining and
   searching.  If you have an ASP.NET membership provider database, note
   that the aspnet_users table has a column named LoweredUserName. 
   If this approach is good enough for Microsoft, it's good enough for me.
   

Tip 4: Data Warehousing

Sometimes you just *must* have that report that performs a 23 table join
against a 40 million record database.  There's just no way you can put
out your TPS reports without it.  What many companies do is perform
certain data mining and harvesting tasks once per day, rolling up and
summarizing certain data into a "data warehouse" for offline reporting
needs.  This data is often de-normalized to make faster queries and it's
usually stored on a different database server and even a different
network to prevent reporting from slowing down the workday.

The idea is that you create a query that performs certain common joins
and relations... rolls up hourly/daily/monthly or whatever kind of
totals, creates various types of crosstabs, and stores the rolled up
data in a reporting database.

These summaries are run during off hours (or infrequently during the
workday) to prevent the database servers from clogging with work while
you're open for business.

Managers and their lackeys can hammer away at the reporting database all
day long and your main operation could not care less.



Tip 5: Joins vs. Sub-Selects

Sometimes when you just want a single lookup from a table (say decoding a
County ID to a name) , it's faster to not join in the county table. 
Some SQL servers can detect this kind of lookup and perform this
optimization for you.  Note...

select U.UserName, RR.RoleName from aspnet_Users U

join aspnet_UsersInRoles R on R.UserId=U.UserId

join aspnet_Roles RR on RR.RoleId=R.RoleId

We have joined the aspnet_UsersInRoles table to get the ROLEID of the
user's roles, and joined the aspnet_Roles table to get the name of the
roles for the user.



Note the difference here:

select U.UserName,

(select RR.RoleName from aspnet_Roles RR where RR.RoleId=R.RoleId) as Role

from aspnet_Users U

join aspnet_UsersInRoles R on R.UserId=U.UserId

See how we're using the sub-select to pick up one field?  The thing
about this tip is that sometimes the former way is faster and sometimes
the latter.  You have to experiment.



Tip 6: Explain Plan/Query Optimizers

Ok, most SQL Servers have a module called a Query Optimizer.  This
module can take your weak, human SQL and tweak it before execution to
improve performance.  But there is also usually a UI that you can use to
performance tune your queries.  I know Oracle has this, and have used
it many times.  It breaks your query up into chunks called a query
plan, and assigns an estimated cost to each part of the
plan.  You can then alter the text of your query to improve the overall
performance.



Tip 7:  Query Hints

On the rare occasions when your SQL is tricking the optimizer into
taking the wrong approach to your query, Oracle and SQL Server have the
ability for you to add query hints to your SQL.  These hints can
tell the optimizer to use a specific kind of cursor, or prefer a
different index, and alter the query plan used during execution.

The syntax differs greatly between the various SQL servers
(Oracle/MSSQL/etc.), and will be shown only as an example here.

1. SQL
   Server


2. More SQL
   Server


3. Oracle

Tip 8: Views

Sometimes creating a view can help optimize your query.  The idea is to
optimize the SQL for certain common transactions and save it as a view,
making the need to reinvent the wheel significantly lower.  But there is
also such a thing as a materialized view. This is a type of view
that is maintained like an index, as the live data is stored in the
main tables.  A normal view does not store it's own data, but a
materialized view does.  The view's data can then be queried without
having to do whatever complex joins were needed to generate the data. 
Of course the trade-off here is storage space.

Using MSSQL to Calculate Map Distances

This procedure will show you how to create a SQL Server function that accepts 2 map coordinates in and calculates the surface distance between them.



First we will create the function.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Bryan Valencia
-- Create date: 12/16/2009
-- Description: takes latitude/longitude for 2 
--   places on Earth and gives the spherical distance.
-- =============================================
DROP FUNCTION Geo_Distance
GO 

CREATE FUNCTION Geo_Distance
( 
 @lat1 float,
 @long1 float,
 @lat2 float,
 @long2 float
)
RETURNS float
AS
BEGIN
 
 DECLARE @r float 
 --radius of the Earth
 --select @r=3437.74677 --(nautical miles)
 --select @r=6378.7 --(kilometers)
 SELECT @r=3963.0 --(statute miles) 
 
 --radians
 DECLARE @radlat1 float
 DECLARE @radlong1 float
 DECLARE @radlat2 float
 DECLARE @radlong2 float
 
 SELECT @radlat1 = RADIANS(@lat1)
 SELECT @radlong1 = RADIANS(@long1)
 SELECT @radlat2 = RADIANS(@lat2)
 SELECT @radlong2 = RADIANS(@long2)
 
 --calculate answer (from http://www.sqlteam.com/article/intro-to-user-defined-functions-updated)
 -- and http://www.meridianworlddata.com/Distance-Calculation.asp
 
 DECLARE @answer float
 SELECT @answer = @r * ACOS(SIN(@radlat1)*SIN(@radlat2)+COS(@radlat1)*COS(@radlat2)*cos(@radlong2 - @radlong1))
 RETURN @answer

END

GO

So now we have a function to accept two lat/long coords and return the distance.  To use it in a select, (assuming you have a data table of geodata organized by zip code, like this... Access Zip Code Database.



Import this data to SQL Server and then use this select statement.

Select distinct Z1.[ZIP Code], Z1.City, Z1.[State Code], 
 dbo.Geo_Distance(
  cast(Z2.Latitude as float),cast(Z2.Longitude as float),
  cast(Z1.Latitude as float),cast(Z1.Longitude as float)) as Distance 
 
from [ZIP Codes] Z1
left outer join [ZIP Codes] Z2 on (Z2.[ZIP Code]='94558')
order by 4

Note here that the data as presented in the Access table stores the latitudes and longitudes as text, so we need to use cast to force it to floats. This gives us a result set that looks a lot like this...