/// <summary> /// تست ارتباط /// </summary> /// <param name="ErrorMessage">متن خطا</param> public bool TestConnection(out string ErrorMessage) { System.Data.ConnectionState previousConnectionState = this.Connection.State; try { if (((this.Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open)) { this.Connection.Open(); } } catch (Exception ex) { ErrorMessage = ex.Message; this.Connection.Close(); return(false); } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { this.Connection.Close(); } } ErrorMessage = ""; return(true); }
/// <summary> /// Função que executa uma query e retorna um DataTable /// </summary> /// <param name="strQuery">SELECT SQL</param> /// <param name="strNomeTabela">Nome da tabela a ser retornada</param> /// <param name="msgErro">Mensagem de erro, caso ocorra algum</param> /// <returns>Retorna um DataTable</returns> public DataTable F_ExecutaSQL_Retorna_DataTable(string strQuery, string strNomeTabela, ref string msgErro) { DataTable tAux = new DataTable(strNomeTabela); //Cria tabela com os dados vindos do DB System.Data.ConnectionState EstadoConexao = new System.Data.ConnectionState(); EstadoConexao = F_AbreConexao(ref msgErro); //Chama função que abre a conexão if (EstadoConexao == System.Data.ConnectionState.Open) //Se o estado da conexão for diferente de "Open", não faz o select { try { try { MySqlCommand comando = new MySqlCommand(strQuery, mConn); MySqlDataAdapter adaptador = new MySqlDataAdapter(comando); adaptador.Fill(tAux); } catch (Exception e) { msgErro = e.Message; throw; } } catch (Exception e) { msgErro = e.Message; throw; } } return(tAux); }
public static DataTable ExecuteCmdTable(System.Data.SQLite.SQLiteCommand cmd) { System.Data.ConnectionState origSate = cmd.Connection.State; if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } DataTable dt; System.Data.SQLite.SQLiteDataReader dr; dt = new DataTable(); dr = cmd.ExecuteReader(); dt.Load(dr); dr.Close(); dr = null; if (origSate == ConnectionState.Closed) { cmd.Connection.Close(); } return(dt); }
/// <summary> /// تست ارتباط /// </summary> public bool Test() { bool Result = true; System.Data.ConnectionState previousConnectionState = Connection.State; if (((Connection.State & System.Data.ConnectionState.Open) == System.Data.ConnectionState.Open)) { return(true); } try { Connection.Open(); } catch { Result = false; } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { Connection.Close(); } } return(Result); }
public DataTable GetTable(FbCommand cmd) { System.Data.ConnectionState original = cmd.Connection.State; if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } DataTable dt = new DataTable(); FbDataReader dr; try { dr = cmd.ExecuteReader(); dt.Load(dr); dr.Close(); dr.Dispose(); } catch (Exception ex) { MessageBox.Show("Проверьте корректность введенных данных и фильтров!" + Environment.NewLine + ex.Message, "Ошибка выполнения запроса", MessageBoxButtons.OK, MessageBoxIcon.Error); } if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } return(dt); }
public int execute(FbCommand cmd) { System.Data.ConnectionState original = cmd.Connection.State; if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } int result = -13; try { result = cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Проверьте корректность введенных данных и фильтров!" + Environment.NewLine + ex.Message, "Ошибка выполнения запроса", MessageBoxButtons.OK, MessageBoxIcon.Error); } if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } return(result); }
public void Dispose() { if (_state == System.Data.ConnectionState.Open) { this.Close(); } _state = ConnectionState.Closed; }
/// <summary> /// ویرایش /// </summary> /// <param name="model">یک شی معادل با جدول پایگاه داده</param> /// <param name="originalModel">یک شی معادل با جدول پایگاه داده</param> /// <returns>تعداد سطرهای ویرایش شده برگشت داده میشود</returns> public int UpdateWithObject(object model, object originalModel) { Type type = model.GetType(); Njit.Sql.TableHelper tableHelper = new TableHelper(this.Connection, this.Transaction, type.Name); List <string> identityColumns = tableHelper.GetIdentityColumns(); List <string> keyColumns = tableHelper.GetKeyColumns(); Command.Parameters.Clear(); System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties().Where(t => !identityColumns.Contains(t.Name) && t.GetCustomAttributes(typeof(System.Data.Linq.Mapping.ColumnAttribute), false).Count() > 0).ToArray(); List <System.Data.SqlClient.SqlParameter> parameters = new List <System.Data.SqlClient.SqlParameter>(); foreach (var property in properties) { System.Data.SqlClient.SqlParameter parameter = new System.Data.SqlClient.SqlParameter("@" + property.Name, property.GetValue(model, null) ?? DBNull.Value); Command.Parameters.Add(parameter); parameters.Add(parameter); } System.Reflection.PropertyInfo[] keyProperties = model.GetType().GetProperties().Where(t => keyColumns.Contains(t.Name)).ToArray(); foreach (var property in keyProperties) { System.Data.SqlClient.SqlParameter originalParameter = new System.Data.SqlClient.SqlParameter("@original_" + property.Name, property.GetValue(originalModel, null) ?? DBNull.Value); Command.Parameters.Add(originalParameter); parameters.Add(originalParameter); } Command.CommandText = string.Format("UPDATE [{0}] SET {1} WHERE {2}", model.GetType().Name, properties.Select(t => "[" + t.Name + "]" + "=" + "@" + t.Name).Aggregate((a, b) => a + "," + b), keyProperties.Select(t => "[" + t.Name + "]" + "=" + "@original_" + t.Name).Aggregate((a, b) => a + " AND " + b)); Command.CommandType = CommandType.Text; System.Data.ConnectionState previousConnectionState = Connection.State; if (((Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open)) { try { Connection.Open(); } catch (Exception ex) { throw new Exception("ارتباط با سرور اس کیو ال برقرار نیست" + "\n\n" + ex.Message); } } int i; try { i = Command.ExecuteNonQuery(); } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { Connection.Close(); } } return(i); }
public override void Open() { if (string.IsNullOrEmpty(_connectionString) || string.IsNullOrWhiteSpace(_connectionString)) { throw new ConnectionException("Connection string cannot be empty or whitespace"); } _database = Alachisoft.NosDB.Client.NosDB.InitializeDatabase(_connectionString); _state = ConnectionState.Open; }
public int Update(object model, object originalModel, string[] keyColumns, params string[] identityColumns) { Command.Parameters.Clear(); System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties().Where(t => !identityColumns.Contains(t.Name)).ToArray(); List <System.Data.OleDb.OleDbParameter> parameters = new List <System.Data.OleDb.OleDbParameter>(); foreach (var property in properties) { System.Data.OleDb.OleDbParameter parameter = new System.Data.OleDb.OleDbParameter("@" + property.Name, property.GetValue(model, null) ?? DBNull.Value); Command.Parameters.Add(parameter); parameters.Add(parameter); } System.Reflection.PropertyInfo[] keyProperties = model.GetType().GetProperties().Where(t => keyColumns.Contains(t.Name)).ToArray(); foreach (var property in keyProperties) { System.Data.OleDb.OleDbParameter originalParameter = new System.Data.OleDb.OleDbParameter("@original_" + property.Name, property.GetValue(originalModel, null) ?? DBNull.Value); Command.Parameters.Add(originalParameter); parameters.Add(originalParameter); } Command.CommandText = string.Format("UPDATE [{0}] SET {1} WHERE {2}", model.GetType().Name, properties.Select(t => "[" + t.Name + "]" + "=" + "@" + t.Name).Aggregate((a, b) => a + "," + b), keyProperties.Select(t => "[" + t.Name + "]" + "=" + "@original_" + t.Name).Aggregate((a, b) => a + " AND " + b)); Command.CommandType = CommandType.Text; System.Data.ConnectionState previousConnectionState = Connection.State; if (((Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open)) { try { Connection.Open(); } catch (Exception ex) { throw new Exception("ارتباط با سرور اس کیو ال برقرار نیست" + "\n\n" + ex.Message); } } int i; try { i = Command.ExecuteNonQuery(); } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { Connection.Close(); } } return(i); }
/// <summary> /// اجرای دستور /// Select /// </summary> /// <param name="command">دستور</param> /// <param name="parameters">پارامترها</param> public List <T> GetData <T>(string command, params object[] parameters) { if (!TestConnection()) { throw new Exception("ارتباط با سرور اس کیو ال برقرار نیست"); } Command.Parameters.Clear(); Command.CommandText = command; if (parameters != null) { if (parameters.Length % 2 != 0) { throw new Exception("پارامترها نادرست است"); } for (int index = 0; index < parameters.Length; index += 2) { Command.Parameters.AddWithValue(parameters[index].ToString().StartsWith("@") ? parameters[index].ToString() : "@" + parameters[index].ToString(), parameters[index + 1]); } } Adapter.SelectCommand = Command; System.Data.ConnectionState previousConnectionState = Connection.State; DataTable dt = new DataTable(); try { Adapter.Fill(dt); } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { Connection.Close(); } } Type type = typeof(T); List <T> list = new List <T>(); for (int i = 0; i < dt.Rows.Count; i++) { list.Add(((T)Activator.CreateInstance(type))); System.Reflection.PropertyInfo[] properties = type.GetProperties().Where(t => t.GetCustomAttributes(typeof(System.Data.Linq.Mapping.ColumnAttribute), false).Count() > 0).ToArray(); foreach (System.Reflection.PropertyInfo item in properties) { item.SetValue(list[list.Count - 1], dt.Rows[i][item.Name] == DBNull.Value ? null : dt.Rows[i][item.Name], null); } } return(list); }
/// <summary> /// ثبت /// </summary> /// <param name="model">یک شی معادل با جدول پایگاه داده</param> /// <returns>تعداد سطرهای درج شده برگشت داده میشود</returns> public int InsertObject(object model) { Type type = model.GetType(); Njit.Sql.TableHelper _TableHelper = new TableHelper(this.Connection, this.Transaction, type.Name); List <string> identityColumns = _TableHelper.GetIdentityColumns(); Command.Parameters.Clear(); System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties().Where(t => !identityColumns.Contains(t.Name) && t.GetCustomAttributes(typeof(System.Data.Linq.Mapping.ColumnAttribute), false).Count() > 0).ToArray(); List <System.Data.SqlClient.SqlParameter> parameters = new List <System.Data.SqlClient.SqlParameter>(); int index = 0; foreach (var property in properties) { SqlParameter p = new SqlParameter("@p" + (index++).ToString(), property.GetValue(model, null) ?? DBNull.Value); Command.Parameters.Add(p); parameters.Add(p); } Command.CommandText = string.Format("INSERT INTO [{0}] ({1}) VALUES({2})", type.Name, properties.Select(t => "[" + t.Name + "]").Aggregate((a, b) => a + "," + b), parameters.Select(t => t.ParameterName).Aggregate((a, b) => a + "," + b)); Command.CommandType = CommandType.Text; System.Data.ConnectionState previousConnectionState = Connection.State; if (((Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open)) { try { Connection.Open(); } catch (Exception ex) { throw new Exception("ارتباط با سرور اس کیو ال برقرار نیست" + "\n\n" + ex.Message); } } int i; try { i = Command.ExecuteNonQuery(); } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { Connection.Close(); } } return(i); }
/// <summary> /// اجرای دستور اس کیو ال /// </summary> /// <param name="CommandType">نوع دستور</param> /// <param name="command">دستور</param> /// <param name="parameters">پارامتر ها</param> /// <returns>تعداد سطرهای مورد تغییر قرار گرفته برگشت داده می شود</returns> public int Execute(System.Data.CommandType CommandType, string command, params object[] parameters) { Command.Parameters.Clear(); Command.CommandText = command; Command.CommandType = CommandType; if (parameters != null) { if (parameters.Length % 2 != 0) { throw new Exception("پارامترها نادرست است"); } for (int index = 0; index < parameters.Length; index += 2) { Command.Parameters.AddWithValue(parameters[index].ToString().StartsWith("@") ? parameters[index].ToString() : "@" + parameters[index].ToString(), parameters[index + 1]); } } System.Data.ConnectionState previousConnectionState = Connection.State; if (((Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open)) { try { Connection.Open(); } catch (Exception ex) { throw new Exception("ارتباط با سرور اس کیو ال برقرار نیست" + "\n\n" + ex.Message); } } int i; try { i = Command.ExecuteNonQuery(); } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { Connection.Close(); } } return(i); }
/// <summary> /// Insert /// </summary> /// <param name="model">یک شی معادل با جدول پایگاه داده</param> /// <param name="identityColumns">نام ستون هایی که آیدنتیتی تعریف شده اند</param> /// <returns>تعداد سطرهای درج شده برگشت داده میشود</returns> public int Insert(object model, params string[] identityColumns) { Command.Parameters.Clear(); System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties().Where(t => !identityColumns.Contains(t.Name)).ToArray(); List <System.Data.OleDb.OleDbParameter> parameters = new List <System.Data.OleDb.OleDbParameter>(); int index = 0; foreach (var property in properties) { System.Data.OleDb.OleDbParameter p = new System.Data.OleDb.OleDbParameter("@p" + (index++).ToString(), property.GetValue(model, null) ?? DBNull.Value); Command.Parameters.Add(p); parameters.Add(p); } Command.CommandText = string.Format("INSERT INTO [{0}] ({1}) VALUES({2})", model.GetType().Name, properties.Select(t => "[" + t.Name + "]").Aggregate((a, b) => a + "," + b), parameters.Select(t => t.ParameterName).Aggregate((a, b) => a + "," + b)); Command.CommandType = CommandType.Text; System.Data.ConnectionState previousConnectionState = Connection.State; if (((Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open)) { try { Connection.Open(); } catch (Exception ex) { throw new Exception("ارتباط با سرور اس کیو ال برقرار نیست" + "\n\n" + ex.Message); } } int i; try { i = Command.ExecuteNonQuery(); } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { Connection.Close(); } } return(i); }
void PLC_DataChange(string groupName, int[] item, object[] value, short[] Qualities) { bool r = true; foreach (short q in Qualities) { r &= (q >= 192) ? (true) : (false); } ConnectionState = (r) ? (ConnectionState.Open) : (ConnectionState.Closed); switch (groupName) { case "Cfg": for (int i = 0; i < item.Length; i++) { if (value[i] == null) { continue; } meters[item[i]] = (ushort)value[i]; } break; case "Cfg-DataItem": for (int i = 0; i < item.Length; i++) { if (value[i] == null) { continue; } dataItems[item[i]] = (ushort)value[i]; } getConfig = true; break; case "CtMeters": break; } }
protected int ExecuteNonQuery(System.Data.SqlClient.SqlCommand cmd) { System.Data.ConnectionState previousConnectionState = cmd.Connection.State; if (((cmd.Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open)) { cmd.Connection.Open(); } int returnValue; try { returnValue = cmd.ExecuteNonQuery(); } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { cmd.Connection.Close(); } } return(returnValue); }
public DataTable GetTable(SQLiteCommand cmd) { System.Data.ConnectionState original = cmd.Connection.State; if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } DataTable dt = new DataTable(); SQLiteDataReader dr; dr = cmd.ExecuteReader(); dt.Load(dr); dr.Close(); dr.Dispose(); if (original == ConnectionState.Closed) { cmd.Connection.Clone(); } return(dt); }
static string getConnectionStatus(System.Data.ConnectionState state) { switch (state) { case ConnectionState.Broken: return("Broken"); case ConnectionState.Closed: return("Disconnected"); case ConnectionState.Connecting: return("Connecting..."); case ConnectionState.Executing: return("Operating..."); case ConnectionState.Fetching: return("Retrieving..."); case ConnectionState.Open: return("Connected"); } return("n/a"); }
/// <summary> /// اجرای دستور /// Select /// </summary> /// <param name="command">دستور</param> /// <param name="parameters">پارامترها</param> public DataTable GetData(string command, params object[] parameters) { if (!TestConnection()) { throw new Exception("ارتباط با سرور اس کیو ال برقرار نیست"); } Command.Parameters.Clear(); Command.CommandText = command; if (parameters != null) { if (parameters.Length % 2 != 0) { throw new Exception("پارامترها نادرست است"); } for (int index = 0; index < parameters.Length; index += 2) { Command.Parameters.AddWithValue(parameters[index].ToString().StartsWith("@") ? parameters[index].ToString() : "@" + parameters[index].ToString(), parameters[index + 1]); } } Adapter.SelectCommand = Command; System.Data.ConnectionState previousConnectionState = Connection.State; DataTable dt = new DataTable(); try { Adapter.Fill(dt); } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { Connection.Close(); } } return(dt); }
void PLC_DataChange(string groupName, int[] item, object[] value, short[] Qualities) { bool r = true; foreach (short q in Qualities) { r &= (q >= 192) ? (true) : (false); } ConnectionState = (r) ? (ConnectionState.Open) : (ConnectionState.Closed); switch (groupName) { case "Cfg": for (int i = 0; i < item.Length; i++) { if (value[i] == null) continue; meters[item[i]] = (ushort)value[i]; } break; case "Cfg-DataItem": for (int i = 0; i < item.Length; i++) { if (value[i] == null) continue; dataItems[item[i]] = (ushort)value[i]; } getConfig = true; break; case "CtMeters": break; } }
public override void Close() { //TODO _database.Close(); OR _database = null; _state = System.Data.ConnectionState.Closed; }
public NosDbConnection(string connectionString) { this._state = System.Data.ConnectionState.Closed; this._connectionString = connectionString; }