Solomon Tech Blog

C# file crashed in Visual Studio not able to recover.

2021-04-01T13:40:00.001+05:30

Hi Please find the Solution in the URL given below.

https://www.samnoble.co.uk/2014/11/30/visual-studio-crashes-and-a-corrupted-cs-file/

For me Solution 4 has worked.

Use LUIS (Language Understanding Intelligent Service) in ChatBot

2020-01-21T15:42:00.001+05:30

Find table size in SQL Server

2018-10-03T18:49:00.002+05:30

Hi There,

Worried about how to find which table contains more records, Which table consuming more space in your database.

Just simply run the Query given below. Your problem solved.

SELECT

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

t.Name

Hope it will help you!. If yes please give your comment.

Thanks
Solomon S.

Remove .aspx extension from ASP.Net application page on Runtime

2018-10-03T18:45:00.000+05:30

Hey There,

It's being a long time I havn't write any thing to this blog. But today I have found very nice trick to avoid .aspx extension from the URL.

First you have to install a Package (Microsoft.AspNet.FriendlyURL) from Nuget

Install-Package Microsoft.AspNet.FriendlyUrls -Version 1.0.2

Then Simple add a Global.asax page in application and write a simple code in Application_Start section.

protected void Application_Start(object sender, EventArgs e)
{
RouteConfig.RegisterRoutes(System.Web.Routing.RouteTable.Routes);
}

Hope it will be helpful for you. Don'r forgot to comment if you face any problem or it resolved your problem.

Thanks
Solomon Sarkar

Microsoft Report Viewer Print in Other browser as well as in different OS

2017-12-16T12:59:00.000+05:30

As report viewer need ActiveX control to print the report which was possible in Windows IE only.

But after a long search on google. I have found 2 Solution which will allow you to print in Chrome and FireFox as well.

Solution 1: (Render content in HTML and call Print Command through JavaScript)

Put this code in Head of the page.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js" type="text/javascript"></script>

<script type="text/javascript">

// Linking the print function to the print button

$(document).ready(function () {

$('#printreport').click(function () {

//alert('Hi');

printReport('ReportViewer1');

});

function printReport(report_ID) {

var rv1 = $('#' + report_ID);

var iDoc = rv1.parents('html');

// Reading the report styles

var styles = iDoc.find("head style[id$='ReportControl_styles']").html();

if ((styles == undefined) || (styles == '')) {

iDoc.find('head script').each(function () {

var cnt = $(this).html();

var p1 = cnt.indexOf('ReportStyles":"');

if (p1 > 0) {

p1 += 15;

var p2 = cnt.indexOf('"', p1);

styles = cnt.substr(p1, p2 - p1);

}

});

}

if (styles == '') { alert("Cannot generate styles, Displaying without styles.."); }

styles = '<style type="text/css">' + styles + "</style>";

// Reading the report html

var table = rv1.find("div[id$='_oReportDiv']");

if (table == undefined) {

alert("Report source not found.");

return;

}

// Generating a copy of the report in a new window

var docType = '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/loose.dtd">';

var docCnt = styles + table.parent().html();

var docHead = '<style>body{margin:0;padding:0; line-height: 0.5em;} @page { size: A4; margin: 0;} @media print { html, body { width: 210mm; height: 297mm; }} @media print { footer { page-break-after: always; }} td {padding:0;}</style>';

var winAttr = "location=yes, statusbar=no, directories=no, menubar=no, titlebar=no, toolbar=no, dependent=no, width=900px, height=700px, resizable=yes, screenX=100, screenY=100, personalbar=no, scrollbars=yes";;

var newWin = window.open("", "_blank", winAttr);

writeDoc = newWin.document;

writeDoc.open();

writeDoc.write(docType + '<html>' + docHead + '<body onload="window.print();">' + docCnt + '</body></html>');

writeDoc.close();

// The print event will fire as soon as the window loads

newWin.focus();

// uncomment to autoclose the preview window when printing is confirmed or canceled.

// newWin.close();

};

});

</script>

Control on Page

<input id="printreport" type="button" value="Print" />

<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" InteractiveDeviceInfos="(Collection)" ShowExportControls="true" WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="600px" Width="820px" ShowPrintButton="true">

<LocalReport ReportPath="report.rdlc"> </LocalReport>

</rsweb:ReportViewer>

Solution 2 (Render report content in PDF and call print function.)

Required itextsharp.dll version 5.0

