Hand-Rolling DAL Code

Every once in a while, I find myself needing to hand-code my own DAL (mostly because I play with different kinds of DAL and ORM approaches).

Today, I wanted to generate a lot of code that basically needs the names of the tables and the stored procedures that affect them. Fortunately, I had named all my stored procedures to start with the name of the table that they depend on. All I would need to do is run a query on the sysobjects table, pull the results into EditPlus and generate another set of queries, and then run those queries to get the text names that I’m looking for.

Here’s the steps (in detail).

1. Run a query to get the names of the tables I want:
   1. select name from sysobjects where xtype='U' and name like 'Exam%' order by name
   2. This returned the following result:
      ExamBank_Choice
      ExamBank_ExamProfile
      ExamBank_MultipleChoiceProblem
      ExamBank_ProblemSet
      ExamBank_ProfileItemSet
      ExamWriter_Choice
      ExamWriter_Exam
      ExamWriter_MultipleChoiceProblem
      ExamWriter_ProblemSet
2. Copy this text into EditPlus and run the following search/replace:
   1. Search:
      ^(Exam.+)$
   2. Replace with:
      SELECT 'Table\1'
      SELECT name FROM sysobjects WHERE xtype='P' AND name like '\1%' ORDER BY name
      SELECT '--  --'
   3. This produces the following script.

      SELECT 'TableExamBank_Choice'

      SELECT name FROM sysobjects WHERE xtype='P' AND name like 'ExamBank_Choice%' ORDER BY name

      SELECT '--  --'
3. Executing the generated sql, I get the following result (shortened), which I can paste into Visual Studio, comment out, and pull my stored procedure names as needed for my code.
1.     '--------------------
       'TableExamBank_Choice
   
       'ExamBank_Choice_Add
       'ExamBank_Choice_Delete
       'ExamBank_Choice_Get
       'ExamBank_Choice_GetByMultipleChoiceProblem
       'ExamBank_Choice_List
       'ExamBank_Choice_Update

And that’s all. Now, I’ve got it blogged & I (or you) can refer to it again when needed. For more useful SQL scripts, check out some tips on this StackOverflow posting. That’s also where I got this list of short and handy SQL scripts.