DateTime.ToString() Patterns

http://www.geekzilla.co.uk/View00FF7904-B510-468C-A2C8-F859AA20581F.htm

a conversion of interest

DateTime.Now.Date.ToString(System.Globalization.CultureInfo.GetCultureInfo("en-GB").DateTimeFormat.ShortDatePattern)

How to attach MDF without LDF - good link

http://blog-rat.blogspot.com/2009/04/how-to-attach-mdf-without-ldf-into-sql_13.html

Passing multiple rows to a stored procedure

Passing multiple rows to a stored procedure

Passing multiple rows to a sp has been quite a problem for a while in sql server. Although many workarounds such as using an XML data type can be used, it can be quite cumbersome. With SQL 2008 this burden has been mitigated by the introduction of table-valued parameters. It can also interface with .net applications as you will see later on in this article.

Using Table-Valued Parameters

First and foremost we will have to create a user-defined table type.

Database Node > Programmability > Types > User-Defined Table Types

Script to create a User-Defined Table type

--Create User-defined Table Type

CREATE TYPE dbo.EmployeeRecords AS TABLE

(

id int PRIMARY KEY,

FirstName varchar(20) NOT NULL,

LastName varchar(20) NOT NULL,

HireDate datetime NOT NULL DEFAULT GETDATE()

)

GO

--Using the User-Defined Table Type

DECLARE @MyEmployeeRecords dbo.EmployeeRecords

INSERT INTO @MyEmployeeRecords(id ,FirstName,LastName,HireDate)

VALUES (1112,'Anne','Smith','2009-09-30 10:00:00'),

(2222,'John','Hemir','2009-09-30 10:00:00')

-- Select the inserted records using new type

SELECT * FROM @MyEmployeeRecords

Using a user-defined table type as a parameter in a stored procedure

1. Create the user-defined table type if you haven’t already done so

--Create User-defined Table Type

CREATE TYPE dbo.EmployeeRecords AS TABLE

(

id int PRIMARY KEY,

FirstName varchar(20) NOT NULL,

LastName varchar(20) NOT NULL,

HireDate datetime NOT NULL DEFAULT GETDATE()

)

GO

2. Grant permission to the user to use the user-defined table type

GRANT EXECUTE ON TYPE::dbo.EmployeeRecords TO ;

3. Create the stored procedure

a. Create the table to which data will be added

CREATE TABLE dbo.Employee

(

id int PRIMARY KEY,

FirstName varchar(20) NOT NULL,

LastName varchar(20) NOT NULL,

HireDate datetime NOT NULL DEFAULT GETDATE()

)

b. Write the sp

CREATE PROCEDURE InsertEmployee

@MyParameter EmployeeRecords READONLY

AS

INSERT INTO Employee (id ,FirstName,LastName,HireDate)

SELECT id,FirstName,LastName,HireDate

FROM @MyParameter

-- for the table valued parameter

 GO

c. Executing the SP

--Using the User-Defined Table Type in stored procedure

DECLARE @MyEmployeeRecords EmployeeRecords

INSERT INTO @MyEmployeeRecords(id ,FirstName,LastName,HireDate)

VALUES (1111,'Anne','Smith','2009-09-30 10:00:00'),

(2222,'John','Hemir','2009-09-30 10:00:00')

EXEC InsertEmployee @MyEmployeeRecords

-- Select the records inserted using Stored procedure

SELECT * FROM Employee

Simple, isn’t it? So next time you think about passing multiple rows try this out, it is a lot easier and would save you some programming time as well.

Now let’s see as to how we can use this in a .net application.

Table-Valued parameters with ADO.NET

Table-valued parameters facilitate developers to send many rows of data to the database in a single trip thereby saving network resources and increasing performance. As of now you cannot return table-valued parameters, they are only available in one flavor, Input only. It is also worth making a note that these variables are strongly typed and their structure is automatically validated. Once all the rows are bundled into it and it is passed to the SQL server it can be used in any transact-SQL statement like a conventional table.

Drawbacks of table-valued parameters

1. Can’t use ALTER TABLE statements to modify its design

