How to insert records to a table with an identity column

If you have a table with an identity column, you can’t just do a simple insert.  That identity column needs to be excluded from your insert statement.

There are times when you need to keep the ID values during your insert.  Like moving data between environments.

Here is the SQL you need to allow your inserts to work.
First turn on identity_insert

SET IDENTITY_INSERT <TABLENAME> ON;

Then do your insert.

Then turn off identity_insert

SET IDENTITY_INSERT <TABLENAME> OFF;
[tweetmeme only_single=”false”]

How to add a content editor web part to a SharePoint page

Here is some code to add a content editor web part to a page. So if you are creating a page in code (like during feature activation), then you put a content editor web part on the page.

Setting the InnerText (contentXMLElement.InnerText = “”;) will set the text in the content editor, incase you want something to be there by default.  User HTML.

using (SPWeb webSite = SPContext.Current.Site.OpenWeb(SiteToOpen))
{
using (SPLimitedWebPartManager mgr = webSite.GetFile(“default.aspx”).GetLimitedWebPartManager(PersonalizationScope.Shared))
{
if (mgr != null)
{
# region AddNewLink

ContentEditorWebPart cewp = new ContentEditorWebPart();
cewp.AllowClose = false;
cewp.AllowEdit = false;
cewp.AllowHide = false;
cewp.AllowMinimize = false;
cewp.ID = “ContentEditorWP”;
cewp.Title = “Content Editor Web Part”;
cewp.ChromeType = PartChromeType.None;

//Add content to the content editor web part
XmlDocument addNewXMLDoc = new XmlDocument();
XmlElement contentXMLElement = addNewXMLDoc.CreateElement(“Root”);
contentXMLElement.InnerText = “”;
cewp.Content = contentXMLElement;
cewp.Content.InnerText = contentXMLElement.InnerText;

// add the web part.
// first argument: web part object
// second argument: zone
// third argument: index (location within the zone)
mgr.AddWebPart(cewp,”left”, 0);

# endregion

}
}
}

How to programmatically determine the current method name

If you want to determine the current method in code (C#) here is the line of code you need.

MethodInfo.GetCurrentMethod().Name

This call will return the current method name as a string.  (It is from the namespace System.Reflection).

Why is this helpful?  If you write a generic logging routine and want to log the current method this is a big help.  So you don’t need to modify your log error call (in your catch) with anything not generic (like hard coding your method name).

 

Here is an example of this.

String currentMethodName = MethodInfo.GetCurrentMethod().Name;

 

If this line is in the Page_Load method, it returns “Page_Load”

Track Changes to objects in your database

Ever have the need to be able to track changes to the objects in your database? This code sample was provided by a friend, Josh Shilling.  It provides you with a change log on your database.  This will be helpful in multi developer environments when you think someone made a change to your database, but you don’t know what changes were made.

So you can run Select * from dbo.ChangeLog to see what changes have been to your db objects.

I had a hard time getting the image of the results to show.

image

GO
/****** Object: Table [DBVersion].[ChangeLog] ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[ChangeLog](
[LogId] [bigint] IDENTITY(1,1) NOT NULL,
[DatabaseName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL DEFAULT (getutcdate()),
[LoginName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY
KEY NONCLUSTERED
(
[LogId] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
--Trigger
GO
/****** Object: DdlTrigger [ddltrg_ObjectRevisionHistory] ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TRIGGER [ddltrg_ObjectRevisionHistory]
ON
database
FOR
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE
, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_INDEX
, ALTER_INDEX, DROP_INDEX, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_STATISTICS
, DROP_STATISTICS, UPDATE_STATISTICS, CREATE_SCHEMA, ALTER_SCHEMA,
DROP_SCHEMA
, GRANT_DATABASE, DENY_DATABASE, REVOKE_DATABASE, CREATE_USER, ALTER_USER,
DROP_USER
, CREATE_VIEW, ALTER_VIEW, DROP_VIEW
as
BEGIN
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO dbo.ChangeLog
(databasename, eventtype, objectname, objecttype, sqlcommand, loginname)
VALUES
(
@data
.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data
.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data
.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data
.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data
.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data
.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
END
GO
SET
ANSI_NULLS OFF
GO
SET
QUOTED_IDENTIFIER OFF
GO
ENABLE
TRIGGER [ddltrg_ObjectRevisionHistory] ON DATABASE

Record count of all tables in a database

Here is a sql script that will give a count of all records in every table in
your database.

This comes from my friend David McCollough.

CREATE TABLE #sizes ([name] nvarchar(200), 
             [rowcount] varchar(25))
DECLARE @tablename VARCHAR (128)

DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
   order by TABLE_NAME

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
  --faster than select count(*)
  exec ('Insert into #sizes Select ''' +  @tablename + ''' as [name],(SELECT rows FROM sysindexes WHERE id = OBJECT_ID(''' +  @tablename + ''') AND indid < 2)')
FETCH NEXT
   FROM tables
   INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

select * from #sizes Order by [name]
drop table #sizes

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

JS to tell if an object is viewable on the screen

 
Here is some JavaScript that has a function to tell if an object 
is viewable on the screen.
This is quite helpful if you have something on the screen you 
want to make sure the user sees,

like a an error or license agreement, etc.
 
<HTML>
<HEAD>
<SCRIPT>
function isinView(oObject)
{
    var oParent = oObject.offsetParent;
    var iOffsetTop = oObject.offsetTop;
    var iClientHeight = oParent.clientHeight;
    if (iOffsetTop > iClientHeight) {
        alert("Special Text not in view. Expand Window to put Text in View.");
    }
    else{
         alert("Special Text in View!");
    }
}
</SCRIPT>
</HEAD>
<BODY onload="window.resizeTo(430,250)" onclick="isinView(oID_1)"  SCROLL=NO>

<DIV STYLE="position:absolute;left:20px">Click anywhere in window to see if special text is in
view.</DIV>

<DIV id="oID_1" STYLE="position:absolute; left:50px;top:300px;width:280px;color:lightGreen;
font-size:large;font-weight:bold;background-color:hotPink;font-family:Arial">
            Here's some special text
</DIV>
</BODY>
</HTML>
Technorati Tags: ,