sqldataprovider.cs

SharpNuke源代码

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;

using Microsoft.ApplicationBlocks.Data;

using DotNetNuke;
using DotNetNuke.Common.Utilities;
using DotNetNuke.Framework.Providers;
using DotNetNuke.Entities.Users;

//
// DotNetNuke -  http://www.dotnetnuke.com
// Copyright (c) 2002-2005
// by Shaun Walker ( [e-mail] ) of Perpetual Motion Interactive Systems Inc. ( http://www.perpetualmotion.ca )
//
// Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated
// documentation files (the "Software"), to deal in the Software without restriction, including without limitation
// the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and
// to permit persons to whom the Software is furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in all copies or substantial portions
// of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
// TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
// THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
// CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
// DEALINGS IN THE SOFTWARE.
//

namespace DotNetNuke.Data
{
	
	public class SqlDataProvider : DataProvider
	{
		#region private members
		private const string ProviderType = "data";
		
		private ProviderConfiguration providerConfiguration;
		private string connectionString;
		private string providerPath;
		private string objectQualifier;
		private string databaseOwner;
		private string upgradeConnectionString;
		#endregion

		#region .ctor
		public SqlDataProvider() 
		{
			providerConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType);
			
			// Read the configuration specific information for this provider
			Provider provider = (Provider) providerConfiguration.Providers[providerConfiguration.DefaultProvider];
			
			// Read the attributes for this provider
			string connectionStringName = provider.Attributes["connectionStringName"];
			if (connectionStringName != null && connectionStringName.Length > 0 && System.Configuration.ConfigurationSettings.AppSettings[connectionStringName] != null && System.Configuration.ConfigurationSettings.AppSettings[connectionStringName].Length > 0)
			{
				connectionString = System.Configuration.ConfigurationSettings.AppSettings[connectionStringName];
			}
			else
			{
				connectionString = provider.Attributes["connectionString"];
			}
						
			providerPath = provider.Attributes["providerPath"];
			
			objectQualifier = provider.Attributes["objectQualifier"];
			if (objectQualifier != null  && objectQualifier.Length > 0 && !objectQualifier.EndsWith("_"))
			{
				objectQualifier += "_";
			}
			
			databaseOwner = provider.Attributes["databaseOwner"];
			if (databaseOwner != "" && databaseOwner.Length > 0 && !databaseOwner.EndsWith("."))
			{
				databaseOwner += ".";
			}
			
			upgradeConnectionString = provider.Attributes["upgradeConnectionString"];
			if (upgradeConnectionString == null || upgradeConnectionString.Length == 0)
			{
				upgradeConnectionString = connectionString;
			}
			
		}
		#endregion

		#region public properties
		public string ConnectionString
		{
			get { return this.connectionString; }
		}
		
		public string ProviderPath
		{
			get { return this.providerPath; }
		}
		
		public string ObjectQualifier
		{
			get { return this.objectQualifier; }
		}
		
		public string DatabaseOwner
		{
			get { return this.databaseOwner; }
		}
		
		public string UpgradeConnectionString
		{
			get { return this.upgradeConnectionString; }
		}
		#endregion

		#region general
		private object GetNull(object field)
		{
			return Null.GetNull(field, DBNull.Value);
		}
		#endregion
		
		#region upgrade
		public override string GetProviderPath()
		{
			string returnValue;
			HttpContext httpContext = HttpContext.Current;
			
			returnValue = this.providerPath;
			
			if (returnValue != null && returnValue.Length > 0)
			{
				returnValue = httpContext.Server.MapPath(returnValue);
				
				if (Directory.Exists(returnValue))
				{
					try
					{
						// check if database is initialized
						IDataReader dr = GetDatabaseVersion();
						dr.Close();
					}
					catch
					{
						// initialize the database
						StreamReader streamReader;
						streamReader = File.OpenText(returnValue + "00.00.00." + providerConfiguration.DefaultProvider);
						string script = streamReader.ReadToEnd();
						streamReader.Close();
						
						if (ExecuteScript(script) != string.Empty)
						{
							returnValue = "ERROR: Could not connect to database specified in connectionString for SqlDataProvider";
						}
					}
				}
				else
				{
					returnValue = "ERROR: providerPath folder " + returnValue + " specified for SqlDataProvider does not exist on web server";
				}
			}
			else
			{
				returnValue = "ERROR: providerPath folder value not specified in web.config for SqlDataProvider";
			}
			return returnValue;
		}

