JohnPapa.net

Need a "WHERE field IN (x, y, z)" Clause with LINQ

Tuesday, May 20 2008 - sql, linq - 2 comments

A colleague and I were talking about some LINQ features yesterday and one of the topics that came up was how to implement an IN clause. Ya know, similar to how SQL statements can use an IN clause in the WHERE clause like this:

SELECT city FROM Customers WHERE state IN ('FL', 'NY'', 'NC')

Then I remembered Dan Wahlin's post on a similar topic where he shows the following solution (from Dan's post):

The code uses the Contains() method to search for a specific ProductID within the collection which ends up creating a WHERE IN type query.  You can do the same thing with LINQ:

public static Product[] GetProducts(Guid[] prodIDs)
{
   return (from p in GetProducts()
           where prodIDs.Contains(p.ProductID)
           select p).ToArray<Product>();
}

The idea here is that the LINQ query is written normally (meaning the from and the select clauses reference the objects). Then the where clause in the LINQ query operates on an array of values. The array of values contains the values you would put in the IN clause. The array supports standard query operators so you can use the Contains method on the array and pass in the value you want to look for.

Silverlight, WCF and LINQ to SQL

Wednesday, May 07 2008 - silverlight, entity-framework, linq, sql, wcf - 1 comments

I've had some requests lately to write some articles that work with LINQ to SQL. I've ben focusing on the Entity Framework a lot due to its vast nature and that fact that its still relatively unknown to many people. So I am going to work on a LINQ to SQL project that ties into a multi tier model. I am also going to show how this can be hooked into a  Silverlight 2 UI that connects through WCF to the lower layers.

I'll probably be demonstrating these first at an event I am trying to set up with Joe Healy of Microsoft in Tampa this coming August. The demos will be ready way before then, but I wanted to have some cool materials for this event. There is no title nor date yet, but once the event is set I will be sure to let blog about it. It should be a great day overall.

I also plan on giving some love to the Entity Framework, regular persistence/mapper models, MVP patterns and more. I just have to find a way to work it all in and still give enough to each topic. Of course, this means all demos are subject to change, but the content will be fresh and cool for certain.

Here is a very poorly and quickly thrown together diagram of one of the demos I am tweaking.

Data Access "Practically Done"

Friday, February 01 2008 - net, ado-net, entity-framework, linq, ent-lib, sql - 3 comments

I will be kicking off my move to South Florida in a few weeks with a 1 day event for the “Practically Done” event series. On Wednesday August 1^st and on Saturday August 4^th I will be presenting “Data Access Done Right”, a full day event in Orlando.

Register here for Wednesday, August 1^st – 9am to 5pm

Using SMO for DDL with SQL Server

Friday, February 01 2008 - sql - 2 comments

My June 2007 Data Points column has been posted to the MSDN Magazine web site. This edition is titled "SQL Server Management Objects".

Here is an excerpt from the article:

"Not only must database developers query and manipulate data, but they must also perform administrative tasks on a regular basis. SQL Server™ Management Objects (SMO) offer developers a robust toolset for operations such as backing up and restoring databases, and issuing Data Definition Language (DDL) commands. Using SQL SMO you can also connect to SQL Server, iterate through a collection of database objects and perform a variety of tasks against them.

In this column, I discuss how SMO can be used to examine a database’s objects and to perform a variety of administrative tasks. Specifically, I cover topics such as how to design your project to use SMO and how to connect to a server. I also walk through a sample application that issues DDL or Data Manipulation Language (DML) commands against the database. Finally, I demonstrate how to use SMO to perform database backups, restores, and backup verification."

Disabling Constraints and Triggers

Friday, February 01 2008 - sql - 0 comments

My April 2007 Data Points column has been posted to the MSDN Magazine web site. This edition is titled "Disabling Constraints and Triggers".

Entity SQL & the EntityClient Provider in ADO.NET vNext and Orcas

Friday, February 01 2008 - net, sql, ado-net, entity-framework, linq - 0 comments

51st MSDN Magazine article! - Where has the time gone?

Friday, February 01 2008 - net, sql, ado-net, mobile-dev - 5 comments

