The Penton-izer

A better, more manageable DataReader

2004-09-20T12:57:00Z

I had mentioned to the Plano DNUG when I presented a while back that I didn't like the idea of passing back a DataReader at all from a DAL. You lose control of what happens to it when you do (as in the DAL no longer has the responsibility of closing the reader and ensuring its disposal). Well, Teemu Kieski had a great idea for how to handle this situation in a new article “Using Delegates with DataReaders to control DAL Responsibility“ on AspAlliance. For a typical developer, it is still a bit advanced but I think the complexity that it hides and the elegance of execution far outweigh that. I'll be updating my DAL for this...

Not-so Dynamic Sql

2004-09-08T18:02:00Z

Suppose you need to exclude or include parameters to a sql statement. Let's take this example:

CREATE PROCEDURE dbo.myProc
    @pkA int
    , @pkB varchar(10)
    , @pkC decimal(12, 2)

AS

SELECT
    t.colA, t.colB, t.colC
FROM
    dbo.myTable t
WHERE
    t.pkA = @pkA
    AND t.pkB = @pkB
    AND t.pkC = @pkC

This procedure expects three parameters, as does the sql statement. So we want to omit the parameters based on being set to NULL. There are several ways to do this:

Use dynamic sql in your client language
Use dynamic sql in your stored procedure
Use conditional logic in your query with various combinations of OR, AND or COALESCE/ISNULL in your query
Use structured dynamic sql in a stored procedure

Let me give an example of each:

Build the sql in a string such as this:

string sql = “SELECT colA FROM tblA“;
if(prmB != null && prmB.Length != 0) sql += “ WHERE colB = '“ + prmB.Replace(“'“, “''“) + “'“;

You have to contend with ensuring that your logic in the final query is correct. This is easy with a single parameter, but when you end up having more than one in your WHERE clause (or other clauses) then the logic can get quite complex. We also needed to deal with SQL Injection. Of course, this is more easily dealt with by using parameters on command objects (like using a SqlParameter with a SqlCommand object).
This is similar to the above but you are doing the logic in the stored procedure. You still need to contend with sql injection in this case.
Conditional logic in your query means resorting to using OR and AND when needed:

SELECT colA FROM tblA
WHERE (colB = @colB OR @colB IS NULL)

-- OR --

SELECT colA FROM tblA
WHERE colB = COALESCE(@colB, colB)

This suffers from branching logic in your query. It becomes more an more difficult for SQL Server to make a realistic execution plan for this type of query, especially when more conditions are present.

Use structured dynamic sql in your procedure:

CREATE PROCEDURE dbo.myProc
    @pkA int
    , @pkB varchar(10)
    , @pkC decimal(12, 2)

AS
SET NOCOUNT ON 

DECLARE @sql nvarchar(4000) 

SET @sql = N' 
SELECT 
  t.colA, t.colB, t.colC 
FROM 
  dbo.myTable t 
WHERE 
  1 = 1 
  @pkAREP 
  @pkBREP 
  @pkCREP'

IF @pkA IS NOT NULL
  SET @sql = REPLACE(@sql, '@pkAREP', 'AND a.pkA = @pkA')

ELSE
  SET @sql = REPLACE(@sql, '@pkAREP', '')

IF @pkB IS NOT NULL
  SET @sql = REPLACE(@sql, '@pkBREP', 'AND a.pkB = @pkB')

ELSE
  SET @sql = REPLACE(@sql, '@pkBREP', '')

IF @pkC IS NOT NULL
  SET @sql = REPLACE(@sql, '@pkCREP', 'AND a.pkC = @pkC')

ELSE
  SET @sql = REPLACE(@sql, '@pkCREP', '')


EXECUTE "dbo"."sp_executesql" 
  @sql 
  , N'@pkA int, @pkB varchar(10), @pkC decimal(12, 0)' 
  , @pkA = @pkA 
  , @pkB = @pkB 
  , @pkC = @pkC 

RETURN 0

I want to discuss option #4 above. I believe it to do a number of things for the developer:

