//lista bairros
public List<Bairros> ListaBairros(string cidade)
{
List<Bairros> ListaBairros = new List<Bairros>();
String Qry;
SQLServer SQL = new SQLServer();
SQL.ConectaDB();
Qry = "select * from tblBairro where codCidade = " + cidade + " order by nomeBairro";
SqlDataReader Rs = SQL.DR(Qry);
while (Rs.Read())
{
Bairros Brrs = new Bairros();
Brrs.idBairro = Convert.ToInt32(Rs["codBairro"].ToString());
Brrs.Bairro = Rs["nomeBairro"].ToString();
ListaBairros.Add(Brrs);
}
SQL.Cnx.Close();
return ListaBairros;
}
//Lista categoria :)
public List<Categoria> ListaCategoria()
{
List<Categoria> ListaCategoria = new List<Categoria>();
String Qry;
SQLServer SQL = new SQLServer();
SQL.ConectaDB();
Qry = "select * from tblCategoria order by nomeCategoria";
SqlDataReader Rs = SQL.DR(Qry);
while (Rs.Read())
{
Categoria Catego = new Categoria();
Catego.Cat = Rs["nomeCategoria"].ToString();
Catego.idCategoria = Convert.ToInt32(Rs["codCategoria"].ToString());
ListaCategoria.Add(Catego);
}
SQL.Cnx.Close();
return ListaCategoria;
}
/// <summary>
/// 备份数据库
/// </summary>
/// <param name="serverName">数据实例名</param>
/// <param name="userName">用户</param>
/// <param name="password">密码</param>
/// <param name="databaseName">库名</param>
/// <param name="path">备份路径</param>
public static void CompressDatabase(string serverName,string userName,string password,string databaseName,string path)
{
Backup oBackup = new Backup();
SQLServer oSQLServer = new SQLServer();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(serverName, userName, password);
oBackup.Action = SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.Database = databaseName;
oBackup.Files = path;
oBackup.BackupSetName = databaseName;
oBackup.BackupSetDescription = string.Format("{0} {1}", databaseName, DateTime.Now);
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
}
catch
{
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
public List<Cidades> ListaCitys(string estados)
{
List<Cidades> ListaCitys = new List<Cidades>();
String Qry;
SQLServer SQL = new SQLServer();
SQL.ConectaDB();
Qry = "select * from tblCidade where codEstado = '" + estados + "' order by nomeCidade";
SqlDataReader Rs = SQL.DR(Qry);
while (Rs.Read())
{
Cidades Cdd = new Cidades();
Cdd.City = Rs["nomeCidade"].ToString();
Cdd.idCity = Convert.ToInt32(Rs["codCidade"].ToString());
ListaCitys.Add(Cdd);
}
SQL.Cnx.Close();
return ListaCitys;
}
public static DataTable CPXLRestoreSup(string id, ref string msg)
{
string sql = "select FNumber,FName from t_chanpinxilie cpxl left join t_Supplier per on cpxl.supID=per.FItemID where id=" + id + "";
DataTable dt = SQLServer.GetDataTable(sql, ref Msg);
msg = Msg;
return(dt);
}
public static bool CPXLEditSup(string id, string sup, ref string msg)
{
string sql = "update t_chanpinxilie set supID=per.FItemID from(select*from t_Supplier where FNumber ='" + sup + "')per where id=" + id + "";
bol = SQLServer.ExecSql(sql, ref Msg);
msg = Msg;
return(bol);
}
private int InsertMaterial(MaterialMsg material)
{
string insertSQL = $"INSERT INTO {DbTable.F_MATERIAL_NAME}({DbTable.F_Material.MATERIAL_CODE},{DbTable.F_Material.MATERIAL_AMOUNT}) " +
$"VALUES('{material.MaterialCode}','{material.MaterialAmount}')";
LogHelper.Log.Info($"InsertMaterial={insertSQL}");
return(SQLServer.ExecuteNonQuery(insertSQL));
}
public int DeleteProductMaterial(string typeNo, string materialCode)
{
string deleteSQL = $"DELETE FROM {DbTable.F_PRODUCT_MATERIAL_NAME} " +
$"WHERE {DbTable.F_PRODUCT_MATERIAL.TYPE_NO} = '{typeNo}' AND " +
$"{DbTable.F_PRODUCT_MATERIAL.MATERIAL_CODE} = '{materialCode}'";
return(SQLServer.ExecuteNonQuery(deleteSQL));
}
public static DataSet SelectTypeNoList()
{
var selectSQL = $"SELECT {DbTable.F_PRODUCT_PACKAGE_STORAGE.PRODUCT_TYPE_NO} " +
$"FROM " +
$"{DbTable.F_PRODUCT_PACKAGE_STORAGE_NAME} ";
return(SQLServer.ExecuteDataSet(selectSQL));
}
private int UpdateMaterial(MaterialMsg material)
{
string updateSQL = $"UPDATE {DbTable.F_MATERIAL_NAME} SET {DbTable.F_Material.MATERIAL_AMOUNT} = '{material.MaterialAmount}' " +
$"WHERE {DbTable.F_Material.MATERIAL_CODE} = '{material.MaterialCode}'";
LogHelper.Log.Info($"UpdateMaterial={updateSQL}");
return(SQLServer.ExecuteNonQuery(updateSQL));
}
private void Disconnect()
{
if (db != null)
{
db.Close();
}
db = null;
}
/// <summary>
/// 查询结果
/// </summary>
/// <param name="snInner"></param>
/// <param name="snOutter"></param>
/// <param name="typeNumber"></param>
/// <param name="stationName"></param>
/// <returns></returns>
public static DataSet SelectProductData(string snInner, string snOutter, string typeNumber, string stationName)
{
string selectSQL = "SELECT [SN],[Type_Number],[Station_Name],[Test_Result],[CreateDate],[UpdateDate],[Remark] " +
"FROM [WT_SCL].[dbo].[Product_Data] " +
$"WHERE [SN] = '{snInner}' OR [SN]='{snInner}-{snOutter}' AND [Type_Number]='{typeNumber}' AND [Station_Name]='{stationName}'";
return(SQLServer.ExecuteDataSet(selectSQL));
}
public void Remover(int idCurso, int idProfessor)
{
using (conexao = new SQLServer())
{
var query = $"DELETE FROM curso_professor WHERE id_curso = {idCurso} and id_professor = {idProfessor}";
conexao.ExecutarComando(query, false);
}
}
//Tạo SQLDMO database
/// <summary>
/// Huỷ kết nối trước đó
/// </summary>
private void disconnectDB()
{
if (sqlSrv != null)
{
sqlSrv = null;
}
sqlSrv = new SQLDMO.SQLServerClass();
}
public void Remover(int id)
{
using (conexao = new SQLServer())
{
var query = $"DELETE FROM endereco WHERE id = {id};";
conexao.ExecutarComando(query, false);
}
}
public Form1()
{
InitializeComponent();
// MyWSIM = new WaterSimManager_DB(".", ".");
SQLServer ServerType = SQLServer.stText;
string TheDefaultDatabase = Path.GetDirectoryName(Application.ExecutablePath);
MyWSIM = new WaterSimManager_DB(ServerType, ".", ".", TheDefaultDatabase, "", "", "", "");
// MyWSIM = new WaterSimManager_SIO(".", ".");
//UnitData TheData = null;
//TheData = MyWSIM.TheCRFNetwork.CRFUnitData;
// This add some groups for the west data
WaterSimDCDC.WestTools.AddWestParameterGroups(MyWSIM);
SetupPhrasesAndColors();
foreach (string name in MyWSIM.WaterSimWestModel.UnitModelNames)
{
DisplaySankeyMenuItem.DropDownItems.Add(name);
SanKeyGraphcomboBox.Items.Add(name);
}
parameterTreeView1.ParameterManager = MyWSIM.ParamManager;
// Set al;l Industry to purple, default is red
foreach (WaterSimCRFModel WSM in MyWSIM.WaterSimWestModel.WaterSimCRFModels)
{
WSM.TheCRFNetwork.Industrial.Color = Color.Purple;
}
// Reset Size of Sankey Graph
sankeyGraphUnit.Width = SankeyGraphPanel.Width - 2;
sankeyGraphUnit.Invalidate();
Application.DoEvents();
// Ok, reset the graph
ResetSanKeyGraphUnit(ModelUnitName);
// Create the chart manager
MyCM = new ChartManager(chart1, "MyCHart");
// Setup Input Tree
treeViewInput.CheckBoxes = true;
foreach (int emp in MyWSIM.ParamManager.eModelParameters())
{
ModelParameterClass MP = MyWSIM.ParamManager.Model_Parameter(emp);
if (MP.isInputParam)
{
treeViewInput.Nodes.Add(new ParameterTreeNode(MP));
}
}
// Setup MultiSankey Window
MyMultiSankey = new ShowMultipleSankeyV1(MyWSIM);
MyMultiSankey.Hide();
//regionTreeViewClass1.WaterSim = MyWSIM;
regionTreeViewClass1.WaterSim = MyWSIM;
regionTreeViewClass1.CallBackHandler = RegionTreeViewCallback;
// LoadParameterDropDown();
}
public static void UpdatebindTestResultHistory(string pid, string sid, int state)
{
var updateSQL = $"update {DbTable.F_TEST_RESULT_HISTORY_NAME} set " +
$"{DbTable.F_TEST_RESULT_HISTORY.bindState} = '{state}' where " +
$"{DbTable.F_TEST_RESULT_HISTORY.pcbaSN} = '{pid}' and " +
$"{DbTable.F_TEST_RESULT_HISTORY.productSN} = '{sid}'";
SQLServer.ExecuteNonQuery(updateSQL);
}
public void Inserir(CursoProfessor cursoProfessor)
{
string query = "INSERT INTO curso_professor (id_curso, id_professor) " +
$"VALUES ('{cursoProfessor.Curso.Id}', '{cursoProfessor.Professor.Id}');";
using (conexao = new SQLServer())
{
conexao.ExecutarComando(query, false);
}
}
//***************************************************
//功能：用于获得Bus_CurrentUser_deptName
//***************************************************
public static string getBus_CurrentUser_deptName()
{
string t = "";
using (SQLServer db = new SQLServer(SqlStr.ConnStringBuilder.ConnectionString)) {
t = db.GetStrBySQL("select dept from puku where userpu='" + Bus_DataBus.Bus_CurrentUser_Username + "'");
}
return(t);
}
static void Main(string[] args)
{
var ss = new SQLServer();
var ok = ss.Connect("localhost", "sqlexpress", "EdDb");
var students = ss.ExcecuteQuery("SELECT * From Student;");
ss.Disconnect();
}
//private static SQLServer.UniDbTypes ConvertToDatabaseType(Property property)
//{
// if ((property.Type == "System.String" || property.Type == "string") &&
// (property.ValidationOptions.MaximumLength.HasValue && property.ValidationOptions.MaximumLength > 0))
// {
// return SQLServer.UniDbTypes.NVarChar;
// }
// return SQLServer.ConvertCLRTypeNameToSQLTypeName(property.Type);
//}
private SQLServer.UniDbTypes ConvertToDatabaseType(ComponentProperty property)
{
if ((property.Type == "System.String" || property.Type == "string") &&
(property.ValidationOptions.MaximumLength.HasValue && property.ValidationOptions.MaximumLength > 0))
{
return(SQLServer.UniDbTypes.NVarChar);
}
return(SQLServer.ConvertCLRTypeNameToSQLTypeName(property.Type));
}
public static DataTable LoadPlugResource()
{
sql = @"select id,1 as ResourceTypeID,'页面' as ResourceType,EName,CName,null from t_PlugWebPage union
select id,2 as ResourceTypeID,'按钮' as ResourceType,code,name,WebPage from t_PlugResource";
DataTable dt = SQLServer.GetDataTable(sql, ref Msg);
return(dt);
}
public void Remover(int id, int idEndereco)
{
using (conexao = new SQLServer())
{
var query = $"DELETE FROM professor WHERE id = {id}";
conexao.ExecutarComando(query, false);
}
enderecoPersistencia.Remover(idEndereco);
}
public DataTable GetPassWord(string userid)
{
DataTable dt = new DataTable();
SQLServer db = new SQLServer();
string sql = "Select * From A007 Where A007_ID= '" + userid + "'";
db.ExcuteDataTable(dt, sql, CommandType.Text);
return(dt);
}
public LogInjectorService(IServiceScopeFactory serviceScopeFactory)
{
SqlServer = new SQLServer(Helper.ConfigurationDto.Config.SqlServer.Active, serviceScopeFactory);
var file = new File(Helper.ConfigurationDto.Config.File.Active);
var sentry = new Sentry(Helper.ConfigurationDto.Config.Sentry.Active);
SqlServer.SetNext(file);
file.SetNext(sentry);
}
public string CheckUserExist(string userid)
{
DataTable dt = new DataTable();
SQLServer db = new SQLServer();
string sql = "Select Top 1 count(*) From A007 Where A007_ID= '" + userid + "'";
db.ExcuteDataTable(dt, sql, CommandType.Text);
return(dt.Rows[0][0].ToString());
}
public void Delete(int id)
{
sb = new StringBuilder();
sb.Append($"DELETE FROM aula WHERE id = {id};");
using (connection = new SQLServer())
{
connection.ExecuteCommand(sb.ToString());
}
}
public InputData GetCurveData(String currency, int runID)
{
Utils.Log("Creating List");
string sql = "EXEC CurveGeneratorGetLatestData '" + currency + "', " + runID;
SQLServer db = new SQLServer(Settings.Default.DSN);
Utils.Log(string.Format("Calling DSN: {0} {1}", Settings.Default.DSN, sql));
SqlDataReader dr = db.FetchDataReader(sql);
InputData input = new InputData();
input.LiborData = new List <Rate>();
input.SwapData = new List <Rate>();
input.FutureData = new List <Rate>();
while (dr.Read())
{
input.StartDate = DateTime.Parse(dr["TimeStamp"].ToString()); // DateTime.Now;
Rate rate = new Rate();
//rate.StartDate = DateTime.Now;
rate.Ask = (double)dr["Ask"];
rate.Bid = (double)dr["Bid"];
rate.SecType = dr["TypeName"].ToString();
if (rate.SecType == "Future")
{
rate.Expiry = DateTime.Parse(dr["Maturity"].ToString());
}
else
{
rate.TermCode = dr["Type"].ToString();
}
switch (rate.SecType)
{
case "Libor":
input.LiborData.Add(rate);
break;
case "Swap":
input.SwapData.Add(rate);
break;
case "Future":
input.FutureData.Add(rate);
break;
}
Utils.Log(string.Format("{0} Curve data used: {1}", currency, rate.ToString()));
}
dr.Close();
db.Close();
return(input);
}
public void Create(Aula aula)
{
sb = new StringBuilder();
sb.Append("INSERT INTO aula (nome_disciplina, quantidade_aluno, nome_professor, nome_faculdade) ");
sb.Append($"VALUES ('{aula.NomeDisciplina}', {aula.QuantidadeAluno}, '{aula.NomeProfessor}', '{aula.NomeFaculdade}');");
using (connection = new SQLServer())
{
connection.ExecuteCommand(sb.ToString());
}
}
private void Connect()
{
string dsn = "BossLive";;
#if DEBUG
dsn = "BossTest";
#endif
db = new SQLServer(dsn);
}
public DataTable GetColumnByTable(string str_table)
{
DataTable dt = new DataTable();
SQLServer db = new SQLServer();
string sql;
sql = "Select t.* from " + str_table + " t where 1 = 2 ";
db.ExcuteDataTable(dt, sql, CommandType.Text);
return(dt);
}
public void Update(Aula aula)
{
sb = new StringBuilder();
sb.Append($"UPDATE aula SET nome_disciplina = '{aula.NomeDisciplina}', quantidade_aluno = {aula.QuantidadeAluno}, nome_professor = '{aula.NomeProfessor}', nome_faculdade = '{aula.NomeFaculdade}' ");
sb.Append($"WHERE id = {aula.Id};");
using (connection = new SQLServer())
{
connection.ExecuteCommand(sb.ToString());
}
}
public void Create(Finalidade finalidade)
{
sb = new StringBuilder();
sb.Append("INSERT INTO finalidade (descricao, origem) ");
sb.Append($"VALUES ('{finalidade.Descricao}', '{finalidade.Origem}');");
using (connection = new SQLServer())
{
connection.ExecuteCommand(sb.ToString());
}
}
public int Inserir(Endereco endereco)
{
string query = "INSERT INTO endereco (logradouro, numero, cep, bairro, complemento, localidade, uf) " +
$"VALUES ('{endereco.Logradouro}', {endereco.Numero}, '{endereco.Cep}', '{endereco.Bairro}', '{endereco.Complemento}', '{endereco.Localidade}', '{endereco.Uf}');";
using (conexao = new SQLServer())
{
return(conexao.ExecutarComando(query, true));
}
}
public string InsereItemEncontrado(ParametrosEncontreiPerdi Parametros)
{
try
{
//Carrega variável com strings contidas no cookie criado, caso ele exista.
string Login = HttpContext.Current.User.Identity.Name;
string[] Usr = Login.Split(';');
string usuario = Usr[0];
string senha = Usr[1];
String Qry;
SQLServer SQL = new SQLServer();
SQL.ConectaDB();
Qry = "select idUsuario from tblUsuario where emailUsuario = '" + usuario.Trim() + "' and senhaUsuario = '" + senha.Trim() + "'";
SqlDataReader Rs = SQL.DR(Qry);
string Id = "";
if (Rs.Read() == true)
{
Id = Rs["idUsuario"].ToString();
}
SQL.Cnx.Close();
SQL.ConectaDB();
Qry = "Insert into tblItemEncontrado " +
"(idUsuario, codCategoria, codSubCategoria, codPais, "
+ "dataCadastro, descricao, Latitude, Longitude) " +
"values (" + Id + ", " + Parametros.Categoria + ","
+ Parametros.Subcategoria + ", 1, getdate(), 'null', '" +
Parametros.lttde + "','" + Parametros.lgttde + "')";
SQL.ExecQry(Qry);
SQL.Cnx.Close();
return "OK";
}
catch (Exception Ex)
{
return "ERRO: " + Ex.Message;
}
}
private bool Down11X5(DownParameter param, SQLServer.D11X5.DwNumberBiz biz, DateTime currentDate)
{
int intDate = int.Parse(currentDate.ToString("yyyyMMdd"));
string url = string.Format(param.Category.DownUrl, intDate, "&");
try
{
string htmlText = Regex.Match(this._webClient.DownloadString(url), "<tbody id=\"row_show\">.*</tbody>",
RegexOptions.Singleline | RegexOptions.IgnoreCase).Value;
MatchCollection matchs = Regex.Matches(htmlText, "<tr>.*?</tr>", RegexOptions.Singleline | RegexOptions.IgnoreCase);
if (matchs.Count == 0)
{
Logger.Instance.Write(string.Format("name:{0},url:{1},msg:{2}", param.Category.Name, url, "count=0"));
return false;
}
long lastP = biz.GetLatestPeroid();
//HashSet<long> pSet = biz.GetPeroidsByDate(currentDate);
for (int i = matchs.Count - 1; i >= 0; i--)
{
Match match = matchs[i];
string peroid = Regex.Match(match.Value, "<td width=\"25%\">(\\d+)期</td>",
RegexOptions.IgnoreCase | RegexOptions.Singleline).Groups[1].Value.Trim();
string datetime = Regex.Match(match.Value, "<td width=\"25%\">(\\d{4}-\\d{2}-\\d{2}.*?)</td>",
RegexOptions.IgnoreCase | RegexOptions.Singleline).Groups[1].Value.Trim();
string code = Regex.Match(match.Value, @"[<spans >|<span >](\d{2},\d{2},\d{2},\d{2},\d{2})[</spans>|<spans>]",
RegexOptions.IgnoreCase | RegexOptions.Singleline).Groups[1].Value.Trim();
if (string.IsNullOrEmpty(code) || code.Trim().Length == 0) continue;
int p = int.Parse(peroid);
//把期号统一成{yyyymmddnn}
if (p < 2000000000) p += 2000000000;
//if (pSet.Contains(p)) continue;
if (p <= lastP) continue;
int n = int.Parse(peroid.Substring(peroid.Length - 2));
if (!biz.Add(p, n, code, intDate, datetime)) return false;
}
return true;
}
catch (Exception ex)
{
Logger.Instance.Write(string.Format("name:{0},url:{1},msg:{2}", param.Category.Name, url, ex.Message));
return false;
}
}
private bool Down3D(DownParameter param, SQLServer.D3.DwNumberBiz biz, DateTime currentDate)
{
string url = param.Category.DownUrl;
try
{
string htmlText = Regex.Match(this._webClient.DownloadString(url), "<div id=\"conKjlist\" class=\"kjlist\">.*?</table>",
RegexOptions.Singleline | RegexOptions.IgnoreCase).Value;
MatchCollection matchs = Regex.Matches(htmlText, "<tr>.*?</tr>", RegexOptions.Singleline | RegexOptions.IgnoreCase);
if (matchs.Count == 0)
{
Logger.Instance.Write(string.Format("name:{0},url:{1},msg:{2}", param.Category.Name, url, "count=0"));
return false;
}
long lastP = biz.GetLatestPeroid();
//HashSet<long> pSet = biz.GetPeroidsByDate(currentDate);
for (int i = matchs.Count - 1; i >= 0; i--)
{
Match match = matchs[i];
string peroid = Regex.Match(match.Value, "<td>(\\d{7})</td>", RegexOptions.IgnoreCase | RegexOptions.Singleline).Groups[1].Value.Trim();
string datetime = Regex.Match(match.Value, "<td>(\\d{4}-\\d{2}-\\d{2})</td>", RegexOptions.IgnoreCase | RegexOptions.Singleline).Groups[1].Value.Trim();
string code = Regex.Match(match.Value, "<div class=\"aball\">.*?</div>", RegexOptions.IgnoreCase | RegexOptions.Singleline).Value;
code = Regex.Replace(code, "<.*?>", "");
code = string.Join(",", code.ToArray());
if (string.IsNullOrEmpty(code) || code.Trim().Length == 0) continue;
int intDate = int.Parse(datetime.Replace("-", ""));
int p = int.Parse(peroid);
//把期号统一成{yyyynnn}
if (p < 2000000) p += 2000000;
//if (pSet.Contains(p)) continue;
if (p <= lastP) continue;
int n = int.Parse(peroid.Substring(peroid.Length - 3));
if (!biz.Add(p, n, code, intDate, datetime)) return false;
}
return true;
}
catch (Exception ex)
{
Logger.Instance.Write(string.Format("name:{0},url:{1},msg:{2}", param.Category.Name, url, ex.Message));
return false;
}
}
//Lista SubCat
public List<SubCat> ListaSubCat(string categoria)
{
List<SubCat> ListaSubCat = new List<SubCat>();
String Qry;
SQLServer SQL = new SQLServer();
SQL.ConectaDB();
Qry = "select codCategoria, nomeSubCategoria from tblSubCategoria where codCategoria = '" + categoria +
"' group by codCategoria, nomeSubCategoria order by codCategoria, nomeSubCategoria";
SqlDataReader Rs = SQL.DR(Qry);
while (Rs.Read())
{
SubCat SubCatego = new SubCat();
SubCatego.SubCatego = Rs["nomeSubCategoria"].ToString();
SubCatego.idSubCat = Convert.ToInt32(Rs["codCategoria"].ToString());
ListaSubCat.Add(SubCatego);
}
SQL.Cnx.Close();
return ListaSubCat;
}
private bool Open()
{
try
{
string strIP = GetConfigValue("Server").ToString();
string uid = GetConfigValue("uid").ToString();
string pwd = GetConfigValue("pwd").ToString();
if (svr == null)
{
svr = new SQLServerClass();
// svr.Connect(".", "sa", "sa");
svr.Connect(strIP, uid, pwd);
}
else
{
svr.Close();
svr = new SQLServerClass();
// svr.Connect(".", "sa", "sa");
svr.Connect(strIP, uid, pwd);
}
}
catch (System.Runtime.InteropServices.COMException ce)
{
if (ce.ErrorCode == -2147203048)
{
ErrorMessage(2023048, ce.StackTrace, "[DataBaseManage:Open]", ce.Message);
return false;
}
//不存在或访问被拒绝
if (ce.ErrorCode == -2147221504)
{
ErrorMessage(2021504, ce.StackTrace, "[DataBaseManage:Open]", ce.Message);
return false;
}
}
return true;
}
//Lista Descrição
public List<Descricao> ListaDescricao(string subcat)
{
List<Descricao> ListaDescr = new List<Descricao>();
String Qry;
SQLServer SQL = new SQLServer();
SQL.ConectaDB();
Qry = "select codSubCategoria, nomeSubCategoria, detalheSubCategoria from tblSubCategoria where nomeSubCategoria = '" + subcat + "'";
SqlDataReader Rs = SQL.DR(Qry);
while (Rs.Read())
{
Descricao Desc = new Descricao();
Desc.Descri = Rs["detalheSubCategoria"].ToString();
Desc.idDescricao = Rs["codSubCategoria"].ToString();
ListaDescr.Add(Desc);
}
SQL.Cnx.Close();
return ListaDescr;
}
public string InsereCadastro(string Nome, string SobreNome,
string Cpf, string Tel, string Cel,
string Email, string Senha, string ConfirmSenha)
{
try
{
String Qry = "";
SQLServer SQL = new SQLServer();
SQL.ConectaDB();
Qry = "Insert into tblUsuario " +
"( emailUsuario, senhaUsuario, nomeUsuario, sobrenomeUsuario, telFixo, telCelular, cpf) " +
"values ('" + Email + "','"
+ Senha + "','" + Nome + "','"
+ SobreNome + "','" + Tel + "','" + Cel + "','" + Cpf + "')";
SQL.ExecQry(Qry);
SQL.Cnx.Close();
return "CADASTRO REALIZADO COM SUCESSO.";
}
catch (Exception Ex)
{
return "ERRO: " + Ex.Message;
}
}
/// <summary>
/// 还原数据库
/// </summary>
/// <param name="serverName">数据实例名</param>
/// <param name="userName">用户</param>
/// <param name="password">密码</param>
/// <param name="databaseName">库名</param>
/// <param name="path">文件路径</param>
public static void RestoreDatabase(string serverName, string userName, string password, string databaseName, string path)
{
SQLDMO.Restore oRestore = new SQLDMO.Restore();
SQLServer oSQLServer = new SQLServer();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(serverName, userName, password);
oRestore.Action = SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = databaseName;
oRestore.Files = path;
oRestore.FileNumber = path.Split(',').Count();
oRestore.BackupSetName = databaseName;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
}
catch
{
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
public List<Uf> ListaUF()
{
List<Uf> ListaEstados = new List<Uf>();
String Qry;
SQLServer SQL = new SQLServer();
string retornoCnn = SQL.ConectaDB();
if (retornoCnn.Contains("ERRO") == true)
{
Erro = retornoCnn;
return ListaEstados;
}
Qry = "select * from tblEstado order by nomeEstado";
SqlDataReader Rs = SQL.DR(Qry);
while (Rs.Read())
{
Uf Estado = new Uf();
Estado.UF = Rs["nomeEstado"].ToString();
Estado.idUF = Convert.ToInt32(Rs["codEstado"].ToString());
ListaEstados.Add(Estado);
}
SQL.Cnx.Close();
return ListaEstados;
}
private void cbbDb_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
if (cbbDb.SelectedItem + "" == STR_FIND)
{
var a = ConnectionStringSetting.ConnectionString.Replace("xBXEv1", "master");
var db = new SQLServer(a);
var tbl = db.GetDatabases();
cbbDb.Properties.Items.Remove(STR_FIND);
cbbDb.Properties.Items.Clear();
foreach (var dtr in tbl)
cbbDb.Properties.Items.Add(dtr);
cbbDb.Properties.Items.Add(STR_FIND);
if (cbbDb.Properties.Items.Count > 1) cbbDb.SelectedIndex = 0;
else cbbDb.SelectedIndex = -1;
}
}
catch { return; }
}
public string RecuperarSenha(String email, RecSenha ModelRecSenha)
{
String Qry;
EmailService e_mail = new EmailService();
SQLServer SQL = new SQLServer();
SQL.ConectaDB();
Qry = "select senhaUsuario from tblUsuario where emailUsuario = '" + email + "'";
SqlDataReader Rs = SQL.DR(Qry);
//encontrou o e-mail
if (Rs.Read() == true)
{
ModelRecSenha.Senha = Rs["senhaUsuario"].ToString();
bool retorno = SendSenha("*****@*****.**", email, "Sua senha", "AchadosPerdidos\n\nSua senha é: " + ModelRecSenha.Senha);
// // Gmail Remetente
//var fromAddress = "*****@*****.**";
//// Email destino
//var toAddress = "*****@*****.**";
////Senha do e-mail remetente
//const string fromPassword = "******";
//// Assunto
//string subject = Assunto;
//// Corpo do e-mail
//string body = "De: " + Nome + "\n";
//body += "Email: " + Email + "\n";
//body += "Assunto: " + Assunto + "\n";
//body += "Mensagem: \n" + Msg + "\n";
//// smtp settings
//var smtp = new System.Net.Mail.SmtpClient();
//{
// smtp.Host = "smtp.gmail.com";
// smtp.Port = 587;
// smtp.EnableSsl = true;
// smtp.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
// smtp.Credentials = new NetworkCredential(fromAddress, fromPassword);
// smtp.Timeout = 20000;
//}
//// Realiza os envios.
//smtp.Send(fromAddress, toAddress, subject, body);
//retorno = e_mail.Send()
if (retorno == false)
{
ModelRecSenha.Result = "Não foi possível enviar a senha para o e-mail informado, tente novamente.";
}
else
{
ModelRecSenha.Result = "Sua senha foi enviada para o e-mail informado.";
}
}
return "Ok";
}
//Tạo SQLDMO database
/// <summary>
/// Huỷ kết nối trước đó
/// </summary>
private void disconnectDB()
{
if (sqlSrv != null )
sqlSrv =null;
sqlSrv =new SQLDMO.SQLServerClass();
}
/// <summary>
/// Them backup device vao sqlserver, neu da ton tai thi xoa truoc khi them.
/// </summary>
/// <param name="server"></param>
/// <param name="bkdv"></param>
/// <param name="bkdvPhysical">Chuoi ten file vat ly cua backup device dinh them</param>
private void addBackupdevice(SQLServer server,BackupDevice bkdv, string bkdvPhysical)
{
for(int i = 0;i<server.BackupDevices.Count ;i++)
{
string backupname=sqlSrv.BackupDevices.Item(i+1).PhysicalLocation ;
if (backupname==dlgSaveBackUp.FileName)
server.BackupDevices.Remove(i+1) ;
}
server.BackupDevices.Add(bkdv);
}
public bool ConsultaCpf(string cpf)
{
String Qry;
SQLServer SQL = new SQLServer();
string retornoCnn = SQL.ConectaDB();
if (retornoCnn.Contains("ERRO") == true)
{
return false;
}
Qry = "select * from tblUsuario where cpf = '" + cpf + "'";
SqlDataReader Rs = SQL.DR(Qry);
if (Rs.Read())
{
return false;
}
SQL.Cnx.Close();
return true;
}
public List<CamposItemMapa> ListaItemsMapa(string tipoItems)
{
List<CamposItemMapa> ListaItemsMapa = new List<CamposItemMapa>();
String Qry;
SQLServer SQL = new SQLServer();
string retornoCnn = SQL.ConectaDB();
if (retornoCnn.Contains("ERRO") == true)
{
Erro = retornoCnn;
return ListaItemsMapa;
}
String filtros1 = null;
String filtros2 = null;
//verifica se parâmetro contem filtro
if (tipoItems.Contains("-") == true)
{
String[] vSplit = tipoItems.Split('-');
filtros1 = vSplit[1];
filtros2 = vSplit[2];
}
if (tipoItems.Contains("Encontrados") == true)
{
Qry = "Select A.Latitude, A.Longitude, A.codItemPerdido, "
+ "D.emailUsuario, C.nomeCategoria, "
+ "B.nomeSubCategoria, D.nomeUsuario, D.sobrenomeUsuario, D.telFixo, D.telCelular"
+ " from tblItemPerdido as A inner Join tblSubCategoria as B"
+ " on (A.codSubCategoria = B.codSubCategoria) #filtros1 #filtros2"
+ " inner join tblCategoria as C on (a.codCategoria = c.codCategoria)"
+ " inner join tblUsuario as D on (a.idUsuario = d.idUsuario)";
if (filtros1 != "0" && filtros1 != null)
{
Qry = Qry.Replace("#filtros1", " and A.codCategoria = "
+ filtros1);
}
else {
Qry = Qry.Replace("#filtros1", "");
}
if (filtros2 != "0" && filtros2 != null)
{
Qry = Qry.Replace("#filtros2", " and A.codSubCategoria = "
+ filtros2);
}
else
{
Qry = Qry.Replace("#filtros2", "");
}
}
else
{
Qry = "Select A.codItemEncontrado, A.Latitude, A.Longitude, D.emailUsuario, "
+ " C.nomeCategoria, B.nomeSubCategoria, D.nomeUsuario, D.sobrenomeUsuario, "
+ "D.telFixo, D.telCelular"
+ " from tblItemEncontrado as A inner Join tblSubCategoria as B"
+ " on (A.codSubCategoria = B.codSubCategoria) #filtros1 #filtros2"
+ " inner join tblCategoria as C on (a.codCategoria = c.codCategoria)"
+ " inner join tblUsuario as D on (a.idUsuario = d.idUsuario)";
if (filtros1 != "0" && filtros1 != null)
{
Qry = Qry.Replace("#filtros1", " and A.codCategoria = "
+ filtros1);
}
else
{
Qry = Qry.Replace("#filtros1", "");
}
if (filtros2 != "0" && filtros2 != null)
{
Qry = Qry.Replace("#filtros2", " and A.codSubCategoria = "
+ filtros2);
}
else
{
Qry = Qry.Replace("#filtros2", "");
}
}
SqlDataReader Rs = SQL.DR(Qry);
while (Rs.Read())
{
CamposItemMapa ItemsMapa = new CamposItemMapa();
if (tipoItems.Contains("Encontrados") == true)
{
ItemsMapa.idItem = Convert.ToInt32(Rs["codItemPerdido"].ToString());
}
else
{
ItemsMapa.idItem = Convert.ToInt32(Rs["codItemEncontrado"].ToString());
}
ItemsMapa.Categoria = Rs["nomeCategoria"].ToString();
ItemsMapa.SubCategoria = Rs["nomeSubCategoria"].ToString();
ItemsMapa.email = Rs["emailUsuario"].ToString();
ItemsMapa.Nome = Rs["nomeUsuario"].ToString();
ItemsMapa.Sobrenome = Rs["sobrenomeUsuario"].ToString();
ItemsMapa.Tel = Rs["telFixo"].ToString();
ItemsMapa.Celular = Rs["telCelular"].ToString();
ItemsMapa.email = Rs["emailUsuario"].ToString();
ItemsMapa.Latitude = Rs["longitude"].ToString().Replace(",",".");
ItemsMapa.Longitude = Rs["latitude"].ToString().Replace(",", ".");
ListaItemsMapa.Add(ItemsMapa);
}
SQL.Cnx.Close();
////********TRECHO DE TESTES - HABILITAR OS CODIGOS ACIMA QUANDO CONSTATADO QUE FUNCIONALIDADE ESTÁ OK.
//CamposItemMapa ItemsMapa = new CamposItemMapa();
//ItemsMapa.idItem = 1;
//ItemsMapa.Categoria = "Animal";
//ItemsMapa.email = "*****@*****.**";
//ItemsMapa.Latitude = "-46.70011240000002";
//ItemsMapa.Longitude = "-23.54984692299866";
//ListaItemsMapa.Add(ItemsMapa);
return ListaItemsMapa;
}
