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
- MainMenu.cs
- DriveNotFoundException.cs
- BamlTreeNode.cs
- StrongNameIdentityPermission.cs
- ExtensionCollection.cs
- AutoResetEvent.cs
- Message.cs
- AssemblyCollection.cs
- SocketElement.cs
- DiscoveryDocumentLinksPattern.cs
- TabletDevice.cs
- Environment.cs
- AssemblyUtil.cs
- JournalEntry.cs
- DataGridTextBoxColumn.cs
- PersistencePipeline.cs
- ContentOperations.cs
- QueryOperator.cs
- PreviewPrintController.cs
- BoundColumn.cs
- Ops.cs
- DeclaredTypeValidatorAttribute.cs
- ManifestResourceInfo.cs
- ValidationException.cs
- SizeLimitedCache.cs
- SoapExtensionTypeElementCollection.cs
- XmlNamespaceDeclarationsAttribute.cs
- ViewBase.cs
- MediaPlayerState.cs
- DataGridColumnCollection.cs
- RoutedEventValueSerializer.cs
- BridgeDataRecord.cs
- IDReferencePropertyAttribute.cs
- GridPatternIdentifiers.cs
- CurrentChangedEventManager.cs
- WindowsFormsLinkLabel.cs
- StateMachineWorkflow.cs
- WebPartConnectionsConnectVerb.cs
- ObjectPersistData.cs
- FrameworkTextComposition.cs
- DesignerHelpers.cs
- BitmapCacheBrush.cs
- hebrewshape.cs
- ToolStripCustomTypeDescriptor.cs
- MissingMethodException.cs
- CodeNamespaceCollection.cs
- GroupQuery.cs
- FontWeightConverter.cs
- DataControlFieldCollection.cs
- JobInputBins.cs
- HttpApplication.cs
- HtmlHead.cs
- SecurityCriticalDataForSet.cs
- FontConverter.cs
- ManipulationDelta.cs
- SoapHttpTransportImporter.cs
- Visual3D.cs
- FormViewUpdateEventArgs.cs
- PseudoWebRequest.cs
- ImageBrush.cs
- DataViewManagerListItemTypeDescriptor.cs
- LinearGradientBrush.cs
- Authorization.cs
- Vector3DAnimationBase.cs
- XmlSchemaComplexContentExtension.cs
- DomainUpDown.cs
- IntPtr.cs
- TdsParserStaticMethods.cs
- DesignerEditorPartChrome.cs
- Native.cs
- AttributeQuery.cs
- ObjectMemberMapping.cs
- ClientScriptManagerWrapper.cs
- DbModificationClause.cs
- WebException.cs
- TextTreeInsertUndoUnit.cs
- RotateTransform.cs
- DataGridViewComboBoxEditingControl.cs
- DataControlField.cs
- WebPartUtil.cs
- UserValidatedEventArgs.cs
- SqlVersion.cs
- XmlSchemaAttribute.cs
- SecurityUniqueId.cs
- MimeMapping.cs
- PackWebRequestFactory.cs
- GridErrorDlg.cs
- ItemMap.cs
- BinaryMethodMessage.cs
- OleDbParameter.cs
- DataTable.cs
- TypeListConverter.cs
- PageSetupDialog.cs
- FormViewInsertedEventArgs.cs
- _TransmitFileOverlappedAsyncResult.cs
- GACMembershipCondition.cs
- Vars.cs
- Semaphore.cs
- QilXmlWriter.cs
- BrowserCapabilitiesFactory.cs