Preventing SQL injection because you are using parameterized queries
Caching query plans because you are using sp_executesql
Controlling the data coming in to the procedure
Preventing branching logic in your WHERE clause in the query

I actually would do a little more with the '1=1' part, but for clarity in this post I am not. Also, in my queries I typically have values that are always required so the '1=1' isn't necessary. Of course, as the developer you need to ensure that you don't inadvertantly put any sql injection points in your dynamic portions of your sql. But this would get caught in a code review, right? ;-)

More on Interfaces and ADO.Net

2004-06-03T14:28:00Z

I spoke at the Plano DNUG last night and had a pretty good time. Basically I presented some information on interfaces, talked about the interfaces in System.Data, discussed what happens behind the scenes with SqlConnection.Close() and SqlConnection.Dispose(), presented issues with creating a data layer, then some discussion on the DAAB. One of the things I didn't show in the meeting last night is how you would deal with parameters. It is a little different. First off, how about a link to my files? :-)

Presentation Files

So, here is a code snip for executing a command on a SQL Server database:

using(DbHelper db = new DbHelper(Configuration.ConnectionString, DbLibrary.SqlClient, true))
{
    IDbDataParameter prmDiv = db.GetDbDataParameter("@division", DbType.Int16);
    prmDiv.Value = System.Int16.Parse(this.ddlDivisions.SelectedItem.Value);

    IDbDataParameter prmOrgStore = db.GetDbDataParameter("@original_store_num", DbType.Int32);
    prmOrgStore.Value = System.Int32.Parse(orgStore);

    IDbDataParameter prmTrgStores = db.GetDbDataParameter("@target_store_num_xml", DbType.AnsiString);
    prmTrgStores.Value = xml.InnerXml;

    IDbDataParameter prmExecutingLoginId = db.GetDbDataParameter("@executing_login_id", DbType.AnsiString, 20);
    prmExecutingLoginId.Value = EM.Web.Configuration.WebUserId;

    db.ExecuteNonQuery(CommandType.StoredProcedure
        , spStorePlanCopy, prmDiv, prmOrgStore, prmTrgStores, prmExecutingLoginId);
}

This means that instead of using things like SqlDbType you are using the base DbType instead.

Something that I don't have in this [and that I want] is Parameter Caching. That would be great, but I opted to not put that in here for now. Why? Because I'd rather examine the Microsoft DAAB a little closer to see if there are some other ways to accomplish that. Also, not every provider/driver accepts parameterized sql statements (at least with some of the things I have developed before). So, before I add it in, I wanted to make sure that I do the right things with it. That could mean some sql statement parsing, so I wanted to let that go until later.

So, if you download my presentation, at a minimum check out the DataAccess project. That was the meat and potatoes of this presentation. Let me know what you think!

More on inserting XML to SQL Server

2004-04-15T04:48:00Z

First off....I know I badly need to blog more. Life just keeps running me over (enough of the excuses). My friend Steve Schofield wanted me to blog more on inserting XML into SQL Server. So, I am going to post a number of things. All that actually work by downloading the files.

[1] Table layout (dbo.TestMessages.TAB)
[2] Stored Procedure (dbo.InsertMessageXml.PRC)
[3] C# code with some XML

I am also posting a zip of everything (compiled EXE also). Please change XmlInsert.exe.config file to point to your database. Then, take the dbo.InsertMessageXml.PRC and dbo.TestMessages.TAB files and execute them to put those objects in your database.

These files are based on the code in my previous blog entry.

SQLXML: Steps to usage

2004-03-22T13:43:00Z

Every once in a while people post to the lists that I am on (AspAdvice, SSWUG, 15seconds, VISBAS-L) asking about how to pass lists of data into stored procedures. There are a number of ways to accomplish this, some of which include (but not limited to):

[1] Insert into a temporary table on the client, use that in the procedure
[2] Call the stored procedure multiple times with the next value
[3] Pass in a delimited string to the procedure. Then, have a UDF or other procedure to split this into a table so set processing can be done with it.
[4] Pass in an XML string and use it as a table

