using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace ConsoleApplication1
{
static class Test8
{
static SqlConnection cn;
static SqlTransaction tr;
public static void Test()
{
string dbConnection = "data source=192.168.131.38;initial catalog=SWSK_YOSAN_161115;persist security info=True;user id=sa;password=S3300859!;MultipleActiveResultSets=True;";
cn = new SqlConnection(dbConnection);
cn.Open();
tr = cn.BeginTransaction();
try
{
//To update data by ExecuteNonQuery()
UpDataByExecuteNonQuery("EDI_ZENRECVNO", "E0760002", 44444, DateTime.Now);
//GetZenrecNo("E0760002", "EDI_ZENRECVNO");
GetByExecuteScalar("E0760002", "EDI_ZENRECVNO");
//TEST
DataTable dt = GetDataBySqlDataAdapter();
}
catch
{
tr.Rollback();
}
finally
{
tr.Commit();
if (cn != null)
cn.Close();
}
}
private static void UpDataByExecuteNonQuery(string tblName, string kinouID, int upRecvNo, DateTime upSysDate)
{
try
{
SqlCommand cmUpd = new SqlCommand();
cmUpd.CommandTimeout = 60;
cmUpd.Connection = cn;
cmUpd.Transaction = tr;
StringBuilder stb = new StringBuilder();
Int32 rtn = 0;
stb.AppendLine(" UPDATE " + tblName);
stb.AppendLine(" SET EDIRECVNO = @UPRECVNO");
stb.AppendLine(" ,TRKYMD = @TRKYMD");
stb.AppendLine(" ,TRKTIME = @TRKTIME");
stb.AppendLine(" WHERE KINOKANRIID = @KINOID");
cmUpd.CommandText = stb.ToString();
cmUpd.Parameters.Add("@UPRECVNO", SqlDbType.BigInt);
cmUpd.Parameters.Add("@KINOID", SqlDbType.VarChar);
cmUpd.Parameters.Add("@TRKYMD", SqlDbType.DateTime);
cmUpd.Parameters.Add("@TRKTIME", SqlDbType.DateTime);
cmUpd.Parameters["@UPRECVNO"].Value = upRecvNo;
cmUpd.Parameters["@KINOID"].Value = kinouID;
cmUpd.Parameters["@TRKYMD"].Value = upSysDate.Date;
cmUpd.Parameters["@TRKTIME"].Value = upSysDate;
rtn = cmUpd.ExecuteNonQuery();
cmUpd.CommandText = stb.ToString();
rtn = cmUpd.ExecuteNonQuery();
Console.WriteLine(rtn.ToString());
Console.ReadLine();
}
catch (Exception ex)
{
throw ex;
}
}
private static Int32 GetByExecuteNonQuery(string kinouID, string tblName)
{
try
{
DataTable ret = new DataTable();
SqlCommand cm = new SqlCommand();
cm.CommandTimeout = 60;
cm.Connection = cn;
SqlDataAdapter adp = new SqlDataAdapter();
DataSet ds = new DataSet();
StringBuilder stb = new StringBuilder();
Int32 rtn = 0;
stb.AppendLine(" SELECT EDIRECVNO");
stb.AppendLine(" FROM " + tblName);
stb.AppendLine(" WHERE KINOKANRIID = '" + kinouID + "'");
stb.AppendLine(" ORDER BY TRKYMD, TRKTIME");
cm.CommandText = stb.ToString();
cm.Transaction = tr;
adp.SelectCommand = cm;
adp.Fill(ds, "GetRecvT");
ret = ds.Tables["GetRecvT"];
if (ret.Rows.Count > 0)
{
rtn = int.Parse(ret.Rows[0][0].ToString());
}
Console.WriteLine(rtn.ToString());
Console.ReadLine();
return rtn;
}
catch
{
throw;
}
}
private static string GetByExecuteScalar(string kinouID, string tblName)
{
try
{
SqlCommand cm = new SqlCommand
{
CommandTimeout = 60,
Connection = cn,
Transaction = tr
};
StringBuilder stb = new StringBuilder();
stb.AppendLine(" SELECT EDIRECVNO");
stb.AppendLine(" FROM " + tblName);
stb.AppendLine(" WHERE KINOKANRIID = '" + kinouID + "'");
stb.AppendLine(" ORDER BY TRKYMD, TRKTIME");
cm.CommandText = stb.ToString();
var rtn = cm.ExecuteScalar();
Console.WriteLine(rtn.ToString());
Console.ReadLine();
return rtn.ToString();
}
catch
{
throw;
}
}
private static DataTable GetDataBySqlDataAdapter()
{
var ds = new DataSet();
var cm = new SqlCommand
{
CommandTimeout = 60,
Connection = cn,
Transaction = tr
};
//SQL発行時のタイムアウトを設定
using (var adp = new SqlDataAdapter())
{
var stb = new StringBuilder();
//空白のデータベースを取得する
stb.AppendLine(" SELECT *");
stb.AppendLine(" FROM " + "TABLENAME");
stb.AppendLine(" ORDER BY ENTRYTIME, SEQ, MAT_DOC");
cm.CommandText = stb.ToString();
adp.SelectCommand = cm;
adp.Fill(ds);
}
return ds.Tables[0];
}
}
}