So I am a bit late realizing that my 50th article was published in MSDN Magazine (including its predecessors MIND [Microsoft Internet Developer] and MIND [Microsoft Interactive Developer]) last month. The article was titled Revisiting System.Transactions and was in the November 2006 issue of MSDN Magazine. I did not realize this until today when I saw my 51st article was published (RSS Feeds on a SmartPhone).

My first article with MIND was back in December of 1998 and I wrote every few months for them for a few years. But it was not until 2001 when I started the Data Points column ... which makes this year my 5th year authoring the column. Wow, time sure does fly!

The last book I was involved in was published back in 2000 ... I stopped writing books once the kids started coming along. Wow, talk about working overtime ... when I was writing books I would put in a regular work week at my job plus another 50 hours a week on the book. It was an insane time. I remember when writing my last book my youngest daughter had colic, so she would stay up all night crying. The only way to ease her crying was to hold and sway with her. Since I was up all night writing the book, I would have her in one arm, and i would be typing on the keyboard all while swaying ... all through the night (I dedicated that book to her since she was there the whole time I wrote it). That was when I decided it was time to hang up the books before I missed their childhood.

Anyway ... it's been a long ride for me with writing. Thank you very much for reading my thoughts all these years!

System.Transactions Revisited 2 Years Later

Friday, February 01 2008 - net, sql, ado-net - 1 comments

The November 2006 issue of MSDN Magazine is now posted online. My latest Data Points column revisits System.Transactions and its features.

You can read the article online here.

Accessing .NET Code from SQL Server Reporting Services' Reports

Friday, February 01 2008 - net, sql - 1 comments

The July 2007 issue of MSDN Magazine is now posted online. My latest Data Points column discusses how to create charts, embedded code, and access existing .NET class libraries from SQL Server Reporting Services 2005’s reports.

You can read the article online here.

Its been a busy year for me with family and work. So I am taking a short break from writing but will be back in the October 2006 issue of MSDN Magazine with my Data Points column that discusses System.Transactions in .NET 2. It will build upon my earlier article on this topic about 2 years ago (during the beta phase of .NET when it was still called Whidbey and SQL Server was Yukon).

SQL Server 2005 Everywhere Edition CTP

Friday, February 01 2008 - sql - 0 comments

As of a few days ago, the SQL Server 2005 Everywhere Edition CTP is avaialble for download. This is the successor to SQL Server CE (aren’t name changes great). You can grab it here. if you want some documentation, you can download the SQL Server 2005 Everywhere Edition Community Technology Preview Books Online as well.

Some pretty cool features have been added and some MSDN Magazine readers have reminded me that its about time I wrote another article on mobile devices and using SQL Server … I have a full slate of topics right now, but probably later this year.

In the meantime, i’ll be throwing my thoughts out on the topic on codebetter.com.

T Minus 3 hours: WebCast Using System.Transactions in .NET 2.0

Friday, February 01 2008 - net, ado-net, sql - 1 comments

As I mentioned late last week, I'll be presenting Using System.Transactions in .NET 2.0 in on of the MSDN WebCast events later today (9:00am Pacific Time or 12:00 noon for us on the East Coast).

I have several demonstations and slides but I will try to focus most of the time on the demos.

Here is a brief  summary of the agenda:

The Microsoft .NET Framework 2.0 includes the System.Transactions namespace, which enhances transactional support for managed code. This webcast discusses how System.Transactions can handle transactions without deriving from a ServicedComponent, using interception or reflection. Designed to integrate with Microsoft SQL Server 2005, System.Transactions supports promotion of local lightweight to fully distributed transactions. We illustrate this using examples that modify default transactional settings as well as the isolation level, timeout period, and transaction context. We also demonstrate several examples of using transactions and when and if they are promoted.

Hope to "see" you there!

Join me for the System.Transactions in .NET 2 MSDN WebCast

Friday, February 01 2008 - net, ado-net, sql - 2 comments

If you are interested in System.Transactions, I'll be doing a MSDN WebCast next week on Using System.Transactions in .NET 2.0. The WebCast is on May 31st, at 9:00am Pacific Time. (That's 12 noon for us on the East Coast). Here is a brief  summary of the agenda:

