SQL Server SP Parameter Default Value

SQL Server does not allow creating a stored procedure with parameter default values that are not constant. This can be a problem if you want to give a parameter a default value that is predictable but not constant such as giving a datetime parameter a default value of the current time.

There are two ways to resolve this. One is solution but it's not very flexible. The other is a workaround but can be used in more situations. First the solution. If the parameter is used as a minimum or maximum value check, you can totally ignore the value if it's null.

Here is an example of the first solution. Let's say we want to write a stored procedure to get all records from an Activity table that occured from one point in time to another. In this case we want to include all records if the boundry variable is not specified. In the example below we simply do this by checking for NULL and using the logical OR.

CREATE PROCEDURE GetActivityRecords
@From DATETIME
@TO DATETIME

AS
 BEGIN
   SELECT * FROM Activity
   WHERE (@From is null or LogTime >= @From)
   and (@To is null or LogTime <= @To)
 END

Another way to do this that is more flexible is to check if the parameter is null and in that case set it equal to whatever value you wish.

For example, in the code below I check if the @To variable is null and if it is I set it equal to the current time.

CREATE PROCEDURE GetActivityRecords
@From DATETIME
@TO DATETIME

AS
 BEGIN
   IF @To is null
   BEGIN
      SET @To = GetDate()
   END

   SELECT * FROM Activity
   WHERE (@From is null or LogTime >= @From)
   and LogTime <= @To
 END

C# .NET Default Access Modifiers

It's good to refresh what the default access modifiers are in C#. Default access modifiers could explain why you can't see something you declared when expect to see it and vice versa.

First let's quickly review all the access modifiers in C#.
private: Can be accessed by members of the same class only.
protected: Can be accessed by members of the same class plus members of derived classes only.
internal: Can be accessed by all that is in the same assembly regardless of what class they are in. But can only be accessed from the same assembly.
public: Can be accessed by everything.

The general rule is that the default access modifier is as secured as possible for the declaration context. This does not mean that everything is private by default because private does not make sense for everything. So here is the breakdown.

Data types: class, struct, enum
For data types such as classes the most strict reasonable access modifier is internal. And that is exactly the default modifier. So when declaring a new class it is internal by default so that it can be accessed by other classes in the same assembly. It does not make sense for a class to be private by default because it would inaccessible.

Members: variables, methods
For member variables and methods which are inside a certain data type from the list above, the most restrictive reasonable modifier is private and that is it. Any time a member is declared with no access modifier, it is private by default.

Inline ASP.NET Tags Differences <%#, <%, <%=, <%$, <%--, <%@

It gets confusing at times when figuring out which ASP.NET tag is best to use. Moreover, it is good to know the settle difference between a couple of the similar tags. One might think that for example the "<%#" tag and "<%=" are the same for certain cases and can be used interchangeably. Actually they are different and in most cases only one of them would work unless some code change is done. So here is a summary of the tag attributes and the differences amongst them:

<% ... %>

This tag is used to insert code into an ASP.NET page usually with a .aspx extension. One of the most common use of this tag is conditional statements where you can choose to display certain content only when a condition is met.

<% if(isLoggedIn){ %>
   Hello user
<% } else { %>
   Please login
<% } %>

For more info:
http://msdn.microsoft.com/en-us/library/ms178135(vs.80).aspx

<%# ... %>

This tag is used with data binding. It can be used with DataBinder.Eval() or DataBinder.Bind() or just with any protected or public member as shown in the example. The trick about this tag is that it must be used inside a server-side element (with runat="server"). Also the DataBind() method of that element must be called at some point. However, certain ASP.NET elements automatically call their DataBind() method in their PreRender() methods. So there is no need to is explicitly call their DataBind() methods. Such elements are GridView, DetailsView, and FormView.

<div id="div1" runat="server">
   Hello <%# userName %>
</div>

In the code behind:

protected void Page_Load(object sender, EventArgs e)
{
   div1.DataBind();
}

For more info:
http://msdn2.microsoft.com/en-us/library/ms178366.aspx

<%= ... %>

This tag is similar to the above tag in the sense that it is used to evaluate the value of a protected or public member variable. The tag evaluates the ToString() method of the variable used and displays it. The nice thing about this tag is that it does not need to be inside a server-side element and its parent element does not even need to have a name.

<div>
   Hello <%= userName %>
</div>

