Notes

//Parametreye gore mailleri listeleme
CREATE PROCEDURE [dbo].[GetMailsByBuyType]
@isPDF int = 0,
@isBook int = 0,
@isPhoto int = 0
AS
BEGIN
SELECT DISTINCT strEmail FROM tblUsers INNER JOIN tblUserInfo ON tblUsers.intUserID = tblUserInfo.intUserID
WHERE strEmail IS NOT NULL
AND ((@isPDF=1 AND isPDFBuyer = 1) OR (@isPDF=0 AND (isPDFBuyer = 0 OR isPDFBuyer = 1)))
AND ((@isBook=1 AND isBookBuyer = 1) OR (@isBook=0 AND (isBookBuyer = 0 OR isBookBuyer = 1)))
AND ((@isPhoto=1 AND isPhotograph = 1) OR (@isPhoto=0 AND (isPhotograph = 0 OR isPhotograph = 1)))
END

//Mailleri cekmek icin c# kodu (Database Manager)
public static DataTable GetMailsByBuyType(int isBook, int isPDF, int isPhoto)
{
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("GetMailsByBuyType");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "isPDF", isPDF);
DataBaseHelper.SetParameterValue(sqlCmd, "isBook", isBook);
DataBaseHelper.SetParameterValue(sqlCmd, "isPhoto", isPhoto);
//Sadece tek column donduruyor o da mail adreslerini iceriyor.
return DataBaseHelper.ExecuteDataSet(sqlCmd).Tables[0];
}
catch (Exception ex)
{
return null;
}
}

//Foto yukleme, buna gore kurup yapacagız
http://www.c-sharpcorner.com/UploadFile/0c1bb2/uploading-images-to-database-using-Asp-Net-C-Sharp/

//Text yukleme
//Table'i

CREATE TABLE [dbo].[tblComments](
[intCommentID] [int] IDENTITY(1,1) NOT NULL,
[intUserID] [int] NULL,
[strComment] [varchar](max) NULL,
[intStatus] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

//Ekleme
CREATE PROCEDURE [dbo].[InsertComment]
@intUserID int,
@strComment VARCHAR(MAX)
AS
BEGIN
INSERT INTO tblComments (intUserID, strComment, intStatus) VALUES (@intUserID, @strComment, 0)
END
//InsertCommand DatabaseManager.cs
public static void InsertComment(int intUserID, string strComment)
{
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("InsertComment");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "intUserID", intUserID);
DataBaseHelper.SetParameterValue(sqlCmd, "strComment", strComment);
DataBaseHelper.ExecuteNonQuery(sqlCmd);
}
catch (Exception ex)
{
}
}
//Status Update 0-> Default onaylama ile ilgili birsey yapılmadı
// 1-> Reddedildi, 2->Onaylandı yıllıkta gorunecek
CREATE PROCEDURE [dbo].[UpdateCommentStatus]
@intCommentID int,
@intStatus int
AS
BEGIN
UPDATE tblComment SET intStatus = @intStatus WHERE intCommentID = @intCommentID
END
//UpdateCommentStatus DatabaseManager.cs
public static void UpdateCommentStatus(int intCommentID, int intStatus)
{
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("UpdateCommentStatus");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "intCommentID", intCommentID);
DataBaseHelper.SetParameterValue(sqlCmd, "intStatus", intStatus);
DataBaseHelper.ExecuteNonQuery(sqlCmd);
}
catch (Exception ex)
{
}
}
//Commentleri userid ve status'e gore getirme. Status 4 girilirse o user'a ait tum commentler geliyor.
CREATE PROCEDURE [dbo].[GetCommentsByUserIDAndStatusType]
@intUserID int,
@intStatus int
AS
BEGIN
SELECT intCommentID, strComment, intStatus FROM tblComments WHERE intUserID = @intUserID AND ((@intStatus = 4) OR (@intStatus <> 4 AND intStatus = @intStatus))
END
//GetCommentsByUserIDAndStatusType
public static DataTable GetCommentsByUserIDAndStatusType(int intUserID, int intStatus)
{
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("GetCommentsByUserIDAndStatusType");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "intUserID", intUserID);
DataBaseHelper.SetParameterValue(sqlCmd, "intStatus", intStatus);
return DataBaseHelper.ExecuteDataSet(sqlCmd).Tables[0];
}
catch (Exception ex)
{
return null;
}
}

