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;
}
}
/// <summary>
/// 执行查询
/// </summary>
/// <param name="ServerFileName">xls文件路径</param>
/// <param name="SelectSQL">查询SQL语句</param>
/// <returns>DataSet</returns>
public static System.Data.DataSet SelectFromXLS(string ServerFileName, string SelectSQL)
{
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + ServerFileName + "';Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(connStr);
OleDbDataAdapter da = null;
System.Data.DataSet ds = new System.Data.DataSet();
try
{
conn.Open();
da = new OleDbDataAdapter(SelectSQL, conn);
da.Fill(ds, "SelectResult");
}
catch (Exception e)
{
conn.Close();
throw e;
}
finally
{
conn.Close();
}
return ds;
}
/// <summary>
/// 导出Excel，以旧列名-新列名词典为标头
/// </summary>
/// <param name="dt"></param>
/// <param name="dicCoumnNameMapping"></param>
public static void DataTableToExcel(System.Data.DataTable dt, Dictionary<string, string> dicCoumnNameMapping, string fileName)
{
ExcelHandler eh = new ExcelHandler();
SheetExcelForm frm = new SheetExcelForm();
eh.ProcessHandler = frm.AddProcess;
eh.ProcessErrorHandler = new Action<string>((msg) =>
{
MessageBox.Show(msg);
});
try
{
frm.Show();
Delay(20);
var ds = new System.Data.DataSet();
ds.Tables.Add(dt);
eh.DataSet2Excel(ds, dicCoumnNameMapping, fileName);
ds.Tables.Remove(dt);
ds.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("导出Excel错误:" + ex.Message);
}
finally
{
Delay(20);
frm.Close();
}
}
public void GetAllRecordsFromXML()
{
System.Data.DataSet ds = new System.Data.DataSet();
ds.ReadXml(Server.MapPath("Login.xml"));
GridView1.DataSource = ds;
GridView1.DataBind();
}
public static System.Data.DataTable GetDataBySql(string sql)
{
SqlConnection con = new SqlConnection(StrCon); con.Open();
SqlDataAdapter da = new SqlDataAdapter(sql, StrCon); System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds); con.Dispose(); con.Close();
return ds.Tables[0];
}
protected EmployeeDataSet(System.Runtime.Serialization.SerializationInfo info, System.Runtime.Serialization.StreamingContext context) :
base(info, context, false) {
if ((this.IsBinarySerialized(info, context) == true)) {
this.InitVars(false);
System.ComponentModel.CollectionChangeEventHandler schemaChangedHandler1 = new System.ComponentModel.CollectionChangeEventHandler(this.SchemaChanged);
this.Tables.CollectionChanged += schemaChangedHandler1;
this.Relations.CollectionChanged += schemaChangedHandler1;
return;
}
string strSchema = ((string)(info.GetValue("XmlSchema", typeof(string))));
if ((this.DetermineSchemaSerializationMode(info, context) == System.Data.SchemaSerializationMode.IncludeSchema)) {
System.Data.DataSet ds = new System.Data.DataSet();
ds.ReadXmlSchema(new System.Xml.XmlTextReader(new System.IO.StringReader(strSchema)));
if ((ds.Tables["employee"] != null)) {
base.Tables.Add(new employeeDataTable(ds.Tables["employee"]));
}
this.DataSetName = ds.DataSetName;
this.Prefix = ds.Prefix;
this.Namespace = ds.Namespace;
this.Locale = ds.Locale;
this.CaseSensitive = ds.CaseSensitive;
this.EnforceConstraints = ds.EnforceConstraints;
this.Merge(ds, false, System.Data.MissingSchemaAction.Add);
this.InitVars();
}
else {
this.ReadXmlSchema(new System.Xml.XmlTextReader(new System.IO.StringReader(strSchema)));
}
this.GetSerializationData(info, context);
System.ComponentModel.CollectionChangeEventHandler schemaChangedHandler = new System.ComponentModel.CollectionChangeEventHandler(this.SchemaChanged);
base.Tables.CollectionChanged += schemaChangedHandler;
this.Relations.CollectionChanged += schemaChangedHandler;
}
//Procedimiento que consulta información definida por parámetros del
//query, retorna un dataset con la información
public System.Data.DataSet consultaInformacion(String queryToExecute)
{
String resultadoOperacion;
System.Data.DataSet sqlDsConsulta = new System.Data.DataSet(); ;
resultadoOperacion = "EXITOSO";
abreConexion();
try
{
//Crea las instancias
this.sqlCmdConsulta = new SqlCommand();
this.sqlDaConsulta = new SqlDataAdapter();
//Construye el comando Select
sqlCmdConsulta.Connection = cnBaseDatos;
sqlCmdConsulta.CommandText = queryToExecute;
sqlCmdConsulta.CommandType = System.Data.CommandType.Text;
sqlDaConsulta.SelectCommand = sqlCmdConsulta;
//Carga el DataSet
this.sqlDaConsulta.Fill(sqlDsConsulta);
}
catch (SqlException exc)
{
//resultadoOperacion = "Error(" & exc.Number.ToString & "): " & exc.Messag;
}
return sqlDsConsulta;
}
private static void TestStreamDataSet()
{
//Create a sample DataSet
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.DataTable table = ds.Tables.Add("Table1");
table.Columns.Add("Col1", typeof(string));
table.Columns.Add("Col2", typeof(double));
table.Rows.Add("Value 1", 59.7);
table.Rows.Add("Value 2", 59.9);
byte[] buffer;
//Serialize the DataSet (where ds is the dataset)
using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
{
DevAge.Data.StreamDataSet.Write(stream, ds,
DevAge.Data.StreamDataSetFormat.Binary);
buffer = stream.ToArray();
}
//Deserialize the DataSet (where 'buffer' is the previous serialized byte[])
System.Data.DataSet deserializedDs = new System.Data.DataSet();
using (System.IO.MemoryStream stream = new System.IO.MemoryStream(buffer))
{
DevAge.Data.StreamDataSet.Read(stream, deserializedDs,
DevAge.Data.StreamDataSetFormat.Binary, true);
}
System.Diagnostics.Debug.Assert(deserializedDs.Tables[0].Rows.Count == 2);
}
public void TC_SetDislay(System.Data.DataSet ds)
{
checkConntected();
string oldDispStr = "";
if (ds.Tables[0].Rows[0]["func_name"].ToString() != "set_ctl_sign")
throw new Exception("only support func_name = set_ctl_sign");
if (this.currDisplayds == null )
{
lock (currDispLockObj)
currDisplayds = ds;
Comm.SendPackage pkg = this.m_protocol.GetSendPackage(ds, 0xffff);
this.Send(pkg);
this.InvokeOutPutChangeEvent(this, this.GetCurrentDisplayDecs());
return;
}
else
oldDispStr = this.GetCurrentDisplayDecs();
lock(currDispLockObj)
currDisplayds = ds;
if (oldDispStr != this.GetCurrentDisplayDecs())
{
Comm.SendPackage pkg = this.m_protocol.GetSendPackage(ds, 0xffff);
this.Send(pkg);
this.InvokeOutPutChangeEvent(this, this.GetCurrentDisplayDecs());
}
}
internal static void Process (List<string> assemblies, string outputPath)
{
List<string> valid_config_files = new List<string> ();
foreach (string assembly in assemblies) {
string assemblyConfig = assembly + ".config";
if (File.Exists (assemblyConfig)) {
XmlDocument doc = new XmlDocument ();
try {
doc.Load (assemblyConfig);
} catch (XmlException) {
doc = null;
}
if (doc != null)
valid_config_files.Add (assemblyConfig);
}
}
if (valid_config_files.Count == 0)
return;
string first_file = valid_config_files [0];
System.Data.DataSet dataset = new System.Data.DataSet ();
dataset.ReadXml (first_file);
valid_config_files.Remove (first_file);
foreach (string config_file in valid_config_files) {
System.Data.DataSet next_dataset = new System.Data.DataSet ();
next_dataset.ReadXml (config_file);
dataset.Merge (next_dataset);
}
dataset.WriteXml (outputPath + ".config");
}
private void acquireData()
{
OdbcConnection conn = new OdbcConnection("Driver={SQL Server Native Client 11.0}; server=jfsql2014;database=d3charting;trusted_connection=yes;");
OdbcCommand cmd = new OdbcCommand("select * from bar_chart;");
conn.Open();
cmd.Connection = conn;
OdbcDataAdapter da = new OdbcDataAdapter();
System.Data.DataSet ds = new System.Data.DataSet();
da.SelectCommand = cmd;
da.Fill(ds);
JSONDocument = JsonConvert.SerializeObject(ds.Tables[0], Formatting.None);
cmd = new OdbcCommand("select T.Product, T.[This Year] as TY, F.Forecast FROM bar_chart T JOIN forecast F ON T.Product = F.Product");
cmd.Connection = conn;
da = new OdbcDataAdapter();
ds = new System.Data.DataSet();
da.SelectCommand = cmd;
da.Fill(ds);
ForecastData = JsonConvert.SerializeObject(ds.Tables[0], Formatting.None);
conn.Close();
}
private System.Data.DataRow GetParameter(string IDParametro, int? IDPortal, int? IDSistema, string IDUsuario)
{
// Aca se lee la informacion de la base de datos
// y se preparan los layers
string connStr = ValidacionSeguridad.Instance.GetSecurityConnectionString();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr);
conn.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand
("SELECT * FROM dbo.SF_VALOR_PARAMETRO(@IDParametro, @IDPortal, @IDSistema, @IDUsuario)", conn);
System.Data.SqlClient.SqlParameter prm = new System.Data.SqlClient.SqlParameter("@IDParametro", System.Data.SqlDbType.VarChar, 100);
prm.Value = IDParametro;
cmd.Parameters.Add(prm);
prm = new System.Data.SqlClient.SqlParameter("@IDPortal", System.Data.SqlDbType.Int);
if (IDPortal.HasValue)
{
prm.Value = IDPortal.Value;
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new System.Data.SqlClient.SqlParameter("@IDSistema", System.Data.SqlDbType.Int);
if (IDSistema.HasValue)
{
prm.Value = IDSistema.Value;
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new System.Data.SqlClient.SqlParameter("@IDUsuario", System.Data.SqlDbType.VarChar);
if (IDUsuario != null)
{
prm.Value = IDUsuario;
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
// IdParametro, Alcance, ValorTexto, ValorEntero, ValorDecimal, ValorLogico, ValorFechaHora
cmd.CommandType = System.Data.CommandType.Text;
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds);
conn.Close();
return ds.Tables[0].Rows[0];
//return resultado;
}
public List<Combustivel> listar()
{
List<Combustivel> lista = new List<Combustivel>();
System.Data.DataSet ds = new System.Data.DataSet();
Int32 x = 0;
try
{
SqlDataReader reader;
command.Connection = MsSQL.getConexao();
command.Connection.Open();
vsql.Append("SELECT ID, DESCRICAO FROM COMBUSTIVEL ");
vsql.Append("ORDER BY DESCRICAO ");
command.CommandText = vsql.ToString();
reader = command.ExecuteReader();
while (reader.Read())
{
lista.Add(new Combustivel());
lista[x].ID = Convert.ToInt32(reader["ID"]);
lista[x].Descricao = reader["DESCRICAO"].ToString();
x++;
}
return lista;
}
catch (Exception e)
{
throw new Exception("Erro ao montar a lista de Tipo de combustível. " + e.Message);
}
finally
{
command.Connection.Close();
}
}
private void LoadData()
{
taskDataAdapter = new System.Data.SQLite.SQLiteDataAdapter("SELECT * FROM events WHERE ID=" + id, connection);
alertsDataAdapter = new System.Data.SQLite.SQLiteDataAdapter("SELECT * FROM events_alerts WHERE event_id=" + id, connection);
dataSet = new System.Data.DataSet();
var taskCommandBuilder = new System.Data.SQLite.SQLiteCommandBuilder(taskDataAdapter);
var alertsCommandBuilder = new System.Data.SQLite.SQLiteCommandBuilder(alertsDataAdapter);
taskDataAdapter.Fill(dataSet, "event");
alertsDataAdapter.Fill(dataSet, "alerts");
var parentColumn = dataSet.Tables["event"].Columns["ID"];
{
var childColumn = dataSet.Tables["alerts"].Columns["event_id"];
var relation = new System.Data.DataRelation("event_alerts", parentColumn, childColumn);
dataSet.Relations.Add(relation);
}
//var table = dataSet.Tables["event"];
row = dataSet.Tables["event"].Rows[0];
dataSet.Tables["event"].RowChanged += table_RowChanged;
dataSet.Tables["alerts"].RowChanged += table_RowChanged;
dataSet.Tables["alerts"].RowDeleted += table_RowDeleted;
dataSet.Tables["alerts"].TableNewRow += table_TableNewRow;
FillDeadline(row);
FillTags(id);
TaskGrid.DataContext = dataSet.Tables["event"].DefaultView;
AlertsDataGrid.ItemsSource = dataSet.Tables["alerts"].DefaultView;
}
public void TC_SetDislay(System.Data.DataSet ds)
{
checkConntected();
if (ds.Tables[0].Rows[0]["func_name"].ToString() != "set_speed")
throw new Exception("only support func_name = set_speed");
lock (this.currDispLockObj)
{
if (currDisplayds == null)
{
this.InvokeOutPutChangeEvent(this, ds.Tables[0].Rows[0]["speed"].ToString());
}
else
if (currDisplayds.Tables[0].Rows[0]["speed"].ToString() != ds.Tables[0].Rows[0]["speed"].ToString())
this.InvokeOutPutChangeEvent(this, ds.Tables[0].Rows[0]["speed"].ToString());
currDisplayds = ds;
Comm.SendPackage pkg = this.m_protocol.GetSendPackage(ds, 0xffff);
this.Send(pkg);
}
}
public static void Demonstrate()
{
var myInt = 100;
myInt.DisplayDefiningAssembly();
var ds = new System.Data.DataSet();
ds.DisplayDefiningAssembly();
}
protected FullDataSet(System.Runtime.Serialization.SerializationInfo info, System.Runtime.Serialization.StreamingContext context) :
base(info, context, false) {
if ((this.IsBinarySerialized(info, context) == true)) {
this.InitVars(false);
System.ComponentModel.CollectionChangeEventHandler schemaChangedHandler1 = new System.ComponentModel.CollectionChangeEventHandler(this.SchemaChanged);
this.Tables.CollectionChanged += schemaChangedHandler1;
this.Relations.CollectionChanged += schemaChangedHandler1;
return;
}
string strSchema = ((string)(info.GetValue("XmlSchema", typeof(string))));
if ((this.DetermineSchemaSerializationMode(info, context) == System.Data.SchemaSerializationMode.IncludeSchema)) {
System.Data.DataSet ds = new System.Data.DataSet();
ds.ReadXmlSchema(new System.Xml.XmlTextReader(new System.IO.StringReader(strSchema)));
if ((ds.Tables["Product"] != null)) {
base.Tables.Add(new ProductDataTable(ds.Tables["Product"]));
}
if ((ds.Tables["Country"] != null)) {
base.Tables.Add(new CountryDataTable(ds.Tables["Country"]));
}
if ((ds.Tables["FarmGroup"] != null)) {
base.Tables.Add(new FarmGroupDataTable(ds.Tables["FarmGroup"]));
}
if ((ds.Tables["FarmGroupLevel2"] != null)) {
base.Tables.Add(new FarmGroupLevel2DataTable(ds.Tables["FarmGroupLevel2"]));
}
if ((ds.Tables["Manufacturer"] != null)) {
base.Tables.Add(new ManufacturerDataTable(ds.Tables["Manufacturer"]));
}
if ((ds.Tables["Packing"] != null)) {
base.Tables.Add(new PackingDataTable(ds.Tables["Packing"]));
}
if ((ds.Tables["StorageCondition"] != null)) {
base.Tables.Add(new StorageConditionDataTable(ds.Tables["StorageCondition"]));
}
if ((ds.Tables["Unit"] != null)) {
base.Tables.Add(new UnitDataTable(ds.Tables["Unit"]));
}
if ((ds.Tables["Substance"] != null)) {
base.Tables.Add(new SubstanceDataTable(ds.Tables["Substance"]));
}
this.DataSetName = ds.DataSetName;
this.Prefix = ds.Prefix;
this.Namespace = ds.Namespace;
this.Locale = ds.Locale;
this.CaseSensitive = ds.CaseSensitive;
this.EnforceConstraints = ds.EnforceConstraints;
this.Merge(ds, false, System.Data.MissingSchemaAction.Add);
this.InitVars();
}
else {
this.ReadXmlSchema(new System.Xml.XmlTextReader(new System.IO.StringReader(strSchema)));
this.InitExpressions();
}
this.GetSerializationData(info, context);
System.ComponentModel.CollectionChangeEventHandler schemaChangedHandler = new System.ComponentModel.CollectionChangeEventHandler(this.SchemaChanged);
base.Tables.CollectionChanged += schemaChangedHandler;
this.Relations.CollectionChanged += schemaChangedHandler;
}
public bool bl_CheckExistingUser(string l, string p, string rowsName)
{
ds = oCAD.m_GetRows(oMpgUser.CheckExistingUser(l, p), rowsName);
if(int.Parse(ds.Tables["test"].Rows[0].ToString()) == 1)
{
return true;
}
return false;
}
public static System.Data.DataTable GetDataTable(string sql, System.Data.SqlClient.SqlConnection conn)
{
System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(sql, conn);
adp.MissingSchemaAction = System.Data.MissingSchemaAction.AddWithKey;
System.Data.DataSet ds = new System.Data.DataSet();
adp.Fill(ds);
System.Data.DataTable tbl = ds.Tables[0];
return tbl;
}
public CL_comparaison()
{
this.oMiddleware = new CL_middleware ();
this.oDS = this.oMiddleware.m_selectionner("rows");
// en supposant qu'il n'y a qu'un enregistrement dans le dataset
this.sOrdinateur.CPU = (float)this.oDS.Tables[0].Rows[0]["CPU"];
this.sOrdinateur.RAM = (float)this.oDS.Tables[0].Rows[0]["RAM"];
this.sOrdinateur.HDD = (float)this.oDS.Tables[0].Rows[0]["HDD"];
}
protected NorthwindDataSet(System.Runtime.Serialization.SerializationInfo info, System.Runtime.Serialization.StreamingContext context) :
base(info, context) {
if ((this.IsBinarySerialized(info, context) == true)) {
this.InitVars(false);
System.ComponentModel.CollectionChangeEventHandler schemaChangedHandler1 = new System.ComponentModel.CollectionChangeEventHandler(this.SchemaChanged);
this.Tables.CollectionChanged += schemaChangedHandler1;
this.Relations.CollectionChanged += schemaChangedHandler1;
return;
}
string strSchema = ((string)(info.GetValue("XmlSchema", typeof(string))));
if ((strSchema != null)) {
System.Data.DataSet ds = new System.Data.DataSet();
ds.ReadXmlSchema(new System.Xml.XmlTextReader(new System.IO.StringReader(strSchema)));
if ((ds.Tables["Products"] != null)) {
base.Tables.Add(new ProductsDataTable(ds.Tables["Products"]));
}
if ((ds.Tables["Orders"] != null)) {
base.Tables.Add(new OrdersDataTable(ds.Tables["Orders"]));
}
if ((ds.Tables["Suppliers"] != null)) {
base.Tables.Add(new SuppliersDataTable(ds.Tables["Suppliers"]));
}
if ((ds.Tables["Shippers"] != null)) {
base.Tables.Add(new ShippersDataTable(ds.Tables["Shippers"]));
}
if ((ds.Tables["Customers"] != null)) {
base.Tables.Add(new CustomersDataTable(ds.Tables["Customers"]));
}
if ((ds.Tables["Categories"] != null)) {
base.Tables.Add(new CategoriesDataTable(ds.Tables["Categories"]));
}
if ((ds.Tables["Order Details"] != null)) {
base.Tables.Add(new Order_DetailsDataTable(ds.Tables["Order Details"]));
}
if ((ds.Tables["Employees"] != null)) {
base.Tables.Add(new EmployeesDataTable(ds.Tables["Employees"]));
}
this.DataSetName = ds.DataSetName;
this.Prefix = ds.Prefix;
this.Namespace = ds.Namespace;
this.Locale = ds.Locale;
this.CaseSensitive = ds.CaseSensitive;
this.EnforceConstraints = ds.EnforceConstraints;
this.Merge(ds, false, System.Data.MissingSchemaAction.Add);
this.InitVars();
}
else {
this.BeginInit();
this.InitClass();
this.EndInit();
}
this.GetSerializationData(info, context);
System.ComponentModel.CollectionChangeEventHandler schemaChangedHandler = new System.ComponentModel.CollectionChangeEventHandler(this.SchemaChanged);
base.Tables.CollectionChanged += schemaChangedHandler;
this.Relations.CollectionChanged += schemaChangedHandler;
}
private System.Data.DataSet MyDataSet()
{
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(str_con);
con.Open();
da_1 = new System.Data.SqlClient.SqlDataAdapter(SQL_string, con);
System.Data.DataSet data_set = new System.Data.DataSet();
da_1.Fill(data_set, "Test");
con.Close();
return data_set;
}
private string GetBatchData()
{
var assembly = System.Reflection.Assembly.GetExecutingAssembly();
string resourceName = "Processors.BBPS.CreditCard.Data.BatchData.xml";
using (System.IO.Stream strm = assembly.GetManifestResourceStream(resourceName))
{
System.Data.DataSet orginalBatch = new System.Data.DataSet();
System.Data.DataSet refund = new System.Data.DataSet();
refund.Tables.Add(new System.Data.DataTable());
var x = orginalBatch.ReadXml(strm);
var dr = orginalBatch.Tables[0].CreateDataReader();
refund.Tables[0].Columns.Add(new System.Data.DataColumn("transactionid"));
refund.Tables[0].Columns.Add(new System.Data.DataColumn("referenceTransactionid"));
refund.Tables[0].Columns.Add(new System.Data.DataColumn("amount"));
while (dr.Read())
{
var drow = refund.Tables[0].NewRow();
string refTrxId, amount;
refTrxId = dr.GetString(0);
amount = dr.GetString(6);
drow.ItemArray = new String[] { Guid.NewGuid().ToString(), refTrxId, amount };
refund.Tables[0].Rows.Add(drow);
}
using (System.IO.StringWriter sw = new System.IO.StringWriter(new System.Text.StringBuilder()))
{
List<string> columns = new List<string>();
foreach (System.Data.DataColumn d in refund.Tables[0].Columns)
{
columns.Add(d.ColumnName);
}
sw.WriteLine(string.Join(",", columns.ToArray()));
foreach (System.Data.DataRow _dr in refund.Tables[0].Rows)
{
sw.WriteLine(string.Join(",", _dr.ItemArray));
}
return sw.GetStringBuilder().ToString();
}
}
}
public CL_User bl_SearchUser(string login, string password)
{
CL_User user = new CL_User();
ds = oCAD.m_GetRows(oMpgUser.SearchUser(login, password), "USER");
user.setUserId(int.Parse(ds.Tables["USER"].Rows[0]["ID_USER"].ToString()));
user.setUserName(ds.Tables["USER"].Rows[0]["NAME_USER"].ToString());
user.setUserForName(ds.Tables["USER"].Rows[0]["FORNAME_USER"].ToString());
user.setUserLogin(ds.Tables["USER"].Rows[0]["LOGIN_USER"].ToString());
user.setUserPassword(ds.Tables["USER"].Rows[0]["PASSWORD_USER"].ToString());
return user;
}
private System.Data.DataSet MyDataSet()
{
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon);
con.Open();
dataAdap1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);
System.Data.DataSet dataSet = new System.Data.DataSet();
dataAdap1.Fill(dataSet, "Table_Data_1");
con.Close();
return dataSet;
}
private void TabItem_Loaded(object sender, RoutedEventArgs e)
{
string sql = "select strftime('%m', tr_date) as 'Maand', sum (tr_amount) as 'Totaal' from transactions " +
"where tr_date >= '2016-01-01' and internal_payment = 'false' " +
"group by strftime('%m', tr_date) order by strftime('%m', tr_date)";
System.Data.DataSet dataSet = new System.Data.DataSet();
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(sql, global.DB.connection);
dataAdapter.Fill(dataSet);
dgJaaroverzicht.ItemsSource = dataSet.Tables[0].DefaultView;
}
/// <summary>
/// 按照模板导出
/// </summary>
/// <param name="dt">要导出的datatable</param>
/// <param name="templateFile">要导出的excel模板文件</param>
/// <param name="saveFile">要保存的文件名，可以为空</param>
public void DataTableToExcelWithTemplate(System.Data.DataTable dt, string templateFile, string saveFile)
{
ExcelHandler eh = new ExcelHandler();
try
{
string exportDir = "~/ExcelFile/";//注意:该文件夹您须事先在服务器上建好才行
if (System.IO.Directory.Exists(GetPhysicalPath(exportDir)) == false)
{
System.IO.Directory.CreateDirectory(GetPhysicalPath(exportDir));
}
if (string.IsNullOrEmpty(saveFile)) saveFile = DateTime.Now.ToString("yyyyMMddHHmmssfff_") + ".xls";
//设置文件在服务器上的路径
string physicalFileName = GetPhysicalPath(System.IO.Path.Combine(exportDir, saveFile));
eh.ProcessErrorHandler = this.ErrorMessageHandler;
var ds = new System.Data.DataSet();
ds.Tables.Add(dt);
string tmpFile = eh.ExportWithTemplate(dt, templateFile, physicalFileName, false);
ds.Tables.Remove(dt);
ds.Dispose();
//==============返回客户端
ResponeToClient(physicalFileName, saveFile);
try
{
if (!string.IsNullOrEmpty(tmpFile))
{
System.IO.FileInfo fi = new System.IO.FileInfo(tmpFile);
if (fi.Exists)
{
fi.Attributes = System.IO.FileAttributes.Normal;
System.IO.File.Delete(tmpFile);
}
}
}
catch (Exception ex)
{
AddMessage(ex.Message);
}
}
catch (Exception ex)
{
if (this.ErrorMessageHandler != null) ErrorMessageHandler("导出过程中出错，" + ex.Message);
if (ErrorHandler != null) ErrorHandler(ex);
GC.Collect();
}
}
public System.Collections.ArrayList GetChapterByPid(int pid,int type)
{
using (this.getConnection)
{
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.IDbCommand cmd = this.conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM CB_Chapter WHERE C_Pid = " + pid + " AND (C_Type=" + type + " OR C_Type='9')";
System.Data.IDbDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter((System.Data.SQLite.SQLiteCommand)cmd);
adapter.Fill(ds);
return Framework.Class.PackageEntity.Package(new Framework.Entity.Chapter(), ds.Tables[0]);
}
}
public System.Collections.ArrayList GetTemplateByChapter(int cid)
{
using (this.getConnection)
{
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.IDbCommand cmd = this.conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM CB_Template WHERE C_Id = " + cid;
System.Data.IDbDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter((System.Data.SQLite.SQLiteCommand)cmd);
adapter.Fill(ds);
return Framework.Class.PackageEntity.Package(new Framework.Entity.Template(), ds.Tables[0]);
}
}
public System.Collections.ArrayList GetContentModule()
{
using (this.getConnection)
{
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.IDbCommand cmd = this.conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM FW_Module WHERE M_Level = " + Framework.Entity.Module.CHILD + " AND M_Position = " + Framework.Entity.Module.CONTENT;
System.Data.IDbDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter((System.Data.SQLite.SQLiteCommand)cmd);
adapter.Fill(ds);
return Framework.Class.PackageEntity.Package(new Framework.Entity.Module(), ds.Tables[0]);
}
}
private List <object> _caching; //Cache Pattern
public EntityRepository(System.Data.DataSet rawData)
{
_rawData = rawData;
}
public DataViewManager(System.Data.DataSet dataSet) : this(dataSet, false)
{
}
protected override void DoSalvaRegistro(System.Data.DataSet dataSet)
{
}
internal void Fill(System.Data.DataSet BolosDataSet, string p)
{
throw new NotImplementedException();
}
public RegisterPeerResult RegisterMyPeerGetCountAndPeerList(string version, string channel, System.Guid guid, out System.Data.DataSet peers, out int count)
{
object[] results = this.Invoke("RegisterMyPeerGetCountAndPeerList", new object[] {
version,
channel,
guid
});
peers = ((System.Data.DataSet)(results[1]));
count = ((int)(results[2]));
return((RegisterPeerResult)(results[0]));
}
/// <remarks/>
public RegisterPeerResult EndRegisterMyPeerGetCountAndPeerList(System.IAsyncResult asyncResult, out System.Data.DataSet peers, out int count)
{
object[] results = this.EndInvoke(asyncResult);
peers = ((System.Data.DataSet)(results[1]));
count = ((int)(results[2]));
return((RegisterPeerResult)(results[0]));
}
public object GetMerchInfo(Dictionary <string, object> dicParas)
{
try
{
string errMsg = string.Empty;
string merchId = dicParas.ContainsKey("merchId") ? dicParas["merchId"].ToString() : string.Empty;
XCCloudUserTokenModel userTokenKeyModel = (XCCloudUserTokenModel)dicParas[Constant.XCCloudUserTokenModel];
string currentMerchId = userTokenKeyModel.DataModel.MerchID;
#region 商户信息和功能菜单
//返回商户信息
string sql = "select * from Base_MerchantInfo where MerchID=@MerchID";
SqlParameter[] parameters = new SqlParameter[1];
if (string.IsNullOrEmpty(merchId))
{
parameters[0] = new SqlParameter("@MerchID", currentMerchId);
}
else
{
parameters[0] = new SqlParameter("@MerchID", merchId);
}
System.Data.DataSet ds = XCCloudBLL.ExecuteQuerySentence(sql, parameters);
if (ds.Tables.Count != 1)
{
errMsg = "获取数据异常";
return(ResponseModelFactory.CreateFailModel(isSignKeyReturn, errMsg));
}
//返回功能菜单信息
var base_MerchInfoModel = Utils.GetModelList <Base_MerchInfoModel>(ds.Tables[0]).FirstOrDefault() ?? new Base_MerchInfoModel();
sql = " exec SelectMerchFunction @MerchID";
parameters = new SqlParameter[1];
if (string.IsNullOrEmpty(merchId))
{
parameters[0] = new SqlParameter("@MerchID", currentMerchId);
}
else
{
parameters[0] = new SqlParameter("@MerchID", merchId);
}
ds = XCCloudBLL.ExecuteQuerySentence(sql, parameters);
if (ds.Tables.Count != 1)
{
errMsg = "获取数据异常";
return(ResponseModelFactory.CreateFailModel(isSignKeyReturn, errMsg));
}
//实例化一个根节点
Base_MerchFunctionModel rootRoot = new Base_MerchFunctionModel();
rootRoot.ParentID = 0;
TreeHelper.LoopToAppendChildren(Utils.GetModelList <Base_MerchFunctionModel>(ds.Tables[0]), rootRoot);
base_MerchInfoModel.MerchFunction = rootRoot.Children;
#endregion
if (string.IsNullOrEmpty(merchId))
{
#region 商户类型列表
sql = " exec SP_DictionaryNodes @MerchID,@DictKey,@RootID output ";
parameters = new SqlParameter[3];
parameters[0] = new SqlParameter("@MerchID", string.Empty);
parameters[1] = new SqlParameter("@DictKey", "商户类别");
parameters[2] = new SqlParameter("@RootID", 0);
parameters[2].Direction = System.Data.ParameterDirection.Output;
ds = XCCloudBLL.ExecuteQuerySentence(sql, parameters);
if (ds.Tables.Count == 0)
{
errMsg = "没有找到节点信息";
return(ResponseModelFactory.CreateFailModel(isSignKeyReturn, errMsg));
}
var dictionaryResponse = Utils.GetModelList <DictionaryResponseModel>(ds.Tables[0]);
//代理商不能创建代理商
if (base_MerchInfoModel.MerchType == (int)MerchType.Agent)
{
dictionaryResponse = dictionaryResponse.Where(p => !p.DictValue.Equals(Convert.ToString(MerchType.Agent), StringComparison.OrdinalIgnoreCase)).ToList();
}
//实例化一个根节点
int rootId = 0;
int.TryParse(parameters[2].Value.ToString(), out rootId);
var rootRoot2 = new DictionaryResponseModel();
rootRoot2.ID = rootId;
TreeHelper.LoopToAppendChildren(dictionaryResponse, rootRoot2);
base_MerchInfoModel.MerchTypes = rootRoot2.Children;
#endregion
}
return(ResponseModelFactory.CreateSuccessModel(isSignKeyReturn, base_MerchInfoModel));
}
catch (Exception e)
{
return(ResponseModelFactory.CreateReturnModel(isSignKeyReturn, Return_Code.F, e.Message));
}
}
/// <summary>
/// Load or reloads a subset of the chosen resultset returned by the stored procedure.
/// You can specify which record you want to be checked by default.
/// </summary>
/// <param name="PrimaryKey">Primary key of the record you want to be selected by default.</param>
/// <param name="startRecord">The zero-based record number to start with.</param>
/// <param name="maxRecords">The maximum number of records to retrieve.</param>
public void RefreshData(object PrimaryKey, int startRecord, int maxRecords)
{
if (this.LastKnownConnectionType == OlymarsDemo.DataClasses.ConnectionType.None)
{
throw new InvalidOperationException("You must call the 'Initialize' method before calling this method.");
}
this.param = new Params.spS_tblOrderItem_SelectDisplay();
switch (this.LastKnownConnectionType)
{
case OlymarsDemo.DataClasses.ConnectionType.ConnectionString:
this.param.SetUpConnection(this.connectionString);
break;
case OlymarsDemo.DataClasses.ConnectionType.SqlConnection:
this.param.SetUpConnection(this.sqlConnection);
break;
}
this.param.CommandTimeOut = this.commandTimeOut;
if (!this.param_Oit_GuidID.IsNull)
{
this.param.Param_Oit_GuidID = this.param_Oit_GuidID;
}
if (!this.param_Oit_GuidOrderID.IsNull)
{
this.param.Param_Oit_GuidOrderID = this.param_Oit_GuidOrderID;
}
if (!this.param_Oit_GuidProductID.IsNull)
{
this.param.Param_Oit_GuidProductID = this.param_Oit_GuidProductID;
}
System.Data.DataSet DS = null;
SPs.spS_tblOrderItem_SelectDisplay SP = new SPs.spS_tblOrderItem_SelectDisplay();
if (SP.Execute(ref this.param, ref DS, startRecord, maxRecords))
{
this.DataSource = DS.Tables[this.tableName].DefaultView;
this.DataValueField = this.valueMember;
this.DataTextField = this.displayMember;
if (this.doDataBindAfterRefreshData)
{
this.DataBind();
}
if (PrimaryKey != null)
{
System.Web.UI.WebControls.ListItem listItem = this.Items.FindByValue(Convert.ToString(PrimaryKey));
if (listItem != null)
{
listItem.Selected = true;
}
}
}
else
{
SP.Dispose();
throw new OlymarsDemo.DataClasses.CustomException(this.param, "WebListBoxCustom_spS_tblOrderItem_SelectDisplay", "RefreshData");
}
}
// Update DB method
public void UpdateDB(System.Data.DataSet ds)
{
System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(da_1);
cb.DataAdapter.Update(ds.Tables[0]);
}
public static string Academy2Uni(string input)
{
String unistr = "";
unistr = input.Substring(0);
#region call
System.Data.DataSet ds = new System.Data.DataSet();
string xmlpath = System.IO.Directory.GetCurrentDirectory() + "\\XmlMapFiles\\AcademyFontFamily.xml";
ds.ReadXml(xmlpath);
System.Data.DataRowCollection drc = ds.Tables["fontTable"].Rows;
foreach (System.Data.DataRow dr in drc)
{
if (unistr.Contains(dr[0].ToString()))
{
unistr = unistr.Replace(dr[0].ToString(), dr[1].ToString());
}
}
#endregion call
#region comment
#region Kinzi
// unistr = Regex.Replace(unistr, "F", "င်္");
// unistr = Regex.Replace(unistr, "à", "င်္ံ");
// unistr = Regex.Replace(unistr, "À", "င်္ီ");
#endregion
#region Consonants
// unistr = Regex.Replace(unistr, "u", "က");
// unistr = Regex.Replace(unistr, "c", "ခ");
// unistr = Regex.Replace(unistr, "}", "ဂ");
// unistr = Regex.Replace(unistr, "C", "ဃ");
// unistr = Regex.Replace(unistr, "i", "င");
// unistr = Regex.Replace(unistr, "p", "စ");
// unistr = Regex.Replace(unistr, "q", "ဆ");
// unistr = Regex.Replace(unistr, "Z", "ဇ");
// unistr = Regex.Replace(unistr, "ç", "ဈ");
// unistr = Regex.Replace(unistr, "Ù", "ဉ ");
// unistr = Regex.Replace(unistr, "[nò]", "ည");
// unistr = Regex.Replace(unistr, "[\u00F5\u02C9\u00AF]", "ဋ");
// unistr = Regex.Replace(unistr, "\u0058", "ဌ");
// unistr = Regex.Replace(unistr, "ø", "ဍ");
// unistr = Regex.Replace(unistr, "Í", "ဎ");
// unistr = Regex.Replace(unistr, "E", "ဏ");
// unistr = Regex.Replace(unistr, "\u0077", "တ");
// unistr = Regex.Replace(unistr, "\u0078", "ထ");
// unistr = Regex.Replace(unistr, "['°]", "ဒ");
// unistr = Regex.Replace(unistr, "\u0022", "ဓ");
// unistr = Regex.Replace(unistr, "[\u0065ó]", "န");
// unistr = Regex.Replace(unistr, "\u0079", "ပ");
// unistr = Regex.Replace(unistr, "z", "ဖ");
// unistr = Regex.Replace(unistr, "\u0041", "ဗ");
// unistr = Regex.Replace(unistr, "b", "ဘ");
// unistr = Regex.Replace(unistr, "\u0072", "မ");
// unistr = Regex.Replace(unistr, "\u003C", "ယ");
// unistr = Regex.Replace(unistr, "[\\\\]", "ရ");
// unistr = Regex.Replace(unistr, "[|]", "ရ");
// unistr = Regex.Replace(unistr, "\u0076", "လ");
// unistr = Regex.Replace(unistr, "\u0026", "ဝ");
// unistr = Regex.Replace(unistr, "\u006F", "သ");
// unistr = Regex.Replace(unistr, "[[]", "ဟ");
// unistr = Regex.Replace(unistr, " V ", "ဠ");
#endregion
#region Stacked Consonant
// unistr = Regex.Replace(unistr, "Q", "\u1039" + 'ခ');
// unistr = Regex.Replace(unistr, "P", "\u1039" + 'စ');
// unistr = Regex.Replace(unistr, "é", "\u1039" + 'ဆ');
// unistr = Regex.Replace(unistr, "ß", "\u1039" + 'ဇ');
// unistr = Regex.Replace(unistr, "W", "\u1039" + 'တ');
// unistr = Regex.Replace(unistr, "M", "\u1039" + 'ထ');
// unistr = Regex.Replace(unistr, "N", "\u1039" + 'ဒ');
// unistr = Regex.Replace(unistr, "Ý", "\u1039" + 'ဓ');
// unistr = Regex.Replace(unistr, "í", "\u1039" + 'ပ');
// unistr = Regex.Replace(unistr, "ÿ", "\u1039" + 'ဖ');
// unistr = Regex.Replace(unistr, "[Ô•]", "\u1039" + 'ဗ');
// unistr = Regex.Replace(unistr, "B", "\u1039" + 'ဘ');
// unistr = Regex.Replace(unistr, "R", "\u1039" + 'မ');
// unistr = Regex.Replace(unistr, "ì", "\u1039" + 'လ');
#endregion
#region Medial
// unistr = Regex.Replace(unistr, "\u0073", "ျ");
// unistr = Regex.Replace(unistr, "\u00A1", "ျွ");
// unistr = Regex.Replace(unistr, "¡", "ျွ");
// unistr = Regex.Replace(unistr, "\u00FB", "ျှ");
// unistr = Regex.Replace(unistr, "[\u005D\u006A]", "ြ");
// unistr = Regex.Replace(unistr, "[\u00DAÚ]", "ြု");
// unistr = Regex.Replace(unistr, "G", "ွ");
// unistr = Regex.Replace(unistr, "Ï", "ွွှ");
// unistr = Regex.Replace(unistr, "[SÛ]", "ှ");
// unistr = Regex.Replace(unistr, "ë", "ွှှု");
// unistr = Regex.Replace(unistr, "ä", "ှူ");
#endregion
#region Indepandent Vowel
// unistr = Regex.Replace(unistr, "\u0074", "အ");
// unistr = Regex.Replace(unistr, "\u00C3", "ဣ");
// unistr = Regex.Replace(unistr, "T", "ဤ");
// unistr = Regex.Replace(unistr, "O", "ဥ");
// unistr = Regex.Replace(unistr, "{", "ဧ");
#endregion
#region Depandent Vowel
// unistr = Regex.Replace(unistr, "g", "ါ");
// unistr = Regex.Replace(unistr, ";", "ါ်");
// unistr = Regex.Replace(unistr, "m", "ာ");
// unistr = Regex.Replace(unistr, "d", "ိ");
// unistr = Regex.Replace(unistr, "D", "ီ");
// unistr = Regex.Replace(unistr, "k", "ု");
// unistr = Regex.Replace(unistr, "K", "ု");
// unistr = Regex.Replace(unistr, "l", "ူ");
// unistr = Regex.Replace(unistr, "L", "ူ");
// unistr = Regex.Replace(unistr, "a", "ေ");
// unistr = Regex.Replace(unistr, "J", "ဲ");
// unistr = Regex.Replace(unistr, "H", "ံ");
#endregion
# region Asat
// unistr = Regex.Replace(unistr, "f", "်");
# endregion
# region Tone Marks
// unistr = Regex.Replace(unistr, "[\u0068\u00D0\u00F0]", "့"); // Aukmyit
// unistr = Regex.Replace(unistr, ":", "း");//Visarga
# endregion
# region Digits
// unistr = Regex.Replace(unistr, "0", "၀");
// unistr = Regex.Replace(unistr, "1", "၁");
// unistr = Regex.Replace(unistr, "2", "၂");
// unistr = Regex.Replace(unistr, "3", "၃");
// unistr = Regex.Replace(unistr, "4", "၄");
// unistr = Regex.Replace(unistr, "5", "၅");
// unistr = Regex.Replace(unistr, "6", "၆");
// unistr = Regex.Replace(unistr, "7", "၇");
// unistr = Regex.Replace(unistr, "8", "၈");
// unistr = Regex.Replace(unistr, "9", "၉");
# endregion
# region Punctuation
// unistr = Regex.Replace(unistr, "[\u003F]", "၊");
// unistr = Regex.Replace(unistr, "\u0060", "။");
# endregion
# region Various Signs
// unistr = Regex.Replace(unistr, "Y", "၌");
// unistr = Regex.Replace(unistr, "I", "၍");
// unistr = Regex.Replace(unistr, "&", "၎");
// unistr = Regex.Replace(unistr, ">", "၏");
# endregion
# region Consonant Letter Great SA
// unistr = Regex.Replace(unistr, "\u00F9", "ဿ");
# endregion
# region combine characters
// unistr = Regex.Replace(unistr, "Ö", "ဏ္ဍ");
// unistr = Regex.Replace(unistr, "›", "ဏ္ဎ");
// unistr = Regex.Replace(unistr, "Ø", "ဍ္ဍ");
// unistr = Regex.Replace(unistr, "×", "ဍ္ဎ");
// unistr = Regex.Replace(unistr, "É", "ဏ္ဌ");
// unistr = Regex.Replace(unistr, " Ó", "ဏ္ဏ");
// unistr = Regex.Replace(unistr, "Å", "ဏ္ဋ");
// unistr = Regex.Replace(unistr, "\u00F7", "ဋ္ဌ");
// unistr = Regex.Replace(unistr, "Õ", "ဋ္ဋ");
# endregion
# region General
// unistr = Regex.Replace(unistr, "\u00DC", "“");
// unistr = Regex.Replace(unistr, "\u00C1", "”");
// unistr = Regex.Replace(unistr, "¼", "\u00BC");
# endregion
#endregion
# region reordering process
private void btnOrderGoods_Click(object sender, System.EventArgs e)
{
string text = "";
if (!string.IsNullOrEmpty(base.Request["CheckBoxGroup"]))
{
text = base.Request["CheckBoxGroup"];
}
if (text.Length <= 0)
{
this.ShowMsg("请选要下载配货表的订单", false);
return;
}
System.Collections.Generic.List <string> list = new System.Collections.Generic.List <string>();
string[] array = text.Split(new char[]
{
','
});
for (int i = 0; i < array.Length; i++)
{
string str = array[i];
list.Add("'" + str + "'");
}
System.Data.DataSet orderGoods = OrderHelper.GetOrderGoods(string.Join(",", list.ToArray()));
System.Text.StringBuilder stringBuilder = new System.Text.StringBuilder();
stringBuilder.AppendLine("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=gb2312\"></head><body>");
stringBuilder.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");
stringBuilder.AppendLine("<caption style='text-align:center;'>配货单(仓库拣货表)</caption>");
stringBuilder.AppendLine("<tr style=\"font-weight: bold; white-space: nowrap;\">");
stringBuilder.AppendLine("<td>订单单号</td>");
stringBuilder.AppendLine("<td>商品名称</td>");
stringBuilder.AppendLine("<td>货号</td>");
stringBuilder.AppendLine("<td>规格</td>");
stringBuilder.AppendLine("<td>拣货数量</td>");
stringBuilder.AppendLine("<td>现库存数</td>");
stringBuilder.AppendLine("<td>备注</td>");
stringBuilder.AppendLine("</tr>");
foreach (System.Data.DataRow dataRow in orderGoods.Tables[0].Rows)
{
stringBuilder.AppendLine("<tr>");
stringBuilder.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + dataRow["OrderId"] + "</td>");
stringBuilder.AppendLine("<td>" + dataRow["ProductName"] + "</td>");
stringBuilder.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + dataRow["SKU"] + "</td>");
stringBuilder.AppendLine("<td>" + dataRow["SKUContent"] + "</td>");
stringBuilder.AppendLine("<td>" + dataRow["ShipmentQuantity"] + "</td>");
stringBuilder.AppendLine("<td>" + dataRow["Stock"] + "</td>");
stringBuilder.AppendLine("<td>" + dataRow["Remark"] + "</td>");
stringBuilder.AppendLine("</tr>");
}
stringBuilder.AppendLine("</table>");
stringBuilder.AppendLine("</body></html>");
base.Response.Clear();
base.Response.Buffer = false;
base.Response.Charset = "GB2312";
base.Response.AppendHeader("Content-Disposition", "attachment;filename=ordergoods_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
base.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
base.Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
base.Response.Write(stringBuilder.ToString());
base.Response.End();
}
/// <summary>
/// Returns all features with the view box
/// </summary>
/// <param name="bbox">view box</param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public override void ExecuteIntersectionQuery(Envelope bbox, FeatureDataSet ds)
{
//List<Geometry> features = new List<Geometry>();
using (var conn = new SqlConnection(ConnectionString))
{
//Get bounding box string
string strBbox = GetBoxFilterStr(bbox);
//string strSQL = "SELECT g.*, g." + GeometryColumn + ".STAsBinary() AS sharpmap_tempgeometry ";
string strSQL = String.Format(
"SELECT g.*, g.{0}{1}.STAsBinary() AS sharpmap_tempgeometry FROM {2} g {3} WHERE ",
GeometryColumn, MakeValidString, Table, BuildTableHints());
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSQL += DefinitionQuery + " AND ";
}
strSQL += strBbox;
string extraOptions = GetExtraOptions();
if (!string.IsNullOrEmpty(extraOptions))
{
strSQL += " " + extraOptions;
}
using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn))
{
conn.Open();
System.Data.DataSet ds2 = new System.Data.DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
{
FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"], Factory);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Load or reloads a subset of the chosen resultset returned by the stored procedure.
/// You can specify which record you want to be checked by default.
/// </summary>
/// <param name="ArrayOf_PrimaryKeys">Primary keys of the records you want to be checked by default.</param>
/// <param name="startRecord">The zero-based record number to start with.</param>
/// <param name="maxRecords">The maximum number of records to retrieve.</param>
public void RefreshData(object[] ArrayOf_PrimaryKeys, int startRecord, int maxRecords)
{
this.CreateControl();
if (this.LastKnownConnectionType == Bob.DataClasses.ConnectionType.None)
{
throw new InvalidOperationException("You must call the 'Initialize' method before calling this method.");
}
this.param = new Params.spS_JobPartType();
switch (this.LastKnownConnectionType)
{
case Bob.DataClasses.ConnectionType.ConnectionString:
this.param.SetUpConnection(this.connectionString);
break;
case Bob.DataClasses.ConnectionType.SqlConnection:
this.param.SetUpConnection(this.sqlConnection);
break;
}
this.param.CommandTimeOut = this.commandTimeOut;
if (!this.param_JobPartTypeId.IsNull)
{
this.param.Param_JobPartTypeId = this.param_JobPartTypeId;
}
System.Data.DataSet DS = null;
SPs.spS_JobPartType SP = new SPs.spS_JobPartType();
if (SP.Execute(ref this.param, ref DS, startRecord, maxRecords))
{
this.BeginUpdate();
this.bindingInProgress = true;
this.DataSource = DS.Tables[this.tableName].DefaultView;
this.ValueMember = this.valueMember;
this.DisplayMember = this.displayMember;
this.bindingInProgress = false;
if (ArrayOf_PrimaryKeys != null && ArrayOf_PrimaryKeys.Length > 0)
{
this.SetRecordsCheckState(ArrayOf_PrimaryKeys, System.Windows.Forms.CheckState.Checked);
}
else
{
base.OnSelectedIndexChanged(EventArgs.Empty);
}
this.EndUpdate();
SP.Dispose();
}
else
{
SP.Dispose();
throw new Bob.DataClasses.CustomException(this.param, "WinCheckedListBoxCustom_spS_JobPartType", "RefreshData");
}
}
///*******************************************************************************
///NOMBRE DE LA FUNCIÓN: Consultar_Ubicaciones
///DESCRIPCIÓN: Consulta las Ubicaciones
///PARAMENTROS:
/// 1. P_Ubicacion. Instancia de la Clase de Negocio de Ubicaciones
/// con los datos que servirán de
/// filtro.
///CREO: Miguel Angel Bedolla Moreno.
///FECHA_CREO: 13/Abr/2013 11:30:00 a.m.
///MODIFICO:
///FECHA_MODIFICO:
///CAUSA_MODIFICACIÓN:
///*******************************************************************************
public static System.Data.DataTable Consultar_Ubicaciones(Cls_Cat_Ubicaciones_Negocio P_Ubicacion)
{
System.Data.DataTable Tabla = new System.Data.DataTable();
StringBuilder Mi_SQL = new StringBuilder();
String Aux_Filtros = "";
Conexion.Iniciar_Helper();
Conexion.HelperGenerico.Conexion_y_Apertura();
try
{
Mi_SQL.Append("SELECT " + Cat_Ubicaciones.Campo_Ubicacion_Id
+ ", " + Cat_Ubicaciones.Campo_Ubicacion
+ ", " + Cat_Ubicaciones.Campo_Descripcion_Ubicacion
+ ", " + Cat_Ubicaciones.Campo_Cont_Minimos
+ ", " + Cat_Ubicaciones.Campo_Estatus
+ ", " + Cat_Ubicaciones.Campo_Clasificacion
+ ", " + Cat_Ubicaciones.Campo_Fecha_Creo
+ ", " + Cat_Ubicaciones.Campo_Usuario_Creo
+ ", " + Cat_Ubicaciones.Campo_Fecha_Modifico
+ ", " + Cat_Ubicaciones.Campo_Usuario_Modifico
+ " FROM " + Cat_Ubicaciones.Tabla_Cat_Ubicaciones
+ " WHERE ");
if (P_Ubicacion.P_Ubicacion != null && P_Ubicacion.P_Ubicacion.Trim() != "")
{
Mi_SQL.Append(Cat_Ubicaciones.Campo_Ubicacion + " LIKE '%" + P_Ubicacion.P_Ubicacion + "%' AND ");
}
if (P_Ubicacion.P_Descripcion_Ubicacion != null && P_Ubicacion.P_Descripcion_Ubicacion.Trim() != "")
{
Mi_SQL.Append(Cat_Ubicaciones.Campo_Descripcion_Ubicacion + " LIKE '%" + P_Ubicacion.P_Descripcion_Ubicacion + "%' AND ");
}
if (P_Ubicacion.P_Estatus != null && P_Ubicacion.P_Estatus.Trim() != "")
{
Mi_SQL.Append(Cat_Ubicaciones.Campo_Estatus + P_Ubicacion.P_Estatus + " AND ");
}
if (P_Ubicacion.P_Clasificacion != null && P_Ubicacion.P_Clasificacion.Trim() != "")
{
Mi_SQL.Append("(" + Cat_Ubicaciones.Campo_Clasificacion + P_Ubicacion.P_Clasificacion + ") AND ");
}
if (P_Ubicacion.P_Ubicacion_Id != null && P_Ubicacion.P_Ubicacion_Id.Trim() != "")
{
Mi_SQL.Append(Cat_Ubicaciones.Campo_Ubicacion_Id + " = '" + P_Ubicacion.P_Ubicacion_Id + "' AND ");
}
if (Mi_SQL.ToString().EndsWith(" AND "))
{
Aux_Filtros = Mi_SQL.ToString().Substring(0, Mi_SQL.Length - 5);
Mi_SQL.Length = 0;
Mi_SQL.Append(Aux_Filtros);
}
if (Mi_SQL.ToString().EndsWith(" WHERE "))
{
Aux_Filtros = Mi_SQL.ToString().Substring(0, Mi_SQL.Length - 7);
Mi_SQL.Length = 0;
Mi_SQL.Append(Aux_Filtros);
}
// agregar filtro y orden a la consulta
System.Data.DataSet dataset = Conexion.HelperGenerico.Obtener_Data_Set(Mi_SQL.ToString());
if (dataset != null)
{
Tabla = dataset.Tables[0];
}
}
catch (Exception Ex)
{
String Mensaje = "Error al intentar consultar las ubicaciones. Error: [" + Ex.Message + "]."; //"Error general en la base de datos"
throw new Exception(Mensaje);
}
finally
{
Conexion.HelperGenerico.Cerrar_Conexion();
}
return(Tabla);
}
private void PrintReceipt(System.Data.DataSet allSoldItems)
{
PosPrinter printer = GetReceiptPrinter();
try
{
ConnectToPrinter(printer);
string[] splitDetails = null;
var thisUserName = User.Identity.Name;
try
{
var shopDetails = ConfigurationManager.AppSettings["SHOPDETAILS"].ToString();
splitDetails = shopDetails.Split('@');
if (splitDetails.Length != 4)
{
splitDetails = null;
}
}
catch
{
}
if (splitDetails != null)
{
PrintReceiptHeader(printer, splitDetails[0].Trim(), splitDetails[1].Trim(), splitDetails[2].Trim(), splitDetails[3].Trim(), DateTime.Now, thisUserName);
}
else
{
PrintReceiptHeader(printer, "ABCDEF Pte. Ltd.", "123 My Street, My City,", "My State, My Country", "012-3456789", DateTime.Now, thisUserName);
}
int count = allSoldItems.Tables[0].Rows.Count;
double total = 0;
for (int i = 0; i < count; i++)
{
//SI.STOCKITEMNAME, SIA.Qty, SIA.TOTALPRICE
var description = allSoldItems.Tables[0].Rows[i][0].ToString();
var qty = int.Parse(allSoldItems.Tables[0].Rows[i][1].ToString());
var totalPrice = double.Parse(allSoldItems.Tables[0].Rows[i][2].ToString());
total += totalPrice;
var unitPrice = totalPrice / qty;
PrintLineItem(printer, description, qty, unitPrice);
}
//PrintLineItem(printer, "Item 1", 10, 99.99);
//PrintLineItem(printer, "Item 2", 101, 0.00);
//PrintLineItem(printer, "Item 3", 9, 0.1);
//PrintLineItem(printer, "Item 4", 1000, 1);
PrintReceiptFooter(printer, total, 0, 0, "YOUR SHIFT HAS NOW BEEN CLOSED.");
}
finally
{
DisconnectFromPrinter(printer);
}
}
public override void AddReportSpecificTables(System.Data.DataSet dataSet)
{
SoilMoistureNamespace.ReportSpecificTableBuilder.AddReportSpecificTables(dataSet);
}
/// <summary>
/// Load or reloads a subset of the chosen resultset returned by the stored procedure.
/// You can specify which record you want to be checked by default.
/// </summary>
/// <param name="PrimaryKey">Primary key of the record you want to be selected by default.</param>
/// <param name="startRecord">The zero-based record number to start with.</param>
/// <param name="maxRecords">The maximum number of records to retrieve.</param>
public void RefreshData(object PrimaryKey, int startRecord, int maxRecords)
{
this.CreateControl();
if (this.LastKnownConnectionType == OlymarsDemo.DataClasses.ConnectionType.None)
{
throw new InvalidOperationException("You must call the 'Initialize' method before calling this method.");
}
this.param = new Params.spS_xReadOrderItems();
switch (this.LastKnownConnectionType)
{
case OlymarsDemo.DataClasses.ConnectionType.ConnectionString:
this.param.SetUpConnection(this.connectionString);
break;
case OlymarsDemo.DataClasses.ConnectionType.SqlConnection:
this.param.SetUpConnection(this.sqlConnection);
break;
}
this.param.CommandTimeOut = this.commandTimeOut;
if (!this.param_OrderID.IsNull)
{
this.param.Param_OrderID = this.param_OrderID;
}
System.Data.DataSet DS = null;
SPs.spS_xReadOrderItems SP = new SPs.spS_xReadOrderItems();
if (SP.Execute(ref this.param, ref DS, startRecord, maxRecords))
{
this.BeginUpdate();
this.bindingInProgress = true;
this.DataSource = DS.Tables[this.tableName].DefaultView;
this.ValueMember = this.valueMember;
this.DisplayMember = this.displayMember;
this.bindingInProgress = false;
if (PrimaryKey != null)
{
this.SelectedValue = PrimaryKey;
}
else
{
base.OnSelectedIndexChanged(EventArgs.Empty);
}
this.EndUpdate();
SP.Dispose();
}
else
{
SP.Dispose();
throw new OlymarsDemo.DataClasses.CustomException(this.param, "WinComboBoxCustom_spS_xReadOrderItems", "RefreshData");
}
}
public MemoryStream Export(string excelFileName,
string[] excelWorksheetName,
string tableStyle,
System.Data.DataSet ds)
{
Application excel = new Application();
excel.DisplayAlerts = false;
excel.Visible = true;
excel.ScreenUpdating = false;
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Add(Type.Missing);
// Count of data tables provided.
int iterator = ds.Tables.Count;
for (int i = 0; i < iterator; i++)
{
Sheets worksheets = workbook.Sheets;
Worksheet worksheet = (Worksheet)worksheets[i + 1];
worksheet.Name = excelWorksheetName[i];
int rows = ds.Tables[i].Rows.Count;
int columns = ds.Tables[i].Columns.Count;
// Add the +1 to allow room for column headers.
var data = new object[rows + 1, columns];
// Insert column headers.
for (var column = 0; column < columns; column++)
{
data[0, column] = ds.Tables[i].Columns[column].ColumnName;
}
// Insert the provided records.
for (var row = 0; row < rows; row++)
{
for (var column = 0; column < columns; column++)
{
data[row + 1, column] = ds.Tables[i].Rows[row][column];
}
}
// Write this data to the excel worksheet.
Range beginWrite = (Range)worksheet.Cells[1, 1];
Range endWrite = (Range)worksheet.Cells[rows + 1, columns];
Range sheetData = worksheet.Range[beginWrite, endWrite];
sheetData.Value2 = data;
// Additional row, column and table formatting.
worksheet.Select();
sheetData.Worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
sheetData,
System.Type.Missing,
XlYesNoGuess.xlYes,
System.Type.Missing).Name = excelWorksheetName[i];
sheetData.Select();
var cabececera = sheetData.Range[sheetData.Cells[1, 1], sheetData.Cells[1, columns]];
cabececera.Interior.Color = System.Drawing.Color.FromArgb(176, 0, 45);
//sheetData.Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]);
sheetData.Worksheet.ListObjects[excelWorksheetName[i]].TableStyle = tableStyle;
excel.Application.Range["2:2"].Select();
excel.ActiveWindow.FreezePanes = true;
excel.ActiveWindow.DisplayGridlines = false;
excel.Application.Cells.EntireColumn.AutoFit();
excel.Application.Cells.EntireRow.AutoFit();
// Select the first cell in the worksheet.
excel.Application.Range["$A$2"].Select();
}
// Turn off alerts to prevent asking for 'overwrite existing' and 'save changes' messages.
excel.DisplayAlerts = false;
MemoryStream obj_stream = new MemoryStream();
//var tempFile = System.IO.Path.Combine(System.IO.Path.GetTempPath(), Guid.NewGuid() + ".xls");
var tempFile = System.IO.Path.Combine(System.IO.Path.GetTempPath(), excelFileName + ".xls");
workbook.SaveAs(tempFile);
workbook.Close(false, Type.Missing, Type.Missing);
excel.Quit();
obj_stream = new MemoryStream(File.ReadAllBytes(tempFile)); File.Delete(tempFile);
obj_stream.Position = 0;
return(obj_stream);
//string SaveFilePath = string.Format(@"{0}.xls", excelFileName);
//workbook.SaveAs(SaveFilePath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//workbook.Close(false, Type.Missing, Type.Missing);
//excel.Quit();
//return SaveFilePath;
}
protected override void DoCriaDataSet(out System.Data.DataSet dataSet)
{
dataSet = new DSInvestidor();
}