using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace Mesoft
{ namespace DatabaseActivities
{ public class SqlServerActiviteis
{ #region "Variable Declration"
private SqlConnection con = new SqlConnection();
private SqlDataReader rdr;
private string ConnectionString = "";
#endregion
#region "Constructores"
public SqlServerActiviteis(String ConnectionString)
{ this.ConnectionString = ConnectionString;
}
#endregion
#region "Methods"
public Boolean OpenConnection()
{ try
{ con.ConnectionString = ConnectionString;
con.Open();
return true;
}
catch (Exception ex)
{ throw ex;
}
}
public Boolean CloseConnection()
{ try
{ if (con.State.ToString().Equals("Open")) { con.Close();
return true;
}
else
{ throw new Exception("Connection is already close."); }
}
catch (Exception ex
{ throw ex;
}
}
private SqlCommand CreateCommand(string comstr, string ImagePath)
{ try
{ byte[] imageSampleData = null;
SqlCommand com = new SqlCommand(comstr, con);
imageSampleData = ReadImageToBytes(ImagePath); //From Here The Method For Conver Image To Byte Can Be Call
com.Parameters.Add(new SqlParameter("@Image", (object)imageSampleData));//Here Image Can Be Stored Into Database com.ExecuteNonQuery();
com.Parameters.Clear();
return com;
}
catch (Exception ex)
{ throw ex;
}
}
private SqlCommand CreateCommand(string ComStr)
{ try
{ SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandText = ComStr;
com.ExecuteNonQuery();
return com;
}
catch (Exception ex)
{ throw ex;
}
}
public SqlDataAdapter CollectTable(string Query)
{ try
{ SqlDataAdapter adp = new SqlDataAdapter(Query, con);
return adp;
}
catch (Exception ex)
{ throw ex;
}
}
public DataTable FillData(string Query)
{ try
{ DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(Query, con);
adp.Fill(dt);
return dt;
}
catch (Exception ex)
{ throw ex;
}
}
public DataSet FillData(string Query, string TableName)
{ try
{ DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(Query, con);
adp.Fill(ds, TableName);
return ds;
}
catch (Exception ex)
{ throw ex;
}
}
public SqlDataReader ReadData(string comstr)
{ try
{ SqlCommand com = new SqlCommand(comstr, con);
rdr = com.ExecuteReader();
return rdr;
}
catch (Exception ex)
{ throw ex;
}
}
public object GetScalar(string Query)
{ try
{ SqlCommand com = new SqlCommand(Query, con);
return com.ExecuteScalar();
}
catch (Exception ex)
{ throw ex;
}
}
public Boolean ExecutQuery(string comstr)
{ try
{ CreateCommand(comstr);
return true;
}
catch (Exception ex)
{ throw ex;
}
}
public Boolean ExecutQuery(string comstr, string ImagePath)
{ try
{ CreateCommand(comstr, ImagePath);
return true;
}
catch (Exception ex)
{ throw ex;
}
}
byte[] ReadImageToBytes(String sPath) ///This Metod Conver the Photo IN Binary
{ byte[] data = null;
FileInfo finfo = new FileInfo(sPath);
long numBytes = 0;
numBytes = finfo.Length;
FileStream fstream = new FileStream(sPath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fstream);
data = br.ReadBytes((int)numBytes);
fstream.Close();
return data;
}
public Boolean Login(string UserId, string Password, string TableName, string WhereUserIdField, string WherePasswordField)
{ try
{ if (UserId != "")
{ if (Password != "")
{
object temp = null;
temp = GetScalar("Select count(*) from " + TableName + " Where " + WhereUserIdField + "='" + UserId + "' AND " + WherePasswordField + "='" + Password + "'");
if (temp.ToString() == "1")
{ return true;
}
else
{ throw new Exception("No User Avilable."); }
}
else
{ throw new Exception("Please Enter Password."); }
}
else
{ throw new Exception("Please Enter Userid"); }
}
catch (Exception ex)
{ throw ex;
}
}
#endregion
}
}
}
