public static DbDataReader GetReader(ConnectionItem item, string sql, out string message, out DbConnection conn,
Dictionary <string, object> dbParams)
{
conn = null;
if (String.IsNullOrWhiteSpace(sql))
{
message = "The query cannot be blank.";
return(null);
}
conn = CreateConnection(item, out message);
if (conn == null || !String.IsNullOrWhiteSpace(message))
{
return(null);
}
DbCommand cmd = null;
try
{
switch (item.ConnectionType)
{
case ConnectionTypes.MSSQL:
cmd = new SqlCommand(sql, conn as SqlConnection);
if (dbParams != null)
{
foreach (KeyValuePair <string, object> kvp in dbParams)
{
cmd.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
}
}
break;
case ConnectionTypes.MySQL:
cmd = new MySqlCommand(sql, conn as MySqlConnection);
if (dbParams != null)
{
foreach (KeyValuePair <string, object> kvp in dbParams)
{
cmd.Parameters.Add(new MySqlParameter(kvp.Key, kvp.Value));
}
}
break;
}
if (cmd != null)
{
return(cmd.ExecuteReader());
}
}
catch (Exception ex)
{
message = ex.Message;
}
return(null);
}
public ReportItemBase(int id,
string name,
string groupName,
ConnectionItem connection,
FormItem form,
TemplateItem template)
{
this.ID = id;
this.Name = name;
this.GroupName = groupName;
this.ConnectionItem = connection;
this.FormItem = form;
this.TemplateItem = template;
}
public ReportItemBase(int id,
string name,
string groupName,
ConnectionItem connection,
FormItem form,
TemplateItem template)
{
this.ID = id;
this.Name = name;
this.GroupName = groupName;
this.ConnectionItem = connection;
this.FormItem = form;
this.TemplateItem = template;
}
private static DbConnection CreateConnection(ConnectionItem item, out string message)
{
message = "";
if (item == null)
{
message = "Invalid item.";
return(null);
}
DbConnection result = null;
try
{
string database = "";
switch (item.ConnectionType)
{
case ConnectionTypes.MySQL:
if (!String.IsNullOrWhiteSpace(item.DefaultDatabase))
{
database = "; Database=" + item.DefaultDatabase + ";";
}
result = new MySqlConnection(String.Format("Server={0}; {4} Port={1}; Uid={2}; Pwd={3} {4}", item.Host, item.ConnPort, item.Username, item.Password, database));
result.Open();
break;
case ConnectionTypes.MSSQL:
if (!String.IsNullOrWhiteSpace(item.DefaultDatabase))
{
database = "; Initial Catalog=" + item.DefaultDatabase + ";";
}
result = new SqlConnection(String.Format("Server={0}; User Id={1}; Password={2} {3}", item.Host, item.Username, item.Password, database));
result.Open();
break;
}
}
catch (Exception ex)
{
message = ex.Message;
}
return(result);
}
public static bool TestConnection(ConnectionItem item, out string message)
{
DbConnection conn = CreateConnection(item, out message);
if (conn == null || !String.IsNullOrWhiteSpace(message))
{
return(false);
}
try
{
return(conn.State == System.Data.ConnectionState.Open);
}
catch (Exception ex)
{
message = ex.Message;
}
finally
{
conn.Close();
conn.Dispose();
conn = null;
}
return(false);
}
protected override ApplyResult ProcessApplyItem(ref object obj, out string errorMessage, out string successMessage, out bool edited)
{
errorMessage = "";
successMessage = "";
edited = false;
string action = this.Request.Form.action.Value.ToLower();
int objectID = -1;
if (this.Request.Form.objectID != null)
{
objectID = int.Parse(this.Request.Form.objectID.Value.ToString());
}
string defaultDatabase = "";
if (this.Request.Form.DefaultDatabase != null)
{
defaultDatabase = this.Request.Form.DefaultDatabase.Value;
}
int port = 0;
bool portError = false;
if (this.Request.Form.ConnPort != null)
{
if (!int.TryParse(this.Request.Form.ConnPort.Value, out port))
{
portError = true;
}
}
ConnectionItem.ConnectionTypes type =
this.Request.Form.ConnectionTypes.Value == "mysql" ? ConnectionItem.ConnectionTypes.MySQL : ConnectionItem.ConnectionTypes.MSSQL;
ConnectionItem item = new ConnectionItem(objectID,
this.Request.Form.Name.Value,
this.Request.Form.Host.Value,
port,
this.Request.Form.Username.Value,
this.Request.Form.Password.Value,
type,
defaultDatabase);
obj = item;
bool showRefreshMessage = false;
switch (action)
{
case BaseWebModule.PostSave:
bool result = false;
if (!portError)
{
result = new DBContent().SaveConnection(item, out errorMessage);
edited = item.ID > 0;
}
else
{
errorMessage = "Invalid Port.";
}
if (result)
{
return ApplyResult.Save;
}
break;
case ConnectionModule.PostRefresh:
showRefreshMessage = true;
break;
case BaseWebModule.PostCancel:
return ApplyResult.Cancel;
case ConnectionModule.PostTest:
if (ConnectionItem.TestConnection(item, out errorMessage))
{
successMessage = "Successfully connected to the database.";
}
break;
}
string message;
if(item.RefreshDatabases(out message))
{
if (showRefreshMessage)
{
successMessage = "Successfully refreshed.";
}
}
else
{
if (showRefreshMessage)
{
errorMessage = message;
}
}
return ApplyResult.Message;
}
public bool SaveConnection(ConnectionItem item, out string errormessage)
{
errormessage = "";
try
{
if (item == null)
{
errormessage = "Invalid item.";
return(false);
}
if (String.IsNullOrWhiteSpace(item.Name))
{
errormessage = "The connection name cannot be blank.";
return(false);
}
int connectionType = 0; // mysql
switch (item.ConnectionType)
{
case ConnectionItem.ConnectionTypes.MSSQL:
connectionType = 1;
break;
}
this.AddParameter("@id", item.ID);
this.AddParameter("@name", item.Name);
this.AddParameter("@address", item.Host);
this.AddParameter("@port", item.ConnPort);
this.AddParameter("@connectionType", connectionType);
this.AddParameter("@username", item.Username);
this.AddParameter("@password", DBContent.AES_Encrypt(System.Text.Encoding.UTF8.GetBytes(item.Password)));
this.AddParameter("@defaultDatabase", item.DefaultDatabase);
string sql = "";
if (item.ID > 0)
{
SqliteDataReader reader = this.ExecuteReader("SELECT id FROM ReportingConnections WHERE id != @id AND name LIKE @name");
if (reader.HasRows)
{
errormessage = "A reporting connection with that name already exists.";
return(false);
}
sql = "UPDATE ReportingConnections SET name = @name, address = @address, port = @port, ";
sql += "connectionType = @connectionType, username=@username, password = @password,";
sql += "defaultDatabase = @defaultDatabase, password = @password WHERE id = @id";
}
else
{
SqliteDataReader reader = this.ExecuteReader("SELECT id FROM ReportingConnections WHERE name LIKE @name");
if (reader.HasRows)
{
errormessage = "A reporting connection with that name already exists.";
return(false);
}
sql = "INSERT INTO ReportingConnections(name, address, port, connectionType, username, password, defaultDatabase)";
sql += " VALUES(@name, @address, @port, @connectionType, @username, @password, @defaultDatabase)";
}
this.ExecuteNonQuery(sql);
return(true);
}
finally
{
this.CleanUp();
}
}
public ReportItemBase GetReport(int id,
out string errormessage)
{
errormessage = "";
ConnectionItem connection = null;
FormItem form = null;
TemplateItem template = null;
List <SWBaseTag> formTags = new List <SWBaseTag>();
List <SWBaseTag> templateTags = new List <SWBaseTag>();
try
{
string sql = "SELECT fk_connection, fk_template, fk_form, [Report].name, [ReportGroup].name FROM [Report]";
sql += " LEFT JOIN [ReportGroup] ON [ReportGroup].id = [Report].fk_group";
sql += " WHERE [Report].ID = " + id;
_reader = this.ExecuteReader(sql);
int connectionID = -1;
int formID = -1;
int templateID = -1;
string reportName = "";
string groupName = "";
if (!_reader.HasRows)
{
_reader.Close();
errormessage = "Invalid report.";
return(null);
}
while (_reader.Read())
{
connectionID = int.Parse(_reader[0].ToString());
templateID = int.Parse(_reader[1].ToString());
if (_reader[2] != DBNull.Value)
{
formID = int.Parse(_reader[2].ToString());
}
reportName = _reader[3].ToString();
groupName = _reader[4].ToString();
break;
}
_reader.Close();
connection = new DBContent().GetConnection(connectionID);
if (connection == null)
{
errormessage = "Invalid connection.";
return(null);
}
form = new DBContent().GetForm(formID);
template = new DBContent().GetTemplate(templateID);
if (template == null)
{
errormessage = "Invalid template.";
return(null);
}
sql = "SELECT [TagValue].itemType, [Tag].type, [TagValue].name, [TagValue].value FROM [Tag]";
sql += " INNER JOIN [TagValue] ON [TagValue].fk_tag = [Tag].id";
sql += " WHERE [TagValue].fk_report = " + id;
_reader = this.ExecuteReader(sql);
while (_reader.Read())
{
int itemType = 0;
if (!int.TryParse(_reader[0].ToString(), out itemType))
{
continue;
}
string type = _reader[1].ToString().ToLower();
string name = _reader[2].ToString().ToLower();
string value = _reader[3].ToString();
SWBaseTag tag = SWBaseTag.GetTag(type);
tag.Name = name;
tag.Value = value;
if (tag == null)
{
continue;
}
switch (itemType)
{
case (int)DBContent.TagValueItemTypes.Form:
if (tag.BaseTagType == SWBaseTag.BaseTagTypes.Form ||
tag.BaseTagType == SWBaseTag.BaseTagTypes.Both)
{
formTags.Add(tag);
}
break;
case (int)DBContent.TagValueItemTypes.Template:
if (tag.BaseTagType == SWBaseTag.BaseTagTypes.Template ||
tag.BaseTagType == SWBaseTag.BaseTagTypes.Both)
{
templateTags.Add(tag);
}
break;
default:
continue;
}
}
ReportItemBase item = new ReportItemBase(id, reportName, groupName, connection, form, template);
foreach (SWBaseTag dbtag in formTags)
{
foreach (SWBaseTag itemTag in item.FormTags)
{
if (dbtag.Name.Equals(itemTag.Name) &&
dbtag.BaseTagType.Equals(itemTag.BaseTagType) &&
dbtag.TagType.Equals(itemTag.TagType))
{
itemTag.Value = dbtag.Value;
break;
}
}
}
foreach (SWBaseTag dbtag in templateTags)
{
foreach (SWBaseTag itemTag in item.TemplateTags)
{
if (dbtag.Name.Equals(itemTag.Name) &&
dbtag.BaseTagType.Equals(itemTag.BaseTagType) &&
dbtag.TagType.Equals(itemTag.TagType))
{
itemTag.Value = dbtag.Value;
break;
}
}
}
return(item);
}
finally
{
this.CleanUp();
}
}
public static bool TestQuery(ConnectionItem item, string sql, out string message)
{
if (String.IsNullOrWhiteSpace(sql))
{
message = "The query cannot be blank.";
return(false);
}
DbConnection conn = CreateConnection(item, out message);
if (conn == null || !String.IsNullOrWhiteSpace(message))
{
return(false);
}
DbDataReader reader = null;
DbCommand cmd = null;
try
{
switch (item.ConnectionType)
{
case ConnectionTypes.MSSQL:
cmd = new SqlCommand(sql, conn as SqlConnection);
foreach (Match match in Regex.Matches(sql, @"(?<!\w)@\w+"))
{
if (!cmd.Parameters.Contains(match.Value))
{
cmd.Parameters.Add(new SqlParameter(match.Value, ""));
}
}
reader = cmd.ExecuteReader();
break;
case ConnectionTypes.MySQL:
cmd = new MySqlCommand(sql, conn as MySqlConnection);
foreach (Match match in Regex.Matches(sql, @"(?<!\w)@\w+"))
{
if (!cmd.Parameters.Contains(match.Value))
{
cmd.Parameters.Add(new MySqlParameter(match.Value, ""));
}
}
reader = cmd.ExecuteReader();
break;
}
return(true);
}
catch (Exception ex)
{
message = ex.Message;
}
finally
{
if (reader != null)
{
reader.Close();
reader.Dispose();
reader = null;
}
conn.Close();
conn.Dispose();
conn = null;
}
return(false);
}
private static DbConnection CreateConnection(ConnectionItem item, out string message)
{
message = "";
if (item == null)
{
message = "Invalid item.";
return null;
}
DbConnection result = null;
try
{
string database = "";
switch (item.ConnectionType)
{
case ConnectionTypes.MySQL:
if (!String.IsNullOrWhiteSpace(item.DefaultDatabase))
{
database = "; Database=" + item.DefaultDatabase + ";";
}
result = new MySqlConnection(String.Format("Server={0}; {4} Port={1}; Uid={2}; Pwd={3} {4}", item.Host, item.ConnPort, item.Username, item.Password, database));
result.Open();
break;
case ConnectionTypes.MSSQL:
if (!String.IsNullOrWhiteSpace(item.DefaultDatabase))
{
database = "; Initial Catalog=" + item.DefaultDatabase + ";";
}
result = new SqlConnection(String.Format("Server={0}; User Id={1}; Password={2} {3}", item.Host, item.Username, item.Password, database));
result.Open();
break;
}
}
catch (Exception ex)
{
message = ex.Message;
}
return result;
}
public static bool TestQuery(ConnectionItem item, string sql, out string message)
{
if (String.IsNullOrWhiteSpace(sql))
{
message = "The query cannot be blank.";
return false;
}
DbConnection conn = CreateConnection(item, out message);
if (conn == null || !String.IsNullOrWhiteSpace(message)) return false;
DbDataReader reader = null;
DbCommand cmd = null;
try
{
switch (item.ConnectionType)
{
case ConnectionTypes.MSSQL:
cmd = new SqlCommand(sql, conn as SqlConnection);
foreach (Match match in Regex.Matches(sql, @"(?<!\w)@\w+"))
{
if (!cmd.Parameters.Contains(match.Value))
{
cmd.Parameters.Add(new SqlParameter(match.Value, ""));
}
}
reader = cmd.ExecuteReader();
break;
case ConnectionTypes.MySQL:
cmd = new MySqlCommand(sql, conn as MySqlConnection);
foreach (Match match in Regex.Matches(sql, @"(?<!\w)@\w+"))
{
if (!cmd.Parameters.Contains(match.Value))
{
cmd.Parameters.Add(new MySqlParameter(match.Value, ""));
}
}
reader = cmd.ExecuteReader();
break;
}
return true;
}
catch (Exception ex)
{
message = ex.Message;
}
finally
{
if (reader != null)
{
reader.Close();
reader.Dispose();
reader = null;
}
conn.Close();
conn.Dispose();
conn = null;
}
return false;
}
public static bool TestConnection(ConnectionItem item, out string message)
{
DbConnection conn = CreateConnection(item, out message);
if (conn == null || !String.IsNullOrWhiteSpace(message)) return false;
try
{
return conn.State == System.Data.ConnectionState.Open;
}
catch (Exception ex)
{
message = ex.Message;
}
finally
{
conn.Close();
conn.Dispose();
conn = null;
}
return false;
}
public static DbDataReader GetReader(ConnectionItem item, string sql, out string message, out DbConnection conn,
Dictionary<string, object> dbParams)
{
conn = null;
if (String.IsNullOrWhiteSpace(sql))
{
message = "The query cannot be blank.";
return null;
}
conn = CreateConnection(item, out message);
if (conn == null || !String.IsNullOrWhiteSpace(message)) return null;
DbCommand cmd = null;
try
{
switch (item.ConnectionType)
{
case ConnectionTypes.MSSQL:
cmd = new SqlCommand(sql, conn as SqlConnection);
if (dbParams != null)
{
foreach (KeyValuePair<string, object> kvp in dbParams)
{
cmd.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
}
}
break;
case ConnectionTypes.MySQL:
cmd = new MySqlCommand(sql, conn as MySqlConnection);
if (dbParams != null)
{
foreach (KeyValuePair<string, object> kvp in dbParams)
{
cmd.Parameters.Add(new MySqlParameter(kvp.Key, kvp.Value));
}
}
break;
}
if (cmd != null)
{
return cmd.ExecuteReader();
}
}
catch (Exception ex)
{
message = ex.Message;
}
return null;
}