There are pros and cons about each method listed above. I will focus on the fourth method.

One of the benefits to passing in an xml string of data is ease of ability to maintain a heirarchy. You can pass in multiple levels of data and multiple values for a single logical row of data as well. This offers much flexibility to the developer in terms of passing data to a SQL Server database. I'll focus on SLQ Server 2000 (there are much more XML capabilities in Yukon). So, let's start with some basics. You can have element-centric documents, attribute-centric documents, and mixed. Here are examples of that:

Element-centric data:
<messages><message>This is a message<po>123454321</po></message></messages>

Attribute-Centric data:
<messages><message text=“This is a message” po=“123454321” /></messages>

Mixed data:
<messages><message text=“This is a message” po=“123454321”>Here is inner text</message></messages>

SQL Server can use any of these types, but I tend to use Attribute-centric data more often, mostly for the smaller payload. These can come into a stored procedure with any of the character datatypes (char/varchar, nchar/nvarchar, text/ntext) I typically use TEXT because I don't deal with National Text (extended character sets) very often. And, since TEXT has an uppe rlimit of 2GB of data, I am pretty confident that I will not overflow with whatever I send. Now, let's look at the methods we would use to utilize this xml in SQL Server. When this data is passed into a stored procedure we have to tell SQL Server that this is XML. We use sp_xml_preparedocument for that. sp_xml_preparedocument is used to associate some text [that is in XML format] in a node tree that can be XPath searched. It can then be binded to a rowset provider [such as OPENXML] and be treated like any other table or view. With this binding, we must be able to deallocate it. sp_xml_removedocument is used to deallocate an xml node tree. This is vital to SQL Server as neglecting to release resources can lead to memory leaks. The main construct in SQL Server ot use tihs isthe OPENXML clause in a query:

OPENXML(
idoc int [in], rowpattern nvarchar[in], [flags byte[in]])
[WITH (SchemaDeclaration | TableName)]

idoc is a handle to an XML document that was prepared with sp_xml_preparedocument

rowpattern is an XPath string that tells OPENXML where to start or search

flags is a bitmask value that tells OPENXML to use element or attribute centric mapping

SchemaDeclaration is a table layout mapped to SQL data types

TableName is an existing table that OPENXML can use to map elements/attributes directly to columns by name

The rowpattern is a typical XPath query:

/messages/message/segment
/messages/message/segment[@unit_cost>5.0]

This is an NVARCHAR string and can be assigned programmatically in your stored procedures. Flags are used to define the mapping to the relation:

[] 0: Defaults to attribute-centric mapping
[] 1: Use attribute-centric mapping
[] 2: Use element-centric mapping
[] 8: Can combine element and attribute centric mappings (deals with @mp:xmltext as well)

Even if you are using flags 1 or 2 you can still access values across those boundaries (i.e. use attribute-centric mapping and still access element values) but there is a performance penalty. SchemaDeclaration or TableName: This tells OPENXML the name, type and location of data it is using. Using the TableName is much easier, but it is not flexible. SchemaDeclarations are much more versatile.

TableName:

SELECT * FROM OPENXML(@idoc, ‘/ROOT’, 1)
WITH dbo.MyTable

SchemaDeclaration:

    SELECT a.* FROM OPENXML(@idoc, ‘/ROOT’, 1)
    WITH (
        colA varchar(10) ‘@myAttributeA’
        , colB int ‘@myAttributeB’
    ) AS a

More information about SchemaDeclarations

Be sure that you have the right datatypes defined. Conversions are not always automatic. For instance, ‘’ (a zero-length string) will implicitly convert to an integer but will not implicitly convert to a decimal/numeric. If your data is variable (as in it could be a ZLS) then you can set your ColumnPattern to a VARCHAR and then manage your conversions in the SELECT clause.

Let's look at some basic code for this (leaving out error code for simplicity):

