static public bool CreateStoredProcedure(StoredProcedureName name, SqlConnection conn)
{
StringBuilder sb = new StringBuilder();
string cmdTxt;
switch (name)
{
case StoredProcedureName.uspCheckSynTable:
sb = new StringBuilder("CREATE PROCEDURE [dbo].[uspCheckSyncTable] AS\r\n");
sb.AppendLine("BEGIN");
sb.AppendLine("Declare @tableName nvarchar(50)");
sb.AppendLine("DECLARE @program nvarchar(512)");
sb.AppendLine("DECLARE @maxID int");
sb.AppendLine("DECLARE @result int");
sb.AppendLine("SET NOCOUNT ON");
sb.AppendLine("DECLARE My_Cursor Cursor");
sb.AppendLine("FOR (Select SO.name from sysobjects SO where SO.xtype ='U' and SO.status>= 0) ORDER BY SO.name");
sb.AppendLine("OPEN My_Cursor");
sb.AppendLine("FETCH NEXT FROM My_Cursor INTO @tableName");
sb.AppendLine("WHILE @@FETCH_STATUS =0");
sb.AppendLine("BEGIN");
sb.AppendLine(" IF (@tableName='BankDetail') or ((@tableName<>'InventoryProducts') and (@tableName not like '%Item') and (@tableName not like 'Sync%') and (@tableName not like '%Detail'))");
sb.AppendLine(" BEGIN");
sb.AppendLine(" SET @result=(SELECT Count(Name) from SyncTable where Name=@tableName)");
sb.AppendLine(" IF (@result<=0)");
sb.AppendLine(" BEGIN");
sb.AppendLine(" Set @maxID=ISNULL((SELECT MAX(TableID) from SyncTable),0)+1");
sb.AppendLine(" INSERT INTO [dbo].[SyncTable] ([TableID],[Name]) Values(@maxID,@tableName)");
sb.AppendLine(" END");
sb.AppendLine(" END");
sb.AppendLine(" FETCH NEXT FROM My_Cursor INTO @tableName");
sb.AppendLine("END");
sb.AppendLine("CLOSE My_Cursor");
sb.AppendLine("DEALLOCATE My_Cursor");
sb.AppendLine("END");
cmdTxt = sb.ToString();
break;
default: MessageBox.Show("不知名字的Stored Procedure 程式錯誤!");
return(false);
}
try
{
SqlCommand sc = new SqlCommand(cmdTxt, conn);
sc.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("創建Stored Procedure<" + name.ToString() + ">失敗,原因:" + ex.Message);
return(false);
}
return(true);
}
/// <inheritdoc/>
public string ToDelimitedString()
{
CultureInfo culture = CultureInfo.CurrentCulture;
return(string.Format(
culture,
StringHelper.StringFormatSequence(0, 5, Configuration.FieldSeparator),
Id,
QueryTag,
QueryResponseFormatCode,
StoredProcedureName?.ToDelimitedString(),
InputParameterList != null ? string.Join(Configuration.FieldRepeatSeparator, InputParameterList.Select(x => x.ToDelimitedString())) : null
).TrimEnd(Configuration.FieldSeparator.ToCharArray()));
}
/// <summary>
/// Common method to bind parameters with Stored procedure
/// </summary>
/// <returns> DataTable</returns>
public DataTable Execute()
{
try
{
if (!string.IsNullOrEmpty(StoredProcedureName.Trim()))
{
sqlCommand = new System.Data.SqlClient.SqlCommand(StoredProcedureName);
foreach (var parameter in SQLParameters)
{
sqlCommand.Parameters.AddWithValue("@" + parameter.Key, parameter.Value);
}
return(ExecuteProcedure(sqlCommand));
}
else
{
throw new ArgumentNullException();
}
}
catch
{
throw;
}
}
/// <summary>
/// Pega o nome da storedprocedure.
/// </summary>
/// <param name="storedProcedureName">Nome da stored procedure.</param>
/// <returns></returns>
public virtual ITranslatedName GetName(StoredProcedureName storedProcedureName)
{
return(new TranslatedStoredProcedureName(storedProcedureName.Name, storedProcedureName.Schema));
}
public override ITranslatedName GetName(StoredProcedureName a)
{
return(new TranslatedStoredProcedureName((a.Name ?? "").ToUpper(), (a.Schema ?? "").ToUpper()));
}
/// <summary>
/// Recupera o nome do provedor associado com o nom e da storedprocedure.
/// </summary>
/// <param name="storedProcedureName">Nome do storedprocedure.</param>
/// <returns></returns>
public string GetProviderName(StoredProcedureName storedProcedureName)
{
return(GDA.GDASettings.DefaultProviderName);
}
