Asp.net & Sql server fundas with Rajat Jaiswal

HTML to Word Document in .net with simple trick

2011-11-20T17:06:00.000-08:00

Dear Friends,
Recently my friend got a new task to generate a word & PDF document at runtime from FCK Editor and he was in real hurry, so he told me a sample for him. I tried with a simple which I am sharing here.
I created a resume layout with FCK editor. Please find the below screen and code.

I hope you will also enjoy.

you can download code at

Thanks & Best Regards,
Rajat Jaiswal

How to check your SQL Server Performance using new Utility Performance Dashboard Reports ?

2011-05-06T07:01:00.000-07:00

Hello friends,
I know after reading the post title you are very excited to know more, even I was excited too to know more for this topic which I like very much.
Microsoft Introduced Performance Dashboard Utility reports which help us in finding the cause of low performance of our SQL Server Database.
You can download the performance Dashboard utility reports setup from below link
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc
When you install this installer you will find a new folder is created with Performance Dashboard name in tools folder of Microsoft SQL SERVER (“C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard”)
Now when you open this folder you will get many RDL files.
Next question is how to use it so here we go.
1) Firstly run the script with name “setup.sql “ from Performance Dashboard folder (“C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard”)

2) Now open our SQL Server Management studio
3) Open object explorer
4) Right click on the server select reports option then select standard reports as shown in below fig

5) Now select Performance_dashboard _main.rdl report
6) When you select this report you will find following screen

Great it means you are able to see performance dashboard report.
The performance dashboard report gives you following result
1) First graph of report shows System CPU utilization (With the help of this you can find how much CPU utilization done by SQL)
2) Just Below the CPU utilization graph you can see current Activity (CPU consumption time, wait time) We can see detail with two parameter either by User Session or User request (When you click on the header of the grid you will get different report)
3) Below this we have miscellaneous information which will give basic information like number of database currently on server, active trace etc.
4) Waiting log just right side of CPU utilization graph which shows different wait type for SQL
5) Below that one more tabular grid there which shows historical records of SQL according to different criteria like waits, IO Statics
6) Expensive SQL report with different parameters like BY CPU, BY Duration, BY Logical Reads, BY Logical Writes etc.
Checks out there are many more feature reports.

So with these different reports we can find where we are lacking. Where is the problem?

My personal feeling is the utility is good to finding the performance counter but it will not provide any suggestion and solution to improve performance.
So why are you waiting?
Just download and enjoy the performance dashboard.
Have fun with reports in weekends: D.
Thanks & Regards
Rajat Jaiswal

Check Best Practices with SQL SERVER with Best Practices Analyzer (BPA)

2011-05-06T00:00:00.000-07:00

Hello Friends,
We always heard that we should adopt Best Practices in SQL Server and most of the time we tried our best to adopt best practices in our work but the problem is who will analyze the best practices which we did for our database ,SQL Server.
Hmm, that’s the point my dear friends but don’t worry on this part also because Microsoft introduce BPA (Best Practice Analyzer).
I tried this tool recently it is easy to use. It scans your database, SQL SERVER and provides you the best way to adopt Best Practices with your current structure.
You can download the installer of this tool from http://www.microsoft.com/downloads/en/details.aspx?FamilyId=da0531e4-e94c-4991-82fa-f0e3fbd05e63
After install this BPA utility you can easily scan your SQL SERVER, Databases with this.

The BPA will provide you report of scan. The scan report will give you idea about what is missing, what need to do?
Below are some listed options which BPA raised after scan report
1) Backup is latest or is it out dated?
2) Database having any anomalies.
3) Database integrity check required or not?
4) Check Disk IO delay problem
5) Backup should be on separate volume
6) Bulletin\Administrators in sysAdmin Role
7) Set Page verify option
8) SQL Login Password policy violation
9) Database is not in full recovery mode
Although some of the warning in the above list which we can ignore according to our business need.
And many more issues to make SQL Server database robust just check below report snap.

As per my opinion the tool is good but need some more functionality or utility to manage and apply
Best practices. So why you people waiting just download, install and use.
Make your database robust with best practices.

Enjoy Best Practices.

Thanks & Regards
Rajat Jaiswal

Space issue of SQL Server Database how to claim free space of SQL SERVER ?

2011-04-27T02:59:00.000-07:00

Dear Friends,

I have faced recently database size issue. In my current project I need to add a column bit field in table and update the bit field conditionally.

The table contains 547,173,777 rows. Before adding the column the database size in 177 GB and after adding and updating the Bit column for the table the database size increased 223 GB.

I am suppressed with that behavior.

