Notes

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DatabaseManager
/// </summary>
public class DatabaseManager
{
public static SqlDatabase DataBaseHelper;
public static ParameterCache ParameterCacheHelper;
private static DatabaseManager instance = new DatabaseManager();

public static string ConnectionString
{
get { return System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); }
}
public DatabaseManager()
{
DataBaseHelper = new SqlDatabase(ConnectionString);
ParameterCacheHelper = new ParameterCache();
}
public static int AuthenticateUser(string sUserName, string sPassword)
{
Rijndael oDecEnc = new Rijndael();
int sReturnValue = 0;
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("AuthenticateUser");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "strUserName", sUserName);
DataBaseHelper.SetParameterValue(sqlCmd, "strPassword", oDecEnc.EncryptText(sPassword));
DataBaseHelper.ExecuteNonQuery(sqlCmd);
sReturnValue = Convert.ToInt32(DataBaseHelper.GetParameterValue(sqlCmd, "@intUserID").ToString());
}
catch (Exception ex)
{
}

return sReturnValue;
}
public static bool AddEmail(string sFrom, string sTo, string sCC, string sSubject, string sBody)
{
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("AddEmail");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "From", sFrom);
DataBaseHelper.SetParameterValue(sqlCmd, "To", sTo);
DataBaseHelper.SetParameterValue(sqlCmd, "CC", sCC);
DataBaseHelper.SetParameterValue(sqlCmd, "Subject", sSubject);
DataBaseHelper.SetParameterValue(sqlCmd, "Body", sBody);

DataBaseHelper.ExecuteNonQuery(sqlCmd);

return true;
}
catch (Exception ex)
{
return false;
}
}
public static int AddUser(string sName, string sSurname, string sUserName, string sEmail, string sPassword, int iRoleID, string sTelNo)
{
Rijndael oDecEnc = new Rijndael();
int sReturnValue = 0;
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("AddUser");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "strName", sName);
DataBaseHelper.SetParameterValue(sqlCmd, "strSurname", sSurname);
DataBaseHelper.SetParameterValue(sqlCmd, "strUsername", sUserName);
DataBaseHelper.SetParameterValue(sqlCmd, "strEmail", sEmail);
DataBaseHelper.SetParameterValue(sqlCmd, "sTelNo", sTelNo);
DataBaseHelper.SetParameterValue(sqlCmd, "intRoleID", iRoleID);
DataBaseHelper.SetParameterValue(sqlCmd, "strPassword", oDecEnc.EncryptText(sPassword));
DataBaseHelper.ExecuteNonQuery(sqlCmd);
sReturnValue = Convert.ToInt32(DataBaseHelper.GetParameterValue(sqlCmd, "@intUserID").ToString());
}
catch (Exception ex)
{
}

return sReturnValue;
}
public static int AddUserInfo(int intUserID, int intSchoolID, int intDepartmentID, int intFacultyID, int intStatusID, int isBookBuyer, int isPDFBuyer, int isPhotoBuyer)
{
int sReturnValue = 0;
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("AddUser");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "intUserID", intUserID);
DataBaseHelper.SetParameterValue(sqlCmd, "intSchoolID", intSchoolID);
DataBaseHelper.SetParameterValue(sqlCmd, "intDepartmentID", intDepartmentID);
DataBaseHelper.SetParameterValue(sqlCmd, "intFacultyID", intFacultyID);
DataBaseHelper.SetParameterValue(sqlCmd, "intStatusID", intStatusID);
DataBaseHelper.SetParameterValue(sqlCmd, "isBookBuyer", isBookBuyer);
DataBaseHelper.SetParameterValue(sqlCmd, "isPDFBuyer", isPDFBuyer);
DataBaseHelper.SetParameterValue(sqlCmd, "isPhotoBuyer", isPhotoBuyer);
DataBaseHelper.ExecuteNonQuery(sqlCmd);
sReturnValue = Convert.ToInt32(DataBaseHelper.GetParameterValue(sqlCmd, "@isAdded").ToString());
}
catch (Exception ex)
{
}

return sReturnValue;
}
public static void AddSponsor(string strSponsorName, string strSponsorURL, string strImgURL)
{
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("AddSponsor");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "strSponsorName", strSponsorName);
DataBaseHelper.SetParameterValue(sqlCmd, "strSponsorURL", strSponsorURL);
DataBaseHelper.SetParameterValue(sqlCmd, "strImgURL", strImgURL);
DataBaseHelper.ExecuteNonQuery(sqlCmd);
}
catch (Exception ex)
{
}
}
public static DataTable GetSponsors()
{
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("GetSponsors");
return DataBaseHelper.ExecuteDataSet(sqlCmd).Tables[0];
}
catch (Exception ex)
{
return null;
}
}
public static DataTable GetLookupsByLookupTypeID(int LookupTypeID)
{
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("GetLookupsByLookupTypeID");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "LookupTypeID", LookupTypeID);
return DataBaseHelper.ExecuteDataSet(sqlCmd).Tables[0];
}
catch (Exception ex)
{
return null;
}
}
public static DataTable GetUserInfo(int intUserID)
{
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("GetUserInfo");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "intUserID", intUserID);
return DataBaseHelper.ExecuteDataSet(sqlCmd).Tables[0];
}
catch (Exception ex)
{
return null;
}
}
public static void SendPassword(string sUserName)
{
Rijndael oDecEnc = new Rijndael();
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("GetUserPassword");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "strUserName", sUserName);
RefCountingDataReader sqlDr = (RefCountingDataReader)DataBaseHelper.ExecuteReader(sqlCmd);
SqlDataReader dr = (SqlDataReader)sqlDr.InnerReader;
if (dr.Read())
{
string sPassword = oDecEnc.DecryptText((byte[])(dr.GetSqlBinary(1)));
string sTo = dr[0].ToString();
SMTPMail.SendMail("Şifre", "<p>2017 yıllık sistemine hoşgeldin ula! Şifren burda ver arkadaşına yazsın gelsin sana oku</p><p>hadi hayırlı işler</p><table><tr border=2><td>Kullanıcı Adı</td><td><b>" + sUserName + "</b></td></tr><td>Şifreniz</td><td><b>" + sPassword + "</b></td><tr></tr></table>", sTo);
}
}
catch (Exception ex)
{
}
}
public static void ResetUser(string strUserName)
{
try
{
DbCommand sqlCmd = DataBaseHelper.GetStoredProcCommand("ResetUser");
ParameterCacheHelper.SetParameters(sqlCmd, DataBaseHelper);
DataBaseHelper.SetParameterValue(sqlCmd, "strUserName", strUserName);
DataBaseHelper.ExecuteNonQuery(sqlCmd);

SendPassword(strUserName);
}
catch (Exception ex)
{
}
}

}