GS&F

Supposing you have 50 tables without identity column, adding identity manually in the table designer would take you so much time. Here's a script to automatically add identity to a column.

List all tables without identity column.

SELECT 

(SCHEMA_NAME(schema_id) + '.' + name) as SchemaTable

FROM sys.tables

WHERE [name] NOT IN

(

SELECT 

OBJECT_NAME(OBJECT_ID) AS TABLENAME

FROM SYS.IDENTITY_COLUMNS

)

ORDER BY SchemaTable;

GO

Add identity

DECLARE cur CURSOR FOR 

select   

a.TABLE_SCHEMA as sn, 

a.table_name as tn, 

a.column_name as cn 

from information_schema.columns as a

inner join sys.tables as b on a.table_Name = b.name

where table_schema in(schema names) and  

ordinal_position <= 1 and 

table_name NOT IN(SELECT OBJECT_NAME(OBJECT_ID) AS TABLENAME   FROM SYS.IDENTITY_COLUMNS)and 

substring(a.column_name, len(column_name)-1, 2) = 'Id' 

DECLARE @sn sysname,

@tn sysname, 

@cn sysname, 

@sql varchar(500) 

OPEN cur     

FETCH NEXT FROM cur INTO @sn, @tn, @cn 

WHILE @@FETCH_STATUS = 0     

BEGIN 

SET @sql = 'ALTER TABLE [' + @sn + '].['+ @tn +'] ADD ' + @tn +' int NOT NULL IDENTITY (1,1)'    

PRINT @sql   

exec (@sql)  

FETCH NEXT FROM cur INTO @sn, @tn, @cn     

END 

CLOSE cur     

DEALLOCATE cur

Happy coding :)

 

Adding primary key using designer in SQL would take you so much time.
Here's a script that would automatically list all the tables without a primary key and create one. You can also filter by schema.

List all tables without Primary Key 

select   

a.table_name, 

a.column_name,

a.ordinal_position

from information_schema.columns as a

inner join sys.tables as b on a.table_Name = b.name

where ordinal_position <= 1 

and (object_id not in(SELECT object_id from sys.indexes WHERE is_primary_key=1))

Add Primary key

DECLARE cur CURSOR FOR 

select   

a.TABLE_SCHEMA as sn, 

a.table_name as tn, 

a.column_name as cn 

from information_schema.columns as a

inner join sys.tables as b on a.table_Name = b.name

where table_schema in(schema names) and  

ordinal_position <= 1 and 

(object_id not in(SELECT object_id from sys.indexes WHERE is_primary_key=1)) and  

substring(a.column_name, len(column_name)-1, 2) = 'Id' 

DECLARE @sn sysname,

@tn sysname, 

@cn sysname, 

@sql varchar(500) 

OPEN cur     

FETCH NEXT FROM cur INTO @sn, @tn, @cn 

WHILE @@FETCH_STATUS = 0     

BEGIN 

SET @sql = 'ALTER TABLE [' + @sn + '].['+ @tn +']  ADD  CONSTRAINT [PK_' +@tn +'_'+ @cn+ '] PRIMARY KEY CLUSTERED

(['+ @cn+'] ASC) ON [PRIMARY]'    

PRINT @sql   

exec (@sql)  

FETCH NEXT FROM cur INTO @sn, @tn, @cn     

END 

CLOSE cur     

DEALLOCATE cur

 

Happy coding ;)

To resolve this, you need to change the target framework of the Application Pool.

• Select the name of your application from the list.
• Double click to Edit.
• From the dropdownlist of the .Net Framework version, select .Net Framework v4.0.30319
• Click OK
• Refresh your site

When you are modifying table columns in SQL example changing data type or adding a new column, after you click save you are prompted with this error: "The changes you have made require the following tables to be dropped and re-created" from SQL Server."

To solve this issue you must do the following:

• Go to Tools, and select Options
• Select designer in the pop-up window
• Uncheck Prevent saving changes that requires table re-creation
• Click OK

This code snippet will write to registry on  a client side.
 (Only works in IE!)

 

 function writeInRegistry(path, sRegEntry, sRegValue) {

        var regpath = path + sRegEntry;

        var oShell = new ActiveXObject("WScript.Shell");

        oShell.RegWrite(regpath, sRegValue, "REG_SZ");

    }

Notes:
1. On the client side, please make sure that the UAC(User Account Control Settings is set to Never Notify)

• Internet Options
• Security
• Custom Level
• Look for ActiveX controls and plug-ins
• Mark enable option under Initialize and script ActiveX Controls not marked as safe for scripting(not secure)

• Mark enable option under Script ActiveX Controls marked safe for scripting.

• Click  Ok

In your MVC View

@model IEnumerable<ProjName.Models.ModelClass>
@{
 
    if(Model!=null)
    {
        int count= Model.Count();
            <table >
                <tr>
                    <td >
                        <div id="divId" class="scrollable">      
                            <table >
                                @for (int i = 0; i< count; i++ )
                                {
                                     string cbName = @Model.ElementAt(i).UniqueId.ToString();
                                    <tr>              
                                        <td align="left" >
                                             <div style=" font-weight:bold">
                                                 @Html.CheckBox(cbName , Model.ElementAt(i).IsChecked, new { @class="cbclass"}) @Model.ElementAt(i).Description
                                             </div>                
                                        </td>                      
                                    </tr>
                                }          
                            </table>
                        </div>
                    </td>
                </tr>  
            </table>
    }
}

 JS 

 $("#btnGetChecked").click(function () {

       var values = $('input:checkbox:checked.cbclass').map(function () {

            var cbname = this.name;

            return cbname;

        }).get();

});

