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