So my main motto at that time first recovers space. I searched many articles for claim space. Firstly I am sharing the with you all the options related to space and later on I will share the steps which help me to recover space

1) Shrink File:-
Shrink file is the most used option to recover space again from database it mainly used to recover space from log files.

Its syntax is DBCC SHRINKFILE (Database_LogFileName, Target Size MB)

2) Shrink Database:-
Shrink Database is another option which is used to shrink Database. With this option we physically gain the size.

It syntax is DBCC SHRINKDATABASE (DatabaseName, Percentage)

3) Clean Table :-

Clean table option can be used in particular condition suppose you have added a VARCHAR column in table and added data also to that column now later on you want to remove the column then After removing the column you will see the table size is unchanged to recover VARCHAR column space you can use CLEANTABLE option.

Its syntax is DBCC CLEANTABLE (DatabaseName, TableName)

4) SP_SPACEUSED:

This option will provide you space used by table or database .sp space used column will return number of rows, reserved space, Data space, index space, unused.

Its syntax is SP_SPACEUSED ‘TableName’
5) UPDATEUSAGE:-

Update usage is another most important command which update accurate Rows & pages count. Its syntax is as follows DBCC UPDATEUSAGE (DATabaseName, tableName)

6) Index Defrag:
Index Defrag is one of the main commands. It should be used in maintenance plan also this command defrag the entire index and rearrange the index.

Its syntax is as follows DBCC INDEXDEFRAG (DatabaseName, TableName, IndexName)
Other than this re indexing, re organizing index are option which help in actual database size projection. So all the above statements are related to the space information for database.

Now in my case I got help from INDEXDEFRAG command.

I did defragmentation of all the index of a table on which I have added the bit column. After running defragmentation for each index I was amazed when I run SP_SPACEUSED it recovered almost 40 GB. But the problem not ends here when I saw my disk drive it not reflect actually on disk. Then I used shrink Database command which increase my server disk free space with 40%.

So here are the steps to recover space from a table or database

1) Check current space used by table with SP_SPACEUSED command
2) Run Index defragment command INDEXDEFRAG for each index on table
3) Shrink the Log file with SHRINKFILE command

4) Shrink the database using SHRINKDATABASE command

After running above steps you will surprised. I hope these steps will help you.

Thanks & Regards

Rajat Jaiswal

Create Database with isqlW command

2011-02-20T01:38:00.000-08:00

Hello friends,
Batch files many times reduce our work. Many tables, stored procedures, functions times we need to create same database, tables, stored procedures, and pre pop data on multiple instances or even same environment. Then in that case we just create scripts and run again and again in SQL Management Studio. But if you are in hurry and you want to run the script on single click without opening file then the easiest way is use ” ISQLW” .isqlw is the another way and we can use it with batch file.
So here are the basic steps:-
Suppose I want to create friends DB database with all theand pre pop data.
Then step 1:- Generate, a script file in which we will keep all the create table’s statements, create procedures statements and pre pop scripts.
Step2:- create a batch file for this just open notepad and save the file with extension “.bat” then the file is saved as batch file. (A batch file is executable file)
Step 3: Just write isqlw command in that as shown below
IsqlW -E -i "\fdb.sql" -o "\rajat.txt"
Where -I switch for input file – o for output file. With isqlw you can give credential also but my sql server is with mix mode so there is no problem.
After writing isqlw statement just save it and run it will create database FriendDB with all the tables, stored procedures and prepop data.
And the entire log will register in rajat.txt file.
In this way you can enjoy one click database creation with all tables’ stored procedures and pre pop data.

Thanks & Esteemed Regards
Rajat Jaiswal

Restore database from zip file in SQL SERVER

2011-02-16T03:44:00.000-08:00

Recently one of my colleagues shared a link which is very useful in many ways.

http://clay.lenharts.net/blog/category/mssql-compressed-backup/

The link is very useful for restoring compressed backup. Hope this will help you also.

trust me it saved my whole day work because my production database size is 100 GB.

Thanks & Regards

Rajat Jaiswal

Pivot Viewer "Cricket World Cup 2011"

2011-02-12T10:49:00.000-08:00

Hello friends,