<asp:ImageButton ID="btnPrint" runat="server" OnClick="btnPrint_Click" ImageUrl="https://cdn3.iconfinder.com/data/icons/black-easy/512/535129-print_512x512.png" Height="50px" />

<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" InteractiveDeviceInfos="(Collection)" ShowExportControls="true" WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="600px" Width="820px" ShowPrintButton="true">

<LocalReport ReportPath="report.rdlc"> </LocalReport>

</rsweb:ReportViewer>

using System.IO;

using iTextSharp.text.pdf;

using iTextSharp.text;

using Microsoft.Reporting.WebForms;

protected void btnPrint_Click(object sender, ImageClickEventArgs e)

{

Warning[] warnings;

string[] streamids;

string mimeType;

string encoding;

string extension;

byte[] bytes = ReportViewer1.LocalReport.Render("PDF", null, out mimeType,

out encoding, out extension, out streamids, out warnings);

FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("output.pdf"), FileMode.Create);

fs.Write(bytes, 0, bytes.Length);

fs.Close();

//Open exsisting pdf

Document document = new Document(PageSize.A4);

PdfReader reader = new PdfReader(HttpContext.Current.Server.MapPath("output.pdf"));

//Getting a instance of new pdf wrtiter

PdfWriter writer = PdfWriter.GetInstance(document, new FileStream(

HttpContext.Current.Server.MapPath("Print.pdf"), FileMode.Create));

document.Open();

PdfContentByte cb = writer.DirectContent;

int i = 0;

int p = 0;

int n = reader.NumberOfPages;

Rectangle psize = reader.GetPageSize(1);

float width = psize.Width;

float height = psize.Height;

//Add Page to new document

while (i < n)

{

document.NewPage();

p++; i++;

PdfImportedPage page1 = writer.GetImportedPage(reader, i);

cb.AddTemplate(page1, 0, 0);

}

//Attach javascript to the document

PdfAction jAction = PdfAction.JavaScript("this.print(true);\r", writer);

writer.AddJavaScript(jAction);

document.Close();

//Attach pdf to the iframe

frmPrint.Attributes["src"] = "Print.pdf";

}

This solution has been checked in Windows, Mac & Ubuntu with Chrome & FireFox Browser

Error : Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

2017-12-11T11:47:00.001+05:30

While we are trying to upload Excel Sheet in the database through ASP.Net application, we normally found the error

Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

And we google for it and download 2007 Office System Driver: Data Connectivity Components and install it on the server but the problem never solved because its 32 Bits application you need to download 64 Bit Driver.

URL is https://www.microsoft.com/en-us/download/details.aspx?id=13255

And the problem solved.

How to change schema of all tables, views and stored procedures in MSSQL

2016-07-29T12:01:00.001+05:30

Yes, it is possible.
To change the schema of a database object you need to run the following SQL script:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName

Where ObjectName can be the name of a table, a view or a stored procedure. The problem seems to be getting the list of all database objects with a given shcema name. Thankfully, there is a system table named sys.Objects that stores all database objects. The following query will generate all needed SQL scripts to complete this task:

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))

Where type 'U' denotes user tables, 'V' denotes views and 'P' denotes stored procedures.

Now you can run all these generated queries to complete the transfer operation.

Reference : http://stackoverflow.com/questions/17571233/how-to-change-schema-of-all-tables-views-and-stored-procedures-in-mssql

Setup Database Mail in SQL Server 2012 Express

2015-07-08T17:02:00.001+05:30

SQL Server 2012 Express still supports Database Mail (DB Mail), but it’s well hidden.
Step 1
One should enable Database mail on the server, before setting up the database mail profile and accounts, Either can be done by using Transact SQL to enable Database Mail. Run the following statement in the SQl Server Management Studio.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go

Step 2
One can enable the Configuration Component Database account by using the sysmail_add_account procedure.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'TestMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'tanmaya@mydomain.com',
@display_name = 'MyAccount',
@username='tanmaya@mydomain.com',
@password='1qwe432',
@mailserver_name = 'mail.mydomain.com'

Step 3
Now one should create a Mail profile.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'TestMailProfile',
@description = 'Profile needed for database mail'

Step 4
Next will be the sysmail_add_profileaccount procedure, to include the Database Mail account which is created in step 2, along with the Database Mail profile in step 3.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'TestMailProfile',
@account_name = 'TestMailAccount',
@sequence_number = 1