2. In transact-sql these parameters are read only. Data manipulation of any kind is not possible. If parameters which are passed into SP’s need alteration then they need to be inserted into a table variable or temporary variable.

3. The cannot be passed to user-defined functions

4. Can only be indexed to support PK and Unique constraints

Modifying data with table-valued parameters

Following are few examples as to how one may go about using these variables in data manipulation.

Updating data

UPDATE dbo.Employee

    SET Employee.Branch = e.Branch

    FROM dbo.Employee INNER JOIN @MyEmployeeRecords AS e

    ON dbo.Employee.id = e.id;

Inserting Data

 INSERT INTO Employee (id ,FirstName,LastName,HireDate)

 SELECT id,FirstName,LastName,HireDate

 FROM @MyEmployeeRecords

Passing Table-Valued parameters to SP’s

Let’s consider the following application where data in a table is updated using a table-valued parameter.

Updating Employee Information

The following application displays employee information in a grid and also facilitates to update all rows by the way of a table-valued parameter.

The following code snippet has a command object with a table-valued parameter whose type is set to “structured”. This parameter carries the table to the db layer. In this case the variable “updatedEmployees” is of type datatable.

// Configure the SqlCommand and SqlParameter.

SqlCommand updateCommand = new SqlCommand("UpdateEmployee", conn);

conn.Open();

updateCommand.CommandType = CommandType.StoredProcedure;

SqlParameter tvpParam = updateCommand.Parameters.AddWithValue(

"@MyParameter", updatedEmployees);

tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.

updateCommand.ExecuteNonQuery();

I have also included the code sample I have been working on in this article. Hope this will be useful and helpful. Happy coding to all.

JQuery

Although I have been using this for quite a sometime I never got the opportunity to look deep into this js framework. I’m delving deep into it and hope to produce my own plugin soon,iaJ

Bits about SEO with asp.net

It was my newest research. To find out how to develop search engine friendly web sites in asp.net. In here I will just summarize my findings so that it will help amateurs like me to get an insight into it.

Things to be considered in developing search engine friendly web sites

1. Post back

Post backs are just too detrimental when it comes to search engines. Spiders simply can’t evaluate them. As an example assume you display a block of information based on a button’s click event, spiders won’t be able to index them. If you want them indexed then you will have to avoid the post back and reload the page. A parameter in the url will determine the status of the page, that is whether the button was clicked or not.

2. View state

Another major bottle neck would be the use of view states. Since some view states can be pretty huge after getting collected over a period, the keyword might not be found or will be found deep within the content which in turn means that the rank of the site with respect to the keyword will be far lower than what’s expected. Hence it would be very much advisable if suitable workarounds can be used in place of view states.

3. URL rewriting

This will help you to create spider friendly URL’s. What a URL rewriter basically does is convert the parameters to a directory like structure.

For example, page1.aspx?param1=101&param2=anne becomes something like: /page1/1/anne/default.aspx.

This will help spiders crawl deep into your site and would also increase the efficiency to a substantial level.

4. Titles and Meta information

If these information are not changed all your pages will be considered as the same by search engines, this also means that many of your pages will lose their credibility. So it’s very important to change the title and meta description for each and every page. Say you change the title but not the meta description, but the meta description will be displayed in the search result and it would look irrelevant. So it’s important to use mechanisms by which we ensure that these are changed before deployment.

5. Managing site traffic

Mind you none of us like to work with sites which load at the speed of snails. So if our sites are to be indexed by search engine robots and in the mean time if we want to have an acceptable response time we should place close attention to our site performance before moving to production. Search engine spiders will inevitably drop sites from their indices if they respond too slowly.

6. AJAX

Using Ajax could spell trouble for you, because spiders don’t run java scripts. So at least in the initial sections of your page try to avoid using java scripts. So that spiders can index your pages more accurately, this in turn means that you will be found fasterJ. One simple test you can do to see what spiders will index is to switch off js support and test the page to see what’s going to get indexed.

These are just a few things I learnt from my research. Hope it will be useful.

How to change user in Team Explorer

Since i installed Team Foundation Server 2008, i used Administrator login for test purposes. But after that i couldn't change user in team explorer.
To do it, you should run Stored User Names and Passwords dialog box, to do so, either run command rundll32 keymgr.dll,KRShowKeyMgr or open it from control panel
Select your server and press Change button. Type new username in popup window.

