Create Login and User in SQL Server 2005 from ASP.NET or C#

I have seen many times that people used to use DML and DDL command of SQL Server with SQL Connection and SQL Command from C# but since they have never tried other T-SQL command they are unaware with how to do that? This is not something different than other DDL and DML command. .NET Framework 3.5 is pretty much powerful to handle almost every script of SQL Server. Let us see how to create User Login in SQL Server with ASP.NET C# code.

I have one webform with one button and it will call one method to which used to do the job of creating user in SQL Server.

//button1’s click event which will call CreateSQLLogin method

//and will create Login in SQL Server and User for AdventureWorks db

protected void Button1_Click(object sender, EventArgs e)

{

string strConn = “Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa”;

string strLoginName = “TestUser”;

string strPassword = “testing123”;

int i=CreateSQLLogin(strConn, strLoginName, strPassword);

if (i == 1)

{

Response.Write(“Created Successfully!!!!”);

}

else

{

Response.Write(“problem in creating account”);

}

}

//create login and user

public int CreateSQLLogin(string strConn, string strLoginName, string strPassword)

{

string strCreateLogin = @”use master CREATE LOGIN [“ + strLoginName + “] WITH PASSWORD=N'” + strPassword + “‘ , DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON”;

string strCreateUser = @”use AdventureWorks CREATE User[“ + strLoginName + “] FOR LOGIN [“+ strLoginName +“]”;

try

{

System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(strConn);

sqlConn.Open();

System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(strCreateLogin, sqlConn);

sqlCmd.ExecuteNonQuery();

System.Data.SqlClient.SqlCommand sqlCmd1 = new System.Data.SqlClient.SqlCommand(strCreateUser, sqlConn);

sqlCmd1.ExecuteNonQuery();

return 1;

}

catch

{

return 0;

}

}

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

UPDATE Statement with Aggregate function in WHERE condition

I seen many places developer get confused about how to use AGGREGATE function in WHERE condition with UPDATE statement. Suppose I have one table with user name and percentage. I want to update percentage if one user’s percentage sum is greater or lower than some value.

–table used

create table test11

(

uName varchar(20),

percentage int

)

–data used

INSERT INTO test11

SELECT ‘RITESH’,90 UNION ALL

SELECT ‘RAJAN’,75 UNION ALL

SELECT ‘RITESH’,80 UNION ALL

SELECT ‘ALKA’,70

–update query

update test11 set percentage=100

where uname=‘Ritesh’

AND

percentage in

(

select percentage from test11

WHERE uname=‘Ritesh’ group by percentage having sum(percentage)>70

)

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

DateADD function for Quarter in SQL Server 2005

Recently I have read few questions in forums about limitations of DATEADD function. Support I have one date 30-Mar-09 and I want to find last quarter of it. What should I do?

select CONVERT(VARCHAR,dateadd(q, –1, ‘2009-03-30’),106)

above query will display December as last quarter but what If I enter date like 10-Jan-09?

select CONVERT(VARCHAR,dateadd(q, –1, ‘2009-01-10’),106)

It will still show October as a last quarter of date but my need is, if I enter Jan, Feb or March, my quarter month should be December. In that case, I have found one solution which I am going to share with you. If there is any better alternative, I would like my reader to share it here so that people can use it.

select CASE datepart(m,‘2009-07-28’)

WHEN 1 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-03-31’),106)

WHEN 2 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-03-31’),106)

WHEN 3 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-03-31’),106)

WHEN 4 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-06-30’),106)

WHEN 5 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-06-30’),106)

WHEN 6 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-06-30’),106)

WHEN 7 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-09-30’),106)

WHEN 8 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-09-30’),106)

WHEN 9 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-09-30’),106)

WHEN 10 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-12-31’),106)

WHEN 11 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-12-31’),106)

WHEN 12 THEN CONVERT(VARCHAR,dateadd(q, –1, ‘2009-12-31’),106)

END

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Expert and Premium member in Expert Exchange.

If you have read my 101th article at http://www.sqlhub.com/2009/03/milestone-after-100-articles-in.html, you might remember that I have started helping the .NET and SQL Server community at Expert-Exchange web site. Today my earning points become 10600 at expert exchange so that I become qualified expert and premium member there. Have a look at my EE profile at:
http://www.experts-exchange.com/M_4847866.html