Step 5
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'TestMailProfile',
@principal_name = 'public',
@is_default = 1 ;

UPDATE msdb.dbo.sysmail_server SET enable_ssl=1

Step 6
After all these settings done, try to send a test mail from MSSQL Server.
You’d execute the below query.

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' Test DB Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='tanmaya@mydomain.com',
@subject = 'Test',
@body = @body1,
@body_format = 'HTML' ;

Step 7

You can review the logs linked to Database Mail.

You’d execute the below query.

SELECT * FROM msdb.dbo.sysmail_event_log

Listing all tables in a MSSQL Database and their row counts and space uses

2015-03-04T11:34:00.001+05:30

Using sp_spaceused

sp_spaceused without parameters displays the disk space reserved and used by the whole database. However by specifying a table name as the first parameter it will display the number of rows, disk space used and reserved by a table. We can use this with the sp_MSForEachTable procedure mentioned above to get results for every table. An advantage to this approach is that it also shows the space used each table (data and index).

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),
       unused VARCHAR(18))

EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows,
           CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM       #RowCountsAndSizes
ORDER BY   NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes

Hope the content is helpful.

Thanks

Generate Random Password in SQL Server Store Procedure

2015-01-09T16:22:00.004+05:30

Sometimes there is a need to reset a password using a temporary password or generate a random password for a new user. This simple store procedure is creating it.

create proc [dbo].uspRandChars
    @len int,
    @min tinyint = 48,
    @range tinyint = 74,
    @exclude varchar(50) = '0:;<=>?@O[]`^\/',
    @output varchar(50) output
as 
    declare @char char
    set @output = ''
 
    while @len > 0 begin
       select @char = char(round(rand() * @range + @min, 0))
       if charindex(@char, @exclude) = 0 begin
           set @output += @char
           set @len = @len - 1
       end
    end
;
go