The Microsoft .NET Framework 2.0 includes the System.Transactions namespace, which enhances transactional support for managed code. This webcast discusses how System.Transactions can handle transactions without deriving from a ServicedComponent, using interception or reflection. Designed to integrate with Microsoft SQL Server 2005, System.Transactions supports promotion of local lightweight to fully distributed transactions. We illustrate this using examples that modify default transactional settings as well as the isolation level, timeout period, and transaction context. We also demonstrate several examples of using transactions and when and if they are promoted.

  Hope to "see" you there!

PS ... I will likely be doing some other WebCast's this summer and i will post them once they are scheduled.

UPDATE: I included the sample codin the downlink below this post. Thanks for attending!

SQL Server 2005: T-SQL and XML Samples

Friday, February 01 2008 - sql - 1 comments

I've been asked for some T-SQL and XML code samples that I have presented of late. Here are some sample snippets of code that you can use to examine some of the SQL 2005 T-SQL features and its XML features, as well. The code is in a zip file below this post.

All code is "as-is" ... no warranty :)

Enjoy!

Developing with SQL Server 2005 - XML Code Samples

Friday, February 01 2008 - net, sql - 0 comments

As promised here are the main code samples I demonstrated last week at the Toronto VSLive! conference for the Developing with SQL Server 2005 (XML Features) session. I will also post the code for the SQL Server 2005 and XML session here.

Topics include:

• XML Data Type
• XML Schema Collections
• FOR XML
• Querying with XQuery
• Modifying data with XQuery

See the code in the zip file attachment below this post.

Developing with SQL Server 2005 - T-SQL Code Samples

Friday, February 01 2008 - net, sql - 0 comments

As promised here are the main code samples I demonstrated last week at the Toronto VSLive! conference for the Developing with SQL Server 2005 (T-SQL) session. I will also post the code for the SQL Server 2005 XML session here.

Topics include:

• Exception Handling
• EventData
• Triggers
• Common Table Expressions
• Views and Derived/Inline Tables
• DRI Enhancements

See the code in the zip file attachment below this post.

SQL Server 2005 XML Support, Exception Handling, DDL Triggers

Friday, February 01 2008 - net, sql - 1 comments

SQL Server and ADO.NET in Toronto

Friday, February 01 2008 - net, ado-net, sql - 0 comments

Man have things crept up on me! This past weekend I reviewed my code sample for my presentations at VSLive in Toronto later this week. It should be a good show up in Canada … I enjoyed my trip there last time. For any of you who will be there, here is a brief glimpse at what I will be discussing/demonstrating:

Developing with SQL Server 2005 – Part 1 (April 26th, 10:30 am)

• XML Data Type
• XML Schema Collections
• FOR XML
• Querying with XQuery
• Modifying data with XQuery

Hard Core Coding with ADO.NET 2 (April 26th, 2:00 pm)

• New Features and Major Changes in ADO.NET 2
• DataTable Enhancements
• DataTableReader
• Batch Updates
• Bulk copying techniques
• Performance Enhancements

Developing with SQL Server 2005 – Part 2 (April 26th, 3:15 pm)

• Exception Handling
• EventData
• Triggers
• Common Table Expressions
• Views and Derived/Inline Tables
• DRI Enhancements

The agenda is now online for VSLive Toronto right here. All of my sessions in Toronto are on April 26th. I’ll be presenting 2 sessions on developing with SQL Server 2005 and a 3rd session on developing using ADO.NET 2. If you are planning on attending the conference, please stop by and say hi!

SCOPE_IDENTITY() and @@IDENTITY Demystified

Friday, February 01 2008 - sql - 23 comments

I’ve run across several applications where the use of IDENTITY values has caused some confusion, specifically in how the @@IDENTITY function operates given external influences. We’ve all likely been faced with how to grab the newly generated IDENTITY value from a SQL Server database table. And there are a 2 popular techniques that accomplish this with SQL Server:

1. the @@IDENTITY function
2. the SCOPE_IDENTITY() function

They both will return a newly generated ID value but the difference between the 2 is very important. Important enough that you could get back an ID that you did not desire. The @@IDENTITY function returns the last IDENTITY value that was generated in your connection. So if you are running a stored procedure that looks something like this (using the Northwind database):