But if you try to change a file in VS you'll get error "TF14091:You cannot perform this operation on workspace workspace;server\username because you are not the owner of the workspace."
To fix the error, you should remove old workspace and add new.
(Note, you should check-in your pending changes to avoid data loss).
1. Run Visual Studio 2008 Command Prompt (Start->All Programs->Microsoft Visual Studio 2008->Visual Studio Tools->Visual Studio 2008 Command Prompt)
2. Type tf workspace /delete yourcomputername /server:servername
3. Type tf workspace /new /server:servername and fill fields in popup window

Last thing you should do is to open your project, right-click on solution and select Get Latest Version command.

Source - http://ogolikhin.blogspot.com/2008/06/how-to-change-user-in-team-explorer.html

ASP.Net based Tab Control

Coming Up !!!!

Obtaining values from a hidden column in a grid view

Although in .NET 2003, accessing a hidden cell in a grid was pretty straight forward, with 2005, a bit of a work-around is required in order to achieve the same. Although ‘Visible=False’ would allow us to access a cell for a hidden column in 2003, in the case of 2005, we need to use the style ‘display:none;’ in order to achieve the same. A simple example is as given below.










Applying it to hide the column in the grid,

Checking All Pages in a GRID - ASP.NET

Checking All Pages in a GRID - ASP.NET

This article explains how to maintain the selected checkboxes across pages and also through sorting. I have referred many resources on the net in developing this code. It’s quite a simple code at the end of the day, although I did spend sometime figuring it out at the beginning.

Download Code

References

The following article forms the basis of what I have done here. Instead of the “Data Keys” used here I am using the unique “rowno” for each record.

http://aspalliance.com/774

Why couldn’t I use the code in the above article in my application?

The above application uses the primary key value in order to uniquely identify each record, but in the case of my application (i.e. my real world app,not the sample here) which queried many tables I couldn’t find a key which was unique to the entire rowset. So I thought it was ideal to use the row index. So I have modified the application to cater for this requirement. The rest is more or less identical to the above article.

Anyway let me explain!

The grid in this article enables us to select records in the current page and also to select all records in the grid as depicted below.

Data extraction code

The following code snippets demonstrate the data retrieval code for this application. I’m using a session variable in order to eliminate server trips to the db server.

The dataset is stored in a session variable and handled across by using a static dataset.

private DataSet getProductData()
{
try
{
string strqry = "select ProductID,ProductName,SupplierID,CategoryID,UnitPrice from Products";

SqlConnection con = new SqlConnection(ConnectionString);
SqlDataAdapter da = new SqlDataAdapter(strqry, con);
DataSet ds = new DataSet();
da.Fill(ds, "Products");
return ds;
}
catch (Exception ex)
{
lblMessage.Text = ex.Message.ToString();
return null;
}
}


private void getData()
{
try
{

if (Session["ProductList"] == null)
{
dsProducts = getProductData();
DataColumn dc = new DataColumn("rowno", System.Type.GetType("System.Int32"));
dsProducts.Tables[0].Columns.Add(dc);

int I = 0;

foreach (DataRow r in dsProducts.Tables[0].Rows)
{
r[dsProducts.Tables[0].Columns.Count - 1] = I;
I += 1;
}

Session["ProductList"] = dsProducts;
GridView1.PageIndex = 0;
}
else
{
dsProducts = (DataSet)Session["ProductList"];
}

DataTable dt = dsProducts.Tables[0];

DataView dv = new DataView(dt);


string SortField = (ViewState["SortOrder"] == null) ? "" : ViewState["SortOrder"].ToString();
// set the default sort order
if (SortField == "")
SortField = "ProductID asc";

dv.Sort = SortField;

GridView1.DataSource = dv;
GridView1.DataBind();


}
catch (Exception ex)
{
lblMessage.Text = ex.Message.ToString();
}
}



As you might see in this code I’m creating a new data column named “rowno”, and assigning it with the row index value for each record, which undoubtedly is unique across this dataset.