we have left the last topic with definition of Pivot Viewer now in this post we are going to make a sample which use pivot viewer. Our sample is “Cricket World Cup 2011” so here we go
Step 1:- First checks on your machine following things are installed if not then please installed them
a) Silverlight 4 (http://silverlight.codeplex.com/ )
b) Pivot collection tool (http://www.silverlight.net/learn/pivotviewer/collection-tools/)

Step 2:- Once you have installed above tools then create a pivot collection first. The pivot collection can also be generate using code also (at run time) but here I am using excel pivot collection generator.
Step 3:- For generating collection just open excel book go to pivot collection menu when you click pivot collection just below the menu new pivot collection tools will be visible. Then just create new collection button as shown in above image.

Excel Pivot Collection
Step 4:- When you click the new collection button you will get new excel object as shown in below fig

Pivot Collection Template
Step 5:- Now add the data according to your need as I added following columns
a) Team
b) Name
c) Bating Style
d) Bowling style
e) Is Captain
f) 100’s
g) 50’s
h) Runs
I) Average
j) URL
k) Description
l) Image
Step 6: Just fill the data as per the filtration column as shown in below fig

Step 7: Now once your data is fill then just click on publish collection button on toolbar
It will generate a “CXML” file which is collection Xml file with

deep zoom images collection.

Step8:- Now your collection is ready so next thing is how to use it. So first create a VS2010 Silverlight web application project.

Step 9: Copy CXML & and images folder to your web project first
Step 10: Once you copied the CXML and Images then open your Silverlight project and drag drop Pivot control or if it is not exist in your tool manually add for that just add reference as shown below.
xmlns:pivot="clr-namespace:System.Windows.Pivot;assembly=System.Windows.Pivot"
Step 11:- Now in the code behind just assign the collection URL to pivot control. (The URL nothing but the CXML URL which you already copied in your web project in step 9) as shown below.
this.pivot.LoadCollection("http://localhost:54210/CricketWorldCup2011.cxml", "");
Step 12: Now run the project you will get following screen. On left side you will get filter criteria when you change t

hat your data will also change in centre.

Cricket World Cup 2011 Pivot
Hope the steps will be helpful and you can download code from.
Excel Data Cricket World Cup Team 2011 Data
Solution of Pivot Viewer Download Solution
Thanks & Esteemed Regards
Rajat Jaiswal

What is Pivot Viewer in Silverlight ?

2011-01-24T18:41:00.000-08:00

Hi Folks,

Sorry for such a late post. I know you were waiting for the new post from last 2 week. So here I am with a new interesting area which will help in data visualization. “Pivot” control is great tool supported by Silverlight 4 .
I know that I am bit late to explore such a great control. I really want to thank Microsoft Pivot team for this they did such a great job.

Again our 3 basic questions WWH means,
What is pivot viewer control?
Why pivot viewer control?
And lasts but not the least How we can use & utilize it?

So, we first start with pivot viewer.
Pivot viewer is new control supported with Silverlight 4. It is a representational control you can use this control where you want a
BI functionality . Or we can say that it provide a unique way to represent data to analyse. You can filter data on the basis of various filters which you define and the transition from one filter to another filter is so smooth you worth to watch it. You can change view from normal grid view to column view.

Now if you are aware of BI (Business Intelligence) tools then I am just saying here it is one of the BI tools which help end user to get the result by applying multiple filters. You can define facts & fact category (facts are actual data & fact categories are the fields on which you can apply filter to get desire result).

Now I know you are most interested in where & how can we use? So I am taking very interesting topic which is “Cricket World Cup 2011”.

I will explain whole topic with exam

ple in next post.

Thanks
Rajat Jaiswal

Silverlight Out Of Browser Application (OOB Application)

2011-01-10T10:59:00.000-08:00

Hello friends,

As we have seen last night how to access Video capturing devices with Silverlight 4.0.
Now today I thought why we don’t try for OOB Application means Out Of Browser Application.
Out of Browser application means you can run your Silverlight application just like an EXE we don’t require any browser like IE, Firefox etc to run our Silverlight application. So here is the step which we have to follow to get out of Browser application.

Step 1:- Check Silverlight project’s property page. On this page you will find a checkbox which is “Enable running application out of the browser” just check this checkbox as shown in below fig.

Step 2:- Click on the button “Out-Of-Browser Settings...” and you will find below screen. In this screen you set title for the application, give description for the application, define icons for application.

Step3:- Be sure if you want to access local files of computer then check “Require elevated trust when running outside the browser” checkbox. When this checkbox is checked then you can easily access the file from my documents and local directory easily it give full trust to your application.
Step4 :- you can check whether the application is installed on machine or not with app.current.InstallState you can check Installation state if not installed the you can choose app.current.Install() method to install app.

Hope you will enjoy OOB application.
Thanks & Regards
Rajat Jaiswal

Webcam or other video device access using Silverlight 4.0

2011-01-08T11:16:00.000-08:00

Hello friends,

