public override object ToDbValue(Type fieldType, object value)
{
if (value == null)
{
return(SqlGeography.Null);
}
if (value is SqlGeography)
{
return(value);
}
if (value is string)
{
var str = value as string;
return(SqlGeography.Parse(str));
}
if (value is byte[])
{
var bin = value as byte[];
var sqlBin = new System.Data.SqlTypes.SqlBytes(bin);
return(SqlGeography.Deserialize(sqlBin));
}
return(base.ToDbValue(fieldType, value));
}
public override object ToDbValue(Type fieldType, object value)
{
if (value == null)
{
return SqlGeography.Null;
}
if (value is SqlGeography)
{
return value;
}
if (value is string)
{
var str = value as string;
return SqlGeography.Parse(str);
}
if (value is byte[])
{
var bin = value as byte[];
var sqlBin = new System.Data.SqlTypes.SqlBytes(bin);
return SqlGeography.Deserialize(sqlBin);
}
return base.ToDbValue(fieldType, value);
}
private static void WriteDll(System.Data.SqlTypes.SqlBytes bytes, string dllFullName)
{
using (var bytestream = new FileStream(dllFullName, FileMode.CreateNew))
{
bytestream.Write(bytes.Value, 0, (int)bytes.Length);
}
}
public static void SaveAssembly(string assemblyName, string destinationPath)
{
string sql = @"SELECT af.name, af.content FROM sys.assemblies a INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id WHERE a.name = @assemblyname";
using (SqlConnection conn = new SqlConnection("context connection=true")) //Create current context connection
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlParameter param = new SqlParameter("@assemblyname", System.Data.SqlDbType.NVarChar);
param.Value = assemblyName;
// param.Size = 128;
cmd.Parameters.Add(param);
cmd.Connection.Open(); //Open the context connetion
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read()) //Iterate through assembly files
{
string assemblyFileName = reader.GetString(0); //get assembly file name from the name (first) column
System.Data.SqlTypes.SqlBytes bytes = reader.GetSqlBytes(1); //get assembly binary data from the content (second) column
string outputFile = System.IO.Path.Combine(destinationPath, assemblyFileName);
SqlContext.Pipe.Send(string.Format("Exporting assembly file [{0}] to [{1}]", assemblyFileName, outputFile)); //Send information about exported file back to the calling session
using (System.IO.FileStream byteStream = new System.IO.FileStream(outputFile, System.IO.FileMode.CreateNew))
{
byteStream.Write(bytes.Value, 0, (int)bytes.Length);
byteStream.Close();
}
}
}
}
conn.Close();
}
} // End Sub SaveAssembly
static void Main(string[] args)
{
System.Data.SqlTypes.SqlBytes bytes = null;
int imageNum = 0;
using (SqlConnection conn = new SqlConnection("Data Source=192.168.2.81;Initial Catalog=RAMSSERVER;User ID=xianuser;Password=xianuser"))
{
String sql = "select * from tb_lte_stati_amr_log where ifileid = 804302556";
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
bytes = dr.GetSqlBytes(imageNum + 7);//3是varbinary列从零开始的列序号。bytes.Stream能返回一个用于读取的Stream对象
}
}
}
byte[] byteSource = bytes.Buffer;
byte[] byteDecompress = ZlibHelper.DecompressBytes(byteSource);
string imageStr = BitConverter.ToString(byteDecompress).Replace("-", "");
string gongText = "5F2502XXX";
bool flog = imageStr.Contains(gongText);
if (byteDecompress != null)
{
Console.WriteLine(imageStr);
}
Console.ReadKey();
}
public System.IO.MemoryStream BlobRead(String tableName, String columnName, String criteria)
{
System.IO.MemoryStream dataStream = new System.IO.MemoryStream();
String selectStatement = "SELECT " + columnName + " FROM " + tableName + " WHERE " + criteria;
System.Data.SqlClient.SqlCommand selectCommand = null;
System.Data.SqlClient.SqlDataReader dataReader = null;
try {
OnDemandOpen();
selectCommand = (System.Data.SqlClient.SqlCommand)CreateCommand(selectStatement);
dataReader = selectCommand.ExecuteReader(System.Data.CommandBehavior.Default);
if (dataReader.Read())
{
System.Data.SqlTypes.SqlBytes sourceBytes = dataReader.GetSqlBytes(0);
if (sourceBytes.Buffer != null)
{
dataStream = new System.IO.MemoryStream(sourceBytes.Buffer);
}
else
{
dataStream = new System.IO.MemoryStream();
}
}
}
catch (Exception sqlException) {
SetLastException(sqlException);
}
finally {
if (dataReader != null)
{
dataReader.Close();
} // end if
if (selectCommand != null)
{
selectCommand.Dispose();
} // end if
dataReader = null;
selectCommand = null;
OnDemandClose();
}
return(dataStream);
}
internal static byte[] DbValueToBytes(SqlDataReader dr, string columnName)
{
byte[] r = null;
int ordinal = dr.GetOrdinal(columnName);
if (AppShared.IsNotNull(dr[ordinal]))
{
System.Data.SqlTypes.SqlBytes sqlData = dr.GetSqlBytes(ordinal);
r = sqlData.Value;
}
return(r);
}
/// <summary>
/// Gets the value of the specified binary column as <see cref="System.Byte"/>[].
/// </summary>
/// <param name="sqlDr">The data reader.</param>
/// <param name="name">The column name.</param>
public static byte[] GetSqlBytes(this SqlDataReader sqlDr, string name)
{
if (sqlDr is null)
{
throw new ArgumentNullException(nameof(sqlDr));
}
if (string.IsNullOrWhiteSpace(name))
{
throw new ArgumentNullException(nameof(name));
}
var ord = sqlDr.GetOrdinal(name); // Throws IndexOutOfRangeException
if (sqlDr.IsDBNull(ord))
{
return(null);
}
System.Data.SqlTypes.SqlBytes val = sqlDr.GetSqlBytes(ord);
return(val.IsNull ? null : val.Buffer);
}
public static System.Data.SqlTypes.SqlBytes Unzip(System.Data.SqlTypes.SqlBytes input)
{
// Open an existing zip file for reading
ZipStorer zip = ZipStorer.Open(input.Stream, FileAccess.Read);
// Read the central directory collection
List <ZipStorer.ZipFileEntry> dir = zip.ReadCentralDir();
System.Data.SqlTypes.SqlBytes sqlBytes = new System.Data.SqlTypes.SqlBytes();
// Look for the desired file
foreach (ZipStorer.ZipFileEntry entry in dir)
{
// File found, extract it
var unzippedStream = new MemoryStream();
zip.ExtractFile(entry, unzippedStream);
sqlBytes = new System.Data.SqlTypes.SqlBytes(unzippedStream);
break;
}
zip.Close();
return(sqlBytes);
}
private void btn_driving_times_Click(object sender, EventArgs e)
{
// i diadikasia afti apeti reference tou Microsoft.SqlServer.Types;
// opou exei mesa to SqlGeometry pou theloume na xrisimopoisoume
// Ta bimata einai
// 1. ypologisomos tou polygonou (sqlgeometry) apo to webservice TERRA apo 2 inputs CenterPoint + DriveTime (mins)
// 2. ypologismos tou minX,minY,maxX,maxY tou polygonou.
// 3. select * from pois in database where poiX>=minX and poiY>=minY and poiX<=maxX and poiY<=maxY
// to select tha mas ferei osa simeia einai sto extent tou polygonou, periorizontas ton arithmo ton simeion.
// Tora prepei na ta kanoume intersect ena ena na doume pia simeia einai mesa sto polygono.
// 4. Epistrefoume sto site osa pois kanoun intersect me to polygono
// 1. ypologisomos tou polygonou (sqlgeometry) apo to webservice TERRA
xoServices.xoServiceSoapClient newRequest = new xoServices.xoServiceSoapClient("xoServiceSoap");
// oi 3 parametri einai
double x = double.Parse(center_x.Text);
double y = double.Parse(center_y.Text);
string drivemin = txt_drivemin.Text; // <--- PROSOXH afto einai STRING
SqlGeometry centerPoint = SqlGeometry.Point(x, y, 2100);
//to sqlgeometry prepei na ginei byte[] gia na perasei sto webservice
List<byte[]> pointGeom = new List<byte[]>();
pointGeom.Add(centerPoint.STAsBinary().Buffer);
xoServices.webResponse tmpRes = newRequest.getDrivingTimesForGeometries("", "", pointGeom.ToArray(), drivemin, 30, 2100);
if (tmpRes.resType == xo_test_app.xoServices.webResponseType.Ok)
{
xoServices.terraFeature[] results = (xoServices.terraFeature[])tmpRes.resObject;
System.Data.SqlTypes.SqlBytes sBytes = new System.Data.SqlTypes.SqlBytes(results[0].geometryWKB);
// to proto result einai to polygono pou theloume.
SqlGeometry polygDrive = SqlGeometry.STGeomFromWKB(sBytes, 2100);
// tora exoume polygDrive. prepei na kanoume intersect ta simeia tis baseis
int MinX = 0, MinY = 0, MaxX = 0, MaxY = 0;
getBoundsFromPolygon(polygDrive, ref MinX, ref MinY, ref MaxX, ref MaxY);
// SELECT * FROM POIS WHERE poiX >= MinX AND poiX <=MaxX AND poiY <= MaxY AND poiY >=MinY
// FOR EACH FOUND POI
int poiX = 0, poiY = 0;
SqlGeometry poiGeom = SqlGeometry.Point(poiX, poiY, 2100);
if (polygDrive.STIntersects(poiGeom))
{
// FOUND VALID POI INTO DRIVING TIME
}
//NEXT
}
else
{
// error on server
txt_reverse.Text = tmpRes.resDescription;
}
}
