|
Hello
Hope it's ok to post this here - it's a general programming query but maybe it should be in "Database"? Anyway...
I've never really understood database connection pooling...
If I want to make a lot of separate database updates I could take one of two approaches:
Do
run some code to prepare SQL command
Open DB connection
Run SQL command
Close DB connection
LoopOr
Open DB connection
Do
run some code to prepare SQL command
Run SQL command
Loop
Close DB connection
Which is the better approach?
Thanks...
|
|
|
|
|
Those aren't separate database-updates. Combine them, execute a single IDbCommand and wrap it in a transaction.
As for the loop; There's a good explanation on MSDN[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Well, I'm not sure to combine a number of statements of the form
insert into table (field1, field2,...) values (?,?,...) on duplicate key update field2 = ?, ... with associated command parameters into one.... (MySQL)
I guess I need to look into using transactions more....
thanks
|
|
|
|
|
A_Griffin wrote: Well, I'm not sure to combine a number of statements of the form
In case of doubt, verify.
INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. I don't work with MySQL a lot, but in SQLite there is a very noticeable slowdown if you insert each record separately.
It looks a lot like a bulk-insert. If you don't want to import it in a single go, I'd still recommend doing it at least in batches.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Yes, for simple inserts MySQL can use bulk inserts as you suggest, but when combined with the "on duplicate key update" option then, as far as I know, this cannot be done.
|
|
|
|
|
|
Yes, thanks - it says nothing there about how (or even if one can) combine multiple such statements into one.
And why, out of interest, would you never use it?
|
|
|
|
|
A_Griffin wrote: Yes, thanks - it says nothing there about how (or even if one can) combine multiple such statements into one.
It does, even comes with an example - and a note it might be unsafe.
A_Griffin wrote: And why, out of interest, would you never use it?
Because I could not longer guarantee the integrity of the database, which is rather important to me.
What is your key? Let's take a VARCHAR(10) as an example key. There's already an entry "test" in your DB, and the same key occurs in your import-set 15 times. Which of those would you reckon to be the correct one? Which one do you think will be inserted? That may lead to some inconsistencies in a replication-scenario.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
The best solution is the one that minimizes the number of "new" connections made (which is extra overhead); and doesn't keep a connection open longer than needed (hogs resources).
In your case, the choice depends on the amount of "looping" and what "run some code" involves; so there is no "one answer".
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Gerry Schmitz wrote: The best solution is the one that minimizes the number of "new" connections made (which is extra overhead);
In doubt, open and closing has the preference. Thanks to connection pooling the overhead is limited.
There is a Dokan-implementation in the wild available that emulates a drive using a SQL server database. Opening and closing a connection for each request did not slow the machine down even a tiny bit.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thank you - that is interesting. I do wonder though, when you say "If in doubt..." - what doubt? Under what circumstances then would one prefer the other option (a single open/close outside the loop)?
|
|
|
|
|
|
Indeed, I could, but I don't expect a huge (or even noticeable) difference in performance with the size of databases I'm working with at the moment. I just wondered if one method was considered "best practice", really, and is maybe more scalable as the size of the database grows over time... MY gut feeling prefers, like you, to have each command wrapped in its own connection, but the other half of my gut (!) feels uneasy about opening and closing a hundred or so connections in such quick succession... it feels like a lot of unnecessary work. But if pooling is about minimizing this work, then good... (hence why I started off by saying I've never really understood (the guts) of pooling...)
|
|
|
|
|
A_Griffin wrote: but the other half of my gut (!) feels uneasy about opening and closing a hundred or so connections in such quick succession...
After implementing a scenario where that actually happened, I'm not.
A Dokan-drive is something that shows up and acts like a drive in Windows Explorer (including any save-dialog), with the implementation of fetching the data left to the programmer. Clicking on a folder in Windows Explorer would launch a whole lotta operations - it would first want to know if there is such a file or folder, then a request to get its contents, then a request for each file in there, so it could display a size and icon. And the virusscanner requesting those files too, since they were being accessed.
Each operation, in VB.NET, would open and close a connection on my local machine, with a local database-server. The speed was limited by the capabilities of the Dokan-driver, not the speed of the database or opening/closing connections.
A_Griffin wrote: I don't expect a huge (or even noticeable) difference in performance with the size of databases I'm working with at the moment.
..then fill a table with 2 million records. Generate some nonsense-data in a loop and give it a try.
If your boss asks why you choose one or the other, it is better to present an experiment than to point to some idiot on the internet 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I said "NEW"; pooling is not making "new" connections.
I also didn't limit the number of connections from a single client to multiple servers / devices.
I also didn't specify "database servers" (SQL or otherwise); or only "client and (one) server".
TCP/IP is not limited to "databases".
A "new" TCP connection (first or a reconnect) requires hand-shaking not incurred once the connection is "open".
"Databases"? ok .. You get it by default; no thinking there.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Gerry Schmitz wrote: I said "NEW"; pooling is not making "new" connections.
Obvious to both of us, but not to the casual reader who just sees "New Connection()".
Gerry Schmitz wrote: I also didn't specify "database servers" (SQL or otherwise); or only "client and (one) server".
Would not need to, anything behind the abstraction of the Data Provider is irrelefant.
Gerry Schmitz wrote:
TCP/IP is not limited to "databases".
..I did not imply such.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
How to get each items "valuemember" value of checkedlistbox using for loop and show it in messagebox in winform
|
|
|
|
|
|
I need a OneWire Library to use in vb.net 2017.
a library wich Had emplimented the hole protocol as do Dallas for Ardino .
Thanks to your helps .
|
|
|
|
|
|
I need one implement the ptotocol in vb.net these are using he protocol.
thanks to your help.
|
|
|
|
|
Hi All, Hope you all Fine
I Create a controller (Account ), with 3 Action Result(Main Login Page,Login,register)as the bellow code:
public class AccountController : Controller
{
public ActionResult MainLoginPage()
{
return View();
}
public ActionResult Login()
{
return View();
}
[HttpPost]
public ActionResult Login(UserInfo user)
{
if (Membership.ValidateUser(user.UserName, user.Password))
{
FormsAuthentication.SetAuthCookie(user.UserName, false);
return RedirectToAction("HomePage", "Home");
}
else
{
ModelState.AddModelError("", "Login details are wrong.");
}
return View(user);
}
public ActionResult Register()
{
return View();
}
[HttpPost]
public ActionResult Register(UserInfo user)
{
if (ModelState.IsValid)
{
Membership.CreateUser(user.UserName, user.Password, user.Email);
return RedirectToAction("HomePage", "Home");
}
else
{
ModelState.AddModelError("", "already registered.");
}
return View();
}
I create one main View(MainLoginPage)which contain two Partial Views(_login,_Register) as follow:.
@{
ViewBag.Title = "MainLoginPage";
}
@section PageHeader
{
<link href="~/Content/Css/Login.css" rel="stylesheet" />
<link href="~/Content/Css/animation.css" rel="stylesheet" />
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
}
<pre>
<div class="wrapper">
<div id="login" class="animate form">
@{
Html.RenderPartial("_Login"); }
</div>
<div id="register" class="animate form">
@{ Html.RenderPartial("_Register"); }
</div>
</div>
_Login
@model myWebsite.Models.UserInfo
@using (Html.BeginForm("Login", "Account", FormMethod.Post))
{
<pre>
@Html.AntiForgeryToken() @*The anti-forgery token can be used to help protect
your application against cross - site request forgery.*@
@Html.ValidationSummary(true, "Login Failed, check details");
<label data-icon="u"> </label>
@Html.TextBoxFor(user => Model.UserName, new { placeholder = "username" })
@Html.ValidationMessageFor(user => Model.UserName)
<label data-icon="p"> </label>
@Html.PasswordFor(user => Model.Password, new { placeholder = "password" })
@Html.ValidationMessageFor(user => Model.Password@*,"*"*@)
_Register
code similar to Login
When login with valid user it work probably but My problem is when in enter invalid login details it showing the following Error:
Server Error in '/' Application.
The view 'Login' or its master was not found or no view engine supports the searched locations. The following locations were searched:
~/Views/Account/Login.aspx
~/Views/Account/Login.ascx
~/Views/Shared/Login.aspx
~/Views/Shared/Login.ascx
~/Views/Account/Login.cshtml
~/Views/Account/Login.vbhtml
~/Views/Shared/Login.cshtml
~/Views/Shared/Login.vbhtml
It seem that searching for the view login while login is Partial(_Login)
Could You please advice me
Note:
Quote: It work probably when I work on it as normal view not as partial view
modified 6-Jul-17 7:27am.
|
|
|
|
|
When you call the View method[^] without specifying a view name, it tries to find a view that matches the action name. You can see the list of views it looked for in the error message.
The problem is, you don't have a view called either Login or Register, so when you call View from those actions, it won't be able to find a matching view.
You can either add the missing views:
Login.cshml:
@{
ViewBag.Title = "Login";
}
@section PageHeader
{
<link href="~/Content/Css/Login.css" rel="stylesheet" />
<link href="~/Content/Css/animation.css" rel="stylesheet" />
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
}
@{ Html.RenderPartial("_Login"); }
Register.cshtml:
@{
ViewBag.Title = "Register";
}
@section PageHeader
{
<link href="~/Content/Css/Login.css" rel="stylesheet" />
<link href="~/Content/Css/animation.css" rel="stylesheet" />
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
}
@{ Html.RenderPartial("_Register"); }
Or, you can specify the view name:
public class AccountController : Controller
{
public ActionResult MainLoginPage()
{
return View();
}
[HttpPost]
public ActionResult Login(UserInfo user)
{
if (Membership.ValidateUser(user.UserName, user.Password))
{
FormsAuthentication.SetAuthCookie(user.UserName, false);
return RedirectToAction("HomePage", "Home");
}
ModelState.AddModelError("", "Login details are wrong.");
return View("MainLoginPage", user);
}
[HttpPost]
public ActionResult Register(UserInfo user)
{
if (ModelState.IsValid)
{
MembershipCreateStatus status;
Membership.CreateUser(user.UserName, user.Password, user.Email, null, null, true, out status);
switch (status)
{
case MembershipCreateStatus.Success:
{
return RedirectToAction("HomePage", "Home");
}
case MembershipCreateStatus.InvalidUserName:
{
ModelState.AddModelError(nameof(UserInfo.UserName), "Invalid username.");
break;
}
case MembershipCreateStatus.InvalidPassword:
{
ModelState.AddModelError(nameof(UserInfo.Password), "Invalid password.");
break;
}
case MembershipCreateStatus.InvalidEmail:
{
ModelState.AddModelError(nameof(UserInfo.Email), "Invalid email address.");
break;
}
case MembershipCreateStatus.DuplicateUserName:
{
ModelState.AddModelError(nameof(UserInfo.UserName), "Already registered.");
break;
}
case MembershipCreateStatus.DuplicateEmail:
{
ModelState.AddModelError(nameof(UserInfo.Email), "Already registered.");
break;
}
case MembershipCreateStatus.UserRejected:
{
ModelState.AddModelError("", "User rejected.");
break;
}
default:
{
ModelState.AddModelError("", "Unknown error.");
break;
}
}
}
return View("MainLoginPage");
}
}
NB: In this case, you'll also want to remove the GET versions of Login and Register, since you don't have separate views for them.
You also need to verify the status when registering a new user, using the MembershipCreateStatus enum.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I add this
return View("MainLoginPage", user);
line and it work fine in Login Form, but in registration form still I face problem Since both forms are in same view.
and when the page load, only Login form appear till I click on link go to register login form disappear and register form appear with some fade animation,,
now I add the line in Register
return View("MainLoginPage", user);
when already registered it return to login form and already register error appear there..
<div class="container">
<a class="hiddenanchor" id="toregister"></a>
<a class="hiddenanchor" id="tologin"></a>
<pre>
<div class="wrapper">
<div id="login" class="animate form">
@{ Html.RenderPartial("_Login"); }
</div>
<div id="register" class="animate form">
@{ Html.RenderPartial("_Register"); }
</div>
</div>
|
|
|
|
|
Then you'll need to add something to tell whatever script or CSS you're using to switch between the forms that the register form should be active instead of the login form.
Since you haven't shared that part of the code, I can't tell you how to do that.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