Microsoft Silverlight 4.0 provided another great feature of Audio & Video capturing. Today I am interested in using one of these cool features which is video capturing.
With the help of the video & audio capturing feature we can get all the capturing devices and use them. In current example there is only one source which is my laptop webcam.

So we are using 8 simple steps. With the help this simple we are capable of capturing webcam and stop capturing.

Step 1:- Create a new project of Silverlight with version Silverlight 4 version.
Step 2:- Draw a shape either rectangle or eclipse. The shape will hold the video.
Step 3:- CaptureDeviceConfiguration provides GetAvailableVideoCaptureDevice & GetDefaultvideoCaptureDevice methods.
The GetAvailableVideoCaptureDevice list out the entire available video capture device attached with your machine and GetDefaultVideoCaptureDevice provides default video capture device.

But before accessing any device from any machine we need to request device access and need to determine whether the device access is allowed or not. For determining the permission or request a device access we have following 2 methods

CaptureDeviceConfiguration.AllowedDeviceAccess CaptureDeviceConfiguration.RequestDeviceAccess

It will prompt message for accessing the capturing device when you run the program.

var webCam = CaptureDeviceConfiguration.GetDefaultVideoCaptureDevice();

Step 4:-
Once you determine the capture device configuration then you need to assign it to assign as a video capture source device of capturesource.
captureSource = new CaptureSource();

Step 5:- Once you assign the capture source’s video capture device then use a new brush utility provided by Silverlight which is Video Brush. You just need to set source of video Brush which will be your capture source.
var videoBrush = new VideoBrush();
videoBrush.SetSource(captureSource);

Step 6:- Once the video Brush’s source is set then you can fill any shape with video brush.
rectangle1.Fill = videoBrush;
ellipse1.Fill = videoBrush;

Step 7:- now to start capturing from video source you need to call start method of capture source.

captureSource.Start();

Step 8:- you can stop capturing video by stop method.

captureSource.Stop();

From the above description you can capture video from Webcam or any other video device which attached with your machine using Silverlight video capture facility. One more advantage is that you can take image also means image capturing is also possible. For this you have to define asynchronous image capture event.

In this way you can access video capture device. More than this you can download code from
download source code

Enjoy Silverlight.

Thanks & Regards
Rajat Jaiswal

how to find column or text in entire stored procedure?

2011-01-05T05:43:00.000-08:00

Hello Friends,
Sometime we want to search a particular column or some text in entire stored procedures. For this we can utilize following two simple approaches
Approach 1:-
BEGIN TRY
DECLARE @strColumn VARCHAR(1000)
SET @strColumn ='Rajat'
SELECT DISTINCT o.name
FROM sys.syscomments c
INNER JOIN sys.objects o ON o.object_Id = c.Id
AND o.type ='P'
WHERE text like '%' + @strColumn +'%'
ORDER BY o.NAME
END TRY
BEGIN CATCH
SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()
END CATCH
Approach 2:-
BEGIN TRY
DECLARE @strColumn VARCHAR(1000)
SET @strColumn ='Rajat'

SELECT SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE= 'PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%' + @strColumn +'%'
ORDER BY SPECIFIC_NAME
END TRY
BEGIN CATCH
SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()
END CATCH

Hope this will helpful to you somewhere.

Thanks
Rajat Jaiswal

Call SQL Server Reporting Services in ASP.NET by 5 easy steps

2011-01-03T06:49:00.000-08:00

Hello friends,
Wish you Happy New Year.

Today I am going to share a simple way to use SQL Server Reporting Services locally. I am trying to share simple steps for creating report.
I am using North wind database and try to create a sample report which shows customer information.
Here we go
Step 1:- Create a project in my case I am using web project you can choose either web/window. In the project add a typed dataset and drag drop customers table on XSD. (You can customize schema according to your need) as shown in below fig.

Step2:- Now add a report (.RDLC)

Step 3:- When you add report then on right side a screen(Just like solution explore) will appear with name “ Web Site Data source “ in this you will find the typed dataset which we have added and when you expand the typed dataset object you will find all the table schema as show in fig.

Step 4: Now just drag drop a table from toolbars’ report item. And drag drop column from data source (type data set which shown in step3) to table column which you need to show in report.

