How do I Recompile the MySQL Library to Run Under the Medium Trust Environment in ASP.NET?

Update: You no longer need that if you use the MySQL Connector/Net 5.2 or later.

  1. Download the source code for the MySQL Connector/Net from www.mysql.com.
  2. Extract the contents of the zip file to a local directory.
  3. Open mysql.csproj project file in Visual Studio.
  4. Open the AssemblyInfo.cs file, and add the following code, in the using block, at the top of the file (if it is not already there):using System.Security;
  5. Add the following code to the assembly section of the file:[assembly: AllowPartiallyTrustedCallers]
  6. Recompile the dll.

You may now reference this dll from other projects. When you decide to publish your project to your hosting server, you need to upload this modified version of the dll to your bin directory.

Via How do I Recompile the MySQL Library to Run Under the Medium Trust Environment? – Help Center—Knowledge Base and FAQ

MySQL Tutorial from MySQL.com

 


MySQL Tutorial

3.1. Connecting to and Disconnecting from the Server
3.2. Entering Queries
3.3. Creating and Using a Database [+/-]
3.3.1. Creating and Selecting a Database
3.3.2. Creating a Table
3.3.3. Loading Data into a Table
3.3.4. Retrieving Information from a Table
3.4. Getting Information About Databases and Tables
3.5. Using mysql in Batch Mode
3.6. Examples of Common Queries [+/-]
3.6.1. The Maximum Value for a Column
3.6.2. The Row Holding the Maximum of a Certain Column
3.6.3. Maximum of Column per Group
3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field
3.6.5. Using User-Defined Variables
3.6.6. Using Foreign Keys
3.6.7. Searching on Two Keys
3.6.8. Calculating Visits Per Day
3.6.9. Using AUTO_INCREMENT

11.6. Date and Time Functions

25.2.3. Connector/NET Examples and Usage Guide
25.2.4. Connector/NET Reference
25.2.5. Connector/NET Notes and Tips
25.2.6. Connector/NET Support

25.3.1. Installing the MySQL Visual Studio Plugin
25.3.2. Creating a connection to the MySQL server
25.3.3. Using the MySQL Visual Studio Plugin
25.3.4. Visual Studio Plugin Support

A. MySQL 5.0 Frequently Asked Questions

MySQL Forums

Installing MySQL on Windows

 

Installing MySQL Community Server on Windows

Via http://dev.mysql.com/doc/refman/5.0/en/installing-cs.html

Ease your SQL Server Management Studio SSMS experience

SQL Server Management Studio Express Utility

In SSMS Tools Pack 1.0  you can find these features:

- Uppercase/Lowercase keywords:

          Set all keywords to uppercase or lowercase letters. Custom keywords can be added.

- Run one script on multiple databases:

          Run selected or full window text on selected databases on the currently connected server.

- Copy execution plan bitmaps to clipboard:

          Copy selected or all execution plans to a bitmap that is saved on the clipboard.

- Search Results in Grid Mode and Execution Plans:

          Find all occurrences of your search string in the execution plans or in the results in datagrid mode.

- Generate Insert statements for a single table, the whole database or current resultsets in grids:

          Generate insert statement from your data.

- Query Execution History (Soft Source Control)

           Save all executed queries to file or database and easily find them.

- Text document Regions and Debug sections

           Add Regions and Debug section in your scripts to ease development experience.

- Running custom scripts from Object explorer’s Context menu

           Speedy execution of custom scripts from Object Explorer’s context menus.

- CRUD (Create, Read, Update, Delete) stored procedure generation:

           Generate Customizable CRUD stored procedures for all tables in your database.

- New query template:

           Create a template that is shown when creating a new query window.

Ease your SSMS experience: SSMS Tools PACK 1.0 is out!

How to Minimize SQL Server Blocking

Blocking occurs when one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away. Blocking is not the same thing as a deadlock.

A certain amount of blocking is normal and unavoidable. Too much blocking can cause connections (representing applications and users) to wait extensive periods of time, hurting overall SQL Server performance. In the worst cases, blocking can escalate as more and more connections are waiting for locks to be released, creating extreme slowdowns. The goal should be to reduce blocking as much as possible.

Via How to Minimize SQL Server Blocking

Troubleshooting Blocking

The first step in troubleshooting a problem is figuring out the cause and type of problem. If you have several phone calls from users whose screens just freeze when they hit the CREATE RECORD button, chances are you have some blocking issues. Fortunately, there are some tools that can help you identify the root of the problem.

Your first line of defense should be the system stored procedures sp_lock, sp_who, and sp_who2. The sp_lock procedure lets you see the type of locks acquired by one, many, or all sessions connected to the server. The syntax is as follows:

Via http://www.informit.com/articles/article.aspx?p=26949&seqNum=1

How do I load text or csv file data into SQL Server?

 

How do I load text or csv file data into SQL Server?

