本文整理汇总了C#中NpgsqlCommand.ExecuteBlind方法的典型用法代码示例。如果您正苦于以下问题:C# NpgsqlCommand.ExecuteBlind方法的具体用法?C# NpgsqlCommand.ExecuteBlind怎么用?C# NpgsqlCommand.ExecuteBlind使用的例子?那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类NpgsqlCommand
的用法示例。
在下文中一共展示了NpgsqlCommand.ExecuteBlind方法的11个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: ValidateUser
//
// MembershipProvider.ValidateUser
//
public override bool ValidateUser(string username, string password)
{
bool isValid = false;
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"SELECT Password, is_approved FROM " + tableName + "" +
" WHERE user_name = @user_name AND application_name = @application_name AND is_locked_out = false", conn);
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
NpgsqlDataReader reader = null;
bool isApproved = false;
string pwd = "";
try
{
conn.Open();
using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.HasRows)
{
reader.Read();
pwd = reader.GetString(0);
isApproved = reader.GetBoolean(1);
}
else
{
return false;
}
reader.Close();
}
if (CheckPassword(password, pwd))
{
if (isApproved)
{
isValid = true;
NpgsqlCommand updateCmd =
new NpgsqlCommand(
"UPDATE " + tableName + " SET last_login_date = @last_login_date, last_activity_date = @last_activity_date" +
" WHERE user_name = @user_name AND application_name = @application_name", conn);
updateCmd.Parameters.Add("@last_login_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
updateCmd.Parameters.Add("@last_activity_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
// fixed by Alex .ToString("yyyy/MM/dd HH:mm:ss");
updateCmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
updateCmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
updateCmd.ExecuteBlind();
}
}
else
{
cmd.Dispose();
conn.Close();
UpdateFailureCount(username, "password");
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "ValidateUser");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
cmd.Dispose();
conn.Close();
}
return isValid;
}
示例2: GetUser
/// <summary>
/// MembershipProvider.GetUser(string, bool)
/// </summary>
/// <param name="username"></param>
/// <param name="userIsOnline"></param>
/// <returns></returns>
public override MembershipUser GetUser(string username, bool userIsOnline)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
string.Format("SELECT UserId, user_name, Email, password_question, Comment, is_approved, is_locked_out, creation_date, last_login_date, last_activity_date, last_password_changed_date, last_locked_out_date FROM {0} WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
MembershipUser u = null;
NpgsqlDataReader reader = null;
try
{
conn.Open();
using (reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
u = GetUserFromReader(reader);
reader.Close();
if (userIsOnline)
{
NpgsqlCommand updateCmd =
new NpgsqlCommand(
string.Format("UPDATE {0} SET last_activity_date = @last_activity_date WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);
updateCmd.Parameters.Add("@last_activity_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
// fixed by Alex .ToString("yyyy/MM/dd HH:mm:ss");
updateCmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
updateCmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
updateCmd.ExecuteBlind();
}
}
reader.Close();
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetUser(String, Boolean)");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
cmd.Dispose();
conn.Close();
}
return u;
}
示例3: RemoveUsersFromRoles
//
// RoleProvider.RemoveUsersFromRoles
//
public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
if (!IsUserInRole(username, rolename))
{
throw new ProviderException("User is not in role.");
}
}
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"DELETE FROM " + usersInRolesTable + "" +
" WHERE user_name = @user_name AND role_name = @role_name AND application_name = @application_name", conn);
NpgsqlParameter userParm = cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255);
NpgsqlParameter roleParm = cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255);
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
NpgsqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
userParm.Value = username;
roleParm.Value = rolename;
cmd.ExecuteBlind();
}
}
tran.Commit();
}
catch (NpgsqlException e)
{
try
{
if (tran != null)
{
tran.Rollback();
}
}
catch
{
}
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "RemoveUsersFromRoles");
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
}
示例4: UpdateUser
//
// MembershipProvider.UpdateUser
//
public override void UpdateUser(MembershipUser user)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"UPDATE " + tableName + "" + " SET Email = @Email, Comment = @Comment," + " is_approved = @is_approved" +
" WHERE user_name = @user_name AND application_name = @application_name", conn);
cmd.Parameters.Add("@Email", NpgsqlDbType.Text, 128).Value = user.Email;
cmd.Parameters.Add("@Comment", NpgsqlDbType.Text, 255).Value = user.Comment;
cmd.Parameters.Add("@is_approved", NpgsqlDbType.Boolean).Value = user.IsApproved;
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = user.UserName;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
try
{
conn.Open();
cmd.ExecuteBlind();
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "UpdateUser");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
}
示例5: CreateRole
//
// RoleProvider.CreateRole
//
public override void CreateRole(string rolename)
{
if (rolename.IndexOf(',') > 0)
{
throw new ArgumentException("Role names cannot contain commas.");
}
if (RoleExists(rolename))
{
throw new ProviderException("Role name already exists.");
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"INSERT INTO " + rolesTable + "" + " (role_name, application_name) " + " Values(@role_name, @application_name)",
conn);
cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
try
{
conn.Open();
cmd.ExecuteBlind();
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "CreateRole");
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
}
示例6: DeleteRole
//
// RoleProvider.DeleteRole
//
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role does not exist.");
}
if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)
{
throw new ProviderException("Cannot delete a populated role.");
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"DELETE FROM " + rolesTable + "" + " WHERE role_name = @role_name AND application_name = @application_name", conn);
cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
NpgsqlCommand cmd2 =
new NpgsqlCommand(
"DELETE FROM " + usersInRolesTable + "" + " WHERE role_name = @role_name AND application_name = @application_name",
conn);
cmd2.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename;
cmd2.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
NpgsqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd2.Transaction = tran;
cmd2.ExecuteBlind();
cmd.ExecuteBlind();
tran.Commit();
}
catch (NpgsqlException e)
{
try
{
if (tran != null)
{
tran.Rollback();
}
}
catch
{
}
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteRole");
return false;
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
return true;
}
示例7: AddUsersToRoles
//
// System.Web.Security.RoleProvider methods.
//
//
// RoleProvider.AddUsersToRoles
//
public override void AddUsersToRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
if (username.IndexOf(',') > 0)
{
throw new ArgumentException("User names cannot contain commas.");
}
foreach (string rolename in rolenames)
{
if (IsUserInRole(username, rolename))
{
throw new ProviderException("User is already in role.");
}
}
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"INSERT INTO " + usersInRolesTable + "" + " (user_name, role_name, application_name) " +
" Values(@user_name, @role_name, @application_name)", conn);
NpgsqlParameter userParm = cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255);
NpgsqlParameter roleParm = cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255);
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
NpgsqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
userParm.Value = username;
roleParm.Value = rolename;
cmd.ExecuteBlind();
}
}
tran.Commit();
}
catch (NpgsqlException e)
{
try
{
if (tran != null)
{
tran.Rollback();
}
}
catch
{
}
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "AddUsersToRoles");
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
}
示例8: UpdateLastActivityDate
/// <summary>
/// Updates last activity date
/// </summary>
/// <param name="conn"></param>
/// <param name="userId"></param>
private static void UpdateLastActivityDate(NpgsqlConnection conn, Guid userId)
{
NpgsqlCommand cmd =
new NpgsqlCommand("UPDATE aspnet_Users SET LastActivityDate = @LastUpdatedDate WHERE UserId = '" + userId + "'",
conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@LastUpdatedDate", NpgsqlDbType.Timestamp).Value = DateTime.UtcNow;
try
{
cmd.ExecuteBlind();
}
finally
{
cmd.Dispose();
}
}
示例9: DeleteProfiles
/// <summary>
/// Deletes Profiles
/// </summary>
/// <param name="usernames"></param>
/// <returns></returns>
public override int DeleteProfiles(string[] usernames)
{
if (usernames == null || usernames.Length < 1)
{
return 0;
}
int numProfilesDeleted = 0;
bool beginTranCalled = false;
NpgsqlConnection conn = null;
try
{
conn = new NpgsqlConnection(_NpgsqlConnectionString);
conn.Open();
NpgsqlCommand cmd;
int numUsersRemaing = usernames.Length;
while (numUsersRemaing > 0)
{
cmd = new NpgsqlCommand(String.Empty, conn);
cmd.Parameters.Add("@UserName0", usernames[usernames.Length - numUsersRemaing]);
StringBuilder allUsers = new StringBuilder("@UserName0");
numUsersRemaing--;
int userIndex = 1;
for (int iter = usernames.Length - numUsersRemaing; iter < usernames.Length; iter++)
{
// REVIEW: Should we check length of command string instead of parameter lengths?
if (allUsers.Length + usernames[iter].Length + 3 >= 4000)
{
break;
}
string userNameParam = "@UserName" + userIndex;
allUsers.Append(",");
allUsers.Append(userNameParam);
cmd.Parameters.Add(userNameParam, usernames[iter]);
numUsersRemaing--;
++userIndex;
}
// We don't need to start a transaction if we can finish this in one sql command
if (!beginTranCalled && numUsersRemaing > 0)
{
NpgsqlCommand beginCmd = new NpgsqlCommand("BEGIN TRANSACTION", conn);
beginCmd.ExecuteBlind();
beginTranCalled = true;
}
cmd.CommandText =
string.Format(
"DELETE FROM {0} WHERE UserId IN ( SELECT u.UserId FROM vw_aspnet_Users u WHERE u.ApplicationId = '{1}' AND u.UserName IN ({2}))",
_table, AppId, allUsers.ToString());
cmd.CommandTimeout = CommandTimeout;
numProfilesDeleted += cmd.ExecuteNonQuery();
}
if (beginTranCalled)
{
cmd = new NpgsqlCommand("COMMIT TRANSACTION", conn);
cmd.ExecuteBlind();
beginTranCalled = false;
}
}
catch
{
if (beginTranCalled)
{
NpgsqlCommand cmd = new NpgsqlCommand("ROLLBACK TRANSACTION", conn);
cmd.ExecuteBlind();
beginTranCalled = false;
}
throw;
}
finally
{
if (conn != null)
{
conn.Close();
conn = null;
}
}
return numProfilesDeleted;
}
示例10: SetPropertyValues
//.........这里部分代码省略.........
try
{
reader = cmd.ExecuteReader();
if (reader.Read())
{
tmpUserId = reader.GetString(0);
if (!string.IsNullOrEmpty(tmpUserId)) // != null && tmpUserId != "")
{
userId = new Guid(tmpUserId);
}
else
{
userId = Guid.NewGuid();
}
}
else
{
reader.Close();
cmd.Dispose();
reader = null;
cmd =
new NpgsqlCommand(
" INSERT INTO aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate) " +
" VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate) ", conn);
userId = Guid.NewGuid();
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@UserId", NpgsqlDbType.Text, 36).Value = userId.ToString();
cmd.Parameters.Add("@ApplicationId", NpgsqlDbType.Text, 36).Value = AppId.ToString();
cmd.Parameters.Add("@UserName", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@IsUserAnonymous", NpgsqlDbType.Boolean).Value = !userIsAuthenticated;
cmd.Parameters.Add("@LastActivityDate", NpgsqlDbType.Timestamp).Value = DateTime.UtcNow;
cmd.ExecuteBlind();
}
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
cmd.Dispose();
}
// Figure out if the row already exists in the table and use appropriate SELECT/UPDATE
cmd = new NpgsqlCommand("SELECT * FROM " + _table + " WHERE UserId = @UserId", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@UserId", NpgsqlDbType.Text, 36).Value = userId.ToString();
NpgsqlDataReader readerSelect = null;
bool IfExists = false;
try
{
using (readerSelect = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (readerSelect.HasRows)
{
// IF EXISTS (SELECT * FROM aspnet_users WHERE UserId = '')
IfExists = true;
}
else
{
IfExists = false;
}
readerSelect.Close();
示例11: GetProfilesForQuery
/// <summary>
/// Gets Profiles for Query
/// </summary>
/// <param name="insertArgs"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="insertQuery"></param>
/// <param name="totalRecords"></param>
/// <returns></returns>
private ProfileInfoCollection GetProfilesForQuery(NpgsqlParameter[] insertArgs, int pageIndex, int pageSize,
StringBuilder insertQuery, out int totalRecords)
{
if (pageIndex < 0)
{
throw new ArgumentException("pageIndex");
}
if (pageSize < 1)
{
throw new ArgumentException("pageSize");
}
long lowerBound = (long) pageIndex*pageSize;
long upperBound = lowerBound + pageSize - 1;
if (upperBound > Int32.MaxValue)
{
throw new ArgumentException("pageIndex and pageSize");
}
NpgsqlConnection conn = null;
NpgsqlDataReader reader = null;
NpgsqlCommand cmd = null;
try
{
conn = new NpgsqlConnection(_NpgsqlConnectionString);
conn.Open();
StringBuilder cmdStr = new StringBuilder(200);
// Create a temp table TO store the select results
cmd =
new NpgsqlCommand(
"CREATE TABLE #PageIndexForProfileUsers(IndexId int IDENTITY (0, 1) NOT NULL, UserId varchar(36))", conn);
cmd.CommandTimeout = CommandTimeout;
cmd.ExecuteBlind();
cmd.Dispose();
insertQuery.Append(" ORDER BY UserName");
cmd = new NpgsqlCommand(insertQuery.ToString(), conn);
cmd.CommandTimeout = CommandTimeout;
if (insertArgs != null)
{
foreach (NpgsqlParameter arg in insertArgs)
{
cmd.Parameters.Add(arg);
}
}
cmd.ExecuteBlind();
cmd.Dispose();
cmdStr = new StringBuilder(200);
cmdStr.Append("SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate FROM vw_aspnet_Users u, ").
Append(_table);
cmdStr.Append(" p, #PageIndexForProfileUsers i WHERE u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= ");
cmdStr.Append(lowerBound).Append(" AND i.IndexId <= ").Append(upperBound);
cmd = new NpgsqlCommand(cmdStr.ToString(), conn);
cmd.CommandTimeout = CommandTimeout;
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
ProfileInfoCollection profiles = new ProfileInfoCollection();
while (reader.Read())
{
string username;
DateTime dtLastActivity, dtLastUpdated = DateTime.UtcNow;
bool isAnon;
username = reader.GetString(0);
isAnon = reader.GetBoolean(1);
dtLastActivity = DateTime.SpecifyKind(reader.GetDateTime(2), DateTimeKind.Utc);
dtLastUpdated = DateTime.SpecifyKind(reader.GetDateTime(3), DateTimeKind.Utc);
profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, 0));
}
totalRecords = profiles.Count;
if (reader != null)
{
reader.Close();
reader = null;
}
cmd.Dispose();
cmd = new NpgsqlCommand("DROP TABLE #PageIndexForProfileUsers", conn);
cmd.ExecuteBlind();
return profiles;
}
finally
{
if (reader != null)
{
//.........这里部分代码省略.........