logo

VS 2012 T4 Template Code Gen Stored Proc from SQL Server 2008 Sample

logo

I have been working on trying to use the T4 Templates to Code Gen stored procs, DTO/Model classes, and Enterprise Library 5.0 data access layer. I thought I’d post these so people have an example. There are bits and pieces out there, but not a whole lot of basic samples.

They say you can include the T4 in your existing projects, and it will automatically update all the database changes. For me, I prefer to have a separate Code Gen solution and update them when I feel it is necessary. I don’t want to accidentally overwrite something I’ve added manually.

So, I created a separate VS 2012 solution, added a project and created folders for stored procedures, the Model, the BLL, and the DAL.

Here is the T4 template for generating CRUD procedures. I am teaching myself C#, so go easy on me (I have been doing VB.NET for over 6 years)! These will output to one file (my preference). However, when I post the others, they will output to separate .cs files. Below, you will see the code, and then the results. You will need to change the database name to your specific database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
<#@ template   debug="true" hostSpecific="true" #>
<#@ output extension="sql" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo"#>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc" #>


<#
    string serverName = @"(local)";
    string databaseName = @"MyDatabaseName";
    Server server = new Server(serverName);      
    Database database = new Database(server, databaseName);
    database.Refresh();

    int count=0;

foreach(Table table in database.Tables)
    {      
    #>
/* -------------------------------------------------------------------------------------*/
/* DELETE SCRIPTS */
/* ------------------------------------------------------------------------------------- */

/* -------------------------------------------------------------------------------------
/     <#= table.Name #>DeleteBy<#= table.Name#>ID
/  ------------------------------------------------------------------------------------- */


IF  EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'<#=  table.Name #>DeleteBy<#= table.Name#>ID') AND type in (N'P', N'PC'))
        DROP PROCEDURE  <#=  table.Name  #>DeleteBy<#= table.Name#>ID
        GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE <#= table.Name #>DeleteBy<#= table.Name#>ID
<#
        //int count=0;
        count=0;
        PushIndent("     ");
        foreach (Column column in table.Columns)
        {          
            if (column.InPrimaryKey){
           
                if (count>0) this.WriteLine(",");
                    this.Write("@"+column.Name +" "+ column.DataType);
                    count ++;
            }  
        }
        this.PopIndent();
        WriteLine("");
#>

AS
    SET NOCOUNT ON;

DELETE FROM <#= table.Name #>
WHERE <#    
        count = 0;
        foreach (Column column in table.Columns)
        {                      
            if (column.InPrimaryKey){
                if (count>0) this.Write(" AND ");
                WriteLine(column.Name + " = @" + column.Name);
                count ++;
            }
        }
        this.PopIndent();
        WriteLine("");
        WriteLine("GO");

#>
/* -------------------------------------------------------------------------------------*/
/* SELECT BY PRIMARY KEY SCRIPTS */
/* ------------------------------------------------------------------------------------- */

/* -------------------------------------------------------------------------------------
/     <#= table.Name #>SelectBy<#= table.Name#>ID
/  ------------------------------------------------------------------------------------- */


   
    IF  EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'<#=  table.Name #>SelectBy<#= table.Name#>ID') AND type in (N'P', N'PC'))
        DROP PROCEDURE  <#=  table.Name  #>SelectBy<#= table.Name#>ID
        GO

       
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE <#= table.Name #>SelectBy<#= table.Name#>ID
<#
        count=0;
        PushIndent("     ");
        foreach (Column column in table.Columns)
        {          
            if (column.InPrimaryKey){
           
                if (count>0) this.WriteLine(",");
                    this.Write("@"+column.Name +" "+ column.DataType);
                    count ++;
            }  
        }
        this.PopIndent();
        WriteLine("");
#>

AS
SET NOCOUNT ON;
SELECT * FROM <#= table.Name #>
WHERE <#    
        count = 0;
        foreach (Column column in table.Columns)
        {                      
            if (column.InPrimaryKey){
                if (count>0) this.Write(" AND ");
                WriteLine(column.Name + " = @" + column.Name);
                count ++;
            }
        }
        this.PopIndent();
        WriteLine("");
        WriteLine("GO");

#>
/* -------------------------------------------------------------------------------------*/
/* INSERT TABLE SCRIPTS */
/* ------------------------------------------------------------------------------------- */
       