//Password Generator
http://www.obviex.com/samples/password.aspx
//RandomPassword.cs App_Code altına koydum
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Security.Cryptography;

/// <summary>
/// Summary description for RandomPassword
/// </summary>
public class RandomPassword
{
private static int DEFAULT_MIN_PASSWORD_LENGTH = 8;
private static int DEFAULT_MAX_PASSWORD_LENGTH = 10;

private static string PASSWORD_CHARS_LCASE = "abcdefgijkmnopqrstwxyz";
private static string PASSWORD_CHARS_UCASE = "ABCDEFGHJKLMNPQRSTWXYZ";
private static string PASSWORD_CHARS_NUMERIC = "23456789";
private static string PASSWORD_CHARS_SPECIAL = "*$-+?_&=!%{}/";
public RandomPassword()
{

}
public static string Generate(int minLength,
int maxLength)
{
// Make sure that input parameters are valid.
if (minLength <= 0 || maxLength <= 0 || minLength > maxLength)
return null;

// Create a local array containing supported password characters
// grouped by types. You can remove character groups from this
// array, but doing so will weaken the password strength.
char[][] charGroups = new char[][]
{
PASSWORD_CHARS_LCASE.ToCharArray(),
PASSWORD_CHARS_UCASE.ToCharArray(),
PASSWORD_CHARS_NUMERIC.ToCharArray(),
PASSWORD_CHARS_SPECIAL.ToCharArray()
};

// Use this array to track the number of unused characters in each
// character group.
int[] charsLeftInGroup = new int[charGroups.Length];

// Initially, all characters in each group are not used.
for (int i = 0; i < charsLeftInGroup.Length; i++)
charsLeftInGroup[i] = charGroups[i].Length;

// Use this array to track (iterate through) unused character groups.
int[] leftGroupsOrder = new int[charGroups.Length];

// Initially, all character groups are not used.
for (int i = 0; i < leftGroupsOrder.Length; i++)
leftGroupsOrder[i] = i;

// Because we cannot use the default randomizer, which is based on the
// current time (it will produce the same "random" number within a
// second), we will use a random number generator to seed the
// randomizer.

// Use a 4-byte array to fill it with random bytes and convert it then
// to an integer value.
byte[] randomBytes = new byte[4];

// Generate 4 random bytes.
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
rng.GetBytes(randomBytes);

// Convert 4 bytes into a 32-bit integer value.
int seed = BitConverter.ToInt32(randomBytes, 0);

// Now, this is real randomization.
Random random = new Random(seed);

// This array will hold password characters.
char[] password = null;

// Allocate appropriate memory for the password.
if (minLength < maxLength)
password = new char[random.Next(minLength, maxLength + 1)];
else
password = new char[minLength];

// Index of the next character to be added to password.
int nextCharIdx;

// Index of the next character group to be processed.
int nextGroupIdx;

// Index which will be used to track not processed character groups.
int nextLeftGroupsOrderIdx;

// Index of the last non-processed character in a group.
int lastCharIdx;

// Index of the last non-processed group.
int lastLeftGroupsOrderIdx = leftGroupsOrder.Length - 1;

// Generate password characters one at a time.
for (int i = 0; i < password.Length; i++)
{
// If only one character group remained unprocessed, process it;
// otherwise, pick a random character group from the unprocessed
// group list. To allow a special character to appear in the
// first position, increment the second parameter of the Next
// function call by one, i.e. lastLeftGroupsOrderIdx + 1.
if (lastLeftGroupsOrderIdx == 0)
nextLeftGroupsOrderIdx = 0;
else
nextLeftGroupsOrderIdx = random.Next(0,
lastLeftGroupsOrderIdx);

// Get the actual index of the character group, from which we will
// pick the next character.
nextGroupIdx = leftGroupsOrder[nextLeftGroupsOrderIdx];

// Get the index of the last unprocessed characters in this group.
lastCharIdx = charsLeftInGroup[nextGroupIdx] - 1;

// If only one unprocessed character is left, pick it; otherwise,
// get a random character from the unused character list.
if (lastCharIdx == 0)
nextCharIdx = 0;
else
nextCharIdx = random.Next(0, lastCharIdx + 1);

// Add this character to the password.
password[i] = charGroups[nextGroupIdx][nextCharIdx];

// If we processed the last character in this group, start over.
if (lastCharIdx == 0)
charsLeftInGroup[nextGroupIdx] =
charGroups[nextGroupIdx].Length;
// There are more unprocessed characters left.
else
{
// Swap processed character with the last unprocessed character
// so that we don't pick it until we process all characters in
// this group.
if (lastCharIdx != nextCharIdx)
{
char temp = charGroups[nextGroupIdx][lastCharIdx];
charGroups[nextGroupIdx][lastCharIdx] =
charGroups[nextGroupIdx][nextCharIdx];
charGroups[nextGroupIdx][nextCharIdx] = temp;
}
// Decrement the number of unprocessed characters in
// this group.
charsLeftInGroup[nextGroupIdx]--;
}

// If we processed the last group, start all over.
if (lastLeftGroupsOrderIdx == 0)
lastLeftGroupsOrderIdx = leftGroupsOrder.Length - 1;
// There are more unprocessed groups left.
else
{
// Swap processed group with the last unprocessed group
// so that we don't pick it until we process all groups.
if (lastLeftGroupsOrderIdx != nextLeftGroupsOrderIdx)
{
int temp = leftGroupsOrder[lastLeftGroupsOrderIdx];
leftGroupsOrder[lastLeftGroupsOrderIdx] =
leftGroupsOrder[nextLeftGroupsOrderIdx];
leftGroupsOrder[nextLeftGroupsOrderIdx] = temp;
}
// Decrement the number of unprocessed groups.
lastLeftGroupsOrderIdx--;
}
}

// Convert password characters into a string and return the result.
return new string(password);
}
}
//Buna benzer bir kod user sayisi ile yaratılıp, her password bir user ile match edilip database'e atılacak
List<String> passwordList = new List<String>();
for (int i = 0; i < 100; i++)
{
string pass = RandomPassword.Generate(8, 10);
int index = passwordList.IndexOf(pass);
if (index == -1)
passwordList.Add(pass);
else
i--;
}
//Comment Password'u ekleme kodu

