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