Reference: Ritesh Shah
http://www.sqlhub.comNote: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Sp_configure in SQL Server 2005

Today I was reading few of my old articles, when I seen sp_configure there, idea of this article pops up in my mind as there are many properties to set and I have used few of them only in my past article. There are many more one can use in real life so I have started digging into Microsoft Book Online for those properties of sp_configure and here is a results of that.

Sp_configure: When you run this SP in your query window, it will show you current setting of the server. You can execute it like below

EXEC sp_configure

NOTE: many of the server property change will not take effect until and unless your restart server or its service that is why we have to use RECONFIGURE command rather than restart.

“Show Advanced Option”: This property will show you all advanced option you can set with sp_configure. If you run only EXEC sp_configure command as per above, you may see few rows as a result, I got 14 rows in my development server but there are lot more option other than these few rows. To see all the advanced option, run following commands in your query window.

–set advanced option to true

EXEC sp_configure ‘show advanced option’,1

–reconfigure the server so that property change

–take effect right a way

reconfigure

GO

–list the properies after advanced option true

EXEC sp_configure

GO

“Query Wait”: When memory is not available to execute the query, you can specify seconds, how many seconds will it wait to execute before gets time out?

EXEC sp_configure ‘query wait’,30

reconfigure

GO

Min and Max server memory: These properties mainly used to change the memory usage of SQL Server. I have used these properties along with “awe enabled” property in my following article.

http://www.sqlhub.com/2009/03/memory-configuration-of-sql-server-2005.html

“Min memory per query”: default 1MB set as a “Min memory per query” but you can increase or decrease this limit for better performance but beware, increasing limit could cause memory shortage problem as well.

EXEC sp_configure ‘min memory per query’,1024

reconfigure

GO

“Scan for startup procs”: I have used this property in my article of Startup Stored Procedure at http://www.sqlhub.com/2009/03/startup-stored-procedure-sql-server.html

This property is mandatory if you are making startup stored procedure and want SQL Server to scan that SP while SQL Server restart.

EXEC sp_configure ‘scan for startup procs’,1

reconfigure

GO

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

ADO.NET and Microsoft Sync Services

Today I was digging Visual Studio 2008 and SQL Server 2008 more and I found one amazing feature which is supported by SQL Server 2008. The feature is Sync Services for ADO.NET.

If you have many users of your application who used to roam around with lap top or palm top and don’t have constant internet connection than you can give them local copy of database, they can work with application and whenever network or internet available, they can synchronize their own local copy with main database. Isn’t it amazing feature? Yes, it is. This is just an example to make you understand about need this new service but this concept is much much bigger than what I have said.

Let us see what Microsoft is saying about this in its own words from Microsoft Website.

“Sync Services for ADO.NET is a Microsoft Sync Framework powered solution for synchronizing ADO.NET enabled databases in offline and collaboration scenarios. Sync Services for ADO.NET allows developers who are familiar with the concepts of ADO.NET to apply that knowledge to data synchronization through a very similar set of APIs to that of ADO.NET. Sync Services for ADO.NET provides the flexibility of a programming model like offline datasets and a richer synchronization feature set like that found in Merge replication. Sync Services for ADO.NET also supports synchronization over services, such as Windows Communication Foundation (WCF). ”

I have visited following few links to get an idea about this amazing service.

http://msdn.microsoft.com/en-us/sync/bb887608.aspx

http://msdn.microsoft.com/en-us/sync/default.aspx

http://blogs.msdn.com/sync/archive/2008/09/16/now-available-sync-services-for-ado-net-on-windows-mobile-devices.aspx

Reference Collacted By: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Dynamic PIVOT with WHERE condition in SQL Server 2005

I have written many articles on PIVOT even I feel to write one more on practical situation. Suppose you have one table which contain studentID, year of student, total marks, increase and decrease. We need to find list of student on increase and decrease. How can we do that?

Have a look at this example with dummy data.

CREATE TABLE StudDetail

(

StudentID INT,

PassYear VARCHAR(10),

Grades int,

Increase INT,

Decrease INT

)

GO

INSERT INTO StudDetail

SELECT 1, ’08-09′, 3333,0,0 union all

SELECT 1, ’09-10′, 4252,25,0 union all

SELECT 2, ’08-09′, 2100,0,0 union all

SELECT 2, ’09-10′, 2002,0,-10

GO

–pivot query

SELECT * from (select StudentID,PassYear,grades FROM StudDetail where increase>=0) up

PIVOT (sum(grades) for passyear in ([08-09], [09-10])) AS pivo

–I can work with above query but what if I don’t know how much year I have to make column???

–I have to go for dynamic query, let us create it.

–START creating dynamic code for PIVOT

DECLARE @Cols NVARCHAR(2000)

SET @Cols=”

–make column list for PIVOT

SELECT @Cols=@Cols+ ‘[‘+s.Passyear +‘]’+ ‘, ‘ FROM

(SELECT DISTINCT PassYear FROM StudDetail) AS s

–remove last comma from column list

SET @Cols=LEFT(@Cols,LEN(@Cols)-1)

–create pivot query as we have just added distinct year list in @Cols variable

SET @Cols=‘SELECT * from (select StudentID,PassYear,grades FROM StudDetail where increase>=0) up

PIVOT (sum(grades) for passyear in (‘+@cols+‘)) AS pivo’

–print query and check

print @cols

EXECUTE sp_executeSQL @Cols

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Environment.CurrentDirectory in C#

Well, this blog is mainly focuses on SQL Server technology but sometime I would like to give tips on other Microsoft Technology as I did it in past as well.

I have seen many C# developer use full path to access some file or folder insider the root directory of project. They might be unaware with Environment.CurrentDirectory property.

Have a look at below small tips for that.

string var1;
var1 = Environment.CurrentDirectory;
var1 = var1.Substring(0, var1.Length – 9);

Note: default CurrentDirectory property point to Bin\Debug folder if you are running your application in Debug mode so I removed last nine character to get root path. You can make it more customize as per your need. Hope this will help.

Cheers!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Keep log of Update in same table with Instead Of Trigger in SQL Server 2005

I wrote few articles on audit trails and would prefer to keep log in separate table even I see many times how to keep update track records in same table so I thought to create one example and share it with my readers.

CREATE TABLE STATSofTable

(

Status INT NOT NULL,

LOGS VARCHAR(MAX)

)

–insert into stats table

INSERT INTO STATSofTable (status,LOGS)

SELECT 1,‘TEST’ UNION ALL

SELECT 2,‘TEST’ UNION ALL

SELECT 3,‘TEST’

–CREATE TRIGGER TO UPDATE LOG AUTOMATICALLY

CREATE TRIGGER AuditTrailOnSTATSofTable ON STATSofTable

INSTEAD OF UPDATE

AS

SET NOCOUNT ON

BEGIN

IF UPDATE(Status)

BEGIN

DECLARE @OldValue VARCHAR(1)

DECLARE @NewValue VARCHAR(1)

DECLARE @OldLog VARCHAR(max)

DECLARE @NewLog VARCHAR(max)

SELECT @OldValue = Status FROM Deleted

SELECT @NewValue = Status FROM INSERTED

SELECT @OldLog= LOGS from Deleted

SET @NewLog=@OldLog + ‘ STATUS CHANGE FROM ‘ + @OldValue + ‘ TO ‘+ @NewValue + ‘ ON ‘ + CONVERT(VARCHAR(50), getdate())

UPDATE STATS SET Status=@NewValue, LOGS=@NewLog WHERE Status=@OldValue

print @oldvalue

print @newvalue

print @OldLog

END

END

–UPDATE STATUS

update STATSofTable set status=4 where status =1

update STATSofTable set status=1 where status =4

select * from STATSofTable

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Database file size in MB in SQL Server 2005

I seen people ask in many forums that how can they find the size of their MDF and LDF file in MB by T-SQL so I thought to publish it here in my blog.

select ceiling(size/128) as ‘fileSizeinMB’,name as ‘filename’,physical_name from sys.database_files

It will return all available files for your database.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah