The Stark Zone

Execute sqlcmd (oSql) from .NET and capture the output

2006-09-14T05:28:00Z

I have recently seen quite a few questions about executing sqlcmd (oSql for those on 2000) from .NET code. I have a bunch of utilities that i have written that do this and thought I would share the basic code to do it.

You can also easily capture the output as a stream and do whatever you want with it. In the example below I dump it to a file....I also use the -o to output the results of the sql that was excuted.
Just use the RedirectStandardOutput and RedirectStandardError
One caveat is to make sure you do the ReadToEnd before and after the waitforexit(). I had issues where it wouldn't complete and ended up just hanging if I didn't do that.

using (Process proc = new Process())

{

proc.StartInfo.FileName = @"""C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SqlCmd.exe""";
proc.StartInfo.Arguments = @"-Uuser -Ppassword -S" + DestinationServer + " -d" + DestinationDatabase + @" -i""" + SqlToExecute+ @""" -o""" + OutSqlFileName + @"""";
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.CreateNoWindow = true;
proc.Start();
System.IO.File.AppendAllText(OutFileName, proc.StandardOutput.ReadToEnd());
proc.WaitForExit();
System.IO.File.AppendAllText(OutFileName, proc.StandardOutput.ReadToEnd());
proc.Close();

}

Let me know if you have any questions or comments on this.

Messenger 7.5 Beta

2005-08-26T02:44:00Z

I downloaded the messenger 7.5 beta and found a hack to get rid of the annoying ads that messenger puts out. The new messenger has a slightly different look, but for the most part it is the same. I am just happy to get rid of the ads.

SQL Renumbering

2005-03-10T03:04:00Z

I recently had a need to allow users to order all of their different phone numbers. I wanted to make it simple and just provide a text box so the user can type in whatever priority they want the phone number to have. I also wanted it to automatically renumber. So if they already have a number with a priority of 1 and they add or update a number and set it's priority to 1, that one becomes the new #1 and everything else moves down. For this problem, there is a contact table, a phone table and a contactphonexref table that contains the contactid, a phoneid, and a sequence number (priority)

Here is what I came up with in the stored proc to do the reordering....

First thing was to insert or update the contactphonexref record. Here we set the seqno to whatever the user typed in be it 1,3, or 10000. I left this code out because it seems obvious.

Next was to figure out a way to renumber. I wanted to guarantee that the record being inserted or updated got put in the position that the user requested even if it meant bumping others down.

I didn't want to use a cursor, so I thought I would try creating a table variable that has an identity and store the contactphonexrefid and the seqno
declare @t table (newseqno int identity(1,1), contactphonexrefid int, seqno int)

Then I just do an insert into my table variable for all phone records for this contact and order by seqno. As I noted above, I wanted to make sure if the record being inserted or updated had the same sequence number as another record for that contact, the new record got that number. So, I decided I would take the absolute value of the contactphonexrefid in my table variable minus the contactphonexrefid of the record that got inserted or updated. The thinking there is the record being inserted or updated will always have a absolute value of zero and everything else will be greater than zero so it ensures the new record wins.

insert into @t (contactphonexrefid, seqno)

select contactphonexrefid, seqno from contactphonexref (nolock) where contactid = @contactid

order by seqno, abs(@ContactPhonexrefId - contactphonexrefid)

Once this is done, simply update the contactphonexref table from the table variable and set the seqno equal to the identity column which I called newseqno.

update contactphonexref set seqno = t.newseqno

FROM contactphonexref cpa

inner join @t t on t.contactphonexrefid = cpa.contactphonexrefid

where contactid = @contactid

This solution seems to work quite well for me. It is basically an implementation of a stack. I would be interested in hearing other solutions people have used to accomplish this. Special thanks to beej for some insight on this.

C# Type Conversion

2005-02-11T00:26:00Z

I have been running into some issues with type conversion lately. I never have quite figured out the differences between all the different ways you can do type conversions. There are 3 that I use throughout my code.

(int) myVariable
myVariable as int
Convert.ToInt32(myVariable)

I found a few articles on MSDN regarding the subject that helped clear the water a little.

If anyone else has any good references surrounding type conversions, please let me know.