Webparts personalization Using Mysql
Posted by: jalpa prajapati
Date: November 23, 2011 12:21AM

Create One class
public class MysqlPersonalizationProvider : PersonalizationProvider
{
private string m_ApplicationName;
public override string ApplicationName
{
get { return m_ApplicationName; }
set { m_ApplicationName = value; }
}

private string m_ConnectionStringName;

public string ConnectionStringName
{
get { return m_ConnectionStringName; }
set { m_ConnectionStringName = value; }
}

public override void Initialize(string name, NameValueCollection config)
{
// Verify that config isn't null
if (config == null)
throw new ArgumentNullException("config");

// Assign the provider a default name if it doesn't have one
if (String.IsNullOrEmpty(name))
name = "SimpleMySqlPersonalizationProvider";

// Add a default "description" attribute to config if the
// attribute doesn't exist or is empty
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description",
"Simple MySql personalization provider");
}

// Call the base class's Initialize method
base.Initialize(name, config);

if (string.IsNullOrEmpty(config["connectionStringName"]))
{
throw new ProviderException
("ConnectionStringName property has not been specified");
}
else
{
m_ConnectionStringName = config["connectionStringName"];
config.Remove("connectionStringName");
}

if (string.IsNullOrEmpty(config["applicationName"]))
{
throw new ProviderException
("applicationName property has not been specified");
}
else
{
m_ApplicationName = config["applicationName"];
config.Remove("applicationName");
}

// Throw an exception if unrecognized attributes remain
if (config.Count > 0)
{
string attr = config.GetKey(0);
if (!String.IsNullOrEmpty(attr))
throw new ProviderException
("Unrecognized attribute: " + attr);
}

}

protected override void LoadPersonalizationBlobs (WebPartManager webPartManager, string path, string userName, ref byte[] sharedDataBlob, ref byte[] userDataBlob)
{
// Load shared state
sharedDataBlob = null;
userDataBlob = null;
object sharedBlobDataObject = null;
object userBlobDataObject = null;
string sSQLShared = null;
string sSQLUser = null;
try
{
sSQLUser = "SELECT `personalizationblob` FROM `my_aspnet_personalization`" + Environment.NewLine +
"WHERE `username` = '" + userName + "' AND " + Environment.NewLine +
"`path` = '" + path + "' AND " + Environment.NewLine +
"`applicationname` = '" + m_ApplicationName + "';";
sSQLShared = "SELECT `personalizationblob` FROM `my_aspnet_personalization`" + Environment.NewLine +
"WHERE `username` IS NULL AND " + Environment.NewLine +
"`path` = '" + path + "' AND " + Environment.NewLine +
"`applicationname` = '" + m_ApplicationName + "';";

sharedBlobDataObject = RawDBQuery.ExecuteScalarOnDB(sSQLShared, System.Configuration.ConfigurationManager.ConnectionStrings[m_ConnectionStringName].ToString());
userBlobDataObject = RawDBQuery.ExecuteScalarOnDB(sSQLUser, System.Configuration.ConfigurationManager.ConnectionStrings[m_ConnectionStringName].ToString());
if (sharedBlobDataObject != null)
sharedDataBlob =
(byte[])sharedBlobDataObject;
if (userBlobDataObject != null)
userDataBlob =
(byte[])userBlobDataObject;
}
catch (FileNotFoundException)
{
// Not an error if file doesn't exist
}
finally
{
sSQLUser = null;
sSQLShared = null;
}
}

protected override void ResetPersonalizationBlob (WebPartManager webPartManager, string path, string userName)
{
// Delete the specified personalization file
string sSQL = null;
try
{
sSQL = "DELETE FROM `my_aspnet_personalization` WHERE `username` = '" + userName + "' AND `path` = '" + path + "' AND `applicationname` = '" + m_ApplicationName + "';";
RawDBQuery.ExecuteNonQueryOnDB(sSQL, System.Configuration.ConfigurationManager.ConnectionStrings[m_ConnectionStringName].ToString());
}
catch (MySqlException) { }
}

