本文整理汇总了C#中System.Data.SqlClient.SqlConnection.?.Close方法的典型用法代码示例。如果您正苦于以下问题：C# SqlConnection.?.Close方法的具体用法？C# SqlConnection.?.Close怎么用？C# SqlConnection.?.Close使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类System.Data.SqlClient.SqlConnection的用法示例。
在下文中一共展示了SqlConnection.?.Close方法的14个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: GetAllItemStatuss
public IList<ItemStatusEntity> GetAllItemStatuss()
{
List<ItemStatusEntity> itemStatusList = new List<ItemStatusEntity>();
try
{
_conn = new SqlConnection(ConnString);
_conn.Open();
string sql = "SELECT itemStatusUd, status, updateTimestamp, updatePersonFK FROM DBO.itemStatus;";
var cmd = new SqlCommand(sql, _conn);
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var itemStatus = new ItemStatusEntity()
{
itemStatusId = rdr.IsDBNull(rdr.GetOrdinal("itemStatusId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemStatusId")),
status = rdr.IsDBNull(rdr.GetOrdinal("status")) ? null : rdr.GetString(rdr.GetOrdinal("status")),
updateTimestamp = rdr.IsDBNull(rdr.GetOrdinal("updateTimestamp")) ? new DateTime() : rdr.GetDateTime(rdr.GetOrdinal("updateTimestamp")),
updatePersonFK = rdr.IsDBNull(rdr.GetOrdinal("updatePersonFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("updatePersonFK"))
};
itemStatusList.Add(itemStatus);
}
}
finally
{
_conn?.Close();
}
return itemStatusList;
}示例2: Test
/// <summary>
/// Test if connection is OK
/// </summary>
/// <returns>Bool true, if it is ok</returns>
public static void Test()
{
SqlConnection connection = null;
try
{
connection = new SqlConnection(ConnectString);
connection.Open();
}
catch
{
throw;
}
finally
{
connection?.Close();
}
}示例3: ContactExists
public bool ContactExists(string email)
{
try
{
const string sql = "SELECT COUNT([Id]) AS ContactsId FROM Contacts WHERE email = @Email;";
_conn = new SqlConnection(ConnString);
_conn.Open();
var cmd = new SqlCommand(sql, _conn);
cmd.Parameters.Add("@CEmail", SqlDbType.VarChar);
cmd.Parameters["@Email"].Value = email;
return ((int)cmd.ExecuteScalar() >= 1);
}
finally
{
_conn?.Close();
}
}示例4: GetAllContacts
public IList<Contact> GetAllContacts(string query, int page, int pageSize)
{
var contacts = new List<Contact>();
try
{
_conn = new SqlConnection(ConnString);
_conn.Open();
var cmd = new SqlCommand(_selectSql, _conn);
var paramQuery = new SqlParameter
{
ParameterName = "@query",
Value = "%" + query + "%"
};
cmd.Parameters.Add(paramQuery);
var paramStartRow = new SqlParameter
{
ParameterName = "@StartRow",
Value = page * pageSize
};
cmd.Parameters.Add(paramStartRow);
var paramEndRow = new SqlParameter
{
ParameterName = "@EndRow",
Value = (page * pageSize) + pageSize
};
cmd.Parameters.Add(paramEndRow);
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var contact = new Contact()
{
Id = rdr.IsDBNull(rdr.GetOrdinal("Id")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("Id")),
FirstName = rdr.IsDBNull(rdr.GetOrdinal("FirstName")) ? null : rdr.GetString(rdr.GetOrdinal("FirstName")),
LastName = rdr.IsDBNull(rdr.GetOrdinal("LastName")) ? "" : rdr.GetString(rdr.GetOrdinal("LastName")),
Address = rdr.IsDBNull(rdr.GetOrdinal("Address")) ? null : rdr.GetString(rdr.GetOrdinal("Address")),
ZipCode = rdr.IsDBNull(rdr.GetOrdinal("ZipCode")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("ZipCode")),
City = rdr.IsDBNull(rdr.GetOrdinal("City")) ? null : rdr.GetString(rdr.GetOrdinal("City")),
Telephone = rdr.IsDBNull(rdr.GetOrdinal("Telephone")) ? null : rdr.GetString(rdr.GetOrdinal("Telephone")),
Email = rdr.IsDBNull(rdr.GetOrdinal("Email")) ? null : rdr.GetString(rdr.GetOrdinal("Email")),
BirthDate = rdr.IsDBNull(rdr.GetOrdinal("BirthDate")) ? null as DateTime? : rdr.GetDateTime(rdr.GetOrdinal("BirthDate"))
};
contacts.Add(contact);
}
}
finally
{
_conn?.Close();
}
return contacts;
}示例5: Update
public void Update(int id, Contact contact)
{
var contactToUpdate = GetContactById(id);
if (contactToUpdate == null)
{
throw new Exception("Contact does not exist in database");
}
try
{
_conn = new SqlConnection(ConnString);
var cmd = _conn.CreateCommand();
cmd.CommandText = @"UPDATE Contacts SET [FirstName][email protected],
[LastName][email protected],
[Address][email protected],
[ZipCode][email protected],
[City][email protected],
[Telephone][email protected],
[Email][email protected],
[BirthDate][email protected]
WHERE [email protected]";
cmd.Parameters.Add("@Id", SqlDbType.Int);
cmd.Parameters["@Id"].Value = id;
cmd.Parameters.Add("@paramFirstName", SqlDbType.VarChar);
cmd.Parameters["@paramFirstName"].Value = contact.FirstName;
cmd.Parameters.Add("@paramLastName", SqlDbType.VarChar);
cmd.Parameters["@paramLastName"].Value = contact.LastName;
cmd.Parameters.Add("@paramAddress", SqlDbType.VarChar);
cmd.Parameters["@paramAddress"].Value = contact.Address;
cmd.Parameters.Add("@paramZipCode", SqlDbType.Int);
cmd.Parameters["@paramZipCode"].Value = contact.ZipCode;
cmd.Parameters.Add("@paramCity", SqlDbType.VarChar);
cmd.Parameters["@paramCity"].Value = contact.City;
cmd.Parameters.Add("@paramTelephone", SqlDbType.VarChar);
cmd.Parameters["@paramTelephone"].Value = contact.Telephone;
cmd.Parameters.Add("@paramEmail", SqlDbType.VarChar);
cmd.Parameters["@paramEmail"].Value = contact.Email;
cmd.Parameters.Add("@paramBirthDate", SqlDbType.DateTime);
cmd.Parameters["@paramBirthDate"].Value = contact.BirthDate;
_conn.Open();
var number = cmd.ExecuteNonQuery();
if (number != 1)
{
throw new Exception($"No Contacts were updated with Id: {id}");
}
}
finally
{
_conn?.Close();
}
}示例6: Insert
public long Insert(Contact contact)
{
try
{
var contactExists = GetContactByEmail(contact.Email);
if (contactExists != null)
{
throw new Exception($"Entity {contact.Email} already exists in database!");
}
_conn = new SqlConnection(ConnString);
var cmd = _conn.CreateCommand();
cmd.CommandText =
@"INSERT INTO[dbo].[Contacts] (FirstName, LastName, [Address], ZipCode, City, Telephone, Email, BirthDate)
VALUES(@FirstName, @LastName, @Address, @ZipCode, @City, @Telephone, @Email, @BirthDate);SELECT CAST(scope_identity() AS int)";
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar);
cmd.Parameters["@FirstName"].Value = contact.FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar);
cmd.Parameters["@LastName"].Value = contact.LastName;
cmd.Parameters.Add("@Address", SqlDbType.VarChar);
cmd.Parameters["@Address"].Value = contact.Address;
cmd.Parameters.Add("@ZipCode", SqlDbType.Int);
cmd.Parameters["@ZipCode"].Value = contact.ZipCode;
cmd.Parameters.Add("@City", SqlDbType.VarChar);
cmd.Parameters["@City"].Value = contact.City;
cmd.Parameters.Add("@Telephone", SqlDbType.VarChar);
cmd.Parameters["@Telephone"].Value = contact.Telephone;
cmd.Parameters.Add("@Email", SqlDbType.VarChar);
cmd.Parameters["@Email"].Value = contact.Email;
cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime);
cmd.Parameters["@BirthDate"].Value = contact.BirthDate;
_conn.Open();
try
{
return long.Parse(cmd.ExecuteScalar().ToString());
}
catch (Exception)
{
throw new Exception($"Entity {contact.FirstName} {contact.LastName} not inserted in database!");
}
}
finally
{
_conn?.Close();
}
}示例7: GetNumberOfContacts
public long GetNumberOfContacts(string query)
{
try
{
const string sql = "SELECT COUNT(*) FROM Contacts where FirstName LIKE @query OR LastName LIKE @query";
_conn = new SqlConnection(ConnString);
_conn.Open();
var cmd = new SqlCommand(sql, _conn);
var paramQuery = new SqlParameter
{
ParameterName = "@query",
Value = "%" + query + "%"
};
cmd.Parameters.Add(paramQuery);
return long.Parse(cmd.ExecuteScalar().ToString());
}
finally
{
_conn?.Close();
}
}示例8: GetContactById
public Contact GetContactById(int id)
{
Contact contact = null;
try
{
_conn = new SqlConnection(ConnString);
_conn.Open();
const string sql = "SELECT Id, FirstName, LastName, [Address], ZipCode, City, Telephone, Email, BirthDate FROM Contacts WHERE [Id] = @Id;";
var cmd = new SqlCommand(sql, _conn);
var paramId = new SqlParameter
{
ParameterName = "@Id",
Value = id
};
cmd.Parameters.Add(paramId);
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
contact = new Contact
{
Id = rdr.IsDBNull(rdr.GetOrdinal("Id")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("Id")),
FirstName = rdr.IsDBNull(rdr.GetOrdinal("FirstName")) ? null : rdr.GetString(rdr.GetOrdinal("FirstName")),
LastName = rdr.IsDBNull(rdr.GetOrdinal("LastName")) ? "" : rdr.GetString(rdr.GetOrdinal("LastName")),
Address = rdr.IsDBNull(rdr.GetOrdinal("Address")) ? null : rdr.GetString(rdr.GetOrdinal("Address")),
ZipCode = rdr.IsDBNull(rdr.GetOrdinal("ZipCode")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("ZipCode")),
City = rdr.IsDBNull(rdr.GetOrdinal("City")) ? null : rdr.GetString(rdr.GetOrdinal("City")),
Telephone = rdr.IsDBNull(rdr.GetOrdinal("Telephone")) ? null : rdr.GetString(rdr.GetOrdinal("Telephone")),
Email = rdr.IsDBNull(rdr.GetOrdinal("Email")) ? null : rdr.GetString(rdr.GetOrdinal("Email")),
BirthDate = rdr.IsDBNull(rdr.GetOrdinal("BirthDate")) ? null as DateTime? : rdr.GetDateTime(rdr.GetOrdinal("BirthDate"))
};
}
}
finally
{
_conn?.Close();
}
return contact;
}示例9: BulkCopy_DoWork
private void BulkCopy_DoWork(object sender, DoWorkEventArgs e)
{
//TODO: use more usings!
var sw = Stopwatch.StartNew();
SqlConnection connSource = null;
SqlConnection connDest = null;
var result = new BulkCopyResult();
e.Result = result;
var parameters = (BulkCopyParameters)e.Argument;
try {
connSource = new SqlConnection(parameters.Source.ConnectionString);
connDest = new SqlConnection(parameters.Destination.ConnectionString);
connSource.Open();
connDest.Open();
SqlDataReader reader = null;
foreach (var table in parameters.Tables.Values) {
if (worker.CancellationPending) {
e.Cancel = true;
return;
}
Log.Information("Copying rows to {0}", "[" + connDest.DataSource + "].[" + connDest.Database + "]." + table.FullName);
SqlTransaction transaction = null;
SqlBulkCopy bulkCopy = null;
try {
//Use Dictionary<,> so that destination column names can be case-insensitively located in their proper case because SqlBulkCopy mappings are case-sensitive!
var destSchema = connDest.GetSchema("Columns", new[] {connDest.Database, table.Schema, table.Name});
var destColumnsMap = destSchema.AsEnumerable().Select(x => x.Field<string>("COLUMN_NAME")).ToDictionary(k => k, v => v, StringComparer.OrdinalIgnoreCase);
transaction = connDest.BeginTransaction();
string query = String.Format(parameters.Query, table.FullName);
reader = new SqlCommand(query, connSource) { CommandTimeout = 9000 }.ExecuteReader();
//TODO: any FKs should be dropped and then recreated after truncating
try {
new SqlCommand(String.Format(Query_TruncateTable, table.FullName), connDest, transaction) { CommandTimeout = 120 }.ExecuteNonQuery();
} catch {
new SqlCommand(String.Format(Query_DeleteAllInTable, table.FullName), connDest, transaction) { CommandTimeout = 120 }.ExecuteNonQuery();
}
bulkCopy = new SqlBulkCopy(connDest, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, transaction) {
BulkCopyTimeout = 9000,
BatchSize = 10000,
NotifyAfter = 10000,
DestinationTableName = table.FullName
};
bulkCopy.SqlRowsCopied += sbc_SqlRowsCopied;
//Iterate over the Reader to get source column names because they may be defined by query results rather than a table-schema
var sourceColumns = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
for (int i = 0; i < reader.FieldCount; i++) {
sourceColumns.Add(reader.GetName(i));
}
var mapColumns = bulkCopy.ColumnMappings;
foreach (var column in sourceColumns) {
if (destColumnsMap.ContainsKey(column)) {
mapColumns.Add(column, destColumnsMap[column]);
}
}
rowsInCurrentTable = table.RowCount;
//Make sure the progress indicators are updated immediately, so the correct progress details are shown
sbc_SqlRowsCopied(bulkCopy, new SqlRowsCopiedEventArgs(0));
bulkCopy.WriteToServer(reader);
transaction.Commit();
Log.Information("Copied approximately {0} rows to {1}", table.RowCount, "[" + connDest.DataSource + "].[" + connDest.Database + "]." + table.FullName);
} catch (Exception ex) {
result.FailedTables[table.FullName] = ex;
transaction?.Rollback();
} finally {
bulkCopy?.Close();
reader?.Close();
}
if (worker.CancellationPending) {
e.Cancel = true;
return;
}
}
} finally {
connDest?.Close();
connSource?.Close();
sw.Stop();
result.Elapsed = sw.Elapsed;
}
//.........这里部分代码省略.........
示例10: GetItemStatusById
public ItemStatusEntity GetItemStatusById(int id)
{
List<ItemStatusEntity> itemStatusList = new List<ItemStatusEntity>();
try
{
_conn = new SqlConnection(ConnString);
_conn.Open();
string sql = "SELECT itemStatusUd, status, updateTimestamp, updatePersonFK FROM DBO.itemStatus WHERE itemStatusId = @id;";
var cmd = new SqlCommand(sql, _conn);
var paramQuery = new SqlParameter
{
ParameterName = "@id",
Value = "%" + id + "%"
};
cmd.Parameters.Add(paramQuery);
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var itemStatus = new ItemStatusEntity()
{
itemStatusId = rdr.IsDBNull(rdr.GetOrdinal("itemStatusId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemStatusId")),
status = rdr.IsDBNull(rdr.GetOrdinal("status")) ? null : rdr.GetString(rdr.GetOrdinal("status")),
updateTimestamp = rdr.IsDBNull(rdr.GetOrdinal("updateTimestamp")) ? new DateTime() : rdr.GetDateTime(rdr.GetOrdinal("updateTimestamp")),
updatePersonFK = rdr.IsDBNull(rdr.GetOrdinal("updatePersonFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("updatePersonFK"))
};
itemStatusList.Add(itemStatus);
}
}
finally
{
_conn?.Close();
}
return itemStatusList.FirstOrDefault();
}示例11: Update
public void Update(int id, ItemStatusEntity itemStatus)
{
CheckItemStatusForRequiredValues(itemStatus, RepositoryUtils.RepositoryAction.Update);
var contactToUpdate = GetItemStatusById(id);
if (contactToUpdate == null)
{
throw new Exception("ItemStatus does not exist in database");
}
try
{
_conn = new SqlConnection(ConnString);
var cmd = _conn.CreateCommand();
cmd.CommandText = @"UPDATE person SET [status][email protected],
[updateTimestamp]=getdate(),
[updatePersonFK][email protected]
WHERE [email protected]";
cmd.Parameters.Add("@Id", SqlDbType.Int);
cmd.Parameters["@Id"].Value = id;
cmd.Parameters.Add("@status", SqlDbType.VarChar);
cmd.Parameters["@status"].Value = itemStatus.status;
cmd.Parameters.Add("@updatePersonFK", SqlDbType.Int);
cmd.Parameters["@updatePersonFK"].Value = itemStatus.updatePersonFK;
_conn.Open();
var number = cmd.ExecuteNonQuery();
if (number != 1)
{
throw new Exception($"No Contacts were updated with Id: {id}");
}
}
finally
{
_conn?.Close();
}
}示例12: ItemStatusExists
public bool ItemStatusExists(string status)
{
try
{
const string sql = "SELECT COUNT([itemStatusId]) FROM dbo.itemStatus WHERE status = @status;";
_conn = new SqlConnection(ConnString);
_conn.Open();
var cmd = new SqlCommand(sql, _conn);
cmd.Parameters.Add("@status", SqlDbType.VarChar);
cmd.Parameters["@status"].Value = status;
return ((int)cmd.ExecuteScalar() >= 1);
}
finally
{
_conn?.Close();
}
}示例13: Insert
public long Insert(ItemStatusEntity itemStatus)
{
CheckItemStatusForRequiredValues(itemStatus, RepositoryUtils.RepositoryAction.Insert);
try
{
var contactExists = GetItemStatusByStatusName(itemStatus.status);
if (contactExists != null)
{
throw new Exception($"Entity {itemStatus.status} already exists in database!");
}
_conn = new SqlConnection(ConnString);
var cmd = _conn.CreateCommand();
cmd.CommandText =
@"INSERT INTO[dbo].[itemStatus] (status, updateTimestamp, updatePersonFK)
VALUES(@status, getdate(), @updatePersonFK);SELECT CAST(scope_identity() AS int)";
cmd.Parameters.Add("@status", SqlDbType.VarChar);
cmd.Parameters["@status"].Value = itemStatus.status;
cmd.Parameters.Add("@updatePersonFK", SqlDbType.Int);
cmd.Parameters["@updatePersonFK"].Value = itemStatus.updatePersonFK;
_conn.Open();
try
{
return int.Parse(cmd.ExecuteScalar().ToString());
}
catch (Exception)
{
throw new Exception($"ItemStatus {itemStatus.status} not inserted in database!");
}
}
finally
{
_conn?.Close();
}
}示例14: GetNumberOfItemStatus
public long GetNumberOfItemStatus()
{
try
{
_conn = new SqlConnection(ConnString);
_conn.Open();
string sql = "SELECT COUNT(itemStatusId) FROM dbo.itemStatus;";
var cmd = new SqlCommand(sql, _conn);
return Int32.Parse(cmd.ExecuteScalar().ToString());
}
finally
{
_conn?.Close();
}
}