private void UpdateHpcrDatabase(SqlAdapter adapter, HpcrArchiveRecord record)
{
string sql = string.Empty;
if (string.IsNullOrEmpty(record.SessionId))
{
record.StfLogStatus = StfLogStatus.Ignore;
}
if ((record.JobType.Equals("HpcrScanToMe") || record.JobType.Equals("HpcrPublicDistributions")) && record.DateDelivered.HasValue)
{
sql = @"
update ArchiveTable
set StfDigitalSendServerJobId = '{0}', StfLogId = '{1}', StfLogStatus = '{2}', prDateDelivered = '{3}'
where RowIdentifier = {4}
".FormatWith(record.StfDigitalSendServerJobId.ToString(), record.StfLogId.ToString(), record.StfLogStatus, record.DateDelivered, record.RowIdentifier);
}
else
{
sql = @"
update ArchiveTable
set StfDigitalSendServerJobId = '{0}', StfLogId = '{1}', StfLogStatus = '{2}'
where RowIdentifier = {3}
".FormatWith(record.StfDigitalSendServerJobId.ToString(), record.StfLogId.ToString(), record.StfLogStatus, record.RowIdentifier);
}
TraceFactory.Logger.Debug(sql);
adapter.ExecuteNonQuery(sql);
}
private static string GetSessionConfiguration(string sessionId)
{
string sql = String.Format(Resources.SessionConfigurationSQL, sessionId);
DataTable table = new DataTable();
using (SqlAdapter adapter = new SqlAdapter(ReportingSqlConnection.ConnectionString))
{
using (SqlDataReader reader = adapter.ExecuteReader(sql))
{
if (reader.HasRows)
{
while (reader.Read())
{
try
{
return(reader.GetString(0));
}
catch (SqlNullValueException)
{
return(null);
}
}
}
}
}
return(null);
}
public static bool Insert_tbl_AdProduct(SqlSchema smp, NameValueCollection form)
{
//string sql = @"insert into [Gungnir].[dbo].[tbl_AdProduct]
// (advertiseid,pid,position,state,createdatetime,promotionprice,promotionnum,new_modelid)
// values(@advertiseid,@pid,@position,@state,@cdt,@promotionprice,@promotionnum,@new_modelid)";
if (form == null)
{
return(false);
}
string sql = @" declare @Count int
select @Count = count(1) from [Gungnir].[dbo].[tbl_AdProduct] WITH (NOLOCK) where PID=@pid and new_modelID=@new_modelid
if(@Count = 0)
begin
insert into [Gungnir].[dbo].[tbl_AdProduct]
(advertiseid,pid,position,state,createdatetime,promotionprice,promotionnum,new_modelid)
values(@advertiseid,@pid,@position,@state,@cdt,@promotionprice,@promotionnum,@new_modelid)
end ";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "gungnir")
.Par(form)
.Par("@cdt", DateTime.Now, SqlDbType.DateTime)
.ExecuteNonQuery() > 0);
}
public static bool Get_act_saleproduct_mid(SqlSchema smSaleProd, NameValueCollection form)
{
string sql = @"select top 1 pnum from gungnir..act_saleproduct with (nolock) where pnum like @pnum and mid=@mid and id<>@id";
return(SqlAdapter.Create(sql, smSaleProd, CommandType.Text, "Aliyun")
.Par(form).ExecuteModel().IsEmpty);
}
public virtual int InsertOrUpdateObject(Dictionary <string, object> sqlParams, string key, string databaseTable, string condition)
{
var cols = String.Empty;
var vals = String.Empty;
var updvals = String.Empty;
int rows = -1;
using (var cn = SqlAdapter.GetNewConnection())
{
var cmd = cn.CreateCommand();
foreach (var p in sqlParams)
{
cmd.Parameters.Add(CreateSQLParam(cmd, "@" + p.Key, p.Value));
vals += "@" + p.Key + ",";
updvals += p.Key + "=@" + p.Key + ",";
}
vals = vals.TrimEnd(',');
updvals = updvals.TrimEnd(',');
cols = vals.Replace("@", null);
cmd.CommandText = "SELECT " + key + " FROM " + databaseTable + @" WHERE 1=1 AND " + condition;
var result = cmd.ExecuteScalar();
cmd.CommandText = (result == null || result == DBNull.Value) ? "INSERT INTO " + databaseTable + "(" + cols + ") VALUES(" + vals + ")" + SqlAdapter.LastInsertIdSelector
: "UPDATE " + databaseTable + " SET " + updvals + " WHERE 1=1 AND " + condition + "; SELECT 1;";
result = cmd.ExecuteScalar();
rows = (result != null && result != DBNull.Value) ? Convert.ToInt32(result) : 0;
}
return(rows);
}
public static void InsertVIN_REGION(string region)
{
SqlAdapter.Create("INSERT INTO VIN_REGION (region,isenable) VALUES(@region,@isenable)")
.Par("@region", region, SqlDbType.NVarChar)
.Par("@isenable", 0, SqlDbType.Int)
.ExecuteNonQuery();
}
public virtual List <T> GetObjects(Dictionary <string, object> sqlParams, string query)
{
List <T> lstObj = new List <T>(64);
using (var cn = SqlAdapter.GetNewConnection())
{
var cmd = cn.CreateCommand();
cmd.CommandText = query;
foreach (var p in sqlParams)
{
cmd.Parameters.Add(CreateSQLParam(cmd, "@" + p.Key, p.Value));
}
using (var r = cmd.ExecuteReader())
{
T obj;
while (r.Read())
{
obj = resultReader(r);
lstObj.Add(obj);
}
}
}
return(lstObj);
}
public static DyModel GetVin_record(int ps, int pe)
{
return(SqlAdapter.Create("select * from (select (row_number() over(order by id)) as rownum,* from gungnir..vin_record) T where T.rownum between @start and @end order by createtime desc", CommandType.Text, "Aliyun")
.Par("@start", ps, SqlDbType.Int)
.Par("@end", pe, SqlDbType.Int)
.ExecuteModel());
}
public static bool Update_tbl_AdProduct(SqlSchema smp, NameValueCollection form, string id)
{
//string sql = @"update [Gungnir].[dbo].[tbl_AdProduct] set pid=@pid,position=@position,state=@state,
// lastupdatedatetime=@lastupdatedatetime,promotionprice=@promotionprice,
// promotionnum=@promotionnum where pid=@id and new_modelid=@new_modelid";
if (form == null && form["AdvertiseID"] == null)
{
return(false);
}
string sql = @"
declare @GetAdvertiseID varchar(256),@Count int
select @GetAdvertiseID = AdvertiseID from [Gungnir].[dbo].[tbl_AdProduct] WITH (NOLOCK) where PID=@pid and new_modelID=@new_modelid
select @Count = count(1) from [Gungnir].[dbo].[tbl_AdProduct] WITH (NOLOCK) where PID=@pid and new_modelID=@new_modelid
if(@Count = 0)
begin
update [Gungnir].[dbo].[tbl_AdProduct] set pid=@pid,position=@position,state=@state,
lastupdatedatetime=@lastupdatedatetime,promotionprice=@promotionprice,
promotionnum=@promotionnum where pid=@id and new_modelid=@new_modelid;
end
else if(@Count = 1 and @GetAdvertiseID = @advertiseid)
begin
update [Gungnir].[dbo].[tbl_AdProduct] set pid=@pid,position=@position,state=@state,
lastupdatedatetime=@lastupdatedatetime,promotionprice=@promotionprice,
promotionnum=@promotionnum where pid=@id and new_modelid=@new_modelid;
end
";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "gungnir")
.Par(form)
.Par("@lastupdatedatetime", DateTime.Now)
.Par("@id", id, SqlDbType.VarChar, 256)
.ExecuteNonQuery() > 0);
}
//根据parentid获取SaleModule的数据
public ActionResult Modules(string id)
{
var md = SqlAdapter.Create(sqlsel, schema).Par("@parentid", id)
.ExecuteModel();
if (md.IsEmpty)
{
return(Content(error("模块不存在")));
}
var parentidmd = SqlAdapter.Create(selectparentid, schema).ExecuteModel();
var parentidArray = parentidmd.DATA.AsEnumerable().Select(r => r["parentid"]);
List <SaleModule> list = ModelConvertHelper <SaleModule> .ConvertToModel(md.DATA).ToList();
for (int i = 0; i < list.Count; i++)
{
if (!parentidArray.Contains(list[i].id))
{
list[i].state = "open";
}
else
{
list[i].state = "closed";
}
}
return(Content(list.ToJson().ToString()));
}
private ISqlAdapter GetAdapterInstance(SqlAdapter adapter)
{
switch (adapter)
{
case SqlAdapter.SqlServer2005:
return(new SqlserverAdapter());
case SqlAdapter.Sqlite3:
return(new Sqlite3Adapter());
case SqlAdapter.Oracle:
return(new OracleAdapter());
case SqlAdapter.MySql:
return(new MySqlAdapter());
case SqlAdapter.Postgres:
return(new PostgresAdapter());
case SqlAdapter.SqlAnyWhere:
return(new SqlAnyWhereAdapter());
default:
throw new ArgumentException("The specified Sql Adapter was not recognized");
}
}
// GET all companies from Sql and store them in StockPolishcompany list
private void btGetAllCompaniesFromSql_Click(object sender, RoutedEventArgs e)
{
SqlAdapter SqlAdapt = new SqlAdapter();
ListOfPolishCompanies = SqlAdapt.GetCompaniesList();
ShowRaportOnScreen(SqlAdapt.GetRaport());
}
public SqlLamBase(SqlAdapter adater, string tableName)
{
_type = SqlType.Query;
_adapter = adater;
_builder = new Builder(_type, tableName, GetAdapterInstance(_adapter));
_resolver = new LambdaResolver(_builder);
}
static async Task Main(string[] args)
{
var port = Convert.ToInt32(args[0]);
var clientPid = Convert.ToInt32(args[1]);
var adapter = new SqlAdapter();
var server = Server.Create((config) =>
{
config.UsePort(port);
config.UseClientPid(clientPid);
config.UseRoutes((routes) =>
{
routes.Add("Connect", adapter.Connect);
routes.Add("Disconnect", adapter.Disconnect);
routes.Add("Execute", adapter.Execute);
routes.Add("ExecuteInTransaction", adapter.ExecuteInTransaction);
routes.Add("ExecutePreparedStatement", adapter.ExecutePreparedStatement);
routes.Add("ExecutePreparedStatementInTransaction", adapter.ExecutePreparedStatementInTransaction);
routes.Add("BeginTransaction", adapter.BeginTransaction);
routes.Add("RollbackTransaction", adapter.RollbackTransaction);
routes.Add("CommitTransaction", adapter.CommitTransaction);
routes.Add("PrepareStatement", adapter.PrepareStatement);
routes.Add("ClosePreparedStatement", adapter.ClosePreparedStatement);
});
});
await server.Start();
}
// SEND - data to Sql
private void btnExportData_Click(object sender, RoutedEventArgs e)
{
// ToDo : code connection here
SqlAdapter SqlAdapter = new SqlAdapter();
SqlAdapter.SaveFinancialDataOnSqlServer(ListOfYearlyRaports);
}
public static int Delete_act_saleproduct(int id, SqlSchema smSaleProd)
{
string sql = @"delete from gungnir..act_saleproduct where id=@id";
return(SqlAdapter.Create(sql, smSaleProd, CommandType.Text, "Aliyun")
.Par("@id", id).ExecuteNonQuery());
}
static async Task TestAsync()
{
try {
var adapter = new SqlAdapter {
ConnectionString = connectionString
};
var book = new Book {
IsbnCode = "4774180947", Title = "C#プログラマーのための 基礎からわかるLINQマジック!"
};
await adapter.InsertAsync(book);
await adapter.DeleteAsync(book);
await adapter.CreateAsync <Author>();
await adapter.CreateAsync <Writing>();
await adapter.DropAsync <Author>();
await adapter.DropAsync <Writing>();
}
catch (Exception ex) {
Console.WriteLine($"エラー: {ex.Message}");
}
}
/// <summary>
///
/// </summary>
/// <param name="hostName"></param>
/// <param name="instanceName"></param>
/// <param name="port"></param>
private void SetupDatabase(string hostName, string instanceName, int port)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder()
{
InitialCatalog = "DSS_MACHINE",
IntegratedSecurity = true,
MultipleActiveResultSets = true
};
// If a port is specified, use it. Otherwise use default port settings.
StringBuilder dataSource = new StringBuilder(hostName);
dataSource.Append("\\");
dataSource.Append(instanceName);
if (port > 0)
{
dataSource.Append(",");
dataSource.Append(port);
}
builder.DataSource = dataSource.ToString();
TraceFactory.Logger.Debug("Attempting connection to " + builder.DataSource);
_connectionString = builder.ToString();
using (SqlAdapter adapter = new SqlAdapter(_connectionString))
{
adapter.ExecuteNonQuery(Resource.CreateTransactionTableSql);
adapter.ExecuteNonQuery(Resource.DeleteTriggersSql);
adapter.ExecuteNonQuery(Resource.CreateInsertTriggerSql);
}
TraceFactory.Logger.Debug("Connected to database instance: " + builder.DataSource);
}
public static bool IsRepeatVin_record(string phone, string vin)
{
return(SqlAdapter.Create("select top 1 * from gungnir..vin_record with(NOLOCK) where rphone=@phone and vin=@vin", CommandType.Text, "Aliyun")
.Par("@phone", phone, SqlDbType.NVarChar, 20)
.Par("@vin", vin, SqlDbType.NVarChar, 32)
.ExecuteModel().IsEmpty);
}
public void Test_ChaningChipsSqlAdapter(int id, int chips)
{
SqlAdapter sa = new SqlAdapter(1);
sa.UpdateChips(id, chips);
Assert.True(sa.getUsers().FirstOrDefault(x => x.UserId == id).ChipTotal == chips);
}
public static bool Delete_tal_newappsetdata(SqlSchema smAppModel, string id)
{
string sql = @"delete from gungnir..tal_newappsetdata where id=@id";
return(SqlAdapter.Create(sql, smAppModel, CommandType.Text, "gungnir")
.Par("@id", id).ExecuteNonQuery() > 0);
}
public static int UpdateVin_record(string phone, string vin, string id)
{
return(SqlAdapter.Create("update gungnir..vin_record with (ROWLOCK) set rphone=@phone,vin=@vin where id=@id", CommandType.Text, "Aliyun")
.Par("@phone", phone, SqlDbType.NVarChar, 20)
.Par("@vin", vin, SqlDbType.NVarChar, 32)
.Par("@id", id, SqlDbType.UniqueIdentifier)
.ExecuteNonQuery());
}
public SqlLam(SqlAdapter adapterType = SqlAdapter.SqlServer2008)
: base()
{
_type = SqlType.Query;
_adapter = adapterType;
_builder = new SqlQueryBuilder(_type, LambdaResolver.GetTableName <T>(), _defaultAdapter);
_resolver = new LambdaResolver(_builder);
}
public static bool Delete_tbl_AdProduct(SqlSchema smp, string mid, string id)
{
string sql = @"delete from [Gungnir].[dbo].[tbl_AdProduct] where pid=@id and new_modelid=@new_modelid";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "gungnir")
.Par("@new_modelid", mid)
.Par("@id", id, SqlDbType.VarChar, 256).ExecuteNonQuery() > 0);
}
public static DyModel Get_tbl_AdProduct(SqlSchema smp, string mid)
{
string sql = @"select * from [Gungnir].[dbo].[tbl_AdProduct] where [new_modelid]=@new_modelid order by Position";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "Gungnir_AlwaysOnRead")
.Par("@new_modelid", mid)
.ExecuteModel());
}
protected override Expression VisitMember(MemberExpression node)
{
MemberInfo memberInfo = node.Member;
sb.Append(SqlAdapter.GetColumnName(memberInfo));
MemberInfo = memberInfo;
return(node);
}
public SqlLam(SqlAdapter type = SqlAdapter.SqlServer2005)
: base(type, LambdaResolver.GetTableName <T>())
{
//_type = SqlType.Query;
//GetAdapterInstance(type);
//_builder = new Builder(_type, LambdaResolver.GetTableName<T>(), _defaultAdapter);
//_resolver = new LambdaResolver(_builder);
}
public static DyModel Get_act_salemodule(SqlSchema smSaleModule, int parentid)
{
string sql = @"select * from gungnir..act_salemodule with(nolock) where parentid=@parentid order by sort";
return(SqlAdapter.Create(sql, smSaleModule, CommandType.Text, "Aliyun")
.Par("@parentid", parentid)
.ExecuteModel());
}
public void Init()
{
Constants.Init();
SqlAdapter.Init();
testLink = new Link();
testLink.DateCreated = DateTime.Now;
testLink.IdUser = 1;
testLink.Url = "Unit Test .Net";
}
void Application_Start(object sender, EventArgs e)
{
// Code that runs on application startup
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
Constants.Init();
SqlAdapter.Init();
RegisterRoutes(RouteTable.Routes);
}
private static ISqlAdapter GetAdapterInstance(SqlAdapter adapter)
{
switch (adapter)
{
case SqlAdapter.SqlServer2008:
return new SqlServer2008Adapter();
case SqlAdapter.SqlServer2012:
return new SqlServer2012Adapter();
default:
throw new ArgumentException("The specified Sql Adapter was not recognized");
}
}
public static void SetAdapter(SqlAdapter adapter)
{
_defaultAdapter = GetAdapterInstance(adapter);
}