CREATE PROCEDURE prRegion_Insert_1

@description NCHAR(50)

    INSERT INTO Region (RegionDescription) VALUES (@description)

    SELECT @@IDENTITY

Assuming that your database has no other actions that occur between the INSERT statement and the SELECT @@IDENTITY statement, then you will get back the newly created RegionID value for the new Region row. But what if there is n Insert Trigger, for example, on the Region table that inserts a record into another table that also has an IDENTITY column. (Perhaps it does this to audit data.) In this case, the sequence of events would be:

1. the stored proc fires
2. insert the new region record
3. the trigger fires
4. the trigger’s code inserts the audit record
5. the stored proc then executes the SELECT @@IDENTITY, which then returns the ID that was created by the last statement, which is the audit table’s ID

In this case, the ID returned is the ID value that was created by the insert statement inside of the trigger. You might say that you would never put a trigger on a table that inserts into a table with an IDENTITY column in the first place. And I’d believe you … However … other developers and database administrators may create a trigger in the future that does this. They might not know that you wrote a stored procedure (or series of them)  that relies on this situation not occurring. The point is that “stuff” happens (polite way of saying it).

So what does SCOPE_IDENTITY() do? It returns the last IDENTITY value generated in the scope. So what is the scope? The scope is a batch of SQL code such as s stored procedure, a trigger, or a user defined function. Using the previous example, the scope is the context of the stored procedure and not anything that gets called indirectly by it such as a trigger. This allows you to grab the last generated ID in the stored procedure, as you likely intended for it to happen. Here is another stored procedure that uses the SCOPE_IDENTITY() to grab the last ID generated in the Region table.

CREATE PROCEDURE prRegion_Insert_2

@description NCHAR(50)

    INSERT INTO Region (RegionDescription) VALUES (@description)

    SELECT SCOPE_IDENTITY()

Is it magic? Nah. But sometimes the smallest things can have huge impacts on our applications.

SQL Server 2005 - Using XQuery and Large Datatypes

Friday, February 01 2008 - net, sql - 0 comments

The March 2006 issue of MSDN Magazine is now posted online. My latest Data Points column answers a few questions I have received from readers reagarding how the XQuery works with the SQL Server 2005 XML datatype as well as the new large datatype enhancements. 

You can read the article online here.

In the upcoming issues I'll be getting into some more of SQL Server 2005's new features including exception handling, EventData and DDL triggers. I'll also focus on a SQL Server Reporting Services article in the near future.

Forms Authentication with SQL Server Reporting Services 2000

Friday, February 01 2008 - net, sql - 9 comments

A few days ago I mentioned that my colleagues and I worked out some kinks and got SQL Server Reporting Services  (SSRS) to use Forms Authentication and integrate withour ASP.NET project. It was not as straight forward as we had all thought heading into this endeavor and we did not find much help on the web. But the help we did find was very useful. For example, here is a very good article posted on MSDN that discusses and demosntrates how to implement Forms Auth with SSRS. The article discusses what needs to be done to set up a sample application (which is downloadable) to work with Forms Authentication. We still had to tweak it here and there to meet our needs of course, but overall this is the scheme that we followed.

I'll followup with other tidbits of our findings, but if you are looking for a good jumpstart into SSRS and Forms Auth, check out this article.

Binary Serialization, Batch Updates, and DataView Enhancements

Friday, February 01 2008 - net, sql - 0 comments

The January 2006 issue of MSDN Magazine is now posted online. My latest Data Points column answers a few questions I have received from readers reagarding how the XQuery works with the SQL Server 2005 XML datatype as well as the new large datatype enhancements. 

You can read the article online here.

In the upcoming issues I'll be getting into some more of SQL Server 2005's new features including DDL triggers. I'll also focus on SQL Server Reporting Services article in the near future, too.

Tip: Side by Side Installation of SQL Server 2005 and SQL Server 2000

Friday, February 01 2008 - net, sql - 7 comments

Tip: when installing SQL Server, consider using named instances.