/* -------------------------------------------------------------------------------------
/     <#= table.Name #>Insert
/  ------------------------------------------------------------------------------------- */

   
    IF  EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'<#=  table.Name #>Insert') AND type in (N'P', N'PC'))
        DROP PROCEDURE  <#=  table.Name  #>Insert
        GO

       
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

   
        CREATE PROCEDURE <#=  table.Name  #>Insert
<#
        //int count = 0;
        count=0;
        string primarykeyname = "";
        this.PushIndent("        ");
        this.Write("    ");
        foreach(Column column in table.Columns)
        {
            if (!column.Identity){

                if (count>0)
                {  
                this.Write(" , ");
           
                if (column.DataType.Name == "varchar")
                    this.Write("@"+column.Name +" "+ column.DataType + "(" + column.Properties["Length"].Value + ")");

                else
                    this.Write("@" + column.Name + " " + column.DataType );
                    this.WriteLine("");

                }  

                else {
                    this.Write("   ");
               
                        if (column.DataType.Name == "varchar")
                            this.Write("@"+column.Name +" "+ column.DataType + "(" + column.Properties["Length"].Value + ")");

                        else
                            this.Write("@" + column.Name + " " + column.DataType );
                            this.WriteLine("");
                }

            count ++;
            }  

            else
            {
                   
                    primarykeyname = column.Name;
                    this.WriteLine("");
               
                }
               
        }

        this.Write(",@" + primarykeyname + " int OUTPUT");
        this.WriteLine("    ");
        this.PopIndent();
        this.WriteLine("    ");
        #>
        AS
        SET NOCOUNT ON;
        INSERT INTO <#= table.Name #>
        (
        <#
            this.PushIndent("        ");
            count = 0;
           
            this.Write("    ");
            foreach (Column column in table.Columns)
            {
                if (!column.Identity){
                    if  (count>0) this.Write(" , ");
                    this.Write(column.Name);
                    this.WriteLine("");
                    count ++;
                }
            }
            this.WriteLine("");
            this.PopIndent();
        #>
        ) VALUES (
        <#  
            this.PushIndent("        ");
            count = 0;
            this.Write("    ");
            foreach(Column column in table.Columns)
            {
                if (!column.Identity){
                    if  (count>0) this.Write(" , ");
                    this.Write("@" + column.Name);
                    this.WriteLine("");
                    count ++;
                }
            }
        #>
<#

    this.WriteLine(")");
    this.WriteLine("SELECT @" + primarykeyname + " = SCOPE_IDENTITY()");
    this.WriteLine("GO");
        this.PopIndent();
        WriteLine("");
        WriteLine("");
   

#>
/* -------------------------------------------------------------------------------------*/
/* UPDATE TABLE SCRIPTS */
/* ------------------------------------------------------------------------------------- */

/* -------------------------------------------------------------------------------------
/     <#= table.Name #>Update
/  ------------------------------------------------------------------------------------- */


IF  EXISTS (SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'<#=  table.Name #>Update') AND type in (N'P', N'PC'))
DROP PROCEDURE  <#=  table.Name  #>Update
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
       
        CREATE PROCEDURE <#=  table.Name  #>Update
<#
        count = 0;
        this.PushIndent("        ");
        this.Write("    ");
        foreach(Column column in table.Columns)
        {
            if (count>0) this.Write(" , ");

            if (column.DataType.Name == "varchar")
            this.Write("@"+column.Name +" "+ column.DataType + "(" + column.Properties["Length"].Value + ")");

                    else
                this.Write("@" + column.Name + " " + column.DataType );

            this.WriteLine("");
            count ++;
        }
        this.PopIndent();
        this.WriteLine("    ");
        #>
        AS
        SET NOCOUNT ON
        UPDATE <#= table.Name #> SET
        <#
           
            count = 0;
            this.Write("    ");
            foreach (Column column in table.Columns)
            {
                if (!column.InPrimaryKey){
                    if  (count>0) this.Write("          , ");
                    this.WriteLine(column.Name + " = @" + column.Name);
                    count ++;
                }
            }
           
        #>      WHERE <#  
           
            count = 0;
           
            foreach(Column column in table.Columns)
            {
                if (column.InPrimaryKey){
                    if  (count>0) this.Write(" , ");
                    this.Write(column.Name + " = @" + column.Name);
                    count ++;
                }
            }
        #>
