public DbAccess(string providerName, string connectionString)
: this(DbProviderFactories.GetFactory(providerName), connectionString)
{
}
internal ServerVersionInfo GetSetVersion(string connectionString, string providerName, ILogger logger)
{
var factory = DbProviderFactories.GetFactory(providerName);
var connection = factory.CreateConnection();
if (connection == null)
{
throw new InvalidOperationException($"Could not create a connection for provider \"{providerName}\".");
}
// Edition: "Express Edition", "Windows Azure SQL Database..."
// EngineEdition: 1/Desktop 2/Standard 3/Enterprise 4/Express 5/Azure
// ProductLevel: RTM, SPx, CTP...
const string sql = @"select
SERVERPROPERTY('Edition') Edition,
SERVERPROPERTY('EditionID') EditionId,
SERVERPROPERTY('InstanceName') InstanceName,
SERVERPROPERTY('ProductVersion') ProductVersion,
SERVERPROPERTY('BuildClrVersion') BuildClrVersion,
SERVERPROPERTY('EngineEdition') EngineEdition,
SERVERPROPERTY('IsClustered') IsClustered,
SERVERPROPERTY('MachineName') MachineName,
SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime,
SERVERPROPERTY('ProductLevel') ProductLevel;";
string GetString(IDataReader reader, int ordinal, string defaultValue)
=> reader.IsDBNull(ordinal) ? defaultValue : reader.GetString(ordinal);
int GetInt32(IDataReader reader, int ordinal, int defaultValue)
=> reader.IsDBNull(ordinal) ? defaultValue : reader.GetInt32(ordinal);
connection.ConnectionString = connectionString;
ServerVersionInfo version;
using (connection)
{
try
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
reader.Read();
// InstanceName can be NULL for the default instance
version = new ServerVersionInfo(
GetString(reader, 0, "Unknown"),
GetString(reader, 2, "(default)"),
GetString(reader, 3, string.Empty),
(EngineEdition)GetInt32(reader, 5, 0),
GetString(reader, 7, "DEFAULT"),
GetString(reader, 9, "Unknown"));
}
connection.Close();
}
catch (Exception e)
{
logger.Error <UmbracoDatabaseFactory>(e, "Failed to detected SqlServer version.");
version = new ServerVersionInfo(); // all unknown
}
}
return(ServerVersion = version);
}
protected void Page_Command(Object sender, CommandEventArgs e)
{
// 08/21/2005 Paul. Redirect to parent if that is where the note was originated.
Guid gPARENT_ID = Sql.ToGuid(Request["PARENT_ID"]);
string sMODULE = String.Empty;
string sPARENT_TYPE = String.Empty;
string sPARENT_NAME = String.Empty;
try
{
SqlProcs.spPARENT_Get(ref gPARENT_ID, ref sMODULE, ref sPARENT_TYPE, ref sPARENT_NAME);
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
// The only possible error is a connection failure, so just ignore all errors.
gPARENT_ID = Guid.Empty;
}
if (e.CommandName == "Save")
{
// 01/16/2006 Paul. Enable validator before validating page.
SplendidDynamic.ValidateEditViewFields(m_sMODULE + ".EditView", this);
SplendidDynamic.ValidateEditViewFields(m_sMODULE + ".EditAddress", this);
SplendidDynamic.ValidateEditViewFields(m_sMODULE + ".EditDescription", this);
if (Page.IsValid)
{
string sCUSTOM_MODULE = "PROSPECTS";
DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sCUSTOM_MODULE);
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
// 04/24/2006 Paul. Upgrade to SugarCRM 4.2 Schema.
SqlProcs.spPROSPECTS_Update
(ref gID
, new DynamicControl(this, "ASSIGNED_USER_ID").ID
, new DynamicControl(this, "SALUTATION").SelectedValue
, new DynamicControl(this, "FIRST_NAME").Text
, new DynamicControl(this, "LAST_NAME").Text
, new DynamicControl(this, "TITLE").Text
, new DynamicControl(this, "DEPARTMENT").Text
, new DynamicControl(this, "BIRTHDATE").DateValue
, new DynamicControl(this, "DO_NOT_CALL").Checked
, new DynamicControl(this, "PHONE_HOME").Text
, new DynamicControl(this, "PHONE_MOBILE").Text
, new DynamicControl(this, "PHONE_WORK").Text
, new DynamicControl(this, "PHONE_OTHER").Text
, new DynamicControl(this, "PHONE_FAX").Text
, new DynamicControl(this, "EMAIL1").Text
, new DynamicControl(this, "EMAIL2").Text
, new DynamicControl(this, "ASSISTANT").Text
, new DynamicControl(this, "ASSISTANT_PHONE").Text
, new DynamicControl(this, "EMAIL_OPT_OUT").Checked
, new DynamicControl(this, "INVALID_EMAIL").Checked
, new DynamicControl(this, "PRIMARY_ADDRESS_STREET").Text
, new DynamicControl(this, "PRIMARY_ADDRESS_CITY").Text
, new DynamicControl(this, "PRIMARY_ADDRESS_STATE").Text
, new DynamicControl(this, "PRIMARY_ADDRESS_POSTALCODE").Text
, new DynamicControl(this, "PRIMARY_ADDRESS_COUNTRY").Text
, new DynamicControl(this, "ALT_ADDRESS_STREET").Text
, new DynamicControl(this, "ALT_ADDRESS_CITY").Text
, new DynamicControl(this, "ALT_ADDRESS_STATE").Text
, new DynamicControl(this, "ALT_ADDRESS_POSTALCODE").Text
, new DynamicControl(this, "ALT_ADDRESS_COUNTRY").Text
, new DynamicControl(this, "DESCRIPTION").Text
, sMODULE
, gPARENT_ID
, new DynamicControl(this, "LEAD_ID").ID
, new DynamicControl(this, "ACCOUNT_NAME").Text
, trn
);
SplendidDynamic.UpdateCustomFields(this, trn, gID, sCUSTOM_MODULE, dtCustomFields);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
ctlEditButtons.ErrorText = ex.Message;
return;
}
}
}
if (!Sql.IsEmptyGuid(gPARENT_ID))
{
Response.Redirect("~/" + sMODULE + "/view.aspx?ID=" + gPARENT_ID.ToString());
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
else if (e.CommandName == "Cancel")
{
if (!Sql.IsEmptyGuid(gPARENT_ID))
{
Response.Redirect("~/" + sMODULE + "/view.aspx?ID=" + gPARENT_ID.ToString());
}
else if (Sql.IsEmptyGuid(gID))
{
Response.Redirect("default.aspx");
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(Request.QueryString["id"]))
{
newsid.Attributes["ref"] = Request.QueryString["id"];
string newsitem = "<div class='subitem top10 newsitem'><div class='delete-item'>DELETE</div><div class='top10 itemtitle'>~name~</div><div class='formitem top10'><label for='newstitle'>News Title</label><input name='' class='newstitle' value='~title~' /></div><div class='formitem top10'><label for='newstitle'>News Source</label><input name='' class='newssource' value='~source~' /></div><div class='formitem top10'><label for='newstitle'>News Date</label><input name='' class='newsdate' value='~date~' /></div><div class='top10'>Story Content</div><textarea class='leadin rtf top10' id='~newsid~'>~content~</textarea></div>";
string highlightitem = "<div class='subitem top10 highlightitem'><div class='delete-item'>DELETE</div><div class='top10 itemtitle'>~name~</div><div class='formitem top10'><label for='highlighttitle'>Highlight Title</label><input name='' class='highlighttitle' value='~title~' /></div><div class='formitem top10'><label for='highlightlink'>Highlight Link: Please put the full URL in this field including the 'http://'</label><input name='' class='highlightlink' value='~source~' /></div></div>";
Entry item = new Entry();
DataTable dtFB = null;
GenericDatabase db = new GenericDatabase(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, DbProviderFactories.GetFactory("MySql.Data.MySqlClient"));
DbCommand cmd = db.GetSqlStringCommand("Select * from media where id ='" + Request.QueryString["id"] + "'");
dtFB = db.ExecuteDataSet(cmd).Tables[0];
cmd.Dispose();
foreach (DataRow row in dtFB.Rows)
{
item.LeadIn = row["leadin"].ToString();
item.Count = row["newscount"].ToString();
item.NewItems = row["newsitem"].ToString();
item.HighlightItems = row["highlightitem"].ToString();
newsid.InnerText = row["name"].ToString();
}
leadin.Value = item.LeadIn;
List <News> newitems = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize <List <News> >(item.NewItems);
string tmp = "";
Int32 ctr = 1;
foreach (News x in newitems)
{
tmp += newsitem.Replace("~name~", "News " + ctr.ToString()).Replace("~title~", x.Title).Replace("~source~", x.Source).Replace("~date~", x.Date).Replace("~newsid~", "news" + ctr.ToString()).Replace("~content~", x.Content);
ctr++;
}
news.InnerHtml = tmp;
tmp = "";
List <Highlight> highitems = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize <List <Highlight> >(item.HighlightItems);
ctr = 1;
foreach (Highlight x in highitems)
{
tmp += highlightitem.Replace("~name~", "Highlight " + ctr.ToString()).Replace("~title~", x.Title).Replace("~source~", x.Source);
ctr++;
}
highlights.InnerHtml = tmp;
//leadin.Value = item.LeadIn;
storycount.Value = item.Count;
}
DataTable dtFBa = null;
GenericDatabase dba = new GenericDatabase(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, DbProviderFactories.GetFactory("MySql.Data.MySqlClient"));
DbCommand cmda = dba.GetSqlStringCommand("Select id, name from media order by id desc");
dtFBa = dba.ExecuteDataSet(cmda).Tables[0];
cmda.Dispose();
ListItem mn = new ListItem();
mn.Text = "Select Saved Email Template";
mn.Value = "0";
ddmenu.Items.Add(mn);
foreach (DataRow row in dtFBa.Rows)
{
mn = new ListItem();
mn.Text = row["name"].ToString();
mn.Value = row["id"].ToString();
ddmenu.Items.Add(mn);
}
}
} //Parsowanie danych z pliku.
public static bool czyIstniejeRaport(DateTime data, string Numer, Rejestr rejestr)
{
//Łączenie z bazą danych
string provider = ConfigurationManager.AppSettings["provider"];
string connectionString = ConfigurationManager.AppSettings["connectionString"];
DbProviderFactory factory = DbProviderFactories.GetFactory(provider); //to pozwala na słanie zapytań do bazy danych
using (DbConnection connection = factory.CreateConnection())
{
if (connection == null)
{
Form1.log.Error("Connection Error");
Debug.WriteLine("Connection Error");
Console.ReadLine();
return(false);
}
connection.ConnectionString = connectionString;
connection.Open();
DbCommand command = factory.CreateCommand();
if (command == null)
{
Form1.log.Error("Commant Error");
Debug.WriteLine("Command Error");
Console.ReadLine();
return(false);
}
command.Connection = connection;
command.CommandText = "Select * From [CDN_Firma_Demo].[CDN].[BnkRaporty]";
List <Raport> raporty = new List <Raport>();
using (DbDataReader dataReader = command.ExecuteReader())
{
DateTime dataS;
while (dataReader.Read()) // zapełniamy listę raportów obiektami Raport
{
string dataOtw = dataReader["BRp_DataDok"].ToString();
dataS = DateTime.Parse(dataOtw.Substring(0, 10));
raporty.Add(new Raport
{
NazwaRejestru = dataReader["BRp_NumerPelny"].ToString(),
DataOtwarcia = dataS
});
}
} // zapełnienie listy wszystkich raportów z daty
//Filtrowanie listy raportów po numerze rejestru i Dacie otwarcia, by sprawdzić czy istnieje konkretny raport
var rap = from Raport in raporty
where Raport.NazwaRejestru.Contains(Numer) && Raport.DataOtwarcia == data
select Raport;
if (rap.Any())
{
return(true);
}
else //Jeżeli raport nie istnieje tworzymy Nowy Raport
{
Debug.WriteLine("Brak Raportu. Raport jest tworzony.");
Form1.log.Debug("Brak Raportu.");
try
{
NowyRaport(rejestr); //jeżeli nie ma raportu, raport jest tworzony od razu i zwracana jest wartosć true
return(true);
}
catch (Exception e)
{
Debug.WriteLine("Raport nie mógł zostać utworzony: " + e.Message);
Form1.log.Error("Raport nie mógł zostać utworzony: " + e.Message);
}
return(false);
} //tworzenie nowego raportu - return true / catch exeption return false
}
}
protected void Page_Command(Object sender, CommandEventArgs e)
{
if (e.CommandName == "Save")
{
// 01/16/2006 Paul. Enable validator before validating page.
SplendidDynamic.ValidateEditViewFields(m_sMODULE + ".EditView", this);
SplendidDynamic.ValidateEditViewFields(m_sMODULE + ".EditAddress", this);
if (Page.IsValid)
{
string sCUSTOM_MODULE = "USERS";
DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sCUSTOM_MODULE);
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
SqlProcs.spEMPLOYEES_Update
(ref gID
, new DynamicControl(this, "FIRST_NAME").Text
, new DynamicControl(this, "LAST_NAME").Text
, new DynamicControl(this, "REPORTS_TO_ID").ID
, new DynamicControl(this, "DESCRIPTION").Text
, new DynamicControl(this, "TITLE").Text
, new DynamicControl(this, "DEPARTMENT").Text
, new DynamicControl(this, "PHONE_HOME").Text
, new DynamicControl(this, "PHONE_MOBILE").Text
, new DynamicControl(this, "PHONE_WORK").Text
, new DynamicControl(this, "PHONE_OTHER").Text
, new DynamicControl(this, "PHONE_FAX").Text
, new DynamicControl(this, "EMAIL1").Text
, new DynamicControl(this, "EMAIL2").Text
, new DynamicControl(this, "ADDRESS_STREET").Text
, new DynamicControl(this, "ADDRESS_CITY").Text
, new DynamicControl(this, "ADDRESS_STATE").Text
, new DynamicControl(this, "ADDRESS_POSTALCODE").Text
, new DynamicControl(this, "ADDRESS_COUNTRY").Text
, new DynamicControl(this, "EMPLOYEE_STATUS").SelectedValue
, new DynamicControl(this, "MESSENGER_ID").Text
, new DynamicControl(this, "MESSENGER_TYPE").SelectedValue
, trn
);
SplendidDynamic.UpdateCustomFields(this, trn, gID, sCUSTOM_MODULE, dtCustomFields);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
ctlEditButtons.ErrorText = ex.Message;
return;
}
}
}
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
else if (e.CommandName == "Cancel")
{
if (Sql.IsEmptyGuid(gID))
{
Response.Redirect("default.aspx");
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
protected void Page_Command(Object sender, CommandEventArgs e)
{
// 08/21/2005 Paul. Redirect to parent if that is where the note was originated.
Guid gPARENT_ID = Sql.ToGuid(Request["PARENT_ID"]);
string sMODULE = String.Empty;
string sPARENT_TYPE = String.Empty;
string sPARENT_NAME = String.Empty;
try
{
SqlProcs.spPARENT_Get(ref gPARENT_ID, ref sMODULE, ref sPARENT_TYPE, ref sPARENT_NAME);
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
// The only possible error is a connection failure, so just ignore all errors.
gPARENT_ID = Guid.Empty;
}
if (e.CommandName == "Save")
{
// 01/16/2006 Paul. Enable validator before validating page.
SplendidDynamic.ValidateEditViewFields(m_sMODULE + ".EditView", this);
if (Page.IsValid)
{
string sCUSTOM_MODULE = "TEST_RUNS";
DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sCUSTOM_MODULE);
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
/*
* SqlProcs.spTEST_RUNS_Update(ref gID
* , new DynamicControl(this, "ASSIGNED_USER_ID").ID
* , new DynamicControl(this, "NAME" ).Text
* , new DynamicControl(this, "DESCRIPTION" ).Text
* , sMODULE
* , gPARENT_ID
* , trn
* );
* SplendidDynamic.UpdateCustomFields(this, trn, gID, sCUSTOM_MODULE, dtCustomFields);
*/
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
ctlEditButtons.ErrorText = ex.Message;
return;
}
}
}
if (!Sql.IsEmptyGuid(gPARENT_ID))
{
Response.Redirect("~/" + sMODULE + "/view.aspx?ID=" + gPARENT_ID.ToString());
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
else if (e.CommandName == "Cancel")
{
if (!Sql.IsEmptyGuid(gPARENT_ID))
{
Response.Redirect("~/" + sMODULE + "/view.aspx?ID=" + gPARENT_ID.ToString());
}
else if (Sql.IsEmptyGuid(gID))
{
Response.Redirect("default.aspx");
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
private DbProviderFactory CreateDbProviderFactory() => DbProviderFactories.GetFactory(m_ProviderName);
protected void Page_Command(Object sender, CommandEventArgs e)
{
if (e.CommandName == "Save")
{
// 01/16/2006 Paul. Enable validator before validating page.
this.ValidateEditViewFields(m_sMODULE + ".EditView");
if (Page.IsValid)
{
string sCUSTOM_MODULE = "PRODUCT_TEMPLATES";
DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sCUSTOM_MODULE);
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
// 11/18/2007 Paul. Use the current values for any that are not defined in the edit view.
DataRow rowCurrent = null;
DataTable dtCurrent = new DataTable();
if (!Sql.IsEmptyGuid(gID))
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwPRODUCT_TEMPLATES_Edit" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Security.Filter(cmd, m_sMODULE, "edit");
Sql.AppendParameter(cmd, gID, "ID", false);
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
da.Fill(dtCurrent);
if (dtCurrent.Rows.Count > 0)
{
rowCurrent = dtCurrent.Rows[0];
}
else
{
// 11/19/2007 Paul. If the record is not found, clear the ID so that the record cannot be updated.
// It is possible that the record exists, but that ACL rules prevent it from being selected.
gID = Guid.Empty;
}
}
}
}
con.Open();
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
SqlProcs.spPRODUCT_TEMPLATES_Update
(ref gID
, new DynamicControl(this, rowCurrent, "NAME").Text
, new DynamicControl(this, rowCurrent, "STATUS").SelectedValue
, new DynamicControl(this, rowCurrent, "QUANTITY").IntegerValue
, new DynamicControl(this, rowCurrent, "DATE_AVAILABLE").DateValue
, new DynamicControl(this, rowCurrent, "DATE_COST_PRICE").DateValue
, new DynamicControl(this, rowCurrent, "ACCOUNT_ID").ID
, new DynamicControl(this, rowCurrent, "MANUFACTURER_ID").ID
, new DynamicControl(this, rowCurrent, "CATEGORY_ID").ID
, new DynamicControl(this, rowCurrent, "TYPE_ID").ID
, new DynamicControl(this, rowCurrent, "WEBSITE").Text
, new DynamicControl(this, rowCurrent, "MFT_PART_NUM").Text
, new DynamicControl(this, rowCurrent, "VENDOR_PART_NUM").Text
, new DynamicControl(this, rowCurrent, "TAX_CLASS").SelectedValue
, new DynamicControl(this, rowCurrent, "WEIGHT").FloatValue
, new DynamicControl(this, rowCurrent, "CURRENCY_ID").ID
, new DynamicControl(this, rowCurrent, "COST_PRICE").DecimalValue
, new DynamicControl(this, rowCurrent, "LIST_PRICE").DecimalValue
, new DynamicControl(this, rowCurrent, "DISCOUNT_PRICE").DecimalValue
, new DynamicControl(this, rowCurrent, "PRICING_FACTOR").IntegerValue
, new DynamicControl(this, rowCurrent, "PRICING_FORMULA").SelectedValue
, new DynamicControl(this, rowCurrent, "SUPPORT_NAME").Text
, new DynamicControl(this, rowCurrent, "SUPPORT_CONTACT").Text
, new DynamicControl(this, rowCurrent, "SUPPORT_DESCRIPTION").Text
, new DynamicControl(this, rowCurrent, "SUPPORT_TERM").SelectedValue
, new DynamicControl(this, rowCurrent, "DESCRIPTION").Text
, trn
);
SplendidDynamic.UpdateCustomFields(this, trn, gID, sCUSTOM_MODULE, dtCustomFields);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
ctlEditButtons.ErrorText = ex.Message;
return;
}
}
}
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
else if (e.CommandName == "Cancel")
{
if (Sql.IsEmptyGuid(gID))
{
Response.Redirect("default.aspx");
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
//creat new table in carrent profile
private void button_create_Click(object sender, EventArgs e)
{
SQLiteFactory factory = (SQLiteFactory)DbProviderFactories.GetFactory("System.Data.SQLite");
//connect to database
using (SQLiteConnection connection = (SQLiteConnection)factory.CreateConnection())
{
connection.ConnectionString = "Data Source = " + Work_Form.login + ".db3";
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "SELECT name FROM sqlite_master WHERE type='table'";
command.CommandType = CommandType.Text;
SQLiteDataReader reader = command.ExecuteReader();
Boolean flag_copy = false;
//searching table duplicate
while (reader.Read())
{
if (text_name.Text == $"{reader.GetString(0)}")
{
flag_copy = true;
break;
}
}
//if duplicate exist then show message and close window
//else create and show new table
if (flag_copy)
{
MessageBox.Show("Таблица с таким именем уже существует.");
reader.Close();
connection.Close();
Close();
Dispose();
}
else
{
reader.Close();
command.CommandText = "CREATE TABLE IF NOT EXISTS [" + text_name.Text + "] (" +
"[id] INTEGER PRIMARY KEY AUTOINCREMENT," +
"[name] TEXT," +
"[surname] TEXT," +
"[patronymic] TEXT," +
"[birthday] TEXT," +
"[address] TEXT," +
"[department] TEXT," +
"[about] TEXT" +
")";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
Work_Form.table = new DataTable();
DataColumn col;
command.CommandText = "SELECT * FROM [" + text_name.Text + "]";
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
for (int i = 1; i < reader.FieldCount; i++)
{
col = new DataColumn(reader.GetName(i));
Work_Form.table.Columns.Add(col);
}
reader.Close();
connection.Close();
MessageBox.Show("Таблица создана.");
Work_Form.table_name = text_name.Text; //current table = new table
Close();
Dispose();
}
}
}
}
public static DataSet ReadExcelFileforecast(string fpath, int noColumn)
{
string connectionString = String.Format(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", fpath);
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbDataAdapter adapter = factory.CreateDataAdapter();
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = "Select * From [" + PickSheet(fpath) + "]";
DbConnection connection = factory.CreateConnection();
DataSet ds = new DataSet();
bool empty;
try
{
connection.ConnectionString = connectionString;
selectCommand.Connection = connection;
adapter.SelectCommand = selectCommand;
adapter.Fill(ds, "Consumption");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
empty = true;
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i][col].ToString().Trim()))
{
empty = false;
}
}
if (empty)
{
ds.Tables[0].Rows[i].Delete();
}
}
ds.Tables[0].AcceptChanges();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
int colcount = ds.Tables[0].Columns.Count;
//if (noColumn > colcount)
// throw new Exception("Imported excel file has less columns than needed.");
//if (noColumn < colcount)
// throw new Exception("Imported excel file has too many columns.");
if (ds.Tables[0].Rows.Count == 0)
{
throw new Exception("Imported excel is empty.");
}
return(ds);
}
/// <summary>
/// Get database provider factory so caller can create dbparams, etc.
/// </summary>
/// <returns></returns>
public DbProviderFactory GetFactory()
{
DbProviderFactory factory = DbProviderFactories.GetFactory(_connection.ProviderName);
return(factory);
}
public IngredientsExpanseDataDto GetAllIngredientsExpanseDataForPeriod(DateTime startDate,
DateTime endDate)
{
var factory = DbProviderFactories.GetFactory(_context.Database.GetDbConnection());
using var cmd = factory.CreateCommand();
if (cmd == null)
{
return(null);
}
cmd.CommandText = $"EXEC dbo.GetAllIngredientsForPeriod '{startDate:yyyy-MM-dd}', '{endDate:yyyy-MM-dd}'";
cmd.CommandType = CommandType.Text;
cmd.Connection = _context.Database.GetDbConnection();
using var adapter = factory.CreateDataAdapter();
if (adapter == null)
{
return(null);
}
adapter.SelectCommand = cmd;
var dataTable = new DataTable();
adapter.Fill(dataTable);
var rows = dataTable.Rows;
if (rows.Count == 0)
{
return(null);
}
var ingredients = new List <Ingredient>();
var idToCount = new Dictionary <string, decimal>();
foreach (DataRow row in rows)
{
ingredients.Add(new Ingredient
{
Id = (long)row["iId"],
Name = (string)row["iName"],
Price = (decimal)row["iPrice"],
MeasurementUnitId = (long)row["muId"],
MeasurementUnit = new MeasurementUnit
{
Id = (long)row["muId"],
Name = (string)row["muName"],
Ingredient = null
}
});
idToCount.Add(
((long)row["iId"]).ToString(),
(decimal)row["iCount"]
);
}
return(new IngredientsExpanseDataDto
{
Ingredients = ingredients,
Counts = idToCount
});
}
public DbAccess(ConnectionStringSettings connSetting)
: this(DbProviderFactories.GetFactory(connSetting.ProviderName), connSetting.ConnectionString)
{
}
public static void RIndexReplication()
{
{
int replicationFactor = 0;
string[] lines = Exec.Shell("Qizmt replicationview").Split('\n');
for (int i = 0; i < lines.Length; i++)
{
string line = lines[i].Trim();
if (line.Length > 0)
{
int del = line.LastIndexOf(' ');
replicationFactor = Int32.Parse(line.Substring(del + 1));
if (replicationFactor > 0)
{
break;
}
}
}
if (replicationFactor < 2)
{
throw new Exception("Cannot run RIndexReplication regression test when replication factor is less than 2.");
}
}
string guid = Guid.NewGuid().ToString().Replace("-", "");
string tablename = "rselect_test_" + guid;
string indexname = guid + "apple";
string indexname_order = guid + "order";
string[] allhosts = null;
string qizmtrootdir = "";
{
string[] installs = Exec.Shell("Qizmt slaveinstalls").Trim()
.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
allhosts = new string[installs.Length];
for (int ip = 0; ip < installs.Length; ip++)
{
string[] parts = installs[ip].Split(' ');
string installpath = parts[1];
int del = installpath.IndexOf(@"\", 2);
qizmtrootdir = installpath.Substring(del + 1);
allhosts[ip] = parts[0];
}
}
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
{
Console.WriteLine("Creating data, table, and index...");
string job = (@"<SourceCode>
<Jobs>
<Job Name=`Cleanup_Previous_Data` Custodian=`` Email=``>
<IOSettings>
<JobType>local</JobType>
</IOSettings>
<Add Reference=`System.Data.dll` Type=`system`/>
<Using>System.Data</Using>
<Using>System.Data.Common</Using>
<Local>
<![CDATA[
public virtual void Local()
{
//Clean up previous data.
Shell(@`Qizmt del data_Input" + guid + @"*`);
Shell(@`Qizmt del data_Output" + guid + @"*`);
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory(`Qizmt_DataProvider`);
using (DbConnection conn = fact.CreateConnection())
{
try
{
conn.ConnectionString = `Data Source = localhost`;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = `drop table " + tablename + @"`;
cmd.ExecuteNonQuery();
cmd.CommandText = `drop rindex " + indexname + @"`;
cmd.ExecuteNonQuery();
conn.Close();
}
catch
{
}
}
}
]]>
</Local>
</Job>
<Job Name=`data_CreateSampleData` Custodian=`` Email=`` Description=`Create sample data`>
<IOSettings>
<JobType>remote</JobType>
<DFS_IO>
<DFSReader></DFSReader>
<DFSWriter>dfs://data_Input" + guid + @".txt</DFSWriter>
</DFS_IO>
</IOSettings>
<Remote>
<![CDATA[
public virtual void Remote(RemoteInputStream dfsinput, RemoteOutputStream dfsoutput)
{
dfsoutput.WriteLine(`1`);
}
]]>
</Remote>
</Job>
<Job Name=`data` Custodian=`` Email=``>
<IOSettings>
<JobType>mapreduce</JobType>
<KeyLength>nInt</KeyLength>
<DFSInput>dfs://data_Input" + guid + @".txt</DFSInput>
<DFSOutput>dfs://data_Output" + guid + @"1.bin@nInt,nInt</DFSOutput>
<OutputMethod>grouped</OutputMethod>
</IOSettings>
<Add Reference=`RDBMS_DBCORE.dll` Type=`dfs`/>
<Using>RDBMS_DBCORE</Using>
<MapReduce>
<Map>
<![CDATA[
public virtual void Map(ByteSlice line, MapOutput output)
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(4);
output.Add(rkey.ToByteSlice(), ByteSlice.Prepare());
}
]]>
</Map>
<Reduce>
<![CDATA[
public override void Reduce(ByteSlice key, ByteSliceList values, ReduceOutput output)
{
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(5);
rkey.PutInt(1);
output.Add(rkey.ToByteSlice());
}
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(5);
rkey.PutInt(2);
output.Add(rkey.ToByteSlice());
}
}
]]>
</Reduce>
</MapReduce>
</Job>
<Job Name=`data` Custodian=`` Email=``>
<IOSettings>
<JobType>mapreduce</JobType>
<KeyLength>nInt</KeyLength>
<DFSInput>dfs://data_Input" + guid + @".txt</DFSInput>
<DFSOutput>dfs://data_Output" + guid + @"2.bin@nInt,nInt</DFSOutput>
<OutputMethod>grouped</OutputMethod>
</IOSettings>
<Add Reference=`RDBMS_DBCORE.dll` Type=`dfs`/>
<Using>RDBMS_DBCORE</Using>
<MapReduce>
<Map>
<![CDATA[
public virtual void Map(ByteSlice line, MapOutput output)
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(4);
output.Add(rkey.ToByteSlice(), ByteSlice.Prepare());
}
]]>
</Map>
<Reduce>
<![CDATA[
public override void Reduce(ByteSlice key, ByteSliceList values, ReduceOutput output)
{
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(10);
rkey.PutInt(1);
output.Add(rkey.ToByteSlice());
}
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(10);
rkey.PutInt(2);
output.Add(rkey.ToByteSlice());
}
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(14);
rkey.PutInt(1);
output.Add(rkey.ToByteSlice());
}
}
]]>
</Reduce>
</MapReduce>
</Job>
<Job Name=`data` Custodian=`` Email=``>
<IOSettings>
<JobType>mapreduce</JobType>
<KeyLength>nInt</KeyLength>
<DFSInput>dfs://data_Input" + guid + @".txt</DFSInput>
<DFSOutput>dfs://data_Output" + guid + @"3.bin@nInt,nInt</DFSOutput>
<OutputMethod>grouped</OutputMethod>
</IOSettings>
<Add Reference=`RDBMS_DBCORE.dll` Type=`dfs`/>
<Using>RDBMS_DBCORE</Using>
<MapReduce>
<Map>
<![CDATA[
public virtual void Map(ByteSlice line, MapOutput output)
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(4);
output.Add(rkey.ToByteSlice(), ByteSlice.Prepare());
}
]]>
</Map>
<Reduce>
<![CDATA[
public override void Reduce(ByteSlice key, ByteSliceList values, ReduceOutput output)
{
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(21);
rkey.PutInt(1);
output.Add(rkey.ToByteSlice());
}
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(25);
rkey.PutInt(1);
output.Add(rkey.ToByteSlice());
}
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(80);
rkey.PutInt(1);
output.Add(rkey.ToByteSlice());
}
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(80);
rkey.PutInt(2);
output.Add(rkey.ToByteSlice());
}
}
]]>
</Reduce>
</MapReduce>
</Job>
<Job Name=`data` Custodian=`` Email=``>
<IOSettings>
<JobType>mapreduce</JobType>
<KeyLength>nInt</KeyLength>
<DFSInput>dfs://data_Input" + guid + @".txt</DFSInput>
<DFSOutput>dfs://data_Output" + guid + @"4.bin@nInt,nInt</DFSOutput>
<OutputMethod>grouped</OutputMethod>
</IOSettings>
<Add Reference=`RDBMS_DBCORE.dll` Type=`dfs`/>
<Using>RDBMS_DBCORE</Using>
<MapReduce>
<Map>
<![CDATA[
public virtual void Map(ByteSlice line, MapOutput output)
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(4);
output.Add(rkey.ToByteSlice(), ByteSlice.Prepare());
}
]]>
</Map>
<Reduce>
<![CDATA[
public override void Reduce(ByteSlice key, ByteSliceList values, ReduceOutput output)
{
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(80);
rkey.PutInt(3);
output.Add(rkey.ToByteSlice());
}
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(80);
rkey.PutInt(4);
output.Add(rkey.ToByteSlice());
}
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(90);
rkey.PutInt(1);
output.Add(rkey.ToByteSlice());
}
{
DbRecordset rkey = DbRecordset.Prepare();
rkey.PutInt(102);
rkey.PutInt(1);
output.Add(rkey.ToByteSlice());
}
}
]]>
</Reduce>
</MapReduce>
</Job>
<Job Name=`Prepare_tblFriends_Table_Create_RIndex` Custodian=`` Email=``>
<IOSettings>
<JobType>local</JobType>
</IOSettings>
<Add Reference=`RDBMS_DBCORE.dll` Type=`dfs`/>
<Add Reference=`System.Data.dll` Type=`system`/>
<Using>RDBMS_DBCORE</Using>
<Using>System.Data</Using>
<Using>System.Data.Common</Using>
<Local>
<![CDATA[
public virtual void Local()
{
Shell(@`qizmt combine data_Output" + guid + @"1.bin data_Output" + guid + @"2.bin data_Output" + guid + @"3.bin data_Output" + guid + @"4.bin +data_Output" + guid + @".bin`);
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory(`Qizmt_DataProvider`);
using (DbConnection conn = fact.CreateConnection())
{
conn.ConnectionString = `Data Source = localhost`;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = `create table " + tablename + @" (id int, rid int)`;
cmd.ExecuteNonQuery();
cmd.CommandText = `insert into " + tablename + @" bind 'dfs://data_Output" + guid + @".bin'`;
cmd.ExecuteNonQuery();
conn.Close();
}
using (DbConnection conn = fact.CreateConnection())
{
conn.ConnectionString = `Data Source = localhost`;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = `create rindex " + indexname + @" from " + tablename + @" pinmemoryHASH ON id`;
cmd.ExecuteNonQuery();
conn.Close();
}
}
]]>
</Local>
</Job>
</Jobs>
</SourceCode>").Replace('`', '"');
string tempdir = @"\\" + System.Net.Dns.GetHostName() + @"\" + Environment.CurrentDirectory.Replace(':', '$') + @"\" + Guid.NewGuid().ToString().Replace("-", "");
if (System.IO.Directory.Exists(tempdir))
{
System.IO.Directory.Delete(tempdir, true);
}
System.IO.Directory.CreateDirectory(tempdir);
string tempjobname = Guid.NewGuid().ToString();
System.IO.File.WriteAllText(tempdir + @"\" + tempjobname, job);
Exec.Shell("Qizmt importdir \"" + tempdir + "\"");
Exec.Shell("dspace exec " + tempjobname);
//Clean up
Exec.Shell(@"Qizmt del " + tempjobname);
System.IO.Directory.Delete(tempdir, true);
}
{
Console.WriteLine("RInsert/RDeleting rindex...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = " + string.Join(",", allhosts) + "; rindex=pooled";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText =
"rdelete from " + indexname + " where key = 80 and rid=1\0" +
"rdelete from " + indexname + " where key = 80 and rid=2\0" +
"rdelete from " + indexname + " where key = 80 and rid=3\0" +
"rdelete from " + indexname + " where key = 80 and rid=4\0" +
"rinsert into " + indexname + " values(10, 3) where key = 10\0" +
"rinsert into " + indexname + " values(10, 19) where key = 10\0" +
"rdelete from " + indexname + " where key = 10 and rid=19\0" +
"rinsert into " + indexname + " values(20, 1) where key = 20\0" +
"rinsert into " + indexname + " values(30, 1) where key = 30\0" +
"rinsert into " + indexname + " values(30, 2) where key = 30\0" +
"rinsert into " + indexname + " values(9, 1) where key = 9\0" +
"rinsert into " + indexname + " values(9, 2) where key = 9\0" +
"rinsert into " + indexname + " values(99, 1) where key = 99\0" +
"rdelete from " + indexname + " where key = 99 and rid=4444444\0" + //deleting existing key but secondary column value doesn't match
"rdelete from " + indexname + " where key = 100000 and rid=4444444\0" + //deleting non-existing key.
"rdelete from " + indexname + " where key = 5 and rid=2\0" +
"rinsert into " + indexname + " values(2, 1) where key = 2\0" +
"rinsert into " + indexname + " values(2, 19) where key = 2\0" +
"rinsert into " + indexname + " values(2, 2) where key = 2\0" +
"rdelete from " + indexname + " where key = 2 and rid=19\0";
cmd.ExecuteNonQuery();
conn.Close();
}
Dictionary <string, string> renamed = new Dictionary <string, string>();
{
Console.WriteLine("Invalidating chunks...");
string bulkget = @"\\" + System.Net.Dns.GetHostName() + @"\" + Environment.CurrentDirectory.Replace(':', '$') + @"\" + Guid.NewGuid().ToString().Replace("-", "");
Exec.Shell("Qizmt bulkget " + bulkget + " RDBMS_Table_" + tablename);
string[] lines = System.IO.File.ReadAllLines(bulkget);
System.IO.File.Delete(bulkget);
foreach (string line in lines)
{
string[] parts = line.Split(' ');
string firsthost = parts[0].Split(';')[0];
string chunkname = parts[1];
string oldpath = @"\\" + firsthost + @"\" + qizmtrootdir + @"\" + chunkname;
string newpath = oldpath + "_regtest";
System.IO.File.Move(oldpath, newpath);
renamed.Add(oldpath, newpath);
}
}
{
Console.WriteLine("RSelecting from rindex after invalidating chunks, should fail over...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = " + string.Join(",", allhosts) + "; rindex=pooled";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "rselect * from " + indexname + " where key=80 or key = 2 or key = 5 or key=9 or key=10 or key=14 or key=20 or key=21 or key=25 or key=30 or key=90 or key=99 or key=102";
DbDataReader reader = cmd.ExecuteReader();
int cnt = 0;
List <KeyValuePair <int, int> > results = new List <KeyValuePair <int, int> >();
while (reader.Read())
{
int x = reader.GetInt32(0);
int y = reader.GetInt32(1);
KeyValuePair <int, int> row = new KeyValuePair <int, int>(x, y);
results.Add(row);
}
reader.Close();
conn.Close();
results.Sort(delegate(KeyValuePair <int, int> x, KeyValuePair <int, int> y)
{
if (x.Key != y.Key)
{
return(x.Key.CompareTo(y.Key));
}
else
{
return(x.Value.CompareTo(y.Value));
}
});
List <KeyValuePair <int, int> > expected = new List <KeyValuePair <int, int> >();
expected.Add(new KeyValuePair <int, int>(2, 1));
expected.Add(new KeyValuePair <int, int>(2, 2));
expected.Add(new KeyValuePair <int, int>(5, 1));
expected.Add(new KeyValuePair <int, int>(9, 1));
expected.Add(new KeyValuePair <int, int>(9, 2));
expected.Add(new KeyValuePair <int, int>(10, 1));
expected.Add(new KeyValuePair <int, int>(10, 2));
expected.Add(new KeyValuePair <int, int>(10, 3));
expected.Add(new KeyValuePair <int, int>(14, 1));
expected.Add(new KeyValuePair <int, int>(20, 1));
expected.Add(new KeyValuePair <int, int>(21, 1));
expected.Add(new KeyValuePair <int, int>(25, 1));
expected.Add(new KeyValuePair <int, int>(30, 1));
expected.Add(new KeyValuePair <int, int>(30, 2));
expected.Add(new KeyValuePair <int, int>(90, 1));
expected.Add(new KeyValuePair <int, int>(99, 1));
expected.Add(new KeyValuePair <int, int>(102, 1));
if (results.Count != expected.Count)
{
throw new Exception("Expected " + expected.Count.ToString() + " rows to be returned. But received " + results.Count.ToString() + " rows instead.");
}
for (int i = 0; i < results.Count; i++)
{
if (results[i].Key != expected[i].Key || results[i].Value != expected[i].Value)
{
throw new Exception("Row returned is different from expected results. Received row:(" + results[i].Key.ToString() + "," + results[i].Value.ToString() + "). Expected row:(" + expected[i].Key.ToString() + "," + expected[i].Value.ToString() + ")");
}
}
}
{
Console.WriteLine("Fixing chunks...");
foreach (KeyValuePair <string, string> pair in renamed)
{
System.IO.File.Move(pair.Value, pair.Key);
}
}
{
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "drop table " + tablename;
cmd.ExecuteNonQuery();
cmd.CommandText = "drop rindex " + indexname;
cmd.ExecuteNonQuery();
conn.Close();
}
}
/// <summary>
/// Create the configuration section
/// </summary>
/// <param name="parent"></param>
/// <param name="configContext"></param>
/// <param name="section"></param>
/// <returns></returns>
public object Create(object parent, object configContext, System.Xml.XmlNode section)
{
// Connection manager configuration
XmlNode ctsSectionConfig = section.SelectSingleNode("./*[local-name() = 'cts']"),
dbSectionConfig = section.SelectSingleNode("./*[local-name() = 'qdcdb']");
FillInCodeSets = new List <string>();
// Memory configuration
if (section.Attributes["maxMemoryCacheSize"] != null)
{
this.MaxMemoryCacheSize = int.Parse(section.Attributes["maxMemoryCacheSize"].Value);
}
// CTS Configuration
if (ctsSectionConfig != null)
{
if (ctsSectionConfig.Attributes["messageRuntimeUrl"] != null)
{
MessageRuntimeUrl = ctsSectionConfig.Attributes["messageRuntimeUrl"].Value;
}
else
{
throw new ConfigurationErrorsException("When configuring CTS a Service URL must be provided via the 'messageRuntimeUrl' attribute");
}
if (ctsSectionConfig.Attributes["proxyAddress"] != null)
{
ProxyAddress = ctsSectionConfig.Attributes["proxyAddress"].Value;
}
// Next, we want to fill in code sets
XmlNodeList fillInCodeSets = ctsSectionConfig.SelectNodes(".//*[local-name() = 'fillInDetails']/@codeSystem");
foreach (XmlNode v in fillInCodeSets)
{
this.FillInCodeSets.Add(v.Value);
}
}
if (dbSectionConfig != null)
{
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[dbSectionConfig.Attributes["connection"].Value];
if (settings == null)
{
throw new ConfigurationErrorsException(String.Format("Cannot find the connection string '{0}'", dbSectionConfig.Attributes["connection"].Value), dbSectionConfig);
}
// Create the dbProvider and cstring
m_connectionString = settings.ConnectionString;
if (dbSectionConfig.Attributes["enableCtsFallback"] != null)
{
this.EnableCtsFallback = Convert.ToBoolean(dbSectionConfig.Attributes["enableCtsFallback"].Value);
}
// get the type
m_providerFactory = DbProviderFactories.GetFactory(settings.ProviderName);
if (m_providerFactory == null)
{
throw new ConfigurationErrorsException(String.Format("Can't find provider type '{0}'", settings.ProviderName), dbSectionConfig);
}
}
return(this);
}
protected virtual DbProviderFactory GetFactory(string providerName)
{
return(DbProviderFactories.GetFactory(providerName));
}
public SqlDbProviderDataStoreSimpleTest()
: base(new DbProviderDataStore <FooModel>(DbProviderFactories.GetFactory("System.Data.SqlClient"), TEST_CONNECTION_STRING))
{
RecreateTable();
}
/// <summary>
/// Default Constructor
/// </summary>
/// <param name="connectionString">The DB-ConnectionString to use.</param>
/// <param name="providerInvariantName">The type of the provider. CodeReview: use Type instead?</param>
public DataAccessFactory(string providerInvariantName, string connectionString)
{
_Provider = DbProviderFactories.GetFactory(providerInvariantName);
_Con = _Provider.CreateConnection();
_Con.ConnectionString = connectionString;
}
public void DbTesting()
{
// This class is used to verify database connection when starting application (Login Form)
// Updated 17 Dec 2017
// Version 01
try
{
// This method will be called from the Main.cs(Main_Load)
string provider = ConfigurationManager.AppSettings["provider"];
string connectionString = ConfigurationManager.AppSettings["connectionString"];
DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
using (DbConnection connection = factory.CreateConnection())
{
// Verify Database connection
if (connection == null)
{
MessageBox.Show("Connection Error", "PIMS", MessageBoxButtons.OK, MessageBoxIcon.Error);
//Console.WriteLine("Connection Error");
return;
}
connection.ConnectionString = connectionString;
connection.Open();
DbCommand command = factory.CreateCommand();
if (command == null)
{
MessageBox.Show("Command Error", "PIMS", MessageBoxButtons.OK, MessageBoxIcon.Error);
//Console.WriteLine("Command Error");
return;
}
// Read data
/**
* // Sample Query
* command.Connection = connection;
* command.CommandText = "Select * from Registered_Patient";
*
* using (DbDataReader dataReader = command.ExecuteReader())
* {
* string result = "";
*
* while (dataReader.Read())
* {
* result += $"{dataReader["Patient_Name"]}" + $"{dataReader["Patient_NIC"]}" + "\n";
* //Console.WriteLine($"{dataReader["User_ID"]}" + $"{dataReader["ContactNo"]}");
* }
* MessageBox.Show(result, "PIMS", MessageBoxButtons.OK, MessageBoxIcon.Information);
* }
**/
}
}
catch (Exception err)
{
MessageBox.Show(err.Message, "PIMS", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Page_Load(object sender, System.EventArgs e)
{
gID = Sql.ToGuid(Request["ID"]);
Guid gTHREAD_ID = Sql.ToGuid(txtTHREAD_ID.Value);
if (!Sql.IsEmptyGuid(gTHREAD_ID))
{
try
{
SqlProcs.spOPPORTUNITIES_THREADS_Update(gID, gTHREAD_ID);
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwOPPORTUNITIES_THREADS " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 11/27/2006 Paul. Make sure to filter relationship data based on team access rights.
Security.Filter(cmd, m_sMODULE, "list");
cmd.CommandText += " and OPPORTUNITY_ID = @OPPORTUNITY_ID" + ControlChars.CrLf;
cmd.CommandText += " order by DATE_ENTERED desc " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@OPPORTUNITY_ID", gID);
if (bDebug)
{
RegisterClientScriptBlock("vwOPPORTUNITIES_THREADS", Sql.ClientScriptBlock(cmd));
}
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
// 09/05/2005 Paul. LinkButton controls will not fire an event unless the the grid is bound.
//if ( !IsPostBack )
{
grdMain.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
public FlightRepository(string connectionString, string providerName)
{
this.connectionString = connectionString;
providerFactory = DbProviderFactories.GetFactory(providerName);
}
public static Status Submit(int id, string leadin, string count, string newsitems, string highlightitems)
{
Status stat = new Status();
GenericDatabase db = new GenericDatabase(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, DbProviderFactories.GetFactory("MySql.Data.MySqlClient"));
DbCommand cmd = db.GetStoredProcCommand("ins_media");
DateTime dte = DateTime.Now;
string name = "";
name = "BOD_News_" + GetWeekNumber(dte).ToString() + "_" + id.ToString() + "_" + dte.Year.ToString();
db.AddOutParameter(cmd, "ReturnCode", DbType.Int32, 8);
db.AddOutParameter(cmd, "ErrorMessage", DbType.String, 500);
db.AddOutParameter(cmd, "NewID", DbType.Int32, 8);
db.AddInParameter(cmd, "InID", DbType.Int32, id);
db.AddInParameter(cmd, "InName", DbType.String, name);
db.AddInParameter(cmd, "InLeadin", DbType.String, leadin);
db.AddInParameter(cmd, "InCount", DbType.String, count);
db.AddInParameter(cmd, "InNewsItems", DbType.String, newsitems);
db.AddInParameter(cmd, "InHighlightItems", DbType.String, highlightitems);
db.ExecuteNonQuery(cmd);
stat.ReturnCode = db.GetParameterValue(cmd, "ReturnCode") != null ? (Int32)db.GetParameterValue(cmd, "ReturnCode") : 0;
stat.ErrorMessage = name + ".html";
stat.NewID = db.GetParameterValue(cmd, "NewID") != null?db.GetParameterValue(cmd, "NewID").ToString() : "0";
Entry item = new Entry();
item.Name = name;
item.LeadIn = leadin;
item.NewItems = newsitems;
item.HighlightItems = highlightitems;
item.Count = count;
CreateHTML(item);
return(stat);
}
public SqlDataAccess()
{
dbProvider = DbProviderFactories.GetFactory("System.Data.SqlClient");
}
static void GeneraExcel(DataTable tabla, DataTable tablaDetCte, DataTable tablaDetFac, DataTable tablaDetPrd, string NombreMes)
{
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + NombreMes + @".xlsx;Extended Properties=""Excel 12.0 Xml""";
DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.OleDb");
try{
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = connection.CreateCommand())
{
connection.Open(); //open the connection
//use the '$' notation after the sheet name to indicate that this is
// an existing sheet and not to actually create it. This basically defines
// the metadata for the insert statements that will follow.
// If the '$' notation is removed, then a new sheet is created named 'Sheet1'.
#region OrdenCOmpra
//command.CommandText = "CREATE TABLE [Resumen] (CDI number, Id Number, NombreRik char(150), NombreCD char(150), MVI number , ComisionNeta number,Amortizacion number,TecSer number,Utrem number)";
command.CommandText = "CREATE TABLE [Resumen] (CDI number, NombreCD char(150), Año number, Mes number ,Rik Number, NombreRik char(150), Amortizacion number, TecSer number, Utrem number ,Comision number ,MVI number )";
command.ExecuteNonQuery();
for (int j = 0; j < tabla.Rows.Count; j++)
{
string comando = "";
//string descripcion = "";
comando = "INSERT INTO [Resumen] VALUES(";
comando = comando + tabla.Rows[j]["Id_Cd"].ToString() + ",";
comando = comando + '\"' + tabla.Rows[j]["CdiNombre"].ToString() + '\"' + ",";
comando = comando + tabla.Rows[j]["Año"].ToString() + ",";
comando = comando + tabla.Rows[j]["Mes"].ToString() + ",";
comando = comando + tabla.Rows[j]["Id"].ToString() + ",";
comando = comando + '\"' + tabla.Rows[j]["Nombre_Empleado"].ToString() + '\"' + ",";
comando = comando + tabla.Rows[j]["Amortizacion"].ToString() + ",";
comando = comando + tabla.Rows[j]["TecSer"].ToString() + ",";
comando = comando + tabla.Rows[j]["UTrem"].ToString() + ",";
comando = comando + tabla.Rows[j]["ComisionNeta"].ToString() + ",";
comando = comando + tabla.Rows[j]["MVI"].ToString();
//descripcion = tabla.Rows[j][i].ToString();
//descripcion = descripcion.Replace("\"", " ");
//comando = comando + '\"' + descripcion + '\"' + ",";
////for (int i = 0; i < tabla.Columns.Count; i++)
////{
//// if (tabla.Columns[i].ColumnName == "Id_Cd")
//// {
//// comando = comando + tabla.Rows[j][i].ToString() + ",";
//// }
//// //if (tabla.Columns[i].ColumnName == "Mes")
//// //{
//// // comando = comando + tabla.Rows[j][i].ToString() + ",";
//// //}
//// //if (tabla.Columns[i].ColumnName == "Año")
//// //{
//// // comando = comando + tabla.Rows[j][i].ToString() + ",";
//// //}
//// if (tabla.Columns[i].ColumnName == "CdiNombre")
//// {
//// comando = comando + '\"' + tabla.Rows[j][i].ToString() + '\"' + ",";
//// }
//// if (tabla.Columns[i].ColumnName == "Id")
//// {
//// comando = comando + tabla.Rows[j][i].ToString() + ",";
//// }
//// if (tabla.Columns[i].ColumnName == "Nombre_Empleado")
//// {
//// descripcion = tabla.Rows[j][i].ToString();
//// descripcion = descripcion.Replace("\"", " ");
//// comando = comando + '\"' + descripcion + '\"' + ",";
//// }
//// if (tabla.Columns[i].ColumnName == "Amortizacion")
//// {
//// comando = comando + tabla.Rows[j][i].ToString() + ",";
//// }
//// if (tabla.Columns[i].ColumnName == "TecSer")
//// {
//// comando = comando + tabla.Rows[j][i].ToString() + ",";
//// }
//// if (tabla.Columns[i].ColumnName == "UTrem")
//// {
//// comando = comando + tabla.Rows[j][i].ToString() ;
//// }
//// if (tabla.Columns[i].ColumnName == "ComisionNeta")
//// {
//// comando = comando + tabla.Rows[j][i].ToString() + ",";
//// }
//// if (tabla.Columns[i].ColumnName == "MVI")
//// {
//// comando = comando + tabla.Rows[j][i].ToString() + ",";
//// }
////}
command.CommandText = comando + ")";
command.ExecuteNonQuery();
}
#endregion OrdenCOmpra
#region Detalle Cliente
command.CommandText = "CREATE TABLE [Detalle por Cliente] (Id_Cd number, CdiNombre char(240), Id_Cte number,[Nombre Cliente] char(130) , Rik number, [Importe Vta Cob] number, [Utilidad Prima] number,Amortizacion number,GST number,[Utilidad Bruta] number, FPPP number,[Factor Rentabilidad] number,[Comisión Base] Number,Año number, Mes number)";
command.ExecuteNonQuery();
for (int jr = 0; jr < tablaDetCte.Rows.Count; jr++)
{
string comando = "INSERT INTO [Detalle por Cliente] VALUES(";
comando = comando + tablaDetCte.Rows[jr]["Id_Cd"].ToString() + ",";
comando = comando + '\"' + tablaDetCte.Rows[jr]["CdiNombre"].ToString() + '\"' + ",";
comando = comando + tablaDetCte.Rows[jr]["Id_Cte"].ToString() + ",";
comando = comando + '\"' + tablaDetCte.Rows[jr]["Nombre_Empleado"].ToString() + '\"' + ",";
comando = comando + tablaDetCte.Rows[jr]["Id"].ToString() + ",";
comando = comando + tablaDetCte.Rows[jr]["VtaCob"].ToString() + ",";
comando = comando + tablaDetCte.Rows[jr]["UP"].ToString() + ",";
comando = comando + tablaDetCte.Rows[jr]["Amortizacion"].ToString() + ",";
comando = comando + tablaDetCte.Rows[jr]["GST"].ToString() + ",";
comando = comando + tablaDetCte.Rows[jr]["UB"].ToString() + ",";
comando = comando + tablaDetCte.Rows[jr]["PPPA"].ToString() + ",";
comando = comando + tablaDetCte.Rows[jr]["FR"].ToString() + ",";
comando = comando + tablaDetCte.Rows[jr]["CB"].ToString() + ",";
comando = comando + tablaDetCte.Rows[jr]["Año"].ToString() + ",";
comando = comando + tablaDetCte.Rows[jr]["Mes"].ToString();
command.CommandText = comando + ")";
command.ExecuteNonQuery();
}
#endregion DetCliente
#region Detalle Factura
command.CommandText = "CREATE TABLE [DetalleFacturas] (Id_Cd number, CdiNombre char(240), [Factura] char(40), Id_Cte number, [Cliente] char(80) , Territorio number, Rik number, [Fecha Venc.] char(40), [Fecha Pago] char(40),[Días] number,Importe number,[Utilidad Prima] number,[Mult Ajuste Cobranza] number, [Ajuste Cobranza] number,Año number, Mes number)";
command.ExecuteNonQuery();
for (int jr = 0; jr < tablaDetFac.Rows.Count; jr++)
{
string comando = "INSERT INTO [DetalleFacturas] VALUES(";
comando = comando + tablaDetFac.Rows[jr]["Id_Cd"].ToString() + ",";
comando = comando + '\"' + tablaDetFac.Rows[jr]["CdiNombre"].ToString() + '\"' + ",";
comando = comando + tablaDetFac.Rows[jr]["Pag_referencia"].ToString() + ",";
comando = comando + tablaDetFac.Rows[jr]["Id_Cte"].ToString() + ",";
comando = comando + '\"' + tablaDetFac.Rows[jr]["Nombre_Empleado"].ToString() + '\"' + ",";
comando = comando + tablaDetFac.Rows[jr]["Id_Territorio"].ToString() + ",";
comando = comando + tablaDetFac.Rows[jr]["Id"].ToString() + ",";
comando = comando + '\"' + tablaDetFac.Rows[jr]["FechaVencimiento"].ToString() + '\"' + ",";
comando = comando + '\"' + tablaDetFac.Rows[jr]["FechaPago"].ToString() + '\"' + ",";
comando = comando + tablaDetFac.Rows[jr]["Dias"].ToString() + ",";
comando = comando + tablaDetFac.Rows[jr]["Importe"].ToString() + ",";
comando = comando + tablaDetFac.Rows[jr]["UP"].ToString() + ",";
comando = comando + tablaDetFac.Rows[jr]["Mult_Porc"].ToString() + ",";
comando = comando + tablaDetFac.Rows[jr]["AjCobranza"].ToString() + ",";
comando = comando + tablaDetFac.Rows[jr]["Año"].ToString() + ",";
comando = comando + tablaDetFac.Rows[jr]["Mes"].ToString();
command.CommandText = comando + ")";
command.ExecuteNonQuery();
}
#endregion Remision
#region Detalle Productos
command.CommandText = "CREATE TABLE [DetalleProductos] (Id_Cd number, CdiNombre char(240), [Factura] char(40), Id_Cte number,Cliente char(80) , Territorio number, Rik number, Producto int, Utilidad_Prima number,Año number, Mes number)";
command.ExecuteNonQuery();
for (int jr = 0; jr < tablaDetPrd.Rows.Count; jr++)
{
string comando = "INSERT INTO [DetalleProductos] VALUES(";
comando = comando + tablaDetPrd.Rows[jr]["Id_Cd"].ToString() + ",";
comando = comando + '\"' + tablaDetPrd.Rows[jr]["CdiNombre"].ToString() + '\"' + ",";
comando = comando + tablaDetPrd.Rows[jr]["Pag_referencia"].ToString() + ",";
comando = comando + tablaDetPrd.Rows[jr]["Id_Cte"].ToString() + ",";
comando = comando + '\"' + tablaDetPrd.Rows[jr]["Nombre_Empleado"].ToString() + '\"' + ",";
comando = comando + tablaDetPrd.Rows[jr]["Id_Territorio"].ToString() + ",";
comando = comando + tablaDetPrd.Rows[jr]["Id"].ToString() + ",";
comando = comando + tablaDetPrd.Rows[jr]["Id_TipoRepresentante"].ToString() + ",";
comando = comando + tablaDetPrd.Rows[jr]["UP"].ToString() + ",";
comando = comando + tablaDetPrd.Rows[jr]["Año"].ToString() + ",";
comando = comando + tablaDetPrd.Rows[jr]["Mes"].ToString();
command.CommandText = comando + ")";
command.ExecuteNonQuery();
}
#endregion Productos
connection.Close();
}
}
}
catch (Exception ex)
{
//DisplayMensajeAlerta(string.Concat(ex.Message, "Page_Load_error"));
}
}
protected void Page_Load(object sender, EventArgs e)
{
String strSetup = ConfigurationManager.AppSettings["Setup"];
String strProvider = ConfigurationManager.AppSettings["Provider"];
String strConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
lblSetup.Text = strSetup;
lblConnectionString.Text = strConnectionString;
lblProvider.Text = strProvider;
String strDetailMessage = "<div style='border:1px solid #B20012;background:#EAD1D3;display:block;padding:10px;margin:10px;'>{0}</div>";
DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(strProvider);
using (DbConnection dbConnection = dbProviderFactory.CreateConnection())
{
try
{
dbConnection.ConnectionString = strConnectionString;
dbConnection.Open();
lblConnectionTest.Text = "Success";
lblConnectionTest.ForeColor = Color.FromName("#0F6004");
lblConnectionTest.Font.Bold = true;
dbConnection.Close();
}
catch (Exception ex)
{
Exception inner = ex.InnerException;
string strTitle = String.Format("<b>{0}</b><br><br>", ex.Message);
lblConnectionTest.Text = "Error";
lblConnectionTest.Text += String.Format(strDetailMessage, strTitle + ex.StackTrace.Replace("\n", "<br>"));
while (inner != null)
{
strTitle = String.Format("<b>{0}</b><br><br>", inner.Message);
lblConnectionTest.Text += String.Format(strDetailMessage, strTitle + inner.StackTrace.Replace("\n", "<br>"));
inner = inner.InnerException;
}
}
}
// Mail Settings
try
{
string strSubject = "Test Mail", strFrom = Blogsa.Settings["smtp_email"].Value, strTo = strFrom,
strBody = "This mail sended because of test. If you see this mail then your smtp settings is okey.";
bool bIsBodyHtml = true;
MailMessage message = new MailMessage();
message.Subject = "Test Mail";
message.From = new MailAddress(strFrom);
message.To.Add(new MailAddress(strTo, strTo));
message.Body = strBody;
message.IsBodyHtml = bIsBodyHtml;
message.Priority = MailPriority.High;
string strServer = Blogsa.Settings["smtp_server"].Value;
int iPort = Convert.ToInt32(Blogsa.Settings["smtp_port"].Value);
string strUserName = Blogsa.Settings["smtp_user"].Value;
string strPassword = Blogsa.Settings["smtp_pass"].Value;
lblSMTPUsername.Text = strUserName;
lblSMTPPassword.Text = strPassword;
lblSMTPServerPort.Text = String.Format("{0} / {1}", strServer, iPort);
SmtpClient client = new SmtpClient(strServer, iPort);
NetworkCredential SMTPUserInfo = new NetworkCredential(strUserName, strPassword);
client.UseDefaultCredentials = false;
client.Credentials = SMTPUserInfo;
client.EnableSsl = Convert.ToBoolean(Blogsa.Settings["smtp_usessl"].Value);
client.DeliveryMethod = SmtpDeliveryMethod.Network;
client.Send(message);
SuccessMessage(lblSMTPTest, "Success");
}
catch (Exception ex)
{
Exception inner = ex.InnerException;
string strTitle = String.Format("<b>{0}</b><br><br>", ex.Message);
lblSMTPTest.Text = "Error";
lblSMTPTest.Text += String.Format(strDetailMessage, strTitle + ex.StackTrace.Replace("\n", "<br>"));
while (inner != null)
{
strTitle = String.Format("<b>{0}</b><br><br>", inner.Message);
lblSMTPTest.Text += String.Format(strDetailMessage, strTitle + inner.StackTrace.Replace("\n", "<br>"));
inner = inner.InnerException;
}
}
// File System Settings
try
{
FileStream file = File.Open(Server.MapPath("~/web.config"), FileMode.Append);
file.Close();
SuccessMessage(lblWebConfig, "Read / Write [OK]");
}
catch
{
SuccessMessage(lblWebConfig, "Read / Write [Access Error]");
}
try
{
StreamWriter Sw = new StreamWriter(Server.MapPath("~/App_Data/accesscontrol.txt"));
Sw.Write("please delete this file");
Sw.Close();
File.Delete(Server.MapPath("~/App_Data/accesscontrol.txt"));
SuccessMessage(lblAppData, "Read / Write [OK]");
}
catch
{
SuccessMessage(lblAppData, "Read / Write [Access Error]");
}
try
{
StreamWriter Sw = new StreamWriter(Server.MapPath("~/Upload/accesscontrol.txt"));
Sw.Write("please delete this file");
Sw.Close();
File.Delete(Server.MapPath("~/Upload/accesscontrol.txt"));
SuccessMessage(lblUpload, "Read / Write [OK]");
}
catch
{
SuccessMessage(lblUpload, "Read / Write [Access Error]");
}
}
private void Page_Load(object sender, System.EventArgs e)
{
Utils.SetPageTitle(Page, L10n.Term(".moduleList." + m_sMODULE));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "edit") >= 0);
if (!this.Visible)
{
return;
}
try
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
gID = Sql.ToGuid(Request["ID"]);
if (!IsPostBack)
{
Guid gDuplicateID = Sql.ToGuid(Request["DuplicateID"]);
if (!Sql.IsEmptyGuid(gID) || !Sql.IsEmptyGuid(gDuplicateID))
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwPROSPECTS_Edit" + ControlChars.CrLf
+ " where ID = @ID " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
if (!Sql.IsEmptyGuid(gDuplicateID))
{
Sql.AddParameter(cmd, "@ID", gDuplicateID);
gID = Guid.Empty;
}
else
{
Sql.AddParameter(cmd, "@ID", gID);
}
con.Open();
#if DEBUG
Page.RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
#endif
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rdr.Read())
{
ctlModuleHeader.Title = Sql.ToString(rdr["SALUTATION"]) + " " + Sql.ToString(rdr["FIRST_NAME"]) + " " + Sql.ToString(rdr["LAST_NAME"]);
Utils.SetPageTitle(Page, L10n.Term(".moduleList." + m_sMODULE) + " - " + ctlModuleHeader.Title);
Utils.UpdateTracker(Page, m_sMODULE, gID, ctlModuleHeader.Title);
ViewState["ctlModuleHeader.Title"] = ctlModuleHeader.Title;
this.AppendEditViewFields(m_sMODULE + ".EditView", tblMain, rdr);
this.AppendEditViewFields(m_sMODULE + ".EditAddress", tblAddress, rdr);
this.AppendEditViewFields(m_sMODULE + ".EditDescription", tblDescription, rdr);
}
}
}
}
}
else
{
this.AppendEditViewFields(m_sMODULE + ".EditView", tblMain, null);
this.AppendEditViewFields(m_sMODULE + ".EditAddress", tblAddress, null);
this.AppendEditViewFields(m_sMODULE + ".EditDescription", tblDescription, null);
}
}
else
{
// 12/02/2005 Paul. When validation fails, the header title does not retain its value. Update manually.
ctlModuleHeader.Title = Sql.ToString(ViewState["ctlModuleHeader.Title"]);
Utils.SetPageTitle(Page, L10n.Term(".moduleList." + m_sMODULE) + " - " + ctlModuleHeader.Title);
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
ctlEditButtons.ErrorText = ex.Message;
}
}
public static void DbAggregators_COUNTDISTINCT(string tablename)
{
{
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
DbConnection conn = fact.CreateConnection();
try
{
conn.ConnectionString = "Data Source = localhost";
conn.Open();
{
Console.WriteLine("Testing countdistinct(int)...");
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select id, countdistinct(id) from " + tablename + " group by id";
Dictionary <int, int> expected = new Dictionary <int, int>();
expected[10] = 1;
expected[20] = 1;
expected[30] = 1;
Dictionary <int, int> resultCount = new Dictionary <int, int>();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
int count = (int)reader.GetInt64(1);
if (!expected.ContainsKey(id))
{
throw new Exception("id returned from countdistinct group by is invalid.");
}
if (expected[id] != count)
{
throw new Exception("Expected countdistinct: " + expected[id].ToString() + ", but got " + count.ToString() + " instead.");
}
if (!resultCount.ContainsKey(id))
{
resultCount[id] = 0;
}
resultCount[id]++;
}
reader.Close();
int cnt = 0;
foreach (int c in resultCount.Values)
{
cnt += c;
}
if (cnt != 3)
{
throw new Exception("Expected row count: 3, but got " + cnt.ToString() + " instead.");
}
Console.WriteLine("Expected results received.");
}
}
finally
{
conn.Close();
}
}
{
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
DbConnection conn = fact.CreateConnection();
try
{
conn.ConnectionString = "Data Source = localhost";
conn.Open();
{
Console.WriteLine("Testing countdistinct(double)...");
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select id, countdistinct(cost) from " + tablename + " group by id";
Dictionary <int, int> expected = new Dictionary <int, int>();
expected[10] = 3;
expected[20] = 2;
expected[30] = 2;
Dictionary <int, int> resultCount = new Dictionary <int, int>();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
int count = (int)reader.GetInt64(1);
if (!expected.ContainsKey(id))
{
throw new Exception("id returned from countdistinct group by is invalid.");
}
if (expected[id] != count)
{
throw new Exception("Expected countdistinct: " + expected[id].ToString() + ", but got " + count.ToString() + " instead.");
}
if (!resultCount.ContainsKey(id))
{
resultCount[id] = 0;
}
resultCount[id]++;
}
reader.Close();
int cnt = 0;
foreach (int c in resultCount.Values)
{
cnt += c;
}
if (cnt != 3)
{
throw new Exception("Expected row count: 3, but got " + cnt.ToString() + " instead.");
}
Console.WriteLine("Expected results received.");
}
}
finally
{
conn.Close();
}
}
}
static void Main()
{
DbProviderFactories.RegisterFactory("provider", SqlClientFactory.Instance);
var providerFactory = DbProviderFactories.GetFactory("provider");
DataSet dataSet = new DataSet("myAutoDb");
var dataAdapter = providerFactory.CreateDataAdapter();
var connection = providerFactory.CreateConnection();
var selectCommand = providerFactory.CreateCommand();
connection.ConnectionString = @"Provider=1-PC\SQLEXPRESS;Data Source=1-PC\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=msdb";
selectCommand.CommandText = "select * from Users";
selectCommand.Connection = connection;
dataAdapter.SelectCommand = selectCommand;
var commandBuilder = providerFactory.CreateCommandBuilder();
commandBuilder.DataAdapter = dataAdapter;
DataTable usersTable = new DataTable("Users");
DataColumn idColumn = new DataColumn
{
ColumnName = "Id",
DataType = typeof(int),
AllowDBNull = false,
AutoIncrement = true,
Unique = true
};
usersTable.Columns.Add(idColumn);
usersTable.PrimaryKey = new DataColumn[] { idColumn };
DataColumn loginColumn = new DataColumn
{
ColumnName = "Login",
DataType = typeof(string),
AllowDBNull = false,
AutoIncrement = true,
Unique = true,
MaxLength = 20
};
usersTable.Columns.Add(loginColumn);
DataColumn PasswordColumn = new DataColumn
{
ColumnName = "Password",
DataType = typeof(string),
AllowDBNull = false,
AutoIncrement = true,
Unique = true,
MaxLength = 20
};
usersTable.Columns.Add(PasswordColumn);
DataTable ticketTable = new DataTable("Ticket");
ticketTable.Columns.Add(idColumn);
ticketTable.PrimaryKey = new DataColumn[] { idColumn };
DataColumn nameOfTicet = new DataColumn
{
ColumnName = "nameOfTicet",
DataType = typeof(string),
AllowDBNull = false,
AutoIncrement = true,
Unique = true,
MaxLength = 20
};
ticketTable.Columns.Add(nameOfTicet);
DataColumn type = new DataColumn
{
ColumnName = "typeOfTicket",
DataType = typeof(string),
AllowDBNull = false,
AutoIncrement = true,
Unique = true,
MaxLength = 20
};
ticketTable.Columns.Add(type);
dataAdapter.Fill(dataSet);
}
public List <PetPlaceListModel> FillPetPlaceSearchGrid(PetPlaceListModel model)
{
ShomaRMEntities db = new ShomaRMEntities();
List <PetPlaceListModel> lstData = new List <PetPlaceListModel>();
try
{
DataTable dtTable = new DataTable();
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_GetPetPlacePaginationAndSearchData";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter paramC = cmd.CreateParameter();
paramC.ParameterName = "Criteria";
paramC.Value = model.Criteria;
cmd.Parameters.Add(paramC);
DbParameter paramPN = cmd.CreateParameter();
paramPN.ParameterName = "PageNumber";
paramPN.Value = model.PageNumber;
cmd.Parameters.Add(paramPN);
DbParameter paramNOR = cmd.CreateParameter();
paramNOR.ParameterName = "NumberOfRows";
paramNOR.Value = model.NumberOfRows;
cmd.Parameters.Add(paramNOR);
DbParameter paramSortBy = cmd.CreateParameter();
paramSortBy.ParameterName = "SortBy";
paramSortBy.Value = model.SortBy;
cmd.Parameters.Add(paramSortBy);
DbParameter paramOrderBy = cmd.CreateParameter();
paramOrderBy.ParameterName = "OrderBy";
paramOrderBy.Value = model.OrderBy;
cmd.Parameters.Add(paramOrderBy);
DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dtTable);
db.Database.Connection.Close();
}
foreach (DataRow dr in dtTable.Rows)
{
PetPlaceListModel usm = new PetPlaceListModel();
usm.PetPlaceID = int.Parse(dr["PetPlaceID"].ToString());
usm.PetPlace = dr["PetPlace"].ToString();
usm.PropertyID = long.Parse(dr["PropertyID"].ToString());
usm.Charges = decimal.Parse(dr["Charges"].ToString());
usm.Description = dr["Description"].ToString();
usm.NumberOfPages = int.Parse(dr["NumberOfPages"].ToString());
lstData.Add(usm);
}
db.Dispose();
return(lstData.ToList());
}
catch (Exception ex)
{
db.Database.Connection.Close();
throw ex;
}
}
