Simple way to creating the update query for the table in SQL Server

Declare @TableName sysname = 'MemberRegistration'

Declare @result varchar(max) = ''

Declare @StartString varchar(max) = 'Update '+@TableName+' Set'

SELECT

       @result = @result + '

       [' + ColumnName + ']=@' + ColumnName +','

FROM (SELECT

       REPLACE(col.name, ' ', '_') ColumnName,

       column_id,

       (CASE

              WHEN (typ.name = 'nvarchar' OR

                     typ.name = 'varchar') THEN typ.name + '(' + CONVERT(NVARCHAR(10), (col.max_length / 2)) + '),'

              ELSE typ.name + ','

       END) AS ColumnType

FROM sys.columns col

JOIN sys.types typ

       ON col.system_type_id = typ.system_type_id

       AND col.user_type_id = typ.user_type_id

WHERE object_id = OBJECT_ID(@TableName)) t

ORDER BY column_id

SET @result = @StartString + '' + @result + ')

Where '

print @result

Read More »

Simple way to creating the insert query for the table in SQL Server

Simple way to creating the insert query for the table in SQL Server

Declare @TableName sysname = 'MemberRegistration'

Declare @result varchar(max) = ''

Declare @StartString varchar(max) = 'Insert into '+@TableName+''

SELECT

       @result = @result + '

       [' + ColumnName + '],' + +''

FROM (SELECT

       REPLACE(col.name, ' ', '_') ColumnName,

       column_id,

       (CASE

              WHEN (typ.name = 'nvarchar' OR

                     typ.name = 'varchar') THEN typ.name + '(' + CONVERT(NVARCHAR(10), (col.max_length / 2)) + '),'

              ELSE typ.name + ','

       END) AS ColumnType

FROM sys.columns col

JOIN sys.types typ

       ON col.system_type_id = typ.system_type_id

       AND col.user_type_id = typ.user_type_id

WHERE object_id = OBJECT_ID(@TableName)) t

ORDER BY column_id

SET @result = @StartString + '

(' + @result + ')

values ('

SELECT

       @result = @result + '

       @' + ColumnName + ',' + +''

FROM (SELECT

       REPLACE(col.name, ' ', '_') ColumnName,

       column_id,

       (CASE

              WHEN (typ.name = 'nvarchar' OR

                     typ.name = 'varchar') THEN typ.name + '(' + CONVERT(NVARCHAR(10), (col.max_length / 2)) + '),'

              ELSE typ.name + ','

       END) AS ColumnType

FROM sys.columns col

JOIN sys.types typ

       ON col.system_type_id = typ.system_type_id

       AND col.user_type_id = typ.user_type_id

WHERE object_id = OBJECT_ID(@TableName)) t

ORDER BY column_id

SET @result = @result + ')'

SET @result = REPLACE(@result, ',)', ')')

print @result

Read More »

How to disable the unwanted SSRS report export formats using asp.net c#

How to disable the unwanted SSRS report export formats using asp.net c#

/// <summary>

        /// Disable Unwanted Export Formats

        /// </summary>

        public void DisableUnwantedExportFormats()

        {

            FieldInfo info;

            foreach (RenderingExtension extension in this.rptname.ServerReport.ListRenderingExtensions())

            {

                if (extension.Name == "EXCEL" || extension.Name == "PDF" || extension.Name == "WORD")

                {

                    //info = extension.GetType().GetField("m_isVisible", BindingFlags.Instance | BindingFlags.NonPublic);

                    //if (info != null)

                    //{

                    //    Extension rsExtension = info.GetValue(extension) as Extension;

                    //    if (rsExtension != null)

                    //    {

                    //        rsExtension.Visible = false;

                    //    }

                    //}

                    info = extension.GetType().GetField("m_isVisible", BindingFlags.Instance | BindingFlags.NonPublic);

                    info.SetValue(extension, true);

                }

                else

                {

                    info = extension.GetType().GetField("m_isVisible", BindingFlags.Instance | BindingFlags.NonPublic);

                    info.SetValue(extension, false);

                }

            }

        }

Call this method in page load method

protected void Page_Load(object sender, EventArgs e)

        {

            try

            {

               

                if (!IsPostBack)

                {

                   

                }

                this.DisableUnwantedExportFormats();

            }

            catch (Exception ex)

            {

            }

        }

Read More »