Which datatype is good for my database field?

Every time at the designing of new database table we stuck for the choosing right datatype for the particular field.

It has been said that if there is right database design have been done, it’s easy to accomplish changes in the application. If you require to change database field type or require to add new field in the table, it costs a lot at later stage.

So with this confusion of choosing right database, I have some check list what to use for the string type field.

Consider following points for your string data field.

àWhat will be max size of field, how many characters could be there?

àIs there any fix length I will have?

àDoes field require using Unicode character?

There are basic for three types of datatype are supported.

Char : this datatype can be used when we know our field’s data. We have prior idea of value. It will be good to use this type.

Varchar: when we are not firm about the value come in the field, this can be the choice.

Now one interesting datatype

Nvarchar: this is same as varchar expect it stores value as Unicode character.

Unicode character are especially useful when we would like to support Multilanguage in application else there is not much reason to use it without any purpose.

Nvarachar datatype occupies double space than the varchar, it should be considered when there will be large amount of data will be feed in the system.

You can check it with following sample

After running query you will notice that varchar datatype takes less space compared to others.

declare @na char(30)

SET @na = 'shailesh'

declare @nam nvarchar(30)

SET @nam = 'shailesh'

declare @name varchar(30)

SET @name = 'shailesh'

select datalength(@na)

select datalength(@nam)

select datalength(@name)

LightSwitch the new approach for RAD

Light Switch is the new technology or you can say new development IDE provided by the Microsoft. It’s based on the .net framework seats on the silverlight, through which you can build out desktop as well as web application.

Currently it’s available in the beta version and you can download it from here.

All the required software and hardware configuration are listed out on the page.

Languages Supported: Currently light switch support vb.net and c#.

Core parts of Light Switch

Data: this is one of the very basic parameter we use for any light switch application. During the development we should use any of the data store to be used in the application.

Screen: As it names implied it deals with the User interface of the application. In light switch user interface can be created from the underlying data structure. We can also customize the screen at the runtime. This would be best features so far we get facility at the runtime.

Queries: in the data store we can use customized query too. It’s not necessary to use plain data for the application, we can attach query that is created from more than on data store.

Code: As we have told that we can built out application without writing single line of code, but sometime it requires to change basic functionality provided by framework. We can do it so. It includes adding customize list from the code, different validation as per the business needs.

User Permission: in application we can place role based security and only authorized user can have access to system. There are so many types of user permission we could use in it.

Office Integration: many of the office features are integrated and we can directly use it. We don’t require writing custom code for it.

Deployment: Light switch application can be deployed on desktop as well as on the remote server as per the need. Light switch screen is built on the silverlight so there would be no extra item we require apart from silverlight when we use it on the web.

Apart from above mentioned parts there are so many features are available like we can place different validation on the input fields without writing any code, can customize look of application, can export data to the excel sheet, can search data in the grid, different look for the editing of record, tab panel layout for the data objects and screens.

Where it can be useful?

Light switch could be the one the best choice when we require to develop in very short period of time and for the small scale industry. It also supports various data sources behind the scene. You can use any of the data source including SQL Azure, SQL server etc.

We can say now with use of light switch developer can totally focus on the business logic and they don’t need to spend time in the insert/update and other basic coding. Even they don’t need to design screen layout.

After all business needs to have solid business logic and security.

Some useful resources

http://www.lightswitchtutorial.com/

http://msdn.microsoft.com/en-us/lightswitch/ff938857

Pass CSV parameter in SQL Stored procedure

Recently, I have been asked to prepare query that accept CSV value and parse that value in stored procedure.

In early, I didn’t give much attention as I thought it was just like the Where clause with IN. If we prepare dynamic query and execute it works well. But we don’t want to do that, we want some solution that should work every time when we pass CSV value.

The solution was to create function and it returns list of value. Here is the function; I have found this function during my search.

CREATE FUNCTION dbo.CSVToList (@CSV varchar(3000))

RETURNS @Result TABLE (Value varchar(30))

AS

BEGIN

DECLARE @List TABLE

(

Value varchar(30)

)

DECLARE

@Value varchar(30),

@Pos int

SET @CSV = LTRIM(RTRIM(@CSV))+ ','

SET @Pos = CHARINDEX(',', @CSV, 1)

IF REPLACE(@CSV, ',', '') <> ''

BEGIN

WHILE @Pos > 0

BEGIN

SET @Value = LTRIM(RTRIM(LEFT(@CSV, @Pos - 1)))

IF @Value <> ''

INSERT INTO @List (Value) VALUES (@Value)

SET @CSV = RIGHT(@CSV, LEN(@CSV) - @Pos)

SET @Pos = CHARINDEX(',', @CSV, 1)

END

END

INSERT @Result

SELECT

Value

FROM

@List

RETURN

END

Now say for example you want to create stored procedure that should return you all state name where we passed id with CSV,

We can pass any number of state id in the @StateId parameter.

CREATE PROCEDURE State_Search

@StateId nvarchar(2000)

AS

BEGIN

SELECT * FROM tblState

WHERE tblState.StateId IN (SELECT * FROM dbo.CSVToLIst(@StateId))

END

Allowing special character in xml with .net in built class

Recently I was finding solution to allowing special character in xml, it can be done by replacing with replaced code. You can check here

But if you are using .net then we have one inbuilt class that replaces all of the special character with replaced code and you don’t need to worry for every special character replacement.

Code sample:

XMLstring = System.Security.SecurityElement.Escape(XMLstring)

Access to the path FileListAbsolute.txt is Denied (Error for TFS Project)

Recently during our development we come to know very strange problem and stuck to find solution for it. We use TFS for source control system for our various projects that adds capability to work parallel. After getting this error we thought it could be problem of user right’s but all people where getting same error during the build.

After going through project checked in files in server we come to know that during check in some one has checked in obj folder and that was causing this issue around the development area. After deleting this from source control and commit these changes, error of access denied was gone.
We also found same solution in after searching on internet.

Allow special character in XML

XML is very useful in any of the programming language. You have many controls of your data representation. One of the features of platform independent makes vital use of it.

In actual data we have different characters which are not supported in XML if we directly write in it. Here are some lists which can be used to replace actual character in XML.

Less than < can be replaced with &lt

Greater than > can be replaced with &gt

Ampersand & can be replaced with &amp

Quotation "can be replaced with &quot

Apostrophe ' can be replaced with &apos

At the end of replaced character we should keep one ;

Hope this list is useful when we want to use special character in XML.

Get next identity value in my table For SQL Server

It may require some time to know what will be our identity value before we actually insert record in our table. Usually we tend to find our max id value by selecting max id from records. But it may be possible you deleted records and now you want to know next identity value. SQL server has given a very nice function for it.

SELECT IDENT_CURRENT(‘mytable’)

In above line, mytable is table name, in which we want to find next identity value. Very impressive function!!