I recently installed SQL Server 2005 on my PC. It went fairly quick and installed with 0 issues. I was also quite pleased that it installed very nicely side by side with a SQL Server 2000 installation already on my PC. Now when I installed SQL Server 2000 on my PC way back when, I installed it as a named instance called SQL2000. Thus, my server name is MYSERVERNAME\SQL2000 in all of my connection strings and so on. One of the nice things about installing all of your SQL Server instances as named instances is that they stay isolated from one another pretty well.

OK, to better demosntrate how named instances work ... For example all of my folder in the C:\Program Files\Microsoft SQL Server directory are as follows: 

• \80  // This folder seems to have assorted SQL 2000 files in it
• \90  // This folder seems to have assorted SQL 2005 files in it
• \MSSQL  // This folder contains SQL 2000 SQL instance's reporting services
• \MSSQL$SQL // This folder contains SQL 2000's SQL instance's system files & data
• \MSSQL.1 // This folder contains SQL 2005 system files & data
• \MSSQL.2 // This folder contains SQL 2005 reporting services

Notice that all of the folders are segregated from each other so that the SQL 2000 and SQL 2005 installation do no interfere with each other. Also, the Reporting Services installations for both 2000 and 2005 have separate virtual web sites and separate services.

While this isn't earth shatterring news, its still pretty darn cool. I certainly don't take for granted that these products work side by side nor that Visual Studio.NET 2005 and Visual Studio.NET 2003 work side by side. I've been through too many backward incompatible and version incompatible nightmares. Side by side is a good thing. Virtual PC is nice, but side by side is better.

SQL Server Reporting Services 2000 and Forms Authentication Works!

Friday, February 01 2008 - net, sql - 4 comments

Wow. Several days of anguish is finally paying off. My colleagues and I have been trying to implement a SSRS 2000  with a web site using Forms Authentication and it is not as easy as you might think. It certainly hasn't been for us. Microsoft's own documentation is built upon Windows Integrated security which is awesome, but not always practical. They also expand on it by offerring examples on how to use Forms Authentication with SSRS 2000 by expanding on the SSRS archtitecture. Their examples aren't bad at all, actually they helped quite a bit. But they did not take us all the way home. We had to improvise on how to pass the credentials and then parse them on the SSRS side.

Anyway, I'll be blogging more about this fun weekend (was there a holiday to enjoy?) once I take a breather. But I wanted to get this poost out to say "Yes! You can use Forms Authentication with SSRS!!!"

<collective sigh/>

Fixing SQL Server Reporting Services

Friday, February 01 2008 - net, sql - 54 comments

So I install Visual Studio.NET 2005 side by side with VS.NET 2003 on my computer and things are running smoothly. But then I see that a project that interacts with Reporting Services hits a brick wall.

Error rsReportServerDisabled : The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service.

After a few brief searches on the web it seems that the installation of VS.NET 2005 which updates some ASPNET account information can cause this problem. The confusing part is that most of the MSDN documentation tells you how to avoid this issue by going back in time and backing up your encrypted keys using the rskeymgt utility. Too bad I don't have a time machie to do that. Another article talks about deleting your encrypted data and then restoring the keys using hte same utility. Finally I found some information on how to reactrivate the report server and fix this problem using the following command:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn> rsactivate -r -c"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"

(Obviously you substitute your installation location with what mine are in the above command.)

Anyway, this fixed everything for me and I hpoe it helps save you some time if or when you run into this issue with Reporting Services.

Looking at PromptSQL - Intellisense for T-SQL

Friday, February 01 2008 - reviews, sql - 4 comments

Recently Sahil posted about his use of PromptSQL, a relitvely inexpensive tool that provides intellisense for T-SQL. I totally agree with Sahil in that one area that has been lacking for way too long is intellisense in T-SQL. While I use enterprise manager and visual studio to cerate queries and manage my stored procedures, triggers, and functions I still enjoy cracking open an editor isntead of a designer to write and debug T-SQL. Far too often, however, I am faced with a new database on a new project and I have to rack my brain trying to figure out what the columns and the tables are called. Normally I try to print out a E/R diagram so I can post it on my wall, but sometimes that is not feasible. This is why I tried out PromptSQL.

PromptSQL is $25, a nominal fee for a product like this IMO. Of course there is a trial version available for download, too. It works inside of Query Analyzer and Visual Studio.NET (in SQL files), requiring its own connection to the database for what I assume is to access the INFORMATION_SCHEMA views or the system tables.