Here's how I generate CSV File from Stored Procedure

Public Sub GetDataFromStored()
        Try
            Dim cmd As SqlCommand = New SqlCommand
            Dim strpath As String = Application.StartupPath + "\Result.txt"
  
            con.ConnectionString = strCon
            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "StoredProcedureName"

            pagingAdapter = New SqlDataAdapter(cmd)
            pagingDS = New DataSet()

            con.Open()
            pagingAdapter.Fill(pagingDS, scrollVal, pagingDS.Tables.Count, "StoredProcedureName")
            con.Close()

            Dim resTable As DataTable = pagingDS.Tables("StoredProcedureName")

            WriteToCSV(resTable, strpath)
          
        Catch ex As Exception

        End Try
  End Sub

#Region "CSV"

    Sub WriteToCSV(dt As DataTable, path As String)

        Dim str As New StringBuilder

        For Each dr As DataRow In dt.Rows
            For Each field As Object In dr.ItemArray
                str.Append(field.ToString & ",")
            Next
            str.Replace(",", vbNewLine, str.Length - 1, 1)
        Next

        Try
            My.Computer.FileSystem.WriteAllText(path, str.ToString, False)
        Catch ex As Exception
            MessageBox.Show("Write Error")
        End Try

    End Sub

#End Region

Here's how I generate text file from a stored procedure.

Public Sub GetDataFromStored()
        Try
            Dim cmd As SqlCommand = New SqlCommand
            Dim strpath As String = Application.StartupPath + "\Result.txt"
 
            con.ConnectionString = strCon
            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "StoredProcedureName"

            pagingAdapter = New SqlDataAdapter(cmd)
            pagingDS = New DataSet()

            con.Open()
            pagingAdapter.Fill(pagingDS, scrollVal, pagingDS.Tables.Count, "StoredProcedureName")
            con.Close()

            Dim resTable As DataTable = pagingDS.Tables("StoredProcedureName")

            WriteToText(resTable, strpath)
         
        Catch ex As Exception

        End Try
  End Sub


#Region "ToText"
    Private Shared Sub WriteToText(dt As DataTable, outputFilePath As String)
        Dim maxLengths As Integer() = New Integer(dt.Columns.Count - 1) {}

        For i As Integer = 0 To dt.Columns.Count - 1
            maxLengths(i) = dt.Columns(i).ColumnName.Length

            For Each row As DataRow In dt.Rows
                If Not row.IsNull(i) Then
                    Dim length As Integer = row(i).ToString().Length

                    If length > maxLengths(i) Then
                        maxLengths(i) = length
                    End If
                End If
            Next
        Next

        Using sw As New StreamWriter(outputFilePath, False)
            For i As Integer = 0 To dt.Columns.Count - 1
                sw.Write(dt.Columns(i).ColumnName.PadRight(maxLengths(i) + 2))
            Next

            sw.WriteLine()

            For Each row As DataRow In dt.Rows
                For i As Integer = 0 To dt.Columns.Count - 1
                    If Not row.IsNull(i) Then
                        sw.Write(row(i).ToString().PadRight(maxLengths(i) + 2))
                    Else
                        sw.Write(New String(" "c, maxLengths(i) + 2))
                    End If
                Next

                sw.WriteLine()
            Next

            sw.Close()
        End Using
    End Sub
#End Region

Here's my way of exporting a report generated using Crystal Reports viewed in PDF format to Excel File that is already ready for download by the user using MVC.

Controller Method

 public ActionResult ExportPDFToEXCEL(string searchOption, string searchKeyWord)
        {

            ReportClass rptCls = new ReportClass ();//new instance of the class of the crystal report
            MemoryStream oStream;
            rptCls .SetDatabaseLogon("username", "password");

         
            rptCls .SetParameterValue(0, searchOption);
            rptCls .SetParameterValue(1, searchKeyWord);
             
            oStream =(MemoryStream)rptCls .ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
            rptCls .Close();
            rptCls .Dispose();
            var arr = oStream.ToArray();
            oStream.Close();
            oStream.Dispose();
            string contentType = "application/vnd.ms-excel";
            return new FileContentResult(arr, contentType);


        }

JS

$("#btnConvertReport").click(function () {
        var searchOption = $("#searchOption option:selected").text();
        var keyword = $("#keyOption option:selected").text();

                $("#IFrameReport").attr('src', '../Report/ExportPDFToEXCEL?searchOption=' + searchOption + "&searchKeyWord=" + keyword);
         

    });

Happy coding!

Follow the following steps on how to install IIS(Internet Information Services) 7.0 in Windows 7 since it is not installed by default when you install Windows.

One of the issues encountered during publishing of MVC Applications in IIS is the registration of ASP.Net 4.0.

Here's how it is done using Visual Studio Command Prompt.

1. In Start Menu, select All Programs.
2. Select Microsoft Visual Studio 2010, then Visual Studio Tools.
3. Right click on Visual Studio Command Prompt and select Run as Administrator.


4. Execute this command, aspnet_regiis -i.



Hope this helps.

This would be my first blog that is related to my career as a Web Developer. This serves also as a documentation, since I'm not good enough in organizing my files and too young enough to easily forget things.

Assembly Information