private String[] GetExcelSheetNames(string excelFile, bool blnXlsx = false) { System.Data.OleDb.OleDbConnection objConn = null; System.Data.DataTable dt = null; try { String connString = null; if (blnXlsx) { connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\""; } else { connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\""; } objConn = new System.Data.OleDb.OleDbConnection(connString); objConn.Open(); dt = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i += 1; } return excelSheets; } catch (Exception ex) { throw (new Exception("Cannot Read Excel Sheet Names -" + ex.Message)); } finally { if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } }
/// <summary> /// Recebe o caminho do arquivo e o nome da Planilha excel e retorna em um datatable /// </summary> /// <param name="Path">Caminho do Arquivo</param> /// <param name="PlanName">Nome da Planilha</param> public static DataTable ImportaExel(string Path, string PlanName) { string cnnString = string.Empty; if (Path.Substring(Path.Length - 1, 1).ToUpper() == "X") { cnnString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES';", Path); } else { cnnString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", Path); } string sql = "select * from [{0}$]"; System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(cnnString); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(String.Format(sql, PlanName), cnn); DataSet ds = new DataSet(); var dt = new DataTable(); try { cnn.Open(); da.Fill(ds); dt = ds.Tables[0]; } finally { cnn.Close(); cnn.Dispose(); da.Dispose(); ds.Dispose(); } return(dt); }
public DataSet navigate(string sortfield) { System.Data.OleDb.OleDbConnection conn = GetConnection(); DataSet ds = new DataSet(); try { int inc; int MaxRows; string sql = "select * from table1"; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn); try { da.Fill(ds, "grade1"); MaxRows = ds.Tables["grade1"].Rows.Count; inc = -1; } finally { da.Dispose(); } return(ds); } finally { conn.Close(); conn.Dispose(); } }
public System.Data.DataTable CreateDataTable(string connection, string query) { //create vars var dataTable = new DataTable(); var oleConnection = new System.Data.OleDb.OleDbConnection(connection); var oleCommand = new System.Data.OleDb.OleDbCommand(query, oleConnection); //get data System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(oleCommand); oleConnection.Open(); adapter.Fill(dataTable); oleConnection.Close(); //clean up oleConnection.Dispose(); adapter.Dispose(); oleCommand.Dispose(); //foreach (var rw in dataTable.Rows) //{ //} return(dataTable); }
private void button5_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); ofd.DefaultExt = "*.xls;*.xlsx"; ofd.Filter = "Microsoft Excel (*.xls*)|*.xls*"; ofd.Title = "Выберите документ для загрузки данных"; if (ofd.ShowDialog() != DialogResult.OK) { MessageBox.Show("Вы не выбрали файл для открытия", "Загрузка данных...", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ofd.FileName + ";Extended Properties='Excel 12.0 XML;HDR=YES;IMEX=1';"; System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(constr); con.Open(); DataSet ds = new DataSet(); DataTable schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); string sheet1 = (string)schemaTable.Rows[0].ItemArray[2]; string select = String.Format("SELECT * FROM [{0}]", sheet1); System.Data.OleDb.OleDbDataAdapter ad = new System.Data.OleDb.OleDbDataAdapter(select, con); ad.Fill(ds); DataTable dt = ds.Tables[0]; con.Close(); con.Dispose(); dataGridView1.DataSource = dt; }
public int isHomologyNote(string Table, string term1, string term2, string Value1, string Value2) { string tem_sql = "";//定义字符串变量 System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr); //连接数据库 System.Data.OleDb.OleDbCommand tem_comm; //定义OleDbCommand类 tem_conn.Open(); //打开数据库的连接 //设置SQL语句,查找要添加的记录 tem_sql = "select top 1 * From " + Table + " where " + term1 + " = '" + Value1 + "' and " + term2 + " = '" + Value2 + "'"; tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn); //执行SQL语句 int RecordCount = 0; //定义变量 if (tem_comm.ExecuteScalar() == null) //如果查询为空 { RecordCount = 0; } else { RecordCount = (int)tem_comm.ExecuteScalar(); //返回查找结果的个数 } tem_conn.Close(); //关闭连接 tem_comm.Dispose(); //释放资源 tem_conn.Dispose(); //释放资源 return(RecordCount); //返回查询记录数量 }
public DataSet GetLoad(string sortfield) { System.Data.OleDb.OleDbConnection conn = GetConnection(); DataSet ds = new DataSet(); try { // Dim sql As String = "SELECT ID data1 as [Firstname],data2 as [Lastname],data3 as [20],data4 as [EG],data5 as [1st-10%],data6 as [50pt],data7 as [EG1],data8 as [2nd-10%],data9 as [50pts],data10 as [EG2],data11 as [20%],data12 as [100pt],data13 as [EG3],data14 as [30%],data15 as [100],data16 as [EG4],data17 as [2nd-30%],data18 as [PrelimGrade],data19 as [Remarks]FROM Table1 order by " + sortfield string sql = "SELECT STUDENTID,data1 AS Firstname, data2 AS Lastname," + "data3 AS 20, data4 AS EG, data5 AS [1st-10%], data6 AS 50pt," + "data7 AS EG1, data8 AS [2nd-10%], data9 AS 50pts, data10 AS EG2," + "data11 AS [20%], data12 AS 100pt, data13 AS EG3, data14 AS [30%]," + "data15 AS 100, data16 AS EG4, data17 AS [2nd-30%], data18 AS PrelimGrade," + "data19 AS Remarks FROM Table1 ORDER BY ID"; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn); try { da.Fill(ds, "grade1"); } finally { da.Dispose(); } return(ds); } finally { conn.Close(); conn.Dispose(); } }
private void InstantiateConnectionObject(ado_data_access p_oAdo) { if (_oConn == null) { } else { p_oAdo.CloseConnection(_oConn); _oConn.Dispose(); _oConn = null; } _oConn = new System.Data.OleDb.OleDbConnection(); }
public static System.Data.DataTable GetDataByExcel(string FileName, string FileSheet) { System.Data.DataTable dt = new System.Data.DataTable(); //System.Data.OleDb.OleDbConnection ocon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"); System.Data.OleDb.OleDbConnection ocon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"); System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter("select * from [" + FileSheet + "$]", ocon); oda.Fill(dt); if (ocon.State == ConnectionState.Open) { ocon.Close(); } ocon.Dispose(); return(dt); }
public System.Data.DataSet ReadExcelFile(String filename) { // Create connection string variable. String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/Images/" + filename) + ";" + "Extended Properties=Excel 8.0;"; // Create connection object System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(sConnectionString); // Open connection with the database. objConn.Open(); // The code to follow uses a SQL SELECT command to display the data from the worksheet. // Create new OleDbCommand to return data from worksheet. System.Data.OleDb.OleDbCommand objCmdSelect = new System.Data.OleDb.OleDbCommand("SELECT * FROM [Products$]", objConn); // Create new OleDbDataAdapter that is used to build a DataSet // based on the preceding SQL SELECT statement. System.Data.OleDb.OleDbDataAdapter objAdapter1 = new System.Data.OleDb.OleDbDataAdapter(); // Pass the Select command to the adapter. objAdapter1.SelectCommand = objCmdSelect; // Create new DataSet to hold information from the worksheet. System.Data.DataSet objDataset1 = new System.Data.DataSet(); try { // Fill the DataSet with the information from the worksheet. objAdapter1.Fill(objDataset1, "Products"); } catch (Exception exp) { objConn.ResetState(); objConn.Dispose(); objConn.Close(); } //objConn.Dispose(); // Clean up objects. objConn.Close(); return(objDataset1); }
public DataSet deldata(string sortfield) { System.Data.OleDb.OleDbConnection conn = GetConnection(); DataSet ds = new DataSet(); try { int inc; int MaxRows; //Dim sql As String = "select * from table1" //Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, conn) try { string sqldelete = default(string); sqldelete = "DELETE * FROM table1 WHERE STUDENTID=\'" + Form1.Default.STUDID.Text + "\'"; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqldelete, conn); // Gets the records from the table and fills our adapter with those. DataTable dt = new DataTable("grade1"); da.Fill(dt); MessageBox.Show("Data has been deleted"); clearall(); Form1.Default.DataGridView1.DataSource = dt; RefreshDGV(); } finally { //da.Dispose() } return(ds); } finally { conn.Close(); conn.Dispose(); } }
private static void CreateTable() { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection( ); conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbPath + ";user id=;password=;"; conn.Open( ); System.Data.OleDb.OleDbCommand cmd = conn.CreateCommand( ); try { Create_BASEDOCTOR(cmd); Create_BASE_MZFP_ITEM(cmd); Create_BASE_PATIENTTYPE(cmd); Create_BASE_STAT_ITEM(cmd); Create_BASE_TEMPLATE_DETAIL(cmd); Create_BASE_EMPLOYEE_PROPERTY(cmd); Create_BASE_DEPT_PROPERTY(cmd); Create_BASE_USER(cmd); Create_BASE_PATIENTTYPE_COST(cmd); Create_BASE_ITEM_FAVORABLE(cmd); Create_BASE_ITEMMX_FAVORABLE(cmd); Create_BASE_WORK_UNIT(cmd); Create_MZ_INVOICE(cmd); Create_VI_MZ_SHOWCARD(cmd); Create_MZ_PRESMASTER(cmd); Create_MZ_PRESORDER(cmd); Create_MZ_COSTMASTER(cmd); Create_MZ_COSTORDER(cmd); Create_MZ_PATLIST(cmd); Create_MZ_ACCOUNT(cmd); } catch (Exception err) { throw err; } finally { conn.Close( ); } cmd.Dispose( ); conn.Dispose( ); }
#pragma warning restore 1591 //------------------------------------------------------------------------------ // // Method: Dispose // //------------------------------------------------------------------------------ /// <summary> /// Provides a method to free unmanaged resources used by this class. /// </summary> /// <param name="disposing">Whether the method is being called as part of an explicit Dispose routine, and hence whether managed resources should also be freed.</param> protected virtual void Dispose(bool disposing) { if (!disposed) { if (disposing) { // Free other state (managed objects). } // Free your own state (unmanaged objects). if (oleDbConnection != null) { oleDbConnection.Close(); oleDbConnection.Dispose(); } // Set large fields to null. oleDbConnection = null; disposed = true; } }
public int isHomologyNote(string Table, string term1, string term2, string Value1, string Value2) { string tem_sql = "";//定义字符串变量 System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr);//连接数据库 System.Data.OleDb.OleDbCommand tem_comm;//定义OleDbCommand类 tem_conn.Open();//打开数据库的连接 //设置SQL语句,查找要添加的记录 tem_sql = "select top 1 * From " + Table + " where " + term1 + " = '" + Value1 + "' and " + term2 + " = '" + Value2 + "'"; tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//执行SQL语句 int RecordCount = 0;//定义变量 if (tem_comm.ExecuteScalar() == null)//如果查询为空 RecordCount = 0; else RecordCount = (int)tem_comm.ExecuteScalar();//返回查找结果的个数 tem_conn.Close();//关闭连接 tem_comm.Dispose();//释放资源 tem_conn.Dispose();//释放资源 return RecordCount;//返回查询记录数量 }
private void Main_Load(object sender, EventArgs e) { label_username.Text = username; n = 312; remainingcard.Text = Convert.ToString(n); for (int i = 0; i < 52; i++) { card[i, 0] = i + 1; card[i, 1] = 6; } //List<Image> list = new List<Image>(); wager_amount = 0; String m_conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\21.mdb;"; System.Data.OleDb.OleDbConnection m_conn = new System.Data.OleDb.OleDbConnection(m_conn_str); m_conn.Open(); String query1 = "select * from BlackJack where ID = " + this.login_id; System.Data.OleDb.OleDbDataAdapter dal = new System.Data.OleDb.OleDbDataAdapter(query1, m_conn); DataTable dt = new DataTable(); dal.Fill(dt); wager_amount = Convert.ToInt32(dt.Rows[0]["wager"].ToString()); total = Convert.ToInt32(dt.Rows[0]["total"].ToString()); win = Convert.ToInt32(dt.Rows[0]["win"].ToString()); draw = Convert.ToInt32(dt.Rows[0]["draw"].ToString()); lose = Convert.ToInt32(dt.Rows[0]["lose"].ToString()); winingpercent = Convert.ToDouble(dt.Rows[0]["winp"].ToString()); m_conn.Close(); m_conn.Dispose(); show(); hit.Enabled = false; stand.Enabled = false; split.Enabled = false; Double.Enabled = false; }
void end(int p)//结束,清屏,做收尾工作的函数 { int i; if (p == 0)//输 { lose++; MessageBox.Show("you lose!"); } else if (p == 1)//赢 { win++; wager_amount += 2 * Convert.ToInt32(tbx_wager.Text); MessageBox.Show("you win!"); } else if (p == 2)//平局 { draw++; wager_amount += Convert.ToInt32(tbx_wager.Text); MessageBox.Show("draw!"); } else if (p == 3) { pictureBox11.Image = null; pictureBox12.Image = null; pictureBox13.Image = null; pictureBox14.Image = null; pictureBox15.Image = null; for (int qwe = 0; qwe < 10; qwe++)//清空牌 { player_card2[qwe] = 0; } } total++; winingpercent = Convert.ToDouble(win) / total; splited = 0; end_1 = 0; wager2 = 0; card_sum1 = 0; card_sum2 = 0; for (i = 0; i < 5; i++) { end_player(player_card, i); //清空桌面 } for (i = 0; i < 5; i++) { end_dealer(dealer_card, i); } for (int j = 0; j < 10; j++)//清空牌 { dealer_card[j] = 0; player_card[j] = 0; } if (n <= 20) { MessageBox.Show("Shuffle!"); n = 312; for (int k = 0; k < 52; k++) { card[k, 1] = 6; } } if (wager_amount <= 0) { wager_amount = 500; MessageBox.Show("$500 extra.Value your money!", "hahahameiqianleba"); } remainingcard.Text = Convert.ToString(n); show(); //显示数据 String t = Convert.ToString(total); //上传数据 String w = Convert.ToString(win); String l = Convert.ToString(lose); String wp = Convert.ToString(winingpercent); String wa = Convert.ToString(wager_amount); String d = Convert.ToString(draw); String m_conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\21.mdb;"; System.Data.OleDb.OleDbConnection m_conn = new System.Data.OleDb.OleDbConnection(m_conn_str); m_conn.Open(); //string query1 = "insert into BlackJack([ID],[wager],[total],[win],[lose,winp]) values('" + a + "'," + wa +"','" + t + "','" + w + "','" + l + "','" + wp + "')"; string query1 = "update BlackJack set total='" + t + "',draw='" + d + "',wager='" + wa + "',win='" + w + "',lose='" + l + "',winp='" + wp + "' where [ID] =" + this.login_id; System.Data.OleDb.OleDbCommand m_comm = new System.Data.OleDb.OleDbCommand(query1, m_conn); m_comm.ExecuteNonQuery(); m_conn.Close(); m_conn.Dispose(); //准备下一局 hit.Enabled = false; stand.Enabled = false; split.Enabled = false; Double.Enabled = false; start.Enabled = true; }
public void ExecImportDataToOracle(string tabname, string connectionAccess) { using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionAccess)) { #region 定义Access数据连接对象 string select = string.Format("SELECT AXISNUM,OVERLOADRATE,TOTALWEIGHT,TOTALTOLL,DISTANCE,VEHICLELICENSE,ENTRYSTATION,ENTRYSTATIONNAME,ENTRYTIME,EXITSTATION,EXITSTATIONNAME,EXITTIME,PAYTYPE FROM {0}", tabname); connection.Open(); System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(select, connection); System.Data.OleDb.OleDbDataReader dataRead = command.ExecuteReader(); //获取记录总数 int size = 0; int count = 0; DataTable dtCount = new DataTable(); System.Data.OleDb.OleDbDataAdapter oleDa = new System.Data.OleDb.OleDbDataAdapter( string.Format("select count(1) from {0}", tabname), connectionAccess); oleDa.Fill(dtCount); count = Convert.ToInt32(dtCount.Rows[0][0].ToString()); #endregion #region Oracle #region Insert语句 StringBuilder strSql = new StringBuilder(); strSql.Append("insert into BASE_BUS_OVERRUN("); strSql.Append("AXISNUM,OVERLOADRATE,TOTALWEIGHT,TOTALTOLL,DISTANCE,VEHICLELICENSE,ENTRYSTATION,ENTRYSTATIONNAME,ENTRYTIME,EXITSTATION,EXITSTATIONNAME,EXITTIME,PAYTYPE"); strSql.Append(") values ("); strSql.Append(":AXISNUM,:OVERLOADRATE,:TOTALWEIGHT,:TOTALTOLL,:DISTANCE,:VEHICLELICENSE,:ENTRYSTATION,:ENTRYSTATIONNAME,:ENTRYTIME,:EXITSTATION,:EXITSTATIONNAME,:EXITTIME,:PAYTYPE"); strSql.Append(") "); #endregion #region Oracle参数对象 OracleParameter[] parameters = { new OracleParameter(":AXISNUM", OracleType.Number, 4), new OracleParameter(":OVERLOADRATE", OracleType.Number, 4), new OracleParameter(":TOTALWEIGHT", OracleType.Number, 4), new OracleParameter(":TOTALTOLL", OracleType.Number, 4), new OracleParameter(":DISTANCE", OracleType.Number, 4), new OracleParameter(":VEHICLELICENSE", OracleType.VarChar, 200), new OracleParameter(":ENTRYSTATION", OracleType.Number, 4), new OracleParameter(":ENTRYSTATIONNAME", OracleType.VarChar, 200), new OracleParameter(":ENTRYTIME", OracleType.DateTime), new OracleParameter(":EXITSTATION", OracleType.Number, 4), new OracleParameter(":EXITSTATIONNAME", OracleType.VarChar, 200), new OracleParameter(":EXITTIME", OracleType.DateTime), new OracleParameter(":PAYTYPE", OracleType.VarChar, 200) }; #endregion OracleConnection OraConnection = new OracleConnection(OracleHelper.ConnectionString); OraConnection.Open(); OracleCommand Oracommand = OraConnection.CreateCommand(); Oracommand.CommandType = CommandType.Text; Oracommand.CommandText = strSql.ToString(); #endregion #region Begin try { while (dataRead.Read()) { parameters[0].Value = dataRead["AXISNUM"]; parameters[1].Value = dataRead["OVERLOADRATE"]; parameters[2].Value = dataRead["TOTALWEIGHT"]; parameters[3].Value = dataRead["TOTALTOLL"]; parameters[4].Value = dataRead["DISTANCE"]; parameters[5].Value = dataRead["VEHICLELICENSE"]; parameters[6].Value = dataRead["ENTRYSTATION"]; parameters[7].Value = dataRead["ENTRYSTATIONNAME"]; parameters[8].Value = dataRead["ENTRYTIME"]; parameters[9].Value = dataRead["EXITSTATION"]; parameters[10].Value = dataRead["EXITSTATIONNAME"]; parameters[11].Value = dataRead["EXITTIME"]; parameters[12].Value = dataRead["PAYTYPE"]; foreach (OracleParameter pm in parameters) { Oracommand.Parameters.Add(pm); } Oracommand.ExecuteNonQuery(); Oracommand.Parameters.Clear(); size++; if (On_CompleteSingle != null) { On_CompleteSingle(size, count); } } } catch (Exception ex) { throw ex; } finally { connection.Close(); connection.Dispose(); } #endregion } }
public ActionResult Create(FormCollection collection, HttpPostedFileBase ArchAuto, HttpPostedFileBase ArchManual) { try { if (ArchAuto != null && ArchAuto.ContentLength > 0 && ArchAuto.FileName.EndsWith("txt") && ArchManual != null && ArchManual.ContentLength > 0 && (ArchManual.FileName.EndsWith("xls") || ArchManual.FileName.EndsWith("xlsx")) ) { string pathArchManual = ""; if (!Directory.Exists(Server.MapPath("Excels"))) { Directory.CreateDirectory(Server.MapPath("Excels")); } var readerArchAuto = new BinaryReader(ArchAuto.InputStream); string resultArchAuto = System.Text.Encoding.UTF8.GetString(readerArchAuto.ReadBytes(ArchAuto.ContentLength)); string[] lineasArchAuto = Regex.Split(resultArchAuto, "\r\n"); Relevamientos objRelevamiento = new Relevamientos(); objRelevamiento.Observaciones = collection["Observaciones"]; objRelevamiento.FechaCarga = DateTime.Now; objRelevamiento.FechaInicio = DateTime.Parse(lineasArchAuto[0].Split(';')[0].ToString()); objRelevamiento.FechaFinal = DateTime.Parse(lineasArchAuto[lineasArchAuto.Length - 1].Split(';')[0].ToString()); objRelevamiento.IdEstado = 1; objRelevamiento.IdTrampa = int.Parse(lineasArchAuto[0].Split(';')[2].ToString()); db.Relevamientos.Add(objRelevamiento); //db.SaveChanges(); pathArchManual = Server.MapPath("Excels") + @"\" + ArchManual.FileName; if (System.IO.File.Exists(pathArchManual)) { System.IO.File.Delete(pathArchManual); } ArchManual.SaveAs(pathArchManual); string cnnStr = ""; if (pathArchManual.EndsWith(".xlsx")) { //Excel 2007 cnnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; cnnStr += ";Data Source=" + pathArchManual + ";"; } else { //Excel 97-2003 //http://www.connectionstrings.com/excel (leer sobre la clave de registro TypeGuessRows) cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; cnnStr += ";Data Source=" + pathArchManual + ";"; } System.Data.OleDb.OleDbConnection oCnn = new System.Data.OleDb.OleDbConnection(cnnStr); System.Data.OleDb.OleDbDataAdapter oDa = null; DataTable dtArchManual = new DataTable(); try { oCnn.Open(); //Obtenemos los nombres de las hojas del Excel. DataTable dtHojas = oCnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); if (dtHojas.Rows.Count > 0) { string firstSheet = dtHojas.Rows[0]["TABLE_NAME"].ToString().Trim(); string selectCmd = "select * from [" + firstSheet + "]"; oDa = new System.Data.OleDb.OleDbDataAdapter(selectCmd, oCnn); oDa.Fill(dtArchManual); } oCnn.Close(); dtArchManual.Columns.Add("Fecha"); foreach (DataRow drFila in dtArchManual.Rows) { int iAño = 0; int iMes = 0; int iDia = 0; int.TryParse(drFila[0].ToString(), out iAño); int.TryParse(drFila[1].ToString(), out iMes); int.TryParse(drFila[2].ToString(), out iDia); if (iAño > 0 && iMes > 0 && iDia > 0) { DateTime tFecha = new DateTime(iAño, iMes, iDia); drFila["Fecha"] = tFecha.Date.ToShortDateString(); } if (drFila["Fecha"].ToString() == objRelevamiento.FechaFinal.ToShortDateString()) { foreach (DataColumn dcColumna in dtArchManual.Columns) { if (dcColumna.Ordinal > 2) { //CORREGIR SELECCION DE INSECTO Insectos objInsecto = (from obj in db.Insectos where obj.NombreCientifico == dcColumna.ColumnName select obj).FirstOrDefault(); if (objInsecto != null) { int Cantidad = 0; int.TryParse(drFila[dcColumna.Ordinal].ToString(), out Cantidad); if (Cantidad > 0) { LecturasManuales objLecturasManuales = new LecturasManuales(); objLecturasManuales.IdRelevamiento = objRelevamiento.IdRelevamiento; objLecturasManuales.IdInsecto = objInsecto.IdInsecto; objLecturasManuales.Cantidad = Cantidad; objLecturasManuales.IdEstado = 1; db.LecturasManuales.Add(objLecturasManuales); } } } } //db.SaveChanges(); break; } } } catch (Exception ex) { throw ex; } finally { if (oCnn.State == ConnectionState.Open) { oCnn.Close(); } } if (oDa != null) { oDa.Dispose(); } if (oCnn != null) { oCnn.Dispose(); } //var objRelevamiento2 = (from obj in db.Relevamientos select obj).OrderByDescending(i => i.IdRelevamiento).First(); foreach (string Linea in lineasArchAuto) { string[] arrDatos = Linea.Split(';'); if (arrDatos.Length > 1 && arrDatos[1].ToString() == "LECTURA") { Lecturas objLecturas = new Lecturas(); objLecturas.IdEstado = 1; objLecturas.IdRelevamiento = objRelevamiento.IdRelevamiento; objLecturas.Frecuencia = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture); objLecturas.Aleteos = int.Parse(arrDatos[3]); objLecturas.FechaLectura = DateTime.Parse(arrDatos[0]); db.Lecturas.Add(objLecturas); } else if (arrDatos.Length > 1 && arrDatos[1].ToString() == "ESTADO") { Monitoreos objMonitoreos = new Monitoreos(); objMonitoreos.IdEstado = 1; objMonitoreos.IdRelevamiento = objRelevamiento.IdRelevamiento; objMonitoreos.Humedad = double.Parse(arrDatos[3], System.Globalization.CultureInfo.InvariantCulture); objMonitoreos.Temperatura = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture); objMonitoreos.Bateria = double.Parse(arrDatos[4], System.Globalization.CultureInfo.InvariantCulture); objMonitoreos.FechaMonitoreo = DateTime.Parse(arrDatos[0]); db.Monitoreos.Add(objMonitoreos); } } db.SaveChanges(); return(Redirect("~/Alarmas/GenerarAlarmas")); } else { return(View()); } } catch (Exception ex) { throw ex; } }
public List <DataTable> ReadXLSFile(List <string> listFiles) { listTables = new List <DataTable>(); DataSet dataSet1 = new DataSet(); DataTable dt = new DataTable(); String[] sheetName = null; System.Data.OleDb.OleDbDataAdapter dataAdapter1; try { foreach (var item in listFiles) { if (System.IO.Path.GetExtension(item).ToLower().Equals(".csv")) { dt = CSVReader.GetDataTable(item); if (null == dt) { //log return(null); } listTables.Add(dt); } else { //not cvs file -> XLSX file //reading Excel sheets names if (InitConnectionString(item) == false) { Log.Write(" InitConnectionString(item) == false", LogMessageType.Warning); // Easynet.Edge.Services.FileImport.Classes.Convertors.Log.Write(@"D:\log.txt", "InitConnectionString(item) == false"); return(null); } if (OpenConnection() == false) { Log.Write("OpenConnection() == false", LogMessageType.Warning); // Easynet.Edge.Services.FileImport.Classes.Convertors.Log.Write(@"D:\log.txt", "Cannot open connection !"); //write.WriteLine("Cannot open connection !"); return(null); } //write.WriteLine(" open connection !"); dt = _connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); sheetName = new String[dt.Rows.Count]; int i = 0; foreach (DataRow row in dt.Rows) { sheetName[i] = row["TABLE_NAME"].ToString(); i++; } dataAdapter1 = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName[0] + "]", _connection); dataSet1 = new DataSet(); dataAdapter1.Fill(dataSet1); // write.WriteLine(dataSet1.Tables[0].Rows.Count); listTables.Add(dataSet1.Tables[0]); Log.Write(" dataSet1.Tables[0]: rows count: " + dataSet1.Tables[0].Rows.Count, LogMessageType.Information); _connection.Close(); } } _connection.Close(); // Easynet.Edge.Services.FileImport.Classes.Convertors.Log.Write(@"D:\log.txt", "_connection.Close();"); return(listTables); } catch (Exception ex) { Log.Write("error ReadXLSFile: ", ex); // Easynet.Edge.Services.FileImport.Classes.Convertors.Log.Write(@"D:\log.txt", "error ReadXLSFile: " + ex.Message); //WriteToEventLog("Converter: \n" + ex.ToString()); _connection.Close(); _connection.Dispose(); dt.Dispose(); dataSet1.Dispose(); return(null); } }
public DataSet SaveItem(string sortfield) { System.Data.OleDb.OleDbConnection conn = GetConnection(); try { string SQL = default(string); SQL = "INSERT INTO TABLE1 (STUDENTID, DATA1, DATA2, DATA3, DATA4, DATA5,DATA6, DATA7," + "DATA8, DATA9, DATA10, DATA11, DATA12, DATA13, DATA14, DATA15, DATA16, DATA17, DATA18," + "DATA19) VALUES(@STUDENTID, @DATA1, @DATA2, @DATA3, @DATA4, @DATA5,@DATA6, @DATA7,DATA8, @DATA9," + "@DATA10, @DATA11, @DATA12, @DATA13, @DATA14, @DATA15, @DATA16, @DATA17, @DATA18, @DATA19)"; System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(SQL, conn); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@STUDENTID", Form1.Default.STUDID.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA1", Form1.Default.TextBox1.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA2", Form1.Default.TextBox2.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA3", Form1.Default.TextBox3.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA4", Form1.Default.TextBox4.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA5", Form1.Default.TextBox5.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA6", Form1.Default.TextBox6.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA7", Form1.Default.TextBox7.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA8", Form1.Default.TextBox8.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA9", Form1.Default.TextBox9.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA10", Form1.Default.TextBox10.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA11", Form1.Default.TextBox11.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA12", Form1.Default.TextBox12.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA13", Form1.Default.TextBox13.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA14", Form1.Default.TextBox14.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA15", Form1.Default.TextBox15.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA16", Form1.Default.TextBox16.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA17", Form1.Default.TextBox17.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA18", Form1.Default.TextBox18.Text)); cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA19", Form1.Default.TextBox19.Text)); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); MessageBox.Show("New data is Added!"); Form1.Default.TextBox1.Text = ""; Form1.Default.TextBox2.Text = ""; Form1.Default.TextBox3.Text = ""; Form1.Default.TextBox4.Text = ""; Form1.Default.TextBox5.Text = ""; Form1.Default.TextBox6.Text = ""; Form1.Default.TextBox7.Text = ""; Form1.Default.TextBox8.Text = ""; Form1.Default.TextBox9.Text = ""; Form1.Default.TextBox10.Text = ""; Form1.Default.TextBox11.Text = ""; Form1.Default.TextBox12.Text = ""; Form1.Default.TextBox13.Text = ""; Form1.Default.TextBox14.Text = ""; Form1.Default.TextBox15.Text = ""; Form1.Default.TextBox16.Text = ""; Form1.Default.TextBox17.Text = ""; Form1.Default.TextBox18.Text = ""; Form1.Default.TextBox19.Text = ""; Form1.Default.STUDID.Text = ""; RefreshDGV(); } finally { conn.Close(); conn.Dispose(); } return(default(DataSet)); }
/// <summary> /// InsertData /// </summary> /// <param name="fileName">模板文件在服务器端的完全路径</param> /// <param name="theData"></param> private bool InsertData(string fileName, DataTable theData) { // connection use oledb DataTable dt = theData; string strConn = string.Format(CONNECTION_STRING_WRITE, fileName); System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); System.Data.OleDb.OleDbCommand cmd = null; bool bRet = false; try { conn.Open(); //cmd = new System.Data.OleDb.OleDbCommand("Create table [sheet1]([name] Text, [age] int, [phone] Text)", conn); //cmd.ExecuteNonQuery(); string strSQL = InsertSQL; cmd = new System.Data.OleDb.OleDbCommand(strSQL, conn); for (int i = 0; i < dt.Columns.Count; i++) { cmd.Parameters.Add(i.ToString(), System.Data.OleDb.OleDbType.VarChar); } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { cmd.Parameters[j.ToString()].Value = dt.Rows[i][j].ToString(); } cmd.ExecuteNonQuery(); } bRet = true; } catch (Exception e2) { bRet = false; } finally { if (cmd != null) { cmd.Dispose(); } conn.Dispose(); } return bRet; }
public static bool DoExport(ExcelXMLLayout layout) { try { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(DB.ExcelFile.MakeConnectionString(layout.solution.path)); StringBuilder sb = new StringBuilder(1024 * 1024); try { conn.Open(); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand( DB.ExcelFile.MakeSelectString(layout), conn); try { System.Data.OleDb.OleDbDataReader r = cmd.ExecuteReader(); sb.Append("<?xml version='1.0' encoding='utf-8'?>"); sb.Append("<dataroot>"); while (r.Read()) { if (IsHeadRow(r, layout)) { continue; } sb.AppendFormat("<{0}>", layout.name); for (int i = 0; i < r.FieldCount; ++i) { if (layout.typed) { sb.AppendFormat("<{0} type='{1}'>{2}</{0}>", layout[i].name, ExcelXMLLayout.Type2Int(layout[i].type), r[i].ToString()); } else { sb.AppendFormat("<{0}>{1}</{0}>", layout[i].name, r[i].ToString()); } } sb.AppendFormat("</{0}>", layout.name); } sb.Append("</dataroot>"); r.Dispose(); } catch (Exception rErr) { lastError = string.Format("表格查询命令\r\n{0}", rErr.ToString()); cmd.Dispose(); conn.Close(); conn.Dispose(); return false; } cmd.Dispose(); conn.Close(); conn.Dispose(); } catch (Exception connOpenErr) { lastError = string.Format("表格打开失败\r\n{0}", connOpenErr.ToString()); conn.Dispose(); return false; } //System.Diagnostics.Debug.WriteLine(sb.ToString()); try { System.IO.TextWriter w = System.IO.File.CreateText(layout.path); w.Write(sb.ToString()); w.Close(); } catch (Exception wErr) { lastError = string.Format("目标XML文件无法写入\r\n{0}", wErr.ToString()); return false; } return true; } catch (Exception outErr) { lastError = string.Format("Excel 无法打开\r\n{0}", outErr.ToString()); System.Diagnostics.Debug.WriteLine(outErr.ToString()); } return false; }
private void btnRun_Click(object sender, EventArgs e) { string sDataSourceFox = txtDBName.Text.Trim(); string sCnnStrJet = "Provider=" + sProviderJet + "; Data Source=" + sDataSourceJet; string sCnnStrFox = "Provider=" + sProviderFox + "; Data Source=" + sDataSourceFox; // Fox System.Data.OleDb.OleDbConnection cnnFox = null; System.Data.OleDb.OleDbCommand cmdFox = null; System.Data.OleDb.OleDbDataAdapter daFox = null; System.Data.DataSet daSet = null; //MDB System.Data.OleDb.OleDbConnection cnnJet = null; System.Data.OleDb.OleDbCommand cmdJet = null; string sSqlFox = "select * from " + System.IO.Path.GetFileName(sDataSourceFox.Trim()); bool is99 = false; string sPagina = null; int iTotalImg = 0; int iTotalExp = 0; string INSUREDID = string.Empty; string PROVIDERNPI = string.Empty; string SERVICEDATE = string.Empty; string NOMBREIMAGEN = string.Empty; try { //fox cnnFox = new System.Data.OleDb.OleDbConnection(); cmdFox = new System.Data.OleDb.OleDbCommand(); daFox = new System.Data.OleDb.OleDbDataAdapter(); daSet = new DataSet(); //mdb cnnJet = new System.Data.OleDb.OleDbConnection(); cmdJet = new System.Data.OleDb.OleDbCommand(); if (cnnFox.State == ConnectionState.Closed) { cnnFox.ConnectionString = sCnnStrFox; cnnFox.Open(); } cmdFox.Connection = cnnFox; cmdFox.CommandType = CommandType.Text; cmdFox.CommandText = sSqlFox; daFox.SelectCommand = cmdFox; daFox.Fill(daSet); if (cnnJet.State == ConnectionState.Closed) { cnnJet.ConnectionString = sCnnStrJet; cnnJet.Open(); } cmdJet.Connection = cnnJet; cmdJet.CommandType = CommandType.Text; cmdJet.CommandText = "delete from OHCINDEX"; cmdJet.ExecuteNonQuery(); foreach (DataTable T in daSet.Tables) { foreach (DataRow R in T.Rows) { iTotalImg++; sPagina = R["V1PG"].ToString().Trim(); if (sPagina != "99") { is99 = false; iTotalExp++; } else { is99 = true; } if (is99 == false) { INSUREDID = R["V1INSUREID"].ToString().Trim(); PROVIDERNPI = RB1500.Checked ? R["V133ANPI"].ToString().Trim() : R["V156NPI"].ToString().Trim(); SERVICEDATE = R["V1DATEFROM"].ToString().Trim(); NOMBREIMAGEN = "D:\\OHC\\" + R["V0IFNAME01"].ToString().Trim(); } else { NOMBREIMAGEN = "D:\\OHC\\" + R["V0IFNAME01"].ToString().Trim(); } /* * InsuredId ProviderNpi ServiceDate ImageFile */ string sSqlJet = "insert into OHCINDEX VALUES('" + INSUREDID + "','" + PROVIDERNPI + "','" + SERVICEDATE + "','" + NOMBREIMAGEN + "')"; cmdJet.CommandText = sSqlJet.Trim(); cmdJet.ExecuteNonQuery(); } }//end Loop //Close cnnFox.Close(); cnnJet.Close(); cnnJet.Dispose(); cnnFox.Dispose(); // Copy MDB .... // Program.copyImg(this.txtOutPut.Text.Trim(), sMdbPath, true, "*.mdb"); MessageBox.Show("Total de Imagenes:\r" + iTotalImg.ToString(), "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); MessageBox.Show("Total de Record:\r" + iTotalExp.ToString(), "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); MessageBox.Show("Done .. \r:-) Lindo Dia ", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public static List <TableInfo> GetShemaTables(DbConfig config = null) { if (config == null) { config = DbConfig.Default; } List <TableInfo> tables = new List <TableInfo>(); DataTable shemaTables = null; int tabNameIndex = 0, tabRowCount = 0; if (config.DbType == "System.Data.OleDb") { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = config.ConnectionString; conn.Open(); shemaTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); tabNameIndex = shemaTables.Columns.IndexOf("TABLE_NAME"); tabRowCount = shemaTables.Rows.Count; for (int i = 0; i < tabRowCount; i++) { DataRow tabDataRow = shemaTables.Rows[i]; string strTable = tabDataRow.ItemArray.GetValue(tabNameIndex).ToString(); DataTable dtColumns = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, strTable, null }); DataTable dtPrimarys = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Primary_Keys, new string[] { null, null, strTable }); int tColumnsCount = dtColumns.Rows.Count; int tColumnNameIndex = dtColumns.Columns.IndexOf("COLUMN_NAME"); int tColumnTypeNameIndex = dtColumns.Columns.IndexOf("DATA_TYPE"); TableInfo tInfo = new TableInfo(); tInfo.Name = strTable; tInfo.ColList = new List <ColumnInfo>(); for (int j = 0; j < tColumnsCount; j++) { DataRow drColumn = dtColumns.Rows[j]; ColumnInfo cInfo = new ColumnInfo(); cInfo.Name = drColumn.ItemArray.GetValue(tColumnNameIndex).ToString(); cInfo.TypeName = GetCSharpTypeByAccess(drColumn.ItemArray.GetValue(tColumnTypeNameIndex).ToString()); cInfo.Description = string.Empty; foreach (DataRow row in dtPrimarys.Rows) { if (row["COLUMN_NAME"].ToString() == cInfo.Name) { cInfo.IsPrimaryKey = true; cInfo.IsIdentity = true; break; } } tInfo.ColList.Add(cInfo); } tables.Add(tInfo); } conn.Close(); conn.Dispose(); conn = null; } else { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = config.ConnectionString; conn.Open(); shemaTables = conn.GetSchema("Tables"); DataTable dtColumns = conn.GetSchema("Columns"); int colIndex = dtColumns.Columns.IndexOf("COLUMN_NAME"); int typIndex = dtColumns.Columns.IndexOf("DATA_TYPE"); tabNameIndex = shemaTables.Columns.IndexOf("TABLE_NAME"); tabRowCount = shemaTables.Rows.Count; string sqlColumns = "SELECT OBJECT_NAME(c.object_id) [TableName],c.name [ColumnName],ISNULL(ex.value,'') [DescriptionName] FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0"; DataTable dtColumnDescriptions = new DataTable(); var adapter = new System.Data.SqlClient.SqlDataAdapter(sqlColumns, conn); adapter.Fill(dtColumnDescriptions); sqlColumns = "select o.name as [TableName],c.name as [ColumnName] from sysindexes i join sysindexkeys k on i.id = k.id and i.indid = k.indid join sysobjects o on i.id = o.id join syscolumns c on i.id=c.id and k.colid = c.colid where o.xtype = 'U' and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name)"; DataTable dtColumnPrimaryKeys = new DataTable(); adapter = new System.Data.SqlClient.SqlDataAdapter(sqlColumns, conn); adapter.Fill(dtColumnPrimaryKeys); sqlColumns = "select b.name as [TableName],a.name as [ColumnName],a.is_identity as [IsIdentity] from sys.columns a inner join sys.objects b on a.object_id=b.object_id "; DataTable dtColumnIdentitys = new DataTable(); adapter = new System.Data.SqlClient.SqlDataAdapter(sqlColumns, conn); adapter.Fill(dtColumnIdentitys); for (int i = 0; i < tabRowCount; i++) { DataRow itemDataRow = shemaTables.Rows[i]; string strTable = itemDataRow.ItemArray.GetValue(tabNameIndex).ToString(); DataRow[] drColumns = dtColumns.Select("TABLE_NAME='" + strTable + "'"); int drColumnsLength = drColumns.Length; DataRow[] drDescriptions = dtColumnDescriptions.Select("TableName='" + strTable + "'"); DataRow[] drPrimaryKeys = dtColumnPrimaryKeys.Select("TableName='" + strTable + "'"); DataRow[] drIdentitys = dtColumnIdentitys.Select("TableName='" + strTable + "'"); TableInfo tInfo = new TableInfo(); tInfo.Name = strTable; tInfo.ColList = new List <ColumnInfo>(); for (int j = 0; j < drColumnsLength; j++) { DataRow tmpDataRow = drColumns[j]; ColumnInfo cInfo = new ColumnInfo(); cInfo.Name = tmpDataRow.ItemArray.GetValue(colIndex).ToString(); cInfo.TypeName = GetCSharpTypeBySqlServer(tmpDataRow.ItemArray.GetValue(typIndex).ToString()); var tmpDescription = drDescriptions.FirstOrDefault(m => m["ColumnName"].ToString() == cInfo.Name); cInfo.Description = (tmpDescription != null) ? tmpDescription["DescriptionName"].ToString() : string.Empty; var tmpPrimaryKey = drPrimaryKeys.FirstOrDefault(m => m["ColumnName"].ToString() == cInfo.Name); cInfo.IsPrimaryKey = (tmpPrimaryKey != null); var tmpIsIdentity = drIdentitys.FirstOrDefault(m => m["ColumnName"].ToString() == cInfo.Name); cInfo.IsIdentity = (tmpIsIdentity != null ? (tmpIsIdentity["IsIdentity"].ToString().ToLower() == "true" || tmpIsIdentity["IsIdentity"].ToString() == "1") : false); tInfo.ColList.Add(cInfo); } tables.Add(tInfo); } conn.Close(); conn.Dispose(); conn = null; } return(tables); }
public System.Data.DataSet GetDataSet_Excel(HttpPostedFileBase file, out string ErrorMessage) { using (iGst_Svc.GSTServiceClient iGstSvc = new iGst_Svc.GSTServiceClient()) { System.Data.DataSet ds = null; ErrorMessage = ""; try { System.Data.OleDb.OleDbConnection conn = null; System.Data.OleDb.OleDbCommand cmd = null; System.Data.OleDb.OleDbDataAdapter da = null; string TableName = "", ConnectionString = ""; var fileName = System.IO.Path.GetFileName(file.FileName); var path = System.IO.Path.Combine(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["UploadDataPath"].ToString()), fileName); switch (System.IO.Path.GetExtension(path).Trim().ToUpper()) { case ".XLS": ConnectionString = string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel03ConnStr"].ConnectionString, path); break; case ".XLSX": ConnectionString = string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel07ConnStr"].ConnectionString, path); break; default: ErrorMessage = "Please select a valid excel file"; break; } if (ErrorMessage.Trim().Length == 0) { try { try { file.SaveAs(path); } catch (Exception ex) { Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - Saveas", "Error"); ErrorMessage = "Problem in saving file:" + ex.Message; return(null); } try { conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel15ConnStr"].ConnectionString, path)); conn.Open(); } catch { try { conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel14ConnStr"].ConnectionString, path)); conn.Open(); } catch { try { conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel13ConnStr"].ConnectionString, path)); conn.Open(); } catch { try { conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel07ConnStr"].ConnectionString, path)); conn.Open(); } catch { try { conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel03ConnStr"].ConnectionString, path)); conn.Open(); } catch (Exception ex) { Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - Connection", "Error"); ErrorMessage = "Problem in saving file:" + ex.Message; return(null); } } } } } try { TableName = conn.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString(); cmd = new System.Data.OleDb.OleDbCommand(@"SELECT * FROM [" + TableName + "]", conn); da = new System.Data.OleDb.OleDbDataAdapter(cmd); ds = new System.Data.DataSet(); da.Fill(ds); da.Dispose(); cmd.Dispose(); conn.Dispose(); return(ds); } catch (Exception ex) { Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - GetDataSet", "Error"); ErrorMessage = "Inner Problem in reading file"; return(null); } } catch (Exception ex) { Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - GetDataSet", "Error"); ErrorMessage = "Problem in reading file"; return(null); } } if (System.IO.File.Exists(path)) { System.IO.File.Delete(path); } } catch (Exception ex) { ErrorMessage = "Problem in reading file" + ex.Message; return(null); } return(null); } }
private void ExportToXL(string sheetToCreate, List <DataRow> selectedRows, DataTable origDataTable, string tableName) { char Space = ' '; string dest = sheetToCreate; if (File.Exists(dest)) { File.Delete(dest); } sheetToCreate = dest; if (tableName == null) { tableName = string.Empty; } tableName = tableName.Trim().Replace(Space, '_'); if (tableName.Length == 0) { tableName = origDataTable.TableName.Replace(Space, '_'); } if (tableName.Length == 0) { tableName = "NoTableName"; } if (tableName.Length > 30) { tableName = tableName.Substring(0, 30); } //Excel names are less than 31 chars string queryCreateExcelTable = "CREATE TABLE [" + tableName + "] ("; Dictionary <string, string> colNames = new Dictionary <string, string>(); foreach (DataColumn dc in origDataTable.Columns) { //Cause the query to name each of the columns to be created. string modifiedcolName = dc.ColumnName;//.Replace(Space, '_').Replace('.', '#'); string origColName = dc.ColumnName; colNames.Add(modifiedcolName, origColName); queryCreateExcelTable += "[" + modifiedcolName + "]" + " text,"; } queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")"; //adds the closing parentheses to the query string if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls")) { //use Excel 2007 for large sheets. sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx"; } string strCn = string.Empty; string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower(); if (ext == ".xls") { strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sheetToCreate + "; Extended Properties='Excel 8.0;HDR=YES'"; } if (ext == ".xlsx") { strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Xml;HDR=YES' "; } if (ext == ".xlsb") { strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0;HDR=YES' "; } if (ext == ".xlsm") { strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Macro;HDR=YES' "; } System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(strCn); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn); cn.Open(); cmd.ExecuteNonQuery(); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "]", cn); System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da); //creates the INSERT INTO command cb.QuotePrefix = "["; cb.QuoteSuffix = "]"; cmd = cb.GetInsertCommand(); //gets a hold of the INSERT INTO command. foreach (DataRow row in selectedRows) { foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters) { param.Value = row[colNames[param.SourceColumn.Replace('#', '.')]]; } cmd.ExecuteNonQuery(); //INSERT INTO command. } cn.Close(); cn.Dispose(); da.Dispose(); //GC.Collect(); //GC.WaitForPendingFinalizers(); }
private void PrepareDatabase() { string sConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" + TempBurnPath + @"\Data\RODB.DAT;Mode=Read|Write|Share Deny None;"; System.Data.OleDb.OleDbConnection AC = new System.Data.OleDb.OleDbConnection(sConnection); try { AC.Open(); } catch(System.Data.OleDb.OleDbException Err) { MessageBox.Show(Err.Message); string peek = Err.Message; return; } catch(Exception Err) { MessageBox.Show(Err.Message); string sPeek = Err.Message; return; } #region TheWork System.Data.OleDb.OleDbCommand CM = new System.Data.OleDb.OleDbCommand(); CM.Connection = AC; System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter(); System.Data.DataTable DT = new System.Data.DataTable(); foreach(OCL.RecordingSession RS in SelectedRecordingSessions) { string sSQL = @"INSERT INTO tblSession(Description,IsPresentation,OwnerId,CreatedDate,IsOysterSession) VALUES('" + RS.Description + "'" + "," +Convert.ToInt32(RS.IsPresentation) + "," + 23 + ",'" + RS.CreatedDate +"'" + "," + Convert.ToInt32(true) + ")"; int SessionID = 0; try { CM.CommandText = sSQL; CM.ExecuteNonQuery(); CM.CommandText = "SELECT @@IDENTITY"; SessionID = (int)CM.ExecuteScalar(); } catch(System.Data.OleDb.OleDbException Err) { MessageBox.Show(Err.Message); string peek = Err.Message; return; } catch(Exception Err) { MessageBox.Show(Err.Message); string sPeek = Err.Message; return; } foreach(OCL.Recording R in RS.CurrentRecordings(frmParent.LUser)) { string FileName = ""; string FileExt = ""; string[] sFileName = R.Description.Split(".".ToCharArray()); if(sFileName.Length == 1) { FileExt = ""; } else if(sFileName.Length == 2) { FileExt = "." + sFileName[1]; } FileName = RS.Description + "_" + R.DisplayName; string NewDescription = RenameFile(FileName,FileExt); sSQL = @"INSERT INTO tblRecording(Name,Created,VideoStorageServerId," + "DisplayName, StreamingEncoderId,SessionId," + "IsDesktopCapture,IsPrimaryCamera,DiskSize,Playlength," + "HasAudio,HasVideo) " + "VALUES ('" + NewDescription + "', '" + R.CreatedDate + "', 9" + ", '" + R.DisplayName + "', 15" + "," + SessionID + "," + Convert.ToInt32(R.IsDesktopCapture) + "," + Convert.ToInt32(R.IsPrimaryCamera) + "," + R.SizeOnDisk + "," + R.PlayLength + "," + Convert.ToInt32(R.HasAudio) + "," + Convert.ToInt32(R.HasVideo) + ")"; try { CM.CommandText = sSQL; CM.ExecuteNonQuery(); } catch(System.Data.OleDb.OleDbException Err) { MessageBox.Show(Err.Message); string peek = Err.Message; return; } catch(Exception Err) { MessageBox.Show(Err.Message); string sPeek = Err.Message; return; } } foreach(OCL.Note N in RS.AllVisibleNotes(frmParent.LUser)) { int NoteID = 0; sSQL = "INSERT INTO tblNotes(SyncTime,Description,SessionId,Created) VALUES('" + N.SyncTime + "','" + N.Description + "'," + SessionID + ",'" + N.Created +"'" + ")"; CM.CommandText = sSQL; try { CM.CommandText = sSQL; CM.ExecuteNonQuery(); CM.CommandText = "SELECT @@IDENTITY"; NoteID = (int)CM.ExecuteScalar(); } catch(System.Data.OleDb.OleDbException Err) { MessageBox.Show(Err.Message); string peek = Err.Message; return; } catch(Exception Err) { MessageBox.Show(Err.Message); string sPeek = Err.Message; return; } OCL.Attachments FileAttachments = N.AllVisibleAttachments(frmParent.LUser); foreach(OCL.Attachment A in FileAttachments) { sSQL = "INSERT INTO tblAttachments(OriginalName,StoredName,NoteId,Created,FileSize) VALUES(" + " '" + A.OriginalName + "','" + A.StoredName + "'," + NoteID.ToString() + ",'" + A.Created + "'" + "," + A.FileSize + ")"; CM.CommandText = sSQL; try { CM.ExecuteNonQuery(); } catch(System.Data.OleDb.OleDbException Err) { MessageBox.Show(Err.Message); string peek = Err.Message; return; } catch(Exception Err) { MessageBox.Show(Err.Message); string sPeek = Err.Message; return; } } } } DA.Dispose(); DT.Dispose(); CM.Dispose(); #endregion AC.Close(); AC.Dispose(); System.Threading.Thread.SpinWait(500000); }
private void btnImport_Click(object sender, EventArgs e) { Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook; Excel.Worksheet activeSheet = (Excel.Worksheet)WB.ActiveSheet; List <int> deletes = new List <int>(); string connString = Utilities.Constants.EXCEL_CONNSTRING; for (int i = 0; i < checkedListBox2.Items.Count; i++) { if (checkedListBox2.GetItemChecked(i)) { SourceFile fl = (SourceFile)checkedListBox2.Items[i]; string flnm = fl.FileName.Substring(fl.FileName.LastIndexOf("\\")).Replace("\\", ""); // create new worksheet string newSheetNm = ExcelHelpers.CreateValidWorksheetName(WB, fl.ShortName, 0); Excel.Worksheet newSheet = (Excel.Worksheet)WB.Sheets.Add(System.Type.Missing, WB.Sheets[WB.Sheets.Count], System.Type.Missing, System.Type.Missing); newSheet.Name = newSheetNm; // add custom properties ExcelHelpers.addWorksheetCustomProperty(newSheet, Utilities.Constants.WS_ROLLUP, "TRUE"); ExcelHelpers.addWorksheetCustomProperty(newSheet, Utilities.Constants.WS_SRCFILE, flnm); // import the data DataTable dt = new DataTable(); connString = connString.Replace("{0}", fl.FileName); System.Data.OleDb.OleDbConnection aConn = new System.Data.OleDb.OleDbConnection(connString); aConn.Open(); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(fl.SQLStatement, aConn); try { da.Fill(dt); } catch { MessageBox.Show("An error was encountered while attempting to import sheets from workbook " + this.openFileDialog1.FileNames.GetValue(i).ToString() + ". Please re-run the data in a new workbook before including it in the corporate roll-up."); break; } aConn.Close(); aConn.Dispose(); // copy the data to the new sheet Excel.Range rng = newSheet.Rows.get_Resize(dt.Rows.Count + 1, dt.Columns.Count + 1).get_Offset(3, 0); //rng.get_Offset(0, 0).get_Resize(1, 1).Value2 = "Name"; for (int c = 0; c < dt.Columns.Count; c++) { rng.get_Offset(0, c).get_Resize(1, 1).Value2 = dt.Columns[c].ColumnName; } rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, dt.Columns.Count).Value2 = DataHelper.dataTableArrayObject(dt); //rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, 1).Formula = fl.ShortName;//"=IFERROR(RIGHT(CELL(\"filename\",$A$1), LEN(CELL(\"filename\",$A$1)) - FIND(\"]\",CELL(\"filename\",$A$1),1)),\"\")"; object[,] tmp1 = (object[, ])rng.Value2; bool hasProd = false; bool hasBuildSqFt = false; fromEnergyCost = false; foreach (DataRow dr in dt.Rows) { if (dr[0].ToString().Contains("Total Production Output") || dr[0].ToString().Contains("Production Energy Intensity (MMBtu/unit production)")) { hasProd = true; } if (dr[0].ToString().Contains("Building Energy Intensity")) { hasBuildSqFt = true; } if (dr[0].ToString().Contains("Estimated Cost Savings")) { fromEnergyCost = true; } } //----------------------------------------------- newSheet.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);//.AddEx(Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing, System.Type.Missing); DetailTable newdt = new DetailTable(newSheet.ListObjects[1], fl.ShortName, fl.numOfSources, fl.fromActual, hasProd, hasBuildSqFt, true, fromEnergyCost); newdt.DisplayName = newSheetNm + " (" + flnm + ")"; tables.Add(newdt); } } files.Clear(); checkedListBox2.Refresh(); checkedListBox2.Visible = false; btnImport.Visible = false; // reload the tables box AddTablesToList(); activeSheet.Activate(); // // copy the data to the new sheet // int jslkdf = dt.Rows.Count; // Excel.Range rng = newSheet.get_Range("A1").get_Resize(((dt.Rows.Count + 1)/2)+1, dt.Columns.Count + 1); // rng.get_Offset(0, 0).get_Resize(1, 1).Value2 = "Name"; // for (int c = 0; c < dt.Columns.Count; c++) // { // rng.get_Offset(0, c + 1).get_Resize(1,1).Value2 = dt.Columns[c].ColumnName; // } // rng.get_Offset(1, 1).get_Resize(dt.Rows.Count, dt.Columns.Count).Value2 = DataHelper.dataTableArrayObject(dt); // rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, 1).Formula = "=IFERROR(RIGHT(CELL(\"filename\",$A$1), LEN(CELL(\"filename\",$A$1)) - FIND(\"]\",CELL(\"filename\",$A$1),1)),\"\")"; // object[,] tmp1 = (object[,])rng.Value2; // newSheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing, System.Type.Missing); // DetailTable newdt = new DetailTable(newSheet.ListObjects[1], fl.ShortName); // newdt.DisplayName = newSheetNm + " (" + flnm + ")"; // tables.Add(newdt); // } //} //files.Clear(); //checkedListBox2.Refresh(); //checkedListBox2.Visible = false; //btnImport.Visible = false; //// reload the tables box //AddTablesToList(); //activeSheet.Activate(); }
public ActionResult Create(FormCollection collection, HttpPostedFileBase ArchAuto, HttpPostedFileBase ArchManual) { try { if (ArchAuto != null && ArchAuto.ContentLength > 0 && ArchAuto.FileName.EndsWith("txt") && ArchManual != null && ArchManual.ContentLength > 0 && (ArchManual.FileName.EndsWith("xls") || ArchManual.FileName.EndsWith("xlsx")) ) { string pathArchManual = ""; if (!Directory.Exists(Server.MapPath("Excels"))) Directory.CreateDirectory(Server.MapPath("Excels")); var readerArchAuto = new BinaryReader(ArchAuto.InputStream); string resultArchAuto = System.Text.Encoding.UTF8.GetString(readerArchAuto.ReadBytes(ArchAuto.ContentLength)); string[] lineasArchAuto = Regex.Split(resultArchAuto, "\r\n"); Relevamientos objRelevamiento = new Relevamientos(); objRelevamiento.Observaciones = collection["Observaciones"]; objRelevamiento.FechaCarga = DateTime.Now; objRelevamiento.FechaInicio = DateTime.Parse(lineasArchAuto[0].Split(';')[0].ToString()); objRelevamiento.FechaFinal = DateTime.Parse(lineasArchAuto[lineasArchAuto.Length - 1].Split(';')[0].ToString()); objRelevamiento.IdEstado = 1; objRelevamiento.IdTrampa = int.Parse(lineasArchAuto[0].Split(';')[2].ToString()); db.Relevamientos.Add(objRelevamiento); //db.SaveChanges(); pathArchManual = Server.MapPath("Excels") + @"\" + ArchManual.FileName; if (System.IO.File.Exists(pathArchManual)) System.IO.File.Delete(pathArchManual); ArchManual.SaveAs(pathArchManual); string cnnStr = ""; if (pathArchManual.EndsWith(".xlsx")) { //Excel 2007 cnnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; cnnStr += ";Data Source=" + pathArchManual + ";"; } else { //Excel 97-2003 //http://www.connectionstrings.com/excel (leer sobre la clave de registro TypeGuessRows) cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; cnnStr += ";Data Source=" + pathArchManual + ";"; } System.Data.OleDb.OleDbConnection oCnn = new System.Data.OleDb.OleDbConnection(cnnStr); System.Data.OleDb.OleDbDataAdapter oDa = null; DataTable dtArchManual = new DataTable(); try { oCnn.Open(); //Obtenemos los nombres de las hojas del Excel. DataTable dtHojas = oCnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); if (dtHojas.Rows.Count > 0) { string firstSheet = dtHojas.Rows[0]["TABLE_NAME"].ToString().Trim(); string selectCmd = "select * from [" + firstSheet + "]"; oDa = new System.Data.OleDb.OleDbDataAdapter(selectCmd, oCnn); oDa.Fill(dtArchManual); } oCnn.Close(); dtArchManual.Columns.Add("Fecha"); foreach (DataRow drFila in dtArchManual.Rows) { int iAño = 0; int iMes = 0; int iDia = 0; int.TryParse(drFila[0].ToString(), out iAño); int.TryParse(drFila[1].ToString(), out iMes); int.TryParse(drFila[2].ToString(), out iDia); if (iAño > 0 && iMes > 0 && iDia > 0) { DateTime tFecha = new DateTime(iAño, iMes, iDia); drFila["Fecha"] = tFecha.Date.ToShortDateString(); } if (drFila["Fecha"].ToString() == objRelevamiento.FechaFinal.ToShortDateString()) { foreach (DataColumn dcColumna in dtArchManual.Columns) { if (dcColumna.Ordinal > 2) { //CORREGIR SELECCION DE INSECTO Insectos objInsecto = (from obj in db.Insectos where obj.NombreCientifico == dcColumna.ColumnName select obj).FirstOrDefault(); if (objInsecto != null) { int Cantidad = 0; int.TryParse(drFila[dcColumna.Ordinal].ToString(), out Cantidad); if (Cantidad > 0) { LecturasManuales objLecturasManuales = new LecturasManuales(); objLecturasManuales.IdRelevamiento = objRelevamiento.IdRelevamiento; objLecturasManuales.IdInsecto = objInsecto.IdInsecto; objLecturasManuales.Cantidad = Cantidad; objLecturasManuales.IdEstado = 1; db.LecturasManuales.Add(objLecturasManuales); } } } } //db.SaveChanges(); break; } } } catch (Exception ex) { throw ex; } finally { if (oCnn.State == ConnectionState.Open) oCnn.Close(); } if (oDa != null) { oDa.Dispose(); } if (oCnn != null) { oCnn.Dispose(); } //var objRelevamiento2 = (from obj in db.Relevamientos select obj).OrderByDescending(i => i.IdRelevamiento).First(); foreach (string Linea in lineasArchAuto) { string[] arrDatos = Linea.Split(';'); if (arrDatos.Length > 1 && arrDatos[1].ToString() == "LECTURA") { Lecturas objLecturas = new Lecturas(); objLecturas.IdEstado = 1; objLecturas.IdRelevamiento = objRelevamiento.IdRelevamiento; objLecturas.Frecuencia = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture); objLecturas.Aleteos = int.Parse(arrDatos[3]); objLecturas.FechaLectura = DateTime.Parse(arrDatos[0]); db.Lecturas.Add(objLecturas); } else if (arrDatos.Length > 1 && arrDatos[1].ToString() == "ESTADO") { Monitoreos objMonitoreos = new Monitoreos(); objMonitoreos.IdEstado = 1; objMonitoreos.IdRelevamiento = objRelevamiento.IdRelevamiento; objMonitoreos.Humedad = double.Parse(arrDatos[3], System.Globalization.CultureInfo.InvariantCulture); objMonitoreos.Temperatura = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture); objMonitoreos.Bateria = double.Parse(arrDatos[4], System.Globalization.CultureInfo.InvariantCulture); objMonitoreos.FechaMonitoreo = DateTime.Parse(arrDatos[0]); db.Monitoreos.Add(objMonitoreos); } } db.SaveChanges(); return Redirect("~/Alarmas/GenerarAlarmas"); } else { return View(); } } catch (Exception ex) { throw ex; } }
public void SaveScenarioProperties() { bool bOptimizer; string strDesc = ""; string strSQL = ""; System.Text.StringBuilder strFullPath; m_intError = 0; //validate the input // //Optimization id // if (this.txtScenarioId.Text.Length == 0) { MessageBox.Show("Enter A Unique Optimization scenario Id"); this.txtScenarioId.Focus(); m_intError = -1; return; } // //check for duplicate scenario id // System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection(); string strProjDir = frmMain.g_oFrmMain.getProjectDirectory(); string strScenarioDir = strProjDir + "\\" + ScenarioType + "\\db"; string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; strFullPath = new System.Text.StringBuilder(strScenarioDir); strFullPath.Append("\\"); strFullPath.Append(strFile); ado_data_access oAdo = new ado_data_access(); string strConn = oAdo.getMDBConnString(strFullPath.ToString(), "admin", ""); oAdo.SqlQueryReader(strConn, "select scenario_id from scenario"); if (oAdo.m_OleDbDataReader.HasRows) { while (oAdo.m_OleDbDataReader.Read()) { if (oAdo.m_OleDbDataReader["scenario_id"] != System.DBNull.Value) { if (this.txtScenarioId.Text.Trim().ToUpper() == Convert.ToString(oAdo.m_OleDbDataReader["scenario_id"]).Trim().ToUpper()) { this.m_intError = -1; MessageBox.Show("Cannot have a duplicate Optimization scenario id"); oAdo.m_OleDbDataReader.Close(); oAdo.m_OleDbDataReader = null; oAdo = null; this.txtScenarioId.Focus(); return; } } } } else { } oAdo.m_OleDbDataReader.Close(); oAdo.m_OleDbDataReader = null; // //create the scenario path if it does not exist and //copy the scenario_results.mdb to it // try { if (!System.IO.Directory.Exists(this.txtScenarioPath.Text)) { System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text); System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text.ToString() + "\\db"); //copy default processor scenario_results database to the new project directory if (this.ScenarioType == "processor") { dao_data_access oDao = new dao_data_access(); string strDestFile = this.txtScenarioPath.Text + "\\" + Tables.ProcessorScenarioRun.DefaultHarvestCostsTableDbFile; oDao.CreateMDB(strDestFile); oDao.m_DaoWorkspace.Close(); oDao = null; string strScenarioResultsConn = oAdo.getMDBConnString(strDestFile, "", ""); System.Data.OleDb.OleDbConnection OleDbScenarioResultsConn = new System.Data.OleDb.OleDbConnection(); oAdo.OpenConnection(strScenarioResultsConn, ref OleDbScenarioResultsConn); frmMain.g_oTables.m_oProcessor.CreateHarvestCostsTable( oAdo, OleDbScenarioResultsConn, Tables.ProcessorScenarioRun.DefaultHarvestCostsTableName); frmMain.g_oTables.m_oProcessor.CreateTreeVolValSpeciesDiamGroupsTable( oAdo, OleDbScenarioResultsConn, Tables.ProcessorScenarioRun.DefaultTreeVolValSpeciesDiamGroupsTableName); OleDbScenarioResultsConn.Close(); OleDbScenarioResultsConn.Dispose(); } } } catch { MessageBox.Show("Error Creating Folder"); m_intError = -1; return; } // //copy the project data source values to the scenario data source // string strProjDBDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\db"; string strProjFile = "project.mdb"; StringBuilder strProjFullPath = new StringBuilder(strProjDBDir); strProjFullPath.Append("\\"); strProjFullPath.Append(strProjFile); string strProjConn = oAdo.getMDBConnString(strProjFullPath.ToString(), "admin", ""); System.Data.OleDb.OleDbConnection p_OleDbProjConn = new System.Data.OleDb.OleDbConnection(); oAdo.OpenConnection(strProjConn, ref p_OleDbProjConn); string strScenarioDBDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db"; string strScenarioFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; StringBuilder strScenarioFullPath = new StringBuilder(strScenarioDBDir); strScenarioFullPath.Append("\\"); strScenarioFullPath.Append(strScenarioFile); string strScenarioConn = oAdo.getMDBConnString(strScenarioFullPath.ToString(), "admin", ""); oAdo.OpenConnection(strScenarioConn); if (oAdo.m_intError == 0) { if (this.txtDescription.Text.Trim().Length > 0) { strDesc = oAdo.FixString(this.txtDescription.Text.Trim(), "'", "''"); } strSQL = "INSERT INTO scenario (scenario_id,description,Path,File) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + strDesc + "'," + "'" + this.txtScenarioPath.Text.Trim() + "','scenario_" + ScenarioType + "_rule_definitions.mdb');"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL); oAdo.SqlQueryReader(p_OleDbProjConn, "select * from datasource"); if (oAdo.m_intError == 0) { try { while (oAdo.m_OleDbDataReader.Read()) { bOptimizer = false; switch (oAdo.m_OleDbDataReader["table_type"].ToString().Trim().ToUpper()) { case "PLOT": bOptimizer = true; break; case "CONDITION": bOptimizer = true; break; //case "FIRE AND FUEL EFFECTS": // bCore = true; // break; //case "HARVEST COSTS": // bCore = true; // break; case "ADDITIONAL HARVEST COSTS": bOptimizer = true; break; case "TREATMENT PRESCRIPTIONS": bOptimizer = true; break; //case "TREE VOLUMES AND VALUES BY SPECIES AND DIAMETER GROUPS": // bCore = true; // break; case "TRAVEL TIMES": bOptimizer = true; break; case "PROCESSING SITES": bOptimizer = true; break; //case "TREE SPECIES AND DIAMETER GROUPS DOLLAR VALUES": // bCore = true; // break; case "PLOT AND CONDITION RECORD AUDIT": if (ScenarioType == "optimizer") { bOptimizer = true; } break; case "PLOT, CONDITION AND TREATMENT RECORD AUDIT": if (ScenarioType == "optimizer") { bOptimizer = true; } break; case "TREE": if (ScenarioType == "processor") { bOptimizer = true; } break; case "HARVEST METHODS": if (ScenarioType == "processor") { bOptimizer = true; } break; case "TREATMENT PACKAGES": bOptimizer = true; break; //case "FVS TREE LIST FOR PROCESSOR": // if (ScenarioType=="processor") bCore=true; // break; case "TREE SPECIES": if (ScenarioType == "processor") { bOptimizer = true; } break; case "TREATMENT PRESCRIPTIONS HARVEST COST COLUMNS": if (ScenarioType == "processor") { bOptimizer = true; } break; case "FIA TREE SPECIES REFERENCE": if (ScenarioType == "processor") { bOptimizer = true; } break; default: break; } if (bOptimizer == true) { strSQL = "INSERT INTO scenario_datasource (scenario_id,table_type,Path,file,table_name) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + oAdo.m_OleDbDataReader["table_type"].ToString().Trim() + "'," + "'" + oAdo.m_OleDbDataReader["path"].ToString().Trim() + "'," + "'" + oAdo.m_OleDbDataReader["file"].ToString().Trim() + "'," + "'" + oAdo.m_OleDbDataReader["table_name"].ToString().Trim() + "');"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL); } } } catch (Exception caught) { m_intError = -1; m_strError = caught.Message; MessageBox.Show(strError); } oAdo.m_OleDbDataReader.Close(); oAdo.m_OleDbDataReader = null; oAdo.m_OleDbCommand = null; p_OleDbProjConn.Close(); p_OleDbProjConn = null; } if (ScenarioType.Trim().ToUpper() == "OPTIMIZER") { string strTemp = oAdo.FixString("SELECT @@PlotTable@@.* FROM @@PlotTable@@ WHERE @@PlotTable@@.plot_accessible_yn='Y'", "'", "''"); strSQL = "INSERT INTO scenario_plot_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + strTemp + "'," + "'Y');"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL); strTemp = oAdo.FixString("SELECT @@CondTable@@.* FROM @@CondTable@@", "'", "''"); strSQL = "INSERT INTO scenario_cond_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + strTemp + "'," + "'Y');"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL); } } oAdo.m_OleDbConnection.Close(); oAdo.m_OleDbConnection = null; oAdo = null; }
private void button1_Click(object sender, EventArgs e) { String m_conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\21.mdb;"; System.Data.OleDb.OleDbConnection m_conn = new System.Data.OleDb.OleDbConnection(m_conn_str); m_conn.Open(); if (textBox_name.Text.Trim() == "" || textBox_password.Text.Trim() == "" || textBox_password_repeat.Text.Trim() == "") { if (textBox_name.Text.Trim() == "") { MessageBox.Show("name cannot be empty!", "wrong"); } if (textBox_password.Text.Trim() == "") { MessageBox.Show("password cannot be empty!", "wrong"); } if (textBox_password_repeat.Text.Trim() == "") { MessageBox.Show("comfirm password cannot be empty!", "wrong"); } goto end; }//非空判断 String name = textBox_name.Text.Trim(); String password = textBox_password.Text.Trim(); String confirm_pass = textBox_password_repeat.Text.Trim(); String query2 = "select count(*) from BlackJack where user_id = '" + name + "'"; System.Data.OleDb.OleDbCommand m_comm2 = new System.Data.OleDb.OleDbCommand(query2, m_conn); int check = Convert.ToInt32(m_comm2.ExecuteScalar()); if (check != 0) { MessageBox.Show("this username has already existed!", "warning"); goto end; } if (password == confirm_pass) { string query1 = "insert into BlackJack (user_id,wager,[password],total,win,draw,lose,winp) values('" + name + "','" + 30000 + "','" + password + "','" + 0 + "','" + 0 + "','" + 0 + "','" + 0 + "','" + 0 + "')"; System.Data.OleDb.OleDbCommand m_comm = new System.Data.OleDb.OleDbCommand(query1, m_conn); m_comm.ExecuteNonQuery(); MessageBox.Show("success!"); m_conn.Close(); m_conn.Dispose(); Login login1 = new Login(); this.Hide(); Dispose(); login1.Show(); } else { MessageBox.Show("Your new and confirm password are different. Please enter your passwords again.", "wrong"); textBox_password_repeat.Clear(); } end : m_conn.Close(); m_conn.Dispose(); }