本文整理汇总了C#中YAF.Classes.Data.MsSqlDbConnectionManager类的典型用法代码示例。如果您正苦于以下问题：C# MsSqlDbConnectionManager类的具体用法？C# MsSqlDbConnectionManager怎么用？C# MsSqlDbConnectionManager使用的例子？那么恭喜您, 这里精选的类代码示例或许可以为您提供帮助。
MsSqlDbConnectionManager类属于YAF.Classes.Data命名空间，在下文中一共展示了MsSqlDbConnectionManager类的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: system_initialize_executescripts
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));
}
}
}
}
}示例2: forum_moderatelist
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;
}
}
}
}示例3: system_initialize_executescripts
/// <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));
}
}
}
//.........这里部分代码省略.........
示例4: db_reindex_new
/// <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;
}
}示例5: db_shrink_new
/// <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;
}
}示例6: user_register
/// <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;
}示例7: system_initialize_executescripts
/// <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));
}
}
}
}
}示例8: system_initialize_replace_entries
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))
//.........这里部分代码省略.........
示例9: db_getstats_new
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;
}
}示例10: poll_save
/// <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)
{
//.........这里部分代码省略.........
示例11: SetProfileProperties
/// <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(",[email protected]_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(",[email protected]_LastActivity");
cmd.Parameters.AddWithValue("@i_LastActivity", DateTime.UtcNow);
columnStr.Append(",ApplicationName ");
valueStr.Append(",@i_ApplicationName");
setStr.Append(",[email protected]_ApplicationName");
cmd.Parameters.Add("i_ApplicationName", MySqlDbType.VarChar).Value = appName;
columnStr.Append(",IsAnonymous ");
valueStr.Append(",@i_IsAnonymous");
//.........这里部分代码省略.........
示例12: db_runsql_new
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;
}
}示例13: db_reindex_new
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;
}
}示例14: db_getstats_new
/// <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;
}
}示例15: db_getstats_new
/// <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;
}
}