Step 5:- Once you done with step4 now next step to call report from “Asp.net”. For this just drag drop Microsoft reporting viewer and do following code.
Below is the code
Dim sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("connectionString").ConnectionString.ToString())
sqlcon.Open()
Dim sqlcmd As New SqlCommand
sqlcmd.CommandText = "SELECT * FROM Customers"
sqlcmd.CommandType = CommandType.Text
sqlcmd.Connection = sqlcon
Dim sda As New SqlDataAdapter
Dim ds As New myDs
sda.SelectCommand = sqlcmd
sda.Fill(ds, ds.Customers.TableName)
‘clearing report viewer
Me.ReportViewer1.LocalReport.DataSources.Clear()
‘Create datasource for report
Dim rds As New ReportDataSource()
rds.Name = "myDs_Customers"
rds.Value = ds.Customers
Me.ReportViewer1.LocalReport.DataSources.Add(rds)
‘assigning report path.
Me.ReportViewer1.LocalReport.ReportPath = "myReport.rdlc"
Me.ReportViewer1.LocalReport.Refresh()

Now run the page and you will get report as shown below.

Thanks
Rajat

Happy New Year

2011-01-01T11:26:00.000-08:00

Wish you all happy new year.

Hope we all do best of the best in the life.Try to put our self best in all the fields.

God Bless :) Jai mata di

thanks

Rajat Jaiswal

How to write an insert statement for a table which has only single column and that column is auto incremented?

2010-12-28T03:27:00.000-08:00

Hello friends,
Recently a question asked to my friend in an interview “How to write an insert statement for a table which has only single column and that column is auto incremented?”
So here is the answer for it with sample
DECLARE @tblTest AS TABLE (id INT IDENTITY)
INSERT INTO @tblTest DEFAULT VALUES
INSERT INTO @tblTest DEFAULT VALUES
INSERT INTO @tblTest DEFAULT VALUES
INSERT INTO @tblTest DEFAULT VALUES
INSERT INTO @tblTest DEFAULT VALUES
SELECT * FROM @tblTest
But I don’t know what will be the use of this table?

Enjoy Query
Thanks & Regards
Rajat Jaiswal

Generate thumbnail image in asp.net

2010-12-27T00:50:00.000-08:00

Dear All,
Many times we require thumbnail image on the fly.
ASP.Net provides an easy way by which we can create thumbnail. I am showing basic steps by which we can create thumbnail.
Asp.net provides “System.Drawing.Image “namespace which having property “GetThumbnailImage” by which we can get Thumbnail image.
It’s a simplest way to get a thumbnail.
Below is the code written in VB.NET by which we upload an image, save image & create thumbnail and save thumbnail with thumbnail prefix.

Protected Sub Unnamed1_Click(ByVal sender As Object, ByVal e As EventArgs)
Try
If Me.FileUpload1.FileName.Trim <> String.Empty AndAlso Me.FileUpload1.FileContent.Length > 0 Then
Dim strUploadPath As String = Server.MapPath("images")
Dim strFileName As String = Me.FileUpload1.FileName
FileUpload1.SaveAs(strUploadPath & "\" & strFileName)
Dim img1 As System.Drawing.Image = System.Drawing.Image.FromFile(strUploadPath & "\" & strFileName)
Dim thumbNail As System.Drawing.Image = img1.GetThumbnailImage(100, 100, Nothing, IntPtr.Zero)
thumbNail.Save(strUploadPath & "\thubmnail_" & strFileName)
Me.imgActual.ImageUrl = "~/images/" & strFileName
Me.imgThumb.ImageUrl = "~/images/" & "thubmnail_" & strFileName
End If
Catch ex As Exception

End Try
End Sub

Enjoy programming.

Thanks & Regards
Rajat Jaiswal

Project List On which i have to work

2010-12-25T11:44:00.000-08:00

Dear Friends,

Merry Christmas!

I Hope you are enjoying the holidays.

After seeing the market trend and requirements I am interested in making following projects which will help me and other friends to understand basic project development and use latest technologies with some productive output.

In coming up sessions i am going to use this project topic and create sample with latest technologies like Silverlight, Windows phone 7, VS 2010 etc.

So here we go

1) College Management

2) School Management

3) Job Portal

4) Forums

5) Blog

6) Employee Management

7) Bug Tracker

8) Time Sheet

9) Address Book

10) Project Management

11) Survey engine

12) Stock Manager

13) Property Broker

14) Shop Management

15) Matrimonial

16) Social Networking

17) Hotel Management

18) Inventory Management

19) Accounting

20) Transport Management

21) Hospital Management

22) Ecommerce

Etc are the project list on which I will work in future to enhance my knowledge with productive work.

I will share code base and case study also in future

Hope you like it Once again wish you Merry Christmas.

Thanks

Rajat Jaiswal

Best Practices in database design (SQL Server)

2010-12-03T05:25:00.000-08:00