LEN - specifies the length of the result (required)
MIN - sets the starting ASCII code (optional: defaults to "48": which is the number zero)
RANGE - determines the range of how many ASCII characters to include (optional: defaults to "74"  (48 + 74 = 122) where 122 is a lowercase "z")
EXCLUDE - a string of characters to exclude from the final output (optional: defaults include zero, capital "O", and these punctuation marks :;<=>?@[]`^\/)

To use the stored procedure issue commands such as the following.

declare @newpwd varchar(20)


-- all values between ASCII code 48 - 122 excluding defaults
exec [dbo].uspRandChars @len=8, @output=@newpwd out
select @newpwd


-- all lower case letters excluding o and l
exec [dbo].uspRandChars @len=10, @min=97, @range=25, @exclude='ol', @output=@newpwd out
select @newpwd


-- all upper case letters excluding O
exec [dbo].uspRandChars @len=12, @min=65, @range=25, @exclude='O', @output=@newpwd out
select @newpwd


-- all numbers between 0 and 9
exec [dbo].uspRandChars @len=14, @min=48, @range=9, @exclude='', @output=@newpwd out
select @newpwd

Here is sample output from the above commands:





Reference : http://www.mssqltips.com/sqlservertip/2534/sql-server-stored-procedure-to-generate-random-passwords/

How to make a copy of a existing database into new database

2014-12-26T17:04:00.002+05:30

This feature is not available in Express version.

Right Click on database -> Task -> Copy Database.

It will open a wizard you have to follow, where you will have to select the source database and enter a new database name to copy whole database objects into the newer one.

How to round to the nearest whole number in C# ?

2014-08-30T15:31:00.002+05:30

I have Googled for it and found some interesting answers:

My problem was solved by simple using on Math.Ceiling

var wholeNumber = (int)Math.Ceiling(fractionalNumber);

And other interesting answers are :

Math.Ceiling

always rounds up (towards the ceiling)

Math.Floor

always rounds down (towards to floor)

References :

http://stackoverflow.com/questions/8666069/decimal-double-to-integer-round-up-not-just-to-nearest

http://stackoverflow.com/questions/8844674/how-to-round-to-the-nearest-whole-number-in-c-sharp

http://stackoverflow.com/questions/977796/why-does-math-round2-5-return-2-instead-of-3-in-c

Microsoft Report Viewer Print Button not Working / Visible in IE 10 & 11

2014-08-22T12:44:00.001+05:30

I had created report in on of my client website using "Microsoft Report Viewer Control" but a very known problem with Report Viewer is Printing is not possible in other browser instead of Internet Explorer.

Today I have faced a new problem that Print Button is not visible in IE 11 when Client updated their Windows 8 to Windows 8.1.

Then I started goggling and Finally I found that it's happens because of Active X. And I followed these steps give below.

Start Windows Internet Explorer.
On the Tools menu, click Internet Options.
On the Security tab, click Local Intranet, and then click Sites
Click Advanced.
Type the URL of your site in the Add this website to the zone box, and then click Add. (in development mode it will be http://localhost)
Click Close to close the Local Intranet dialog box, and then click OK.
On the Security tab, click Custom Level.
Click Enable for each component that is listed under ActiveX controls and plugins.
Click OK to close the Internet Options dialog box.
Restart Internet Explorer
Try printing the report.

It's working fine now !!!.

Hope it will help you. :)

Maintain Scroll Position on Postback in ASP.NET

2014-05-09T12:06:00.002+05:30

After a long time googling I have got a good solutions to maintain scroll position. See the post given below.

This article shows how to allows pages to automatically maintain the current scroll position across postbacks.

The MaintainScrollPositionOnPostback page directive attribute allows to do that.

This feature is useful for large pages where scrolling is necessary to view input controls down further on the page.

There are three ways of applying the property to a web page.

You can set it programmatically
Page.MaintainScrollPositionOnPostBack = true;
In the page declaration
<%@ Page MaintainScrollPositionOnPostback="true" %>
Or in the web.configs <system.web> section.
<pages maintainScrollPositionOnPostBack="true" />

I have used 3 point & It's working absolutely fine.

This feature is an absolute must-have on large web pages built for postback scenarios.

A simple but very useful feature.

Smartnavigation = true implemented the same feature in 1.1 framework SmartNavigation only had "issues" and it only worked in IE but the new MaintainScrollPositionOnPostback apparently works in most common browsers.

Reference : http://www.c-sharpcorner.com/uploadfile/yogesh12/postback12292006050702am/postback.aspx

Generate a unique alphanumeric password using SQL

2014-03-31T15:13:00.002+05:30

DECLARE @PwdLen SMALLINT,
@Chr TINYINT,
@Password VARCHAR(20)

-- Seed
SET @Chr = RAND(DATEPART(ms, GETDATE())) * 0
SET @Password = ''

SET @PwdLen = 1
WHILE @PwdLen < 8
BEGIN
SET @Chr = RAND() * 62
SET @Password = @Password + CHAR(
CASE WHEN @Chr < 10 THEN @Chr + 48
WHEN @Chr BETWEEN 10 AND 35 THEN @Chr + 55
ELSE @Chr + 61
END)

SET @PwdLen = @PwdLen + 1
END

SELECT @Password [Password]

SQL statement which will give me all the days of a given month as individual rows

2014-03-19T14:40:00.002+05:30

Hi friends,

I have searched a lot in Google and finally I found that its too easy to get the result. Check the query given below.

DECLARE @month INT
DECLARE @year INT
SET @month=2
SET @year = 2016

SELECT CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number 'date'
FROM master..spt_values WHERE type = 'P'
AND
(CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) )

Last modified Tables & Stored Procedures details in Microsoft SQL Server

2014-02-22T19:41:00.000+05:30

Let's find out the last modified Tables & Stored Procedures details in MSSQL by writting a simple Query.

For User Tables

SELECT * FROM sys.objects WHERE type='U' ORDER BY modify_date DESC

For Stored Procedures

SELECT * FROM sys.objects WHERE type='P' ORDER BY modify_date DESC

These Queries fetching records from system object table order by last modified date & time.

Enable remote connections for SQL Server Express 2012

2014-02-21T10:31:00.002+05:30

Run SQL Server Configuration Manager.
Go to SQL Server Network Configuration > Protocols for SQLEXPRESS.
Make sure TCP/IP is enabled.

So far, so good, and entirely expected. But then:

Right-click on TCP/IP and select Properties.
Verify that, under IP2, the IP Address is set to the computer's IP address on the local subnet.
Scroll down to IPAll.
Make sure that TCP Dynamic Ports is blank. (Mine was set to some 5-digit port number.)
Make sure that TCP Port is set to 1433. (Mine was blank.)

(Also, if you follow these steps, it's not necessary to enable SQL Server Browser, and you only need to allow port 1433, not 1434.)

References : http://stackoverflow.com/questions/11278114/enable-remote-connections-for-sql-server-express-2012

http://support.webecs.com/KB/a868/how-do-i-configure-sql-server-express-to-allow-remote.aspx

Files on External/Flash Drive Changed to Shortcuts Virus

2014-01-27T13:30:00.002+05:30

Issue

I caught a virus on my flash drive at work and it appears to have changed all my file names to short cuts. I believe I’ve cleaned the virus but how do i get my files back so that I can view them?

Solution

If you did not format your flash drive, then check whether the files are not in hidden mode.
Click on “Start” –>Run–>type cmd and click on OK.
Check your external Drive letter in My Computer
Here I assume your external drive as G:
Enter this command.attrib -h -r -s /s /d g:*.*
Delete the unnecessary shortcuts.

Note: Replace the letter g with your flash drive letter.

Still problem? Write down in comments section.

Want 5 digit string which is incremented automatic (A0001 - Z9999)

2013-12-10T14:56:00.002+05:30

The first thing you need is a table to hold your generated keys:

CREATE TABLE [dbo].[IdGenerator](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Pk] [varchar](5) NOT NULL
)
 
-- Insert the first key
INSERT INTO IdGenerator (Pk) VALUES ('A0001')

It contains 2 columns. Id (the primary key, used to order your keys) and the key you want!

Then you need this script to generate a new Key base on the previous one:

-- Declare some variables we need
DECLARE @PK varchar(5)
DECLARE @Character char(1)
DECLARE @Number int
 
-- Get the last generated key (I'll call it @PK)
SET @PK = (SELECT TOP 1 PK FROM IdGenerator Order by Id desc)
-- Extract the character (A,B,C ...)
SET @Character = (SELECT SUBSTRING (@PK ,1, 1))
-- Extract the number (4 last characters. "0001", "0002"....)
SET @Number = (SELECT SUBSTRING (@PK ,2, 4))
 
-- Increase the number by 1
SET @Number = @Number + 1
 
-- Check if the number has reached the top (9999)
IF @Number > 9999
BEGIN
   -- If it has, set it to 1
   SET @Number = 1
   -- And find the next character in the alphabet (A->B, B->C...)
   SET @Character = char(ascii(@Character)+1)
END
 
-- Pad your number (1->0001, 2->0002...) and set you newly generated key
SET @PK = @Character + REPLACE(STR(@Number, 4), SPACE(1), '0')
 
-- Insert it to the IdGenerator table so we can use it for the next key
INSERT INTO IdGenerator (PK) VALUES (@PK)
 
-- And heres your new unique key :)
SELECT @PK

For this to work, you will have to add the first value (A0001) manually. And you
You can put this code in a stored procedure.

Reference : http://www.codeproject.com/Questions/441365/want-7-digit-string-which-is-incremented-automatic

How to know last updated Tables / Stored Procedures in Microsoft SQL Server 2008

2013-11-29T13:08:00.000+05:30

Very simple query will return the result. How to know the last updated Tables / Stored Procedures in Microsoft SQL Server 2008.

Here is the query given below.

For Stored Procedures

SELECT * FROM sys.objects WHERE type='P' ORDER BY modify_date DESC

And for Tables

SELECT * FROM sys.objects WHERE type='U' ORDER BY modify_date DESC

Hope this will help !!!!!

Thanks
Solomon S.
(http://www.solomonsarkar.com)

Sending a DataTable to a Stored Procedure

2013-03-09T15:44:00.000+05:30

I found this beautiful article from CodeProject written by _Amy

Reference Link : http://www.codeproject.com/Articles/412802/Sending-a-DataTable-to-a-Stored-Procedure

Introduction

This is a small article explaining how to send an entire data-table to a Stored Procedure in the database server as an input parameter.

Background

It uses a logic which allows us to do add, modify, and delete operations on a set of records in the database using a simple form. The details also need to be shown after adding, updating, and deleting of records in a table. And the data-table should be given to a stored procedure to perform the operations. This feature will work with SQL Server 2008.

Advantages

For each event on the page the form will not be interacting with the database. This reduces server round trips.
All necessary operations such as:

Inserting a new record
Updating an existing record
Deleting an existing record

are performed on client side only. They will be persisted in the database only after getting confirmation from the user. So, the performance of the application will improve.

After performing a certain action (such as Insert, Update, or Delete) on the grid-view the data will be saved in “Session”. And whenever the data is required, it is be fetched from the Session.
We send the entire data-table from the session to the database for manipulation, and with the use of a Stored Procedure we can do operations such as insert, delete, and update.
It is easier to perform all operations on a set of rows at a time.
The user can deal with thousands of data-rows at the same time without a connection.
Extends the functionality of programming in a database engine.

Using the code

When a page is getting loaded the first time, data from the database is loaded to a data-table. In that data-table a column is added (named Operation). Whenever the user performs an action on the grid-view, the indicator bit is added to that column for that record (Insert 0, Update 1, and Delete 2). If the user clicks on a delete link on a particular row then the operation column of that row is updated to 2, and in the RowDataBound event of the grid-view we hide the records whose operation bit is 2. The temporary table is stored in session state. Before performing any operation, the session table will be called and the action will be performed on that table and again that table will be stored in the session. After performing all the necessary operations, when the user clicks on the Save button, the functionfnMangeOperations is called which will filter all the rows on which the operation was performed and the Save button will send only those details in which the operation has to be performed (you can find the functionfnMangeOperations in the source code from the attached files).

Step 1: Declaring global variables:

private clsEmpDetails _objEmpDetails;
private DataTable _dtEmpDetails;
private DataTable _dtEmpDetailsVals;

Step 2: Initializing the data members when page loads.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        _objEmpDetails = new clsEmpDetails();
        _dtEmpDetails = _objEmpDetails.fnGetDetails();
        if(_dtEmpDetails != null){
           //Adding a new column to the table which will store the operation details
           //For new insert it will store 1
           //For updating the existing record it will store 0
           _dtEmpDetails.Columns.Add("Operation", typeof(string));
           Session["EmpDetails"] = _dtEmpDetails;
           gvEmpDetails.DataSource = _dtEmpDetails;
           gvEmpDetails.DataBind();
        }       
    }
}

Just store the data in session and maintain the indicator for each operation. The operation will be like this:

protected void gvEmpDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName == "AddNew") 
    {
       try{
            _dtEmpDetails = Session["EmpDetails"] as DataTable;
            string strID = (TextBox)gvEmpDetails.FooterRow.Cells[0].FindControl("txtInsID")).Text;
            string strName = (TextBox)gvEmpDetails.FooterRow.Cells[1].FindControl("txtInsName")).Text;
            string strAddress = (TextBox)gvEmpDetails.FooterRow.Cells[1].FindControl("txtInsAddress")).Text;
            _dtEmpDetails.Rows.Add(strID, strName, strAddress, "0");
            Session["EmpDetails"] = _dtEmpDetails;
            fnBindEmpDetails(); //This function will bind the data to the gridview by fetching the data from session
        }
        catch(Exception ex){            
             //Handle your exception
        }
    }
}

On click of the Save button, call the following function by passing your DataTable as a parameter.

public string fnStoredProc(DataTable dtDetails)
{
    string strMsg = "";
    try
    {
        fnConOpen();//Function for opening connection
        SqlCommand cmdProc = new SqlCommand("spEmpDetails", con);
        cmdProc.CommandType = CommandType.StoredProcedure;
        cmdProc.Parameters.AddWithValue("@Type", "InsertDetails");
        cmdProc.Parameters.AddWithValue("@Details", dtDetails);
        cmdProc.ExecuteNonQuery();
        strMsg = "Saved successfully.";
    }
    catch (SqlException e) {
        //strMsg = "Data not saved successfully.";
        strMsg = e.Message.ToString();
    }
    finally 
    {
        fnConClose();//Function for closing connection

    }
    return strMsg;
}

Procedure structure

First create a table type with matching columns which comes from the front-end. In this case:

/*Creating type for the procedure parameter*/
CREATE TYPE EmpType AS TABLE 
(
    ID INT, Name VARCHAR(3000), Address VARCHAR(8000), Operation SMALLINT
)

Write the procedure for the operations.

ALTER PROCEDURE spEmpDetails
@Type VARCHAR(15),
@Details EmpType READONLY
AS
BEGIN
    IF(@Type='FetchDetails')
        SELECT * FROM EmployeeDetails
    ELSE
        BEGIN
            --For deleting the details from the table
            DELETE FROM EmployeeDetails WHERE ID IN(SELECT ID FROM @Details WHERE Operation=2)
            
            --For updating the details in the table
            UPDATE e SET e.Name=d.Name, e.Address=d.Address FROM EmployeeDetails e, @Details d 
            WHERE d.ID=e.ID and d.Operation=1
            
            --For inserting the new records in the table
            INSERT INTO EmployeeDetails(ID, Name, Address) 
            SELECT ID, Name, Address FROM @Details WHERE Operation=0;
        END
    END
GO

Note: This procedure takes a datatabe as its parameter and performs insert, update, and delete operations based on the table data.

SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008

2012-09-17T11:14:00.000+05:30

Note: Please read the complete post before taking any actions.

This blog post would discuss SHRINKFILE and TRUNCATE Log File. The script mentioned in the email received from reader contains the following questionable code:

“Hi Pinal,

If you could remember, I and my manager met you at TechEd in Bangalore.

We just upgraded to SQL Server 2008. One of our jobs failed as it was using the following code.

The error was:

Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

The code was:

DBCC SHRINKFILE(TestDBLog, 1)BACKUP LOG TestDB WITH TRUNCATE_ONLYDBCC SHRINKFILE(TestDBLog, 1)GO

I have modified that code to subsequent code and it works fine. But, are there other suggestions you have at the moment?

USE [master]
GOALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAITDBCC SHRINKFILE(TestDbLog, 1)ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

Configuration of our server and system is as follows:

[Removed not relevant data]“

An email like this that suddenly pops out in early morning is alarming email. Because I am a dead, busy mind, so I had only one min to reply. I wrote down quickly the following note. (As I said, it was a single-minute email so it is not completely accurate). Here is that quick email shared with all of you.

“Hi Mr. DBA [removed the name]

Thanks for your email. I suggest you stop this practice. There are many issues included here, but I would list two major issues:

1) From the setting database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.

2) Shrinking database file or database adds fragmentation.

There are a lot of things you can do. First, start taking proper log backup using following command instead of truncating them and losing them frequently.

BACKUP LOG [TestDb] TO DISK = N'C:\Backup\TestDb.bak'GO

Remove the code of SHRINKING the file. If you are taking proper log backups, your log file usually (again usually, special cases are excluded) do not grow very big.

There are so many things to add here, but you can call me on my [phone number]. Before you call me, I suggest for accuracy you read Paul Randel‘s two posts here and here and Brent Ozar‘s Post here.

Kind Regards,
Pinal Dave”

I guess this post is very much clear to you. Please leave your comments here. As mentioned, this is a very huge subject; I have just touched a tip of the ice-berg and have tried to point to authentic knowledge.

Update: Small typo correction and small detail corrected based on feedback.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Could not load file or assembly ‘AjaxControlToolkit’ or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0×80070057 (E_INVALIDARG))

2012-08-28T12:45:00.003+05:30

If you are working with the Ajax Control Toolkit, sometimes the AjaxControlToolkit assembly will become corrupted in your Temporary ASP.NET Files folder. Here are a set of steps that might help you get back up and running. This is only relevant to developing with the built in web server, Cassini.

But first, here is the scenario.

Last Friday, my computer crashed while I was working on a project which references the AJAX Control Toolkit assembly.

After my computer restarted, I opened the project in Visual Studio 2008. I launched the default web page and I received the following error:

Could not load file or assembly ‘AjaxControlToolkit’ or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0×80070057 (E_INVALIDARG))

I tried closing and reopening the project; I even restarted my computer, but none of these efforts were successful.

I deleted and re-added the AjaxControlToolkit assembly reference, but still no dice.

Here are the steps that I found which worked for me:

Close Visual Studio
Open the Temporary ASP.NET Files folder and delete its contents.

If the OS is Windows Vista, then the folder is

%LocalAppData%\Temp\Temporary ASP.NET Files
If the OS is Windows XP, then the folder is%WINDIR%\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files

Restart Visual Studio
Open the project

That's it. Works great now.

Hope this helps.

Reference From : http://weblogs.asp.net/joshuajohnson/archive/2008/12/22/could-not-load-file-or-assembly-ajaxcontroltoolkit-or-one-of-its-dependencies-the-parameter-is-incorrect-exception-from-hresult-0-215-80070057-e-invalidarg.aspx

How to return the ID from the database on insert

2012-06-08T12:12:00.004+05:30

Here is the Example of a Insert statement which returns ID of the row got inserted.

DECLARE ID int
INSERT INTO <tablename> (<colums name>) VALUES (<values>)
SELECT ID = SCOPE_IDENTITY()

Hope it will help you.

Thanks
Solomon S.

If information is helpful then don't forget to post comments