public IEnumerable <FileEntity> GetFileDetails(int UserId)
{
List <FileEntity> FilesCollection = new List <FileEntity>();
SqlCommand cmd;
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
con.Open();
if (UserId == 0)//is admin
{
cmd = new SqlCommand("select * from inz_file order by 1 desc", con);
}
else
{
cmd = new SqlCommand("select * from inz_file order by 1 desc", con);
cmd.Parameters.AddWithValue("@UserID", UserId);
}
cmd.CommandType = CommandType.Text;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
FileEntity _File = new FileEntity();
_File.FileId = (int)rdr["FileID"];
_File.Name = rdr["FileName"].ToString();
_File.Description = rdr["Description"].ToString();
_File.FileType = rdr["FileType"].ToString();
_File.Document = rdr["DocumentName"].ToString();
FilesCollection.Add(_File);
}
}
return(FilesCollection);
}
public static bool AuthenticateUser(string username, string password)
{
// ConfigurationManager class is in System.Configuration namespace
string CS = ConnectSQL.GetConnectionString();
// SqlConnection is in System.Data.SqlClient namespace
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spAuthenticateUser", con);
cmd.CommandType = CommandType.StoredProcedure;
// FormsAuthentication is in System.Web.Security
string EncryptedPassword = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "SHA1");
// SqlParameter is in System.Data namespace
SqlParameter paramUsername = new SqlParameter("@UserName", username);
SqlParameter paramPassword = new SqlParameter("@Password", EncryptedPassword);//we are not using authentiacated password ,use EncryptedPassword to use authenticated password
cmd.Parameters.Add(paramUsername);
cmd.Parameters.Add(paramPassword);
con.Open();
int ReturnCode = (int)cmd.ExecuteScalar();
return(ReturnCode == 1);
}
}
public static List <User> GetAllusers()
{
List <User> userlist = new List <User>();
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("select UserID,Username,Rowstatus from inz_USERS", con);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
User user = new User();
user.UserID = (int)rdr["UserID"];
user.Username = rdr["Username"].ToString();
user.Rowstatus = Convert.ToChar(rdr["Rowstatus"]);
userlist.Add(user);
}
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
return(userlist);
}
public static bool UserAccessPermission(int fileId, int UserID)
{
string Access = "D";
try
{
SqlCommand cmd = ConnectSQL.ExecuteCommand("select [Status] from inzFileAccess where USERID=@UserId and FileId=@FileId");
cmd.Parameters.AddWithValue("@FileId", fileId);
cmd.Parameters.AddWithValue("@UserId", UserID);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
Access = rdr["Status"].ToString();
}
}
return(Access == "A");
}
catch
{
return(false);
}
}
public static List <int> GetFileCount(int UserId)
{
List <int> Countlist = new List <int>();
try
{
SqlCommand cmd = ConnectSQL.ExecuteCommand("select [Count] from inz_file_status Where UserId=@UserId");
cmd.Parameters.AddWithValue("@UserID", UserId);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Countlist.Add((int)(rdr["Count"]));
}
if (Countlist.Count == 0)
{
Countlist.Add(0);
}
return(Countlist);
}
catch
{
Countlist.Add(0);
return(Countlist);
}
}
public static SqlCommand ExecuteCommand(string Query)
{
SqlCommand cmd = new SqlCommand(Query, ConnectSQL.SqlConnect());
cmd.CommandType = CommandType.Text;
return(cmd);
}
public static string GetFileName(int FileId)
{
string FileName = "NIL";
try
{
SqlCommand cmd = ConnectSQL.ExecuteCommand("select [DocumentName] from inz_file Where FileId=@FileId");
cmd.Parameters.AddWithValue("@FileId", FileId);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
FileName = rdr["DocumentName"].ToString();
}
}
return(FileName);
}
catch
{
return(FileName);
}
}
public static bool SaveFileToDatabase(FileEntity file)
{
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
int FileId = 0;
SqlCommand cmd = new SqlCommand("spInsertFileDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FileName", file.Name);
cmd.Parameters.AddWithValue("@description", file.Description);
cmd.Parameters.AddWithValue("@Key", file.Key);
cmd.Parameters.AddWithValue("@UserId", file.UserId);
cmd.Parameters.AddWithValue("@IsEncrypted", 1);
cmd.Parameters.AddWithValue("@FileType", file.FileType);
cmd.Parameters.AddWithValue("@Document", file.Document);
//FileId = (int)cmd.ExecuteScalar();
return(InsertInitalApproveForCurrentUser(FileId));
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
}
public static bool UserRegister(User _user)
{
string CS = ConnectSQL.GetConnectionString();
// SqlConnection is in System.Data.SqlClient namespace
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spRegisterUser", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter username = new SqlParameter("@UserName", _user.Username);
// FormsAuthentication calss is in System.Web.Security namespace
string encryptedPassword = FormsAuthentication.
HashPasswordForStoringInConfigFile(_user.Password, "SHA1");
SqlParameter password = new SqlParameter("@Password", encryptedPassword);
SqlParameter email = new SqlParameter("@Email", _user.EmailID);
cmd.Parameters.Add(username);
cmd.Parameters.Add(password);
cmd.Parameters.Add(email);
con.Open();
int ReturnCode = (int)cmd.ExecuteScalar();
return(ReturnCode == 1);
}
}
public static SqlCommand ExecuteProcedure(string Query)
{
SqlCommand cmd = new SqlCommand(Query, ConnectSQL.SqlConnect());
cmd.CommandType = CommandType.StoredProcedure;
return(cmd);
}
public static bool DeleteFile(int fileId)
{
try
{
SqlCommand cmd = ConnectSQL.ExecuteCommand("Delete from inz_file where FileID=@FileId Delete from inz_file_status where FileID=@FileId");
cmd.Parameters.AddWithValue("@FileID", fileId);
cmd.ExecuteNonQuery();
return(true);
}
catch
{
return(false);
}
}
public static bool UpdateFileAccessCount(int fileId, int UserID)
{
try
{
SqlCommand cmd = ConnectSQL.ExecuteProcedure("spUpdateFileStatus");
cmd.Parameters.AddWithValue("@FileID", fileId);
cmd.Parameters.AddWithValue("@UserID", UserID);
cmd.ExecuteNonQuery();
return(true);
}
catch
{
return(false);
}
}
public static List <User> UserAllUser(int FileId)
{
List <User> userlist = new List <User>();
try
{
SqlCommand cmd = ConnectSQL.ExecuteProcedure("SpFileAccess");
cmd.Parameters.AddWithValue("@FileId", FileId);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
User user = new User();
user.UserID = (int)rdr["UserID"];
user.Username = rdr["Username"].ToString();
user.Rowstatus = Convert.ToChar(rdr["Rowstatus"]);
userlist.Add(user);
}
}
else
{
rdr.Dispose();
cmd.Dispose();
cmd = ConnectSQL.ExecuteCommand("select UserID,Username,Rowstatus from inz_USERS");
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
User user = new User();
user.UserID = (int)rdr["UserID"];
user.Username = rdr["Username"].ToString();
user.Rowstatus = Convert.ToChar(rdr["Rowstatus"]);
userlist.Add(user);
}
}
}
return(userlist);
}
catch
{
return(userlist);
}
}
public static bool SaveFileAccess(int UserId, int FileId, string Status)
{
try
{
SqlCommand cmd = ConnectSQL.ExecuteProcedure("spUpdateFileAccess");
cmd.Parameters.AddWithValue("@FileId", FileId);
cmd.Parameters.AddWithValue("@UserId", UserId);
cmd.Parameters.AddWithValue("@Status", Status);
cmd.ExecuteNonQuery();
return(true);
}
catch
{
return(false);
}
}
public static bool SaveFileToDatabase(FileEntity file)
{
try
{
SqlCommand cmd = ConnectSQL.ExecuteProcedure("spInsertFileDetails");
cmd.Parameters.AddWithValue("@FileName", file.Name);
cmd.Parameters.AddWithValue("@description", file.Description);
cmd.Parameters.AddWithValue("@Key", file.Key);
cmd.Parameters.AddWithValue("@UserId", file.UserId);
cmd.Parameters.AddWithValue("@IsEncrypted", 1);
cmd.Parameters.AddWithValue("@FileType", file.FileType);
cmd.Parameters.AddWithValue("@Document", file.Document);
cmd.ExecuteNonQuery();
return(true);
}
catch
{
return(false);
}
}
public static int GetUserID(string USername)
{
int Userid = 0;
try
{
SqlCommand cmd = ConnectSQL.ExecuteCommand("select UserId from inz_USERS Where Username=@Username");
cmd.Parameters.AddWithValue("@Username", USername);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Userid = (int)rdr["UserId"];
}
return(Userid);
}
catch
{
return(Userid);
}
}
public IEnumerable <FileEntity> GetAllFiles()
{
List <FileEntity> FilesCollection = new List <FileEntity>();
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
con.Open();
SqlCommand cmd = new SqlCommand("select * from inz_file", con);
cmd.CommandType = CommandType.Text;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
FileEntity _File = new FileEntity();
_File.FileId = (int)rdr["FileID"];
_File.Name = rdr["FileName"].ToString();
_File.Description = rdr["Description"].ToString();
_File.FileType = rdr["FileType"].ToString();
_File.Document = rdr["DocumentName"].ToString();
FilesCollection.Add(_File);
}
}
return(FilesCollection);
}
