private void buttonInfoMessage_Click(object sender, EventArgs e) { /**** Create this Store Procedure in the tempdb database first **** Create Proc TestInfoMessage as Print 'Using a Print Statement' RaisError('RaisError in Stored Procedures', 9, 1) ****************************************************/ //1. Make a Connection System.Data.OleDb.OleDbConnection objOleCon = new System.Data.OleDb.OleDbConnection(); objOleCon.ConnectionString = strConnection; objOleCon.Open(); objOleCon.InfoMessage += new System.Data.OleDb.OleDbInfoMessageEventHandler(objOleCon_InfoMessage); //2. Issue a Command System.Data.OleDb.OleDbCommand objCmd; objCmd = new System.Data.OleDb.OleDbCommand("Raiserror('Typical Message Goes Here', 9, 1)", objOleCon); objCmd.ExecuteNonQuery(); objCmd = new System.Data.OleDb.OleDbCommand("Exec TestInfoMessage", objOleCon); //This executes the stored procedure. objCmd.ExecuteNonQuery(); //3. Process the Results /** No Results at this time **/ //4. Clean up code objOleCon.Close(); }
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(); } } }
protected void btnGo_Click(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); string sql = ""; System.Data.DataSet ds = new System.Data.DataSet(); System.Data.OleDb.OleDbDataReader dr; System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand(); //http://www.c-sharpcorner.com/UploadFile/dchoksi/transaction02132007020042AM/transaction.aspx //get this from connectionstrings.com/access conn.ConnectionString = txtConnString.Text; conn.Open(); //here I can talk to my db... System.Data.OleDb.OleDbTransaction Trans; Trans = conn.BeginTransaction(System.Data.IsolationLevel.Chaos); try { comm.Connection = conn; comm.Transaction = Trans; // Trans.Begin(); //Console.WriteLine(conn.State); sql = txtSQL.Text; comm.CommandText = sql; if (sql.ToLower().IndexOf("select") == 0) { dr = comm.ExecuteReader(); while (dr.Read()) { txtresults.Text = dr.GetValue(0).ToString(); } } else { txtresults.Text = comm.ExecuteNonQuery().ToString(); } Trans.Commit(); } catch (Exception ex) { txtresults.Text = ex.Message; Trans.Rollback(); } finally { comm.Dispose(); conn.Close(); conn = null; } }
public int AddEvent(String eventName, String eventRoom, String eventAttendees, String eventDate) { System.Data.OleDb.OleDbConnection oleDbConnection1; System.Data.OleDb.OleDbDataAdapter daEvents; DataSet ds; oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\\Inetpub\\wwwroot\\PCSWebApp3\\PCSWebApp3.mdb"; String oleDbCommand = "INSERT INTO Events (Name, Room, AttendeeList," + " EventDate) VALUES ('" + eventName + "', '" + eventRoom + "', '" + eventAttendees + "', '" + eventDate + "')"; System.Data.OleDb.OleDbCommand insertCommand = new System.Data.OleDb.OleDbCommand(oleDbCommand, oleDbConnection1); oleDbConnection1.Open(); int queryResult = insertCommand.ExecuteNonQuery(); if (queryResult == 1) { daEvents = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Events", oleDbConnection1); ds = (DataSet)Application["ds"]; ds.Tables["Events"].Clear(); daEvents.Fill(ds, "Events"); Application.Lock(); Application["ds"] = ds; Application.UnLock(); oleDbConnection1.Close(); } return queryResult; }
/// <summary> ///创建excel数据源连接 /// </summary> /// <param name="strFileName">文件路径名</param> /// <param name="DbConn">返回数据源连接</param> /// <returns></returns> public static bool CreateConnection(string strFileName, ref IDbConnection DbConn) { bool bolReturn = false; string strConnectString = string.Empty; if (string.IsNullOrEmpty(strFileName)) { return false; } if (!System.IO.File.Exists(strFileName)) { return false; } strConnectString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\";Data Source={0};", strFileName); try { //实例化连接 DbConn = new System.Data.OleDb.OleDbConnection(); DbConn.ConnectionString = strConnectString; DbConn.Open(); bolReturn = true; } catch (Exception exp) { Hy.Common.Utility.Log.OperationalLogManager.AppendMessage(exp.ToString()); } return bolReturn; }
/// <summary> /// Create access database file /// </summary> public static void create_mdb_file(DataSet ds, string destination, string template) { if(SqlConvert.ToString(template) == "") throw new Err("You must specify the location of a template mdb file inherit from."); if(!System.IO.File.Exists(template)) throw new Err("The specified template file \"" + template + "\" could not be found."); if(SqlConvert.ToString(destination) == "") throw new Err("You must specify the destination location and filename of the mdb file to create."); //COPY THE TEMPLATE AND CREATE DESTINATION FILE System.IO.File.Copy(template,destination,true); //CONNECT TO THE DESTINATION FILE string sconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destination + ";"; System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection(sconn); oconn.Open(); System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand(); ocmd.Connection = oconn; //WRITE TO THE DESTINATION FILE try { oledb.insert_dataset(ds,ocmd); } catch(Exception ex) { //System.Web.HttpContext.Current.Response.Write(ex.ToString()); General.Debugging.Report.SendError("Error exporting to MS Access", ex.ToString() + "\r\n\r\n\r\n" + ocmd.CommandText); } finally { // Close the connection. oconn.Close(); } }
public static System.Data.OleDb.OleDbConnection GetWDSConnection() { string wds_connection_string = @"Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"; var wds_connection = new System.Data.OleDb.OleDbConnection(wds_connection_string); wds_connection.Open(); return wds_connection; }
public Form1() { InitializeComponent(); comboBox2.Text = "Column"; comboBox2.Items.Add("Column"); comboBox2.Items.Add("Lines"); comboBox2.Items.Add("Pie"); comboBox2.Items.Add("Bar"); comboBox2.Items.Add("Funnel"); comboBox2.Items.Add("PointAndFigure"); comboBox1.Items.Clear(); if (System.IO.File.Exists("your_base.mdb")) { string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5"; System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr); connectDb.Open(); DataTable cbTb = connectDb.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (DataRow row in cbTb.Rows) { string tbName = row["TABLE_NAME"].ToString(); comboBox1.Items.Add(tbName); } connectDb.Close(); } }
public void write_log_file(Form1 f,String type_of_network) { this.f = f; int time = f.time; Console.Write("Time in excel : " + time); Console.Write("Selected network : " + type_of_network); String network_type = make_network_string(type_of_network); nodes = Convert.ToString(f.no_of_nodes); String connections = make_connections_string(); if (System.IO.File.Exists("log.xls")) file_exists = true; // MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\ABHISHEK\\ExcelData1.xls;;Extended Properties=Excel 8.0;"); MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source = log.xls;;Extended Properties=Excel 8.0;"); MyConnection.Open(); myCommand.Connection = MyConnection; if (!file_exists) { Console.Write("does not exist"); sql = "CREATE TABLE Simulation (TypeofNetwork String, Nodes int, Packets int,Seeder_Nodes int,Seeder_Packets int,Download_edges int,Upload_edges int,Ratio float,Thresh_Probability float,Nodes_row int,Connections String,Alturists int,Traders int,Parasites int,Time_taken int)"; myCommand.CommandText = sql; myCommand.ExecuteNonQuery(); } sql = "INSERT INTO [Simulation$] (TypeofNetwork, Nodes, Packets,Seeder_Nodes,Seeder_Packets,Download_edges,Upload_edges,Ratio,Thresh_Probability,Nodes_row,Connections,Alturists,Traders,Parasites,Time_taken) values (" + " ' " + network_type + " ' " + "," + nodes + "," + f.no_of_packets.ToString() + "," + f.seeder_nodes.ToString() + "," + f.seeder_packets.ToString() + "," + f.no_of_download_edges.ToString() + "," + f.no_of_upload_edges.ToString() + "," + f.ratio.ToString() + "," + (f.threshold_probability / 100.0).ToString() + "," + f.widthstep.ToString() + "," + "'" + connections +"'" + "," +f.alturists.ToString() + "," + f.traders.ToString() + "," + f.parasites.ToString()+"," +time.ToString()+ ")"; myCommand.CommandText = sql; myCommand.ExecuteNonQuery(); MyConnection.Close(); }
public DataTable SelectToDataTable(string sql) { //set connection System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(); con.ConnectionString = connectString; //set commandtext System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(); command.CommandText = sql; command.Connection = con; //set adapter System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(); adapter.SelectCommand = command; //creat a datatable DataTable dt = new DataTable(); try { //open this connection con.Open(); adapter.Fill(dt); } catch (Exception ex) { //throw new Exception con.Close(); } finally { //close this connection con.Close(); } //return a datatable return dt; }
protected void Application_Start(Object sender, EventArgs e) { System.Data.DataSet ds; System.Data.OleDb.OleDbConnection oleDbConnection1; System.Data.OleDb.OleDbDataAdapter daAttendees; System.Data.OleDb.OleDbDataAdapter daRooms; System.Data.OleDb.OleDbDataAdapter daEvents; oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Inetpub\wwwroot\PCSWebApp3\PCSWebApp3.mdb;Mode=ReadWrite|Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"; oleDbConnection1.Open(); ds = new DataSet(); daAttendees = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Attendees", oleDbConnection1); daRooms = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Rooms", oleDbConnection1); daEvents = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Events", oleDbConnection1); daAttendees.Fill(ds, "Attendees"); daRooms.Fill(ds, "Rooms"); daEvents.Fill(ds, "Events"); oleDbConnection1.Close(); Application["ds"] = ds; }
public System.Collections.Generic.List<string> GetColumnNames(string file, string table) { System.Data.DataTable dataSet = new System.Data.DataTable(); string connString = ""; if (Global.filepassword != "") { if (Path.GetExtension(file).ToString().ToUpper() != ".ACCDB") connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Jet OLEDB:Database Password="******"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Jet OLEDB:Database Password="******".ACCDB") connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file; else connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Persist Security Info=False;"; } using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connString)) { connection.Open(); System.Data.OleDb.OleDbCommand Command = new System.Data.OleDb.OleDbCommand("SELECT * FROM " + table, connection); using (System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter(Command)) { dataAdapter.Fill(dataSet); } } System.Collections.Generic.List<string> columns = new System.Collections.Generic.List<string>(); for (int i = 0; i < dataSet.Columns.Count; i++) { columns.Add(dataSet.Columns[i].ColumnName); } return columns; }
protected void btnInsert_Click(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(); Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("app_data/products.mdb"); Conn.Open(); //Response.Write(Conn.State); string strSQL = "insert into [users] ([username], firstname, lastname, email, [password]) values (?,?,?,?,?)"; object returnVal; System.Data.OleDb.OleDbCommand Comm = new System.Data.OleDb.OleDbCommand(); Comm.Connection = Conn; Comm.CommandText = "select [username] from [users] where [username] = ?"; Comm.Parameters.AddWithValue("[username]", txtUserName.Text); returnVal = Comm.ExecuteScalar(); if (returnVal == null) { Comm.Parameters.Clear(); Comm.CommandText = strSQL; Comm.Parameters.AddWithValue("username", txtUserName.Text); Comm.Parameters.AddWithValue("firstname", txtFName.Text); Comm.Parameters.AddWithValue("lastname", txtLName.Text); Comm.Parameters.AddWithValue("email", txtEmail.Text); Comm.Parameters.AddWithValue("password", txtPassword.Text); Comm.ExecuteNonQuery(); } else { Response.Write("Username already exists."); } Conn.Close(); Conn = null; }
/// <summary> /// 取得 OLEDB 的连接字符串. /// 优先启动 ACE 驱动, /// 假如 ACE 失败,再尝试启动 JET /// /// 该方法可能用不上。 /// 因为 在 Office 2010 上面,Jet 与 ACE 都能正常运作 /// /// 唯一需要注意的是, 如果目标机器的操作系统,是64位的话。 /// 项目需要 编译为 x86, 而不是简单的使用默认的 Any CPU. /// </summary> /// <param name="excelFileName"></param> /// <returns></returns> public static string GetOleDbConnectionString(string excelFileName) { // Office 2007 以及 以下版本使用. string jetConnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", excelFileName); // xlsx 扩展名 使用. string aceConnXlsxString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\"", excelFileName); // xls 扩展名 使用. string aceConnXlsString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", excelFileName); // 默认非 xlsx string aceConnString = aceConnXlsString; if (excelFileName.EndsWith(".xlsx", StringComparison.CurrentCultureIgnoreCase)) { // 如果扩展名为 xlsx. // 那么需要将 驱动切换为 xlsx 扩展名 的. aceConnString = aceConnXlsxString; } // 尝试使用 ACE. 假如不发生错误的话,使用 ACE 驱动. try { System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(aceConnString); cn.Open(); cn.Close(); // 使用 ACE return aceConnString; } catch (Exception) { // 启动 ACE 失败. } // 尝试使用 Jet. 假如不发生错误的话,使用 Jet 驱动. try { System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(jetConnString); cn.Open(); cn.Close(); // 使用 Jet return jetConnString; } catch (Exception) { // 启动 Jet 失败. } // 假如 ACE 与 JET 都失败了,默认使用 JET. return jetConnString; }
/// <summary> Private constructor ... checks system properties for connection /// information /// </summary> private NormativeDatabase() { //UPGRADE_ISSUE: Method 'java.lang.System.getProperty' was not converted. "ms-help://MS.VSCC.v80/dv_commoner/local/redirect.htm?index='!DefaultContextWindowIndex'&keyword='jlca1000_javalangSystem'" _connectionString = ca.uhn.Properties.Settings.Default.ConnectionString; _conn = new System.Data.OleDb.OleDbConnection(_connectionString); _conn.Open(); }
public static bool AnalyzeExcel(ExcelXMLLayout layout) { System.Data.OleDb.OleDbConnection conn = null; try { conn = new System.Data.OleDb.OleDbConnection(MakeConnectionString(layout.solution.path)); conn.Open(); System.Data.DataTable table = conn.GetOleDbSchemaTable( System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, layout.sheet + "$", null }); layout.Clear(); System.Diagnostics.Debug.WriteLine("Start Analyze [" + table.Rows.Count + "]"); foreach (System.Data.DataRow row in table.Rows) { string name = row["Column_Name"].ToString(); System.Diagnostics.Debug.WriteLine(name); // 测试数据类型 ExcelXMLLayout.KeyType testType = ExcelXMLLayout.KeyType.Unknown; { System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand( string.Format("select [{0}] from [{1}$]", name, layout.sheet), conn ); System.Data.OleDb.OleDbDataReader r = cmd.ExecuteReader(); while (r.Read()) { System.Diagnostics.Debug.WriteLine(r[0].GetType()); if (r[0].GetType() == typeof(System.Double)) { testType = ExcelXMLLayout.KeyType.Integer; break; } if (testType == ExcelXMLLayout.KeyType.String) { break; } testType = ExcelXMLLayout.KeyType.String; } r.Close(); cmd.Dispose(); } layout.Add(name, testType); } table.Dispose(); conn.Close(); return true; } catch (Exception outErr) { lastError = string.Format("无法分析,Excel 无法打开\r\n{0}", outErr.Message); } return false; }
private void Form1_Load(object sender, EventArgs e) { try { conexao = new System.Data.OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/temp/Universidade3.mdb"); conexao.Open(); } catch (Exception exsql) { } }
public Form1() { InitializeComponent(); DataTable dtProviderFactorys = DbProviderFactories.GetFactoryClasses(); DbConnectionStringBuilder dcsBuilder = new DbConnectionStringBuilder(); dcsBuilder.Add("User ID", "hzzgis"); dcsBuilder.Add("Password", "hzzgis"); dcsBuilder.Add("Service Name", "sunz"); dcsBuilder.Add("Host", "172.16.1.9"); dcsBuilder.Add("Integrated Security", false); string licPath = Application.StartupPath + "\\DDTek.lic"; if (!System.IO.File.Exists(licPath)) licPath = CretateDDTekLic.CreateLic(); dcsBuilder.Add("License Path", licPath); //若路径中存在空格,则会在路径名称前加上"\"" string conStr = dcsBuilder.ConnectionString; conStr = conStr.Replace("\"", ""); DDTek.Oracle.OracleConnection orclConnection = new DDTek.Oracle.OracleConnection(conStr); DDTek.Oracle.OracleCommand cmd = new DDTek.Oracle.OracleCommand(); DDTek.Oracle.OracleDataAdapter adapter = new DDTek.Oracle.OracleDataAdapter(); adapter.SelectCommand = cmd; DbDataAdapter dAdapter = adapter; DbCommand dbCommand = dAdapter.SelectCommand; orclConnection.Open(); //Configuration config = new Configuration(); //ISessionFactory pFactory = config.BuildSessionFactory(); //ISession pSession= pFactory.OpenSession(orclConnection as IDbConnection); //DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OracleClient"); //IDbConnection dbConn = factory.CreateConnection(); //if (dbConn != null) // MessageBox.Show("Connection Created"); //Conn.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Server + ")(PORT=" + Port + ")))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + Service + ")));user id=" + User + ";password="******";pooling = true;Unicode=True"; IDbConnection dbConn=new System.Data.OleDb.OleDbConnection(); string Server = "sunzvm-lc", Port = "1521", Service = "sunz", User = "******", PWD = "hzzgis"; //dbConn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Server + ")(PORT=" + Port + ")))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + Service + ")));user id=" + User + ";password="******";pooling = true;Unicode=True"; dbConn.ConnectionString = "Provider=MSDAORA.1;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Server + ")(PORT=" + Port + ")))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + Service + ")));user id=" + User + ";password="******";pooling = true;Unicode=True"; try { dbConn.Open(); } catch(Exception exp) { MessageBox.Show(exp.ToString()); } }
public static DataTable GetDataTableExcel(string strFileName, string Table) { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";"); conn.Open(); string strQuery = "SELECT * FROM [" + Table + "]"; System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn); System.Data.DataSet ds = new System.Data.DataSet(); adapter.Fill(ds); return ds.Tables[0]; }
public DataBase(string fileName) { this.FileName = fileName; string connStr = BuildExcel2007ConnectionString(fileName, true); conn = new System.Data.OleDb.OleDbConnection(connStr); conn.Open(); DataTable tableschema = conn.GetSchema(OdbcMetaDataCollectionNames.Tables); DataSet set = tableschema.DataSet; TableName = tableschema.Rows[0]["Table_name"].ToString(); }
public void BuildAxisOrderBitArray() { if (!File.Exists(EpsgAccessDatabase)) throw new IgnoreException("Epsg Access Database not found"); var ba = new BitArray(32768); using (var cn = new System.Data.OleDb.OleDbConnection( string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};", EpsgAccessDatabase))) { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandText = Sql; using (var dr = cmd.ExecuteReader()) { if (dr != null) while (dr.Read()) { var code = dr.GetInt32(0); if (code > 32767) continue; ba[code] = true; } } } string enc; var buffer = new byte[4096]; ba.CopyTo(buffer, 0); #if NET45 using (var bufferStream = new MemoryStream(buffer)) { using (var compressedStream = new MemoryStream()) { using (var s = new DeflateStream(compressedStream, CompressionMode.Compress)) { bufferStream.CopyTo(s); compressedStream.Flush(); } enc = Convert.ToBase64String(compressedStream.ToArray()); Console.WriteLine("Compressed"); WriteBlocks(enc); } } #endif enc = Convert.ToBase64String(buffer); Console.WriteLine("\nUncompressed"); WriteBlocks(enc); Console.WriteLine("\nByte array"); WriteBytes(buffer, 20); }
static void Main(string[] args) { using (var cn = new System.Data.OleDb.OleDbConnection(new Connection().ConnectionString)) { cn.Open(); // 手抜き:第一引数に以下の文字列を渡すことで処理分岐 switch (args.FirstOrDefault().ToLower()) { case "select": SelectAll(cn); SelectWithoutClass(cn); SelectWithClass(cn); break; case "insert": Insert(cn); SelectAll(cn); break; case "update": Update(cn); SelectAll(cn); break; case "commit": RunTransaction(cn, hasError: false); SelectAll(cn); break; case "rollback": RunTransaction(cn, hasError: true); SelectAll(cn); break; case "tableadapter": GetIdentityWithTableAdapter(); break; case "identity": SelectAll(cn); GetIdentityWithDapper(cn); SelectAll(cn); break; default: break; } cn.Close(); } }
protected override void CreateTables() { base.CreateTables(); var conn = new System.Data.OleDb.OleDbConnection(GetConnectionString()); conn.Open(); new JetDbCommand("INSERT INTO thing VALUES (1, 'Car');", conn).ExecuteNonQuery(); new JetDbCommand("INSERT INTO thing VALUES (2, 'Cat');", conn).ExecuteNonQuery(); new JetDbCommand("INSERT INTO thing VALUES (3, 'Apple');", conn).ExecuteNonQuery(); new JetDbCommand("INSERT INTO thing VALUES (4, 'Albacete');", conn).ExecuteNonQuery(); new JetDbCommand("INSERT INTO thing VALUES (5, 'Foo');", conn).ExecuteNonQuery(); }
public System.Data.DataTable Read(string filename) { var excelDataAdapter = new System.Data.OleDb.OleDbDataAdapter(); _connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + "; Extended Properties = Excel 8.0;"; var excelConn = new System.Data.OleDb.OleDbConnection(_connectionString); excelConn.Open(); var dtProtocol = new System.Data.DataTable { TableName = "Protocol" }; var excelCommand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [протокол плавки$]", excelConn); excelDataAdapter.SelectCommand = excelCommand; excelDataAdapter.Fill(dtProtocol); excelConn.Close(); return dtProtocol; }
public void AttemptReadingDBFWithOLEDB() { //FeatureLayer target = new MapPolygonLayer(); string filename = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "TestFiles", "soils.shp"); string dBaseFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "TestFiles"); string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source = " + dBaseFile + ";Extended Properties =dBase IV;"; System.Data.OleDb.OleDbConnection dBaseConnection; dBaseConnection = new System.Data.OleDb.OleDbConnection(connectionString); dBaseConnection.Open(); System.Data.OleDb.OleDbCommand dBaseCommand; dBaseCommand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [soils]", dBaseConnection); System.Data.OleDb.OleDbDataReader dBaseDataReader; dBaseDataReader = dBaseCommand.ExecuteReader(CommandBehavior.SequentialAccess); }
public static DataTable GetDataTableFromCSV(string strFileName) { try { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\""); conn.Open(); string strQuery = "Select * from [" + System.IO.Path.GetFileName(strFileName) + "]"; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn); System.Data.DataSet ds = new System.Data.DataSet(); da.Fill(ds); return ds.Tables[0]; } catch (Exception ex) { } return new DataTable(); }
public void ConvertExcelToCsv(int worksheetNumber = 1) { if (!System.IO.File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath); if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile); // connection string var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath); var cnn = new System.Data.OleDb.OleDbConnection(cnnStr); // get schema, then data var dt = new DataTable(); try { cnn.Open(); var schemaTable = cnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet"); string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", ""); string sql = String.Format("select * from [{0}]", worksheet); var da = new System.Data.OleDb.OleDbDataAdapter(sql, cnn); da.Fill(dt); } catch (Exception e) { Console.WriteLine(e.ToString()); throw e; } finally { // free resources cnn.Close(); } // write out CSV data using (var wtr = new StreamWriter(csvOutputFile)) { foreach (DataRow row in dt.Rows) { bool firstLine = true; foreach (DataColumn col in dt.Columns) { if (!firstLine) { wtr.Write(","); } else { firstLine = false; } var data = row[col.ColumnName].ToString().Replace("\"", "\"\""); wtr.Write(String.Format("\"{0}\"", data)); } wtr.WriteLine(); } } }
private void btnGo_Click(object sender, EventArgs e) { lblError.Text = ""; //open connection to database System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = txtConnString.Text; conn.Open(); // MessageBox.Show(conn.State.ToString()); //create my command System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand(); comm.Connection = conn; //send the sql through the command comm.CommandText = txtSQL.Text; //receive the result into a data container. System.Data.OleDb.OleDbDataReader dr; try { if (txtSQL.Text.ToUpper().StartsWith("SELECT")) { dr = comm.ExecuteReader(); System.Data.DataTable dt = new DataTable(); dt.Load(dr); Grid1.AutoGenerateColumns = true; //bind the result to the grid Grid1.DataSource = dt; } else { MessageBox.Show(comm.ExecuteNonQuery().ToString()); } } catch (Exception ex) { lblError.Text = ex.Message; } finally { conn.Close(); } }
static void Main(string[] args) { string connstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DropBox\My Dropbox\Devry\CIS407\SU10B\day5\NorthWind.mdb;"; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); string sql = ""; System.Data.DataSet ds = new System.Data.DataSet(); System.Data.OleDb.OleDbDataReader dr; System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand(); try { //get this from connectionstrings.com/access conn.ConnectionString = connstring; conn.Open(); //here I can talk to my db... comm.Connection = conn; //Console.WriteLine(conn.State); sql = Console.ReadLine(); comm.CommandText = sql; if (sql.ToLower().IndexOf("select") == 0) { dr = comm.ExecuteReader(); while (dr.Read()) { Console.WriteLine(dr.GetString(0)); } } else { Console.WriteLine(comm.ExecuteNonQuery().ToString()); } } catch ( Exception ex) { Console.WriteLine(ex.Message); Console.Read(); } finally { Console.ReadLine(); comm.Dispose(); conn.Close(); conn = null; } }
public void insertall(string path) { try { System.Data.OleDb.OleDbConnection yaalconnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; // public static string path = @"C:\src\RedirectApplication\RedirectApplication\301s.xlsx"; // public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;"; //yaalconnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='Z:\\33BRKPR0174G1ZS_032019_R2A.xlsx';Extended Properties=Excel 12.0;"); yaalconnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [B2B$]", yaalconnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dataGridView1.DataSource = DtSet.Tables[0]; yaalconnection.Close(); System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(" Provider = Microsoft.ACE.OLEDB.12.0; Data Source =D:\\json\\test.accdb"); System.Data.OleDb.OleDbCommand comm = con.CreateCommand(); con.Open(); String gstno; string mystring; mystring = ""; string res; res = ""; for (int i = 0; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1.Rows[i].Cells[j].Value == DBNull.Value) { dataGridView1.Rows[i].Cells[j].Value = 0; } } } dataGridView1.Update(); int strlen; for (int i = 0; i < dataGridView1.Rows.Count; i++) { if (dataGridView1.Rows[i].Cells[2].Value != null && dataGridView1.Rows[i].Cells[2].ToString() != String.Empty) { res = ""; string value = (string)dataGridView1.Rows[i].Cells[2].Value; mystring = value; strlen = mystring.Length; if (strlen > 5) { res = mystring.Substring(strlen - 5, 5); } } string path1 = ""; string StrQuery; if (res == "Total") { double amount = (double)(dataGridView1.Rows[i].Cells[5].Value); if (amount > 0) { //33BKMPP8726K1ZJ_012019_R2A.xlsx path1 = path.Substring(path.Length - 15, 6); StrQuery = @"INSERT INTO test(gstno,monthname,invno,invdt,amount,TaxAmt,camount,samount,name) VALUES('" + dataGridView1.Rows[i].Cells[0].Value + "', '" + path1 + "','" + dataGridView1.Rows[i].Cells[2].Value + "','" + dataGridView1.Rows[i].Cells[4].Value + "'," + dataGridView1.Rows[i].Cells[5].Value + "," + dataGridView1.Rows[i].Cells[9].Value + ", " + dataGridView1.Rows[i].Cells[11].Value + ", " + dataGridView1.Rows[i].Cells[12].Value + ", '" + dataGridView1.Rows[i].Cells[1].Value + "'); "; //string StrQuery = "INSERT INTO tableName VALUES ('" + dataGridView1.Rows[i].Cells[0].Value + "',' " + dataGridView1.Rows[i].Cells[1].Value + "', '" + dataGridView1.Rows[i].Cells[2].Value + "', '" + dataGridView1.Rows[i].Cells[3].Value + "',' " + dataGridView1.Rows[i].Cells[4].Value + "')"; // 11,12,1 // StrQuery = @"INSERT INTO test VALUES (" + dataGridView1.Rows[i].Cells["ColumnName"].Text + ", " // + dataGridView1.Rows[i].Cells["ColumnName"].Text + ");"; //VALUES ('" + dataGridView1.Rows[i].Cells[0].Value +"'," + dataGridView1.Rows[i].Cells[5].Value +",'" + dataGridView1.Rows[i].Cells[1].Value +"');"; comm.Connection = con; comm.CommandText = StrQuery; comm.ExecuteNonQuery(); } } } // cmd.CommandText = "Insert into test(test)Values(1)"; //comm.Connection = con; //comm.ExecuteNonQuery(); MessageBox.Show("Record Submitted", "Congrats"); con.Close(); MessageBox.Show("Completed"); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public static bool SearchCDInCDArchiveLocalFile(string cdarchivFilename, CD cd) { string sql; sql = string.Format("SELECT * FROM ((Track INNER JOIN CD ON Track.IDCD = CD.ID) INNER JOIN Artist AS ArtistTrack ON Track.IDArtist = ArtistTrack.ID) INNER JOIN Artist ON CD.IDArtist = Artist.ID WHERE (((CD.sIdentity)='{0}')) ORDER BY Track.wTrackNumber", cd.Identity); using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + cdarchivFilename + "'")) { conn.Open(); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn); using (System.Data.OleDb.OleDbDataReader reader = cmd.ExecuteReader()) { if (!reader.Read()) // CD nicht vorhanden { return(false); } cd.Artist = (string)reader["Artist.sArtist"]; cd.Title = (string)reader["CD.sTitle"]; cd.TotalLength = (int)reader["dwTotalLength"]; cd.Sampler = ((byte)reader["bCDSampler"]) == 1 ? true : false; cd.NumberOfTracks = (int)(byte)reader["cNumberOfTracks"]; cd.InitTracks(cd.NumberOfTracks); if ((Settings.Current.CDArchiveFields & CDArchiveFields.Category) == CDArchiveFields.Category) { cd.Category = (string)reader["sCategory"]; } if ((Settings.Current.CDArchiveFields & CDArchiveFields.Medium) == CDArchiveFields.Medium) { cd.Medium = (string)reader["sMedium"]; } if ((Settings.Current.CDArchiveFields & CDArchiveFields.Comment) == CDArchiveFields.Comment) { cd.Comment = (string)reader["CD.sComment"]; } cd.YearRecorded = reader["CD.dwYearRecorded"] is DBNull ? 0 : (int)reader["CD.dwYearRecorded"]; cd.Copyright = reader["sCopyright"] is DBNull ? "" : (string)reader["sCopyright"]; // Neue Felder. Ich frag hier die Exception ab, damit ein altes CD-Archiv auch noch klappt try { cd.UPC = reader["sUPC"] is DBNull ? "" : (string)reader["sUPC"]; cd.Label = reader["sLabel"] is DBNull ? "" : (string)reader["sLabel"]; } catch { } if (cd.Sampler && Settings.Current.SamplerUseFixedArtist) { cd.Artist = Settings.Current.SamplerFixedArtistText; } for (int i = 0; i < cd.NumberOfTracks; i++) { cd.Tracks[i].Artist = (string)reader["ArtistTrack.sArtist"]; cd.Tracks[i].Title = (string)reader["Track.sTitle"]; cd.Tracks[i].TrackNumber = (short)reader["wTrackNumber"]; cd.Tracks[i].Length = (int)reader["dwLength"]; if ((Settings.Current.CDArchiveFields & CDArchiveFields.BPM) == CDArchiveFields.BPM) { cd.Tracks[i].Bpm = (short)reader["wBpm"]; } if ((Settings.Current.CDArchiveFields & CDArchiveFields.TrackComment) == CDArchiveFields.TrackComment && !(reader["Track.sComment"] is DBNull)) { cd.Tracks[i].Comment = (string)reader["Track.sComment"]; } cd.Tracks[i].YearRecorded = reader["Track.dwYearRecorded"] is DBNull ? 0 : (int)reader["Track.dwYearRecorded"]; reader.Read(); } } } return(true); }
private static Map InitializeMapOsmWithXls(float angle) { Map map = new Map(); TileLayer tileLayer = new TileLayer(new OsmTileSource(), "TileLayer - OSM with XLS"); map.Layers.Add(tileLayer); //Get data from excel var xlsPath = string.Format(XlsConnectionString, System.IO.Directory.GetCurrentDirectory(), "GeoData\\Cities.xls"); var ds = new System.Data.DataSet("XLS"); using (var cn = new System.Data.OleDb.OleDbConnection(xlsPath)) { cn.Open(); using (var da = new System.Data.OleDb.OleDbDataAdapter(new System.Data.OleDb.OleDbCommand("SELECT * FROM [Cities$]", cn))) da.Fill(ds); } #if !DotSpatialProjections //The SRS for this datasource is EPSG:4326, therefore we need to transfrom it to OSM projection var ctf = new ProjNet.CoordinateSystems.Transformations.CoordinateTransformationFactory(); var cf = new ProjNet.CoordinateSystems.CoordinateSystemFactory(); var epsg4326 = cf.CreateFromWkt("GEOGCS[\"WGS 84\",DATUM[\"WGS_1984\",SPHEROID[\"WGS 84\",6378137,298.257223563,AUTHORITY[\"EPSG\",\"7030\"]],AUTHORITY[\"EPSG\",\"6326\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4326\"]]"); var epsg3857 = cf.CreateFromWkt("PROJCS[\"Popular Visualisation CRS / Mercator\", GEOGCS[\"Popular Visualisation CRS\", DATUM[\"Popular Visualisation Datum\", SPHEROID[\"Popular Visualisation Sphere\", 6378137, 0, AUTHORITY[\"EPSG\",\"7059\"]], TOWGS84[0, 0, 0, 0, 0, 0, 0], AUTHORITY[\"EPSG\",\"6055\"]],PRIMEM[\"Greenwich\", 0, AUTHORITY[\"EPSG\", \"8901\"]], UNIT[\"degree\", 0.0174532925199433, AUTHORITY[\"EPSG\", \"9102\"]], AXIS[\"E\", EAST], AXIS[\"N\", NORTH], AUTHORITY[\"EPSG\",\"4055\"]], PROJECTION[\"Mercator\"], PARAMETER[\"False_Easting\", 0], PARAMETER[\"False_Northing\", 0], PARAMETER[\"Central_Meridian\", 0], PARAMETER[\"Latitude_of_origin\", 0], UNIT[\"metre\", 1, AUTHORITY[\"EPSG\", \"9001\"]], AXIS[\"East\", EAST], AXIS[\"North\", NORTH], AUTHORITY[\"EPSG\",\"3857\"]]"); var ct = ctf.CreateFromCoordinateSystems(epsg4326, epsg3857); foreach (System.Data.DataRow row in ds.Tables[0].Rows) { if (row["X"] == DBNull.Value || row["Y"] == DBNull.Value) { continue; } double[] coords = new double[] { Convert.ToDouble(row["X"]), Convert.ToDouble(row["Y"]) }; coords = ct.MathTransform.Transform(coords); row["X"] = coords[0]; row["Y"] = coords[1]; } #else var epsg4326 = DotSpatial.Projections.KnownCoordinateSystems.Geographic.World.WGS1984; var epsg3857 = new DotSpatial.Projections.ProjectionInfo(); epsg3857.ReadEsriString("PROJCS[\"Popular Visualisation CRS / Mercator\", GEOGCS[\"Popular Visualisation CRS\", DATUM[\"Popular Visualisation Datum\", SPHEROID[\"Popular Visualisation Sphere\", 6378137, 0, AUTHORITY[\"EPSG\",\"7059\"]], TOWGS84[0, 0, 0, 0, 0, 0, 0], AUTHORITY[\"EPSG\",\"6055\"]],PRIMEM[\"Greenwich\", 0, AUTHORITY[\"EPSG\", \"8901\"]], UNIT[\"degree\", 0.0174532925199433, AUTHORITY[\"EPSG\", \"9102\"]], AXIS[\"E\", EAST], AXIS[\"N\", NORTH], AUTHORITY[\"EPSG\",\"4055\"]], PROJECTION[\"Mercator\"], PARAMETER[\"False_Easting\", 0], PARAMETER[\"False_Northing\", 0], PARAMETER[\"Central_Meridian\", 0], PARAMETER[\"Latitude_of_origin\", 0], UNIT[\"metre\", 1, AUTHORITY[\"EPSG\", \"9001\"]], AXIS[\"East\", EAST], AXIS[\"North\", NORTH], AUTHORITY[\"EPSG\",\"3857\"]]"); foreach (System.Data.DataRow row in ds.Tables[0].Rows) { if (row["X"] == DBNull.Value || row["Y"] == DBNull.Value) { continue; } double[] coords = new double[] { Convert.ToDouble(row["X"]), Convert.ToDouble(row["Y"]) }; DotSpatial.Projections.Reproject.ReprojectPoints(coords, null, epsg4326, epsg3857, 0, 1); row["X"] = coords[0]; row["Y"] = coords[1]; } #endif //Add Rotation Column ds.Tables[0].Columns.Add("Rotation", typeof(float)); foreach (System.Data.DataRow row in ds.Tables[0].Rows) { row["Rotation"] = -angle; } //Set up provider var xlsProvider = new SharpMap.Data.Providers.DataTablePoint(ds.Tables[0], "OID", "X", "Y"); var xlsLayer = new SharpMap.Layers.VectorLayer("XLS", xlsProvider); xlsLayer.Style.Symbol = SharpMap.Styles.VectorStyle.DefaultSymbol; //Add layer to map map.Layers.Add(xlsLayer); var xlsLabelLayer = new SharpMap.Layers.LabelLayer("XLSLabel"); xlsLabelLayer.DataSource = xlsProvider; xlsLabelLayer.LabelColumn = "Name"; xlsLabelLayer.PriorityColumn = "Population"; xlsLabelLayer.Style.CollisionBuffer = new System.Drawing.SizeF(2f, 2f); xlsLabelLayer.Style.CollisionDetection = true; xlsLabelLayer.LabelFilter = SharpMap.Rendering.LabelCollisionDetection.ThoroughCollisionDetection; map.Layers.Add(xlsLabelLayer); map.ZoomToBox(tileLayer.Envelope); return(map); }
static void Main(string[] args) { // set up variables with default values string dbName = ConfigurationManager.AppSettings["DefaultDbName"]; string folderName = ConfigurationManager.AppSettings["DefaultFolderName"]; // set up capitalized word list List <string> capitalizedWords = new List <string>(); foreach (var word in ConfigurationManager.AppSettings["CapitalizedWordList"].Split(',')) { capitalizedWords.Add(word.Trim()); } // prepare for songs List <Song> songs = new List <Song>(); // get argument list if (args.Length > 0) { dbName = args[0]; } if (args.Length > 1) { folderName = args[1]; } // ensure database exists if (!File.Exists(dbName)) { throw new FileNotFoundException("Could not find database file!", dbName); } // ensure folder exists (or create it) if (!Directory.Exists(folderName)) { Directory.CreateDirectory(folderName); } // build the connection string DbConnectionStringBuilder builder = new DbConnectionStringBuilder(); builder.ConnectionString = ConfigurationManager.ConnectionStrings["Access"].ConnectionString; builder.Add("Data Source", dbName); // get a new connection using (IDbConnection conn = new System.Data.OleDb.OleDbConnection(builder.ConnectionString)) { conn.Open(); IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT SongTitle, Song, Chorus2, Chorus3, Chorus4, Verse1, Verse2, Verse3, Verse4 FROM Songs"; using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { Song currSong; while (reader.Read()) { currSong = new Song( System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(Regex.Replace(reader.GetString(0).ToLower(), @"\s+", " ")) ); for (int i = 1; i < reader.FieldCount; i++) { if ((!reader.IsDBNull(i)) && (!string.IsNullOrWhiteSpace(reader.GetString(i)))) { currSong.Parts.Add(x[reader.GetName(i)], Normalize(reader.GetString(i), capitalizedWords)); } } songs.Add(currSong); } } } foreach (var song in songs) { song.Serialize(File.Open(Path.Combine(folderName, MakeValidFileName(song.Name + ".txt")), FileMode.Truncate, FileAccess.Write, FileShare.Write)); } }
/// <summary> 查看 案卷级 的 电子案卷 </summary> /// <param name="FileID">案卷ID</param> public MemoryStream OpenFilePDF(int FileID) { MemoryStream ms = new MemoryStream(); if (!UIPublicClass.PublicClass.IsSingleClent) { SqlConnection conn = new SqlConnection(DataSql.ConStr); string sql = "select Pdf from XfPdf.dbo.CjFilePdf where 案卷ID = " + FileID; try { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { ms = new MemoryStream((byte[])dr[0]); //创建Stream对象 } else { ms = null; } dr.Close(); conn.Close(); return(ms); } catch (System.Exception e) { MessageBox.Show("打开失败!" + e.Message); return(null); } } else { string connstr = DataSql.ConStr_Pdf; string sql = "select Pdf from CJFilePdf where 案卷ID = " + FileID; try { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connstr); conn.Open(); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); cmd.Connection = conn; cmd.CommandText = sql; System.Data.OleDb.OleDbDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { ms = new MemoryStream((byte[])dr[0]); //创建Stream对象 } else { ms = null; } dr.Close(); conn.Close(); return(ms); } catch (System.Exception e) { MessageBox.Show("打开失败!" + e.Message); return(null); } } }
/// <summary> Private constructor ... checks system properties for connection /// information /// </summary> private NormativeDatabase() { _connectionString = ConfigurationSettings.ConnectionString; _conn = new System.Data.OleDb.OleDbConnection(_connectionString); _conn.Open(); }
/// <summary> /// 取得 OLEDB 的连接字符串. /// 优先启动 ACE 驱动, /// 假如 ACE 失败,再尝试启动 JET /// /// 该方法可能用不上。 /// 因为 在 Office 2010 上面,Jet 与 ACE 都能正常运作 /// /// 唯一需要注意的是, 如果目标机器的操作系统,是64位的话。 /// 项目需要 编译为 x86, 而不是简单的使用默认的 Any CPU. /// </summary> /// <param name="excelFileName"> Excel 文件.</param> /// <param name="hasTitle">Excel 文件是否有标题行. </param> /// <param name="isReadonly">是否是只读操作.</param> /// <returns></returns> public static string GetOleDbConnectionString(string excelFileName, bool hasTitle, bool isReadonly = false) { string optionString = null; // HDR表示要把第一行作为数据还是作为列名,作为数据用 HDR=no,作为列名用HDR=yes; if (hasTitle) { optionString = "HDR=yes;"; } else { optionString = "HDR=no;"; } // 当 IMEX = 0 时为“汇出模式”,这个模式开启的 Excel 档案只能用来做“写入”用途。 // 当 IMEX = 1 时为“汇入模式”,这个模式开启的 Excel 档案只能用来做“读取”用途。 // 当 IMEX = 2 时为“连結模式”,这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。 if (isReadonly) { optionString = String.Format("{0};IMEX=1", optionString); } // Office 2007 以及 以下版本使用. string jetConnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;" + optionString + "\"", excelFileName); // xlsx 扩展名 使用. string aceConnXlsxString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;" + optionString + "\"", excelFileName); // xls 扩展名 使用. string aceConnXlsString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;" + optionString + "\"", excelFileName); // 默认非 xlsx string aceConnString = aceConnXlsString; if (excelFileName.EndsWith(".xlsx", StringComparison.CurrentCultureIgnoreCase)) { // 如果扩展名为 xlsx. // 那么需要将 驱动切换为 xlsx 扩展名 的. aceConnString = aceConnXlsxString; } // 尝试使用 ACE. 假如不发生错误的话,使用 ACE 驱动. try { System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(aceConnString); cn.Open(); cn.Close(); // 使用 ACE return(aceConnString); } catch (Exception e) { // 启动 ACE 失败. //Console.WriteLine(e.Message); } // 尝试使用 Jet. 假如不发生错误的话,使用 Jet 驱动. try { System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(jetConnString); cn.Open(); cn.Close(); // 使用 Jet return(jetConnString); } catch (Exception e) { // 启动 Jet 失败. //Console.WriteLine(e.Message); } // 假如 ACE 与 JET 都失败了,默认使用 JET. return(jetConnString); }
private void btnUpsize_Click(object sender, EventArgs e) { try { #region "GetAccessTables" string[] path = txtAccessFile.Text.Split('\\'); int i = 0; string theSystemDB = ""; for (i = 0; i < path.Length - 1; i++) { if (theSystemDB == "") { theSystemDB = path[i].ToString(); } else { theSystemDB = theSystemDB + "\\" + path[i].ToString(); } } theSystemDB = theSystemDB + "\\System.mdw"; System.Data.OleDb.OleDbConnection theAccessCon = new System.Data.OleDb.OleDbConnection(); if (rbtCareWare.Checked == true) { theAccessCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtAccessFile.Text.Trim() + ";User ID=" + txtAccessUid.Text.Trim() + ";Password="******";Jet OLEDB:System database=" + theSystemDB; } else { theAccessCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtAccessFile.Text.Trim() + ";Jet OLEDB:Database Password="******";Jet OLEDB:System database=" + theSystemDB; } theAccessCon.Open(); DataTable theAccessTables = theAccessCon.GetSchema("TABLES"); DataTable theColumns = theAccessCon.GetSchema("COLUMNS"); #endregion pgbar1.Maximum = theAccessTables.Rows.Count; System.Data.OleDb.OleDbCommand theAccessCmd = new System.Data.OleDb.OleDbCommand(); pgbar1.Value = 0; foreach (DataRow theDR in theAccessTables.Rows) { if (theDR["TABLE_TYPE"].ToString() == "TABLE") { theAccessCmd.CommandText = "Select * from " + theDR["TABLE_NAME"].ToString(); theAccessCmd.Connection = theAccessCon; System.Data.OleDb.OleDbDataAdapter theAccessAdpt = new System.Data.OleDb.OleDbDataAdapter(theAccessCmd); DataTable theResult = new DataTable(); theAccessAdpt.Fill(theResult); //////Migration UpsizeManager = new Migration(); IMigration UpsizeManager; UpsizeManager = (IMigration)ObjectFactory.CreateInstance("BusinessProcess.Service.BMigration, BusinessProcess.Service"); #region "ExporttoSQL" string theConstr = string.Format("data source={0};uid={1};pwd={2};initial catalog={3}", txtSqlServer.Text.Trim(), txtSqlUid.Text.Trim(), txtSqlPwd.Text.Trim(), txtSqlDbName.Text.Trim()); UpsizeManager.UpsizeData(theResult, theColumns, txtSqlDbName.Text, theDR["TABLE_NAME"].ToString()); #endregion } pgbar1.Value = pgbar1.Value + 1; } MessageBox.Show("Upsizing Completed.", "IQCare", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception err) { MessageBox.Show(err.Message.ToString(), "IQCare", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
protected void btnUpload_Click(object sender, EventArgs e) { if (fpUpload.HasFile) { string sWJBH = SysClass.SysGlobal.GetCreateGUID(); char[] de = { '\\' }; string[] Afilename = fpUpload.FileName.Split(de); string strFileName = Afilename[Afilename.Length - 1]; string[] AExt = fpUpload.FileName.Split('.'); string strExt = ""; if (AExt.Length > 1) { strExt = AExt[AExt.Length - 1]; } string sUploadFile = sWJBH; if (strExt.Length > 0) { sUploadFile = sUploadFile + '.' + strExt; } string sPathFile = Server.MapPath("..") + "\\" + SysClass.SysUploadFile.UploadDirectory + "\\ImportExcel\\" + sUploadFile; sPathFile = Server.MapPath("../" + SysClass.SysUploadFile.UploadDirectory); if (Directory.Exists(sPathFile) == false) { Directory.CreateDirectory(sPathFile); } sPathFile = Server.MapPath("../" + SysClass.SysUploadFile.UploadDirectory + "/ImportExcel"); if (Directory.Exists(sPathFile) == false) { Directory.CreateDirectory(sPathFile); } sPathFile = Server.MapPath("..") + "\\" + SysClass.SysUploadFile.UploadDirectory + "\\ImportExcel\\" + sUploadFile; fpUpload.SaveAs(sPathFile); lblUploadFile.Text = sPathFile; lblUploadFileName.Text = fpUpload.FileName; string strConn, sheetname = "Sheet1$"; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lblUploadFile.Text + ";Extended Properties=Excel 8.0;"; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); conn.Open(); DataTable dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); if (dtExcelSchema.Rows.Count > 0) { sheetname = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); } System.Data.OleDb.OleDbDataAdapter oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", strConn); DataSet ds = new DataSet(); oada.Fill(ds); CyxPack.CommonOperation.DataBinder.BindGridViewData(gvLists, ds); conn.Close(); DataSet dk = SysClass.SysUserSalary.GetUserSalaryFieldsImportLstByDataSet(" and 1<>1"); bool _HasOpName = false; for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { DataRow row = dk.Tables[0].NewRow(); row["SourceName"] = ds.Tables[0].Columns[i].ColumnName; if (ddlTargetName.Items.FindByText(ds.Tables[0].Columns[i].ColumnName) != null) { row["TargetName"] = ddlTargetName.Items.FindByText(ds.Tables[0].Columns[i].ColumnName).Value; } else if (ddlTargetName.Items.FindByText(ds.Tables[0].Columns[i].ColumnName + "|扩展") != null) { row["TargetName"] = ddlTargetName.Items.FindByText(ds.Tables[0].Columns[i].ColumnName + "|扩展").Value; } dk.Tables[0].Rows.Add(row); if (ds.Tables[0].Columns[i].ColumnName == "姓名") { _HasOpName = true; } } gvImportSet.DataSource = dk.Tables[0].DefaultView; gvImportSet.DataBind(); } else { lblProcess.Text = "请选择上传XLS文件。"; } }
//************************************************************************* // Method: generateKeyButton_Click // Description: called when the user clicks the generate button // // Parameters: // sender - the sender of the event // e - the args representing the event // // Return Value: None //************************************************************************* private void generateKeyButton_Click(object sender, System.EventArgs e) { // store the data in a registration data object ManagedRegistrationData data = new ManagedRegistrationData(); data.CustType = customerTypeComboBox.SelectedIndex; data.Functionality = functionalityComboBox.SelectedIndex; data.Product = productComboBox.SelectedIndex; if (noExpirationCheckBox.Checked) { data.NumberOfDaysValid = 0; } else { data.NumberOfDaysValid = (int)numDaysValidNumericUpDown.Value; } if (siteLicenseCheckBox.Checked) { data.NumberOfLicensesPurchased = 0; } else { data.NumberOfLicensesPurchased = (uint)numLicensesNumericUpDown.Value; } data.SerialNumber = serialNumberTextBox.Text; data.RegistrationKey = registrationKeyTextBox.Text; // generate the key if (!ManagedRegistrationKeyGenerator.GetInstance().GenerateRegistrationKey(data)) { MessageBox.Show("Could not generate key"); return; } // save the data to the database try { regDataDbConnection.Open(); regDataDbDataAdapter.InsertCommand.CommandText = "INSERT INTO RegData(CompanyName, CustomerName, CustomerType, DaysValid, Functionality, KeyVersion, LicensesPurchased, Product, RegistrationKey, SerialNumber) VALUES (" + "'" + companyNameTextBox.Text + "', " + "'" + customerNameTextBox.Text + "', " + (data.CustType + 1) + ", " + data.NumberOfDaysValid + ", " + (data.Functionality + 1) + ", " + (data.KeyVersion + 1) + ", " + data.NumberOfLicensesPurchased + ", " + (data.Product + 1) + ", " + "'" + data.RegistrationKey + "', " + "'" + data.SerialNumber + "')"; regDataDbDataAdapter.InsertCommand.ExecuteNonQuery(); regDataDbConnection.Close(); MessageBox.Show("Key Generated Successfully!\nData Saved to the Database!"); } catch (System.Data.OleDb.OleDbException) { MessageBox.Show("Could not save data to the database!"); } // fill out all the UI fields with the new data customerTypeComboBox.SelectedIndex = data.CustType; functionalityComboBox.SelectedIndex = data.Functionality; productComboBox.SelectedIndex = data.Product; if (data.NumberOfDaysValid == 0) { numDaysValidNumericUpDown.Enabled = false; numDaysValidNumericUpDown.Value = 1; noExpirationCheckBox.Checked = true; } else { noExpirationCheckBox.Checked = false; numDaysValidNumericUpDown.Enabled = true; numDaysValidNumericUpDown.Value = data.NumberOfDaysValid; } if (data.NumberOfLicensesPurchased == 0) { siteLicenseCheckBox.Checked = true; numLicensesNumericUpDown.Value = 1; numLicensesNumericUpDown.Enabled = false; } else { siteLicenseCheckBox.Checked = false; numLicensesNumericUpDown.Value = data.NumberOfLicensesPurchased; numLicensesNumericUpDown.Enabled = true; } serialNumberTextBox.Text = data.SerialNumber; registrationKeyTextBox.Text = data.RegistrationKey; }
//********************************************************************************************************** /// <summary> /// Load excel file using Microsoft access database /// </summary> /// <param name="PthDtbaseFile"></param> /// <param name="tblSource"></param> /// <param name="dbAdapt"></param> /// <param name="dbDtSet"></param> /// <param name="dbConnect"></param> /// <param name="intMaxRow"></param> /// <returns></returns> private string GlbLoadDtbase(string PthDtbaseFile, string tblSource, System.Data.OleDb.OleDbDataAdapter dbAdapt, System.Data.DataSet dbDtSet, System.Data.OleDb.OleDbConnection dbConnect, int intMaxRow = 0) { string dbProvider = ""; //create Provider for access database Miccrosoft Jet 4.0 //string dbPassword = ""; //The Password protect database file, only author of this program know! string dbSource = ""; //The Full database source file name string dbSql = ""; //SQL command string string strExcelTableDataName = "ExcelTable"; //The name assign to excel table try { string strFileExtension = ""; strFileExtension = System.IO.Path.GetExtension(PthDtbaseFile); strFileExtension = strFileExtension.ToLower(); if (strFileExtension == ".xlsx") { dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"; //For working with Ms office 2007 & later version dbSource = "Data Source = " + PthDtbaseFile + ";"; //dbPassword = "******"; dbConnect.ConnectionString = dbProvider + dbSource + "Extended Properties=\"Excel 12.0;HDR=NO;\""; } else if (strFileExtension == ".xls") { //dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"; //For working with Ms office 2007 erlier version //dbSource = "Data Source = " + PthDtbaseFile + ";"; ////dbPassword = "******"; //dbConnect.ConnectionString = dbProvider + dbSource + "Extended Properties=\"Excel 8.0;HDR=NO;\""; dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"; //For working with Ms office 2007 & later version dbSource = "Data Source = " + PthDtbaseFile + ";"; //dbPassword = "******"; dbConnect.ConnectionString = dbProvider + dbSource + "Extended Properties=\"Excel 12.0;HDR=NO;\""; } else { MessageBox.Show("Cannot recognize excel file with extension: " + strFileExtension + " !", "GlbLoadDtbase"); } dbConnect.Open(); dbSql = "SELECT * FROM " + tblSource; dbAdapt = new System.Data.OleDb.OleDbDataAdapter(dbSql, dbConnect); if (intMaxRow == 0) { dbAdapt.Fill(dbDtSet, strExcelTableDataName); } else { dbAdapt.Fill(dbDtSet, 0, intMaxRow, strExcelTableDataName); } //return "0"; //OK code } catch (Exception ex) { MessageBox.Show("Error: Cannot loading program list [" + PthDtbaseFile + "].\r\nError message: \r\n" + ex.Message + "\r\nAre you opening program list in another program? \r\nPlease check and restart program!", "GlbLoadDtbase()"); Environment.Exit(0); return("ex.Message"); } finally { dbConnect.Close(); } return("0"); //OK code }
/************** Obtiene_Prescrip * Metodo que obtiene las prescripciones desde el Sis (dbf) ***************/ private bool Obtiene_Prescrip2xxxxxxxxxxxx() { bool exito = false; dat_presccabRetail = null; dat_prescdetRetail = null; dat_presccabNoRetail = null; dat_prescdetNoRetail = null; using (System.Data.OleDb.OleDbConnection dbConn = new System.Data.OleDb.OleDbConnection("hola")) { dbConn.Open(); try { //-- Obtenemos prescripciones RETAIL Cabecera string sql_prescripc = "SELECT cgud_gudis,cgud_tndcl,cgud_canal,cgud_caden,cgud_almac,cgud_femis FROM SCCCGUD WHERE EMPTY(CGUD_FTXTD) AND DTOS(CGUD_FEMIS)>=DTOS(DATE()-8)"; System.Data.OleDb.OleDbCommand com = new System.Data.OleDb.OleDbCommand(sql_prescripc, dbConn); System.Data.OleDb.OleDbDataAdapter ada = new System.Data.OleDb.OleDbDataAdapter(com); dat_presccabRetail = new DataTable(); ada.Fill(dat_presccabRetail); //-- Obtenemos prescripciones RETAIL Detalle string sql_prescripd = "SELECT dgud_gudis,dgud_artic,dgud_calid,dgud_costo,dgud_codpp,dgud_cpack,dgud_touni,dgud_med00,dgud_med01,dgud_med02,dgud_med03,dgud_med04,dgud_med05,dgud_med06,dgud_med07,dgud_med08,dgud_med09,dgud_med10,dgud_med11 FROM SCCCGUD INNER JOIN SCDDGUD ON CGUD_GUDIS=DGUD_GUDIS WHERE EMPTY(CGUD_FTXTD) AND DTOS(CGUD_FEMIS)>=DTOS(DATE()-8)"; System.Data.OleDb.OleDbCommand comd = new System.Data.OleDb.OleDbCommand(sql_prescripd, dbConn); System.Data.OleDb.OleDbDataAdapter adad = new System.Data.OleDb.OleDbDataAdapter(comd); dat_prescdetRetail = new DataTable(); adad.Fill(dat_prescdetRetail); } catch { // omitido } try { //-- Obtenemos prescripciones NO RETAIL Cabecera string sql_prescripc = "SELECT oc_nord,oc_client,oc_secci,oc_almac,oc_fecha,oc_ccli,oc_caden,oc_tipref,oc_docref FROM vmaoc WHERE EMPTY(oc_ftx) and oc_fecha>=date()-7"; System.Data.OleDb.OleDbCommand com = new System.Data.OleDb.OleDbCommand(sql_prescripc, dbConn); System.Data.OleDb.OleDbDataAdapter ada = new System.Data.OleDb.OleDbDataAdapter(com); dat_presccabNoRetail = new DataTable(); ada.Fill(dat_presccabNoRetail); //-- Obtenemos prescripciones NO RETAIL Detalle string sql_prescripd = "SELECT od_nord,od_cart,od_cali,od_costo,od_qo00,od_qo01,od_qo02,od_qo03,od_qo04,od_qo05,od_qo06,od_qo07,od_qo08,od_qo09,od_qo10,od_qo11 FROM vmaoc INNER JOIN vmaod ON oc_nord=od_nord WHERE EMPTY(oc_ftx) and oc_fecha>=date()-7"; System.Data.OleDb.OleDbCommand comd = new System.Data.OleDb.OleDbCommand(sql_prescripd, dbConn); System.Data.OleDb.OleDbDataAdapter adad = new System.Data.OleDb.OleDbDataAdapter(comd); dat_prescdetNoRetail = new DataTable(); adad.Fill(dat_prescdetNoRetail); } catch { // omitido } if ((dat_presccabRetail != null && dat_presccabRetail.Rows.Count > 0) || (dat_presccabNoRetail != null && dat_presccabNoRetail.Rows.Count > 0)) { exito = true; } } return(exito); }
// create excel sheet for WoT hierarchical structure public static void write_hierarchicals_excel(string dir, SqlConnection con2db) { string path = dir + "\\WoT"; string filename = "WoT_Structure.xls"; string sheet1 = "Building", sheet2 = "Device"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } if (File.Exists(path + "\\" + filename)) { File.Delete(path + "\\" + filename); } string header1 = "[id]int,[title]string,[root_id]int,[type]string,[description]string,[image_url]string"; string header2 = "[id]int,[building_id]int,[title]string,[type]string,[model]string,[serial]string,[manufacturer]string,[description]string,[gateway_id]int,[pin_id]int,[ws_address]string,[img_on_url]string,[img_off_url]string"; System.Data.OleDb.OleDbConnection ExcelCon; ExcelCon = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "\\" + filename + "';Extended Properties='Excel 8.0;HDR=Yes'"); ExcelCon.Open(); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("create table [" + sheet1 + "] (" + header1 + " ) ", ExcelCon); cmd.ExecuteNonQuery(); cmd = new System.Data.OleDb.OleDbCommand("create table [" + sheet2 + "] (" + header2 + " ) ", ExcelCon); cmd.ExecuteNonQuery(); ExcelCon.Close(); string sql1 = "Select id, title, root_id, type, description from building"; string sql2 = "Select id, root_id, title, type, model, serial, manufacturer, description, gateway_id, pin_id,ws_address from device"; con2db.Close(); con2db.Open(); ExcelCon.Close(); ExcelCon.Open(); SqlCommand sqlcmd = new SqlCommand(sql1, con2db); SqlDataAdapter dt = new SqlDataAdapter(sqlcmd); DataSet st = new DataSet(); dt.Fill(st); DataRowCollection dtc = st.Tables[0].Rows; foreach (DataRow dr in dtc) { try { int.Parse(dr[2] + ""); } catch { dr[2] = 0; } //string txt = dr[4].ToString(); for (int i = 0; i < 5; i++) { if (dr[i].ToString().Contains("'")) { dr[i] = dr[i].ToString().Replace("'s", "''s"); } } cmd.CommandText = " insert into [" + sheet1 + "] ([id],[title],[root_id],[type],[description]) values (" + dr[0] + ",'" + dr[1].ToString() + "'," + dr[2] + ",'" + dr[3].ToString() + "','" + dr[4] + "')"; cmd.Connection = ExcelCon; cmd.ExecuteNonQuery(); } con2db.Close(); con2db.Open(); ExcelCon.Close(); ExcelCon.Open(); sqlcmd = new SqlCommand(sql2, con2db); dt = new SqlDataAdapter(sqlcmd); st = new DataSet(); dt.Fill(st); dtc = st.Tables[0].Rows; foreach (DataRow dr in dtc) { cmd.CommandText = " insert into [" + sheet2 + "] ([id],[building_id],[title],[type],[model],[serial],[manufacturer],[description],[gateway_id],[pin_id],[ws_address]) values (" + dr[0] + "," + dr[1] + ",'" + dr[2] + "','" + dr[3] + "','" + dr[4] + "','" + dr[5] + "','" + dr[6] + "','" + dr[7] + "'," + dr[8] + "," + dr[9] + ",'" + dr[10] + "')"; cmd.Connection = ExcelCon; cmd.ExecuteNonQuery(); } con2db.Close(); ExcelCon.Close(); }
public void showInGrid() { System.Data.OleDb.OleDbConnection MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + saveTo + ";Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\";"); MyConnection.Open(); DataTable dtExcelSchema; dtExcelSchema = MyConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); // MessageBox.Show(dtExcelSchema.Rows.Count.ToString()); DataRow[] drSheet = null; drSheet = dtExcelSchema.Select("TABLE_NAME like '%MIM Build Plan%'"); if (drSheet.Length > 0) { string sheetName = drSheet[0]["TABLE_NAME"].ToString(); // sheetName = "MIM Build Plan 2016-07-13 [[Enormous Sabre-Toothed Tiger]]"; // MessageBox.Show("Sheet Name is " + dtExcelSchema.Rows[0]["TABLE_NAME"].ToString() + " " + dtExcelSchema.Rows[1]["TABLE_NAME"].ToString() + " " + dtExcelSchema.Rows[2]["TABLE_NAME"].ToString()); System.Data.OleDb.OleDbDataAdapter MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "]", MyConnection); MyCommand.TableMappings.Add("Table", "TestTable"); System.Data.DataSet DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); DataTable myTable = DtSet.Tables[0]; DataTable newtab = new DataTable(); for (int colind = 0; colind < myTable.Columns.Count; colind++) { if (myTable.Rows[0][colind].ToString().Trim() != "") { newtab.Columns.Add(new DataColumn(myTable.Rows[0][colind].ToString())); } } myTable.Rows[0].Delete(); myTable = myTable.Select("F1 <>''").CopyToDataTable(); foreach (DataRow dr in myTable.Rows) { DataRow drnew = newtab.NewRow(); for (int j = 0; j < newtab.Columns.Count; j++) { drnew[j] = dr[j]; } newtab.Rows.Add(drnew); } dataGridView1.DataSource = newtab; //dataGridView1.DisplayLayout.Bands[0].Columns["F1"].Width = 80; //dataGridView1.DisplayLayout.Bands[0].Columns["F2"].Width = 70;+ //dataGridView1.DisplayLayout.Bands[0].Columns["F3"].Width = 100; //dataGridView1.DisplayLayout.Bands[0].Columns["F4"].Width = 140; //dataGridView1.DisplayLayout.Bands[0].Columns["F5"].Width = 30; //dataGridView1.DisplayLayout.Bands[0].Columns["F6"].Width = 80; //dataGridView1.DisplayLayout.Bands[0].Columns["F7"].Width = 80; //dataGridView1.DisplayLayout.Bands[0].Columns["F8"].Width = 350; //dataGridView1.DisplayLayout.Bands[0].Columns["F9"].Width = 50; //dataGridView1.DisplayLayout.Bands[0].Columns["F10"].Width = 80; //dataGridView1.DisplayLayout.Bands[0].Columns["F11"].Width = 40; //dataGridView1.DisplayLayout.Bands[0].Columns["F12"].Width = 100; //dataGridView1.DisplayLayout.Bands[0].Columns["F13"].Width = 90; //dataGridView1.DisplayLayout.Bands[0].Columns["F14"].Width = 50; //dataGridView1.DisplayLayout.Bands[0].Columns["F15"].Width = 50; //dataGridView1.DisplayLayout.Bands[0].Columns["F16"].Width = 50; //dataGridView1.DisplayLayout.Bands[0].Columns["F17"].Width = 50; //dataGridView1.DisplayLayout.Bands[0].Columns["F18"].Width = 50; //dataGridView1.DisplayLayout.Bands[0].Columns["F19"].Width = 120; //dataGridView1.DisplayLayout.Bands[0].Columns["F20"].Width = 120; //dataGridView1.DisplayLayout.Bands[0].Columns["F21"].Width = 80; //MyCommand.Fill(DtSet); //dataGridView1.DataSource = DtSet.Tables[0]; lblNumOfRow.Text = "Numbers of rows : " + newtab.Rows.Count.ToString(); } MyConnection.Close(); }
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)); }
//根据XML节点获取连接信息 private object GetDBInfoByXMLNode(XmlElement dbElement, string strPath) { try { string strType = dbElement.GetAttribute("类型"); string strServer = dbElement.GetAttribute("服务器"); string strInstance = dbElement.GetAttribute("服务名"); string strDB = dbElement.GetAttribute("数据库"); if (strPath != "") { strDB = strPath + strDB; } string strUser = dbElement.GetAttribute("用户"); string strPassword = dbElement.GetAttribute("密码"); string strVersion = dbElement.GetAttribute("版本"); IPropertySet pPropSet = null; switch (strType.Trim().ToLower()) { case "pdb": pPropSet = new PropertySetClass(); AccessWorkspaceFactory pAccessFact = new AccessWorkspaceFactoryClass(); if (!File.Exists(strDB)) { FileInfo filePDB = new FileInfo(strDB); pAccessFact.Create(filePDB.DirectoryName, filePDB.Name, null, 0); } pPropSet.SetProperty("DATABASE", strDB); IWorkspace pdbWorkspace = pAccessFact.Open(pPropSet, 0); pAccessFact = null; return(pdbWorkspace); case "gdb": pPropSet = new PropertySetClass(); FileGDBWorkspaceFactoryClass pFileGDBFact = new FileGDBWorkspaceFactoryClass(); if (!Directory.Exists(strDB)) { DirectoryInfo dirGDB = new DirectoryInfo(strDB); pFileGDBFact.Create(dirGDB.Parent.FullName, dirGDB.Name, null, 0); } pPropSet.SetProperty("DATABASE", strDB); IWorkspace gdbWorkspace = pFileGDBFact.Open(pPropSet, 0); pFileGDBFact = null; return(gdbWorkspace); case "sde": pPropSet = new PropertySetClass(); IWorkspaceFactory pSdeFact = new SdeWorkspaceFactoryClass(); pPropSet.SetProperty("SERVER", strServer); pPropSet.SetProperty("INSTANCE", strInstance); pPropSet.SetProperty("DATABASE", strDB); pPropSet.SetProperty("USER", strUser); pPropSet.SetProperty("PASSWORD", strPassword); pPropSet.SetProperty("VERSION", strVersion); IWorkspace sdeWorkspace = pSdeFact.Open(pPropSet, 0); pSdeFact = null; return(sdeWorkspace); case "access": System.Data.Common.DbConnection dbCon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDB); dbCon.Open(); return(dbCon); //case "oracle": // string strOracle = "Data Source=" + strDB + ";Persist Security Info=True;User ID=" + strUser + ";Password="******";Unicode=True"; // System.Data.Common.DbConnection dbConoracle = new OracleConnection(strOracle); // dbConoracle.Open(); // return dbConoracle; //case "sql": // string strSql = "Data Source=" + strDB + ";Initial Catalog=" + strInstance + ";User ID=" + strUser + ";Password=" + strPassword; // System.Data.Common.DbConnection dbConsql = new SqlConnection(strSql); // dbConsql.Open(); // return dbConsql; default: break; } return(null); } catch (Exception e) { //******************************************************************* //guozheng added //if (ModData.SysLog != null) //{ // ModData.SysLog.Write(e, null, DateTime.Now); //} //else //{ // ModData.SysLog = new SysCommon.Log.clsWriteSystemFunctionLog(); // ModData.SysLog.Write(e, null, DateTime.Now); //} //******************************************************************** return(null); } }
private Licenza SonoLicenziato_pri(string Utente, string Codice) { var l = Licenza.Fallito; var p = System.IO.Path.Combine(Server.MapPath("public"), "/files/files.mdb"); using (var c = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p)) { try { c.Open(); var s = "SELECT top 1 * FROM Licenze where Chiave = @Chiave"; var dl = new DatiLicenza(false); using (var cm = new System.Data.OleDb.OleDbCommand(s, c)) { var p1 = new System.Data.OleDb.OleDbParameter("Chiave", Codice); cm.Parameters.Add(p1); using (var dr = cm.ExecuteReader()) { while (dr.HasRows && dr.Read()) { dl.Chiave = ObjectToString(dr["Chiave"]); dl.Stato = ObjectToString(dr["Stato"]); dl.Utente = ObjectToString(dr["Utente"]); dl.Ok = true; } dr.Close(); } } if (dl.Ok) { if (dl.Utente.Equals(Utente, StringComparison.OrdinalIgnoreCase)) { l = Licenza.GiaRegistrato; if (dl.Stato == "OK") { l = Licenza.RegistratoOK; } } else { if (dl.Stato == "OK") { l = Licenza.GiaRegistrato; } else { var i = 0; var z = "update Licenze set Utente = @Utente, Stato = @Stato where Chiave = @Chiave"; var p3 = new System.Data.OleDb.OleDbParameter[] { new System.Data.OleDb.OleDbParameter("Utente", Utente), new System.Data.OleDb.OleDbParameter("Stato", "OK"), new System.Data.OleDb.OleDbParameter("Chiave", Codice) }; using (var cm2 = new System.Data.OleDb.OleDbCommand(z, c)) { cm2.Parameters.AddRange(p3); try { i = cm2.ExecuteNonQuery(); } catch { i = 0; } } if (i > 0) { l = Licenza.RegistratoOK; } } } } else { l = Licenza.CodiceInesistente; } } catch { //error } if (c.State == System.Data.ConnectionState.Open) { c.Close(); } } return(l); }
static void Main(string[] args) { if (args.Count() > 0) { if (args[0] == "ship") { #region Mark As Shipped try { //This is a fix for it being a desktop application. This should not //be run on a live webserver ServicePointManager.ServerCertificateValidationCallback = delegate { return(true); }; //Use app.config so the directory can be changed regardless of the code string dir = ConfigurationManager.AppSettings["ShippingData"].ToString(); //grab all the files in the directory Directory.GetFiles(dir); foreach (var file in Directory.GetFiles(dir)) { //Get just the name of the file FileInfo import = new FileInfo(file); string filename = import.Name.Split('.')[0]; //Split on '_' and it should create a array with the length of 3 string[] filenamesplit = filename.Split('_'); if (filenamesplit.Length == 4) { string consumerKey = filenamesplit[0]; string consumerSecret = filenamesplit[1]; var client = new RestClient(); client.BaseUrl = APIHelper.GetBaseUri(); client.Authenticator = new HttpBasicAuthenticator(consumerKey, consumerSecret); var summaries = ImportHelper.ImportShippingSummaries(file); if (summaries.FirstOrDefault() != null && summaries.FirstOrDefault().Company_ID != "-1") { //changed to false if any of the orders fail to be posted. bool allShipped = true; foreach (var summary in summaries) { //Use the summary information to create a JSON object string order = OrderServices.GetOrderByOrderNumber(client, summary); //if the string did not return and error try to //do a post. if (!order.StartsWith("An Error Has Occured")) { //Shipsation async code, but it causes business logic problems //Task tsk = OrderServices.MakeAsyncOrderPost(order, consumerKey, consumerSecret); //tsk.Wait(); //Post the order to be marked. allShipped = OrderServices.MarkOrderPost(client, order); } else { allShipped = false; } } if (allShipped) { // File.Delete(file); //Update first row companyID to -1 string DSN = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", file); using (System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection(DSN)) { Con.Open(); using (System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand()) { Com.Connection = Con; Com.CommandText = "UPDATE [Shipping_Summary___Optional_Sav$] SET [Company_ID] = -1 WHERE [Company_ID] = " + summaries.FirstOrDefault().Company_ID; Com.ExecuteNonQuery(); } Con.Close(); } } else { MailMessage mail = new MailMessage(); SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com"); mail.From = new MailAddress("*****@*****.**"); mail.To.Add("*****@*****.**"); mail.Subject = "An Error Has Occured Posting to Ship Station"; mail.Body = string.Format("Not all orders for the file {0} were marked as shipped", import.Name); SmtpServer.Port = 587; SmtpServer.Credentials = new NetworkCredential("*****@*****.**", "5HB7c9ut"); SmtpServer.EnableSsl = true; SmtpServer.Send(mail); } } } } } catch (Exception ex) { MailMessage mail = new MailMessage(); SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com"); mail.From = new MailAddress("*****@*****.**"); mail.To.Add("*****@*****.**"); mail.Subject = "An Error Has Occured Posting to Ship Station"; mail.Body = string.Format(ex.ToString()); SmtpServer.Port = 587; SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "5HB7c9ut"); SmtpServer.EnableSsl = true; SmtpServer.Send(mail); } #endregion } if (args[0] == "orders") { #region Pull Order var customers = ImportHelper.ImportShipstationCustomers(); foreach (var customer in customers) { Console.WriteLine("Processing {0}", customer.CompanyName); var client = new RestClient(); client.BaseUrl = APIHelper.GetBaseUri(); client.Authenticator = new HttpBasicAuthenticator(customer.Key, customer.Token); //RestRequest request = new RestRequest("users", Method.GET); //IRestResponse response = client.Execute(request); //get all the stores Console.WriteLine("Getting All Stores"); List <Store> stores = StoreService.GetAllStores(client); Console.WriteLine("Pulling down unshipped orders"); //var orders = OrderServices.GetOrders(client, storeId); List <Order> orders = new List <Order>(); foreach (var store in stores) { Console.WriteLine("Pulling down unshipped orders for {0}", store.storeName); orders.AddRange(OrderServices.GetOrders(client, store.storeId)); } var SClientorders = orders.Where(o => o.userId == APIHelper.GetShipstationUserID()).ToList(); //check to see if the id's have been List <string> extractedids = new List <string>(); using (StreamReader reader = new StreamReader(System.Configuration.ConfigurationManager.AppSettings["Path"] + "extractedids.txt")) { string line = reader.ReadLine(); while (line != null) { extractedids.Add(line.Trim()); line = reader.ReadLine(); } reader.Close(); } foreach (var id in extractedids) { var order = SClientorders.Where(xo => xo.orderNumber == id).FirstOrDefault(); if (order != null) { SClientorders.Remove(order); } } if (SClientorders.Count > 0) { Console.WriteLine("Creating Extraction Report Count : {0}", SClientorders.Count); ExportHelpers.ExtractionReport(SClientorders, customer, stores); Console.WriteLine("Sending Email"); MailHelpers.SendEmailWithOrders(customer.CompanyName); Console.WriteLine("Finished Processing {0}", customer.CompanyName); //save the id's using (StreamWriter writer = new StreamWriter(System.Configuration.ConfigurationManager.AppSettings["Path"] + "extractedids.txt", true)) { foreach (var order in SClientorders.OrderBy(y => y.orderNumber)) { writer.WriteLine(order.orderNumber); } } } else { MailMessage mail = new MailMessage(); SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com"); mail.From = new MailAddress("*****@*****.**"); mail.To.Add("[email protected],[email protected]"); mail.Bcc.Add("*****@*****.**"); mail.Subject = customer.CompanyName + " Orders From Ship Station - No New Orders"; mail.Body = string.Format("No New Orders"); SmtpServer.Port = 587; SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "5HB7c9ut"); SmtpServer.EnableSsl = true; SmtpServer.Send(mail); } } #endregion } Console.WriteLine("done"); } else { MailMessage mail = new MailMessage(); SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com"); mail.From = new MailAddress("*****@*****.**"); mail.To.Add("*****@*****.**"); mail.Subject = "An Error Has Occured Posting to Ship Station"; mail.Body = string.Format("No Argument Has Been Passed"); SmtpServer.Port = 587; SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "5HB7c9ut"); SmtpServer.EnableSsl = true; SmtpServer.Send(mail); } }
public void BuildAxisOrderBitArray() { if (!File.Exists(EpsgAccessDatabase)) { throw new IgnoreException("Epsg Access Database not found"); } var ba = new BitArray(32768); using (var cn = new System.Data.OleDb.OleDbConnection( //$"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={EpsgAccessDatabase};" $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={EpsgAccessDatabase};")) { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandText = Sql; using (var dr = cmd.ExecuteReader()) { if (dr != null) { while (dr.Read()) { var code = dr.GetInt32(0); if (code > 32767) { continue; } ba[code] = true; } } } } string enc; var buffer = new byte[4096]; ba.CopyTo(buffer, 0); #if NET45 using (var bufferStream = new MemoryStream(buffer)) { using (var compressedStream = new MemoryStream()) { using (var s = new DeflateStream(compressedStream, CompressionMode.Compress)) { bufferStream.CopyTo(s); compressedStream.Flush(); } enc = Convert.ToBase64String(compressedStream.ToArray()); Console.WriteLine("Compressed"); WriteBlocks(enc); } } #endif Console.WriteLine("\nByte array"); WriteBytes(buffer, 20); enc = Convert.ToBase64String(buffer); Console.WriteLine("\nUncompressed"); WriteBlocks(enc); Console.WriteLine("\nByte array"); WriteBytes(buffer, 20); }
/// <include file='InterfaceDocumentationComments.xml' path='doc/members/member[@name="M:FrameworkAbstraction.IOleDbConnection.Open"]/*'/> public void Open() { oleDbConnection.Open(); }
public bool AggiornaUtente_pri(UtenteProgramma u) { var s = ""; var i = 0; var n = 0; try { var p = System.IO.Path.Combine(Server.MapPath("public"), "/files/files.mdb"); using (var c = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p)) { try { c.Open(); try { s = "SELECT count(*) Num FROM Utenti where Programma=:Programma and Nome=:Nome"; var pr = new System.Data.OleDb.OleDbParameter[] { new System.Data.OleDb.OleDbParameter("Programma", u.Programma), new System.Data.OleDb.OleDbParameter("Nome", u.Utente) }; using (var m = new System.Data.OleDb.OleDbCommand(s, c)) { m.Parameters.AddRange(pr); using (var dr = m.ExecuteReader()) { while (dr.HasRows && dr.Read()) { try { n = Convert.ToInt32(dr["Num"]); } catch { //error n = 0; } } dr.Close(); } } } catch { //error } try { if (n > 0) { s = "update Utenti set Versione=:Versione where Nome=:Nome and Programma=:Programma"; } else { s = "insert into Utenti (Versione,Nome,Programma) values (:Versione,:Nome,:Programma)"; } var pr = new System.Data.OleDb.OleDbParameter[] { new System.Data.OleDb.OleDbParameter("Versione", u.Versione), new System.Data.OleDb.OleDbParameter("Nome", u.Utente), new System.Data.OleDb.OleDbParameter("Programma", u.Programma) }; using (var m = new System.Data.OleDb.OleDbCommand(s, c)) { m.Parameters.AddRange(pr); i = m.ExecuteNonQuery(); } } catch { //error } c.Close(); } catch { //error } } } catch { //error } return(i > 0); }
public static void TestConnection(string connectionString) { var cn = new System.Data.OleDb.OleDbConnection(connectionString); cn.Open(); }
/// <summary> /// 查看 案卷级 的 电子案卷 /// </summary> /// <param name="FileID">案卷ID</param> public MemoryStream OpenFilePDF(int FileID, bool isSingleClent) { string tablename = string.Empty; switch (DataBase.archivesType) { case ArchivesType.文书档案: tablename = "WS_FilePdf"; break; case ArchivesType.归档文件: tablename = "GD_FilePdf"; break; case ArchivesType.公安业务档案: tablename = "YW_FilePdf"; break; case ArchivesType.科技档案_2000: tablename = "keJi_FilePdf"; break; case ArchivesType.城建档案新: tablename = "CjFilePdf"; break; case ArchivesType.城建档案旧: tablename = "OldCjFilePdf"; break; case ArchivesType.社保业务档案: tablename = "SBYW_FilePdf"; break; case ArchivesType.编制档案: tablename = "BZ_FilePdf"; break; case ArchivesType.沿革档案: tablename = "YG_FilePdf"; break; default: break; } if (tablename == string.Empty) { return(null); } string fieldname = (DataBase.archivesType == ArchivesType.归档文件) ? "文件ID" : "案卷ID"; MemoryStream ms = new MemoryStream(); if (!isSingleClent) { SqlConnection conn = new SqlConnection(DataSql.ConStr); string sql = "select Pdf from XfPdf.dbo." + tablename + " where " + fieldname + " = " + FileID; try { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { ms = new MemoryStream((byte[])dr[0]); //创建Stream对象 } else { ms = null; } dr.Close(); conn.Close(); return(ms); } catch (System.Exception e) { return(null); } } else { string connstr = DataSql.ConStr_Pdf; string sql = "select Pdf from " + tablename + " where " + fieldname + " = " + FileID; try { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connstr); conn.Open(); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); cmd.Connection = conn; cmd.CommandText = sql; System.Data.OleDb.OleDbDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { ms = new MemoryStream((byte[])dr[0]); //创建Stream对象 } else { ms = null; } dr.Close(); conn.Close(); return(ms); } catch (System.Exception e) { return(null); } } }
private static void ValidaProductoVentas() { // Gets the Calendar instance associated with a CultureInfo. CultureInfo myCI = new CultureInfo("en-US"); Calendar myCal = myCI.Calendar; // Gets the DTFI properties required by GetWeekOfYear. CalendarWeekRule myCWR = myCI.DateTimeFormat.CalendarWeekRule; DayOfWeek myFirstDOW = myCI.DateTimeFormat.FirstDayOfWeek; //string sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.GetDirectoryName(path) + ";Extended Properties=dBASE IV;"; string sConn = "Provider = vfpoledb.1;Data Source=" + System.IO.Path.GetDirectoryName(path) + ";Collating Sequence=general"; DataTable productos = new DataTable(); productos = ListaProductoVentas(); using (System.Data.OleDb.OleDbConnection dbConn = new System.Data.OleDb.OleDbConnection(sConn)) { try { dbConn.Open(); } catch (Exception ex) { ActualizaLogVentas("", "Error en encontrar DBF en " + path, ex.Message, proceso_log); throw(ex); } foreach (DataRow row in productos.Rows) { int weekofyear = myCal.GetWeekOfYear(Convert.ToDateTime(row["fecha"]), myCWR, myFirstDOW); try { string stock_pri = "SELECT * FROM SCACSAL WHERE CSAL_EMPRE ='" + empresa + "' And CSAL_ANO = '" + Convert.ToDateTime(row["fecha"]).ToString("yyyy") + "' And CSAL_SEMAN = '" + weekofyear.ToString().PadLeft(2).Replace(" ", "0") + "' And CSAL_ALMAC = '" + almacen + "' And CSAL_ARTIC='" + row["producto"].ToString() + "' And CSAL_CALID = '" + calidad + "'"; System.Data.OleDb.OleDbCommand stk1 = new System.Data.OleDb.OleDbCommand(stock_pri, dbConn); System.Data.OleDb.OleDbDataAdapter cn_stk1 = new System.Data.OleDb.OleDbDataAdapter(stk1); DataTable stock1 = new DataTable(); cn_stk1.Fill(stock1); string stock_seg = "SELECT * FROM SCACSALP WHERE CSAL_EMPRE ='" + empresa + "' And CSAL_ANO = '" + Convert.ToDateTime(row["fecha"]).ToString("yyyy") + "' And CSAL_SEMAN = '" + weekofyear.ToString().PadLeft(2).Replace(" ", "0") + "' And CSAL_ALMAC = '" + almacen + "' And CSAL_ARTIC='" + row["producto"].ToString() + "' And CSAL_CALID = '" + calidad + "'"; System.Data.OleDb.OleDbCommand stk2 = new System.Data.OleDb.OleDbCommand(stock_seg, dbConn); System.Data.OleDb.OleDbDataAdapter cn_stk2 = new System.Data.OleDb.OleDbDataAdapter(stk2); DataTable stock2 = new DataTable(); cn_stk2.Fill(stock2); if (stock1.Rows.Count == 0 || stock2.Rows.Count == 0) { ActualizaVentas(row["id"].ToString(), "E", row["tipo"].ToString()); ActualizaLogVentas(row["id"].ToString(), row["tipo"].ToString() + " - Producto " + row["producto"].ToString() + " no tiene registro semanal.", "Tablas SCACSAL y SCACSALP", proceso_log); } } catch (Exception ex) { ActualizaLogVentas("", "Error en Validación de Productos.", ex.Message, proceso_log); } } try { //Se termina transacción y cerramos conexión dbConn.Close(); } catch (Exception ex) { ActualizaLogVentas("", "Error en Cierre de Conexión a DBF.", ex.Message, proceso_log); } } }
private bool SaveCheck() { bool bFlag = true; if (lblUploadFile.Text.Length == 0) { bFlag = false; Dialog.OpenDialogInAjax(txtOrganName, "请选择Excel工资文件!"); } else if (GetOpCodeFieldName().Length == 0) { bFlag = false; Dialog.OpenDialogInAjax(txtOrganName, "请选择工号字段!"); } else if (GetOpNameFieldName().Length == 0) { bFlag = false; Dialog.OpenDialogInAjax(txtOrganName, "请选择姓名字段!"); } else { string _OpCodeFieldName = GetOpCodeFieldName(); string _OpNameFieldName = GetOpNameFieldName(); string strConn, sheetname = "Sheet1$"; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lblUploadFile.Text + ";Extended Properties=Excel 8.0;"; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); conn.Open(); DataTable dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); if (dtExcelSchema.Rows.Count > 0) { sheetname = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); } System.Data.OleDb.OleDbDataAdapter oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", strConn); DataSet ds = new DataSet(); oada.Fill(ds); oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "] Where 1<>1", strConn); DataSet ds1 = new DataSet(); oada.Fill(ds1); conn.Close(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string _SQL = "Select Top 1 1 From SysUser_Info Where OpCode='" + ds.Tables[0].Rows[i][_OpCodeFieldName].ToString() + "' And OpName<>'" + ds.Tables[0].Rows[i][_OpNameFieldName].ToString() + "'"; if (SysClass.SysGlobal.GetExecSqlIsExist(_SQL)) { DataRow row = ds1.Tables[0].NewRow(); for (int k = 0; k < ds1.Tables[0].Columns.Count; k++) { row[ds1.Tables[0].Columns[k].ColumnName] = ds.Tables[0].Rows[i][ds1.Tables[0].Columns[k].ColumnName].ToString(); } ds1.Tables[0].Rows.Add(row); } } gvAbnormal.DataSource = ds1.Tables[0].DefaultView; gvAbnormal.DataBind(); gvAbnormal.Visible = gvAbnormal.Rows.Count > 0; if (gvAbnormal.Rows.Count > 0) { bFlag = false; Dialog.OpenDialogInAjax(txtOrganName, "下列工号和姓名与系统内有异,请检查!"); } } return(bFlag); }
public ExecuteNonQueryOnExcel(string dbFileName) { MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + dbFileName + "';Extended Properties=Excel 8.0;"); MyConnection.Open(); }
public void LoadApAr(object sender, EventArgs e) { this.asset = 0.00; System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(); con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=acct.mdb;"; con.Open(); string quryString = "select * from AcctAP where accountid=" + this.accountid + " order by id desc"; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(quryString, con); DataSet ds = new DataSet(); da.Fill(ds, "AcctAP"); dataGridView1.Columns.Clear(); dataGridView1.Columns.Add("id", ""); dataGridView1.Columns.Add("company", "Company"); dataGridView1.Columns.Add("firstName", "First Name"); dataGridView1.Columns.Add("lastName", "Last Name"); dataGridView1.Columns.Add("date", "Date"); dataGridView1.Columns.Add("amount", "Amount"); //set autosize mode dataGridView1.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridView1.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridView1.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridView1.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; //datagrid has calculated it's widths so we can store them for (int i = 0; i <= dataGridView1.Columns.Count - 1; i++) { //store autosized widths int colw = dataGridView1.Columns[i].Width; //remove autosizing dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.None; //set width to calculated by autosize dataGridView1.Columns[i].Width = colw; } int row = ds.Tables["AcctAP"].Rows.Count - 1; for (int r = 0; r <= row; r++) { dataGridView1.Rows.Add(); dataGridView1.Rows[r].Cells[0].Value = ds.Tables["AcctAP"].Rows[r].ItemArray[7]; dataGridView1.Rows[r].Cells[1].Value = ds.Tables["AcctAP"].Rows[r].ItemArray[8]; dataGridView1.Rows[r].Cells[2].Value = ds.Tables["AcctAP"].Rows[r].ItemArray[0]; dataGridView1.Rows[r].Cells[3].Value = ds.Tables["AcctAP"].Rows[r].ItemArray[3]; dataGridView1.Rows[r].Cells[4].Value = ds.Tables["AcctAP"].Rows[r].ItemArray[5]; dataGridView1.Rows[r].Cells[5].Value = ds.Tables["AcctAP"].Rows[r].ItemArray[4]; } quryString = "select * from AcctAR where accountid=" + this.accountid + " order by id desc"; da = new System.Data.OleDb.OleDbDataAdapter(quryString, con); ds = new DataSet(); da.Fill(ds, "AcctAR"); dataGridView2.Columns.Clear(); dataGridView2.Columns.Add("id", ""); dataGridView2.Columns.Add("company", "Company"); dataGridView2.Columns.Add("firstName", "First Name"); dataGridView2.Columns.Add("lastName", "Last Name"); dataGridView2.Columns.Add("date", "Date"); dataGridView2.Columns.Add("amount", "Amount"); //set autosize mode dataGridView2.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridView2.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridView2.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridView2.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridView2.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; //datagrid has calculated it's widths so we can store them for (int i = 0; i <= dataGridView2.Columns.Count - 1; i++) { //store autosized widths int colw = dataGridView2.Columns[i].Width; //remove autosizing dataGridView2.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.None; //set width to calculated by autosize dataGridView2.Columns[i].Width = colw; } row = ds.Tables["AcctAR"].Rows.Count - 1; for (int r = 0; r <= row; r++) { dataGridView2.Rows.Add(); dataGridView2.Rows[r].Cells[0].Value = ds.Tables["AcctAR"].Rows[r].ItemArray[8]; dataGridView2.Rows[r].Cells[1].Value = ds.Tables["AcctAR"].Rows[r].ItemArray[6]; dataGridView2.Rows[r].Cells[2].Value = ds.Tables["AcctAR"].Rows[r].ItemArray[0]; dataGridView2.Rows[r].Cells[3].Value = ds.Tables["AcctAR"].Rows[r].ItemArray[1]; dataGridView2.Rows[r].Cells[4].Value = ds.Tables["AcctAR"].Rows[r].ItemArray[2]; dataGridView2.Rows[r].Cells[5].Value = ds.Tables["AcctAR"].Rows[r].ItemArray[4]; } CalculateTotalAsset(con); con.Close(); }
public Form1() { InitializeComponent(); DataTable dtProviderFactorys = DbProviderFactories.GetFactoryClasses(); DbConnectionStringBuilder dcsBuilder = new DbConnectionStringBuilder(); dcsBuilder.Add("User ID", "hzzgis"); dcsBuilder.Add("Password", "hzzgis"); dcsBuilder.Add("Service Name", "sunz"); dcsBuilder.Add("Host", "172.16.1.9"); dcsBuilder.Add("Integrated Security", false); string licPath = Application.StartupPath + "\\DDTek.lic"; if (!System.IO.File.Exists(licPath)) { licPath = CretateDDTekLic.CreateLic(); } dcsBuilder.Add("License Path", licPath); //若路径中存在空格,则会在路径名称前加上"\"" string conStr = dcsBuilder.ConnectionString; conStr = conStr.Replace("\"", ""); DDTek.Oracle.OracleConnection orclConnection = new DDTek.Oracle.OracleConnection(conStr); DDTek.Oracle.OracleCommand cmd = new DDTek.Oracle.OracleCommand(); DDTek.Oracle.OracleDataAdapter adapter = new DDTek.Oracle.OracleDataAdapter(); adapter.SelectCommand = cmd; DbDataAdapter dAdapter = adapter; DbCommand dbCommand = dAdapter.SelectCommand; orclConnection.Open(); //Configuration config = new Configuration(); //ISessionFactory pFactory = config.BuildSessionFactory(); //ISession pSession= pFactory.OpenSession(orclConnection as IDbConnection); //DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OracleClient"); //IDbConnection dbConn = factory.CreateConnection(); //if (dbConn != null) // MessageBox.Show("Connection Created"); //Conn.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Server + ")(PORT=" + Port + ")))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + Service + ")));user id=" + User + ";password="******";pooling = true;Unicode=True"; IDbConnection dbConn = new System.Data.OleDb.OleDbConnection(); string Server = "sunzvm-lc", Port = "1521", Service = "sunz", User = "******", PWD = "hzzgis"; //dbConn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Server + ")(PORT=" + Port + ")))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + Service + ")));user id=" + User + ";password="******";pooling = true;Unicode=True"; dbConn.ConnectionString = "Provider=MSDAORA.1;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Server + ")(PORT=" + Port + ")))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + Service + ")));user id=" + User + ";password="******";pooling = true;Unicode=True"; try { dbConn.Open(); } catch (Exception exp) { MessageBox.Show(exp.ToString()); } }
public static void ActualizaenDBF() { //LLena Datos Generales Llenar_datos(); //string sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.GetDirectoryName(path) + ";Extended Properties=dBASE IV;"; string sConn = "Provider = vfpoledb.1;Data Source=" + System.IO.Path.GetDirectoryName(path) + ";Collating Sequence=general"; string producto = ""; string validavta = ""; // Gets the Calendar instance associated with a CultureInfo. CultureInfo myCI = new CultureInfo("en-US"); Calendar myCal = myCI.Calendar; // Gets the DTFI properties required by GetWeekOfYear. CalendarWeekRule myCWR = myCI.DateTimeFormat.CalendarWeekRule; DayOfWeek myFirstDOW = myCI.DateTimeFormat.FirstDayOfWeek; //validacion de errores bool ActSCACODI = false; bool InsSCCCSXN = false; bool InsSCDRSXN = false; bool ActSCACSAL = false; bool ActSCACSALP = false; // Datos para hacer rollback string codigo1_ant = ""; string codigo2_ant = ""; string codigo = ""; string[] datscasal = new string[3]; List <string[]> scasal = new List <string[]>(); List <string[]> scasalp = new List <string[]>(); //Valida semanas anteriores int min_week = Convert.ToInt32(Hoy.ToString("yyyy") + Convert.ToString(myCal.GetWeekOfYear(Hoy, myCWR, myFirstDOW)).PadLeft(2).Replace(" ", "0")); DataTable datos = new DataTable(); try { ValidaProductoVentas(); //Lleno datos iniciales datos = ListaVentas(); } catch (Exception ex) { ActualizaLogVentas("", "Error en Obtención de datos de SLQ Server.", ex.Message, proceso_log); return; } using (System.Data.OleDb.OleDbConnection dbConn = new System.Data.OleDb.OleDbConnection(sConn)) { try { // Abro conexión dbConn.Open(); } catch (Exception ex) { ActualizaLogVentas("", "Error en encontrar DBF.", ex.Message, proceso_log); return; } foreach (DataRow venta in datos.Rows) { producto = venta["product_id"].ToString(); // variables string codigo1 = ""; string codigo2 = ""; int weekofyear = myCal.GetWeekOfYear(Convert.ToDateTime(venta["fecha_vta"]), myCWR, myFirstDOW); //Valida la menor semana q se está actualizando if (Convert.ToInt32(Convert.ToDateTime(venta["fecha_vta"]).ToString("yyyy") + Convert.ToString(weekofyear).PadLeft(2).Replace(" ", "0")) < min_week) { min_week = Convert.ToInt32(Convert.ToDateTime(venta["fecha_vta"]).ToString("yyyy") + Convert.ToString(weekofyear).PadLeft(2).Replace(" ", "0")); } try { if (validavta != venta["id_venta"].ToString()) { // Se da valor al concepto que se usará en la transaccion string concepto = ""; if (venta["tipo"].ToString() == "VT") { concepto = concepto_vt; } if (venta["tipo"].ToString() == "NC") { concepto = concepto_nc; } // Se Setean en False (Nueva Venta) ActSCACODI = false; InsSCCCSXN = false; InsSCDRSXN = false; ActSCACSAL = false; ActSCACSALP = false; // se limpian variables para hacer rollback codigo1_ant = ""; codigo2_ant = ""; scasal.Clear(); scasalp.Clear(); //**-- CODIGO //------------------------------------- // Obtengo código //------------------------------------- string condicion = ""; if (venta["tipo"].ToString() == "VT") { condicion = " And TAB_TIPO='090' And TAB_CTAB = '004'"; } if (venta["tipo"].ToString() == "NC") { condicion = " And TAB_TIPO='090' And TAB_CTAB = '004'"; } string consulta = "SELECT TAB_CPAR2,TAB_CPAR1 FROM SCACODI WHERE TAB_EMPRE='" + empresa + "' " + condicion; System.Data.OleDb.OleDbCommand con = new System.Data.OleDb.OleDbCommand(consulta, dbConn); System.Data.OleDb.OleDbDataAdapter cona = new System.Data.OleDb.OleDbDataAdapter(con); DataTable dt = new DataTable(); cona.Fill(dt); foreach (DataRow r in dt.Rows) { codigo1_ant = Convert.ToString(r["TAB_CPAR2"]).Trim(); codigo2_ant = Convert.ToString(r["TAB_CPAR1"]).Trim(); if (Convert.ToString(r["TAB_CPAR2"]).Trim() == Hoy.ToString("yyyy")) { codigo1 = Convert.ToString(r["TAB_CPAR2"]).Trim(); codigo2 = Convert.ToString(Convert.ToInt32(r["TAB_CPAR1"]) + 1).PadLeft(8).Replace(" ", "0"); } else { codigo1 = Hoy.ToString("yyyy"); codigo2 = Convert.ToString(1).Trim().PadLeft(8).Replace(" ", "0"); } codigo = codigo1 + codigo2; } //------------------------------------- // Actualizo datos //------------------------------------- string actualiza = "UPDATE SCACODI SET TAB_CPAR1='" + codigo2 + "',TAB_CPAR2='" + codigo1 + "' WHERE TAB_EMPRE='" + empresa + "' " + condicion; System.Data.OleDb.OleDbCommand act = new System.Data.OleDb.OleDbCommand(actualiza, dbConn); act.ExecuteNonQuery(); ActSCACODI = true; //**-- MOVIMIENTO //------------------------------------- // Ingreso datos de Movimiento Cabecera //------------------------------------- string mov_cab = "Insert Into SCCCSXN (CSXN_ALMAC, CSXN_CODIG, CSXN_FSALI, CSXN_CONCE, CSXN_ENTID, CSXN_GGUIA, CSXN_ESTAD, CSXN_TUCAL, CSXN_TUNCA, CSXN_TENTI, CSXN_TDOC1, CSXN_NDOC1, CSXN_TDOC2, CSXN_NDOC2, CSXN_SECCI, CSXN_HSALI, CSXN_PROVE, CSXN_DESTI, CSXN_AORIG, CSXN_OBSER, CSXN_EMPRE, CSXN_CANAL, CSXN_CADEN, CSXN_NMOVC, CSXN_FLASE, CSXN_UUSUA, CSXN_FUACT, CSXN_HUACT, CSXN_FTX, CSXN_FTXAQ, CSXN_TXLX, CSXN_LOG) "; mov_cab += "VALUES ('" + almacen + "','" + codigo + "',CTOD('" + venta["fecha_vta"].ToString() + "'),'" + concepto + "', '" + client + "', '',''," + venta["cant_calzado"].ToString() + "," + venta["cant_no_calzado"].ToString() + ",'','" + venta["tpdoc"].ToString() + "','" + venta["nro_doc"].ToString() + "','" + venta["tp_ped"].ToString() + "','" + venta["cod_ped"].ToString() + "','" + almacen + "','" + venta["hora_vta"].ToString() + "','','','','Venta por E-Commerce Pedido: " + venta["cod_ped"].ToString() + "','" + empresa + "','" + canal + "','" + cadena + "','','','E-COM',CTOD('" + venta["fecha_reg"].ToString() + "'),'" + venta["hora_reg"].ToString() + "','','','','" + Hoy.ToString("yyyy-MM-dd HH:mm:ss") + " E-COMMERCE')"; System.Data.OleDb.OleDbCommand mov1 = new System.Data.OleDb.OleDbCommand(mov_cab, dbConn); mov1.ExecuteNonQuery(); InsSCCCSXN = true; } //------------------------------------- // Ingreso datos de Movimiento Detalle //------------------------------------- string cadenaMED = ""; if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "00") { cadenaMED = "RSXN_MED00, RSXN_MED01, RSXN_MED02, RSXN_MED03, RSXN_MED04, RSXN_MED05, RSXN_MED06, RSXN_MED07, RSXN_MED08, RSXN_MED09, RSXN_MED10, RSXN_MED11"; } else if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "01") { cadenaMED = "RSXN_MED01, RSXN_MED00, RSXN_MED02, RSXN_MED03, RSXN_MED04, RSXN_MED05, RSXN_MED06, RSXN_MED07, RSXN_MED08, RSXN_MED09, RSXN_MED10, RSXN_MED11"; } else if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "02") { cadenaMED = "RSXN_MED02, RSXN_MED01, RSXN_MED00, RSXN_MED03, RSXN_MED04, RSXN_MED05, RSXN_MED06, RSXN_MED07, RSXN_MED08, RSXN_MED09, RSXN_MED10, RSXN_MED11"; } else if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "03") { cadenaMED = "RSXN_MED03, RSXN_MED01, RSXN_MED02, RSXN_MED00, RSXN_MED04, RSXN_MED05, RSXN_MED06, RSXN_MED07, RSXN_MED08, RSXN_MED09, RSXN_MED10, RSXN_MED11"; } else if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "04") { cadenaMED = "RSXN_MED04, RSXN_MED01, RSXN_MED02, RSXN_MED03, RSXN_MED00, RSXN_MED05, RSXN_MED06, RSXN_MED07, RSXN_MED08, RSXN_MED09, RSXN_MED10, RSXN_MED11"; } else if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "05") { cadenaMED = "RSXN_MED05, RSXN_MED01, RSXN_MED02, RSXN_MED03, RSXN_MED04, RSXN_MED00, RSXN_MED06, RSXN_MED07, RSXN_MED08, RSXN_MED09, RSXN_MED10, RSXN_MED11"; } else if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "06") { cadenaMED = "RSXN_MED06, RSXN_MED01, RSXN_MED02, RSXN_MED03, RSXN_MED04, RSXN_MED05, RSXN_MED00, RSXN_MED07, RSXN_MED08, RSXN_MED09, RSXN_MED10, RSXN_MED11"; } else if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "07") { cadenaMED = "RSXN_MED07, RSXN_MED01, RSXN_MED02, RSXN_MED03, RSXN_MED04, RSXN_MED05, RSXN_MED06, RSXN_MED00, RSXN_MED08, RSXN_MED09, RSXN_MED10, RSXN_MED11"; } else if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "08") { cadenaMED = "RSXN_MED08, RSXN_MED01, RSXN_MED02, RSXN_MED03, RSXN_MED04, RSXN_MED05, RSXN_MED06, RSXN_MED07, RSXN_MED00, RSXN_MED09, RSXN_MED10, RSXN_MED11"; } else if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "09") { cadenaMED = "RSXN_MED09, RSXN_MED01, RSXN_MED02, RSXN_MED03, RSXN_MED04, RSXN_MED05, RSXN_MED06, RSXN_MED07, RSXN_MED08, RSXN_MED00, RSXN_MED10, RSXN_MED11"; } else if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "10") { cadenaMED = "RSXN_MED10, RSXN_MED01, RSXN_MED02, RSXN_MED03, RSXN_MED04, RSXN_MED05, RSXN_MED06, RSXN_MED07, RSXN_MED08, RSXN_MED09, RSXN_MED00, RSXN_MED11"; } else if (venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") == "11") { cadenaMED = "RSXN_MED11, RSXN_MED01, RSXN_MED02, RSXN_MED03, RSXN_MED04, RSXN_MED05, RSXN_MED06, RSXN_MED07, RSXN_MED08, RSXN_MED09, RSXN_MED10, RSXN_MED00"; } string mov_det = "Insert Into SCDRSXN (RSXN_ALMAC, RSXN_CODIG, RSXN_ARTIC, RSXN_CALID, RSXN_CALDV, RSXN_TOUNI, RSXN_CPACK, RSXN_CODPP, RSXN_PPACK, RSXN_ORDCO, RSXN_GPROV, RSXN_RMED, " + cadenaMED + ", RSXN_FECHA, RSXN_EMPRE, RSXN_SECCI, RSXN_PRECI, RSXN_CODV, RSXN_PREDV, RSXN_COSDV, RSXN_CPORC, RSXN_VPORC, RSXN_FLAGC, RSXN_MERC, RSXN_CATEG, RSXN_SUBCA, RSXN_MARCA, RSXN_MERC3, RSXN_CATE3, RSXN_SUBC3, RSXN_MARC3, RSXN_FTXV, RSXN_FTX) "; mov_det += "VALUES ('" + almacen + "','" + codigo + "','" + producto + "', '" + venta["calidad"].ToString() + "', '', " + Convert.ToString(Convert.ToInt32(venta["cant_calzado"]) + Convert.ToInt32(venta["cant_no_calzado"])) + ",'" + pack + "',''," + cant_pack + ",'','','" + venta["tipo_med"].ToString() + "'," + Convert.ToString(Convert.ToInt32(venta["cant_calzado"]) + Convert.ToInt32(venta["cant_no_calzado"])) + ",0,0,0,0,0,0,0,0,0,0,0,CTOD('" + venta["fecha_vta"].ToString() + "'),'" + empresa + "','" + almacen + "'," + venta["precio"].ToString() + "," + venta["costo"].ToString() + ",0.00,0.00,'',0.00,'" + venta["estandar_consig"].ToString() + "','" + venta["linea"].ToString() + "','" + venta["categ"].ToString() + "','" + venta["subcat"].ToString() + "','" + venta["marca"].ToString() + "','" + venta["rims_linea"].ToString() + "','" + venta["rims_categ"].ToString() + "','" + venta["rims_subcat"].ToString() + "','" + venta["rims_marca"].ToString() + "','','')"; System.Data.OleDb.OleDbCommand mov2 = new System.Data.OleDb.OleDbCommand(mov_det, dbConn); mov2.ExecuteNonQuery(); InsSCDRSXN = true; //**-- STOCK //------------------------------------- // Busco datos del producto en tabla 1 //------------------------------------- string signo1 = ""; string signo2 = ""; if (venta["tipo"].ToString() == "VT") { signo1 = "-"; // resta stock signo2 = "+"; // aumenta ventas } if (venta["tipo"].ToString() == "NC") { signo1 = "+"; // aumenta stock signo2 = "-"; // resta ventas } string act_stock_pri = ""; datscasal[0] = producto; datscasal[1] = Convert.ToString(Convert.ToInt32(venta["cant_calzado"]) + Convert.ToInt32(venta["cant_no_calzado"])); datscasal[2] = venta["col_med"].ToString(); act_stock_pri = "UPDATE SCACSAL SET CSAL_STKTO=CSAL_STKTO" + signo1 + datscasal[1] + ", CSAL_SALNO = CSAL_SALNO" + signo2 + datscasal[1] + ",CSAL_MED" + venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") + "=CSAL_MED" + venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") + signo1 + datscasal[1] + " WHERE CSAL_EMPRE ='" + empresa + "' And CSAL_ANO = '" + Convert.ToDateTime(venta["fecha_vta"]).ToString("yyyy") + "' And CSAL_SEMAN = '" + weekofyear.ToString().PadLeft(2).Replace(" ", "0") + "' And CSAL_ALMAC = '" + almacen + "' And CSAL_CALID = '" + calidad + "' And CSAL_ARTIC='" + producto + "'"; // Se ejecuta sentencia System.Data.OleDb.OleDbCommand act_stk1 = new System.Data.OleDb.OleDbCommand(act_stock_pri, dbConn); act_stk1.ExecuteNonQuery(); scasal.Add(datscasal); ActSCACSAL = true; string act_stock_seg = ""; datscasal[0] = producto; datscasal[1] = Convert.ToString(Convert.ToInt32(venta["cant_calzado"]) + Convert.ToInt32(venta["cant_no_calzado"])); datscasal[2] = venta["col_med"].ToString(); act_stock_seg = "UPDATE SCACSALP SET CSAL_STKTO=CSAL_STKTO" + signo1 + datscasal[1] + ", CSAL_SALNO = CSAL_SALNO" + signo2 + datscasal[1] + ",CSAL_MED" + venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") + "=CSAL_MED" + venta["col_med"].ToString().PadLeft(2).Replace(" ", "0") + signo1 + datscasal[1] + " WHERE CSAL_EMPRE ='" + empresa + "' And CSAL_ANO = '" + Convert.ToDateTime(venta["fecha_vta"]).ToString("yyyy") + "' And CSAL_SEMAN = '" + weekofyear.ToString().PadLeft(2).Replace(" ", "0") + "' And CSAL_ALMAC = '" + almacen + "' And CSAL_CALID = '" + calidad + "' And CSAL_ARTIC='" + producto + "' And CSAL_CPACK='" + pack + "'"; // Se ejecuta sentencia System.Data.OleDb.OleDbCommand act_stk2 = new System.Data.OleDb.OleDbCommand(act_stock_seg, dbConn); act_stk2.ExecuteNonQuery(); scasalp.Add(datscasal); ActSCACSALP = true; // Termina de Actualizar y actualiza en la BD P = PROCESADO ActualizaVentas(venta["id_venta"].ToString(), "P", venta["tipo"].ToString()); } catch (Exception ex) { // Signo para hacer rollback, deben ser inversos string signo1 = ""; string signo2 = ""; if (venta["tipo"].ToString() == "VT") { signo1 = "+"; // resta stock signo2 = "-"; // aumenta ventas } if (venta["tipo"].ToString() == "NC") { signo1 = "-"; // aumenta stock signo2 = "+"; // resta ventas } // RollBack Manual /*if (ActSCACODI) * { * string actualiza_rb = "UPDATE SCACODI SET TAB_CPAR1='" + codigo2_ant + "',TAB_CPAR2='" + codigo1_ant + "' WHERE TAB_EMPRE='"+empresa+"' And TAB_TIPO='090' And TAB_CTAB = '004'"; * System.Data.OleDb.OleDbCommand act_rb = new System.Data.OleDb.OleDbCommand(actualiza_rb, dbConn); * act_rb.ExecuteNonQuery(); * }*/ if (InsSCCCSXN) { string mov_cab_rb = "DELETE FROM SCCCSXN WHERE CSXN_ALMAC='" + almacen + "' And CSXN_CODIG='" + codigo + "'"; System.Data.OleDb.OleDbCommand mov1_rb = new System.Data.OleDb.OleDbCommand(mov_cab_rb, dbConn); mov1_rb.ExecuteNonQuery(); } if (InsSCDRSXN) { string mov_det_rb = "DELETE FROM SCDRSXN WHERE RSXN_ALMAC='" + almacen + "' And RSXN_CODIG='" + codigo + "'"; System.Data.OleDb.OleDbCommand mov2_rb = new System.Data.OleDb.OleDbCommand(mov_det_rb, dbConn); mov2_rb.ExecuteNonQuery(); } if (ActSCACSAL) { foreach (string[] dat_rb in scasal) { string act_stock_pri_rb = "UPDATE SCACSAL SET CSAL_STKTO=CSAL_STKTO" + signo1 + dat_rb[1].ToString() + ", CSAL_SALNO=CSAL_SALNO" + signo2 + dat_rb[1].ToString() + ", CSAL_MED" + dat_rb[2].ToString().PadLeft(2).Replace(" ", "0") + "=CSAL_MED" + dat_rb[2].ToString().PadLeft(2).Replace(" ", "0") + signo2 + dat_rb[1].ToString() + " WHERE CSAL_ANO = '" + Hoy.ToString("yyyy") + "' And CSAL_SEMAN = '" + weekofyear.ToString().PadLeft(2).Replace(" ", "0") + "' And CSAL_ALMAC = '" + almacen + "' And CSAL_ARTIC='" + dat_rb[0].ToString() + "' And CSAL_CALID = '" + calidad + "' And CSAL_EMPRE ='" + empresa + "'"; System.Data.OleDb.OleDbCommand act_stk1_rb = new System.Data.OleDb.OleDbCommand(act_stock_pri_rb, dbConn); act_stk1_rb.ExecuteNonQuery(); } } if (ActSCACSALP) { foreach (string[] dat_rb in scasalp) { string act_stock_seg_rb = "UPDATE SCACSAL SET CSAL_STKTO=CSAL_STKTO" + signo1 + dat_rb[1].ToString() + ", CSAL_SALNO=CSAL_SALNO" + signo2 + dat_rb[1].ToString() + ", CSAL_MED" + dat_rb[2].ToString().PadLeft(2).Replace(" ", "0") + "=CSAL_MED" + dat_rb[2].ToString().PadLeft(2).Replace(" ", "0") + signo2 + dat_rb[1].ToString() + " WHERE CSAL_ANO = '" + Hoy.ToString("yyyy") + "' And CSAL_SEMAN = '" + weekofyear.ToString().PadLeft(2).Replace(" ", "0") + "' And CSAL_ALMAC = '" + almacen + "' And CSAL_ARTIC='" + dat_rb[0].ToString() + "' And CSAL_CALID = '" + calidad + "' And CSAL_EMPRE ='" + empresa + "' And CSAL_CPACK='" + pack + "'"; System.Data.OleDb.OleDbCommand act_stk2_rb = new System.Data.OleDb.OleDbCommand(act_stock_seg_rb, dbConn); act_stk2_rb.ExecuteNonQuery(); } } // Se realiza RollBack del Estado en la BD si es necesario "" ROLLBACK ActualizaVentas(venta["id_venta"].ToString(), "", venta["tipo"].ToString()); ActualizaLogVentas(venta["id_venta"].ToString(), venta["tipo"].ToString() + " - Error en Actualización de DBF.", ex.Message, proceso_log); } validavta = venta["id_venta"].ToString(); }// fin foreach try { //Se termina transacción y cerramos conexión if (min_week < Convert.ToInt32(Hoy.ToString("yyyy") + Convert.ToString(myCal.GetWeekOfYear(Hoy, myCWR, myFirstDOW)).PadLeft(2).Replace(" ", "0"))) { Envia_correo(); } dbConn.Close(); } catch (Exception ex) { ActualizaLogVentas("", "Error en Cierre de Conexión a DBF.", ex.Message, proceso_log); //tran.Rollback(); //throw ex; } }// fin using }
private string MerchantImport(string path) { string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\";"; var conn = new System.Data.OleDb.OleDbConnection(strConn); var bOpen = true; try { conn.Open(); } catch { bOpen = false; } if (!bOpen) { try { strConn = "Provider = Microsoft.Ace.OleDb.12.0; Persist Security Info = False; " + "data source = " + path + "; Extended Properties = 'Excel 12.0; HDR=yes; IMEX=1'"; conn = new System.Data.OleDb.OleDbConnection(strConn); conn.Open(); } catch (Exception err) { return(string.Format("Excel表打开失败,{0}", err.Message.ToString())); } } DataTable sheetNames = conn.GetOleDbSchemaTable (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); string Sql = "select * from [" + sheetNames.Rows[0][2] + "]"; var myCommand = new System.Data.OleDb.OleDbCommand(); myCommand.Connection = conn; myCommand.CommandText = Sql; var adapter = new System.Data.OleDb.OleDbDataAdapter(myCommand); var dset = new DataSet(); adapter.Fill(dset); conn.Close(); DataTable dt = dset.Tables[0]; int iok = 0; string[] fileds = new string[] { "商户名", "地区", "区县", "地址" }; foreach (string field in fileds) { if (!dt.Columns.Contains(field)) { return(string.Format("Excel表缺少“{0}”列", field)); } } int i = 0, total = dt.Rows.Count; foreach (DataRow dr in dt.Rows) { i++; string m_name = dr["商户名"].ToString().Trim(); m_name = string.IsNullOrEmpty(dr["商户名"].ToString().Trim()) ? "未知" : m_name; string area = dr["地区"].ToString().Trim(); string dist = dr["区县"].ToString().Trim(); string address = dr["地址"].ToString().Trim().Replace("山东省", ""); if (!string.IsNullOrEmpty(area)) { address = address.Replace(area, ""); } if (!string.IsNullOrEmpty(dist)) { address = address.Replace(dist, ""); } address = address.Replace("#N/A", "").Trim(); if (!string.IsNullOrEmpty(address)) { NameValueCollection nv = new NameValueCollection(); nv["m_name"] = m_name; nv["m_area"] = area; nv["m_dist"] = dist; nv["m_addr"] = "山东省" + area + dist + address; string[] latlng = getLatLng("山东省" + area + address); if (latlng != null) { nv["m_lat"] = latlng[0]; nv["m_lng"] = latlng[1]; } Scaler.Access.ExecuteSql(Scaler.MyForm.GetInsertSQL("m_merchant", nv)); update_status(i, total); } else { continue; } } return("ok" + iok + "条记录已导入!"); }
private void button1_Click(object sender, EventArgs e) { //Mostrar na listBox2 os alunos do curso selecionado no listBox1: try { listBox2.Items.Clear(); //limpamos a segunda lista string cursoSelec = (string)((DataRowView)listBox1.SelectedItem)[0]; string sql = "SELECT * FROM Alunos WHERE abrevcurso = '" + cursoSelec + "'"; //suponhamos o BD em C:\tempo System.Data.OleDb.OleDbConnection conexao = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/temp/Universidade3.mdb"); conexao.Open(); System.Data.OleDb.OleDbCommand comando = new System.Data.OleDb.OleDbCommand(sql, conexao); System.Data.OleDb.OleDbDataReader dr = comando.ExecuteReader(); while (dr.Read()) { string dadosAluno = dr.GetString(0) + ", " + dr.GetString(1) + ", " + dr.GetString(2) + ", " + dr.GetInt32(3) + ", " + dr.GetString(4); listBox2.Items.Add(dadosAluno); } dr.Close(); comando.Dispose(); conexao.Close(); } catch (Exception exc) { MessageBox.Show(exc.Message); } }