List <TResult> ISpProvider.ExecutelstSql <TResult>(string spName, params object[] parameters)
{
DbConnection conn = _context.Database.GetDbConnection();
ConnectionState initialState = conn.State;
try
{
if (initialState != ConnectionState.Open)
{
conn.Open(); // open connection if not already open
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
try
{
//string query = "EXECUTE " + spName;
//for (var i = 0; i < parameters.Length; i++)
// query += parameters[i].GetParameterInfo() + ",";
//query = query.TrimEnd(',');
AddParameters(cmd, parameters);
cmd.CommandText = spName;
//DataTable dt = new DataTable();
//var dataAdapter = conn.CreateDataAdapter();
//dataAdapter.SelectCommand = cmd;
//dataAdapter.Fill(dt);
using (DbDataReader dr = cmd.ExecuteReader())
{
var PropertyType = typeof(TResult);
if (PropertyType.IsPrimitive || PropertyType.IsValueType || PropertyType == (typeof(string)))
{
return(dr.Cast <IDataRecord>().ToList().Select(s => s[0].TryCast <TResult>()).ToList());
}
List <TResult> _TResult = dr.Cast <IDataRecord>().CastTo <TResult>();
return(_TResult);
}
}
catch (Exception exception)
{
throw new DataException("Something went wrong, please contact system administrator.");
}
}
}
finally
{
if (initialState != ConnectionState.Open)
{
conn.Close(); // only close connection if not initially open
}
}
}
List <TResult> ISpProvider.ExecutelstQuery <TResult>(string query, params object[] parameters)
{
DbConnection conn = _context.Database.GetDbConnection();
ConnectionState initialState = conn.State;
try
{
if (initialState != ConnectionState.Open)
{
conn.Open(); // open connection if not already open
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 0;
try
{
AddParameters(cmd, parameters);
cmd.CommandText = query;
using (DbDataReader dr = cmd.ExecuteReader())
{
var PropertyType = typeof(TResult);
if (PropertyType.IsPrimitive || PropertyType.IsValueType || PropertyType == (typeof(string)))
{
return(dr.Cast <IDataRecord>().ToList().Select(s => s[0].TryCast <TResult>()).ToList());
}
List <TResult> _TResult = dr.Cast <IDataRecord>().CastTo <TResult>();
return(_TResult);
}
}
catch (Exception exception)
{
throw new DataException("Something went wrong, please contact system administrator.");
}
}
}
finally
{
if (initialState != ConnectionState.Open)
{
conn.Close(); // only close connection if not initially open
}
}
}
public List <List <IDataRecord> > ExecuteMultilstSql(string spName, params object[] parameters)
{
var entityConnection = (EntityConnection)_context.Connection;
DbConnection conn = entityConnection.StoreConnection;// _context.Connection;
ConnectionState initialState = conn.State;
try
{
if (initialState != ConnectionState.Open)
{
conn.Open(); // open connection if not already open
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandTimeout = 100000;
cmd.CommandType = CommandType.Text;
try
{
string query = "EXECUTE " + spName;
if (parameters != null)
{
for (var i = 0; i < parameters.Length; i++)
{
query += parameters[i].GetParameterInfo() + ",";
}
}
query = query.TrimEnd(',');
cmd.CommandText = query;
DbDataReader dr = cmd.ExecuteReader();
List <List <IDataRecord> > _TResult = new List <List <IDataRecord> >();
do
{
_TResult.Add(dr.Cast <IDataRecord>().ToList());
}while (dr.NextResult());
return(_TResult);
}
catch (Exception exception)
{
throw new DataException(exception.Message);
}
}
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close(); // only close connection if not initially open
}
conn.Dispose();
}
}
TResult ISpProvider.ExecuteSql <TResult>(string spName, params object[] parameters)
{
DbConnection conn = _context.Database.GetDbConnection();
ConnectionState initialState = conn.State;
try
{
if (initialState != ConnectionState.Open)
{
conn.Open(); // open connection if not already open
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
try
{
AddParameters(cmd, parameters);
cmd.CommandText = spName;
using (DbDataReader dr = cmd.ExecuteReader())
{
List <TResult> _TResult = dr.Cast <IDataRecord>().CastTo <TResult>();
return(_TResult.FirstOrDefault());
}
}
catch (Exception exception)
{
throw new DataException("Something went wrong, please contact system administrator.");
}
}
}
finally
{
if (initialState != ConnectionState.Open)
{
conn.Close(); // only close connection if not initially open
}
}
}
List <string> ISpProvider.ExecutelstQuery(string query, params object[] parameters)
{
DbConnection conn = _context.Database.GetDbConnection();
ConnectionState initialState = conn.State;
try
{
if (initialState != ConnectionState.Open)
{
conn.Open(); // open connection if not already open
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 0;
try
{
AddParameters(cmd, parameters);
cmd.CommandText = query;
using (DbDataReader dr = cmd.ExecuteReader())
{
return(dr.Cast <IDataRecord>().ToList().Select(s => Convert.ToString(s[0])).ToList());
}
}
catch (Exception exception)
{
throw new DataException("Something went wrong, please contact system administrator.");
}
}
}
finally
{
if (initialState != ConnectionState.Open)
{
conn.Close(); // only close connection if not initially open
}
}
}
public List <T> MapResultsToObject(DbDataReader reader)
{
List <T> objects = new List <T>();
PropertyInfo[] _propertyInfo = typeof(T).GetProperties();
reader.Cast <IDataRecord>().ToList().ForEach
(i =>
{
T obj = new T();
List <string> lstProps = Enumerable.Range(1, i.FieldCount).Select((e, index) => i.GetName(index).ToString().ToLower()).ToList();
foreach (PropertyInfo prop in _propertyInfo)
{
if ((lstProps.Any(a => a == prop.Name.ToLower())))
{
prop.SetValue(obj, i[prop.Name] != System.DBNull.Value ? i[prop.Name] : null, null);
}
}
objects.Add(obj);
}
);
return(objects);
}
List <TResult> ISpProvider.ExecutelstQuery <TResult>(string query, Dictionary <string, object> parameters)
{
DbConnection conn = _context.Database.GetDbConnection();
ConnectionState initialState = conn.State;
try
{
if (initialState != ConnectionState.Open)
{
conn.Open(); // open connection if not already open
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
try
{
AddParameters(cmd, parameters);
cmd.CommandText = query;
using (DbDataReader dr = cmd.ExecuteReader())
{
List <TResult> _TResult = dr.Cast <IDataRecord>().CastTo <TResult>();
return(_TResult);
}
}
catch (Exception exception)
{
throw new DataException("Something went wrong, please contact system administrator.");
}
}
}
finally
{
if (initialState != ConnectionState.Open)
{
conn.Close(); // only close connection if not initially open
}
}
}
protected void Button3_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
try
{
string path = string.Concat(Server.MapPath("~/Uploaded Folder/" + FileUpload1.FileName));
FileUpload1.SaveAs(path);
//String path1 = string.Concat(Server.MapPath("~/Uploaded Folder1/" + FileUpload1.FileName));
//FileUpload1.SaveAs(path1);
filename = FileUpload1.FileName;
// Connection String to Excel Workbook
string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = excelConnectionString;
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
DbDataReader dr1 = command.ExecuteReader();
int numofrow = dr1.Cast <object>().Count();
dr1.Close();
DbDataReader dr = command.ExecuteReader();
// String[] exam_name = new String[10];
//int numofcoloum = 0;
coloum_name[0] = dr.GetName(0).ToString();
//ViewState["0"] = coloum_name[0];
excel = new String[numofrow, dr.FieldCount];
for (int col = 1; col < dr.FieldCount; col++)
{
coloum_name[col] = dr.GetName(col).ToString();
// ViewState[col + ""] = coloum_name[col];
//DropDownList1.Items.Add(new ListItem(coloum_name[col], col + ""));
//add(new ListItem(coloum_name[col], coloum_name[col]));
// Gets the column name
// Console.Write(dr.GetFieldType(col).ToString()); // Gets the column type
//Console.Write(dr.GetDataTypeName(col).ToString()); // Gets the column database type
}
n = 0;
while (dr.Read())
{
for (int col = 0; col < dr.FieldCount; col++)
{
excel[n, col] = dr[coloum_name[col]].ToString();
}
n++;
}
//ViewState["n"] = n;
int fieldcount = dr.FieldCount;
int attid = 0;
int exist_att = 0;
//int temp = 0;
for (int j = 1; j < fieldcount; j++)
{
String search = "SELECT COUNT(*) from fac_attendence where courseid='" + Session["courseid"] + "' and groupid='" + Session["groupid"] + "' and facid='" + Session["id"] + "' and date='" + DateTime.Parse(coloum_name[j]) + "'";
SqlCommand cmd = new SqlCommand(search, conn);
int temp = (int)cmd.ExecuteScalar();
//Response.Write(j);
if (temp == 0)
{
SqlCommand cmd1;
cmd1 = new SqlCommand("insert into fac_attendence values('" + Session["id"] + "','" + Session["courseid"] + "','" + Session["groupid"] + "','" + DateTime.Parse(coloum_name[j]) + "')", conn);
cmd1.ExecuteScalar();
cmd1 = new SqlCommand("select attid from fac_attendence where facid ='" + Session["id"] + "' and courseid= '" + Session["courseid"] + "' and groupid ='" + Session["groupid"] + "' and date='" + DateTime.Parse(coloum_name[j]) + "'", conn);
attid = (int)cmd1.ExecuteScalar();
exist_att = 0;
//Response.Write(j);
}
else
{
SqlCommand cmd1;
cmd1 = new SqlCommand("select attid from fac_attendence where facid ='" + Session["id"] + "' and courseid= '" + Session["courseid"] + "' and groupid ='" + Session["groupid"] + "' and date='" + DateTime.Parse(coloum_name[j]) + "'", conn);
attid = (int)cmd1.ExecuteScalar();
exist_att = 1;
//Response.Write(j);
}
for (int i = 0; i < n; i++)
{
// ViewState[i + "," + j] = excel[i, j];
if (exist_att == 0)
{
SqlCommand cmd2;
cmd2 = new SqlCommand("insert into attendence values('" + excel[i, 0] + "','" + excel[i, j] + "','" + attid + "')", conn1);
cmd2.ExecuteScalar();
//Response.Write(i);
}
// Response.Write(excel[i, j]);
//Response.Write(i + "," + j + "<br/>");
}
}
//ViewState["array"] = excel;
// SQL Server Connection String
//string sqlConnectionString = @"Data Source=data";
// Bulk Copy to SQL Server
//SqlBulkCopy bulkInsert = new SqlBulkCopy(conn);
//bulkInsert.DestinationTableName = "Student_Record";
//bulkInsert.WriteToServer(dr);
//Label1.Text = "Ho Gaya";
dr.Close();
connection.Close();
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
//conn.Close();
}
}
protected override IEnumerable <IDataRecord> GetRows()
{
return(reader.Cast <IDataRecord>());
}