DataColumn dc = new DataColumn("rowno", System.Type.GetType("System.Int32"));

dsProducts.Tables[0].Columns.Add(dc);

int I = 0;

foreach (DataRow r in dsProducts.Tables[0].Rows)
{
r[dsProducts.Tables[0].Columns.Count - 1] = I;
I += 1;
}

(The following functions are from the article mentioned above; I have modified it to cater for my requirement)


The “RememberOldValues()” method uses an Array list by the way of a session to remember the selected values throughout the application. Here I’m checking to see whether the “select entire grid” check box is selected, if so I will be using the dataset to get all the values or else I will be checking the current data grid page to get the selected values while preserving the formerly selected values.

private void RememberOldValues()
{

ArrayList categoryIDList = new ArrayList();
int index = -1;

HtmlInputCheckBox cbHeaderAllPages = (HtmlInputCheckBox)GridView1.HeaderRow.FindControl("checkAllPages");

if (((cbHeaderAllPages != null) & (cbHeaderAllPages.Checked)))
{
DataTable dt = dsProducts.Tables[0];

foreach (DataRow di in dt.Rows)
{

index = (int)di[dt.Columns.Count - 1];

// Check in the Session
if ((Session["CHECKED_ITEMS"] != null))
{
categoryIDList = (ArrayList)Session["CHECKED_ITEMS"];
}

if (!categoryIDList.Contains(index))
{
categoryIDList.Add(index);
}

}
}
else
{

foreach (GridViewRow di in GridView1.Rows)
{


index = Int32.Parse(di.Cells[di.Cells.Count - 1].Text);

bool result = ((HtmlInputCheckBox)di.FindControl("pId")).Checked;

// Check in the Session
if ((Session["CHECKED_ITEMS"] != null))
{
categoryIDList = (ArrayList)Session["CHECKED_ITEMS"];
}

if (result)
{
//Response.Write(index.ToString());
if (!categoryIDList.Contains(index))
{
categoryIDList.Add(index);
}
}
else
{
categoryIDList.Remove(index);
}

}
}

if (((categoryIDList != null) & categoryIDList.Count > 0))
{
Session["CHECKED_ITEMS"] = categoryIDList;
}


}


By using the following function you may populate the data grid with the formerly selected rows.


private void RePopulateValues()
{
ArrayList categoryIDList = (ArrayList)Session["CHECKED_ITEMS"];

if (((categoryIDList != null)))
{

if (categoryIDList.Count > 0)
{
foreach (GridViewRow di in GridView1.Rows)
{
int index = Int32.Parse(di.Cells[di.Cells.Count - 1].Text);
if (categoryIDList.Contains(index))
{
HtmlInputCheckBox myCheckBox = (HtmlInputCheckBox)di.FindControl("pId");
myCheckBox.Checked = true;
}
}
}

}

}


The above functions can be called in the “pageindexchanging” and “sort” as shown events in order to achieve the desired results.

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
try
{
RememberOldValues();
GridView1.PageIndex = e.NewPageIndex;
getData ();
RePopulateValues();
}
catch (Exception ex)
{
lblMessage.Text = ex.Message.ToString();
}
}


protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
try
{
RememberOldValues();
GridView1.PageIndex = 0;

string strSort = "";
//get the existing sort cookie
HttpCookie objCookieExisting = Request.Cookies["SortOrder"];
if (objCookieExisting != null)
strSort = objCookieExisting.Value.ToString();

//set so it does a descending order first
if (strSort == e.SortExpression + " Desc")
strSort = e.SortExpression + " Asc";
else
strSort = e.SortExpression + " Desc";

ViewState["SortOrder"] = strSort;
//'write the sort information to a cookie
HttpCookie objCookie = new HttpCookie("SortOrder", strSort);
objCookie.Expires = DateTime.Today.AddYears(1);
Response.Cookies.Add(objCookie);

getData();
RePopulateValues();
}
catch (Exception ex)
{
lblMessage.Text = ex.Message.ToString();
}
}



I hope whoever hits this page will find this code useful. All comments, suggestions and improvements are most welcome. I like to improve through my mistakes whether it is life or coding, so please feel free to criticize my work.

Many Thanks,
Mifla