For more info:
http://msdn.microsoft.com/en-us/library/6dwsdcf5(VS.71).aspx

<%$ ... %>

This tag is used to evaluate expressions in a configuration file. It is usually used for connection strings but it can be used with AppSettings and other configurations.

<asp:SqlDataSource ID="SqlDataSource1" Runat="server" 
    SelectCommand="SELECT * FROM [Employees]"
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString1 %>">
</asp:SqlDataSource>

For more info:
http://msdn.microsoft.com/en-us/library/d5bd1tad.aspx

<%@ ... %>

This is usually used at top of a page as a directive to register controls or import a library.

<%@ Register TagPrefix="uc" Namespace="MyCustomUserControl" %>

For more info:
http://msdn.microsoft.com/en-us/library/xz702w3e(VS.80).aspx

<%-- ... --%>

This tag allows you to add server-side comments which would never show in the HTML output. This is different from using an HTML comment (<! -->) because HTML comments do get rendered in the outputted HTML.

<%-- this is a comment --%>

For more info:
http://msdn.microsoft.com/en-us/library/4acf8afk.aspx

That should be a good summary but there is obviously more to some of tags.

ASP .NET Connection Strings in Web.config File

ASP .NET offers an elegant way to store data source connection creditentials. Connection strings should be stored in a .config file, commonly and by default the Web.config file.

Firstly, what are connection strings? Connection strings are strings of text that contain information about a data source used in the code for data access. Information contained in a connection string includes the data source name, data source address, security mode, login creditentials, and the data source type.

There are a couple reasons why the Web.config file is a good place to store connection. One, ASP .NET is automatically configured to never display a file with a .config extension. So you can assume a level of security when storing login information in the Web.config. In addition, ASP .NET can be configured to encrypt the Web.config file, which would add more security. Nonetheless, you can be assured that any web request to a .config file would result in an error. Secondly, using Web.config allows a central way to manage information without requiring digging into the code nor code modification.

Below are examples of how connection strings would be used:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
 <connectionStrings>
  <add name="connectionName" connectionString="Data Source=serverName;
  Initial Catalog=databaseName;User Id=username;Password=password;"
  providerName="System.Data.SqlClient"/>
</connectionStrings>
...
</configuration>

To retrieve the above connection string in C# code:

string connStr = ConfigurationManager.ConnectionStrings["connectionName"];

To use the connection string directly in an ASP .NET page:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
  ConnectionString="<%$ ConnectionStrings:connectionName %>"
  SelectCommand="SELECT * FROM [Employees]" />

Reading/Writing Data with ADO .NET in C#

In .NET coding there are two main methods of handling database data using ADO.NET. The two methods are distinguished by the classes used to access and manipulate the data. The classes are DataReader and DataSet. I will briefly go over each class and major differences between the two in addition to some examples.

DataReader

The DataReader is the core class for retrieving data from a database in ADO.NET. When using ADO.NET to read data from a database, a DataReader is always used although sometimes implicitly. Even in the case of using a DataSet, the DataAdapter that is used to populate the DataSet uses a DataReader internally.
Now a few things about the DataReader. A DataReader object can merely provide read-only access to a database and only in a forward direction. The DataReader is connection based, meaning the connection to database is maintained while data is read by the DataReader. The DataReader class is efficient and has a relatively small memory fingerprint. If no data writing is required, a DataReader is usually the best choice.

Example

using (SqlConnection conn = new SqlConnection(connectionString))
{
   SqlCommand command = new SqlCommand("SELECT * FROM Categories");
   command.Connection = conn;
   conn.Open();
   SqlDataReader dr = command.ExecuteReader();
}

DataSet

As we have seen above, the DataSet aproach also uses a DataReader. However, the difference is that the DataReader is used to read the data into a DataSet object which retains the data in memory. Once in memory, the data can be manipulated as the programmer wishes. After the data is modified inside the DataSet, it can be written back to a database.
The DataSet is a connectionless object. Once the data is read into the DataSet, the connection to the database is halted. Clearly, using a DataSet to hold records has a memory cost. While in memory, the data can be modified multiple times and when finalized written once to the database. If data memory retention or modification is needed, a DataSet is usually the solution.

Example

using (SqlConnection conn = new SqlConnection(connectionString))
{
   SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Categories",conn);
   DataSet ds = new DataSet();
   ad.Fill(ds);
}