Hello friends,
Designing database is one of the most interesting works but on the same time it should be proper because it is base of any business application. We should follow Microsoft best practices while designing the database. Here I am with a simple database design (Address book) concept. I will try to put my best to use Microsoft best practices while designing database. I know you are thinking Address book is so much easy to design, so my answer is yes you are right you can design database easily but using Microsoft best practices it bit more important and I am trying for those best practices.
So first best practice is
1)Define proper data type :-
Most of the time we design database but we ignore proper database but it should not because when you talking for very large scale database application (VLSDB) then wrong data type will give you space problem. According to best practices choose smallest data type first (if it fit in your requirement).We understand this by an example suppose you designed a table with name “seed Data” in which column with name lngId having data type float and float consume 8 bytes and if the seed data cannot exceed more than 10 rows then we should use Tinyint data type instead of float data type because tinyint consume only 1 byte.
2)Define proper Primary & Foreign key:-
According to best practices we should make database normalized and for normalized database we should create proper Primary & Foreign keys. By creating proper primary & foreign keys we get two advantage 1) data anomalies chances reduce we get fast response as well after defining primary key (because cluster index created on primary key)
3)Define proper constraints :-
Microsoft best practices also suggest defining proper constraints for column like not null, unique key constraint on columns so that data anomalies will reduce and we get proper data.
4)Define partition :-
If your database is going to be very large then define proper partition both horizontally & vertically.
5)Normalization: - Database should be proper normalized.
In our Address Book database example we have categorized the data table in 3 categories

1)Lookup table having prefix “lku”
The lookup table is mainly for seed value or we can say this table contains all the data which just act like pre pop data. We have following tables for our Address book
lkuCountryMaster,lkuStateMaster,lkuCityMaster,lkuPhoneType,lkuAddressType,lkuPrefixMaster,lkuURLType,lkuDesignationType,lkuRelationShipType

2)Link table having prefix “lnk”
By the name it is clear that its main purpose is making relation between main data record and other data. We have used following tables lnkPhoneToPerson,lnkAddressToPerson,lnkAddressToCompany,lnkURLToPerson,lnkPersonToCompany,lnkPersonRelations,lnkPhoneToCompany

3)Main table which start with prefix “tbl”
The main table indicates by “tbl” in our Address book database we have used following tables tblPerson, tblAddresses, tblPhotos and tblCompany.

In the entire lookup table we have preferred tiny int & Small int data type because we know the value in look up table could not go beyond the tiny int & small int max limit.
In similar way we have applied all the not null constraint to require field provide proper data type.
Please see fig below.

For more detail you can download script for Address book.

Thanks & Esteemed Regards
Rajat Jaiswal

PHP Code Generator Using Dotnet

2010-11-14T09:18:00.000-08:00

Hello Friend,
if you are new to PHP and expert in ASP.NET the don't worry just download the PHP Code Generator.
And enjoy the PHP to.

Please download the code from PHP Code Generator

For more detail visit Indiandotnet.wordpress.com

Thanks & Esteemed Regards
Rajat Jaiswal
Practical Code Generation in .NET: Covering Visual Studio 2005, 2008, and 2010 (Addison-Wesley Microsoft Technology Series)

Hack SQL Injection with prevention (Secure your web site )

2010-10-13T22:31:00.000-07:00

Dear All,
I am always curios about hacking not in destructive manner but how to prevent my sites from hacking.
So one of the thing which I want to share with you is “SQL Injection “. SQL Injection is a unique way by which you can play with database of the site.
Firstly I give you a brief introduction about SQL injection and then I will provide you information how to prevent your site by SQL injection.
So SQL injections are just like SQL statements or we can say combination of SQL statements which can be used as destructive manner by hackers.
And you cannot believe how SQL Server is powerful. With the help of” xp_cmdShell “command then end user (hacker) can crash your server too. With the help of “xp_cmdShell” you can do many things like delete file, delete dir, shutdown even format too.
So first let me show you basic example
Suppose you have login screen

on which you have done following code to validate user on
protected void btnLogin_Click(object sender, EventArgs e)
{
String connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlcon = new SqlConnection(connectionString);
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandText = “SELECT * FROM tblUser WHERE strUserName =’” + txtUserName.Text + “‘ AND strPassword =’” + txtPassword.Text + “‘”;
sqlcmd.CommandType = CommandType.Text;
sqlcmd.Connection = sqlcon;
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = sqlcmd;
DataSet ds = new DataSet();
sda.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
Response.Write(“Valid user”);
}else{
Response.Write(“Invalid user”);
}
}

Now if you see here we have directly used txtUserName, and txtPassword value here.

Now if end user enters following value as shown your screen.