protected override void SavePersonalizationBlob (WebPartManager webPartManager, string path, string userName, byte[] dataBlob)
{
MySqlCommand updateCommand = null;
MySqlConnection updateConnection = null;
string sSQL = null;
try
{
sSQL = "SELECT COUNT(`username`) FROM `my_aspnet_personalization` WHERE `username` = '" + userName + "' AND `path` = '" + path + "' and `applicationname` = '" + m_ApplicationName + "';";
updateConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings[m_ConnectionStringName].ToString());
if (int.Parse(RawDBQuery.ExecuteScalarOnDB(sSQL,ConfigurationManager.ConnectionStrings[m_ConnectionStringName].ToString()).ToString()) > 0)
{
sSQL = "UPDATE `my_aspnet_personalization` SET `personalizationblob` = '' WHERE `username` = '' AND `applicationname` = '' AND `path` = '';";
updateCommand = new MySqlCommand(sSQL, updateConnection);
updateCommand.Parameters.Clear();
updateCommand.Parameters.Add(new MySqlParameter("personalizationblob", dataBlob));
updateCommand.Parameters.Add(new MySqlParameter("username", userName));
updateCommand.Parameters.Add(new MySqlParameter("applicationname", m_ApplicationName));
updateCommand.Parameters.Add(new MySqlParameter("path", path));
}
else
{
sSQL = "INSERT INTO `my_aspnet_personalization` (`username`,`path`,`applicationname`,`personalizationblob`) VALUES ('','','', '');";
updateCommand = new MySqlCommand(sSQL, updateConnection);
updateCommand.Parameters.Clear();
updateCommand.Parameters.Add(new MySqlParameter("username", userName));
updateCommand.Parameters.Add(new MySqlParameter("path", path));
updateCommand.Parameters.Add(new MySqlParameter("applicationname", m_ApplicationName));
updateCommand.Parameters.Add(new MySqlParameter("personalizationblob", dataBlob));
}
updateConnection.Open();
updateCommand.ExecuteNonQuery();
}
finally
{
if (updateConnection != null)
if (updateConnection.State != System.Data.ConnectionState.Closed)
updateConnection.Close();
else
updateConnection.Dispose();
updateConnection = null;
if (updateCommand != null) updateCommand.Dispose();
updateCommand = null;
sSQL = null;
}
}

public override PersonalizationStateInfoCollection FindState
(PersonalizationScope scope, PersonalizationStateQuery query,
int pageIndex, int pageSize, out int totalRecords)
{
throw new NotSupportedException();
}

public override int GetCountOfState(PersonalizationScope scope,
PersonalizationStateQuery query)
{
throw new NotSupportedException();
}

public override int ResetState(PersonalizationScope scope,
string[] paths, string[] usernames)
{
throw new NotSupportedException();
}

public override int ResetUserState(string path,
DateTime userInactiveSinceDate)
{
throw new NotSupportedException();
}
}
2.Create one another class

public static class RawDBQuery
{
public static object ExecuteScalarOnDB(string sSQL, string s_ConnectionString)
{
MySqlConnection QConnection = null;
MySqlCommand QCommand = null;
// System.Data.Odbc.OdbcConnection QConnection = null;
// System.Data.Odbc.OdbcCommand QCommand = null;
try
{
QConnection = new MySqlConnection(s_ConnectionString);
QCommand = new MySqlCommand(sSQL, QConnection);

QConnection.Open();

return QCommand.ExecuteScalar();
}
finally
{
if (QCommand != null) QCommand.Dispose();
QCommand = null;
if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed) QConnection.Close();
if (QConnection != null) QConnection.Dispose();
QConnection = null;
}
}

public static void ExecuteNonQueryOnDB(string sSQL, string s_ConnectionString)
{
MySqlConnection QConnection = null;
MySqlCommand QCommand = null;
try
{
QConnection = new MySqlConnection(s_ConnectionString);
QCommand = new MySqlCommand(sSQL, QConnection);

QConnection.Open();

QCommand.ExecuteNonQuery();
}
finally
{
if (QCommand != null) QCommand.Dispose();
QCommand = null;
if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed) QConnection.Close();
if (QConnection != null) QConnection.Dispose();
QConnection = null;
}
}

public static MySqlDataReader ExecuteReaderQueryOnDB(string sSQL, string s_ConnectionString)
{
MySqlConnection QConnection = null;
MySqlCommand QCommand = null;
try
{
QConnection = new MySqlConnection(s_ConnectionString);
QCommand = new MySqlCommand(sSQL, QConnection);

QConnection.Open();
return QCommand.ExecuteReader();

}
finally
{
if (QCommand != null) QCommand.Dispose();
QCommand = null;
if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed) QConnection.Close();
if (QConnection != null) QConnection.Dispose();
QConnection = null;
}
}

public static System.Data.DataSet ExecuteDatasetQueryOnDB(string sSQL, string sConnectionString)
{
MySqlDataAdapter QDataAdapter = null;
DataSet QDataSet = null;
try
{
QDataSet = new DataSet();
QDataAdapter = new MySqlDataAdapter(sSQL, sConnectionString);

QDataAdapter.Fill(QDataSet);

return QDataSet;
}
finally
{
if (QDataSet != null) QDataSet.Dispose();
QDataSet = null;
if (QDataAdapter != null) QDataAdapter.Dispose();
QDataAdapter = null;
}
}
}
3.Add configration in webconfig
<webParts>
<personalization defaultProvider="MyMultiPageProvider">
<authorization>
<allow users="*" verbs="enterSharedScope"/>
</authorization>
<providers>
<clear/>
<add connectionStringName="YourConnection" applicationName="/" name="MyMultiPageProvider" type="Providers.MysqlPersonalizationProvider"/>
</providers>
</personalization>
</webParts>
4.small change in .aspx desgin view
<asp:WebPartManager ID="WebPartManager1" runat="server">
<Personalization ProviderName="MyMultiPageProvider" />
</asp:WebPartManager>
5. run the solution

Options: ReplyQuote


Subject
Views
Written By
Posted
Webparts personalization Using Mysql
1544
November 23, 2011 12:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.