private int MaxOldUserid()
{
//Find the max userid in database <rating>,that is the max userid of old users with ratings history
int maxolduserid = 0;
Connection con = new CFRMovie.Connection();
SqlDataReader drNew;
SqlConnection con2 = new SqlConnection("Data Source=PSUCP3-PC\\SQLEXPRESS;Initial Catalog=CFDB;Integrated Security=True");
SqlCommand cmd = new SqlCommand(@"select userid from dbo.[rating]", con2);
try
{
con2.Open();
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
drNew = myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
if (drNew.HasRows)
{
while (drNew.Read())
{
maxolduserid = int.Parse(drNew[0].ToString());
}
}
return(maxolduserid);
}
void DeleteRecords()
{
Connection con = new CFRMovie.Connection();
string sql = string.Format(@"Delete FROM dbo.[user] WHERE dbo.[user].userid={0}", textBox1.Text);
SqlCommand cmd = new SqlCommand(sql, con.ActiveCon());
cmd.ExecuteNonQuery();
}
void UpdateRecords()
{
Connection con = new CFRMovie.Connection();
string sql = string.Format(@"UPDATE [dbo].[user] SET [userid] = {0},[age] = {1} ,[gender] = '{2}',
[occupation] = '{3}' ,[zipcode] ={4} WHERE[userid] = {0}", textBox1.Text, textBox2.Text,
textBox3.Text, textBox4.Text, textBox5.Text);
SqlCommand cmd = new SqlCommand(sql, con.ActiveCon());
cmd.ExecuteNonQuery();
}
void AddRecords()
{
Connection con = new CFRMovie.Connection();
string sql = string.Format(@"INSERT INTO[dbo].[user]([userid],[age] ,[gender] ,[occupation],[zipcode])
VALUES ({0},{1},'{2}','{3}',{4})", textBox1.Text, textBox2.Text, textBox3.Text,
textBox4.Text, textBox5.Text);
SqlCommand cmd = new SqlCommand(sql, con.ActiveCon());
cmd.ExecuteNonQuery();
}
void PrintUser()
{
Connection con = new CFRMovie.Connection();
string sql = "SELECT * FROM dbo.[user]";
DataSet ds = new DataSet();
SqlDataAdapter command = new SqlDataAdapter(sql, con.ActiveCon());
command.Fill(ds, "ds");
this.dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
private void PrintPredictRatingsInOder()
{
//Print out the predicted ratings in order in database table dbo.[predictratings], selected by userid
int maxolduserid = MaxOldUserid();
int userid = int.Parse(textBox1.Text);
int similaruserid = 0;
if (userid < 1 && userid > 945)
{
MessageBox.Show("invalid user id!");
}
if (userid <= maxolduserid)//see if the user has rating history, if so ......
{
Connection con = new CFRMovie.Connection();
string sql = string.Format(@"SELECT * FROM dbo.[predictratings] WHERE dbo.[predictratings].userid = {0} ORDER BY dbo.[predictratings].predictrating DESC", textBox1.Text);
DataSet ds = new DataSet();
SqlDataAdapter command = new SqlDataAdapter(sql, con.ActiveCon());
command.Fill(ds, "ds");
this.dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
if (userid > maxolduserid)//see if the user has rating history, if not ......
{
Connection con = new CFRMovie.Connection();
string strSQL = string.Format(@"select * from dbo.[user] WHERE dbo.[user].userid={0}", textBox1.Text);
SqlDataReader drNew;
SqlConnection con2 = new SqlConnection("Data Source=PSUCP3-PC\\SQLEXPRESS;Initial Catalog=CFDB;Integrated Security=True");
SqlCommand cmd = new SqlCommand(strSQL, con2);
try
{
con2.Open();
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
drNew = myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
if (drNew.HasRows)
{
while (drNew.Read())
{
int id = int.Parse(drNew[0].ToString());
int age = int.Parse(drNew[1].ToString());
string gender = drNew[2].ToString();
string occupation = drNew[3].ToString();
similaruserid = NewUserSimlarUserFind(id, age, gender, occupation);
}
}
string sql = string.Format(@"SELECT * FROM dbo.[predictratings] WHERE dbo.[predictratings].userid = {0} ORDER BY dbo.[predictratings].predictrating DESC", similaruserid);
DataSet ds = new DataSet();
SqlDataAdapter command = new SqlDataAdapter(sql, con.ActiveCon());
command.Fill(ds, "ds");
this.dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
}
private void InitialofPredictTable()
{
//Put the predicted ratings we calculated into the database table [CFDB].[dbo].[predictratings]
Connection con = new CFRMovie.Connection();
for (int i = 1; i <= PredictSetNum; i++)
{
PredictTable[i] = new double[MovieNum + 1];
for (int j = 1; j <= MovieNum; j++)
{
PredictTable[i][j] = 0;
}
}
for (int i = 1; i <= PredictSetNum; i++)
{
for (int j = 1; j <= MovieNum; j++)
{
if (RateTable[i + TrainSetNum][j] != 0)
{
PredictTable[i][j] = PredictCompute(i, j);
if (PredictTable[i][j] > 0)
{
int reali = i + 780;
string sql = string.Format(@"IF EXISTS (SELECT * FROM [CFDB].[dbo].[predictratings]
WHERE [CFDB].[dbo].[predictratings].userid= {0} and [CFDB].[dbo].[predictratings].movieid= {1})
UPDATE [CFDB].[dbo].[predictratings] set [CFDB].[dbo].[predictratings].predictrating = {2}
WHERE [CFDB].[dbo].[predictratings].userid= {0} and [CFDB].[dbo].[predictratings].movieid= {1}
ELSE INSERT INTO [CFDB].[dbo].[predictratings] ([userid] ,[movieid],[predictrating])
VALUES ({0},{1},{2})", reali, j, PredictTable[i][j]);
SqlCommand cmd = new SqlCommand(sql, con.ActiveCon());
cmd.ExecuteNonQuery();
}
}
}
}
}
