Sunday, August 29, 2010

Interesting Clipboard Functionality

Last week’s blog post was an excerpt from my .Net Tips column in the April 2008 Issue of the Universal Thread Magazine. Well, that was such a good issue for Tips, that I'm going to post another one from the same issue. I hope no one minds! ;0)

Did you ever need to copy an instantiated class to the clipboard, retreive it and access a property or method on it? Well, here's how to do it:

Clipboard.SetData("MyClassFormat", new MyClass("42"));
if (Clipboard.ContainsData("MyClassFormat"))
{
MyClass o = Clipboard.GetData("MyClassFormat") as MyClass;
if (o != null)
{
// do stuff here, such as execute a method
// o.MyMethod();
// or set a property
// o.MyProperty = "xyz";
}
}
else
{
MessageBox.Show("Ain't nothing there!!");
}

There's only one gotcha and that is that your class must have the [Serializable] attribute.

Thanks to Einar Kvandahl in Message #1135004 on the Universal Thread.

Sunday, August 22, 2010

Determine new PK after an Insert

Today’s blog post will be another excerpt from my .Net Tips column in the April 2008 Issue of the Universal Thread Magazine.

You have a couple of options to get the PK value of a newly inserted record. I am assuming SQL Server database in these examples.

First, if you are not using Stored Procs and simply sending an INSERT INTO statement, just add a "SELECT SCOPE_IDENTITY() at the end of your query and be sure to execute your query by reading the result back (into a datareader or dataset for example).

Your query string should look like this:

string Sql = "INSERT INTO MyTable (ColumnOne) VALUES ('Bob') SELECT SCOPE_IDENTITY() AS MyPK";

And, here's the code that shows how to execute and read the results back using a DataReader:

int MyPK = 0;
SqlCommand sc = new SqlCommand(Sql, new SqlConnection(this.TestConnection));
sc.Connection.Open();
SqlDataReader rd = sc.ExecuteReader(CommandBehavior.CloseConnection);

while (rd.Read())
{
MyPK = Convert.ToInt32(rd["MyPK"]);
}

Alternatively, if you're using a Stored Proc, you'd use an OUTPUT parameter in your Stored Proc, and a ParameterDirection.InputOutput in your code:

-- The Stored Proc
CREATE PROCEDURE MySP
@PK int = NULL OUTPUT,
@ColumnOne char(8) = NULL,
@ColumnTwo char(4) = NULL
AS
INSERT MyTable (ColumnOne, ColumnTwo)
SELECT @ColumnOne, @ColumnTwo

SELECT @PK = SCOPE_IDENTITY()

And here's how you would call it in your code:

Command.CommandText = "MySP";
Command.Parameters.Add("@PK", 0);
Command.Parameters["@PK"].Direction = ParameterDirection.InputOutput;
Command.Parameters.Add("@ColumnOne", OneValue);
Command.Parameters.Add("@ColumnTWo", TwoValue);

Command.ExecuteNonQuery();

MyNewPK = Command.Parameters["@PK"].Value;

Thanks to Éric Moreau (and another post by me) in Messages #1090511 and #930339 on the Universal Thread.