private void CheckClientVer()
{
//检查DUMMY表，仅用于强行使用户不能使用旧版本的客户端程序
/*System.Data.DataTable tbldummy = CustomClassCache.GetDataTableWithKey("select * from sys_dummy_1");*/
System.Data.DataTable tbldummy = CustomClassCache.GetDataTableWithKeyProxy("sys_dummy_1");
tbldummy.Dispose();
}
} // End Sub ExportDatabase
public override void ImportTableData()
{
string strBasePath = @"D:\Stefan.Steiger\Desktop\ValidationResults\SRGSSRReis_Export_sts";
System.Data.DataTable dtTableInOrder = new System.Data.DataTable();
dtTableInOrder.ReadXml(strBasePath = System.IO.Path.Combine(System.IO.Path.Combine(strBasePath, "DependencyOrder"), "Foreign_Key_Depencency_Order.xml"));
foreach (System.Data.DataRow dr in dtTableInOrder.Rows)
{
string strDestTable = System.Convert.ToString(dr["TableName"]);
System.Console.WriteLine(strDestTable);
string strPath = System.IO.Path.Combine(strBasePath, "Tables");
strPath = System.IO.Path.Combine(strPath, strDestTable + ".xml");
System.Console.WriteLine(strPath);
System.Data.DataTable dt = new System.Data.DataTable();
dt.ReadXml(strPath);
BulkCopy(strDestTable, dt);
dt.Clear();
dt.Dispose();
} // Next dr
dtTableInOrder.Clear();
dtTableInOrder.Dispose();
} // End Sub ImportTableData
private void ctpOrderImport_Load(object sender, EventArgs e)
{
System.Data.DataTable dt = new System.Data.DataTable();
string consqlserver = ConfigurationManager.ConnectionStrings["HGWHConnectionString"].ToString() + ";Password=HEGII;";
string sql = "SELECT OrderType FROM OrderTypeList";
SqlConnection con = new SqlConnection(consqlserver);
SqlDataAdapter da = new SqlDataAdapter(sql, con);
try
{
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
comboOldOrderType.Items.Add(dt.Rows[i][0].ToString());
}
}
catch (Exception msg)
{
MessageBox.Show(msg.Message);
}
finally
{
con.Close();
con.Dispose();
da.Dispose();
dt.Dispose();
}
}
/// <summary>
/// Checa se houve uma entrada em estoque para o intervalo.
/// </summary>
public static Boolean ExisteIntervalo(int numeroInicio, int numeroFinal, Object operadoraId, Int32 qtdZerosEsquerda, String letraTematica)
{
String query = "SELECT almox_movimentacao_id FROM almox_movimentacao INNER JOIN almox_produto ON almox_movimentacao_produtoId = almox_produto_id WHERE almox_movimentacao_tipo=0 AND almox_movimentacao_numDe <= " + numeroInicio.ToString() + " AND almox_movimentacao_numAte >= " + numeroFinal.ToString();
if (operadoraId != null)
{
query += " AND almox_produto_operadoraId=" + operadoraId;
}
if (!String.IsNullOrEmpty(letraTematica))
{
query += " AND almox_movimentacao_letra='" + letraTematica + "'";
}
if (qtdZerosEsquerda > 0)
{
query += " AND almox_movimentacao_zerosAEsquerda=" + qtdZerosEsquerda;
}
System.Data.DataTable dt = LocatorHelper.Instance.ExecuteQuery(query, "resultset").Tables[0];
Boolean result = dt != null && dt.Rows.Count > 0;
if (dt != null)
{
dt.Dispose();
}
return(result);
}
/// <summary>
/// Checa se um determinado intervado de produtos numerados foi retirado do estoque.
/// True = INTERVALO NÃO ESTÁ NO ESTOQUE, FOI RETIRADO
/// </summary>
public static Boolean ExisteIntervaloRetirado(int numeroInicio, int numeroFinal)
{
String query = "SELECT almox_movimentacao_id, almox_movimentacao_data FROM almox_movimentacao WHERE almox_movimentacao_tipo=1 and almox_movimentacao_numDe <= " + numeroInicio.ToString() + " AND almox_movimentacao_numAte >= " + numeroFinal.ToString() + " ORDER BY almox_movimentacao_data DESC";
System.Data.DataTable dtSaida = LocatorHelper.Instance.ExecuteQuery(query, "resultset").Tables[0];
if (dtSaida == null || dtSaida.Rows.Count == 0)
{
return(false);
} //nao consta SAÍDA deste intervalo no estoque
query = "SELECT almox_movimentacao_id, almox_movimentacao_data FROM almox_movimentacao WHERE almox_movimentacao_tipo=0 and almox_movimentacao_numDe <= " + numeroInicio.ToString() + " AND almox_movimentacao_numAte >= " + numeroFinal.ToString() + " ORDER BY almox_movimentacao_data DESC";
System.Data.DataTable dtEntrada = LocatorHelper.Instance.ExecuteQuery(query, "resultset2").Tables[0];
if (dtEntrada == null || dtEntrada.Rows.Count == 0)
{
return(false);
} //nao consta ENTRADA deste intervalo no estoque
//para estar fora do estoque, a data da última saída deve ser superior à data da última entrada
DateTime dataSaida = Convert.ToDateTime(dtSaida.Rows[0]["almox_movimentacao_data"]);
DateTime dataEntrada = Convert.ToDateTime(dtEntrada.Rows[0]["almox_movimentacao_data"]);
Boolean result = dataSaida > dataEntrada;
dtSaida.Dispose();
dtEntrada.Dispose();
return(result);
}
private int checkOrder(int intDataRow, int intCustInfoID)
{
Worksheet ActiveSheet = Globals.ThisAddIn.Application.ActiveSheet;
System.Data.DataTable dt = new System.Data.DataTable();
string consqlserver = ConfigurationManager.ConnectionStrings["HGWHConnectionString"].ToString() + ";Password=HEGII;";
string sql = "EXEC progGetOrder " + intCustInfoID.ToString();
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("报装").Column].Value == null)
{
sql = sql + ", NULL";
}
else
{
if (DateTime.TryParse(ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("报装").Column].Value.ToString(), out DateTime dtApplyDate))
{
sql = sql + ", '" + dtApplyDate.ToString() + "'";
}
else
{
sql = sql + ", NULL";
}
}
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("预约").Column].Value == null)
{
sql = sql + ", NULL";
}
else
{
sql = sql + ", '" + ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("预约").Column].Value.ToString() + "'";
}
sql = sql + ", '" + comboOldOrderType.Text + "'";
SqlConnection con = new SqlConnection(consqlserver);
SqlDataAdapter da = new SqlDataAdapter(sql, con);
try
{
da.Fill(dt);
switch (dt.Rows.Count)
{
case 0:
return(0);
default:
return(-1 - dt.Rows.Count);
}
}
catch (Exception msg)
{
MessageBox.Show(msg.Message);
return(-1);
}
finally
{
con.Close();
con.Dispose();
da.Dispose();
dt.Dispose();
}
}
protected void RecuperarDados()
{
try
{
string comando = "SELECT Codigo, Nome, Descricao,Data FROM Pedido WHERE Status='Aguardando Cotacao' ORDER BY Codigo ASC";
//DATATABLE
System.Data.DataTable tb = new System.Data.DataTable();
//CONEXAO AO BANCO DE DADOS, ENVIO DOS COMANDOS SQL
AppDatabase.OleDBTransaction db = new AppDatabase.OleDBTransaction();
db.ConnectionString = conexao;
tb = (System.Data.DataTable)db.Query(comando);
Pedido.DataSource = tb;
Pedido.DataBind();
tb.Dispose();
Pesquisa.Text = "";
Limpar.Visible = false;
Limpar_Busca.Visible = false;
Responder.Visible = false;
EnviarMsg.Visible = false;
editor.Visible = false;
ControleCotacao.Visible = false;
this.Master.MasterForm = false;
}
catch (Exception ex)
{
Erro.Text = "Houve um erro ao carregar os pedidos";
RecoverExceptions re = new RecoverExceptions();
re.SaveException(ex);
}
}
/// <summary>
/// Checa se um intervalo de contratos foi usando em estoque.
/// TODO: Acrescentar na checagem a qtd de zeros e a letra temática
/// </summary>
public static Boolean UsadoEmContrato(int numeroInicio, int numeroFinal)
{
String query = "SELECT contrato_id FROM contrato WHERE contrato_numero IN(";
String inclausule = "";
for (int i = numeroInicio; i <= numeroFinal; i++)
{
if (inclausule.Length > 0)
{
inclausule += ",";
}
inclausule += "'" + i.ToString() + "'";
}
query += inclausule + ")";
System.Data.DataTable dt = LocatorHelper.Instance.ExecuteQuery(query, "resultset").Tables[0];
Boolean result = dt != null && dt.Rows.Count > 0;
if (dt != null)
{
dt.Dispose();
}
return(result);
}
public void cpyApexSwat()
{
var SWATF = new System.Data.DataTable();
SWATF = CEEOT_dll.AccessDB.getDBDataTable("SELECT * FROM SwatApexF WHERE Version='" + CEEOT_dll.Initial.Version + "'");
for (int i = 0; i < SWATF.Rows.Count - 1; i++)
{
var name1 = CEEOT_dll.Initial.OrgDir + "\\" + SWATF.Rows[i]["File"];
var fileo = CEEOT_dll.Initial.OrgDir + "\\" + SWATF.Rows[i]["File"];
string filet, filet1;
if (((String)SWATF.Rows[i]["file"]).ToCharArray()[0] == '*')
{
filet = CEEOT_dll.Initial.Output_files + "\\";
filet1 = CEEOT_dll.Initial.Swat_Output + "\\";
}
else
{
filet = CEEOT_dll.Initial.Output_files + "\\" + SWATF.Rows[i]["File"];
filet1 = CEEOT_dll.Initial.Swat_Output + "\\" + SWATF.Rows[i]["File"];
}
System.IO.File.Copy(fileo, filet, true);
System.IO.File.Copy(fileo, filet1, true);
}
SWATF.Dispose();
}
private void button3_Click_1(object sender, EventArgs e)
{
System.Data.DataTable dataTable = new System.Data.DataTable("NULL");
Grid_FoldersData.DataSource = dataTable;
dataTable.Dispose();
Grid_FoldersData.DataSource = null;
}
/// Generic method to return nullable type values.
/// https://stackoverflow.com/questions/17363937/generic-method-to-return-nullable-type-values
//public static T? GetValue<T>(DataRow dr, string fieldName) where T : struct
//{
// //if (CheckDbNull(dr, fieldName)) return null;
// //return (T?)dr[fieldName];
// return (T?)GetObject(dr, fieldName);
//}
//public static int? GetInt(DataRow dr, string fieldName)
//{
// //if (int.TryParse(GetString(dr, fieldName), out int i)) return i;
// //return vDefault;
// if (CheckDbNull(dr, fieldName)) return null;
// return (int)dr[fieldName];
//}
public static void DisposeDataTable(ref System.Data.DataTable dt)
{
if (dt != null)
{
dt.Dispose(); dt = null;
}
}
private int insertCustInfo(int intDataRow)
{
Worksheet ActiveSheet = Globals.ThisAddIn.Application.ActiveSheet;
System.Data.DataTable dt = new System.Data.DataTable();
string consqlserver = ConfigurationManager.ConnectionStrings["HGWHConnectionString"].ToString() + ";Password=HEGII;";
string sql = "EXEC progInsertCustInfo '" + ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("地址").Column].Value + "', '" +
ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("电话").Column].Value + "'";
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("用户名称").Column].Value == null)
{
sql = sql + ", NULL";
}
else
{
sql = sql + ", '" + ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("用户名称").Column].Value + "'";
}
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("大范围").Column].Value == null)
{
sql = sql + ", NULL";
}
else
{
sql = sql + ", '" + ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("大范围").Column].Value + "'";
}
SqlConnection con = new SqlConnection(consqlserver);
SqlDataAdapter da = new SqlDataAdapter(sql, con);
try
{
da.Fill(dt);
if (dt.Rows.Count > 0)
{
return((int)dt.Rows[0][0]);
}
else
{
return(0);
}
}
catch (Exception msg)
{
MessageBox.Show(msg.Message);
return(-1);
}
finally
{
con.Close();
con.Dispose();
da.Dispose();
dt.Dispose();
}
}
/// <summary>
/// 数据绑定
/// </summary>
private void BindData()
{
try
{
string sqlColumns = " ROW_NUMBER() OVER(ORDER BY pe.id) AS RowNum,pe.id ID,us.Name Name,ed.Name WorkPlace,eui.Position Position,epi.ExamName ExamName,es.SubjectName SubjectName, pe.Score Score,pe.ExamState ExamState ";
string sqlEnd = sql;
//加载查询条件
string textSearch = ttbSearchMessage.Text;
if (!string.IsNullOrEmpty(textSearch))
{
if (sql_inj(textSearch))
{
//存在SQL注入风险
}
else
{
sqlEnd = sql + " and( us.Name like '" + textSearch + "' or ed.Name like '" + textSearch + "' or eui.Position like '" + textSearch + "' or epi.ExamName like '" + textSearch + "' or es.SubjectName like '" + textSearch + "')";
}
}
//数据量 条数
int count = Helpers.DbHelperSQL.Query("select pe.id " + sqlEnd).Tables[0].Rows.Count;
//排序判断
if (Grid1.SortDirection == "ASC")
{
sqlEnd += " order by " + Grid1.SortField + " ASC";
}
else
{
sqlEnd += " order by " + Grid1.SortField + " desc";
}
sqlEnd = "select " + sqlColumns + sqlEnd;
//数据加载及绑定
System.Data.DataSet ds = Helpers.DbHelperSQL.Query(sqlEnd);
System.Data.DataTable data = ds.Tables[0];
Grid1.RecordCount = count;
int pageIndexMax = (Grid1.PageIndex + 1) * Grid1.PageSize;
int pageIndexmin = pageIndexMax - Grid1.PageSize;
Grid1.DataSource = data.Select("" + pageIndexmin + " < RowNum and RowNum <=" + pageIndexMax + "");
Grid1.DataBind();
ds.Dispose();
data.Dispose();
}
catch (Exception ex)
{
throw ex;
}
}
//<Snippet28>
void UpdateDB()
{
System.Data.DataTable DeletedChildRecords =
dsNorthwind1.Orders.GetChanges(System.Data.DataRowState.Deleted);
System.Data.DataTable NewChildRecords =
dsNorthwind1.Orders.GetChanges(System.Data.DataRowState.Added);
System.Data.DataTable ModifiedChildRecords =
dsNorthwind1.Orders.GetChanges(System.Data.DataRowState.Modified);
try
{
if (DeletedChildRecords != null)
{
daOrders.Update(DeletedChildRecords);
}
if (NewChildRecords != null)
{
daOrders.Update(NewChildRecords);
}
if (ModifiedChildRecords != null)
{
daOrders.Update(ModifiedChildRecords);
}
dsNorthwind1.AcceptChanges();
}
catch (Exception ex)
{
// Update error, resolve and try again
}
finally
{
if (DeletedChildRecords != null)
{
DeletedChildRecords.Dispose();
}
if (NewChildRecords != null)
{
NewChildRecords.Dispose();
}
if (ModifiedChildRecords != null)
{
ModifiedChildRecords.Dispose();
}
}
}
protected void Buscar_Click(object sender, EventArgs e)
{
string comando = "SELECT Codigo,Nome,Endereco FROM Computadores WHERE Nome+' '+Endereco LIKE '%" + BuscarComputador.Text + "%'ORDER BY Nome;";
AppDatabase.OleDBTransaction db = new AppDatabase.OleDBTransaction();
db.ConnectionString = conexao;
System.Data.DataTable tb = (System.Data.DataTable)db.Query(comando);
Computadores.DataSource = tb;
Computadores.DataBind();
tb.Dispose();
CancelarBuscar.Visible = true;
}
private void CleanUp()
{
for (int i = 0; i < this.DataTableCollection.Count - 1; i++)
{
System.Data.DataTable table = this.DataTableCollection[i];
if (table != null)
{
table.Dispose();
if (table != null)
{
table = null;
}
}
}
}
/// <summary>
/// Checa se houve uma entrada em estoque para o intervalo.
/// </summary>
public static Boolean ExisteIntervalo(int numeroInicio, int numeroFinal)
{
String query = "SELECT almox_movimentacao_id FROM almox_movimentacao WHERE almox_movimentacao_tipo=0 AND almox_movimentacao_numDe <= " + numeroInicio.ToString() + " AND almox_movimentacao_numAte >= " + numeroFinal.ToString();
System.Data.DataTable dt = LocatorHelper.Instance.ExecuteQuery(query, "resultset").Tables[0];
Boolean result = dt != null && dt.Rows.Count > 0;
if (dt != null)
{
dt.Dispose();
}
return(result);
}
} // End Function IsTableEmpty
public override bool TableHasColumn(string strTableName, string strColumnName)
{
System.Data.DataTable dt = GetColumnNamesForTable(strTableName);
int i = dt.Select("ColumnName like '" + strColumnName + "'").Length;
dt.Clear();
dt.Dispose();
if (i > 0)
{
return(true);
}
return(false);
} // End Function TableHasColumn
// EXIBE OS DADOS NO GRIDVIEW
protected void ExibirComputadores()
{
string comando = "SELECT Codigo,Nome,Endereco FROM Computadores ORDER BY Nome;";
AppDatabase.OleDBTransaction db = new AppDatabase.OleDBTransaction();
db.ConnectionString = conexao;
System.Data.DataTable tb = new System.Data.DataTable();
tb = (System.Data.DataTable)db.Query(comando);
Computadores.DataSource = tb;
Computadores.DataBind();
tb.Dispose();
}
private void OpenExcel2(bool isOpenXMLFormat)
{
//open the excel file using OLEDB
OleDbConnection con;
if (isOpenXMLFormat)
{
//read a 2007 file
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
}
else
{
//read a 97-2003 file
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileName + ";Extended Properties=Excel 8.0;";
}
con = new OleDbConnection(connectionString);
con.Open();
//get all the available sheets
System.Data.DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//get the number of sheets in the file
workSheetNames = new String[dataSet.Rows.Count];
int i = 0;
foreach (DataRow row in dataSet.Rows)
{
//insert the sheet's name in the current element of the array
//and remove the $ sign at the end
workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { '$' });
i++;
}
if (con != null)
{
con.Close();
con.Dispose();
}
if (dataSet != null)
{
dataSet.Dispose();
}
}
public void Close()
{
this.CloseFileStream();
_recordBuffer = null;
_dbfHeader = null;
_dbfFields = null;
_isFileOpened = false;
_fieldCount = 0;
//---add by miao 2015-07-28-----
if (_dbfTable != null)
{
_dbfTable.Dispose();
_dbfTable = null;
}
}
/// <summary>
/// DataTable拆包
/// </summary>
/// <param name="builder">数据对象拆包器</param>
/// <returns></returns>
internal System.Data.DataTable Get(DataReader builder)
{
System.Data.DataTable table = new System.Data.DataTable(name);
if (rowCount != 0)
{
bool isGet = false;
try
{
get(table, builder);
isGet = true;
}
finally
{
if (!isGet)
{
table.Dispose();
table = null;
}
}
}
return(table);
}
protected void Computadores_SelectedIndexChanged(object sender, EventArgs e)
{
// coloca o valor do código no controle de formulário "Codigo"
Codigo.Text = Computadores.SelectedRow.Cells[1].Text;
string comando = "SELECT * FROM Computadores WHERE Codigo=" + Codigo.Text;
AppDatabase.OleDBTransaction db = new AppDatabase.OleDBTransaction();
db.ConnectionString = conexao;
System.Data.DataTable tb = (System.Data.DataTable)db.Query(comando);
if (tb.Rows.Count == 1)
{
Nome.Text = tb.Rows[0]["Nome"].ToString();
Endereco.Text = tb.Rows[0]["Endereco"].ToString();
Anotacoes.Text = tb.Rows[0]["Anotacoes"].ToString();
tb.Dispose();
Excluir.Visible = true;
Cancelar.Visible = true;
}
}
// EXIBE OS DADOS NO GRIDVIEW
protected void ExibirComputadores()
{
string comando = "SELECT Codigo,Nome,Endereco FROM Computadores WHERE Nome+' '+Endereco LIKE '%" + BuscarComputador.Text + "%' ORDER BY Nome;";
AppDatabase.OleDBTransaction db = new AppDatabase.OleDBTransaction();
db.ConnectionString = conexao;
System.Data.DataTable tb = new System.Data.DataTable();
tb = (System.Data.DataTable)db.Query(comando);
Computadores.DataSource = tb;
Computadores.DataBind();
tb.Dispose();
Codigo.Text = "";
Nome.Text = "";
Endereco.Text = "";
Anotacoes.Text = "";
Excluir.Visible = false;
Cancelar.Visible = false;
}
private int checkCustInfo(string strCustAddress, string strCustPhone, int intDataRow)
{
Worksheet ActiveSheet = Globals.ThisAddIn.Application.ActiveSheet;
System.Data.DataTable dt = new System.Data.DataTable();
string consqlserver = ConfigurationManager.ConnectionStrings["HGWHConnectionString"].ToString() + ";Password=HEGII;";
string sql = "EXEC progGetCustInfo '" + ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("地址").Column].Value + "', '" +
ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("电话").Column].Value + "'";
SqlConnection con = new SqlConnection(consqlserver);
SqlDataAdapter da = new SqlDataAdapter(sql, con);
try
{
da.Fill(dt);
switch (dt.Rows.Count)
{
case 0:
return(0); //没有找到匹配的客户信息，返回0
case 1:
return((int)dt.Rows[0]["ID"]); //找到一条匹配的信息，返回客户信息ID
default:
return(0 - dt.Rows.Count); //找到多条匹配的信息，以负数形式返回重复的信息数量
}
}
catch (Exception msg)
{
MessageBox.Show(msg.Message);
return(-1); //发生错误返回-1
}
finally
{
con.Close();
con.Dispose();
da.Dispose();
dt.Dispose();
}
}
/// <summary>
/// 根据设置DropDownList下拉选项值和文本
/// </summary>
/// <param name="o">下拉列表控件</param>
/// <param name="d">数据源datatable</param>
/// <param name="key">datatable里对应listitem控件key的列</param>
/// <param name="val">datatable里对应listitem控件value的列</param>
/// <param name="defaulText">添加默认下拉选项</param>
public static void SetDataTableDropDownList(this DropDownList o, System.Data.DataTable d, string key, string val, string defaulText)
{
if (!string.IsNullOrEmpty(defaulText))
{
o.Items.Add(new ListItem
{
Value = "",
Text = defaulText
});
}
if (d != null && d.Rows.Count > 0)
{
foreach (System.Data.DataRow item in d.Rows)
{
o.Items.Add(new System.Web.UI.WebControls.ListItem()
{
Text = item[val].ToString(),
Value = item[key].ToString()
});
}
}
d.Dispose();
}
public void GetTasks()
{
try
{
SqlConnection con = new SqlConnection(Properties.Settings.Default.SkoLifeDBConnection);
SqlCommand cmd = new SqlCommand(
"select d.sms_msg_detail_id, d.sms_msg, d.sms_target_number, g.modem_ip, g.modem_user, g.modem_pass from sms_message s inner join sms_message_detail d " +
"on s.sms_msg_id = d.sms_msg_id inner join sms_config g on s.sms_config_id = g.sms_config_id where s.sms_status = 2 AND d.sms_status = 2 order by d.sms_msg_detail_id"
, con)
{
CommandTimeout = int.MaxValue
};
SqlDataAdapter adp = new SqlDataAdapter(cmd);
System.Data.DataTable dt = new System.Data.DataTable();
adp.Fill(dt);
foreach (System.Data.DataRow row in dt.Rows)
{
Task tsk = new Task(
Convert.ToInt32(row["sms_msg_detail_id"]),
row["sms_msg"].ToString(),
row["sms_target_number"].ToString(),
row["modem_ip"].ToString(),
row["modem_user"].ToString(),
row["modem_pass"].ToString()
);
Tasks.Enqueue(tsk);
}
dt.Dispose(); con.Close(); con.Dispose(); cmd.Dispose(); adp.Dispose();
}
catch (Exception ex)
{
LogsManager.DefaultInstance.LogMsg(LogsManager.LogType.Error, ex.Message, typeof(TaskManager));
}
}
/// <summary>
/// 将数据表转换成JSON类型串
/// </summary>
/// <param name="dt">要转换的数据表</param>
/// <param name="dispose">数据表转换结束后是否dispose掉</param>
/// <returns></returns>
public static StringBuilder DataTableToJson(System.Data.DataTable dt, bool dt_dispose)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("[\r\n");
//数据表字段名和类型数组
string[] dt_field = new string[dt.Columns.Count];
int i = 0;
string formatStr = "{{";
string fieldtype = "";
foreach (System.Data.DataColumn dc in dt.Columns)
{
dt_field[i] = dc.Caption.ToLower().Trim();
formatStr += "'" + dc.Caption.ToLower().Trim() + "':";
fieldtype = dc.DataType.ToString().Trim().ToLower();
if (fieldtype.IndexOf("int") > 0 || fieldtype.IndexOf("deci") > 0 ||
fieldtype.IndexOf("floa") > 0 || fieldtype.IndexOf("doub") > 0 ||
fieldtype.IndexOf("bool") > 0)
{
formatStr += "{" + i + "}";
}
else
{
formatStr += "'{" + i + "}'";
}
formatStr += ",";
i++;
}
if (formatStr.EndsWith(","))
{
formatStr = formatStr.Substring(0, formatStr.Length - 1);//去掉尾部","号
}
formatStr += "}},";
i = 0;
object[] objectArray = new object[dt_field.Length];
foreach (System.Data.DataRow dr in dt.Rows)
{
foreach (string fieldname in dt_field)
{ //对 \ , ' 符号进行转换
objectArray[i] = dr[dt_field[i]].ToString().Trim().Replace("\\", "\\\\").Replace("'", "\\'");
switch (objectArray[i].ToString())
{
case "True":
{
objectArray[i] = "true"; break;
}
case "False":
{
objectArray[i] = "false"; break;
}
default: break;
}
i++;
}
i = 0;
stringBuilder.Append(string.Format(formatStr, objectArray));
}
if (stringBuilder.ToString().EndsWith(","))
{
stringBuilder.Remove(stringBuilder.Length - 1, 1);//去掉尾部","号
}
if (dt_dispose)
{
dt.Dispose();
}
return(stringBuilder.Append("\r\n];"));
}
/// <summary>
/// Binds this entity instance to database data using current data of <paramref name="filterMembers">Filter Members</paramref>.
/// </summary>
/// <param name="lazyLoading">Defines weather lazy loading is enabled.</param>
/// <param name="filterMembers">Property members used to filter data.</param>
/// <param name="dataRow">A System.Data.DataRow to bind this instance. When this parameter is not set, Tenor return data from the database.</param>
protected virtual void Bind(bool lazyLoading, string baseFieldAlias, string[] filterMembers, System.Data.DataRow dataRow)
{
bool fromSearch = (dataRow != null);
//dataRow is null when Bind is called from LoadForeing or directly from user code.
//dataRow is not null when Bind is called from Search (see Search.cs)
if (!fromSearch && ClassMetadata.Cacheable && LoadFromCache())
{
//LoadFromCache returns true, the Bind was done.
//If not cacheable, never LoadFromCache (see Cache.cs)
return;
}
TableInfo table = TableInfo.CreateTableInfo(this.GetType());
ConnectionStringSettings connection = table.GetConnection();
if (!fromSearch)
{
//Retrieve data
SearchOptions so = new SearchOptions(this.GetType());
// SELECT and FILTERS
List <FieldInfo> filters = new List <FieldInfo>();
foreach (string s in filterMembers)
{
FieldInfo field = FieldInfo.Create(this.GetType().GetProperty(s));
if (field == null)
{
throw new Tenor.Data.MissingFieldException(this.GetType(), s);
}
filters.Add(field);
}
foreach (FieldInfo field in filters)
{
if (so.Conditions.Count > 0)
{
so.Conditions.Add(Tenor.Data.LogicalOperator.And);
}
so.Conditions.Add(field.RelatedProperty.Name, field.PropertyValue(this));
}
System.Data.DataTable dt = so.SearchWithDataTable(connection);
if (dt.Rows.Count == 0)
{
dt.Dispose();
throw (new RecordNotFoundException());
}
else if (dt.Rows.Count > 1)
{
throw new ManyRecordsFoundException();
}
dataRow = dt.Rows[0];
}
FieldInfo[] fields = GetFields(this.GetType());
SpecialFieldInfo[] spfields = GetSpecialFields(this.GetType());
foreach (FieldInfo f in fields)
{
string alias = baseFieldAlias + f.DataFieldName;
if (dataRow.Table.Columns.Contains(alias))
{
if (f.PrimaryKey || !f.LazyLoading)
{
f.SetPropertyValue(this, dataRow[alias]);
}
else if (f.LazyLoading && f.FieldType.IsAssignableFrom(typeof(BinaryStream)))
{
object len = dataRow[alias];
if (len == DBNull.Value)
{
len = 0;
}
f.SetPropertyValue(this, new BinaryStream(this, f.RelatedProperty.Name, Convert.ToInt64(len)));
}
}
}
foreach (SpecialFieldInfo f in spfields)
{
string alias = baseFieldAlias + f.Alias;
if (dataRow.Table.Columns.Contains(alias))
{
f.SetPropertyValue(this, dataRow[alias]);
}
}
dataRow = null;
if (ClassMetadata.Cacheable)
{
SaveToCache();
}
//Setting the lazy status.
this.EnableLazyLoading(lazyLoading);
}
private void PrepareDatabase()
{
string sConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" + TempBurnPath + @"\Data\RODB.DAT;Mode=Read|Write|Share Deny None;";
System.Data.OleDb.OleDbConnection AC = new System.Data.OleDb.OleDbConnection(sConnection);
try
{
AC.Open();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
#region TheWork
System.Data.OleDb.OleDbCommand CM = new System.Data.OleDb.OleDbCommand();
CM.Connection = AC;
System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter();
System.Data.DataTable DT = new System.Data.DataTable();
foreach(OCL.RecordingSession RS in SelectedRecordingSessions)
{
string sSQL = @"INSERT INTO tblSession(Description,IsPresentation,OwnerId,CreatedDate,IsOysterSession) VALUES('" +
RS.Description + "'" +
"," +Convert.ToInt32(RS.IsPresentation) +
"," + 23 +
",'" + RS.CreatedDate +"'" +
"," + Convert.ToInt32(true) + ")";
int SessionID = 0;
try
{
CM.CommandText = sSQL;
CM.ExecuteNonQuery();
CM.CommandText = "SELECT @@IDENTITY";
SessionID = (int)CM.ExecuteScalar();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
foreach(OCL.Recording R in RS.CurrentRecordings(frmParent.LUser))
{
string FileName = "";
string FileExt = "";
string[] sFileName = R.Description.Split(".".ToCharArray());
if(sFileName.Length == 1)
{
FileExt = "";
}
else if(sFileName.Length == 2)
{
FileExt = "." + sFileName[1];
}
FileName = RS.Description + "_" + R.DisplayName;
string NewDescription = RenameFile(FileName,FileExt);
sSQL = @"INSERT INTO tblRecording(Name,Created,VideoStorageServerId," +
"DisplayName, StreamingEncoderId,SessionId," +
"IsDesktopCapture,IsPrimaryCamera,DiskSize,Playlength," +
"HasAudio,HasVideo) " +
"VALUES ('" + NewDescription +
"', '" + R.CreatedDate +
"', 9" +
", '" + R.DisplayName +
"', 15" +
"," + SessionID +
"," + Convert.ToInt32(R.IsDesktopCapture) +
"," + Convert.ToInt32(R.IsPrimaryCamera) +
"," + R.SizeOnDisk +
"," + R.PlayLength +
"," + Convert.ToInt32(R.HasAudio) +
"," + Convert.ToInt32(R.HasVideo) +
")";
try
{
CM.CommandText = sSQL;
CM.ExecuteNonQuery();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
}
foreach(OCL.Note N in RS.AllVisibleNotes(frmParent.LUser))
{
int NoteID = 0;
sSQL = "INSERT INTO tblNotes(SyncTime,Description,SessionId,Created) VALUES('" +
N.SyncTime + "','" + N.Description + "'," + SessionID + ",'" +
N.Created +"'" +
")";
CM.CommandText = sSQL;
try
{
CM.CommandText = sSQL;
CM.ExecuteNonQuery();
CM.CommandText = "SELECT @@IDENTITY";
NoteID = (int)CM.ExecuteScalar();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
OCL.Attachments FileAttachments = N.AllVisibleAttachments(frmParent.LUser);
foreach(OCL.Attachment A in FileAttachments)
{
sSQL = "INSERT INTO tblAttachments(OriginalName,StoredName,NoteId,Created,FileSize) VALUES(" +
" '" + A.OriginalName + "','" + A.StoredName +
"'," + NoteID.ToString() +
",'" + A.Created + "'" +
"," + A.FileSize +
")";
CM.CommandText = sSQL;
try
{
CM.ExecuteNonQuery();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
}
}
}
DA.Dispose();
DT.Dispose();
CM.Dispose();
#endregion
AC.Close();
AC.Dispose();
System.Threading.Thread.SpinWait(500000);
}
private int insertOrder(int intDataRow, int intCustInfoID)
{
Worksheet ActiveSheet = Globals.ThisAddIn.Application.ActiveSheet;
System.Data.DataTable dt = new System.Data.DataTable();
string consqlserver = ConfigurationManager.ConnectionStrings["HGWHConnectionString"].ToString() + ";Password=HEGII;";
string sql = "EXEC progInsertOrder " + intCustInfoID + ", '历史数据导入'";
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("报装").Column].Value == null)
{
sql = sql + ", NULL";
}
else
{
if (DateTime.TryParse(ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("报装").Column].Value.ToString(), out DateTime dtApplyDate))
{
sql = sql + ", '" + dtApplyDate.ToString() + "'";
}
else
{
sql = sql + ", NULL";
}
}
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("预约").Column].Value == null)
{
sql = sql + ", NULL";
}
else
{
if (DateTime.TryParse(ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("预约").Column].Value.ToString(), out DateTime dtReserveDate))
{
sql = sql + ", '" + dtReserveDate.ToString() + "'";
}
else
{
sql = sql + ", NULL";
}
}
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("销售点").Column].Value == null)
{
sql = sql + ", NULL";
}
else
{
sql = sql + ", '" + ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("销售点").Column].Value.ToString() + "'";
}
sql = sql + ", '" + comboOldOrderType.Text + "', 0";
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("安装产品").Column].Value == null)
{
sql = sql + ", NULL";
}
else
{
sql = sql + ", '" + ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("安装产品").Column].Value + "'";
}
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("预约").Column].Value == null)
{
sql = sql + ", NULL";
}
else
{
sql = sql + ", '" + ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("预约").Column].Value.ToString() + "'";
}
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("预约").Column].Value == null)
{
sql = sql + ", '待上门'";
}
else
{
if (DateTime.TryParse(ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("预约").Column].Value.ToString(), out DateTime dtReserveDate))
{
if (dtReserveDate > DateTime.Today)
{
sql = sql + ", '待上门'";
}
else
{
sql = sql + ", '完成'";
}
}
else
{
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("预约").Column].Value.IndexOf("改期") > 0)
{
sql = sql + ", '改期'";
}
else
{
sql = sql + ", '完成'";
}
}
}
if (ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("安装产品").Column + 1].Value == null)
{
sql = sql + ", NULL";
}
else
{
sql = sql + ", '" + ActiveSheet.Cells[intDataRow, ActiveSheet.Range["1:1"].Find("安装产品").Column + 1].Value + "'";
}
SqlConnection con = new SqlConnection(consqlserver);
SqlDataAdapter da = new SqlDataAdapter(sql, con);
try
{
da.Fill(dt);
if (dt.Rows.Count > 0)
{
return((int)dt.Rows[0][0]);
}
else
{
return(0);
}
}
catch (Exception msg)
{
MessageBox.Show(msg.Message);
return(-1);
}
finally
{
con.Close();
con.Dispose();
da.Dispose();
dt.Dispose();
}
}
/// <summary>
/// дXML�ĵ�
/// </summary>
/// <param name="name">���ݿ������������</param>
/// <param name="con_str">���ݿ������Դ��Ϣ</param>
public void writeXML(string name , ConfigStruct con_str)
{
try
{
//����һ��dataset
System.Data.DataSet ds = new System.Data.DataSet("Autoconfig");
//�ж��Ƿ����config.xml�ļ�,������ڴӸ��ļ��ж�ȡ���ݵ�dataset
if(System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory.TrimEnd(new char[] {'\\'}) +"\\" +_FileName))
{
ds.ReadXml(AppDomain.CurrentDomain.BaseDirectory.TrimEnd(new char[] {'\\'}) +"\\" +_FileName);
}
//�ж��Ƿ���ڸñ�,���������ɾ���ñ�
if(ds.Tables.IndexOf(name.ToUpper()) != -1 )
{
ds.Tables.Remove(name.ToUpper());
}
//����һ��datatable
System.Data.DataTable dt = new System.Data.DataTable(name.ToUpper());
//Ϊ�¶���ı�������
dt.Columns.Add("key");
dt.Columns.Add("value");
SymmetricMethod sm = new SymmetricMethod();
//���Ӽ�¼���¶���ı���
dt.Rows.Add(new object[2]{ sm.Encrypto( str_HA ), sm.Encrypto( con_str.hostAddress)});
dt.Rows.Add(new object[2]{ sm.Encrypto( str_UN ), sm.Encrypto( con_str.userName)});
dt.Rows.Add(new object[2]{ sm.Encrypto( str_PWD ), sm.Encrypto( con_str.password)});
dt.Rows.Add(new object[2]{ sm.Encrypto( str_DBN ), sm.Encrypto( con_str.DBName)});
//�������ӵ�������µ�dataset��
ds.Tables.Add(dt);
//д��xml�ĵ�
ds.WriteXml(AppDomain.CurrentDomain.BaseDirectory.TrimEnd(new char[] {'\\'}) +"\\" +_FileName);
//�ͷ�datatable �� dataset
dt.Dispose();
ds.Dispose();
}
catch(Exception exp)
{
//System.Windows.Forms.MessageBox.Show(exp.Message);
throw exp;
}
}