<#
    this.WriteLine("        GO");
        this.PopIndent();
        WriteLine("");
        WriteLine("");
            #>


<#     

    }

   
#>

Here is what it outputs:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
/* -------------------------------------------------------------------------------------*/
/* DELETE SCRIPTS */
/* ------------------------------------------------------------------------------------- */

/* -------------------------------------------------------------------------------------
/     ActivityDeleteByActivityID
/  ------------------------------------------------------------------------------------- */


IF  EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'ActivityDeleteByActivityID') AND TYPE IN (N'P', N'PC'))
        DROP PROCEDURE  ActivityDeleteByActivityID
        GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE ActivityDeleteByActivityID
     @ActivityID INT

AS
    SET NOCOUNT ON;

DELETE FROM Activity
WHERE ActivityID = @ActivityID

GO
/* -------------------------------------------------------------------------------------*/
/* SELECT BY PRIMARY KEY SCRIPTS */
/* ------------------------------------------------------------------------------------- */

/* -------------------------------------------------------------------------------------
/     ActivitySelectByActivityID
/  ------------------------------------------------------------------------------------- */


   
    IF  EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'ActivitySelectByActivityID') AND TYPE IN (N'P', N'PC'))
        DROP PROCEDURE  ActivitySelectByActivityID
        GO

       
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE ActivitySelectByActivityID
     @ActivityID INT

AS
SET NOCOUNT ON;
SELECT * FROM Activity
WHERE ActivityID = @ActivityID

GO
/* -------------------------------------------------------------------------------------*/
/* INSERT TABLE SCRIPTS */
/* ------------------------------------------------------------------------------------- */
       
/* -------------------------------------------------------------------------------------
/     ActivityInsert
/  ------------------------------------------------------------------------------------- */

   
    IF  EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'ActivityInsert') AND TYPE IN (N'P', N'PC'))
        DROP PROCEDURE  ActivityInsert
        GO

       
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

   
        CREATE PROCEDURE ActivityInsert
           
           @DestinationID INT
         , @ActivityTypeID INT
         , @ActivityName VARCHAR(50)
         , @ActivityDescription VARCHAR(500)
         , @ActivityStartTime datetime
         , @ActivityEndTime datetime
         , @URL VARCHAR(100)
         , @Cost DECIMAL
         , @DateCreated datetime
         , @CreatedBy INT
        ,@ActivityID INT OUTPUT    
   
        AS
        SET NOCOUNT ON;
        INSERT INTO Activity
        (
            DestinationID
         , ActivityTypeID
         , ActivityName
         , ActivityDescription
         , ActivityStartTime
         , ActivityEndTime
         , URL
         , Cost
         , DateCreated
         , CreatedBy

        ) VALUES (
            @DestinationID
         , @ActivityTypeID
         , @ActivityName
         , @ActivityDescription
         , @ActivityStartTime
         , @ActivityEndTime
         , @URL
         , @Cost
         , @DateCreated
         , @CreatedBy
         
        )
        SELECT @ActivityID = SCOPE_IDENTITY()
        GO


/* -------------------------------------------------------------------------------------*/
/* UPDATE TABLE SCRIPTS */
/* ------------------------------------------------------------------------------------- */

/* -------------------------------------------------------------------------------------
/     ActivityUpdate
/  ------------------------------------------------------------------------------------- */


IF  EXISTS (SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'ActivityUpdate') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE  ActivityUpdate
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
       
        CREATE PROCEDURE ActivityUpdate
            @ActivityID INT
         , @DestinationID INT
         , @ActivityTypeID INT
         , @ActivityName VARCHAR(50)
         , @ActivityDescription VARCHAR(500)
         , @ActivityStartTime datetime
         , @ActivityEndTime datetime
         , @URL VARCHAR(100)
         , @Cost DECIMAL
         , @DateCreated datetime
         , @CreatedBy INT
   
        AS
        SET NOCOUNT ON
        UPDATE Activity SET
            DestinationID = @DestinationID
          , ActivityTypeID = @ActivityTypeID
          , ActivityName = @ActivityName
          , ActivityDescription = @ActivityDescription
          , ActivityStartTime = @ActivityStartTime
          , ActivityEndTime = @ActivityEndTime
          , URL = @URL
          , Cost = @Cost
          , DateCreated = @DateCreated
          , CreatedBy = @CreatedBy
        WHERE ActivityID = @ActivityID
        GO




