This is why i like Dr. House
1 comments Published by easymovet on Monday, December 01, 2008 at 12:14Being technically minded, people bring questions to me when they have a technical problem. This is great, it's my job and i usually have the answer; as it's my job to know. So why am i complaining?. Well the other day I get an IM from a client, the dreaded words: "does my new site support safari?" so i prepare to arm myself expecting that some huge functionality has proven to be incompatible with Safari and it's probably one of our biggest clients that needed the feature, "I don't really test much on safari, so some things may look different but it should work" as i write that IM i scramble to gather browser statistics and brainstorm on what elements are most likely to fail, i should have used jsLint.com more... no reply ... "why?" i finally ask. Reply: "oh, i got this message from someone: 'Safari can’t verify the identity of the website. The certificate for this website was signed by an unknown certifying authority. ..." . Ahh the actual error, an expired SSL, nothing to do with browsers, if only they said that in the first place.
In most cases the challenge lies not in solving the problem but in solving what the problem is. Non technically minded folks don't know what information is needed to diagnose a problem so they try to shape the information to either save face by hiding info, or they add superfluous info to show that they have been working on the problem before bothering me, which is nice as they do that out of respect for me, but really it just lowers the signal to noise ratio for me. There are also the folks who are just plain frustrated and don't care what i think so they simply show me the error and leave, i like these folks the best becasue by removing themselves it eliminates a barrier between me and the problem and then the solution.
So what i envision as a remedy is some sort of definable framework for collecting the nessesary information of an error, but thats another post. - Peace
Since i can't afford a fancy code generator i've been using a SQL query based on one that i found on SQLservercentral by Cade Bryant which uses the system schema tables to generate some simple data objects in C#, there are a few glitches that requre tweaking the SQL result, but overall it makes for quick work, (please feel free to use your SQL prowess to incorporate the twaks into the query, oh and let me know)
Your PK for the table has to be called tableName + 'ID', so a table called [Person] would have to have a Primary Key called [PersonID].
run the query then copy the column with C# into visual studio where you can make the following changes before applying automatic formating to clean it all up.
The query leaves a bit to clean up:
1. in the paramter list of the input method you have to delete the last comma.
2. Delete the last comma in the SQL strings where colums are listed.
3. Delete the primary key from the update SQL query and from the insert query
Syntax highlighted HTML generated on http://tools.devshed.com/webmaster-tools/syntax-highlighting/
CREATE PROCEDURE [dbo].[usp_TableToClass]
/*
Created by Cade Bryant.
Generates C# class code for a table
and fields/properties for each column.
Run as "Results to Text" or "Results to File" (not Grid)
Example: EXEC usp_TableToClass 'MyTable'
*/
@table_name SYSNAME
AS
SET NOCOUNT ON
DECLARE @temp TABLE
(
sort INT,
code TEXT
)
INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{'
INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, 'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10) + '{'
+ CHAR(13) + CHAR(10) + '}'
INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 5, '#region Public Properties' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, 'public ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
WHEN DATA_TYPE = 'MONEY' THEN 'decimal '
ELSE 'object '
/*END + '_' + COLUMN_NAME + ';' */
END + COLUMN_NAME + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 8, 'public ' + @table_name + '(int ' + @table_name + 'ID)'
+ CHAR(13) + CHAR(10) + '{' + CHAR(13) + CHAR(10) + '
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YourConnectionStringKeyValue"].ToString())) {' + CHAR(13) + CHAR(10) + '
using (SqlCommand cmd = new SqlCommand("Select * from '+@table_name+' where '+@table_name+'ID = @id", conn)) {' + CHAR(13) + CHAR(10) + '
cmd.Parameters.AddWithValue("id", '+@table_name+'ID);' + CHAR(13) + CHAR(10) + '
conn.Open();' + CHAR(13) + CHAR(10) + '
using (SqlDataReader dr = cmd.ExecuteReader()) {' + CHAR(13) + CHAR(10) + '
if (dr.Read())' + CHAR(13) + CHAR(10) + '
{'
INSERT INTO @temp
SELECT 9, 'this.' + COLUMN_NAME + ' = ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'Convert.ToString(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%INT%' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new int() : Convert.ToInt32(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new DateTime() : Convert.ToDateTime(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new byte() : (byte[])dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'BIT' THEN 'Convert.ToBoolean(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'Convert.ToString(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'MONEY' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new decimal() : Convert.ToDecimal(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'DECIMAL' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new decimal() : Convert.ToDecimal(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'FLOAT' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new double() : Convert.ToDouble(dr["' + COLUMN_NAME + '"])'
ELSE ' --check: ' + COLUMN_NAME
/*END + '_' + COLUMN_NAME + ';' */
END + ';' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 10, ' } } } } } '
/* insert */
INSERT INTO @temp
SELECT 11, 'public int insert('
INSERT INTO @temp
SELECT 12,
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%INT%' THEN 'int _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] _' + COLUMN_NAME + ','
WHEN DATA_TYPE = 'BIT' THEN 'bool _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string _' + COLUMN_NAME + ','
WHEN DATA_TYPE = 'MONEY' THEN 'decimal _' + COLUMN_NAME + ','
WHEN DATA_TYPE = 'DECIMAL' THEN 'decimal _' + COLUMN_NAME + ','
ELSE ' --check: ' + COLUMN_NAME
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 13, ' )' + CHAR(13) + CHAR(10) +'
{' + CHAR(13) + CHAR(10) +'
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNSTR"].ToString())) {' + CHAR(13) + CHAR(10) +'
using (SqlCommand cmd = new SqlCommand(@"' + CHAR(13) + CHAR(10) +'
INSERT INTO [' + @table_name + '] (
'
INSERT INTO @temp
SELECT 14, '[' + COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 15, ' ) VALUES ('
INSERT INTO @temp
SELECT 16, '[' + COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 17, ' )
SELECT SCOPE_IDENTITY() AS ' + @table_name + 'ID ", conn)) {' + CHAR(13) + CHAR(10) +'
'
INSERT INTO @temp
SELECT 18, 'cmd.Parameters.AddWithValue("@' + COLUMN_NAME + '",' + COLUMN_NAME + ');'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 19, '' + CHAR(13) + CHAR(10) +'
conn.Open();
return (int)cmd.ExecuteScalar();' + CHAR(13) + CHAR(10) +'
}}}' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 20, '
public void Update()' + CHAR(13) + CHAR(10) +'
{' + CHAR(13) + CHAR(10) +'
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNSTR"].ToString())) {' + CHAR(13) + CHAR(10) +'
using (SqlCommand cmd = new SqlCommand(@"' + CHAR(13) + CHAR(10) +'
UPDATE
['+ @table_name +']
SET
'
INSERT INTO @temp
SELECT 21, '[' + COLUMN_NAME + '] = @' + COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 22, '
WHERE
['+ @table_name + 'ID] = @'+ @table_name + 'ID
", conn)) {' + CHAR(13) + CHAR(10) +'
'
INSERT INTO @temp
SELECT 23, 'cmd.Parameters.AddWithValue("@' + COLUMN_NAME + '",' + COLUMN_NAME + ');' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 24, 'cmd.ExecuteNonQuery(); ' + CHAR(13) + CHAR(10) + '
}}}}'
SELECT * FROM @temp
ORDER BY sort
/*
Created by Cade Bryant.
Generates C# class code for a table
and fields/properties for each column.
Run as "Results to Text" or "Results to File" (not Grid)
Example: EXEC usp_TableToClass 'MyTable'
*/
@table_name SYSNAME
AS
SET NOCOUNT ON
DECLARE @temp TABLE
(
sort INT,
code TEXT
)
INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{'
INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, 'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10) + '{'
+ CHAR(13) + CHAR(10) + '}'
INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 5, '#region Public Properties' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, 'public ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
WHEN DATA_TYPE = 'MONEY' THEN 'decimal '
ELSE 'object '
/*END + '_' + COLUMN_NAME + ';' */
END + COLUMN_NAME + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 8, 'public ' + @table_name + '(int ' + @table_name + 'ID)'
+ CHAR(13) + CHAR(10) + '{' + CHAR(13) + CHAR(10) + '
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YourConnectionStringKeyValue"].ToString())) {' + CHAR(13) + CHAR(10) + '
using (SqlCommand cmd = new SqlCommand("Select * from '+@table_name+' where '+@table_name+'ID = @id", conn)) {' + CHAR(13) + CHAR(10) + '
cmd.Parameters.AddWithValue("id", '+@table_name+'ID);' + CHAR(13) + CHAR(10) + '
conn.Open();' + CHAR(13) + CHAR(10) + '
using (SqlDataReader dr = cmd.ExecuteReader()) {' + CHAR(13) + CHAR(10) + '
if (dr.Read())' + CHAR(13) + CHAR(10) + '
{'
INSERT INTO @temp
SELECT 9, 'this.' + COLUMN_NAME + ' = ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'Convert.ToString(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%INT%' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new int() : Convert.ToInt32(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new DateTime() : Convert.ToDateTime(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new byte() : (byte[])dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'BIT' THEN 'Convert.ToBoolean(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'Convert.ToString(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'MONEY' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new decimal() : Convert.ToDecimal(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'DECIMAL' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new decimal() : Convert.ToDecimal(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'FLOAT' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new double() : Convert.ToDouble(dr["' + COLUMN_NAME + '"])'
ELSE ' --check: ' + COLUMN_NAME
/*END + '_' + COLUMN_NAME + ';' */
END + ';' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 10, ' } } } } } '
/* insert */
INSERT INTO @temp
SELECT 11, 'public int insert('
INSERT INTO @temp
SELECT 12,
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%INT%' THEN 'int _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] _' + COLUMN_NAME + ','
WHEN DATA_TYPE = 'BIT' THEN 'bool _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string _' + COLUMN_NAME + ','
WHEN DATA_TYPE = 'MONEY' THEN 'decimal _' + COLUMN_NAME + ','
WHEN DATA_TYPE = 'DECIMAL' THEN 'decimal _' + COLUMN_NAME + ','
ELSE ' --check: ' + COLUMN_NAME
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 13, ' )' + CHAR(13) + CHAR(10) +'
{' + CHAR(13) + CHAR(10) +'
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNSTR"].ToString())) {' + CHAR(13) + CHAR(10) +'
using (SqlCommand cmd = new SqlCommand(@"' + CHAR(13) + CHAR(10) +'
INSERT INTO [' + @table_name + '] (
'
INSERT INTO @temp
SELECT 14, '[' + COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 15, ' ) VALUES ('
INSERT INTO @temp
SELECT 16, '[' + COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 17, ' )
SELECT SCOPE_IDENTITY() AS ' + @table_name + 'ID ", conn)) {' + CHAR(13) + CHAR(10) +'
'
INSERT INTO @temp
SELECT 18, 'cmd.Parameters.AddWithValue("@' + COLUMN_NAME + '",' + COLUMN_NAME + ');'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 19, '' + CHAR(13) + CHAR(10) +'
conn.Open();
return (int)cmd.ExecuteScalar();' + CHAR(13) + CHAR(10) +'
}}}' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 20, '
public void Update()' + CHAR(13) + CHAR(10) +'
{' + CHAR(13) + CHAR(10) +'
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNSTR"].ToString())) {' + CHAR(13) + CHAR(10) +'
using (SqlCommand cmd = new SqlCommand(@"' + CHAR(13) + CHAR(10) +'
UPDATE
['+ @table_name +']
SET
'
INSERT INTO @temp
SELECT 21, '[' + COLUMN_NAME + '] = @' + COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 22, '
WHERE
['+ @table_name + 'ID] = @'+ @table_name + 'ID
", conn)) {' + CHAR(13) + CHAR(10) +'
'
INSERT INTO @temp
SELECT 23, 'cmd.Parameters.AddWithValue("@' + COLUMN_NAME + '",' + COLUMN_NAME + ');' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 24, 'cmd.ExecuteNonQuery(); ' + CHAR(13) + CHAR(10) + '
}}}}'
SELECT * FROM @temp
ORDER BY sort
To see it right click on, say, an image and choose "Inspect Element".
The console lets you run commands that interface with the elements on the page, so it works with the Jquery library, and it has autosuggest. Here I entered a jquerry command to edit the value attribute of the input variable thats it highlighted in yellow. You can dbl- click on the css to edit it and see the results live.
UPDATE: Try out firebug in Firefox, it does most of this and more!
Shreenshot of the element inspector.
Shreenshot of another cool Chrome Feature. To see it right click on, say, an image and choose "Inspect Element"
Shreenshot of another cool Chrome Feature. To see it right click on, say, an image and choose "Inspect Element"
First i tried consuming web services that serialized structs into XML but now I got it spewing JSON which means no more escaping strings for javascript. When dumping large bits of content into a page XML made sense to me since it was already HTML escaped, but if i wanted to send commands in the same message i would have to make sure it was JS safe too, Having an un-escaped string will kill the JS but a pooly formatted HTML will probably survive (nat that its then a an excuse for pooly format HTML), in fact it may make sense to return an XML type in the struct, i'll have to look into that. So here is how to make it workie:
in the .asmx
using System;
using System.Web.Script.Services;
using System.Web.Script.Serialization;
namespace mcpV2
{///}
/// Summary description for WebService2
///
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
[ScriptService]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class WebService2 : System.Web.Services.WebService
{public struct tests}
{public string thestr;}
public int theint;
[WebMethod]
[ScriptMethod]
public tests HelloWorld()
{tests ttt = new tests();}
ttt.theint = 9;
ttt.thestr ="hello worls";
return ttt;
This will spit out:
{"d":{"__type":"mcpV2.WebService2+tests","thestr":"hello worls","theint":9}}I called it with the JQUERY .ajax method :
$.ajax({I added some other stuff for error catching but you get the idea. I had some trouble with Web services making huge memory leaks with the XMLserializer, hopefully the .NET AJAX serializer is not a memory hog...
type: "POST",
url: "webservice2.asmx/HelloWorld",
beforeSend: function(xhr) {
xhr.setRequestHeader("Content-type",
"application/json; charset=utf-8");
},
dataType: "json",
success: function(msg) {
// Insert the returned HTML into the .
$('#Div1').text(msg.d.thestr);
}
});