Now put break point on your sqlcommand statement and see what value going on.
You will find following SQL command.
SELECT * FROM tblUser WHERE strUserName =’Rajat’ OR ‘1’ =’1’ AND password =’test’ OR ‘1’ =’1’
Now when you run this command in your SQL Browser you will be surprised that it returns all the Records. So this is power of SQL injection with just “OR “statement.

Now if you have aware of SQL Server than you understand “;” (semicolon) & — (dash dash) means.
“;” semicolon means current statement is completed and — (dash dash) means comment.
So suppose, if I enter semicolon combination with update command on my username password screen then what happen
SELECT * FROM tblUser WHERE strUserName =’RAJAT’ OR ‘1’=’1’ ; update tblUser set password =’’; –

Other than this a hacker can user various combination like this.

Now question came in your mind how to prevent this.
So here are the basic things by which you can prevent your site by SQL injections
1) Apply check for special character on login form textbox
2) Use storedprocedures
3) Use encrypted password
I hope you will be more secure programmer now so enjoy secure sites.

In future i will provide you how a hacker can hack your site and how can you prevent your site.

Thanks
Rajat Jaiswal

Stored Procedure Generator using vb.net

2010-10-13T22:29:00.001-07:00

Dear All,
I hope you have great time. Sometimes it happens that you have lot of work load and you have to deliver within time limits on the same time you have to make your coding consistency. If I think about me then I can say I am not going to compromise at least my coding style while the work load is high. So I just got idea to write some useful programs or tools which help me in finishing work as fast as possible.
So I created different utilities which generate following things
1) SQL Server Stored Procedure Generator
2) Dot net Code Generator (Three tire architecture using typed Dataset)
3) Dot net Code Generator ( Three tire architecture using Entity class)
4) PHP Code generator

In future I am interested in WCF & WPF & Silverlight code generator according to my coding style.
In this post I am giving SQL server Stored Procedure Code. It is basically a web application and you have to setup this web application and it will work as per your need.
Here is some screenshots take a look

You can download code from

http://cid-1eda2012469ff8ad.office.live.com/browse.aspx/.Public/StoredProcedureCodeGenerator

In Next post I will share dot net code generator till than b bye.

Thanks
Rajat Jaiswal

Three tire code Generator using VB.NET

2010-10-13T22:28:00.001-07:00

Hello Friends,
Today I am sharing vb.net code generator. Its interface is simple same as our stored procedure code generator. (**Important I am using Microsoft Application block for Data Access Layer**)
You need to give user credential as shown in below fig.

Once the credential is successful you will get below screen.

Just select the table for which you want to generate code. Now if you want dataset as entity then just check the use dataset checkbox.
Then press Generate button.

It will generate y our windows form, Business manager class, Data Access layer class.
Just copy and paste in your project it will work fine with some minor changes.
You can download the code from below link
Download code
Enjoy coding!!
Thanks & Regards
Rajat Jaiswal

Asp.net website in hindi or other native language with simple steps

2010-09-14T10:59:00.000-07:00

Dear all,
For Indian its great news that you can now create web site in your native language like Hindi, Marathi, Punjabi, Urdu, Tamil etc.
Kudos to Microsoft for introducing “Microsoft Indic Language” with help of this we can easily create web site in any language without doing much efforts.
So here we go with the sample and things to remember with simple step

Step 1:- Add following line in your html page
< input type=”hidden” id=”MicrosoftILITWebEmbedInfo” attachMode=”optout” value=”" />

Remember here: I am using hindi here that’s why i used hindi.js you can use your own choice.
Step 2:- Add new attribute with each input control which is “MicrosoftILITWebAttach” as shown in below.
< asp:TextBox ID=”txtPost” runat=”server” MicrosoftILITWebAttach=”true” >

Step 3:- If you want to store this value in database the just remember all the field which contain string data type should be “NVARCHAR”
Otherwise your searching will not work and all the data will be change to question mark instead of actual data.

Step 4:- for searching in database hindi text or text which you insert you have to use following syntax

SELECT * FROM tblPost where postName =N’प्रोफेसर’
So Enjoy

Thanks
Rajat
Toy Story 3 (Four-Disc Blu-ray/DVD Combo + Digital Copy)

How to resolve Log Shipping Error “Exclusive access could not be obtained because the database is in use.RESTORE LOG is terminating abnormally”?

2010-09-14T10:56:00.000-07:00

Hello Friends,
This error normally came when you did log shipping on your database and your secondary server database in stand by mode.
In this error your secondary server database restore job will not work. To resolve this error you just need to change the log shipping setting.
When you configure secondary server database mode there is check box which says disconnect all collection at the time of restore database so just check that check box and you will find your log shipping is working.
Thanks

Rajat

