本文整理汇总了C#中NpgsqlCommand类的典型用法代码示例。如果您正苦于以下问题：C# NpgsqlCommand类的具体用法？C# NpgsqlCommand怎么用？C# NpgsqlCommand使用的例子？那么恭喜您, 这里精选的类代码示例或许可以为您提供帮助。
NpgsqlCommand类属于命名空间，在下文中一共展示了NpgsqlCommand类的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: ExecuteNonQuery
/// <summary>
/// 执行 Transact-SQL 语句并返回受影响的行数。
/// </summary>
public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
NpgsqlCommand cmd = new NpgsqlCommand();
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}示例2: DeriveParametersVarious
public void DeriveParametersVarious()
{
using (var conn = OpenConnection())
{
// This function returns record because of the two Out (InOut & Out) parameters
conn.ExecuteNonQuery(@"
CREATE OR REPLACE FUNCTION pg_temp.func(IN param1 INT, OUT param2 text, INOUT param3 INT) RETURNS record AS
'
BEGIN
param2 = ''sometext'';
param3 = param1 + param3;
END;
' LANGUAGE 'plpgsql';
");
var cmd = new NpgsqlCommand("pg_temp.func", conn) { CommandType = CommandType.StoredProcedure };
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(3));
Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Output));
Assert.That(cmd.Parameters[2].Direction, Is.EqualTo(ParameterDirection.InputOutput));
cmd.Parameters[0].Value = 5;
cmd.Parameters[2].Value = 4;
cmd.ExecuteNonQuery();
Assert.That(cmd.Parameters[0].Value, Is.EqualTo(5));
Assert.That(cmd.Parameters[1].Value, Is.EqualTo("sometext"));
Assert.That(cmd.Parameters[2].Value, Is.EqualTo(9));
}
}示例3: ExecuteQuery
/// <summary>
/// 在事务中执行查询，返回DataSet
/// </summary>
public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
NpgsqlCommand cmd = new NpgsqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
return ds;
}示例4: RecordWithNonIntField
public void RecordWithNonIntField()
{
using (var conn = OpenConnection())
using (var cmd = new NpgsqlCommand("SELECT ('one', 2)", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
var record = reader.GetFieldValue<object[]>(0);
Assert.That(record[0], Is.EqualTo("one"));
Assert.That(record[1], Is.EqualTo(2));
}
}示例5: Bug1285
public void Bug1285()
{
using (var conn = OpenConnection())
using (var cmd = new NpgsqlCommand { Connection = conn })
{
cmd.CommandText = Bug1285CreateStatement;
cmd.ExecuteNonQuery();
cmd.CommandText = Bug1285SelectStatement;
cmd.Parameters.Add(new NpgsqlParameter("@1", Guid.NewGuid()));
cmd.ExecuteNonQuery();
}
}示例6: AllowDBNull
public void AllowDBNull()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (nullable INTEGER, non_nullable INTEGER NOT NULL)");
using (var cmd = new NpgsqlCommand("SELECT nullable,non_nullable,8 FROM data", conn))
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
{
var columns = reader.GetColumnSchema();
Assert.That(columns[0].AllowDBNull, Is.True);
Assert.That(columns[1].AllowDBNull, Is.False);
Assert.That(columns[2].AllowDBNull, Is.Null);
}
}
}示例7: BaseCatalogName
public void BaseCatalogName()
{
var dbName = new NpgsqlConnectionStringBuilder(ConnectionString).Database;
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (foo INTEGER)");
using (var cmd = new NpgsqlCommand("SELECT foo,8 FROM data", conn))
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
var columns = reader.GetColumnSchema();
Assert.That(columns[0].BaseCatalogName, Is.EqualTo(dbName));
Assert.That(columns[1].BaseCatalogName, Is.EqualTo(dbName));
}
}
}示例8: BaseColumnName
public void BaseColumnName()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (foo INTEGER)");
using (var cmd = new NpgsqlCommand("SELECT foo,8 AS bar,8,'8'::VARCHAR(10) FROM data", conn))
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
var columns = reader.GetColumnSchema();
Assert.That(columns[0].BaseColumnName, Is.EqualTo("foo"));
Assert.That(columns[1].BaseColumnName, Is.EqualTo("bar"));
Assert.That(columns[2].BaseColumnName, Is.Null);
Assert.That(columns[3].BaseColumnName, Is.EqualTo("varchar"));
}
}
}示例9: PrimaryKeyFieldMetadataSupport
public void PrimaryKeyFieldMetadataSupport()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (id SERIAL PRIMARY KEY, serial SERIAL)");
using (var command = new NpgsqlCommand("SELECT * FROM data", conn))
{
using (var dr = command.ExecuteReader(CommandBehavior.KeyInfo))
{
dr.Read();
var metadata = dr.GetSchemaTable();
var key = metadata.Rows.Cast<DataRow>().Single(r => (bool)r["IsKey"]);
Assert.That(key["ColumnName"], Is.EqualTo("id"));
}
}
}
}示例10: ExecuteReader
/// <summary>
/// 执行查询，返回DataReader
/// </summary>
public DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
NpgsqlCommand cmd = new NpgsqlCommand();
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
NpgsqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}示例11: ManyParametersWithMixedFormatCode
public void ManyParametersWithMixedFormatCode()
{
using (var conn = OpenConnection())
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
var sb = new StringBuilder("SELECT @text_param");
cmd.Parameters.AddWithValue("@text_param", "some_text");
for (var i = 0; i < conn.BufferSize; i++)
{
var paramName = $"@binary_param{i}";
sb.Append(",");
sb.Append(paramName);
cmd.Parameters.AddWithValue(paramName, 8);
}
cmd.CommandText = sb.ToString();
Assert.That(() => cmd.ExecuteNonQuery(), Throws.Exception
.TypeOf<PostgresException>()
.With.Property(nameof(PostgresException.SqlState)).EqualTo("54000")
);
}
}示例12: PrimaryKeyFieldsMetadataSupport
public void PrimaryKeyFieldsMetadataSupport()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("DROP TABLE IF EXISTS DATA2 CASCADE");
conn.ExecuteNonQuery(@"CREATE TEMP TABLE DATA2 (
field_pk1 INT2 NOT NULL,
field_pk2 INT2 NOT NULL,
field_serial SERIAL,
CONSTRAINT data2_pkey PRIMARY KEY (field_pk1, field_pk2)
) WITH OIDS");
using (var command = new NpgsqlCommand("SELECT * FROM DATA2", conn))
using (var dr = command.ExecuteReader(CommandBehavior.KeyInfo))
{
dr.Read();
var keyColumns =
dr.GetSchemaTable().Rows.Cast<DataRow>().Where(r => (bool)r["IsKey"]).ToArray();
Assert.That(keyColumns, Has.Length.EqualTo(2));
Assert.That(keyColumns.Count(c => (string)c["ColumnName"] == "field_pk1"), Is.EqualTo(1));
Assert.That(keyColumns.Count(c => (string)c["ColumnName"] == "field_pk2"), Is.EqualTo(1));
}
}
}示例13: DeriveParametersInOnly
public void DeriveParametersInOnly()
{
using (var conn = OpenConnection())
{
// This function returns record because of the two Out (InOut & Out) parameters
conn.ExecuteNonQuery(@"
CREATE OR REPLACE FUNCTION pg_temp.func(IN param1 INT, IN param2 INT) RETURNS int AS
'
BEGIN
RETURN param1 + param2;
END;
' LANGUAGE 'plpgsql';
");
var cmd = new NpgsqlCommand("pg_temp.func", conn) { CommandType = CommandType.StoredProcedure };
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(2));
Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Input));
cmd.Parameters[0].Value = 5;
cmd.Parameters[1].Value = 4;
Assert.That(cmd.ExecuteScalar(), Is.EqualTo(9));
}
}示例14: DeleteUser
//
// MembershipProvider.DeleteUser
//
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
string.Format("DELETE 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;
int rowsAffected = 0;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (deleteAllRelatedData)
{
// Process commands to delete all data for the user in the database.
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteUser");
// 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();
}
return (rowsAffected > 0); }示例15: CreateUser
//
// MembershipProvider.CreateUser
//
public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion,
string passwordAnswer, bool isApproved, object providerUserKey,
out MembershipCreateStatus status)
{
ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);
OnValidatingPassword(args);
if (args.Cancel)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
if (RequiresUniqueEmail && !string.IsNullOrEmpty(GetUserNameByEmail(email)))
{
status = MembershipCreateStatus.DuplicateEmail;
return null;
}
MembershipUser u = GetUser(username, false);
if (u == null)
{
DateTime createDate = DateTime.Now;
if (providerUserKey == null)
{
providerUserKey = Guid.NewGuid();
}
else
{
if (!(providerUserKey is Guid))
{
status = MembershipCreateStatus.InvalidProviderUserKey;
return null;
}
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
string.Format("INSERT INTO {0} (UserId, user_name, Password, Email, password_question, password_answer, is_approved, Comment, creation_date, last_password_changed_date, last_activity_date, application_name, is_locked_out, last_locked_out_date, failed_password_attempt_count, failed_password_attempt_window_start, failed_password_answer_attempt_count, failed_password_answer_attempt_window_start) Values(@UserId, @user_name, @Password, @Email, @password_question, @password_answer, @is_approved, @Comment, @creation_date, @last_password_changed_date, @last_activity_date, @application_name, @is_locked_out, @last_locked_out_date, @failed_password_attempt_count, @failed_password_attempt_window_start, @failed_password_answer_attempt_count, @failed_password_answer_attempt_window_start)", tableName), conn);
cmd.Parameters.Add("@UserId", NpgsqlDbType.Text).Value = providerUserKey.ToString();
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@Password", NpgsqlDbType.Text, 255).Value = EncodePassword(password);
cmd.Parameters.Add("@Email", NpgsqlDbType.Text, 128).Value = email;
cmd.Parameters.Add("@password_question", NpgsqlDbType.Text, 255).Value = passwordQuestion;
cmd.Parameters.Add("@password_answer", NpgsqlDbType.Text, 255).Value = passwordAnswer == null
? null
: EncodePassword(passwordAnswer);
cmd.Parameters.Add("@is_approved", NpgsqlDbType.Boolean).Value = isApproved;
cmd.Parameters.Add("@Comment", NpgsqlDbType.Text, 255).Value = "";
cmd.Parameters.Add("@creation_date", NpgsqlDbType.Timestamp).Value = createDate;
cmd.Parameters.Add("@last_password_changed_date", NpgsqlDbType.Timestamp).Value = createDate;
cmd.Parameters.Add("@last_activity_date", NpgsqlDbType.Timestamp).Value = createDate;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
cmd.Parameters.Add("@is_locked_out", NpgsqlDbType.Boolean).Value = false; //false
cmd.Parameters.Add("@last_locked_out_date", NpgsqlDbType.Timestamp).Value = createDate;
cmd.Parameters.Add("@failed_password_attempt_count", NpgsqlDbType.Integer).Value = 0;
cmd.Parameters.Add("@failed_password_attempt_window_start", NpgsqlDbType.Timestamp).Value = createDate;
cmd.Parameters.Add("@failed_password_answer_attempt_count", NpgsqlDbType.Integer).Value = 0;
cmd.Parameters.Add("@failed_password_answer_attempt_window_start", NpgsqlDbType.Timestamp).Value = createDate;
try
{
conn.Open();
int recAdded = cmd.ExecuteNonQuery();
if (recAdded > 0)
{
status = MembershipCreateStatus.Success;
}
else
{
status = MembershipCreateStatus.UserRejected;
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "CreateUser");
}
status = MembershipCreateStatus.ProviderError;
}
finally
{
cmd.Dispose();
conn.Close();
}
//.........这里部分代码省略.........