/// <summary>
/// SQLのパラメータを追加します。サニタイジング(SQLインジェクション対策)をします
/// </summary>
/// <param name="cmd"></param>
/// <param name="direction"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="value">値がnullの場合はDBNull.Valueを挿入します</param>
public static void AddSqlParameter(SqlCommand cmd, ParameterDirection direction, string paramName, SqlDbType type, Object value)
{
if (cmd == null)
{
return;
}
if (string.IsNullOrEmpty(paramName) || string.IsNullOrWhiteSpace(paramName))
{
return;
}
if (value == null)
{
value = DBNull.Value;
}
SqlParameter param = cmd.CreateParameter();
param.ParameterName = paramName;
param.SqlDbType = type;
param.Direction = direction;
param.Value = value;
cmd.Parameters.Add(param);
}
/// <summary>
/// 매개 변수를 사용하여 <c>SqlParameter</c>클래스의 새 인스턴스를 반환합니다.
/// </summary>
/// <param name="parameterName">매핑할 매개 변수의 이름</param>
/// <param name="dbType"><c>SqlDbType</c>값 중 하나</param>
/// <param name="size">매개 변수의 길이</param>
/// <param name="sourceColumn">소스 열의 이름</param>
/// <returns><c>SqlParameter</c>클래스의 새 인스턴스</returns>
public static SqlParameter CreateParameter(string parameterName, SqlDbType dbType, int size, string sourceColumn)
{
SqlParameter ret = CreateParameter(parameterName, dbType);
ret.Size = size;
ret.SourceColumn = sourceColumn;
return ret;
}
public static Type SqlType2CsharpType(SqlDbType sqlType)
{
switch (sqlType)
{
case SqlDbType.BigInt:
return typeof(Int64);
case SqlDbType.Binary:
return typeof(Object);
case SqlDbType.Bit:
return typeof(Boolean);
case SqlDbType.Char:
return typeof(String);
case SqlDbType.DateTime:
return typeof(DateTime);
case SqlDbType.Decimal:
return typeof(Decimal);
case SqlDbType.Float:
return typeof(Double);
case SqlDbType.Image:
return typeof(Object);
case SqlDbType.Int:
return typeof(Int32);
case SqlDbType.Money:
return typeof(Decimal);
case SqlDbType.NChar:
return typeof(String);
case SqlDbType.NText:
return typeof(String);
case SqlDbType.NVarChar:
return typeof(String);
case SqlDbType.Real:
return typeof(Single);
case SqlDbType.SmallDateTime:
return typeof(DateTime);
case SqlDbType.SmallInt:
return typeof(Int16);
case SqlDbType.SmallMoney:
return typeof(Decimal);
case SqlDbType.Text:
return typeof(String);
case SqlDbType.Timestamp:
return typeof(Object);
case SqlDbType.TinyInt:
return typeof(Byte);
case SqlDbType.Udt:
return typeof(Object);
case SqlDbType.UniqueIdentifier:
return typeof(Guid);
case SqlDbType.VarBinary:
return typeof(Object);
case SqlDbType.VarChar:
return typeof(String);
case SqlDbType.Variant:
return typeof(Object);
case SqlDbType.Xml:
return typeof(Object);
default:
return null;
}
}
/// <summary>
/// Metodo constructor con todos los campos asignados
/// </summary>
/// <param name="etiqueta">etiqueta del parametro ejemplo: @nombreDeUsuario</param>
/// <param name="tipoDato">SqlDbType con el tipo de dato del parametro
/// ejemplo: SqlDbType.VarChar</param>
/// <param name="valor">valor: string con el valor que se le asigno al
/// parametro ejemplo: Pepe</param>
/// <param name="esOutput">esOutput: si es un parametro de output: true, si no: false</param>
public Parametro(string etiqueta, SqlDbType tipoDato, string valor, bool esOutput)
{
this.etiqueta = etiqueta;
this.tipoDato = tipoDato;
this.valor = valor;
this.esOutput = esOutput;
}
/// <summary>
/// 매개 변수를 사용하여 <c>SqlParameter</c>클래스의 새 인스턴스를 반환합니다.
/// </summary>
/// <param name="parameterName">매핑할 매개 변수의 이름</param>
/// <param name="dbType"><c>SqlDbType</c>값 중 하나</param>
/// <param name="size">매개 변수의 길이</param>
/// <param name="direction"><c>ParameterDirection</c>값 중 하나</param>
/// <returns><c>SqlParameter</c>클래스의 새 인스턴스</returns>
public static SqlParameter CreateParameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction)
{
SqlParameter ret = CreateParameter(parameterName, dbType);
ret.Direction = direction;
ret.Size = size;
return ret;
}
public void AddParameter(string paramName, SqlDbType paramType, object paramValue)
{
SqlParameter prm = new SqlParameter(paramName, paramType);
prm.Direction = ParameterDirection.Input;
prm.Value = paramValue;
m_Cmd.Parameters.Add(prm);
}
public static object SqlValue(this string strVal, SqlDbType type)
{
if (type == SqlDbType.DateTime)
return DateTime.Parse(strVal);
return strVal;
}
private SqlDataType(SqlDbType type, int? length, int? precision, int? scale)
{
_Type = type;
_Length = length;
_Precision = precision;
_Scale = scale;
}
public void SetOutParam(string ParamName, SqlDbType Sqltype)
{
//Initialize Sql parameter
m_Param = new SqlParameter();
//Set parameter name
m_Param.ParameterName = ParamName;
//Set parameter datatype
m_Param.SqlDbType = Sqltype;
//Check Out param type
switch (Sqltype)
{
//If outparam type is varchar
case SqlDbType.VarChar:
case SqlDbType.NVarChar:
{
//Set default size
m_Param.Size = 1000;
break;
}
}
//Set parameter direction as output
m_Param.Direction = ParameterDirection.Output;
//Add parameter in hashtable
SetParam(m_Param);
}
public SqlParam(string paramName, object paramValue, ParameterDirection paramDirection, SqlDbType type)
{
Name = paramName;
Direction = paramDirection;
Value = paramValue;
Type = type;
}
public SqlField(int tableIndex, string fieldName, SqlDbType dataType, bool visible)
{
this.tabIndex = tableIndex;
this.fldName = fieldName;
this.dbType = dataType;
this.visible = visible;
}
public StoredProcedureParameterAttribute(SqlDbType dataType, StoredProcedureParameterOptions options, int size)
{
DataType = dataType;
Options = options;
Direction = ParameterDirection.Input;
Size = size;
}
/// <summary>
/// Create new Store proc parameter.
/// </summary>
/// <param name="paramName">Sql parameter name.</param>
/// <param name="paramType"><see cref="SqlDbType"/> parameter type.</param>
/// <param name="size"><see cref="Int32"/> sql parameter size - use with string.</param>
/// <param name="usedFor">Array of <see cref="SqlStatementType"/> values to determine the SP usage.</param>
internal StoredProcParameter(string paramName,
SqlDbType paramType,
int size,
SqlStatementType[] usedFor)
: this(new SqlParameter(paramName, paramType, size), usedFor)
{
}
/// <summary>
/// Create new Store proc parameter.
/// </summary>
/// <param name="paramName">Sql parameter name.</param>
/// <param name="paramType"><see cref="SqlDbType"/> parameter type.</param>
/// <param name="paramSize"><see cref="Int32"/> sql parameter size - use with string.</param>
/// <param name="usedFor1">Identifies the purpose of the parameter.</param>
internal StoredProcParameter(string paramName,
SqlDbType paramType,
int paramSize,
SqlStatementType usedFor1)
: this(paramName, paramType, paramSize, new[] {usedFor1})
{
}
public string Create(string name, SqlDbType sqlDbType, long? length = null)
{
var lStr = length.HasValue
? string.Format("({0})", length.Value)
: string.Empty;
return string.Format("\"{0}\" {1}{2}", name.Trim(), sqlDbType.ToString().ToLower(), lStr);
}
/// <summary>
/// Output Ÿ���� Parameter
/// </summary>
/// <param name="paramName">�Ķ���� �̸�</param>
/// <param name="type">SqlDbType</param>
/// <param name="size">ũ��</param>
/// <param name="val">�Ķ���� ��</param>
/// <returns></returns>
protected static SqlParameter CreateOutParam(string paramName, SqlDbType type, int size)
{
SqlParameter param = new SqlParameter( paramName, type, size );
param.Direction = ParameterDirection.Output;
return param;
}
public SqlMetaData (string name, SqlDbType type, byte precision, byte scale)
{
this.name = name;
this.precision = precision;
this.scale = scale;
this.sqlDbType = type;
}
public SqlCeParameter AddInParameter(string name, object val, SqlDbType type)
{
SqlCeParameter sp = new SqlCeParameter(name, val);
sp.SqlDbType = type;
return sp;
}
///<summary>
///</summary>
///<param name="paramName"></param>
///<param name="dbType"></param>
///<param name="size"></param>
///<param name="direction"></param>
///<param name="value"></param>
///<returns></returns>
///<exception cref="ArgumentOutOfRangeException"></exception>
public static SqlParameter MakeParam(string paramName, SqlDbType dbType, int size, ParameterDirection direction, object value)
{
SqlParameter sqlParameter = null;
try
{
paramName = paramName ?? string.Empty;
//modify reason:验证花时比较大
//if (!MathUtils.IsMachVarName(paramName))
//{
// throw new ArgumentOutOfRangeException("paramName", "参数名格式不正确");
//}
if (size > 0)
{
sqlParameter = new SqlParameter(FormatParamName(paramName), dbType, size);
}
else
{
sqlParameter = new SqlParameter(FormatParamName(paramName), dbType);
}
sqlParameter.Direction = direction;
if (direction != ParameterDirection.Output || value != null)
{
sqlParameter.Value = value;
}
}
catch (Exception ex)
{
TraceLog.WriteError("{0}", ex);
}
return sqlParameter;
}
/// <summary>
/// ����� ���� Parameter
/// </summary>
/// <param name="paramName">�Ķ���� �̸�</param>
/// <param name="type">SqlDbType</param>
/// <param name="size">ũ��</param>
/// <param name="val">�Ķ���� ��</param>
/// <returns></returns>
protected static SqlParameter CreateInParam(string paramName, SqlDbType type, int size, object val)
{
SqlParameter param = new SqlParameter( paramName, type, size );
param.Value = val;
return param;
}
public SqlFieldAttribute(string fieldName, int fieldLength, SqlDbType sqlDbType, bool needsSpellCheck)
{
this.m_FieldName = fieldName;
this.m_FieldLength = fieldLength;
this.m_SqlDbType = sqlDbType;
this.m_NeedsSpellCheck = needsSpellCheck;
}
internal SmiExtendedMetaData(SqlDbType dbType, long maxLength, byte precision, byte scale, long localeId, SqlCompareOptions compareOptions, Type userDefinedType, string udtAssemblyQualifiedName, bool isMultiValued, IList<SmiExtendedMetaData> fieldMetaData, SmiMetaDataPropertyCollection extendedProperties, string name, string typeSpecificNamePart1, string typeSpecificNamePart2, string typeSpecificNamePart3) : base(dbType, maxLength, precision, scale, localeId, compareOptions, userDefinedType, udtAssemblyQualifiedName, isMultiValued, fieldMetaData, extendedProperties)
{
this._name = name;
this._typeSpecificNamePart1 = typeSpecificNamePart1;
this._typeSpecificNamePart2 = typeSpecificNamePart2;
this._typeSpecificNamePart3 = typeSpecificNamePart3;
}
protected SqlParameter genSqlParameter(String paramName, SqlDbType type, int size, Object value)
{
if (value == null)
value = DBNull.Value;
SqlParameter param = null;
if (type == SqlDbType.DateTime)
{
if (value == DBNull.Value)
{
param = new SqlParameter(paramName, value);
}
else
{
param = new SqlParameter(paramName, ((Nullable<DateTime>)value).Value);
}
}
else
{
param = new SqlParameter(paramName, type, size);
param.Value = value;
}
return param;
}
/// <summary>
/// Método genérico que chama os métodos correspondentes ao Tipo do Banco para execução da Procedure.
/// </summary>
/// <param name="query"></param>
/// <param name="tipoBanco"></param>
/// <returns></returns>
public bool executaProc(string nameProc, TipoBanco tipoBanco, object[] values, string[] parametros, SqlDbType[] sqlDBType)
{
bool bRet = false;
try
{
switch (tipoBanco)
{
case TipoBanco.SQLServer:
bRet = executaProcSQL(nameProc, values, parametros, sqlDBType);
break;
case TipoBanco.Oracle:
break;
case TipoBanco.OLDB:
break;
default:
break;
}
}
catch (Exception ex)
{
bRet = false;
throw ex;
}
finally
{
}
return bRet;
}
/// <summary>
/// Sets up a parameter for the query
/// </summary>
/// <param name="id">The ID of the parameter</param>
/// <param name="type">The Sql type of the parameter</param>
/// <param name="Value">The value of the parameter</param>
public static void AddParameter(this List<SqlParameter> collection, string parameterName, SqlDbType type, Object Value)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = parameterName;
parameter.SqlDbType = type;
if (Value == null)
{
parameter.Value = Convert.DBNull;
}
else if (Value.ToString() == "" && type != SqlDbType.VarChar)
{
// must convert the empty string to a DBNull
parameter.Value = Convert.DBNull;
}
else if (Value.ToString() == "" && (type == SqlDbType.Float || type == SqlDbType.Int || type == SqlDbType.Money))
{
parameter.Value = 0;
}
else
{
// set the value of the parameter
parameter.Value = Value;
}
collection.Add(parameter);
}
public MetaType(byte precision, byte scale, int fixedLength, bool isFixed, bool isLong, bool isPlp, byte tdsType, byte nullableTdsType, string typeName, Type classType, Type sqlType, SqlDbType sqldbType, DbType dbType, byte propBytes) {
this.Precision = precision;
this.Scale = scale;
this.FixedLength = fixedLength;
this.IsFixed = isFixed;
this.IsLong = isLong;
this.IsPlp = isPlp;
// can we get rid of this (?just have a mapping?)
this.TDSType = tdsType;
this.NullableType = nullableTdsType;
this.TypeName = typeName;
this.SqlDbType = sqldbType;
this.DbType = dbType;
this.ClassType = classType;
this.SqlType = sqlType;
this.PropBytes = propBytes;
IsAnsiType = _IsAnsiType(sqldbType);
IsBinType = _IsBinType(sqldbType);
IsCharType = _IsCharType(sqldbType);
IsNCharType = _IsNCharType(sqldbType);
IsSizeInCharacters = _IsSizeInCharacters(sqldbType);
IsNewKatmaiType = _IsNewKatmaiType(sqldbType);
IsVarTime = _IsVarTime(sqldbType);
Is70Supported = _Is70Supported(SqlDbType);
Is80Supported = _Is80Supported(SqlDbType);
Is90Supported = _Is90Supported(SqlDbType);
Is100Supported = _Is100Supported(SqlDbType);
}
public DbParam(String paramName, Object paramValue, SqlDbType paramType)
{
ParamName = paramName;
ParamValue = paramValue;
ParamType = paramType;
ParamDirection = ParameterDirection.Input;
}
public static DbParameter CreateParameter(SqlDbType dbType)
{
return new SqlParameter
{
SqlDbType = dbType
};
}
//thoai Add sql parameter
protected static SqlParameter AddSqlPrameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction, object value)
{
SqlParameter parameter = new SqlParameter(parameterName, dbType, size);
parameter.Direction = direction;
parameter.Value = value;
return parameter;
}
public static string ConvertToSystemDataType(SqlDbType sqlDbType)
{
switch (sqlDbType)
{
case SqlDbType.BigInt:
return "System.Int64";
case SqlDbType.Bit:
return "System.Boolean";
case SqlDbType.DateTime:
case SqlDbType.SmallDateTime:
return "System.DateTime";
case SqlDbType.Decimal:
case SqlDbType.Float:
case SqlDbType.Money:
case SqlDbType.Real:
case SqlDbType.SmallMoney:
return "System.Decimal";
case SqlDbType.Int:
return "System.Int32";
case SqlDbType.UniqueIdentifier:
return "System.Guid";
case SqlDbType.SmallInt:
return "System.Int16";
case SqlDbType.TinyInt:
return "System.Byte";
}
return "System.String";
}
public abstract DbParameter CreateParameter(string parameterName, SqlDbType type, string udtTypeName, bool nullable, object value);
public bool ExecuteNonQuery(string sNombre_SP, DataTable dtParametros, ref string sMsjError)
{
cls_BD_DAL obj_BD_DAL = new cls_BD_DAL();
try
{
obj_BD_DAL.sCadena_Conexion = ConfigurationManager.ConnectionStrings["WIN_AUT"].ConnectionString.ToString().Trim();
obj_BD_DAL.obj_sql_cnx = new SqlConnection(obj_BD_DAL.sCadena_Conexion);
if (obj_BD_DAL.obj_sql_cnx.State == ConnectionState.Closed)
{
obj_BD_DAL.obj_sql_cnx.Open();
}
obj_BD_DAL.obj_sql_cmd = new SqlCommand(sNombre_SP, obj_BD_DAL.obj_sql_cnx);
obj_BD_DAL.obj_sql_cmd.CommandType = CommandType.StoredProcedure;
if (dtParametros.Rows.Count > 0)
{
foreach (DataRow dr in dtParametros.Rows)
{
SqlDbType dbt = SqlDbType.VarChar;
switch (dr[1].ToString())
{
case "1":
{
dbt = SqlDbType.Int;
break;
}
case "2":
{
dbt = SqlDbType.VarChar;
break;
}
case "3":
{
dbt = SqlDbType.NVarChar;
break;
}
case "4":
{
dbt = SqlDbType.Char;
break;
}
case "5":
{
dbt = SqlDbType.NChar;
break;
}
case "6":
{
dbt = SqlDbType.Decimal;
break;
}
case "7":
{
dbt = SqlDbType.DateTime;
break;
}
case "8":
{
dbt = SqlDbType.TinyInt;
break;
}
case "9":
{
dbt = SqlDbType.BigInt;
break;
}
case "10":
{
dbt = SqlDbType.SmallInt;
break;
}
case "11":
{
dbt = SqlDbType.Money;
break;
}
default:
dbt = SqlDbType.VarChar;
break;
}
obj_BD_DAL.obj_sql_cmd.Parameters.Add(dr[0].ToString(), dbt).Value = dr[2].ToString();
}
obj_BD_DAL.obj_sql_cmd.ExecuteNonQuery();
}
sMsjError = String.Empty;
return(true);
}
catch (Exception ex)
{
sMsjError = ex.Message.ToString();
return(false);
}
finally
{
if (obj_BD_DAL.obj_sql_cnx != null)
{
if (obj_BD_DAL.obj_sql_cnx.State == ConnectionState.Open)
{
obj_BD_DAL.obj_sql_cnx.Close();
}
obj_BD_DAL.obj_sql_cnx.Dispose();
}
}
}
// Constructors
public SqlDbTypeSize(SqlDbType sqlDbType, int size = -1)
{
SqlDbType = sqlDbType;
Size = size;
}
public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection)
{
string username = (string)context["UserName"];
bool userIsAuthenticated = (bool)context["IsAuthenticated"];
if (username == null || username.Length < 1 || collection.Count < 1)
{
return;
}
SqlConnection conn = null;
SqlDataReader reader = null;
SqlCommand cmd = null;
try {
bool anyItemsToSave = false;
// First make sure we have at least one item to save
foreach (SettingsPropertyValue pp in collection)
{
if (pp.IsDirty)
{
if (!userIsAuthenticated)
{
bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
if (!allowAnonymous)
{
continue;
}
}
anyItemsToSave = true;
break;
}
}
if (!anyItemsToSave)
{
return;
}
conn = new SqlConnection(_sqlConnectionString);
conn.Open();
List <ProfileColumnData> columnData = new List <ProfileColumnData>(collection.Count);
foreach (SettingsPropertyValue pp in collection)
{
if (!userIsAuthenticated)
{
bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
if (!allowAnonymous)
{
continue;
}
}
//Normal logic for original SQL provider
//if (!pp.IsDirty && pp.UsingDefaultValue) // Not fetched from DB and not written to
//Can eliminate unnecessary updates since we are using a table though
if (!pp.IsDirty)
{
continue;
}
string persistenceData = pp.Property.Attributes["CustomProviderData"] as string;
// If we can't find the table/column info we will ignore this data
if (String.IsNullOrEmpty(persistenceData))
{
// REVIEW: Perhaps we should throw instead?
continue;
}
string[] chunk = persistenceData.Split(new char[] { ';' });
if (chunk.Length != 2)
{
// REVIEW: Perhaps we should throw instead?
continue;
}
string columnName = chunk[0];
// REVIEW: Should we ignore case?
SqlDbType datatype = (SqlDbType)Enum.Parse(typeof(SqlDbType), chunk[1], true);
object value = null;
// REVIEW: Is this handling null case correctly?
if (pp.Deserialized && pp.PropertyValue == null) // is value null?
{
value = DBNull.Value;
}
else
{
value = pp.PropertyValue;
}
// REVIEW: Might be able to ditch datatype
columnData.Add(new ProfileColumnData(columnName, pp, value, datatype));
}
// Figure out userid, if we don't find a userid, go ahead and create a user in the aspnetUsers table
Guid userId = Guid.Empty;
cmd = new SqlCommand("SELECT u.UserId FROM vw_aspnet_Users u WHERE u.ApplicationId = '" + AppId + "' AND u.UserName = LOWER(@Username)", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Username", username);
try {
reader = cmd.ExecuteReader();
if (reader.Read())
{
userId = reader.GetGuid(0);
}
else
{
reader.Close();
cmd.Dispose();
reader = null;
cmd = new SqlCommand("dbo.aspnet_Users_CreateUser", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationId", AppId);
cmd.Parameters.AddWithValue("@UserName", username);
cmd.Parameters.AddWithValue("@IsUserAnonymous", !userIsAuthenticated);
cmd.Parameters.AddWithValue("@LastActivityDate", DateTime.UtcNow);
cmd.Parameters.Add(CreateOutputParam("@UserId", SqlDbType.UniqueIdentifier, 16));
cmd.ExecuteNonQuery();
userId = (Guid)cmd.Parameters["@userid"].Value;
}
}
finally {
if (reader != null)
{
reader.Close();
reader = null;
}
cmd.Dispose();
}
// Figure out if the row already exists in the table and use appropriate SELECT/UPDATE
cmd = new SqlCommand(String.Empty, conn);
StringBuilder sqlCommand = new StringBuilder("IF EXISTS (SELECT 1 FROM ").Append(_table);
sqlCommand.Append(" WHERE UserId = @UserId) ");
cmd.Parameters.AddWithValue("@UserId", userId);
// Build up strings used in the query
StringBuilder columnStr = new StringBuilder();
StringBuilder valueStr = new StringBuilder();
StringBuilder setStr = new StringBuilder();
int count = 0;
foreach (ProfileColumnData data in columnData)
{
columnStr.Append(", ");
valueStr.Append(", ");
columnStr.Append(data.ColumnName);
string valueParam = "@Value" + count;
valueStr.Append(valueParam);
cmd.Parameters.AddWithValue(valueParam, data.Value);
// REVIEW: Can't update Timestamps?
if (data.DataType != SqlDbType.Timestamp)
{
if (count > 0)
{
setStr.Append(",");
}
setStr.Append(data.ColumnName);
setStr.Append("=");
setStr.Append(valueParam);
}
++count;
}
columnStr.Append(",LastUpdatedDate ");
valueStr.Append(",@LastUpdatedDate");
setStr.Append(",LastUpdatedDate=@LastUpdatedDate");
cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow);
sqlCommand.Append("BEGIN UPDATE ").Append(_table).Append(" SET ").Append(setStr.ToString());
sqlCommand.Append(" WHERE UserId = '").Append(userId).Append("'");
sqlCommand.Append("END ELSE BEGIN INSERT ").Append(_table).Append(" (UserId").Append(columnStr.ToString());
sqlCommand.Append(") VALUES ('").Append(userId).Append("'").Append(valueStr.ToString()).Append(") END");
cmd.CommandText = sqlCommand.ToString();
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
// Need to close reader before we try to update
if (reader != null)
{
reader.Close();
reader = null;
}
UpdateLastActivityDate(conn, userId);
}
finally {
if (reader != null)
{
reader.Close();
}
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null)
{
conn.Close();
}
}
}
public ProfileColumnData(string col, SettingsPropertyValue pv, object val, SqlDbType type)
{
EnsureValidTableOrColumnName(col);
ColumnName = col;
PropertyValue = pv;
Value = val;
DataType = type;
}
private void GetProfileDataFromTable(SettingsPropertyCollection properties, SettingsPropertyValueCollection svc, string username, SqlConnection conn)
{
List <ProfileColumnData> columnData = new List <ProfileColumnData>(properties.Count);
StringBuilder commandText = new StringBuilder("SELECT u.UserID");
SqlCommand cmd = new SqlCommand(String.Empty, conn);
int columnCount = 0;
foreach (SettingsProperty prop in properties)
{
SettingsPropertyValue value = new SettingsPropertyValue(prop);
svc.Add(value);
string persistenceData = prop.Attributes["CustomProviderData"] as string;
// If we can't find the table/column info we will ignore this data
if (String.IsNullOrEmpty(persistenceData))
{
// REVIEW: Perhaps we should throw instead?
continue;
}
string[] chunk = persistenceData.Split(new char[] { ';' });
if (chunk.Length != 2)
{
// REVIEW: Perhaps we should throw instead?
continue;
}
string columnName = chunk[0];
// REVIEW: Should we ignore case?
SqlDbType datatype = (SqlDbType)Enum.Parse(typeof(SqlDbType), chunk[1], true);
columnData.Add(new ProfileColumnData(columnName, value, null /* not needed for get */, datatype));
commandText.Append(", ");
commandText.Append("t." + columnName);
++columnCount;
}
commandText.Append(" FROM " + _table + " t, vw_aspnet_Users u WHERE u.ApplicationId = '").Append(AppId);
commandText.Append("' AND u.UserName = LOWER(@Username) AND t.UserID = u.UserID");
cmd.CommandText = commandText.ToString();
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Username", username);
SqlDataReader reader = null;
try {
reader = cmd.ExecuteReader();
//If no row exists in the database, then the default Profile values
//from configuration are used.
if (reader.Read())
{
Guid userId = reader.GetGuid(0);
for (int i = 0; i < columnData.Count; ++i)
{
object val = reader.GetValue(i + 1);
ProfileColumnData colData = columnData[i];
SettingsPropertyValue propValue = colData.PropertyValue;
//Only initialize a SettingsPropertyValue for non-null values
if (!(val is DBNull || val == null))
{
propValue.PropertyValue = val;
propValue.IsDirty = false;
propValue.Deserialized = true;
}
}
// need to close reader before we try to update the user
if (reader != null)
{
reader.Close();
reader = null;
}
UpdateLastActivityDate(conn, userId);
}
}
finally {
if (reader != null)
{
reader.Close();
}
}
}
public void AddParameterWithValue(SqlCommand command, string parameterName, SqlDbType dbType, object value)
{
command.Parameters.AddWithValue(parameterName, value);
}
public void AddParameter(SqlCommand command, string parameterName, SqlDbType dbType)
{
command.Parameters.Add(parameterName, dbType);
}
/*Metodo para agregar los parametros a enviar al Procedure dentro de la BD*/
public static void Agrega_parametro(ref SqlDataAdapter sql_data_adapter, string nombre_parametro, string valor_parametro, SqlDbType tipo_dato)
{
SqlParameter param = new SqlParameter();
param.ParameterName = nombre_parametro;
param.Value = valor_parametro;
param.SqlDbType = tipo_dato;
sql_data_adapter.SelectCommand.Parameters.Add(param);
}
/*Metodo de la estrutura de los metodos para la instancia a los procedure*/
public static void agregar_datos_estructura_parametros(ref ParamStruct[] parametros, int posicion, string nombre_parametro, SqlDbType tipo_dato_parametro, object valor_parametro)
{
parametros[posicion].Nombre_Parametro = nombre_parametro.ToString();
parametros[posicion].Tipo_Dato = tipo_dato_parametro;
parametros[posicion].Valor_Parametro = valor_parametro;
}
protected static bool IsDate(SqlDbType type)
{
return(type == SqlDbType.Date || type == SqlDbType.DateTime || type == SqlDbType.DateTime2 || type == SqlDbType.SmallDateTime);
}
public SqlDataType(SqlDbType sqlDbType, string sqlDataTypeName, string cSharpTypeName)
{
SqlDbType = sqlDbType;
SqlDataTypeName = sqlDataTypeName;
CSharpTypeName = cSharpTypeName;
}
// Internal Methods
/// <summary>
/// Try to get the SQL Metadata for this SqlDbTypeSize.
/// Note that for some types it would be possible for a metadata to be returned here, but they are not.
/// This is because it makes more sense to send it to SQL Server as varchar in order to not lose precision or scale.
/// These should be handled before calling this method.
/// </summary>
internal bool tryToSqlMetaData(string name, out SqlMetaData metaData)
{
// SqlMetaData requires a size the following DbTypes (from https://msdn.microsoft.com/en-us/library/ms127243(v=vs.110).aspx):
// Binary, Char, Image, NChar, Ntext, NVarChar, Text, VarBinary, VarChar
// It will get strongly typed by SQL Server later because of the table type being specified.
// The following must not be supplied with a length
// Bit, BigInt, DateTime, Decimal, Float, Int, Money, Numeric (doesn't exist in .NET), SmallDateTime,
// SmallInt, SmallMoney, TimeStamp, TinyInt, UniqueIdentifier, Xml
// Inferred types (that aren't in either of the lists above) appear to be:
// DateTimeOffset, Time, Variant
// So this method tries to convert types where possible to the first or second list, except
// where it would be preferable to let SqlMetaData.InferFromValue handle it to get a type from the
// second list
bool success = false;
SqlDbType type = SqlDbType;
int size = Size;
bool useSize = true;
object inferFrom = null;
switch (type)
{
/*
* Any input type accepted with size
*/
case SqlDbType.Binary:
case SqlDbType.Char:
case SqlDbType.Image:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.VarBinary:
case SqlDbType.VarChar:
success = true;
break;
// This is a guess & is untested
case SqlDbType.Udt:
type = SqlDbType.VarBinary;
success = true;
break;
/*
* Any input type already accepted without size
*/
case SqlDbType.Bit:
case SqlDbType.BigInt:
case SqlDbType.DateTime:
case SqlDbType.Decimal:
case SqlDbType.Float:
case SqlDbType.Int:
case SqlDbType.Money:
case SqlDbType.SmallDateTime:
case SqlDbType.SmallInt:
case SqlDbType.SmallMoney:
case SqlDbType.Timestamp:
case SqlDbType.TinyInt:
case SqlDbType.UniqueIdentifier:
case SqlDbType.Xml:
success = true;
useSize = false;
break;
// Real is equivelant to float
case SqlDbType.Real:
type = SqlDbType.Float;
success = true;
useSize = false;
break;
/*
* Inferred
*/
case SqlDbType.DateTimeOffset:
success = true;
inferFrom = new DateTimeOffset();
break;
case SqlDbType.Time:
success = true;
inferFrom = new TimeSpan(1, 0, 0);
break;
case SqlDbType.Variant:
success = true;
inferFrom = new object();
break;
/*
* Unsupported
*/
// Structured is used to send DataTables from .NET to SQL Server as a User-defined table type
case SqlDbType.Structured:
throw new InvalidOperationException(
"Cannot use Structured SqlDbType in SqlMetaData and there is no equivelant");
}
// If we've successfully determined what to do to get the meta data, do so
if (success)
{
// If we're inferring from a value
if (inferFrom != null)
{
metaData = SqlMetaData.InferFromValue(inferFrom, name);
}
// Else if we can constuct the SqlMetaData and must supply a length
else if (useSize)
{
metaData = new SqlMetaData(name, type, size);
}
// Otherwise we can construct the SqlMetaData and must not supply a length
else
{
metaData = new SqlMetaData(name, type);
}
}
else // Otherwise, set to null, and log
{
metaData = null;
Logging.Write("Couldn't calculate the SqlMetaData for SqlDbType {0}. " +
"Please open a bug at https://github.com/JoshKeegan/SqlServerHelpers/issues with the SqlDbType in question and code to reproduce this.",
type);
}
return(success);
}
public abstract MemberInitExpression ParameterFactory(Expression parameterName, SqlDbType type, string udtTypeName, bool nullable, Expression value);
//公有方法，实例化一个用于调用存储过程的输入参数
//输入：
// ParamName - 数据库存储过程的参数名称
// DbType - 参数类型
// Size - 参数大小
// Value - 传给参数的值
public static SqlParameter MakeInParam(string ParamName, SqlDbType DbTpye, int Size, object Value)
{
return(MakeParam(ParamName, DbTpye, Size, ParameterDirection.Input, Value));
}
/// <summary>
/// 转换字段类型
/// </summary>
/// <param name="sqlDbType"></param>
/// <param name="length"></param>
/// <returns></returns>
public string ConversionFieldType(SqlDbType sqlDbType, int length)
{
string fieldType = string.Empty;
switch (sqlDbType)
{
case SqlDbType.Int:
{
fieldType = $"Int({(length == 0 ? 11 : length)})";
break;
}
case SqlDbType.BigInt:
{
fieldType = $"BigInt({(length == 0 ? 11 : length)})";
break;
}
case SqlDbType.Decimal:
{
fieldType = $"Decimal({(length == 0 ? 8 : length)})";
break;
}
case SqlDbType.Bit:
{
fieldType = $"Bit({(length == 0 ? 2 : length)})";
break;
}
case SqlDbType.VarChar:
{
fieldType = $"VarChar({(length == 0 ? 50 : length)})";
break;
}
case SqlDbType.NVarChar:
{
fieldType = $"NVarChar({(length == 0 ? 50 : length)})";
break;
}
case SqlDbType.Date:
{
fieldType = $"Date";
break;
}
case SqlDbType.Time:
{
fieldType = $"Time";
break;
}
case SqlDbType.DateTime:
{
fieldType = $"DateTime({(length == 0 ? 6 : length)})";
break;
}
default:
{
throw new Exception.DapperExtensionException("不存在的数据类型，请参考文档设置SqlDbType");
}
}
return(fieldType);
}
public StoredProcedureParameterAttribute(SqlDbType dataType)
{
DataType = dataType;
Direction = ParameterDirection.Input;
}
public static Query AddParam(this Query q, string name, SqlDbType dbtype, string val)
{
//q._parameters.Add(new SqlParameter(name, dbtype, 5, val));
return(q);
}
private static bool IsUnicodeType(SqlDbType t)
{
return(t == SqlDbType.NChar || t == SqlDbType.NText || t == SqlDbType.NVarChar);
}
public static void AddWithValueSafe(this SqlParameterCollection target, string parameterName, SqlDbType dbType, object value, string udtTypeName)
{
target.Add(new SqlParameter()
{
SqlDbType = dbType,
ParameterName = parameterName,
Value = value ?? DBNull.Value,
UdtTypeName = udtTypeName
});
}
private static SqlParameter GenerateSqlParameter(string parameterName, object paramValue, bool mandatory, int size,
bool isUserDefinedTableParameter, string udtType, SqlDbType dataType, ParameterDirection direction)
{
var sqlParameter = new SqlParameter("@" + parameterName, paramValue ?? DBNull.Value)
{
Direction = direction,
IsNullable = !mandatory,
Size = SetSize(size, direction),
};
if (isUserDefinedTableParameter)
{
sqlParameter.TypeName = udtType;
}
else
{
sqlParameter.SqlDbType = dataType;
}
return(sqlParameter);
}
public StoredProcedureParameterAttribute(SqlDbType dataType, StoredProcedureParameterOptions options)
{
DataType = dataType;
Options = options;
Direction = ParameterDirection.Input;
}
public SearchCriteriaParameter(string _parameterName, SqlDbType _dbType)
{
parameterName = _parameterName;
dbType = _dbType;
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="ID">ID of the parameter</param>
/// <param name="Type">Database type</param>
/// <param name="Value">Value of the parameter</param>
/// <param name="Direction">Direction of the parameter</param>
/// <param name="ParameterStarter">Parameter starter</param>
public Parameter(string ID, SqlDbType Type, object Value = null, ParameterDirection Direction = ParameterDirection.Input, string ParameterStarter = "@")
: base(ID, Type, Value, Direction, ParameterStarter)
{
}
private void حفظToolStripMenuItem_Click_1(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(textBox1.Text) || string.IsNullOrEmpty(textBox4.Text) || string.IsNullOrEmpty(textBox6.Text) ||
string.IsNullOrEmpty(textBox25.Text))
{
MessageBox.Show("قم باكمال البيانات");
return;
}
// try
{
con.OpenConection();
try
{
//int d = int.Parse(textBox15.Text);
//int d1 = Int32.Parse(textBox19.Text);
string[] s = new string[] { "@em", "@iden", "@pnam", "@gen", "@dob", "@pob", "@ag",
"@datreg", "@ms", "@catcod", "@addw", "@addp", "@comwn", "@comwv", "@conn",
"@prov", "@vill", "@nat", "@rel", "@jo", "@qual", "@gdat" };
string[] s2 = new string[] { textBox3.Text, textBox4.Text, textBox6.Text, comboBox20.Text, textBox1.Text, comboBox4.Text, textBox15.Text,
textBox16.Text, comboBox5.Text, textBox19.Text, textBox21.Text, textBox25.Text, comboBox9.Text, textBox22.Text, comboBox12.Text,
comboBox14.Text, textBox17.Text, comboBox2.Text, comboBox6.Text, comboBox16.Text, comboBox17.Text, textBox11.Text };
SqlDbType[] s3 = new SqlDbType[] { SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar,
SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar,
SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar };
// string[] s2 = new string[] { textBox3.Text.ToString(), textBox4.Text, textBox6.Text, comboBox20.Text, textBox1.Text, comboBox4.Text, d.ToString(), textBox16.Text, comboBox5.Text, d1.ToString(), textBox21.Text, textBox25.Text, comboBox9.Text, textBox22.Text, comboBox12.Text, comboBox14.Text, textBox17.Text, comboBox2.Text, comboBox6.Text, comboBox16.Text, comboBox17.Text, textBox11.Text };
// SqlDbType[] s3 = new SqlDbType[] {SqlDbType.Int, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.Int, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.Int, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar };
con.ExecuteInsertOrUpdateOrDeleteUsingStoredProc("insertPatient", s, s2, s3);
MessageBox.Show("تم حفظ البيانات بنجاح");
}
catch (Exception ex) { MessageBox.Show(ex.ToString()); }
// string s1 = "outer";
// string s2 = "inner";
/* string s = "insert into Registeration_patientRegisteration(employ_code,identity_type,patient_name,gender,date_of_birth,place_of_birth"
+ ",age,date_Regist ,marital_status,catogrical_code,address_working,address_of_patient,communication_way_name,communication_way_value"
+ ",country_name,provinence_name ,vcomboBox16.Textillage_name,nationality,religion,job,gualified_name,graduation_date) values(@em,@iden,@pnam,@gen,@dob,"
+ "@pob,@ag,@datreg,@ms,@catcod,@addw,@addp,@comwn,@comwv,@conn,@prov,@vill,@nat,@rel,@jo,@qual,@gdat)";
+
*/
// cmd4 = new MySqlCommand("insertPatient", con);
// cmd4.CommandType = CommandType.StoredProcedure;
// // cmd4 = new MySqlCommand(s, con);
// cmd4.Prepare();
// cmd4.Parameters.AddWithValue("@em", 1);//1 as a random value
// cmd4.Parameters.AddWithValue("@iden", textBox4.Text);
// // cmd4.Parameters.AddWithValue("@pi", Int32.Parse(textBox5.Text));
// //if (textBox6.Text.Length == 0)
// //{
// // MessageBox.Show("قم بادخال الاسم");
// // }
// cmd4.Parameters.AddWithValue("@pnam", textBox6.Text);
// cmd4.Parameters.AddWithValue("@gen", comboBox20.SelectedItem);
// cmd4.Parameters.AddWithValue("@dob", textBox1.Text);
// cmd4.Parameters.AddWithValue("@pob", comboBox4.SelectedItem);
// cmd4.Parameters.AddWithValue("@ag", Int32.Parse(textBox15.Text));
// cmd4.Parameters.AddWithValue("@datreg", textBox16.Text);
// cmd4.Parameters.AddWithValue("@ms", comboBox5.SelectedItem);
// cmd4.Parameters.AddWithValue("@catcod", Int32.Parse(textBox19.Text));
// cmd4.Parameters.AddWithValue("@addw", textBox21.Text);
// cmd4.Parameters.AddWithValue("@addp", textBox25.Text);
// cmd4.Parameters.AddWithValue("@comwn", comboBox9.SelectedItem);
// cmd4.Parameters.AddWithValue("@comwv", textBox22.Text);
// cmd4.Parameters.AddWithValue("@conn", comboBox12.SelectedItem);
// cmd4.Parameters.AddWithValue("@prov", comboBox14.SelectedItem);
// cmd4.Parameters.AddWithValue("@vill", textBox17.Text);
// cmd4.Parameters.AddWithValue("@nat", comboBox2.SelectedItem);
// cmd4.Parameters.AddWithValue("@rel", comboBox6.SelectedItem);
// cmd4.Parameters.AddWithValue("@jo", comboBox16.Text);
// cmd4.Parameters.AddWithValue("@qual", comboBox17.SelectedItem);
// cmd4.Parameters.AddWithValue("@gdat", textBox11.Text);
// cmd4.ExecuteNonQuery();
// MessageBox.Show("تم حفظ البيانات بنجاح");
///*
// try
// {
// if (radioButton1.Checked == true)
// {
// cmd4.Parameters.AddWithValue("@tp", "enter");
// }
// else if (radioButton2.Checked == true)
// {
// cmd4.Parameters.AddWithValue("@tp", "out");
// }
// cmd4.ExecuteNonQuery();
// }
// catch (Exception ex) { MessageBox.Show(ex.Message); }
// MessageBox.Show("sucess");*/
// }
//// catch (Exception ex)
// {
// // MessageBox.Show(ex.ToString());
// }
// /*validation*/
// /*end of validation*/
// con.CloseConnection();
}
}
public SearchCriteriaParameter(SqlDbType _dbType)
{
dbType = _dbType;
}
// This is a modified version of SmiMetaDataFromSchemaTableRow above
// Since CoreCLR doesn't have GetSchema, we need to infer the MetaData from the CLR Type alone
static internal SmiExtendedMetaData SmiMetaDataFromType(string colName, Type colType)
{
// Determine correct SqlDbType.
SqlDbType colDbType = InferSqlDbTypeFromType_Katmai(colType);
if (InvalidSqlDbType == colDbType)
{
// Unknown through standard mapping, use VarBinary for columns that are Object typed, otherwise we error out.
if (typeof(object) == colType)
{
colDbType = SqlDbType.VarBinary;
}
else
{
throw SQL.UnsupportedColumnTypeForSqlProvider(colName, colType.ToString());
}
}
// Determine metadata modifier values per type (maxlength, precision, scale, etc)
long maxLength = 0;
byte precision = 0;
byte scale = 0;
switch (colDbType)
{
case SqlDbType.BigInt:
case SqlDbType.Bit:
case SqlDbType.DateTime:
case SqlDbType.Float:
case SqlDbType.Image:
case SqlDbType.Int:
case SqlDbType.Money:
case SqlDbType.NText:
case SqlDbType.Real:
case SqlDbType.UniqueIdentifier:
case SqlDbType.SmallDateTime:
case SqlDbType.SmallInt:
case SqlDbType.SmallMoney:
case SqlDbType.Text:
case SqlDbType.Timestamp:
case SqlDbType.TinyInt:
case SqlDbType.Variant:
case SqlDbType.Xml:
case SqlDbType.Date:
// These types require no metadata modifiers
break;
case SqlDbType.Binary:
case SqlDbType.VarBinary:
// source isn't specifying a size, so assume the Maximum
if (SqlDbType.Binary == colDbType)
{
maxLength = SmiMetaData.MaxBinaryLength;
}
else
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
break;
case SqlDbType.Char:
case SqlDbType.VarChar:
// source isn't specifying a size, so assume the Maximum
if (SqlDbType.Char == colDbType)
{
maxLength = SmiMetaData.MaxANSICharacters;
}
else
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
break;
case SqlDbType.NChar:
case SqlDbType.NVarChar:
// source isn't specifying a size, so assume the Maximum
if (SqlDbType.NChar == colDbType)
{
maxLength = SmiMetaData.MaxUnicodeCharacters;
}
else
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
break;
case SqlDbType.Decimal:
// Decimal requires precision and scale
precision = SmiMetaData.DefaultDecimal.Precision;
scale = SmiMetaData.DefaultDecimal.Scale;
break;
case SqlDbType.Time:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
// requires scale
scale = SmiMetaData.DefaultTime.Scale;
break;
case SqlDbType.Udt:
case SqlDbType.Structured:
default:
// These types are not supported from SchemaTable
throw SQL.UnsupportedColumnTypeForSqlProvider(colName, colType.ToString());
}
return(new SmiExtendedMetaData(
colDbType,
maxLength,
precision,
scale,
Locale.GetCurrentCultureLcid(),
SmiMetaData.GetDefaultForType(colDbType).CompareOptions,
false, // no support for multi-valued columns in a TVP yet
null, // no support for structured columns yet
null,
colName,
null,
null,
null));
}
/// <summary>
/// Create input SQL parametet, its name is @ and column name
/// </summary>
/// <param name="columnName">Column name which matches with parameter</param>
/// <param name="dbType">Parameter type</param>
/// <param name="value">Parameter value</param>
/// <returns>Filled SQL parameter</returns>
/// <remarks></remarks>
public SqlParameter CreateSqlParameter(string columnName, SqlDbType dbType, object value)
{
return(CreateSqlParameter(columnName, dbType, value, ParameterDirection.Input));
}
// If we know we're only going to use this object to assign to a specific SqlDbType back end object,
// we can save some processing time by only checking for the few valid types that can be assigned to the dbType.
// This assumes a switch statement over SqlDbType is faster than getting the ClrTypeCode and iterating over a
// series of if statements, or using a hash table.
// NOTE: the form of these checks is taking advantage of a feature of the JIT compiler that is supposed to
// optimize checks of the form '(xxx.GetType() == typeof( YYY ))'. The JIT team claimed at one point that
// this doesn't even instantiate a Type instance, thus was the fastest method for individual comparisions.
// Given that there's a known SqlDbType, thus a minimal number of comparisions, it's likely this is faster
// than the other approaches considered (both GetType().GetTypeCode() switch and hash table using Type keys
// must instantiate a Type object. The typecode switch also degenerates into a large if-then-else for
// all but the primitive clr types.
internal static ExtendedClrTypeCode DetermineExtendedTypeCodeForUseWithSqlDbType(
SqlDbType dbType,
bool isMultiValued,
object value
)
{
ExtendedClrTypeCode extendedCode = ExtendedClrTypeCode.Invalid;
// fast-track null, which is valid for all types
if (null == value)
{
extendedCode = ExtendedClrTypeCode.Empty;
}
else if (DBNull.Value == value)
{
extendedCode = ExtendedClrTypeCode.DBNull;
}
else
{
switch (dbType)
{
case SqlDbType.BigInt:
if (value.GetType() == typeof(Int64))
{
extendedCode = ExtendedClrTypeCode.Int64;
}
else if (value.GetType() == typeof(SqlInt64))
{
extendedCode = ExtendedClrTypeCode.SqlInt64;
}
break;
case SqlDbType.Binary:
case SqlDbType.VarBinary:
case SqlDbType.Image:
case SqlDbType.Timestamp:
if (value.GetType() == typeof(byte[]))
{
extendedCode = ExtendedClrTypeCode.ByteArray;
}
else if (value.GetType() == typeof(SqlBinary))
{
extendedCode = ExtendedClrTypeCode.SqlBinary;
}
else if (value.GetType() == typeof(SqlBytes))
{
extendedCode = ExtendedClrTypeCode.SqlBytes;
}
else if (value.GetType() == typeof(StreamDataFeed))
{
extendedCode = ExtendedClrTypeCode.Stream;
}
break;
case SqlDbType.Bit:
if (value.GetType() == typeof(bool))
{
extendedCode = ExtendedClrTypeCode.Boolean;
}
else if (value.GetType() == typeof(SqlBoolean))
{
extendedCode = ExtendedClrTypeCode.SqlBoolean;
}
break;
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.VarChar:
if (value.GetType() == typeof(string))
{
extendedCode = ExtendedClrTypeCode.String;
}
if (value.GetType() == typeof(TextDataFeed))
{
extendedCode = ExtendedClrTypeCode.TextReader;
}
else if (value.GetType() == typeof(SqlString))
{
extendedCode = ExtendedClrTypeCode.SqlString;
}
else if (value.GetType() == typeof(char[]))
{
extendedCode = ExtendedClrTypeCode.CharArray;
}
else if (value.GetType() == typeof(SqlChars))
{
extendedCode = ExtendedClrTypeCode.SqlChars;
}
else if (value.GetType() == typeof(char))
{
extendedCode = ExtendedClrTypeCode.Char;
}
break;
case SqlDbType.Date:
case SqlDbType.DateTime2:
case SqlDbType.DateTime:
case SqlDbType.SmallDateTime:
if (value.GetType() == typeof(DateTime))
{
extendedCode = ExtendedClrTypeCode.DateTime;
}
else if (value.GetType() == typeof(SqlDateTime))
{
extendedCode = ExtendedClrTypeCode.SqlDateTime;
}
break;
case SqlDbType.Decimal:
if (value.GetType() == typeof(Decimal))
{
extendedCode = ExtendedClrTypeCode.Decimal;
}
else if (value.GetType() == typeof(SqlDecimal))
{
extendedCode = ExtendedClrTypeCode.SqlDecimal;
}
break;
case SqlDbType.Real:
if (value.GetType() == typeof(Single))
{
extendedCode = ExtendedClrTypeCode.Single;
}
else if (value.GetType() == typeof(SqlSingle))
{
extendedCode = ExtendedClrTypeCode.SqlSingle;
}
break;
case SqlDbType.Int:
if (value.GetType() == typeof(Int32))
{
extendedCode = ExtendedClrTypeCode.Int32;
}
else if (value.GetType() == typeof(SqlInt32))
{
extendedCode = ExtendedClrTypeCode.SqlInt32;
}
break;
case SqlDbType.Money:
case SqlDbType.SmallMoney:
if (value.GetType() == typeof(SqlMoney))
{
extendedCode = ExtendedClrTypeCode.SqlMoney;
}
else if (value.GetType() == typeof(Decimal))
{
extendedCode = ExtendedClrTypeCode.Decimal;
}
break;
case SqlDbType.Float:
if (value.GetType() == typeof(SqlDouble))
{
extendedCode = ExtendedClrTypeCode.SqlDouble;
}
else if (value.GetType() == typeof(Double))
{
extendedCode = ExtendedClrTypeCode.Double;
}
break;
case SqlDbType.UniqueIdentifier:
if (value.GetType() == typeof(SqlGuid))
{
extendedCode = ExtendedClrTypeCode.SqlGuid;
}
else if (value.GetType() == typeof(Guid))
{
extendedCode = ExtendedClrTypeCode.Guid;
}
break;
case SqlDbType.SmallInt:
if (value.GetType() == typeof(Int16))
{
extendedCode = ExtendedClrTypeCode.Int16;
}
else if (value.GetType() == typeof(SqlInt16))
{
extendedCode = ExtendedClrTypeCode.SqlInt16;
}
break;
case SqlDbType.TinyInt:
if (value.GetType() == typeof(Byte))
{
extendedCode = ExtendedClrTypeCode.Byte;
}
else if (value.GetType() == typeof(SqlByte))
{
extendedCode = ExtendedClrTypeCode.SqlByte;
}
break;
case SqlDbType.Variant:
// SqlDbType doesn't help us here, call general-purpose function
extendedCode = DetermineExtendedTypeCode(value);
// Some types aren't allowed for Variants but are for the general-purpos function.
// Match behavior of other types and return invalid in these cases.
if (ExtendedClrTypeCode.SqlXml == extendedCode)
{
extendedCode = ExtendedClrTypeCode.Invalid;
}
break;
case SqlDbType.Udt:
throw ADP.DbTypeNotSupported(SqlDbType.Udt.ToString());
case SqlDbType.Time:
if (value.GetType() == typeof(TimeSpan))
{
extendedCode = ExtendedClrTypeCode.TimeSpan;
}
break;
case SqlDbType.DateTimeOffset:
if (value.GetType() == typeof(DateTimeOffset))
{
extendedCode = ExtendedClrTypeCode.DateTimeOffset;
}
break;
case SqlDbType.Xml:
if (value.GetType() == typeof(SqlXml))
{
extendedCode = ExtendedClrTypeCode.SqlXml;
}
if (value.GetType() == typeof(XmlDataFeed))
{
extendedCode = ExtendedClrTypeCode.XmlReader;
}
else if (value.GetType() == typeof(System.String))
{
extendedCode = ExtendedClrTypeCode.String;
}
break;
case SqlDbType.Structured:
if (isMultiValued)
{
if (value is IEnumerable <SqlDataRecord> )
{
extendedCode = ExtendedClrTypeCode.IEnumerableOfSqlDataRecord;
}
else if (value is DbDataReader)
{
extendedCode = ExtendedClrTypeCode.DbDataReader;
}
}
break;
default:
// Leave as invalid
break;
}
}
return(extendedCode);
}
/// <summary>
/// Gets the SQL parameter.
/// </summary>
/// <param name="name">The name.</param>
/// <param name="direction">The direction.</param>
/// <param name="dbType">Type of the db.</param>
/// <param name="size">The size.</param>
/// <param name="value">The value.</param>
/// <returns></returns>
private SqlParameter GetSqlParameter(string name, ParameterDirection direction, SqlDbType dbType, int size, object value)
{
SqlParameter p = new SqlParameter(name, dbType, size);
p.Value = value;
p.Direction = direction;
return(p);
}