		public override string ExecuteScript(string script)
		{
			return ExecuteScript(script, false);
		}

		public override string ExecuteScript(string script, bool useTransactions)
		{
			StringBuilder exceptions = new StringBuilder();
			string Delimiter = "GO\r\n";
			string sql = string.Empty;
			
			string[] arrSQL = System.Text.RegularExpressions.Regex.Split(script, Delimiter, RegexOptions.IgnoreCase);
			
			if (useTransactions)
			{
				SqlConnection connection = new SqlConnection(UpgradeConnectionString);
				connection.Open();
				try
				{
					SqlTransaction transaction = connection.BeginTransaction();
					bool ignoreErrors;
					
					foreach (string sqlCommand in arrSQL)
					{
						sql = sqlCommand.Trim();
						if (sql.Length > 0)
						{
							// script dynamic substitution
							sql = sql.Replace("{databaseOwner}", DatabaseOwner);
							sql = sql.Replace("{objectQualifier}", ObjectQualifier);
							
							ignoreErrors = false;
							
							if (sql.StartsWith("{IgnoreError}"))
							{
								ignoreErrors = true;
								sql = sql.Replace("{IgnoreError}", "");
							}
							
							try
							{
								SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sql);
							}
							catch (SqlException sqlException)
							{
								if (! ignoreErrors)
								{
									exceptions.Append(sqlException.ToString() + "\n\n" + sql + "\n\n");
								}
							}
						}
					}
					if (exceptions.Length == 0)
					{
						//No exceptions so go ahead and commit
						transaction.Commit();
					}
					else
					{
						//Found exceptions, so rollback db
						transaction.Rollback();
						exceptions.Append("SQL Execution failed.  Database was rolled back" + "\n\n" + sql + "\n\n");
					}
				}
				finally
				{
					connection.Close();
				}
			}
			else
			{
				foreach (string sqlCommand in arrSQL)
				{
					sql = sqlCommand.Trim();
					if (sql.Length > 0)
					{
						// script dynamic substitution
						sql = sql.Replace("{databaseOwner}", DatabaseOwner);
						sql = sql.Replace("{objectQualifier}", ObjectQualifier);
						
						try
						{
							SqlHelper.ExecuteNonQuery(UpgradeConnectionString, CommandType.Text, sql);
						}
						catch (SqlException sqlException)
						{
							exceptions.Append(sqlException.ToString() + "\n\n" + sql + "\n\n");
						}
					}
				}
			}
			
			// if the upgrade connection string is specified
			if (UpgradeConnectionString != ConnectionString)
			{
				try
				{
					// grant execute rights to the public role for all stored procedures. This is
					// necesary because the UpgradeConnectionString will create stored procedures
					// which restrict execute permissions for the ConnectionString user account.
					exceptions.Append(GrantStoredProceduresPermission("EXECUTE", "public"));
				}
				catch (SqlException sqlException)
				{
					exceptions.Append(sqlException.ToString() + "\n\n" + sql + "\n\n");
				}
			}
			
			return exceptions.ToString();
		}
		
		private string GrantStoredProceduresPermission(string permission, string loginOrRole)
		{
			StringBuilder sql = new StringBuilder();
			StringBuilder exceptions = new StringBuilder();
			try
			{
				// grant rights to a login or role for all stored procedures
				sql.Append("declare @exec nvarchar(2000) ");
				sql.Append("declare @name varchar(150) ");
				sql.Append("declare sp_cursor cursor for select o.name as name ");