|
Thanks Guys. Appreciated. All good with the replies. Job done.
|
|
|
|
|
i am using OleDbConnection to read excel files.
excel file is read properly.
but my form is resize and all text looks bigger and buggy when i open the connection.
i really dont understand whats happend.
please help me for this.
thanks in advance.
DataTable sheet1 = new DataTable("Excel Sheet");
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = filename;
csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
string selectSql = @"SELECT * FROM [Sheet1$]";
using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
connection.Open();
adapter.Fill(sheet1);
connection.Close();
}
|
|
|
|
|
Inherently, there's nothing in there that would cause the UI to resize. I assume, however, that there is a lot more to your code than this - what do you do with the DataTable? Is this being bound to something on the form?
|
|
|
|
|
I know about the ConcurrentBag, ConcurrentDictionary, ConcurrentQueue and ConcurrentStack collection classes. But is there any Concurrent equivalent to the good ol' List<>?
The ConcurrentBag says it provides a thread safe unordered collection, but I need an ordered collection.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
There's a discussion about adding a concurrent list here:
ConcurrentList<T> · Issue #41740 · dotnet/runtime · GitHub[^]
The conclusion is that there's no meaningful way to implement it without locking the entire list - at which point, you might was well wrap a regular List<T> with locks.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you for the reply, Richard.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
A concurrent dictionary loaded ordered is essentially an ordered list.
Whether you handle it as KeyValue pairs or as .Values is another matter (maybe).
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Yeah, that's what I've settled on doing - using ConcurrentDictionary's.
Thanks for your reply. 
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I'm using EF 6 Core Code First. I'm trying to set up some basic code and I'm getting an exception at runtime.
Here's my Context class:
using Microsoft.EntityFrameworkCore;
namespace EFCoreDBFirstExample.Models
{
public partial class ModelContext : DbContext
{
public virtual DbSet Departments { get; set; }
public virtual DbSet Employees { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionString = @"Server=MAROIS_KEVIN_1\SQLEXPRESS;Database=Test;Trusted_Connection=true;Encrypt=false;";
optionsBuilder.UseSqlServer(connectionString, options => options.EnableRetryOnFailure());
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity(entity =>
{
entity.ToTable("Departments", "public");
entity.HasKey(e => e.DepartmentId)
.HasName("DeptartmentId");
entity.Property(e => e.DepartmentId)
.HasColumnName("DeptartmentId");
entity.Property(e => e.DepartmentName)
.HasColumnName("DepartmentName")
.HasColumnType("varchar")
.HasMaxLength(30);
});
modelBuilder.Entity(entity =>
{
entity.ToTable("EmployeeName", "public");
entity.HasKey(e => e.EmployeeId)
.HasName("EmployeeId");
entity.Property(e => e.EmployeeId)
.HasColumnName("EmployeeId");
entity.Property(e => e.EmployeeName)
.HasColumnName("EmployeeName")
.HasColumnType("varchar")
.HasMaxLength(30);
});
}
}
}
Here's how I'm using it
using EFCoreDBFirstExample.Models;
using (var db = new ModelContext())
{
var newDept = new Departments();
newDept.DepartmentId = 1;
newDept.DepartmentName = "Development";
db.Departments.Add(newDept);
var count = db.SaveChanges();
Console.WriteLine("{0} records saved to database", count);
Console.WriteLine();
Console.WriteLine("Departments:");
foreach (var dept in db.Departments)
{
Console.WriteLine($"{dept.DepartmentId}: {dept.DepartmentName}" );
}
Console.ReadLine();
}
Here's the exception:
Microsoft.EntityFrameworkCore.Storage.RetryLimitExceededException
HResult=0x80131500
Message=The maximum number of retries (6) was exceeded while executing database operations with 'SqlServerRetryingExecutionStrategy'. See the inner exception for the most recent failure.
Inner Exception 1:
SqlException: Cannot open database "Test" requested by the login. The login failed.
Login failed for user 'MAROIS_KEVIN_1\kevin'.
As you can see, I'm using Trusted Connection in my connection string. Not sure why EF is trying to connect with the user 'kevin'.
I could use some help here. Thanks
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Where did it get "kevin" from? That's the account you logged into Windows as. "Integrated Security" means use the Windows account the code is running as.
The error message is blatantly obvious: "Cannot open database "Test" requested by the login. The login failed.
Login failed for user 'MAROIS_KEVIN_1\kevin'."
You SQL Server doesn't know who "kevin using a Windows Account" is. You have to go into SQL Server Manager to correctly setup a Windows Login account in your SQL Server Security and give it appropriate permissions to the database and tables as appropriate for your situation.
If you haven't created the database yet and are relying on EF to do it for you, you have to give your account admin permissions to the SQL Server so YOU can create databases on the server.
|
|
|
|
|
Yes, Kevin is my Windows login, but all my other apps use Trusted Connection without a user specified.
connectionString="Server=MAROIS_KEVIN_1\SQLEXPRESS;Database=Jayhawk;Trusted_Connection=true;"
I do have a user Kevin in SQL and I tried it with the password. I also just tried creating a new user and password and got the same error.
Something else is wrong.
Thanks
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
When you created the "kevin" account in SQL Server Manager, did you tell it to use Windows Authentication or SQL Server Authentication?
|
|
|
|
|
Windows Auth
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Next up, on the Server Roles tab, which roles have checkmarks next to them?
|
|
|
|
|
Public & SysAdmin
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
OK, based on your answers, the login should work.
I would go back to SQL Server Manager and create a new SQL account, password, and permissions of dbcreator and public. Change your connection string and remove TrustedConnection and put in the username and password of the account you create.
|
|
|
|
|
That's what I thought too.
I have created a new login and still get the same error. Here's my Conn string
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionString = @"Server=MAROIS_KEVIN_1\SQLEXPRESS;Database=Test;User Id=FatALbert;Password=AlbertIsFat!;;Encrypt=false;";
optionsBuilder.UseSqlServer(connectionString, options => options.EnableRetryOnFailure());
}
and the exception
Microsoft.EntityFrameworkCore.Storage.RetryLimitExceededException
HResult=0x80131500
Message=The maximum number of retries (6) was exceeded while executing database operations with 'SqlServerRetryingExecutionStrategy'. See the inner exception for the most recent failure.
Source=Microsoft.EntityFrameworkCore
StackTrace:
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation[TState,TResult](Func`3 operation, Func`3 verifySucceeded, TState state)
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Program.<Main>$(String[] args) in C:\Projects\SandBox\Learning\EF6Core\Database First\EFCoreDBFirstExample\Program.cs:line 11
This exception was originally thrown at this call stack:
[External Code]
Inner Exception 1:
SqlException: Cannot open database "Test" requested by the login. The login failed.
Login failed for user 'FatAlbert'.
I also tried creating the DB 'Test' first in SQL and assigning it to FatAlbert and the same exception.
Again, I have other apps running on my Dev PC that don't use SQL Authentication and they connect just fine. So I has to be something with EF.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Wait a minute. Did you put the connection string ONLY in the OnConfiguring method?
|
|
|
|
|
Yes. The examples I followed show it there. Isn't that what OnConfiguring does? A one time set up?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
This works differently from the older Entity Frameworks. The reason you're getting the login failure is because the database does not exist in SQLEXPRESS yet.
You cannot create the database just by running the code you have, as is. You first have to enable migrations in the project, then create your first migration ("InitialCreate"). Once that is done, you can add the following line your program:
using (var db = new ModelContext())
{
db.Database.Migrate();
var newDept = new Departments();
newDept.DepartmentId = 1;
newDept.DepartmentName = "Development";
I highly recommend AGAINST doing this! You are far better off managing and applying migrations using the EF command line tools! You can EASILY make a mistake that will destroy a production database just by running your code at the wrong time and with the wrong connection string!
Migrations Overview - EF Core | Microsoft Learn[^]
On top of that, there's a few mistakes in your code in your initial post above. For example, every DbSet should be DbSet<type> ...
public virtual DbSet<Department> Departments { get; set; }
public virtual DbSet<Employee> Employees { get; set; }
...and there are mispellings in your modelBuilder code, like
entity.ToTable("Employees", "public");
|
|
|
|
|
Thanks alot!
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Applying migration '20221127185110_Initial'.
Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
IF SCHEMA_ID(N'public') IS NULL EXEC(N'CREATE SCHEMA [public];');
Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named 'public' in the database.
CREATE SCHEMA failed due to previous errors.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:5f3126d9-a1c8-4a81-b0f8-8eba5fd77448
Error Number:2714,State:6,Class:16
There is already an object named 'public' in the database.
CREATE SCHEMA failed due to previous errors.
Set Rant On
This is why I have stayed with Linq-To-SQL. EF is WAAAAAY too complicated. The fact the I need to run console commands is mind-boggling. There's no UI for this? Point & click! And the amount of learning and work to set it up is astounding. And god forbid you make a mistake.
Set Rant Off
Ok, so I'm not even sure what 'public' it thinks is in the DB. It created the DB but not any tables with the exception of MigrationsHistory. So I'm now back where I started - stuck. Any idea what 'public' it's referring to?
Also, do you know of any references that can walk me through setting up & using EF Core. Assume I know nothing. I have this book. I went through the entire first chapter where he sets up the tables, relations, and DBContext, and there's no mention of Migration so far.
Thanks again.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
modified 27-Nov-22 14:55pm.
|
|
|
|
|
Change "public" to "dbo" in your code.
Where did you find this tutorial you're following?
The problem with Linq-To-Sql is that it is a dead product and no longer under development.
|
|
|
|
|
Dave Kreskowiak wrote: Where did you find this tutorial you're following?
I have the book I mentioned in my other reply, as well as a couple of YouTube vids I followed.
Dave Kreskowiak wrote: The problem with Linq-To-Sql is that it is a dead product and no longer under development.
Yup. Thats why I'm doing this.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
OK, I think I have this now.
I did the migration and update, and the DB was created and Department and Employee tables added.
Next, I added a Companies entity, linked it to Departments, and ran
dotnet ef migrations add AddCompanies
dotnet ef database update
and the Db is now up to date.
So I need to run these 2 commands each time I make a change?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
