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