Code:
/ 4.0 / 4.0 / untmp / DEVDIV_TFS / Dev10 / Releases / RTMRel / ndp / fx / src / xsp / System / Extensions / ClientServices / Providers / SqlHelper.cs / 1305376 / SqlHelper.cs
//------------------------------------------------------------------------------ //// Copyright (c) Microsoft Corporation. All rights reserved. // //----------------------------------------------------------------------------- namespace System.Web.ClientServices.Providers { using System; using System.Security; using System.Security.Permissions; using System.Security.AccessControl; using System.Globalization; using System.Data; using System.Data.Common; using System.Data.OleDb; using System.IO; using System.Windows.Forms; using System.Data.SqlClient; using System.Reflection; using System.Diagnostics.CodeAnalysis; using System.Web.Resources; internal static class SqlHelper { private const string _SQL_CE_Tag = "|SQL/CE|"; private const string _SQL_FILES_Tag = "|FILES|"; private const string _SQL_CE_CONN_STRING = "Data Source = |SQL/CE|"; private const string _Isolated_Storage_Tag = "|Isolated_Storage|"; ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// internal static string GetDefaultConnectionString() { return _SQL_FILES_Tag; } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// internal static int IsSpecialConnectionString(string connectionString) { if (string.IsNullOrEmpty(connectionString)) return 1; // Default to FILES if (string.Compare(connectionString, _SQL_FILES_Tag, StringComparison.OrdinalIgnoreCase) == 0) return 1; // if (string.Compare(connectionString, _Isolated_Storage_Tag, StringComparison.OrdinalIgnoreCase) == 0) // return 2; -- disable isolated storage return 3; } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// internal static DbConnection GetConnection(string username, string connectionString, string sqlProvider) { if (connectionString.Contains(_SQL_CE_Tag) || (sqlProvider != null && sqlProvider.Contains(".SqlServerCe"))) { try { return GetSqlCeConnection(username, connectionString); } catch (TypeLoadException e) { throw new ArgumentException(AtlasWeb.SqlHelper_SqlEverywhereNotInstalled, e); } } DbConnection connection = new SqlConnection(connectionString); connection.Open(); return connection; } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// internal static void AddParameter(DbConnection conn, DbCommand cmd, string paramName, object paramValue) { if (!(conn is SqlConnection)) AddSqlCeParameter(cmd, paramName, paramValue); else cmd.Parameters.Add(new SqlParameter(paramName, paramValue)); } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// internal static string GetCookieFromDB(string name, string username, string connectionString, string sqlProvider) { if (connectionString == _SQL_FILES_Tag) return ClientDataManager.GetCookie(username, name, false); if (connectionString == _Isolated_Storage_Tag) return ClientDataManager.GetCookie(username, name, true); using (DbConnection connection = GetConnection(username, connectionString, sqlProvider)) { DbCommand cmd = connection.CreateCommand(); cmd.CommandText = "SELECT PropertyValue FROM UserProperties WHERE PropertyName = @PropName"; AddParameter(connection, cmd, "@PropName", "CookieName_" + name); return (cmd.ExecuteScalar() as string); } } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// internal static string StoreCookieInDB(string cookieName, string cookieValue, string username, string connectionString, string sqlProvider) { if (connectionString == _SQL_FILES_Tag) return ClientDataManager.StoreCookie(username, cookieName, cookieValue, false); if (connectionString == _Isolated_Storage_Tag) return ClientDataManager.StoreCookie(username, cookieName, cookieValue, true); string name = Guid.NewGuid().ToString("N"); using (DbConnection connection = GetConnection(username, connectionString, sqlProvider)) { DbTransaction trans = null; try { trans = connection.BeginTransaction(); DbCommand cmd = connection.CreateCommand(); // delete old cookie cmd.CommandText = "DELETE FROM UserProperties WHERE PropertyName LIKE N'CookieName_%' AND PropertyValue LIKE @PropValue"; cmd.Transaction = trans; AddParameter(connection, cmd, "@PropValue", cookieName + "=%"); cmd.ExecuteNonQuery(); if (!string.IsNullOrEmpty(cookieValue)) { cmd = connection.CreateCommand(); cmd.Transaction = trans; cmd.CommandText = "INSERT INTO UserProperties (PropertyName, PropertyValue) VALUES (@PropName, @PropValue)"; AddParameter(connection, cmd, "@PropName", "CookieName_" + name); AddParameter(connection, cmd, "@PropValue", cookieName + "=" + cookieValue); cmd.ExecuteNonQuery(); return name; } return cookieName; } catch { if (trans != null) { trans.Rollback(); trans = null; } throw; } finally { if (trans != null) trans.Commit(); } } } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// internal static void DeleteAllCookies(string username, string connectionString, string sqlProvider) { if (connectionString == _SQL_FILES_Tag || connectionString == _Isolated_Storage_Tag) { ClientDataManager.DeleteAllCookies(username, connectionString == _Isolated_Storage_Tag); return; } using (DbConnection connection = GetConnection(username, connectionString, sqlProvider)) { DbTransaction trans = null; try { trans = connection.BeginTransaction(); DbCommand cmd = connection.CreateCommand(); cmd.CommandText = "DELETE FROM UserProperties WHERE PropertyName LIKE N'CookieName_%'"; cmd.Transaction = trans; cmd.ExecuteNonQuery(); } catch { if (trans != null) { trans.Rollback(); trans = null; } throw; } finally { if (trans != null) trans.Commit(); } } } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// private static Type _SqlCeConnectionType = null; private static Type _SqlCeParamType = null; ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// private static DbConnection GetSqlCeConnection(string username, string connectionString) { DbConnection conn = CreateDBIfRequired(username, connectionString); if (conn == null) conn = CreateNewSqlCeConnection(connectionString, true); //new SqlCeConnection(connectionString); return conn; } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// // [PermissionSet(SecurityAction.Assert, Unrestricted = true)] private static DbConnection CreateDBIfRequired(string username, string connectionString) { if (!connectionString.Contains(_SQL_CE_Tag)) return null; DbConnection conn = null; try { conn = CreateNewSqlCeConnection(connectionString, false); if (string.Compare(conn.Database.Trim(), _SQL_CE_Tag, StringComparison.OrdinalIgnoreCase) != 0) { conn.Open(); return conn; } conn.Dispose(); conn = null; } catch (TypeLoadException e) { throw new ArgumentException(AtlasWeb.SqlHelper_SqlEverywhereNotInstalled, e); } string fileName = GetFullDBFileName(username, "_DB.spf"); bool needToCreateDB = (File.Exists(fileName) == false); connectionString = connectionString.Replace(_SQL_CE_Tag, fileName); if (needToCreateDB) { using (IDisposable engine = (IDisposable)Activator.CreateInstance(GetSqlCeType("SqlCeEngine"), new object[] { connectionString })) { engine.GetType().InvokeMember("CreateDatabase", BindingFlags.InvokeMethod | BindingFlags.Public | BindingFlags.Instance, null, engine, null, CultureInfo.InvariantCulture); } using (conn = CreateNewSqlCeConnection(connectionString, true)) { DbCommand cmd = conn.CreateCommand(); if (username == null) { cmd.CommandText = "CREATE TABLE ApplicationProperties (PropertyName nvarchar(256), PropertyValue nvarchar(256))"; cmd.ExecuteNonQuery(); } else { cmd.CommandText = "CREATE TABLE UserProperties (PropertyName nvarchar(256), PropertyValue nvarchar(256))"; cmd.ExecuteNonQuery(); cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE Roles (UserName nvarchar(256), RoleName nvarchar(256))"; cmd.ExecuteNonQuery(); cmd = conn.CreateCommand(); cmd.CommandText = "CREATE TABLE Settings (PropertyName nvarchar(256), PropertyStoredAs nvarchar(1), PropertyValue nvarchar(2048))"; cmd.ExecuteNonQuery(); } } } return CreateNewSqlCeConnection(connectionString, true); } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// private static Type GetSqlCeType(string typeName) { // Try versionless Type t = Type.GetType("System.Data.SqlServerCe." + typeName + ", System.Data.SqlServerCe", false, true); if (t != null) return t; // Try version 3.5 t = Type.GetType("System.Data.SqlServerCe." + typeName + ", System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91", false, true); if (t != null) return t; // Try version 3.0 t = Type.GetType("System.Data.SqlServerCe." + typeName + ", System.Data.SqlServerCe, Version=3.0.3600.0, Culture=neutral, PublicKeyToken=3be235df1c8d2ad3", false, true); if (t != null) return t; // Call 3.5 again to throw error return Type.GetType("System.Data.SqlServerCe." + typeName + ", System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91", true, true); } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// private static DbConnection CreateNewSqlCeConnection(string connectionString, bool openConn) { if (_SqlCeConnectionType == null) _SqlCeConnectionType = GetSqlCeType("SqlCeConnection"); DbConnection conn = (DbConnection) Activator.CreateInstance(_SqlCeConnectionType, new object[] {connectionString}); if (openConn) conn.Open(); return conn; } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// private static void AddSqlCeParameter(DbCommand cmd, string paramName, object paramValue) { if (_SqlCeParamType == null) _SqlCeParamType = GetSqlCeType("SqlCeParameter"); cmd.Parameters.Add((DbParameter) Activator.CreateInstance(_SqlCeParamType, new object[]{paramName, paramValue})); } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// internal static string GetFullDBFileName(string username, string extension) { return Path.Combine(Application.UserAppDataPath, GetPartialDBFileName(username, extension)); } internal static string GetPartialDBFileName(string username, string extension) { if (string.IsNullOrEmpty(username)) { return "Application" + extension; } char[] usernameChars = username.ToCharArray(); for (int iter = 0; iter < usernameChars.Length; iter++) if (!char.IsLetterOrDigit(usernameChars[iter])) usernameChars[iter] = '_'; return "User_" + new string(usernameChars) + extension; } } } // File provided for Reference Use Only by Microsoft Corporation (c) 2007.
Link Menu
This book is available now!
Buy at Amazon US or
Buy at Amazon UK
- CommonDialog.cs
- GetImportFileNameRequest.cs
- AnnotationStore.cs
- ProviderManager.cs
- DataSourceView.cs
- DBConnection.cs
- ModelItemDictionary.cs
- CodeAssignStatement.cs
- PreviousTrackingServiceAttribute.cs
- CallContext.cs
- FilterEventArgs.cs
- TargetConverter.cs
- AnnotationComponentChooser.cs
- nulltextnavigator.cs
- Wildcard.cs
- CompModSwitches.cs
- AliasedSlot.cs
- BitmapPalette.cs
- XmlDataImplementation.cs
- WizardStepBase.cs
- SimpleApplicationHost.cs
- IERequestCache.cs
- MessageHeaderAttribute.cs
- LayoutDump.cs
- UnsignedPublishLicense.cs
- ComPlusServiceHost.cs
- QilList.cs
- StateChangeEvent.cs
- ListViewDataItem.cs
- AddDataControlFieldDialog.cs
- NameService.cs
- ImageDrawing.cs
- TerminatorSinks.cs
- PathNode.cs
- HandleRef.cs
- CacheChildrenQuery.cs
- RuleAttributes.cs
- DoubleSumAggregationOperator.cs
- SecurityAccessDeniedException.cs
- DirtyTextRange.cs
- FormsIdentity.cs
- EventRouteFactory.cs
- DataGridViewCellPaintingEventArgs.cs
- serverconfig.cs
- SHA1CryptoServiceProvider.cs
- XmlSchemaImport.cs
- PhysicalOps.cs
- HostingPreferredMapPath.cs
- PcmConverter.cs
- TypeLibConverter.cs
- FacetValues.cs
- ExpressionBuilderCollection.cs
- LinkedResource.cs
- WindowsIPAddress.cs
- ZoneMembershipCondition.cs
- SelectionRangeConverter.cs
- NumberFunctions.cs
- CompleteWizardStep.cs
- Brushes.cs
- ToolStripDropDown.cs
- X509Utils.cs
- ConfigurationErrorsException.cs
- xdrvalidator.cs
- ComplexTypeEmitter.cs
- TableHeaderCell.cs
- RequestReplyCorrelator.cs
- DBAsyncResult.cs
- BamlLocalizableResourceKey.cs
- CommentEmitter.cs
- AvTrace.cs
- SystemColorTracker.cs
- HttpContext.cs
- EnumType.cs
- ContextMenuAutomationPeer.cs
- SecondaryIndexList.cs
- PriorityChain.cs
- ManagementInstaller.cs
- ClassImporter.cs
- SqlDataSourceCache.cs
- TagPrefixCollection.cs
- TextServicesLoader.cs
- RoleManagerModule.cs
- TreeWalker.cs
- SecUtil.cs
- FieldTemplateFactory.cs
- ObjectStateEntry.cs
- AuthenticatingEventArgs.cs
- AutomationElement.cs
- OperationCanceledException.cs
- Base64Stream.cs
- ResourcePool.cs
- DataGridViewMethods.cs
- Speller.cs
- ConfigXmlDocument.cs
- ModifierKeysValueSerializer.cs
- AnnotationAdorner.cs
- XmlCharacterData.cs
- ProgressiveCrcCalculatingStream.cs
- HttpApplicationFactory.cs
- ObjectDataSourceMethodEventArgs.cs