protected void showtb_Click(object sender, EventArgs e) { conn = new SqlConnection(connstr); cmd = new SqlCommand("select * from Registration", conn); System.Data.DataTable dt = new System.Data.DataTable(); conn.Open(); rdr = cmd.ExecuteReader(); if (rdr.HasRows) { dt.Load(rdr); grdMemberView.DataSource = dt; grdMemberView.DataBind(); } else { dt.Load(rdr); grdMemberView.DataSource = dt; grdMemberView.EmptyDataText = "No members to show"; grdMemberView.DataBind(); } conn.Close(); }
public System.Data.DataTable UserDataConnections(string name, int legajo, int tipo, int nroSucursal) { SqlConnection connection; SqlCommand command; string sql = ""; SqlDataReader dataReader; System.Data.DataTable dt = new System.Data.DataTable(); sql = StringQuery(name, legajo, tipo, nroSucursal); connection = new SqlConnection(connectionString); try { connection.Open(); command = new SqlCommand(sql, connection); dataReader = command.ExecuteReader(); /*while (dataReader.Read()) * {*/ dt.Load(dataReader); //} dataReader.Close(); command.Dispose(); connection.Close(); } catch (Exception ex) { dt = null; } return(dt); }
public void Init() { var connectionString = new SQLiteConnectionStringBuilder { DataSource = $"{SqlFileName}" }; try { using (var con = new SQLiteConnection(connectionString.ToString())) { con.Open(); var cmd = con.CreateCommand(); cmd.CommandText = InitQuery; var reader = cmd.ExecuteReader(); //table = reader.GetSchemaTable().DefaultView; var datatable = new System.Data.DataTable(); datatable.Load(reader); table = datatable.DefaultView; con.Close(); } } catch (Exception e) { System.Windows.Forms.MessageBox.Show(e.Message); } }
} //OpenConnect(string connName) /// <summary> /// 执行查询返回DataTable /// </summary> /// <param name="sql">Sql语句</param> /// <returns>成功返回DataTable,失败则返回 null</returns> public static System.Data.DataTable ExecuteQueryToDataTable(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param = null) { System.Data.DataTable dt = new System.Data.DataTable(); System.Data.IDataReader reader = ExecuteQuery(sql, Conn, param); dt.Load(reader); return(dt); } //ExecuteQueryToDataTable(string sql)
/// <summary> Small helper method querying and returning a textual representation of the SQLite execution plan. </summary> public static string GetSQLiteExecutionPlan(this System.Data.IDbCommand cmd) { using (var qpCmd = cmd.Connection.CreateCommand()) { qpCmd.CommandText = "EXPLAIN QUERY PLAN " + cmd.CommandText; qpCmd.Connection = cmd.Connection; qpCmd.CommandType = cmd.CommandType; qpCmd.Transaction = cmd.Transaction; foreach (System.Data.IDataParameter p in cmd.Parameters) { qpCmd.AddParameter(p.Value, p.ParameterName); } System.Data.DataTable dt = new System.Data.DataTable(); using (var rd = qpCmd.ExecuteReader()) dt.Load(rd); string plan = String.Join("\t", dt.Columns.Cast <System.Data.DataColumn>().Select(c => c.ColumnName).ToArray()) + "\n" + string.Join("\n", dt.Rows.Cast <System.Data.DataRow>().Select(r => String.Join("\t", r.ItemArray.Select(o => o == null ? "" : o.ToString()).ToArray())).ToArray()); return(plan); } }
public ProductImage GetProductImage(string productCode) { var productimage = db.ExecuteSprocAccessor(DBRoutine.SELECTPRODUCTIMAGE, MapBuilder <ProductImage> .BuildAllProperties(), productCode).FirstOrDefault(); byte[] photo; if (productimage != null) { var searchcommand = db.GetStoredProcCommand(DBRoutine.SELECTPRODUCTIMAGE); db.AddInParameter(searchcommand, "ProductCode", System.Data.DbType.String, productCode); var reader = (System.Data.IDataReader)db.ExecuteReader(searchcommand); System.Data.DataTable dt = new System.Data.DataTable(); dt.Load(reader); photo = (byte[])dt.Rows[0]["ProductImg"]; productimage.ProductImg = photo; } return(productimage); }
private static async System.Threading.Tasks.Task <System.Data.DataTable> fetchDataTable(NamedQuery qry, string ConnectionString) { // Create a connection, open it and create a command on the connection try { System.Data.DataTable dt = new System.Data.DataTable(qry.TableName); using (SqlConnection connection = new SqlConnection(ConnectionString)) { await connection.OpenAsync().ConfigureAwait(false); System.Diagnostics.Debug.WriteLine("Connection Opened ... " + qry.TableName); using (SqlCommand command = new SqlCommand(qry.SQL, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { System.Diagnostics.Debug.WriteLine("Query Executed ... " + qry.TableName); dt.Load(reader); System.Diagnostics.Debug.WriteLine(string.Format("Record Count '{0}' ... {1}", dt.Rows.Count, qry.TableName)); return(dt); } } } } catch (Exception ex) { System.Diagnostics.Debug.WriteLine("Exception Raised ... " + qry.TableName); System.Diagnostics.Debug.WriteLine(ex.Message); return(new System.Data.DataTable(qry.TableName)); } }
public Boolean Get_Job_Details(Int32 jobId) { Boolean isSuccessful = true; ErrorMessage = string.Empty; try { String strSQL = "SELECT * FROM JobDetails "; strSQL = strSQL + "INNER JOIN WorkOrderDetails ON JobDetails.WorkOrderDetailId = WorkOrderDetails.WorkorderDetailId "; strSQL = strSQL + "INNER JOIN Parts ON WorkOrderDetails.PartId = Parts.PartId "; strSQL = strSQL + "WHERE JobDetails.Jobid = " + jobId.ToString(); System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection); System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader(); if (rdrGet.HasRows == true) { myJobDetails.Clear(); myJobDetails.Load(rdrGet); } rdrGet.Close(); cmdGet.Dispose(); } catch (Exception ex) { isSuccessful = false; ErrorMessage = "Get Job Details - " + ex.Message; } return(isSuccessful); }
public Boolean Get_Utilisation_Record(Int32 lineId, DateTime workDay) { Boolean isSuccessful = true; ErrorMessage = string.Empty; try { String strSQL = "SELECT * FROM ProductionLineUtilisation WHERE LineUtilisationLineId = " + lineId.ToString() + " AND LineUtilisationWorkDate = CONVERT(datetime,'" + workDay.ToShortDateString() + " 00:00:00', 103)"; System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection); System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader(); if (rdrGet.HasRows == true) { UtilisationRecord.Clear(); UtilisationRecord.Load(rdrGet); isSuccessful = Gather_Utilisation_Record(); } else { isSuccessful = false; ErrorMessage = "Get Production Line Utilisation Record - Not Found !"; } rdrGet.Close(); cmdGet.Dispose(); } catch (Exception ex) { isSuccessful = false; ErrorMessage = "Get Production Line Utilisation Record - " + ex.Message; } return(isSuccessful); }
private System.Data.DataTable GetData(int city, string lang) { try { SqlConnection myConnection = new SqlConnection("user id=Uib;" + "password=Uib;server=localhost;" + "Trusted_Connection=yes;" + "database=Uib; " + "connection timeout=30"); myConnection.Open(); var command = new SqlCommand("GET_HOTELS_BY_CITY", myConnection); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@city", city); command.Parameters.AddWithValue("@lang", lang); SqlDataReader reader = command.ExecuteReader(); var dataTable = new System.Data.DataTable(); dataTable.Load(reader); reader.Close(); myConnection.Close(); return dataTable; } catch (Exception e) { Console.WriteLine(e.ToString()); } return null; }
public void SQLServerExecuteReader() { var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString); var scriptBuilder = conn.GetScriptBuild(); var cliente = new Cliente() { Nome = "Miranda" }; var createTableScript = scriptBuilder.GetCreateTableCommand <Cliente>(); conn.Execute(createTableScript); var id = conn.InsertReturningId <Cliente>(cliente); cliente.Id = id; cliente.Nome = "Moisés Miranda"; conn.Insert <Cliente>(cliente); var reader = scriptBuilder.ExecuteReader("select * from Cliente", conn); var data = new System.Data.DataTable(); data.Load(reader); conn.Execute("drop table [Cliente]"); Assert.AreEqual(2, data.Rows.Count); }
public void PopulateEmployerGroupDDL() { dbProcedures db = new dbProcedures(); try { System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("SELECT DISTINCT EmployerGroup FROM tbl_Members ORDER BY EmployerGroup", new db().SqlConnection); sqlCmd.CommandType = System.Data.CommandType.Text; System.Data.DataTable loadReaderdata = new System.Data.DataTable(); loadReaderdata.Load(sqlCmd.ExecuteReader()); ddlEmployerGroup.DataSource = loadReaderdata; ddlEmployerGroup.DataTextField = "EmployerGroup"; ddlEmployerGroup.DataValueField = "EmployerGroup"; ddlEmployerGroup.DataBind(); ddlEmployerGroup.Items.Insert(0, new ListItem("", "")); ddlEmployerGroup.SelectedIndex = 0; } catch (Exception ex) { db.LogAction("", "Error", "Error loading Members UI page. " + ex.Message.ToString()); } db.Close(); }
public static DataSet1 get_report() { DataSet1 ds = new DataSet1(); try { using (var db = new Models.jotunContext()) { db.Database.Connection.Open(); using (var cmd = db.Database.Connection.CreateCommand()) { cmd.CommandText = "select * from view_barang order by terjual desc"; cmd.CommandType = System.Data.CommandType.Text; using (var reader = cmd.ExecuteReader()) { var dt = new System.Data.DataTable(); dt.Load(reader); ds.Tables["view_barang"].Merge(dt); } } } } catch (Exception ex) { Helpers.Generic.MsgError(ex.Message); } return ds; }
public Boolean Get_Progress_Record(Int32 jobId) { Boolean isSuccessful = true; System.Data.DataTable myProgressRecord = new System.Data.DataTable(); ErrorMessage = string.Empty; try { String strSQL = "SELECT * FROM JobProgress WHERE ProgressJobId = " + jobId.ToString(); System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection); System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader(); if (rdrGet.HasRows == true) { myProgressRecord.Load(rdrGet); isSuccessful = Gather_Progress_Record(myProgressRecord); } else { isSuccessful = false; } rdrGet.Close(); cmdGet.Dispose(); } catch (Exception ex) { isSuccessful = false; ErrorMessage = "Get Progress Record - " + ex.Message; } return(isSuccessful); }
public static DataSet1 get_report_per_jenis(string param) { DataSet1 result = new DataSet1(); try { string sql = string.Format("select * from barang where jenis in ({0})", "'" + param.Replace(", ", "','") + "'"); using (var db = new Models.sim_penjualanContext()) { db.Database.Connection.Open(); using (var cmd = db.Database.Connection.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; using (var reader = cmd.ExecuteReader()) { var dt = new System.Data.DataTable(); dt.Load(reader); result.Tables["barang"].Merge(dt); } } } } catch (Exception ex) { Helpers.Generic.MsgError(ex.Message); } return(result); }
/// Get a data table from database. public static System.Data.DataTable SelectDataTable(string sql, params SqlParameter[] arrayOfParameters) { System.Data.DataTable dtReturn = new System.Data.DataTable(); using (SqlConnection cn = new SqlConnection(ConnectionString)) { //using (SqlDataAdapter da = new SqlDataAdapter(sql, cn)) //{ // //if (arrayOfParameters != null && arrayOfParameters.Length > 0) // if ((arrayOfParameters?.Length ?? 0) > 0) da.SelectCommand.Parameters.AddRange(arrayOfParameters); // cn.Open(); // da.Fill(dtReturn); //} using (SqlCommand com = new SqlCommand(sql, cn)) { if ((arrayOfParameters?.Length ?? 0) > 0) { com.Parameters.AddRange(arrayOfParameters); } com.Connection.Open(); using (SqlDataReader rdr = com.ExecuteReader()) { dtReturn.Load(rdr); } } return(dtReturn); } }
// Is Customer public bool isCustomer(long customerID) { // Validate Connection if (this.connection == null) { return(false); } // Validate Customer ID if (customerID < 0) { return(false); } // Build the SQL statement string sql = "SELECT CustomerID FROM Customers WHERE CustomerID=" + customerID.ToString(); System.Console.WriteLine(sql); // Create and execute the SQL command MySqlCommand command = new MySqlCommand(sql, this.connection); MySqlDataReader result = command.ExecuteReader(); // Find out how many rows there are System.Data.DataTable dataTable = new System.Data.DataTable(); dataTable.Load(result); result.Close(); if (dataTable.Rows.Count == 1) { // We found a customer with the ID provided return(true); } // We did not find a customer with the ID provided return(false); }
public Boolean Job_Already_Completed(String jobNumber, System.Data.SqlClient.SqlTransaction trnEnvelope) { Boolean isCompleted = false; System.Data.DataTable thisJob = new System.Data.DataTable(); ErrorMessage = string.Empty; try { String strSQL = "SELECT * FROM Jobs WHERE JobNumber = '" + jobNumber + "'"; System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection, trnEnvelope); System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader(); if (rdrGet.HasRows == true) { thisJob.Load(rdrGet); if (thisJob.Rows[0]["JobStatus"].ToString() == "Completed") { isCompleted = true; } } rdrGet.Close(); cmdGet.Dispose(); } catch (Exception ex) { ErrorMessage = "Check Job Completed - " + ex.Message; } return(isCompleted); }
}// mExecutacomandoSql public System.Data.DataTable mRetornaDataTable(string strSql) { System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(strSql, conn); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = strSql; System.Data.OleDb.OleDbDataReader dr; System.Data.DataTable dt = new System.Data.DataTable("dados"); if (mConectaDbOracle()) { try { dr = cmd.ExecuteReader(); if (dr.HasRows) { dt.Load(dr); dr.Close(); } } catch (Exception ex) { error_ = ex.Message; log.Error("Erro no banco:->" + strSql + "\r\n" + ex.Message + "\r\n"); mDesconectaDbOracle(); } finally { mDesconectaDbOracle(); } } return(dt); } //mVerificaExistenciaRegistroSql
public IEnumerable <Tank> Create(int user_tank_num, string path) { if (user_tank_num == 1) { path += @"\279.csv"; //return new Tank("", 1, 1, 1); } else if (user_tank_num == 2) { path += @"\907.csv"; } else { path += @"\Chief.csv"; } var csvTable = new System.Data.DataTable(); using (var csvReader = new CsvReader(new StreamReader(System.IO.File.OpenRead(path)), true)) { csvTable.Load(csvReader); } yield return(new Tank(csvTable.Rows[0][0].ToString(), Convert.ToInt32(csvTable.Rows[0][1]), Convert.ToInt32(csvTable.Rows[0][2]), Convert.ToInt32(csvTable.Rows[0][3]))); }
public Boolean Get_Line_Defaults() { Boolean isSuccessful = true; System.Data.DataTable myDefaults = new System.Data.DataTable(); ErrorMessage = string.Empty; try { String strSQL = "SELECT * FROM SystemDefaults"; System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection); System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader(); if (rdrGet.HasRows == true) { myDefaults.Load(rdrGet); DefaultStartTime = Convert.ToDateTime(myDefaults.Rows[0]["ProductionStartTime"].ToString()).TimeOfDay; DefaultStopTime = Convert.ToDateTime(myDefaults.Rows[0]["ProductionFinishTime"].ToString()).TimeOfDay; } else { isSuccessful = false; ErrorMessage = "Get Production Line Defaults - Not Found !"; } rdrGet.Close(); cmdGet.Dispose(); } catch (Exception ex) { isSuccessful = false; ErrorMessage = "Get Production Line Defaults - " + ex.Message; } return(isSuccessful); }
public String MultiCoat_PaintProduct(Int32 sequence, Int32 workOrderId) { String myPrimer = string.Empty; ErrorMessage = string.Empty; try { String strSQL = "SELECT WorkOrdersPrimer.*, PaintSystemSteps.ProcessSeq, SupplierPaintProducts.SupplierPaintProductCode, ColorName.Description as ColourName, SupplierProductGroups.SupplierProductGroupCode "; strSQL = strSQL + "FROM WorkOrdersPrimer "; strSQL = strSQL + "INNER JOIN PaintSystemSteps ON WorkOrdersPrimer.PaintSystemStepId = PaintSystemSteps.PaintSystemStepId "; strSQL = strSQL + "INNER JOIN SupplierPaintProducts ON WorkOrdersPrimer.SupplierPaintProductId = SupplierPaintProducts.SupplierPaintProductId "; strSQL = strSQL + "INNER JOIN ColorName ON SupplierPaintProducts.ColorNameId = ColorName.ColorNameId "; strSQL = strSQL + "INNER JOIN SupplierProductGroups ON SupplierPaintProducts.SupplierProductGroupId = SupplierProductGroups.SupplierProductGroupId "; strSQL = strSQL + "WHERE WorkOrdersPrimer.WorkOrderId = " + workOrderId.ToString() + " "; strSQL = strSQL + "ORDER BY PaintSystemSteps.ProcessSeq"; System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection); System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader(); if (rdrGet.HasRows) { System.Data.DataTable myPrimers = new System.Data.DataTable(); myPrimers.Load(rdrGet); if (sequence == 1) { myPrimer = myPrimers.Rows[0]["SupplierProductGroupCode"].ToString() + ","; myPrimer = myPrimer + myPrimers.Rows[0]["SupplierPaintProductCode"].ToString() + ","; myPrimer = myPrimer + myPrimers.Rows[0]["ColourName"].ToString(); } else if (sequence == 2) { myPrimer = myPrimers.Rows[1]["SupplierProductGroupCode"].ToString() + ","; myPrimer = myPrimer + myPrimers.Rows[1]["SupplierPaintProductCode"].ToString() + ","; myPrimer = myPrimer + myPrimers.Rows[1]["ColourName"].ToString(); } else if (sequence == 3) { myPrimer = myPrimers.Rows[2]["SupplierProductGroupCode"].ToString() + ","; myPrimer = myPrimer + myPrimers.Rows[2]["SupplierPaintProductCode"].ToString() + ","; myPrimer = myPrimer + myPrimers.Rows[2]["ColourName"].ToString(); } else if (sequence == 4) { myPrimer = myPrimers.Rows[3]["SupplierProductGroupCode"].ToString() + ","; myPrimer = myPrimer + myPrimers.Rows[3]["SupplierPaintProductCode"].ToString() + ","; myPrimer = myPrimer + myPrimers.Rows[3]["ColourName"].ToString(); } } rdrGet.Close(); cmdGet.Dispose(); } catch (Exception ex) { myPrimer = string.Empty; ErrorMessage = "Get Multi Coat Records - " + ex.Message; } return(myPrimer); }
public static System.Data.DataTable SQLiteDataReader2Table(this System.Data.SQLite.SQLiteDataReader d) { //Create a new DataTable. System.Data.DataTable dt = new System.Data.DataTable("Result"); //Load DataReader into the DataTable. dt.Load(d); return(dt); }
/// <summary> /// Method that uploads the data from the MySqlDataReader that contains the data. /// </summary> /// <param name="reader">Data reader that contains the source data that to be uploaded</param> public void WriteToServer(System.Data.IDataReader reader) { //if (reader.HasRows) //{ System.Data.DataTable table = new System.Data.DataTable(); table.Load(reader); WriteToServer(table); //} }
/// <summary> /// Method that uploads the data from the MySqlDataReader that contains the data. /// </summary> /// <param name="reader">Data reader that contains the source data that to be uploaded</param> public void Upload(MySqlDataReader reader) { if (reader.HasRows) { System.Data.DataTable table = new System.Data.DataTable(); table.Load(reader); Upload(table); } }
public static DataSet1 get_faktur(string no_faktur) { DataSet1 result = new DataSet1(); try { string[] table1 = { "pembayaran", "penjualan", "detail_penjualan" }; foreach (var table in table1) { string sql = string.Format("select * from {0} where no_faktur = '{1}'", table, no_faktur); using (var db = new Models.sim_penjualanContext()) { db.Database.Connection.Open(); using (var cmd = db.Database.Connection.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; using (var reader = cmd.ExecuteReader()) { var dt = new System.Data.DataTable(); dt.Load(reader); result.Tables[table].Merge(dt); } } } } string[] table2 = { "karyawan", "barang", "konsumen" }; foreach (var table in table2) { string sql = string.Format("select * from {0}", table); using (var db = new Models.sim_penjualanContext()) { db.Database.Connection.Open(); using (var cmd = db.Database.Connection.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; using (var reader = cmd.ExecuteReader()) { var dt = new System.Data.DataTable(); dt.Load(reader); result.Tables[table].Merge(dt); } } } } } catch (Exception ex) { Helpers.Generic.MsgError(ex.Message); } return(result); }
private static int RunDataTable(SqlCeCommand cmd, SqlCeConnection conn) { cmd.Connection = conn; System.Data.DataTable table = new System.Data.DataTable(); table.Locale = CultureInfo.InvariantCulture; table.TableName = "data"; table.Load(cmd.ExecuteReader()); table.WriteXml(Console.Out, System.Data.XmlWriteMode.WriteSchema); return(table.Rows.Count); }
public static DataSet1 get_report(string column, string param) { DataSet1 result = new DataSet1(); try { string sql = "select * from barang"; column = column.ToLower(); if (column.Contains("kode")) { sql += string.Format(" where kd_barang like '%{0}%'", param); } else if (column.Contains("nama")) { sql += string.Format(" where nama like '%{0}%'", param); } else if (column.Contains("harga")) { sql += string.Format(" where harga like '%{0}%'", param); } else if (column.Contains("satuan")) { sql += string.Format(" where satuan like '%{0}%'", param); } else if (column.Contains("jenis")) { sql += string.Format(" where jenis like '%{0}%'", param); } else if (column.Contains("stok")) { sql += string.Format(" where stok like '%{0}%'", param); } using (var db = new Models.sim_penjualanContext()) { db.Database.Connection.Open(); using (var cmd = db.Database.Connection.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; using (var reader = cmd.ExecuteReader()) { var dt = new System.Data.DataTable(); dt.Load(reader); result.Tables["barang"].Merge(dt); } } } } catch (Exception ex) { Helpers.Generic.MsgError(ex.Message); } return(result); }
public static DataSet1 get_report(string no_surat_jalan) { DataSet1 ds = new DataSet1(); try { using (var db = new Models.jotunContext()) { db.Database.Connection.Open(); using (var cmd = db.Database.Connection.CreateCommand()) { cmd.CommandText = string.Format("select * from surat_jalan where no_surat_jalan = '{0}'", no_surat_jalan); cmd.CommandType = System.Data.CommandType.Text; using (var reader = cmd.ExecuteReader()) { var dt = new System.Data.DataTable(); dt.Load(reader); ds.Tables["surat_jalan"].Merge(dt); } cmd.CommandText = string.Format("select * from detail_surat_jalan where no_surat_jalan = '{0}'", no_surat_jalan); cmd.CommandType = System.Data.CommandType.Text; using (var reader = cmd.ExecuteReader()) { var dt = new System.Data.DataTable(); dt.Load(reader); ds.Tables["detail_surat_jalan"].Merge(dt); } cmd.CommandText = string.Format("select * from barang"); cmd.CommandType = System.Data.CommandType.Text; using (var reader = cmd.ExecuteReader()) { var dt = new System.Data.DataTable(); dt.Load(reader); ds.Tables["barang"].Merge(dt); } cmd.CommandText = string.Format("select * from customer"); cmd.CommandType = System.Data.CommandType.Text; using (var reader = cmd.ExecuteReader()) { var dt = new System.Data.DataTable(); dt.Load(reader); ds.Tables["customer"].Merge(dt); } } } } catch (Exception ex) { Helpers.Generic.MsgEx(ex.Message); } return(ds); }
public List <string> getDataBaseCliente(int dni) { List <string> lista = new List <string>(); SqlConnection connection; SqlCommand command; string sql = ""; SqlDataReader dataReader; System.Data.DataTable dt = new System.Data.DataTable(); sql = "SELECT " + "uId AS 'FORMA DE PAGO' " + ",[iNroY] AS 'NÚMERO DE Y' " + ",[iNroZ] AS 'NÚMERO DE Z' " + ",[iNroPos] AS 'NÚMERO DE POS' " + ",[iNroSuc] AS 'NÚMERO DE SUCURSAL' " + ",[iNroUser] AS 'LEGAJO CAJERO' " + ",[cDescripcion] AS 'DESCRIPCIÓN FORMA DE PAGO' " + ",REPLACE(CONVERT(VARCHAR,[dImporte]), '.', ',') AS 'IMPORTE' " + ",[dRecDesc] AS 'RECARGO/DESCUENTO' " + ",[cComprobante] AS 'COMPROBANTE' " + ",[cCliente] AS 'TARJETA CLIENTE' " + ",[cBarCode] AS 'PLAN TARJETA' " + ",[cPlan] AS 'CPLAN' " + ",[fTimeSynchro] AS 'FECHA Y HORA' " + ",[vData] AS 'DATOS DEL CLIENTE (TIPO TELEFONO, NRO DE TELEFONO, DNI)' " + "FROM dE_DDMP " + "WHERE vData LIKE '%" + dni + "%'"; connection = new SqlConnection(connectionString); try { connection.Open(); command = new SqlCommand(sql, connection); dataReader = command.ExecuteReader(); /*while (dataReader.Read()) * {*/ dt.Load(dataReader); //} dataReader.Close(); command.Dispose(); connection.Close(); } catch (Exception ex) { dt = null; } lista.Add(dt.ToString()); return(lista); }
override protected DataTables.Result _Exec() { var dt = new System.Data.DataTable(); using (var dr = _stmt.ExecuteReader()) { dt.Load(dr); } return(new Sqlserver.Result(_db, dt, this)); }
private string sqlDatoToJson(SqlDataReader dataReader) { var dataTable = new System.Data.DataTable(); dataTable.Load(dataReader); string JSONString = string.Empty; JSONString = JsonConvert.SerializeObject(dataTable); return(JSONString); }
/// <summary> /// Devuelve un datatable a partir de un excel, debemos seleccionar la pestaña e introducir la columna clave para eliminar filas en blanco /// </summary> /// <param name="strFile">Path al fichero</param> /// <param name="sheetName">Pestaña de la hoja de calculos</param> /// <param name="keyColumn">Columna clave se eliminaran todas las filas en las que esta casilla no este valorizada</param> /// <returns></returns> public static System.Data.DataTable GetDataTableFromExcel(string strFile, string sheetName, string keyColumn = "") { var con = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;TypeGuessRows=0;HDR=Yes;ImportMixedTypes=Text;'", strFile); using (OleDbConnection connection = new OleDbConnection(con)) { connection.Open(); var command = new OleDbCommand("Select * from [{0}$]".FormatWith(sheetName), connection); if (keyColumn.HasValue()) command = new OleDbCommand("Select * from [{0}$] where {1} is not null".FormatWith(sheetName, keyColumn), connection); using (OleDbDataReader dr = command.ExecuteReader()) { var table = new System.Data.DataTable(); table.Load(dr); return table; } } }
public void FilterResults(Object sender, EventArgs a) { dbProcedures db = new dbProcedures(); try { System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM tbl_Members WHERE Firstname + ' ' + LastName like '%" + txtEmployeeName.Text + "%' AND EmployerGroup like '%" + ddlEmployerGroup.SelectedValue + "%' ORDER BY Username", new db().SqlConnection); sqlCmd.CommandType = System.Data.CommandType.Text; System.Data.DataTable loadReaderdata = new System.Data.DataTable(); loadReaderdata.Load(sqlCmd.ExecuteReader()); GridView1.DataSource = loadReaderdata; GridView1.DataBind(); } catch (Exception ex) { db.LogAction("", "Error", "Error loading Members UI page. " + ex.Message.ToString()); } db.Close(); }
public void MemberSearch(string strUsername) { dbProcedures db = new dbProcedures(); try { System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM tbl_Members WHERE Username like '%" + strUsername + "%' ORDER BY Username", new db().SqlConnection); sqlCmd.CommandType = System.Data.CommandType.Text; System.Data.DataTable loadReaderdata = new System.Data.DataTable(); loadReaderdata.Load(sqlCmd.ExecuteReader()); GridView1.DataSource = loadReaderdata; GridView1.DataBind(); } catch (Exception ex) { db.LogAction("", "Error", "Error loading Members UI page. " + ex.Message.ToString()); } db.Close(); }
public static System.Data.DataTable Obtener(string sql, params object[] parametros) { Conectar(); try { System.Data.DataTable dt = new System.Data.DataTable(); System.Data.OleDb.OleDbCommand com = Comando(sql, parametros); com.Transaction = transaccion; System.Data.OleDb.OleDbDataReader r = com.ExecuteReader(); dt.Load(r); return dt; } catch (System.Exception ex) { throw new System.Exception("Conexion: Error al obtener.", ex); } finally { Desconectar(); } }
public override System.Data.DataTable ExecuteDataTable(string commandText, System.Data.CommandType commandType, ref System.Data.IDataParameter[] args, int commandTimeOut) { System.Data.DataTable results = new System.Data.DataTable(); SqlConnection connection = null; if (dw_dbConn != null) { connection = dw_dbConn; } else { connection = new SqlConnection(connectionString); } try { if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } using (SqlCommand cmd = new SqlCommand(commandText, connection)) { if (commandTimeOut > 0) { cmd.CommandTimeout = commandTimeOut; } cmd.CommandType = commandType; if (args != null && args.Length > 0) { cmd.Parameters.AddRange(args); } using (System.Data.IDataReader reader = cmd.ExecuteReader()) { results.Load(reader, System.Data.LoadOption.OverwriteChanges); } } } finally { if (connection != null && connection != dw_dbConn) { connection.Close(); connection.Dispose(); } } return results; }
public bool VendorCopyProduct(ArrayList execSql, ArrayList specs, string selMaster, string moveMaster, string movePrice) { MySqlCommand mySqlCmd = new MySqlCommand(); MySqlConnection mySqlConn = new MySqlConnection(strConn); try { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed) { mySqlConn.Open(); } mySqlCmd.Connection = mySqlConn; mySqlCmd.Transaction = mySqlConn.BeginTransaction(); mySqlCmd.CommandType = System.Data.CommandType.Text; foreach (string str in execSql) { mySqlCmd.CommandText = str; mySqlCmd.ExecuteNonQuery(); } SerialDao serialDao = new SerialDao(""); foreach (string str in specs) { mySqlCmd.CommandText = serialDao.Update(18);//規格編號 int specId = Convert.ToInt32(mySqlCmd.ExecuteScalar()); mySqlCmd.CommandText = string.Format(str, specId); mySqlCmd.ExecuteNonQuery(); } if (!string.IsNullOrEmpty(selMaster) && !string.IsNullOrEmpty(moveMaster) && !string.IsNullOrEmpty(movePrice)) { mySqlCmd.CommandText = selMaster; MySqlDataReader reader = mySqlCmd.ExecuteReader(System.Data.CommandBehavior.SingleResult); System.Data.DataTable _dt = new System.Data.DataTable(); _dt.Load(reader); if (_dt == null) { mySqlCmd.Transaction.Rollback(); return false; } else { foreach (System.Data.DataRow item in _dt.Rows) { mySqlCmd.CommandText = string.Format(moveMaster, item["price_master_id"]); int masterId = Convert.ToInt32(mySqlCmd.ExecuteScalar()); mySqlCmd.CommandText = string.Format(movePrice, masterId, item["price_master_id"]); mySqlCmd.ExecuteNonQuery(); } } } mySqlCmd.Transaction.Commit(); return true; } catch (Exception ex) { mySqlCmd.Transaction.Rollback(); throw new Exception("ProductTempDao.VendorCopyProduct-->" + ex.Message, ex); } finally { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open) { mySqlConn.Close(); } } }
/// <summary> /// Get task from database /// </summary> /// <param name="task">Return result</param> /// <param name="guid">Task unique guid</param> /// <returns>Returns 'true' if operation was successful</returns> public bool GetTask(out DbCommon.Task task, Guid guid) { bool result = false; task = null; System.Data.DataTable dt = new System.Data.DataTable(); try { if (this.OpenDb() && guid != null) { SQLiteCommand myCommand = new SQLiteCommand(_dbConnection); myCommand.CommandText = String.Format("SELECT {0},{1},{2},{3} FROM {4} WHERE {5}='{6}'", DbCommon.GUIN_COLUMN, // {0} DbCommon.TEXT_COLUMN, // {1} DbCommon.IMPORTANT_COLUMN, // {2} DbCommon.URGENT_COLUMN, // {3} DbCommon.TABLE_NAME, // {4} DbCommon.GUIN_COLUMN, // {5} guid.ToString()); // {6} SQLiteDataReader reader = myCommand.ExecuteReader(); dt.Load(reader); reader.Close(); result = _dbConnection.ResultCode() == SQLiteErrorCode.Ok; this.CloseDb(); if (dt.Rows.Count > 0 && dt.Rows[0].ItemArray.Count() > 3) { task = new DbCommon.Task(dt.Rows[0].ItemArray[0].ToString(), dt.Rows[0].ItemArray[1].ToString(), dt.Rows[0].ItemArray[2].ToString(), dt.Rows[0].ItemArray[3].ToString()); } } } catch (Exception exc) { Console.WriteLine(exc.Message); result = false; } return result; }
/// <summary> /// Method that uploads the data from the MySqlDataReader that contains the data. /// </summary> /// <param name="reader">Data reader that contains the source data that to be uploaded</param> public void Upload(MySqlDataReader reader) { if (reader.HasRows) { System.Data.DataTable table = new System.Data.DataTable(); table.Load(reader); Upload(table); } }
//TODO Lobby Changed. From /* public System.Data.DataTable SelectCards(string[] conditions) { var sb = new StringBuilder(); sb.Append("SELECT * FROM Card"); if (conditions != null) { string connector = " WHERE "; foreach (string condition in conditions) { sb.Append(connector); sb.Append("("); sb.Append(condition); sb.Append(")"); connector = " AND "; } } using (var conn = new VistaDB.Provider.VistaDBConnection(db)) { var cmd = new VistaDB.Provider.VistaDBCommand(); cmd.Connection = conn; cmd.CommandText = sb.ToString(); var result = new System.Data.DataTable(); result.Load(cmd.ExecuteReader()); return result; } } */ //TO public System.Data.DataTable SelectCards(string[] conditions) { var sb = new StringBuilder("SELECT * FROM Card"); List<String> sets = new List<String>(); if (conditions != null) { string connector; connector = " ( "; int i = 0; bool foundSet = false; sb = new StringBuilder(); foreach (string condition in conditions) { if (condition.Substring(0, 10).Equals("Card.setId")) { sb.Append(connector); sb.Append("("); sb.Append(condition); sb.Append(")"); connector = " OR "; conditions[i] = ""; foundSet = true; } i++; } sb.Append(")"); if (foundSet) { Array.Resize<string>(ref conditions, conditions.Length + 1); conditions[conditions.Length - 1] = sb.ToString(); } sb = new StringBuilder("SELECT * FROM Card"); connector = " WHERE "; foreach (string condition in conditions) { if (!condition.Trim().Equals("")) { sb.Append(connector); sb.Append("("); sb.Append(condition); sb.Append(")"); connector = " AND "; } } } using (var conn = new VistaDB.Provider.VistaDBConnection(db)) { var cmd = new VistaDB.Provider.VistaDBCommand(); cmd.Connection = conn; cmd.CommandText = sb.ToString(); var result = new System.Data.DataTable(); result.Load(cmd.ExecuteReader()); return result; } }
public HttpResponseMessage GetDailyLog(int page, double UTCOffset, int rows, string sidx, string sord, string filters = "", string devicefilters = "", string deviceIds = "") { //condition string condition = null; string condition2 = null; //存放KEYWARD條件 ArrayList al = null; if (!string.IsNullOrEmpty(devicefilters)) { System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer(); jqGridUtil.Filters f = serializer.Deserialize<jqGridUtil.Filters>(devicefilters); al = new System.Collections.ArrayList(f.rules.Count); foreach (jqGridUtil.Rule rule in f.rules) { if (!string.IsNullOrEmpty(rule.data)) { al.Add(jqGridUtil.GetSearchString(rule.field, rule.data, rule.op)); } } condition = string.Join(string.Format(" {0} ", f.groupOp), al.ToArray()); } else if(!string.IsNullOrEmpty(deviceIds)) { condition = string.Format("DeviceID in ({0})", deviceIds); } if (!string.IsNullOrEmpty(filters)) { System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer(); jqGridUtil.Filters f = serializer.Deserialize<jqGridUtil.Filters>(filters); al = new System.Collections.ArrayList(f.rules.Count); foreach (jqGridUtil.Rule rule in f.rules) { if (!string.IsNullOrEmpty(rule.data)) { al.Add(jqGridUtil.GetSearchString(rule.field, rule.data, rule.op)); } } if ( al.Count>0) { //condition = string.Join(string.Format(" {0} ", f.groupOp), al.ToArray()) + " and " + condition; condition2 = string.Join(string.Format(" {0} ", f.groupOp), al.ToArray()); } } //if (!string.IsNullOrEmpty(filters)) //{ // System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer(); // jqGridUtil.Filters f = serializer.Deserialize<jqGridUtil.Filters>(filters); // al = new System.Collections.ArrayList(f.rules.Count); // foreach (jqGridUtil.Rule rule in f.rules) // { // if (!string.IsNullOrEmpty(rule.data)) // { // al.Add(jqGridUtil.GetSearchString(rule.field, rule.data, rule.op)); // } // } // condition += string.Join(string.Format(" {0} ", f.groupOp), al.ToArray()); //} try { al = new ArrayList(1); al.Add(condition); al.Add(condition2);//新增車機過濾條件 int totalCount = DataProvider.Instance().GetCount_DailyLog(al); al = new ArrayList(); al.Add(page); al.Add(rows); al.Add(sidx); al.Add(sord); al.Add(condition); al.Add(condition2); System.Data.IDataReader dr = DataProvider.Instance().FetchAll_DailyLog(al); System.Data.DataTable dt = new System.Data.DataTable(); dt.Load(dr); int totalPage = 0; if (totalCount > 0) { totalPage = totalCount / rows; if ((totalCount % rows) > 0) totalPage += 1; } else { totalPage = 0; } if (page > totalPage) page = totalPage; // 產生JSON 字串 List<Row> rowlist = new List<Row>() { }; int i = 0; foreach (System.Data.DataRow item in dt.Rows) { Row rowobj = new Row(); var stringList = new List<string>(); rowobj.id = i; stringList.Add(i.ToString()); stringList.Add(item["DeviceID"].ToString()); stringList.Add(item["AlertID"].ToString()); stringList.Add(item["CarNo"].ToString()); //將車牌改成車號 stringList.Add(item["DriverName"].ToString()); stringList.Add(item["DriverID"].ToString()); //stringList.Add(string.Format(Localization.GetString("DateTimeFormat", Localization.SharedResourceFile), Convert.ToDateTime(item["CreateDate"]))); stringList.Add(string.Format(Localization.GetString("DateTimeFormat", Localization.SharedResourceFile), Convert.ToDateTime(item["CreateDate"]).AddHours(UTCOffset))); stringList.Add(item["RuleName"].ToString()); stringList.Add(item["AlarmTypeName"].ToString()); stringList.Add(item["SeverityCode"].ToString()); stringList.Add(string.Empty); //警報訊息 stringList.Add(item["HandleStatus"].ToString()); //工作狀態 stringList.Add(item["MisInfo"].ToString()); stringList.Add(item["AlarmType"].ToString()); stringList.Add(item["WorkListAlertID"].ToString()); //stringList.Add(item["GPS_Latitude"].ToString()); //stringList.Add(item["GPS_Longitude"].ToString()); stringList.Add(item["fleetSetting_WorkListID"].ToString()); rowobj.cell = stringList; rowlist.Add(rowobj); i = i + 1; } var jsonToReturn = new { total = totalPage.ToString(), page = page.ToString(), records = totalCount.ToString(), rows = rowlist }; return Request.CreateResponse(HttpStatusCode.OK, jsonToReturn); } catch (Exception) { throw; } }
public HttpResponseMessage GetDailyLogExcel(double UTCOffset, string filters = "", string cultureName = "") { Type objectType = Type.GetType("Keywin.DNN.Modules.Fleetmgm.fleetSetting_Company.ServicesController, Keywin.DNN.Modules.Fleetmgm.fleetSetting_Company", true, true); Object objComponent = Activator.CreateInstance(objectType); MethodInfo mi = objectType.GetMethod("GetPortalDeviceIdInternal"); int pID = PortalSettings.PortalId; object[] param = { pID }; List<string> deviceIdList = (List<string>)mi.Invoke(objComponent, param); string deviceIds = string.Empty; foreach (string deviceId in deviceIdList) { deviceIds += ",'" + deviceId + "'"; } //condition string condition = null; string condition2 = null; //存放KEYWARD條件 ArrayList al = null; condition = string.Format("DeviceID in ({0})", deviceIds.TrimStart(',')); if (!string.IsNullOrEmpty(filters)) { System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer(); jqGridUtil.Filters f = serializer.Deserialize<jqGridUtil.Filters>(filters); al = new System.Collections.ArrayList(f.rules.Count); foreach (jqGridUtil.Rule rule in f.rules) { if (!string.IsNullOrEmpty(rule.data)) { al.Add(jqGridUtil.GetSearchString(rule.field, rule.data, rule.op)); } } if (al.Count > 0) { condition2 = string.Join(string.Format(" {0} ", f.groupOp), al.ToArray()); } } try { al = new ArrayList(); al.Add(1); al.Add(2147483647); al.Add("CreateDate"); al.Add("desc"); al.Add(condition); al.Add(condition2); System.Data.IDataReader dr = DataProvider.Instance().FetchAll_DailyLog(al); System.Data.DataTable dt = new System.Data.DataTable(); dt.Load(dr); //EXCEL產生 HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); //新增試算表 HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("DailyLog"); //插入資料值 //表頭 HSSFRow title = (HSSFRow)sheet.CreateRow(0); for (int i = 0; i <= 5; i++) { title.CreateCell(i).SetCellValue(Localization.GetString("Col" + i, "~/App_GlobalResources/List_DailyLogExcelColName." + cultureName + ".resx")); } if (dt.Rows.Count > 0) { //表身 int rowIndex = 1; foreach (System.Data.DataRow item in dt.Rows) { HSSFRow row = (HSSFRow)sheet.CreateRow(rowIndex++); row.CreateCell(0).SetCellValue(item["CarNo"].ToString()); row.CreateCell(1).SetCellValue(item["DriverName"].ToString()); row.CreateCell(2).SetCellValue(string.Format(Localization.GetString("DateTimeFormat", Localization.SharedResourceFile), Convert.ToDateTime(item["CreateDate"]).AddHours(UTCOffset))); row.CreateCell(3).SetCellValue(item["RuleName"].ToString()); row.CreateCell(4).SetCellValue(item["AlarmTypeName"].ToString()); row.CreateCell(5).SetCellValue(item["SeverityCode"].ToString()); } } else { HSSFRow row = (HSSFRow)sheet.CreateRow(1); row.CreateCell(0).SetCellValue("Too many data, please use conditons"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 6)); } workbook.Write(ms); HttpResponseMessage result = Request.CreateResponse(HttpStatusCode.OK); result.Content = new ByteArrayContent(ms.ToArray()); result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); result.Content.Headers.ContentDisposition.FileName = HttpUtility.UrlEncode("DailyLog.xls", System.Text.Encoding.UTF8); workbook = null; ms.Close(); ms.Dispose(); return result; } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message); } }
public static System.Data.DataTable FromCache(this DataAccessComponents.DataAccessComponent q, DbCommand commands) { try { bool UseCache = false; Boolean.TryParse(System.Configuration.ConfigurationManager.AppSettings["UseCache"],out UseCache); System.Data.SqlClient.SqlCommand command = commands as System.Data.SqlClient.SqlCommand; System.Data.DataTable objCache; if (!UseCache) { objCache = new System.Data.DataTable(); objCache.Load(q.Database.ExecuteReader(command), System.Data.LoadOption.OverwriteChanges); return objCache; } if (command == null) return null; string CacheId = command.CommandText + "?"; foreach (System.Data.Common.DbParameter dbp in command.Parameters) { CacheId += dbp.ParameterName + "=" + dbp.Value.ToString() + "&"; } //if (Environment.StackTrace.Contains("Administrator\\")) //{ // objCache = q.ToList(); // return objCache; //} //else { // System.Threading.Thread.Sleep(500); objCache = System.Web.HttpRuntime.Cache.Get(CacheId) as System.Data.DataTable; } if (objCache == null) { List<string> tablesNames = q.TableNames; //command.Mapping.GetTables().Where(t => (t.TableNames.Contains("[")) ? CacheId.Contains(t.TableNames.Substring(4)) : CacheId.Contains("[" + t.TableNames.Substring(4) + "]")).Select(t => t.TableNames.Substring(4)).ToList(); string connStr = q.Database.ConnectionString; // System.Configuration.ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString; using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr)) { conn.Open(); System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connStr); System.Web.Caching.SqlCacheDependency sqldep; AggregateCacheDependency aggDep = new AggregateCacheDependency(); foreach (string tableName in tablesNames) { if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(tableName)) System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, tableName); if (tableName.Contains("Comment") || tableName.Contains("FormBuilder")) sqldep = new System.Web.Caching.SqlCacheDependency("NatiqNewsPoll", tableName); else sqldep = new System.Web.Caching.SqlCacheDependency("NatiqNews", tableName); aggDep.Add(sqldep); } objCache = new System.Data.DataTable(); objCache.Load(q.Database.ExecuteReader(command), System.Data.LoadOption.OverwriteChanges); if (objCache != null) System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, aggDep, DateTime.Now.AddDays(1), System.Web.Caching.Cache.NoSlidingExpiration); } } //Return the created (or cached) List return objCache; } catch (Exception ex) { throw ex; } }
private static QueryData GetQueryData(string procName, string validName, string selectText, string descText, string number, string execText, string projectName, SqlConnection conn) { Log.Write("Start GetQueryData:" + execText); var obj = new QueryData(); try { var lstColumns = new List<string>(); var sql = execText; var cmd = new SqlCommand(sql.Replace("\\r\\n", "").Replace("\\r", ""), conn); if (cmd.CommandText.StartsWith("EXEC")) { cmd.CommandTimeout = 200; } if (cmd.CommandText.Contains("check_patient")) { cmd.CommandTimeout = 90; } cmd.CommandTimeout = 200; var rdrQD = cmd.ExecuteReader(); if (rdrQD.HasRows) { for (int i = 0; i < rdrQD.FieldCount; i++) { lstColumns.Add(rdrQD.GetName(i)); } obj.FieldsName = lstColumns; // Получаем сами данные var dtData = new DataTable(); dtData.Load(rdrQD); obj.Data = dtData; obj.ProjectName = projectName; obj.ValidationRule = validName; obj.Description = descText; obj.NameList = number; } else { obj = null; } rdrQD.Close(); rdrQD = null; } catch (Exception ex) { Log.Write(ex); } finally { Log.Write("Exit GetQueryData:" + execText); } return obj; }
public override System.Data.DataTable ExecuteDataTable(String commandText, System.Data.CommandType commandType, ref System.Data.IDataParameter[] args, Int32 commandTimeOut) { System.Data.DataTable results = new System.Data.DataTable(); OracleConnection connection = null; if (dw_dbConn != null) { connection = dw_dbConn; } else { connection = new OracleConnection(connectionString); } try { if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } using (OracleCommand cmd = new OracleCommand(commandText, connection)) { if (commandTimeOut > 0) { cmd.CommandTimeout = commandTimeOut; } cmd.CommandType = commandType; DeriveParameters(cmd); AssignParameters(cmd, ref args); using (OracleDataReader reader = cmd.ExecuteReader()) { results.Load(reader, System.Data.LoadOption.OverwriteChanges); } } } finally { if (connection != null && connection != dw_dbConn) { connection.Close(); connection.Dispose(); } } return results; }
public System.Data.DataTable SelectCards(string[] conditions) { var sb = new StringBuilder(); sb.Append("SELECT * FROM Card"); if(conditions != null) { string connector = " WHERE "; foreach(string condition in conditions) { sb.Append(connector); sb.Append("("); sb.Append(condition); sb.Append(")"); connector = " AND "; } } using(var conn = new VistaDB.Provider.VistaDBConnection(db)) { var cmd = new VistaDB.Provider.VistaDBCommand(); cmd.Connection = conn; cmd.CommandText = sb.ToString(); var result = new System.Data.DataTable(); result.Load(cmd.ExecuteReader()); return result; } }
public virtual void ImportarTabla(MapaDeTabla mapa) { Progreso.Value = 0; Progreso.ChangeStatus("Leyendo la tabla " + mapa.ToString()); string SqlSelect = @"SELECT * FROM " + mapa.TablaExterna; if (mapa.Where != null) SqlSelect += " WHERE " + mapa.Where; // Hago un SELECT de la tabla System.Data.IDbCommand TableCommand = ConexionExterna.CreateCommand(); TableCommand.CommandText = SqlSelect; System.Data.DataTable ReadTable = new System.Data.DataTable(); ReadTable.Locale = System.Globalization.CultureInfo.CurrentCulture; ReadTable.Load(TableCommand.ExecuteReader()); if (mapa.AutoSaltear) { mapa.Saltear = Lfx.Workspace.Master.CurrentConfig.ReadGlobalSetting<int>("Importar.RegistrosImportados." + this.Nombre + "." + mapa.Nombre, 0); if (mapa.Saltear > 1) mapa.Saltear--; } System.Data.IDbTransaction Trans = this.Connection.BeginTransaction(); // Navegar todos los registros Progreso.ChangeStatus("Incorporando " + ReadTable.Rows.Count.ToString() + " registros de la tabla " + mapa.ToString()); Progreso.Max = ReadTable.Rows.Count; int RowNumber = 0; foreach (System.Data.DataRow OriginalRow in ReadTable.Rows) { ++RowNumber; if (mapa.Saltear == 0 || RowNumber > mapa.Saltear) { Lfx.Data.Row ProcessedRow = this.ProcesarRegistro(mapa, OriginalRow); this.ImportarRegistro(mapa, ProcessedRow); } if ((RowNumber % 200) == 0) { Progreso.Advance(200); Lfx.Workspace.Master.CurrentConfig.WriteGlobalSetting("Importar.RegistrosImportados." + this.Nombre + "." + mapa.Nombre, RowNumber); Trans.Commit(); Trans.Dispose(); Trans = this.Connection.BeginTransaction(); } if (mapa.Limite > 0 && RowNumber > mapa.Limite) break; } Trans.Commit(); Trans.Dispose(); Trans = null; Lfx.Workspace.Master.CurrentConfig.WriteGlobalSetting("Importar.RegistrosImportados." + this.Nombre + "." + mapa.Nombre, RowNumber); }
/// <summary> /// Get tasks from database /// </summary> /// <param name="important">If tasks are important</param> /// <param name="urgent">If tasks are urgent</param> /// <param name="tasks">Return result</param> /// <returns>Returns 'true' if operation was successful</returns> public bool GetAllTasks(out List<DbCommon.Task> tasks, bool? important = null, bool? urgent = null) { bool result = false; tasks = new List<DbCommon.Task>(); System.Data.DataTable dt = new System.Data.DataTable(); try { if (this.OpenDb()) { string where = String.Empty; if (important != null) { where = String.Format(" WHERE {0}='{1}'", DbCommon.IMPORTANT_COLUMN, // {0} important.Value ? "1" : "0");// {1} } if (urgent != null) { if (!String.IsNullOrWhiteSpace(where)) { where = String.Format("{0} AND ", where); } else { where = " WHERE "; } where = String.Format("{0}{1}='{2}'", where, // {0} DbCommon.URGENT_COLUMN, // {1} urgent.Value ? "1" : "0");// {2} } SQLiteCommand myCommand = new SQLiteCommand(_dbConnection); myCommand.CommandText = String.Format("SELECT {0},{1},{2},{3} FROM {4}{5}", DbCommon.GUIN_COLUMN, // {0} DbCommon.TEXT_COLUMN, // {1} DbCommon.IMPORTANT_COLUMN, // {2} DbCommon.URGENT_COLUMN, // {3} DbCommon.TABLE_NAME, // {4} where); // {5} SQLiteDataReader reader = myCommand.ExecuteReader(); dt.Load(reader); reader.Close(); result = _dbConnection.ResultCode() == SQLiteErrorCode.Ok; this.CloseDb(); IEnumerator<System.Data.DataRow> enumerator = (IEnumerator<System.Data.DataRow>)dt.Rows.GetEnumerator(); while(enumerator.MoveNext()) { if (enumerator.Current.ItemArray.Count() > 3) { DbCommon.Task task = new DbCommon.Task(enumerator.Current.ItemArray[0].ToString(), enumerator.Current.ItemArray[1].ToString(), enumerator.Current.ItemArray[2].ToString(), enumerator.Current.ItemArray[3].ToString()); tasks.Add(task); } } } } catch (Exception exc) { Console.WriteLine(exc.Message); result = false; } return result; }