public static string GetNewDJBH(int Type, string QDDM, string KHDM, string TableName)
{
try
{
string pic = "P_GET_BILL_CODE_New";
string DJBH = string.Empty;
if (string.IsNullOrEmpty(QDDM))
{
QDDM = "000";
}
StoredProc storeProc = new StoredProc(pic, _testDbManager);
storeProc.AddParameter("@TYPE", SqlDbType.Int, 4, ParameterDirection.Input, Type);
storeProc.AddParameter("@CODE0", SqlDbType.VarChar, 50, ParameterDirection.Input, "");
storeProc.AddParameter("@CODE1", SqlDbType.VarChar, 50, ParameterDirection.Input, DateTime.Now.ToString("yyyyMMdd"));
storeProc.AddParameter("@CODE2", SqlDbType.VarChar, 50, ParameterDirection.Input, QDDM);
storeProc.AddParameter("@CODE3", SqlDbType.VarChar, 50, ParameterDirection.Input, KHDM);
storeProc.AddParameter("@CODE4", SqlDbType.VarChar, 50, ParameterDirection.Input, "");
storeProc.AddParameter("@TABLE", SqlDbType.VarChar, 50, ParameterDirection.Input, TableName);
storeProc.AddParameter("@BUFFER", SqlDbType.VarChar, 20, ParameterDirection.Output, DJBH);
DJBH = storeProc.INFExecute();
return(DJBH);
// P_GET_BILL_CODE(3, ' ', v_NOW, v_QDDM, v_KHDM, ' ', 'SDPHD', v_BILL) ;
}
catch (Exception ex)
{
return("");
}
}
protected void getCurrentSeasonWeek(int leagueId)
{
StoredProc SP = new StoredProc();
SP.Name = "ScheduleCurrentWeek_select";
SP.DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SP.ParameterSet = new System.Data.SqlClient.SqlCommand();
SP.ParameterSet.Parameters.AddWithValue("@leagueId", leagueId);
DataSet ds = StoredProc.ShowMeTheData(SP);
if (ds.Tables[0].Rows.Count < 1)
{
ddlWeek.SelectedIndex = 16;
}
foreach (DataRow item in ds.Tables[0].Rows)
{
ddlSeason.SelectedIndex = ddlSeason.Items.IndexOf(ddlSeason.Items.FindByValue(Helper.StringNull(item["seasonIndex"])));
int currentWeek = Helper.IntegerNull(item["weekIndex"]);
if (currentWeek > 0)
{
currentWeek = currentWeek - 1;
}
ddlWeek.SelectedIndex = ddlWeek.Items.IndexOf(ddlWeek.Items.FindByValue(Helper.StringNull(currentWeek)));
}
ddlWeek_SelectedIndexChanged(null, null);
}
public List <ChartFoodCharacterResultSet> GetChartFoodCharacter()
{
StoredProc <ChartFoodCharacter> getChartFoodCharacter = new StoredProc <ChartFoodCharacter>(typeof(ChartFoodCharacterResultSet));
ResultsList results = _getChartFoodCharacterDal.CallStoredProc(getChartFoodCharacter, null);
var r1 = results.ToList <ChartFoodCharacterResultSet>();
return(r1);
}
public List <ChartGoodsKindResultSet> GetChartGoodsKind()
{
StoredProc <ChartGoodsKind> getChartGoodsKind = new StoredProc <ChartGoodsKind>(typeof(ChartGoodsKindResultSet));
ResultsList results = _getChartGoodsKindDal.CallStoredProc(getChartGoodsKind, null);
var r1 = results.ToList <ChartGoodsKindResultSet>();
return(r1);
}
static public DataTable GetSalesReps()
{
Query q = new StoredProc("usp_GetSalesReps");
q.CnnStr = SCOUT.Core.Data.Helpers.Config["DB:Security"];
return(q.ExecuteDataTable());
}
public List <ChartGoodsTypeResultSet> GetChartGoodsType()
{
StoredProc <ChartGoodsType> getChartGoodsType = new StoredProc <ChartGoodsType>(typeof(ChartGoodsTypeResultSet));
ResultsList results = _getChartGoodsTypeDal.CallStoredProc(getChartGoodsType, null);
var r1 = results.ToList <ChartGoodsTypeResultSet>();
return(r1);
}
protected override void OnInitUpdateSp(StoredProc sp, User user)
{
sp.SetParam(UserName, SqlDbType.NVarChar, 50, user.UserName);
sp.SetParam(Password, SqlDbType.NVarChar, 300, user.Password);
sp.SetParam(Email, SqlDbType.NVarChar, 200, user.Email);
sp.SetParam(ProviderId, SqlDbType.Int, user.ProviderId);
sp.SetParam(ProviderUserId, SqlDbType.NVarChar, 500, user.ProviderUserId);
}
public DateTime GetSysDateTime()
{
StoredProc <GetSysDateTime> getSysDateTime = new StoredProc <GetSysDateTime>(typeof(GetSysDateTimeResultSet));
GetSysDateTime pams1 = new GetSysDateTime();
ResultsList results = _getSysDateTimeDal.CallStoredProc(getSysDateTime, pams1);
var r1 = results.ToList <GetSysDateTimeResultSet>();
return(r1.FirstOrDefault().currentDate);
}
static public User GetUser(string login)
{
Query q = new StoredProc("usp_GetUser");
q.CnnStr = Helpers.Config["DB:Security"];
q.Parameters.AddWithValue("@Username", login);
return(q.ExecuteSingle <User>());
}
protected override void OnInitUpdateSp(StoredProc proc, PageRequest entity)
{
proc.SetParam(DomainField, SqlDbType.NVarChar, 4000, entity.Domain);
proc.SetParam(UrlField, SqlDbType.NVarChar, 4000, entity.Url);
proc.SetParam(IpAddressField, SqlDbType.NVarChar, 50, entity.IpAddress);
proc.SetParam(BrowserField, SqlDbType.SmallInt, entity.Browser);
proc.SetParam(ReffererField, SqlDbType.NVarChar, 4000, entity.Refferer);
proc.SetParam(SiteIdField, SqlDbType.UniqueIdentifier, entity.SiteId);
proc.SetParam(UserIdField, SqlDbType.UniqueIdentifier, entity.UserId);
}
public List <ChartGoodsTrendByYearResultSet> GetChartGoodsTrendByYear(int inoutType, string startDate, string endDate)
{
StoredProc <ChartGoodsTrendByYear> getChartGoodsTrendByYear = new StoredProc <ChartGoodsTrendByYear>(typeof(ChartGoodsTrendByYearResultSet));
ChartGoodsTrendByYear pams1 = new ChartGoodsTrendByYear();
pams1.flag = inoutType;
pams1.begin_time = startDate;
pams1.end_time = endDate;
ResultsList results = _getChartGoodsTrendByYearDal.CallStoredProc(getChartGoodsTrendByYear, pams1);
var r1 = results.ToList <ChartGoodsTrendByYearResultSet>();
return(r1);
}
public string GetBillNo(int billNoId, string OriginalDept)
{
StoredProc <GetBillNo> getBillNo = new StoredProc <GetBillNo>(typeof(GetBillNoResultSet));
GetBillNo pams1 = new GetBillNo();
pams1.bill_id = billNoId;
pams1.num_bit = 3;
pams1.org_code = OriginalDept;
ResultsList results = _sysBillNoDal.CallStoredProc(getBillNo, pams1);
var r1 = results.ToList <GetBillNoResultSet>();
return(r1.FirstOrDefault().billNumber);
}
static public RobotList <User> GetUsers()
{
Query q = new StoredProc("usp_GetAllUsers");
q.CnnStr = Helpers.Config["DB:Security"];
RobotList <User> rval = new RobotList <User>(
q.ExecuteList <User>());
rval.ApplySort("FullName", ListSortDirection.Ascending);
return(rval);
}
protected void GetLeagues()
{
tblLeagues.InnerHtml = "No Leagues Created";
var sp = new StoredProc
{
Name = "League_select", DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString, ParameterSet = new SqlCommand()
};
var ds = StoredProc.ShowMeTheData(sp);
if (ds.Tables.Count == 0)
{
return;
}
var userId = string.Empty;
if (Request.IsAuthenticated)
{
userId = User.Identity.GetUserId();
}
var sbTable = new StringBuilder();
sbTable.Append("<table id='sumtable' class='table tablesorter' >");
//<th data-sorter='true' data-toggle='tooltip' data-html='true' data-container='body' data-placement='top' title='Request number'
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th>League Name</th>");
sbTable.Append("<th>Export URL</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
foreach (DataRow item in ds.Tables[0].Rows)
{
if (userId == item.Field <string>("ownerUserID"))
{
sbTable.Append("<tr>");
sbTable.Append($"<td class='leagueName'>{item.Field<string>("Name")}</td>");
sbTable.Append($"<td class='exportURL'>{ConfigurationManager.AppSettings["exportURL"]}/{item.Field<string>("exportID").ToLower()}</td>");
sbTable.Append("</tr>");
}
}
sbTable.Append("</tbody>");
sbTable.Append("</table>");
tblLeagues.InnerHtml = sbTable.ToString();
}
protected override void OnInitUpdateSp(StoredProc sp, Geolocation geolocation)
{
sp.SetParam(IpMin, SqlDbType.BigInt, geolocation.IpMin);
sp.SetParam(IpMax, SqlDbType.BigInt, geolocation.IpMax); /*
* sp.SetParam(CountryCode, SqlDbType.NVarChar, 50, geolocation.CountryCode);
* sp.SetParam(CountryName, SqlDbType.NVarChar, 50, geolocation.CountryName);
* sp.SetParam(State, SqlDbType.NVarChar, 50, geolocation.State);
* sp.SetParam(City, SqlDbType.NVarChar, 50, geolocation.City);*/
sp.SetParam(Latitude, SqlDbType.NVarChar, 50, geolocation.Latitude);
sp.SetParam(Longitude, SqlDbType.NVarChar, 50, geolocation.Longitude);
/*sp.SetParam(ZipCode, SqlDbType.NVarChar, 50, geolocation.ZipCode);
* sp.SetParam(TimeZone, SqlDbType.NVarChar, 50, geolocation.TimeZone);*/
}
public IHttpActionResult getTabblesVisibleToUserExceptWs(string uname, string pwd, int sort, int topLevelOnly, int outputTabblesWithInitData, int includeTagsOfSubordinateUsers)
{
try
{
var db = new tabblesEntities();
SqlParameter noAuth = null;
var add = new Action <SqlCommand>(c =>
{
c.Parameters.AddWithValue("@uname", uname);
c.Parameters.AddWithValue("@pwd", pwd);
c.Parameters.AddWithValue("@sort", sort);
c.Parameters.AddWithValue("@topLevelOnly", topLevelOnly);
c.Parameters.AddWithValue("@outputTabblesWithInitData", outputTabblesWithInitData == 1 ? true : false);
c.Parameters.AddWithValue("@includeTagsOfSubordinateUsers", includeTagsOfSubordinateUsers == 1 ? true : false);
noAuth = addOutputParameterInt("@noAuth", c);
});
var res = StoredProc.ExecuteStoredProc("getTabblesVisibleToUserExceptWs", add);
if (res.err == "timeout")
{
return(Ok(new Result {
error = "timeout"
}));
}
else
{
var r = new resultTables(dataTables: res.tables,
noAuth: (int)noAuth.Value,
machineName: null
, obj: null);
//var str = Newtonsoft.Json.JsonConvert.SerializeObject(r);
return(Ok(new Result {
ret = r
}));
}
}
catch (Exception e)
{
return(Ok(new Result {
error = "generic-error", stringOfExc = Utils.stringOfException(e)
}));
}
}
public async Task Mondo_Database_ExecuteSingleRecordDictionary()
{
IDictionary <string, object> dict = null;
using (Database db = Database.Create(_connectionString, false))
{
using (StoredProc sp = new StoredProc(db, "dbo.GetCustomerList"))
{
dict = await db.ExecuteSingleRecordDictionaryAsync(sp);
}
}
Assert.AreEqual("John", dict["Name"]);
Assert.AreEqual(27, dict["Age"]);
}
internal static string GetSqlForStoredProceduresColumns(StoredProc sp)
{
var sb = new StringBuilder();
System.Windows.Forms.Application.DoEvents();
sb.AppendLine("SET FMTONLY ON");
sb.Append("EXEC [" + (string.IsNullOrEmpty(sp.Schema) ? "dbo" : sp.Schema) + "].[" + sp.Name + "] ");
foreach (var parameter in sp.ParameterList)
{
if (parameter.DataType == SqlDbType.UniqueIdentifier)
{
sb.Append("@" + parameter.Name + "='540C6D43-5645-40FB-980F-2FF126BFBD5E'");
}
else if (parameter.IsTextType())
{
sb.Append("@" + parameter.Name + "=''");
}
else if (parameter.IsNumericType())
{
sb.Append("@" + parameter.Name + "=0");
}
else if (parameter.IsBinaryType())
{
sb.Append("@" + parameter.Name + "=0x0");
}
else if (parameter.DataType == SqlDbType.Bit)
{
sb.Append("@" + parameter.Name + "=0");
}
else if (parameter.IsDateType())
{
sb.Append("@" + parameter.Name + "='2000-01-01'");
}
else
{
System.Diagnostics.Debug.Write(string.Empty);
}
if (sp.ParameterList.IndexOf(parameter) < sp.ParameterList.Count - 1)
{
sb.Append(", ");
}
}
sb.AppendLine();
return(sb.ToString());
}
public IEnumerable <string> GetStoredProcedureList(string connectionString)
{
var retval = new List <string>();
using (var tableReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlForStoredProcedures()))
{
while (tableReader.Read())
{
var newEntity = new StoredProc();
newEntity.Name = tableReader["name"].ToString();
retval.Add(newEntity.Name);
//newEntity.Schema = tableReader["schema"].ToString();
}
}
return(retval);
}
private void AddToursToChildNode(XmlWriter xmlWriter, int parcatId)
{
List <Tour> SQLTours = new List <Tour>();
int nRet = GetSQLTourArrayList(SQLTours, "exec spGetWWTToursForDateRangeFromCatId " + parcatId + ", null, null, 0");
foreach (Tour tr in SQLTours)
{
xmlWriter.WriteStartElement("Tour");
xmlWriter.WriteAttributeString("Title", tr.TourTitle);
xmlWriter.WriteAttributeString("ID", tr.TourGuid.ToString());
xmlWriter.WriteAttributeString("Description", tr.TourDescription);
xmlWriter.WriteAttributeString("Classification", "Other");
xmlWriter.WriteAttributeString("AuthorEmail", tr.AuthorEmailAddress);
xmlWriter.WriteAttributeString("Author", tr.AuthorName);
xmlWriter.WriteAttributeString("AuthorUrl", tr.AuthorURL);
xmlWriter.WriteAttributeString("AverageRating", tr.AverageRating.ToString());
xmlWriter.WriteAttributeString("LengthInSecs", tr.LengthInSecs.ToString());
xmlWriter.WriteAttributeString("OrganizationUrl", tr.OrganizationURL);
xmlWriter.WriteAttributeString("OrganizationName", tr.OrganizationName);
xmlWriter.WriteAttributeString("ITHList", tr.TourITHList);
xmlWriter.WriteAttributeString("AstroObjectsList", tr.TourAstroObjectList);
xmlWriter.WriteAttributeString("Keywords", tr.TourKeywordList);
xmlWriter.WriteAttributeString("RelatedTours", tr.TourExplicitTourLinkList);
xmlWriter.WriteEndElement();
}
StoredProc sp1 = new StoredProc("exec spGetSubCatDetailsFromParCatId " + parcatId.ToString());
DataTable dt = new DataTable();
int nRet1 = sp1.RunQuery(dt);
sp1.Dispose();
foreach (DataRow dr in dt.Rows)
{
int tempcatId = Convert.ToInt32(dr[0]);
int tempparcatId = Convert.ToInt32(dr[1]);
string catName = Convert.ToString(dr[2]);
string catTNUrl = Convert.ToString(dr[3]);
xmlWriter.WriteStartElement("Folder");
xmlWriter.WriteAttributeString("Name", catName);
xmlWriter.WriteAttributeString("Group", "Tour");
xmlWriter.WriteAttributeString("Thumbnail", catTNUrl);
AddToursToChildNode(xmlWriter, tempcatId);
xmlWriter.WriteEndElement();
}
}
private void AddToursToChildNode(XmlWriter xmlWriter, int parcatId)
{
List <Tour> sqlTours = new List <Tour>();
int nRet = GetSQLTourArrayList(sqlTours, "exec spGetWWTToursForDateRangeFromCatId " + parcatId + ", null, null, 0");
foreach (Tour tr in sqlTours)
{
xmlWriter.WriteStartElement("Tour");
xmlWriter.WriteAttributeString("Title", tr.TourTitle);
xmlWriter.WriteAttributeString("ID", tr.TourGuid.ToString());
xmlWriter.WriteAttributeString("Description", tr.TourDescription);
xmlWriter.WriteAttributeString("Classification", "Other");
xmlWriter.WriteAttributeString("AuthorEmail", tr.AuthorEmailAddress);
xmlWriter.WriteAttributeString("Author", tr.AuthorName);
xmlWriter.WriteAttributeString("AuthorUrl", tr.AuthorURL);
xmlWriter.WriteAttributeString("AverageRating", tr.AverageRating.ToString());
xmlWriter.WriteAttributeString("LengthInSecs", tr.LengthInSecs.ToString());
xmlWriter.WriteAttributeString("OrganizationUrl", tr.OrganizationURL);
xmlWriter.WriteAttributeString("OrganizationName", tr.OrganizationName);
WriteTour(xmlWriter, tr);
xmlWriter.WriteEndElement();
}
StoredProc sp1 = new StoredProc(SqlCommandString + parcatId.ToString(), _options.WwtToursDBConnectionString);
DataTable dt = new DataTable();
int nRet1 = sp1.RunQuery(dt);
sp1.Dispose();
foreach (DataRow dr in dt.Rows)
{
int tempcatId = Convert.ToInt32(dr[0]);
int tempparcatId = Convert.ToInt32(dr[1]);
string catName = Convert.ToString(dr[2]);
string catTnUrl = Convert.ToString(dr[3]);
xmlWriter.WriteStartElement("Folder");
xmlWriter.WriteAttributeString("Name", catName);
xmlWriter.WriteAttributeString("Group", "Tour");
xmlWriter.WriteAttributeString("Thumbnail", catTnUrl);
AddToursToChildNode(xmlWriter, tempcatId);
xmlWriter.WriteEndElement();
}
}
public IHttpActionResult GetUserIdOfNameAndPwd(string userName, string pwd, string machineName)
{
try
{
var db = new tabblesEntities();
SqlParameter idUserPar = null;
var add = new Action <SqlCommand>(c =>
{
c.Parameters.AddWithValue("@userName", userName);
c.Parameters.AddWithValue("@pwd", pwd);
c.Parameters.AddWithValue("@machineName", machineName);
idUserPar = addOutputParameterInt("@idUser", c);
});
var res = StoredProc.ExecuteStoredProc("getUserIdOfNameAndPwd", add);
if (res.err == "timeout")
{
return(Ok(new Result {
error = "timeout"
}));
}
else
{
var idUser = (int)idUserPar.Value;
if (idUser == 0)
{
return(Ok(new Result {
ret = "user-not-found"
}));
}
return(Ok(new Result {
ret = idUser
}));
}
}
catch (Exception e)
{
return(Ok(new Result {
error = "generic-error", stringOfExc = Utils.stringOfException(e)
}));
}
}
/// <summary>
/// create/insert the new league woohooo
/// </summary>
private bool InsertLeague(string exportId)
{
var SP = new StoredProc
{
Name = "League_insert", DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString, ParameterSet = new SqlCommand()
};
// don't let greater than 50 characters in there
if (txtLeagueName.Text.Length > 50)
{
txtLeagueName.Text = txtLeagueName.Text.Substring(0, 50);
}
SP.ParameterSet.Parameters.AddWithValue("@leagueName", txtLeagueName.Text); // TODO: USE REGEX ELIMINATE BAD DIGITS
SP.ParameterSet.Parameters.AddWithValue("@exportID", exportId);
SP.ParameterSet.Parameters.AddWithValue("@ownerUserID", User.Identity.GetUserId());
return(StoredProc.NonQuery(SP));
}
private Dictionary <string, int> GetScheduleId
(
int seasonIndex,
int weekIndex,
int teamid,
int stageIndex,
int leagueId
)
{
var sp = new StoredProc
{
Name = "ScheduleId_select",
DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString,
ParameterSet = new SqlCommand()
};
sp.ParameterSet.Parameters.AddWithValue("@stageIndex", stageIndex);
sp.ParameterSet.Parameters.AddWithValue("@seasonIndex", seasonIndex);
sp.ParameterSet.Parameters.AddWithValue("@teamId", teamid);
sp.ParameterSet.Parameters.AddWithValue("@weekindex", weekIndex);
sp.ParameterSet.Parameters.AddWithValue("@leagueId", leagueId);
var ds = StoredProc.ShowMeTheData(sp);
if (ds.Tables.Count == 0)
{
return(null);
}
var dict = new Dictionary <string, int>();
foreach (DataRow row in ds.Tables[0].Rows)
{
dict.Add("scheduleId", Helper.IntegerNull(row["scheduleId"]));
dict.Add("homeTeamId", Helper.IntegerNull(row["homeTeamId"]));
dict.Add("awayTeamId", Helper.IntegerNull(row["awayTeamId"]));
break;
}
return(dict);
}
private void UpdateBtn_Click(object sender, EventArgs e)
{
string oldAWB;
string newAWB;
string packlistID;
oldAWB = currentRow.Cells["WAYBILLID"].Value.ToString();
newAWB = waybillText.Text;
packlistID = packlistText.Text;
StoredProc proc = new StoredProc("usp_UpdatePacklistAWB");
proc.Parameters.AddWithValue("@OldAWB", oldAWB);
proc.Parameters.AddWithValue("@NewAWB", newAWB);
proc.Parameters.AddWithValue("@PacklistID", packlistID);
proc.Execute();
LoadDetails(packlistText.Text);
UpdateBtn.Enabled = false;
}
private void LoadActions()
{
if (m_actions != null)
{
return;
}
m_actions = new ActionList();
m_actions.m_parent = this;
// Load action list for non-administrator roles
if (m_id != 0)
{
Query q = new StoredProc("usp_GetRoleActions");
q.CnnStr = Helpers.Config["DB:Security"];
q.Parameters.AddWithValue("@RoleId", m_id);
IList <int> l = q.ExecuteList <int>();
foreach (int i in l)
{
foreach (Action a in UserSecurity.Actions)
{
if (a.Value == i)
{
m_actions.Add(a);
break;
}
}
}
}
else
{
foreach (Action a in UserSecurity.Actions)
{
m_actions.Add(a);
}
}
m_actions.m_isDirty = false;
}
public BindableCollection <string> GetDataBasesFromInstance(string valueInstance)
{
var resultado = new BindableCollection <string>();
try
{
//var t = _utilities.GetSqlConfigData("DbTerminalContext");
//var t = _utilities.GetIfConnection();
//var t = GetSqlConfigData("DbTerminalContext");
using (var tmp = new DbTerminalContext(_utilities.GetIfConnection()))
{
tmp.Database.Initialize(true);
StoredProc ldbStoredProc =
new StoredProc().HasName("sp_databases").ReturnsTypes(typeof(ListDataBaseResultSet));
ResultsList results = tmp.CallStoredProc(ldbStoredProc);
List <ListDataBaseResultSet> tmplst = results.ToList <ListDataBaseResultSet>();
List <string> x = (from b in tmplst
where
b.DbName != "master" && b.DbName != "msdb" && b.DbName != "model" &&
b.DbName != "IFDB" && b.DbName != "tempdb" &&
b.DbName != "ReportServer$" + valueInstance &&
b.DbName != "ReportServer$" + valueInstance + "TempDB"
orderby b.DbName ascending
select b.DbName).ToList();
if (x.Count > 0)
{
resultado = new BindableCollection <string>(x);
}
}
}
catch (Exception ex)
{
EventAggregationProvider.Aggregator.Publish(new EventoBackend(EventoTipo.Erro, ex.Message));
}
return(resultado);
}
private void FixDraft()
{
StoredProc sp = new StoredProc
{
Name = "SELECT rookieYear, presentationId, portraitId, rosterId FROM tblPlayerProfile WHERE leagueId = 20", IsSqlCommand = true, DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString
};
var ds = StoredProc.ShowMeTheData(sp);
if (ds.Tables.Count == 0)
{
return;
}
foreach (DataRow item in ds.Tables[0].Rows)
{
var updateString = $"UPDATE tblPlayerProfile SET ModifiedOn = GetUTCDate(), rookieYear = {item.Field<int>("rookieYear")} WHERE leagueId = 14 AND presentationId = {item.Field<int>("presentationId")} AND portraitId = {item.Field<int>("portraitId")} AND rosterId = {item.Field<int>("rosterid")}";
var sp2 = new StoredProc
{
Name = updateString,
IsSqlCommand = true,
DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString
};
//sp2.ParameterSet = new System.Data.SqlClient.SqlCommand();
//sp2.ParameterSet.Parameters.AddWithValue("@rookieYear", item.Field<int>("rookieYear"));
//sp2.ParameterSet.Parameters.AddWithValue("@birthDay", item.Field<int>("birthDay"));
//sp2.ParameterSet.Parameters.AddWithValue("@birthMonth", item.Field<int>("birthMonth"));
//sp2.ParameterSet.Parameters.AddWithValue("@rosterId", item.Field<int>("rosterId"));
var status = StoredProc.NonQuery(sp2);
Console.WriteLine(status);
}
}
private string GetExportId(int leagueId)
{
var export = "";
var sp = new StoredProc
{
Name = "League_select",
DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString,
ParameterSet = new SqlCommand()
};
var userId = string.Empty;
if (Request.IsAuthenticated)
{
userId = User.Identity.GetUserId();
}
sp.ParameterSet.Parameters.AddWithValue("@ownerUserID", userId);
var ds = StoredProc.ShowMeTheData(sp);
if (ds.Tables.Count == 0)
{
return(export);
}
foreach (DataRow item in ds.Tables[0].Rows)
{
if (leagueId == item.Field <int>("ID"))
{
export = item.Field <string>("exportId");
break;
}
}
return(export.ToLower());
}
/// <summary>
/// We need the SQL League ID
/// </summary>
private int GetLeagueId(string exportId)
{
var leagueID = 0;
var SP = new StoredProc
{
Name = "LeagueID_select", DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString, ParameterSet = new SqlCommand()
};
SP.ParameterSet.Parameters.AddWithValue("@exportID", exportId);
var ds = StoredProc.ShowMeTheData(SP);
if (ds.Tables.Count == 0)
{
return(0);
}
foreach (DataRow item in ds.Tables[0].Rows)
{
leagueID = Helper.IntegerNull(item["ID"]);
}
return(leagueID);
}
internal static string GetSqlForStoredProceduresColumns(StoredProc sp)
{
var sb = new StringBuilder();
System.Windows.Forms.Application.DoEvents();
sb.AppendLine("SET FMTONLY ON");
sb.Append("EXEC [" + (string.IsNullOrEmpty(sp.Schema) ? "dbo" : sp.Schema) + "].[" + sp.Name + "] ");
foreach (var parameter in sp.ParameterList)
{
if (parameter.DataType == SqlDbType.UniqueIdentifier)
sb.Append("@" + parameter.Name + "='540C6D43-5645-40FB-980F-2FF126BFBD5E'");
else if (parameter.IsTextType())
sb.Append("@" + parameter.Name + "=''");
else if (parameter.IsNumericType())
sb.Append("@" + parameter.Name + "=0");
else if (parameter.IsBinaryType())
sb.Append("@" + parameter.Name + "=0x0");
else if (parameter.DataType == SqlDbType.Bit)
sb.Append("@" + parameter.Name + "=0");
else if (parameter.IsDateType())
sb.Append("@" + parameter.Name + "='2000-01-01'");
else
System.Diagnostics.Debug.Write(string.Empty);
if (sp.ParameterList.IndexOf(parameter) < sp.ParameterList.Count - 1)
sb.Append(", ");
}
sb.AppendLine();
return sb.ToString();
}
private static bool LoadStoredProcedures(Database database, string procName, string connectionString)
{
try
{
var dsSP = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForStoredProcedures(procName));
var dsSPParameter = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForStoredProceduresParameters());
//Add the Stored Procedures
StoredProc customStoredProcedure = null;
foreach (DataRow rowSP in dsSP.Tables[0].Rows)
{
var id = (int)rowSP["id"];
var name = (string)rowSP["name"];
var schema = (string)rowSP["schemaname"];
customStoredProcedure = database.StoredProcList.FirstOrDefault(x => x.Name == name);
if (customStoredProcedure == null)
{
customStoredProcedure = new StoredProc();
customStoredProcedure.Name = name;
customStoredProcedure.SQL = SchemaModelHelper.GetSqlForStoredProceduresBody(schema, name, connectionString);
customStoredProcedure.Schema = schema;
database.StoredProcList.Add(customStoredProcedure);
}
}
//Add the parameters
var sortOrder = 1;
foreach (DataRow rowSP in dsSPParameter.Tables[0].Rows)
{
if (!DatabaseHelper.IsValidSQLDataType((SqlNativeTypes)int.Parse(rowSP["xtype"].ToString())))
{
customStoredProcedure.InError = true;
customStoredProcedure.ParameterList.Clear();
customStoredProcedure.FieldList.Clear();
return false;
}
var id = (int)rowSP["id"];
var spName = (string)rowSP["name"];
var name = (string)rowSP["ColName"];
var typeName = (string)rowSP["ColType"];
var dataType = DatabaseHelper.GetSQLDataType(rowSP["xtype"].ToString(), database.UserDefinedTypes);
var length = int.Parse(rowSP["length"].ToString());
var isOutput = ((int)rowSP["isoutparam"] != 0);
//The length is half the bytes for these types
if ((dataType == SqlDbType.NChar) ||
(dataType == SqlDbType.NVarChar))
{
length = length / 2;
}
if (customStoredProcedure != null)
{
var parameter = new Parameter();
parameter.Name = name.Replace("@", string.Empty);
parameter.SortOrder = sortOrder;
sortOrder++;
parameter.DataType = dataType;
parameter.Length = length;
parameter.Nullable = (int)rowSP["isnullable"] == 1 ? true : false;
parameter.IsOutputParameter = isOutput;
customStoredProcedure.ParameterList.Add(parameter);
}
}
//Try to get the columns
var errorItems = new List<string>();
foreach (var sp in database.StoredProcList)
{
try
{
DataSet dsSPColumn = null;
try
{
dsSPColumn = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForStoredProceduresColumns(sp));
}
catch (Exception)
{
sp.ColumnFailure = true;
}
if ((dsSPColumn != null) && dsSPColumn.Tables.Count > 0)
{
var dt = dsSPColumn.Tables[0];
foreach (DataColumn column in dt.Columns)
{
var newColumn = new Field();
var dataType = Extensions.GetSqlDbType(column.DataType);
var length = newColumn.DataType.ValidateDataTypeMax(1000000);
newColumn.Name = column.ColumnName;
newColumn.DataType = dataType;
newColumn.Nullable = true;
newColumn.Length = length;
if (newColumn.DataType == SqlDbType.Decimal)
{
newColumn.Length = 18;
newColumn.Scale = 4;
}
if (newColumn.DataType == SqlDbType.DateTime2)
{
newColumn.Length = 7;
newColumn.Scale = 0;
}
if (newColumn.DataType == SqlDbType.VarChar)
newColumn.Length = 50;
sp.FieldList.Add(newColumn);
}
}
}
catch (Exception ex)
{
//Do Nothing - Skip to next
if (ex.Message.Contains("Invalid object name '#")) //this is a temp table. it cannot be run so there is nothing we can do
{
//Do Nothing
}
else
{
errorItems.Add(sp.Name);
}
}
}
return true;
}
catch (Exception /*ignored*/)
{
throw;
}
}
public IEnumerable<string> GetStoredProcedureList(string connectionString)
{
var retval = new List<string>();
using (var tableReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlForStoredProcedures()))
{
while (tableReader.Read())
{
var newEntity = new StoredProc();
newEntity.Name = tableReader["name"].ToString();
retval.Add(newEntity.Name);
//newEntity.Schema = tableReader["schema"].ToString();
}
}
return retval;
}
