CREATING A SQL SERVER DATABASE PROJECT IN VISUAL STUDIO 2012

A link from Michael Lang with description for how to create a database project in VS 2012:

http://candordeveloper.com/2013/01/08/creating-a-sql-server-database-project-in-visual-studio-2012/

 

 

Advertisements

Fill Form Hidden Field with Querystring Paramater

I have need to populate a hidden field on a Form with a value from incoming querystring with javascript.
I used the script I found from this thread to get the job done.
http://www.webmasterworld.com/javascript/4313611.htm

 
function getquerystring() {
    var url = location.href;
    if (url.split('?')[1] != undefined) {
        var qs = url.split('?')[1];
        String.prototype.splitquerystring = function () {
            return this.split(/[=&]/);
        }
        document.getElementById('promocode').value = qs.splitquerystring()[1];

//        for (var c = 1; c < qs.splitquerystring().length; c += 2) {

//            document.getElementById('inp' + c).value = qs.splitquerystring()[c]; 
//        } 

    }
}
window.addEventListener ? window.addEventListener('load', getquerystring, false) : window.attachEvent('onload', getquerystring); 
  



Add Auto Number (serial number) For an Existing Datatable

It will be a lot easier to do it either from front end if you use datacontrol or from back end in T-SQL query with a Row_number function.

If both options are not your choice, you can work with your datatable object directly through datatable.Load method.

Here is the code snippet to add autoincremental number column to an existing datatable:

 private static DataTable AddAutoIncrementColumn() 
        { 

            DataColumn myDataColumn = new DataColumn(); 
            myDataColumn.AllowDBNull = false; 
            myDataColumn.AutoIncrement = true; 
            myDataColumn.AutoIncrementSeed = 1; 
            myDataColumn.AutoIncrementStep = 1; 
            myDataColumn.ColumnName = "autoID"; 
            myDataColumn.DataType = System.Type.GetType("System.Int32"); 
            myDataColumn.Unique = true; 

            //Create a new datatable 
            DataTable mydt = new DataTable(); 

            //Add this AutoIncrement Column to a new datatable 
            mydt.Columns.Add(myDataColumn);            

            return mydt; 

        } 

protected void Page_Load(object sender, EventArgs e) 
        { 

            DataTable myDataTable = new DataTable(); 
            myDataTable = AddAutoIncrementColumn(); 

            //Read your existing datatable into a datatableReader 
            DataTableReader reader = new DataTableReader(GetTable()); 
            myDataTable.Load(reader); 

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

        }

http://msdn.microsoft.com/en-us/library/7x8ccbsb.aspx

Another way is to add a column and loop through the datatable to add the counter:
(From this thread: http://forums.asp.net/t/1712346.aspx/1?add+serial+number+in+existing+datatable )
 

dt.Columns.Add(“S.No”, typeof(int));
    for (int count = 0; count < dt.Rows.Count; count++)
         {
             dt.Rows[count][“S.No”] = count+1;
          }

 

Dynamic sort for a GridView

Here is a sample to pass column variables to control SORT ORDER:

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”  
            DataSourceID=”SqlDataSource1″> 
            <Columns> 
                <asp:BoundField DataField=”Item” HeaderText=”Item” SortExpression=”Item” /> 
                <asp:BoundField DataField=”Category” HeaderText=”Category”  
                    SortExpression=”Category” /> 
                <asp:BoundField DataField=”id” HeaderText=”id” SortExpression=”id” /> 
            </Columns> 
        </asp:GridView> 
        <asp:SqlDataSource ID=”SqlDataSource1″ runat=”server”  
            ConnectionString=”<%$ ConnectionStrings:mytest1ConnectionString %>” > 
           
        </asp:SqlDataSource> 
        <asp:Button ID=”Button1″ runat=”server” onclick=”Button1_Click” Text=”Sort” /> 
         <asp:Button ID=”Button2″ runat=”server” onclick=”Button2_Click” Text=”ReverseSort” /> 
 
 
protected void Page_Load(object sender, EventArgs e) 
        { 
            if (!IsPostBack) 
            { 
                SqlDataSource1.SelectCommand = “SELECT id, Item, Category FROM Items order by id desc”; 
                GridView1.DataBind(); 
            } 
        } 
 
        protected void Button1_Click(object sender, EventArgs e) 
        { 
            string sortBySelected = “Item”;  
            string sortBySelected2 = “Category”; 
           
            SqlDataSource1.SelectCommand = “SELECT id,Item, Category FROM Items ORDER BY CASE WHEN @sortBySelected = ‘Item’ THEN Item END, CASE WHEN @sortBySelected2 = ‘Category’ THEN Category END”; 
            SqlDataSource1.SelectParameters.Clear(); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected”, sortBySelected); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected2”, sortBySelected2); 
                       
            GridView1.DataBind(); 
        } 
 
        protected void Button2_Click(object sender, EventArgs e) 
        { 
            string sortBySelected = “Item”; 
            string sortBySelected2 = “Category”; 
 
            SqlDataSource1.SelectCommand = “SELECT id,Item, Category FROM Items ORDER BY CASE WHEN @sortBySelected = ‘Item’ THEN Item END DESC, CASE WHEN @sortBySelected2 = ‘Category’ THEN Category END DESC”; 
            SqlDataSource1.SelectParameters.Clear(); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected”, sortBySelected); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected2”, sortBySelected2); 
 
        } 
    }


Turn off a Child Portal in DNN (DotNetNuke)

Sometimes you may have a need to turn off a child portal for sometime without using the expiration date host setting.

There is an easy way to do it with IIS’s URLRewrite module.

Here is an example code in web.config file:

<system.webServer>

….

<rewrite>

<rules>

<rule name=”RedirectChildPortal” stopProcessing=”true”>

<match url=”^myChildPortal/$” />

<conditions>

<add input=”{REQUEST_FILENAME}” matchType=”IsFile” negate=”true” />

</conditions>

<action type=”Redirect” url=”SomePage.htm” />

</rule>

</rules>

</rewrite>

</system.webServer>

<system.web>

…..


How to Determine What Fiscal Year of Today Datein ASP.NET?

Here is a code snippet I posted early:

protected void Page_Load(object sender, EventArgs e)

{

int fYear;

if (DateTime.Today.Month < 7)

fYear = DateTime.Today.Year;

else

fYear = DateTime.Today.Year + 1;

Response.Write( “Current fisical year:”+fYear.ToString().Remove(0,2));

}


Format string with GridView

One user at ASP.NEt forum asked a question for how to format a 15 digit number(string) with two hyphen (-) inside at position 10 and 15.

It can be don easily in SQL:

SELECT  LEFT(@From,9) +’-‘+SUBSTRING(@From,10,5)+’-‘+RIGHT(@From,1)

The same can be done at the front end with string manipulation.

For the data column in GridView, we need to convert the BoundField to TemplateField first and apply the string.Insert to Eval at the designated position twice.

Text='<%# Eval(“rawCol”).ToString().Insert(9,”-“).Insert(15,”-“) %>’

We can see this in action below:

<asp:TemplateField HeaderText=”rawCol” SortExpression=”rawCol”>

<EditItemTemplate>

<asp:TextBox ID=”TextBox1″ runat=”server” Text='<%# Bind(“rawCol”) %>’></asp:TextBox>

</EditItemTemplate>

<ItemTemplate>

<asp:Label ID=”Label1″ runat=”server” Text='<%# Eval(“rawCol”).ToString().Insert(9,”-“).Insert(15,”-“) %>’></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField=”col” HeaderText=”col” ReadOnly=”True”

SortExpression=”col” />