static RoomClient()
{
//string ConnectString = "metadata=res://*/Model2.csdl|res://*/Model2.ssdl|res://*/Model2.msl;provider=System.Data.SqlClient;provider connection string="Server=192.168.2.24,8605;Database=dbroom;User ID=sa;Password=654321;MultipleActiveResultSets=True"";
//System.Data.SqlClient.SqlConnectionStringBuilder sqlConnection = new System.Data.SqlClient.SqlConnectionStringBuilder();
//sqlConnection.DataSource = @"192.168.2.24,8605";
//sqlConnection.ApplicationName = "";
//sqlConnection.InitialCatalog = "dbroom";
//sqlConnection.IntegratedSecurity = true;
//sqlConnection.PersistSecurityInfo = true;
//sqlConnection.UserID = "sa";
//sqlConnection.Password ="******";
//string connectString = "Server=10.21.99.82;Database=SecureDB;User ID=secure;Password=secure;";
string connectString = "data source=10.21.99.80;initial catalog=SecureDB;persist security info=True;user id=secure;password=secure;MultipleActiveResultSets=True;App=EntityFramework;";
string dir = System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName;
dir = dir.Remove(dir.LastIndexOf('\\'));
if (System.IO.File.Exists(dir +@"\Server.txt"))
{
string serverSet = System.IO.File.ReadLines(dir + @".\Server.txt").First();
if (serverSet.Length < 300)
{
connectString = serverSet;
}
}
//System.Data.SqlClient.SqlConnection s = new System.Data.SqlClient.SqlConnection(sqlConnection.ConnectionString);
//System.Data.EntityClient.EntityConnectionStringBuilder ecsb = new System.Data.EntityClient.EntityConnectionStringBuilder();
//ecsb.Provider = "System.Data.SqlClient";
//ecsb.ProviderConnectionString = sqlConnection.ConnectionString;
//ecsb.Metadata = @"res://*/Model2.csdl|res://*/Model2.ssdl|res://*/Model2.msl";
//System.Data.EntityClient.EntityConnection ec = new System.Data.EntityClient.EntityConnection(ecsb.ConnectionString);
//dbroomClientEntities dbroom = new dbroomClientEntities(ec);
try
{
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter("Select * from tblHostConfig;", connectString);
System.Data.DataTable DT = new System.Data.DataTable();
adapter.Fill(DT);
adapter.Dispose();
objUrl = "tcp://" + DT.Rows[0]["IP"] + ":" + DT.Rows[0]["Port"] + "/RoomObj";
System.Runtime.Remoting.Channels.Tcp.TcpChannel tcp = new System.Runtime.Remoting.Channels.Tcp.TcpChannel(0);
System.Runtime.Remoting.Channels.ChannelServices.RegisterChannel(tcp, false);
//var HostData = (from o in dbroom.tblHostConfigs select o).First();
//objUrl = "tcp://" + HostData.IP + ":" + HostData.Port + "/RoomObj";
}
catch (Exception )
{
throw new Exception("資料庫讀取失敗");
}
roomEvent.RoomEvent += new RoomEventHandler(RoomClient_RoomEvent);
timer.Elapsed += new System.Timers.ElapsedEventHandler(timer_Elapsed);
timer.Interval = 10000;
timer.Start();
}
/// <summary>
/// Run a transact SQL query against the database.
/// </summary>
/// <param name="sqlQuery">String with the SQL query (Select, etc).</param>
/// <returns>The DataTable with the result set. Returns a New, empty DataTable if there are any exceptions.</returns>
public System.Data.DataTable GetDataTable(string sqlQuery)
{
// Do not proceed if the database is not connected.
if (this.IsConnected == false)
{
this.hasException = true;
this.lastException = new System.Exception("You cannot query a database until you connect to it (ExecuteQuery(string). Connect first.");
if (this.ThrowExceptions == true) throw this.lastException;
return new System.Data.DataTable();
}
// Clear past exceptions
this.hasException = false;
/*
* Switch to the appropriate database client and execute the query
*
* Create an adapter to run the SQL query
* It was found some WFS Queries exceeded the default time out, so we bump up the command timeout
* Fill the DataTable with the results of the SQL query
* Dispose of the Adapter
* Send the Adapter to Nothing
* Return the DataTable
*/
System.Data.DataTable output = new System.Data.DataTable();
switch (this.DataClient)
{
case DatabaseClient.OleClient:
try
{
System.Data.OleDb.OleDbDataAdapter oleAdapter = new System.Data.OleDb.OleDbDataAdapter(sqlQuery, this.oleConn);
oleAdapter.SelectCommand.CommandTimeout = this.CommandTimeout;
oleAdapter.Fill(output);
oleAdapter.Dispose();
return output;
}
catch (Exception exception)
{
this.hasException = true;
this.lastException = exception;
if (this.ThrowExceptions == true) throw this.lastException;
}
break;
case DatabaseClient.SqlClient:
try
{
System.Data.SqlClient.SqlDataAdapter sqlAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlQuery, this.sqlConn);
sqlAdapter.SelectCommand.CommandTimeout = this.CommandTimeout;
sqlAdapter.Fill(output);
sqlAdapter.Dispose();
return output;
}
catch (Exception exception)
{
this.hasException = true;
this.lastException = exception;
if (this.ThrowExceptions == true) throw this.lastException;
}
break;
default:
this.hasException = true;
this.lastException = new SystemException("The database client type entered is invalid (DataClient=" + this.DataClient.ToString() + ").");
if (this.ThrowExceptions == true) throw this.lastException;
break;
}
return new System.Data.DataTable();
}
//TODO not sure where you are trying to connect on this one. :)
/* neither am I comes out of the example in 'Professional Android Programming with Mono for Android and .NET#3aC#'
* I don't really understand this bit.
*/
void DataBaseButton_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection sqlCn = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand sqlCm = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataAdapter sqlDa = new System.Data.SqlClient.SqlDataAdapter();
DataTable dt = new DataTable();
string strSql = "select * from Session";
string strCn = "Server=mobiledev.scalabledevelopment.com;Database=AnDevConTest;User ID=AnDevCon;Password=AnDevConPWD;Network Library=DBMSSOCN";
sqlCn.ConnectionString = strCn;
sqlCm.CommandText = strSql;
sqlCm.CommandType = CommandType.Text;
sqlCm.Connection = sqlCn;
sqlDa.SelectCommand = sqlCm;
try
{
sqlDa.Fill(dt);
tv.Text = "Records returned: " + dt.Rows.Count.ToString();
}
catch (System.Exception sysExc)
{
Console.WriteLine("Exc: " + sysExc.Message);
tv.Text = "Exc: " + sysExc.Message;
}
finally
{
if (sqlCn.State != ConnectionState.Closed)
{
sqlCn.Close();
}
sqlCn.Dispose();
sqlCm.Dispose();
sqlDa.Dispose();
sqlCn = null;
sqlCm = null;
sqlDa = null;
}
}
private void getMenu()
{
System.Data.SqlClient.SqlCommand _lcJetCmd = null;
//System.Data.SqlClient.SqlDataReader _lcJetDReader = null;
System.Data.DataSet _lcDataSet = null;
System.Data.SqlClient.SqlDataAdapter _lcDataAdatapter = null;
System.Windows.Forms.MenuItem _lcMenuItem = new MenuItem();
byte menukey = 0;
byte intMenuKey = 0;
bool _lcIsPrimeraVes = true;
try
{
if (Program._glbControlQuality2012Config.State == ConnectionState.Closed) { Program._glbControlQuality2012Config.Open(); }
_lcJetCmd = new System.Data.SqlClient.SqlCommand();
_lcDataAdatapter = new System.Data.SqlClient.SqlDataAdapter();
_lcDataSet = new DataSet();
_lcJetCmd.Connection = Program._glbControlQuality2012Config;
_lcJetCmd.CommandType = CommandType.Text;
_lcJetCmd.CommandText = "SELECT A.*,B.* FROM tblMenu A INNER JOIN tblMenuItems B ON A.menuKey = B.MenuKey order by A.menuKey;";
_lcDataAdatapter.SelectCommand = _lcJetCmd;
_lcDataAdatapter.Fill(_lcDataSet);
foreach (System.Data.DataRow _lcJetDReader in _lcDataSet.Tables[0].Rows)
{
intMenuKey = Convert.ToByte(_lcJetDReader["menukey"].ToString());
if (menukey != Convert.ToByte(intMenuKey) || _lcIsPrimeraVes)
{
writeMenu(
_lcJetDReader["Label"].ToString(),
Convert.ToByte(_lcJetDReader["menukey"].ToString()),
_lcJetDReader["Label1"].ToString(),
ref _lcMenuItem);
_lcIsPrimeraVes = false;
}
writeMenuItems(
_lcJetDReader["Label1"].ToString(),
Convert.ToByte(_lcJetDReader["menukey"].ToString()),
ref _lcMenuItem);
menukey = Convert.ToByte(_lcJetDReader["menukey"].ToString());
}//end For
_lcDataAdatapter.Dispose();
_lcJetCmd.Dispose();
}
catch (Exception ex)
{
throw new Exception(ex.ToString() + "---Hay Problema con el archivo de configuracion de la aplicacion---");
}
finally
{
if (Program._glbControlQuality2012Config.State == ConnectionState.Open)
{
Program._glbControlQuality2012Config.Close();
}
_lcJetCmd = null;
_lcDataAdatapter = null;
}
}
// Thanks to Kevin Trickey for providing the C# implementation of this method!
protected static DataSet SprocGetToken(string token)
{
// create a connection...
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(EnterpriseApplication.Application.ConnectionString);
connection.Open();
// create a command...
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("GetToken", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
// parameters...
System.Data.SqlClient.SqlParameter tokenParam = command.Parameters.Add("@token", System.Data.SqlDbType.VarChar, 256);
tokenParam.Value = token;
// extract the dataset...
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(command);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
adapter.Dispose();
// cleanup...
command.Dispose();
connection.Close();
// return dataset...
return dataset;
}
/// <summary>
/// ִ��SQL��䲢����DateTable
/// </summary>
/// <param name="conn">���Ӷ���</param>
/// <param name="sqlString">SQL���</param>
/// <returns>System.Data.DataTable</returns>
public System.Data.DataTable execSqlReturnDataTable(System.Data.SqlClient.SqlConnection conn ,
string sqlString)
{
try
{
if (conn.State == System.Data.ConnectionState.Closed )
{
conn.Open();
}
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqlString , conn);
cmd.CommandTimeout = 36000 ;
System.Data.SqlClient.SqlDataAdapter dap = new System.Data.SqlClient.SqlDataAdapter(cmd);
System.Data.DataTable dt = new System.Data.DataTable();
dap.Fill(dt);
cmd.Dispose();
conn.Close();
dap.Dispose();
conn.Close();
return dt;
}
catch//(Exception exp)
{
//System.Windows.Forms.MessageBox.Show(exp.Message);
return new System.Data.DataTable();
}
}
private DateTime GetPublicationsServerDAtetime()
{
DateTime dtime = default(DateTime);
bool dateObtained = false;
try
{
using (System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(this._dataBaseSonnectionString))
{
cnn.Open();
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = cnn;
using (System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
//selects a datetime as to use as reference to retrieve data
while (!dateObtained)
{
try
{
//using the server current datetime
cmd.CommandText = "Select getdate()";
da.Fill(dt);
if (dt.Rows.Count > 0)
{
dtime = System.Convert.ToDateTime(dt.Rows[0][0]);
dateObtained = true;
}
}
catch (Exception)
{
//if retrieving the server datetime fails then tries to use the las datetime
//of the current data on the table
try
{
cmd.CommandText = "select max([datetime]) from " + this._tablename;
dtime = System.Convert.ToDateTime(cmd.ExecuteScalar());
dateObtained = true;
}
catch (Exception)
{
}
}
}
try
{
dt.Dispose();
}
catch (Exception)
{
}
} //datatable
try
{
da.Dispose();
}
catch (Exception)
{
}
} //SqlDataAdapter
} //SqlCommand
try
{
cnn.Close();
cnn.Dispose();
}
catch (Exception)
{
}
} //SqlConnection
}
catch (Exception ex)
{
CustomEventLog.DisplayEvent(EventLogEntryType.Error, ex.ToString());
}
return(dtime);
}
/// <summary>
/// Run a transact SQL query against the database.
/// </summary>
/// <param name="sqlQuery">String with the SQL query (Select, etc).</param>
/// <returns>The DataTable with the result set. Returns a New, empty DataTable if there are any exceptions.</returns>
public System.Data.DataTable GetDataTable(string sqlQuery)
{
// Do not proceed if the database is not connected.
if (this.IsConnected == false)
{
this.hasException = true;
this.lastException = new System.Exception("You cannot query a database until you connect to it (ExecuteQuery(string). Connect first.");
if (this.ThrowExceptions == true)
{
throw this.lastException;
}
return(new System.Data.DataTable());
}
// Clear past exceptions
this.hasException = false;
/*
* Switch to the appropriate database client and execute the query
*
* Create an adapter to run the SQL query
* It was found some WFS Queries exceeded the default time out, so we bump up the command timeout
* Fill the DataTable with the results of the SQL query
* Dispose of the Adapter
* Send the Adapter to Nothing
* Return the DataTable
*/
System.Data.DataTable output = new System.Data.DataTable();
switch (this.DataClient)
{
case DatabaseClient.OleClient:
try
{
System.Data.OleDb.OleDbDataAdapter oleAdapter = new System.Data.OleDb.OleDbDataAdapter(sqlQuery, this.oleConn);
oleAdapter.SelectCommand.CommandTimeout = this.CommandTimeout;
oleAdapter.Fill(output);
oleAdapter.Dispose();
return(output);
}
catch (Exception exception)
{
this.hasException = true;
this.lastException = exception;
if (this.ThrowExceptions == true)
{
throw this.lastException;
}
}
break;
case DatabaseClient.SqlClient:
try
{
System.Data.SqlClient.SqlDataAdapter sqlAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlQuery, this.sqlConn);
sqlAdapter.SelectCommand.CommandTimeout = this.CommandTimeout;
sqlAdapter.Fill(output);
sqlAdapter.Dispose();
return(output);
}
catch (Exception exception)
{
this.hasException = true;
this.lastException = exception;
if (this.ThrowExceptions == true)
{
throw this.lastException;
}
}
break;
default:
this.hasException = true;
this.lastException = new SystemException("The database client type entered is invalid (DataClient=" + this.DataClient.ToString() + ").");
if (this.ThrowExceptions == true)
{
throw this.lastException;
}
break;
}
return(new System.Data.DataTable());
}
void ReloadTime_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
DateTime startLoad = DateTime.Now;
System.Xml.XmlDocument mv_schema_xml_temp = new System.Xml.XmlDocument();
System.Xml.XmlDocument import_attribute_flow_xml_temp = new System.Xml.XmlDocument();
System.Data.DataTable mms_management_agent_temp = new System.Data.DataTable("mms_metaverse_multivalue");
System.Data.DataTable mms_metaverse_temp = new System.Data.DataTable("mms_metaverse");
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["FIMSynchronizationService"]);
connection.Open();
//Load XML schema
//mv_schema_xml
System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand("select mv_schema_xml from mms_server_configuration (nolock)", connection);
System.Xml.XmlReader XmlReader = SqlCommand.ExecuteXmlReader();
mv_schema_xml_temp.Load(XmlReader);
XmlReader.Dispose();
SqlCommand.Dispose();
//import_attribute_flow_xml
SqlCommand = new System.Data.SqlClient.SqlCommand("select import_attribute_flow_xml from mms_server_configuration (nolock)", connection);
XmlReader = SqlCommand.ExecuteXmlReader();
import_attribute_flow_xml_temp.Load(XmlReader);
XmlReader.Dispose();
SqlCommand.Dispose();
//
////Load Tabell schema
//SqlCommand = new System.Data.SqlClient.SqlCommand("select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'mms_metaverse'", connection);
//System.Data.DataTable INFORMATION_SCHEMA = new System.Data.DataTable("INFORMATION_SCHEMA");
//System.Data.SqlClient.SqlDataAdapter DataAdapter = new System.Data.SqlClient.SqlDataAdapter(SqlCommand);
//int count = DataAdapter.Fill(INFORMATION_SCHEMA);
//DataAdapter.Dispose();
//SqlCommand.Dispose();
System.Data.SqlClient.SqlDataAdapter DataAdapter;
int count;
//Load mms_metaverse attributes
SqlCommand = new System.Data.SqlClient.SqlCommand("select top 1 * from mms_metaverse (nolock)", connection);
DataAdapter = new System.Data.SqlClient.SqlDataAdapter(SqlCommand);
count = DataAdapter.Fill(mms_metaverse_temp);
DataAdapter.Dispose();
SqlCommand.Dispose();
//Load mms_management_agent
SqlCommand = new System.Data.SqlClient.SqlCommand("select * from mms_management_agent (nolock)", connection);
DataAdapter = new System.Data.SqlClient.SqlDataAdapter(SqlCommand);
count = DataAdapter.Fill(mms_management_agent_temp);
DataAdapter.Dispose();
SqlCommand.Dispose();
connection.Close();
connection.Dispose();
mv_schema_xml = mv_schema_xml_temp;
import_attribute_flow_xml = import_attribute_flow_xml_temp;
//mms_management_agent
Dictionary <Guid, LD.IdentityManagement.API.Models.managementagent> Schema_managementagent_temp = new Dictionary <Guid, LD.IdentityManagement.API.Models.managementagent>();
foreach (DataRow row in mms_management_agent_temp.Rows)
{
LD.IdentityManagement.API.Models.managementagent ma = new LD.IdentityManagement.API.Models.managementagent();
ma.name = (string)row["ma_name"];
ma.guid = (Guid)row["ma_id"];
ma.description = (string)row["ma_description"];
ma.type = (string)row["ma_type"];
Schema_managementagent_temp.Add(ma.guid, ma);
//import-flow
foreach (System.Xml.XmlNodeList flowrule in import_attribute_flow_xml.SelectNodes(string.Format("/import-attribute-flow/import-flow-set/import-flows/import-flow[@src-ma='{0}']", ma.guid)))
{
flowObject temp = new flowObject()
{
type = "",
mvclassobjectname = "",
csclassobjectname = "",
attribute = new flowAttribute[0]
};
}
}
Schema_managementagent = Schema_managementagent_temp;
System.Xml.XmlNamespaceManager ns = new System.Xml.XmlNamespaceManager(mv_schema_xml.NameTable);
ns.AddNamespace("dsml", "http://www.dsml.org/DSML");
//Create Schema Attributes list
Dictionary <string, LD.IdentityManagement.API.Models.Attribute> Schema_Attributes_temp = new Dictionary <string, LD.IdentityManagement.API.Models.Attribute>();
Dictionary <string, LD.IdentityManagement.API.Models.Attribute> Schema_Private_Attributes_temp = new Dictionary <string, LD.IdentityManagement.API.Models.Attribute>();
foreach (System.Xml.XmlNode Node in mv_schema_xml.SelectNodes("dsml:dsml/dsml:directory-schema/dsml:attribute-type", ns))
{
LD.IdentityManagement.API.Models.Attribute NewAttribute = new LD.IdentityManagement.API.Models.Attribute();
NewAttribute.name = Node["dsml:name"].InnerText.ToLower();
NewAttribute.mulitvalue = Node.Attributes["single-value"] != null ? false : true;
NewAttribute.indexable = Node.Attributes["ms-dsml:indexable"] == null || Node.Attributes["ms-dsml:indexable"].Value == "false" ? false : true;
NewAttribute.indexed = Node.Attributes["ms-dsml:indexed"] == null || Node.Attributes["ms-dsml:indexed"].Value == "false" ? false : true;
NewAttribute.syntax = Node["dsml:syntax"].InnerText;
//syntax to type
switch (NewAttribute.syntax)
{
case "1.3.6.1.4.1.1466.115.121.1.5":
//Binary
NewAttribute.type = typeof(byte[]);
break;
case "1.3.6.1.4.1.1466.115.121.1.7":
//Boolean
NewAttribute.type = typeof(bool);
break;
case "1.3.6.1.4.1.1466.115.121.1.12":
//DN (string)
NewAttribute.type = typeof(string);
break;
case "1.3.6.1.4.1.1466.115.121.1.15":
//DirectoryString
NewAttribute.type = typeof(string);
break;
case "1.3.6.1.4.1.1466.115.121.1.27":
//Integer
NewAttribute.type = typeof(int);
break;
default:
//NewAttribute.type = typeof(string);
break;
}
if (!Schema_private.Contains(NewAttribute.name))
{
if (NewAttribute.mulitvalue || mms_metaverse_temp.Columns.Contains(NewAttribute.name))
{
//logger.Debug("{0} {1}", NewAttribute.name, NewAttribute.mulitvalue);
Schema_Attributes_temp.Add(NewAttribute.name, NewAttribute);
}
}
else
{
Schema_Private_Attributes_temp.Add(NewAttribute.name, NewAttribute);
}
}
Schema_Attributes = Schema_Attributes_temp;
Schema_Private_Attributes = Schema_Private_Attributes_temp;
//Create Schema Object list
Dictionary <string, LD.IdentityManagement.API.Models.classobject> Schema_Object_temp = new Dictionary <string, LD.IdentityManagement.API.Models.classobject>();
foreach (System.Xml.XmlNode Node in mv_schema_xml.SelectNodes("dsml:dsml/dsml:directory-schema/dsml:class", ns))
{
LD.IdentityManagement.API.Models.classobject newClassObject = new LD.IdentityManagement.API.Models.classobject();
newClassObject.name = Node["dsml:name"].InnerText.ToLower();
List <LD.IdentityManagement.API.Models.objectattribute> ObjectAttributeList = new List <LD.IdentityManagement.API.Models.objectattribute>();
foreach (System.Xml.XmlNode attributeNodes in Node.SelectNodes("dsml:attribute", ns))
{
LD.IdentityManagement.API.Models.objectattribute NewObjectAttribute = new LD.IdentityManagement.API.Models.objectattribute();
NewObjectAttribute.required = attributeNodes.Attributes["required"].Value == "true" ? true : false;
LD.IdentityManagement.API.Models.Attribute Attribute;
string attname = attributeNodes.Attributes["ref"].Value.Substring(1);
if (Schema_Attributes.TryGetValue(attname, out Attribute) || Schema_Private_Attributes.TryGetValue(attname, out Attribute))
{
NewObjectAttribute.attribute = Attribute;
ObjectAttributeList.Add(NewObjectAttribute);
}
}
newClassObject.objectattributes = ObjectAttributeList.ToArray();
Schema_Object_temp.Add(newClassObject.name, newClassObject);
}
Schema_Object = Schema_Object_temp;
TimeSpan time = DateTime.Now - startLoad;
ReloadTime.Enabled = true;
}
/// <summary>
/// This method allows you to execute the [spS_xReadOrderItems] stored procedure and retrieve back the data
/// populated in a DataSet (System.Data.DataSet).
/// </summary>
/// <param name="parameters">
/// Contains all the necessary information to execute correctly the stored procedure, i.e.
/// the database connection to use and all the necessary input parameters to be supplied
/// for this stored procedure execution. After the execution, this object will allow you
/// to retrieve back the stored procedure return value and all the output parameters.
/// </param>
/// <param name="dataset">
/// Will be populated with the data after execution.
/// </param>
/// <param name="tableName">
/// Will be the based name of the table in the database.
/// </param>
/// <param name="startRecord">The zero-based record number to start with.</param>
/// <param name="maxRecords">The maximum number of records to retrieve.</param>
/// <returns>True if the call was successful. Otherwise, it returns False.</returns>
public bool Execute(ref OlymarsDemo.DataClasses.Parameters.spS_xReadOrderItems parameters, ref System.Data.DataSet dataset, string tableName, int startRecord, int maxRecords)
{
System.Data.SqlClient.SqlCommand sqlCommand = null;
System.Data.SqlClient.SqlDataAdapter sqlDataAdapter = null;
System.Boolean returnStatus = false;
System.Boolean connectionMustBeClosed = true;
try {
if (dataset == null)
{
dataset = new System.Data.DataSet();
}
ResetParameter(ref parameters);
parameters.internal_SetErrorSource(ErrorSource.ConnectionInitialization);
returnStatus = InitializeConnection(ref parameters, out sqlCommand, ref connectionMustBeClosed);
if (!returnStatus)
{
return(false);
}
parameters.internal_SetErrorSource(ErrorSource.ParametersSetting);
returnStatus = DeclareParameters(ref parameters, ref sqlCommand);
if (!returnStatus)
{
return(false);
}
parameters.internal_SetErrorSource(ErrorSource.QueryExecution);
sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlCommand;
if (startRecord == -1)
{
sqlDataAdapter.Fill(dataset, tableName);
}
else
{
sqlDataAdapter.Fill(dataset, startRecord, maxRecords, tableName);
}
sqlDataAdapter.Dispose();
parameters.internal_SetErrorSource(ErrorSource.ParametersRetrieval);
returnStatus = RetrieveParameters(ref parameters, ref sqlCommand);
}
catch (System.Data.SqlClient.SqlException sqlException) {
dataset = null;
parameters.internal_UpdateExceptionInformation(sqlException);
returnStatus = false;
if (this.throwExceptionOnExecute)
{
throw sqlException;
}
}
catch (System.Exception exception) {
dataset = null;
parameters.internal_UpdateExceptionInformation(exception);
returnStatus = false;
parameters.internal_SetErrorSource(ErrorSource.Other);
if (this.throwExceptionOnExecute)
{
throw exception;
}
}
finally {
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
if (parameters.SqlTransaction == null)
{
if (this.sqlConnection != null && connectionMustBeClosed && this.sqlConnection.State == System.Data.ConnectionState.Open)
{
this.sqlConnection.Close();
this.sqlConnection.Dispose();
}
}
if (returnStatus)
{
parameters.internal_SetErrorSource(ErrorSource.NoError);
}
else
{
if (this.throwExceptionOnExecute)
{
if (parameters.SqlException != null)
{
throw parameters.SqlException;
}
else
{
throw parameters.OtherException;
}
}
}
}
return(returnStatus);
}
public Stream printer(string printerId, string parameters)
{
var request = WebOperationContext.Current.IncomingRequest;
var headers = request.Headers;
var JSONString = string.Empty;
try
{
//var httpToken = headers["Authorization"].Trim().Replace("Bearer ", "");
//using (SecureData sd = new SecureData(false, httpToken))
//{
using (var connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["Oraculus"].ConnectionString))
{
connection.Open();
var dt = new DataTable();
var command = new System.Data.SqlClient.SqlCommand("select PrinterId, rtrim(PrinterName) as PrinterPath, PrinterResolution, rtrim(PrinterMethod) as PrinterMethod from printers where PrinterId = " + int.Parse(printerId), connection);
var da = new System.Data.SqlClient.SqlDataAdapter(command);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
var dtr = dt.Rows[0];
var printerPath = dtr.Field <string>("PrinterPath");
var printerMethod = dtr.Field <string>("PrinterMethod");
if (string.IsNullOrEmpty(parameters))
{
parameters = "^XA\r\n^LH20,20 ^BY2,3\r\n^FO40,10 ^BXN,8,200 ^FD280810^FS\r\n^FO10,5 ^ADR,18^FD280810^FS\r\n^FO175,10 ^ADR,18^FDCOL Number^FS\r\n^FO200,5 ^ADR,18^FDCIP 460630^FS\r\n^LH20,110\r\n^FO130,10 ^ADR,18 ^FDCultvrname ^FS\r\n^FO100,10 ^ADR,18 ^FDCultvrname ^FS\r\n^FO70,10 ^ADR,18 ^FDTypeCrossName^FS\r\n^FO40,10 ^ADR,18 ^FD2017^FS\r\n^FO40,85 ^ADR,18 ^FDSPP ^FS\r\n^XZ";
}
JSONString = JsonConvert.SerializeObject(dt, Formatting.Indented);
if (printerMethod.Equals("Shared"))
{
var FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath("~/ZPLII.txt");
//var directory = System.Web.Hosting.HostingEnvironment.MapPath("~/uploads/logs");
//File.Create(FILE_NAME.Replace("ZPLII","ZPL2"));
File.WriteAllText(FILE_NAME, parameters);
File.Copy(FILE_NAME, printerPath);
}
else if (printerMethod.Equals("IP"))
{
const int port = 9100;
using (System.Net.Sockets.TcpClient client = new System.Net.Sockets.TcpClient())
{
client.Connect(printerPath, port);
using (System.IO.StreamWriter writer = new System.IO.StreamWriter(client.GetStream()))
{
writer.Write(parameters);
}
}
}
}
da.Dispose();
connection.Close();
}
//}
}
catch (Exception e)
{
WebOperationContext.Current.OutgoingResponse.StatusCode = System.Net.HttpStatusCode.InternalServerError; //500
JSONString = JsonConvert.SerializeObject(e.Message);
}
WebOperationContext.Current.OutgoingResponse.ContentType = "application/json; charset=utf-8";
return(new MemoryStream(Encoding.UTF8.GetBytes(JSONString)));
}
private void cmbSqlTables_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
string dataBase = this.cmbSqlDataBases.SelectedItem.ToString();
System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(Program._cnnStringNoDataBase+ dataBase) ;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter();
System.Data.DataSet dataset = new DataSet();
cnn.Open();
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '" + cmbSqlTables.Text + "';";
ad.SelectCommand = cmd;
ad.Fill(dataset);
lsbSqlFild.Items.Clear();
foreach (DataRow R in dataset.Tables[0].Rows)
{
lsbSqlFild.Items.Add(R[0].ToString());
}
dataset.Dispose();
ad.Dispose();
cnn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnGetInfoSql_Click(object sender, EventArgs e)
{
try
{
/*si quieres obtener todas las tablas
* SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
si quieres obtener todas las columnas
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
*/
String dataBase = this.cmbSqlDataBases.SelectedItem.ToString();
System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(Program._cnnStringNoDataBase + dataBase);
cnn.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES", cnn);
System.Data.SqlClient.SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter(cmd);
System.Data.DataSet dtset = new DataSet();
ad.Fill(dtset);
cmbSqlTables.Items.Clear();
foreach (DataRow R in dtset.Tables[0].Rows)
{
cmbSqlTables.Items.Add(R[0].ToString());
}
ad.Dispose();
dtset.Dispose();
cnn.Close();
MessageBox.Show("Done...");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public DataSet TranslateData() //Method to translate the data in the Data2ToTranslate dataset
{
int rownumber;
DataRow[] FoundRows;
string sTempField;
DataSet ds = new DataSet();
try
{
//get tranlation table data from database
string connString = Properties.Settings.Default.ConnectionString;
string query = "select [Data_Column_Name],[Value],[Export_Value] from Export_Values";
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn);
conn.Open();
// create data adapter
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// this will query the database and return the result to your datatable
da.Fill(ds);
conn.Close();
da.Dispose();
TranslationTable = ds.Tables[0]; //set the translation table
ds.Dispose();
}
catch (Exception e)
{
MessageBox.Show("Error loading Export translation table from database. \n" +
"Internet connection or SQL server may be down. \n" +
"Please note what was done prior to error and contact administrator or see error log for more details. \n");
Error_Logging el = new Error_Logging("Error loading Export (Adams) translation table from database. \n" + e);
ds.Dispose();
Completed = false;
return(Data2Translate);
}
//Translate values generic routine *************************************************************************
//Takes the list of fields to evaluate and iterates through each of them.
try
{
foreach (string sField in List2Translate)
{
rownumber = 0;
//This is for the case where the size and style are contatonated into one field in the data streadsheet. ***
if ((TemplateSettings.SizeColumn == TemplateSettings.StyleColumn) & ((sField == "Size") || (sField == "Style")))
{
sTempField = "PackCode";
}
else
{
sTempField = sField;
}
//***
FoundRows = TranslationTable.Select("Data_Column_Name = " + "'" + sTempField + "'"); //get all rows for sField from Translation table
foreach (DataRow row in Data2Translate.Tables[0].Rows)
{
foreach (DataRow TranslatingRow in FoundRows) //FoundRows contains all the translation rows for sField
{
if (row[TemplateSettings.DataColumnLocation(sField).Column].ToString().ToUpper().Trim() == TranslatingRow[1].ToString().ToUpper().Trim())
// && TranslatingRow[2].ToString() != "") //Famous value in column 1
{
//set field to translated value. Export value in column 2
Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.DataColumnLocation(sField).Column] =
TranslatingRow[2].ToString();
}
}
rownumber++;
}
}
Completed = true;
} //end of try block
catch (Exception e)
{
Completed = false;
MessageBox.Show("Export Translation process had an error. Please note what was done and contact administrator for help or see error log for more details. \n");
Error_Logging el = new Error_Logging("Export (Adams) Translation process had an error. \n " + e);
}
return(Data2Translate);
} //end of TranslateData method
public DataRowCollection GetSP_Rows(string ProcedureName)
{
this.cmd.CommandText = ProcedureName;
this.cmd.CommandType = CommandType.StoredProcedure;//指定是存储过程
this.GenParameters();
System.Data.SqlClient.SqlDataAdapter ada = new System.Data.SqlClient.SqlDataAdapter();
ada.SelectCommand = (System.Data.SqlClient.SqlCommand)cmd;
DataTable dt = new DataTable();
ada.Fill(dt);
ada.Dispose();
return dt.Rows;
}