If you need to load data into SQL Server (e.g. from log files, csv files, chat transcripts etc), then chances are, you’re going to be making good friends with the BULK INSERT command. 

The command to bulk insert comma-delimite data would be: 

BULK INSERT OrdersBulk
    FROM ‘c:\file.csv’
    WITH
    (
        FIELDTERMINATOR = ‘,’,
        ROWTERMINATOR = ‘\n’
    )

If the csv file has a header row, try this:

BULK INSERT OrdersBulk
    FROM ‘c:\file.csv’
    WITH
    (
FIRSTROW = 2,
        FIELDTERMINATOR = ‘,’,
        ROWTERMINATOR = ‘\n’
    )

Finally, you can also specify how many errors you want to allow before considering that the BULK INSERT failed.

BULK INSERT OrdersBulk
    FROM ‘c:\file.csv’
    WITH
    (
        FIRSTROW = 2,
MAXERRORS = 0,
        FIELDTERMINATOR = ‘,’,
        ROWTERMINATOR = ‘\n’
    )

Vai How do I load text or csv file data into SQL Server?

ASP.NET 2.0 Membership, Roles, Forms Authentication, and Security Resources – ScottGu’s Blog

The following is excerpt from ScottGu’s Blog:

Getting Started with ASP.NET 2.0 Membership, Roles and Forms Authentication Video

Watch this great online video. It walks through how to add Forms Authentication (using the <asp:login> control) with a secure Membership Credential Store + Role Based Security to a site, then implement pages that enable Registration (using the <asp:createuserwizard> control) + Change Password (using the <asp:changepassword> control) + Reset Password (using the <asp:recoverypassword> control), and then authorize page access and hide menu navigation links using the role groupings of the authenticated user. The video shows how to-do all of this from scratch in only 17 minutes. You can watch it here. You can also find other great ASP.NET “how to” videos here.

ASP.NET 2.0 Membership and Role Management Overview Articles

Here are a few good tutorial articles that provide a good conceptual overview of how the new membership and role management system works.

Scott Mitchell’s: Examining ASP.NET 2.0’s Membership, Roles and Profile (Part 1)

Scott Mitchell’s: Examining ASP.NET 2.0’s Membership, Roles and Profile (Part 2)

Scott Mitchell’s: Examining ASP.NET 2.0′s Membership, Roles and Profile (Part 3)

Scott Mitchell’s: Examining ASP.NET 2.0′s Membership, Roles and Profile (Part 4)

Scott Mitchell’s: Examining ASP.NET 2.0′s Membership, Roles and Profile (Part 5)

MSDN: Explained: Forms Authentication in ASP.NET 2.0

MSDN: Explained: Windows Authentication in ASP.NET 2.0

Scott Allen’s: Membership Providers (Part 1)

Scott Allen’s: Role Providers (Part 2)

ASP.NET 2.0 Security, Membership and Role Management Book

Stefan Schackow is the ASP.NET Team technical expert and feature-owner for a lot of the core sub-systems in ASP.NET, and he owned the security, membership and role management features for ASP.NET 2.0. He has recently published an awesome book on ASP.NET Security, Membership and Roles that you can buy for $26 on Amazon here.

You can read two big recommendations of it from ASP.NET MVPs here: Dave Sussman and Christoph Wille I highly recommend getting a copy.

Setting up Membership + Roles on a SQL 2000 or SQL 2005 Server

By default ASP.NET 2.0 auto-creates and uses a SQL Express database to store Membership, Roles and Profile data. If you want to instead use a SQL 2000 or SQL 2005 database, you can easily learn how to configure it using this blog post of mine.

Don’t forget to always set the “applicationName” attribute when configuring ASP.NET Membership, Roles, Profile and other providers.

One common issue people forget to-do when registering membership and other providers is to configure the “applicationName” attribute on the provider declaration.  This can prevent logins from seeming to work when you copy an application to another machine.  This blog post covers this scenario more and how to fix it.

Custom Membership and Roles Providers

ASP.NET 2.0 ships with built-in SQL Server, SQL Express and Active Directory Membership and Role Providers.  The source code for these built-in providers can now be downloaded from here.

The nice thing about the system is that it is entirely extensible, which means you can create and configure your own custom credential/role stores into the system as well (either using the source code from the built-in providers, or just by extending the provider contract).

The ASP.NET Provider Toolkit Site provides tons of content on how to create and build your own providers (including Membership and Role Providers). It also has a link to a fully functional Membership and Role Provider that works with Access databases.  This article also discusses how to build your own Membership Provider, and can be a useful guide to integrating the membership APIs with your own existing database.

Here is a list of other free custom Membership and Roles providers (with complete source code) that I know of on the web:

You can download and configure your application to use any of the above providers.  The beauty of the system is that the Membership, Roles APIs + Login Controls don’t change at all. 

