Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
N

Niladri_Biswas

@Niladri_Biswas
About
Posts
300
Topics
1
Shares
0
Groups
0
Followers
0
Following
0

Posts

Recent Best Controversial

  • how to start Schema design?
    N Niladri_Biswas

    Also you can read this article(The Life of a Project - Issue Tracking: Day 1[^]. It is a five part article and i believe it will be helpful. The Life of a Project - Issue Tracking: Day 2[^] The Life of a Project - Issue Tracking: Day 3[^] The Life of a Project - Issue Tracking: Day 4[^] The Life of a Project - Issue Tracking: Day 5[^]

    Niladri Biswas (Code Project MVP 2012)

    Database help database com design xml

  • Find phonetic word in SQL
    N Niladri_Biswas

    Please try out this Code Project Article[^]. It has a total of 6 parts and the current one handles your situation. Also you can try out with Metaphone approach which performs better that Soundex. Here[^] is an implementation for the same. Hope that helps

    Niladri Biswas (Code Project MVP 2012)

    Database database algorithms help tutorial question

  • how to start Schema design?
    N Niladri_Biswas

    This[^] may help you to start.

    Niladri Biswas (Code Project MVP 2012)

    Database help database com design xml

  • Updating or inserting entities using Entity Framework
    N Niladri_Biswas

    Hope this[^] answers your problem

    Niladri Biswas (Code Project MVP 2012)

    ASP.NET csharp asp-net database help announcement

  • Encrypting Database Credentials
    N Niladri_Biswas

    indian143 wrote:

    My ASP.Net UI is interacting with WCF service that is intern interacting with Entity Framework, in which projects I should keep these connection strings

    It needs to be kept in the WCF service layer

    You can refer the article on Task Management System that uses the similar technologies that you are using in your project. That may help you.[^]

    Niladri Biswas (Code Project MVP 2012)

    ASP.NET csharp question asp-net database wcf

  • Asp.Net - Oracle Connection failed
    N Niladri_Biswas

    That means the supplied credentials are wrong (for the second case). Provide correct credentials and it should work. Check Checking Connection to Oracle Database for testing the connectivity. In case , the credentials are correct, then may be the connection path is wrong e.g. in

    OracleConnection connection = new OracleConnection(@connectionPath);

    check the value for @connectionPath e.g.

    string connectionPath = "Data Source=orcl;User Id=scott;Password=tiger;Persist Security Info=False";

    Hope this will help. [^]

    Niladri Biswas (Code Project MVP 2012)

    ASP.NET database csharp help asp-net oracle

  • How do you offload a Dictionary?
    N Niladri_Biswas

    Try this

    Dictionary<object, object> dummyDictionary = new Dictionary<object, object>
    {
    {"India","Delhi"},
    {"USA","WashingtonDC"},
    {"Bangaladesh","Dhaka"},
    {"Pakistan","Karachi"}
    };

    //Foreach loop construct

    foreach (KeyValuePair<object, object> kvp in dummyDictionary)
    {
    Console.WriteLine(string.Format("Key = {0} Value = {1}", kvp.Key, kvp.Value));
    }

    //using Linq and Foreach Extension method

    var result =
    (from kvp in dummyDictionary
    select new
    {
    Key = kvp.Key
    ,
    Value = kvp.Value
    });
    result.ToList().ForEach(kvp => Console.WriteLine(string.Format("Key = {0} Value = {1}", kvp.Key, kvp.Value)));

    Console.ReadKey();

    Niladri Biswas (Code Project MVP 2012)

    C# question csharp

  • sql server 2008 r2
    N Niladri_Biswas

    Do it using SQL Script (like alter etc...) instead of using SSMS

    Niladri Biswas (Code Project MVP 2012)

    Database help database sql-server sysadmin

  • How do you use the AVG api in sql to manage grouped output?
    N Niladri_Biswas

    All Version of Sql server

    Select City, AVG(Salary) AS Salary
    FROM @t
    Group By City
    Order By City DESC

    This can also be done by treating AVG function as Analytical function which is available since SQL Server 2005

    Select City,Salary From
    (
    Select
    Rn = Row_Number() Over(PARTITION BY City Order By (Select 1))
    ,City
    ,AVG(Salary) OVER(PARTITION BY City) AS Salary
    From @t) X
    Where X.Rn = 1
    Order By City DESC

    The answer for both the cases City Salary Seattle 20000 Redmond 30000

    Niladri Biswas (Code Project MVP 2012)

    Database database json tutorial question career

  • Help with Decimal Roundings
    N Niladri_Biswas

    Declare @X float = 0.833333333333333

    SELECT ROUND(@X*100,0)

    Niladri Biswas (Code Project MVP 2012)

    Database csharp help asp-net com

  • SQL Table Grouping
    N Niladri_Biswas

    Hope this may help (Sql Server 2000+)

    Declare @t table(PI int, JobID int, FormID int, ShiftID int, StartEvent datetime, SW int,SG int, EndEvent datetime,EG int,EW int)
    Insert Into @t
    Select 3,9249,36208,6241,'2010-08-14 10:00:15.610',0,0,'2010-08-14 10:14:52.000',143,109 Union All
    Select 3,9249,36208,6241,'2010-08-14 10:14:52.217',109,143,'2010-08-14 10:15:04.000',150,109 Union All
    Select 3,9249,36208,6241,'2010-08-14 10:15:04.763',109,150,'2010-08-14 10:15:07.000',150,109 Union All
    Select 3,9249,36208,6241,'2010-08-14 10:15:09.820',109,150,'2010-08-14 10:29:15.000', 150,221 Union All
    Select 3,9249,36208,6241,'2010-08-14 10:29:15.570', 221,150,'2010-08-14 10:53:09.000',376,300 Union All
    Select 3,9249,36208,6241,'2010-08-14 10:53:09.240',300,376,'2010-08-14 11:01:18.000',4294,1824 Union All
    Select 3,9249,36208,6241,'2010-08-14 11:01:18.553',1824,4294 ,'2010-08-14 11:02:06.000',4294,1942 Union All
    Select 3,9249,6208,6241,'2010-08-14 11:02:06.363',1942,4294,'2010-08-14 11:02:14.000',4294,1920

    Select X.*,Y.EndEvent,Y.EG,Y.EW
    From( Select Top 1 PI,JobID,FormID,ShiftID,StartEvent,SW,SG
    From @t
    Order By StartEvent)X
    Join ( Select Top 1 PI,EndEvent,EG,EW
    From @t
    Order By EndEvent DESC)Y
    On X.PI = Y.PI

    Result

    PI JobID FormID ShiftID StartEvent SW SG EndEvent EG EW
    3 9249 36208 6241 2010-08-14 10:00:15.610 0 0 2010-08-14 11:02:14.000 4294 1920

    Niladri Biswas (Code Project MVP 2012)

    Database database help

  • SQL TABLE JOIN
    N Niladri_Biswas

    Try

    'c' + c.ID = co.CID

    and not

    c.ID = 'c' + co.CID

    If C.Id is of integer type then cast it

    'c' + Cast(c.ID as varchar(20))= co.CID

    Try this

    Declare @City table (Id int,Name Varchar(20))
    Insert Into @City
    Select 123,'City1' Union All Select 1,'City2' Union ALl Select 124,'City3' Union ALl Select 5,'City4'

    Declare @Country table (Id int,CID Varchar(10),Name Varchar(20))
    Insert Into @Country
    Select 1,'c123','Country 1' Union All
    Select 1,'1','Country 1' Union All
    Select 1,'c5','Country 1'

    SELECT c.Name, co.Name
    FROM @City C
    JOIN @Country co ON 'c' + Cast(c.ID as varchar(20))= co.CID

    Hope this helps

    Niladri Biswas

    Database question database help

  • SQL Query Select
    N Niladri_Biswas

    Try this

    Declare @Course table ([Course Name] Varchar(100),[Course Type] Int)
    Insert Into @Course
    Select 'MCA',1 Union All
    Select 'MCA',2

    ;With CTE AS(
    Select [Course Type] = 1,[Course Description] = 'Regular' Union All
    Select [Course Type] = 2,[Course Description] = 'Distance'
    )

    Select
    Result =
    c.[Course Name]
    + '('
    + CTE.[Course Description]
    + ')'
    From @Course c
    Join CTE ON c.[Course Type] = CTE.[Course Type]

    If you are using Denali CTP 3, you can use the new Choose function

    Select
    Result =
    Concat(
    c.[Course Name]
    ,'('
    ,CTE.[Course Description]
    ,')'
    )
    From @Course c
    Join CTE ON c.[Course Type] = CTE.[Course Type]

    Output

    Result
    MCA(Regular)
    MCA(Distance)

    Niladri Biswas

    Database help database tutorial learning

  • isnull in linq where condition
    N Niladri_Biswas

    This may help

    where
    !string.IsNullOrEmpty(record.Field("userpk"))
    ? record.Field("userpk")
    : null

    Niladri Biswas

    Database database csharp linq tutorial

  • select unique record question
    N Niladri_Biswas

    Try this

    ;With CTE AS
    (
    Select
    Rn = Row_Number() Over(Partition By ('973' + contact_moblle) Order By file_no)
    ,('973' + contact_moblle) AS mobile_number
    from persons
    where contact_moblle is not null
    and contact_moblle != ''
    and len(contact_moblle) = 8
    and left(contact_moblle, 1) = '3'
    )

    Select mobile_number
    From CTE
    Where Rn = 1

    Hope this helps

    Niladri Biswas

    Database database sql-server sysadmin help question

  • SQL Query
    N Niladri_Biswas

    Try this Declare @t table (Record Varchar(20)) Insert Into @t Select 'OTH' Union All Select 'IA' Union All Select 'LE' Union All Select 'xxx' Select * From @t Where Record like '%[H,A,E]%' /* Result */ Record OTH IA LE Hope this helps Thanks in advance

    Niladri Biswas

    Database database help tutorial question

  • Single row for multiple results
    N Niladri_Biswas

    Assuming Sql Server 2005 and above, try this

    Declare @t table([Name] varchar(20),[Status] varchar(50))
    insert into @t values
    ('John Smith','Pending Approval'),('John Smith','Waiting Email Verfication')
    --,('User2','Pending Approved'),('User2','Sent email'),('User2','Other activity')
    --Select * from @t

    select Result = [Name] + ' ' + Stuff((Select ',' + CAST([Status] as varchar(100)) from @t t2 where t1.[Name]
    = t2.[Name] for XML Path('')),1,1,'')
    from @t t1
    group by t1.[Name]

    /*
    Result

    John Smith Pending Approval,Waiting Email Verfication
    */

    Niladri Biswas

    Database tutorial

  • How to do a select statement from two or more tables?
    N Niladri_Biswas

    Declare @Student table(StudentID int identity,Name varchar(20))
    Declare @Poke table(Poke_Id int identity,Poke_Giver_Id int,Poke_Receiver_Id int)
    insert into @Student values('Alex'),('Bob'),('Caveman')
    insert into @Poke values(1,2),(1,2),(2,1)

    Select x.Poke_Id,x.Poke_Giver_Name,Poke_Receiver_Name = s.Name
    from(
    Select p.Poke_Id,s.Name Poke_Giver_Name ,p.Poke_Receiver_Id
    from @Poke p
    join @Student s
    on p.Poke_Giver_Id = s.StudentID
    )x
    join @Student s on s.StudentID =x.Poke_Receiver_Id

    /*

    Poke_Id Poke_Giver_Name Poke_Receiver_Name
    1 Alex Bob
    2 Alex Bob
    3 Bob Alex
    */

    Niladri Biswas

    Database help tutorial question

  • UPDATE Multiple tables on one filed. [modified]
    N Niladri_Biswas

    Hi, I am providing a solution to this problem but ideally you should follow as what Shameel has said.. Let us create 4 tables (one master and three child tables) as under

    CREATE TABLE [dbo].[tblmaster](
    [testID] [varchar](50) NULL,
    [Name] [varchar](50) NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[tblChild1](
    [testID] [varchar](50) NULL,
    [Name] [varchar](50) NULL,
    [Age] [int] NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[tblChild2](
    [testID] [varchar](50) NULL,
    [Name] [varchar](50) NULL,
    [Address] [varchar](50) NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[tblChild3](
    [testID] [varchar](50) NULL,
    [Sex] [varchar](6) NULL,
    [Address] [varchar](50) NULL,
    [Phone Number] [varchar](50) NULL
    ) ON [PRIMARY]

    insert into tblmaster
    select 's401', 'name10' union all
    select 's402', 'name11' union all
    select 's403', 'name12' union all
    select 's404', 'name14'

    insert into tblChild1
    select 's401', 'name20', 20 union all
    select 's402', 'name21', 21 union all
    select 's403', 'name22', 22 union all
    select 's404', 'name23',23

    insert into tblChild2
    select 's401', 'name30', 'address30' union all
    select 's402', 'name31', 'address31' union all
    select 's403', 'name32', 'address32' union all
    select 's404', 'name33', 'address33'

    insert into tblChild3
    select 's401', 'male', 'address40','9885446789' union all
    select 's402', 'female', 'address41','12345678' union all
    select 's403', 'male', 'address42','34567891' union all
    select 's404', 'female', 'address43','0986234'

    Select * from tblmaster
    Select * from tblChild1
    Select * from tblChild2
    Select * from tblChild3

    Now fire the below query

    exec sp_msforeachtable N'
    IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID(''?'') AND name = ''testID'')
    EXEC (''UPDATE ? SET testID= case when testID = ''''s401'''' then ''''I00010''''
    when testID = ''''s402'''' then ''''I00020''''
    when testID = ''''s403'''' then ''''I00030''''
    when testID = ''''s404'''' then ''''I00040''''
    end'')'

    N.B.~ Use this example as a reference for your problem. Thanks

    Niladri Biswas

    modified on Tuesday, July 19, 2011 5:48 AM

    Database tools tutorial announcement

  • UnQuote a nvarchar() string and use it as a table name
    N Niladri_Biswas

    Hi try this Input:

    Declare @tblPlaceholder table(ID int,TableName Nvarchar(20),FieldName Nvarchar(20))
    insert into @tblPlaceholder
    select 1,'spt_values','name' union all select 1,'spt_values','number' union all
    select 2,'spt_monitor','lastrun' union all select 2,'spt_monitor','cpu_busy'
    --select * from @tblPlaceholder

    Query:

    Declare @t table(ID int,Query VARCHAR(2000))
    Declare @QueryList VARCHAR(2000)
    Declare @i int
    set @i = 1

    -- Step 1: Build the query and insert the same into a table variable
    Insert into @t
    Select ID, Query = ' Select ' + FieldNames + ' from ' + TableName
    From
    (
    Select ID,TableName, FieldNames = Stuff((select ',' + CAST(TableName as Nvarchar(20)) + '.' + CAST(FieldName as Nvarchar(20))
    from @tblPlaceholder t2 where t2.ID = t1.ID
    FOR XML PATH('')),1,1,'')
    From @tblPlaceholder t1
    Group By t1.ID,t1.TableName)X

    /* output of step 1
    select * from @t

    ID Query
    1 Select spt_values.name,spt_values.number from spt_values
    2 Select spt_monitor.lastrun,spt_monitor.cpu_busy from spt_monitor
    */

    -- Step 2 : loop thru the ID and execute the queries
    While (@i <= 2) -- since there are two id's.. this can even be configurable as max(id)
    Begin
    SELECT @QueryList = (Select Query from @t where ID = @i)
    exec(@QueryList)
    set @i += 1
    End

    /* Final output

    Output of Select spt\_values.name,spt\_values.number from spt\_values
    
    name	number
    (rpt)	-32768
    YES OR NO	-1
    SYSREMOTELOGINS TYPES	-1
    SYSREMOTELOGINS TYPES (UPDATE)	-1
    
    
    Output of Select spt\_monitor.lastrun,spt\_monitor.cpu\_busy from spt\_monitor
    
    lastrun					cpu\_busy
    2008-07-0916:46:13.877	10
    

    */

    I have given the demonstration by using two system tables (spt_values and spt_monitor) found in the Master database. The comments about the steps are mentioned in the code itself. Hope this will help you Let me know in case of any concern. Thanks

    Niladri Biswas

    Database
  • Login

  • Don't have an account? Register

  • Login or register to search.
  • First post
    Last post
0
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups