Grant rights to Active Directory Group on SQL Server

Rather than adding each user from an AD security group, use that group to globally manage rights on SQL Server to all of those users, where appropriate, of course.

USE master;
GO
CREATE LOGIN [DOMAIN\Group Name] FROM WINDOWS;
GO
USE DatabaseName;
GO
CREATE USER [DOMAIN\Group Name] FROM LOGIN [DOMAIN\Group Name];
GO
-- assign the user a role
EXEC sp_addrolemember db_datareader, [DOMAIN\Group Name];
GO

Return Value From SQL Server to C#

On some occasions it is important to retrieve a value from a Store Procedure and return it to a C# function. This example is an excerpt from a project to create an image library. File names are pushed into SQL Server using a Stored Procedure. Part of it is to record the path. Paths are stored in one table, file names in another. The record ID of the path is required to create the image record.

SQL Server Stored Procedure

CREATE PROCEDURE [dbo].[sp_newPath]
@path nvarchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- NewPathID is used to retain the Record ID of the existing or new path ID
DECLARE @NewPathID int

-- If the path already has a record, get the ID
SELECT TOP 1 @NewPathID = dbo.Paths.path_ID from dbo.Paths WHERE dbo.Paths.path_path = @path;

-- If there is no record, @NewPathID will be 0 or NULL
if @NewPathID = 0 OR @NewPathID IS NULL
BEGIN

-- Insert the new path record into the Table
INSERT dbo.Paths(path_path)
OUTPUT INSERTED.path_ID INTO @TblforID
VALUES(@path)

-- Get the new Identity value
SELECT @NewPathID = SCOPE_IDENTITY()
END
RETURN (@NewPathID)
END

C# function to push data into SQL Server and retrieve the record ID.

private static int InsertPathSQL(string dirPath)
{

string sqlConnStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=Image_Library;Integrated Security=true";
SqlConnection sqlConn = new SqlConnection(sqlConnStr);

SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlConn;
sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
sqlComm.CommandText = "dbo.sp_newPath";
sqlComm.Parameters.Add("@path", System.Data.SqlDbType.NVarChar).Direction = System.Data.ParameterDirection.Input;
sqlComm.Parameters["@path"].SqlValue = dirPath;
sqlComm.Parameters.Add("@returnValue", System.Data.SqlDbType.Int, 4).Direction = System.Data.ParameterDirection.ReturnValue;
sqlComm.Connection.Open();
sqlComm.ExecuteNonQuery();
int j = (int)sqlComm.Parameters["@returnValue"].Value;
sqlComm.Connection.Close();
return j;
}

SQL Server Instances

Finding running instances of SQL Server on a computer

Launch PowerShell

Get-Service

Lists all services on the computer. Look for SQL Server

Named instances are displayed as MSSQL$NAME

Default instance will display as MSSQLSERVER

SQL Server Express Edition installs as a Named Instance

ASP.Net SQL Server Express Connection

  1. In Web.Config or App.Config include a connection string:

    <connectionStrings>
    <add name="NAMEFORCONNECTION" connectionString="Data Source=SERVER\SQLEXPRESS;Initial Catalog=DATABASENAME;Integrated Security=True" providerName="System.Data.SQLClient"/>
    </connectionStrings>
  2. On your Page.aspx, you can refer to the Connection string and include your SQL statement:

    <asp:SqlDataSource ID="sqlConnection1" runat="server" ConnectionString="<%$ ConnectionStrings:NAMEFORCONNECTION %>" SelectCommand="[dbo].[sp_DoStoredProcedure] param1, param2, paramN"/>
  3. Other controls can refer to the SqlDataSource, similarly to this GridView:

    <asp:GridView ID="GridView1" runat="server" DataSourceID="sqlConnection1" AutoGenerateColumns="true" CssClass="Grid" AlternatingRowStyle-CssClass="alt" PagerStyle-CssClass="pgr"></asp:GridView>