Friday, September 26, 2008

Upgrade varbinary(max) to varbinary(max) FILESTREAM

Code Snippet

-- Initial schema:

CREATE TABLE [dbo].[Pictureimage](

[PictureId] [uniqueidentifier] NOT NULL,

[Image] [varbinary](max) NOT NULL,

[OriginalImage] [varbinary](max) NOT NULL,

[Version] [timestamp] NOT NULL,

CONSTRAINT [PK_Pictureimage] PRIMARY KEY CLUSTERED

(

[PictureId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

-- Attach 90 db…

-- Set Comp level

EXEC sp_dbcmptlevel Amigo, 100;

GO

-- Create FILESTREAM filegroup

ALTER database Amigo

ADD FILEGROUP fsfg_Amigo

CONTAINS FILESTREAM

GO

--Add a file for storing database photos to FILEGROUP

ALTER database Amigo

ADD FILE

(

NAME= 'fs_Amigo',

FILENAME = 'C:\fs_Amigo'

)

TO FILEGROUP fsfg_Amigo

GO

-- Migration to FILESTREAM

ALTER TABLE dbo.PictureImage

SET ( FILESTREAM_ON = fsfg_Amigo )

GO

ALTER TABLE dbo.PictureImage

ALTER COLUMN PictureId ADD ROWGUIDCOL

GO

ALTER TABLE dbo.PictureImage

ADD OriginalImageFS varbinary(MAX) FILESTREAM NULL;

GO

UPDATE dbo.PictureImage SET OriginalImageFS = [OriginalImage];

GO

ALTER TABLE dbo.PictureImage

DROP COLUMN OriginalImage;

GO

EXEC sp_rename 'AmigoProd.dbo.PictureImage.OriginalImageFS', 'OriginalImage', 'COLUMN';

GO

Monday, September 15, 2008

Recive PostSubmitter

if (Request.HttpMethod == "POST")
{
Stream dataStream = Request.InputStream;
StreamReader reader = new StreamReader(dataStream);
string responseFromServer = reader.ReadToEnd();
string[] splittempQuery = responseFromServer.Split(("&").ToCharArray());
for (int i = 0; i < splittempQuery.Length; i++)
{
string[] qryValues = splittempQuery[i].Split(("=").ToCharArray());
if (qryValues[0].ToLower() == "op")
{
string aa = qryValues[1].ToString();
}
else if (qryValues[0].ToLower() == "rel_code")
{
string aa = qryValues[1].ToString();
}
else if (qryValues[0].ToLower() == "FREE_TEXT")
{
string aa = qryValues[1].ToString();
}
else if (qryValues[0].ToLower() == "SEARCH")
{
string aa = qryValues[1].ToString();
}
}
reader.Close();
dataStream.Close();
}

call postsubmitter

PostSubmitter post = new PostSubmitter();
post.Url = url;
post.PostItems.Add("op", "100");
post.PostItems.Add("rel_code", "1102");
post.PostItems.Add("FREE_TEXT", "c# jobs");
post.PostItems.Add("SEARCH", "");
post.Type = PostSubmitter.PostTypeEnum.Post;
string result = post.Post();

Http Post Post Submiter

public class PostSubmitter
{
///
/// determines what type of post to perform.
///
public enum PostTypeEnum
{
///
/// Does a get against the source.
///
Get,
///
/// Does a post against the source.
///
Post
}

private string m_url = string.Empty;
private NameValueCollection m_values = new NameValueCollection();
private PostTypeEnum m_type = PostTypeEnum.Get;
///
/// Default constructor.
///
public PostSubmitter()
{
}

///
/// Constructor that accepts a url as a parameter
///
///The url where the post will be submitted to.
public PostSubmitter(string url)
: this()
{
m_url = url;
}

///
/// Constructor allowing the setting of the url and items to post.
///
public PostSubmitter(string url, NameValueCollection values)
: this(url)
{
m_values = values;
}

///
/// Gets or sets the url to submit the post to.
///
public string Url
{
get
{
return m_url;
}
set
{
m_url = value;
}
}
///
/// Gets or sets the name value collection of items to post.
///
public NameValueCollection PostItems
{
get
{
return m_values;
}
set
{
m_values = value;
}
}
///
/// Gets or sets the type of action to perform against the url.
///
public PostTypeEnum Type
{
get
{
return m_type;
}
set
{
m_type = value;
}
}
///
/// Posts the supplied data to specified url.
///
string Post()
{
StringBuilder parameters = new StringBuilder();
for (int i = 0; i < result =" PostData(m_url,">
string Post(string url)
{
m_url = url;
return this.Post();
}
string Post(string url, NameValueCollection values)
{
m_values = values;
return this.Post(url);
}
string PostData(string url, string postData)
{
HttpWebRequest request = null;
if (m_type == PostTypeEnum.Post)
{
Uri uri = new Uri(url);
request = (HttpWebRequest)WebRequest.Create(uri);
request.Method = "POST";
request.ContentType = "application/x-www-form-urlencoded";
request.ContentLength = postData.Length;
using (Stream writeStream = request.GetRequestStream())
{
UTF8Encoding encoding = new UTF8Encoding();
byte[] bytes = encoding.GetBytes(postData);
writeStream.Write(bytes, 0, bytes.Length);
}
}
else
{
Uri uri = new Uri(url + "?" + postData);
request = (HttpWebRequest)WebRequest.Create(uri);
request.Method = "GET";
}
string result = string.Empty;
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
using (Stream responseStream = response.GetResponseStream())
{
using (StreamReader readStream = new StreamReader(responseStream, Encoding.UTF8))
{
result = readStream.ReadToEnd();
}
}
}
return result;
}
void EncodeAndAddItem(ref StringBuilder baseRequest, string key, string dataItem)
{
if (baseRequest == null)
{
baseRequest = new StringBuilder();
}
if (baseRequest.Length != 0)
{
baseRequest.Append("&");
}
baseRequest.Append(key);
baseRequest.Append("=");
baseRequest.Append(System.Web.HttpUtility.UrlEncode(dataItem));
}
}

Friday, September 5, 2008

SMO Backup DB

// Delete the Preview directory
if ( Directory.Exists(previewAssetUnc + @"\" + studioCode) )
{
Directory.Delete(previewAssetUnc + @"\" + studioCode, true);
}

// Delete the Master Asset Backup directory
if ( Directory.Exists(backupUnc + @"\Masters\" + studioCode) )
{
Directory.Delete(backupUnc + @"\Masters\" + studioCode, true);
}

// Delete the Import directory
if ( Directory.Exists(importUnc + @"\" + studioCode) )
{
Directory.Delete(importUnc + @"\" + studioCode, true);
}

SMO Delete DB

// Kill the Studio DB
server.KillDatabase(dbName);

// Delete the Master Asset directory
if ( Directory.Exists(masterAssetUnc + @"\" + studioCode) )
{
Directory.Delete(masterAssetUnc + @"\" + studioCode, true);
}


// Delete the Preview directory
if ( Directory.Exists(previewAssetUnc + @"\" + studioCode) )
{
Directory.Delete(previewAssetUnc + @"\" + studioCode, true);
}

// Delete the Master Asset Backup directory
if ( Directory.Exists(backupUnc + @"\Masters\" + studioCode) )
{
Directory.Delete(backupUnc + @"\Masters\" + studioCode, true);
}

// Delete the Import directory
if ( Directory.Exists(importUnc + @"\" + studioCode) )
{
Directory.Delete(importUnc + @"\" + studioCode, true);
}

SMO Clean DB

string dbServerMachineName = dataSource;

Server server = new Server(dbServerMachineName);
Database db = server.Databases["sampleDBName"];

StringCollection tables = new StringCollection();
// Clean log data
tables.Add("SampleTable");


// Drops FK's
int dropCount = 0;
StringCollection script = new StringCollection();
for ( int i = 0; i < tables.Count; i++ )
{
try
{
if ( i == 43 )
{
string x = null;
}
Table table = db.Tables[tables[i], "Sample"];
for ( int j = 0; j < table.ForeignKeys.Count; j++ )
{
string[] fkScript = new string[table.ForeignKeys[j].Script().Count];
table.ForeignKeys[j].Script().CopyTo(fkScript, 0);
script.AddRange(fkScript);
table.ForeignKeys[j].MarkForDrop(true);
dropCount++;
}
table.Alter();
}
catch ( Exception ex )
{
throw ex;
}
}

// Truncate data
SqlServerController sqlServerController = new SqlServerController(dbServerMachineName);
for ( int i = 0; i < tables.Count; i++ )
{
sqlServerController.CleanTable(dbPrefix + studioCode + dbSuffix, tables[i], "Sample");
}

// Recreate FK's
for ( int i = 0; i < script.Count; i++ )
{
script[i] = script[i].Replace("REFERENCES", "REFERENCES[Sample].");
}
db.ExecuteNonQuery(script);

// Delete asset delivery files
if ( options.DeleteAssetFiles )
{
DirectoryInfo masterAssetDir = new DirectoryInfo(masterAssetUnc + @"\" + studioCode);
masterAssetDir.Delete(true);
masterAssetDir.Create();
}

// Rebuild Indexes
for ( int i = 0; i < tables.Count; i++ )
{
db.Tables[i].RebuildIndexes(0);
}

// Shring DB
db.Shrink(0, ShrinkMethod.Default);