Leave a Comment
How to shrink Log File when Log shipping is implemented on database ?
Filed under: Sql server — indiandotnet @ 7:29 pm
Tags: Log Shipping, Rajat, Sql server

To shrink Log file you have to run following command.
DBCC SHRINKFILE ( ‘Database_log’ , NOTRUNCATE)
So here we used NOTRUNCATE option because in log shipping transaction log sequence important if you run the normal Shrink file or with truncate file option then log shipping will be failed.
So run Shrink File with NOTRUNCATE option. Although many DBA & Sql Server experts gave suggestion to avoid shrink Log file.
But we can do shrink file in log shipping with NOTRUNCATE option if database Log size too much large and you have space issue.
Thanks

Rajat
Toy Story 3 (Four-Disc Blu-ray/DVD Combo + Digital Copy)

Learn Simple SQL Server Integration Service example database to text file transformation in 5 minutes

2010-09-14T10:54:00.000-07:00

Dear Friends,
Today we are going to create simple SSIS task which we will create a text file output using ETL (Extract Transform Loading) of SSIS.
For this first basic this which we need to know
1) Source Database
2) Output file name & path

Step 1:- Open SQL Server Business Intelligence Development Studio (BIDS) and create a new Integration project as shown in below fig.

Step 2:- Now drag and drop Data Flow Task from control flow item’s toolbar as shown in below fig.

Step3:- Once you done with this just double click on Data flow task and you will get a new screen for data flow. Now we have to choose data source for our ETL process for this just drag drop data source from data flow source tool bar.
Step 4:- configure source database. In this example we are using OLEDB data source and SQL server Database. You can choose other database source too. To configure OLEDB data source just right click the OLEDB data source and click on Edit.

You will get OLEDB Source Editor as shown in fig.
Just click on new button as highlighted in above fig set database. Once database source is done we can choose any data mode like table or view, SQL Command as shown in fig.

I am choosing table employees of North wind database here. After doing this just choose desire column which you want to export in file. You can choose this column by selecting columns option.

Step 5:- Now Drag drop destination control as we require flat file destination and configure it. We can configure destination same as we did for data source. Just right click on flat file destination control and edit it.

Step6: – Once you done with configuration of file click on mapping and map the columns. As shown in below fig.

Step 7:- Now all set just run using”F5”

If everything is green then our SSIS package is done successfully.
In next session we will learn how to deploy it, how to use configuration for it.
Till than enjoy SSIS.

Thanks
Rajat Jaiswal
Triple Exposure

Replication – A way of disaster management

2010-09-14T10:53:00.000-07:00

What is Replication?

Replication is another way of disaster recovery with the help of this we can recover our database and we do not need to do much manual efforts.

Replication is the way by which you can transfer data or database object from one server to another server, synchronize them, and maintain them identical.

The interesting part is you can implement replication for any location like LAN, WAN, Internet without much effort.

What is the initial requirement for Replication?

The replication can work on SQL server 2000 2005, 2008.

What are the good points of Replication?

There are many advantage of replication

•High availability solution
◦Minimum error
◦Easy to implement
What are the points which we would not like for replication?

•Main server overhead increase
•If there is failover occurs then we have to handle that manually
What are the terminologies we used in Replication?

We used following terminologies in replication

1) Publisher:-

The publisher is main database source which publish data or other data object to different server or we can say subscriber.

2) Subscriber :-

The Subscriber by name it is clear that it subscribe the database from publisher server.

3) Distributor :-

The distribution is the server which manages flow of the data. It not mandatory to have distributor always. A publisher sever can manage role of both (publisher & distributor).

4) Article:-

An article can be any data object like data table, Rows, Store procedure or view. A publisher can be published number of article.

What are the different types of Replication?

We can implement 3 type of replication

Snapshot Replication, Transactional Replication, Merge Replication.

Snapshot Replication:-

By the name it is clear that in this publisher take snap shot of entire database and publish to the subscriber. And when its take full snap shot obviously it taking it consumes more resource. Or we can say its resource intensive process. It also known as simple replication. It used where database changes are rarely.

Transactional Replication: It also known as dynamic replication.

In this the publisher and subscriber are always synchronized. It used where we need exact same copy on subscriber with immediate effect. It can be useful when subset of database or table is required.

Merge Replication:-

It’s very interesting replication by the name it’s clear that it use merge method. In this both publisher and subscriber has freedom to work without network and when they connect the replication agents check both the database and merge each other’s changes. In merging if any conflict occurs than preference is given to publisher.

Later on in the post we will get a brief introduction how to implement replication tillthan enjoy definition .

Thanks & Regards

Rajat
Triple Exposure