public static void system_initialize_executescripts(
string script,
string scriptFile,
bool useTransactions)
{
CreateDatabase();
script = MsSqlDbAccess.GetCommandTextReplaced(script);
// apply database owner
if (!String.IsNullOrEmpty(MsSqlDbAccess.SchemaName))
{ script = script.Replace("dbN", MsSqlDbAccess.DBName.ToUpper()); }
else
{ script = script.Replace("dbN", "YAFNET"); }
// apply grantee name
if (!String.IsNullOrEmpty(MsSqlDbAccess.GranteeName))
{ script = script.Replace("grantName", MsSqlDbAccess.GranteeName.ToUpper()); }
else
{ script = script.Replace("grantName", "PUBLIC");}
// apply host name
script = script.Replace("hostName", MsSqlDbAccess.HostName);
//Scripts separation regexp
string[] statements = System.Text.RegularExpressions.Regex.Split(script, "(?:--GO)", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
// Here comes add SET ARITHABORT ON for MSSQL amd Linq class
// statements.Insert(0, "SET ARITHABORT ON");
using (YAF.Classes.Data.MsSqlDbConnectionManager connMan = new MsSqlDbConnectionManager())
{
// use transactions...
if (useTransactions)
{
using (FbTransaction trans = connMan.OpenDBConnection.BeginTransaction(YAF.Classes.Data.MsSqlDbAccess.IsolationLevel))
{
foreach (string sql0 in statements)
{
string sql = sql0.Trim();
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
continue;
if (sql.Length > 0)
{
using (FbCommand cmd = new FbCommand())
{
cmd.Transaction = trans;
cmd.Connection = connMan.DBConnection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception x)
{
trans.Rollback();
throw new Exception(String.Format("FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}", scriptFile, sql, x.Message));
}
}
trans.Commit();
}
}
else
{
// don't use transactions
foreach (string sql0 in statements)
{
string sql = sql0.Trim();
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
continue;
if (sql.Length > 0)
{
using (FbCommand cmd = new FbCommand())
{
cmd.Connection = connMan.OpenDBConnection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception x)
{
throw new Exception(String.Format("FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}", scriptFile, sql, x.Message));
}
}
}
}
}
public static DataSet forum_moderatelist([NotNull] object userID, [NotNull] object boardID)
{
using (var connMan = new MsSqlDbConnectionManager())
{
using (var ds = new DataSet())
{
using (var da = new SqlDataAdapter(DbHelpers.GetObjectName("category_list"), connMan.OpenDBConnection))
{
using (SqlTransaction trans = da.SelectCommand.Connection.BeginTransaction())
{
da.SelectCommand.Transaction = trans;
da.SelectCommand.AddParam("BoardID", boardID);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds, DbHelpers.GetObjectName("Category"));
da.SelectCommand.CommandText = DbHelpers.GetObjectName("forum_moderatelist");
da.SelectCommand.AddParam("UserID", userID);
da.Fill(ds, DbHelpers.GetObjectName("ForumUnsorted"));
DataTable dtForumListSorted = ds.Tables[DbHelpers.GetObjectName("ForumUnsorted")].Clone();
dtForumListSorted.TableName = DbHelpers.GetObjectName("Forum");
ds.Tables.Add(dtForumListSorted);
dtForumListSorted.Dispose();
forum_list_sort_basic(
ds.Tables[DbHelpers.GetObjectName("ForumUnsorted")],
ds.Tables[DbHelpers.GetObjectName("Forum")],
0,
0);
ds.Tables.Remove(DbHelpers.GetObjectName("ForumUnsorted"));
// vzrus: Remove here all forums with no reports. Would be better to do it in query...
// Array to write categories numbers
var categories = new int[ds.Tables[DbHelpers.GetObjectName("Forum")].Rows.Count];
int cntr = 0;
// We should make it before too as the colection was changed
ds.Tables[DbHelpers.GetObjectName("Forum")].AcceptChanges();
foreach (DataRow dr in ds.Tables[DbHelpers.GetObjectName("Forum")].Rows)
{
categories[cntr] = dr["CategoryID"].ToType<int>();
if (dr["ReportedCount"].ToType<int>() == 0 && dr["MessageCount"].ToType<int>() == 0)
{
dr.Delete();
categories[cntr] = 0;
}
cntr++;
}
ds.Tables[DbHelpers.GetObjectName("Forum")].AcceptChanges();
foreach (
DataRow dr in from DataRow dr in ds.Tables[DbHelpers.GetObjectName("Category")].Rows
let dr1 = dr
where
!categories.Where(
category => category == dr1["CategoryID"].ToType<int>()).Any()
select dr)
{
dr.Delete();
}
ds.Tables[DbHelpers.GetObjectName("Category")].AcceptChanges();
ds.Relations.Add(
"FK_Forum_Category",
ds.Tables[DbHelpers.GetObjectName("Category")].Columns["CategoryID"],
ds.Tables[DbHelpers.GetObjectName("Forum")].Columns["CategoryID"]);
trans.Commit();
}
return ds;
}
}
}
}
/// <summary>
/// The system_initialize_executescripts.
/// </summary>
/// <param name="script">
/// The script.
/// </param>
/// <param name="scriptFile">
/// The script file.
/// </param>
/// <param name="useTransactions">
/// The use transactions.
/// </param>
/// <exception cref="Exception">
/// </exception>
public static void system_initialize_executescripts([NotNull] string script, [NotNull] string scriptFile, bool useTransactions)
{
script = DbHelpers.GetCommandTextReplaced(script);
List<string> statements = Regex.Split(script, "\\sGO\\s", RegexOptions.IgnoreCase).ToList();
ushort sqlMajVersion = SqlServerMajorVersionAsShort();
using (var connMan = new MsSqlDbConnectionManager())
{
// use transactions...
if (useTransactions)
{
using (SqlTransaction trans = connMan.OpenDBConnection.BeginTransaction())
{
foreach (string sql0 in statements)
{
string sql = sql0.Trim();
sql = DbHelpers.CleanForSQLServerVersion(sql, sqlMajVersion);
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length > 0)
{
using (var cmd = new SqlCommand())
{
// added so command won't timeout anymore...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
cmd.Transaction = trans;
cmd.Connection = connMan.DBConnection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception x)
{
trans.Rollback();
throw new Exception(
String.Format("FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}", scriptFile, sql, x.Message));
}
}
trans.Commit();
}
}
else
{
// don't use transactions
foreach (string sql0 in statements)
{
string sql = sql0.Trim();
// add ARITHABORT option
// sql = "SET ARITHABORT ON\r\nGO\r\n" + sql;
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length > 0)
{
using (var cmd = new SqlCommand())
{
cmd.Connection = connMan.OpenDBConnection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception x)
{
throw new Exception(
String.Format("FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}", scriptFile, sql, x.Message));
}
}
}
}
}
/// <summary>
/// The db_reindex_new.
/// </summary>
public static string db_reindex_new()
{
try
{
using (var connMan = new MsSqlDbConnectionManager())
{
connMan.InfoMessage += new YafDBConnInfoMessageEventHandler(reindexDb_InfoMessage);
connMan.DBConnection.FireInfoMessageEventOnUserErrors = true;
// create statistic getting SQL...
var sb = new StringBuilder();
sb.AppendLine("DECLARE @MyTable VARCHAR(255)");
sb.AppendLine("DECLARE myCursor");
sb.AppendLine("CURSOR FOR");
sb.AppendFormat(
"SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_name LIKE '{0}%'",
Config.DatabaseObjectQualifier);
sb.AppendLine("OPEN myCursor");
sb.AppendLine("FETCH NEXT");
sb.AppendLine("FROM myCursor INTO @MyTable");
sb.AppendLine("WHILE @@FETCH_STATUS = 0");
sb.AppendLine("BEGIN");
sb.AppendLine("PRINT 'Reindexing Table: ' + @MyTable");
sb.AppendLine("DBCC DBREINDEX(@MyTable, '', 80)");
sb.AppendLine("FETCH NEXT");
sb.AppendLine("FROM myCursor INTO @MyTable");
sb.AppendLine("END");
sb.AppendLine("CLOSE myCursor");
sb.AppendLine("DEALLOCATE myCursor");
using (var cmd = new SqlCommand(sb.ToString(), connMan.OpenDBConnection))
{
cmd.Connection = connMan.DBConnection;
// up the command timeout...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
// run it...
cmd.ExecuteNonQuery();
}
return reindexDbMessage;
}
}
finally
{
reindexDbMessage = string.Empty;
}
}
/// <summary>
/// The db_shrink.
/// </summary>
/// <param name="DBName">
/// The db name.
/// </param>
public static string db_shrink_new()
{
try
{
using (var conn = new MsSqlDbConnectionManager())
{
conn.InfoMessage += new YafDBConnInfoMessageEventHandler(dbShink_InfoMessage);
conn.DBConnection.FireInfoMessageEventOnUserErrors = true;
string ShrinkSql = "DBCC SHRINKDATABASE(N'" + conn.DBConnection.Database + "')";
var ShrinkConn = new SqlConnection(Config.ConnectionString);
var ShrinkCmd = new SqlCommand(ShrinkSql, ShrinkConn);
ShrinkConn.Open();
ShrinkCmd.ExecuteNonQuery();
ShrinkConn.Close();
using (var cmd = new SqlCommand(ShrinkSql, conn.OpenDBConnection))
{
cmd.Connection = conn.DBConnection;
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
cmd.ExecuteNonQuery();
}
}
return dbShinkMessage;
}
finally
{
dbShinkMessage = string.Empty;
}
}
/// <summary>
/// The user_register.
/// </summary>
/// <param name="boardID">
/// The board id.
/// </param>
/// <param name="userName">
/// The user name.
/// </param>
/// <param name="password">
/// The password.
/// </param>
/// <param name="hash">
/// The hash.
/// </param>
/// <param name="email">
/// The email.
/// </param>
/// <param name="location">
/// The location.
/// </param>
/// <param name="homePage">
/// The home page.
/// </param>
/// <param name="timeZone">
/// The time zone.
/// </param>
/// <param name="approved">
/// The approved.
/// </param>
/// <returns>
/// The user_register.
/// </returns>
public static bool user_register([NotNull] object boardID, [NotNull] object userName, [NotNull] object password, [NotNull] object hash, [NotNull] object email, [NotNull] object location, [NotNull] object homePage, [NotNull] object timeZone,
bool approved)
{
using (var connMan = new MsSqlDbConnectionManager())
{
using (SqlTransaction trans = connMan.OpenDBConnection.BeginTransaction(MsSqlDbAccess.IsolationLevel))
{
try
{
using (var cmd = MsSqlDbAccess.GetCommand("user_save", connMan.DBConnection))
{
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
int UserID = 0;
cmd.Parameters.AddWithValue("UserID", UserID);
cmd.Parameters.AddWithValue("BoardID", boardID);
cmd.Parameters.AddWithValue("UserName", userName);
cmd.Parameters.AddWithValue(
"Password", FormsAuthentication.HashPasswordForStoringInConfigFile(password.ToString(), "md5"));
cmd.Parameters.AddWithValue("Email", email);
cmd.Parameters.AddWithValue("Hash", hash);
cmd.Parameters.AddWithValue("Location", location);
cmd.Parameters.AddWithValue("HomePage", homePage);
cmd.Parameters.AddWithValue("TimeZone", timeZone);
cmd.Parameters.AddWithValue("Approved", approved);
cmd.Parameters.AddWithValue("PMNotification", 1);
cmd.Parameters.AddWithValue("AutoWatchTopics", 0);
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception x)
{
trans.Rollback();
eventlog_create(null, "user_register in YAF.Classes.Data.DB.cs", x, EventLogTypes.Error);
return false;
}
}
}
return true;
}
/// <summary>
/// System initialize and execute script's.
/// </summary>
/// <param name="script">The script.</param>
/// <param name="scriptFile">The script file.</param>
/// <param name="useTransactions">The use transactions.</param>
public static void system_initialize_executescripts([NotNull] string script, [NotNull] string scriptFile, bool useTransactions)
{
script = DbHelpers.GetCommandTextReplaced(script);
var statements = Regex.Split(script, "\\sGO\\s", RegexOptions.IgnoreCase).ToList();
using (var connMan = new MsSqlDbConnectionManager())
{
// use transactions...
if (useTransactions)
{
using (SqlTransaction trans = connMan.OpenDBConnection.BeginTransaction())
{
foreach (var sql in statements.Select(sql0 => sql0.Trim()))
{
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length <= 0)
{
continue;
}
using (var cmd = new SqlCommand())
{
// added so command won't timeout anymore...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
cmd.Transaction = trans;
cmd.Connection = connMan.DBConnection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
cmd.ExecuteNonQuery();
}
}
catch (Exception x)
{
trans.Rollback();
throw new Exception(
"FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}".FormatWith(scriptFile, sql, x.Message));
}
}
trans.Commit();
}
}
else
{
// don't use transactions
foreach (var sql in statements.Select(sql0 => sql0.Trim()))
{
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length <= 0)
{
continue;
}
using (var cmd = new SqlCommand())
{
cmd.Connection = connMan.OpenDBConnection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
cmd.ExecuteNonQuery();
}
}
catch (Exception x)
{
throw new Exception(
"FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}".FormatWith(scriptFile, sql, x.Message));
}
}
}
}
}
public static string system_initialize_replace_entries(string script)
{
bool conEncoding = false;
string[] options = null;
// apply object qualifier
if (!String.IsNullOrEmpty(Config.DatabaseObjectQualifier))
{ script = script.Replace("{objectQualifier}", Config.DatabaseObjectQualifier); }
else
{ script = script.Replace("{objectQualifier}", "yaf_"); }
string dbcharset = null;
string dbcollation = null;
script = MsSqlDbAccess.GetCommandTextReplaced(script);
using (YAF.Classes.Data.MsSqlDbConnectionManager connMan = new MsSqlDbConnectionManager())
{
options = connMan.DBConnection.ConnectionString.Split(';');
}
foreach (string str in options)
{
string[] optionValue = str.Split('=');
// apply database name
if (optionValue[0].Trim().ToLower() == "database")
{
if (optionValue[1].Trim() != Config.SchemaName || !string.IsNullOrEmpty(optionValue[1].Trim()))
{
script = script.Replace("{databaseName}", optionValue[1].Trim());
}
else
script = script.Replace("{databaseName}", Config.SchemaName);
}
// apply user name from connection string to override defaults in config
// currently it's not used
if (optionValue[0].Trim().ToLowerInvariant().Contains("user id")
|| optionValue[0].Trim().ToLowerInvariant().Contains("Username")
|| optionValue[0].Trim().ToLowerInvariant().Contains("User name")
|| optionValue[0].Trim().ToLowerInvariant().Contains("Uid"))
{
if (optionValue[1].Trim() != Config.DatabaseOwner || !string.IsNullOrEmpty(optionValue[1].Trim()))
{
script = script.Replace("{databaseName}", optionValue[1].Trim());
}
else
script = script.Replace("{databaseName}", Config.DatabaseOwner.Trim());
}
// Encodings
// apply charset
if ((str.Contains("Charset") || str.Contains("Character Set")) && string.IsNullOrEmpty(optionValue[1]))
{
//Verify if it's valid
using (MySqlCommand cmd = MsSqlDbAccess.GetCommand("SHOW VARIABLES LIKE 'character_set_database'", true))
{
DataTable dtt = MsSqlDbAccess.Current.GetData(cmd);
if (dtt.Rows.Count > 0)
{
foreach (DataRow dr in dtt.Rows)
{
if (dr["Variable_name"] == optionValue[1].Trim())
{
dbcharset = dr["Value"].ToString();
}
}
conEncoding = true;
}
}
}
if (!string.IsNullOrEmpty(Config.DatabaseEncoding))
{
//Verify if it's valid
using (MySqlCommand cmd = MsSqlDbAccess.GetCommand("SHOW VARIABLES LIKE 'character_set_database'", true))
{
DataTable dtt1 = MsSqlDbAccess.Current.GetData(cmd);
if (dtt1.Rows.Count > 0)
{
foreach (DataRow dr in dtt1.Rows)
{
if (dr["Variable_name"].ToString() == "character_set_database")
{
dbcharset = dr["Value"].ToString();
}
}
conEncoding = true;
}
}
}
}
if (conEncoding)
{
if (Config.DatabaseCollation.Contains(dbcharset))
dbcollation = Config.DatabaseCollation;
if (string.IsNullOrEmpty(dbcollation))
{
using (MySqlCommand cmd = MsSqlDbAccess.GetCommand("SHOW CHARACTER SET;", true))
{
DataTable dttt = MsSqlDbAccess.Current.GetData(cmd);
foreach (DataRow dr in dttt.Rows)
{
if (dr["Charset"].ToString() == dbcharset)
{
dbcollation = dr["Default collation"].ToString();
}
}
}
}
}
//No entry for encoding in connection string or app.config
if (!conEncoding)
{
using (MySqlCommand cmd = MsSqlDbAccess.GetCommand("SHOW VARIABLES LIKE 'collation_database'", true))
{
dbcollation = MsSqlDbAccess.Current.GetData(cmd).Rows[0]["Value"].ToString();
}
using (MySqlCommand cmd = MsSqlDbAccess.GetCommand("SHOW VARIABLES LIKE 'character_set_database'", true))
{
dbcharset = MsSqlDbAccess.Current.GetData(cmd).Rows[0]["Value"].ToString();
}
}
script = script.Replace("{databaseEncoding}_{databaseCollation}", dbcollation);
script = script.Replace("{databaseEncoding}", dbcharset);
return script;
}
public static string db_getstats_new()
{
try
{
using (var connMan = new MsSqlDbConnectionManager())
{
connMan.InfoMessage += new YafDBConnInfoMessageEventHandler(getStats_InfoMessage);
using (NpgsqlCommand cmd = new NpgsqlCommand("VACUUM ANALYZE VERBOSE;", connMan.OpenDBConnection))
{
cmd.CommandType = CommandType.Text;
// up the command timeout...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
// run it...
cmd.ExecuteNonQuery();
return getStatsMessage;
}
}
}
finally
{
getStatsMessage = string.Empty;
}
}
/// <summary>
/// The method saves many questions and answers to them in a single transaction
/// </summary>
/// <param name="pollList">List to hold all polls data</param>
/// <returns>Last saved poll id.</returns>
public static int? poll_save(List<PollSaveList> pollList)
{
foreach (PollSaveList question in pollList)
{
using (MsSqlDbConnectionManager connMan = new MsSqlDbConnectionManager())
{
using (
MySqlTransaction trans = connMan.OpenDBConnection.BeginTransaction(MsSqlDbAccess.IsolationLevel)
)
{
try
{
int? myPollID = null;
StringBuilder sb = new StringBuilder();
// Check if the group already exists
if (question.TopicId > 0)
{
sb.Append("select PollID from ");
sb.Append(MsSqlDbAccess.GetObjectName("Topic"));
sb.Append(" WHERE TopicID = ?TopicID; ");
}
else if (question.ForumId > 0)
{
sb.Append("select PollGroupID from ");
sb.Append(MsSqlDbAccess.GetObjectName("Forum"));
sb.Append(" WHERE ForumID = ?ForumID;");
}
else if (question.CategoryId > 0)
{
sb.Append("select PollGroupID from ");
sb.Append(MsSqlDbAccess.GetObjectName("Category"));
sb.Append(" WHERE CategoryID = ?CategoryID;");
}
int? pollGroupId = null;
object pollGroupIdObj = null;
using (MySqlCommand cmdPoll = MsSqlDbAccess.GetCommand(sb.ToString(), true))
{
cmdPoll.Transaction = trans;
if (question.TopicId > 0)
{
cmdPoll.Parameters.Add("?TopicID", MySqlDbType.Int32).Value = question.TopicId;
}
else if (question.ForumId > 0)
{
cmdPoll.Parameters.Add("?ForumID", MySqlDbType.Int32).Value = question.ForumId;
}
else if (question.CategoryId > 0)
{
cmdPoll.Parameters.Add("?CategoryID", MySqlDbType.Int32).Value = question.CategoryId;
}
pollGroupIdObj = MsSqlDbAccess.Current.ExecuteScalar(cmdPoll, false);
}
sb = new StringBuilder();
// the group doesn't exists, create a new one
int pgIdcheck = 0;
if (!int.TryParse(pollGroupIdObj.ToString(), out pgIdcheck))
{
sb.Append(string.Format("INSERT INTO {0}(UserID,Flags ) VALUES(?UserID, ?Flags); ", MsSqlDbAccess.GetObjectName("PollGroupCluster")));
// sb.Append("SELECT PollGroupID FROM ");
sb.Append("SELECT LAST_INSERT_ID(); ");
// sb.Append(MsSqlDbAccess.GetObjectName("PollGroupCluster"));
// sb.Append(" WHERE PollGroupID = LAST_INSERT_ID(); ");
using (MySqlCommand cmdPoll = MsSqlDbAccess.GetCommand(sb.ToString(), true))
{
cmdPoll.Transaction = trans;
cmdPoll.Parameters.Add("?UserID", MySqlDbType.Int32).Value = question.UserId;
// set poll group flags
int groupFlags = 0;
if (question.IsBound)
{
groupFlags = groupFlags | 2;
}
cmdPoll.Parameters.Add("?Flags", MySqlDbType.Int32).Value = groupFlags;
pollGroupId = Convert.ToInt32(MsSqlDbAccess.Current.ExecuteScalar(cmdPoll, false));
}
}
else
{
sb.Append(String.Format("UPDATE {0} SET Flags = (CASE WHEN Flags <> 0 AND (?Flags & 2) = 2 THEN Flags = Flags | 2 ELSE ?Flags END) WHERE PollGroupID = ?PollGroupID; ", MsSqlDbAccess.GetObjectName("PollGroupCluster")));
using (MySqlCommand cmdPollUpdate = MsSqlDbAccess.GetCommand(sb.ToString(), true))
{
cmdPollUpdate.Transaction = trans;
cmdPollUpdate.Parameters.Add("?UserID", MySqlDbType.Int32).Value = question.UserId;
// set poll group flags
int groupFlags = 0;
if (question.IsBound)
{
groupFlags = groupFlags | 2;
}
cmdPollUpdate.Parameters.Add("?Flags", MySqlDbType.Int32).Value = groupFlags;
cmdPollUpdate.Parameters.Add("?PollGroupID", MySqlDbType.Int32).Value = pollGroupId;
MsSqlDbAccess.Current.ExecuteNonQuery(cmdPollUpdate, false);
}
pollGroupId = (int?)pollGroupIdObj;
}
sb = new System.Text.StringBuilder(string.Format("INSERT INTO {0}",MsSqlDbAccess.GetObjectName("Poll")));
if (question.Closes > DateTime.MinValue)
{
sb.Append("(Question,Closes, UserID,PollGroupID,ObjectPath,MimeType,Flags) ");
}
else
{
sb.Append("(Question,UserID,PollGroupID,ObjectPath,MimeType,Flags) ");
}
sb.Append(" VALUES(");
sb.Append("?Question");
if (question.Closes > DateTime.MinValue)
{
sb.Append(",?Closes");
}
sb.Append(",?UserID,?PollGroupID,?QuestionObjectPath,?QuestionMimeType,?PollFlags");
sb.Append("); ");
sb.Append("SELECT ");
sb.Append(" LAST_INSERT_ID(); ");
using (MySqlCommand cmdPoll = MsSqlDbAccess.GetCommand(sb.ToString(), true))
{
cmdPoll.Transaction = trans;
cmdPoll.CommandType = CommandType.Text;
cmdPoll.Parameters.Add("?Question", MySqlDbType.VarChar).Value = question.Question;
if (question.Closes > DateTime.MinValue)
{
cmdPoll.Parameters.Add("?Closes", MySqlDbType.DateTime).Value = question.Closes;
}
cmdPoll.Parameters.Add("?UserID", MySqlDbType.VarChar).Value = question.UserId;
cmdPoll.Parameters.Add("?PollGroupID", MySqlDbType.VarChar).Value = pollGroupId;
cmdPoll.Parameters.Add("?QuestionObjectPath", MySqlDbType.VarChar).Value =
question.QuestionObjectPath;
cmdPoll.Parameters.Add("?QuestionMimeType", MySqlDbType.VarChar).Value =
question.QuestionMimeType;
int pollFlags = question.IsClosedBound ? 0 | 4 : 0;
pollFlags = question.AllowMultipleChoices ? pollFlags | 8 : pollFlags;
pollFlags = question.ShowVoters
? pollFlags | 16
: pollFlags;
pollFlags = question.AllowSkipVote
? pollFlags | 32
: pollFlags;
cmdPoll.Parameters.Add("?PollFlags", MySqlDbType.VarChar).Value = pollFlags;
object dd = MsSqlDbAccess.Current.ExecuteScalar(cmdPoll, false);
myPollID = Convert.ToInt32(dd);
}
sb = new System.Text.StringBuilder();
// The cycle through question reply choices
for (uint choiceCount = 0; choiceCount < question.Choice.GetUpperBound(1) + 1; choiceCount++)
{
if (!string.IsNullOrEmpty(question.Choice[0, choiceCount]))
{
// sb.Append(string.Format(" INSERT INTO "));
// sb.Append(MsSqlDbAccess.GetObjectName("Choice"));
// sb.Append(string.Format("("));
// sb.Append(string.Format(" INSERT INTO {0}(",MsSqlDbAccess.GetObjectName("Choice")));
// sb.AppendFormat("PollID,Choice,Votes) VALUES(?PollID{0},?Choice{0},?Votes{0}); ",
// choiceCount);
sb.AppendFormat("INSERT INTO {0}(PollID,Choice,Votes,ObjectPath,MimeType) VALUES(?PollID{1},?Choice{1},?Votes{1},?ChoiceObjectPath{1},?ChoiceMimeType{1}); ",
MsSqlDbAccess.GetObjectName("Choice"), choiceCount);
}
}
using (MySqlCommand cmd = MsSqlDbAccess.GetCommand(sb.ToString(), true))
{
/* MySqlParameter ret = new MySqlParameter();
ret.ParameterName = "?PollIDOut";
ret.MySqlDbType = MySqlDbType.Int32;
ret.Direction = ParameterDirection.Output;
cmd.Parameters.Add(ret);
cmd.Parameters.Add("?Question", MySqlDbType.VarChar ).Value = question.Question;
if (question.Closes > DateTime.MinValue)
{
cmd.Parameters.Add("?Closes", MySqlDbType.DateTime ).Value = question.Closes;
}
*/
cmd.Transaction = trans;
for (uint choiceCount1 = 0;
choiceCount1 < question.Choice.GetUpperBound(1) + 1;
choiceCount1++)
{
if (!string.IsNullOrEmpty(question.Choice[0, choiceCount1]))
{
cmd.Parameters.Add("?PollID{0}".FormatWith(choiceCount1), MySqlDbType.Int32).Value =
myPollID;
cmd.Parameters.Add(String.Format("?Choice{0}", choiceCount1), MySqlDbType.VarChar).
Value
= question.Choice[0, choiceCount1];
cmd.Parameters.Add(String.Format("?Votes{0}", choiceCount1), MySqlDbType.Int32).
Value =
0;
cmd.Parameters.Add(String.Format("?ChoiceObjectPath{0}", choiceCount1),
MySqlDbType.VarChar).Value =
question.Choice[1, choiceCount1].IsNotSet()
? String.Empty
: question.Choice[1, choiceCount1];
cmd.Parameters.Add(String.Format("?ChoiceMimeType{0}", choiceCount1),
MySqlDbType.VarChar).Value =
question.Choice[2, choiceCount1].IsNotSet()
? String.Empty
: question.Choice[2, choiceCount1];
}
}
MsSqlDbAccess.Current.ExecuteNonQuery(cmd, false);
}
sb = new StringBuilder();
// fill a pollgroup field - double work if a poll exists
if (question.TopicId > 0)
{
sb.Append("UPDATE ");
sb.Append(MsSqlDbAccess.GetObjectName("Topic"));
sb.Append(" SET PollID = ?NewPollGroupID WHERE TopicID =?TopicID; ");
}
// fill a pollgroup field in Forum Table if the call comes from a forum's topic list
if (question.ForumId > 0)
{
sb.Append("UPDATE ");
sb.Append(MsSqlDbAccess.GetObjectName("Forum"));
sb.Append(" SET PollGroupID= ?NewPollGroupID WHERE ForumID= ?ForumID; ");
}
// fill a pollgroup field in Category Table if the call comes from a category's topic list
if (question.CategoryId > 0)
{
sb.Append("UPDATE ");
sb.Append(MsSqlDbAccess.GetObjectName("Category"));
sb.Append(" SET PollGroupID = ?NewPollGroupID WHERE CategoryID= ?CategoryID; ");
}
using (MySqlCommand cmdPoll = MsSqlDbAccess.GetCommand(sb.ToString(), true))
{
cmdPoll.Transaction = trans;
cmdPoll.Parameters.Add("?NewPollGroupID", MySqlDbType.Int32).Value = pollGroupId;
if (question.TopicId > 0)
{
cmdPoll.Parameters.Add("?TopicID", MySqlDbType.Int32).Value = question.TopicId;
}
// fill a pollgroup field in Forum Table if the call comes from a forum's topic list
if (question.ForumId > 0)
{
cmdPoll.Parameters.Add("?ForumID", MySqlDbType.Int32).Value = question.ForumId;
}
// fill a pollgroup field in Category Table if the call comes from a category's topic list
if (question.CategoryId > 0)
{
cmdPoll.Parameters.Add("?CategoryID", MySqlDbType.Int32).Value = question.CategoryId;
}
MsSqlDbAccess.Current.ExecuteNonQuery(cmdPoll, false);
}
/*if (ret.Value != DBNull.Value)
{
return (int?)ret.Value;
}*/
trans.Commit();
return pollGroupId;
}
catch (Exception e)
{
trans.Rollback();
throw new Exception(e.Message);
}
finally
{
connMan.CloseConnection();
}
}
}
}
return null;
}
/// <summary>
/// The set profile properties.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="userID">
/// The user id.
/// </param>
/// <param name="values">
/// The values.
/// </param>
/// <param name="settingsColumnsList">
/// The settings columns list.
/// </param>
public static void SetProfileProperties([NotNull] int boardId, [NotNull] object appName, [NotNull] int userID, [NotNull] SettingsPropertyValueCollection values, [NotNull] List<SettingsPropertyColumn> settingsColumnsList, bool dirtyOnly)
{
string userName = string.Empty;
var dtu = LegacyDb.UserList(boardId, userID, true, null, null, true);
foreach (var typedUserList in dtu)
{
userName = typedUserList.Name;
break;
}
if (userName.IsNotSet())
{
return;
}
string sql = @"SELECT 1 FROM {0} WHERE UserId = @i_UserID AND ApplicationName = @i_ApplicationName LIMIT 1".FormatWith(MsSqlDbAccess.GetObjectName("UserProfile"));
bool exists = false;
using (var cmd = MsSqlDbAccess.GetCommand(sql, true))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("i_UserID", MySqlDbType.Int32).Value = userID;
cmd.Parameters.Add("i_ApplicationName", MySqlDbType.VarChar).Value = appName;
exists = Convert.ToInt32(MsSqlDbAccess.Current.ExecuteScalar(cmd)) > 0;
}
using (var conn = new MsSqlDbConnectionManager().OpenDBConnection)
{
var cmd = new MySqlCommand();
cmd.Connection = conn;
string table = MsSqlDbAccess.GetObjectName("UserProfile");
StringBuilder sqlCommand = new StringBuilder();
// StringBuilder sqlCommand = new StringBuilder("IF EXISTS (SELECT 1 FROM ").Append(table);
// sqlCommand.Append(" WHERE UserId = @UserID AND ApplicationName = @ApplicationName) ");
cmd.Parameters.Add("i_UserID",MySqlDbType.Int32).Value = userID;
// Build up strings used in the query
var columnStr = new StringBuilder();
var valueStr = new StringBuilder();
var setStr = new StringBuilder();
int count = 0;
foreach (SettingsPropertyColumn column in settingsColumnsList.Where(column => !dirtyOnly || values[column.Settings.Name].IsDirty))
{
columnStr.Append(", ");
valueStr.Append(", ");
columnStr.Append(column.Settings.Name);
string valueParam = "@i_Value" + count;
valueStr.Append(valueParam);
cmd.Parameters.AddWithValue(valueParam, values[column.Settings.Name].PropertyValue);
if ((column.DataType != MySqlDbType.Timestamp) || column.Settings.Name != "LastUpdatedDate" || column.Settings.Name != "LastActivity")
{
if (count > 0)
{
setStr.Append(",");
}
setStr.Append(column.Settings.Name);
setStr.Append("=");
setStr.Append(valueParam);
}
count++;
}
columnStr.Append(",LastUpdatedDate ");
valueStr.Append(",@i_LastUpdatedDate");
setStr.Append(",LastUpdatedDate=@i_LastUpdatedDate");
cmd.Parameters.AddWithValue("@i_LastUpdatedDate", DateTime.UtcNow);
// MembershipUser mu = System.Web.Security.Membership.GetUser(userID);
columnStr.Append(",LastActivity ");
valueStr.Append(",@i_LastActivity");
setStr.Append(",LastActivity=@i_LastActivity");
cmd.Parameters.AddWithValue("@i_LastActivity", DateTime.UtcNow);
columnStr.Append(",ApplicationName ");
valueStr.Append(",@i_ApplicationName");
setStr.Append(",ApplicationName=@i_ApplicationName");
cmd.Parameters.Add("i_ApplicationName", MySqlDbType.VarChar).Value = appName;
columnStr.Append(",IsAnonymous ");
valueStr.Append(",@i_IsAnonymous");
setStr.Append(",IsAnonymous=@i_IsAnonymous");
cmd.Parameters.AddWithValue("@i_IsAnonymous", 0);
columnStr.Append(",UserName ");
valueStr.Append(",@i_UserName");
setStr.Append(",UserName=@i_UserName");
cmd.Parameters.AddWithValue("@i_UserName", userName);
if (exists)
{
sqlCommand.Append("UPDATE ").Append(table).Append(" SET ").Append(setStr.ToString());
sqlCommand.Append(" WHERE UserID = @i_UserID").Append("");
}
else
{
sqlCommand.Append("INSERT ").Append(table).Append(" (UserID").Append(columnStr.ToString());
sqlCommand.Append(") VALUES (@i_UserID").Append("").Append(valueStr.ToString()).Append(
");");
}
cmd.CommandText = sqlCommand.ToString();
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
public static string db_runsql_new(string sql, bool useTransaction)
{
try
{
using (var connMan = new MsSqlDbConnectionManager())
{
connMan.InfoMessage += new YafDBConnInfoMessageEventHandler(my_runSql_InfoMessage);
sql = MsSqlDbAccess.GetCommandTextReplaced(sql.Trim());
using (var command = new MySqlCommand(sql, connMan.OpenDBConnection))
{
command.CommandTimeout = 9999;
command.Connection = connMan.OpenDBConnection;
return InnerRunSqlExecuteReader(command, useTransaction);
}
}
}
finally
{
my_messageRunSql = string.Empty;
}
}
public static string db_reindex_new()
{
try
{
using (var connMan = new MsSqlDbConnectionManager())
{
connMan.InfoMessage += new YafDBConnInfoMessageEventHandler(my_reindexDb_InfoMessage);
using (MySqlCommand cmd = new MySqlCommand(String.Format("ANALYZE TABLE {0}.{1}user;", Config.SchemaName, Config.DatabaseObjectQualifier)))
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
cmd.CommandType = CommandType.Text;
// up the command timeout...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
// run it...
sb.Append( "SELECT table_name FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE t.engine ='InnoDB' AND t.TABLE_TYPE='BASE TABLE' " );
sb.Append( ";" );
MsSqlDbAccess.Current.ExecuteNonQuery(cmd, false);
return my_reindexDbMessage;
}
}
}
finally
{
my_reindexDbMessage = string.Empty;
}
}
/// <summary>
/// The db_getstats_new.
/// </summary>
public static string db_getstats_new()
{
try
{
using (var connMan = new MsSqlDbConnectionManager())
{
connMan.InfoMessage += new YafDBConnInfoMessageEventHandler(getStats_InfoMessage);
using (
MySqlCommand cmd =
new MySqlCommand(String.Format("ANALYZE TABLE {0}.{1}user;", Config.SchemaName,
Config.DatabaseObjectQualifier)))
{
cmd.CommandType = CommandType.Text;
// up the command timeout...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
// run it...
MsSqlDbAccess.Current.ExecuteNonQuery(cmd, false);
return getStatsMessage;
}
}
}
finally
{
getStatsMessage = string.Empty;
}
}
/// <summary>
/// The db_getstats_new.
/// </summary>
public static string db_getstats_new()
{
try
{
using (var connMan = new MsSqlDbConnectionManager())
{
connMan.InfoMessage += new YafDBConnInfoMessageEventHandler(getStats_InfoMessage);
connMan.DBConnection.FireInfoMessageEventOnUserErrors = true;
// create statistic getting SQL...
var sb = new StringBuilder();
sb.AppendLine("DECLARE @TableName sysname");
sb.AppendLine("DECLARE cur_showfragmentation CURSOR FOR");
sb.AppendFormat(
"SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_name LIKE '{0}%'",
Config.DatabaseObjectQualifier);
sb.AppendLine("OPEN cur_showfragmentation");
sb.AppendLine("FETCH NEXT FROM cur_showfragmentation INTO @TableName");
sb.AppendLine("WHILE @@FETCH_STATUS = 0");
sb.AppendLine("BEGIN");
sb.AppendLine("DBCC SHOWCONTIG (@TableName)");
sb.AppendLine("FETCH NEXT FROM cur_showfragmentation INTO @TableName");
sb.AppendLine("END");
sb.AppendLine("CLOSE cur_showfragmentation");
sb.AppendLine("DEALLOCATE cur_showfragmentation");
using (var cmd = new SqlCommand(sb.ToString(), connMan.OpenDBConnection))
{
cmd.Connection = connMan.DBConnection;
// up the command timeout...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
// run it...
cmd.ExecuteNonQuery();
return getStatsMessage;
}
}
}
finally
{
getStatsMessage = string.Empty;
}
}
public static string db_reindex_new()
{
///VACUUM ANALYZE VERBOSE;VACUUM cannot be implemeneted within function or multiline command line string
try
{
using (var connMan = new MsSqlDbConnectionManager())
{
connMan.InfoMessage += new YafDBConnInfoMessageEventHandler(reindexDb_InfoMessage);
using (NpgsqlCommand cmd = new NpgsqlCommand(String.Format(
@"REINDEX DATABASE {0};", Config.SchemaName.IsSet() ? Config.SchemaName : "public"),
connMan.OpenDBConnection))
{
cmd.CommandType = CommandType.Text;
// up the command timeout...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
// run it...
cmd.ExecuteNonQuery();
return reindexDbMessage;
}
}
}
finally
{
reindexDbMessage = string.Empty;
}
}
public static DataSet forum_moderatelist([NotNull] object userID, [NotNull] object boardID)
{
using (var connMan = new MsSqlDbConnectionManager())
{
using (var ds = new DataSet())
{
using (var da = new SqlDataAdapter(MsSqlDbAccess.GetObjectName("category_list"), connMan.OpenDBConnection))
{
using (SqlTransaction trans = da.SelectCommand.Connection.BeginTransaction(MsSqlDbAccess.IsolationLevel))
{
da.SelectCommand.Transaction = trans;
da.SelectCommand.Parameters.AddWithValue("BoardID", boardID);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds, MsSqlDbAccess.GetObjectName("Category"));
da.SelectCommand.CommandText = MsSqlDbAccess.GetObjectName("forum_moderatelist");
da.SelectCommand.Parameters.AddWithValue("UserID", userID);
da.Fill(ds, MsSqlDbAccess.GetObjectName("ForumUnsorted"));
DataTable dtForumListSorted = ds.Tables[MsSqlDbAccess.GetObjectName("ForumUnsorted")].Clone();
dtForumListSorted.TableName = MsSqlDbAccess.GetObjectName("Forum");
ds.Tables.Add(dtForumListSorted);
dtForumListSorted.Dispose();
forum_list_sort_basic(
ds.Tables[MsSqlDbAccess.GetObjectName("ForumUnsorted")],
ds.Tables[MsSqlDbAccess.GetObjectName("Forum")],
0,
0);
ds.Tables.Remove(MsSqlDbAccess.GetObjectName("ForumUnsorted"));
// vzrus: Remove here all forums with no reports. Would be better to do it in query...
// Array to write categories numbers
var categories = new int[ds.Tables[MsSqlDbAccess.GetObjectName("Forum")].Rows.Count];
int cntr = 0;
// We should make it before too as the colection was changed
ds.Tables[MsSqlDbAccess.GetObjectName("Forum")].AcceptChanges();
foreach (DataRow dr in ds.Tables[MsSqlDbAccess.GetObjectName("Forum")].Rows)
{
categories[cntr] = Convert.ToInt32(dr["CategoryID"]);
if (Convert.ToInt32(dr["ReportedCount"]) == 0 && Convert.ToInt32(dr["MessageCount"]) == 0)
{
dr.Delete();
categories[cntr] = 0;
}
cntr++;
}
ds.Tables[MsSqlDbAccess.GetObjectName("Forum")].AcceptChanges();
foreach (DataRow dr in ds.Tables[MsSqlDbAccess.GetObjectName("Category")].Rows)
{
bool deleteMe = true;
for (int i = 0; i < categories.Length; i++)
{
// We check here if the Category is missing in the array where
// we've written categories number for each forum
if (categories[i] == Convert.ToInt32(dr["CategoryID"]))
{
deleteMe = false;
}
}
if (deleteMe)
{
dr.Delete();
}
}
ds.Tables[MsSqlDbAccess.GetObjectName("Category")].AcceptChanges();
ds.Relations.Add(
"FK_Forum_Category",
ds.Tables[MsSqlDbAccess.GetObjectName("Category")].Columns["CategoryID"],
ds.Tables[MsSqlDbAccess.GetObjectName("Forum")].Columns["CategoryID"]);
trans.Commit();
}
return ds;
}
}
}
}
/// <summary>
/// The db_runsql.
/// </summary>
/// <param name="sql">
/// The sql.
/// </param>
/// <param name="connectionManager">
/// The conn man.
/// </param>
/// <param name="useTransaction">
/// The use Transaction.
/// </param>
/// <returns>
/// The db_runsql.
/// </returns>
public static string db_runsql_new( [NotNull] string sql, bool useTransaction)
{
try
{
using (var connMan = new MsSqlDbConnectionManager())
{
connMan.InfoMessage += new YafDBConnInfoMessageEventHandler(runSql_InfoMessage);
sql = MsSqlDbAccess.GetCommandTextReplaced(sql.Trim());
var results = new System.Text.StringBuilder();
using (var cmd = new NpgsqlCommand(sql, connMan.OpenDBConnection))
{
cmd.CommandTimeout = 9999;
NpgsqlDataReader reader = null;
var trans = useTransaction ? cmd.Connection.BeginTransaction(MsSqlDbAccess.IsolationLevel)
: null;
try
{
cmd.Connection = connMan.DBConnection;
cmd.Transaction = trans;
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
int rowIndex = 1;
results.Append("RowNumber");
int gg = 0;
var columnNames = new string[reader.GetSchemaTable().Rows.Count - 1];
foreach (DataRow drd in reader.GetSchemaTable().Rows)
{
columnNames[gg] = drd["ColumnName"].ToString();
results.Append(",");
results.Append(drd["ColumnName"].ToString());
gg++;
}
// var columnNames = reader.GetSchemaTable().Rows.Cast<DataRow>().Select(r => r["ColumnName"].ToString()).ToList();
results.AppendLine();
while (reader.Read())
{
results.AppendFormat(@"""{0}""", rowIndex++);
// dump all columns...
foreach (var col in columnNames)
{
results.AppendFormat(@",""{0}""", reader[col].ToString().Replace("\"", "\"\""));
}
results.AppendLine();
}
}
else if (reader.RecordsAffected > 0)
{
results.AppendFormat("{0} Record(s) Affected", reader.RecordsAffected);
results.AppendLine();
}
else
{
if (messageRunSql.IsSet())
{
results.AppendLine(messageRunSql);
results.AppendLine();
}
results.AppendLine("No Results Returned.");
}
reader.Close();
trans.Commit();
}
catch (Exception x)
{
if (reader != null)
{
reader.Close();
}
// rollback...
trans.Rollback();
results.AppendLine();
results.AppendFormat("SQL ERROR: {0}", x);
}
return results.ToString();
}
}
}
finally
{
messageRunSql = string.Empty;
}
}
/// <summary>
/// The set profile properties.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="userID">
/// The user id.
/// </param>
/// <param name="values">
/// The values.
/// </param>
/// <param name="settingsColumnsList">
/// The settings columns list.
/// </param>
public static void SetProfileProperties([NotNull] int boardId, [NotNull] object appName, [NotNull] int userID, [NotNull] SettingsPropertyValueCollection values, [NotNull] List<SettingsPropertyColumn> settingsColumnsList, bool dirtyOnly)
{
string userName = string.Empty;
var dtu = LegacyDb.UserList(boardId, userID, true, null, null, true);
foreach (var typedUserList in dtu)
{
userName = typedUserList.Name;
break;
}
if (userName.IsNotSet())
{
return;
}
using ( var conn = new MsSqlDbConnectionManager().OpenDBConnection)
{
var cmd = new SqlCommand();
cmd.Connection = conn;
string table = MsSqlDbAccess.GetObjectName("UserProfile");
StringBuilder sqlCommand = new StringBuilder("IF EXISTS (SELECT 1 FROM ").Append(table);
sqlCommand.Append(" WHERE UserId = @UserID AND ApplicationName = @ApplicationName) ");
cmd.Parameters.AddWithValue("@UserID", userID);
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Build up strings used in the query
var columnStr = new StringBuilder();
var valueStr = new StringBuilder();
var setStr = new StringBuilder();
int count = 0;
foreach (SettingsPropertyColumn column in settingsColumnsList)
{
// only write if it's dirty
if (!dirtyOnly || values[column.Settings.Name].IsDirty)
{
columnStr.Append(", ");
valueStr.Append(", ");
columnStr.Append(column.Settings.Name);
string valueParam = "@Value" + count;
valueStr.Append(valueParam);
cmd.Parameters.AddWithValue(valueParam, values[column.Settings.Name].PropertyValue);
if ((column.DataType != SqlDbType.Timestamp) || column.Settings.Name != "LastUpdatedDate" || column.Settings.Name != "LastActivity")
{
if (count > 0)
{
setStr.Append(",");
}
setStr.Append(column.Settings.Name);
setStr.Append("=");
setStr.Append(valueParam);
}
count++;
}
}
columnStr.Append(",LastUpdatedDate ");
valueStr.Append(",@LastUpdatedDate");
setStr.Append(",LastUpdatedDate=@LastUpdatedDate");
cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow);
// MembershipUser mu = System.Web.Security.Membership.GetUser(userID);
columnStr.Append(",LastActivity ");
valueStr.Append(",@LastActivity");
setStr.Append(",LastActivity=@LastActivity");
cmd.Parameters.AddWithValue("@LastActivity", DateTime.UtcNow);
columnStr.Append(",ApplicationName ");
valueStr.Append(",@ApplicationName");
setStr.Append(",ApplicationName=@ApplicationName");
// cmd.Parameters.AddWithValue("@ApplicationID", appId);
columnStr.Append(",IsAnonymous ");
valueStr.Append(",@IsAnonymous");
setStr.Append(",IsAnonymous=@IsAnonymous");
cmd.Parameters.AddWithValue("@IsAnonymous", 0);
columnStr.Append(",UserName ");
valueStr.Append(",@UserName");
setStr.Append(",UserName=@UserName");
cmd.Parameters.AddWithValue("@UserName", userName);
sqlCommand.Append("BEGIN UPDATE ").Append(table).Append(" SET ").Append(setStr.ToString());
sqlCommand.Append(" WHERE UserId = ").Append(userID.ToString()).Append("");
sqlCommand.Append(" END ELSE BEGIN INSERT ").Append(table).Append(" (UserId").Append(columnStr.ToString());
sqlCommand.Append(") VALUES (").Append(userID.ToString()).Append("").Append(valueStr.ToString()).Append(
") END");
cmd.CommandText = sqlCommand.ToString();
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Gets a list of categories????
/// </summary>
/// <param name="boardId">BoardID</param>
/// <returns>DataSet with categories</returns>
public static DataSet ds_forumadmin( object boardId)
{
using (MsSqlDbConnectionManager connMan = new MsSqlDbConnectionManager())
{
using ( DataSet ds = new DataSet() )
{
using ( NpgsqlTransaction trans = connMan.OpenDBConnection.BeginTransaction( MsSqlDbAccess.IsolationLevel ) )
{
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(MsSqlDbAccess.GetObjectName("category_list"), connMan.DBConnection))
{
da.SelectCommand.Transaction = trans;
da.SelectCommand.Parameters.Add(new NpgsqlParameter("i_boardid", NpgsqlDbType.Integer)).Value = boardId;
da.SelectCommand.Parameters.Add(new NpgsqlParameter("i_CategoryID", NpgsqlDbType.Integer)).Value = DBNull.Value;
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill( ds, MsSqlDbAccess.GetObjectName( "Category" ) );
da.SelectCommand.CommandText = MsSqlDbAccess.GetObjectName( "forum_list" );
da.Fill( ds, MsSqlDbAccess.GetObjectName( "ForumUnsorted" ) );
DataTable dtForumListSorted = ds.Tables [MsSqlDbAccess.GetObjectName( "ForumUnsorted" )].Clone();
dtForumListSorted.TableName = MsSqlDbAccess.GetObjectName( "Forum" );
ds.Tables.Add( dtForumListSorted );
dtForumListSorted.Dispose();
LegacyDb.forum_list_sort_basic( ds.Tables[MsSqlDbAccess.GetObjectName("ForumUnsorted")], ds.Tables[MsSqlDbAccess.GetObjectName("Forum")], 0, 0);
ds.Tables.Remove( MsSqlDbAccess.GetObjectName( "ForumUnsorted" ) );
ds.Relations.Add( "FK_Forum_Category", ds.Tables [MsSqlDbAccess.GetObjectName( "Category" )].Columns ["CategoryID"], ds.Tables [MsSqlDbAccess.GetObjectName( "Forum" )].Columns ["CategoryID"] );
trans.Commit();
}
return ds;
}
}
}
}
/// <summary>
/// The db_recovery_mode.
/// </summary>
/// <param name="DBName">
/// The db name.
/// </param>
/// <param name="dbRecoveryMode">
/// The db recovery mode.
/// </param>
public static string db_recovery_mode_new([NotNull] string dbRecoveryMode)
{
try
{
using (var connMan = new MsSqlDbConnectionManager())
{
connMan.InfoMessage += new YafDBConnInfoMessageEventHandler(recoveryDbMode_InfoMessage);
var RecoveryModeConn = new SqlConnection(Config.ConnectionString);
RecoveryModeConn.Open();
string RecoveryMode = "ALTER DATABASE " + connMan.DBConnection.Database + " SET RECOVERY " + dbRecoveryMode;
var RecoveryModeCmd = new SqlCommand(RecoveryMode, RecoveryModeConn);
RecoveryModeCmd.ExecuteNonQuery();
RecoveryModeConn.Close();
using (var cmd = new SqlCommand(RecoveryMode, connMan.OpenDBConnection))
{
cmd.Connection = connMan.DBConnection;
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
cmd.ExecuteNonQuery();
return recoveryDbModeMessage;
}
}
}
catch (Exception error)
{
string expressDb = string.Empty;
if (error.Message.ToUpperInvariant().Contains("'SET'"))
{
expressDb = "MS SQL Server Express Editions are not supported by the application.";
}
recoveryDbModeMessage += "\r\n{0}\r\n{1}".FormatWith(error.Message, expressDb);
return recoveryDbModeMessage;
}
finally
{
recoveryDbModeMessage = string.Empty;
}
}
public static bool forumpage_initdb( out string errorStr, bool debugging)
{
errorStr = null;
try
{
using (YAF.Classes.Data.MsSqlDbConnectionManager connMan = new MsSqlDbConnectionManager())
{ // just attempt to open the connection to test if a DB is available.
NpgsqlConnection getConn = connMan.OpenDBConnection;
}
return true;
}
catch (Npgsql.NpgsqlException ex)
{
// unable to connect to the LegacyDb...
if (debugging)
{
errorStr = "Unable to connect to the Database. Exception Message: " +
ex.Message +
" (" + ex.Code.ToString()
+ ")";
}
else
{
// re-throw since we are debugging...
throw;
}
return false;
}
}
/// <summary>
/// The db_runsql.
/// </summary>
/// <param name="sql">
/// The sql.
/// </param>
/// <param name="connectionManager">
/// The conn man.
/// </param>
/// <param name="useTransaction">
/// The use Transaction.
/// </param>
/// <returns>
/// The db_runsql.
/// </returns>
public static string db_runsql_new([NotNull] string sql, bool useTransaction)
{
try
{
using (var connMan = new MsSqlDbConnectionManager())
{
connMan.InfoMessage += new YafDBConnInfoMessageEventHandler(runSql_InfoMessage);
connMan.DBConnection.FireInfoMessageEventOnUserErrors = true;
sql = DbHelpers.GetCommandTextReplaced(sql.Trim());
using (var command = new SqlCommand(sql, connMan.OpenDBConnection))
{
command.CommandTimeout = 9999;
command.Connection = connMan.OpenDBConnection;
return InnerRunSqlExecuteReader(command, useTransaction);
}
}
}
finally
{
messageRunSql = string.Empty;
}
}
/// <summary>
/// The set profile properties.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="userID">
/// The user id.
/// </param>
/// <param name="values">
/// The values.
/// </param>
/// <param name="settingsColumnsList">
/// The settings columns list.
/// </param>
public static void SetProfileProperties( [NotNull] int boardId, [NotNull] object appName, [NotNull] int userID, [NotNull] SettingsPropertyValueCollection values, [NotNull] List<SettingsPropertyColumn> settingsColumnsList, bool dirtyOnly)
{
string userName = string.Empty;
var dtu = LegacyDb.UserList(boardId, userID, true, null, null, true);
foreach (var typedUserList in dtu)
{
userName = typedUserList.Name;
break;
}
if (userName.IsNotSet())
{
return;
}
using (var conn = new MsSqlDbConnectionManager().OpenDBConnection)
{
var cmd = new NpgsqlCommand();
cmd.Connection = conn;
string table = MsSqlDbAccess.GetObjectName("UserProfile");
StringBuilder sqlCommand = new StringBuilder("SELECT 1 FROM ").Append(table);
sqlCommand.Append(" WHERE UserID = :UserID AND ApplicationName = :ApplicationName");
cmd.Parameters.Add("UserID",NpgsqlDbType.Integer).Value= userID;
cmd.Parameters.Add("ApplicationName", NpgsqlDbType.Varchar).Value = appName;
cmd.CommandText = sqlCommand.ToString();
cmd.CommandType = CommandType.Text;
object o = cmd.ExecuteScalar();
int dd;
// Build up strings used in the query
var columnStr = new StringBuilder();
var valueStr = new StringBuilder();
var setStr = new StringBuilder();
int count = 0;
foreach (SettingsPropertyColumn column in settingsColumnsList)
{
// only write if it's dirty
if (!dirtyOnly || values[column.Settings.Name].IsDirty)
{
columnStr.Append(", ");
valueStr.Append(", ");
columnStr.Append(column.Settings.Name);
string valueParam = ":Value" + count;
valueStr.Append(valueParam);
cmd.Parameters.Add(valueParam, column.DataType).Value = values[column.Settings.Name].PropertyValue;
if ((column.DataType != NpgsqlDbType.Timestamp) || column.Settings.Name != "LastUpdatedDate" || column.Settings.Name != "LastActivity")
{
if (count > 0)
{
setStr.Append(",");
}
setStr.Append(column.Settings.Name);
setStr.Append("=");
setStr.Append(valueParam);
}
else
{
}
count++;
}
}
columnStr.Append(",LastUpdatedDate ");
valueStr.Append(",:LastUpdatedDate");
setStr.Append(",LastUpdatedDate=:LastUpdatedDate");
cmd.Parameters.Add("LastUpdatedDate",NpgsqlDbType.TimestampTZ).Value = DateTime.UtcNow;
// MembershipUser mu = System.Web.Security.Membership.GetUser(userID);
columnStr.Append(",LastActivity ");
valueStr.Append(",:LastActivity");
setStr.Append(",LastActivity=:LastActivity");
cmd.Parameters.Add("LastActivity", NpgsqlDbType.TimestampTZ).Value = DateTime.UtcNow;
columnStr.Append(",ApplicationName ");
valueStr.Append(",:ApplicationName");
setStr.Append(",ApplicationName=:ApplicationName");
columnStr.Append(",IsAnonymous ");
valueStr.Append(",:IsAnonymous");
setStr.Append(",IsAnonymous=:IsAnonymous");
cmd.Parameters.AddWithValue("IsAnonymous", 0);
cmd.Parameters.Add("IsAnonymous", NpgsqlDbType.Boolean).Value = false;
columnStr.Append(",UserName ");
valueStr.Append(",:UserName");
setStr.Append(",UserName=:UserName");
cmd.Parameters.Add("UserName",NpgsqlDbType.Varchar).Value = userName;
// the user exists.
sqlCommand.Clear();
if (o != null && int.TryParse(o.ToString(), out dd))
{
sqlCommand.Append("UPDATE ").Append(table).Append(" SET ").Append(setStr.ToString());
sqlCommand.Append(" WHERE UserID = ").Append(userID.ToString()).Append("");
}
else
{
sqlCommand.Append("INSERT INTO ").Append(table).Append(" (UserID").Append(columnStr.ToString());
sqlCommand.Append(") VALUES (").Append(userID.ToString()).Append("").Append(valueStr.ToString()).Append(
")");
}
cmd.CommandText = sqlCommand.ToString();
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
public static DataSet ds_forumadmin([NotNull] object boardID)
{
// TODO: this function is TERRIBLE. Recode or remove completely.
using (var connMan = new MsSqlDbConnectionManager())
{
using (var ds = new DataSet())
{
using (var trans = connMan.OpenDBConnection.BeginTransaction())
{
using (var da = new SqlDataAdapter(DbHelpers.GetObjectName("category_list"), connMan.DBConnection))
{
da.SelectCommand.Transaction = trans;
da.SelectCommand.AddParam("BoardID", boardID);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds, DbHelpers.GetObjectName("Category"));
da.SelectCommand.CommandText = DbHelpers.GetObjectName("forum_list");
da.Fill(ds, DbHelpers.GetObjectName("ForumUnsorted"));
DataTable dtForumListSorted = ds.Tables[DbHelpers.GetObjectName("ForumUnsorted")].Clone();
dtForumListSorted.TableName = DbHelpers.GetObjectName("Forum");
ds.Tables.Add(dtForumListSorted);
dtForumListSorted.Dispose();
forum_list_sort_basic(
ds.Tables[DbHelpers.GetObjectName("ForumUnsorted")],
ds.Tables[DbHelpers.GetObjectName("Forum")],
0,
0);
ds.Tables.Remove(DbHelpers.GetObjectName("ForumUnsorted"));
ds.Relations.Add(
"FK_Forum_Category",
ds.Tables[DbHelpers.GetObjectName("Category")].Columns["CategoryID"],
ds.Tables[DbHelpers.GetObjectName("Forum")].Columns["CategoryID"]);
trans.Commit();
}
return ds;
}
}
}
}
public static void system_initialize_executescripts(string script, string scriptFile, bool useTransactions)
{
script = MsSqlDbAccess.GetCommandTextReplaced(script);
//Scripts separation regexp
string[] statements = System.Text.RegularExpressions.Regex.Split(script, "(?:--GO)", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
using (var connMan = new MsSqlDbConnectionManager())
{
// use transactions...
if (useTransactions)
{
using (NpgsqlTransaction trans = connMan.OpenDBConnection.BeginTransaction(YAF.Classes.Data.MsSqlDbAccess.IsolationLevel))
{
foreach (string sql0 in statements)
{
string sql = sql0.Trim();
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
continue;
if (sql.Length > 0)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Transaction = trans;
cmd.Connection = connMan.DBConnection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
// added so command won't timeout anymore...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
cmd.ExecuteNonQuery();
}
}
}
catch (Exception x)
{
sql = sql0;
trans.Rollback();
throw new Exception(String.Format("FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}", scriptFile, sql, x.Message));
}
}
trans.Commit();
}
}
else
{
// don't use transactions
foreach (string sql0 in statements)
{
string sql = sql0.Trim();
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
continue;
if (sql.Length > 0)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = connMan.OpenDBConnection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception x)
{
throw new Exception(String.Format("FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}", scriptFile, sql, x.Message));
}
}
}
}
}
/// <summary>
/// The forumpage_initdb.
/// </summary>
/// <param name="errorStr">
/// The error str.
/// </param>
/// <param name="debugging">
/// The debugging.
/// </param>
/// <returns>
/// The forumpage_initdb.
/// </returns>
public static bool forumpage_initdb([NotNull] out string errorStr, bool debugging)
{
errorStr = string.Empty;
try
{
using (var connMan = new MsSqlDbConnectionManager())
{
// just attempt to open the connection to test if a DB is available.
SqlConnection getConn = connMan.OpenDBConnection;
}
}
catch (SqlException ex)
{
// unable to connect to the DB...
if (!debugging)
{
errorStr = "Unable to connect to the Database. Exception Message: " + ex.Message + " (" + ex.Number + ")";
return false;
}
// re-throw since we are debugging...
throw;
}
return true;
}
public static bool user_register( object boardId, object userName, object password, object hash, object email, object location, object homePage, object timeZone, bool approved)
{
using (MsSqlDbConnectionManager connMan = new MsSqlDbConnectionManager())
{
using (NpgsqlTransaction trans = connMan.OpenDBConnection.BeginTransaction(MsSqlDbAccess.IsolationLevel))
{
try
{
using (NpgsqlCommand cmd = MsSqlDbAccess.GetCommand("user_save", connMan.OpenDBConnection))
{
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new NpgsqlParameter("i_userid", NpgsqlDbType.Integer)).Value = DBNull.Value;
cmd.Parameters.Add(new NpgsqlParameter("i_boardid", NpgsqlDbType.Integer)).Value = boardId;
cmd.Parameters.Add(new NpgsqlParameter("i_username", NpgsqlDbType.Varchar)).Value = userName;
cmd.Parameters.Add(new NpgsqlParameter("i_email", NpgsqlDbType.Varchar)).Value = email;
cmd.Parameters.Add(new NpgsqlParameter("i_timezone", NpgsqlDbType.Integer)).Value = timeZone;
cmd.Parameters.Add(new NpgsqlParameter("i_languagefile", NpgsqlDbType.Varchar)).Value = DBNull.Value;
cmd.Parameters.Add(new NpgsqlParameter("i_themefile", NpgsqlDbType.Varchar)).Value = DBNull.Value;
cmd.Parameters.Add(new NpgsqlParameter("i_overridedefaulttheme", NpgsqlDbType.Boolean)).Value = false;
cmd.Parameters.Add(new NpgsqlParameter("i_approved", NpgsqlDbType.Boolean)).Value = approved;
cmd.Parameters.Add(new NpgsqlParameter("i_pmnotification", NpgsqlDbType.Boolean)).Value = false;
cmd.Parameters.Add(new NpgsqlParameter("i_provideruserkey", NpgsqlDbType.Varchar)).Value = DBNull.Value;
cmd.Parameters.Add(new NpgsqlParameter("i_utctimestamp", NpgsqlDbType.TimestampTZ)).Value = DateTime.UtcNow;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception x)
{
trans.Rollback();
eventlog_create(null, "user_register in YAF.Classes.Data.LegacyDb.cs", x, EventLogTypes.Error);
return false;
}
}
}
return true;
}
/// <summary>
/// The system_initialize_fixaccess.
/// </summary>
/// <param name="grant">
/// The grant.
/// </param>
public static void system_initialize_fixaccess(bool grant)
{
using (var connMan = new MsSqlDbConnectionManager())
{
using (SqlTransaction trans = connMan.OpenDBConnection.BeginTransaction())
{
// REVIEW : Ederon - would "{databaseOwner}.{objectQualifier}" work, might need only "{objectQualifier}"
using (
var da =
new SqlDataAdapter(
"select Name,IsUserTable = OBJECTPROPERTY(id, N'IsUserTable'),IsScalarFunction = OBJECTPROPERTY(id, N'IsScalarFunction'),IsProcedure = OBJECTPROPERTY(id, N'IsProcedure'),IsView = OBJECTPROPERTY(id, N'IsView') from dbo.sysobjects where Name like '{databaseOwner}.{objectQualifier}%'",
connMan.OpenDBConnection))
{
da.SelectCommand.Transaction = trans;
using (var dt = new DataTable("sysobjects"))
{
da.Fill(dt);
using (var cmd = connMan.DBConnection.CreateCommand())
{
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select current_user";
var userName = (string)cmd.ExecuteScalar();
if (grant)
{
cmd.CommandType = CommandType.Text;
foreach (DataRow row in dt.Select("IsProcedure=1 or IsScalarFunction=1"))
{
cmd.CommandText = "grant execute on \"{0}\" to \"{1}\"".FormatWith(row["Name"], userName);
cmd.ExecuteNonQuery();
}
foreach (DataRow row in dt.Select("IsUserTable=1 or IsView=1"))
{
cmd.CommandText = "grant select,update on \"{0}\" to \"{1}\"".FormatWith(row["Name"], userName);
cmd.ExecuteNonQuery();
}
}
else
{
cmd.CommandText = "sp_changeobjectowner";
cmd.CommandType = CommandType.StoredProcedure;
foreach (DataRow row in dt.Select("IsUserTable=1"))
{
cmd.Parameters.Clear();
cmd.AddParam("@objname", row["Name"]);
cmd.AddParam("@newowner", "dbo");
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException)
{
}
}
foreach (DataRow row in dt.Select("IsView=1"))
{
cmd.Parameters.Clear();
cmd.AddParam("@objname", row["Name"]);
cmd.AddParam("@newowner", "dbo");
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException)
{
}
}
}
}
}
}
trans.Commit();
}
}
}
/// <summary>
/// The method saves many questions and answers to them in a single transaction
/// </summary>
/// <param name="pollList">List to hold all polls data</param>
/// <returns>Last saved poll id.</returns>
public static int? poll_save(
[NotNull] System.Collections.Generic.List<PollSaveList> pollList)
{
foreach (PollSaveList question in pollList)
{
var sb = new System.Text.StringBuilder();
var paramSb = new System.Text.StringBuilder("EXECUTE BLOCK (");
sb.Append(" RETURNS(OUT_POLLGROUPID INTEGER) AS DECLARE VARIABLE OUT_POLLID INTEGER; BEGIN ");
// Check if the group already exists
if (question.CategoryId > 0)
{
sb.Append("SELECT POLLGROUPID FROM ");
sb.Append(MsSqlDbAccess.GetObjectName("CATEGORY"));
sb.Append(" WHERE CATEGORYID = :I_CATEGORYID INTO :OUT_POLLGROUPID; ");
paramSb.Append("I_CATEGORYID INTEGER = ?,");
}
if (question.ForumId > 0)
{
sb.Append("SELECT POLLGROUPID FROM ");
sb.Append(MsSqlDbAccess.GetObjectName("FORUM"));
sb.Append(" WHERE FORUMID = :I_FORUMID INTO :OUT_POLLGROUPID; ");
paramSb.Append("I_FORUMID INTEGER = ?,");
}
if (question.TopicId > 0)
{
sb.Append(" SELECT POLLID FROM ");
sb.Append(MsSqlDbAccess.GetObjectName("TOPIC"));
sb.Append(" WHERE TOPICID = :I_TOPICID INTO :OUT_POLLGROUPID; ");
paramSb.Append("I_TOPICID INTEGER = ?,");
}
// if the poll group doesn't exists, create a new one
sb.Append("IF (OUT_POLLGROUPID IS NULL) THEN BEGIN ");
sb.Append("INSERT INTO ");
sb.Append(MsSqlDbAccess.GetObjectName("POLLGROUPCLUSTER"));
sb.AppendFormat("(POLLGROUPID, USERID, FLAGS) VALUES((SELECT NEXT VALUE FOR SEQ_{0}PGC_POLLGROUPID FROM RDB$DATABASE), :GROUPUSERID, :POLLGROUPFLAGS) RETURNING POLLGROUPID INTO :OUT_POLLGROUPID; END ", MsSqlDbAccess.ObjectQualifier.ToUpper());
paramSb.Append("GROUPUSERID INTEGER = ?,");
paramSb.Append("POLLGROUPFLAGS INTEGER = ?,");
if (question.CategoryId > 0)
{
sb.Append("UPDATE ");
sb.Append(MsSqlDbAccess.GetObjectName("CATEGORY"));
sb.Append(" SET POLLGROUPID = :OUT_POLLGROUPID WHERE CATEGORYID = :I_CATEGORYID; ");
}
if (question.ForumId > 0)
{
sb.Append("UPDATE ");
sb.Append(MsSqlDbAccess.GetObjectName("FORUM"));
sb.Append(" SET POLLGROUPID = :OUT_POLLGROUPID WHERE FORUMID = :I_FORUMID; ");
}
if (question.TopicId > 0)
{
sb.Append("UPDATE ");
sb.Append(MsSqlDbAccess.GetObjectName("TOPIC"));
sb.Append(" SET POLLID = :OUT_POLLGROUPID WHERE TOPICID = :I_TOPICID; ");
}
// System.Text.StringBuilder paramSb = new System.Text.StringBuilder("EXECUTE BLOCK (");
// INSERT in poll
sb.Append(" INSERT INTO ");
sb.Append(MsSqlDbAccess.GetObjectName("POLL"));
if (question.Closes > DateTime.MinValue)
{
sb.Append("(POLLID,QUESTION, USERID, CLOSES,POLLGROUPID,FLAGS");
}
else
{
sb.Append("(POLLID,QUESTION, USERID,POLLGROUPID,FLAGS");
}
if (question.QuestionObjectPath.IsSet())
{
sb.Append(", OBJECTPATH");
}
if (question.QuestionMimeType.IsSet())
{
sb.Append(", MIMETYPE");
}
sb.Append(") VALUES(");
sb.AppendFormat("(SELECT NEXT VALUE FOR SEQ_{0}POLL_POLLID FROM RDB$DATABASE),", MsSqlDbAccess.ObjectQualifier.ToUpper());
sb.Append(":QUESTION");
paramSb.Append(" QUESTION VARCHAR(255) = ?,");
sb.Append(",:USERID");
paramSb.Append("USERID INTEGER = ?,");
if (question.Closes > DateTime.MinValue)
{
sb.Append(",:CLOSES");
paramSb.Append("CLOSES TIMESTAMP = ?,");
}
sb.Append(",:OUT_POLLGROUPID");
sb.Append(",:FLAGS");
paramSb.Append("FLAGS INTEGER = ?,");
if (question.QuestionObjectPath.IsSet())
{
sb.Append(",:QUESTIONOBJECTPATH");
paramSb.Append("OBJECTPATH VARCHAR(255) = ?,");
}
if (question.QuestionMimeType.IsSet())
{
sb.Append(",:QUESTIONMIMETYPE");
paramSb.Append("MIMETYPE VARCHAR(50) = ?,");
}
sb = new StringBuilder(sb.ToString().TrimEnd(','));
sb.Append(") RETURNING POLLID INTO :OUT_POLLID; ");
// The cycle through question reply choices to create prepare statement
// The cycle through question reply choices
for (uint choiceCount = 0; choiceCount < question.Choice.GetLength(0); choiceCount++)
{
if (!string.IsNullOrEmpty(question.Choice[0, choiceCount]))
{
sb.Append("INSERT INTO ");
sb.Append(MsSqlDbAccess.GetObjectName("CHOICE"));
sb.Append("(CHOICEID, POLLID,CHOICE,VOTES");
if (question.QuestionObjectPath.IsSet())
{
sb.Append(",OBJECTPATH");
}
if (question.QuestionMimeType.IsSet())
{
sb.Append(",MIMETYPE");
}
sb.Append(") VALUES(");
sb.AppendFormat("(SELECT NEXT VALUE FOR SEQ_{0}CHOICE_CHOICEID FROM RDB$DATABASE),",
MsSqlDbAccess.ObjectQualifier.ToUpper());
sb.AppendFormat(":OUT_POLLID,:CHOICE{0},:VOTES{0}", choiceCount);
if (question.QuestionObjectPath.IsSet())
{
sb.AppendFormat(",:CHOICEOBJECTPATH{0}", choiceCount);
}
if (question.QuestionMimeType.IsSet())
{
sb.AppendFormat(",:CHOICEMIMETYPE{0}", choiceCount);
}
sb.Append("); ");
paramSb.AppendFormat("CHOICE{0} VARCHAR(255) = ?,", choiceCount);
paramSb.AppendFormat("VOTES{0} INTEGER = ?,", choiceCount);
if (question.QuestionObjectPath.IsSet())
{
paramSb.AppendFormat("CHOICEOBJECTPATH{0} VARCHAR(255) = ?,", choiceCount);
}
if (question.QuestionMimeType.IsSet())
{
paramSb.AppendFormat("CHOICEMIMETYPE{0} VARCHAR(50) = ?,", choiceCount);
}
}
}
sb.Append(" SUSPEND; END;");
FbCommand cmd = new FbCommand();
cmd.CommandText = paramSb.ToString().TrimEnd(',') + ") " + sb.ToString();
MsSqlDbConnectionManager connMan = new MsSqlDbConnectionManager();
FbConnection con = connMan.OpenDBConnection;
FbTransaction trans = con.BeginTransaction(MsSqlDbAccess.IsolationLevel);
cmd.Transaction = trans;
// cmd.Prepare();
/* FbParameter ret = new FbParameter();
ret.ParameterName = "@OUT_POLLID";
ret.FbDbType = FbDbType.Integer;
ret.Direction = ParameterDirection.Output;
cmd.Parameters.Add(ret); */
object categoryId = DBNull.Value;
object forumId = DBNull.Value;
object topicId = DBNull.Value;
if (question.CategoryId > 0)
{
cmd.Parameters.Add(new FbParameter("I_CATEGORYID", FbDbType.Integer)).Value = question.CategoryId ??
categoryId;
}
if (question.ForumId > 0)
{
cmd.Parameters.Add(new FbParameter("I_FORUMID", FbDbType.Integer)).Value = question.ForumId ??
forumId;
}
if (question.TopicId > 0)
{
cmd.Parameters.Add(new FbParameter("I_TOPICID", FbDbType.Integer)).Value = question.TopicId ??
topicId;
}
cmd.Parameters.Add(new FbParameter("GROUPUSERID", FbDbType.Integer)).Value = question.UserId;
int pollGroupFlags = question.IsBound
? 0 | 2
: 0;
cmd.Parameters.Add(new FbParameter("POLLGROUPFLAGS", FbDbType.Integer)).Value = pollGroupFlags;
cmd.Parameters.Add(new FbParameter("QUESTION", FbDbType.VarChar)).Value = question.Question;
cmd.Parameters.Add(new FbParameter("USERID", FbDbType.Integer)).Value = question.UserId;
if (question.Closes > DateTime.MinValue)
{
cmd.Parameters.Add(new FbParameter("CLOSES", FbDbType.TimeStamp)).Value = question.Closes;
}
// set poll flags
int pollFlags = 0;
if (question.IsClosedBound)
{
pollFlags = pollFlags | 4;
}
if (question.AllowMultipleChoices)
{
pollFlags = pollFlags | 8;
}
if (question.ShowVoters)
{
pollFlags = pollFlags | 16;
}
if (question.AllowSkipVote)
{
pollFlags = pollFlags | 32;
}
cmd.Parameters.Add(new FbParameter("FLAGS", FbDbType.Integer)).Value = pollFlags;
if (question.QuestionObjectPath.IsSet())
{
cmd.Parameters.Add(new FbParameter("QUESTIONOBJECTPATH", FbDbType.VarChar)).Value =
question.QuestionObjectPath;
}
if (question.QuestionMimeType.IsSet())
{
cmd.Parameters.Add(new FbParameter("QUESTIONMIMETYPE", FbDbType.VarChar)).Value =
question.QuestionMimeType;
}
for (uint choiceCount1 = 0; choiceCount1 < question.Choice.GetLength(0); choiceCount1++)
{
if (!string.IsNullOrEmpty(question.Choice[0,choiceCount1]))
{
cmd.Parameters.Add(new FbParameter(String.Format("CHOICE{0}", choiceCount1), FbDbType.VarChar)).Value= question.Choice[0,choiceCount1];
cmd.Parameters.Add(new FbParameter(String.Format("VOTES{0}", choiceCount1), FbDbType.Integer)).Value = 0;
if (question.Choice[1, choiceCount1].IsSet())
{
cmd.Parameters.Add(new FbParameter(String.Format("CHOICEOBJECTPATH{0}", choiceCount1), FbDbType.VarChar)).Value =
question.Choice[1, choiceCount1].IsNotSet() ? String.Empty : question.Choice[1, choiceCount1];
}
if (question.Choice[2,choiceCount1].IsSet())
{
cmd.Parameters.Add(new FbParameter(String.Format("CHOICEMIMETYPE{0}", choiceCount1), FbDbType.VarChar)).Value =
question.Choice[2, choiceCount1].IsNotSet() ? String.Empty : question.Choice[2, choiceCount1];
}
}
}
// cmd.Prepare();
int? result = Convert.ToInt32(MsSqlDbAccess.Current.ExecuteScalar(cmd, true));
trans.Commit();
con.Close();
return result;
}
return null;
}
