public static DbDataAdapter CreateDataAdapter(DbCommand dbCommand, DbConnection connection)
{
DbDataAdapter adapter; //we can't construct an adapter directly
//So let's run around the block 3 times, before potentially crashing
if (connection is System.Data.SqlClient.SqlConnection)
{
adapter = new System.Data.SqlClient.SqlDataAdapter(dbCommand.CommandText, connection as SqlConnection);
}
else if (connection is System.Data.OleDb.OleDbConnection)
{
adapter = new System.Data.OleDb.OleDbDataAdapter(dbCommand.CommandText, connection as System.Data.OleDb.OleDbConnection);
}
else if (connection is System.Data.Odbc.OdbcConnection)
{
adapter = new System.Data.Odbc.OdbcDataAdapter(dbCommand.CommandText, connection as System.Data.Odbc.OdbcConnection);
}
else if (connection is System.Data.SqlServerCe.SqlCeConnection)
{
adapter = new System.Data.SqlServerCe.SqlCeDataAdapter(dbCommand.CommandText, connection as System.Data.SqlServerCe.SqlCeConnection);
}
//TODO: Add more DbConnection kinds as they become invented
else
{
throw new Exception("[CreateDataAdapter] Unknown DbConnection type: " + connection.GetType().FullName);
}
return(adapter);
}
private void button1_Click(object sender, EventArgs e) //save
{
System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection();
System.Data.SqlServerCe.SqlCeDataAdapter daHangmanDB;
DataRow drow;
DataSet dsSavedGames = new DataSet();
DataSet dsSavedGame_Words = new DataSet();
con.ConnectionString = "Data Source=HangmanDB.sdf";
con.Open();
daHangmanDB = new System.Data.SqlServerCe.SqlCeDataAdapter("SELECT * From SavedGames", con);
daHangmanDB.Fill(dsSavedGames, "SavedGames");
con.Close();
if (dsSavedGames.Tables["SavedGames"].Rows.Count > 0)
{
drow = dsSavedGames.Tables["SavedGames"].Rows[dsSavedGames.Tables["SavedGames"].Rows.Count - 1];
latestID = System.Convert.ToInt32(drow[0]) + 1;
}
else
{
latestID = 0;
}
drow = dsSavedGames.Tables["SavedGames"].NewRow();
drow[1] = textBox1.Text;
drow[2] = wins;
drow[3] = losses;
drow[4] = DateTime.Now;
dsSavedGames.Tables["SavedGames"].Rows.Add(drow);
con.Open();
System.Data.SqlServerCe.SqlCeCommandBuilder cb;
cb = new System.Data.SqlServerCe.SqlCeCommandBuilder(daHangmanDB);
cb.DataAdapter.Update(dsSavedGames.Tables["SavedGames"]);
con.Close();
/*---------------------------------------------------------------------*/
con.Open();
daHangmanDB = new System.Data.SqlServerCe.SqlCeDataAdapter("SELECT * From SavedGame_Words", con);
daHangmanDB.Fill(dsSavedGame_Words, "SavedGame_Words");
con.Close();
foreach (int id in wordsplayedimported)
{
drow = dsSavedGame_Words.Tables["SavedGame_Words"].NewRow();
drow[0] = latestID;
drow[1] = id;
dsSavedGame_Words.Tables["SavedGame_Words"].Rows.Add(drow);
}
con.Open();
cb = new System.Data.SqlServerCe.SqlCeCommandBuilder(daHangmanDB);
cb.DataAdapter.Update(dsSavedGame_Words.Tables["SavedGame_Words"]);
con.Close();
MessageBox.Show("Game Saved");
}
private void button1_Click(object sender, EventArgs e)
{
int selectedID = System.Convert.ToInt32(listView1.SelectedItems[0].SubItems[0].Text);
wins = System.Convert.ToInt32(listView1.SelectedItems[0].SubItems[2].Text);
losses = System.Convert.ToInt32(listView1.SelectedItems[0].SubItems[3].Text);
System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection();
System.Data.SqlServerCe.SqlCeDataAdapter daHangmanDB;
DataRow drow;
DataSet dsSavedGame_Words = new DataSet();
con.ConnectionString = "Data Source=HangmanDB.sdf";
con.Open();
daHangmanDB = new System.Data.SqlServerCe.SqlCeDataAdapter("SELECT * From SavedGame_Words", con);
daHangmanDB.Fill(dsSavedGame_Words, "SavedGame_Words");
con.Close();
for (int i = 0; i < dsSavedGame_Words.Tables["SavedGame_Words"].Rows.Count; ++i)
{
drow = dsSavedGame_Words.Tables["SavedGame_Words"].Rows[i];
if (System.Convert.ToInt32(drow[0]) == selectedID)
{
wordsplayed.Add(System.Convert.ToInt32(drow[1]));
}
}
this.Close();
}
public LoadMenu()
{
InitializeComponent();
button1.DialogResult = DialogResult.OK;
System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection();
System.Data.SqlServerCe.SqlCeDataAdapter daHangmanDB;
DataRow drow;
DataSet dsSavedGames = new DataSet();
con.ConnectionString = "Data Source=HangmanDB.sdf";
con.Open();
daHangmanDB = new System.Data.SqlServerCe.SqlCeDataAdapter("SELECT * From SavedGames", con);
daHangmanDB.Fill(dsSavedGames, "SavedGames");
con.Close();
for (int i = 0; i < dsSavedGames.Tables["SavedGames"].Rows.Count; ++i)
{
drow = dsSavedGames.Tables["SavedGames"].Rows[i];
ListViewItem listItem = new ListViewItem(System.Convert.ToString(drow[0]));
listItem.SubItems.Add(System.Convert.ToString(drow[1]));
listItem.SubItems.Add(System.Convert.ToString(drow[2]));
listItem.SubItems.Add(System.Convert.ToString(drow[3]));
listItem.SubItems.Add(System.Convert.ToString(drow[4]));
listView1.Items.Insert(0, listItem);
}
}
private void button3_Click(object sender, EventArgs e) //Loading a new wordlist
{
OpenFileDialog loadlist = new OpenFileDialog();
loadlist.Filter = "Text Files (.txt)|*.txt|All Files (*.*)|*.*";
loadlist.FilterIndex = 1;
loadlist.Multiselect = false;
loadlist.InitialDirectory = "";
if (loadlist.ShowDialog() == DialogResult.OK) //user clicked ok
{
String Filepath = loadlist.FileName;
System.IO.StreamReader inpfile = new System.IO.StreamReader(Filepath);
System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection();
System.Data.SqlServerCe.SqlCeDataAdapter daHangmanDB;
DataRow drow;
DataSet dsWordlist = new DataSet();
con.ConnectionString = "Data Source=HangmanDB.sdf";
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "DELETE FROM Wordlist";
cmd.ExecuteNonQuery();
daHangmanDB = new System.Data.SqlServerCe.SqlCeDataAdapter("SELECT * From Wordlist", con);
daHangmanDB.Fill(dsWordlist, "Wordlist");
con.Close();
String data, category, words;
while ((data = inpfile.ReadLine()) != null)
{
category = data.Split(',')[0];
words = data.Split(',')[1];
drow = dsWordlist.Tables["Wordlist"].NewRow();
drow[1] = category;
drow[2] = words;
dsWordlist.Tables["Wordlist"].Rows.Add(drow);
}
con.Open();
System.Data.SqlServerCe.SqlCeCommandBuilder cb;
cb = new System.Data.SqlServerCe.SqlCeCommandBuilder(daHangmanDB);
cb.DataAdapter.Update(dsWordlist.Tables["Wordlist"]);
con.Close();
MessageBox.Show("Database Updated.");
}
}
private void button2_Click(object sender, EventArgs e) //delete
{
System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection();
System.Data.SqlServerCe.SqlCeDataAdapter daHangmanDB;
DataRow drow;
DataSet dsSavedGame_Words = new DataSet();
con.ConnectionString = "Data Source=HangmanDB.sdf";
con.Open();
daHangmanDB = new System.Data.SqlServerCe.SqlCeDataAdapter("SELECT * From SavedGame_Words", con);
daHangmanDB.Fill(dsSavedGame_Words, "SavedGame_Words");
con.Close();
int selectedID = System.Convert.ToInt32(listView1.SelectedItems[0].SubItems[0].Text);
for (int i = 0; i < dsSavedGame_Words.Tables["SavedGame_Words"].Rows.Count; ++i)
{
drow = dsSavedGame_Words.Tables["SavedGame_Words"].Rows[i];
if (System.Convert.ToInt32(drow[0]) == selectedID)
{
dsSavedGame_Words.Tables["SavedGame_Words"].Rows[i].Delete(); //Marks row for deletion (does not remove)
}
}
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "DELETE FROM SavedGame_Words";
cmd.ExecuteNonQuery();
con.Close();
con.Open();
System.Data.SqlServerCe.SqlCeCommandBuilder cb;
cb = new System.Data.SqlServerCe.SqlCeCommandBuilder(daHangmanDB);
cb.DataAdapter.Update(dsSavedGame_Words.Tables["SavedGame_Words"]);
con.Close();
MessageBox.Show("Database Updated.");
con.Close();
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "DELETE FROM SavedGames WHERE ID=" + System.Convert.ToString(selectedID);
cmd.ExecuteNonQuery();
con.Close();
listView1.Items.Remove(listView1.SelectedItems[0]);
}
public static DbDataAdapter CreateDataAdapter(DbCommand dbCommand, DbConnection connection)
{
DbDataAdapter adapter; //we can't construct an adapter directly
//So let's run around the block 3 times, before potentially crashing
if (connection is System.Data.SqlClient.SqlConnection)
adapter = new System.Data.SqlClient.SqlDataAdapter(dbCommand.CommandText, connection as SqlConnection);
else if (connection is System.Data.OleDb.OleDbConnection)
adapter = new System.Data.OleDb.OleDbDataAdapter(dbCommand.CommandText, connection as System.Data.OleDb.OleDbConnection);
else if (connection is System.Data.Odbc.OdbcConnection)
adapter = new System.Data.Odbc.OdbcDataAdapter(dbCommand.CommandText, connection as System.Data.Odbc.OdbcConnection);
else if (connection is System.Data.SqlServerCe.SqlCeConnection)
adapter = new System.Data.SqlServerCe.SqlCeDataAdapter(dbCommand.CommandText, connection as System.Data.SqlServerCe.SqlCeConnection);
//TODO: Add more DbConnection kinds as they become invented
else
{
throw new Exception("[CreateDataAdapter] Unknown DbConnection type: " + connection.GetType().FullName);
}
return adapter;
}
private void EditarPonto_Closing(object sender, CancelEventArgs e)
{
//string sql = "select * from usuario where nome = '" + Usuario.Nome + "' and senha = '" + Usuario.Senha + "'";
string sql = "select * from usuario where nome = '" + Usuario.Nome + "'";
if (Conn.sqlConndb.State == ConnectionState.Closed)
{
Conn.sqlConndb.Open();
}
System.Data.SqlServerCe.SqlCeDataAdapter adapter = new System.Data.SqlServerCe.SqlCeDataAdapter(sql, Conn.sqlConndb);
System.Data.DataTable table = new System.Data.DataTable();
if (Conn.sqlConndb.State == ConnectionState.Closed)
Conn.sqlConndb.Open();
adapter.Fill(table);
System.Data.DataRowCollection rows = table.Rows;
Conn.sqlConndb.Close();
//conndb.Dispose();
if (rows.Count > 0)
{
string tipo = (string)rows[0]["tipo"];
Usuario.Tipo = tipo;
Usuario.SemFase = (bool)rows[0]["semFase"];
Usuario.SemEstrutura = (bool)rows[0]["semEstrutura"];
Usuario.SemEsforco = (bool)rows[0]["semEsforco"];
Usuario.SemMaterial = (bool)rows[0]["semMaterial"];
Usuario.QuantMinDigitosBarramento = (int)rows[0]["qtDigitBarr"];
Usuario.QuantMaxDigitosBarramento = (int)rows[0]["qtMaxDigitBarr"];
int gps = (int)rows[0]["gps"];
//1 - Trimble
//2 - Holux
//3 - Sem GPS
if (gps == 1)
{
Usuario.UsandoTrimble = true;
Usuario.UsandoHolux = false;
}
if (gps == 2)
{
Usuario.UsandoTrimble = false;
Usuario.UsandoHolux = true;
}
if (gps == 3)
{
Usuario.UsandoTrimble = false;
Usuario.UsandoHolux = false;
}
}
}
/// <summary>
/// Read the values from database and returns ClassList
/// </summary>
/// <returns></returns>
protected System.Collections.Generic.List <ClassList> ReadFromDatabase(int SelectType)
{
System.Collections.Generic.List <ClassList> lstClass = new System.Collections.Generic.List <ClassList>();
string strCon = @"Data Source=..\..\..\..\..\..\..\Common\Data\Diagram\db\Diagram.sdf";
System.Data.SqlServerCe.SqlCeConnection connection = new System.Data.SqlServerCe.SqlCeConnection(strCon);
connection.Open();
System.Data.SqlServerCe.SqlCeDataAdapter adapter;
SqlCeCommand comm;
if (SelectType == 1)
{
comm = new SqlCeCommand("Select * from tblClassDiagram", connection);
}
else
{
comm = new SqlCeCommand("Select * from tblClassDiagram1 order by ClassId", connection);
}
adapter = new System.Data.SqlServerCe.SqlCeDataAdapter(comm);
System.Data.DataSet ds = new System.Data.DataSet("Table");
adapter.Fill(ds);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
ClassList classObj = new ClassList();
classObj.ClassName = dt.Rows[i]["ClassName"].ToString();
classObj.ClassType = dt.Rows[i]["ClassType"].ToString();
classObj.ObjectType = dt.Rows[i]["ObjectType"].ToString();
for (int j = i; j < dt.Rows.Count; j++)
{
if (dt.Rows[i]["ClassName"].ToString() == dt.Rows[j]["ClassName"].ToString())
{
if (!string.IsNullOrEmpty(dt.Rows[j]["Properties"].ToString()))
{
PropertyList propertyList = new PropertyList();
propertyList.PropertyName = dt.Rows[j]["Properties"].ToString();
propertyList.PropertyType = (PropertyType)(Enum.Parse(typeof(PropertyType), dt.Rows[j]["PropertyType"].ToString()));
classObj.PropertyList.Add(propertyList);
}
if (!string.IsNullOrEmpty(dt.Rows[j]["Methods"].ToString()))
{
MethodList methodList = new MethodList();
methodList.MethodName = dt.Rows[j]["Methods"].ToString();
methodList.MethodType = (MethodType)(Enum.Parse(typeof(MethodType), dt.Rows[j]["MethodType"].ToString()));
classObj.MethodList.Add(methodList);
}
}
else
{
i = j - 1;
lstClass.Add(classObj);
break;
}
if (j == dt.Rows.Count - 1)
{
i = j;
lstClass.Add(classObj);
break;
}
}
}
}
return(lstClass);
}
/**
* Returns the nearst bts to the location passed
*
*/
public System.Data.DataTable GetNearstStations(double lat, double lon)
{
string connString = @"Data Source=" + path + @"\Database.sdf";
String[] result = new String[6];
// To radians
lat *= 0.01745;
lon *= 0.01745;
// string sql = @" SELECT top(12) site, ci_1,
// (6378.388 * acos(sin(@latitude) * sin(lat_1_deg*0.01745) + cos(@latitude) * cos(lat_1_deg*0.01745) * cos(lon_1_deg*0.01745 - @longitude))) as distance,
// lon_1_deg, lat_1_deg, bore
// FROM bts_table
// ORDER BY distance ASC";
string sql = @" select t1.site, t1.ci_1, t1.bore, t1.lat_1_deg, t1.lon_1_deg from bts_table t1
inner join (
SELECT distinct top(3) site,
(6378.388 * acos(sin(@latitude) * sin(lat_1_deg*0.01745) + cos(@latitude) * cos(lat_1_deg*0.01745) *
cos(lon_1_deg*0.01745 - @longitude))) as distance
FROM bts_table
ORDER BY distance ASC
) t2
on t1.site = t2.site";
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.DataTable endTable = new System.Data.DataTable();
endTable.Columns.Add("site", typeof(String));
endTable.Columns.Add("ci_1", typeof(String));
endTable.Columns.Add("distance", typeof(double));
using (System.Data.SqlServerCe.SqlCeCommand com = new System.Data.SqlServerCe.SqlCeCommand(sql, conn))
{
com.Parameters.AddWithValue("@latitude", lat);
com.Parameters.AddWithValue("@longitude", lon);
System.Data.SqlServerCe.SqlCeDataAdapter da = new System.Data.SqlServerCe.SqlCeDataAdapter(com);
da.Fill(dt);
//foreach (System.Data.DataRow row in dt.Rows)
//{
// string site = row["site"].ToString();
// string cellIndex = row["ci_1"].ToString();
// result[i] = site;
// result[i + 1] = cellIndex;
// i += 2;
//}
foreach (System.Data.DataRow row in dt.Rows)
{
string site = row["site"].ToString();
string cell = row["ci_1"].ToString();
double bore = Convert.ToDouble(row["bore"].ToString());
double latitude = Convert.ToDouble(row["lat_1_deg"].ToString());
double longitude = Convert.ToDouble(row["lon_1_deg"].ToString());
bore *= 0.01745;
latitude *= 0.01745;
longitude *= 0.01745;
double latNew = NewLatitude(bore, latitude);
double lonNew = NewLongitude(bore, longitude, latitude, latNew);
double distance = CalculateDistance(latNew, lonNew, lat, lon);
endTable = AddToTable(endTable, distance, site, cell);
}
}
return endTable;
}
public System.Data.DataTable GetGraphData(String kpi, String startDate, String endDate, int ci)
{
System.Data.DataTable table = new System.Data.DataTable();
string sql = "select time, " + kpi + " from complaints Where (CAST(time AS DATETIME) >= CAST('" + startDate + "' AS DATETIME)) and (CAST(time AS DATETIME) <= CAST('"+ endDate + @"' AS DATETIME)) and (ci = " + ci + " ) ";
using (System.Data.SqlServerCe.SqlCeCommand com = new System.Data.SqlServerCe.SqlCeCommand(sql, conn))
{
System.Data.SqlServerCe.SqlCeDataAdapter da = new System.Data.SqlServerCe.SqlCeDataAdapter(com);
da.Fill(table);
}
return table;
}
