public static void getWords()
{
#region Npgsql
NpgsqlConnection conn;
conn = new NpgsqlConnection("Server=" + Settings.DBSrvIP + ";Port=" + Settings.DBSrvPort + ";User Id=" +
Settings.DBconnectID + ";Password="******";Database=endoDB;" + Settings.sslSetting);
try
{
conn.Open();
}
catch (NpgsqlException)
{
MessageBox.Show(Properties.Resources.CouldntOpenConn, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
}
catch (System.IO.IOException)
{
MessageBox.Show(Properties.Resources.ConnClosed, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
}
#endregion
string sql = "SELECT no, words1, words2, words3, operator, word_order FROM words ORDER BY word_order";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
da.Fill(localDB, "words");
if (localDB.Tables["words"].Rows.Count == 0)
{ MessageBox.Show("[words]" + Properties.Resources.NoRecord, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
conn.Close();
}
public DataTable ExecuteSelectQuery(NpgsqlCommand command, NpgsqlConnection conn)
{
try
{
if (conn == null)
{
return null;
}
DataTable dataTable = new DataTable();
DataSet dataSet = new DataSet();
NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter(command);
dataSet.Reset();
dataAdapter.Fill(dataSet);
dataTable = dataSet.Tables[0];
conn.Close();
return dataTable.Rows.Count <= 0 ? null : dataTable;
}
catch (NpgsqlException ex)
{
Console.WriteLine(ex.Message);
return null;
}
}
public List <Models.TestSet.Test_Set> GetTestSet(Guid ID)
{
List <Models.TestSet.Test_Set> list = new List <Models.TestSet.Test_Set>();
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from qadata.ref_testset where row_idnumber = @ID;", con);
cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = ID;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow DR in ds.Tables[0].Rows)
{
Models.TestSet.Test_Set item = new Models.TestSet.Test_Set
{
Id = new Guid(Convert.ToString(DR["row_idnumber"])),
Title = Convert.ToString(DR["testset_title"]),
Description = Convert.ToString(DR["testset_description"]),
testset_id = Convert.ToInt32(DR["testset_id"]),
Status = Convert.ToInt32(DR["status"])
};
list.Add(item);
}
return(list);
}
public List <Models.TestSet.Test_Mapping> GetTestSetsCases(Guid TestSet_IDNUM)
{
List <Models.TestSet.Test_Mapping> list = new List <Models.TestSet.Test_Mapping>();
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"SELECT MAPP.*, tc.test_case_title FROM qadata.testset_case_mapping MAPP join qadata.testcasemain tc on MAPP.test_idnum = tc.row_idnum where MAPP.testset_idnum = @TestSet_IDNUM and MAPP.status >= 1 order by order_number;", con);
cmd.Parameters.Add("@TestSet_IDNUM", NpgsqlTypes.NpgsqlDbType.Uuid).Value = TestSet_IDNUM;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow DR in ds.Tables[0].Rows)
{
Models.TestSet.Test_Mapping item = new Models.TestSet.Test_Mapping
{
Id = new Guid(Convert.ToString(DR["row_idnum"])),
TestCase_Title = Convert.ToString(DR["test_case_title"]),
Order_Number = Convert.ToInt32(DR["order_number"]),
Test_Id = new Guid(Convert.ToString(DR["test_idnum"])),
Test_Set_Id = new Guid(Convert.ToString(DR["testset_idnum"])),
Status = Convert.ToInt32(DR["status"]),
};
list.Add(item);
}
return(list);
}
public List <Models.TestLevels.TestStructures> GetTopStructures()
{
List <Models.TestLevels.TestStructures> list = new List <Models.TestLevels.TestStructures>();
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from qadata.ref_structure where parent_idnumber = '00000000-0000-0000-0000-000000000000' order by name;", con);
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow DR in ds.Tables[0].Rows)
{
Models.TestLevels.TestStructures item = new Models.TestLevels.TestStructures();
item.id = new Guid(Convert.ToString(DR["row_idnumber"]));
item.Name = Convert.ToString(DR["name"]);
item.ShortName = Convert.ToString(DR["srtname"]);
item.Parent_IDNumber = new Guid(Convert.ToString(DR["parent_idnumber"]));
list.Add(item);
}
return(list);
}
//Йнкции пользователя
/// <summary>
/// добавление\удаление пользователя
/// добавление записи
/// удаление своей незакрытой записи
/// проверка
/// изменение прав пользователя
///
/// удаление закрытой записи
/// редактирование своей записи
/// редактирование чужой записи
///
/// </summary>
public RegisterForm()
{
InitializeComponent();
divList = new List<string>();
staList = new List<string>();
speList = new List<string>();
NpgsqlConnection cdbo = DBExchange.Inst.connectDb;
NpgsqlDataAdapter div = new NpgsqlDataAdapter("Select trim(name) from divisions order by division ASC", cdbo);
DataTable divTable = new DataTable();
div.Fill(divTable);
foreach (DataRow roww in divTable.Rows )
{
divList.Add((string)roww[0]);
}
otdel.DataSource = divList;
otdel.SelectedIndex = 1;
NpgsqlDataAdapter sta = new NpgsqlDataAdapter("Select trim(name) from posts order by post ASC", cdbo);
DataTable staTable = new DataTable();
sta.Fill(staTable);
foreach (DataRow roww in staTable.Rows)
{
staList.Add((string)roww[0]);
}
status.DataSource = staList;
status.SelectedIndex = 39;
//special.SelectedIndex = 0;
}
private List<string> getFreeDoctors()
{
DTDoctors = new DataTable();
string gfd = "Select doc_id, trim(family_name) as family_name, trim(first_name) as first_name from doctors where special < 4";
fDoc = new List<string>();
if (this.comboBox1.Items.Count > 0)
{
int _trial = (int)DTuserTrials.Rows[this.comboBox1.SelectedIndex]["tr_id"];
gfd = "Select doc_id, trim(family_name) as family_name, trim(first_name) as first_name from doctors ds where ds.special < 4 and ds.doc_id not in "
+"(select doc_id from trial_role where trial_id = '"+_trial+"')";
}
NpgsqlDataAdapter getfd = new NpgsqlDataAdapter(gfd,DBExchange.Inst.connectDb);
try
{
getfd.Fill(DTDoctors);
}
catch (Exception exception)
{
System.Windows.Forms.MessageBox.Show(exception.Message.ToString() + " getFreeDoctors");
}
foreach (DataRow ro in DTDoctors.Rows)
{
fDoc.Add(ro["family_name"] + " " + ro["first_name"]);
}
return fDoc;
}
protected void OnExecuteActionActivated(object sender, System.EventArgs e)
{
string connectionString = "Server=localhost;Database=aula;User Id=aula;Password=clase" ;
IDbConnection dbConnection = new NpgsqlConnection(connectionString);
IDbCommand selectCommand = dbConnection.CreateCommand();
selectCommand.CommandText = "select * from articulo where id=4";
IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
dbDataAdapter.SelectCommand = selectCommand;
DataSet dataSet = new DataSet();
dbDataAdapter.Fill (dataSet);
Console.WriteLine("Tables.Count={0}", dataSet.Tables.Count);
foreach (DataTable dataTable in dataSet.Tables)
show (dataTable);
DataRow dataRow = dataSet.Tables[0].Rows[0];
dataRow["nombre"] = DateTime.Now.ToString ();
Console.WriteLine("Tabla con los cambios");
show (dataSet.Tables[0]);
dbDataAdapter.Update (dataSet);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string connstring = "Server=ec2-54-217-202-110.eu-west-1.compute.amazonaws.com;Port=5432;" +
"User Id=iwzexazhfjxbbt;Password=4JVMJFooosyfdM5Y79Si-c691D;Database=d8u6uelvine6d6;ssl=true";
NpgsqlConnection conn = new NpgsqlConnection(connstring);
conn.Open();
string sql = "SELECT c.name as name1, a.movie, a.id id, b.all, trunc(100*a.movie/b.all,6) procent from genre c, (select count(*) as all from movie_genre) b, (select count(movie_id) as movie, genre_id as id from movie_genre group by genre_id) a where c.id = a.id";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
da.Fill(ds, "movie_genre");
dt = ds.Tables[0];
foreach (DataRow row in ds.Tables[0].Rows)
{
PieChart1.PieChartValues.Add(new AjaxControlToolkit.PieChartValue
{
Category = row["name1"].ToString(),
Data = Convert.ToDecimal(row["procent"])
});
}
conn.Close();
}
catch (Exception msg)
{
}
}
protected void OnPropertiesActionActivated(object sender, System.EventArgs e)
{
string connectionString = "Server=localhost; Database=pruebaBD; Id=ximo; password=admin";
IDbConnection dbConnection = new NpgsqlConnection(connectionString);
IDbCommand selectCommand = dbConnection.CreateCommand();
selectCommand.CommandText = "select * BaseDatos1.from articulo where id=1";
IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
dbDataAdapter.SelectCommand = selectCommand;
DataSet dataSet = new DataSet();
dbDataAdapter.Fill (dataSet);
Console.WriteLine("Tables.Count={0}, dataSet.Tables.Count");
foreach (DataTable dataTable in dataSet.Tables){
Show(dataTable);
DataRow dataRow = dataSet.Tables[0].Rows[0];
dataRow["Nombre"] = DateTime.Now.ToString();
Console.WriteLine("Tabla con los cambios: ");
Show (dataSet.Tables[0]);
dbDataAdapter.Update (dataSet);
}
}
public List <Models.Automation.Test_Run_Notes> GetRunNote(Guid ID)
{
List <Models.Automation.Test_Run_Notes> list = new List <Models.Automation.Test_Run_Notes>();
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from automation.runnotes where row_idnum = @ID;", con);
cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = ID;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow DR in ds.Tables[0].Rows)
{
Models.Automation.Test_Run_Notes item = new Models.Automation.Test_Run_Notes
{
idnum = new Guid(Convert.ToString(DR["row_idnum"])),
run_idnum = new Guid(Convert.ToString(DR["run_idnum"])),
testset_idnum = new Guid(Convert.ToString(DR["testset_idnum"])),
test_idnum = new Guid(Convert.ToString(DR["test_idnum"])),
id = Convert.ToInt32(DR["id"]),
notes = Convert.ToString(DR["notes"]),
method_name = Convert.ToString(DR["method_name"]),
Status = Convert.ToInt32(DR["status"])
};
list.Add(item);
}
return(list);
}
protected void Page_Load(object sender, EventArgs e)
{
string rola;
FormsIdentity id =
(FormsIdentity)HttpContext.Current.User.Identity;
FormsAuthenticationTicket bilet = id.Ticket;
Label1.Text = "Zalogowany jako: " + User.Identity.Name;
// Get the stored user-data, in this case, our roles
rola = bilet.UserData;
if (rola != "admins") Response.Redirect("index.aspx");
DataSet ds = new DataSet();
DataTable dt = new DataTable();
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
// quite complex sql statement
string sql = "SELECT * FROM sala";
// data adapter making request from our connection
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
// i always reset DataSet before i do
// something with it.... i don't know why :-)
ds.Reset();
// filling DataSet with result from NpgsqlDataAdapter
da.Fill(ds);
// since it C# DataSet can handle multiple tables, we will select first
dt = ds.Tables[0];
// connect grid to DataTable
GridView1.DataSource = dt;
GridView1.DataBind();
// since we only showing the result we don't need connection anymore
conn.Close();
}
public bool ExecuteQuery(out DataTable dt)
{
dt = new DataTable();
try
{
if (!string.IsNullOrEmpty(Script))
{
Conectar();
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(Script, Connection))
{
Parametros.ForEach(x => cmd.Parameters.AddWithValue(x.Parameter.ToString(), x.Valor));
Npgsql.NpgsqlDataAdapter da = new Npgsql.NpgsqlDataAdapter(cmd);
da.Fill(dt);
}
return(true);
}
msg = "Comando inválido.";
return(false);
}
catch (NpgsqlException ex)
{
msg = ex.Message;
return(false);
}
finally
{
if (Transacao == false)
{
Desconectar();
}
}
}
public static DataSet ExecuteDataset(string connectionString, string commandText)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
try
{
conn.Open();
NpgsqlCommand command = new NpgsqlCommand(commandText, conn);
var da = new Npgsql.NpgsqlDataAdapter(command);
DataSet _ds = new DataSet();
da.Fill(_ds);
conn.Close();
return(_ds);
}
catch (Exception ex)
{
Common.log.Error(ex.ToString());
if ((conn != null))
{
conn.Close();
}
return(new DataSet());
}
finally
{
if ((conn != null))
{
conn.Dispose();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
string sQuery = "SELECT title, vote_average from movie order by vote_average DESC, release_date ASC LIMIT 20";
string sConn = String.Format("Server=ec2-54-217-202-110.eu-west-1.compute.amazonaws.com;Port=5432;User Id=iwzexazhfjxbbt;Password=4JVMJFooosyfdM5Y79Si-c691D;Database=d8u6uelvine6d6;Protocol=3;SSL=true;SslMode=Require;");
NpgsqlConnection Conn = new NpgsqlConnection(sConn);
Conn.Open();
DataSet DS = new DataSet("DS1");
NpgsqlDataAdapter DA = new NpgsqlDataAdapter();
DA.SelectCommand = new NpgsqlCommand(sQuery, Conn);
DA.Fill(DS, "DS1");
DS.Tables[0].Columns.Add("Linki", typeof(string));
foreach (DataRow item in DS.Tables[0].Rows)
{
string link="http://www.omdbapi.com/?t=" + item[0].ToString();
//string link=String.Format("<a href=\"http://www.omdbapi.com/?t={0}\">Opis</a>", item[0].ToString());
item[2] = link;
}
GridView1.DataSource = DS;
GridView1.DataBind();
Conn.Close();
}
protected void OnExecuteActionActivated(Object sender, System.EventArgs e)
{
String connectionString ="Server=localhost;Database=dbprueba;User Id=dbprueba;Password=1234";
NpgsqlCommand selectCommand = dbConnection.CreateCommand ();
selectCommand.CommandText = "select * from categoria";
NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
new NpgsqlCommandBuilder(dbDataAdapter);
dbDataAdapter.SelectCommand = selectCommand;
DataSet dataset = new Dataset();
dbDataAdapter.Fill (DataSet);
//Console.WriteLine("Tables.Count={0}", dataSet.Tables.Count);
foreach(DataTable dataTable in dataset.Tables)
show (dataTable);
DataRow dataRow = dataset.Tables[0].Rows[0];
dataRow["Nombre"] = DateTime.Now.ToString();
Console.WriteLine("Cambios");
show (dataset.Tables[0]);
dbDataAdapter.Update(dataset);
}
public static IEnumerable<TerrainService.shPoint> getPolygonPoints(string polygon_guid)
{
List<TerrainService.shPoint> pntsList = new System.Collections.Generic.List<TerrainService.shPoint>();
string sql = "select * from polygon_points where polygon_guid='" + polygon_guid + "' order by point_num";
using (NpgsqlConnection connection = new NpgsqlConnection(strPostGISConnection))
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, connection))
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds.Reset();
da.Fill(ds);
dt = ds.Tables[0];
if (dt == null || dt.Rows == null || dt.Rows.Count == 0)
{
return null;
}
foreach (DataRow row in dt.Rows)
{
TerrainService.shPoint mp = new TerrainService.shPoint();
mp.x = System.Convert.ToDouble(row["pointx"]);
mp.y = System.Convert.ToDouble(row["pointy"]);
pntsList.Add(mp);
}
}
return pntsList;
}
protected void OnExecuteActionActivated(object sender, System.EventArgs e)
{
string connectionString = "Server=localhost; Database=dbprueba; Id=javi; password=sistemas";
NpgsqlConnection dbConnection = new NpgsqlConnection(connectionString);
NpgsqlCommand selectCommand = dbConnection.CreateCommand();
selectCommand.CommandText = "select * from articulo";
NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
new NpgsqlCommandBuilder((NpgsqlDataAdapter)dbDataAdapter);
dbDataAdapter.SelectCommand = selectCommand;
DataSet dataSet = new DataSet();
dbDataAdapter.Fill (dataSet);
Console.WriteLine("Tables.Count={0}, dataSet.Tables.Count");
foreach (DataTable dataTable in dataSet.Tables){
show (dataTable);
DataRow dataRow = dataSet.Tables[0].Rows[0];
dataRow["nombre"] = DateTime.Now.ToString();
Console.WriteLine("Tabla con los cambios:");
show (dataSet.Tables[0]);
// dbDataAdapter.RowUpdated +- delegate(object dbDadapterSender, NpgsqlRowUpdatingEventArgs eventArgs){
// Console.WriteLine("RowUpdating command.CommandText= {0}", EventArgs.Command.CommantText);
//
// foreach (IDataParameter dataParameter in EventArgs.Command.Parameters)
// Console.WriteLine ("{0}={1}", dataParameter.ParameterName, dataParameter.Value);
};
dbDataAdapter.Update(dataSet.Tables[0]);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string connstring = "Server=ec2-54-217-202-110.eu-west-1.compute.amazonaws.com;Port=5432;" +
"User Id=iwzexazhfjxbbt;Password=4JVMJFooosyfdM5Y79Si-c691D;Database=d8u6uelvine6d6;ssl=true";
NpgsqlConnection conn = new NpgsqlConnection(connstring);
conn.Open();
string sql = "SELECT id, original_title, vote_average FROM movie ORDER BY vote_average DESC, release_date ASC LIMIT 20";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
da.Fill(ds,"movie");
dt = ds.Tables[0];
this.GridView1.AutoGenerateColumns = false;
GridView1.DataSource = dt;
GridView1.DataBind();
this.GridView1.Columns[1].Visible = false;
conn.Close();
}
catch (Exception msg)
{
}
}
public void CreateNodesVendor()
{
try
{
Postgres.ConnectionString = "Server=" + Properties.Settings.Default.Server + ";Port=" +
Properties.Settings.Default.Port +
";User Id=" + Properties.Settings.Default.UserId + ";Password="******";Database=" + Properties.Settings.Default.Database +
";";
Postgres.Open();
var ds = new DataSet();
var commandVendors = new NpgsqlCommand("SELECT * FROM \"GPL\".\"VENDORS\";", Postgres);
var dataAdapterV = new NpgsqlDataAdapter(commandVendors);
dataAdapterV.Fill(ds, "VENDORS");
foreach (DataRow dr in ds.Tables["VENDORS"].Rows)
{
cbVendor.Items.Add(dr["VENDOR"].ToString());
}
cbVendor.Sorted = true;
ds.Clear();
Postgres.Close();
}
catch (Exception e)
{
throw new Exception(@"Ошибка: ", e);
}
}
public DataTable[] ExecuteDataset(CommandType commandType, string query, NpgsqlParameter[] npgsqlParameters)
{
using (NpgsqlCommand command = GetCommand(query, npgsqlParameters, commandType))
{
try
{
DataSet myDS = new DataSet();
NpgsqlTransaction t = command.Connection.BeginTransaction();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
da.Fill(myDS);
t.Commit();
DataTable[] tables = new DataTable[myDS.Tables.Count];
myDS.Tables.CopyTo(tables, 0);
return tables;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
command.Connection.Close();
}
}
}
public static DataTable GetTable(string catalog, string sql, Collection<ReportParameter> parameters)
{
using (NpgsqlConnection connection = new NpgsqlConnection(GetConnectionString(catalog)))
{
using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
{
foreach (ReportParameter parameter in parameters)
{
if (parameter != null && !string.IsNullOrWhiteSpace(parameter.Name))
{
command.Parameters.AddWithValue(parameter.Name, parameter.TestValue);
}
}
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
using (DataTable dataTable = new DataTable())
{
dataTable.Locale = Thread.CurrentThread.CurrentCulture;
dataTable.TableName = "Report";
adapter.Fill(dataTable);
return dataTable;
}
}
}
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBox1.SelectedValue.ToString() == "Quartiers")
{
grd.ClearSelection();
cmd = new n.NpgsqlCommand("select code,nom,densite from quartier", cnx);
ada = new n.NpgsqlDataAdapter(cmd);
dt = new DataTable();
ada.Fill(dt);
grd.DataSource = dt;
}
else
{
if (comboBox1.SelectedValue.ToString() == "Antennes")
{
grd.ClearSelection();
cmd = new n.NpgsqlCommand("select num,nom,puissance from antenne", cnx);
ada = new n.NpgsqlDataAdapter(cmd);
dt = new DataTable();
ada.Fill(dt);
grd.DataSource = dt;
}
else
{
dt = new DataTable();
grd.DataSource = dt;
}
}
}
public Guid Maintain_RunNotes(Models.Automation.Test_Run_Notes item, string username)
{
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"SELECT automation.maintain_runnote(@IdNum, @run_IdNum, @testset_idnum,@test_IdNum, @ID,@method_name, @notes, @username, @status);", con);
cmd.Parameters.Add("@IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.idnum;
cmd.Parameters.Add("@run_IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.run_idnum;
cmd.Parameters.Add("@testset_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.testset_idnum;
cmd.Parameters.Add("@test_IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.test_idnum;
cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.id;
cmd.Parameters.Add("@method_name", NpgsqlTypes.NpgsqlDbType.Text).Value = item.method_name ?? "";
cmd.Parameters.Add("@notes", NpgsqlTypes.NpgsqlDbType.Text).Value = item.notes ?? "";
cmd.Parameters.Add("@username", NpgsqlTypes.NpgsqlDbType.Text).Value = username;
cmd.Parameters.Add("@status", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.Status;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Guid IDNUM = new Guid(Convert.ToString(ds.Tables[0].Rows[0]["maintain_runnote"]));
return(IDNUM);
}
private void pay_Load(object sender, EventArgs e)
{
NpgsqlCommand acc_info = new NpgsqlCommand("SELECT DISTINCT "
+ " places_cnt1.name, "
+ "places_cnt2.name, "
+ "public.acc_info.flat "
+ "FROM "
+ "public.places_cnt places_cnt2 "
+ "INNER JOIN public.places_cnt places_cnt1 ON (places_cnt2.parent_id = places_cnt1.plc_id) "
+ "INNER JOIN public.places_cnt ON (places_cnt1.parent_id = public.places_cnt.plc_id) "
+ "INNER JOIN public.acc_info ON (places_cnt2.plc_id = public.acc_info.plc_id) "
+ "WHERE "
+ " public.acc_info.acc_id = " + all_user.acc_code + "", conn);
conn.Open();
NpgsqlDataReader reader = acc_info.ExecuteReader();
while (reader.Read())
{
this.Text = "Оплаты по ЛС: " + all_user.acc_code + " Адрес: " + reader[0].ToString() + " " + reader[1].ToString() + " " + reader[2].ToString();
}
conn.Close();
DataTable dt = new DataTable();
NpgsqlDataAdapter da = new NpgsqlDataAdapter("SELECT * FROM public.pay WHERE acc_id = " + all_user.acc_code + " ORDER BY date_pay", conn);
conn.Open();
da.Fill(dt);
conn.Close();
dataGridView1.DataSource = dt;
}
public static DataSet actualShow(String channelNumber)
{
string sSel;
bool exist;
if (channelNumber != "")
{
sSel = "SELECT * ";
sSel += "FROM \"tbl_Program\" p, \"tbl_Channel\" c, \"tbl_Schedule\" s ";
sSel += "WHERE s.\"idProgram\" = p.\"idProgram\" AND s.\"idChannel\" = c.\"idChannel\" AND '" + DateTime.Now.ToString() + "' BETWEEN \"startDate\" AND \"endDate\" AND \"endDate\"<> '" + DateTime.Now.ToString() + "' AND c.\"channelNumber\"=" + channelNumber + " ";
}
else
{
sSel = "";
return null;
}
NpgsqlDataAdapter da;
DataSet dt = new DataSet();
try
{
da = new NpgsqlDataAdapter(sSel, sConexion);
da.Fill(dt);
if (dt.Tables[0].Rows[0][0].ToString() == "0")
exist = false;
else
exist = true;
}
catch (Exception)
{
return null;
}
return dt;
}
public static User[] GetChatUsers()
{
if (ConnectToDatabase())
{
string query = "SELECT \"Mail\", \"Username\", \"LastLogin\", \"IsAdmin\", \"ID\" FROM User";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, mDatabaseConnection);
ds.Reset();
da.Fill(ds);
dt = ds.Tables[0];
User[] users = new User[dt.Rows.Count];
int count = 0;
foreach (DataRow row in dt.Rows)
{
User u = new User();
u.UserID = row.Field<int>(dt.Columns[4]);
u.Email = row.Field<string>(dt.Columns[0]);
u.Username = row.Field<string>(dt.Columns[1]);
u.IsAdmin = row.Field<bool>(dt.Columns[3]);
u.LastOnline = row.Field<DateTime>(dt.Columns[2]);
users[count] = u;
count++;
}
DisconnectFromDatabase();
return users;
}
return null;
}
public List <Models.Application> GetApplications()
{
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from qadata.ref_structure where parent_idnumber = @NoParent order by name;", con);
cmd.Parameters.Add("@NoParent", NpgsqlTypes.NpgsqlDbType.Uuid).Value = new Guid();
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
List <Models.Application> LI = new List <Models.Application>();
foreach (DataRow I in ds.Tables[0].Rows)
{
Models.Application application = new Models.Application();
application.id = new Guid(Convert.ToString(I["row_idnumber"]));
application.Name = Convert.ToString(I["name"]);
application.ShortName = Convert.ToString(I["srtname"]);
LI.Add(application);
}
return(LI);
}
public static User GetChatUser(string pEmail, string pPassword)
{
if (ConnectToDatabase())
{
try
{
string query = "SELECT * FROM \"User\" WHERE \"Mail\"=\'" + pEmail + "\' AND \"Password\"=\'" + pPassword + "\'";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, mDatabaseConnection);
ds.Reset();
da.Fill(ds);
dt = ds.Tables[0];
User u = new User();
DataRow row = dt.Rows[0];
u.UserID = row.Field<int>(dt.Columns[0]);
u.Email = row.Field<string>(dt.Columns[1]);
u.Username = row.Field<string>(dt.Columns[3]);
u.IsAdmin = row.Field<bool>(dt.Columns[5]);
//u.LastOnline = row.Field<DateTime>(dt.Columns[4]);
return u;
}
catch (Exception ex)
{
LoggingService.LogToFile(ex);
return null;
}
finally
{
DisconnectFromDatabase();
}
}
return null;
}
public List <Models.TestCase.Test_step> GetTestSteps(Guid Test_ID)
{
List <Models.TestCase.Test_step> list = new List <Models.TestCase.Test_step>();
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from qadata.test_steps where test_idnum = @Test_ID order by step_number;", con);
cmd.Parameters.Add("@Test_ID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = Test_ID;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow DR in ds.Tables[0].Rows)
{
Models.TestCase.Test_step item = new Models.TestCase.Test_step();
item.Expected_result = Convert.ToString(DR["expected_reult"]);
item.Id = new Guid(Convert.ToString(DR["row_idnum"]));
item.Status = Convert.ToInt32(DR["status"]);
item.Step = Convert.ToString(DR["step"]);
item.Step_num = Convert.ToInt32(DR["step_number"]);
item.Test_case_id = new Guid(Convert.ToString(DR["test_idnum"]));
list.Add(item);
}
return(list);
}
/// <summary>
/// 执行SELECT语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable GetData(String sql)
{
var datatable = new DataTable();
var adapter = new NpgsqlDataAdapter(sql, Conn);
adapter.Fill(datatable);
return datatable;
}
/// <summary>
/// 将数据读取到 DataSet 中.
/// </summary>
public void ReadDataToDataSet()
{
Console.WriteLine("使用DataAdapter，将数据填充到DataSet中，然后脱离数据库，直接对DataSet进行处理。");
// 建立数据库连接.
NpgsqlConnection conn = new NpgsqlConnection(connString);
// 创建一个适配器
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(SQL, conn);
// 创建DataSet，用于存储数据.
DataSet testDataSet = new DataSet();
// 执行查询，并将数据导入DataSet.
adapter.Fill(testDataSet, "result_data");
// 关闭数据库连接.
conn.Close();
// 处理DataSet中的每一行数据.
foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
{
// 将检索出来的数据，输出到屏幕上.
Console.WriteLine("NO:{0} ; Date:{1} ; Money:{2} ",
testRow["NO"], testRow["SALE_DATE"], testRow["SUM_MONEY"]
);
}
}
/// <summary>
/// Metodo para obtener los valores de la cadena de conexion de la empresa
/// </summary>
/// <param name="compania"></param>
/// <returns></returns>
public DataTable EmpresaConexionETL(Int64 idEmpresa)
{
string add = "SELECT id ,"
+ " usuario_etl ,"
+ " nombre ,"
//+ " contrasenia_etl ,"
+ " host ,"
+ " puerto_compania ,"
+ " bd_name ,"
+ " contra_bytes ,"
+ " llave ,"
+ " apuntador "
+ " FROM empresa"
+ " WHERE id = " + idEmpresa;
try
{
conP.Open();
comP = new Npgsql.NpgsqlCommand(add, conP);
Npgsql.NpgsqlDataAdapter daP = new Npgsql.NpgsqlDataAdapter(comP);
DataTable dt = new DataTable();
daP.Fill(dt);
return(dt);
}
catch (Exception ex)
{
conP.Close();
string error = ex.Message;
throw;
}
}
override internal void LoadAll()
{
try
{
string query =
"SELECT CAST(current_database() AS character varying) AS table_catalog, " +
"CAST(nc.nspname AS character varying) AS table_schema, " +
"CAST(c.relname AS character varying) AS table_name, " +
"CAST('BASE TABLE' AS character varying) AS table_type, d.description " +
"FROM pg_namespace nc, pg_user u, pg_class c LEFT OUTER JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0 " +
"WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner AND c.relkind= 'r'";
if(!this.dbRoot.ShowSystemData)
{
query += " AND nc.nspname <> 'pg_catalog'";
}
query += " ORDER BY c.relname";
NpgsqlConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Database.Name);
DataTable metaData = new DataTable();
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, cn);
adapter.Fill(metaData);
cn.Close();
PopulateArray(metaData);
}
catch {}
}
/// <summary>
/// Method gets both person table and testoccasion table.
/// </summary>
/// <returns>Returns datatable with one row holding all info regarding a user and what test he or she have done</returns>
public DataTable GetPersonAndTestInfo(string userName)
{
NpgsqlConnection conn = new NpgsqlConnection("Database=kompetensportal;Server=localhost;User Id=postgres;Password=anna;");
try
{
conn.Open();
NpgsqlCommand cmdGetUserInfo = new NpgsqlCommand("SELECT * FROM person " +
"INNER JOIN testoccasion ON person.id = id_user " +
"WHERE person.id = (SELECT id FROM person WHERE username = @userName) " +
"ORDER BY date DESC LIMIT 1; ", conn);
cmdGetUserInfo.Parameters.AddWithValue("@userName", userName);
NpgsqlDataAdapter nda = new NpgsqlDataAdapter();
nda.SelectCommand = cmdGetUserInfo;
DataSet ds = new DataSet();
nda.Fill(ds);
DataTable dt = ds.Tables[0];
return dt;
}
catch (NpgsqlException ex)
{
NpgsqlException = ex.Message;
return null;
}
finally
{
conn.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
int pojemnosc = System.Int32.Parse(TextBox1.Text.ToString());
string typ_sali = DropDownList1.SelectedItem.ToString();
DataSet ds1 = new DataSet();
DataTable dt1 = new DataTable();
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
string sql = "SELECT id_sali FROM sala WHERE typ_sali = '" + typ_sali + "' AND pojemnosc >= '" + pojemnosc + "'";
int ilosc_wyposazen = CheckBoxList1.Items.Count;
for (int i = 0; i < ilosc_wyposazen; i++)
{
if (CheckBoxList1.Items[i].Selected)
{
string id_wyp = CheckBoxList1.Items[i].Value.ToString();
sql += "INTERSECT SELECT id_sali FROM wyp_sali where id_wyp = '" + id_wyp + "'";
}
}
conn.Open();
NpgsqlDataAdapter da1 = new NpgsqlDataAdapter(sql, conn);
da1.Fill(ds1);
dt1 = ds1.Tables[0];
GridView1.DataSource = dt1;
GridView1.DataBind();
conn.Close();
}
public void refresh_table()
{
string text_sql = @"SELECT
public.service.id,
public.acc_info.acc_id,
public.tarif.tarif_text,
public.tarif.tarif_taxa,
public.service.date_open,
public.service.date_close,
public.service.comment
FROM
public.service
INNER JOIN public.tarif ON (public.service.id_tarif = public.tarif.id)
INNER JOIN public.acc_info ON (public.service.acc_id = public.acc_info.acc_id)
WHERE public.acc_info.acc_id =" + all_user.acc_code + "";
DataTable dt_serv = new DataTable();
NpgsqlDataAdapter all_serv = new NpgsqlDataAdapter(text_sql, conn);
conn.Open();
all_serv.Fill(dt_serv);
conn.Close();
dataGridView1.DataSource = dt_serv;
dataGridView1.Columns[0].HeaderText = "Номер";
dataGridView1.Columns[1].HeaderText = "Лицевйо счет";
dataGridView1.Columns[2].HeaderText = "Тариф(описание)";
dataGridView1.Columns[3].HeaderText = "Такса";
dataGridView1.Columns[4].HeaderText = "Дата открытия услуги";
dataGridView1.Columns[5].HeaderText = "Дата закрытия услуги";
dataGridView1.Columns[6].HeaderText = "Комментарий";
}
public void update_table_period()
{
NpgsqlDataAdapter sql_table = new NpgsqlDataAdapter("SELECT * FROM period ORDER BY id DESC ", conn);
DataTable table = new DataTable();
conn.Open();
sql_table.Fill(table);
dataGridView1.DataSource = table;
conn.Close();
DateTime commentDate = table.Rows[0].Field<DateTime>("date_cnt");
textBox1.Text = commentDate.ToString("dd.MM.yyyy");
textBox2.Text = table.Rows[0].Field<string>("name");
dataGridView1.Columns[0].Width = 60;
dataGridView1.Columns[0].HeaderText = "id";
dataGridView1.Columns[1].Width = 170;
dataGridView1.Columns[1].HeaderText = "Название периода";
dataGridView1.Columns[2].Width = 100;
dataGridView1.Columns[2].HeaderText = "Начальная дата";
dataGridView1.Columns[3].Width = 100;
dataGridView1.Columns[3].HeaderText = "Конечная дата";
dataGridView1.Columns[4].Width = 100;
dataGridView1.Columns[4].HeaderText = "Дата периода";
dataGridView1.Columns[5].Width = 100;
dataGridView1.Columns[5].HeaderText = "Доп.";
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
string connstring = "Server=ec2-54-217-202-110.eu-west-1.compute.amazonaws.com;Port=5432;" +
"User Id=iwzexazhfjxbbt;Password=4JVMJFooosyfdM5Y79Si-c691D;Database=d8u6uelvine6d6;ssl=true";
NpgsqlConnection conn = new NpgsqlConnection(connstring);
conn.Open();
string sql = "SELECT a.original_title, a.vote_average FROM movie a, movie_genre b WHERE a.id=b.movie_id AND b.genre_id = " + DropDownList1.SelectedValue +" AND a.vote_average > "+ DropDownList2.SelectedValue + " ORDER by a.vote_average ASC";
NpgsqlDataAdapter da1 = new NpgsqlDataAdapter(sql, conn);
da1.Fill(ds1,"movie");
dt1 = ds1.Tables[0];
GridView1.DataSource = dt1;
GridView1.DataBind();
conn.Close();
}
catch (Exception msg)
{
}
}
public DataTable ListaDeAgendasDaSala(int IdSala)
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from agenda where idsala = " + Convert.ToString(IdSala), Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
public DataTable ListaDeAgendasDoPeriodo(DateTime DataHoraIni, DateTime DataHoraFim)
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from agenda where datahorareserva >= \'" + Dados.Model_Dao_Dados.ConverterDataToStr(DataHoraIni, false) + "\' and datahorareserva <= \'" + Dados.Model_Dao_Dados.ConverterDataToStr(DataHoraFim, true) + "\' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
public DataTable ListaDeAgendas()
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from agenda", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
public DataTable ListaDeContasARecebersPeriodo(DateTime dtIni, DateTime dtFim)
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from contasareceber where datahoracriacao >= '" + Dao.Dados.Model_Dao_Dados.ConverterDataToStr(dtIni, false) + "' and datahoracriacao <= '" + Dao.Dados.Model_Dao_Dados.ConverterDataToStr(dtFim, true) + "' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
public DataTable ListaDeMovimentacaoEstoquesDoProduto(int IdProduto)
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from movimentacaoestoque where idproduto = " + Convert.ToString(IdProduto), Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
public DataTable ListaDeMovimentacaoEstoquesDoPeriodo(DateTime DataHoraIni, DateTime DataHoraFim)
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from movimentacaoestoque where datahora >= '" + Dados.Model_Dao_Dados.ConverterDataToStr(DataHoraIni, false) + "' and datahora <= '" + Dados.Model_Dao_Dados.ConverterDataToStr(DataHoraFim, true) + "' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
public DataTable MovimentacaoEstoqueAgenda(int idAgenda)
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from movimentacaoestoque where idreservaorigem = " + Convert.ToString(idAgenda) + " ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
public DataTable ListaDeMovimentacaoEstoques()
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from movimentacaoestoque", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
public DataTable ListaDeLivroCaixasPeriodo(DateTime dtIni, DateTime dtFim)
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from livrocaixa where datahora >= '" + Dao.Dados.Model_Dao_Dados.ConverterDataToStr(dtIni, false) + "' and datahora <= '" + Dao.Dados.Model_Dao_Dados.ConverterDataToStr(dtFim, true) + "' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
public List <Models.TestCase.testcase> GetStructuresTestCases(Guid StructureID)
{
List <Models.TestCase.testcase> list = new List <Models.TestCase.testcase>();
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from qadata.testcasemain where structure_idnum = @StructureID order by testcasenum;", con);
cmd.Parameters.Add("@StructureID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = StructureID;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow DR in ds.Tables[0].Rows)
{
Models.TestCase.testcase item = new Models.TestCase.testcase();
item.aprovedby = Convert.ToString(DR["aprovedby"]);
item.aproveddate = Convert.ToDateTime(DR["aproveddate"]);
item.assigned_to = Convert.ToString(DR["assigned_to"]);
item.automated_yes_no = Convert.ToString(DR["automated_yes_no"]);
item.automation_script_name = Convert.ToString(DR["automation_script_name"]);
item.automation_test_idnum = new Guid(Convert.ToString(DR["automation_test_idnum"]));
item.automation_test_status = Convert.ToInt16(DR["automation_test_status"]);
item.br_idnum = new Guid(Convert.ToString(DR["br_idnum"]));
item.client_idnum = new Guid(Convert.ToString(DR["client_idnum"]));
item.feature = Convert.ToString(DR["feature"]);
item.feature_idnumber = new Guid(Convert.ToString(DR["feature_idnumber"]));
item.feature_type = Convert.ToString(DR["feature_type"]);
item.id = new Guid(Convert.ToString(DR["row_idnum"]));
item.iteration = Convert.ToString(DR["iteration"]);
item.module = Convert.ToString(DR["module"]);
item.notes = Convert.ToString(DR["notes"]);
item.prd = Convert.ToString(DR["prd"]);
item.qatestcasenum = Convert.ToInt32(DR["qatestcasenum"]);
item.qa_test_idnum = new Guid(Convert.ToString(DR["qa_test_idnum"]));
item.regression_yes_no = Convert.ToString(DR["regression_yes_no"]);
item.requirement_id = Convert.ToString(DR["requirement_id"]);
item.rorv_idnum = new Guid(Convert.ToString(DR["rorv_idnum"]));
item.standard_test_idnum = new Guid(Convert.ToString(DR["standard_test_idnum"]));
item.status = Convert.ToInt16(DR["status"]);
item.structure_idnum = new Guid(Convert.ToString(DR["structure_idnum"]));
item.testcasenum = Convert.ToInt32(DR["testcasenum"]);
item.testtype = Convert.ToString(DR["testtype"]);
item.test_case = Convert.ToString(DR["test_case"]);
item.test_case_description = Convert.ToString(DR["test_case_description"]);
item.test_case_id = Convert.ToString(DR["test_case_id"]);
item.test_case_title = Convert.ToString(DR["test_case_title"]);
item.test_condition = Convert.ToString(DR["test_condition"]);
item.ticket_mapping_idnum = new Guid(Convert.ToString(DR["ticket_mapping_idnum"]));
item.use_case = Convert.ToString(DR["use_case"]);
list.Add(item);
}
return(list);
}
public DataTable Pesquisar(int idCliente)
{
DataTable tabela = new DataTable();
string pesquisaSql = string.Format("select * from cliente where id = {0}", idCliente);
SqlDataAdapter da = new SqlDataAdapter(pesquisaSql, Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
private void Form1_Load(object sender, EventArgs e)
{
conexionbd claseC = new conexionbd();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds.Reset();
Npgsql.NpgsqlDataAdapter da = claseC.comando("select * from ganancia_diaria");
da.Fill(ds);
dt = ds.Tables[0];
dataGridV1.DataSource = dt;
}
public void Button1_Click(object sender, EventArgs e)
{
try
{
const string pgConstring = "Server=localhost;Port=5432;Database=postgres;User Id=postgres;Password=1234;";
NpgsqlConnection conx = new Npgsql.NpgsqlConnection(pgConstring);
if (conx.State != ConnectionState.Open)
{
conx.Open();
}
if (conx.State == ConnectionState.Open)
{
string cmd1 = string.Format("SELECT user_id, user_pass, firstname, lastname, user_name FROM public.user_login WHERE user_name ='{0}';"
, t3.Text
);
var rder = new Npgsql.NpgsqlDataAdapter(cmd1, conx);
DataSet ds = new DataSet();
rder.Fill(ds);
var usr = ConvertDataTable <user_login>(ds.Tables[0]);
if (usr.Count <= 0 || usr == null)
{
if (t1.Text == "" || t2.Text == "" || t3.Text == "" || t4.Text == "" || t5.Text == "")
{
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + "กรุณากรอกข้อมูลให้ครบ" + "');", true);
}
else
{
string text = string.Format("INSERT INTO user_login( user_pass, firstname, lastname, user_name,user_grade) VALUES ('{0}','{1}','{2}','{3}','{4}');"
, t4.Text
, t1.Text
, t2.Text
, t3.Text
, t5.Text);
NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(text, conx);
cmd.ExecuteNonQuery();
Response.Redirect("register2.aspx");
}
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + "มีuserนี้อยู่ในระบบอยู่แล้ว" + "');", true);
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
public Guid Maintain_TestCase(Models.TestCase.testcase item, string username)
{
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"SELECT qadata.maintain_testcase( @v_row_idnum, @v_client_idnum, @v_structure_idnum, @v_br_idnum, @v_testtype, @v_iteration, @v_prd, @v_requirement_id, @v_use_case, @v_test_case, @v_test_case_title, @v_test_case_description, @v_module, @v_feature, @v_feature_idnumber, @v_feature_type, @v_test_case_id, @v_test_condition, @v_assigned_to, @v_notes, @v_automated_yes_no, @v_automation_script_name, @v_automation_test_status, @v_regression_yes_no, @v_aproveddate, @v_aprovedby, @v_testcasenum, @v_rorv_idnum, @v_ticket_mapping_idnum, @v_automation_test_idnum, @v_standard_test_idnum, @v_qa_test_idnum, @v_qatestcasenum, @q_username, @v_status);", con);
cmd.Parameters.Add("@v_row_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.id;
cmd.Parameters.Add("@v_client_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.client_idnum;
cmd.Parameters.Add("@v_structure_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.structure_idnum;
cmd.Parameters.Add("@v_br_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.br_idnum;
cmd.Parameters.Add("@v_testtype", NpgsqlTypes.NpgsqlDbType.Text).Value = item.testtype ?? "";
cmd.Parameters.Add("@v_iteration", NpgsqlTypes.NpgsqlDbType.Text).Value = item.iteration ?? "";
cmd.Parameters.Add("@v_prd", NpgsqlTypes.NpgsqlDbType.Text).Value = item.prd ?? "";
cmd.Parameters.Add("@v_requirement_id", NpgsqlTypes.NpgsqlDbType.Text).Value = item.requirement_id ?? "";
cmd.Parameters.Add("@v_use_case", NpgsqlTypes.NpgsqlDbType.Text).Value = item.use_case ?? "";
cmd.Parameters.Add("@v_test_case", NpgsqlTypes.NpgsqlDbType.Text).Value = item.test_case ?? "";
cmd.Parameters.Add("@v_test_case_title", NpgsqlTypes.NpgsqlDbType.Text).Value = item.test_case_title ?? "";
cmd.Parameters.Add("@v_test_case_description", NpgsqlTypes.NpgsqlDbType.Text).Value = item.test_case_description ?? "";
cmd.Parameters.Add("@v_module", NpgsqlTypes.NpgsqlDbType.Text).Value = item.module ?? "";
cmd.Parameters.Add("@v_feature", NpgsqlTypes.NpgsqlDbType.Text).Value = item.feature ?? "";
cmd.Parameters.Add("@v_feature_idnumber", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.feature_idnumber;
cmd.Parameters.Add("@v_feature_type", NpgsqlTypes.NpgsqlDbType.Text).Value = item.feature_type ?? "";
cmd.Parameters.Add("@v_test_case_id", NpgsqlTypes.NpgsqlDbType.Text).Value = item.test_case_id ?? "";
cmd.Parameters.Add("@v_test_condition", NpgsqlTypes.NpgsqlDbType.Text).Value = item.test_condition ?? "";
cmd.Parameters.Add("@v_assigned_to", NpgsqlTypes.NpgsqlDbType.Text).Value = item.assigned_to ?? "";
cmd.Parameters.Add("@v_notes", NpgsqlTypes.NpgsqlDbType.Text).Value = item.notes ?? "";
cmd.Parameters.Add("@v_automated_yes_no", NpgsqlTypes.NpgsqlDbType.Text).Value = item.automated_yes_no ?? "";
cmd.Parameters.Add("@v_automation_script_name", NpgsqlTypes.NpgsqlDbType.Text).Value = item.automation_script_name ?? "";
cmd.Parameters.Add("@v_automation_test_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.automation_test_status;
cmd.Parameters.Add("@v_regression_yes_no", NpgsqlTypes.NpgsqlDbType.Text).Value = item.regression_yes_no ?? "";
cmd.Parameters.Add("@v_aproveddate", NpgsqlTypes.NpgsqlDbType.Date).Value = item.aproveddate;
cmd.Parameters.Add("@v_aprovedby", NpgsqlTypes.NpgsqlDbType.Text).Value = item.aprovedby ?? "";
cmd.Parameters.Add("@v_testcasenum", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.testcasenum;
cmd.Parameters.Add("@v_rorv_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.rorv_idnum;
cmd.Parameters.Add("@v_ticket_mapping_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.ticket_mapping_idnum;
cmd.Parameters.Add("@v_automation_test_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.automation_test_idnum;
cmd.Parameters.Add("@v_standard_test_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.standard_test_idnum;
cmd.Parameters.Add("@v_qa_test_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.qa_test_idnum;
cmd.Parameters.Add("@v_qatestcasenum", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.qatestcasenum;
cmd.Parameters.Add("@q_username", NpgsqlTypes.NpgsqlDbType.Text).Value = username;
cmd.Parameters.Add("@v_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.status;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Guid IDNUM = new Guid(Convert.ToString(ds.Tables[0].Rows[0]["maintain_testcase"]));
return(IDNUM);
}
/**
* Pega o extrato de todas salas para a data solicitada, no formado yyyy-mm-yy, ou yyyy-mm, ou yyyy
*
* String sala com nome da sala ou null para todas as salas
**/
public static DataTable getExtrato(String data, String sala)
{
String q = queryBase.Replace("@data", data);
if (sala != null)
{
q += " AND sala.nome='" + sala + "'";
}
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(q, Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
public DataTable ListaDeSalasCom(String psParametro)
{
try
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from sala where cao like '%" + psParametro + "%' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
catch (Exception ex)
{
//throw new Exception(ex.Message);
}
return(null);
}
public DataTable GetAgendaPorDados(int pIdCliente, int pIdSala, DateTime pdDataHora)
{
try
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from agenda where idcliente = " + Convert.ToString(pIdCliente) + " and idsala = " + Convert.ToString(pIdSala) + " and datahorareserva = '" + Dados.Model_Dao_Dados.ConverterDataToStr(pdDataHora, false) + "' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
catch (Exception ex)
{
//throw new Exception(ex.Message);
}
return(null);
}
public DataTable GetProduto(int pid)
{
try
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from produto where id = " + Convert.ToString(pid), Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
catch (Exception ex)
{
//throw new Exception(ex.Message);
}
return(null);
}
public DataTable ListaDeProdutos()
{
try
{
DataTable tabela = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("select * from produto", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao());
da.Fill(tabela);
return(tabela);
}
catch (Exception ex)
{
//throw new Exception(ex.Message);
}
return(null);
}
public static DataSet executeQuery(string statement)
{
/*
* Jordan:
* Every piece of this DSN needs to be derived from the configs. It can't stay
* hard-coded here.
*/
string dsn = "Server=127.0.0.1;port=5432;User Id=admin;Password=admin;Database=pokertracker;";
DataSet result = null;
using (NpgsqlConnection conn = new NpgsqlConnection(dsn))
{
try
{
conn.Open();
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(statement, conn))
{
da.Fill(result);
}
}
catch (Exception e)
{
/*
* Log this exception. We don't have a logging module, so maybe this is a component we can
* add..? Take a look at NLog, whoever wants to take this on. This is a component that cross-
* cuts through the entire project, since emission of logs is important for every component.
*/
}
finally
{
conn.Close();
}
}
return result;
}
protected void OnExecuteActionActivated(object sender, System.EventArgs e)
{
string connectionString = "Server=localhost;Database=dbprueba2;User Id=dbprueba;Password=sistemas;";
IDbConnection dbConnection = new NpgsqlConnection(connectionString);
IDbCommand selectCommand = dbConnection.CreateCommand();
selectCommand.CommandText = "select * from articulos";
IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
dbDataAdapter.SelectCommand = selectCommand;
NpgsqlCommandBuilder commandBuilder = new NpgsqlCommandBuilder((NpgsqlDataAdapter)dbDataAdapter);
dbConnection.Open();
DataSet dataSet = new DataSet();
dbDataAdapter.Fill(dataSet);
Console.WriteLine("Table.Count={0}\n", dataSet.Tables.Count);
foreach (DataTable dataTable in dataSet.Tables)
show (dataTable);
DataRow dataRow = dataSet.Tables[0].Rows[0];
dataRow["nombre"] = DateTime.Now.ToString();
Console.WriteLine ("\nTabla con los cambios:");
show (dataSet.Tables[0]);
IDbCommand comando = commandBuilder.GetUpdateCommand (dataSet.Tables[0].Rows[0]);
//COSAS DE HOY DIA 18
comando.ExecuteNonQuery ();
}