/* -------------------------------------------------------------------------------------*/
/* DELETE SCRIPTS */
/* ------------------------------------------------------------------------------------- */

/* -------------------------------------------------------------------------------------
/     DestinationDeleteByDestinationID
/  ------------------------------------------------------------------------------------- */


IF  EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'DestinationDeleteByDestinationID') AND TYPE IN (N'P', N'PC'))
        DROP PROCEDURE  DestinationDeleteByDestinationID
        GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE DestinationDeleteByDestinationID
     @DestinationID INT

AS
    SET NOCOUNT ON;

DELETE FROM Destination
WHERE DestinationID = @DestinationID

GO
/* -------------------------------------------------------------------------------------*/
/* SELECT BY PRIMARY KEY SCRIPTS */
/* ------------------------------------------------------------------------------------- */

/* -------------------------------------------------------------------------------------
/     DestinationSelectByDestinationID
/  ------------------------------------------------------------------------------------- */


   
    IF  EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'DestinationSelectByDestinationID') AND TYPE IN (N'P', N'PC'))
        DROP PROCEDURE  DestinationSelectByDestinationID
        GO

       
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE DestinationSelectByDestinationID
     @DestinationID INT

AS
SET NOCOUNT ON;
SELECT * FROM Destination
WHERE DestinationID = @DestinationID

GO
/* -------------------------------------------------------------------------------------*/
/* INSERT TABLE SCRIPTS */
/* ------------------------------------------------------------------------------------- */
       
/* -------------------------------------------------------------------------------------
/     DestinationInsert
/  ------------------------------------------------------------------------------------- */

   
    IF  EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'DestinationInsert') AND TYPE IN (N'P', N'PC'))
        DROP PROCEDURE  DestinationInsert
        GO

       
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

   
        CREATE PROCEDURE DestinationInsert
           
           @DestinationStatusID INT
         , @DestinationName VARCHAR(100)
         , @DestinationDetails VARCHAR(500)
         , @StartDate datetime
         , @EndDate datetime
         , @OwnedBy INT
         , @CreatedBy INT
         , @DateCreated datetime
        ,@DestinationID INT OUTPUT    
   
        AS
        SET NOCOUNT ON;
        INSERT INTO Destination
        (
            DestinationStatusID
         , DestinationName
         , DestinationDetails
         , StartDate
         , EndDate
         , OwnedBy
         , CreatedBy
         , DateCreated

        ) VALUES (
            @DestinationStatusID
         , @DestinationName
         , @DestinationDetails
         , @StartDate
         , @EndDate
         , @OwnedBy
         , @CreatedBy
         , @DateCreated
         
        )
        SELECT @DestinationID = SCOPE_IDENTITY()
        GO


/* -------------------------------------------------------------------------------------*/
/* UPDATE TABLE SCRIPTS */
/* ------------------------------------------------------------------------------------- */

/* -------------------------------------------------------------------------------------
/     DestinationUpdate
/  ------------------------------------------------------------------------------------- */


IF  EXISTS (SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'DestinationUpdate') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE  DestinationUpdate
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
       
        CREATE PROCEDURE DestinationUpdate
            @DestinationID INT
         , @DestinationStatusID INT
         , @DestinationName VARCHAR(100)
         , @DestinationDetails VARCHAR(500)
         , @StartDate datetime
         , @EndDate datetime
         , @OwnedBy INT
         , @CreatedBy INT
         , @DateCreated datetime
   
        AS
        SET NOCOUNT ON
        UPDATE Destination SET
            DestinationStatusID = @DestinationStatusID
          , DestinationName = @DestinationName
          , DestinationDetails = @DestinationDetails
          , StartDate = @StartDate
          , EndDate = @EndDate
          , OwnedBy = @OwnedBy
          , CreatedBy = @CreatedBy
          , DateCreated = @DateCreated
        WHERE DestinationID = @DestinationID
        GO

One Response to “VS 2012 T4 Template Code Gen Stored Proc from SQL Server 2008 Sample”

  1. Julio says:

    thanks for the template, I use y work fine, just update the sentence select * for

    SELECT
    0) this.WriteLine(“,”);
    this.Write(column.Name);
    count++;
    }
    #>
    FROM

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

logo
logo
©  2014 Tropical Coding    |    Contact Us