PromptSQL's options allow you to change the font and the size of the intellisense. It also allows you to manage the duration of the database connection that it maintains. Overall PromptSQL helped me with database schemas that were less familiar to me. I could see this being a valuable asset for a development team who are being assembled on a mostly constructed database. However, in databases that I have familiarty of the scehma I found PromptSQL to mostly get in the way. In these cases, I turned it off because I found that it can sometimes take a moment (a split second really) to pop up its list ... it may not sound like a lot of time but when you are typing a query quickly without pause, you don't want to be interupted (at least I don't). It could've been just on my PC that I found this slight sub second delay, so I recommend trying it in your situation to see how it responds. Overall, I like the tool, that is at least until Microsoft offers it as part of Visual Studio.NET (*hoping*). Do I give it a thumbs up? If you know your schema, you probably won't use this that much ... but if you are often in unfamilar database schemas and/or in a situation where you forget the column names of a table, PromptSQL is not a bad idea.

SubString in an ADO.NET DataSet - (DataColumn Expression)

Friday, February 01 2008 - ado-net, sql - 3 comments

One cool feature that I just love are the expression based columns in ADO.NET. I do not use them often but when I do find cause to use them, they are very useful. (Not to mention very cool.) One good use of expression columns in a DataSet is when you want to load a column into a DataGrid but you don't want the entire text description to appear in the column. This is often found when you have a notes, comments or some sort of very long description column that is 100 or even 8000 characters long. You likely don't want to show the entire text string because it could stretch out your grid, so a common way to deal with it is to only show the first X amount of characters. For example, the code sample below will create an expression column called "Co" and add it to the Customers DataTable. This new column will grab the first 10 characters from the CompanyName (from the same DataRow in the DataTable) and append 3 dots. So instead of showing all of the columns in a grid, you could hide the CompanyName and instead show the Co column.

    string cnStr = @"Data Source=mysqlserver;Initial Catalog=northwind;Integrated Security=True";

    SqlConnection cn = new SqlConnection(cnStr);

    string sql = "SELECT CustomerID, CompanyName, City FROM Customers ORDER BY CustomerID";

    SqlCommand cmd = new SqlCommand(sql, cn);

    SqlDataAdapter da = new SqlDataAdapter(cmd);

    DataSet ds = new DataSet();

    da.Fill(ds, "Customers");

    ds.Tables["Customers"].Columns.Add("Co.", 

        typeof(string), 

        "Substring(CompanyName, 1, 10) + '...'");

There is a WHOLE bunch more that you can do with expression based columns, but I'll leave that for later.

Find SQL Server Stored Procedures Containing Some Text

Friday, February 01 2008 - sql - 9 comments

Relatively basic stuff here, but I do like to stick to the KISS rule. I am often required to search SQL Server's stored procedures to find out which ones refer to specific fields, tables, or even variable names. Recently, I had to refactor several stored procedures defined a specific alias for a column. Being the geek that I am, I wrote a little stored proc helper routine that quickly came up with the results I needed. In my case, there were over 1000 procs to search and my results turned up 25 procs to refactor.

CREATE PROCEDURE sp_find_procs_containing

    @search VARCHAR(100) = ''

AS

SET @search = '%' + @search + '%'

SELECT    

    ROUTINE_NAME,

    ROUTINE_DEFINITION

FROM    

    INFORMATION_SCHEMA.ROUTINES

WHERE    

    ROUTINE_DEFINITION LIKE @search

ORDER BY

    ROUTINE_NAME

GO

If you invoke this proc and pass it the phrase you are looking for in the proc, it will return them in sorted order. Not brain surgery, just something to help me code faster.

As you can tell, I like the INFORMATION_SCHEMA views. Here is another post I put up a while back on using them.

T-SQL: IF NOT EXISTS versus @@ROWCOUNT

Friday, February 01 2008 - sql - 15 comments

I was asked a good question the other day regarding why I chose to use "IF NOT EXISTS" in my Nov 2003 MSDN article on User Defined Functions to check to see if a declared table variable had any rows in it versus checking @@ROWCOUNT. Mostly I chose to use IF NOT EXISTS because I know that I find it less apt to break on me in case I do something silly. For example, if I chose to check @@ROWCOUNT in the SQL sample code below instead of using "IF NOT EXISTS", it works just fine. But what happens if I (or someone else) adds another SQL statement at line 15 that alters the @@ROWCOUNT? For example, If I augment this code in the future to inlcude an UPDATE statement at line 15, the ROWCOUNT will no longer represent the number of rows affected by the INSERT statement (lines 10-14). Is this likely to happen? Probably not. Has it happened to me? Oh yeah, I was burnt once on this. Is it bad to use ROWCOUNT? Not at all! I was being overly careful in this code example and also wanted to show how "IF NOT EXISTS" works. So the answer is that both ways work.

Find a Column, Find a Table in SQL Server

Friday, February 01 2008 - sql - 10 comments

OK, so this isn’t rocket science, but then again some of the best things are the simplest. You can do a lot with the information_schema views in SQL Server.

Here is a short stored procedure that you can use to find:

• a table by its full name
• a table by partial name
• a column by its full name
• a column by its partial name

 It comes in handy for me when I am looking for a field in related tables, or a spec I am reading refers to a field by name but not by the table it is in. Like I said, it is simple, but I have used this type of a proc for a long time.

 CREATE PROCEDURE prFindTableOrColumn ( @table_name VARCHAR(128) = NULL, @search_data VARCHAR(128) = NULL ) AS DECLARE @search1 VARCHAR(128) SET @search1 = '%' + @table_name + '%' IF @search1 IS NULL SET @search1 = '%' DECLARE @search2 VARCHAR(128) SET @search2 = '%' + @search_data + '%' IF @search2 IS NULL SET @search2 = '%' SELECT c.table_name, c.column_name, c.data_type, c.character_maximum_length, c.numeric_precision, c.numeric_scale FROM information_schema.columns c INNER JOIN information_schema.tables t  ON c.table_name = t.table_name WHERE c.table_name LIKE @search1 AND c.column_name LIKE @search2 AND t.table_type = 'BASE TABLE' ORDER BY c.table_name, c.column_name 

Clarifying ADO.NET 2 Batch Updates

Friday, February 01 2008 - ado-net, sql - 2 comments

Pablo Castro, Microsoft's Program Manager of the ADO.NET Team, has a good post last week that helps clarify how bath updates are intended to work in ADO.NET 2. This is a common discussion that I am drawn into since the DataAdapter actually invokes its command for each modified row in the DataTable that it is associated with. Thus if you have a DataTable with the following deltas:

• 7 modified rows
• 2 added rows
• 2 deleted rows

The DataAdapter will invoke the UpdateCommand 7 times, the InsertCommand 2 times and the DeleteCommand 2 times for a total of 11 round trips. There are several ways to work around this, some of which Pablo points out such as sending all of the SQL statements down at once manually. You can also use diffgrams using SQL Server's XML features, too. But none of the solutions is as elegant as how ADO.NET shoulda, coulda , woulda handled it. Kudos to Pablo for a good explanation!

May 2005 Issue of MSDN Mag and Data Points

Friday, February 01 2008 - net, ado-net, ent-lib, sql - 0 comments

The May 2005 issue of MSDN Magazine is now available online at http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/default.aspx.

After a 2 month break, my Data Points column is back. In this issue I discuss several Data Access Strategies Using ADO.NET and SQL, inlcuding some tips on writing queries, connection maintenance and building more efficient commands.

In case you are wondering what my next few Data Points columns will cover, here is a sneak peek:

• Using the FOR XML clause in SQL statements and how to access it via ADO.NET
• Configuring the Enterprise Library Data Access Application Blocks (including configuration, cryptography and where Ent Lib is useful)
• Implementing the Enterprise Library Data Access Application Blocks (exensive examples on how to use it, which methods to use, and so on)
• ADO.NET v2 (what's in it, how it has been improved, how you can get started with it, what to look out for)

Reading XML Fragments into ADO.NET DataSets

Friday, February 01 2008 - ado-net, sql - 1 comments

Heading to Boston

Friday, February 01 2008 - ado-net, sql - 0 comments