常用C#方法合集:数据表操作和邮件发送
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
//数据库连接
using System.Data.SqlClient;
//邮件服务
using System.Net.Mail;
using System.Web;
using System.Xml.Linq;
using System.Windows.Forms;
namespace AinB.MyClass
{
public class MyMeth
{
#region 公共变量
getSet gs=new getSet();
public static StringBuilder sb = new StringBuilder();
public static SqlConnection conn = new SqlConnection("Data Source=192.168.XX.XX;User ID=SA;Password=123;Initial Catalog=UD_Student");
public static object obj = null;
public static string varTable = "UD_EWI.dbo.UT_MF_BARCODE_SCAN_INFO_HW";
public static DataSet ds = new DataSet();
#endregion
#region 1:校验用户输入,回传提示
public string StrLen(string str)
{
//正则表达式
string pattern = @"^[a-zA-Z0-9]*$";
string msg = "";
//格式化
str = str.Trim();
if (str.Length > 0)
{
if (!System.Text.RegularExpressions.Regex.IsMatch(str, pattern))
{
msg = "输入非法字符,只能识别数字与字母组成!";
}
}
else
{
msg = "输入不能为空!";
}
return msg;
}
#endregion
#region 2:过滤重复数据方法,回传表方法
public DataTable Distinct(DataTable dt, string[] Names)
{
DataView dv = dt.DefaultView;
DataTable DisTable = dv.ToTable("DisT", true, Names);
return DisTable;
}
#endregion
#region 3:邮件发送
public void sendMail(string mailNo)
{
string a = "条件变量";
//SendMailWebReference.WebService1 SendMailWebs = new SendMailWebReference.WebService1();
if (mailNo.Length > 0)
{//邮件提醒
sb.Length = 0;
sb.Append($"select ISNULL(count(barcode),0) from {MyMeth.varTable} (nolock) where 1=1 and mo='{a}' and processcode='{a}'");
obj = MyMeth.GetScalar(sb.ToString());
if (obj != null && obj.ToString() == "1")//首件
{//发送邮件
string subject = a + "(" + MyMeth.varTable + "):返修品首件【固件(PIE)/软件(RP)维护】邮件提醒";//主题
string mbody = "PIE/RP" + ",你好!" + "<br />";//内容
mbody += "返修品:固件(PIE)、软件(RP)版本维护<br />";
mbody += "工单:" + a + "<br />";
mbody += "类型:" + a.Substring(3, 1) + "<br />";
mbody += "PN编码:" + a + "<br />";
mbody += "公司编码:" + a + "<br />";
mbody += "描述:" + a + "<br />";
mbody += "<br />";
mbody += "该任务令已首件扫描:" + "<br />";
mbody += "若是您负责的产品,有需要未维护的,请您记得维护固件和软件版本信息!";
mbody += "<br />";
mbody += "维护入口:<br />";
//MyMeth.SendMailWebs.SendWIMail(mailNo, "aaa@global-imi.com", subject, mbody);
}
}
if (mailNo.Length > 0)
{
sb.Length = 0;
sb.Append($"select ISNULL(count(b.barcode),0) as ID,b.MO,b.Barcode,b.STA,b.Reason,b.CustNo from {a} (nolock) a join ");
sb.Append($" {MyMeth.varTable} (nolock) b on a.MO=b.MO and a.Barcode=b.Barcode ");
sb.Append($" where 1=1 and a.mo='{a}' and a.processcode='{a}' and DATEDIFF(dd,a.CreateTime2,GETDATE())=0 ");
sb.Append($" group by b.MO,b.Barcode,b.STA,b.Reason,b.CustNo");
DataSet ds_leo = MyMeth.GetDataSet(sb.ToString());
string ID = ds_leo.Tables[0].Rows[0]["ID"].ToString();
if (ds_leo.Tables[0].Rows.Count == 1 && ID == "1")
{
string subject = a + "(" + a + "):返修品隔离条码邮件提醒";//主题
string mbody = "QA" + ",你好!" + "<br />";//内容
mbody += $"返修任务令:{ds_leo.Tables[0].Rows[0]["MO"]}" + "<br />";
mbody += $"工序:{a}" + "<br />";
mbody += $"SN:{ds_leo.Tables[0].Rows[0]["Barcode"]}" + "<br />";
mbody += $"已经被{ds_leo.Tables[0].Rows[0]["Reason"]}" + "<br />";
mbody += $"状态为:{ds_leo.Tables[0].Rows[0]["STA"]}" + "<br />";
mbody += "<br />";
mbody += "维护入口:<br />";
//MyMeth.SendMailWebs.SendWIMail(mailNo, "aaa@global-imi.com", subject, mbody);
}
}
}
#endregion
#region 4:连接数据库
public void dataConn(string where)
{
}
#endregion
#region
//[System.CodeDom.Compiler.GeneratedCodeAttribute("System.Web.Services", "4.7.3056.0")]
//[System.Diagnostics.DebuggerStepThroughAttribute()]
//[System.ComponentModel.DesignerCategoryAttribute("code")]
//[System.Web.Services.WebServiceBindingAttribute(Name = "WebService1Soap", Namespace = "http://tempuri.org/")]
//public partial class WebService1 : System.Web.Services.Protocols.SoapHttpClientProtocol
//{
// private System.Threading.SendOrPostCallback SendWIMailOperationCompleted;
// private System.Threading.SendOrPostCallback SendMailOperationCompleted;
// private System.Threading.SendOrPostCallback SendMailTraceabilityOperationCompleted;
// private System.Threading.SendOrPostCallback SendMailTraceability2OperationCompleted;
// private System.Threading.SendOrPostCallback SendMail2OperationCompleted;
// private bool useDefaultCredentialsSetExplicitly;
// /// <remarks/>
// public WebService1()
// {
// this.Url = global::STSZ_Utility.Properties.Settings.Default.STSZ_Utility_SendMailWebReference_WebService1;
// if ((this.IsLocalFileSystemWebService(this.Url) == true))
// {
// this.UseDefaultCredentials = true;
// this.useDefaultCredentialsSetExplicitly = false;
// }
// else
// {
// this.useDefaultCredentialsSetExplicitly = true;
// }
// }
// public new string Url
// {
// get
// {
// return base.Url;
// }
// set
// {
// if ((((this.IsLocalFileSystemWebService(base.Url) == true)
// && (this.useDefaultCredentialsSetExplicitly == false))
// && (this.IsLocalFileSystemWebService(value) == false)))
// {
// base.UseDefaultCredentials = false;
// }
// base.Url = value;
// }
// }
// public new bool UseDefaultCredentials
// {
// get
// {
// return base.UseDefaultCredentials;
// }
// set
// {
// base.UseDefaultCredentials = value;
// this.useDefaultCredentialsSetExplicitly = true;
// }
// }
#endregion
#region
public static DataSet GetDataSet(string SQL)
{
try
{
if (conn.State == ConnectionState.Closed) conn.Open();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(SQL, conn); //建立适配器
da.Fill(ds); //得到记录集
return ds;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message);
return null;
}
}
#endregion
#region
public static object GetScalar(string SQL)
{
try
{
if (conn.State == ConnectionState.Closed) conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = 90;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
return cmd.ExecuteScalar();
}
catch (System.Exception err)
{
MessageBox.Show(err.Message);
return null;
}
}
#endregion
#region
public static SqlDataReader GetDataReader(string SQL)
{
try
{
if (conn.State == ConnectionState.Closed) conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = 90;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (System.Exception err)
{
MessageBox.Show(err.Message);
return null;
}
}
#endregion
#region
public class getSet
{
public getSet() { }
public string _process_seqn;
public string Name
{
set { _process_seqn = value; }
get { return _process_seqn; }
}
public int age
{
get;
set;
}
}
#endregion
#region
public DataTable MacInfo(string[] ST)
{
string SQL = $"exec PROC_SN '{ST[1]}'";
DataTable macInfo = new DataTable();
//macInfo = null;
SqlConnection conn;
if (ST[0] == "KC")
{
conn = new SqlConnection("Data Source=192.168.xx.xx;User ID=SA;Password=123;Initial Catalog=UD_Student");
}
else if (ST[0] == "PS")
{
conn = new SqlConnection("Data Source=192.168.xx.xx;User ID=SA;Password=123;Initial Catalog=UD_Student");
}
else if (ST[0] == "JX")
{
conn = new SqlConnection("Data Source=192.168.xx.xx;User ID=SA;Password=123;Initial Catalog=UD_Student");
}
else if (ST[0] == "CD")
{
conn = new SqlConnection("Data Source=192.168.xx.xx;User ID=SA;Password=123;Initial Catalog=UD_Student");
}
else
{
//测试库
conn = new SqlConnection("Data Source=192.168.xx.xx;User ID=SA;Password=123;Initial Catalog=UD_Student");
}
SqlCommand cmd = new SqlCommand(SQL, conn);
SqlDataAdapter ada = new SqlDataAdapter(SQL, conn);
ada.Fill(macInfo);
return macInfo;
}
#endregion
}
}
推荐阅读