StreamReader sr = new StreamReader(fileName);
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("return", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("@xml", SqlDbType.Text).Value = sr.ReadToEnd();
sr.Close();
conn.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
conn.Dispose();

The XML we want to pass in:

<?xml version="1.0"?>
<messages currentDate="2003-09-08T15:40:31">
    <message po="100000006980" version_nbr="1" not_before_date="2003-09-11">
        <segment item="5234004210108" unit_cost="6.25" qty="6" />
        <segment item="5234004210109" unit_cost="26.25" qty="6" />
        <segment item="5234004210110" unit_cost="16.25" qty="6" />
        <segment item="5234004210111" unit_cost="26.25" qty="6" />
        <segment item="5234004210112" unit_cost="26.25" qty="6" />
        <segment item="5234004210113" unit_cost="26.25" qty="6" />
    </message>
    <message po="100000006982" version_nbr="1" not_before_date="2003-09-29">
        <segment item="5238052010705" unit_cost="145.40" qty="4" />
        <segment item="5238052010706" unit_cost="145.40" qty="4" />
        <segment item="5238053010605" unit_cost="15.40" qty="20" />
        <segment item="5238053010606" unit_cost="15.40" qty="20" />
    </message>
</messages>

And the SQL:

CREATE PROCEDURE dbo.MyProcedure
    @xml TEXT
AS

SET NOCOUNT ON

DECLARE @ret int, @iXml int

EXECUTE @ret = [master].[dbo].[sp_xml_preparedocument] @iXml OUTPUT, @xml

INSERT INTO [dbo].[XmlTestTable]
SELECT
    a.po, a.version_nbr, a.not_before_date, a.item, a.unit_cost, a.qty
FROM
    OPENXML(@iXml, '/messages/message/segment', 1) -- start at the message segment node
WITH (
    po decimal(12, 0) '../@po'
    , version_nbr int '../@version_nbr'
    , not_before_date datetime '../@not_before_date'
    , item char(13)
    , unit_cost decimal(8, 2)
    , qty int
) AS a

-- release the XML document now that we are done
EXECUTE [master].[dbo].[sp_xml_removedocument] @iXml

GO

There is quite a bit more of this. I'll post more later in the week.

"My eBooks" folder: Die!

2004-03-18T11:43:00Z

In case anyone *hates* the "My eBooks" folder that gets created in "My Documents" when
Adobe Acrobat opens, here is the *fix*:

http://www.annoyances.org/exec/forum/winxp/n1072006739

My...that thing was annoying!

More on Interfaces for System.Data

2004-03-16T18:09:00Z

Aaron blogged about interfaces and asked about people usage. I have been using them more and more in code. Here are some examples of closing and disposing of System.Data (and Microsoft.Data) resources using interfaces:

static void DisposeIDbDataAdapter(ref IDbDataAdapter da)
{
 if(da != null)
 {
   if(da is IDisposable) ((IDisposable)da).Dispose();
   da = null;
 }
}
static void DisposeIDataReader(ref IDataReader dr)
{
 if(dr != null)
 {
   dr.Dispose();
   dr = null;
 }
}
static void DisposeIDbCommand(ref IDbCommand cmd)
{
 if(cmd != null)
 {
   cmd.Dispose();
   cmd = null;
 }
}

static void DisposeIDbConnection(ref IDbConnection conn)
{
 if(conn != null)
 {
   if(conn.State != ConnectionState.Closed) conn.Close();

   conn.Dispose();

   conn = null;
 } 
}

Ok...some questions I am sure will come of this. First off, why do this? Well, these methods can be used for any of the major providers (SqlClient, Odbc, OleDb, OracleClient and others). Much less code to write and maintain. Why the ref keyword? Well, if you have been keeping up with Paul Wilson (here, here, and here) you'd see that his tests show there is some benefit to setting variables to null (Nothing in VB.Net). I use the ref keyword to allow me to operate on the original variable (not only the original object). Why the check on IDbDataAdapter? Well, System.Data.IDbDataAdapter does not implement IDisposable, but the major providers do implement IDisposable (it is in its inheritance chain). Also, why do I call IDbConnection.Close() AND IDbConnection.Dispose()? If you check System.Data.SqlClient.SqlConnection in Anakrino, you'l see that Dispose(bool disposing) does indeed call Close(). Note the signature diferences? We can only call the publically available Dispose() method, not the protected Dispose(bool disposing) method. We have to depend on the framework to manage that. So, even if doing all of this doesn't buy me much, what it does buy me is refactored and self-documented code. Of course, don't forget the C# comments that belong on these functions. :-)

One other thing to add: The using keyword. Why not always use that? You can use it when your coding methodology calls for it. In fact, use it as much as you can. Where it makes sense.

Are there better ways of doing this? I am sure that there are. It would be short order to deal with that with this framework. If anything refactoring and self-documentation are worth it to me.

SQL Server 2000 System Table Map

2004-03-16T12:56:00Z

I thought it would be worth mentioning again that there is a compiled help file called “SQL Server 2000 System Table Map” which is right here. I believe it to be a very valuable documentation tool for the database administrator, as well as the database programmer. An understanding of the lower levels of a product is always a good thing, no?

Yes, I know it will change for Yukon, but people will be on SQL 2000 for some time still.

Security Warning

2004-02-23T13:59:00Z

Something I was reading on a list I am on made me think of security. Periodically I do searches on Google to see how secure some sites are designed. I won't put my actual search here, but if you search for specific parts of connection strings you may occasionally find more information than you bargained for (such as user names and passwords). Most people are pretty vigilant about not exposing any of this. Sometimes this information comes through from comments on a webpage. Typically when I make this type of search I'll find 2 or 3 sites that have these errors and I promptly notify them. This is just a heads-up to developers out there to make sure you aren't subject to this kind of error.

The Axiomatic Method

2004-02-23T13:17:00Z

So, I have started blogging. I had been thinking about how and what I would start this off. It occured to me that I should talk about something that I had learned throughout my life but never knew the name of until college. Even after that, I wasn't sure ow to describe the methods I take when designing systems. Encyclopædia Britannica defines the Axoimatic Method:

in logic, a procedure by which an entire system (e.g., a science) is generated in accordance with specified rules by logical deduction from certain basic propositions (axioms or postulates), which in turn are constructed from a few terms taken as primitive

Simply put, we have it as a "system" with "rules". Looking at this from another angle, each rule could be defined as a system by itself. I suppose this could be likened to a parent-child relationship in the sense that you could have something like

system
--> rule1
        --> rule3
        --> rule4
--> rule2
        --> rule5

where rule1 and rule2 are "systems" themselves. Enough of this meta-speak...so how do I want to relate this to programming in general? Actually, I'd say more specifically system design in general.

Suppose your boss comes in to your office/cubicle and tells you "I need a system that will give me a report on widgets in the warehouse. I need it as soon as possible." Some paths you can take include [] make a basic system from existing resources based on what your boss told you and [] ask questions. From the beginning, you have to think about how this is to be designed. Of course, your design could be a simple, static webpage that you put together on demand (when your boss tells you to). You could make it where someone enters all of the data into your system. You might even want to pull data from an existing system. All somewhat viable options. But, you of course need to dig deeper into what your boss' vision might be. You will most likely need some sort of input mechanism. Possibly some filtering and sorting. Perhaps different style reports (aggregates and such). Each of these functional requirements depends on rules for definition. Each of the functional requirements make up the system you are defining. The smaller each "rule" is, the easier it is to understand and adjust when needed. Also, and this is fairly important...the easier it is to reuse the rule anywhere else in the system when appropriate.

This moves into another topic that I'll just mention here: refactoring. This book is probably one of the most important books to get you started down the path of good system design.

How do you use this in real life? Start looking at your designs from the perspective that even if your "system" is small, you can add to it easily. Consider that youmay need to modify bisiness rules in the future. Make that easier to do without reworking the system design. Realizing what scope your rules are makes these processes easier.

Apply at least some of these ideas to your current programmatic methods and I'll bet a better system will come of it.