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>()); }