Storing Custom Properties about a User during Registration

One very common question I see asked a lot is how to store custom properties about a new user as they register on the system (example: zip code, gender, etc). The good news is that it is easy to-do this with the new ASP.NET Profile System and the built-in <asp:createuserwizard> control.

I have a sample here that shows how to build a registration system for a site with Membership, Login, Registration, Password Recovery, Change Password, Custom Properties and Roles support – all in 24 lines of code.  If you want, you can combine this with the new SQLTableProvider for the Profile system for greater control over your profile database schema. You can learn about that in my blog post here.

Remote Server Administration Tool Mangement of Membership/Roles

The built-in Web Administration Tool with Visual Web Developer and VS 2005 makes it easy to manage the users and roles for a local ASP.NET application. One common question I get asked is how to manage these users/roles against a remote server (for example: an application running on a remote hoster.  This blog post of mine points to two different solutions you can use to enable this.

Via ASP.NET 2.0 Membership, Roles, Forms Authentication, and Security Resources – ScottGu’s Blog

SQL# (SQLsharp) – Enabling more powerful SQL

 

Features:

  • String: Contains, Count, Cut, EndsWith, Equals, IndexOf, InitCap, Join, LastIndexOf, Newline, NthIndexOf, PadLeft, PadRight, Split, SplitIntoFields, StartsWith, Trim, WordWrap
  • RegEx: IsMatch, Matches, Match, MatchLength, MatchSimple, Replace, Split
  • Math: CompoundAmortizationSchedule, Constant (30 physics constants), Convert (22 measurement conversions), Cosh, Factorial, IsPrime, RandomRange, Sinh, Tanh
  • Date: BusinessDays, DaysInMonth, DaysLeftInYear, FirstDayOfMonth, FormatTimeSpan, FromUNIXTime, FullDateString, FullTimeString, IsBusinessDay, IsLeapYear, LastDayOfMonth, ToUNIXTime
  • InterNet (not available in free version): AddressToNumber, FtpDo, FtpGet, FtpPut, GetHostName, GetIPAddress, GetWebPages, IsValidIPAddress, NumberToAddress, Ping, PingTime
  • File (not available in free version): ChangeEncoding, Copy, CopyMultiple, CreateDirectory, Decrypt, Delete, DeleteDirectory, DeleteMultiple, Encrypt, GetDirectoryListing, GetDriveInfo, GetFile, GetFileBinary, GetRandomFileName, GetTempPath, GUnzip, GZip, Move, MoveMultiple, PathExists, SplitIntoFields, WriteFile, WriteFileBinary
  • Miscellaneous: CRC32, Deflate, GenerateDateTimeRange, GenerateDateTimes, GenerateFloatRange, GenerateFloats, GenerateIntRange, GenerateInts, GUnzip, GZip, Hash, Inflate, IsValidCC, IsValidSSN, ToWords
  • Database: DumpData (not available in free version)
  • Convert: BinaryToHexString, FromBase64, HexStringToBinary, ROT13, ToBase64
  • LookUps: GetCountryInfo, GetStateInfo
  • Internal: Version, Help, Setup, Uninstall, GrantPermissions, IsUpdateAvailable, Update (not available in free version), SetSecurity, WebSite
  • User-Defined Aggregates: GeometricAvg, Join, Median, Random, RootMeanSqr
  • User-Defined Types: FloatArray, HashTable, NVarcharArray

What can SQL# do?

  • SQL# gives you the easiest access to the power of the CLR!
    – a single assembly with over 100 functions, 5 User-Defined Aggregates, 3 User-Defined Types, and more being added!
  • SQL# installs easily and in moments!
    – download one small install sql script, execute it, and enjoy the power of the CLR!
  • SQL# is backed up and restored with the database along with all other objects and data!
    – no need to worry about separate DLLs as with COM Extended Stored Procedures
  • SQL# has built in documentation (list of function and procedure signatures)!
    – if you ever lose the documention, the worst off you are is one procedure call away from viewing the entire list of function signatures!
  • SQL# can easily be updated via the web in moments!
    – you can optionally install updates via the web with a single procedure call
  • SQL# saves countless hours learning CLR and .Net, not to mention the cost of Visual Studio 2005!
    – time is money and you have work to do so why stop and learn yet another language, especially when you might need to purchase additional software just to compile a basic function!
  • SQL# lets you focus on SQL programming without sacrificing the power of the CLR!
    – again, there is only so much time in the day so do you want to spend it NOT being productive?

Download

Download Free Version of SQL# (SQLsharp) here!

The manual and “What’s New” document cover both Free and Paid-For versions.
Manual in PDF format (766k)
What’s New in Version 2.5.20/2.5.21 (122k)

Date_BusinessDays ExcludeDaysMask Worksheet .
Download Spreadsheet (19k)

Via SQL# (SQLsharp) – Enabling more powerful SQL (Features)