Tropical Coding

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

admin — Thu, 20 Sep 2012 15:38:41 +0000

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:

/* -------------------------------------------------------------------------------------*/
/* 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

How to Backup Your WordPress Website

admin — Mon, 16 Jul 2012 13:53:38 +0000

Found a pretty comprehensive tutorial on how to backup your WordPress website with either the WordPress import/export function or using cPanel.

http://www.noupe.com/wordpress/how-to-backup-your-wordpress-website.html

.NET Separate Address, City, State, and Zip

tropicalcoding — Tue, 10 Jul 2012 05:07:19 +0000

In this scenario, I was importing an Excel spreadsheet, and I was provided an address in the following format:

Street, City, State, Zip

I needed to strip each one out so that I could store them in separate fields in the database.

1
2
3
4
5
6
7
8
9

Here is the SeparateFullAddress function:

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

Private Function SeparateFullAddress(ByVal FullAddress As String) As Array
Dim AddressArray As String()
Try
Dim Street As String
Dim City As String
Dim State As String
Dim ZipCode As String

Dim lastIndex As String
lastIndex = FullAddress.LastIndexOf(" ")
ZipCode = Trim(FullAddress.Substring(lastIndex))
'Remove Zip Code From String
FullAddress = Trim(FullAddress.Remove(lastIndex))

lastIndex = FullAddress.LastIndexOf(",")
State = (FullAddress.Substring(lastIndex)).Replace(",", "").Trim

'Remove State From String
FullAddress = Trim(FullAddress.Remove(lastIndex))

lastIndex = FullAddress.LastIndexOf(";")
City = (Trim(FullAddress.Substring(lastIndex))).Replace(";", "").Trim
'Remove City From String
FullAddress = Trim(FullAddress.Remove(lastIndex))
Street = FullAddress

AddressArray = New String() {Street, City, State, ZipCode}

Return AddressArray
Catch ex As Exception

AddressArray = New String() {"Error"}
Return AddressArray
End Try
End Function

After the array called “fulladdress” was returned, I split it out and inserted it into another datatable, but you can replace row(“MailingAddress1″),etc with whatever you want.

1
2
3
4
5
6
7
8
9
10
11
12

If fullAddress(0) = "Error" Then
'This was added because the spreadsheets often had weird, unpredictable data (imagine that), so rather than have an ambiguous error message, I had to give the user some feedback. I had previously declared "ErrorMessage" (Dim ErrorMessage As New StringBuilder)

ErrorMessage.Append("ERROR: Address is in an incorrect format.")
row("Error") = ErrorMessage
Else
row("MailingAddress1") = fullAddress(0)
row("MailingCity") = fullAddress(1)
row("MailingState") = fullAddress(2)
row("MailingZipCode") = fullAddress(3)

End If

Full code:

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

'My datarowview and datatable
For Each drvMyDataRowViewName In dtMyDataTableName.DefaultView

'Set the results of the SeparateFullAddressFunction to an array
Dim fullAddress As Array

'Pass in FullAddress as a string
fullAddress = SeparateFullAddress(Trim(drvMyDataRowViewName("TheAddressColumnNameInTheDatatable").ToString))
Next

Private Function SeparateFullAddress(ByVal FullAddress As String) As Array
Dim AddressArray As String()
Try
Dim Street As String
Dim City As String
Dim State As String
Dim ZipCode As String

Dim lastIndex As String
lastIndex = FullAddress.LastIndexOf(" ")
ZipCode = Trim(FullAddress.Substring(lastIndex))
'Remove Zip Code From String
FullAddress = Trim(FullAddress.Remove(lastIndex))

lastIndex = FullAddress.LastIndexOf(",")
State = (FullAddress.Substring(lastIndex)).Replace(",", "").Trim

'Remove State From String
FullAddress = Trim(FullAddress.Remove(lastIndex))

lastIndex = FullAddress.LastIndexOf(";")
City = (Trim(FullAddress.Substring(lastIndex))).Replace(";", "").Trim
'Remove City From String
FullAddress = Trim(FullAddress.Remove(lastIndex))
Street = FullAddress

AddressArray = New String() {Street, City, State, ZipCode}

Return AddressArray
Catch ex As Exception

AddressArray = New String() {"Error"}
Return AddressArray
End Try
End Function

If fullAddress(0) = "Error" Then
'This was added because the spreadsheets often had weird, unpredictable data (imagine that), so rather than have an ambiguous error message, I had to give the user some feedback. I had previously declared "ErrorMessage" (Dim ErrorMessage As New StringBuilder)

ErrorMessage.Append("ERROR: Address is in an incorrect format.")
row("Error") = ErrorMessage
Else
row("MailingAddress1") = fullAddress(0)
row("MailingCity") = fullAddress(1)
row("MailingState") = fullAddress(2)
row("MailingZipCode") = fullAddress(3)

End If

.NET Gridview TemplateField Change Boolean to Yes or No

tropicalcoding — Tue, 10 Jul 2012 04:26:06 +0000

When you bind boolean data to a gridview, and you wish to change the 1 or 0 to something meaningful such as Yes or No, try the following:

1
2
3
4
5
6
7
8

<%#IIf(Boolean.Parse(Eval("ItemStatus").ToString()), "Yes", "No")%>

Developers’ Blog Network is Now Live

tropicalcoding — Mon, 09 Jul 2012 02:11:52 +0000

Over the years, I have looked for sites that would list quality blogs about programming/development – especially when I was trying to learn something new. However, I have only found a few here and there. I’d often have to slog through the search results to find some useful sites. My intention is to gather up these useful sites to minimize the time wasted filtering through the junk.

So, I started another site called Developers’ Blog Network that has listings of all kinds of blogs/articles that are helpful to us programmers. I will be adding more as time goes on. If you have any you’d like to add, please submit a listing.

Thanks!

Install WordPress Locally on Windows with Xampp

tropicalcoding — Fri, 06 Jul 2012 17:08:29 +0000

Found this great site that walks you through the steps of setting up WordPress on a Windows machine using Xampp.

http://wpmu.org/install-wordpress-locally-on-windows-with-xampp/

If you encounter any problems, you might want to check out this site, as it documents one person’s problems (and solutions) when trying to get Xampp to play nicely with Windows:

http://www.conflicteddeveloper.com/?p=5

SSRS Formatting: Remove Date from DateTime

tropicalcoding — Mon, 02 Jul 2012 12:21:46 +0000

When writing reports, sometimes you need to remove the date and just view the time….perhaps in the case of an appointment time, or a manager is checking up on his/her employees because they didn’t finish something on time.

1) Make sure that your column is a textbox. If not drag a textbox from the toolbox into the cell.

2) Right-click textbox, and choose ‘Text Box Properties…’

3) Click on ‘General’ on the left-hand side. Look for the ‘Value:’ prompt, and click ‘fx‘.

4) Paste the following:

1	=Format(Fields!FieldIAmTryingToBendToMyWill.Value,”t”)

This code will take this:

2012-06-30 12:13:52.893

And format it to look like this:
12:13 PM

PHPMyAdmin/MySQL Error: “You probably tried to upload too large file” when trying to import database

tropicalcoding — Sat, 30 Jun 2012 19:53:04 +0000

Yesterday, I was attempting to backup and restore a local copy of my WordPress database on a Windows 7 64 bit machine running Xampp.

Since I was working locally, I am the master of my universe (ok, Super Admin), and I can do whatever I want. I was able to increase the upload size and successfully import the .sql file.

Here are the instructions:

1) Find your php.ini file (for me, it was where I installed Xampp: C:\xampp\php.ini).

2) Find the following values and increase them to whatever you want: upload_max_filesize, memory_limit and post_max_size.

3) Restart the Xampp server. You can do this by going to the Xampp Control Panel and stopping the service.

4) If you try to stop the service, and receive an “error: -1″ message, then exit the control panel (shut it down). You don’t have permissions to do this.

5) Relaunch the Xampp Control Panel as an Admin by Right clicking and running it as administrator.

.NET Objectdatasource Sum Column

tropicalcoding — Mon, 11 Jun 2012 16:04:42 +0000

If you have the need to sum a column of a recordset tied to an objectdatasource, you can try the following. This is just returning sum of the column’s value, which is useful when you just need a piece of information for display purposes. In this scenario, I am totaling the number of hours worked (a decimal value).

1) Declare the dataview and assign the objectdatasource to it:

1
2
3

Dim dv As DataView

dv = MyObjectdatasourceName.Select

2) Declare a new datatable and assign it to the dataview’s table object:

1
2
3

Dim dt As New DataTable

dt = dv.ToTable

3) Create a variable to hold the value:

1	Dim TotalHours As Decimal

4) I like to make sure there actually rows before I try to sum:

1
2
3
4
5
6

If dt.Rows.Count > 0 Then

TotalHours = dt.Compute("SUM(ColumnYouAreTryingToSum)", String.Empty)
Else
TotalHours = 0
End If

5) I then printed it out to a label:

1	lblTimeEntryRecordsResult.Text = "Total Hours For This Issue: " & TotalHours

Full Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Dim dv As DataView

dv = MyObjectdatasourceName.Select

Dim dt As New DataTable

dt = dv.ToTable

Dim TotalHours As Decimal

If dt.Rows.Count > 0 Then

TotalHours = dt.Compute("SUM(Duration)", String.Empty)
Else
TotalHours = 0
End If

lblTimeEntryRecordsResult.Text = "Total Hours For This Issue: " & TotalHours

.NET Datatable: Loop Columns to Remove Spaces in Column Name

tropicalcoding — Sun, 10 Jun 2012 09:37:47 +0000

In one project, I had to loop through the datatable columns and remove the spaces in the column name. I had to import an Excel spreadsheet into a datatable, and sometimes, the spreadsheet provider did not follow the instructions and name the column properly. In this scenario, the column names had additional spaces in them. However, you could apply this technique to replace anything else.

1
2
3
4

For i As Integer = 0 To TheNameOfMyDataTable.Columns.Count - 1

TheNameOfMyDataTable.Columns(i).ColumnName = TheNameOfMyDataTable.Columns(i).ColumnName.ToString.Trim.Replace(" ", "")
Next