CREATE PROCEDURE [dbo].[InsertCommentPassword]
(
@intUserID int,
@strPassword BINARY(128)
)
AS
SET NOCOUNT OFF;

IF EXISTS(SELECT intUserID FROM tblCommentPasswords WHERE intUserID = @intUserID)
BEGIN

UPDATE tblCommentPasswords SET
strPassword = @strPassword
WHERE intUserID = @intUserID
END
ELSE
BEGIN
INSERT INTO tblCommentPasswords (
intUserID,
[strPassword]
)
VALUES
(
@intUserID,
@strPassword
)
END
//InsertCommentPassword DatabaseManager.cs
public static void InsertCommentPassword(int intUserID, string strPassword)
{
Rijndael oDecEnc = new Rijndael();
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("InsertCommentPassword");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "intUserID", intUserID);
DataBaseHelper.SetParameterValue(sqlCmd, "strPassword", oDecEnc.EncryptText(strPassword));
DataBaseHelper.ExecuteNonQuery(sqlCmd);
}
catch (Exception ex)
{
}
}
//O user'in comment passwordunu alma, text girilip yolla denildikten sonra bu check edilecek eger tokenlar uyar ise yukarıdaki insert comment kodu cagirilacak.
CREATE PROCEDURE [dbo].[GetCommentPasswordByUserID]
(
@intUserID int,
@strPassword BINARY(128)
)
AS
BEGIN
SET NOCOUNT OFF;
SELECT intUserID FROM tblCommentPasswords WHERE intUSerID = @intUserID AND lower(strPassword) = lower(@strPassword)
END
//GetCommentPasswordByUserID DatabaseManager.cs -> Bu null donmuyorsa eşleşme var comment insert edilebilir.
public static DataTable GetCommentPasswordByUserID(int intUserID, string strPassword)
{
Rijndael oDecEnc = new Rijndael();
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("GetCommentPasswordByUserID");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "intUserID", intUserID);
DataBaseHelper.SetParameterValue(sqlCmd, "strPassword", oDecEnc.EncryptText(strPassword));
return DataBaseHelper.ExecuteDataSet(sqlCmd).Tables[0];
}
catch (Exception ex)
{
return null;
}
}